gl_qry_subjectbalance.pas

一个MRPII系统源代码版本

unit Gl_Qry_SubjectBalance;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin;

Type
  TFrm_Gl_Qry_SubjectBalance = Class(TFrm_Base_Qry)
    AdoQry_MainkmCode: TStringField;
    AdoQry_MainkmName: TStringField;
    AdoQry_MaindebitFirstBalance: TFloatField;
    AdoQry_MaincreditFirstBalance: TFloatField;
    AdoQry_MainthisdebitBalance: TFloatField;
    AdoQry_MainthiScreditBalance: TFloatField;
    AdoQry_MainYeardebitBalance: TFloatField;
    AdoQry_MainYearcreditBalance: TFloatField;
    AdoQry_MainenddebitBalance: TFloatField;
    AdoQry_MainendcreditBalance: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Gl_Qry_SubjectBalance: TFrm_Gl_Qry_SubjectBalance;

implementation
uses Sys_Global,Gl_Qry_SubjectBalance_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_SubjectBalance.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Month1,Month2:string;
    yy,mm:integer;
    kmlevel:integer;
    kmCode1,kmCode2:string;
    currencyCode:string;
    index:integer;
    cre:string;
begin
Application.ProcessMessages;
inherited;
    Month1:='';
    Month2:='';
    kmlevel:=0;
    yy:=0;mm:=0;
    kmCode1:='';
    kmCode2:='';
    index:=0;
    currencyCode:='';
 with TFrm_Gl_Qry_SubjectBalance_C.Create(self) do
  begin
    try
    AdoQry_tmp.Connection:=dbconnect;
    showmodal;
    if modalResult=mrok then
     begin
        Month1:=medts.Text;
        Month2:=medte.text;
        kmlevel:=strtoint(Trim(edt_kmlevel.text));
        kmCode1:=Trim(edt_kmCode1.text);
        kmCode2:=Trim(edt_kmCode2.text);
        currencyCode:=getCode(cmb_Currency.text);
        index:=cmb_Currency.ItemIndex;
        cre:=cmb_Currency.Text;
     end;
    finally
     Frm_Gl_Qry_SubjectBalance_C.Free;
     Frm_Gl_Qry_SubjectBalance_C:=nil;
    end;
  end;
  if (Month1='') or (Month2='') then abort;
  yy:=strtoint(copy(Month1,1,4));
  mm:=strtoint(copy(Month1,6,2));
  sqltext:='select * from Gl_AccountSubjectBalance'
          +' where AccountperiodYear='+inttostr(yy)
          +'   and AccountperiodMonth>='+inttostr(mm)
          +'   and AccountperiodMonth<='+copy(Month2,6,2)
          +'  Order by AccountperiodMonth ';
 Executesql(AdoQry_tmp,sqltext,0)  ;
 mm:=AdoQry_tmp.fieldbyname('AccountperiodMonth').asinteger;
  if index<=1 then
sqltext:=' select t1.kmCode,'
         +'          t1.kmName,'
         +'          t1.kmproperty,'
         +'          t1.kmlevel, '
         +'          sum(case when t2.AccountperiodYear='+inttostr(yy)+' and t2.AccountperiodMonth='+inttostr(mm)+' then     '
         +'               (case when t2.Balancedirection=1 then FirstBalance    '
         +'                else 0       '
         +'                end)         '
         +'          else 0             '
         +'          end)               '
         +'          as debitFirstBalance, '
         +'          sum(case when t2.AccountperiodYear='+inttostr(yy)+' and t2.AccountperiodMonth='+inttostr(mm)+' then  '
         +'               (case when t2.Balancedirection=2 then FirstBalance  '
         +'                else 0          '
         +'                end)            '
         +'          else 0                '
         +'          end)                  '
         +'          as creditFirstBalance, '
         +'          sum(case when AccountperiodMonth<='+copy(Month1,6,2)+' and AccountperiodMonth>='+copy(Month2,6,2) 
         +'                    then debitBalance          '
         +'              else 0 end) as thisdebitBalance, '
         +'          sum(case when AccountperiodMonth<='+copy(Month1,6,2)+' and AccountperiodMonth>='+copy(Month2,6,2) 
         +'                    then creditBalance              '
         +'              else 0 end) as thiScreditBalance,     '
         +'          sum(debitBalance) as YeardebitBalance,    '
         +'          sum(creditBalance) as YearcreditBalance   '
         +'   into #tmpf1                                      '
         +'   from  Gl_AccountSubject t1                       '
         +'   join  Gl_AccountSubjectBalance t2 on t1.kmid=t2.kmid   '
         +'    and  t2.AccountperiodYear='+inttostr(yy)
          +' where  t1.kmlevel<='+inttostr(kmlevel)
          +'   and  t1.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2)
          +iifstring(index=0,' ',' and Gl_AccountSubject.fcurrecycalculate=0')
         +'   group by t1.kmCode,t1.kmName,t1.kmproperty,t1.kmlevel             '
 else 
sqltext:=' select t1.kmCode,'
         +'          t1.kmName,'
         +'          t1.kmproperty,'
         +'          t1.kmlevel,   '
         +'          sum(case when t2.AccountperiodYear='+inttostr(yy)+' and t2.AccountperiodMonth='+inttostr(mm)+' then     '
         +'               (case when t2.Balancedirection=1 then FirstfBalance    '
         +'                else 0       '
         +'                end)         '
         +'          else 0             '
         +'          end)               '
         +'          as debitFirstBalance, '
         +'          sum(case when t2.AccountperiodYear='+inttostr(yy)+' and t2.AccountperiodMonth='+inttostr(mm)+' then  '
         +'               (case when t2.Balancedirection=2 then FirstfBalance  '
         +'                else 0          '
         +'                end)            '
         +'          else 0                '
         +'          end)                  '
         +'          as creditFirstBalance, '
         +'          sum(case when AccountperiodMonth<='+copy(Month1,6,2)+' and AccountperiodMonth>='+copy(Month2,6,2) 
         +'                    then fdebitBalance          '
         +'              else 0 end) as thisdebitBalance, '
         +'          sum(case when AccountperiodMonth<='+copy(Month1,6,2)+' and AccountperiodMonth>='+copy(Month2,6,2) 
         +'                    then fcreditBalance              '
         +'              else 0 end) as thiScreditBalance,     '
         +'          sum(fdebitBalance) as YeardebitBalance,    '
         +'          sum(fcreditBalance) as YearcreditBalance   '
         +'   into #tmpf1                                      '
         +'   from  Gl_AccountSubject t1                       '
         +'   join  Gl_AccountSubjectBalance t2 on t1.kmid=t2.kmid   '
         +'    and  t2.AccountperiodYear='+inttostr(yy)
          +' where  t1.kmlevel<='+inttostr(kmlevel)
          +'   and  t1.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2)
          +' and t1.fcurrecycalculate=1'
          +' and t1.fcurrecy='+quotedstr(currencyCode)
         +'   group by t1.kmCode,t1.kmName,t1.kmproperty,t1.kmlevel             ';
 Executesql(AdoQry_tmp,sqltext,1);
 sqltext:=' select t1.kmCode,'
          +'         t1.kmName,'
          +'         t1.kmproperty,'
          +'         t1.debitFirstBalance,'
          +'         t1.creditFirstBalance,'
          +'         t1.thisdebitBalance,   '
          +'         t1.thiScreditBalance,   '
          +'         t1.YeardebitBalance,    '
          +'         t1.YearcreditBalance,   '
          +'         case when t1.kmproperty=1 '
          +'              then t1.debitFirstBalance+t1.creditFirstBalance+t1.thisdebitBalance-t1.thiScreditBalance'
          +'         else 0                                                                                       '
          +'         end as enddebitBalance,                                                                       '
          +'         case when t1.kmproperty=2                                                                      '
          +'              then t1.debitFirstBalance+t1.creditFirstBalance+t1.thiScreditBalance-t1.thisdebitBalance   '
          +'         else 0'
          +'         end as endcreditBalance'
          +' into #tmpfff  '
          +'  from  #tmpf1  t1  '
          +' union '
          +' select  ''合计'','
          +'         '' '','
          +'         1,'
          +'         sum(t1.debitFirstBalance),'
          +'         sum(t1.creditFirstBalance),'
          +'         sum(t1.thisdebitBalance),   '
          +'         sum(t1.thiScreditBalance),   '
          +'         sum(t1.YeardebitBalance),    '
          +'         sum(t1.YearcreditBalance),   '
          +'         sum(case when t1.kmproperty=1 '
          +'              then t1.debitFirstBalance+t1.creditFirstBalance+t1.thisdebitBalance-t1.thiScreditBalance'
          +'         else 0                                                                                       '
          +'         end) as enddebitBalance,                                                                       '
          +'         sum(case when t1.kmproperty=2                                                                      '
          +'              then t1.debitFirstBalance+t1.creditFirstBalance+t1.thiScreditBalance-t1.thisdebitBalance   '
          +'         else 0'
          +'         end) as endcreditBalance'
          +'  from  #tmpf1  t1  '
          +' where t1.kmlevel=1 ';
 Executesql(AdoQry_tmp,sqltext,1);

 selectfromsql:='select * from #tmpfff';
 Orderbyfields:=' kmCode';
 Executesql(AdoQry_Main,'select * from #tmpfff Order by kmCode',0);
 lbl_Condition.Caption:='会计期间从 '+Month1+' 到 '+Month2+' /科目级长： '+inttostr(kmlevel)+' /科目代码从 '+kmCode1+' 到 '+kmCode2+' / 货币： '+cre;
 
  
end;

procedure TFrm_Gl_Qry_SubjectBalance.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_SubjectBalance:=nil;
end;

procedure TFrm_Gl_Qry_SubjectBalance.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
try
 Executesql(AdoQry_tmp,'drop table #tmpfff,#tmpf1',1);
except
end;
end;

end.