Title: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on May 28, 2019, 08:01:48 pm
Post by: Fredrick Ughimi on May 28, 2019, 08:01:48 pm
Hello,
With the following slSelAry statement:
How do I return only AccountName and DebitSUM columns?
Kind regards.
With the following slSelAry statement:
Code Select
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
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:
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.
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:
Code Select
#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
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.
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
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:
>> 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.
>>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:
Code Select
#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
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
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
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.
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
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)
I hard-coded parse$, but that should be modified to accept a variable number of columns.
PrintReport() could be modified to print many different reports.
It could be done in different ways including using a 2-dimensional array.
I like it.
This is what I see (click image)
#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"
MACRO WRAP(p1) = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1) = slBuildBindDat((p1),"T")
%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0
GLOBAL gName,gAddress AS STRING
FUNCTION PBMAIN () AS LONG 'fredrick10.bas
gName="Green line":gAddress = "Red line"
LOCAL rs(),sFrom,sTo,sAccountType AS STRING
LOCAL cols,rows,tabstops() AS LONG
LOCAL sb AS ISTRINGBUILDERA
sb = CLASS "STRINGBUILDERA"
CreateTestData
sFrom = "01-01-2019"
sTo = "28-05-2019"
sAccountType = WRAP("Assets")
sb.clear
sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
sb.add " FROM tblJournalEntry"
sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom) + " AND " + SQLiteDate(sTo)
sb.add " and AccountType =" + sAccountType + " GROUP BY AccountName"
slselary(sb.string,rs(),"Q9cE0")
IF slGetErrorNumber THEN
? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
EXIT FUNCTION
END IF
IF UBOUND(rs) < 1 THEN
? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
EXIT FUNCTION
END IF
PrintReport rs(),sFrom,sTo
END FUNCTION
FUNCTION PrintReport(rsAssets() AS STRING,sFrom AS STRING, sTo AS STRING) AS LONG
LOCAL PageHeightMaximum AS SINGLE
LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
LOCAL lineheight,LeftMargin AS SINGLE 'height deleted
LOCAL AssetsHeight AS SINGLE
LOCAL szAssets AS ASCIIZ * 300
PageHeightMaximum = 10.1 'inches
defaultzoom = %DDOC_ZOOM75
zdefaultFont = "Courier New"
TheStyle = %ddoc_Portrait
fontsize = 8
TheBin = %DDOC_Bin_Auto '7
'topmargin= 1.6 'not in use
lineheight = .1666 '1/6 'each line height
LeftMargin = 0.5
AssetsHeight = 1.0
zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
'DefaultZoom=%DDOC_ZOOM75 + %DDOC_VIEWBUILD
'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD
ihandle = dpStartDoc(0,_
zDocTitle, _
zoutputfile,_
%DDOC_INCH,_
%DDOC_PAPER_LETTER,_
%DDOC_SYSTEM_DEFAULT, _
TheBin,_
DefaultZoom&)
IF ihandle < 1 THEN
SELECT CASE ihandle
CASE %DDOC_NOHANDLES : ? "no file handles",,"dpStartDoc"
CASE %DDOC_BADFILE : ? "bad file name",, "dpStartDoc"
CASE %DDOC_FILEEXISTS
? zoutputfile + " already exists so will delete document",,"dpStartDoc"
KILL zoutputfile
IF ERR THEN ? "Could not delete file",,EXE.NAME$
CASE ELSE: ? "Error code" + STR$(iHandle),,"dpStartDoc"
END SELECT
EXIT FUNCTION
END IF
GOSUB PrintHeading
dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
LOCAL s AS STRING
szAssets = SPACE$(80)
FOR x = 1 TO UBOUND(rsAssets)
LSET szAssets = PARSE$(rsAssets(x),$TAB,1)
MID$(szAssets,20) = PARSE$(rsAssets(x),$TAB,2)
MID$(szAssets,40) = "current height" + STR$(AssetsHeight)
GOSUB PrintAssetsDetailLine
'IF height => PageHeightMaximum THEN
IF AssetsHeight => PageHeightMaximum THEN
INCR pagenum
dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
GOSUB PrintHeading
dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
END IF
NEXT
SLEEP 200
endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
dpEndDoc iHandle, EndCode
EXIT FUNCTION
PrintHeading:
dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & gName
dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & gAddress
dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom & " To " & sTo
dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
dpLine IHandle, .5, 10.8, 8, 10.8, 1, %vbBlack
dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
AssetsHeight = 2.5
dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
AssetsHeight+= lineheight +.05 'looks a bit better
RETURN
PrintAssetsDetailLine:
'dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
AssetsHeight+= lineheight
RETURN
END FUNCTION
FUNCTION SQLiteDate(sDate AS STRING) AS STRING
'From dd-MM-yyyy To yyyy-MM-dd
FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION
SUB CreateTestData
LOCAL acctnum AS LONG
LOCAL counter AS LONG
LOCAL s AS STRING
slOpen "junk1.db3","C"
'slexe "drop table if exists tblJournalEntry"
slexe "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"
slexe "begin exclusive"
FOR counter = 1 TO 1 'insert this many records into acctnum
FOR acctnum = 1 TO 100 'number of accounts
s = bindt("account"+FORMAT$(acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum))
slexebind "insert into tblJournalEntry values(?,?,?,?)",s
NEXT
NEXT
slexe "end"
END SUB
Title: Re: Returning Selected and tested over internet
Post by: cj on May 31, 2019, 05:05:00 pm
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
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:
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:
Code Select
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
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: "
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
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
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
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.
>>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
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"
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
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.
>> %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
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
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.
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
Title: Re: Returning Selected Columns Only In slSelAry
Post by: cj on June 06, 2019, 11:07:36 am
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.
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
Title: Re: Returning Selected Columns Only In slSelAry
Post by: Fredrick Ughimi on June 06, 2019, 12:08:10 pm
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
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
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?
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
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
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.
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
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.
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
Post by: Fredrick Ughimi on June 06, 2019, 05:57:56 pm
>>I think there should only be one line counter.
I think I just followed your footsteps.
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.