financialstmtreport.pas

功能全面的商业财会系统源码,清晰,很有参考价值.扩展性强.

unit FinancialStmtReport;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, DBTables, DB, quickrpt, Qrctrls, qrprntr;

type
  TrptFinancialStmt = class(TForm)
    QuickReport: TQuickRep;
    Title: TQRBand;
    PageHeader: TQRBand;
    Detail: TQRBand;
    PageFooter: TQRBand;
    QRSysData1: TQRSysData;
    PageNumber: TQRSysData;
    QRShape3: TQRShape;
    qryFStmtLin: TQuery;
    QRDateTime: TQRSysData;
    lblPeriod: TQRLabel;
    lblColumn2: TQRLabel;
    lblColumn1: TQRLabel;
    txtLineHeading: TQRDBText;
    qryFStmtLinStmtID: TIntegerField;
    qryFStmtLinLineHeading: TStringField;
    qryFStmtLinLineType: TStringField;
    lblCompanyName: TQRLabel;
    tblFStmtLAc: TTable;
    tblFStmtLAcStmtID: TIntegerField;
    tblFStmtLAcSeq: TIntegerField;
    tblFStmtLAcAccountFrom: TStringField;
    tblFStmtLAcAccountTo: TStringField;
    qryFStmtLinActChgThisMon: TCurrencyField;
    qryFStmtLinActChgLastMon: TCurrencyField;
    qryFStmtLinActChgYTD: TCurrencyField;
    qryFStmtLinActChgYTDLastYr: TCurrencyField;
    qryFStmtLinActBalThisMon: TCurrencyField;
    qryFStmtLinActBalThisMonLastYr: TCurrencyField;
    txtColumn1: TQRDBText;
    txtColumn2: TQRDBText;
    lineColumn1: TQRShape;
    lineColumn2: TQRShape;
    tblGLActual: TTable;
    tblGLActualGLAccount: TStringField;
    tblGLActualGLYear: TSmallintField;
    tblGLActualOpeningBal: TCurrencyField;
    tblGLActualAmount1: TCurrencyField;
    tblGLActualAmount2: TCurrencyField;
    tblGLActualAmount3: TCurrencyField;
    tblGLActualAmount4: TCurrencyField;
    tblGLActualAmount5: TCurrencyField;
    tblGLActualAmount6: TCurrencyField;
    tblGLActualAmount7: TCurrencyField;
    tblGLActualAmount8: TCurrencyField;
    tblGLActualAmount9: TCurrencyField;
    tblGLActualAmount10: TCurrencyField;
    tblGLActualAmount11: TCurrencyField;
    tblGLActualAmount12: TCurrencyField;
    qryFStmtLinSeq: TIntegerField;
    tblFStmtLTo: TTable;
    tblFStmtLToStmtID: TIntegerField;
    tblFStmtLToSeq: TIntegerField;
    tblFStmtLToTotalLineSeq: TIntegerField;
    tblGLBudget: TTable;
    tblGLBudgetGLAccount: TStringField;
    tblGLBudgetGLYear: TSmallintField;
    tblGLBudgetOpeningBal: TCurrencyField;
    tblGLBudgetAmount1: TCurrencyField;
    tblGLBudgetAmount2: TCurrencyField;
    tblGLBudgetAmount3: TCurrencyField;
    tblGLBudgetAmount4: TCurrencyField;
    tblGLBudgetAmount5: TCurrencyField;
    tblGLBudgetAmount6: TCurrencyField;
    tblGLBudgetAmount7: TCurrencyField;
    tblGLBudgetAmount8: TCurrencyField;
    tblGLBudgetAmount9: TCurrencyField;
    tblGLBudgetAmount10: TCurrencyField;
    tblGLBudgetAmount11: TCurrencyField;
    tblGLBudgetAmount12: TCurrencyField;
    txtColumn3: TQRDBText;
    lblColumn3: TQRLabel;
    lineColumn3: TQRShape;
    qryFStmtLinBudChgThisMon: TCurrencyField;
    qryFStmtLinBudChgYTD: TCurrencyField;
    qryFStmtLinBudBalThisMon: TCurrencyField;
    lblColumn4: TQRLabel;
    lblColumn5: TQRLabel;
    lblColumn6: TQRLabel;
    txtColumn4: TQRDBText;
    txtColumn5: TQRDBText;
    txtColumn6: TQRDBText;
    lineColumn4: TQRShape;
    lineColumn5: TQRShape;
    lineColumn6: TQRShape;
    qryFStmtLinActChgThisMonLastYr: TCurrencyField;
    qryFStmtLinVariance1: TCurrencyField;
    qryFStmtLinVariance2: TCurrencyField;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure qryFStmtLinCalcFields(DataSet: TDataSet);
    procedure GetLineAmounts(StmtID, Seq: integer);
    procedure GetAndAddRangeTotals(AccountFrom, AccountTo: string);
    procedure DetailBeforePrint(Sender: TQRCustomBand;
      var PrintBand: Boolean);
    procedure QuickReportPreview(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  rptFinancialStmt: TrptFinancialStmt;

implementation

uses BS1Form, GLReportsForm, PreviewForm;

{$R *.DFM}

var
  ActualAmountsThisYr: array[0..12] of currency;
  ActualAmountsLastYr: array[0..12] of currency;
  BudgetAmountsThisYr: array[0..12] of currency;
  BudgetAmountsLastYr: array[0..12] of currency;
  x: integer;

procedure TrptFinancialStmt.GetLineAmounts(StmtID, Seq: integer);
begin
  if tblFStmtLAc.locate('StmtID;Seq',VarArrayOf([StmtID,Seq]), []) = true then begin   //Get account ranges.
    while (tblFStmtLAcStmtID.value = StmtID) and (tblFStmtLAcSeq.value = Seq) and (not tblFStmtLAc.eof) do begin
      //showmessage(IntToStr(StmtID) + '/' + IntToStr(Seq) + ' ' + tblFStmtLAcStmtID.AsString + '/' + tblFStmtLAcSeq.AsString + ' ' + tblFStmtLAcAccountFrom.AsString);
      GetAndAddRangeTotals(tblFStmtLAcAccountFrom.value, tblFStmtLAcAccountTo.value);
      tblFStmtLAc.next;
    end;
  end;
end;

procedure TrptFinancialStmt.GetAndAddRangeTotals(AccountFrom, AccountTo: string);
begin
  if AccountTo = '' then AccountTo := AccountFrom;

  tblGLActual.SetRange([AccountFrom,(frmGLReports.GLYear - 1)],[AccountTo,frmGLReports.GLYear]);
  tblGLActual.First;
  while not tblGLActual.eof do begin
    if tblGLActualGLYear.value = (frmGLReports.GLYear - 1) then begin
      //Showmessage(AccountFrom + '/' + AccountTo + ' Last yr ' + tblGLActualGLAccount.value + ' ' + tblGLActualGLYear.AsString);
      ActualAmountsLastYr[0] := ActualAmountsLastYr[0] + tblGLActualOpeningBal.AsCurrency;
      ActualAmountsLastYr[1] := ActualAmountsLastYr[1] + tblGLActualAmount1.AsCurrency;
      ActualAmountsLastYr[2] := ActualAmountsLastYr[2] + tblGLActualAmount2.AsCurrency;
      ActualAmountsLastYr[3] := ActualAmountsLastYr[3] + tblGLActualAmount3.AsCurrency;
      ActualAmountsLastYr[4] := ActualAmountsLastYr[4] + tblGLActualAmount4.AsCurrency;
      ActualAmountsLastYr[5] := ActualAmountsLastYr[5] + tblGLActualAmount5.AsCurrency;
      ActualAmountsLastYr[6] := ActualAmountsLastYr[6] + tblGLActualAmount6.AsCurrency;
      ActualAmountsLastYr[7] := ActualAmountsLastYr[7] + tblGLActualAmount7.AsCurrency;
      ActualAmountsLastYr[8] := ActualAmountsLastYr[8] + tblGLActualAmount8.AsCurrency;
      ActualAmountsLastYr[9] := ActualAmountsLastYr[9] + tblGLActualAmount9.AsCurrency;
      ActualAmountsLastYr[10] := ActualAmountsLastYr[10] + tblGLActualAmount10.AsCurrency;
      ActualAmountsLastYr[11] := ActualAmountsLastYr[11] + tblGLActualAmount11.AsCurrency;
      ActualAmountsLastYr[12] := ActualAmountsLastYr[12] + tblGLActualAmount12.AsCurrency;
    end else if tblGLActualGLYear.value = frmGLReports.GLYear then begin
      //Showmessage(AccountFrom + '/' + AccountTo + ' This yr ' + tblGLActualGLAccount.value + ' ' + tblGLActualGLYear.AsString);
      ActualAmountsThisYr[0] := ActualAmountsThisYr[0] + tblGLActualOpeningBal.AsCurrency;
      ActualAmountsThisYr[1] := ActualAmountsThisYr[1] + tblGLActualAmount1.AsCurrency;
      ActualAmountsThisYr[2] := ActualAmountsThisYr[2] + tblGLActualAmount2.AsCurrency;
      ActualAmountsThisYr[3] := ActualAmountsThisYr[3] + tblGLActualAmount3.AsCurrency;
      ActualAmountsThisYr[4] := ActualAmountsThisYr[4] + tblGLActualAmount4.AsCurrency;
      ActualAmountsThisYr[5] := ActualAmountsThisYr[5] + tblGLActualAmount5.AsCurrency;
      ActualAmountsThisYr[6] := ActualAmountsThisYr[6] + tblGLActualAmount6.AsCurrency;
      ActualAmountsThisYr[7] := ActualAmountsThisYr[7] + tblGLActualAmount7.AsCurrency;
      ActualAmountsThisYr[8] := ActualAmountsThisYr[8] + tblGLActualAmount8.AsCurrency;
      ActualAmountsThisYr[9] := ActualAmountsThisYr[9] + tblGLActualAmount9.AsCurrency;
      ActualAmountsThisYr[10] := ActualAmountsThisYr[10] + tblGLActualAmount10.AsCurrency;
      ActualAmountsThisYr[11] := ActualAmountsThisYr[11] + tblGLActualAmount11.AsCurrency;
      ActualAmountsThisYr[12] := ActualAmountsThisYr[12] + tblGLActualAmount12.AsCurrency;
    end;
    tblGLActual.next;
  end;

  if (frmGLReports.tblFStmtColumnFormat.value = 'Net Change: Compare budget') or (frmGLReports.tblFStmtColumnFormat.value = 'Balance: Compare budget') then begin
    tblGLBudget.SetRange([AccountFrom,(frmGLReports.GLYear - 1)],[AccountTo,frmGLReports.GLYear]);
    tblGLBudget.First;
    while not tblGLBudget.eof do begin
      if tblGLBudgetGLYear.value = (frmGLReports.GLYear - 1) then begin
        //Showmessage(AccountFrom + '/' + AccountTo + ' Last yr ' + tblGLBudgetGLAccount.value + ' ' + tblGLBudgetGLYear.AsString);
        BudgetAmountsLastYr[0] := BudgetAmountsLastYr[0] + tblGLBudgetOpeningBal.AsCurrency;
        BudgetAmountsLastYr[1] := BudgetAmountsLastYr[1] + tblGLBudgetAmount1.AsCurrency;
        BudgetAmountsLastYr[2] := BudgetAmountsLastYr[2] + tblGLBudgetAmount2.AsCurrency;
        BudgetAmountsLastYr[3] := BudgetAmountsLastYr[3] + tblGLBudgetAmount3.AsCurrency;
        BudgetAmountsLastYr[4] := BudgetAmountsLastYr[4] + tblGLBudgetAmount4.AsCurrency;
        BudgetAmountsLastYr[5] := BudgetAmountsLastYr[5] + tblGLBudgetAmount5.AsCurrency;
        BudgetAmountsLastYr[6] := BudgetAmountsLastYr[6] + tblGLBudgetAmount6.AsCurrency;
        BudgetAmountsLastYr[7] := BudgetAmountsLastYr[7] + tblGLBudgetAmount7.AsCurrency;
        BudgetAmountsLastYr[8] := BudgetAmountsLastYr[8] + tblGLBudgetAmount8.AsCurrency;
        BudgetAmountsLastYr[9] := BudgetAmountsLastYr[9] + tblGLBudgetAmount9.AsCurrency;
        BudgetAmountsLastYr[10] := BudgetAmountsLastYr[10] + tblGLBudgetAmount10.AsCurrency;
        BudgetAmountsLastYr[11] := BudgetAmountsLastYr[11] + tblGLBudgetAmount11.AsCurrency;
        BudgetAmountsLastYr[12] := BudgetAmountsLastYr[12] + tblGLBudgetAmount12.AsCurrency;