DATE RANGE

[Silvio.Falconi]

I wish to make a list of all the rooms from number 1 to 30 (all records Rooms.dbf)
and I wish to try in the range of past dates if there is room and which has been in Reserva.dbf



When I make a seach the procedure not run well
for a sample if you made a seach from 01 March 2013 to 04 March 2013 you can see the room 30 occupied or another status while it must be Free



the SAMPLE TEST

Code: Select all  Expand view

#include "fivewin.ch"
#include "ord.ch"
#include "constant.ch"
#include "dtpicker.ch"
#include "XBROWSE.ch"

#define LIGHTCYAN        nRGB( 203, 225, 252 )


#define PLANNING_RESERVED   1
#define PLANNING_CONFIRMED  2
#define PLANNING_OCCUPIED   3
#define PLANNING_CANCELED   4
#define PLANNING_EMPTY      5

REQUEST DBFCDX
REQUEST DBFFPT

EXTERNAL  ordkeyno, ordkeycount,ordcreate,ordkeygoto








FUNCTION TEST()
   Local oDlg, oLbx
   Local dstart:=date()
   Local dEnd  :=date()
   Loca aCheck :=  array(1,3)
   local aStatus := { "Reserved", "Confirmed", "Occupied", "Canceled", "Empty" }


   Local  nBottom   := 24
   Local  nRight    := 51.9
   Local nWidth :=  Max( nRight * DLG_CHARPIX_W, 180 )
   Local nHeight := nBottom * DLG_CHARPIX_H
   Local bClrGrad := { | lPressed | If( ! lPressed,;
                           { { 1, nRGB( 253, 254, 255 ), nRGB( 179, 217, 255 ) } },;
                           { { 1, nRGB( 179, 217, 255 ), nRGB( 253, 254, 255 ) } } ) }

   RddSetDefault( "DBFCDX" )

   SET DATE ITALIAN
   SET DATE FORMAT "DD/MM/YYYY"
   SET DELETED     ON
   SET CENTURY     ON
   SET EPOCH TO    year( date() ) - 20
   SET MULTIPLE    OFF


   Builddbf()



 DEFINE DIALOG oDlg        ;
   TITLE "Check Free Rooms"    ;
   SIZE nWidth, nHeight  TRANSPARENT PIXEL               ;
     GRADIENT { { 1,CLR_WHITE, LIGHTCYAN } }    ;
         STYLE nOr( DS_MODALFRAME, WS_POPUP, WS_CAPTION,  4 )
   @ 10, 8 SAY "From :" OF oDlg pixel  TRANSPARENT
   @ 10, 30 DTPICKER dStart OF oDlg pixel  SIZE 40,10
   @ 10, 86 SAY "To :" OF oDlg pixel   TRANSPARENT
   @ 10, 100 DTPICKER dEnd OF oDlg pixel  SIZE 40,10

   @ 30, 10 XBROWSE  oLbx OF oDlg ;
      SIZE 185,130 PIXEL
        oLbx:SetArray(aCheck)
        oLbx:nMarqueeStyle       := MARQSTYLE_HIGHLWIN7 //MARQSTYLE_HIGHLROW
        oLbx:nColDividerStyle    := LINESTYLE_LIGHTGRAY
        oLbx:lColDividerComplete := .t.
        oLbx:lRecordSelector     := .f.
        oLbx:lHScroll            := .f.
        oLbx:nHeaderHeight       := 20
        oLbx:nRowHeight          := 20
        oLbx:aCols[1]:cHeader    := i18n("Room")
        oLbx:aCols[1]:nWidth     := 110
        oLbx:aCols[2]:cHeader    := i18n("Name")
        oLbx:aCols[2]:nWidth     := 100
        oLbx:aCols[3]:cHeader    := i18n("Status")
        oLbx:aCols[3]:nWidth     := 100
        oLbx:nDataType           := 1 // array
        oLbx:nStretchCol           := -1
        oLbx:CreateFromCode()
        olbx:bClrStd:= {|| if( acheck[oLbx:nArrayAt,3]="Free",{ CLR_GREEN, RGB(221,245,255)},{ CLR_BLACK, RGB(221,245,255) }) }
          @ 10, 150 BTNBMP oBtnSearch PROMPT "&Search" FILENAME "view.bmp" SIZE 50, 15 ;
                     LEFT NOBORDER           ;
                     ACTION    (Check(@aCheck,dStart,dEnd,aStatus,oLbx),oLbx:show(),oLbx:refresh())
                     oBtnSearch:bClrGrad = bClrGrad
          @ 163, 14 BTNBMP oBtnPrint PROMPT "&Print" FILENAME "REP_PRINTER.bmp" SIZE 50, 15 ;
                     LEFT NOBORDER           ;
                     ACTION   oLbx:Report("Stampa disponbilità dal "+dtos(dStart)+ " al "+dtos(dEnd))
                     oBtnPrint:bClrGrad = bClrGrad
          @ 163, 145 BTNBMP oBtnCan PROMPT "&Exit" FILENAME "DLG_OK.bmp" SIZE 50, 15 ;
                     LEFT NOBORDER           ;
                     ACTION   ( oDlg:end( IDCANCEL ) )
                     oBtnCan:bClrGrad = bClrGrad
           ACTIVATE DIALOG oDlg center   ;
                       ON INIT oLbx:hide()


          DbCloseAll()
   return nil





// check status rooms

Function Check(aCheck,dStart,dEnd,aStatus,oLbx)
   Local cDescribe := "Free..."
   asize(aCheck,0)
    olbx:hide()
     ROOMS->( DbGoTop() )
    DO WHILE ! ROOMS->( Eof() )
             nRoom     := ROOMS->ID
             cRoomName := ROOMS->NAME
             RESERVA->( DbGoTop() )
             IF  RESERVA->(DbSeek(nRoom, .t. ))
                                IF DToS( RESERVA->CHECK_OUT ) >= DToS( dStart ) ;
                                                   .AND. DToS( RESERVA->CHECK_IN ) <= DToS( dEnd )
                               nStatus = Val( RESERVA->STATUS )
                                if nStatus > 0 .and. nStatus < 5
                                    cDescribe = aStatus[ nStatus ]
                                 endif
                                 AAdd( aCheck, {  nRoom, cRoomName,  cDescribe } )
                              ENDIF
                           ELSE
                          AAdd( aCheck, {  nRoom, cRoomName,  cDescribe } )
                      ENDIF
       ROOMS->( DbSkip() )
    ENDDO
    olbx:show()
return aCheck



 Function BuildDbf()

   local aStructure
   local i

   if ! File( "rooms.dbf" )

      aStructure = { { "id"   , "C",   4, 0 },;
                      { "name" , "C",  30, 0 },;
                      { "type" , "C",   2, 0 } }

      DBCreate( "rooms", aStructure, "DBFCDX" )

   endif

   if ! File( "reserva.dbf" )

      aStructure = { { "date"     , "D",   8, 0 },;
                      { "rooms_id" , "C",   4, 0 },;
                      { "check_in" , "D",   8, 0 },;
                      { "check_out", "D",   8, 0 },;
                      { "status"   , "C",   2, 0 },;
                      { "guest"    , "C",  30, 0 } }

      DBCreate( "reserva", aStructure, "DBFCDX" )

   ENDIF

   USE ROOMS ALIAS ROOMS VIA "DBFCDX" NEW
   INDEX ON ROOMS->ID TAG rooms_id TO rooms

   USE RESERVA ALIAS RESERVA VIA "DBFCDX" NEW
   INDEX ON RESERVA->ROOMS_ID + DToS( RESERVA->CHECK_IN ) TAG room_in TO reserva

   if ROOMS->( LastRec() ) == 0
      for i = 1 to 30
         ROOMS->( DbAppend() )
         ROOMS->ID   = StrZero( i, 2 )
         ROOMS->NAME = "Room " + StrZero( i, 2 )
         ROOMS->TYPE = StrZero( i % 5, 2 )
      next
   endif

   RETURN nil

To popolate the Reserva.Dbf for the test execute this function only the first time

Code: Select all  Expand view

Function Appe_Demo()

   // only for demo

   SELECT RESERVA
   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "06"
   Replace  RESERVA->check_in   With  ctod("23/02/2013")
   Replace  RESERVA->check_out  With  ctod("23/02/2013")
   Replace  RESERVA->Status     With  "02"
   Replace  RESERVA->guest      With  "Bill Gates"

     RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "03"
   Replace  RESERVA->check_in   With  ctod("24/02/2013")
   Replace  RESERVA->check_out  With  ctod("02/03/2013")
   Replace  RESERVA->Status     With  "01"
   Replace  RESERVA->guest      With  "Micky Mouse"


   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "07"
   Replace  RESERVA->check_in   With  ctod("26/02/2013")
   Replace  RESERVA->check_out  With  ctod("02/03/2013")
   Replace  RESERVA->Status     With  "03"
   Replace  RESERVA->guest      With  "Donald duck"


    RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "10"
   Replace  RESERVA->check_in   With  ctod("24/02/2013")
   Replace  RESERVA->check_out  With  ctod("26/02/2013")
   Replace  RESERVA->Status     With  "04"
   Replace  RESERVA->guest      With  "Customer Z"

   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "13"
   Replace  RESERVA->check_in   With  ctod("19/02/2013")
   Replace  RESERVA->check_out  With  ctod("01/03/2013")
   Replace  RESERVA->Status     With  "05"
   Replace  RESERVA->guest      With  "Customer X"


    RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "12"
   Replace  RESERVA->check_in   With  ctod("24/02/2013")
   Replace  RESERVA->check_out  With  ctod("27/02/2013")
   Replace  RESERVA->Status     With  "04"
   Replace  RESERVA->guest      With  "Customer Y"





    RESERVA->(DbCommit())



   Retur nil


 

and the I cannot see the room number 6 and the number number 12 but sometimes also the number 13

[ukoenig]

Silvio,

It was a little bit complex, but I think I got it working.
I still added missing date-fields to the array to check the result
Do You want to display rooms "Free" in green as well ?
What is with status "canceled" a extra color needed ( the room is free again ) ?

Result ONLY of used rooms :


Result of ALL rooms :
( room-name or guest )



Best Regards
Uwe

[James Bott]

Why are you converting dates to strings for your filter? Why not just use the date type?

IF DToS( RESERVA->CHECK_OUT ) >= DToS( dStart ) ;
.AND. DToS( RESERVA->CHECK_IN ) <= DToS( dEnd )

Just FYI--For testing I can see why you might want to see all the rooms and their status. However, someone using the software to find available rooms only wants to see the rooms that are available. You don't want the user to have to look through every record--this could really be a real problem if there are hundreds of rooms.

James

[Silvio.Falconi]

changed the date search :

Code: Select all  Expand view

Function Check(aCheck,dStart,dEnd,aStatus,oLbx)
   Local cDescribe := "Free"
   Local nStatus   := 0
   asize(aCheck,0)
   olbx:hide()
     ROOMS->( DbGoTop() )
    DO WHILE ! ROOMS->( Eof() )
             nRoom     := ROOMS->ID
             cRoomName := ROOMS->NAME
             RESERVA->( DbGoTop() )
             IF   RESERVA->(DbSeek(nRoom, .t. ))
                IF   RESERVA->CHECK_OUT >= dStart .AND. RESERVA->CHECK_IN <= dEnd
                                       nStatus := Val( RESERVA->STATUS )
                                      if nStatus > 0 .and. nStatus < 5
                                         cDescribe := aStatus[ nStatus ]
                                      endif
                               ENDIF
                            ENDIF
                    AAdd( aCheck, {  nRoom, cRoomName,  cDescribe } )
     ROOMS->( DbSkip() )
     nStatus:=0
     cDescribe := "Free"
    ENDDO
    olbx:show()
return aCheck
 

It seem to work ok
Only I don't Know why yesterday not give me the rooms number 6 and 12 on xbrowse... Today I can see all rooms on xbrowse!!!


For the topic I insert on this forum viewtopic.php?f=3&t=25870



On Doors test I insert on a Window the rooms as doors bitmaps now run ok ( the search of the date)

only when I select the datepict control on ribbonbar the date go to infinity !!!!

the control datepck you can see on the picture seems crazy and goes up to 2018, 2019, 2020 until I have to stop the exe module from the management computer activity


perhaphs there is a bug on ribbonbar or on the datepict ?


I made
DEFINE RIBBONBAR oRib WINDOW oPorte ; //;
PROMPT "Spiaggia","Clienti","Reservations";
HEIGHT 160 TOPMARGIN 36


@ 15,2 ADD BUTTON oBtns[5] PROMPT "Da :" SAYBUTTON GROUP oGroup[3] SIZE 50,20 TRANSPARENT
@ 50,2 ADD BUTTON oBtns[5] PROMPT "a :" SAYBUTTON GROUP oGroup[3] SIZE 50,20

@ 0.8,10 DTPICKER oDateIn VAR dStart OF oGroup[3] SIZE 140,20 UPDATE //VALID dDataIni<=dDataFin
@ 2.8,10 DTPICKER oDateFin VAR dEnd OF oGroup[3] SIZE 140,20 UPDATE VALID dStart<=dEnd

@ 4,210 ADD BUTTON oBtns[5] PROMPT "Today" BITMAP ".\bitmaps\sun.bmp" GROUP oGroup[3] ;
ROUND SIZE 85,48 ACTION (dStart:=date(),;
dEnd:=date(),;
oDateIn:refresh(),;
oDateFin:refresh(),;
Change_Rooms(oPanel,aBtn,nTotale_Rooms,dStart,dEnd,lNumbers))



oDateFin:bChange := {|| Change_Rooms(oPanel,aBtn,nTotale_Rooms,dStart,dEnd,lNumbers),SysRefresh() }
oDateIn:bChange := {|| Change_Rooms(oPanel,aBtn,nTotale_Rooms,dStart,dEnd,lNumbers),SysRefresh() }


It seem to run the codeblock on bchange before to run the test

[ukoenig]

James,

I noticed that as well.
I my sample I'm working on, I don't converting dates to strings .

Best Regards
Uwe

[James Bott]

Rather than add complexity to the test by requiring that you evaluate all the results manually with each test run, here is how I would do it.


Figure all possible combinations of desired reservation and existing reservations:

Where X is a day in a timeline. The first row is the desired reservation and the second one
is an existing reservation.

Code: Select all  Expand view

1. Desired is entirely before an existing reservation:
XXXX
    XXXX

2. Desired is entirely after an existing reservation:
    XXXX
XXXX

3. Desired checkout is the same day as an existing checking reservation:
XXXX
   XXXXX
   
4. Desired starts before existing and ends after existing:
XXXX
 XX

5. Desired starts the same day as existing checkout:
   XXXX
XXXX
 
6. Desired is entirely within existing reservation:
 XX
XXXX
 

Then I would create a program with all the above reservation tests hardcoded and test them against a database containing the all the test existing reservations. During the test I would print the results to a file for viewing after the test is completed. In the results I would also include the result of each test, pass or fail. Now all you have to do is load the test results file into your editor and see which tests passed and which failed.

There will be 1 desired reservation tested against 6 different existing reservations for a total 6 tests.

You may also want to create reservations that cross year end boundaries to make sure they are working right too.

Regards,
James

[ukoenig]

James,

It is just a test.
Download :
http://www.pflegeplus.com/fw_downloads/range1.zip

the result with 3 filters ( using Silvio's logic )

Filter : ALL


Filter : EMPTY


Filter : OCCUPIED


Best Regards
Uwe

[James Bott]

Uwe,

Well your example really shows how confusing this might be to a user. The two rooms 13 and 16 are shown with customer names and date ranges that fall within the desired reservation date, yet they show as empty. So I am not exactly sure what we are seeing?

Also, there were 30 rooms in Silvio's database so there should be a lot more empty rooms available.

Regards,
James

[ukoenig]

James,

the calculation is done with the second DBF < reserva.dbf >
There are some records ( 6 ) from < rooms.dbf > ( of complete 30 ) selected.
The sample is just some help, showing the needed working calculations ( filter ).
At the end I think, there is still some finetuning needed.
I don't know the complete logic of the application and how it will work all together.

Best Regards
Uwe

[Silvio.Falconi]

Uwe, I cannot download your file ...

[ukoenig]

Silvio,

I will create another Download right now with extension .ZOP
Please let me know about the used logic.

http://www.pflegeplus.com/fw_downloads/range1.zop

A have done a little change of < Empty >, to show the original CHECK_IN and CHECK_OUT.
Before IN := ctod(" / / "), OUT := ctod(" / / ") was used.

There is a SEEK in < rooms.dbf > of the selected recors of < reserva.dbf >
The result is a ARRAY shown inside the browser.
I can fill the positions < NOT FOUND > with the values from < rooms.dbf >.
There will be as a result 30 records displayed ( a mixture of rooms.dbf and reserva.dbf )

Code: Select all  Expand view

FUNCTION ROOMCHECK(cRoom, dStart, dEnd, aStatus)
LOCAL aCheck1 := {}, IN := ctod("  /  /    "), OUT := ctod("  /  /    ")

DBSELECTAREA(2)
DBSETORDER(1)

DbSeek(cRoom)
// aStatus := { "Reserved", "Confirmed", "Occupied", "Canceled", "Empty" }
IN := (2)->CHECK_IN
OUT  := (2)->CHECK_OUT

IF FOUND()
    nStatus = (2)->STATUS
    cDescribe = aStatus[ nStatus ]
    IF (2)->CHECK_OUT >=  dStart  .AND. ;
         (2)->CHECK_IN <= dEnd
//      IN := (2)->CHECK_IN
//      OUT  := (2)->CHECK_OUT
    ELSE
        nStatus = 5
        cDescribe = aStatus[ nStatus ]
    ENDIF
    aCheck1 :=  { cRoom, (2)->GUEST,  cDescribe, IN, OUT }
ENDIF

RETURN aCheck1
 

A overview of ALL 30 records from ROOM.dbf and RESERVA.dbf ( mixed )

Code: Select all  Expand view

FUNCTION ROOMCHECK(cRoom, dStart, dEnd, aStatus)
LOCAL aCheck1 := {}, IN := ctod("  /  /    "), OUT := ctod("  /  /    ")

LOCAL cName := (1)->NAME
LOCAL nType := (1)->TYPE

DBSELECTAREA(2)
DBSETORDER(1)

DbSeek(cRoom)
// aStatus := { "Reserved", "Confirmed", "Occupied", "Canceled", "Empty" }
IN := (2)->CHECK_IN
OUT  := (2)->CHECK_OUT

IF FOUND()
    nStatus = (2)->STATUS
    cDescribe = aStatus[ nStatus ]
    IF (2)->CHECK_OUT >=  dStart  .AND. ;
         (2)->CHECK_IN <= dEnd
//      IN := (2)->CHECK_IN
//      OUT  := (2)->CHECK_OUT
    ELSE
        nStatus = 5
        cDescribe = aStatus[ nStatus ]
    ENDIF
    aCheck1 :=  { cRoom, (2)->GUEST,  cDescribe, IN, OUT }
ELSE
    cDescribe = aStatus[ nType ]
        IN := ctod("  /  /    ")
    OUT := ctod("  /  /    ")
    aCheck1 :=  { cRoom, cName,  cDescribe, IN, OUT }
ENDIF

RETURN aCheck1
 

Best Reagards
Uwe

[James Bott]

Uwe,

the calculation is done with the second DBF < reserva.dbf >
There are some records ( 6 ) from < rooms.dbf > ( of complete 30 ) selected.
The sample is just some help, showing the needed working calculations ( filter ).
At the end I think, there is still some finetuning needed.
I don't know the complete logic of the application and how it will work all together.

I assume the desired solution is to show which rooms are available for the selected date range. This would require using the rooms database and checking for the availability of each room for the selected date range and looking at the reservation database for the answer.

Correct me if I am wrong, Silvio.

Regards,
James

[nageswaragunupudi]

I assume the desired solution is to show which rooms are available for the selected date range. This would require using the rooms database and checking for the availability of each room for the selected date range and looking at the reservation database for the answer.

If this is what all is required, probably this function should display the rooms that are available fully during the period from dFrom to dEnd

Code: Select all  Expand view

function FreeRooms( dStart, dEnd )

   field CHECK_IN, CHECK_OUT
   field ID

   USE ROOMS
   USE RESERVA
   SET ORDER TO TAG ROOM_IN
   SET FILTER TO MAX( dStart, CHECK_IN ) > MIN( dEnd, CHECK_OUT )
   GO TOP
   SELECT ROOMS
   SET RELATION TO ID INTO RESERVA
   GO TOP
   SET FILTER TO EMPTY(RESERVA->ROOMS_ID)
   GO TOP
   XBROWSER "ROOMS"

   CLOSE DATA

return nil
 

I did not test the function, but please try this function just as it is.

[Silvio.Falconi]

James you 're right...
we assume the same on my sample there are 30 rooms but on Hotel the rooms are over 120, the search can be compplicated by user
this operation is made when arrive a customer into Hotel or the customer wish make a reservation by phone



Dear Nages

I tried your test .. at the end it show me the rooms dbf
I wish show all the rooms from 1 to 30 and to control if each room is free or not

is the room is free the xbrowse must have on third column the message "free"

if the room is not free can have a status ( reserved, occupied.....)

there is another problem I wish show also the room can be free after one two three four five days
This happens when the user has all the hotel rooms occupied but want to see if you can get
a free room in the following days to tell the customer that there is a room that could be released between one or two days

[nageswaragunupudi]

Silvio

You can plan user-interface any way you like.

The formula you need to use is this:

if MAX( dStart, RESERVA->CHECK_IN ) > MIN( dEnd, RESERVA->CHECK_OUT ) then the room is free
if MAX( dStart, RESERVA->CHECK_IN ) <= MIN( dEnd, RESERVA->CHECK_OUT ) then the room is not free.

Now you can use this formula to show the results as you want.