• Welcome, Guest. Please login.
 
December 13, 2019, 07:39:54 pm

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - Fredrick Ughimi

31
Wow! Thank you CJ and Been.

I would try both suggested solutions and get back to you.

Regards.
32
You've got Questions? We've got Answers! / Debtors List
November 05, 2019, 05:19:58 pm
Hello,

I am trying to figure out how one can get the list of debtors from the three tables below using the SQLite Statement.

Insignt:

TotalBill = ServicesBill (Amount) +  tblPharmacyBill (Quantity*SellingPrice-Discount)

If TotalBill > Payments (Amount) then
     ? "Patient is a debtor"
Else
     ? "Patient not a debtor"
End If


'ServicesBill
 slExe "Create Table If Not Exists tblServicesBill(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, Amount REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill
 slExe Build$("Create Table If Not Exists tblPharmacyBill(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Name TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")   

 'Payment
 slExe Build$("Create Table If Not Exists tblPayments(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

Any pointers would be appreciated. Thank you.
33
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.
34
>>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.

35
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.
36
>>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 ?
37
>>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.
38
>>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

39
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

40
Hello CJ,

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

Yes, it is working well.
43
>>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.
44
>>Using a timer, at startup, by a click?

Timer would be the best option in the situation. Say 12:05 everyday.
45
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