gl_qry_accountbook2.pas

一个MRPII系统源代码版本

unit Gl_Qry_AccountBook2;

Interface

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

Type
  TFrm_Gl_Qry_AccountBook2 = Class(TFrm_Base_Qry)
    AdoQry_MaindocketName: TStringField;
    AdoQry_MainkmCode: TStringField;
    AdoQry_MainkmName: TStringField;
    AdoQry_MainBalance: TFloatField;
    AdoQry_Mainkmflag: TStringField;
    Label1: TLabel;
    edt_kmCode: TLinkEdit;
    edt_kmName: TEdit;
    AdoQry_MainCredenceCodeno: TStringField;
    AdoQry_MainBalancedirection: TIntegerField;
    AdoQry_MaindebitBalance: TFloatField;
    AdoQry_MaincreditBalance: TFloatField;
    AdoQry_MainCredencedate: TStringField;
    AdoQry_tmp1: TAdoQuery;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure edt_kmCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure edt_kmCodeButtonClick(Sender: TObject);
    procedure edt_kmCodeExit(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Gl_Qry_AccountBook2: TFrm_Gl_Qry_AccountBook2;
    kmCode1,kmCode2:string;

implementation
uses Sys_Global,Gl_Qry_AccountBook2_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_AccountBook2.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Month1,Month2:string;
    Month:string;
    currencyCode:string;
    index:integer;
    include:integer;
    cre:string;
    iSc:string;
begin
  Application.ProcessMessages;
  inherited;
  AdoQry_tmp1.Connection:=dbconnect;
  Month1:='1800.01';
  Month2:='1800.01';
  kmCode1:='';
  kmCode2:='';
  index:=0;
  include:=3;
  currencyCode:='';
  with TFrm_Gl_Qry_AccountBook2_C.Create(self) do
  begin
    try
      AdoQry_tmp.Connection:=dbconnect;
      showmodal;
      if modalResult=mrok then
      begin
        Month1:=medts.Text;
        Month2:=medte.Text;
        kmCode1:=edt_kmCode1.Text;
        kmCode2:=edt_kmCode2.Text;
        currencyCode:=getCode(cmb_Currency.text);
        index:=cmb_Currency.ItemIndex;
        include:=cmb_include.Itemindex+1;
        cre:=cmb_Currency.Text;
        iSc:=cmb_include.Text;
      end;
    finally
     Frm_Gl_Qry_AccountBook2_C.Free;
    end;
  end;   
  if (Month1='1800.01') and (Month2='1800.01') then 
  begin
    edt_kmCode.OnExit:=nil;
    edt_kmCode.Enabled:=False;
    abort;
  end;
  Month:=Month2;
  Executesql(AdoQry_tmp,'select dateAdd(mm,1,'+quotedstr(Month2+'.01')+') as dd',0);
  Month2:=AdoQry_tmp.fieldbyname('dd').asstring;
  sqltext:= 'select t1.Credenceid, '
           +'      t2.Gl_Credencelineno as Credencelineno,'
           +'        t1.Credencedate,  '
           +'        t1.Characterno+''_''+convert(varchAr,t1.Credenceno) as CredenceCodeno,  '
           +'        t1.tallyflag,    '
           +'        t2.docketName,   '
           +'        t2.kmCode,       '
           +'        t3.kmName,       '
           +'        t2.kmCode+'' ''+t3.kmName as kmflag,   '
           +'        t2.debitAmount,      '
           +'        t2.creditAmount,     '
//           +'        t3.fcurrecycalculate,'
           +'        t3.fcurrecy,         '
           +'        t2.fdebitAmount,     '
           +'        t2.fcreditAmount,    '
           +'        t4.Balancedirection, '
           +'        t2.debitAmount-t2.CreditAmount as Balance   '
           +' into #tmpCredence  '
           +' from  Gl_Credenceline t2              '
           +' join Gl_Credence t1 on t2.Credenceid=t1.Credenceid   '
           +'  and t1.Credencedate between '+quotedstr(Month1+'.01')+' and '+quotedstr(Month2)
           +iifstring(include=1,'  and t1.tallyflag in(0,1) ',iifstring(include=2,' and t1.tallyflag=1',' '))
           +' join Gl_AccountSubject t3 on t2.kmCode=t3.kmCode    '
           +iifstring(index>1,'  and  t3.fcurrecy='+quotedstr(currencyCode),iifstring(index=1,' and IsNull(t3.Fcurrecy,'''')<>''''',' '))
           +' left join Gl_AccountSubjectBalance t4 on t3.kmid=t4.kmid  ';
  Executesql(AdoQry_tmp,sqltext,1);
  Executesql(AdoQry_tmp,'select * from Gl_AccountSubject where kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2),0);
  edt_kmCode.Text:=AdoQry_tmp.fieldbyname('kmCode').asstring;
  activecontrol:=dbgrideh;
  selectfromsql:='select * from #ress';
  edt_kmCode.OnExit(self);
  dbgrideh.Columns[1].Width:=64;

 //selectfromsql:='select * from #CredencesummQry';
// Orderbyfields:=' kmCode';
// Executesql(AdoQry_Main,'select * from #CredencesummQry Order by kmCode',0);
  lbl_Condition.Caption:='会计期间从 '+Month1+' 到 '+Month+' /科目代码从 '+kmCode1+' 到 '+kmCode2+' / 包含未过帐凭证： '+iSc+' / 货币： '+cre;

  
end;

procedure TFrm_Gl_Qry_AccountBook2.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_AccountBook2:=nil;
end;

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

procedure TFrm_Gl_Qry_AccountBook2.edt_kmCodeKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if key=120 then
    CommOnHint(Sender,AdoQry_Tmp,'kmName',
             '科目名称','kmCode','科目代码','Gl_AccountSubject','kmCode between'+quotedstr(kmCode1)+' and '+quotedstr(kmCode2));

end;

procedure TFrm_Gl_Qry_AccountBook2.edt_kmCodeButtonClick(Sender: TObject);
begin
  inherited;
  CommOnHint(Sender,AdoQry_Tmp,'kmName',
             '科目名称','kmCode','科目代码','Gl_AccountSubject','kmCode between'+quotedstr(kmCode1)+' and '+quotedstr(kmCode2));

end;

procedure TFrm_Gl_Qry_AccountBook2.edt_kmCodeExit(Sender: TObject);
var sqltext:string;
    lastBalance:double;
begin
  inherited;
  Executesql(AdoQry_tmp,'select * from Gl_AccountSubject where kmCode='+quotedstr(Trim(Tedit(edt_kmCode).text)),0);
  edt_kmName.Text:=AdoQry_tmp.fieldbyname('kmName').asstring;
  try
   Executesql(AdoQry_tmp,'drop table #tmpkmInfo',1);
  except
   end;
  try
   Executesql(AdoQry_tmp,'drop table #tmpResult',1);
  except
  end;
  try
   Executesql(AdoQry_tmp,'drop table #ress',1);
  except
  end;
  sqltext:=' select Credenceid, '
           +'        kmCode,     '
           +'        case when debitAmount<>0 then 1  '
           +'        else 2                           '
           +'        end as kmp,                      '
           +'        Balancedirection                 '
           +' into #tmpkmInfo                        '
           +' from #tmpCredence                       '
           +' where kmCode='+quotedstr(Trim(edt_kmCode.text));
  Executesql(AdoQry_tmp,sqltext,1)         ;
  //^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  try
   Executesql(AdoQry_tmp,'drop table #tmpResult',1);
  except

  end;
  sqltext:='select   0 as Credenceid,1 as Credencelineno,9 as flag,'' 期初           '' as Credencedate,    '
           +'        ''                   '' as CredenceCodeno,   '
           +'        ''                                     '' as docketName,  '
           +'        ''               '' as kmCode,  '
           +'        ''                                                '' as kmName,  '
           +'        ''                                                                   '' as kmflag,  '
           +'        t1.debitBalance,   '
           +'        t1.creditBalance,  '
           +'        t1.Balancedirection,'
           +'        t1.FirstBalance  as Balance   '
           +' into #tmpResult                                     '
           +' from Gl_AccountSubjectBalance t1     '
           +' join Gl_AccountSubject t2 on t1.kmid=t2.kmid and t2.kmCode='+quotedstr(Trim(edt_kmCode.text))
           +' union                                '
           +' select #tmpCredence.Credenceid,#tmpCredence.Credencelineno,9,convert(varchAr,Credencedate,102),                 '
           +'        CredenceCodeno,               '
           +'        docketName,                   '
           +'        #tmpCredence.kmCode,          '
           +'        kmName,                       '
           +'        kmflag,                       '
          +'         case when #tmpkmInfo.kmp=1 then creditAmount '
          + '        else 0                                       '
           +'        end as debitAmount,                          '
           +'        case when #tmpkmInfo.kmp=2 then debitAmount  '
           +'        else 0                                       '
           +'        end as creditAmount,                         '
           +'        #tmpkmInfo.Balancedirection,                 '
          + '        Balance                                      '
          + '  from #tmpCredence                                  '
          + ' join #tmpkmInfo on #tmpCredence.Credenceid=#tmpkmInfo.Credenceid     '
          + ' where #tmpCredence.Credenceid in(select Credenceid from #tmpkmInfo)  '
          + '  and  #tmpCredence.kmCode<>'+quotedstr(Trim(edt_kmCode.text));
  Executesql(AdoQry_tmp,sqltext,1)         ;
  //^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  Executesql(AdoQry_tmp,'select * from #tmpResult where Credencedate='' 期初'' Order by Credencedate',0);
  lastBalance:=AdoQry_tmp.fieldbyname('Balance').asfloat;
  Executesql(AdoQry_tmp,'select * from #tmpResult where Credencedate<>'' 期初'' Order by Credencedate',0);
  while not AdoQry_tmp.Eof do
   begin
     sqltext:='update #tmpResult '
             +' set  Balance='+floattostr(lastBalance)+'+case when Balancedirection =1 then debitBalance-creditBalance else creditBalance-debitBalance end '
             +' where Credenceid='+AdoQry_tmp.fieldbyname('Credenceid').asstring
             +'  and  Credencelineno='+AdoQry_tmp.fieldbyname('Credencelineno').asstring;
     Executesql(AdoQry_Main,sqltext,1);
     sqltext:= 'select Balance from #tmpResult '
             +' where Credenceid='+AdoQry_tmp.fieldbyname('Credenceid').asstring
             +'  and  Credencelineno='+AdoQry_tmp.fieldbyname('Credencelineno').asstring;
     Executesql(AdoQry_tmp1,sqltext,0);
     lastBalance:=AdoQry_tmp1.fieldbyname('Balance').asfloat;
     AdoQry_tmp.Next;
   end;
   //^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   //*********************************************************************
  sqltext:='select *  into #ress from #tmpResult    '
           +' union                      '
          +'  select 0,0,1,convert(varchAr,Credencedate,102)+''小计'',  '
          +'         ''          '',       '
          +'         ''          '',       '
         + '         ''          '',       '
          +'         ''          '',       '
          +'         ''          '',       '
          +'         sum(debitBalance),    '
         + '         sum(creditBalance),   '
         + '         ''    '',               '
         + '        null   '
         + '  from  #tmpResult               '
         + '  where Credencedate not like '' 期初%'' '
         + '  group by Credencedate                 '
         + '  union                                 '
         + '  select 0,0,2,substring(Credencedate,1,7)+''合计'' as dd, '
         + '         ''          '',   '
         + '         ''          '',   '
         + '         ''          '',  '
         + '         ''          '',  '
         + '         ''          '',  '
         + '         sum(debitBalance), '
         + '         sum(creditBalance), '
         + '         ''    '',           '
         + '         null   '
         + '  from  #tmpResult            '
         + '  where Credencedate not like '' 期初%''   '
         + '  group by substring(Credencedate,1,7)+''合计''   ' 
         + '  union                                 '
         + '  select 0,0,3,substring(Credencedate,1,4)+''合计'' as dd, '
         + '         ''          '',   '
         + '         ''          '',   '
         + '         ''          '',  '
         + '         ''          '',  '
         + '         ''          '',  '
         + '         sum(debitBalance), '
         + '         sum(creditBalance), '
         + '         ''    '',           '
         + '         null   '
         + '  from  #tmpResult            '
         + '  where Credencedate not like '' 期初%''   '
         + '  group by substring(Credencedate,1,4)+''合计''   '  ;
  Executesql(AdoQry_Main,sqltext,1);
  //Executesql(AdoQry_Main,'select * from #ress Order by Credencedate',0);
  Executesql(AdoQry_tmp,'select * from #ress where not (Credenceid=0 and Credencelineno=1) Order by Credencedate ',0);
  lastBalance:=0;
  while not AdoQry_tmp.Eof do
   begin
    if (AdoQry_tmp.fieldbyname('Credenceid').asinteger=0) and (AdoQry_tmp.fieldbyname('Credencelineno').asinteger=0) then
     begin
     sqltext:='update #ress '
             +' set  Balance='+floattostr(lastBalance)
             +' where Credenceid='+AdoQry_tmp.fieldbyname('Credenceid').asstring
             +'  and  Credencelineno='+AdoQry_tmp.fieldbyname('Credencelineno').asstring
             +'  and  flag='+AdoQry_tmp.fieldbyname('flag').asstring
             +'  and  Credencedate='+quotedstr(AdoQry_tmp.fieldbyname('Credencedate').asstring);
     Executesql(AdoQry_Main,sqltext,1);
     end
     else lastBalance:=AdoQry_tmp.fieldbyname('Balance').asfloat;
     AdoQry_tmp.Next;
   end;
  Executesql(AdoQry_Main,'select * from #ress Order by Credencedate',0);

  activecontrol:=dbgrideh;


end;

end.