Hello,
I posted a similar question on the PB Forums here :
http://www.powerbasic.com/support/pbforums/showthread.php?t=42032
with no much response so I decided to post it here, maybe with some luck I would get a positive response.
I am writing a report that would enable me generate the Balance C/F and Balance B/F. Generating Balance C/F is not a problem. The probelm is having Balance C/F to become the next day's Balance B/F is my headache. Been wondering, do I need to store Balance C/F in a DB?
My DDOC Codes so far (a little different from the one I posted ealier on the PB Forums)
'Physical Cash Report
Type MY_STRUCTURE
dTotalFirstCollection As Double
dTotalSecondCollection As Double
dTotalCashOutExpenses As Double
dTotalCashOutPurchases As Double
End Type
Global g_struc() As MY_STRUCTURE
Method ReportPhysicalCash(ByVal nCbHndl As Long)
Local dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
Local detailline As Asciiz * 200
Local SNo As Long
Local sDate As String
Local qryCashInFirst As Long
Local qryCashInSecond As Long
Local qryCashOutExpenses&
Local qryCashOutPurchases&
Local dTotalCollection As Double
Local dTotalCashOut As Double
Dim i As Long
Dim dBalance As Double
Local dBalanceCF As Double
dBalance = 0
LineSpacing = .2
TopMargin = .5
PageSize = 8
ihandle% = dpStartDoc(0,"UghimiSoft CashierPro - Physical Cash 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, "L.5W1 L1.W1 L2W1 L3.3W1 L5W1 L6.3W1 L8W2"
Row = PageSize 'force new heading
sDate = VD_GetText(nCbHndl, %ID_FRMPHYSICALCASHRPTDLG_DPKDATE)
'-- First Collection
qryCashInFirst = XDBCREATEQUERY&(gDBhCashIn)
Call XDBQUERYCONDITION(qryCashInFirst, 0, "Date", %EQUAL_TO, sDate, "")
Call XDBQUERYCONDITION(qryCashInFirst, %QUERY_AND, "Collection", %EQUAL_TO, "First", "")
Call xdbQueryCondition(qryCashInFirst, 0, "Amount", %SUM, "", "")
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Creating query condition",%MB_ICONINFORMATION,VD_App.Title
Exit Method
End If
Call XDBQUERYEXECUTE(qryCashInFirst)
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Executing query",%MB_ICONINFORMATION, VD_App.Title
Exit Method
End If
'-- Second Collection
qryCashInSecond = XDBCREATEQUERY&(gDBhCashIn)
Call XDBQUERYCONDITION(qryCashInSecond, 0, "Date", %EQUAL_TO, sDate, "")
Call XDBQUERYCONDITION(qryCashInSecond, %QUERY_AND, "Collection", %EQUAL_TO, "Second", "")
Call xdbQueryCondition(qryCashInSecond, 0, "Amount", %SUM, "", "")
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Creating query condition",%MB_ICONINFORMATION,VD_App.Title
Exit Method
End If
Call XDBQUERYEXECUTE(qryCashInSecond)
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Executing query",%MB_ICONINFORMATION, VD_App.Title
Exit Method
End If
'-- Cash Out Expenses
qryCashOutExpenses& = XDBCREATEQUERY&(gDBhCashOut&)
Call XDBQUERYCONDITION(qryCashOutExpenses&, 0, "Date", %EQUAL_TO, sDate, "")
Call XDBQUERYCONDITION(qryCashOutExpenses&, %QUERY_AND, "ExpType", %EQUAL_TO, "Expenses", "")
Call xdbQueryCondition(qryCashOutExpenses&, 0, "Amount", %SUM, "", "")
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Creating query condition",%MB_ICONINFORMATION,VD_App.Title
Exit Method
End If
Call XDBQUERYEXECUTE(qryCashOutExpenses&)
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Executing query",%MB_ICONINFORMATION, VD_App.Title
Exit Method
End If
'-- Cash Out Purchases
qryCashOutPurchases& = XDBCREATEQUERY&(gDBhCashOut&)
Call XDBQUERYCONDITION(qryCashOutPurchases&, 0, "Date", %EQUAL_TO, sDate, "")
Call XDBQUERYCONDITION(qryCashOutPurchases&, %QUERY_AND, "ExpType", %EQUAL_TO, "Purchases", "")
Call xdbQueryCondition(qryCashOutPurchases&, 0, "Amount", %SUM, "", "")
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Creating query condition",%MB_ICONINFORMATION,VD_App.Title
Exit Method
End If
Call XDBQUERYEXECUTE(qryCashOutPurchases&)
If XDBERROR& Then
MsgBox "Error" & Str$(XDBERROR&) & "Executing query",%MB_ICONINFORMATION, VD_App.Title
Exit Method
End If
For i = 1 To UBound(g_struc())
g_struc(i).dTotalFirstCollection = xdbQuerySUM# (qryCashInFirst, "Amount")
g_struc(i).dTotalSecondCollection = xdbQuerySUM#(qryCashInSecond, "Amount")
g_struc(i).dTotalCashOutExpenses = xdbQuerySUM# (qryCashOutExpenses&, "Amount")
g_struc(i).dTotalCashOutPurchases = xdbQuerySUM# (qryCashOutPurchases&, "Amount")
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.3,.2,2,1
End If
dpText iHandle%, .5,TopMargin, %DDOC_Left,"Page: "+ Str$(PageNumber)+""
dpText iHandle%, 6,TopMargin, %DDOC_Left,"Date: "+ NigeriaDate(Date$)+""
dpText iHandle%, 6,TopMargin +.2, %DDOC_Left,"Time: "+ Time$+""
row =TopMargin + LineSpacing * 4
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
dpText ihandle, 4.25, 1.1, %DDOC_CENTER, XDBFIELDVALUE$(gDBhReportsInformation&, "Name", 0)
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
dpText IHandle%, 4.25, 1.4, %DDOC_CENTER, XDBFIELDVALUE$(gDBhReportsInformation&, "Address", 0)
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
dpText IHandle%, 4.25, 1.7, %DDOC_CENTER, "Physical Cash Report"
'dpText IHandle%, 4.25, 2, %DDOC_CENTER, "Date Range: " & NigeriaDate(DTOS(sFrom)) & " To " & NigeriaDate(DTOS(sTo))
dpText IHandle%, 4.25, 2, %DDOC_CENTER, "Date: " & NigeriaDate(DTOS(sDate))
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD + %DDOC_FONTUNDERLINE, 12, %vbBlack, "Arial"
dpText IHandle%, .5, 2.7, %DDOC_Left, "Cash Collection: "
dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
dBalanceCF = dBalanceCF + dBalance
dpText IHandle%, .5, 3.2, %DDOC_Left, "Opening Balance B/F: " & Format$(dBalanceCF, "#,.00")
dpText IHandle%, .5, 3.5, %DDOC_Left, "First Collection: " & Format$(g_struc(i).dTotalFirstCollection, "#,.00")
dpText IHandle%, .5, 3.8, %DDOC_Left, "Second Collection: " & Format$(g_struc(i).dTotalSecondCollection, "#,.00")
dTotalCollection = dBalance + g_struc(i).dTotalFirstCollection + g_struc(i).dTotalSecondCollection
dpLine IHandle%, .5, 4.1, 3.7, 4.1, 1, %vbBlack
dpText IHandle%, .5, 4.2, %DDOC_Left, "Total Cash At Hand: " & Format$(dTotalCollection, "#,.00")
dpLine IHandle%, .5, 4.5, 3.7, 4.5, 2, %vbBlack
dpText IHandle%, .5, 5.0, %DDOC_Left, "Less: Expenses: " & Format$(g_struc(i).dTotalCashOutExpenses, "#,.00")
dpText IHandle%, .5, 5.3, %DDOC_Left, "Less: Purchases: " & Format$(g_struc(i).dTotalCashOutPurchases, "#,.00")
dTotalCashOut = g_struc(i).dTotalCashOutExpenses + g_struc(i).dTotalCashOutPurchases
dpText IHandle%, .5, 5.6, %DDOC_Left, "Total Cash-Out: " & Format$(dTotalCashOut, "#,.00")
'dBalance = dBalance + g_struc(i).dTotalCashOutExpenses + g_struc(i).dTotalCashOutPurchases
dBalanceCF = dBalance + dTotalCollection - dTotalCashOut
dpText IHandle%, .5, 5.9, %DDOC_Left, "Balance Of Cash C/F: " & Format$(dBalanceCF, "#,.00")
dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
row = row + LineSpacing * 2
End If
' Call XDBMOVENEXT(qryCashInFirst, 0)
dpTabText iHandle%, Row + .9, DetailLine
Row = Row + LineSpacing 'increment line counter
Next i
'Loop
Call XDBDESTROYQUERY(qryCashInFirst)
Call XDBDESTROYQUERY(qryCashInSecond)
Call XDBDESTROYQUERY(qryCashOutExpenses&)
Call XDBDESTROYQUERY(qryCashOutPurchases&)
dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
EndProgram:
End Method
Any suggestions would be appreciated.
Best regards,