gl_qry_accountbook1.pas

一个MRPII系统源代码版本

unit Gl_Qry_AccountBook1;

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_AccountBook1 = Class(TFrm_Base_Qry)
    AdoQry_MainkmCode: TStringField;
    AdoQry_MainkmName: TStringField;
    AdoQry_MaindebitAmount: TFloatField;
    AdoQry_MaincreditAmount: TFloatField;
    AdoQry_MainBalancedirection: TIntegerField;
    AdoQry_MainBalance: 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_AccountBook1: TFrm_Gl_Qry_AccountBook1;

implementation
uses Sys_Global,Gl_Qry_AccountBook1_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_AccountBook1.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Month1,Month2:string;
    Month:string;
    kmlevel:integer;
    kmCode1,kmCode2:string;
    currencyCode:string;
    index:integer;
    cre:string;
begin
  Application.ProcessMessages;
  inherited;
  Month1:='1900.01';
  Month2:='1900.01';
  kmlevel:=0;
  kmCode1:='';
  kmCode2:='';
  index:=0;
  currencyCode:='';
  with TFrm_Gl_Qry_AccountBook1_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;
        kmlevel:=strtoint(Trim(edt_kmlevel.text));
        currencyCode:=getCode(cmb_Currency.text);
        index:=cmb_Currency.ItemIndex;
        cre:=cmb_Currency.Text;
     end;
    finally
     Frm_Gl_Qry_AccountBook1_C.Free;
     Frm_Gl_Qry_AccountBook1_C:=nil;
    end;
  end;
//  if (Month1='') and (Month2='') then abort;
  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;
  if index<=1 then
  sqltext:='select t2.kmCode,   '
          +'       t2.kmName,   '
          +'       sum(t1.DebitBalance) as debitAmount,  '
          +'       sum(t1.CreditBalance) as creditAmount,  '
{          +'       case when t2.kmproperty=1 then (case when sum(t1.DebitBalance)-sum(t1.CreditBalance)>0 then 1 else 2 end)  '
          +'            when t2.kmproperty=2 then (case when sum(t1.CreditBalance)-sum(t1.DebitBalance)>0 then 2 else 1 end) '
          +'       end as Balancedirection, '
          +'       sum(t1.DebitBalance)-sum(t1.CreditBalance) '
          +'        as Balance'
}
          +'       Sum(Case when AccountPeriodYear='+Copy(Month,1,4)+' and AccountPeriodMonth='+Copy(Month,6,2)+' then endBalanceDirection  '
          +'                else 0  '
          +'            end ) as BalanceDirection, '
          +'       Sum(Case when AccountPeriodYear='+Copy(Month,1,4)+' and AccountPeriodMonth='+Copy(Month,6,2)+' then endBalance '
          +'                else 0 '
          +'            end ) as Balance '
          +' into #CredencesummQry '
          +' from   Gl_AccountSubjectBalance t1                   '
          +'  join Gl_AccountSubject t2 on t1.kmId=t2.kmId  '
          +'   and t2.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2)
          +'   and t2.kmlevel<='+inttostr(kmlevel)
          +iifstring(index=0,' ',' and IsNull(t2.fcurrecy,'''')<>''''')
//          +' join   Gl_Credence t3 on t1.Credenceid=t3.Credenceid  '
          +' where  Convert(DateTime,Convert(varchAr,AccountPeriodYear)+''.''+Convert(varchAr,AccountPeriodMonth)+''.01'') >='+quotedstr(iifstring(Month1='','1900.01',Month1)+'.01')
          +' and   Convert(DateTime,Convert(varchAr,AccountPeriodYear)+''.''+Convert(varchAr,AccountPeriodMonth)+''.01'') <'+quotedstr(Month2)
          +' group by t2.kmCode,t2.kmName '
  else 
  sqltext:='select t2.kmCode,   '
          +'       t2.kmName,   '
         // +'       t2.fcurrecycalculate,'
          +'       sum(t1.FDebitBalance) as debitAmount,  '
          +'       sum(t1.FCreditBalance) as creditAmount,  '
{          +'       case when t2.kmproperty=1 then (case when sum(case t2.Fcurrecycalculate when 0 then t1.DebitBalance else t1.FDebitBalance end)-sum(case t2.fcurrecycalculate when 0 then t1.CreditBalance else t1.FCreditBalance end)>0 then 1 else 2 end)  '
          +'            when t2.kmproperty=2 then (case when sum(case t2.fcurrecycalculate when 0 then t1.CreditBalance else t1.FCreditBalance end)-sum(case t2.Fcurrecycalculate when 0 then t1.DebitBalance else t1.FDebitBalance end)>0 then 2 else 1 end) '
          +'       end as Balancedirection, '
          +'        sum(case t2.Fcurrecycalculate when 0 then t1.DebitBalance else t1.FDebitBalance end)-sum(case t2.fcurrecycalculate when 0 then t1.CreditBalance else t1.FCreditBalance end) '
          +'             as Balance'
}
          +'       Sum(Case when AccountPeriodYear='+Copy(Month,1,4)+' and AccountPeriodMonth='+Copy(Month,6,2)+' then endBalanceDirection  '
          +'                else 0  '
          +'            end ) as BalanceDirection, '
          +'       Sum(Case when AccountPeriodYear='+Copy(Month,1,4)+' and AccountPeriodMonth='+Copy(Month,6,2)+' then endFBalance '
          +'                else 0 '
          +'            end ) as Balance '
          +' into #CredencesummQry '
          +' from  Gl_AccountSubjectBalance t1                   '
          +'  join Gl_AccountSubject t2 on t1.KmId=t2.KmId  '
          +'   and t2.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2)
          +'   and t2.fcurrecy='+quotedstr(currencyCode)
          +' where  Convert(DateTime,Convert(varchAr,AccountPeriodYear)+''.''+Convert(varchAr,AccountPeriodMonth)+''.01'') >='+quotedstr(iifstring(Month1='','1900.01',Month1)+'.01')
          +' and   Convert(DateTime,Convert(varchAr,AccountPeriodYear)+''.''+Convert(varchAr,AccountPeriodMonth)+''.01'') <'+quotedstr(Month2)
          +' group by t2.kmCode,t2.kmName ';
  Executesql(AdoQry_tmp,sqltext,1);
  selectfromsql:='select * from #CredencesummQry';
  Orderbyfields:=' kmCode';
  Executesql(AdoQry_Main,'select * from #CredencesummQry Order by kmCode',0);
  lbl_Condition.Caption:='会计期间从 '+Month1+' 到 '+Month+' /科目级长： '+inttostr(kmlevel)+' /科目代码从 '+kmCode1+' 到 '+kmCode2+' / 货币： '+cre;

  
end;

procedure TFrm_Gl_Qry_AccountBook1.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_AccountBook1:=nil;
end;

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

end.