Lookup method of Tdatabase

Lookup method of Tdatabase

Postby Silvio.Falconi » Sat Aug 14, 2021 4:52 pm

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

@ 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

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

Image


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?
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: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: Lookup method of Tdatabase

Postby Otto » Sat Aug 14, 2021 9:22 pm

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

Image
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: Lookup method of Tdatabase

Postby Antonio Linares » Sun Aug 15, 2021 7:03 am

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
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 42059
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: Lookup method of Tdatabase

Postby Silvio.Falconi » Sun Aug 15, 2021 8:57 am

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 view
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 ?
Last edited by Silvio.Falconi on Sun Aug 15, 2021 9:32 am, edited 1 time in total.
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: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: Lookup method of Tdatabase

Postby Silvio.Falconi » Sun Aug 15, 2021 9:15 am

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


Image


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
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: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: Lookup method of Tdatabase

Postby James Bott » Sun Aug 15, 2021 6:12 pm

Silvio,

The Lookup() function used by the isFree() function ignores the current record.
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: Lookup method of Tdatabase

Postby Silvio.Falconi » Sun Aug 15, 2021 8:37 pm

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?
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: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: Lookup method of Tdatabase

Postby James Bott » Sun Aug 15, 2021 10:30 pm

Yes.
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: Lookup method of Tdatabase

Postby nageswaragunupudi » Mon Aug 16, 2021 3:36 pm

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

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

Re: Lookup method of Tdatabase

Postby James Bott » Mon Aug 16, 2021 7:35 pm

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
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: Lookup method of Tdatabase

Postby Silvio.Falconi » Mon Aug 16, 2021 8:31 pm

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
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: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: Lookup method of Tdatabase

Postby James Bott » Mon Aug 16, 2021 8:51 pm

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.
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: Lookup method of Tdatabase

Postby Silvio.Falconi » Mon Aug 16, 2021 10:57 pm

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.
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: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: Lookup method of Tdatabase

Postby James Bott » Mon Aug 16, 2021 11:45 pm

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.
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: Lookup method of Tdatabase

Postby Antonio Linares » Tue Aug 17, 2021 9:20 am

James,

very good finding

many thanks
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 42059
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Next

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 48 guests