rango dates

[Silvio]

I insert on a dialog two control datepick to select two dates

one initial and the second the end date

Now I want to control on an archive if there is a booking order for that period of dates

I have test->dal ( initial date) and test->al end date

How I can make to create the filter ?

thanks

[Otto]

Silvio,
maybe you can try like this:

Code: Select all  Expand view

initialdate < test->da .AND. end > test->dal
 

[Silvio]

Otto not is easy,
On booking reservation I have two field cotiza->dal and COtiza ->al
If the user select the initial date and the end date
I want show all umbrellas and each umbrellas must check into booking archive if have a reservation
I made a test sample but it not run

Code: Select all  Expand view

#include "FiveWin.ch"
#include "dtpicker.ch"




#define TRUE  .T.
#define FALSE .F

STATIC  dDataIniziale , dDataFinale

Function Test ()
Local odlgDate
Local nOmBrellone:=264
Local Xtipo :="O"

dDataIniziale :=DATE()
dDataFinale:=DATE()


DEFINE DIALOG odlgDate SIZE 600,280 TITLE "SEARCH DATES"

             @ 1,2  DTPICKER   dDataIniziale  SIZE 80,12  OF odlgDate
             @ 2,2  DTPICKER    dDataFinale  SIZE 80,12 OF odlgDate VALID  dDataIniziale<=dDataFinale


            @ 3,2 BUTTON  "check" action  SHOW_LISTBOX(dDataIniziale,dDataFinale ,nOmbrellone, XTIPO)    OF odlgDate

ACTIVATE DIALOG odlgDate  CENTER



Function show_listbox( dDataIniziale,dDataFinale ,n, XTIPO)
Local oBPre,oText
USE COTIZA ALIAS COTIZA
INDEX ON COTIZA->CAMERA TO CAMERA
GO TOP
    DO WHILE !cotiza->(EoF())
   IF   alltrim(COTIZA->tipoattrez)=XTIPO .AND. COTIZA->CAMERA=n ;
       .AND.  dDataIniziale < COTIZA->DAL .AND. dDataFinale > COTIZA->dAL

          oText:=TTxtFile():New("check.txt")
          oText:Add("NumDoc: "+alltrim(cotiza->Numdoc))
          oText:Add("Omb: "+str(cotiza->camera))
          oText:Add("Dal: "+cf(cotiza->dal))
          oText:Add("Al: "+cf(cotiza->Al))
          oText:Add("stato "+str(cotiza->stato))
          oText:Close()

    ENDIF

    cotiza->(DbSkip())

       ENDDO

RETURN NIL


   FUNCTION Cf(dTemp)
   LOCAL Meses:="GenFebMarAprMagGiuLugAgoSetOttNovDic"
   LOCAL f, m, cMes, dFech
   f:=DTOS (dTemp)
   m:=MONTH(dTemp)
   cMes:=IF(m<>0,SubStr(Meses,(m*3)-2,3),"")
   dFech:=IF(m<>0,SubStr(f,7,2)+'-'+cMes+'-'+SubStr(f,1,4),'')
RETURN (dFech)



 

this the archive cotiza.dbf http://www.vdswin.it/data_cotiza.rar

[Silvio]

I try also with this :

IF alltrim(COTIZA->tipoattrez)=XTIPO .AND. COTIZA->CAMERA=n ;
.AND. COTIZA->DAL<=(dDataIniziale) .AND. COTIZA->AL>=(dDataFinale) .or. ;
alltrim(COTIZA->tipoattrez)=XTIPO .AND. COTIZA->CAMERA=n .AND. ;
dDataIniziale<= COTIZA->DAL .AND. COTIZA->AL<= dDataFinale


if I select from 13.06 to 14.06 it run ok ( 13.06 - 14-06)
if I select from 14.06 to 14.06 it run ok ( 13.06 - 14-06)
If I select from 14.06 to 15.06 it not run but there is a reservation from 13.06 to 14-06 why it not see it ?

[James Bott]

Try this:

Code: Select all  Expand view

IF alltrim(COTIZA->tipoattrez)=XTIPO .AND. COTIZA->CAMERA=n ;
.AND. COTIZA->DAL<=(dDataIniziale) .AND. COTIZA->AL>=(dDataIniziale) .or. ;
alltrim(COTIZA->tipoattrez)=XTIPO .AND. COTIZA->CAMERA=n .AND. ;
dDataFinale <= COTIZA->DAL .AND. COTIZA->AL <= dDataFinale

James

[James Bott]

Actually, this would be better:

Code: Select all  Expand view

IF alltrim(COTIZA->tipoattrez)=XTIPO .AND. COTIZA->CAMERA=n .AND.;
  ( ;
  ( COTIZA->DAL <= dDataIniziale .AND. COTIZA->AL >= dDataIniziale ) .or. ;
  ( dDataFinale <= COTIZA->DAL .AND. COTIZA->AL <= dDataFinale );
  )

James

[Silvio]

good Now It seem go good

[James Bott]

Glad to hear it.

You can still speed it up considerably by designing the proper index and using it to filter out some of the records before doing your date range test.

James

[Silvio]

SORRY,
I WRITE WRONG ..

IT NOT RUN GOOD

[Otto]

Hello Silvio

have you tried my code. This is the code for occuied:
initialdate < test->da .AND. end > test->dal

You should use it like that:

if initialdate < test->da .AND. end > test->dal
else
here are your add's
endif

What is the result?
Regards,
Otto

[James Bott]

Otto,

>if initialdate < test->da .AND. end > test->dal

It is more complicated than that. He needs to know if the new date range overlaps any existing date range by one or more days. I thought the one I posted would do it, but I haven't tested it. Silvio says it still isn't working. I will test it here.

Regards,
James

[Silvio]

james I sent yesterday the test files to YOU

[Silvio]

Otto and James I explain the code :

Code: Select all  Expand view

 

If   aData[nY,nX]== "O"
           cTipoAttrez:="O"
            cTipoSol :=""
            cPosNome := ""
            nOmbrellone :=  (oDBeach)->camera
            nFila:=  (oDBeach)->Y
            cCaption2:="Ombrellone n.:"+str(nOmbrellone)+CRLF+"Fila:"+str(nFila)
            n:= nOmbrellone

            (oDPre)->(OrdSetFocus(3))
            (oDPre)->(DbGoTop())
            DO WHILE !(oDPre)->(EoF())



           IF alltrim((oDPre)->tipoattrez)=cTipoAttrez .AND. (oDPre)->CAMERA=n .AND.;
  ( ;
  ( (oDPre)->DAL <= dDataIniziale .AND. (oDPre)->AL >= dDataIniziale ) .or. ;
  ( dDataFinale <= (oDPre)->DAL .AND. (oDPre)->AL <= dDataFinale );
  )


                cPosNome := alltrim( (oDPre)->RAZSOC)
                cTipoSol := alltrim( (oDPre)->TIPSOL)
                nStato:=((oDPre)->stato)

          ENDIF
                (oDPre)->(DbSkip())
             ENDDO

        endif
 

aData[nY,nX] is the type of object and this case it is an umbrellas. I made a class where I can draw the beach and the I can save all umbrellas on a dbf

the procedure show all object of the beach but if the object is an umbrellas must to control if there is a booking on cotiza.dbf for this umbrellas for the range dates

it found the number and the type of umbrellas (cTipoAttrez) and if the reservation is made during the two dates entered return the nstate

the nstate can be 1 = booking, 2= occupated

my procedure then show the umbrellas and show a bitmap blu if nstate is 1 , red if the nstate is 2, and if it not have nstate show an green umbrella

green = free

[James Bott]

Silvio,

This sample code seems to be working fine. There is one matching record:

Record no. 7, DAL=06/13/2009, AL=06/14/2009

James

Code: Select all  Expand view

#include "fivewin.ch"

request dbfcdx
REQUEST DBFFPT


function main()

   local cTipoAttrez, nCamera, dIniziale, dFinale, nRecs
   field DAL, AL, CAMERA, TIPOATTREZ

   RddSetDefault( "DBFCDX" )

   set epoch to "1980"
   set century on

   cTipoAttrez:="O"
   nCamera:= 264
   dIniziale:= ctod("06/14/2009")
   dFinale  := ctod("06/15/2009")

   use cotiza shared

   set filter to alltrim(tipoattrez) == cTipoAttrez .AND. CAMERA == nCamera  ;
      .AND. ;
      (;
      ( DAL >= dIniziale .AND. DAL <= dFinale ) .OR.;
      ( AL >= dIniziale .AND. AL <= dFinale );
      )

   go top

   count to nRecs

   msgInfo( nRecs )

return nil

[James Bott]

My previous code was using the American date format. Here it is using the Italian date format.

Regards,
James

Code: Select all  Expand view

#include "fivewin.ch"

request dbfcdx
REQUEST DBFFPT

function main()

   local cTipoAttrez, nCamera, dIniziale, dFinale, nRecs
   field DAL, AL, CAMERA, TIPOATTREZ

   RddSetDefault( "DBFCDX" )

   set epoch to "1980"
   set century on
   set date italian

   cTipoAttrez:="O"
   nCamera:= 264
   dIniziale:= ctod("14/06/2009")
   dFinale  := ctod("15/06/2009")

   use cotiza shared

   set filter to alltrim(tipoattrez) == cTipoAttrez .AND. CAMERA == nCamera  ;
      .AND. ;
      (;
      ( DAL >= dIniziale .AND. DAL <= dFinale ) .OR.;
      ( AL >= dIniziale .AND. AL <= dFinale );
      )

   go top

   count to nRecs

   msgInfo( nRecs )

return nil