ap_qry_noinvoiceinvinbilllist.pas

一个MRPII系统源代码版本

unit Ap_Qry_NoInvoiceInvInBillList;

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_Ap_Qry_NoInvoiceInvInBillList = Class(TFrm_Base_Qry)
    Panel1: TPanel;
    Lb_tax: TLabel;
    Label3: TLabel;
    Lb_Notax: TLabel;
    Label1: TLabel;
    Splitter1: TSplitter;
    DBGridEh1: TDBGridEh;
    DataSource1: TDataSource;
    AdoQuery_Detail: TAdoQuery;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
    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_NoInvoiceInvInBillList: TFrm_Ap_Qry_NoInvoiceInvInBillList;

implementation

{$R *.DFM}

{ TFrm__Ap_Qry_NoInvoiceInvInBillList }

procedure TFrm_Ap_Qry_NoInvoiceInvInBillList.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
begin
  inherited;
  AdoQuery_Detail.Connection :=AdoQry_Main.Connection ;
  Act_Filter.Enabled :=False;
  Act_Filter.Visible :=False;
  {SelectFromSql:='select InvInBill.InvBillId,'+
               ' InvInBill.VendorCode,'+
               ' InvInBill.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
               ' InvInBill.WHCode,'+
               ' InvInBill.WHCode+'' ''+Warehouse.WHName as WarehouseB,'+
               ' InvInBill.InvBillNo,'+
               ' InvInBill.InvBillDate,'+
                ' InvInBill.CurrencyCode,'+
               ' InvInBill.CurrencyCode+'' ''+ currency.currencyName as currencyB,'+
               ' sum(InvInBillline.InvBillnotaxAmountC) as InvBillnotaxAmountC,'+
               ' sum(InvInBillline.InvBillAmountC) as InvBillAmountC '+
        ' from InvInBill'+
        ' join Vendor on InvInBill.VendorCode=Vendor.VendorCode'+
        ' join Warehouse on InvInBill.WHCode=Warehouse.WHCode'+
        ' join currency on InvInBill.CurrencyCode=currency.currencyCode'+
        ' join InvInBillline on  InvInBill.InvBillId=InvInBillline.InvBillid';
  ConditionUserDefine:=' ((InvInBill.BillTypeCode =''0101'' )' +
        '   or (InvInBill.BillTypeCode=''0103'' )' +
        '   or (InvInBill.BillTypeCode=''0102'' )' +
        '   or ( (BillTypeCode=''0199'' )  and InvInBill.BillType2Code in ( select BillType2Code from BillType2 where ChangeAp=1 )))'+
        //'   and InvInBill.InvBillStkChck=1 ' +
        '   and InvInBill.InvBillValuation<>1' +
        '   and InvInBill.InvBillFinChck<>1';
  GroupByFields:=' InvInBill.InvBillId,InvInBill.VendorCode,Vendor.VendorName,'+
                 ' Warehouse.WHName,currency.currencyName,'+
                 ' InvInBill.WHCode,InvInBill.InvBillNo,InvInBill.InvBillDate,InvInBill.CurrencyCode';}
  SelectFromSql:='create  table  #tmp ( InvBillId int  ,isTotal int null ,'+
                                 ' VendorCode varchAr(12) null,VendorB varchAr(75) null,'+
                                 ' whCode varchAr(10) null ,WarehouseB varchAr(60) null ,'+
                                 ' InvBillNO varchAr(10) null,InvBillDate varchAr(10)  null,'+
                                 ' CurrencyCode varchAr (3) null ,currencyB varchAr(35),'+
                                 ' InvBillnotaxAmountC decimal(12,2),InvBillAmountC decimal(12,2), '+
                                 ' InvBillnotaxAmount decimal(12,2),InvBillAmount decimal(12,2)) '+
                 ' insert #tmp '+
                 ' select convert (int, InvInBill.InvBillId),0,'+
                         'InvInBill.VendorCode,'+
                         'InvInBill.VendorCode+'' ''+Vendor.VendorName as VendorB,'+
                         'InvInBill.WHCode,'+
                         ' InvInBill.WHCode+'' ''+Warehouse.WHName as WarehouseB,'+
                         ' InvInBill.InvBillNo,'+
                         ' convert(varchAr(10),InvInBill.InvBillDate,102),'+
                         ' InvInBill.CurrencyCode,'+
                         ' InvInBill.CurrencyCode+'' ''+ currency.currencyName as currencyB,'+
                         ' sum(InvInBillline.InvBillnotaxAmountC) as InvBillnotaxAmountC,'+
                         ' sum(InvInBillline.InvBillAmountC) as InvBillAmountC ,'+
                         ' sum(InvInBillline.InvBillnotaxAmount) as InvBillnotaxAmount,'+
                         ' sum(InvInBillline.InvBillAmount) as InvBillAmount '+

                   ' from InvInBill '+
                         ' join Vendor on InvInBill.VendorCode=Vendor.VendorCode '+
                         ' join Warehouse on InvInBill.WHCode=Warehouse.WHCode '+
                         ' join currency on InvInBill.CurrencyCode=currency.currencyCode '+
                         ' join InvInBillline on  InvInBill.InvBillId=InvInBillline.InvBillid '+
                   ' where  ((InvInBill.BillTypeCode =''0101'' ) '+
                         ' or (InvInBill.BillTypeCode=''0103'' ) '+
                         ' or (InvInBill.BillTypeCode=''0102'' ) '+
                         ' or ( (BillTypeCode=''0199'' )  '+
                         ' and InvInBill.BillType2Code in ( select BillType2Code from BillType2 where ChangeAp=1 ))) '+
                             //'   and InvInBill.InvBillStkChck=1 ' +
                         ' and InvInBill.InvBillValuation<>1 '+
                         ' and InvInBill.InvBillFinChck<>1 '+
                   ' Group By InvInBill.InvBillId,InvInBill.VendorCode,Vendor.VendorName,'+
                         ' Warehouse.WHName,currency.currencyName,'+
                         ' InvInBill.WHCode,InvInBill.InvBillNo,InvInBill.InvBillDate,InvInBill.CurrencyCode'+
                ' insert #tmp '+
                ' select 0,1, VendorCode,VendorB+''(合计)'' As VendorB,'+
                         ' '''','''','''','''','''','''','+
                         ' sum(InvBillnotaxAmountC),sum(InvBillAmountC), sum(InvBillnotaxAmount),sum(InvBillAmount) '+
                ' from #tmp '+
                ' group by VendorCode,VendorB '+
                ' select * from #tmp Order by VendorCode,isTotal,WHCode,InvBillDate,InvBillNo '+
                ' drop table #tmp ';
  //OrderByFields:='VendorCode,WHCode,InvBillDate,InvBillNo';
  PriceFields:='InvBillnotaxAmountC,InvBillAmountC,';
  getdata;
end;

procedure TFrm_Ap_Qry_NoInvoiceInvInBillList.FormCreate(Sender: TObject);
begin
  inherited;
  Pnl_Title.Caption:='未开发票入库单列表';
  Lbl_Condition.Caption :='全部';
  Lbl_Order.Caption :='供应商代码/仓库代码/入库日期/单据号';
  Lb_Notax.Caption:='';
  Lb_tax.Caption:='';
end;

procedure TFrm_Ap_Qry_NoInvoiceInvInBillList.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Qry_NoInvoiceInvInBillList:=nil;
end;

procedure TFrm_Ap_Qry_NoInvoiceInvInBillList.DataSourceDataChange(
  Sender: TObject; Field: TField);
var
  S:string;
begin
  inherited;
  if AdoQry_Main.RecordCount=0 then
  begin
    Lb_Notax.Caption:='';
    Lb_tax.Caption:='';
    if AdoQuery_Detail.Active then AdoQuery_Detail.Close;
    Pnl_Hint.Caption:='提示：查找到'+inttostr(AdoQry_Main.recordCount)+'条记录';
    Application.MessageBox('没有符合条件的记录','提示',mb_ok+MB_IconWArNING);
    abort;
  end;

  S:='select InvInBillLine.InvBilllineno,'+
             ' InvInBillLine.ItemCode+'' ''+Item.ItemName as ItemB,'+
             ' InvInBillLine.InvBillQTY,'+
             ' InvInBillLine.InvBillPriceC,'+
             ' InvInBillLine.InvBillAmountC,'+
             ' InvInBillLine.InvBillPrice,'+
             ' InvInBillLine.InvBillAmount'+
             //' InvInBillLine.InvBillAmountC'+
      ' from InvInBillline'+
      ' left join Item on InvInBillLine.ItemCode=Item.ItemCode '+
      ' where InvInBillLine.InvBillid='+ AdoQry_Main.fieldbyname('InvBillId').asstring;
  AdoQuery_Detail.Close;
  AdoQuery_Detail.SQL.clear;
  AdoQuery_Detail.SQL.Add(s);
  AdoQuery_Detail.Open;
end;


procedure TFrm_Ap_Qry_NoInvoiceInvInBillList.AdoQueryAfterOpen(
  DataSet: TDataSet);
var
  SumNoTax,SumTax:double;
begin
  inherited;
  SumNoTax:=0;
  SumTax:=0;
  with AdoQry_Main do
  begin
    First;
    while not eof do
    begin
      SumNotax:=SumNotax+AdoQry_Main.fieldbyname('InvBillnotaxAmountC').asfloat;
      Sumtax:=Sumtax+AdoQry_Main.fieldbyname('InvBillAmountC').asfloat;
      next;
    end;
  end;
  Lb_Notax.Caption:=floattostr(SumNotax);
  Lb_tax.Caption:=floattostr(Sumtax);
end;

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

end;

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

end;

procedure TFrm_Ap_Qry_NoInvoiceInvInBillList.Act_ExcelExecute(
  Sender: TObject);
var
  tno: TDataChangeEvent;
begin
  If AdoQry_Main.IsEmpty then Exit;
  tno:=DataSource.OnDataChange ;
  DataSource.OnDataChange:=nil;
  try
    inherited;
  finally
    DataSource.OnDataChange:=Tno;
  end;
end;

end.