• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Returning Selected Columns Only In slSelAry

Started by Fredrick Ughimi, May 28, 2019, 08:01:48 PM

Previous topic - Next topic
Print
Go Down Pages 1 2
User actions

Fredrick Ughimi

#15
June 05, 2019, 06:02:40 PM Last Edit: June 05, 2019, 07:25:31 PM by Fredrick Ughimi
Never mind. I think I know what to do.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

#16
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.

Code Select
#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
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

#17
June 06, 2019, 11:07:36 AM Last Edit: June 06, 2019, 11:10:00 AM by cj
I think there should only be one line counter.
Lines will print over themselves in this demo.
Code Select
#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

Fredrick Ughimi

#18
June 06, 2019, 12:08:10 PM
I would take a look at it later today. Thanks.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

#19
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

Fredrick Ughimi

#20
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?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

#21
June 06, 2019, 05:40:31 PM
Yes, sorry.

Fredrick Ughimi

#22
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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

#23
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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

#24
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.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet
Print
Go Up Pages 1 2
User actions