unit AP270Report;

interface

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

type
  TqrAP270 = class(TQuickReport)
    QRLabel8: TQRLabel;
    QRLabel9: TQRLabel;
    QRLabel10: TQRLabel;
    QRLabel11: TQRLabel;
    QRDBText3: TQRDBText;
    QRDBText4: TQRDBText;
    QRDBText5: TQRDBText;
    QRDBText6: TQRDBText;
    UpdateSQL1: TUpdateSQL;
    qyReportJobDate: TStringField;
    qyReportJobID: TStringField;
    qyReportIDType: TStringField;
    qyReportAmount: TIntegerField;
    QRGroup1: TQRGroup;
    QRLabel7: TQRLabel;
    QRDBText1: TQRDBText;
    QRDBText2: TQRDBText;
    qyReportSupplierID: TStringField;
    qyReportSupplierAttribName: TStringField;
  private
    { Private declarations }
  public
    { Public declarations }
    procedure PrepareReport; override;
  end;

var
  qrAP270: TqrAP270;

implementation

uses DataModule, Main, PublicFunction;

{$R *.dfm}

{ TqrAP270 }

procedure TqrAP270.PrepareReport;
begin
  inherited;
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT M.SupplierID, M.PurchaseDate, M.PurchaseProperty, ');
    SQL.Add('M.AccountPayable, M.PurchaseID, S.SupplierAttribName ');
    SQL.Add('FROM PurchaseMaster M, Supplier S ');
    SQL.Add('WHERE M.CompanyID = :CompanyID ');
    SQL.Add('AND M.PurchaseDate >= :BeginPurchaseDate AND M.PurchaseDate <= :EndPurchaseDate ');
    SQL.Add('AND M.SupplierID >= :BeginSupplierID AND M.SupplierID <= :EndSupplierID ');
    SQL.Add('AND M.PurchaseProperty IN (''5'', ''6'') ');
    SQL.Add('AND M.CompanyID = S.CompanyID AND M.SupplierID = S.SupplierID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginPurchaseDate').AsString := DM.tbInput.FieldByName('BeginDate').AsString;
    ParamByName('EndPurchaseDate').AsString := DM.tbInput.FieldByName('EndDate').AsString;
    ParamByName('BeginSupplierID').AsString := DM.tbInput.FieldByName('BeginSupplierID').AsString;
    ParamByName('EndSupplierID').AsString := DM.tbInput.FieldByName('EndSupplierID').AsString;
    Open;
  end;
  DM.qyTemp1.First;
  while not DM.qyTemp1.Eof do
  begin
    qyReport.Locate('SupplierID; JobDate',
                     VarArrayOf([DM.qyTemp1.FieldByName('SupplierID').AsString,
                                 DM.qyTemp1.FieldByName('PurchaseDate').AsString]),
                     [loPartialKey]);
    qyReport.Insert;
    qyReport.FieldByName('SupplierID').AsString := DM.qyTemp1.FieldByName('SupplierID').AsString;
    qyReport.FieldByName('SupplierAttribName').AsString :=
                         DM.qyTemp1.FieldByName('SupplierAttribName').AsString;
    qyReport.FieldByName('JobID').AsString := DM.qyTemp1.FieldByName('PurchaseID').AsString;
    qyReport.FieldByName('JobDate').AsString := DM.qyTemp1.FieldByName('PurchaseDate').AsString;
    if DM.qyTemp1.FieldByName('PurchaseProperty').AsString = '5' then
      qyReport.FieldByName('IDType').AsString := '進貨'
    else
      qyReport.FieldByName('IDType').AsString := '進貨退出';
    qyReport.FieldByName('Amount').AsFloat := DM.qyTemp1.FieldByName('AccountPayable').AsFloat;
    qyReport.Post;
    DM.qyTemp1.Next;
  end;
  with DM.qyTemp1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT M.SupplierID, S.SupplierAttribName, ');
    SQL.Add('M.TotalBalance, M.PayDate, M.PaymentID ');
    SQL.Add('FROM AccountPayableMaster M, Supplier S ');
    SQL.Add('WHERE M.CompanyID = :CompanyID ');
    SQL.Add('AND M.PayDate >= :BeginPayDate AND M.PayDate <= :EndPayDate ');
    SQL.Add('AND M.SupplierID >= :BeginSupplierID AND M.SupplierID <= :EndSupplierID ');
    SQL.Add('AND M.CompanyID = S.CompanyID AND M.SupplierID = S.SupplierID ');
    ParamByName('CompanyID').AsString := sCompanyID;
    ParamByName('BeginPayDate').AsString := DM.tbInput.FieldByName('BeginDate').AsString;
    ParamByName('EndPayDate').AsString := DM.tbInput.FieldByName('EndDate').AsString;
    ParamByName('BeginSupplierID').AsString := DM.tbInput.FieldByName('BeginSupplierID').AsString;
    ParamByName('EndSupplierID').AsString := DM.tbInput.FieldByName('EndSupplierID').AsString;
    Open;
  end;
  DM.qyTemp1.First;
  while not DM.qyTemp1.Eof do
  begin
    qyReport.Locate('SupplierID; JobDate',
                     VarArrayOf([DM.qyTemp1.FieldByName('SupplierID').AsString,
                                 DM.qyTemp1.FieldByName('PayDate').AsString]),
                     [loPartialKey]);
    qyReport.Insert;
    qyReport.FieldByName('SupplierID').AsString := DM.qyTemp1.FieldByName('SupplierID').AsString;
    qyReport.FieldByName('SupplierAttribName').AsString :=
                         DM.qyTemp1.FieldByName('SupplierAttribName').AsString;
    qyReport.FieldByName('JobID').AsString := DM.qyTemp1.FieldByName('PaymentID').AsString;
    qyReport.FieldByName('JobDate').AsString := DM.qyTemp1.FieldByName('PayDate').AsString;
    qyReport.FieldByName('IDType').AsString := '付款沖賬';
    qyReport.FieldByName('Amount').AsFloat := DM.qyTemp1.FieldByName('TotalBalance').AsFloat;
    qyReport.Post;
    DM.qyTemp1.Next;
  end;
end;

end.