📄 ar_qry_araging4.pas
字号:
unit Ar_Qry_ArAging4;
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_ArAging4 = 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_ArAging4: TFrm_Ar_Qry_ArAging4;
implementation
uses Ar_Qry_ArAging2_C,Sys_Global;
{$R *.DFM}
procedure TFrm_Ar_Qry_ArAging4.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 customer.AreaCode,'' '
+' +'' Area.AreaName,'' '
+' +'' customer.AreaCode+'''' ''''+Area.AreaName as customerflag,'' '
+' +'' 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, '
+' 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 '
+' select @ss=''alter table #Result Add [''+convert(varchAr,@lastdays)+''以上金额] float(8) null,[''+convert(varchAr,@lastdays)+''以上比率] varchAr(100) null'' '
+' exec(@ss) '
+' 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)+''以上比率]'' '
+ ' select @string=@string+'' from sa_SaleInvoice '' '
// +' +'' left join Dept on sa_SaleInvoice.SaleDeptCode=Dept.DeptCode '' '
// +' +'' left join SaleType on sa_SaleInvoice.SaleTypeCode=SaleType.SaleTypeCode '' '
+ ' +'' 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 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 customer.AreaCode,AreaName,sa_SaleInvoice.currencyCode,currencyName'' '
+ ' 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:=8;
end;
if i=4 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='货币代码';
AdoQry_Main.Fields[i].DisplayWidth:=8;
end;
if i=5 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='货币名称';
AdoQry_Main.Fields[i].DisplayWidth:=8;
end;
if i=6 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='货币标识';
AdoQry_Main.Fields[i].DisplayWidth:=9;
end;
if i=7 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='帐期内金额';
AdoQry_Main.Fields[i].DisplayWidth:=13;
end;
if i=8 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='帐期内比率';
AdoQry_Main.Fields[i].DisplayWidth:=13;
end;
if i>8 then
AdoQry_Main.Fields[i].DisplayWidth:=11;
AdoQry_Main.Fields[i].Origin:='#Result';
end;
dbgrideh.Columns[0].Visible:=False;
dbgrideh.Columns[1].Visible:=False;
dbgrideh.Columns[4].Visible:=False;
dbgrideh.Columns[5].Visible:=False;
dbgrideh.FrozenCols:=5;
lbl_Condition.Caption:='客户代码从 '+customerCode1+' 到 '+customerCode2+' / 销售区域从 '+AreaCode1+' 到 '+AreaCode2+' / 销售类型从 '+SaleTypeCode1+' 到 '+SaleTypeCode2
+#13+'销售部门代码从 '+DeptCode1+' 到 '+DeptCode2+' / 营销业务员代码从 '+EmployeeCode1+' 到 '+EmployeeCode2
+#13+'货币代码 '+currencyCode;
end;
procedure TFrm_Ar_Qry_ArAging4.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Ar_Qry_ArAging4:=nil;
end;
procedure TFrm_Ar_Qry_ArAging4.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
inherited;
try
Executesql(AdoQry_tmp,'drop table #Result,#tmpAccountAging',1);
except
end;
end;
procedure TFrm_Ar_Qry_ArAging4.Act_FilterExecute(Sender: TObject);
var i:integer;
begin
inherited;
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:=8;
end;
if i=4 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='货币代码';
AdoQry_Main.Fields[i].DisplayWidth:=8;
end;
if i=5 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='货币名称';
AdoQry_Main.Fields[i].DisplayWidth:=8;
end;
if i=6 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='货币标识';
AdoQry_Main.Fields[i].DisplayWidth:=16;
end;
if i=7 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='帐期内金额';
AdoQry_Main.Fields[i].DisplayWidth:=13;
end;
if i=8 then
begin
AdoQry_Main.Fields[i].DisplayLabel:='帐期内比率';
AdoQry_Main.Fields[i].DisplayWidth:=13;
end;
if i>8 then
AdoQry_Main.Fields[i].DisplayWidth:=11;
AdoQry_Main.Fields[i].Origin:='#Result';
end;
dbgrideh.Columns[0].Visible:=False;
dbgrideh.Columns[1].Visible:=False;
dbgrideh.Columns[4].Visible:=False;
dbgrideh.Columns[5].Visible:=False;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -