• Welcome, Guest. Please login.
 
December 13, 2019, 08:30:25 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

16
I know there are security concerns using SQLitening over the Internet. Would using SocketTools with SQLitening solve the security issues?
17
Hello,

Just wondering if SQLitening can be used on a LAN and at the same time over the Internet. I imagine a scenario where users (staff) use the SQLitening Application on a LAN while the Boss accesses or uses the application over the Internet from a remote location.

Any ideas? Thanks.
18
Hello CJ & Bern,

Thank you for helping out to find a solution to this.

I think I would along with Bern's version as I have been able to add more fields to it and implement it.

I haven't been able to make CJ's version work for me. I still intend to take another look at it later on.

Thank you guys, I appreciate your efforts and I have learnt a lot from this.

Best regards.
19
Sqlite3 supports CTEs from version 3.8.3 (see https://www.sqlite.org/changes.html). Not sure what SQLite DLL I have installed. I would check later on.

CJ,

The relational key to all three tables is the HospitalNo and in a many to many relationship.

>>Do payments have to be applied to invoices for some unknown reason?

Yes. Since the hospitals allows installment payments. Invoices are not completely paid for by the patient. Invoices carries the total bill, while the Payment Received table carries the amount paid by the patient.

>>Is this a running system?

Yes, it is.

20
Hello,

What are you guys reading to understand CTEs apart from the official documentation? Is it a new feature added to SQLite? It seems CTEs are powerful arsenal (a supporter of Arsenal by the way) to have.

Kind regards.
21
CJ, Same error near HospitalNo

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen ("HospitalProDB.db3", "C")
 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 [b]HospitalNo[[/b] 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             
22
Thanks CJ. I would give it a shot in the morning.
23
CJ, Check attached.

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"

'DDOC
'  Needed Color Constants (Vb compatible)
'---------------------------------------------------------------------------
%vbBlack = &H0&
%vbRed = &HFF&
%vbGreen = &HFF00&
%vbYellow = &HFFFF&
%vbBlue = &HFF0000
%vbMagenta = &HFF00FF
%vbCyan = &HFFFF00
%vbWhite = &HFFFFFF
%vbGrey = &HC0C0C0
%LIGHT_SHADE = &HE0E0E0

FUNCTION PBDate(sDate AS STRING) AS STRING
     'MM-dd-yyyy to dd-MM-yyyy

     FUNCTION = MID$(sDate,4,2) & "-" & MID$(sDate,1,2) & "-" & MID$(sDate,7,4)

END FUNCTION

FUNCTION NigeriaDate(sDate AS STRING) AS STRING
     'yyyy-MM-dd  to dd-MM-yyyy
     FUNCTION = MID$(sDate,9,2) & "-" & MID$(sDate,6,2) & "-" & MID$(sDate,1,4)

END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
     'From dd-MM-yyyy To yyyy-MM-dd

     FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)

END FUNCTION

FUNCTION PBMAIN () AS LONG

     slOpen ("HospitalProDB.db3", "C")

     ReportDebtors

END FUNCTION

FUNCTION ReportDebtors() AS LONG
      LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL lTotalRecords&
      LOCAL sFrom AS STRING
      LOCAL sTo AS STRING
      LOCAL dTotalAmount AS DOUBLE
      LOCAL dNetAmount AS DOUBLE

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_LANDSCAPE, %DDOC_SYSTEM_DEFAULT, %DDOC_BIN_AUTO OR %DDOC_ALLOWSMTP OR %DDOC_ALLOWSAVE OR %DDOC_ZOOMFIT)
      IF ihandle < 1 THEN
           MSGBOX "Could not StartDoc. Error number" + STR$(Ihandle)
           GOTO EndProgram
      END IF

      dpSetTabs iHandle, "L.5W1 L1.W1 L2.8W1 L5.4W2"

      ROW = PAGESIZE                            'force new heading

      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()

        IF ROW => PAGESIZE THEN
           IF PageNumber THEN      'If first page don't need a new page
              dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_LANDSCAPE, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           LOCAL hjpg AS LONG
             hjpg = dpAddGraphic(ihandle%, EXE.PATH$ & "Logo.jpg")
             IF hjpg THEN
                dpDrawGraphic ihandle%, hjpg,5.5,.2,2,1
             END IF

           dpText iHandle%, .5,TopMargin,  %DDOC_Left,"Page: "+ STR$(PageNumber)+""
           dpText iHandle%, .5,TopMargin + .2,  %DDOC_Left,"Total Records: "+ STR$(lTotalRecords&)+""
           dpText iHandle%, 10,TopMargin,  %DDOC_Left,"Date: "+ PBDate(DATE$)+""
           dpText iHandle%, 10,TopMargin +.2,  %DDOC_Left,"Time: "+ TIME$+""
           ROW =TopMargin + LineSpacing * 4
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
           'dpText ihandle, 6.25, 1.1, %DDOC_CENTER, "" & gName
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
           'dpText IHandle%, 6.25, 1.4, %DDOC_CENTER, "" & gAddress
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
           dpText IHandle%, 6.25, 1.7, %DDOC_CENTER, "Debtors Report"
           dpText IHandle%, 6.25, 2.0, %DDOC_CENTER, "Date Range:  " & sFrom  & " To " & sTo
           dpText IHandle%, .5, 2.3, %DDOC_Left, "Total Amount: " & FORMAT$(dTotalAmount, "#,.00")
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
           dpTabText iHandle%, ROW + 1.3, "S/No" + $TAB + "HNO"  + $TAB + "Surname" + $TAB + "Othernames"
           dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        DetailLine$ =  _
          STR$(SNo) + $TAB + _
          slFN("HospitalNo") + $TAB + _
          slFN("Surname") + $TAB + _
          slFN("Othernames")

          dpTabText iHandle%, ROW + 1.1, DetailLine
          ROW = ROW + LineSpacing                    'increment line counter

      LOOP

      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
 END FUNCTION
24
Hello Bern,

Thank you.

The table name are in the correct order in my version. I used the HospitalNo field in place of the Surname because it is common to all the three tables. I get an error near the bolded HospitalNo. It was the same error with your codes (Name).

Your Code: Error in the bolded "Name"
 
slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblBill " & _
          "Left OUTER JOIN tblBill On tblCashSales.Surname = tblBill.Surname " & _
          "GROUP BY Surname" & _
      "Select [b]Name[/b] From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.Name = tblPayments.Surname " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY Surname"                 

My version with the corect tables in place. Error near the bolded HospitalNo
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 [b]HospitalNo[/b] From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo" 

This method would work best for me if I could it to run.

Best regards.
25
Hello Bern,

The real names of the tables are not the ones I mentioned in my OP. I changed them in my OP just to depict what they do, so others can easily understand.

tblPharmacyBill is tblCashSales (Real Name)

tblPayments is tblPayments (Same Name)

tblServicesBill is tblBill (Real Name)

It still would not compile.

Thanks.
26
>>slexe "create table ClientTable  (Cnum integer,name text, balance integer,debits integer,credits integer)

After further study of your codes, I now understand what you were doing. It makes sense now. But if I create the ClientTable now and insert the values to it as both Billing tables and Payment tables are used, I won't be able to track the previous transactions.
27
>>Not sure why scanning the transactions and payments is needed.

The tblPayments is used to store patients payments against the bills on services (tblBill) and pharmacy (tblCashSales). Installment payments are allowed to clear up the bills.
28
Hello CJ,

Your solution is kinda complicated for me to follow and implement.

Hello Bern,

I am new to CTEs. I am trying to wrap my head around it. I think its a powerful technique.

29
I did that. Same error.
30
Hello Bern,

Wrapping the SQL statement in SQLitening is giving error near the second select statement.

slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblBill " & _
          "Left OUTER JOIN tblPharmacyBill On tblCashSales.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"