ap_qry_purchaseinvoicedetai.pas

一个MRPII系统源代码版本

unit Ap_Qry_PurchaseInvoiceDetai;

Interface

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

Type
  TFrm_Ap_Qry_PurchaseInvoiceDetail = Class(TFrm_Base_Qry)
    Panel2: TPanel;
    Label6: TLabel;
    DBGridEh2: TDBGridEh;
    Splitter1: TSplitter;
    Panel1: TPanel;
    Label2: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    AdoQry_Detail: TAdoQuery;
    DataSource1: TDataSource;
    AdoQry_MainApInvOICEID: TFloatField;
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MaInvendorB: TStringField;
    AdoQry_MainCurrencyCode: TStringField;
    AdoQry_MainCurrencyCodeName: TStringField;
    AdoQry_MainEmployeeCode: TStringField;
    AdoQry_MainEmployeeCodeName: TStringField;
    AdoQry_MainApInvoiceNo: TStringField;
    AdoQry_MainApInvoiceDate: TDateTimeField;
    AdoQry_MainApInvoiceInputDate: TDateTimeField;
    AdoQry_MainApInvoiceAmount: TFloatField;
    AdoQry_MainApInvoicetax: TFloatField;
    AdoQry_MainTotalAmount: TFloatField;
    AdoQry_MainApInvoiceRemArk: TStringField;
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;

var
  Frm_Ap_Qry_PurchaseInvoiceDetail: TFrm_Ap_Qry_PurchaseInvoiceDetail;

implementation

{$R *.DFM}

{ TFrm_Ap_Qry_PurchaseInvoiceDetail }

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
begin
  inherited;
  AdoQry_Detail.Connection :=AdoQry_Main.Connection ;
  SelectFromSql:='select ApInvoice.ApInvOICEID,'+
           ' ApInvoice.VendorCode,'+
           ' ApInvoice.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
           ' ApInvoice.ApInvoiceNo,'+
           ' ApInvoice.CurrencyCode,'+
           ' ApInvoice.CurrencyCode+'' ''+Currency.CurrencyName as CurrencyCodeName,'+
           ' ApInvoice.EmployeeCode,'+
           ' ApInvoice.EmployeeCode+'' ''+Employee.EmployeeName as EmployeeCodeName ,'+
           ' ApInvoice.ApInvoiceDate,'+
           ' ApInvoice.ApInvoiceInputDate,'+
           ' ApInvoice.ApInvoiceAmount,'+
           ' ApInvoice.ApInvoicetax,'+
           ' ApInvoice.ApInvoiceAmount-(ApInvoice.ApInvoicetax) as TotalAmount,'+
           ' ApInvoice.ApInvoiceRemArk'+
           ' from ApInvoice'+
           ' join Vendor on ApInvoice.VendorCode =Vendor.VendorCode'+
           ' join Employee on ApInvoice.EmployeeCode=Employee.EmployeeCode'+
           ' join currency on ApInvoice.CurrencyCode=Currency.CurrencyCode';
  OrderByFields:='VendorCode,ApInvoiceDate ,ApInvoiceNo';
  PriceFields:='ApInvoiceAmount,ApInvoicetax,TotalAmount,';
  getdata;
end;

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.AdoQueryAfterOpen(
  DataSet: TDataSet);
var
  ApInvoiceAmount,ApInvoicetax,TotalAmount:double;
begin
  inherited;
  ApInvoiceAmount:=0;
  ApInvoicetax:=0;
  TotalAmount:=0;
  with AdoQry_Main do
  begin
    First;
    while not eof do
    begin
      ApInvoiceAmount:=ApInvoiceAmount+fieldbyname('ApInvoiceAmount').asfloat;
      ApInvoicetax:=ApInvoicetax+fieldbyname('ApInvoicetax').asfloat;
      TotalAmount:=TotalAmount+fieldbyname('TotalAmount').asfloat;
      next;
    end;
  end;
  Label2.Caption:='发票金额合计：'+floattostr(ApInvoiceAmount);
  Label4.Caption:='税额合计：'+floattostr(ApInvoicetax);
  Label5.Caption:='未税金额合计：'+floattostr(TotalAmount);
end;

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.DataSourceDataChange(
  Sender: TObject; Field: TField);
var
  InvInBill:string;
  InvInBillTotal:string;
  NoTaxAmount:double;
begin
  inherited;
  if AdoQry_Main.RecordCount=0 then
  begin
    //Pnl_Hint.Caption:='提示：查到发票数：'+ inttostr(AdoQry_Main.RecordCount);
    if AdoQry_tmp.Active then AdoQry_tmp.Close;
    if AdoQry_Detail.Active then AdoQry_Detail.Close;
    Application.MessageBox('没有符合条件的记录！','提示',MB_OK+MB_IconInfoRMATION);
    abort;
  end;
  InvInBill:='select InvInBillline.InvBillId,InvInBill.WhCode+'' ''+Warehouse.WhName as zhaiyao,InvInBill.InvBillno,'+
                   ' sum( InvInBillLine.InvBillNotaxAmountC) as NoTaxAmount,'+
                   ' sum( InvInBillLine.InvBillAmountC) as Amount'+                   
             ' from InvInBillLine '+
             ' join InvInBill on InvInBillline.InvBillId=InvInBill.InvBillId'+
             ' join ApInvoiceline on InvInBill.InvBillid=ApInvoiceline.InvBillid'+
             ' join ApInvoice on ApInvoiceline.ApInvoiceId=ApInvoice.ApInvoiceid'+
             ' join Warehouse on InvInBill.whCode=Warehouse.whCode'+
             ' where InvInBillLine.InvBillid=InvInBill.InvBillid and'+
             ' InvInBill.InvBillid=InvInBill.InvBillid and'+
             ' InvInBill.InvBillid=ApInvoiceline.InvBillid and'+
             ' ApInvoiceline.ApInvoiceid= '+  AdoQry_Main.fieldbyname('ApInvOICEID').asstring +
             ' group by InvInBillline.InvBillId,InvInBill.InvBillno,InvInBill.WhCode,Warehouse.WhName ';

  AdoQry_Detail.Close;
  AdoQry_Detail.SQL.clear;
  AdoQry_Detail.SQL.Add(InvInBill);
  AdoQry_Detail.Open;
  //InvInBillTotal:='select sum(a.NoTaxAmount) as NoTaxAmount from '+'('+ InvInBill+') a';
  {AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(InvInBillTotal);
  AdoQry_tmp.Open;
  Label6.Caption:='未税金额合计：'+AdoQry_tmp.fieldbyname('NoTaxAmount').asstring;}
  NoTaxAmount:=0;
  with AdoQry_Detail do
  begin
    First;
    while not eof do
    begin
      NoTaxAmount:=NoTaxAmount+fieldbyname('NoTaxAmount').asfloat;
      next;
    end;
  end;
  Label6.Caption:='未税金额合计：'+floattostr(NoTaxAmount);;
end;
procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.FormCreate(Sender: TObject);
begin
  inherited;
  Pnl_Title.Caption:='采购发票明细表';
  Lbl_Condition.Caption :='全部';
  Lbl_Order.Caption :='供应商代码/发票日期/发票号';
end;

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Qry_PurchaseInvoiceDetail:=nil;
end;

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.Act_PreviewExecute(
  Sender: TObject);
begin
  If AdoQry_Main.IsEmpty then Exit;
  inherited;

end;

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.Act_PrintExecute(
  Sender: TObject);
begin
  If AdoQry_Main.IsEmpty then Exit;
  inherited;

end;

procedure TFrm_Ap_Qry_PurchaseInvoiceDetail.Act_ExcelExecute(
  Sender: TObject);
begin
  If AdoQry_Main.IsEmpty then Exit;
  inherited;

end;

end.