ar_qry_saleinvoice.pas

一个MRPII系统源代码版本

unit Ar_Qry_SaleInvoice;

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_SaleInvoice = Class(TFrm_Base_Qry)
    AdoQry_MaincustomerCode: TStringField;
    AdoQry_MaincustomerName: TStringField;
    AdoQry_Maincustomerflag: TStringField;
    AdoQry_Maincreatedate: TDateTimeField;
    AdoQry_MainInvoiceType: TIntegerField;
    AdoQry_MainInvoiceno: TStringField;
    AdoQry_MainTotaltaxAmount: TFloatField;
    AdoQry_MainCancelAmount: TFloatField;
    AdoQry_MainSaleTypeCode: TStringField;
    AdoQry_MainSaleTypeName: TStringField;
    AdoQry_MainSaleTypeflag: TStringField;
    AdoQry_MainSaleDeptCode: TStringField;
    AdoQry_MainDeptName: TStringField;
    AdoQry_MainDeptflag: TStringField;
    AdoQry_MainSaleEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainSaleEmployeeflag: TStringField;
    AdoQry_MaincurrencyCode: TStringField;
    AdoQry_MaincurrencyName: TStringField;
    AdoQry_Maincurrencyflag: TStringField;
    AdoQry_MainExchangeRate: TFloatField;
    AdoQry_Mainbank: TStringField;
    AdoQry_Mainbankactno: TStringField;
    AdoQry_MaincreateEmployeeCode: TStringField;
    AdoQry_MaincreateEmployeeName: TStringField;
    AdoQry_MaincreateEmployeeflag: TStringField;
    AdoQry_MainremArk: TStringField;
    AdoQry_MainShipno: TStringField;
    AdoQry_MainShiplineno: TIntegerField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainShipqty: TFloatField;
    AdoQry_MaintaxPrice: TFloatField;
    AdoQry_MaintaxAmount: TFloatField;
    AdoQry_MaindiSCountrate: TFloatField;
    AdoQry_MaindiSCountAmount: 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_SaleInvoice: TFrm_Ar_Qry_SaleInvoice;

implementation
 uses Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_SaleInvoice.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
begin
  Application.ProcessMessages;
  inherited;
  sqltext:='select t1.customerCode,   '
           +'        customer.customerName,        '
           +'        t1.customerCode+'' ''+customer.customerName as customerflag, '
           +'        t1.Createdate,                  '
           +'        t1.InvoiceType,                 '
           +'        t1.Invoiceno,                  '
           +'        t1.TotaltaxAmount,             '
           +'        t1.CancelAmount,               '
           +'        t1.SaleTypeCode,               '
           +'        SaleType.SaleTypeName,         '
           +'        t1.SaleTypeCode+'' ''+SaleType.SaleTypeName as SaleTypeflag, '
           +'        t1.SaleDeptCode,                                         '
           +'        Dept.DeptName,                                          '
           +'        t1.SaleDeptCode+'' ''+Dept.DeptName as Deptflag,          '
           +'        t1.SaleEmployeeCode,                                     '
           +'        Employee1.EmployeeName,                                  '
           +'        t1.SaleEmployeeCode+'' ''+Employee1.EmployeeName as SaleEmployeeflag,  '
           +'        t1.currencyCode,            '
           +'        currency.currencyName,                                     '
           +'        t1.currencyCode+'' ''+currency.currencyName as currencyflag, '
           +'        t1.ExchangeRate,                                 '
           +'        t1.bank,                                         '
           +'        t1.bankactno,                                    '
//           +'        t1.assess,                                       '
//           +'        t1.assessorEmployeeCode,                         '
//           +'        Employee2.EmployeeName  as assessEmployeeName,   '
//           +'        t1.assessorEmployeeCode+'' ''+Employee2.EmployeeName as assessEmployeeflag,  '
//           +'        t1.assessdate,                                   '
           +'        t1.CreateEmployeeCode,                           '
           +'        Employee3.EmployeeName as createEmployeeName,    '
           +'        t1.CreateEmployeeCode+'' ''+Employee3.EmployeeName as createEmployeeflag, '
           +'        t1.remArk,                                      '
           +'        t4.InvBillNo as Shipno,                                      '
           +'        Convert(int,t3.InvBillLineNo) as Shiplineno,                                  '
           +'        t3.ItemCode,                                    '
           +'        Item.ItemName,                                  '
           +'        t3.ItemCode+'' ''+Item.ItemName as Itemflag,      '
           +'        Uom.UomName,                                    '
           +'        t3.InvBillQty as Shipqty,                                     '
           +'        t3.InvBillPrice as taxPrice,                                    '
           +'        t3.InvBillAmount as taxAmount,                                   '
           +'        t3.diSCountrate,                                '
           +'        t3.diSCountAmount                               '
           +' into #tMpSaleInvoiceQry '
           +' from sa_SaleInvoiceline t2                             '
           +' join sa_SaleInvoice t1 on t2.Invoiceno=t1.Invoiceno    '
           +' join InvOutBillLine t3 on t2.InvBillId=t3.InvBillId '
           +' join InvOutBill t4 on t3.InvBillId=t4.InvBillId '
           +'   and convert(int,t2.InvBillLineNo)=convert(int,t3.InvBillLineNo) '
           +' left join Employee Employee1 on t1.SaleEmployeeCode=Employee1.EmployeeCode    '
//           +' left join Employee Employee2 on t1.assessorEmployeeCode=Employee2.EmployeeCode '
           +' left join Employee Employee3 on t1.CreateEmployeeCode=Employee3.EmployeeCode '
           +' left join SaleType on t1.SaleTypeCode=SaleType.SaleTypeCode    '
           +' left join customer on t1.customerCode=customer.customerCode   '
           +' left join currency on t1.currencyCode=currency.currencyCode   '
           +' left join Dept on t1.SaleDeptCode=Dept.DeptCode  '
           +' left join Item on t3.ItemCode=Item.ItemCode     '
           +' left join Uom on Item.UomCode=Uom.UomCode     ';

  selectfromsql:='select * from #tMpSaleInvoiceQry';
  condition:= 'createdate>=  '+quotedstr(formatdatetime('yyyy.mm.dd',strtodatetime(GetServerDateTime(dbconnect))-30));
  Orderbyfields:='customerCode,createdate,Invoiceno';
  Executesql(AdoQry_Main,sqltext,1);
  Executesql(AdoQry_Main,'select * from #tMpSaleInvoiceQry',0);
  lbl_Condition.Caption:='开票日期>='+formatdatetime('yyyy.mm.dd',strtodatetime(GetServerDateTime(dbconnect))-30);
end;

procedure TFrm_Ar_Qry_SaleInvoice.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ar_Qry_SaleInvoice:=nil;
end;

procedure TFrm_Ar_Qry_SaleInvoice.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  try
    Executesql(AdoQry_tmp,'drop table #tMpSaleInvoiceQry',1);
  except
  end;
end;

end.