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

1
Hello CJ,

>>Call slSelAry (or slSel once.)

I still will like to make yours work for me.
2
Hello Bern,

 >>Maybe your client's system has a different (older) version of SQLite .dll installed?

Yeah that was the problem. Installed the latest version for the client and the error vanished. Thanks.
3
Hi CJ,

I tried your code directly against my database. It compiled and ran without showing any result.

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG

 slopen "HospitalProDB.db3","C"

 LOCAL sData() AS STRING

 slselary " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(SellingPrice*Quantity-Discount),'tblCashSales' from tblCashSales group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo",sData()

 slselary " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill  group by HospitalNo     UNION "  +_
          " select HospitalNo,SUM(SellingPrice * Quantity  - Discount),'tblCashSales' from tblCashSales  group by HospitalNo UNION "  +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo",sData()

 'without temp table
 LOCAL x,linecounter AS LONG
 DIM hospital(999) AS LONG
 DIM sArray(999)   AS STRING
 FOR x = 1 TO UBOUND(sdata,2)
  hospital(VAL(sdata(1,x))) += VAL(sdata(2,x)) 'combine amounts in column2
 NEXT
 sArray(0) = "Hospital  Amount"  'optional heading
 FOR x = 1 TO UBOUND(hospital)   'hospital 1 to ?
  IF hospital(x) THEN            'if hospital <> 0
   INCR linecounter
   sArray(linecounter) = USING$("#&##.##",x,$TAB,hospital(x)*.01)
  'sArray(linecounter) = FORMAT$(x) + $TAB + USINg$("##.##",hospital(x)*.01)
  END IF
 NEXT
 REDIM PRESERVE sArray(linecounter)
 ? JOIN$(sArray(),$CR),,"Balances"

END FUNCTION


I tried again using DDOC this time and discovered I could retrieve the balances.

#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 PBMAIN () AS LONG

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

   ReportDebtors

END FUNCTION

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 ReportDebtors() AS LONG
      LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL lTotalRecords&
      LOCAL dTotalAmount AS DOUBLE
      LOCAL dNetAmount AS DOUBLE
      LOCAL s AS STRING
      LOCAL dTotalDebts, dTotalBill, dTotalPayment AS DOUBLE

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_PORTRAIT, %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.0W1 L2.5W3 L5.5W1 L6.5W1 L7.5W1"

      ROW = PAGESIZE                            'force new heading

    slSEL " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(SellingPrice*Quantity-Discount),'tblCashSales' from tblCashSales group by HospitalNo" +_
          " UNION" +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments group by HospitalNo"

   slSEL " select HospitalNo,SUM(Amount-Discount), 'tblBill' from tblBill  group by HospitalNo UNION "  +_
          " select HospitalNo,SUM(SellingPrice * Quantity  - Discount),'tblCashSales' from tblCashSales group by HospitalNo UNION "  +_
          " select HospitalNo,SUM(Amount),  'tblPayments' from tblPayments 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_PORTRAIT, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           LOCAL hjpg AS LONG
             ''hjpg = dpAddGraphic(ihandle%, VD_App.Path & "Logo.jpg")
             IF hjpg THEN
                dpDrawGraphic ihandle%, hjpg,3.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%, 6,TopMargin,  %DDOC_Left,"Date: "+ PBDate(DATE$)+""
           dpText iHandle%, 6,TopMargin +.2,  %DDOC_Left,"Time: "+ TIME$+""
           ROW =TopMargin + LineSpacing * 4
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
           dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "Ameso Hospital" ''& gName
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
           dpText IHandle%, 4.25, 1.4, %DDOC_CENTER, "Ogudu, Lagos" ''& gAddress
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
           dpText IHandle%, 4.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 Debts: " & Format$(dTotalDebts, "#,.00")
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
           dpTabText iHandle%, ROW + 0.9, "SNo" + $TAB + "HospitalNo" + $TAB + "Surname" + $TAB + "Othernames" + $TAB + "Balance"
           dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        INCR SNo
        DetailLine$ =  _
          STR$(SNo) + $TAB + _
          slFN("HospitalNo") + $TAB + _
          slFN("Amount")

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

      LOOP

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

>>Then this should work (adjust table names to match your code):

This worked. I am going to test further in the days to come.

Thanks a million.

s=  "WITH"
        s+="   TempT1(TT1HospitalNo,Surname, Othernames,TT1Total) AS (SELECT HospitalNo, Surname, Othernames, Amount - Discount"
        s+="     FROM tblBill),"
        s+="   TempT2(TT2HospitalNo,Name,TT2Total) AS (SELECT tblCashSales.HospitalNo, Name, Quantity * SellingPrice - Discount"
        s+="     FROM tblCashSales),"
        s+="   SumT1( ST1HospitalNo, ST1Total) AS (SELECT TT1HospitalNo, TOTAL( TT1Total)"
        s+="     FROM TempT1 GROUP BY TT1HospitalNo),"
        s+="   SumT2( ST2HospitalNo, ST2Total) AS (SELECT TT2HospitalNo, TOTAL( TT2Total)"
        s+="     FROM TempT2 GROUP BY TT2HospitalNo),"
        s+="   SumT3(ST3HospitalNo, Surname, Othernames, ST3Total) AS (SELECT tblPayments.HospitalNo, Surname, Othernames, TOTAL(Amount)"
        s+="     FROM tblPayments GROUP BY tblPayments.HospitalNo)"
        s+=" SELECT tblBioData.HospitalNo, tblBioData.Surname, tblBioData.Othernames, ST1Total + ST2Total - ST3Total"
        s+="   FROM tblBioData"
        s+="   LEFT OUTER JOIN SumT1 ON tblBioData.HospitalNo = ST1HospitalNo"
        s+="   LEFT OUTER JOIN SumT2 ON tblBioData.HospitalNo = ST2HospitalNo"
        s+="   LEFT OUTER JOIN SumT3 ON tblBioData.HospitalNo = ST3HospitalNo"
        s+="   WHERE ST1Total + ST2Total > ST3Total"           

Aside: Is it possible to get the:

1 = near ""WITH": syntax error

on one system and not get it on another system? One of my clients had this error, but it worked fine on my system.
5
Hello CJ,

>>The dummy columns t1,t2,t3 are required to get the correct results.  Please test!

I will test it as soon as I am with my computer. Thanks a lot.
6
Hello Bern,

>>That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table. 

Thank you for your explanations.


>>Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.


Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.

I will follow your instructions and see how it goes. Thanks a lot.


7
Hello Cj,

I tried implementing your version today:

#COMPILE EXE
#DIM ALL

'Both routines are compared in this posting.
'
'Bern,
'Nice, learned a lot!!
'
'Bern routine results:
'1 payment record must exist to return results for a client
'2 credit balances do not display (they weren't asked for)
'3 optional, modified to add payments (passed as negative amounts)
'
'The CJ routine could eliminate the creating temp table by reading
'through the recordset and handling the debit/credit as they occur.
'Made it easier by just creating a temp file with the balances.
'
'Also, for some reason when I dropped the what I thought was unneeded third column in the
'union statement t1,t2,t3 the union was not always correct.  Need to look into that.
'Leaving "as is" is suggested.
'
'In this example CJ will return 4-records and Bern will return 2-records.
'Fredrick, these differences should be considered.
'
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
   LOCAL sData(),sBind AS STRING, x AS LONG

   slopen "HospitalProDB.db3","C"
   slexe  "drop table if exists TempBalances;create table TempBalances(HospitalNo unique,Amount)"

 'combine tables and payments
 slselary " select HospitalNo,SUM(Amount),'tblBill' from tblBill group by HospitalNo"  +_     'charges table1
          " union "                                      +_
          " select HospitalNo,SUM(SellingPrice * Quantity - Discount) as Amount,'tblCashSales' from tblCashSales group by HospitalNo"  +_     'charges table2
          " union "                                      +_
          " select HospitalNo,SUM(Amount),'tblPayments' from tblPayments group by HospitalNo",sData() 'payments table3

 'update TempBalances() from above sData()
 FOR x = 1 TO UBOUND(sdata,2)
  sBind = slBuildBindDat(sdata(1,x),"T") + slBuildBindDat(sData(2,x),"T")
  slexebind "Insert into TempBalances values(?,?) ON conflict(HospitalNo) DO update SET Amount=Amount+" + sData(2,x),sBind
 NEXT
 ? showit("select * from TempBalances"),,"Balances"
END FUNCTION

FUNCTION showit(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray(),$CR) + $CR
END FUNCTION

I got incomplete input error.

>>Bern routine results: 1 payment record must exist to return results for a client

I also noticed that all three tables needs to have data for a client to return results for that client.
8
>>Very interesting read:
https://proprivacy.com/vpn/comparison/nigeria-vpn

Thanks for the link. Interesting read.
9
Hello CJ,

>>I got Hamachi to connect, but didn't go any further.

How did you do it?
10
Hi CJ,

>>I will also add AES256 to SQLitening TCP within a few days.

That will be great.

>>NordVPN worked great.

Were you able to test without payment. Do they have a trial version? Anyway let's see how Hamachi goes.
11
I was able to sign in and completed downloading and installation. I could not see any help or tutorial file, just the community forum. I did a search for 'getting started' and tutorial and could not anything useful.

Is it that easy that there is no need for the help or tutorial file?
12
Thanks CJ,

>>Free up to 5 users, $49 a year for 32 users.

I would like to test the free package for 5 users first before purchase.

I am downloading Hamachi from https://vpn.net/
13
Hello CJ,

That is interesting. I would like to try it out from here.
14
Hello CJ,

Thanks. This is quite useful

I would try it out in a couple of days time.
15
Hello CJ,

Thank you for your response.

>>If using the server everyone can share the server without a problem.

Yes I intend to use the server.

The question is how to physically connect the server computer to the client computers. We have 11 remote locations that would want to connect to the server over the internet and 8 client computers that would be connected to server on a LAN since they are in the same building with the server computer.

Best regards.