ar_qry_araging1.pas

一个MRPII系统源代码版本

unit Ar_Qry_ArAging1;

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_ArAging1 = Class(TFrm_Base_Qry)
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_FilterExecute(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Ar_Qry_ArAging1: TFrm_Ar_Qry_ArAging1;

implementation
uses Ar_Qry_ArAging2_C,Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_ArAging1.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    customerCode1,customerCode2:string;
    DeptCode1,DeptCode2:string;
    SaleTypeCode1,SaleTypeCode2:string;
    EmployeeCode1,EmployeeCode2:string;
    AreaCode1,AreaCode2:string;
    currencyCode:string;
    i:integer;
begin
Application.ProcessMessages;
 inherited;
 customerCode1:='';
 customerCode2:='';
 SaleTypeCode1:='';
 SaleTypeCode2:='';
 DeptCode1:='';
 DeptCode2:='';
 EmployeeCode1:='';
 EmployeeCode2:='';
 AreaCode1:='';
 AreaCode2:='';
 currencyCode:='';
 try
  with TFrm_Ar_Qry_ArAging2_C.Create(self) do
   begin
     AdoQry_tmp.Connection:=dbconnect;
     showmodal;     
     if modalResult=mrok then 
       begin
         customerCode1:=edt_CustomerCode1.Text;
         customerCode2:=edt_CustomerCode2.Text;
         SaleTypeCode1:=edt_SaleTypeCode1.text;
         SaleTypeCode2:=edt_SaleTypeCode2.text;
         DeptCode1:=edt_DeptCode1.Text;
         DeptCode2:=edt_DeptCode2.Text;
         EmployeeCode1:=edt_EmployeeCode1.Text;
         EmployeeCode2:=edt_EmployeeCode2.Text;
         AreaCode1:=edt_AreaCode1.Text;
         AreaCode2:=edt_AreaCode2.Text;
         currencyCode:=edt_CurrencyCode1.Text;
       end;     
   end;
 finally
  Frm_Ar_Qry_ArAging2_C.Free;
 end;

  sqltext:='select * into #tmpAccountAging from Ar_AccountAging'
         +' declAre @i int       '
         +'   declAre @j int      '
         +'   declAre @lastdays int   '
         +'   declAre @thisdays int    '
         +'   declAre @string varchAr(8000)   '
         +'   declAre @title varchAr(100)     '
         +'   select @string='+quotedstr('')   
         +'   select @lastdays=0             '
         +'   select @j=1                     '
         +'   select @i=Count(*) from #tmpAccountAging      '
         +'   select @i                                     '
         +'   select @string='' insert into #Result select sa_SaleInvoice.customerCode,''           '
         +'                  +''  customer.customerName,''                                                  '
         +'                  +'' sa_SaleInvoice.customerCode+'''' ''''+customer.customerName as customerflag,''           '
         +'                  +'' customer.AreaCode,  ''  '
         +'                  +'' Area.AreaName, ''  '
         +'                  +'' customer.AreaCode+'''' ''''+Area.AreaName as Areaflag, '' '
         +'                  +'' customer.customercontact,  ''  '
         +'                  +'' customer.customerSaleEmployeeCode,  ''  '
         +'                  +'' Employee.EmployeeName, ''  '
         +'                  +'' customer.customerSaleEmployeeCode+'''' ''''+Employee.EmployeeName as Employeeflag,'' '
         +'                  +'' customer.customercredit,  ''  '
         +'                  +'' sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0)) as Balance,''        '
         +'                  +'' sa_SaleInvoice.currencyCode,   ''                                                              '
         +'                  +'' currency.currencyName,        ''                                                                '
         +'                  +'' sa_SaleInvoice.currencyCode+'''' ''''+currency.currencyName as currencyflag,''                   '
         +'                  +'' sum(case when dateDiff(dd,sa_SaleInvoice.Billdate,getdate())<=PayTerm.Paycredays then sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0) else 0 end) as 帐期内金额,''  '
         +'                  +'' case when sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0))= 0 then ''''0%'''' ''                                                                                   '
         +'                  +'' else convert(varchAr,convert(decimal(12,2),sum(case when dateDiff(dd,sa_SaleInvoice.Billdate,getdate())<=PayTerm.Paycredays then '
         +'   sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0) else 0 end)*100.0/sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0))))+''''%'''' end as 帐期内比率,''  '
         +'   declAre @ss varchAr(500)         '
         +'   create table #Result             '
         +'   (DeptCode varchAr(50) null,      '
         +'    DeptName varchAr(500) null,     '
         +'    Deptflag varchAr(1000) null,    '
         +'    AreaCode varchAr(10)  null,  '
         +'    AreaName varchAr(500) null,   '
         +'    Areaflag varchAr(510) null,   '
         +'    customercontact varchAr(50) null, '
         +'    EmployeeCode varchAr(20) null, '
         +'    EmployeeName varchAr(20) null, '
         +'    Employeeflag varchAr(41) null,'
         +'    customercredit  float(8) null ,'
         +'    Balance  float(8) null,         '
         +'    currencyCode varchAr(10) null,  '
         +'    currencyName varchAr(20) null,  '
         +'    currencyflag varchAr(30) null,  '
         +'    agein        float(8)    null,  '
         +'    ageinrate    varchAr(100) null) '
         +'   while (@j<=@i)                      '
         +'   begin                             '
         +'     select @thisdays=days,@title=AgingDESCription from #tmpAccountAging     '
         +'     where  ordinalno=(select min(ordinalno) from #tmpAccountAging)          '
         +'     select @ss=''alter table #Result Add [''+@title+''金额] float(8) null,[''+@title+''比率] varchAr(100) null''   '
         +'     exec(@ss)select @thisdays                                                '
         +'     delete from #tmpAccountAging                                             '
         +'     where  ordinalno=(select min(ordinalno) from #tmpAccountAging)           '
         +'     select @string=@string+'' sum(case when dateDiff(dd,sa_SaleInvoice.Billdate,getdate())<=''+convert(varchAr,@thisdays)+'' and dateDiff(dd,sa_SaleInvoice.Billdate,getdate())>''+convert(varchAr,@lastdays)+'' then '
         +'     sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0) else 0 end) as [''+@title+''金额],''      '
         +'                         +'' case when sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0))=0 then ''''0%'''' ''                                                                                                                            '
         +'                         +'' else convert(varchAr, convert(decimal(12,2),sum(case when dateDiff(dd,sa_SaleInvoice.Billdate,getdate())<=''+convert(varchAr,@thisdays)+'' and dateDiff(dd,sa_SaleInvoice.Billdate,getdate())>'' '
         +'     +convert(varchAr,@lastdays)+'' then '
         +'     sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0) else 0 end)*100.0/sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0))))+''''%'''' end as [''+@title+''比率],''     '
         +'     select @lastdays=@thisdays          '
         +'     select @j=@j+1                      '
         +'   end                                 '
//         +'   if @lastdays<>-1  '
  //       +'   begin  '
         +'     select @ss=''alter table #Result Add [''+convert(varchAr,@lastdays)+''以上金额] float(8) null,[''+convert(varchAr,@lastdays)+''以上比率] varchAr(100) null''        '
         +'     exec(@ss)                                                                                                                                                           '
        // +'   end  '                                                                                                                                                                        
         +'   select @string=@string+'' sum(case when  dateDiff(dd,sa_SaleInvoice.Billdate,getdate())>''+convert(varchAr,@lastdays)+'' then '
         +'   sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0) else 0 end) as [''+convert(varchAr,@lastdays)+''以上金额],''     '
         +'                         +'' case when  sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0))=0 then ''''0%'''' ''     '
         +'                         +'' else convert(varchAr,convert(decimal(12,2),sum(case when  dateDiff(dd,sa_SaleInvoice.Billdate,getdate())>''+convert(varchAr,@lastdays)+'' then '
         +'   sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0) else 0 end)*100.0/sum(sa_SaleInvoice.TotaltaxAmount-isnull(sa_SaleInvoice.CancelAmount,0))))+''''%'''' end as [''+convert(varchAr,@lastdays)+''以上比率]''    '
    //     +'   end   '
        + '   select @string=@string+'' from  sa_SaleInvoice  ''                                                    '
         +'          +''  left join Dept on sa_SaleInvoice.SaleDeptCode=Dept.DeptCode  ''                           '
        + '          +''  left join currency on sa_SaleInvoice.currencyCode=currency.currencyCode ''               '
        + '          +''  left join customer on sa_SaleInvoice.customerCode=customer.customerCode ''                   '
        +'           +''    and  customer.AreaCode between '''+quotedstr(AreaCode1)+''' and '''+quotedstr(AreaCode2)+''''''     
         +'          +''  left join Area on customer.AreaCode=Area.AreaCode''     '
         +'          +''  left join Employee on customer.customerSaleEmployeeCode=Employee.EmployeeCode ''   '
        + '          +''  left join PayTerm on sa_SaleInvoice.SaletermCode=PayTerm.PayTermCode''                        '
        +'           +''  where  sa_SaleInvoice.SaleDeptCode between '''+quotedstr(DeptCode1)+''' and '''+quotedstr(DeptCode2)+''''''  
        +'           +''    and  sa_SaleInvoice.customerCode between '''+quotedstr(customerCode1)+''' and '''+quotedstr(customerCode2)+''''''     
        +'           +''    and  sa_SaleInvoice.SaleTypeCode between '''+quotedstr(SaleTypeCode1)+''' and '''+quotedstr(SaleTypeCode2)+''''''     
        +'           +''    and  sa_SaleInvoice.SaleEmployeeCode between '''+quotedstr(EmployeeCode1)+''' and '''+quotedstr(EmployeeCode2)+''''''     
        +'           +''    and  sa_SaleInvoice.currencyCode= '''+quotedstr(currencyCode) +''''''     
        + '          +'' group by sa_SaleInvoice.customerCode,customerName,sa_SaleInvoice.currencyCode,currencyName,customer.AreaCode,Area.AreaName,customer.customerSaleEmployeeCode,Employee.EmployeeName,customer.customercontact,customer.customercredit'' '
        + '   exec(@string)     ';  
Executesql(AdoQry_tmp,sqltext,1);
selectfromsql:='select * from #Result';
Executesql(AdoQry_Main,'select * from #Result Order by DeptCode',0);
for i:=0 to AdoQry_Main.FieldCount-1 do
begin
 if i=0 then 
  begin
  AdoQry_Main.Fields[i].DisplayLabel:='销售客户代码';
  AdoQry_Main.Fields[i].DisplayWidth:=13;
  end;
 if i=1 then 
  begin
  AdoQry_Main.Fields[i].DisplayLabel:='销售客户名称';
  AdoQry_Main.Fields[i].DisplayWidth:=16;
  end;
 if i=2 then 
  begin
  AdoQry_Main.Fields[i].DisplayLabel:='销售客户标识';
  AdoQry_Main.Fields[i].DisplayWidth:=19;
  end;
 if i=3 then 
  begin
  AdoQry_Main.Fields[i].DisplayLabel:='销售区域代码';
  AdoQry_Main.Fields[i].DisplayWidth:=13;
  end;
 if i=4 then 
  begin
  AdoQry_Main.Fields[i].DisplayLabel:='销售区域名称';
  AdoQry_Main.Fields[i].DisplayWidth:=16;
  end;
 if i=5 then 
  begin
  AdoQry_Main.Fields[i].DisplayLabel:='销售区域标识';
  AdoQry_Main.Fields[i].DisplayWidth:=19;
  end;
 if i=6 then