SQLitening Support Forum

Hello,

With the following slSelAry statement:

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"

How do I return only AccountName and DebitSUM columns?

Kind regards.

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.

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.

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.

I guess bindt is a function?

This might work with ddoc

#INCLUDE "sqlitening.inc"

MACRO WRAP(p1)   = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")

FUNCTION PBMAIN () AS LONG

 LOCAL rs(),sFrom,sTo,sAccountType AS STRING
 LOCAL cols,rows,tabstops() AS LONG
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "STRINGBUILDERA"

 CreateTestData

 'AccountType Report
 sFrom = "01-01-2019"
 sTo   = "28-05-2019"
 sAccountType = WRAP("Assets")

 sb.clear
 sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
 sb.add " FROM tblJournalEntry"
 sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom)  + " AND " + SQLiteDate(sTo)
 sb.add " and AccountType ="   + sAccountType       + " GROUP BY AccountName"

 slselary(sb.string,rs(),"E0")
 IF slGetErrorNumber THEN
  ? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
  EXIT FUNCTION
 END IF

 cols = UBOUND(rs,1)
 rows = UBOUND(rs,2)
 IF rows < 1 THEN
  ? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
  EXIT FUNCTION
 END IF

 REDIM tabstops(1 TO 99) 'avoid assigning too many
 ARRAY ASSIGN tabstops() = 1,40
 PrintReport rs(),tabStops()

END FUNCTION

FUNCTION PrintReport(rs() AS STRING,tabStops() AS LONG) AS LONG

 LOCAL c,r,linecounter AS LONG
 LOCAL sLine AS STRING

 FOR r = 1 TO  UBOUND(rs,2)
  sLine = SPACE$(80)
  FOR c = 1 TO UBOUND(rs,1)
   MID$(sLine,tabstops(c)) = rs(c,r)
  NEXT
  INCR LineCounter
  ? sLine,,USING$("LineCounter #",LineCounter)
 NEXT
END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
 'From dd-MM-yyyy To yyyy-MM-dd
 FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION

SUB CreateTestData

 LOCAL x,y AS LONG
 LOCAL s AS STRING

 slOpen "junk1.db3","C"
 slexe  "drop table if exists tblJournalEntry"
 slexe  "create table tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"
 FOR x = 1 TO 3
  FOR y = 1 TO 3
   s =  bindt("account"+FORMAT$(y)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(y))
   slexebind "insert into tblJournalEntry values(?,?,?,?)",s
  NEXT
 NEXT
END SUB

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.

Hope you get the power working!

I hard-coded parse$, but that should be modified to accept a variable number of columns.
PrintReport() could be modified to print many different reports.
It could be done in different ways including using a 2-dimensional array.
I like it.
This is what I see (click image)

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

MACRO WRAP(p1)   = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0
GLOBAL gName,gAddress AS STRING

FUNCTION PBMAIN () AS LONG 'fredrick10.bas
 gName="Green line":gAddress = "Red line"
 LOCAL rs(),sFrom,sTo,sAccountType AS STRING
 LOCAL cols,rows,tabstops() AS LONG
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "STRINGBUILDERA"

 CreateTestData

 sFrom = "01-01-2019"
 sTo   = "28-05-2019"
 sAccountType = WRAP("Assets")

 sb.clear
 sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
 sb.add " FROM tblJournalEntry"
 sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom)  + " AND " + SQLiteDate(sTo)
 sb.add " and AccountType ="   + sAccountType       + " GROUP BY AccountName"

 slselary(sb.string,rs(),"Q9cE0")
 IF slGetErrorNumber THEN
  ? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
  EXIT FUNCTION
 END IF
 IF UBOUND(rs) < 1 THEN
  ? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
  EXIT FUNCTION
 END IF

 PrintReport rs(),sFrom,sTo

END FUNCTION

FUNCTION PrintReport(rsAssets() AS STRING,sFrom AS STRING, sTo AS STRING) 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 szAssets AS ASCIIZ * 300

  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
  AssetsHeight = 1.0

  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_LETTER,_
                       %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
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
  LOCAL s AS STRING
  szAssets = SPACE$(80)
  FOR x = 1 TO UBOUND(rsAssets)
    LSET szAssets =      PARSE$(rsAssets(x),$TAB,1)
    MID$(szAssets,20) =  PARSE$(rsAssets(x),$TAB,2)
    MID$(szAssets,40) = "current height" + STR$(AssetsHeight)
    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, "Courier"
    END IF
  NEXT
  SLEEP 200
  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, "" & 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
RETURN

PrintAssetsDetailLine:
  'dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
 'From dd-MM-yyyy To yyyy-MM-dd
 FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION

SUB CreateTestData
 LOCAL acctnum AS LONG
 LOCAL counter AS LONG
 LOCAL s AS STRING
 slOpen "junk1.db3","C"
 'slexe  "drop table if exists tblJournalEntry"
 slexe  "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"

 slexe "begin exclusive"
 FOR counter = 1 TO 1    'insert this many records into acctnum
  FOR acctnum = 1 TO 100 'number of accounts
   s =  bindt("account"+FORMAT$(acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum))
   slexebind "insert into tblJournalEntry values(?,?,?,?)",s
  NEXT
 NEXT
 slexe "end"
END SUB

%DropTable = 0

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

MACRO WRAP(p1)   = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0
GLOBAL gName,gAddress AS STRING
GLOBAL gbytes AS LONG

FUNCTION PBMAIN () AS LONG  'fredrick11.bas

  LOCAL sIpAddress AS STRING, portnumber AS LONG

  'sIpAddress = "" 'unremark to test server
  'portnumber =    'unremark to test server

  IF LEN(sIpAddress) THEN
    IF slConnect(sIpAddress,portnumber,"E0") THEN
      ? slGetError,,USING$("&  port #",sIpaddress,portnumber)
      EXIT FUNCTION
    END IF
  END IF

  slOpen "junk1.db3","C"
  IF %DropTable THEN
    slexe  "drop table if exists tblJournalEntry"
  END IF
  slexe  "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"
  InsertData

  gName="Green line":gAddress = "Red line"
  LOCAL rs(),sFrom,sTo,sAccountType AS STRING
  LOCAL cols,rows,tabstops() AS LONG
  LOCAL sb AS ISTRINGBUILDERA
  sb = CLASS "STRINGBUILDERA"

  sFrom = "01-01-2019"
  sTo   = "28-05-2019"
  sAccountType = WRAP("Assets")

  sb.clear
  sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
  sb.add " FROM tblJournalEntry"
  sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom)  + " AND " + SQLiteDate(sTo)
  sb.add " and AccountType ="   + sAccountType       + " GROUP BY AccountName"

  slselary(sb.string,rs(),"Q9cE0")
  IF slGetErrorNumber THEN
    ? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
    EXIT FUNCTION
  END IF
  IF UBOUND(rs) < 1 THEN
  ? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
  EXIT FUNCTION
 END IF

 PrintReport rs(),sFrom,sTo

 IF LEN(sIpAddress) THEN slDisconnect
 ? USING$("Bytes #,",gbytes),,"Done"

END FUNCTION

FUNCTION PrintReport(rsAssets() AS STRING,sFrom AS STRING, sTo AS STRING) 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 szAssets AS ASCIIZ * 300

  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
  AssetsHeight = 1.0

  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_LETTER,_
                       %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
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
  LOCAL s AS STRING
  szAssets = SPACE$(80)
  FOR x = 1 TO UBOUND(rsAssets)
    LSET szAssets =      PARSE$(rsAssets(x),$TAB,1)
    MID$(szAssets,20) =  PARSE$(rsAssets(x),$TAB,2)
    MID$(szAssets,40) = "current height" + STR$(AssetsHeight)
    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, "Courier"
    END IF
  NEXT
  SLEEP 200
  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, "" & 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
RETURN

PrintAssetsDetailLine:
  'dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
 'From dd-MM-yyyy To yyyy-MM-dd
 FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION

SUB InsertData
  LOCAL x         AS LONG
  LOCAL acctnum   AS LONG
  LOCAL numaccts  AS LONG
  LOCAL numrecs   AS LONG
  LOCAL changes   AS LONG
  LOCAL counter   AS LONG
  LOCAL bytes     AS LONG
  gbytes = 0
  numaccts   = 99
  numrecs    = 100
  DIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  changes = slgetchangecount("T")
  FOR acctnum = 1 TO numaccts  'account
    FOR x =1 TO numrecs        'insert this many into account
      INCR counter
      s(counter) = bindt(USING$("Acct #",acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?)",JOIN$(s(),""),"V4"
  slexe "end"

END SUB

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 
   


I don't see the line counter Row& being reset after a new page.
Never mind, I see row = pagesize

The previous code used Gosub when a new page and gosub to detail line, which was nice.
Totally different code.

It may be that the Row is not incremented correctly after each print?
I also saw a $CRLF in the code (don't know what ddoc would do with that?)
 dpTabText iHandle%, Row + 1.6, DetailLine1
 Row = Row + LineSpacing                    'increment line counter
 dpText IHandle%, .5, 3.3, %DDOC_LEFT, "Expenses: "

In your previous code you correctly checked the line counter after each print.

GOSUB PrintAssetsDetailLine
IF AssetsHeight => PageHeightMaximum THEN

Might do that again:

GOSUB PrintDetailLine1
If linecounter => PageHeightMaximum THEN

GOSUB PrintDetailLine2
If linecounter => PageHeightMaximum THEN

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.

Aligning non-fixed length fonts is in the ddoc demo in Fancy text.
The TestP32.Bas demo needs a slight change in WinMain to compile with PbWin10.

BYVAL lpCmdLine    AS ASCIIZ PTR, _

Here is the line that should do what you want:
dpText hFile%, 4.25, 4.15, %DDOC_DECIMAL, "Finally, decimal aligned  $9.99"

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.