• Welcome, Guest. Please login.
 
July 16, 2019, 11:46:43 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 2 3 4 5 ... 10
21
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.
22
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

23
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: "
24
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 
   
25
%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
26
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
27
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.
28
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
29
I guess bindt is a function?
30
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.



Pages 1 2 3 4 5 ... 10