Error on search a record on dbf with tdatabase

[Silvio.Falconi]

I am modifying a reservation
in the reservation I have two datepick controls dFirst and dLast
in Insertion I have no problems but
in modification if the operator changes a date I always have to carry out a search to see if there is a reservation and the function always restores me a true value because it also sees the same reservation which, however, is in modification.
If the operator enters dlast - 1 day the search function tells me that it is already in the archive
if the operator enters dLast + 1 the search function tells me that it is already in the archive
if the operator changes the whole period and the search finds a reservation, it returns me a true value

how can i make sure that the modification does not fall into this error?

this is the function

Code: Select all  Expand view

Function Search_Element(cCamera,dCheck_in,dCheck_out,cTypeRoom)
   Local cRoom
   local  lFree:=.T.
   local nBottomScope, nTopScope
   local oSearchDb
   local afatture:={}
     

     oSearchDb:=TReserva():New()
     oSearchDb:SetOrder("room_in" )
     oSearchDb:GoTop()



    nTopScope:=oSearchDb:ordScope(  0, cCamera )
    nBottomScope:=oSearchDb:ordScope( 1, cCamera )
 
    oSearchDb:GoTop()
   

   do while ! oSearchDb:Eof()

      IF ( dCheck_in <= oSearchDb:CHECK_OUT .AND. dCheck_out >= oSearchDb:CHECK_IN ;
                   .AND. AllTrim(oSearchDb:TYPE ) == AllTrim( cTypeRoom ))
       
           lFree:=.f.
         
                 ENDIF
      oSearchDb:Skip()
   enddo


   oSearchDb:ordScope(0,nTopScope)
   oSearchDb:ordScope(1,nBottomScope)
 
    oSearchDb:Close()
   return  lFree

the dbf is index on oSearchDb:SetOrder("room_in" ) (ROOMS_ID + DToS( CHECK_IN ))

Someone can help me please ?

[James Bott]

Silvio,

As you have found, your current method is not going to help. Think outside the box.

What would a reservation clerk at an expensive hotel do? What would they try first, and next, and after that, just trying to find a solution for the customer? Make a list in order of best probable outcomes. Then post your list here.

Yep, "Outside the box Bott" they used to call me...

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them" -Albert Einstein

[Silvio.Falconi]

Silvio,

As you have found, your current method is not going to help. Think outside the box.

What would a reservation clerk at an expensive hotel do? What would they try first, and next, and after that, just trying to find a solution for the customer? Make a list in order of best probable outcomes. Then post your list here.

Yep, "Outside the box Bott" they used to call me...

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them" -Albert Einstein

James,
I don't know how to do it that's why I asked the forum for help

I hope you understand the concept even if the management of a hotel is clearly different from the management of a bathhouse, that is, if I have a range of dates
from 24/07/2021 to 25/07/2021 for the hotel it is only one night for the chalet by the sea it is two days

for the search ( my problem)
when the operator goes to modify a range of dat, the procedure should check in the reservations archive if there is already a reservation in that date range (fields check_in and check_out) for that number (field rooms_id) and for that type (field type) without excluding the current reservation you are editing


practically now it seems that I do a search of the free rooms excluding the reservation concerned, how resolve ?

[Silvio.Falconi]

any help please

[James Bott]

It is not an error of TDatabase but rather an error in logic.

practically now it seems that I do a search of the free rooms excluding the reservation concerned, how resolve ?

Code: Select all  Expand view

If ROOM_ID = cROOM_ID
   oReservations:skip()
endif

[nageswaragunupudi]

No need to open oSearchDB again. We can check with the main database object itself like this.

Code: Select all  Expand view

cSearch := oReserva:ApplyParams( "ROOM_ID == ? .AND. ALLTRIM(TYPE) == ? .AND. RECNO() != ? .AND. (CHECK_IN > ? .OR. CHECK_OUT < ? )", ;
            { cCamera, ALLTRIM( cTypeRoom ), oReserva:RecNo(), dCheckOut, dCheckIn } )

if oReserva:LookUp( cSearch, nil, { || .T. } ) == .T.
   ? "room is available"
else
   ? "room is not availble"
endif
 

[Silvio.Falconi]

james,


This is not good, because there are many bookings in the archive and the strsso room_id could have many bookings with different periods, and excluding the room_id you would risk making a mes

if, for example, I have two reservations of the same number, one from 08/07/2021 to 28/07/2021 and the other reservation from 29/07/2021 to 29/07/2021 and I exclude the room_id number as you say, the procedure he does not see the second reservation and after that he will assign for example a day that belongs to the other reservation

[James Bott]

Silvio,

I was only showing how to exclude a room number. You still have to check the dates.

[Silvio.Falconi]

No need to open oSearchDB again. We can check with the main database object itself like this.

Code: Select all  Expand view

cSearch := oReserva:ApplyParams( "ROOM_ID == ? .AND. ALLTRIM(TYPE) == ? .AND. RECNO() != ? .AND. (CHECK_IN > ? .OR. CHECK_OUT < ? )", ;
            { cCamera, ALLTRIM( cTypeRoom ), oReserva:RecNo(), dCheckOut, dCheckIn } )

if oReserva:LookUp( cSearch, nil, { || .T. } ) == .T.
   ? "room is available"
else
   ? "room is not availble"
endif
 

Sorry Nages,
but I not understood how I must make can you make a small sample test ?

I not have oReserva opened I Open a copy of reserva on that function and then close it
Only when I show the beach planning I check if there are reservations on reserva.dbf but then I close it
that is I not have reserva.dbf opened, I 'm trying to not have the databse opened but open the dbf when I make an operation

Remember I have this problem when I am on Edit record and I go to change the last date on datepick as you can see on this picture



Remember the datepick on change process before the function on change
I can't even force it by inserting a check for the number because it's wrong

the reservation being modified has:
Room_ID 6
TYpE 01
from 07/08/2021 to 07/28/2021 and I want to change the date until 07/29/2021

there is already a reservation in the archive
Room_ID 6
TYpE 01
from 07/29/2021 to 07/29/2021

if I go to do a search and I am in modification it tells me that it is busy because it finds that reservation of the day 29/07

ok

if instead I would like from 08/07/2021 to 27/07/2021
it tells me that it is busy because in the search it would find the reservation that is being edited

[James Bott]

Silvio,

there is already a reservation in the archive
Room_ID 6
TYpE 01
from 07/29/2021 to 07/29/2021

if I go to do a search and I am in modification it tells me that it is busy because it finds that reservation of the day 29/07

ok

if instead I would like from 08/07/2021 to 27/07/2021
it tells me that it is busy because in the search it would find the reservation that is being edited

All you really want to know is the date 29/07/21 available for that "room." That will show as free. Then you add that date to the existing reservation (i.e. change the end date of the existing reservation).

If that date is not available for that room, then look to see what other rooms are available. Then you could offer one of them as an alternative for that last day. You could also look for the entire range of dates being available for another room.

This is what I was referring earlier, when I said to think like a hotel reservation person. That is what they would do--look for alternative options if the original requested date & room is not available.

[For others reading this thread, the term "room" is really refering to "beach site." This is because the original reservation software was for rooms.]

[Silvio.Falconi]

Silvio,

there is already a reservation in the archive
Room_ID 6
TYpE 01
from 07/29/2021 to 07/29/2021

if I go to do a search and I am in modification it tells me that it is busy because it finds that reservation of the day 29/07

ok

if instead I would like from 08/07/2021 to 27/07/2021
it tells me that it is busy because in the search it would find the reservation that is being edited

All you really want to know is the date 29/07/21 available for that "room." That will show as free. Then you add that date to the existing reservation (i.e. change the end date of the existing reservation).

If that date is not available for that room, then look to see what other rooms are available. Then you could offer one of them as an alternative for that last day. You could also look for the entire range of dates being available for another room.

This is what I was referring earlier, when I said to think like a hotel reservation person. That is what they would do--look for alternative options if the original requested date & room is not available.

[For others reading this thread, the term "room" is really refering to "beach site." This is because the original reservation software was for rooms.]

exact
when the procedure gave me that the umbrella is not available, a button called "nearby elements" is activated and pressing this button shows me the alternatives near that umbrella practically visualizes on the video a portion of the beach with the other umbrellas close to the umbrella that is occupied this because the customer might want an umbrella close to his relative.
I allready made this procedure ( to show alternative umbrella near the umbrella selected)

but I wasn't talking about that

I have the reservation from 08/07/2021 to 28/07/2021 and I making a edit

if I change the date and insert from 08/07/2021 to 27/07/2021 I am going to modify a reservation only for one day

when I check if there is already a reservation for that period it tells me that it already exists
but why does it tell me that it exists?

because looking has also encountered the record that I am editing

it does not notice that that record is the one I am working with and gives me the busy period value but in reality it is not so because occupied by the same reservation

now James do you understand?

[James Bott]

Silvio,

now James do you understand?

Yes, I understand perfectly. But apparently you still do not understand what I am saying.

if I change the date and insert from 08/07/2021 to 27/07/2021 I am going to modify a reservation only for one day.

when I check if there is already a reservation for that period it tells me that it already exists
but why does it tell me that it exists?

It tells you that BECAUSE the original reservation does still exist. It is doing exactly what it is supposed to do (but not what you need).

You don't need to search for the entire NEW reservation. All you need to find out is if the new day is available, so you search for the new day only. If it is, then you change the length of the original reservation to include the new day. Simple.

If they want add 2 days (or more) then you search for availability of the new days only. If those two days are available then you change the check_out day to the last day of the addon period (two days in this case). Done.

[Silvio.Falconi]

Silvio,

now James do you understand?

Yes, I understand perfectly. But apparently you still do not understand what I am saying.

if I change the date and insert from 08/07/2021 to 27/07/2021 I am going to modify a reservation only for one day.

when I check if there is already a reservation for that period it tells me that it already exists
but why does it tell me that it exists?

It tells you that BECAUSE the original reservation does still exist. It is doing exactly what it is supposed to do (but not what you need).

You don't need to search for the entire NEW reservation. All you need to find out is if the new day is available, so you search for the new day only. If it is, then you change the length of the original reservation to include the new day. Simple.

If they want add 2 days (or more) then you search for availability of the new days only. If those two days are available then you change the check_out day to the last day of the addon period (two days in this case). Done.

you talked to me about giving the customer the alternatives, that is another matter that I have already solved, here it is about something else

if I use the function published above I go through all the records including the one I am editing.

1) I add one day he tells me that he is occupied (from another booking)

2) do I decrease by one day? he tells me he's busy (from the same record)

3) do I decrease by three days? he is occupied by the same record

I'm not kidding I didn't really understand how to do it

I'm sorry but I still can't understand maybe with a small example

I have from 08/07/2021 to 28/07/2021

1) we have already seen that if I increase by one day ie dlast = 29/07/2021 there is already a reservation so it tells me that it is busy

2) Now I change dlast = 27/07/2021 that is one day less - so I call the search function

What do I have to look for ? What do I have to look for ? you say only that day that is the 27/07/2021?

Search_Element(cCamera,27/07/2021,27/07/2021,cTypeRoom) return me occupated from the same record I am editing

how do i exclude the current record from the search?

[James Bott]

Silvio,

I'm sorry but I still can't understand maybe with a small example

I have from 08/07/2021 to 28/07/2021

1) we have already seen that if I increase by one day ie dlast = 29/07/2021 there is already a reservation so it tells me that it is busy.

I understand that. You don't need to search for the entire new reservation. You only need to know if 29/07/2021 is free, you already know the other dates 08/07/21 - 27/07/2021 are not free becuase that is the current reservation. But that doesn't matter. So you just search for 29/07/2021 to 29/07/2021. If that is free then you just change the check_out date of the current reservation to 29/07/2021.

2) Now I change dlast = 27/07/2021 that is one day less - so I call the search function

What do I have to look for ? What do I have to look for ? you say only that day that is the 27/07/2021?

Search_Element(cCamera,27/07/2021,27/07/2021,cTypeRoom) return me occupated from the same record I am editing

how do i exclude the current record from the search?

Silvio, think about it, you don't need to search for anything if you are making an existing reservation shorter. Yes, that date is already occupied, HOWEVER, you are going to free that day up, not add a new reservation for that day. So whenever you need to shorten a reservation, by either starting at a later date, or ending at an earlier date, you just change the start date, or the end date to the new date. There is no searching required.

Psuedo code:

Code: Select all  Expand view

Assumption: All dates are in date format

// Current reservation
nRoom:= 10
oReservation:check_in:= 08/07/2021
oReservation:check_out:= 28/02/2021

To add a day (29/07/2021) to the end of the reservation:

nRoom:= 10          // beach site of existing reservation
dDate:= 29/07/2021  // date to add
If isFree(nRoom, dDate)
   oReservation:check_out := dDate
   oReservation:save()
endif

To subtract n days from the end of an existing reservation:
No searching is required since you are freeing up days not booking new days

// No search is required
oReservation:check_out := ( oReservation:check_out - nDays )
oReservation:save()

To subtract n days from the beginning of an existing reservation:

// No search required
oReservation:check_in := ( oReservation:check_in + nDays)
oReservation:save()

Note that this code doesn't include a way to add days for a different Room if they are not available for the same Room.

James

[Silvio.Falconi]

James,
I make as Nageswarao wrote ...and seem to run ok
only the nages wrote wrong a parameter ROOM_ID instead OF ROOMS_ID