topic-15889

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

rango dates
Posted: Sun Jun 14, 2009 11:11 PM

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

Best Regards, Saludos

Falconi Silvio

Otto

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

Re: rango dates
Posted: Mon Jun 15, 2009 09:54 AM

Silvio,
maybe you can try like this:

Code (fw): Select all Collapse

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

Silvio

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

Re: rango dates
Posted: Mon Jun 15, 2009 10:56 AM

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 (fw): Select all Collapse

#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

Best Regards, Saludos

Falconi Silvio

Silvio

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

Re: rango dates
Posted: Mon Jun 15, 2009 11:54 AM

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 ?

Best Regards, Saludos

Falconi Silvio

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: rango dates
Posted: Mon Jun 15, 2009 02:11 PM

Try this:

Code (fw): Select all Collapse

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: rango dates
Posted: Mon Jun 15, 2009 02:16 PM

Actually, this would be better:

Code (fw): Select all Collapse

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Silvio

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

Re: rango dates
Posted: Mon Jun 15, 2009 03:35 PM

good Now It seem go good

Best Regards, Saludos

Falconi Silvio

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: rango dates
Posted: Mon Jun 15, 2009 04:35 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Silvio

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

Re: rango dates
Posted: Mon Jun 15, 2009 05:45 PM

SORRY,
I WRITE WRONG ..

IT NOT RUN GOOD

Best Regards, Saludos

Falconi Silvio

Otto

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

Re: rango dates
Posted: Mon Jun 15, 2009 06:20 PM

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

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: rango dates
Posted: Tue Jun 16, 2009 12:37 AM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Silvio

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

Re: rango dates
Posted: Tue Jun 16, 2009 07:50 AM

james I sent yesterday the test files to YOU

Best Regards, Saludos

Falconi Silvio

Silvio

Posts: 3107

Joined: Fri Oct 07, 2005 06:28 PM

Re: rango dates
Posted: Tue Jun 16, 2009 08:17 AM

Otto and James I explain the code :

Code (fw): Select all Collapse

   

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

Best Regards, Saludos

Falconi Silvio

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: rango dates
Posted: Tue Jun 16, 2009 05:04 PM

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 (fw): Select all Collapse

#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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: rango dates
Posted: Tue Jun 16, 2009 05:31 PM

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

Regards,
James

Code (fw): Select all Collapse

#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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

FiveTech Support Forums