Page 1 of 3
Lookup method of Tdatabase
Posted: Sat Aug 14, 2021 4:52 pm
by 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
@ 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)
Code: Select all | Expand
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
Results0006, 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?
Re: Lookup method of Tdatabase
Posted: Sat Aug 14, 2021 9:22 pm
by 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
Re: Lookup method of Tdatabase
Posted: Sun Aug 15, 2021 7:03 am
by 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
Re: Lookup method of Tdatabase
Posted: Sun Aug 15, 2021 8:57 am
by Silvio.Falconi
Antonio Linares wrote: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
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#p242632I never used LOCATE FOR where I can found a sample ?
Re: Lookup method of Tdatabase
Posted: Sun Aug 15, 2021 9:15 am
by Silvio.Falconi
Otto wrote: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 rangehowever, it must not process the record I am editing
Re: Lookup method of Tdatabase
Posted: Sun Aug 15, 2021 6:12 pm
by James Bott
Silvio,
The Lookup() function used by the isFree() function ignores the current record.
Re: Lookup method of Tdatabase
Posted: Sun Aug 15, 2021 8:37 pm
by Silvio.Falconi
James Bott wrote: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?
Re: Lookup method of Tdatabase
Posted: Sun Aug 15, 2021 10:30 pm
by James Bott
Yes.
Re: Lookup method of Tdatabase
Posted: Mon Aug 16, 2021 3:36 pm
by nageswaragunupudi
I am looking into the prg and dbf you sent to me by email.
I will get back.
Re: Lookup method of Tdatabase
Posted: Mon Aug 16, 2021 7:35 pm
by 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
Re: Lookup method of Tdatabase
Posted: Mon Aug 16, 2021 8:31 pm
by 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
Re: Lookup method of Tdatabase
Posted: Mon Aug 16, 2021 8:51 pm
by 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.
Re: Lookup method of Tdatabase
Posted: Mon Aug 16, 2021 10:57 pm
by 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.
Re: Lookup method of Tdatabase
Posted: Mon Aug 16, 2021 11:45 pm
by 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.
Re: Lookup method of Tdatabase
Posted: Tue Aug 17, 2021 9:20 am
by Antonio Linares
James,
very good finding
many thanks