unit AR230Report;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, BaseReport, DB, DBTables, QRCtrls, QuickRpt, ExtCtrls;

type
  TqrAR230 = class(TQuickReport)
    qyReportCustomerID: TStringField;
    qyReportCustomerAttribName: TStringField;
    UpdateSQL1: TUpdateSQL;
    qyReportLastAR: TIntegerField;
    qyReportPeriodAR: TIntegerField;
    qyReportReceived: TIntegerField;
    qyReportTotalAR: TIntegerField;
    QRLabel6: TQRLabel;
    QRLabel7: TQRLabel;
    QRLabel8: TQRLabel;
    QRLabel9: TQRLabel;
    QRLabel10: TQRLabel;
    QRLabel11: TQRLabel;
    QRDBText1: TQRDBText;
    QRDBText2: TQRDBText;
    QRDBText3: TQRDBText;
    QRDBText4: TQRDBText;
    QRDBText5: TQRDBText;
    QRDBText6: TQRDBText;
    procedure qyReportBeforeOpen(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure PrepareReport; override;
  end;

var
  qrAR230: TqrAR230;

implementation

uses DataModule, Main;

{$R *.dfm}

procedure TqrAR230.PrepareReport;
begin
  inherited;
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CustomerID, BeginningBalance ');
    SQL.Add('FROM AccountReceivableBeginning ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND CustomerID >= :BeginCustomerID AND CustomerID <= :EndCustomerID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginCustomerID').AsString := DM.tbInput.FieldByName('BeginCustomerID').AsString;
    ParamByName('EndCustomerID').AsString := DM.tbInput.FieldByName('EndCustomerID').AsString;
    Open;
  end;
  DM.qyTemp1.First;
  while not DM.qyTemp1.Eof do
  begin
    if qyReport.Locate('CustomerID', DM.qyTemp1.FieldByName('CustomerID').AsString, []) then
    begin
      qyReport.Edit;
      qyReport.FieldByName('LastAR').AsFloat := DM.qyTemp1.FieldByName('BeginningBalance').AsFloat;
      qyReport.Post;
    end;
    DM.qyTemp1.Next;
  end;
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CustomerID, AccountReceivable ');
    SQL.Add('FROM DeliveryMaster ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND CustomerID >= :BeginCustomerID AND CustomerID <= :EndCustomerID ');
    SQL.Add('AND DeliveryDate >= :BeginDeliveryDate AND DeliveryDate <= :EndDeliveryDate ');
    SQL.Add('AND DeliveryProperty IN (''1'', ''2'') ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginCustomerID').AsString := DM.tbInput.FieldByName('BeginCustomerID').AsString;
    ParamByName('EndCustomerID').AsString := DM.tbInput.FieldByName('EndCustomerID').AsString;
    ParamByName('BeginDeliveryDate').AsString :=
                DM.tbInput.FieldByName('BeginYear').AsString + '-' +
                DM.tbInput.FieldByName('BeginMonth').AsString + '-01';
    ParamByName('EndDeliveryDate').AsString :=
                DM.tbInput.FieldByName('BeginYear').AsString + '-' +
                DM.tbInput.FieldByName('BeginMonth').AsString + '-31';
    Open;
  end;
  DM.qyTemp1.First;
  while not DM.qyTemp1.Eof do
  begin
    if qyReport.Locate('CustomerID', DM.qyTemp1.FieldByName('CustomerID').AsString, []) then
    begin
      qyReport.Edit;
      qyReport.FieldByName('PeriodAR').AsFloat :=
               qyReport.FieldByName('PeriodAR').AsFloat +
               DM.qyTemp1.FieldByName('AccountReceivable').AsFloat;
      qyReport.Post;
    end;
    DM.qyTemp1.Next;
  end;
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT CustomerID, TotalBalance ');
    SQL.Add('FROM AccountReceivableMaster ');
    SQL.Add('WHERE CompanyID = :CompanyID ');
    SQL.Add('AND CustomerID >= :BeginCustomerID AND CustomerID <= :EndCustomerID ');
    SQL.Add('AND ReceiveDate >= :BeginReceiveDate AND ReceiveDate <= :EndReceiveDate ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginCustomerID').AsString := DM.tbInput.FieldByName('BeginCustomerID').AsString;
    ParamByName('EndCustomerID').AsString := DM.tbInput.FieldByName('EndCustomerID').AsString;
    ParamByName('BeginReceiveDate').AsString :=
                DM.tbInput.FieldByName('BeginYear').AsString + '-' +
                DM.tbInput.FieldByName('BeginMonth').AsString + '-01';
    ParamByName('EndReceiveDate').AsString :=
                DM.tbInput.FieldByName('BeginYear').AsString + '-' +
                DM.tbInput.FieldByName('BeginMonth').AsString + '-31';
    Open;
  end;
  DM.qyTemp1.First;
  while not DM.qyTemp1.Eof do
  begin
    if qyReport.Locate('CustomerID', DM.qyTemp1.FieldByName('CustomerID').AsString, []) then
    begin
      qyReport.Edit;
      qyReport.FieldByName('Received').AsFloat :=
               qyReport.FieldByName('Received').AsFloat +
               DM.qyTemp1.FieldByName('TotalBalance').AsFloat;
      qyReport.Post;
    end;
    DM.qyTemp1.Next;
  end;
  qyReport.First;
  while not qyReport.Eof do
  begin
    qyReport.Edit;
    qyReport.FieldByName('TotalAR').AsFloat :=
             qyReport.FieldByName('LastAR').AsFloat +
             qyReport.FieldByName('PeriodAR').AsFloat -
             qyReport.FieldByName('Received').AsFloat;
    qyReport.Post;
    if (qyReport.FieldByName('TotalAR').AsFloat = 0) and
       (qyReport.FieldByName('LastAR').AsFloat = 0) and
       (qyReport.FieldByName('PeriodAR').AsFloat = 0) and
       (qyReport.FieldByName('Received').AsFloat = 0) then
    begin
      qyReport.Delete;
      Continue;
    end;
    qyReport.Next;
  end;
end;

procedure TqrAR230.qyReportBeforeOpen(DataSet: TDataSet);
begin
  inherited;
  with qyReport do
  begin
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginCustomerID').AsString := DM.tbInput.FieldByName('BeginCustomerID').AsString;
    ParamByName('EndCustomerID').AsString := DM.tbInput.FieldByName('EndCustomerID').AsString;
  end;
end;

end.