gl_qry_accountbook5.pas

一个MRPII系统源代码版本

unit Gl_Qry_AccountBook5;

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_AccountBook5 = Class(TFrm_Base_Qry)
    AdoQry_tmp1: TAdoQuery;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_FilterExecute(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
  private
    { Private declarations }
  public
    kmproperty:integer;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Gl_Qry_AccountBook5: TFrm_Gl_Qry_AccountBook5;

implementation
uses Sys_Global,Gl_Qry_AccountBook5_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_AccountBook5.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string; 
    Month1,Month2:string;
    kmCode,tmpkmCode:string;
    Month:string;
    AccountBookCode:string;
    cre:string;
    stArtYear,stArtMonth:integer;
    lastBalance:double;
    Firstdirection:string;
    i:integer;
begin
Application.ProcessMessages;
inherited;
    Month1:='';
    Month2:='';
    AccountBookCode:='';
    lastBalance:=0;
    AdoQry_tmp1.Connection:=dbconnect;
 with TFrm_Gl_Qry_AccountBook5_C.Create(self) do
  begin
    try
    AdoQry_tmp.Connection:=dbconnect;
    showmodal;
    if modalResult=mrok then
     begin
        Month1:=medts.Text;
        Month2:=medte.Text;
        AccountBookCode:=getCode(cmb_MoreAccountBook.text);
        cre:=cmb_MoreAccountBook.Text;
        if cre='' then 
         begin
          DispInfo('请选择多栏账！',3);
          abort;
         end;
     end;
    finally
     Frm_Gl_Qry_AccountBook5_C.Free;
     Frm_Gl_Qry_AccountBook5_C:=nil;
    end;
  end;
  if (Month1='') and (Month2='') then abort;
//************************************************************************
//取出期初年度月份
    Executesql(AdoQry_tmp,'select * from Gl_AccountParam',0);
    stArtYear:=AdoQry_tmp.fieldbyname('AccountuseYear').asinteger;
    stArtMonth:=AdoQry_tmp.fieldbyname('AccountuseMonth').asinteger;
//***********************************************************************
  Month:=Month2;
  Executesql(AdoQry_tmp,'select dateAdd(mm,1,'+quotedstr(iifstring(Month2='','1900.01',Month2)+'.01')+') as dd',0);
  Month2:=AdoQry_tmp.fieldbyname('dd').asstring;
//************************************************************************
//取出多栏式帐簿科目代码及其对应科目代码
  Executesql(AdoQry_tmp,'select t1.*,t2.kmproperty from Gl_MoreAccountBook t1 '
                       +' join Gl_AccountSubject t2 on t1.kmCode=t2.kmCode where AccountBookCode='+quotedstr(AccountBookCode),0);
  kmCode:=AdoQry_tmp.fieldbyname('kmCode').asstring;
  kmproperty:=AdoQry_tmp.fieldbyname('kmproperty').asinteger;
  sqltext:='select t1.*,t2.kmid from Gl_MoreAccountBookLine t1'
          +' join Gl_AccountSubject t2 on t1.kmCode=t2.kmCode '
          +' where AccountBookCode='+quotedstr(AccountBookCode)
          +'   and t1.kmCode<>'+quotedstr(kmCode);
  Executesql(AdoQry_tmp,sqltext,0);
//************************************************************************  
//*************************************************************************************************************
//列示逐日会计账分录
  sqltext:=' select 0 as idflag,0 as lineflag,convert(varchAr,t1.Credencedate,102) as Credencedate ,   '
         +'        t1.Characterno+''_''+convert(varchAr,t1.Credenceno) as CredencenoandCode ';
   AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',        sum(case when kmCode='+quotedstr(AdoQry_tmp.fieldbyname('kmCode').asstring)+' then t2.debitAmount else 0 end) as jkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
     sqltext:=sqltext+', sum(t2.debitAmount) as debitTotal ';
     AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',          sum(case when kmCode='+quotedstr(AdoQry_tmp.fieldbyname('kmCode').asstring)+' then t2.creditAmount else 0 end) as dkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
    sqltext:=sqltext+' , sum(t2.creditAmount) as creditTotal,  ';              
    sqltext:=sqltext+'        ''1        ''  as Balancedirection,          '
                    + '        0.0 as Balance                 '
                    +' into #tmPMoreAccountQry  '
                    + ' from Gl_Credenceline t2               '
                    + ' join Gl_Credence t1 on t2.Credenceid=t1.Credenceid    '
                    + ' where kmCode like '+quotedstr(kmCode+'%')   
                    +' and    t1.Credencedate >='+quotedstr(iifstring(Month1='','1900.01',Month1)+'.01')
                    +' and    t1.Credencedate <'+quotedstr(Month2)
                    + ' group by t1.Credencedate,t1.Characterno,t1.Credenceno ';
//*************************************************************************************************************
//*************************************************************************************************************
//计算本期合计
  sqltext:=sqltext+' union '
           +' select 0,1,substring(convert(varchAr,t1.Credencedate,102),1,7)+''合计'' as dd,    '
         +'          ''      '' as CredencenoandCode ';
   AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',        sum(case when kmCode='+quotedstr(AdoQry_tmp.fieldbyname('kmCode').asstring)+' then t2.debitAmount else 0 end) as jkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
     sqltext:=sqltext+', sum(t2.debitAmount) as debitTotal ';
    AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',          sum(case when kmCode='+quotedstr(AdoQry_tmp.fieldbyname('kmCode').asstring)+' then t2.creditAmount else 0 end) as dkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
    sqltext:=sqltext+' , sum(t2.creditAmount) as creditTotal,  ';              
    sqltext:=sqltext+'        ''1       '' as Balancedirection,          '
                    + '        0.0 as Balance                 '
                    + ' from Gl_Credenceline t2               '
                    + ' join Gl_Credence t1 on t2.Credenceid=t1.Credenceid    '
                    + ' where kmCode like '+quotedstr(kmCode+'%')   
                    +' and    t1.Credencedate >='+quotedstr(iifstring(Month1='','1900.01',Month1)+'.01')
                    +' and    t1.Credencedate <'+quotedstr(Month2)
                    + ' group by substring(convert(varchAr,t1.Credencedate,102),1,7)+''合计'' ';
//*****************************************************************************************************************
//*************************************************************************************************************
//计算期初
  sqltext:=sqltext+' union '
           +' select 0,2,'' 期初'' as dd,    '
         +'          ''      '' as CredencenoandCode ';
   AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',        sum(case when t1.kmid='+quotedstr(AdoQry_tmp.fieldbyname('kmid').asstring)+' then debitBalance else 0 end) as jkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
     sqltext:=sqltext+', sum(case when  t2.kmCode>'+quotedstr(kmCode)+' then debitBalance else 0 end) as debitTotal ';
    AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',          sum(case when t1.kmid='+quotedstr(AdoQry_tmp.fieldbyname('kmid').asstring)+' then creditBalance else 0 end) as dkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
    sqltext:=sqltext+' , sum(case when t2.kmCode>'+quotedstr(kmCode)+' then creditBalance else 0 end) as creditTotal,  ';              
    sqltext:=sqltext+'      case when sum(case when kmCode='+quotedstr(kmCode)+' then Balancedirection else 0 end )=1 then ''1 借方'' else ''2 贷方'' end as Balancedirection,          '
                    + '        sum(case when kmCode='+quotedstr(kmCode)+' then FirstBalance else 0.0 end )as Balance                 '
                    + ' from Gl_AccountSubjectBalance t1 join Gl_AccountSubject t2 on  t1.kmid=t2.kmid               '
                    +'  and t2.kmCode like '+quotedstr(kmCode+'%')
                    +'  and t2.kmCode>='+quotedstr(kmCode)
                    + ' where AccountperiodYear='+inttostr(stArtYear)
                    +' and    AccountperiodMonth ='+inttostr(stArtMonth);
//*****************************************************************************************************************
//*************************************************************************************************************
//计算本年合计
  sqltext:=sqltext+' union '
           +' select 0,1,substring(convert(varchAr,t1.Credencedate,102),1,4)+''合计'' as dd,    '
         +'          ''      '' as CredencenoandCode ';
   AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',        sum(case when kmCode='+quotedstr(AdoQry_tmp.fieldbyname('kmCode').asstring)+' then t2.debitAmount else 0 end) as jkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
     sqltext:=sqltext+', sum(t2.debitAmount) as debitTotal ';
    AdoQry_tmp.First;
   while not AdoQry_tmp.Eof do
    begin
     sqltext:=sqltext+',          sum(case when kmCode='+quotedstr(AdoQry_tmp.fieldbyname('kmCode').asstring)+' then t2.creditAmount else 0 end) as dkmCode'+AdoQry_tmp.fieldbyname('kmCode').asstring;
     AdoQry_tmp.Next;                     
    end;
    sqltext:=sqltext+' , sum(t2.creditAmount) as creditTotal,  ';              
    sqltext:=sqltext+'        ''1        '' as Balancedirection,          '
                    + '        0.0 as Balance                 '
                    + ' from Gl_Credenceline t2               '
                    + ' join Gl_Credence t1 on t2.Credenceid=t1.Credenceid    '
                    + ' where kmCode like '+quotedstr(kmCode+'%')   
                    //+' and    t1.Credencedate >='+quotedstr(iifstring(Month1='','1900.01',Month1)+'.01')
                    +' and    substring(convert(varchAr,t1.Credencedate,102),1,4)='+copy(Month1,1,4)
                    + ' group by substring(convert(varchAr,t1.Credencedate,102),1,4)+''合计'' ';
//*****************************************************************************************************************

//  showmessage(sqltext);
 Executesql(AdoQry_tmp,sqltext,1);
//*****************************************************************************************************************
Executesql(AdoQry_tmp,'select * from #tmPMoreAccountQry where  Credencedate='' 期初'' Order by Credencedate',0);
lastBalance:=AdoQry_tmp.fieldbyname('Balance').asfloat;
Firstdirection:=AdoQry_tmp.fieldbyname('Balancedirection').asstring;
Executesql(AdoQry_tmp,'select * from #tmPMoreAccountQry where idflag=0 and lineflag=0 and Credencedate<>'' 期初'' Order by Credencedate',0);
while not AdoQry_tmp.Eof do