Tdatabase & Filter

[Silvio.Falconi]

I must make a count of number of record there are on dbf
and I make an filter with some paramters

sample : I check if field month(DATA) is the selected


It not make error of Invalid filter but not execute the filter why ?

Code: Select all  Expand view  RUN

#include "fivewin.ch"


FUNCTION Main()


   RddSetDefault( "DBFCDX" )

   SetHandleCount( 100 )

   SET DATE FORMAT "dd-mm-yyyy"
   SET DELETED     ON
   SET CENTURY     ON
   SET EPOCH TO    year( date() ) - 20
   SET MULTIPLE    OFF

   
      testfilter ()

RETURN nil

Function testfilter()
local aContMesi:= {.f.,.F.,.f.,.f.,.f.,.f.,.f.,.f.,.f.,.t.,.t.,.t.}
local cFilter:='"'
local oConteggio




// mesi



   If aContmesi[1]=.t.
          nMese:=1
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
          cFilter += " .and. "
    Endif


    If aContmesi[2]=.t.
       nMese:=2
       cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
        cFilter += " .and. "
    Endif



    If aContmesi[3]=.t.
          nMese:=3
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
          cFilter += " .and. "
    Endif


    If aContmesi[4]=.t.
          nMese:=4
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
          cFilter += " .and. "
    Endif



    If aContmesi[5]=.t.
       nMese:=5
       cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
       cFilter += " .and. "
    Endif



    If aContmesi[6]=.t.
          nMese:=6
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
         cFilter += " .and. "
    Endif



    If aContmesi[1]=.t.
          nMese:=1
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
         cFilter += " .and. "
    Endif



    If aContmesi[7]=.t.
       nMese:=7
       cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
       cFilter += " .and. "
    Endif


    If aContmesi[8]=.t.
          nMese:=8
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
         cFilter += " .and. "
    Endif

    If aContmesi[9]=.t.
          nMese:=9
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
          cFilter += " .and. "
    Endif



    If aContmesi[10]=.t.
       nMese:=10
       cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
       cFilter += " .and. "
    Endif



    If aContmesi[11]=.t.
          nMese:=11
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
          cFilter += " .and. "
    Endif



     If aContmesi[12]=.t.
          nMese:=12
          cFilter += " Month(FIELD->data) =='"+alltrim(str(nMese))+"'"
    Endif

      cFilter+=  '"'

   ?cFilter



   oConteggio:=TDatabase():Open( , "Lotto", "DBFCDX", .T. )
   oConteggio:setorder(1)
   oConteggio:setFilter(cFilter)
   oConteggio:gotop()
  // xbrowser oConteggio

   nRecords:=oConteggio:Reccount()
   oConteggio:close()

    msginfo(nrecords)

return nil
 

I tried with Month(FIELD->data) and Month(data) but not execute the filter

Any solution pls

[Antonio Linares]

Dear Silvio,

Forget about filters, use indexes

[Silvio.Falconi]

Dear Silvio,

Forget about filters, use indexes

Antonio,
I have to create a statistical archive from the large archive.



I take the criteria on these listbox

To do this I have to filter each record if they match some criteria:
the months selected,
the days of the week selected,
the number of days selected,
the indexes selected. (Generally there are 156 annual indexes about 13/14 monthly indexes. )

So I have to filter all these criteria and then create an archive in an array and then create an archive lottotemp.dbf and return the number of selected records

[Silvio.Falconi]

Sorry this is the test.prg modified

Code: Select all  Expand view  RUN

#include "fivewin.ch"


FUNCTION Main()


   RddSetDefault( "DBFCDX" )

   SetHandleCount( 100 )

   SET DATE FORMAT "dd-mm-yyyy"
   SET DELETED     ON
   SET CENTURY     ON
   SET EPOCH TO    year( date() ) - 20
   SET MULTIPLE    OFF


      testfilter ()

RETURN nil

Function testfilter()
local aContMesi:= {.t.,.t.,.f.,.f.,.f.,.f.,.f.,.f.,.f.,.f.,.f.,.f.}  // for demo
local cFilter:="'"
local oConteggio

// mesi

     If aContmesi[1]=.t.
          nMese:=1
          cFilter += "month(data) ="+alltrim(str(nMese))
       Endif


       If aContmesi[1]=.t.  .and. aContmesi[2]=.t.
           cFilter += " .OR. "
        Endif


    If aContmesi[2]=.t.
       nMese:=2
       cFilter += "month(data) ="+alltrim(str(nMese))

    Endif

      If aContmesi[2]=.t.  .and. aContmesi[3]=.t.
           cFilter += " .OR. "
        Endif

    If aContmesi[3]=.t.
          nMese:=3
          cFilter += "month(data) ="+alltrim(str(nMese))
       Endif

       If aContmesi[3]=.t.  .and. aContmesi[4]=.t.
           cFilter += " .OR. "
        Endif

    If aContmesi[4]=.t.
          nMese:=4
          cFilter += "month(data) ="+alltrim(str(nMese))
       Endif

         If aContmesi[4]=.t.  .and. aContmesi[5]=.t.
           cFilter += " .OR. "
        Endif


    If aContmesi[5]=.t.
       nMese:=5
       cFilter += "month(data) ="+alltrim(str(nMese))

    Endif

        If aContmesi[5]=.t.  .and. aContmesi[6]=.t.
           cFilter += " .OR. "
        Endif


    If aContmesi[6]=.t.
          nMese:=6
          cFilter += "month(data) ="+alltrim(str(nMese))
    Endif

         If aContmesi[6]=.t.  .and. aContmesi[7]=.t.
           cFilter += " .OR. "
        Endif

    If aContmesi[7]=.t.
       nMese:=7
       cFilter += "month(data) ="+alltrim(str(nMese))
    Endif

          If aContmesi[7]=.t.  .and. aContmesi[8]=.t.
           cFilter += " .OR. "
        Endif

    If aContmesi[8]=.t.
          nMese:=8
          cFilter += "month(data) ="+alltrim(str(nMese))
      Endif

           If aContmesi[8]=.t.  .and. aContmesi[9]=.t.
           cFilter += " .OR. "
        Endif


    If aContmesi[9]=.t.
          nMese:=9
          cFilter += "month(data) ="+alltrim(str(nMese))

    Endif

          If aContmesi[9]=.t.  .and. aContmesi[10]=.t.
           cFilter += " .OR. "
        Endif

    If aContmesi[10]=.t.
       nMese:=10
       cFilter += "month(data) ="+alltrim(str(nMese))
    Endif

      If aContmesi[10]=.t.  .and. aContmesi[11]=.t.
           cFilter += " .OR. "
        Endif


    If aContmesi[11]=.t.
          nMese:=11
          cFilter += "month(data) ="+alltrim(str(nMese))
    Endif

          If aContmesi[11]=.t.  .and. aContmesi[12]=.t.
           cFilter += " .OR. "
        Endif

     If aContmesi[12]=.t.
          nMese:=12
          cFilter += "month(data) ="+alltrim(str(nMese))
    Endif

      cFilter+= "'"

   ?cFilter


   oConteggio:=TDatabase():Open( , "Lotto", "DBFCDX", .T. )
   oConteggio:setorder(0)
   oConteggio:setFilter(cFilter )
   oConteggio:gotop()

  //xbrowser oConteggio

   nRecords:=oConteggio:ordkeycount()
   oConteggio:close()

    msginfo(nrecords)

return nil
 

But Not run also

If I made a test
oConteggio:setFilter("month(data) =10") run ok

why the script not run ok ?

[nageswaragunupudi]

Please remove all that code.
One single line of code is all that you need.

METHOD-1

Code: Select all  Expand view  RUN

  oDbf:SetFilter( FW_ValToExp( aContMesi ) + "[MONTH(DATE)]" )

OR
METHOD-2

Code: Select all  Expand view  RUN

  oDbf:Exec( <||
      SET FILTER TO aContMesi[ MONTH( FIELD->DATE ) ]
      return nil
      > )
 

followed by

Code: Select all  Expand view  RUN

oDbf:GoTop()
? oDbf:KeyCount()
 

[Silvio.Falconi]

Please remove all that code.
One single line of code is all that you need.

METHOD-1

Code: Select all  Expand view  RUN

  oDbf:SetFilter( FW_ValToExp( aContMesi ) + "[MONTH(DATE)]" )

OR
METHOD-2

Code: Select all  Expand view  RUN

  oDbf:Exec( <||
      SET FILTER TO aContMesi[ MONTH( FIELD->DATE ) ]
      return nil
      > )
 

followed by

Code: Select all  Expand view  RUN

oDbf:GoTop()
? oDbf:KeyCount()
 

Sorry Nages
I not understood where I make error

error

Code: Select all  Expand view  RUN

  Error description: Error BASE/1132  Bound error: array access
   Args:
     [   1] = A   {.T.,.T.,.T.,.F.,.F.,.F.} length: 6
     [   2] = N   10

 

the array of day name of week are six
{.T.,.T.,.T.,.F.,.F.,.F.}
because In italy we have

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

So six day no 7

So I made DOW( FIELD->DATA )-1 is it right ?




I have to do a filter more complex

1) Init records
2) End records
3) month(data)
4) dow(data)
5) day(data)
6) monthly indices - I add a new filed to dbf IDX ( numeric 2) from 1 to 15



test.prg

Code: Select all  Expand view  RUN

#include "fivewin.ch"


FUNCTION Main()


   RddSetDefault( "DBFCDX" )

   SetHandleCount( 100 )

   SET DATE FORMAT "dd-mm-yyyy"
   SET DATE ITALIAN
   SET DELETED     ON
   SET CENTURY     ON
   SET EPOCH TO    year( date() ) - 20
   SET MULTIPLE    OFF


      test()

RETURN nil

Function test()
   local oDbf
   local cSection:="ARCHIVIO"
   local cIniFile  :=  cFilePath( GetModuleFileName( GetInstance() ) ) + "lotto.mem"
   local cMese    := GetPvProfString(cSection, "Mesi","", cIniFile)
   local cGiorno  := GetPvProfString(cSection, "Giorni","", cIniFile)
   local cdataday := GetPvProfString(cSection, "Day","", cIniFile)
   local cIndici  := GetPvProfString(cSection, "Indici","", cIniFile)

   local nInit   := VAL(GetPvProfString(cSection, "Inizio","", cIniFile))
   local nEnd    := VAL(GetPvProfString(cSection, "Fine","", cIniFile))
   local nLastRecords := VAL(GetPvProfString(cSection, "LastRecords","", cIniFile))


   local aCountMesi :={}
   local aCountGiorni:={}
   local aCountDate:={}
   local aCountIndici:={}
   local n,k,j,y

      For n=1 to 12// len(aMesi)
         aadd(aCountMesi,IIF(SubStr(cMese, n, 1)= "1",.t.,.f.))
      next
      For k=1 to 6//len(aGiorni)   LMMGVS
         aadd(aCountGiorni,IIF(SubStr(cGiorno, k, 1)= "1",.t.,.f.))
      next
      For j=1 to 31// len(adataday)
         aadd(aCountdate,IIF(SubStr(cdataday, j, 1)= "1",.t.,.f.))
      next
      For y=1 to 15//len(aIndici)
         aadd(aCountIndici,IIF(SubStr(cIndici, y, 1)= "1",.t.,.f.))
      next


    oDbf:=TDatabase():Open( , "Lotto", "DBFCDX", .T. )


    IF nLastRecords > 0
       nInit:=oDbf:lastrec()-nLastRecords
       nEnd:= oDbf:lastrec()
    ENDIF

     oDbf:Exec( <||
      oDbf:Exec( <||
     SET FILTER TO   (  nInit <= RECNO() .AND.             ;
                        nEnd >=  RECNO() .AND.             ;
                        aCountMesi[ MONTH( FIELD->DATA ) ]  .AND. ;
                        aCountGiorni[ DOW( FIELD->DATA )-1 ]  .AND. ;
                        aCountGiorni[ DAY( FIELD->DATA ) ]  .AND. ;
                        aCountIndici[ FIELD->IDX  ]  )

      return nil
      > )


     oDbf:gotop()
    ?oDbf:keyCount
    oDbf:close()
 RETUR NIL

FILE LOTTO.MEM

Code: Select all  Expand view  RUN

[ARCHIVIO]
Inizio=9963
Fine=10083
Mesi=101111111111
Giorni=111000
Day=1111111111111111111111111111111
Indici=111111111111100
LastRecords=0

 

With
SET FILTER TO ( nInit <= RECNO() .AND. ;
nEnd >= RECNO() .AND. ;
aCountMesi[ MONTH( FIELD->DATA ) ] .AND. ;
aCountDate[ DAY( FIELD->DATA ) ] .AND. ;
aCountIndici[ FIELD->IDX ] )

run ok

If I add also

aCountGiorni[ DOW( FIELD->DATA )-1 ] .AND. ; make error

why ?