• Welcome, Guest. Please login.
 
November 21, 2019, 04:01:19 pm

News:

Welcome to the SQLitening support forums!


Is Auto Checkout Guests Candidate For Trigger?

Started by Fredrick Ughimi, September 05, 2019, 03:57:53 pm

Previous topic - Next topic

Fredrick Ughimi

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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

September 06, 2019, 10:24:47 am #1 Last Edit: September 07, 2019, 02:01:28 pm by cj
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

Fredrick Ughimi

Hello CJ,

Interesting. Trying to adapt your technique.

Thanks.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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
                                   
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

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

Fredrick Ughimi

>>Using a timer, at startup, by a click?

Timer would be the best option in the situation. Say 12:05 everyday.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj



Fredrick Ughimi

>>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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

September 06, 2019, 10:03:50 pm #10 Last Edit: September 06, 2019, 10:05:41 pm by cj
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

Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

Welcome
Hope you have something working that didn't require a trigger.

Fredrick Ughimi

Hello CJ,

>>Hope you have something working that didn't require a trigger.

Yes, it is working well.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet