ap_qry_apaging.pas

一个MRPII系统源代码版本

unit Ap_Qry_ApAging;

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_ApAging = Class(TFrm_Base_Qry)
    AdoQry_tmp2: TAdoQuery;
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    { Private declarations }
    Lc_VendorCode:string;
    Lc_IncludeNoInvoice:BooLean;
    procedure GetApData(VendorCode:string;IncludeNoInvoice:boolean);
    //定义建立临时表的函数，返回生成临时表的字符串(生成第一个临时表)
    function  CreateTmpTableSQL:string;
    //根据传入的字段名称，起始时间，终止时间，生成有票应付款的ＳＱＬ脚本
    function  GetHaveInviceApSQL(fieldName,begindate,enddate:string):string;
    //根据传入的字段名称，起始时间，终止时间，生成未票应付款的ＳＱＬ脚本
    function  GetNoInviceApSQL(fieldName,begindate,enddate:string):string;
    //Function  GetGroupSQL:String;
    //创建第二个临时表，求出所有应付款的数额，并显示在屏幕上。
    function  GetTotal:string;
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;    
  end;

var
  Frm_Ap_Qry_ApAging: TFrm_Ap_Qry_ApAging;

implementation

uses Sys_Global;

{$R *.DFM}

{ TFrm_Ap_Qry_ApAging }

function TFrm_Ap_Qry_ApAging.CreateTmpTableSQL: string;
var
  Tmp_Str:String;
begin                                             
  Tmp_Str:='';
  Tmp_Str:=Tmp_Str+' set noCount on ';
  Tmp_Str:=Tmp_Str+' create table #abc ';
  Tmp_Str:=Tmp_Str+' (VendorCode varchAr(20) null, ';
  Tmp_Str:=Tmp_Str+'  VendorB varchAr(60) null,currencyCodeb varchAr(40) null ,';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+fieldbyname('SubApName').AsString +' float null ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+' ) ';
    end;
  end;
  Result:=Tmp_Str;
end;

procedure TFrm_Ap_Qry_ApAging.GetApData(VendorCode: string;
  IncludeNoInvoice: boolean);
var
  S1:string;
  I:integer;
begin
  S1:='';
  s1:=CreateTmpTableSQL;
  with AdoQry_tmp2 do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    First;
    while not eof do
    begin
      S1:=S1+GetHaveInviceApSQL(fieldbyname('SubApName').asstring,fieldbyname('beginday').asstring,fieldbyname('endday').asstring);
      if Lc_includeNoInvoice then
      begin
        S1:=S1+GetNoInviceApSQL(fieldbyname('SubApName').asstring,fieldbyname('beginday').asstring,fieldbyname('endday').asstring);
      end;
      Next;
    end;
  end;

  //Add The Last display Sql
  S1:=S1+' '+getTotal;
  with AdoQry_Main do
  begin
    Close;
    SQL.clear;
    SQL.Add(S1);
    open;
  end;
  OrderByFields:='VendorB';
  //PriceFields:='D1_30,D31_90,';
  //调整DBgrideh的显示状况
  for i:=0 to DBGridEh.Columns.Count-1 do
  begin
    DBGridEh.Columns[i].Title.alignment:=taCenter;
    DBGridEh.Columns[i].Width:=80;
  end;
  DBGridEh.Columns[0].Title.Caption:='供应商代码';
  DBGridEh.Columns[0].Visible:=False;
  DBGridEh.Columns[1].Title.Caption:='供应商标识';
  DBGridEh.Columns[2].Title.Caption:='货币标识';
  DBGridEh.Columns[2].Visible:=True;
  DBGridEh.Columns[3].Title.Caption:='应付帐款余额';
  DBGridEh.Columns[3].Visible:=True;
  DBGridEh.Columns[1].Width:=240;
end;

//根据传入的字段名称，起始时间，终止时间，生成有票应付款的ＳＱＬ脚本
function TFrm_Ap_Qry_ApAging.GetHaveInviceApSQL(fieldName, begindate,enddate: string): string;
var
  Tmp_Str:String;
begin
  Tmp_Str:='';
  Tmp_Str:=' insert into #abc ' +
  ' (VendorCode,VendorB,currencyCodeb,'+
  ''+fieldName+' )'+
  ' select a.VendorCode, a.VendorCode+'' ''+a.VendorName as VendorB,'+
  ' a.currencyCode+'' ''+c.currencyName currencyCodeb,b.Amount30 '+
  ' from Vendor a, '+
  ' (select VendorCode,Sum(ApInvoiceAmount-(ApPayedAmount)) as Amount30 '+
  ' from ApInvoice '+
  ' where dateDiff(dy,ApInvoiceinputdate,getdate())+1>= '+begindate+''+
  ' and dateDiff(dy,ApInvoiceinputdate,getdate())+1<= '+enddate+'';
  if Lc_VendorCode<>''then
    Tmp_Str:=Tmp_Str+' and VendorCode='''+Lc_VendorCode+''' '  ;
  Tmp_Str:=Tmp_Str+ ' and ApPayFlag<>0 '+
  ' group by VendorCode ) b ,currency c'+
  ' where a.VendorCode=b.VendorCode'+
  ' and a.currencyCode=c.currencyCode';
  Result:=tmp_Str;
end;

//根据传入的字段名称，起始时间，终止时间，生成未票应付款的ＳＱＬ脚本
function TFrm_Ap_Qry_ApAging.GetNoInviceApSQL(fieldName, begindate,
  enddate: string): string;
var
  Tmp_Str:string;
begin
  Tmp_Str:='';
  Tmp_Str:=' insert into #abc '+
   '           (VendorCode,VendorB,currencyCodeb,'+
   '            '+fieldName+')'+
   '         select a.VendorCode, '+
   '           a.VendorCode+'' ''+a.VendorName as VendorB, '+
   '           a.currencyCode+'' ''+c.currencyName  as currencyCodeb,'+
   '           b.Amount30 '+
   '         from Vendor a, '+
   '           ( select VendorCode, '+
   '               sum(isnull(InvBillNoTaxAmountC,0)) as Amount30 '+
   '             from InvInBill left join InvInBillline '+
   '               on InvInBill.InvBillid=InvInBillline.InvBillid '+
   '               and InvBillStkchck=1'+
   '               and InvBillValuation<>1'+
   '               and InvBillfinchck<>1'+
   '               and(BillTypeCode=''0101'''+
   '                   or BillTypeCode=''0102'''+
   '                   or BillTypeCode=''0103'''+
   '                   or (BillTypeCode=''0199'''+
   '                        and BillType2Code in'+
   '                           (select BillType2Code'+
   '                            from BillType2'+
   '                            where ChangeAp=1'+
   '                            )'+
   '                   )'+
   '            )'+
   '               and dateDiff(dy,InvBilldate,getdate())+1>='+begindate+''+
   '               and dateDiff(dy,InvBilldate,getdate())+1<='+enddate+''+
   '               and InvBillfinchck<>1'+ ''+
   '             Group by VendorCode ) b ,currency c '+
   '    where a.VendorCode=b.VendorCode and a.currencyCode=c.currencyCode ';
   if Lc_VendorCode<>''then
     Tmp_Str:=Tmp_Str+' and a.VendorCode='''+Lc_VendorCode+''' ';
  Result:=Tmp_Str;
end;

function TFrm_Ap_Qry_ApAging.GetTotal: string;
var
  Tmp_Str:String;
begin
//创建临时表#aaaa
  Tmp_Str:='';
  Tmp_Str:=Tmp_Str+' set noCount on ';
  Tmp_Str:=Tmp_Str+' create table #aaaa ';
  Tmp_Str:=Tmp_Str+' (VendorCode varchAr(20) null, ';
  Tmp_Str:=Tmp_Str+' VendorB varchAr(60) null, currencyCodeb varchAr(40) null , ';
  Tmp_Str:=Tmp_Str+' Total float null , ';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    First;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+fieldbyname('SubApName').AsString +' float null ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+' ) ';
    end;
  end;

  //在临时表中插入数据
  Tmp_Str:=Tmp_Str+' insert into  #aaaa  select VendorCode,VendorB,currencyCodeb, ';
  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+'Sum(isnull('+
               fieldbyname('SubApName').AsString +',0))'+''+'';
      next;
      if not eof then Tmp_Str:=Tmp_Str+'+'
      else Tmp_Str:=Tmp_Str+' as Total, ';
    end;
  end;

  With AdoQry_tmp do
  begin
    Close;
    Sql.clear;
    Sql.Add('select * from SubAp Order by SubApCode ');
    open;
    First;
    while Not eof do
    begin
      Tmp_Str:=Tmp_Str+'Sum(isnull('+
               fieldbyname('SubApName').AsString +',0))  as  '+
               fieldbyname('SubApName').AsString+' ';
      next;
      if not eof then Tmp_Str:=Tmp_Str+','
      else Tmp_Str:=Tmp_Str+'';
    end;
  end;

  Tmp_Str:=Tmp_Str+'from #abc '+
           ' group by VendorCode,VendorB, currencyCodeb '+
           ' select * from #aaaa ';
           //' drop table #abc  drop table #aaaa';
  Result:=Tmp_Str;
end;

procedure TFrm_Ap_Qry_ApAging.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Ap_Qry_ApAging:=nil;
end;

procedure TFrm_Ap_Qry_ApAging.FormCreate(Sender: TObject);
begin
  inherited;
  if AdoQry_Main.Active then  AdoQry_Main.Close;
  Pnl_Title.Caption:='应付帐款帐龄分析';
  Lc_IncludeNoInvoice:=False;
  Lbl_Condition.Caption :='全部';
  Lbl_Order.Caption :='供应商代码';
end;

procedure TFrm_Ap_Qry_ApAging.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
  AdoQry_Main.Connection:=AdOConnection;
  AdoQry_Tmp.Connection:=AdOConnection;
  AdoQry_Tmp2.Connection:=AdOConnection;
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Add('select * from SubAp ');
  AdoQry_tmp.Open;
  if AdoQry_tmp.RecordCount<=0 then
  begin
    Application.MessageBox('请首先在应付帐款帐龄段设置中录入数据','提示',mb_IconwArning);
    exit;
  end;

  //设置PriceFields的值
  With AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQL.Add('select * from SubAp ');
    Open;

    PriceFields:='Total,';
    First;
    while not eof do
    begin
      PriceFields:=PriceFields+AdoQry_tmp.fieldbyname('SubApName').AsString;
      Next;
      if not eof then
        PriceFields:=PriceFields+','
      else
      begin
        PriceFields:=PriceFields+',';
        PriceFields:=quotedstr(PriceFields);
      end;
    end;
  end;
  OrderByFields:='VendorB';
  GetApData(Lc_VendorCode,Lc_IncludeNoInvoice);
  selectfromsql:='select * from #aaaa';
end;

procedure TFrm_Ap_Qry_ApAging.DataSourceDataChange(Sender: TObject;
  Field: TField);
begin
  inherited;
  //Pnl_Hint.Caption:='提示：查到记录数: '+inttostr(AdoQry_Main.recordCount);
end;

procedure TFrm_Ap_Qry_ApAging.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  {ExecuteSql(AdoQry_Tmp,' drop table #abc  drop table #aaaa',1);
  inherited;}
  Action:=CaFree;
end;

procedure TFrm_Ap_Qry_ApAging.AdoQueryAfterOpen(DataSet: TDataSet);
begin
  PriceFields:='Total,'+quotedstr(PriceFields);
  inherited;

end;

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

end;

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

end;

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

end;

end.