gl_qry_accountbook3.pas

一个MRPII系统源代码版本

unit Gl_Qry_AccountBook3;

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_AccountBook3 = Class(TFrm_Base_Qry)
    AdoQry_Mainkmid: TIntegerField;
    AdoQry_MainkmCode: TStringField;
    AdoQry_MainkmName: TStringField;
    AdoQry_MainstArtdir: TIntegerField;
    AdoQry_MainstArtAmount: TFloatField;
    AdoQry_MainstArtPrice: TFloatField;
    AdoQry_MainstArtsum: TFloatField;
    AdoQry_MainthisdebitAmount: TFloatField;
    AdoQry_Mainthisdebitsum: TFloatField;
    AdoQry_MainthiScreditAmount: TFloatField;
    AdoQry_MainthiScreditsum: TFloatField;
    AdoQry_MainthisYeardebitAmount: TFloatField;
    AdoQry_MainthisYeardebitsum: TFloatField;
    AdoQry_MainthisYearcreditAmount: TFloatField;
    AdoQry_MainthisYearcreditsum: TFloatField;
    AdoQry_Mainenddir: TIntegerField;
    AdoQry_MainendAmount: TFloatField;
    AdoQry_MainendPrice: TFloatField;
    AdoQry_Mainendsum: 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_AccountBook3: TFrm_Gl_Qry_AccountBook3;

implementation
uses Sys_Global,Gl_Qry_AccountBook3_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_AccountBook3.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Month1,Month2:string;
    Month:string;
    kmCode1,kmCode2:string;
    stArtYear,stArtMonth:integer;
    nowYear:integer;
begin
Application.ProcessMessages;
inherited;
    Month1:='';
    Month2:='';
    kmCode1:='';
    kmCode2:='';
 with TFrm_Gl_Qry_AccountBook3_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;
     end;
    finally
     Frm_Gl_Qry_AccountBook3_C.Free;
    end;
  end;
  if (Month1='') and (Month2='') then abort;
  Month:=Month2;
  Executesql(AdoQry_tmp,'select dateAdd(mm,1,'+quotedstr(Month2+'.01')+') as dd',0);
  Month2:=AdoQry_tmp.fieldbyname('dd').asstring;
sqltext:=' select t1.kmid,                           '
         +'   t2.kmCode,   '
         +'   t2.kmName,   '
         +'  0 as stArtdir,                           '
         +'    convert(float,0) as stArtAmount,       '
         +'    convert(float,0) as stArtPrice,        '
         +'    convert(float,0) as stArtsum,           '
         +'    convert(float,0) as thisdebitAmount,    '
         +'    convert(float,0) as thisdebitsum,       '
         +'    convert(float,0) as thiScreditAmount,   '
         +'    convert(float,0) as thiScreditsum,      '
         +'    convert(float,0) as thisYeardebitAmount,'
         +'    convert(float,0) as thisYeardebitsum,   '
          +'   convert(float,0) as thisYearcreditAmount,'
          +'   convert(float,0) as thisYearcreditsum, '
          +'   0 as enddir,                           '
          +'   convert(float,0) as endAmount,         '
          +'   convert(float,0) as endPrice,          '
          +'   convert(float,0) as endsum             '
     +' into #tmpResult                               '
      +' from Gl_AccountSubjectBalance t1             '
     +'  join Gl_AccountSubject t2 on t1.kmid=t2.kmid '
     + ' and t2.Amountcalculate=1  '
     +'  and t2.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2)
     +' group by t1.kmid,kmCode,kmName        ';
Executesql(AdoQry_tmp,sqltext,1);

sqltext:='update  #tmpResult  '
         +'   set stArtdir=Gl_AccountSubjectBalance.Balancedirection,  '
         +'      stArtAmount=Gl_AccountSubjectBalance.FirstAmountBalance, '
         +'       stArtsum=Gl_AccountSubjectBalance.FirstBalance,         '
         +'       stArtPrice=case when Gl_AccountSubjectBalance.FirstAmountBalance=0 then 0   '
         +'                   else Gl_AccountSubjectBalance.FirstBalance/Gl_AccountSubjectBalance.FirstAmountBalance  '
         +'                  end                                                     '
         +'   from #tmpResult,Gl_AccountSubjectBalance                               '
         +'   where #tmpResult.kmid*=Gl_AccountSubjectBalance.kmid                   '
         +'     and convert(varchAr,Gl_AccountSubjectBalance.AccountperiodYear)      '
         +'        +case when Gl_AccountSubjectBalance.AccountperiodMonth<10         '
         +'              then ''.0''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth) '
         +'         else  ''.''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth)      '
         +'         end=(select min(convert(varchAr,Gl_AccountSubjectBalance.AccountperiodYear) '
         +'                      +case when Gl_AccountSubjectBalance.AccountperiodMonth<10      '
         +'                            then ''.0''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth)   '
         +'                            else  ''.''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth)    '
         +'                             end)  from Gl_AccountSubjectBalance where convert(varchAr,Gl_AccountSubjectBalance.AccountperiodYear)   '
         + '                     +case when Gl_AccountSubjectBalance.AccountperiodMonth<10                         '
         + '                           then ''.0''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth)     '
         + '                           else  ''.''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth)     '
         + '                            end between '+quotedstr(Month1)+' and  '+quotedstr(Month)+')';
 Executesql(AdoQry_tmp,sqltext,1)         ;

sqltext:='select kmid,sum(AmountBalance) as AmountBalance,  '
         +'        sum(debitBalance) as debitBalance         '
         +' into #tmpthisdebit                               '
         +' from Gl_AccountSubjectBalance                    '
         +' where convert(varchAr,Gl_AccountSubjectBalance.AccountperiodYear)   '
         + '     +case when Gl_AccountSubjectBalance.AccountperiodMonth<10      '
         +'            then ''.0''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth) '
         +'       else  ''.''+convert(varchAr,Gl_AccountSubjectBalance.AccountperiodMonth)  '
         +'       end between '+quotedstr(Month1)+' and '+quotedstr(Month)
         +'   and Balancedirection=1                       '