Using DDOC to generate Balance C/F and Balance B/F

[Fredrick Ughimi]

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)

Code Select Expand



'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,