DATE RANGE

Re: DATE RANGE

Postby James Bott » Sun Mar 17, 2013 12:12 am

Nages,

I expect it is more complicated than you thought. I have made some changes to your test code and have it partially working. The first browse shows only the rooms that are available that are in the reserva database. Note the special situation with records with a STATUS="04" (cancelled).

Also I think we need many more examples in the reserva database, such as multiple records for the same room, reservations with checkin and checkout dates before the desired reservation, and similar with both dates after. Really we need all six eamples that I suggested in an earlier post.

The second browse is just showing all the rooms in the rooms database. I think it is because the fieldnames of the room ID are different in each dbf so SET RELATION is not working. I haven't tested this yet.

James

Code: Select all  Expand view
// Purpose: Test for available rooms for specified
// desired reservation dates.

#include "fivewin.ch"
#include "xbrowse.ch"

function main()
   local dStart,dEnd
   set date italian
   set date format "DDMMYYYY"
   dStart:= ctod("25/02/2013")
   dEnd:= ctod("03/03/2013")

   FreeRooms( dStart, dEnd )

return nil

function FreeRooms( dStart, dEnd )

   field CHECK_IN, CHECK_OUT
   field ID

   USE ROOMS shared
   select 0
   USE RESERVA shared
   SET ORDER TO TAG ROOM_IN
   
   // Must be ">=" since checkin can be the same day as someone checks out.
   // Also any records with STATUS="04" are cancelled so the dates must be ignored.
   SET FILTER TO IF( STATUS ="04", .T., MAX( dStart, CHECK_IN ) >= MIN( dEnd, CHECK_OUT ) )
   //SET FILTER TO MAX( dStart, CHECK_IN ) > MIN( dEnd, CHECK_OUT )
   GO TOP

   // Correctly shows rooms 6, 10, and 12 available of rooms with reservations.
   xbrowser "reserva"

   SELECT ROOMS
   SET RELATION TO ID INTO RESERVA
   GO TOP
   SET FILTER TO EMPTY(RESERVA->ROOMS_ID)
   GO TOP

   // Incorrectly shows all rooms available. Rooms 3,7, and 13 are not available.
   XBROWSER "ROOMS"

   CLOSE DATA

return nil
 
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DATE RANGE

Postby James Bott » Sun Mar 17, 2013 12:56 am

Nages,

I changed the fieldnames in both files to be the same and the second browse is still showing all the rooms. I do not use SET RELATION much so I am not sure where the problem is.

Regards,
James
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DATE RANGE

Postby Silvio.Falconi » Sun Mar 17, 2013 2:14 pm

James,
please start with this test

you can select the date range

I insert FreeRooms( dStart, dEnd,aCheck,oLbx,aStatus)
and my old Check(aCheck,dStart,dEnd,aStatus,oLbx)



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  FreeRooms( dStart, dEnd,aCheck,oLbx,aStatus) // (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

 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



// check status rooms

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












function FreeRooms( dStart, dEnd,aCheck,oLbx,aStatus)
   Local cDescribe := "Free"
   Local nStatus   := 0
   FIELD CHECK_IN, CHECK_OUT
   FIELD ID


     // ERASE THE ARRAY
    asize(aCheck,0)
    olbx:hide()


      /*
   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
    */




        DO WHILE ! ROOMS->( Eof() )
             nRoom     := ROOMS->ID
             cRoomName := ROOMS->NAME
             RESERVA->( DbGoTop() )
             IF  RESERVA->(DbSeek(nRoom, .t. ))

                  *  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.
                         nStatus = Val( RESERVA->STATUS )
                         cDescribe = aStatus[ nStatus ]
                        AAdd( aCheck, {  nRoom, cRoomName,  cDescribe } )
                     endif
                     *AAdd( aCheck, {  nRoom, cRoomName,  cDescribe } )
                  endif
                  ROOMS->( DbSkip() )
                    nStatus:=0
     cDescribe := "Free"
    ENDDO

    olbx:show()
return aCheck
 




I use the range date from 01 March 2013 to 4 March 2013 on this test

If I use this command

if MAX( dStart, RESERVA->CHECK_IN ) > MIN( dEnd, RESERVA->CHECK_OUT )

I have this
Image



If I use if MAX( dStart, RESERVA->CHECK_IN ) <= MIN( dEnd, RESERVA->CHECK_OUT )

I have this

Image

If I rem all I have this

Image
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7052
Joined: Thu Oct 18, 2012 7:17 pm

Re: DATE RANGE

Postby James Bott » Sun Mar 17, 2013 5:19 pm

Silvio,

The only thing I notice that I hadn't noticed before, is that room 13 has a status of "Empty." I assume this would be triggered when the people checked out of the room. It doesn't really make any difference since a reservation could be made starting on the same date as checkout whether the room was empty or not.

It is worth noting that a reservation can also be made ending on the same day as another reservation is checking in so the comparisons need to be <= and >=. This is why I suggest adding some more test records covering all the 6 possible situations and the test reservation should be fixed.

And we still need to either get the SET RELATION working or we need another routine to show all the available rooms, not just those in the reserva database.

Regards,
James
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DATE RANGE

Postby nageswaragunupudi » Sun Mar 17, 2013 7:32 pm

a reservation could be made starting on the same date as checkout whether the room was empty or not

In my formula, instead of CHECK_OUT,
CHECK_OUT - 1 may be substituted.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10622
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DATE RANGE

Postby nageswaragunupudi » Mon Mar 18, 2013 2:18 am

Mr James

I expect it is more complicated than you thought.


I still maintain that the basic formula is as simple as I proposed.
But using it needs *care*.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10622
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DATE RANGE

Postby nageswaragunupudi » Mon Mar 18, 2013 6:49 am

The formula I posted was to check if two ranges ( any datatype ) intersect (overlap).

In case of hotels, check_in can be same as check_out and that is not overlap. The modification required for this to use CHECK_OUT - 1 instead of CHECK_OUT in that formula.

Excluding bookings with cancelled status was a matter of implementation.

The test program I posted was for proof of concept.
Considering the above two modifications also, here is the same program.
Code: Select all  Expand view
#include "FiveWin.Ch"
#include "hbcompat.ch"
#include "ord.ch"
#include "xbrowse.ch"

//----------------------------------------------------------------------------//

REQUEST DBFCDX

//----------------------------------------------------------------------------//

function Main()

   field CHECK_IN, CHECK_OUT, STATUS
   field ID

   local dStart   := {^ 2013/03/01 }
   local dEnd     := {^ 2013/03/04 }

   USE ROOMS NEW VIA "DBFCDX"
   USE RESERVA NEW VIA "DBFCDX"
   SET ORDER TO TAG ROOM_IN
   SET FILTER TO STATUS != '04' .AND. MAX( dStart, CHECK_IN ) > MIN( dEnd, CHECK_OUT - 1 )
   GO TOP
   SELECT ROOMS
   SET RELATION TO ID INTO RESERVA
   GO TOP
   SET FILTER TO EMPTY(RESERVA->ROOMS_ID)
   GO TOP
   XBROWSER "ROOMS" TITLE "FREE ROOMS" + DTOC( dStart ) + " - " + DTOC( dEnd )
   SET FILTER TO !EMPTY(RESERVA->ROOMS_ID)
   GO TOP
   XBROWSER "ROOMS" TITLE "BUSY ROOMS " + DTOC( dStart ) + " - " + DTOC( dEnd )

   CLOSE DATA

return (0)

//----------------------------------------------------------------------------//
 


Results:
Free Rooms:
Image

Busy Rooms:
Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10622
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DATE RANGE

Postby nageswaragunupudi » Mon Mar 18, 2013 7:04 am

Silvio

You want to show all rooms with remarks whether free or not.

I advise you to use this condition:
STATUS != '04' .AND. MAX( dStart, CHECK_IN ) <= MIN( dEnd, CHECK_OUT - 1 )
as filter or for condition on RESERVA.DBF.

That gives the list of rooms busy. If needed we can take the list of rooms into an array.
If a room number is in the list of busy rooms, that room is busy and if not it is free.

You can build any user interface based on this.

For the sake of convenience, I post here a sample:
Compile and test this without any modifications. I have used DBF created by using the code you posted first.
Code: Select all  Expand view
#include "FiveWin.Ch"
#include "hbcompat.ch"
#include "ord.ch"
#include "xbrowse.ch"

//----------------------------------------------------------------------------//

REQUEST DBFCDX

//----------------------------------------------------------------------------//

static aBusy   := {}
static aStatus := { "Reserved", "Confirmed", "Occupied", "Canceled", "Empty" }
//----------------------------------------------------------------------------//

function Main()

   SET DATE ITALIAN
   SET CENTURY ON
   SET DELETED ON

   SetGetColorFocus()
   XBrNumFormat( 'E', .t. )

   OpenData()
   ShowRoomStatus()

return (0)

//----------------------------------------------------------------------------//

static function ShowRoomStatus()

   local dStart      := STOD( "20130301" )
   local dEnd        := STOD( "20130304" )
   local d1, d2, nFilter := 1

   //
   local oDlg, oFont, oBrwRooms, oBrwReserva, oGet1, oGet2, oRad

   GetBusyRooms( d1 := dStart, d2 := dEnd )

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 900,500 PIXEL FONT oFont

   @ 10, 10 GET oGet1 VAR dStart SIZE 80,12 PIXEL OF oDlg ;
      VALID ( If( d1 != dStart, ;
            ( GetBusyRooms( d1 := Min( dStart, dEnd ), d2 := Max( dStart, dEnd ) ), ;
              oBrwRooms:Refresh(), oDlg:Update() ), nil ), .t. )

   @ 10,100 GET oGet2 VAR dEnd SIZE 80,12 PIXEL OF oDlg ;
      VALID ( If( d2 != dEnd, ;
            ( GetBusyRooms( d1 := Min( dStart, dEnd ), d2 := Max( dStart, dEnd ) ), ;
              oBrwRooms:Refresh(), oDlg:Update() ), nil ), .t. )

   @ 10,230 RADIO oRad VAR nFilter ITEMS "Show All", "Free Only", "Busy Only"  ;
         SIZE 100,10 PIXEL OF oDlg ;
         ON CHANGE ROOMS->( SetRoomsFilter( oBrwRooms, nFilter ) )

   @ 30, 10 SAY "Room Status between " + DTOC( Min( dStart, dEnd ) ) + " and " + ;
                  DTOC( Max( dStart, dEnd ) ) SIZE 200,10 PIXEL OF oDlg UPDATE

   @ 50, 10 XBROWSE oBrwRooms SIZE 150,-20 PIXEL OF oDlg DATASOURCE "ROOMS" ;
      COLUMNS "ID", "NAME", { || AScan( aBusy, ROOMS->ID ) == 0 } ;
      HEADERS "No", "RoomName", "Status" ;
      COLSIZES nil, 20, 70 ;
      CELL LINES NOBORDER

   @ 50,170 XBROWSE oBrwReserva SIZE -10,-20 PIXEL OF oDlg DATASOURCE "RESERVA" ;
      COLUMNS "ROOMS_ID", "CHECK_IN", "CHECK_OUT", "STATUS", "GUEST" ;
      HEADERS "ID", "CheckIN", "CheckOUT", "Status", "Guest" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwRooms
      :Status:SetCheck( { 0, 0 }, nil, { "Free", "Busy" } )
      :nStretchCol   := 2
      :bChange       := { || oBrwReserva:Refresh() }
      :bClrStd       := { || If( oBrwRooms:Status:Value, { CLR_BLACK, CLR_WHITE }, { CLR_WHITE, CLR_HRED } ) }
      :bClrSel       := { || { CLR_BLACK, CLR_HGRAY } }
      //
      :CreateFromCode()
   END

   WITH OBJECT oBrwReserva
      :Status:bEditValue := { || aStatus[ Val( RESERVA->STATUS ) ] }
      :bClrSel       := { || { CLR_BLACK, CLR_HGRAY } }
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED ;
      ON INIT ( oBrwRooms:SetFocus(), .f. )

return nil

//----------------------------------------------------------------------------//

static function OpenData()

   field ID

   USE RESERVA NEW SHARED VIA "DBFCDX"
   SET ORDER TO TAG ROOM_IN
   GO TOP

   USE RESERVA NEW SHARED ALIAS "RES" VIA "DBFCDX"
   GO TOP

   USE ROOMS NEW SHARED VIA "DBFCDX"
   SET ORDER TO TAG ROOMS_OD
   GO TOP
   SET RELATION TO ID INTO RESERVA SCOPED
   GO TOP

return .t.

//----------------------------------------------------------------------------//

static function GetBusyRooms( dStart, dEnd )

   field ROOMS_ID, CHECK_IN, CHECK_OUT, STATUS

   ASize( aBusy, 0 )

   RES->( DBGOTOP() )
   RES->( DBEVAL( { || AAdd( aBusy, ROOMS_ID ) }, ;
            { || STATUS != '04' .AND. MAX( dStart, CHECK_IN ) <= MIN( dEnd, CHECK_OUT - 1 ) } ) )

return aBusy

//----------------------------------------------------------------------------//

static function SetRoomsFilter( oBrw, nFilter )

   FIELD ID

   static nPrevFilter   := 1

   if nPrevFilter != nFilter
      if nFilter == 2
         SET FILTER TO ASCAN( aBusy, ID ) == 0
         GO TOP
      elseif nFilter == 3
         SET FILTER TO ASCAN( aBusy, ID ) > 0
         GO TOP
      ELSE
         SET FILTER TO
         GO TOP
      ENDIF
      nPrevFilter := nFilter
      oBrw:Refresh()
   endif

return nil

//----------------------------------------------------------------------------//
 

You can select to display all rooms, free rooms only or busy rooms.
You can also see the reserva for each room selected and you can check the correctness of the formula.
You may add some more bookings and check changing the date range.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10622
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DATE RANGE

Postby nageswaragunupudi » Mon Mar 18, 2013 7:09 am

Screen shots:
All rooms
Image

Busy Rooms
Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10622
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DATE RANGE

Postby Silvio.Falconi » Mon Mar 18, 2013 12:36 pm

Mr. Nages,

WOW!! wonderfull

Maestro Great lesson for me ...
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7052
Joined: Thu Oct 18, 2012 7:17 pm

Re: DATE RANGE

Postby James Bott » Mon Mar 18, 2013 2:18 pm

Nages,

Your code in this post, Sun Mar 17, 2013 11:49 pm, doesn't work for me. The first browse shows all rooms as free, and the second browse shows no rooms busy.

I know it is working for you, and I am baffled as to why it isn't working for me.

Regards,
James
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DATE RANGE

Postby James Bott » Mon Mar 18, 2013 2:27 pm

Nages,

The code in your next post, Mon Mar 18, 2013 12:04 am, isn't working for me either. When first run all the records show as FREE in the left browse, and the right browse is empty. Clicking on the "Free Only" radio button show both browses as empty and the same when clicking the "Busy Only" radio.

Again I have no idea why it is not working. Any ideas?

Regards,
James
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DATE RANGE

Postby nageswaragunupudi » Mon Mar 18, 2013 2:45 pm

Mr James

Could it be due to change in the contents or indexes of the tables?

I copied the code provided by Silvio to create the tables and indexes.
I have only removed alias expressions in the index expressions.
For example where he used
INDEX ON ROOMS->ID .........
I replaced with INDEX ON ID
I consider using alias names within index expressions is a bad practice.

This is the code: Please run this code to recreate the tables and populate the data.
Then test my samples

Code: Select all  Expand view
Function BuildDbf()

   field ID, ROOMS_ID, CHECK_IN

   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 ID TAG rooms_id TO rooms

   USE RESERVA ALIAS RESERVA VIA "DBFCDX" NEW
   INDEX ON ROOMS_ID + DToS( 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


   // 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())

   CLOSE DATA

Return nil
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10622
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: DATE RANGE

Postby James Bott » Mon Mar 18, 2013 3:33 pm

Naves:

I used a fresh copy of the databases and indexes for the tests.

James
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DATE RANGE

Postby Silvio.Falconi » Mon Mar 18, 2013 5:47 pm

Mr. Nages,
I think James use two dbfs I sent him yesterday
Now I send you two dbfs with more records ... you can try with them
the reservations are inserted from 30.01.2013 to 15.03.2013
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7052
Joined: Thu Oct 18, 2012 7:17 pm

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 45 guests

cron