• Welcome, Guest. Please login.
 
July 03, 2020, 01:24:23 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 - cj

31
Sorry, I can't figure this one out.  I do in a different way.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen "Need data"
 ReportDebtors
END FUNCTION

FUNCTION ReportDebtors() AS LONG
 slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select HospitalNo From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo"
    DO WHILE slGetRow()
      ? slf(1)
    LOOP
END FUNCTION
32
Post something with data and we can test it.
33
Another CASE statement that displays client number, message and balance without column alignment or column names.
"Q9c" option was needed with slselAry to get rid of the column name.

slselAry "select " +_
  " Case When Balance > 0 then 'Client '|| cnum || ' balance '  || printf('%.2f',balance*.01)"  +_
  "      When Balance < 0 Then 'Client '|| cnum || ' credit  '  || ABS(printf('%.2f',balance*.01))"  +_
  " else 'Client ' || cnum || ' no balance'" +_
  " end as Balances from ClientTable",s(),"Q9c"
See last 3-lines of the below image
34
Quote from: undefinedTotalBill = ServicesBill (Amount) +  tblPharmacyBill (Quantity*SellingPrice-Discount)

If TotalBill > Payments (Amount) then
    ? "Patient is a debtor"
Else
    ? "Patient not a debtor"
End If
The above code does not take into consideration the previous client balance.
Added a balance column into a ClientTable, posted transactions, updated balance.
Debtors will have a balance > 0.  Used a CASE statement to display message and the balance.
Also, demonstrates using pennies for all money to avoid any rounding error.
slselAry "select cnum," +_
  " Case When Balance > 0 then 'Patient is a debtor '  || printf('%.2f',balance*.01)"  +_
  "      When Balance < 0 Then 'Patient credit balance '  || ABS(printf('%.2f',balance*.01))"  +_
  " else 'Patient no balance'" +_
  " end as Patient_Balance_Report from ClientTable",s(),"Q9"
 s+=JOIN$(s(),$CR)
35
If slConnect is used and successful (returns 0) all data returned is from the machine running SQLiteningServer.exe.
If slConnect fails the program would normally exit function.

This demonstrates returning to local mode after slConnect failed (remote routines are still loaded.)
Not doing this will reward the user with transmission errors.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL sIpAddress,sarray() AS STRING,PortNumber AS LONG
 slconnect sIpAddress,PortNumber,"E0"
 IF slGetErrorNumber THEN 'connect error
  ? "Switching to local mode  ",%MB_SYSTEMMODAL,slGetError
  slSetProcessMods "L0"
 ELSE
  ? "Running in remote mode"
 END IF
 slopen "sample.db3"
 slselary "select rowid,manuf from parts limit 3",sarray(),"Q9"
 sldisconnect
 ? JOIN$(sarray(),$CR),%MB_SYSTEMMODAL,"bye"
END FUNCTION
36
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.
37
What is auto checkout?
Doesn't someone have to pay the bill and hand in a room key?
38
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)
39
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.
40
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.
41
What values? 
Please supply something with a database and tables.
42
Welcome
Hope you have something working that didn't require a trigger.
43
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