ar_qry_arsheet.pas

一个MRPII系统源代码版本

unit Ar_Qry_ArSheet;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, jpeg;

Type
  TFrm_Ar_Qry_ArSheet = Class(TFrm_Base_Qry)
    AdoQry_MainCustomerCode: TStringField;
    AdoQry_MainCustomerName: TStringField;
    AdoQry_MainCustomerFlag: TStringField;
    AdoQry_MainCurrencyCode: TStringField;
    AdoQry_MainCurrencyName: TStringField;
    AdoQry_MainCurrencyFlag: TStringField;
    AdoQry_MainNoInvoiceAmount: TFloatField;
    AdoQry_MainInvoiceAmount: TFloatField;
    AdoQry_MainTotal: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Ar_Qry_ArSheet: TFrm_Ar_Qry_ArSheet;

implementation
 uses Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_ArSheet.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
begin
  Application.ProcessMessages;
  inherited;                                                                                   
  SqlText :=' select CustomerCode,CustomerName,CustomerFlag,                             '
                 +'        CurrencyCode,CurrencyName,CurrencyFlag,                             '
                 +'        Sum(NoInvoiceAmount) as NoInvoiceAmount,                            '
                 +'        Sum(InvoiceAmount) as InvoiceAmount,                                '
                 +'        Sum(NoInvoiceAmount)+Sum(InvoiceAmount) as Total                    '
                 +' into #TmpArSheet '
                 +' from                                                                       '
                 +' (select Customer.CustomerCode,                                             '
                 +'        Customer.CustomerName,                                              '
                 +'        Customer.CustomerCode+'' ''+Customer.CustomerName as CustomerFlag,    '
                 +'        InvOutBill.CurrencyCode,                                            '
                 +'        Currency.CurrencyName,                                              '
                 +'        InvOutBill.CurrencyCode+'' ''+Currency.CurrencyName as CurrencyFlag,  '
                 +'        Sum(IsNull(InvOutBill.TotalInvBillAmount,0)) as NoInvoiceAmount,    '
                 +'        0.0 as InvoiceAmount                                                '
                 +' from Customer                                                              '
                 +'  join InvOutBill on Customer.CustomerCode=InvOutBill.CustomerCode          '
                 +'        and InvOutBill.InvBillWhChCk=1                                      '
                 +'        and IsNull(InvOutBill.InvoiceNo,'''')=''''                              '
                 +' left join Currency on InvOutBill.CurrencyCode=Currency.CurrencyCode        '
                 +' group by Customer.CustomerCode,Customer.CustomerName,                      '
                 +'          InvOutBill.CurrencyCode,Currency.CurrencyName                     '
                 +' union                                                                      '
                 +' select Customer.CustomerCode,                                              '
                 +'        Customer.CustomerName,                                              '
                 +'        Customer.CustomerCode+'' ''+Customer.CustomerName as CustomerFlag,    '
                 +'        Sa_SaleInvoice.CurrencyCode,                                        '
                 +'        Currency.CurrencyName,                                              '
                 +'        Sa_SaleInvoice.CurrencyCode+'' ''+Currency.CurrencyName as CurrencyFlag, '
                 +'        0.0 as NoInvoiceAmount,                                             '
                 +'        Sum(IsNull(Sa_SaleInvoice.TotalTaxAmount,0)-IsNull(Sa_SaleInvoice.CancelAmount,0)) as InvoiceAmount '
                 +'                                                                            '
                 +' from Customer                                                              '
                 +'  join Sa_SaleInvoice on Customer.CustomerCode=Sa_SaleInvoice.CustomerCode  '
                 +' left join Currency on Sa_SaleInvoice.CurrencyCode=Currency.CurrencyCode    '
                 +' group by Customer.CustomerCode,Customer.CustomerName,                      '
                 +'          Sa_SaleInvoice.CurrencyCode,Currency.CurrencyName                 '
                 +' ) tmp                                                                      '
                 +' group by                                                                   '
                 +' CustomerCode,CustomerName,CustomerFlag,                                    '
                 +'        CurrencyCode,CurrencyName,CurrencyFlag                              ';
  ExecuteSQl(AdoQry_Tmp,sqlText,1)                 ;
  SelectFromSql := 'select * from #TmpArSheet';
  Orderbyfields:='customerCode';                                     
  GetData;
//  lbl_Condition.Caption:='开票日期>='+formatdatetime('yyyy.mm.dd',strtodatetime(GetServerDateTime(dbconnect))-30);
end;

procedure TFrm_Ar_Qry_ArSheet.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ar_Qry_ArSheet := nil;
end;

procedure TFrm_Ar_Qry_ArSheet.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Try
    ExecuteSql(AdoQry_Tmp,'drop table #TmpArSheet',1);
  except
  end;
end;

end.