📄 gl_qry_balancesheet.pas
字号:
unit Gl_Qry_BalanceSheet;
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_Gl_Qry_BalanceSheet = Class(TFrm_Base_Qry)
AdoQry_MainprojectCode: TStringField;
AdoQry_Mainassetproject: TStringField;
AdoQry_Mainplineno: TIntegerField;
AdoQry_MainFirstBalance: TFloatField;
AdoQry_MainlastBalance: TFloatField;
AdoQry_Mainowesprossessoryproject: TStringField;
AdoQry_Mainplineno1: TIntegerField;
AdoQry_MainFirstBalance1: TFloatField;
AdoQry_MainlastBalance1: TFloatField;
procedure FormDestroy(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
private
{ Private declarations }
public
procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
procedure initReport;Override;
{ Public declarations }
end;
function getmin(Value1,Value2:integer):integer;
var
Frm_Gl_Qry_BalanceSheet: TFrm_Gl_Qry_BalanceSheet;
implementation
uses Gl_Qry_BalanceSheet_C,Sys_Global;
{$R *.DFM}
function getmin(Value1,Value2:integer):integer;
begin
Result:=Value1;
if (Value1=0) and (Value2=0) then Result:=0
else
if Value1=0 then Result:=Value2
else
if Value2=0 then Result:=Value1
else
if (Value1<Value2) then Result:=Value1
else
if (Value2<Value1) then Result:=Value2;
end;
procedure TFrm_Gl_Qry_BalanceSheet.initReport;
begin
inherited;
ExtprintReport.Subtitle2:='编制单位:Ext';
ExtprintReport.Subtitle3:=lbl_Condition.Caption;
// ExtprintReport.Subtitle4:='单位:元';
end;
procedure TFrm_Gl_Qry_BalanceSheet.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
Month1,Month2:string;
Month:string;
AdoQry:TAdoQuery;
tmpproject:string;
Apos,dpos:integer;
astring,dstring:string;
pretext:string;
begin
tmpproject:='';
astring:='';
dstring:='';
Application.ProcessMessages;
inherited;
Month1:='1800.01';
Month2:='1800.01';
try
with TFrm_Gl_Qry_BalanceSheet_C.Create(self) do
begin
AdoQry_tmp.Connection:=dbconnect;
showmodal;
if modalResult=mrok then
begin
Month1:=medts.Text;
Month2:=medte.Text;
end;
end;
finally
Frm_Gl_Qry_BalanceSheet_C.Free;
end;
if (Month1='1800.01') and (Month2='1800.01') then
abort;
Month:=Month2;
// Executesql(AdoQry_tmp,'select dateAdd(mm,1,'+quotedstr(Month2+'.01')+') as dd',0);
// Month2:=AdoQry_tmp.fieldbyname('dd').asstring;
AdoQry:=TAdoQuery.Create(self);
AdoQry.Connection:=dbconnect;
AdoQry.EnableBCD:=False;
sqltext:='select Gl_AccountSubject.kmCode,Gl_AccountSubject.Kmproperty, '
+' Gl_AccountSubjectBalance.* '
+' into #tMpSubjectBalance '
+' from Gl_AccountSubjectBalance '
+' join Gl_AccountSubject on Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid ';
Executesql(AdoQry_tmp,sqltext,1);
sqltext:=' select t1.projectCode, '
+' t1.assetproject, '
+' t1.plineno, '
+' t1.IsSum , '
+' t1.Firstproject, '
+' convert(decimal(20,8),0) as FirstBalance,'
+' t1.lastproject, '
+' convert(decimal(20,8),0) as lastBalance, '
+' t1.owesprossessoryproject, '
+' t1.plineno1, '
+' t1.Firstproject1, '
+' convert(decimal(20,8),0) as FirstBalance1,'
+' t1.lastproject1 , '
+' convert(decimal(20,8),0) as lastBalance1 '
+' into #tMpSheet '
+' from Gl_BalanceSheet t1 ';
Executesql(AdoQry_tmp,sqltext,1);
Executesql(AdoQry_tmp,'select * from #tMpSheet '
+' Order by IsSum,ProjectCode ',0) ;
while not AdoQry_tmp.Eof do
begin
//期初数
pretext:='+';
astring:=quotedstr('');
dstring:=quotedstr('');
tmpproject:=AdoQry_tmp.fieldbyname('Firstproject').asstring;
while tmpproject<>'' do
begin
// showmessage(tmpproject);
Apos:=pos('+',tmpproject);
dpos:=pos('-',tmpproject);
if (Apos=0) and (dpos=0) then
begin
if pretext='+' then
astring:=astring+iifstring(astring='','',',')+quotedstr(tmpproject)
else
dstring:=dstring+iifstring(dstring='','',',')+quotedstr(tmpproject);
tmpproject:='';
continue;
end;
if pretext='+' then
astring:=astring+iifstring(astring='','',',')+quotedstr(copy(tmpproject,1,getmin(Apos,dpos)-1))
else
dstring:=dstring+iifstring(dstring='','',',')+quotedstr(copy(tmpproject,1,getmin(Apos,dpos)-1));
if ((Apos=getmin(Apos,dpos)) and (Apos<>0) ) or (dpos=0) then
begin
pretext:='+' ;
tmpproject:=copy(tmpproject,Apos+1,length(tmpproject)-Apos);
end
else
begin
pretext:='-' ;
tmpproject:=copy(tmpproject,dpos+1,length(tmpproject)-dpos);
end;
end;
If AdoQry_Tmp.fieldbyname('IsSum').AsInteger=0 then
sqltext:='update #tMpSheet'
+' set FirstBalance=isnull((select sum(isnull(FirstBalance,0)) '
+' from #tMpSubjectBalance '
+' where #tMpSubjectBalance.AccountperiodYear='+copy(Month1,1,4)
+' and #tMpSubjectBalance.AccountperiodMonth='+copy(Month1,6,2)
+' and #tMpSubjectBalance.kmCode in ('+astring+')),0.0) '
+' -isnull((select sum(isnull(FirstBalance,0)) '
+' from #tMpSubjectBalance '
+' where #tMpSubjectBalance.AccountperiodYear='+copy(Month1,1,4)
+' and #tMpSubjectBalance.AccountperiodMonth='+copy(Month1,6,2)
+' and #tMpSubjectBalance.kmCode in ('+dstring+')),0.0)'
+' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring)
else
SqlText := 'update #TMpSheet '
+' set FirstBalance=(select Sum(IsNull(FirstBalance,0.0)) from #TMpSheet '
+' where #TMpSheet.ProjectCode in ('+astring+'))'
+' -(select Sum(IsNull(FirstBalance,0.0)) from #TMpSheet '
+' where #TMpSheet.ProjectCode in ('+dstring+'))'
+' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring);
// showmessage(sqltext);
Executesql(AdoQry,sqltext,1) ;
//期末数
pretext:='+';
astring:=quotedstr('');
dstring:=quotedstr('');
tmpproject:=AdoQry_tmp.fieldbyname('lastproject').asstring;
while tmpproject<>'' do
begin
Apos:=pos('+',tmpproject);
dpos:=pos('-',tmpproject);
if (Apos=0) and (dpos=0) then
begin
if pretext='+' then
astring:=astring+iifstring(astring='','',',')+quotedstr(tmpproject)
else
dstring:=dstring+iifstring(dstring='','',',')+quotedstr(tmpproject);
tmpproject:='';
continue;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -