ap_qry_vendorinvoice.pas

一个MRPII系统源代码版本

unit Ap_Qry_VendorInvoice;
//
Interface

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

Type
  TFrm_Ap_Qry_VendorInvoice = Class(TFrm_Base_Panel)
    Label3: TLabel;
    DBGridEh: TDBGridEh;
    DBGridEh2: TDBGridEh;
    AdoQry_Main: TAdoQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    Lbl_begindate: TLabel;
    Lbl_Enddate: TLabel;
    Splitter1: TSplitter;
    AdoQry_Detail: TAdoQuery;
    Label1: TLabel;
    Lbl_begindate1: TLabel;
    Lbl_Enddate1: TLabel;
    Panel1: TPanel;
    Label2: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Panel2: TPanel;
    Label6: TLabel;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ExtPrintReport: TExtPrintReport;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_FilterExecute(Sender: TObject);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure DBGridEh2DblClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
  private
    { Private declarations }
    procedure SetReport;//报表打印参数设置
    procedure GetTransValue(FieldName,OldValue:String;var NewValue:String);//打印转换过程
  public
    { Public declarations }
    VendorCode,begindate,enddate,begindate1,enddate1:string;
    procedure SetDBConnect(AdOConnection: TAdOConnection);Override;
  protected
    procedure SetColumnsStyle(ItemIndex:Integer;FieldName:String); virtual;
    procedure InitReport; virtual;//设置报表表头属性
  end;

var
  Frm_Ap_Qry_VendorInvoice: TFrm_Ap_Qry_VendorInvoice;

implementation
uses Ap_Qry_VendorInvoice_Condition,Ap_Qry_InvInBillLine;
{$R *.DFM}

procedure TFrm_Ap_Qry_VendorInvoice.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Qry_VendorInvoice:=nil;
end;

procedure TFrm_Ap_Qry_VendorInvoice.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Action:=Cafree;
end;

procedure TFrm_Ap_Qry_VendorInvoice.SetDBConnect(
  AdOConnection: TAdOConnection);
var
  Invoice:string;
  InvoiceTotal:string;
  FirstVendor:string;
begin
  inherited;
  PriceFields:='ApInvoiceAmount,ApInvoicetax,TotalAmount,';

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.clear;
  AdoQry_Tmp.SQL.Add('select top 1 VendorCode  from ApInvoice');
  AdoQry_Tmp.Prepared;
  AdoQry_tmp.Open;
  FirstVendor:=  AdoQry_Tmp.fieldbyname('VendorCode').asstring;

  AdoQry_Main.Connection:=Dbconnect;
  AdoQry_Detail.Connection:=Dbconnect;
  AdoQry_Tmp.Connection:=Dbconnect;
  Lbl_begindate.Caption:=Formatdatetime('yyyy.mm.dd',date-30);
  Lbl_Enddate.Caption:=Formatdatetime('yyyy.mm.dd',date);
  Lbl_begindate1.Caption:=Formatdatetime('yyyy.mm.dd',date-30);
  Lbl_Enddate1.Caption:=Formatdatetime('yyyy.mm.dd',date);


  Invoice:='select ApInvoice.ApInvOICEID,'+
           ' ApInvoice.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
           ' ApInvoice.ApInvoiceNo,'+
           ' 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'+
           ' where ApInvoice.VendorCode='+quotedstr(FirstVendor)+
           ' and ApInvoice.ApInvoiceType<>2 and'+
         ' ApInvoice.ApInvoiceDate>= GetDate()-30 and  ApInvoice.ApInvoiceDate<= GetDate()';
  AdoQry_Main.Close;
  AdoQry_Main.SQL.clear;
  AdoQry_Main.SQL.Add(Invoice);
  AdoQry_Main.Open;

  InvoiceTotal:='select sum(a.ApInvoiceAmount) as ApInvoiceAmount, '+
                ' sum(a.ApInvoicetax) as ApInvoicetax, '+
                ' sum(a.TotalAmount) as TotalAmount from '+'('+ Invoice+') a';
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add(InvoiceTotal);
  AdoQry_tmp.Open;
  Label2.Caption:='发票金额合计：'+AdoQry_tmp.fieldbyname('ApInvoiceAmount').asstring;
  Label4.Caption:='税额合计：'+AdoQry_tmp.fieldbyname('ApInvoicetax').asstring;
  Label5.Caption:='未税金额合计：'+AdoQry_tmp.fieldbyname('TotalAmount').asstring;
end;

procedure TFrm_Ap_Qry_VendorInvoice.Act_FilterExecute(Sender: TObject);
var
  Invoice:string;
  InvoiceTotal:string; 
  Invoicecondition:string;
begin
  inherited;
  if AdoQry_Tmp.Active then AdoQry_Tmp.Close;
//  if AdoQry_Main.Active then AdoQry_Main.Close;
//  if AdoQry_Detail.Active then AdoQry_Detail.Close;
  label2.Caption:='';
  label4.Caption:='';
  label5.Caption:='';
  label6.Caption:='';

  Frm_Ap_Qry_VendorInvoice_Condition:=TFrm_Ap_Qry_VendorInvoice_Condition.Create(Application);
  Frm_Ap_Qry_VendorInvoice_Condition.AdoQry_Tmp.Connection:=Dbconnect;
  if Frm_Ap_Qry_VendorInvoice_Condition.ShowModal=Mrok then
  begin
    Lbl_begindate.Caption:=Frm_Ap_Qry_VendorInvoice_Condition.Medt_begindate.Text;
    Lbl_Enddate.Caption:=Frm_Ap_Qry_VendorInvoice_Condition.Medt_Enddate.Text;
    Lbl_begindate1.Caption:=Frm_Ap_Qry_VendorInvoice_Condition.Medt_begindate1.Text;
    Lbl_Enddate1.Caption:=Frm_Ap_Qry_VendorInvoice_Condition.Medt_Enddate1.Text;

    with Frm_Ap_Qry_VendorInvoice_Condition do
    begin
      VendorCode:=Trim(Extedt_VendorCode.Text);
      begindate:=Medt_begindate.Text;
      enddate:=Medt_Enddate.Text;
      begindate1:=Medt_begindate1.Text;
      enddate1:=Medt_Enddate1.Text;

      if Trim(Extedt_VendorCode.Text)<>'' then
        Invoicecondition:=' and  ApInvoice.VendorCode= '+Quotedstr(VendorCode) ;
      if (Medt_begindate.Text<>'    .  .  ')  then
        Invoicecondition:=Invoicecondition+ ' and ApInvoice.ApInvoiceDate >= '+Quotedstr(begindate);
      if Medt_Enddate.Text<>'    .  .  ' then
        Invoicecondition:=Invoicecondition+ ' and ApInvoice.ApInvoicedate <= '+Quotedstr(enddate);
      if (Medt_begindate1.Text<>'    .  .  ')  then
        Invoicecondition:=Invoicecondition+ ' and ApInvoice.ApInvoiceInputDate >= '+Quotedstr(begindate1);
      if Medt_Enddate1.Text<>'    .  .  ' then
        Invoicecondition:=Invoicecondition+ ' and ApInvoice.ApInvoiceInputDate <= '+Quotedstr(enddate1);
    end;

      Invoice:='select ApInvoice.ApInvOICEID,'+
             ' ApInvoice.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
             ' ApInvoice.ApInvoiceNo,'+
             ' 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 '+
          ' where ApInvoice.ApInvoiceType<>2  ' +Invoicecondition;

      AdoQry_Main.Close;
      AdoQry_Main.SQL.clear;
      AdoQry_Main.SQL.Add(Invoice);
      AdoQry_Main.Open;

      InvoiceTotal:='select sum(a.ApInvoiceAmount) as ApInvoiceAmount, '+
                ' sum(a.ApInvoicetax) as ApInvoicetax, '+
                ' sum(a.TotalAmount) as TotalAmount from '+'('+ Invoice+') a';
      AdoQry_tmp.Close;
      AdoQry_tmp.SQL.clear;
      AdoQry_tmp.SQL.Add(InvoiceTotal);
      AdoQry_tmp.Open;
      Label2.Caption:='发票金额合计：'+AdoQry_tmp.fieldbyname('ApInvoiceAmount').asstring;
      Label4.Caption:='税额合计：'+AdoQry_tmp.fieldbyname('ApInvoicetax').asstring;
      Label5.Caption:='未税金额合计：'+AdoQry_tmp.fieldbyname('TotalAmount').asstring;  
  end;
  Frm_Ap_Qry_VendorInvoice_Condition.free;
end;

procedure TFrm_Ap_Qry_VendorInvoice.DataSource1DataChange(Sender: TObject;
  Field: TField);
var
  InvInBill:string;
  InvInBillTotal:string;
begin