📄 gl_qry_accountbook4.pas
字号:
Executesql(AdoQry_tmp,'drop table #tmpResult',1);
except
end;
Executesql(AdoQry_tmp,'select * from Gl_AccountParam',0);
stArtYear:=AdoQry_tmp.fieldbyname('AccountuseYear').asinteger;
stArtMonth:=AdoQry_tmp.fieldbyname('AccountuseMonth').asinteger;
sqltext:='select 0 as Credenceid,1 as Credencelineno,9 as flag,'' 期初 '' as Credencedate, '
+' '' '' as CredenceCodeno, '
+' '' '' as docketName, '
+' '' '' as kmCode, '
+' '' '' as kmName, '
+' '' '' as kmflag, '
+' '' '' as currencyflag, '
+' case when t1.fdebitBalance=0 then 0 else t1.debitBalance/t1.fdebitBalance end as rate, '
+' case when t1.Balancedirection=1 then AmountBalance else 0 end as jAmount, '
+' case when t1.Balancedirection=2 then AmountBalance else 0 end as dAmount, '
+' case when t1.Balancedirection=1 then (case when AmountBalance=0 then 0 else debitBalance/AmountBalance end) else 0.0 end as jPrice,'
+' case when t1.Balancedirection=2 then (case when AmountBalance=0 then 0 else creditBalance/AmountBalance end) else 0.0 end as dPrice,'
+' case when t1.Balancedirection=1 then (case when AmountBalance=0 then 0 else fdebitBalance/AmountBalance end) else 0.0 end as jfPrice,'
+' case when t1.Balancedirection=2 then (case when AmountBalance=0 then 0 else fcreditBalance/AmountBalance end) else 0.0 end as dfPrice,'
+' t1.debitBalance, '
+' t1.fdebitBalance, '
+' t1.creditBalance, '
+' t1.fcreditBalance, '
+' t1.Balancedirection,'
+' t1.FirstAmountBalance as endAmount, '
+' case when t1.FirstAmountBalance=0 then 0 else t1.FirstBalance/t1.FirstAmountBalance end as endPrice,'
+' case when t1.FirstAmountBalance=0 then 0 else t1.FirstfBalance/t1.FirstAmountBalance end as endfPrice,'
+' t1.FirstBalance as endBalance, '
+' t1.FirstfBalance as endfBalance '
+' into #tmpResult '
+' from Gl_AccountSubjectBalance t1 '
+' join Gl_AccountSubject t2 on t1.kmid=t2.kmid and t2.kmCode='+quotedstr(Trim(edt_kmCode.text))
+' and t1.AccountperiodYear='+inttostr(stArtYear)
+' and t1.AccountperiodMonth='+inttostr(stArtMonth)
+' union '
+' select #tmpAmountCredence.Credenceid,#tmpAmountCredence.Credencelineno,9,convert(varchAr,Credencedate,102), '
+' CredenceCodeno, '
+' docketName, '
+' #tmpAmountCredence.kmCode, '
+' kmName, '
+' kmflag, '
+' currencyflag, '
+' rate, '
+' jAmount, '
+' dAmount, '
+' jPrice, '
+' dPrice, '
+' jfPrice, '
+' dfPrice, '
+' debitAmount, '
+' fdebitAmount,'
+' creditAmount, '
+' fcreditAmount, '
+' Balancedirection ,'
+' endAmount, '
+' endPrice,'
+' endfPrice, '
+' endBalance, '
+' endfBalance '
+ ' from #tmpAmountCredence '
+ ' where kmCode='+quotedstr(Trim(edt_kmCode.text));
Executesql(AdoQry_tmp,sqltext,1) ;
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Executesql(AdoQry_tmp,'select * from #tmpResult where Credencedate='' 期初'' Order by Credencedate',0);
lastBalance:=AdoQry_tmp.fieldbyname('endBalance').asfloat;
lastfBalance:=AdoQry_tmp.fieldbyname('endfBalance').asfloat;
lastAmount:=AdoQry_tmp.fieldbyname('endAmount').asfloat;
Executesql(AdoQry_tmp,'select * from #tmpResult where Credencedate<>'' 期初'' Order by Credencedate',0);
while not AdoQry_tmp.Eof do
begin
sqltext:='update #tmpResult '
+' set endBalance='+floattostr(lastBalance)+'+case when debitBalance<>0 then debitBalance else (-1)*creditBalance end, '
+' endfBalance='+floattostr(lastfBalance)+'+case when fdebitBalance<>0 then fdebitBalance else (-1)*fcreditBalance end ,'
+' endAmount='+floattostr(lastAmount)+'+case when debitBalance<>0 then jAmount else (-1)*dAmount end '
+' where Credenceid='+AdoQry_tmp.fieldbyname('Credenceid').asstring
+' and Credencelineno='+AdoQry_tmp.fieldbyname('Credencelineno').asstring;
Executesql(AdoQry_Main,sqltext,1);
sqltext:= 'select * from #tmpResult '
+' where Credenceid='+AdoQry_tmp.fieldbyname('Credenceid').asstring
+' and Credencelineno='+AdoQry_tmp.fieldbyname('Credencelineno').asstring;
Executesql(AdoQry_tmp1,sqltext,0);
lastBalance:=AdoQry_tmp1.fieldbyname('endBalance').asfloat;
lastfBalance:=AdoQry_tmp1.fieldbyname('endfBalance').asfloat;
lastAmount:=AdoQry_tmp1.fieldbyname('endAmount').asfloat;
AdoQry_tmp.Next;
end;
sqltext:=' update #tmpResult'
+' set endPrice=case when endAmount=0 then 0 else endBalance/endAmount end, '
+' endfPrice=case when endAmount=0 then 0 else endfBalance/endAmount end '
+' where Credencedate<>'' 期初'' ';
Executesql(AdoQry_tmp,sqltext,1);
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//*********************************************************************
sqltext:='select * into #ress from #tmpResult '
+' union '
+' select 0,0,1,convert(varchAr,Credencedate,102)+''小计'', '
+' '' '', '
+' '' '', '
+ ' '' '', '
+' '' '', '
+' '' '', '
+' '' '', '
+' null, '
+' sum(jAmount),'
+' sum(dAmount),'
+' null, '
+' null, '
+' null, '
+' null, '
+' sum(debitBalance), '
+' sum(fdebitBalance), '
+ ' sum(creditBalance), '
+' sum(fcreditBalance), '
+ ' '' '', '
+ ' null, '
+ ' null, '
+ ' null, '
+ ' null, '
+ ' null '
+ ' from #tmpResult '
+ ' where Credencedate not like '' 期初%'' '
+ ' group by Credencedate '
+ ' union '
+ ' select 0,0,2,substring(Credencedate,1,7)+''合计'' as dd, '
+' '' '', '
+' '' '', '
+ ' '' '', '
+' '' '', '
+' '' '', '
+' '' '', '
+' null, '
+' sum(jAmount),'
+' sum(dAmount),'
+' null, '
+' null, '
+' null, '
+' null, '
+' sum(debitBalance), '
+' sum(fdebitBalance), '
+ ' sum(creditBalance), '
+' sum(fcreditBalance), '
+ ' '' '', '
+ ' null, '
+ ' null, '
+ ' null, '
+ ' null, '
+ ' null '
+ ' from #tmpResult '
+ ' where Credencedate not like '' 期初%'' '
+ ' group by substring(Credencedate,1,7)+''合计'' '
+ ' union '
+ ' select 0,0,3,substring(Credencedate,1,4)+''合计'' as dd, '
+' '' '', '
+' '' '', '
+ ' '' '', '
+' '' '', '
+' '' '', '
+' '' '', '
+' null, '
+' sum(jAmount),'
+' sum(dAmount),'
+' null, '
+' null, '
+' null, '
+' null, '
+' sum(debitBalance), '
+' sum(fdebitBalance), '
+ ' sum(creditBalance), '
+' sum(fcreditBalance), '
+ ' '' '', '
+ ' null, '
+ ' null, '
+ ' null, '
+ ' null, '
+ ' null '
+ ' from #tmpResult '
+ ' where Credencedate not like '' 期初%'' '
+ ' group by substring(Credencedate,1,4)+''合计'' ' ;
Executesql(AdoQry_Main,sqltext,1);
//Executesql(AdoQry_Main,'select * from #ress Order by Credencedate',0);
Executesql(AdoQry_tmp,'select * from #ress where not (Credenceid=0 and Credencelineno=1) Order by Credencedate ',0);
lastBalance:=0;
lastfBalance:=0;
lastAmount:=0;
while not AdoQry_tmp.Eof do
begin
if (AdoQry_tmp.fieldbyname('Credenceid').asinteger=0) and (AdoQry_tmp.fieldbyname('Credencelineno').asinteger=0) then
begin
sqltext:='update #ress '
+' set endBalance='+floattostr(lastBalance)+','
+' endfBalance='+floattostr(lastBalance)+','
+' endAmount='+floattostr(lastAmount)
+' where Credenceid='+AdoQry_tmp.fieldbyname('Credenceid').asstring
+' and Credencelineno='+AdoQry_tmp.fieldbyname('Credencelineno').asstring
+' and flag='+AdoQry_tmp.fieldbyname('flag').asstring
+' and Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring);
Executesql(AdoQry_Main,sqltext,1);
end
else
begin
lastBalance:=AdoQry_tmp.fieldbyname('endBalance').asfloat;
lastfBalance:=AdoQry_tmp.fieldbyname('endfBalance').asfloat;
lastAmount:=AdoQry_tmp.fieldbyname('endAmount').asfloat;
end;
AdoQry_tmp.Next;
end;
Executesql(AdoQry_tmp,'select * from Gl_AccountSubject where kmCode='+quotedstr(Trim(edt_kmCode.text)),0);
kmproperty:=AdoQry_tmp.fieldbyname('kmproperty').asinteger;
sqltext:='update #ress '
+' set Balancedirection=case when '+inttostr(kmproperty) +'=1 then (case when endBalance>0 then 1 else 2 end ) '
+' when '+inttostr(kmproperty) +'=2 then (case when endBalance>0 then 2 else 1 end ) end ';
Executesql(AdoQry_tmp,sqltext,1) ;
Executesql(AdoQry_Main,'select * from #ress Order by Credencedate',0);
activecontrol:=dbgrideh;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -