'encoding UTF-8  Do not remove or change this line!
'*************************************************************************
'
'  Licensed to the Apache Software Foundation (ASF) under one
'  or more contributor license agreements.  See the NOTICE file
'  distributed with this work for additional information
'  regarding copyright ownership.  The ASF licenses this file
'  to you under the Apache License, Version 2.0 (the
'  "License"); you may not use this file except in compliance
'  with the License.  You may obtain a copy of the License at
'  
'    http://www.apache.org/licenses/LICENSE-2.0
'  
'  Unless required by applicable law or agreed to in writing,
'  software distributed under the License is distributed on an
'  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
'  KIND, either express or implied.  See the License for the
'  specific language governing permissions and limitations
'  under the License.
'
'*************************************************************************
'*
'* short description : Dbase function test
'*
'\***********************************************************************
global gFileName as string
sub db_DbaseFunction(sfilename as string)

'/// this test need a datasource TT1 and the table TT_Func1 from dbaccess/optional/input/dbase_datasource/TT_Func1.dbf
'/// see http://dba.openoffice.org/specifications/file_based_functions.html for details for each function

    gFileName = sfilename    

    call tLOWER()
    call tLCASE()
    call tUPPER()
    call tUCASE()
    call tASCII()
    call tLENGTH()
    call tOCTET_LENGTH()
    call tCHAR_LENGTH()
    call tCHARACTER_LENGTH()
    call tCHAR()
    call tCONCAT()
    call tLOCATE()
    call tSUBSTRING1()
    call tSUBSTRING2()
    call tSUBSTRING3()    
    call tLTRIM()
    call tRTRIM()
    call tSPACE()
    call tREPLACE()
    call tREPEAT()
    call tINSERT()
    call tLEFT()
    call tRIGHT()    
'-------------------------------------------------------------------------        
'Numeric fucntions
'-------------------------------------------------------------------------       
    call tABS()
    call tSIGN()   
    call tMOD()
    call tFLOOR()
    call tCEILING()
    call tROUND1()
    call tROUND2()
    call tEXP()  
    call tLN()
    call tLOG1()
    call tLOG2()    
    call tLOG10()
    call tPOWER()       
    call tSQRT()       
    call tPI()
    call tCOS()
    call tSIN()
    call tTAN()
    call tACOS()
    call tASIN()    
    call tATAN()        
    call tATAN2()        
    call tDEGREES()        
    call tRADIANS()
'-------------------------------------------------------------------------        
'Date / Time fucntions
'-------------------------------------------------------------------------
    call tDAYOFWEEK()
    call tDAYOFMONTH()
    call tDAYOFYEAR()
    call tMONTH()
    call tDAYNAME()
    call tMONTHNAME()
    call tQUARTER()
    call tWEEK()
    call tWEEK2()
    call tYEAR()
    call tHOUR()
    call tMINUTE()
    call tSECOND()
    call tCURDATE()
    call tCURTIME()
    call tNOW()
    
end sub
'-------------------------------------------------------------------------
testcase tLOWER()
    
    sInit(gFileName,"TT_Func1")    
    sSet("LOWER(f_text)")    
    if fExcecute then
         sCheckValue("text")
    endif    
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tLCASE()
    
    sInit(gFileName,"TT_Func1")    
    sSet("LCASE(f_text)")    
    if fExcecute then
        sCheckValue("text")
    endif    
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tUPPER()
    
    sInit(gFileName,"TT_Func1")
    sSet("UPPER(f_text)")    
    if fExcecute then
        sCheckValue("TEXT") 
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tUCASE()
    
    sInit(gFileName,"TT_Func1")
    sSet("UCASE(f_text)")    
    if fExcecute then
        sCheckValue("TEXT")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tASCII()
    
    sInit(gFileName,"TT_Func1")
    sSet("ASCII(f_text)")    
    if fExcecute then
        sCheckValue("116")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLENGTH()
    
    sInit(gFileName,"TT_Func1")
    sSet("LENGTH(f_text)")       
    if fExcecute then
        sCheckValue("4") 
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tOCTET_LENGTH()
    
    sInit(gFileName,"TT_Func1")
    sSet("OCTET_LENGTH(f_text)")        
    if fExcecute then
        sCheckValue("4")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tCHAR_LENGTH()
    
    sInit(gFileName,"TT_Func1")
    sSet("CHAR_LENGTH(f_text)")    
    if fExcecute then
        sCheckValue("4")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tCHARACTER_LENGTH()
    
    sInit(gFileName,"TT_Func1")
    sSet("CHARACTER_LENGTH(f_text)")    
    if fExcecute then
        sCheckValue("4")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tCHAR()
    
    sInit(gFileName,"TT_Func1")
    sSet("CHAR(120,121)") ' xy
    if fExcecute then    
        sCheckValue("xy")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tCONCAT()
    
    sInit(gFileName,"TT_Func1")
    sSet("CONCAT(f_text,f_text)")    
    if fExcecute then
        sCheckValue("texttext")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLOCATE()
    
    sInit(gFileName,"TT_Func1")
    sSet("LOCATE('ex',f_text)")    
    if fExcecute then
        sCheckValue("2")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tSUBSTRING1()
    
    sInit(gFileName,"TT_Func1")
    sSet("SUBSTRING(f_text,2)")    
    if fExcecute then
        sCheckValue("ext")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tSUBSTRING2()
    
    sInit(gFileName,"TT_Func1")
    sSet("SUBSTRING(f_text,2,2)")    
    if fExcecute then
        sCheckValue("ex")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tSUBSTRING3()
    
    sInit(gFileName,"TT_Func1")
    sSet("SUBSTRING(f_text FROM 2 FOR 2)")    
    if fExcecute then
        sCheckValue("ex")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tLTRIM()
    
    sInit(gFileName,"TT_Func1")
    sSet("LTRIM('   text ')")    
    if fExcecute then
        sCheckValue("text ")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tRTRIM()
    
    sInit(gFileName,"TT_Func1")
    sSet("RTRIM(' text  ')")    
    if fExcecute then
        sCheckValue(" text")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tSPACE()
    
    sInit(gFileName,"TT_Func1")
    sSet("SPACE(5)")    
    if fExcecute then
        sCheckValue("     ")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tREPLACE()
    
    sInit(gFileName,"TT_Func1")
    sSet("REPLACE(f_text,'ex','xe')")    
    if fExcecute then
        sCheckValue("txet")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tREPEAT()
    
    sInit(gFileName,"TT_Func1")
    sSet("REPEAT(f_text,5)")    
    if fExcecute then
        sCheckValue("texttexttexttexttext")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tINSERT()
    
    sInit(gFileName,"TT_Func1")
    sSet("INSERT(f_text,2,2,'tt')")    
    if fExcecute then
        sCheckValue("tttt")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLEFT()
    
    sInit(gFileName,"TT_Func1")
    sSet("LEFT(f_text,2)")    
    if fExcecute then
        sCheckValue("te")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tRIGHT()
    
    sInit(gFileName,"TT_Func1")
    sSet("RIGHT(f_text,2)")    
    if fExcecute then
        sCheckValue("xt")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------        
'Numeric fucntions
'-------------------------------------------------------------------------       
testcase tABS()
    
    sInit(gFileName,"TT_Func1")
    sSet("ABS('1,6')")    
    if fExcecute then
        sCheckValue("1")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tSIGN()
    
    sInit(gFileName,"TT_Func1")
    sSet("SIGN('-2')")    
    if fExcecute then
        sCheckValue("-1")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tMOD()
    
    sInit(gFileName,"TT_Func1")
    sSet("MOD(1,0)")    
    if fExcecute then
        'sCheckValue("1.#NAN") ' this doens't work because sometimes the result is 
        '1.#NAN and sometimes it is -1.#NAN. This have to be investigate in the future.
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tFLOOR()
    
    sInit(gFileName,"TT_Func1")
    sSet("FLOOR(1.789)")    
    if fExcecute then
        sCheckValue("1")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tCEILING()
    
    sInit(gFileName,"TT_Func1")
    sSet("CEILING(1.789)")    
    if fExcecute then
        sCheckValue("2")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tROUND1()
    
    sInit(gFileName,"TT_Func1")
    sSet("ROUND(1.789)")    
    if fExcecute then
        sCheckValue("2")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tROUND2()
    
    sInit(gFileName,"TT_Func1")
    sSet("ROUND(1.789,1)")    
    if fExcecute then
        sCheckValue("1.8")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tEXP()
    
    sInit(gFileName,"TT_Func1")
    sSet("EXP(1)")    
    if fExcecute then
        sCheckValue("2.72")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLN()
     
    sInit(gFileName,"TT_Func1")
    sSet("LN(1)")    
    if fExcecute then
        sCheckValue("0")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLOG1()
    
    sInit(gFileName,"TT_Func1")
    sSet("LOG(1)")    
    if fExcecute then
        sCheckValue("0")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLOG2()
    
    sInit(gFileName,"TT_Func1")
    sSet("LOG(2,3)")    
    if fExcecute then
        sCheckValue("0.63")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tLOG10()
    
    sInit(gFileName,"TT_Func1")
    sSet("LOG10(1)")    
    if fExcecute then
        sCheckValue("0")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tPOWER()
    
    sInit(gFileName,"TT_Func1")
    sSet("POWER(2,3)")    
    if fExcecute then
        sCheckValue("8")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tSQRT()
    
    sInit(gFileName,"TT_Func1")
    sSet("SQRT(2)")    
    if fExcecute then
        sCheckValue("1.41")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------       
testcase tPI()
    
    sInit(gFileName,"TT_Func1")
    sSet("PI()")    
    if fExcecute then
        sCheckValue("3.14")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tCOS()
    
    sInit(gFileName,"TT_Func1")
    sSet("COS(2)")    
    if fExcecute then
        sCheckValue("-0.42")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tSIN()
    
    sInit(gFileName,"TT_Func1")
    sSet("SIN(2)")    
    if fExcecute then
        sCheckValue("0.91")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tTAN()
    
    sInit(gFileName,"TT_Func1")
    sSet("TAN(2)")    
    if fExcecute then
        sCheckValue("-2.19")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tACOS()
    
    sInit(gFileName,"TT_Func1")
    sSet("ACOS(0.2)")    
    if fExcecute then
        sCheckValue("1.37")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tASIN()
    
    sInit(gFileName,"TT_Func1")
    sSet("ASIN(0.2)")    
    if fExcecute then
        sCheckValue("0.2")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tATAN()
    
    sInit(gFileName,"TT_Func1")
    sSet("ATAN(2)")    
    if fExcecute then
        sCheckValue("1.11")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tATAN2()
    
    sInit(gFileName,"TT_Func1")
    sSet("ATAN2(2,2)")    
    if fExcecute then
        sCheckValue("0.79")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tDEGREES()
    
    sInit(gFileName,"TT_Func1")
    sSet("DEGREES(2)")    
    if fExcecute then
        sCheckValue("114.59")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tRADIANS()
    
    sInit(gFileName,"TT_Func1")
    sSet("RADIANS(2)")    
    if fExcecute then
        sCheckValue("0.03")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------        
'Date / Time fucntions
'-------------------------------------------------------------------------
testcase tDAYOFWEEK()
    
    sInit(gFileName,"TT_Func1")
    sSet("DAYOFWEEK('2003-10-13')")    
    if fExcecute then
        sCheckValue("2")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tDAYOFMONTH()
    
    sInit(gFileName,"TT_Func1")
    sSet("DAYOFMONTH('2003-10-13')")    
    if fExcecute then
        sCheckValue("13")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tDAYOFYEAR()
    
    sInit(gFileName,"TT_Func1")
    sSet("DAYOFYEAR('2003-10-13')")    
    if fExcecute then
        sCheckValue("286")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tMONTH()
    
    sInit(gFileName,"TT_Func1")
    sSet("MONTH('2003-10-13')")    
    if fExcecute then
        sCheckValue("10")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tDAYNAME()
    
    sInit(gFileName,"TT_Func1")
    sSet("DAYNAME('2003-10-13')")    
    if fExcecute then
        sCheckValue("Monday")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tMONTHNAME()
    
    sInit(gFileName,"TT_Func1")
    sSet("MONTHNAME('2003-10-13')")    
    if fExcecute then
        sCheckValue("October")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tQUARTER()
    
    sInit(gFileName,"TT_Func1")
    sSet("QUARTER('2003-10-13')")    
    if fExcecute then
        sCheckValue("4")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tWEEK()
    
    sInit(gFileName,"TT_Func1")
    sSet("WEEK('2003-10-13')")    
    if fExcecute then
        sCheckValue("42")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tWEEK2()
    
    sInit(gFileName,"TT_Func1")
    sSet("WEEK('2003-10-13',1)")    
    if fExcecute then
        sCheckValue("41")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tYEAR()
    
    sInit(gFileName,"TT_Func1")
    sSet("YEAR('2003-10-13')")    
    if fExcecute then
        sCheckValue("2003")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tHOUR()
    
    sInit(gFileName,"TT_Func1")
    sSet("HOUR('12:14:25')")    
    if fExcecute then
        sCheckValue("12")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tMINUTE()
    
    sInit(gFileName,"TT_Func1")
    sSet("MINUTE('12:14:25')")    
    if fExcecute then
        sCheckValue("14")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tSECOND()
    
    sInit(gFileName,"TT_Func1")
    sSet("SECOND('12:14:25')")    
    if fExcecute then
        sCheckValue("25")
    endif
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tCURDATE()
    
    sInit(gFileName,"TT_Func1")
    sSet("CURDATE()")    
    fExcecute 
    'the value can't be checked 
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tCURTIME()
    
    sInit(gFileName,"TT_Func1")
    sSet("CURTIME()")    
    fExcecute
    'the value can't be checked
    sDeInit()

endcase
'-------------------------------------------------------------------------
testcase tNOW()
    
    sInit(gFileName,"TT_Func1")
    sSet("NOW()")    
    fExcecute
    'the value can't be checked        
    sDeInit()

endcase
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
'-----------P R I V A T E    F U N C T I O N S----------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
sub sSet( sString )
    Kontext "QueryDesignCriterion"                           
        '/// set value for the field
        printlog "set " + sString  + " for the field"
        Field.setText(sString)
        sleep(1)
end sub          
'-------------------------------------------------------------------------
function fExcecute

    Dim bOK as boolean    
    bOK = true
    Kontext "Toolbar"
        '/// Execute the query
        printlog "Execute the query"
        ExecuteBtn.Click
        sleep(3)
    Kontext "MessageBox"
        if MessageBox.exists(3) then
            warnlog "error message when executing query"
            MessageBox.OK
            bOK = false
        endif        
    Kontext "TableView"
        if NOT AllRecords.Exists(3) then
            warnlog "Execution of a query failed!"
            bOK = false
        endif
    fExcecute = bOK
end function
'-------------------------------------------------------------------------
sub sInit( gFileName, sTableName )
    
    call fOpenDatabase(gFileName)
    
    call fOpenNewQueryDesign

    call fChooseTableInAddTableDialog(sTableName)
    
end sub                
'-------------------------------------------------------------------------               
sub sDeInit

    call fCloseQueryDesign
    call fCloseDatabase
    
end sub
'-------------------------------------------------------------------------
sub sCheckValue(sValue)
    Dim sClipboard as string
    Dim i as integer
    '/// check if the value is right
    printlog "check if the value is " + sValue
    Kontext "TableView"        
        if AllRecords.Exists(3) then
            Kontext "TableView"                
                TableView.TypeKeys "<HOME>" , true
                wait(100)
                TableView.TypeKeys "<SHIFT END>" , true
                wait(100)
                TableView.TypeKeys "<MOD1 C>" , true
                wait(100)
                sClipboard = GetClipboard
                i = instr(sClipboard,",")
                if i <> 0 then
                    sClipboard = Left(sClipboard,i -1) + "." + Right(sClipboard,len(sClipboard) - i) 
                end if
                if StrComp (sValue, sClipboard , 1) <> 0 then 
                    warnlog "it should " + sValue + " it is " + GetClipboard
                endif
        else
            warnlog "Execution of a query failed!"
        end if  
end sub