Hello,
I am stump trying to figure out how to automatically 'trigger' this sequence of SQL startements. I wonder if Trigger is the best condidate.
'Free room
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $Nul & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
'Copy record to History Table
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
Delete Record
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime <= time('12')"
But triggers are only triggered when an INSERT, DELETE or UPDATE occurs. I want the statements triggered when this conditions are met:
If (sDepartureDate < Date$) Or (sDepartureDate = Date$ And Time$ > "12") Then
....
End If
Any help would be appreciated.
Sorry, somebody else might have trigger logic.
Might be able to create a temporary table and create a trigger on it.
https://www.sqlitetutorial.net/sqlite-trigger/
A recordset could be created using a CASE statement
to test any conditions and write what to do for any
of those conditions. When done, process the recordset.
CASE statements can test the same record multiple times
and can be nested.
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN AS LONG
slopen ":memory:"
slexe "create table t1(c1 integer)"
FOR x& = -10 TO 10:slexe "insert into t1 values(" + STR$(x&) + ")":NEXT
s$ = "select c1, "
s$+= " case"
s$+= " when c1 > 0 THEN 'greater'"
s$+= " when c1 < 0 THEN 'less'"
s$+= " else 'equal'"
s$+= " end as NewColName"
s$+= " from t1"
slselary s$,sarray$(),"Q9"
? JOIN$(sarray$(),$CR)
END FUNCTION
Hello CJ,
Interesting. Trying to adapt your technique.
Thanks.
Hello CJ,
Looks good. I would need to do some weeding.
How do I make this piece code activate itself without clicking anything to bring it to action? Thread?
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION CDate(Pbd AS STRING) AS STRING
'Change PB date to mm-dd-yyyy to yyyy-mm-dd
FUNCTION = MID$(Pbd,7,4) & "-" & MID$(Pbd,1,2) & "-" & MID$(Pbd,4,2)
END FUNCTION
FUNCTION PBMAIN AS LONG
LOCAL x AS LONG
LOCAL s$
DIM sarray$()
LOCAL Errorcode&
LOCAL sRoomNo AS STRING
LOCAL sRecordNo AS STRING
sRoomNo = "501"
slOpen ("GuestsProDB.db3","C")
'Registration
slExe BUILD$("Create Table If Not Exists tblRegistration(Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE, Address TEXT,", _
"MobileNo TEXT, CompanyName TEXT, Position TEXT, Nationality TEXT, ArrivalDate TEXT, ArrivalTime TEXT, NoOfNights TEXT, DepartureDate TEXT, DepartureTime TEXT,", _
"StateArrivingFrom TEXT, StateGoingTo TEXT, Identification TEXT, IDNo TEXT, Sponsor TEXT, PurposeOfVisit TEXT, NoOfGuests TEXT, RoomNo TEXT, RoomRate TEXT,", _
"RoomType TEXT, BillNo TEXT, StayOver)")
slExe "Create Index If Not Exists Registrationndx1 ON tblRegistration(BillNo)"
slExe "Create Index If Not Exists Registrationndx2 ON tblRegistration(Surname)"
slExe "Create Index If Not Exists Registrationndx3 ON tblRegistration(Othernames)"
FOR x = -10 TO 10:NEXT
s$ = "select DepartureDate,DepartureTime,"
s$+= " case"
s$+= " when DepartureDate <= date('now') THEN 'greater'"
s$+= " when DepartureDate = date('now') THEN 'less'"
s$+= " WHEN DepartureTime > time('12') THEN 'Greater Than'"
's$+= " else 'equal'"
s$+= " end as NewColName"
s$+= " from tblRegistration"
? s$
slselary s$,sarray$(),"Q9"
? JOIN$(sarray$(),$CR)
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $NUL & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
IF Errorcode& = 19 THEN
MSGBOX "Bill No Already Sent And Is Still Existing In The Registeration History", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
EXIT FUNCTION
END IF
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime >= time('12')"
END FUNCTION
Rearranged the code into a couple of functions.
Not sure when you want to automatically call a function.
Using a timer, at startup, by a click?
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN AS LONG
CreateRecords
ViewRecords
END FUNCTION
FUNCTION CreateRecords AS LONG
LOCAL x AS LONG
LOCAL s$
DIM sarray$()
LOCAL Errorcode&
LOCAL sRoomNo AS STRING
LOCAL sRecordNo AS STRING
sRoomNo = "501"
slOpen ("GuestsProDB.db3","C")
'Registration
slExe BUILD$("Create Table If Not Exists tblRegistration(Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE, Address TEXT,", _
"MobileNo TEXT, CompanyName TEXT, Position TEXT, Nationality TEXT, ArrivalDate TEXT, ArrivalTime TEXT, NoOfNights TEXT, DepartureDate TEXT, DepartureTime TEXT,", _
"StateArrivingFrom TEXT, StateGoingTo TEXT, Identification TEXT, IDNo TEXT, Sponsor TEXT, PurposeOfVisit TEXT, NoOfGuests TEXT, RoomNo TEXT, RoomRate TEXT,", _
"RoomType TEXT, BillNo TEXT, StayOver)")
slExe "Create Index If Not Exists Registrationndx1 ON tblRegistration(BillNo)"
slExe "Create Index If Not Exists Registrationndx2 ON tblRegistration(Surname)"
slExe "Create Index If Not Exists Registrationndx3 ON tblRegistration(Othernames)"
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $NUL & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
IF Errorcode& = 19 THEN
MSGBOX "Bill No Already Sent And Is Still Existing In The Registeration History", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
EXIT FUNCTION
END IF
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime >= time('12')"
END FUNCTION
FUNCTION ViewRecords AS LONG
LOCAL s,sArray() AS STRING
s = "select DepartureDate,DepartureTime,"
s+= " case"
s+= " when DepartureDate <= date('now') THEN 'greater'"
s+= " when DepartureDate = date('now') THEN 'less'"
s+= " WHEN DepartureTime > time('12') THEN 'Greater Than'"
's$+= " else 'equal'"
s+= " end as NewColName"
s+= " from tblRegistration"
slselary s,sarray$(),"Q9"
? JOIN$(sarray$(),$CR)
END FUNCTION
>>Using a timer, at startup, by a click?
Timer would be the best option in the situation. Say 12:05 everyday.
Would it be better to use task scheduler in case your program isn't running?
https://www.windowscentral.com/how-create-automated-task-using-task-scheduler-windows-10
https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-for-windows/763455-running-a-task-scheduler-shortcut
>>Would it be better to use task scheduler in case your program isn't running?
I would first like to implement while the application is running, so the program notifies user that a guest has been automatically checked out.
Looking at adapting:
https://sqlitening.planetsquires.com/index.php?topic=3412.msg24030#msg24030
Post #63.
FUNCTION PBMAIN () AS LONG
LOCAL hThread AS LONG
THREAD CREATE CheckEveryHour(1000 * 60 * 60) TO hThread
THREAD CLOSE hThread TO hThread
? "Done"
END FUNCTION
THREAD FUNCTION CheckEveryHour(BYVAL Milliseconds AS LONG) AS LONG
LOCAL x AS LONG
DO
IF LEFT$(TIME$,2) = "XX" THEN CALL Something
SLEEP milliseconds
LOOP
END FUNCTION
SUB Something
BEEP
END SUB
Thank you Cj.
Welcome
Hope you have something working that didn't require a trigger.
Hello CJ,
>>Hope you have something working that didn't require a trigger.
Yes, it is working well.
Hello CJ,
Just discovered that my auto delete works fine:
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime >= time('12')"
But I could not effectively implement my other two statements, since they are dependent on string variables which are not easy to fill with values due to the nature of the AutoCheckOutGuests function:
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $Nul & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
'Copy record to History Table
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
Full Function:
Function AutoCheckOutGuests() As Long
Local Errorcode&
Local CheckoutAns&
Local sRoomNo As String
Local sRecordNo As String
Local x As Long
Local s$
For x = -10 To 100:Next
s$ = "SELECT RowID as RecordNo,RoomNo as rRoomNo, DepartureDate,DepartureTime,"
s$+= " case"
s$+= " when DepartureDate <= date('now') THEN 'greater'"
s$+= " when DepartureDate = date('now') THEN 'less'"
s$+= " WHEN DepartureTime > time('12') THEN 'Greater Than'"
's$+= " else 'equal'"
s$+= " end as NewColName"
s$+= " from tblRegistration"
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $Nul & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
If Errorcode& = 19 Then
MsgBox "Bill No Already Sent And Is Still Existing In The Registeration History", %MB_TASKMODAL Or %MB_ICONINFORMATION, Exe.Name$
Exit Function
End If
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime >= time('12')"
If slGetChangeCount = 0 Then
? "No Guests is due to leave", %MB_TASKMODAL Or %MB_ICONINFORMATION, Exe.Name$
Exit Function
Else
? "GuestS moved to the History table and deleted from the current guests table", %MB_TASKMODAL Or %MB_ICONINFORMATION, Exe.Name$
End If
End Function
What values?
Please supply something with a database and tables.
>>Please supply something with a database and tables.
sRoomNo and sRecordNo
>>Please supply something with a database and tables.
#COMPILE EXE
#DIM ALL
#INCLUDE "SQLitening.inc"
FUNCTION PBMAIN () AS LONG
slOpen ("GuestsProDB.db3","C")
'Registration
slExe BUILD$("Create Table If Not Exists tblRegistration(Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE, Address TEXT,", _
"MobileNo TEXT, CompanyName TEXT, Position TEXT, Nationality TEXT, ArrivalDate TEXT, ArrivalTime TEXT, NoOfNights TEXT, DepartureDate TEXT, DepartureTime TEXT,", _
"StateArrivingFrom TEXT, StateGoingTo TEXT, Identification TEXT, IDNo TEXT, Sponsor TEXT, PurposeOfVisit TEXT, NoOfGuests TEXT, RoomNo TEXT, RoomRate TEXT,", _
"RoomType TEXT, BillNo TEXT, StayOver)")
slExe "Create Index If Not Exists Registrationndx1 ON tblRegistration(BillNo)"
slExe "Create Index If Not Exists Registrationndx2 ON tblRegistration(Surname)"
slExe "Create Index If Not Exists Registrationndx3 ON tblRegistration(Othernames)"
'RegistrationHistory
slExe BUILD$("Create Table If Not Exists tblRegistrationHistory(Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE, Address TEXT,", _
"MobileNo TEXT, CompanyName TEXT, Position TEXT, Nationality TEXT, ArrivalDate TEXT, ArrivalTime TEXT, NoOfNights TEXT, DepartureDate TEXT, DepartureTime TEXT,", _
"StateArrivingFrom TEXT, StateGoingTo TEXT, Identification TEXT, IDNo TEXT, Sponsor TEXT, PurposeOfVisit TEXT, NoOfGuests TEXT, RoomNo TEXT, RoomRate TEXT,", _
"RoomType TEXT, BillNo TEXT, StayOver)")
slExe "Create Index If Not Exists RegistrationHistoryndx2 ON tblRegistrationHistory(BillNo)"
slExe "Create Index If Not Exists RegistrationHistoryndx3 ON tblRegistrationHistory(Surname)"
slExe "Create Index If Not Exists RegistrationHistoryndx4 ON tblRegistrationHistory(Othernames)"
'RoomRates
slExe "Create Table If Not Exists tblRoomRates (RoomNo TEXT, RoomType, CashRate, CashDeposit, CreditRate, CreditDeposit, Status)"
slExe "Create UNIQUE Index If Not Exists RoomRatesndx1 ON tblRoomRates(RoomNo)"
'Call AutoCheckOut
AutoCheckOutGuests
END FUNCTION
FUNCTION AutoCheckOutGuests() AS LONG
LOCAL Errorcode&
LOCAL CheckoutAns&
LOCAL sRoomNo AS STRING
LOCAL sRecordNo, sStatus AS STRING
LOCAL x AS LONG
LOCAL s$
sStatus = "Available"
FOR x = -10 TO 100:NEXT
s$ = "SELECT RowID as RecordNo,RoomNo as rRoomNo, DepartureDate,DepartureTime,"
s$+= " case"
s$+= " when DepartureDate <= date('now') THEN 'greater'"
s$+= " when DepartureDate = date('now') THEN 'less'"
s$+= " WHEN DepartureTime > time('12') THEN 'Greater Than'"
's$+= " else 'equal'"
s$+= " end as NewColName"
s$+= " from tblRegistration"
slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $Nul & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
IF Errorcode& = 19 THEN
MSGBOX "Bill No Already Sent And Is Still Existing In The Registeration History", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
EXIT FUNCTION
END IF
slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime >= time('12')"
IF slGetChangeCount = 0 THEN
? "No Guests is due to leave", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
EXIT FUNCTION
ELSE
? "GuestS moved to the History table and deleted from the current guests table", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
END IF
END FUNCTION
I think the unique billno should be used instead of recordno.
Everything should be in a transaction.
I would think knowing the roomno or billno would have to be passed to the function.
I don't see any need for the CASE statement.
>>I think the unique billno should be used instead of recordno.
Yeah the BillNo is more reliable
>>Everything should be in a transaction.
Yes
>>I would think knowing the roomno or billno would have to be passed to the function.
Like this? Function AutoCheckOutGuests(ByVal roomno As Long, ByVal billno As Long) As Long
>>I don't see any need for the CASE statement.
Serves a good purpose here. Responsible for triggering the statements.
Yes
Function AutoCheckOutGuests(ByVal roomno As Long, ByVal billno As Long) As Long
BYVAL not required unless the value might be changed and caller needs original number.
If you use all strings then passing strings is fine.
>>If you use all strings then passing strings is fine.
The were all defined as string
Function AutoCheckOutGuests(sRoomNo As String, sBillNo As String) As String ?
If you have the room number you probably can get the bill number.
If you have the bill number you might not need the room number.
I
LOCAL sRoomNo AS STRING
LOCAL sBillNo AS STRING
LOCAL ecode AS LONG
ecode = AutoCheckOutGuests(sRoomNo, sBillNo)
Getting the sBillNo or sRoomNo is the issue now.
If I was executing the code under MLG Notification or Timer I can get both the sBillNo and sRoomNo values
I need to look at using it under MLG or Listview.
What is auto checkout?
Doesn't someone have to pay the bill and hand in a room key?
>>What is auto checkout?
Good question. A client requested for it.
>>Doesn't someone have to pay the bill and hand in a room key?
>>I have tried talking the client out of it, but he wouldn't bulge. He says making the room automatically available after date and time has expired would make the workers at the front office forcefully make the room available physical. His reason doesn't really sit well with me.
Sounds like they owe another day or need to be evicted.
I think they would automatically be charged another day.
or
I suppose they could have left without checking out.
But if they come back would be a real problem if they still have a key.
Hello Cj,
The management of the hotel don't allow guests to owe the hotel except guests that are registered debtors. They have a mechanism is place to get their money from the debtors.
They are trying to prevent guests that are in the habit of delaying their exit when their stay has expired. The feature still doesn't make sense to me.
In the meantime I decided to only display guests that their DepartureDate < Date$, without deleting any records.
Quote from: Fredrick Ughimi on September 05, 2019, 03:57:53 pmHello,
I am stump trying to figure out how to automatically 'trigger' this sequence of SQL startements. I wonder if Trigger is the best condidate.
...
But triggers are only triggered when an INSERT, DELETE or UPDATE occurs. I want the statements triggered when this conditions are met:
If (sDepartureDate < Date$) Or (sDepartureDate = Date$ And Time$ > "12") Then
....
End If
Any help would be appreciated.
TRIGGERs are great. You will likely need 3 triggers to cover all possible data changes that might impact the condition you want monitored: INSERT and DELETE on tblRegistration and UPDATE on tblRegistration.DepartureDate