SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Fredrick Ughimi on May 28, 2019, 08:01:48 pm

Title: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 28, 2019, 08:01:48 pm
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.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 29, 2019, 09:37:40 am
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.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 30, 2019, 06:18:36 am
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.

Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 30, 2019, 03:54:58 pm
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.



Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 30, 2019, 05:05:59 pm
I guess bindt is a function?
Title: Re: Returning Selected Columns Only In slSelAry for ddoc
Post by: cj on May 31, 2019, 05:49:35 am
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
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 31, 2019, 10:16:24 am
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.
Title: Re: Returning Selected Columns Only In slSelAry (ddoc working!)
Post by: cj on May 31, 2019, 01:20:23 pm
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)
fredrick10.png

#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
Title: Re: Returning Selected and tested over internet
Post by: cj on May 31, 2019, 05:05:00 pm
%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
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 31, 2019, 08:36:46 pm
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 
   
Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on May 31, 2019, 08:50:58 pm
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: "
Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on May 31, 2019, 09:18:37 pm
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

Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 31, 2019, 09:48:11 pm
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.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on June 03, 2019, 04:33:24 pm
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"
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 04, 2019, 05:49:20 am
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.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 05, 2019, 06:02:40 pm
Never mind. I think I know what to do.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 08:39:33 am
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

Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on June 06, 2019, 11:07:36 am
I think there should only be one line counter.
Lines will print over themselves in this demo.
#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"
%DropTable = 1

MACRO WRAP(p1)  = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
GLOBAL gName,gAddress AS STRING
GLOBAL gbytes AS LONG

%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 "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, Debit INTEGER)"
 InsertData
 gName="Green line":gAddress = "Red line"
 '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

SUB InsertData
  LOCAL x        AS LONG
  LOCAL acctnum  AS LONG
  LOCAL numaccts  AS LONG
  LOCAL numrecs  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"
  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)) + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?,?)",JOIN$(s(),""),"V5"
  slexe "end"

  counter = 0
  REDIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  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("Revenue") + bindt("2019-05-28") + bindi(STR$(acctnum)) + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?,?)",JOIN$(s(),""),"V5"
  slexe "end"

END SUB
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 12:08:10 pm
I would take a look at it later today. Thanks.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on June 06, 2019, 01:07:59 pm
Instead of listing debits and then credits, how about different reports just listing the table?

DATE/CLIENT REPORT
DATE     CLIENT     TYPE           DEBIT       CREDIT    PAGE 1
------   ------     -------        ------      ------
6/6/19   1          Car            1000        1000   
                    Car            3000          50   
                    Car             400          20

6/7/19   2          Horse          1000        1000   
                    Horse          3000          50   
                    Horse           400          20




CLIENT/DATE REPORT
CLIENT   DATE       TYPE           DEBIT       CREDIT    PAGE 1
------   ------     -------        ------      ------
1        6/6/19     Car            1000        1000   
                    Car            3000          50   
                    Car             400          20

2        6/7/19     Horse          1000        1000   
                    Horse          3000          50   
                    Horse           400          20
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 04:56:34 pm
>>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?
Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on June 06, 2019, 05:40:31 pm
Yes, sorry.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 05:42:28 pm
>>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.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 05:51:36 pm
>>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.
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 05:57:56 pm
>>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.