ApplyParams Tdatabase

[Silvio.Falconi]

I use this Nages function for search on a dbf ( with tdatabase)

Code: Select all  Expand view

Function IsFree(cCamera,dCheck_in,dCheck_out,cTypeRoom,oPrenotazioni)

    local lFree
    local cSearch
         

    cSearch :=  oPrenotazioni:ApplyParams( "ROOMS_ID == ? .AND. ALLTRIM(TYPE) == ? .AND. RECNO() != ? .AND. (CHECK_IN > ? .OR. CHECK_OUT < ? )", ;
           { cCamera, ALLTRIM( cTypeRoom ),  oPrenotazioni:RecNo(), dCheck_out, dCheck_in } )

if  oPrenotazioni:LookUp( cSearch, nil, { || .T. } ) == .T.

   lFree:=.t.
   Msginfo("room is available"+CRLF+CRLF+;
             "Room number : "+cCamera+CRLF+;
             "Room ctype : "+cTypeRoom+CRLF+;
             "Check in   : "+cf(dCheck_in)+CRLF+;
             "Check out  : "+cf(dCheck_out)+CRLF+;
             "No Record     : "+ltrim(str(oPrenotazioni:RecNo())) )
else

   Msginfo("room is not availble"+CRLF+CRLF+;
             "Room number : "+cCamera+CRLF+;
             "Room ctype : "+cTypeRoom+CRLF+;
             "Check in   : "+cf(dCheck_in)+CRLF+;
             "Check out  : "+cf(dCheck_out)+CRLF+;
             "No Record     : "+ltrim(str(oPrenotazioni:RecNo())) )

    lFree:=.f.
endif

return lFree

however, I realized that if I call the function several times, it makes an error, that is, it does not make a truthful search

making mistakes in the procedure

In edit procedure I must change the position continuously and look for the most appropriate for the client
when I change the position I mean the type of the item, the number and the date range for the reservation.



the procedure should search the archive if the element (type) with the selected dates is free or busy and return me an info message

[James Bott]

Silvio,

I don't see why you are searching with so many criteria. And what does recno() have to do with finding an available room? It seems to me there are only three senarios:

1) Find any room available for the desired check_in and check_out dates. --Two criteria

2) Find any room available for a specific room type (or room ID), check_in, and check_out dates. -Three criteria

3) Find out if an existing room reservation can be lengthened and which way (add days for the front or back of the existing reservation). -Three criteria, room ID, length of extension (days), and which way (front or back of the existing reservation).

There is one scenario when you don't have to do any searching. That is shortening an existing reservation--you just do it.

You will obviously need to return the recno() for any results since you don't have a reservation ID. However, you don't need to search for the recno() to find an open reservation.

Using the recno() for an ID is risky since if a record gets deleted, then all the records after that recno() will get renumbered. That will be a mess. Thus you should always have a unique primary key in any database (with only a few rare exceptions). I highly recommend you switch to using a reservation ID.

[Silvio.Falconi]

in fact you didn't understand anything about what I'm doing.

I already have a reservation number "RR2454456667" inserted ok?

From 08/07/2021 to 28/07/2021
Element type 01 ( umbrella)
Number 0006
record number 6


Go to modification of this record.

BECAUSE now the customer has changed his mind and wants another number of umbrella, in another position or near the umbrella of a friend of his,

or he wants to change the period,

or instead of the normal umbrella (4 seats) he wants to take an umbrella hawayano (10 seats),

the recno is needed because I am in modification of that record and when I search for that information it should not be considered the tecord that I am modifying.

I don't understand what's difficult to understand.

>1) Find any room available for the desired check_in and check_out dates. --Two criteria
>2) Find any room available for a specific room type (or room ID), check_in, and check_out dates. -Three criteria
>3) Find out if an existing room reservation can be lengthened and which way (add days for the front or back of the existing reservation). -Three criteria, room ID, length of extension (days), and which way (front or back of the existing reservation).

I don't have to do any of these three things as already explained above.
I have entered a reservation
order number RRxxxxxxxxxxx
Period from 08/07/2021 to 27/07/2021
Customer James Bott
Type Parasol Element (01)
Number 0006

the customer james bott has already paid or given a deposit

the customer James Bott comes to me because Antonio Linares has arrived at number 10 and wants to be close to his friend Antonio

so I have to look for an umbrella that is close to number 10 and that has the free period that the customer James Bott wants

But now customer James Bott thinks about it , the customer James Bott wants to change the period of stay and wants from 08/07/2021 to 29/07/2021

A day goes by and the customer James bott comes back to me and tells me that for family reasons he has to go back to america a week before that is to change the period from 08/07/2021 al 21/07/2021

I see his umbrella free for a week so it's free for other bookings

James, this is the hypothetical scenario that happens every day in the chalet







>You will obviously need to return the recno() for any results since you don't have a reservation ID. However, you don't need to search for the recno() to find an open reservation.

I have a reservation id, I use a unique 13 character order or reservation number "RRxxxxxxxxxxx" that is created every time you make a new add

[James Bott]

Silvio,

in fact you didn't understand anything about what I'm doing.

Maybe not.

I already have a reservation number "RR2454456667" inserted ok?

Hmm, well I didn't know because in the reservation database you sent me that field is named NUMPRE, and it is 18 digits long. The fieldname didn't sound like RESNO, RESID or similar, and I don't even know how big of a number is 18 digits long (1,000 trillion). Wow, that is a lot of reservations for only a 3 month period. Why such a huge field, maybe a government requirement? Also, it is traditional to have the ID field as the first field--and this also allows you to always use setOrder(1), to search the primary-key field in any database.

My ID fields are usually 6 digits, as that handles one less than a million numbers.

the recno is needed because I am in modification of that record and when I search for that information it should not be considered the tecord that I am modifying.

I'm still not sure why you would need that. Aren't you looking for available records only? Since the one you are editing is not available for it's own date range it will not show as available anyway. If you are looking to shorten the length of stay, then you don't need to search at all.

But now customer James Bott thinks about it, the customer James Bott wants to change the period of stay and wants from 08/07/2021 to 29/07/2021

A day goes by and the customer James bott comes back to me and tells me that for family reasons he has to go back to America a week before that is to change the period from 08/07/2021 al 21/07/2021

You can immediately just change the checkout date without any searching at all. Since you are going to free up a week (29/07/2021 is going to be changed to 21/07/2021). So you just change the 29 to 21 and save. No search needed.

Of course, if the customer wants to extend their stay, then you will have to search.

[James Bott]

Silvio,

In edit procedure I must change the position continuously and look for the most appropriate for the client
when I change the position I mean the type of the item, the number and the date range for the reservation.

This could be automated. Just build a routine that looks for all free rooms for the customer's desired date range. Collect them all and present in a browse of available reservations for the user to select from. They could also be sorted by room and/or type.

James

[Silvio.Falconi]

Hmm, well I didn't know because in the reservation database you sent me that field is named NUMPRE, and it is 18 digits long. The fieldname didn't sound like RESNO, RESID or similar, and I don't even know how big of a number is 18 digits long (1,000 trillion). Wow, that is a lot of reservations for only a 3 month period. Why such a huge field, maybe a government requirement? Also, it is traditional to have the ID field as the first field--and this also allows you to always use setOrder(1), to search the primary-key field in any database.

My ID fields are usually 6 digits, as that handles one less than a million numbers.

I honestly don't understand what your common thread is and what the reservation number has to do with it.

No government requirements.
Do I have to explain on the public forum how I created the NUMPRE field or the INVOICE field?
Exactly, the field is 18 characters and is a string formed by an internal code RR (reservation "or SS" single receipt "or by another code that I would like not to explain in the public forum
and is formed by
Year
the current month
the current time including seconds

are you happy that I explained on public forum how that unique code was created?

I thought of this code when * someone * insisted telling me that with tdatabase (or tdata)
I couldn't know the record number and adding +1 to the value because in ancient times the reservation number was made up of the record number - strzero (recno, 4) + 1 -

I would have liked not to publicly explain how the code is made, I don't see why this could affect the search I asked for, the order or reservation number must not be changed, it remains the same even when the record is in modification


>I'm still not sure why you would need that. Aren't you looking for available records only? Since the one you are editing is not available for it's own date range it will not show as available anyway. If you are looking to shorten the >length of stay, then you don't need to search at all.
>You can immediately just change the checkout date without any searching at all. Since you are going to free up a week (29/07/2021 is going to be changed to 21/07/2021). So you just change the 29 to 21 and save. No search >needed.
>Of course, if the customer wants to extend their stay, then you will have to search.

James,
Are you sure you have seen the archive correctly?

this is the sample test I sent you



I 'm edting the record number 6 where you found the NUMPRE or INVOICE NUMBER as "RR20062313371" ( RRAAMMHHMMSS)

ROOMS_ID 0006
TYPE 01
CHECK_IN 08/07/2021
CHECK_OUT 28/07/2021

the customer want another day until 29/07/2021

I cannot automatically add a day I have to have the program search for it if it is free or busy

in fact if you see well there is a record number 12 which is the same element (0006/01) and is occupied on the day 29/07/2021

so I didn't understand your unhealthy idea of adding a day without doing the proper research

>This could be automated. Just build a routine that looks for all free rooms for the customer's desired date range.
> Collect them all and present in a browse of available reservations for the user to select from. They could also be >sorted by room and/or type.

yes of course, I must publish also this ?

good




Naturally, the screen searches based on the search date and draws the beach section where the customer's (orange) umbrella is located
when the customer comes because he wants to be close to his friend or to change the umbrella I show him and choose with this screen
the OMB 11 on the picture is the record I 'am editing, the green boxes are free



but I had requested something else

in the simulation I sent you there were specific searches

and my question was another

that is, calling the Isfree () function this does not work, or rather it works the first time and then flickers

that is, it does not always perform all searches correctly, returning an incorrect value

[James Bott]

>I honestly don't understand what your common thread is and what the reservation number has to do with it.

No government requirements.
Do I have to explain on the public forum how I created the NUMPRE field or the INVOICE field?
Exactly, the field is 18 characters and is a string formed by an internal code RR (reservation "or SS" single receipt "or by another code that I would like not to explain in the public forum
and is formed by
Year
the current month
the current time including seconds

are you happy that I explained on public forum how that unique code was created?

Gee, Silvio, you sent me an email specifically requesting that I answer questions about this message thread on the forum, so that is what I did.

I just mentioned the ID since I didn't know there was an ID. If it was traditional (shorter and the first field) I would have assumed it was the ID. I must say you have come up with a unique way of generating a new ID--I have never seen anything like it before. As long as it works for you.

>I thought of this code when * someone * insisted telling me that with tdatabase (or tdata) I couldn't know the record number and adding +1 to the value because in ancient times the reservation number was made up of the record number - strzero (recno, 4) + 1 -

Well, that is not a complete description of that situation. In a multi-user system, you cannot assign an ID before a record is even entered by the user, since multiple other users could get the same ID before any of the records were saved. Then you will end up with a database having multiple records with the same ID.

>I would have liked not to publicly explain how the code is made, I don't see why this could affect the search I asked for, the order or reservation number must not be changed, it remains the same even when the record is in modification.

I was trying to simplify the search for you--I said nothing about changing a reservation number.

>the customer want another day until 29/07/2021

I cannot automatically add a day I have to have the program search for it if it is free or busy

in fact if you see well there is a record number 12 which is the same element (0006/01) and is occupied on the day 29/07/2021

so I didn't understand your unhealthy idea of adding a day without doing the proper research.

If you re-read the paragraph you are referring to, I said that if you are REMOVING days from a reservation you don't need to do a search. And if you adding them, then you do need to do a search. My exact words were:

Since you are going to free up a week (29/07/2021 is going to be changed to 21/07/2021). So you just change the 29 to 21 and save. No search needed. Of course, if the customer wants to extend their stay, then you will have to search.

James

[Silvio.Falconi]

>I honestly don't understand what your common thread is and what the reservation number has to do with it.

No government requirements.
Do I have to explain on the public forum how I created the NUMPRE field or the INVOICE field?
Exactly, the field is 18 characters and is a string formed by an internal code RR (reservation "or SS" single receipt "or by another code that I would like not to explain in the public forum
and is formed by
Year
the current month
the current time including seconds

are you happy that I explained on public forum how that unique code was created?

Gee, Silvio, you sent me an email specifically requesting that I answer questions about this message thread on the forum, so that is what I did.

I just mentioned the ID since I didn't know there was an ID. If it was traditional (shorter and the first field) I would have assumed it was the ID. I must say you have come up with a unique way of coming up with a new ID--I have never seen anything like it before. As long as it works for you.

>I thought of this code when * someone * insisted telling me that with tdatabase (or tdata)
I couldn't know the record number and adding +1 to the value because in ancient times the reservation number was made up of the record number - strzero (recno, 4) + 1 -

Well, that is not a complete description of that situation. In a multi-user system, you cannot just assign an ID before a record is even entered by the user, since multiple other people could get the same ID before any of the records were saved. Then you will end up with a database having multiple records with the same ID.

>I would have liked not to publicly explain how the code is made, I don't see why this could affect the search I asked for, the order or reservation number must not be changed, it remains the same even when the record is in modification.

I was trying to simplify the search for you--I said nothing about changing a reservation number.

>the customer want another day until 29/07/2021

I cannot automatically add a day I have to have the program search for it if it is free or busy

in fact if you see well there is a record number 12 which is the same element (0006/01) and is occupied on the day 29/07/2021

so I didn't understand your unhealthy idea of adding a day without doing the proper research.

If you re-read the paragraph you are referring to, I said that if you are REMOVING days from a reservation you don't need to do a search. And if you adding them, then you do need to do a search. My exact words were:

Since you are going to free up a week (29/07/2021 is going to be changed to 21/07/2021). So you just change the 29 to 21 and save. No search needed. Of course, if the customer wants to extend their stay, then you will have to search.

James

James,
>Gee, Silvio, you sent me an email specifically requesting that I answer questions about this message thread on the forum, so that is what I did.

I sent you a test with simulation of calls to the Isfree()

this test ( perhaps you not seen it

Code: Select all  Expand view

#include "FiveWin.ch"
#include "dtpicker.ch"


request dbfcdx
request dbffpt

request hb_lang_it
request hb_codepage_itwin




FUNCTION Main()

   RddSetDefault( "DBFCDX" )
   HB_LANGSELECT( "IT" )
   HB_SETCODEPAGE( "ITWIN" )
   SetHandleCount( 100 )
   FWNumFormat( "E", .t. )


   SET DATE FORMAT "dd-mm-yyyy"
   SET DELETED     ON
   SET CENTURY     ON
   SET EPOCH TO    year( date() ) - 20
   SET MULTIPLE    OFF


Test()

RETURN nil
//---------------------------------------------------------------------------------------//




Function test()
local oPrenotazioni
local cCamera
local cTypeRoom
local dCheck_in
local dCheck_out
local cNumPre:="RR20062313371   "
local oRecPrenota



     dCheck_in  := ctod("08/07/2021")
     dCheck_out := ctod("28/07/2021")

 oPrenotazioni := TReserva():New()



// simulation 1  Initial search on Archive from nInvoice number


 Msginfo("Now I search on archive the nIvoice number RR20062313371")

                  // search on archive the record
                  nInvoice:= alltrim(cNumPre)
                  oPrenotazioni:setorder("res_pre" )

              IF oPrenotazioni:seek(nInvoice)
                     oRecPrenota:=oPrenotazioni:record()  //modify
                     dFirst  := oRecPrenota:check_in
                     dLast   := oRecPrenota:check_out
                     cCamera := oRecPrenota:ROOMS_ID
                     cTypeRoom  := oRecPrenota:TYPE
                 ELSE
                     Msgstop("Reservation not found!!","Attention")
                     RETURN NIL
                  Endif

                  xbrowser oPrenotazioni

                // add a day on dLast
                 Msginfo("Now I add a day")
           IsFree(cCamera,dFirst,dLast+1,cTypeRoom,oPrenotazioni)   // 29/07/2021





              Msginfo("Now I change the number and the type, because the customer want change  room (0001) type 02, so the invoice number is the same ")

// simulation 2  // change number and type  same rage of dates

cCamera :="0001"
cTypeRoom  :="02"

IsFree(cCamera,dFirst,dLast+1,cTypeRoom,oPrenotazioni)





Msginfo("Now I change and return to initial state number room and type")

// simulation 3  // change on number and type initial , date ragnge intial

cCamera :="0006"
cTypeRoom  :="01"

IsFree(cCamera,dFirst,dLast,cTypeRoom,oPrenotazioni)


Msginfo("Now I change all numer 0002, type 01, one day 14/7/2021")
// simulation 4  // change on number and type  , date range

cCamera :="0002"
cTypeRoom  :="01"
dFirst:=ctod("14/07/2021")
dLast :=ctod("14/07/2021")
IsFree(cCamera,dFirst,dLast,cTypeRoom,oPrenotazioni)



Msginfo( "The invoice is seeked right on archive"+CRLF+ ;
           "Simulation 1 is ok the range ( 8/07 to 29/07 is not avaible )"+CRLF+ ;
        "Simulation 2 is no ok there's no reservation with number 0001"+CRLF+ ;
        "Simulation 3 is ok I return to initial search "+CRLF+ ;
        "Simulation 4 is no ok  for the 14/07  the rooms seem avaible but the function show is not avaible ")



oRecPrenota:end()
oPrenotazioni:close()
return nil

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

Function IsFree(cCamera,dCheck_in,dCheck_out,cTypeRoom,oPrenotazioni)

    local lFree
    local cSearch
          * ?  cCamera

    cSearch :=  oPrenotazioni:ApplyParams( "ROOMS_ID == ? .AND. ALLTRIM(TYPE) == ? .AND. RECNO() != ? .AND. (CHECK_IN > ? .OR. CHECK_OUT < ? )", ;
           { cCamera, ALLTRIM( cTypeRoom ),  oPrenotazioni:RecNo(), dCheck_out, dCheck_in } )

if  oPrenotazioni:LookUp( cSearch, nil, { || .T. } ) == .T.

   lFree:=.t.
   Msginfo("room is available"+CRLF+CRLF+;
             "Room number : "+cCamera+CRLF+;
             "Room ctype : "+cTypeRoom+CRLF+;
             "Check in   : "+cf(dCheck_in)+CRLF+;
             "Check out  : "+cf(dCheck_out)+CRLF+;
             "No Record     : "+ltrim(str(oPrenotazioni:RecNo())) )
else

   Msginfo("room is not availble"+CRLF+CRLF+;
             "Room number : "+cCamera+CRLF+;
             "Room ctype : "+cTypeRoom+CRLF+;
             "Check in   : "+cf(dCheck_in)+CRLF+;
             "Check out  : "+cf(dCheck_out)+CRLF+;
             "No Record     : "+ltrim(str(oPrenotazioni:RecNo())) )

    lFree:=.f.
endif

return lFree
//----------------------------------------------------------------------------------//
FUNCTION Cf(dTemp)
   LOCAL Meses:="GenFebMarAprMagGiuLugAgoSetOttNovDic"
   LOCAL f, m, cMes, dFech
   f:=DTOS (dTemp)
   m:=MONTH(dTemp)
   cMes:=IF(m<>0,SubStr(Meses,(m*3)-2,3),"")
   dFech:=IF(m<>0,SubStr(f,7,2)+'-'+cMes+'-'+SubStr(f,1,4),'')
   RETURN (dFech)

//---------------------------------------------------------------------------------//
//---------------------------------------------------------------------------------//
CLASS TXData from TDataBase
     DATA cDbfPath init cFilePath(GetModuleFileName( GetInstance() ))    //for test
ENDCLASS


CLASS TReserva from TXData
   METHOD New()
ENDCLASS

METHOD New( lShared ) CLASS TReserva
   Default lShared := .t.
   ::super:Open(,::cDbfPath + "Reserva" ,"DBFCDX", lShared)
   if ::use()
      ::setOrder(1)
      ::gotop()
   endif
   RETURN Sel

the invoice and numpre fields must remain so in modification, I thought of it in this way also to create labels with barcodes and qr code.

I don't use any IDs

the reserva file is indexed on ROOMS_ID + DToS (CHECK_IN)

>If you re-read the paragraph you are referring to, I said that if you are REMOVING days from a reservation you don't need to do a search. And if you adding them, then you do need to do a search. My exact words were:
>Since you are going to free up a week (29/07/2021 is going to be changed to 21/07/2021). So you just change the 29 to 21 and save. No search needed. Of course, if the customer wants to extend their stay, then you will have >to search.

sorry james, but to change the date of the reservation I have to go to "modify reservation" there are no other ways, the user cannot see the archive with a dbu as we see it

so if I open the booking change dialog I have to change the date range that the customer prefers, at the time of changing these dates the procedure must search the archive if the period is free or busy, if the customer he wants a week less or more, or a day more or less or he wants another number, how do I know if he is free or busy for the week in question.

so at any time if the operator being changed changes

1) the type of the element
2) the number
3) the check_in
4) check_out

the procedure must check if it is free or busy
the operator cannot guess

1) if it is free, it can confirm the change
2) if he is busy he must select an alternative of number or dates
or cancel the reservation


there is nothing difficult

I asked

how is it possible that if I call Isfree () several times and with different parameters it gives me an error or exactly does not return a true day value?

[James Bott]

Silvio,

>this test ( perhaps you not seen it)

No I have not, I will look at it.

>the invoice and numpre fields must remain so in modification

Of course.

>I don't use any IDs

I know, and this is one of the reasons you have to work twice as hard as the rest of us. I am working on sample bit of code, that I will show you as soon as it is ready. It is related to IDs.

>sorry james, but to change the date of the reservation I have to go to "modify reservation" there are no other ways, the user cannot see the archive with a dbu as we see it.

I was not implying that the user had to do anything different. They could enter the new dates, then the program decides how to process it based on the original and the new check_in and check_out dates. So if the user extends the date, then the program searches just the new day(s) is free. If the user shortens either the check_in or check_out date, then the program doesn't search, it just saves the record with the new dates. Simple.

I would really think about having an alternative option for the user to find new reservations. Using a blank reservation screen, the user then enters any options the customer wants, i.e. Room id, room_type, check_in, check_out, etc. The user MUST enter check-in and check-out, but other fields are optional. Then a search is done, showing all available reservations using the specified criteria in a browse. Maybe you could even use this for the current problem.

>there is nothing difficult

Apparently it is, or we wouldn't be having this conversation.

>I asked

how is it possible that if I call Isfree () several times and with different parameters it gives me an error or exactly does not return a true day value?

I don't know, but I will look at it.

James