📄 gl_qry_endsubjectbalance.pas
字号:
//--成本类借贷方
{ sqltext:='select sum( case when BalanceDirection=1 then isnull(FirstBalance,0) else 0 end ) as ff,'
+' sum( case when BalanceDirection=2 then isnull(FirstBalance,0) else 0 end ) as gg'
+' from Gl_AccountSubjectBalance'
+' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
+' and Gl_AccountSubject.kmlevel=1 and kmType=4)';
Executesql(AdoQry_tmp,sqltext,0);
Balance40:=AdoQry_tmp.fieldbyname('ff').asfloat;
Balance41:=AdoQry_tmp.fieldbyname('gg').asfloat;
}
SqlText := ' Select JAmount=Sum(Case When endBalanceDirection=1 And KmProperty=1 Then endBalance '
+' When endBalanceDirection=2 And KmProperty=1 Then -endBalance '
+' When endBalanceDirection=2 And KmProperty=2 Then -endBalance '
+' When endBalanceDirection=1 And KmProperty=2 Then endBalance '
+ ' end ) '
+' From Gl_AccountSubjectBalance '
+' Join Gl_AccountSubject ON Gl_AccountSubjectBalance.KMID=Gl_AccountSubject.KMId '
+' where Gl_AccountSubjectBalance.kmid in (Select KmID '
+' From Gl_AccountSubject '
+' Where endkm=1 And kmType in (4)) '
+' and AccountPeriodYear = '+IntToStr(StArtYear)
+' and AccountPeriodMonth = '+IntToStr(StArtMonth);
ExecuteSql(AdoQry_Tmp,SqlText,0);
Balance40 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;
//-----------------------------------------------------------------------
//------------------------------------------------------------------------
//--损益类借贷方
{ sqltext:='select sum( case when BalanceDirection=1 then isnull(FirstBalance,0) else 0 end ) as ff,'
+' sum( case when BalanceDirection=2 then isnull(FirstBalance,0) else 0 end ) as gg'
+' from Gl_AccountSubjectBalance'
+' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
+' and Gl_AccountSubject.kmlevel=1 and kmType=5)';
Executesql(AdoQry_tmp,sqltext,0);
Balance50:=AdoQry_tmp.fieldbyname('ff').asfloat;
Balance51:=AdoQry_tmp.fieldbyname('gg').asfloat;
}
SqlText := ' Select JAmount=Sum(Case When endBalanceDirection=1 And KmProperty=1 Then -endBalance '
+' When endBalanceDirection=2 And KmProperty=1 Then endBalance '
+' When endBalanceDirection=2 And KmProperty=2 Then endBalance '
+' When endBalanceDirection=1 And KmProperty=2 Then -endBalance '
+ ' end ) '
+' From Gl_AccountSubjectBalance '
+' Join Gl_AccountSubject ON Gl_AccountSubjectBalance.KMID=Gl_AccountSubject.KMId '
+' where Gl_AccountSubjectBalance.kmid in (Select KmID '
+' From Gl_AccountSubject '
+' Where endkm=1 And kmType in (5)) '
+' and AccountPeriodYear = '+IntToStr(StArtYear)
+' and AccountPeriodMonth = '+IntToStr(StArtMonth);
ExecuteSql(AdoQry_Tmp,SqlText,0);
Balance50 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;
//-----------------------------------------------------------------------
// if Balance10+Balance20+Balance30+Balance40+Balance50<>Balance11+Balance21+Balance31+Balance41+Balance51 then
If abs((Balance10+Balance40)-(Balance20+Balance30+Balance50))>0.000001 then
conclusion := '期末余额试算不平衡！'
else Conclusion := '期末余额试算平衡！';
try
Frm_Gl_Enter_FirstSubjectBalance_TryBalance:=TFrm_Gl_Enter_FirstSubjectBalance_TryBalance.Create(self);
with Frm_Gl_Enter_FirstSubjectBalance_TryBalance do
begin
lbl_10.Caption:=floattostr(Balance10);
// lbl_11.Caption:=floattostr(Balance11);
lbl_20.Caption:=floattostr(Balance20);
// lbl_21.Caption:=floattostr(Balance21);
lbl_30.Caption:=floattostr(Balance30);
// lbl_31.Caption:=floattostr(Balance31);
lbl_40.Caption:=floattostr(Balance40);
// lbl_41.Caption:=floattostr(Balance41);
lbl_50.Caption:=floattostr(Balance50);
// lbl_51.Caption:=floattostr(Balance51);
lbl_Total0.Caption:=floattostr(Balance10+Balance40);
lbl_Total1.Caption:=floattostr(Balance20+Balance30+Balance50);
lbl_Summery.Caption:=conclusion;
showmodal;
end;
finally
Frm_Gl_Enter_FirstSubjectBalance_TryBalance.Free;
end;
// DispInfo('期末余额试算平衡！',3);
// conclusion:='所有科目借方期初余额与贷方期初余额不平衡！';
{ sqltext:='select sum(isnull(FirstBalance,0)) as ff from Gl_AccountSubjectBalance'
+' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
+' and kmproperty=1 and kmType=1 and kmlevel=1)';
Executesql(AdoQry_tmp,sqltext,0);
yue1:=AdoQry_tmp.fieldbyname('ff').asfloat;
sqltext:='select sum(isnull(FirstBalance,0)) as ff from Gl_AccountSubjectBalance'
+' join Gl_AccountSubject on Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid'
+' and kmType<>1 and kmlevel=1';
Executesql(AdoQry_tmp,sqltext,0);
yue2:=AdoQry_tmp.fieldbyname('ff').asfloat;
if yue1<>yue2 then
begin
if conclusion='' then
conclusion:='资产和所有损益不平衡！';
// DispInfo('资产和所有损益不平衡！',3);
end;
}
//DispInfo('试算平衡！',3);
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.sumnext(kmid:integer);
function existslowlevel(kmid:integer):boolean;
var Qry:TAdoQuery;
begin
Result:=False;
try
Qry:=TAdoQuery.Create(self);
Qry.Connection:=dbconnect;
Executesql(Qry,'select * from Gl_AccountSubject where kmid='+inttostr(kmid),0);
kmCode:=Qry.fieldbyname('kmCode').asstring;
kmlevel:=Qry.fieldbyname('kmlevel').asinteger;
KmProperty := Qry.fieldbyname('KmProperty').AsInteger;
Executesql(Qry,'select * from Gl_AccountSubject where endkm<>1 and kmlevel='+inttostr(kmlevel+1)+' and kmCode like '+quotedstr(kmCode+'%'),0);
if Qry.RecordCount>0 then Result:=True;
finally
Qry.Free;
end;
end;
var sqltext:string;
AdoQry:TAdoQuery;
AdoQry1:TAdoQuery;
begin
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection:=dbconnect;
AdoQry1:=TAdoQuery.Create(self);
AdoQry1.Connection:=dbconnect;
try
if not existslowlevel(kmid) then
begin
Executesql(AdoQry1,'select sum(case when BalanceDirection=1 then isnull(FirstBalance,0) '
+' else 0 '
+' end ) as DBalance,'
+' Sum( case when BalanceDirection=2 then isnull(FirstBalance,0) '
+' else 0 '
+' end '
+' ) as CBalance,'
+' case when sum(case when BalanceDirection=1 then isnull(FirstBalance,0) '
+' else 0 '
+' end )'
+' -Sum( case when BalanceDirection=2 then isnull(FirstBalance,0) '
+' else 0 '
+' end '
+' ) >0 then 1 '
+' when sum(case when BalanceDirection=1 then isnull(FirstBalance,0) '
+' else 0 '
+' end )'
+' -Sum( case when BalanceDirection=2 then isnull(FirstBalance,0) '
+' else 0 '
+' end '
+' )<0 then 2 end as BalanceDirection,'
+' sum(case when BalanceDirection=1 then isnull(FirstfBalance,0) '
+' else 0 '
+' end ) as FDBalance,'
+' Sum( case when BalanceDirection=2 then isnull(FirstfBalance,0) '
+' else 0 '
+' end '
+' ) as FCBalalnce '
// +' sum(isnull(FirstfBalance,0)) as fFirstBalance '
+' from Gl_AccountSubjectBalance t1 '
+' join Gl_AccountSubject t2 on t1.kmid=t2.kmid '
+' and kmlevel='+inttostr(kmlevel+1)
+' and kmCode like '+quotedstr(kmCode+'%'),0);
sqltext:='update Gl_AccountSubjectBalance '
+' set FirstBalance=abs('+AdoQry1.fieldbyname('DBalance').asstring+'-('+AdoQry1.fieldbyname('CBalance').asstring+')),'
+' FirstfBalance=abs('+AdoQry1.fieldbyname('DBalance').asstring+'-('+AdoQry1.fieldbyname('CBalance').asstring+'))/FCurrencyRate, '
+' BalanceDirection = '+IIFString(AdoQry1.fieldbyname('BalanceDirection').AsString='','BalanceDirection',AdoQry1.fieldbyname('BalanceDirection').AsString)
+' where kmid='+inttostr(kmid);
Executesql(AdoQry,sqltext,1);
end
else
begin
Executesql(AdoQry,'select kmid from Gl_AccountSubject where endkm<>1 and kmlevel='+inttostr(kmlevel+1)+' and kmCode like '+quotedstr(kmCode+'%'),0);
while not AdoQry.Eof do
begin
sumnext(AdoQry.fieldbyname('kmid').asinteger);
getkmInfo(kmid);
Executesql(AdoQry1,'select sum(case when BalanceDirection=1 then isnull(FirstBalance,0) '
+' else 0 '
+' end ) as DBalance,'
+' Sum( case when BalanceDirection=2 then isnull(FirstBalance,0) '
+' else 0 '
+' end '
+' ) as CBalance,'
+' case when sum(case when BalanceDirection=1 then isnull(FirstBalance,0) '
+' else 0 '
+' end )'
+' -Sum( case when BalanceDirection=2 then isnull(FirstBalance,0) '
+' else 0 '
+' end '
+' ) >0 then 1 '
+' when sum(case when BalanceDirection=1 then isnull(FirstBalance,0) '
+' else 0 '
+' end )'
+' -Sum( case when BalanceDirection=2 then isnull(FirstBalance,0) '
+' else 0 '
+' end '
+' )<0 then 2 end as BalanceDirection,'
+' sum(case when BalanceDirection=1 then isnull(FirstfBalance,0) '
+' else 0 '
+' end ) as FDBalance,'
+' Sum( case when BalanceDirection=2 then isnull(FirstfBalance,0) '
+' else 0 '
+' end '
+' ) as FCBalalnce '
// +' sum(isnull(FirstfBalance,0)) as fFirstBalance '
+' from Gl_AccountSubjectBalance t1 '
+' join Gl_AccountSubject t2 on t1.kmid=t2.kmid '
+' and kmlevel='+inttostr(kmlevel+1)
+' and kmCode like '+quotedstr(kmCode+'%'),0);
sqltext:='update Gl_AccountSubjectBalance '
+' set FirstBalance=abs('+AdoQry1.fieldbyname('DBalance').asstring+'-('+AdoQry1.fieldbyname('CBalance').asstring+')),'
+' FirstfBalance=abs('+AdoQry1.fieldbyname('DBalance').asstring+'-('+AdoQry1.fieldbyname('CBalance').asstring+'))/FCurrencyRate, '
+' BalanceDirection = '+IIFString(AdoQry1.fieldbyname('BalanceDirection').AsString='','BalanceDirection',AdoQry1.fieldbyname('BalanceDirection').AsString)
+' where kmid='+inttostr(kmid);
Executesql(AdoQry1,sqltext,1);
AdoQry.Next;
end;
end;
finally
AdoQry.Free;
AdoQry1.Free;
end;
end;
function TFrm_Gl_Qry_EndSubjectBalance.gatherlowlevel:boolean;
var sqltext:string;
BookmArk:string;
begin
Result:=False;
BookmArk:=AdoQry_Main.BookmArk;
sqltext:='select * from Gl_AccountSubjectBalance'
+' where exists(select * from Gl_AccountSubject where Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid and Gl_AccountSubject.kmlevel=1 and endkm<>1)';
Executesql(AdoQry_tmp,sqltext,0) ;
while not AdoQry_tmp.Eof do
begin
sumnext(AdoQry_tmp.fieldbyname('kmid').asinteger);
AdoQry_tmp.Next;
end;
try
Executesql(AdoQry_tmp,'drop table #FirstBalance1',1);
except
end;
initform(dbconnect,False);
Result:=True;
AdoQry_Main.BookmArk:=BookmArk;
end;
function TFrm_Gl_Qry_EndSubjectBalance.getuseAccount:boolean;
var sqltext:string;
begin
Result:=False;
sqltext:='select * from Gl_Param'
+' where ParamCode=''StartUseAccount'' ';
Executesql(AdoQry_tmp,sqltext,0);
if AdoQry_tmp.fieldbyname('ParamValuen').asinteger=1 then
Result:=True;
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.FormCreate(Sender: TObject);
begin
inherited;
toolbutton6.Action:=act_look;
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.Act_FilterExecute(Sender: TObject);
begin
If Not AdoQry_Main.Active then Abort;
inherited;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -