• Welcome to SQLitening Support Forum. Please login.
 
December 03, 2021, 06:19:13 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
I thought the system was under construction, but obviously already done.
Would have to see what the output is supposed to look like.  Nothing like I have seen.
I renamed tables to t1,t2,t3  to try to keep in brain. (services bill, cash/pharmacy bill, payments)
https://sqlite.org/lang_with.html  'need to study WITH

Bern,
I created some data (not good data for testing.)
Renamed amount and surname so they were not duplicates in table1 and table3
Get misuse of aggregate TOTAL().  Remarked Total and it runs.
Learning.  Never even knew about the WITH statement.

Fredrick,
Could you add a few records to have some relational data to test?

I find it a lot easier to use table  T1,T2,T3 while testing.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(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       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3
s+="   LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="   GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB



#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL  "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(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      changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",    REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3 to get to work
s+="  LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="  GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="  LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="  WHERE TotalBill > Total(Amount3)"
s+="  GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
32
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
33
Post something with data and we can test it.
34
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
35
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)
36
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
37
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.
38
What is auto checkout?
Doesn't someone have to pay the bill and hand in a room key?
39
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)
40
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.
41
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.
42
What values? 
Please supply something with a database and tables.
43
Welcome
Hope you have something working that didn't require a trigger.
44
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