Lookup method of Tdatabase

[Silvio.Falconi]

I am using the following fields to search:

rooms_id
type
check in
check_out


every time I change a reservation date I have to check if the TYPE element with number ROOMS_ID is free


using this function:

Code: Select all  Expand view  RUN

@ 52, 47  DTPICKER aGet[4]  VAR oRecPrenota:Check_Out OF oDlg SIZE 54, 12 PIXEL FONT oFont   ;
   on change  Isfree(oRecPrenota:Rooms_Id,oRecPrenota:Type,oRecPrenota:Check_Out,oRecPrenota:Check_In,oReserva)

 

Nages IsFree() function

Code: Select all  Expand view  RUN

Function Isfree(cCamera,cTypeRoom,dCheckOut,dCheckIn,oReserva)
local lreturn :=.f.
local 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"
   lreturn :=.t.
else
   ? "room is not availble"
   lreturn :=.f.
endif
return lreturn
 

this my sample archive




Results

0006, 08-07-2021, 29-07-2021, 01, isFree() = F correct F

0001, 08-07-2021, 29-07-2021, 02, isFree() = F incorrect T

0006, 08-07-2021, 28-07-2021, 01, isFree() = T incorrect F

0002, 14-07-2021, 14-07-2021, 01, isFree() = F incorrect T


As you can see, 3 out of 4 are incorrect.

There is something seriously wrong with the search logic in the code for the search parameters or in the TDatabase LookUp() method.


any solution pls?

[Otto]

Hello Silvio,

that's the heart of the entire software.
I remember well when I built this query into my DOS version.
I worked through all the cases individually and also made a kind of auxiliary graphic so that I could understand for myself what I actually need.
You have to do this step by step.
But this function from 1994 still works.
Best regards,
Otto

[Antonio Linares]

Silvio,

Could you try it with a simpler expression like "ROOM_ID == ?" and then check results ?

then keep adding more clauses. Just to find where the error may come from.

Method LookUp() just does a standard LOCATE FOR finally

[Silvio.Falconi]

Silvio,

Could you try it with a simpler expression like "ROOM_ID == ?" and then check results ?

then keep adding more clauses. Just to find where the error may come from.

Method LookUp() just does a standard LOCATE FOR finally

Antonio
My old function was

Code: Select all  Expand view  RUN

Function IsFree2(cCamera,cTypeRoom,dCheckOut,dCheckIn,oReserva)
   local  lFree:=.F.
   local nBottomScope, nTopScope

     oReserva:SetOrder("room_in" )
     oReserva:GoTop()

   *oSearchDb:setOrder(, "room_in")
  //nTopScope:=oReservations:setScope( 0, cCamera ) // Tdata
  // nBottomScope:=oReservations:setScope( 1, cCamera )//tdata

    nTopScope:=oReserva:ordScope(  0, cCamera )
    nBottomScope:=oReserva:ordScope( 1, cCamera )
   * oReserva:setscope(  cCamera )

   oReserva:GoTop()

    do while ! oReserva:Eof()
      IF ( dCheckIn <= oReserva:CHECK_OUT .AND. dCheckOut >= oReserva:CHECK_IN ;
                   .AND. AllTrim(oReserva:TYPE ) == AllTrim( cTypeRoom ))

         lFree:=.f.

      endif

      oReserva:Skip()
   enddo



    oReserva:ordScope(0,nTopScope)
   oReserva:ordScope(1,nBottomScope)
 * oReserva:setscope( NIL)

    return  lFree

but it always returned the false value because the current record that I was modifying was also processed in the search, so Nages made me a function with Lookup at this topic
http://forums.fivetechsupport.com/viewtopic.php?f=3&t=40586&sid=ebeef669aa73778a7204da39a3d0ff00&sid=ebeef669aa73778a7204da39a3d0ff00#p242632

I never used LOCATE FOR where I can found a sample ?

[Silvio.Falconi]

Hello Silvio,

that's the heart of the entire software.
I remember well when I built this query into my DOS version.
I worked through all the cases individually and also made a kind of auxiliary graphic so that I could understand for myself what I actually need.
You have to do this step by step.
But this function from 1994 still works.
Best regards,
Otto

Otto,
perhaps you not understood my problem

See the archive picture I editing the record number 6
This happens when the customer wants another umbrella or wants to change the booking period

I cannot create another new reservation because I have to change the reservation that the customer has because the reservation number does not change.

So I am going to edit a record and open the edit dialog

So in this dialog I can change the type or the number or the booking period





if I change any of these 4 parameters (type, number, check in and check out) must do the search, ie check if this element (type and number) is free in the date range

however, it must not process the record I am editing

[James Bott]

Silvio,

The Lookup() function used by the isFree() function ignores the current record.

[Silvio.Falconi]

Silvio,

The Lookup() function used by the isFree() function ignores the current record.

but it does not work well, can you confirm that you have also tried and seen that lookup makes errors?

[James Bott]

Yes.

[nageswaragunupudi]

I am looking into the prg and dbf you sent to me by email.
I will get back.

[James Bott]

Silvio,

I just discovered that records 3, 6 and 16 in the test Reserva.dbf (reservations) all have the same ROOMS_ID and TYPE with overlapping dates. Theoretically that can't happen. This prevents one of the tests from working since even when you attempt to add a day to record 6’s reservation, isFree() returns false because records 3 and 16 have CHECK_IN and CHECK_OUT dates that are within the desired dates of record 6; 7/08/28 to 7/29/28 (Italian format MM/DD/YY).

This explains why at least one of the tests is not working.

The database should never have overlapping dates for the same ROOMS_ID and TYPE. This is what you are trying to prevent. This shows the importance of creating a valid test database.

James

[Silvio.Falconi]

I'm seeing what you wrote to me ....
Records 3 and 6 there is an error I have probably changed the type you can put 04 to record number 6,


but record 13 is not the same because it has a different type 03


I can make a demo new test dbf but i am sure the search won't work

[James Bott]

I didn't say anything about record 13 but I did about 12 and it is Rooms_ID 0006, and type 01 with CHECK_in date 7/29/21 and CHECK_OUT date of 7/29/21. I must say that is it possible that I changed them during testing. I need to find the original reserva.dbf and compare them.

Anyway it is a good idea to check the test database to make sure it isn't part of the problem. No amount of coding changes will get the right answer when the test database is an issue.

[Silvio.Falconi]

sorry I was wrong
the record 16 does not have the type 01 so it cannot be crossed with the other dates.

Anyway I am preparing an archive with some demo records.

It is possible that in the test phase I have changed some data, then creating the error in search.

[James Bott]

Agreed.

I was talking about records 3, 6, and 12 having dates within the test date range, 08/07/2021 - 29/07/2021. Yes, I did make a mistake and say record 16 instead of 12 in my original message.

Records 3, 6, and 12 are all Room_ID= 0006 and type= 01.

[Antonio Linares]

James,

very good finding

many thanks