• Welcome, Guest. Please login.
 
June 16, 2019, 10:36:32 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
>>I think there should only be one line counter.
QuotePrintHeading:
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
   dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & gName
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
   dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & gAddress
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
   dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
   dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
   dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
   dpLine IHandle, .5, 10.8, 8, 10.8, 1, %vbBlack
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
   AssetsHeight = 2.5
   dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
   AssetsHeight+= lineheight +.05 'looks a bit better   


I think I just followed your footsteps.

2
>>Instead of listing debits and then credits, how about different reports just listing the table?

Not quite. My General Ledger Transactions Report and Journal Report look very different. It would be interesting to have such variant report as you indicated.
3
>>Instead of listing debits and then credits, how about different reports just listing the table?

I have similar reports, the General Ledger Transactions Report and Journal Report.

But my final General Ledger Summary Report looks like the attached.
4
>>I think there should only be one line counter.

I see what you mean now. But I can't see the linecounter variable. Do you mean the lineheight?
5
I would take a look at it later today. Thanks.
6
Hello CJ,

This is what I came up with using slSEL. Just what I wanted. I think slSEL is more suitable for a report like this. Thanks a million.

#COMPILE EXE
#DIM ALL

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

%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 'fredrick10.bas

 slOpen "meganetERPDB.db3","C"
 PrintReport
END FUNCTION

FUNCTION PrintReport() AS LONG

  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
  LOCAL lineheight,LeftMargin AS SINGLE  'height deleted
  LOCAL AssetsHeight AS SINGLE
  LOCAL EquityHeight AS SINGLE
  LOCAL RevenueHeight AS SINGLE
  LOCAL ExpensesHeight AS SINGLE
  LOCAL NetProfitHeight AS SINGLE
  LOCAL szExpensesAccount AS ASCIIZ * 200
  LOCAL szExpensesAmount AS ASCIIZ * 100
  LOCAL szRevenueAccount AS ASCIIZ * 200
  LOCAL szRevenueAmount AS ASCIIZ * 100
  LOCAL szEquityAccount AS ASCIIZ * 200
  LOCAL szEquityAmount AS ASCIIZ * 100
  LOCAL szAssetsAccount AS ASCIIZ * 200
  LOCAL szAssetsAmount AS ASCIIZ * 100
  LOCAL sName,sAddress, sFrom, sTo AS STRING
  LOCAL dTotalRevenue, dTotalExpenses, dNetProfit, dTotalCredit, dTotalDebit AS DOUBLE

  sName="Green line":sAddress = "Red line"
  sFrom = "01-01-2019"
  sTo   = "28-05-2019"


  PageHeightMaximum = 10.1  'inches

  defaultzoom = %DDOC_ZOOM75
  zdefaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  'topmargin= 1.6    'not in use
  lineheight = .1666 '1/6  'each line height
  LeftMargin = 0.5



  zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
  DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM75  + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD

  ihandle = dpStartDoc(0,_
                       zDocTitle, _
                       zoutputfile,_
                       %DDOC_INCH,_
                       %DDOC_PAPER_A4,_
                       %DDOC_SYSTEM_DEFAULT, _
                       TheBin,_
                       DefaultZoom&)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  zoutputfile + " already exists so will delete document",,"dpStartDoc"
        KILL zoutputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF


  GOSUB PrintHeading

  'Revenue
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
   slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
   "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue' GROUP BY AccountName"
    DO WHILE slGetRow()
      szRevenueAccount = slFN("AccountName")
      szRevenueAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintRevenueDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Expenses
  dpFont ihandle, %DDOC_FONTNORMAL, 9, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses' GROUP BY AccountName"
  DO WHILE slGetRow()
      szExpensesAccount =  slFN("AccountName")
      szExpensesAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
    GOSUB PrintExpensesDetailLine
    'IF height => PageHeightMaximum THEN
    IF ExpensesHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
 LOOP
  SLEEP 200

  'Assets
  dpFont ihandle, %DDOC_FONTNORMAL, 5, %vbBlack, "Courier"
  LOCAL s AS STRING
   slSel "Select  AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  " WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName"
  DO WHILE slGetRow()
      szAssetsAccount = slFN("AccountName")
      szAssetsAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
      GOSUB PrintAssetsDetailLine
    'IF height => PageHeightMaximum THEN
    IF AssetsHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Equity
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
   slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
   "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
    DO WHILE slGetRow()
      szEquityAccount = slFN("AccountName")
      szEquityAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintEquityDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Total Credit
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalCredit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
     dTotalCredit = VAL(slFN("TotalCredit"))
  END IF
  slCloseSet

  'Total Debit
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalDebit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
     dTotalDebit = VAL(slFN("TotalDebit"))
  END IF
  slCloseSet

  GOSUB PrintTotalsDetailLine

  'Revenue
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalRevenue From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue'"
  IF ISTRUE slGetRow() THEN
     dTotalRevenue = VAL(slFN("TotalRevenue"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  'Expenses
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalExpenses From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses'"
  IF ISTRUE slGetRow() THEN
     dTotalExpenses = VAL(slFN("TotalExpenses"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  dNetProfit = dTotalRevenue - dTotalExpenses

  GOSUB PrintNetProfitDetailLine

  endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
  dpEndDoc iHandle, EndCode

EXIT FUNCTION

PrintHeading:
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
   dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & sName
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
   dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & sAddress
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
   dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
   dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
   dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
   'dpText IHandle, .5, 10.6, %DDOC_LEFT, "Total Debit: " & FORMAT$(dTotalDebit, "#,.00")
   'dpText IHandle, 3.5, 10.6, %DDOC_LEFT, "Total Credit: " & FORMAT$(dTotalCredit, "#,.00")
   dpLine IHandle, .5, 11.0, 8, 11.0, 1, %vbBlack
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
   RevenueHeight = 2.5
   dpText IHandle, .5, RevenueHeight, %DDOC_LEFT, "Revenue: "
   ExpensesHeight = 3.5
   dpText IHandle, .5, ExpensesHeight, %DDOC_LEFT, "Expenses: "
   NetProfitHeight = 6.5
   dpText IHandle, .5, NetProfitHeight, %DDOC_LEFT, "Net Profit: "
   AssetsHeight = 7.5
   dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
   EquityHeight = 9.0
   dpText IHandle, .5, EquityHeight, %DDOC_LEFT, "Equity: "
   RevenueHeight+= lineheight +.05 'looks a bit better
   ExpensesHeight+= lineheight +.05 'looks a bit better
   AssetsHeight+= lineheight +.05 'looks a bit better
   EquityHeight+= lineheight +.05 'looks a bit better
   'NetProfitHeight+= lineheight +.05 'looks a bit better
RETURN

PrintRevenueDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, RevenueHeight,%ddoc_left,szRevenueAccount
  dpText iHandle,7.0, RevenueHeight,%DDOC_DECIMAL,szRevenueAmount
  RevenueHeight+= lineheight
RETURN

PrintExpensesDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, ExpensesHeight,%ddoc_left,szExpensesAccount
  dpText iHandle,5.0, ExpensesHeight,%ddoc_decimal,szExpensesAmount
  ExpensesHeight+= lineheight
RETURN

PrintNetProfitDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,4.5, NetProfitHeight,%ddoc_left,FORMAT$(dNetProfit, "#,.00")
  NetProfitHeight+= lineheight
RETURN

PrintAssetsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left, szAssetsAccount
  dpText iHandle,5.0, AssetsHeight,%DDOC_DECIMAL, szAssetsAmount
  AssetsHeight+= lineheight
RETURN

PrintEquityDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, EquityHeight,%ddoc_left, szEquityAccount
  dpText iHandle,7.0, EquityHeight,%DDOC_DECIMAL, szEquityAmount
  EquityHeight+= lineheight
RETURN

PrintTotalsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  'dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  dpText IHandle, 0.5, 10.6, %DDOC_LEFT, "Total: "
  dpText IHandle, 5.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalDebit, "#,.00") & " Dr"
  'dpText IHandle, 5.7, 10.6, %DDOC_LEFT, "Total Credit: "
  dpText IHandle, 7.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalCredit, "#,.00") & " Cr"

  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION mySQLiteDate(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

7
Never mind. I think I know what to do.
8
Hello CJ,

>> %DDOC_DECIMAL

I must overlooked this. Thanks for bringing it to my notice. As I mentioned to you earlier I am making great progress in the report using post #7 as the base.
9
Hello CJ,

>>I also saw a $CRLF in the code (don't know what ddoc would do with that?)

You are right. It had no effect. I was just trying different things to space out the Credit and Debit to be on different vertical lines. I know what to do about that.

I will tryout your suggestions tomorrow and see it goes. It is 1.16am here already.

Thanks a bunch.
10
Hello CJ,

All versions of your latest codes worked great. I need to add more recordsets to make the full report.

The code below was my initial effort before I posted a limited array version. The only problem I have with this one is that, whenever I add, modify or delete record(s) the items fall out of position. If I could solve that I would be good:

Method ReportGeneralLedgerSummary(ByVal nCbHndl As Long) As Long
          Local LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
          Local detailline As Asciiz * 200
          Local DetailLine1 As Asciiz * 200
          Local DetailLine2 As Asciiz * 200
          Local DetailLine3 As Asciiz * 200
          Local lTotalRecords&
          Local SNo As Long
          Local dTotalIncome As Double
          Local dTotalExpenses As Double
          Local dBalance As Double
          Local sFrom As String
          Local sTo As String
          Local st      As String
          Dim a(10) As String
          Local i As Long
         
          LineSpacing = .2
          TopMargin   = .5
          PageSize    = 7
       
          ihandle% = dpStartDoc(0,"Mega-Net ERP - General Ledger Summary 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, "L0.5W4 L4.0W1"       
          Row = PageSize                            'force new heading
       
          sFrom = VD_GetText(nCbHndl, %ID_FRMGENARALLEDGERSUMMARYRPTDLG_DPKFROM)
          sTo = VD_GetText(nCbHndl, %ID_FRMGENARALLEDGERSUMMARYRPTDLG_DPKTO)
         
          ReportsInformation()
         
'          'Revenue
'          slSel "SELECT Sum(Credit) As TotalIncome, * FROM tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Revenue' GROUP BY AccountType"
'          If IsTrue slGetRow() Then
'             dTotalIncome = Val(slFN("TotalIncome"))
'          End If
'          slCloseSet
'         
'          slSel "SELECT Sum(Debit) As TotalExpenses, * FROM tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Expense' GROUP BY AccountType"
'          If IsTrue slGetRow() Then
'             dTotalExpenses = Val(slFN("TotalExpenses"))
'          End If
'          slCloseSet

          slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Revenue' GROUP BY AccountName"
         
          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 Accounts:  "+ 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, 13, %vbGreen, "Arial"
               dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & gName
               dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
               dpText IHandle%, 4.25, 1.3, %DDOC_CENTER, "" & gAddress
               dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
               dpText IHandle%, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
               dpText IHandle%, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
               dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
               dpText IHandle%, .5, 2.4, %DDOC_LEFT, "Revenue (Income): "
               'dpText IHandle%, 4.5, 2.4, %DDOC_RIGHT, Format$(dTotalIncome, "#,.00")       
               'dpText IHandle%, .5, 3.3, %DDOC_LEFT, "Expenses: "
               'dpText IHandle%, 4.5, 3.3, %DDOC_RIGHT, Format$(dTotalExpenses, "#,.00") 
               dBalance = dTotalIncome - dTotalExpenses
               dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 10, %vbBlack, "Arial"
               dpText IHandle%, .5, 5.8, %DDOC_LEFT, "Net Profit: "
               'dpText IHandle%, 4.5, 5.8, %DDOC_RIGHT, Format$(dBalance, "#,.00") 
               'dpText IHandle%, .5, 6.1, %DDOC_LEFT, "Assets: "
               'dpText IHandle%, .5, 9.3, %DDOC_LEFT, "Equity: " 
               dpLine IHandle%, .5, 10.3, 8, 10.3, 1, %vbBlack
               dpLine IHandle%, .5, 10.8, 8, 10.8, 1, %vbBlack
               dpTabText iHandle%, Row + 0.8, "    " & $CrLf & " Total Debits " & $CrLf & " Total Credits " & $CrLf & "Net Movement"
               dpFont ihandle%, %DDOC_FONTNORMAL, 9, %vbBlack, "San Serif"
               row =  row + LineSpacing * 2
            End If
             DetailLine$ =  _
              slFN("AccountName") + $Tab + _
              " " & $CrLf & _
              Format$(Val(slFN("CreditSUM")), "#,.00")
             
            dpTabText iHandle%, Row + 1.0, DetailLine
            Row = Row + LineSpacing                    'increment line counter
         
          Loop
         
          'Expenses
          slSel "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Expenses' GROUP BY AccountName"
          Do While slGetRow()
          DetailLine1$ =  _
              slFN("AccountName") + $Tab + _   
              Format$(Val(slFN("DebitSUM")), "#,.00") 
              dpTabText iHandle%, Row + 1.6, DetailLine1
            Row = Row + LineSpacing                    'increment line counter
            dpText IHandle%, .5, 3.3, %DDOC_LEFT, "Expenses: "
          Loop
       
          'Assets
          slSel "Select  AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _ 
          " WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName"
          Do While slGetRow()
          DetailLine2$ =  _
              slFN("AccountName") + $Tab + _   
              Format$(Val(slFN("DebitSUM")), "#,.00") 
              dpTabText iHandle%, Row + 3.8, DetailLine2
            Row = Row + LineSpacing                    'increment line counter
            dpText IHandle%, .5, 6.1, %DDOC_LEFT, "Assets: "
          Loop
         
          'Equity 
          slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _ 
          "WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
          Do While slGetRow()
          DetailLine3$ =  _
              slFN("AccountName") + $Tab + _
              " " & $CrLf & _   
              Format$(Val(slFN("CreditSUM")), "#,.00") 
              dpTabText iHandle%, Row + 6.0, DetailLine3
            Row = Row + LineSpacing                    'increment line counter
            dpText IHandle%, .5, 9.3, %DDOC_LEFT, "Equity: "
          Loop
             
          dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
    End Method 
   
11
Hello CJ,

Thanks a lot. I am going to try it later when electricity on or when I put on my generator by 7pm Nigeria time.

Kind regards.
12
I guess bindt is a function?
13
Hellon CJ,

>>Do you get a message when I post a response?

No. I guess because I don't use the email here anymore. I tried changing it to my current email without success.

>>1. SQLiteDate function is returning yyyy-dd-MM

It returns yyyy-MM-dd from dd-MM-yyyy here:

#COMPILE EXE
#DIM ALL

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
    LOCAL sFrom AS STRING
    LOCAL sTo AS STRING

    sFrom = "28-04-2019"
    sTo = "29-05-2019"

    ? " " & SQLiteDate(sFrom)

END FUNCTION
                                   

>> 2 & 3.

'? Join$(rs(),$CRLF),,"Data in array".  Was commented out in my codes above, because I don't use it when I want display recordset in DDOC. Check my code above.



14
Hello CJ,

I ran your last code. The issue is using Recordset(column,row) to get just the two columns I need out the lot. It seems its not possible. I might have to resort to using slSEL and loop through the records. This also has its issues using DDOC, that is why I wanted to use the slSELAry in the first place.

Regards.

15
Hello CJ,

Thank you for your suggestions. Its seems option 3 is the only feasible one for me. But I get the name of the columns instead of the records. And also fixing into my code another 'wahala'.

Here is where I want to put it:

#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

GLOBAL gName AS STRING
GLOBAL gAddress AS STRING

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
  'LOCAL rs() AS STRING
  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,TheBin,TheStyle,DefaultZoom,NoDialog,x,EndCode,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL OutputFile AS ASCIIZ * 64
  LOCAL DocTitle AS ASCIIZ * 64
  LOCAL height,lineheight,LeftMargin,topmargin AS SINGLE
  LOCAL DefaultFont AS ASCIIZ * 64
  LOCAL rsAssets() AS STRING
  LOCAL sFrom, sTo AS STRING
  LOCAL AssetsHeight AS SINGLE
  LOCAL szAssets AS ASCIIZ * 300


  sFrom = "28-04-2019"
  sTo = "29-05-2019"

  slOpen "meganetERPDB.db3","C"

  'Assets
  slSelAry "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName", rsAssets(),"Q9c"

  IF UBOUND(rsAssets) < 1 THEN ? "No data",,EXE.NAME$:EXIT FUNCTION
  '? Join$(rs(),$CRLF),,"Data in array"

  PageHeightMaximum = 10.1  'inches

  'DefaultZoom = %DDOC_ZOOM100
  'DefaultZoom = %DDOC_ZOOM75
  defaultzoom = %DDOC_ZOOMFIT
  defaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  topmargin= 2.6
  lineheight = .1666 '1/6  'each line height
  'outputfile ="cj.ddc"  'if multiuser would need unique names
  doctitle = "Mega-Net ERP - General Ledger Summary Report"

  'NoDialog = 1 'print direct with no dialog

  IF NoDialog THEN
    endCode = %DDOC_END_PRINT + %DDOC_END_DELETE
    DefaultZoom+= %DDOC_VIEWBUILD 'so no dialog will appear
  ELSE
    endCode = %DDOC_END_VIEW + %DDOC_END_DELETE

  END IF
  'outputfile = "MyFile.DDC" 'optionally name .DDC files and delete when done

  LeftMargin = 0.5
  height = topmargin
  AssetsHeight = 7.0

  ihandle = dpStartDoc(0,_
                       DocTitle, _
                       outputfile,_
                       %DDOC_INCH,_
                       %DDOC_PAPER_LETTER,_
                       %DDOC_SYSTEM_DEFAULT, _
                       TheBin,_
                       DefaultZoom& + %DDOC_VIEWBUILD)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  outputfile + " already exists so will delete document",,"dpStartDoc"
        KILL outputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF
  'if you let ddoc create the outputfile and need its name
  'dpGetFileName iHandle, outputfile, 64: ? outputfile,,"DDoc outputfile"

  IF FontSize THEN
     dpFont iHandle, FontStyle,FontSize,DefaultColor,DefaultFont$
  ELSE
     dpFont iHandle, FontStyle,12,DefaultColor,DefaultFont$
  END IF

 GOSUB PrintHeading

  'Assets
  FOR x = 1 TO UBOUND(rsAssets)
    szAssets = rsAssets(x) '+ " current height" + STR$(AssetsHeight)
    GOSUB PrintAssetsDetailLine
    IF height => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      AssetsHeight = 2  'start new page
    END IF
  NEXT


  IF NODIALOG THEN endCode = %DDOC_END_PRINT_NODIALOG
  'IF DpSpecifyPrinter(Ihandle,zPrinterName)THEN endcode% = %DDOC_END_SPECIFIC_PRINTER
  SLEEP 200
  dpEndDoc iHandle, EndCode
EXIT FUNCTION

PrintHeading:
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
   dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & gName
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
   dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & gAddress
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
   dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
   dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
   dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
   dpLine IHandle, .5, 10.8, 8, 10.8, 1, %vbBlack


PrintAssetsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
  dpText IHandle, .5, 6.8, %DDOC_LEFT, "Assets: "
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  AssetsHeight+= lineheight
RETURN

END FUNCTION

QuoteslSelAry is very convenient.
Some may prefer to loop through the recordset and optionally create an array with only certain items.
Since SQLite is embedded with PowerBASIC, the formatting of the recordset can be done with PowerBASIC or SQLite.

I agree with you. But I have used slSel for a long time now. Just starting to get a hang of slSelAry and I am learning a lot from you.

Best regards.