📄 gl_qry_endsubjectbalance.pas
字号:
unit Gl_Qry_EndSubjectBalance;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Outer, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
StdCtrls, ExtCtrls, ComCtrls, ToolWin, jpeg;
Type
TFrm_Gl_Qry_EndSubjectBalance = Class(TFrm_Base_Outer)
AdoQry_MainkmCode: TStringField;
AdoQry_MainkmName: TStringField;
AdoQry_MainFirstdebitBalance: TFloatField;
AdoQry_MainFirstcreditBalance: TFloatField;
AdoQry_MainFirstBalance: TFloatField;
AdoQry_Mainkmid: TAutoIncField;
AdoQry_Mainkmproperty: TIntegerField;
AdoQry_Mainendkm: TIntegerField;
AdoQry_MainBalancedirection: TIntegerField;
AdoQry_MainendBalancedirection: TIntegerField;
AdoQry_MainendBalance: TFloatField;
AdoQry_MainFirstfdebitBalance: TFloatField;
AdoQry_MainFirstfcreditBalance: TFloatField;
AdoQry_MainfFirstBalance: TFloatField;
AdoQry_MainendFBalance: TFloatField;
procedure FormDestroy(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure Act_ModifyExecute(Sender: TObject);
procedure Act_LookExecute(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Act_FilterExecute(Sender: TObject);
private
{ Private declarations }
public
procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
function SetDeleteSql:String;Override;
function gatherlowlevel:boolean;
function getuseAccount:boolean;
procedure sumnext(kmid:integer);
procedure getkmInfo(kmid:integer);
{ Public declarations }
end;
var
Frm_Gl_Qry_EndSubjectBalance: TFrm_Gl_Qry_EndSubjectBalance;
stArtYear,stArtMonth:integer;
kmCode:string; //科目代码
kmlevel:integer; //科目级次
KmProperty : Integer;//科目性质
implementation
uses Sys_Global,Gl_Qry_EndSubjectBalance_C,Gl_Enter_FirstSubjectBalance_TryBalance;
{$R *.DFM}
procedure TFrm_Gl_Qry_EndSubjectBalance.getkmInfo(kmid:integer);
var AdoQry:TAdoQuery;
begin
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection:=dbconnect;
try
Executesql(AdoQry,'select kmCode,kmlevel,KmProperty from Gl_AccountSubject where kmid='+inttostr(kmid),0);
kmCode:=AdoQry.fieldbyname('kmCode').asstring;
kmlevel:=AdoQry.fieldbyname('kmlevel').asinteger;
KmProperty := AdoQry.fieldbyname('KmProperty').AsInteger;
finally
AdoQry.Free;
end;
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
var sqltext:string;
begin
Application.ProcessMessages;
extendCaption:=False;
inherited;
Application.ProcessMessages;
Act_Modify.Enabled := False;
Act_Modify.Visible := False;
self.Caption:='期末余额查询';
StArtYear := 0;
StArtMonth := 0;
Frm_Gl_Qry_EndSubjectBalance_C := TFrm_Gl_Qry_EndSubjectBalance_C.Create(self);
Try
With Frm_Gl_Qry_EndSubjectBalance_C do begin
Caption := '条件筛选';
AdoQry_Tmp.Connection := Dbconnect;
Executesql(AdoQry_tmp,'select * from Gl_AccountParam ',0);
MEdtS.Text := FormatDateTime('yyyy.mm',EnCodeDate(AdoQry_tmp.fieldbyname('AccountuseYear').asinteger,AdoQry_tmp.fieldbyname('AccountuseMonth').asinteger,1));
If ShowModal=MrOk then
begin
StArtYear := StrToInt(Copy(MEdtS.Text,1,4));
StArtMonth := StrToInt(Copy(MEdtS.Text,6,2));
end;
end;
Finally
Frm_Gl_Qry_EndSubjectBalance_C.Free;
end;
If StArtYear=0 then Abort;
{ Executesql(AdoQry_tmp,'select * from Gl_AccountParam ',0);
stArtYear:=AdoQry_tmp.fieldbyname('AccountuseYear').asinteger;
stArtMonth:=AdoQry_tmp.fieldbyname('AccountuseMonth').asinteger;
} sqltext:='select t1.kmid, '
+' t1.kmCode, '
+' case t1.kmlevel when 1 then t1.kmName '
+' when 2 then '' ''+t1.kmName '
+' when 3 then '' ''+t1.kmName '
+' when 4 then '' ''+t1.kmName '
+' when 5 then '' ''+t1.kmName '
+' when 6 then '' ''+t1.kmName '
+' end as kmName, '
// +' t1.Fcurrecycalculate, '
+' t1.kmproperty, '
+' t1.endkm, '
+' isnull(t2.Balancedirection,t1.kmproperty) as Balancedirection,'
+' isnull(t2.endBalancedirection,t1.kmproperty) as endBalancedirection,'
+' isnull(t2.debitBalance,0) as FirstdebitBalance, '
+' isnull(t2.FdebitBalance,0) as FirstFdebitBalance, '
+' isnull(t2.creditBalance,0)as FirstcreditBalance, '
+' isnull(t2.FcreditBalance,0)as FirstFcreditBalance, '
+' isnull(t2.FirstBalance,0) as FirstBalance, '
+' isnull(t2.FirstFBalance,0) as FFirstBalance, '
+' isnull(t2.endBalance,0) as endFBalance , '
+' isnull(t2.endBalance,0) as endBalance '
+' into #FirstBalance1 '
+' from Gl_AccountSubject t1 '
+' left join Gl_AccountSubjectBalance t2 on t1.kmid=t2.kmid '
+' and t2.AccountPeriodYear='+IntToStr(StArtYear)
+' and t2.AccountPeriodMonth='+IntToStr(StArtMonth);
Executesql(AdoQry_tmp,sqltext,1) ;
selectfromsql:='select * from #FirstBalance1';
Executesql(AdoQry_Main,'select * from #FirstBalance1 Order by kmCode',0);
// Frm_Sys_Detail:=TFrm_Gl_Enter_FirstSubjectBalance_D.Create(self);
end;
function TFrm_Gl_Qry_EndSubjectBalance.SetDeleteSql:String;
begin
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Gl_Qry_EndSubjectBalance:=nil;
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
inherited;
try
Executesql(AdoQry_tmp,'drop table #FirstBalance1',1);
except
end;
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.Act_ModifyExecute(
Sender: TObject);
begin
{ if AdoQry_Main.fieldbyname('endkm').asinteger=0 then exit;
if getuseAccount then
begin
DispInfo('帐套已启用，不可修改期初余额！',3);
exit;
end;
// if AdoQry_Main.fieldbyname('Fcurrecy').asString='' then
// Frm_Sys_Detail:=TFrm_Gl_Enter_FirstSubjectBalance_D.Create(self)
// else
Frm_Sys_Detail:=TFrm_Gl_Enter_FirstSubjectBalance_D1.Create(self);
inherited; }
end;
procedure TFrm_Gl_Qry_EndSubjectBalance.Act_LookExecute(
Sender: TObject);
var sqltext:string;
yue1,yue2:double;
Balance10,Balance11:double;
Balance20,Balance21:double;
Balance30,Balance31:double;
Balance40,Balance41:double;
Balance50,Balance51:double;
conclusion:string;
begin
If Not AdoQry_Main.Active then Abort;
{ Frm_Gl_Qry_EndSubjectBalance_C := TFrm_Gl_Qry_EndSubjectBalance_C.Create(self);
With Frm_Gl_Qry_EndSubjectBalance_C do begin
Caption := '试算期末余额平衡';
AdoQry_Tmp.Connection := Dbconnect;
MEdtS.Text := FormatDateTime('yyyy.mm',EnCodeDate(StArtYear,StArtMonth,1));
MEdtS.Enabled := False;
If ShowModal<>MrOk then
begin
Frm_Gl_Qry_EndSubjectBalance_C.Free ;
Abort;
end;
end;
Frm_Gl_Qry_EndSubjectBalance_C.Free;
}
conclusion:='';
//------------------------------------------------------------------------
//--资产类借贷方
{ 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=1)';
Executesql(AdoQry_tmp,sqltext,0);
Balance10:=AdoQry_tmp.fieldbyname('ff').asfloat;
Balance11:=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 (1)) '
+' and AccountPeriodYear = '+IntToStr(StArtYear)
+' and AccountPeriodMonth = '+IntToStr(StArtMonth);
ExecuteSql(AdoQry_Tmp,SqlText,0);
Balance10 := 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=2)';
Executesql(AdoQry_tmp,sqltext,0);
Balance20:=AdoQry_tmp.fieldbyname('ff').asfloat;
Balance21:=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 (2)) '
+' and AccountPeriodYear = '+IntToStr(StArtYear)
+' and AccountPeriodMonth = '+IntToStr(StArtMonth);
ExecuteSql(AdoQry_Tmp,SqlText,0);
Balance20 := 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=3)';
Executesql(AdoQry_tmp,sqltext,0);
Balance30:=AdoQry_tmp.fieldbyname('ff').asfloat;
Balance31:=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 (3)) '
+' and AccountPeriodYear = '+IntToStr(StArtYear)
+' and AccountPeriodMonth = '+IntToStr(StArtMonth);
ExecuteSql(AdoQry_Tmp,SqlText,0);
Balance30 := AdoQry_Tmp.fieldbyname('JAmount').AsFloat;
//-----------------------------------------------------------------------
//------------------------------------------------------------------------
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -