• Welcome, Guest. Please login.
 
August 12, 2020, 09:14:48 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 8 9 10
91
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Fredrick Ughimi - November 06, 2019, 01:19:33 pm
Wow! Thank you CJ and Been.

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

Regards.
92
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
93
Quote from: cj on July 21, 2019, 11:15:22 am2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

My original code is for generating time stamps on the server.  The SQLite custom function can be called/used from within SQL statements.  This allows for transaction processing using TRIGGERs.  For example, when modifying data in a table (insert, update, delete), a trigger could log the change made and server time in another (transaction) table.  The use of a TRIGGER with the custom function ensures that the logging is atomic with the change.
94
You've got Questions? We've got Answers! / Re: SELECT strftime
Last post by Bern Ertl - November 06, 2019, 12:06:48 pm
Quote from: Fim on October 16, 2019, 01:02:33 pm... how to get it from the server?

I posted some code years ago for creating and using a server side time stamp.  It uses Windows FILETIME data, but it's easily converted to whatever format you need:

https://sqlitening.planetsquires.com/index.php?topic=3539.0
95
Quote from: Fredrick Ughimi on July 20, 2019, 08:13:27 pm...
What is the difference SQLiteningProcsA and SQLiteningProcsB?
...

It doesn't matter. 

SQLiteningProcsA
SQLiteningProcsB
SQLiteningProcsC
SQLiteningProcsD
SQLiteningProcsE
SQLiteningProcsF
...
SQLiteningProcsZ


You can use any of them.  SQLitening allows you to organize your server side code libraries into separate files with this naming convention, but there is no difference as to which you use.  It's just for you to organize your code.

I mainly use SQLiteningProcsT and SQLiteningProcsP for my server code.  The "T" library contains server code that is loaded, used and unloaded (ie. "Temporary").  The "P" library contains code that is loaded and stays loaded until the app closes (ie. "Permanent").
96
You've got Questions? We've got Answers! / Re: Debtors List
Last post by Bern Ertl - November 06, 2019, 11:50:17 am
I believe that you can get the list in one single SQL SELECT statement.  Something like this should work (untested):

WITH Bills(Name,TotalBill) AS (SELECT Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount)
   FROM tblServicesBill
   LEFT OUTER JOIN tblPharmacyBill ON tblServicesBill.Surname = tblPharmacyBill.Surname
   GROUP BY Surname
SELECT Name FROM Bills
   LEFT OUTER JOIN tblPayments ON Bills.Name = tblPayments.Surname
   WHERE TotalBill > Total( tblPayments.Amount)
   GROUP BY Surname
97
You've got Questions? We've got Answers! / Debtors List
Last post by Fredrick Ughimi - 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.
98
You've got Questions? We've got Answers! / Re: SELECT strftime
Last post by Fim - October 17, 2019, 04:30:56 am
Ok, thanks for the answer.
/Fim W.
99
You've got Questions? We've got Answers! / Re: SELECT strftime
Last post by cj - October 16, 2019, 08:16:06 pm
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
100
You've got Questions? We've got Answers! / SELECT strftime
Last post by Fim - October 16, 2019, 01:02:33 pm
SELECT strftime('%H:%M:%f','now','localtime')
Is it the timestamp from the server or the client.
I can't find it in the manual, or I'm bad at finding it.

And, if it is from the client, how to get it from the server?
/Fim W
Pages 1 ... 8 9 10