gl_qry_dailyreport.pas

一个MRPII系统源代码版本

unit Gl_Qry_DailyReport;

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_DailyReport = Class(TFrm_Base_Qry)
    AdoQry_MainkmCode: TStringField;
    AdoQry_MainkmName: TStringField;
    AdoQry_MainBalancedirection1: TIntegerField;
    AdoQry_MainBalance1: TFloatField;
    AdoQry_MaindebitAmount2: TFloatField;
    AdoQry_MaincreditAmount2: TFloatField;
    AdoQry_MainBalancedirection2: TIntegerField;
    AdoQry_MainBalance2: TFloatField;
    AdoQry_MaindebitCount: TIntegerField;
    AdoQry_MaincreditCount: TIntegerField;
    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_DailyReport: TFrm_Gl_Qry_DailyReport;

implementation
uses Sys_Global,Gl_Qry_DailyReport_C;
{$R *.DFM}
procedure TFrm_Gl_Qry_DailyReport.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Credencedate:string;
    stArtYear,stArtMonth:integer;
    yy,mm:integer;
    nowAccountYear:integer;
    nowAccountMonth:integer;
    Monthbegin:string;
    tomorrow:string;
    kmCode1,kmCode2:string;
    include:integer;
    display:integer;
    ddd:string;
    cre:string;
begin
Application.ProcessMessages;
inherited;
    Credencedate:='';
    stArtYear:=0;
    stArtMonth:=0;
    kmCode1:='';
    kmCode2:='';
    display:=0;
    include:=0;
    cre:='';
 with TFrm_Gl_Qry_DailyReport_C.Create(self) do
  begin
    try
    AdoQry_tmp.Connection:=dbconnect;
    showmodal;
    if modalResult=mrok then
     begin
        Credencedate:=medts.Text;
        kmCode1:=edt_kmCode1.Text;
        kmCode2:=edt_kmCode2.Text;
        include:=cmb_include.ItemIndex;
        display:=cmb_Display.ItemIndex;
        ddd:=cmb_include.Text;
        cre:=cmb_Display.Text;
     end;
    finally
     Frm_Gl_Qry_DailyReport_C.Free;
     Frm_Gl_Qry_DailyReport_C:=nil;
    end;
  end;
 sqltext:='select dateName(yy,'+quotedstr(Credencedate)+')+''.''+dateName(mm,'+quotedstr(Credencedate)+')+''.01''  as Monthbegin';
 Executesql(AdoQry_tmp,sqltext,0);
 Monthbegin:=AdoQry_tmp.fieldbyname('Monthbegin').asstring;
 sqltext:='select dateAdd(dd,1,'+quotedstr(Credencedate)+') as yy';
 Executesql(AdoQry_tmp,sqltext,0);
 tomorrow:=AdoQry_tmp.fieldbyname('yy').asstring;
 sqltext:='select datepArt(yy,'+quotedstr(Credencedate)+') as yy,datepArt(mm,'+quotedstr(Credencedate)+') as mm ';
 Executesql(AdoQry_tmp,sqltext,0);
 yy:=AdoQry_tmp.fieldbyname('yy').asinteger;
 mm:=AdoQry_tmp.fieldbyname('mm').asinteger;
 stArtYear:=yy;
 stArtMonth:=mm;
 sqltext:='select * from Gl_Accountperiod where Currentperiod=1';
 Executesql(AdoQry_tmp,sqltext,0);
 nowAccountYear:=AdoQry_tmp.fieldbyname('AccountperiodYear').asinteger;
 nowAccountMonth:=AdoQry_tmp.fieldbyname('AccountperiodMonth').asinteger;
 if not ((yy=nowAccountYear) and (mm=nowAccountMonth)) then 
  begin
   sqltext:='select datepArt(yy,dateAdd(mm,-1,'+quotedstr(Credencedate)+')) as yy,datepArt(mm,dateAdd(mm,-1,'+quotedstr(Credencedate)+')) as mm ';
   Executesql(AdoQry_tmp,sqltext,0);
   stArtYear:=AdoQry_tmp.fieldbyname('yy').asinteger;
   stArtMonth:=AdoQry_tmp.fieldbyname('mm').asinteger;
  end;
 //--********************************************************************************************
//--生成昨日、今日发生额和余额及余额方向
sqltext:=' select  '
         +'            t2.kmCode,     '
         +'            t3.kmName,     '
         +'            t3.kmproperty, '
         +'            sum(case when t1.Credencedate<'+quotedstr(Credencedate)+' then t2.debitAmount else 0 end) as debitAmount1,              '
         +'            sum(case when t1.Credencedate<'+quotedstr(Credencedate)+' then t2.creditAmount else 0 end ) as creditAmount1 ,          '
         +'            case when t3.kmproperty=1 then (case when sum(case when t1.Credencedate<'+quotedstr(Credencedate)+' then t2.debitAmount else 0 end)  '
         +'                        -sum(case when t1.Credencedate<'+quotedstr(Credencedate)+' then t2.creditAmount else 0 end )>0 then 1 else 2 end)   '
         +'                 when t3.kmproperty=2 then (case when sum(case when t1.Credencedate<'+quotedstr(Credencedate)+' then t2.creditAmount else 0 end ) '
         +'                        -sum(case when t1.Credencedate<'+quotedstr(Credencedate)+' then t2.debitAmount else 0 end)>0 then 2 else 1 end)   '
         +'            end as Balancedirection1,              '
         +'            sum(case when t1.Credencedate='+quotedstr(Credencedate)+' then t2.debitAmount else 0 end ) as debitAmount2,   '
         +'            sum(case when t1.Credencedate='+quotedstr(Credencedate)+' then t2.creditAmount else 0 end ) as creditAmount2 , '
         +'            case when t3.kmproperty=1 then (case when sum(t2.debitAmount)-sum(t2.creditAmount)>0 then 1 else 2 end)  '
         +'                 when t3.kmproperty=2 then (case when sum(t2.creditAmount)-sum(t2.debitAmount)>0 then 2 else 1 end)  '
         + '           end as Balancedirection2,        '
         +'            Count(case when t1.Credencedate='+quotedstr(Credencedate)+' and t2.debitAmount>0 then t2.debitAmount else null end) as debitCount,  '
         +'            Count(case when t1.Credencedate='+quotedstr(Credencedate)+' and t2.creditAmount>0 then t2.debitAmount else null end) as creditCount '
         +'     into #tt1                                              '
         +'     from Gl_Credenceline t2                                 '
         +'      join Gl_Credence t1 on t2.Credenceid=t1.Credenceid     '
         +'      join Gl_AccountSubject t3 on t2.kmCode=t3.kmCode       '
         +'     and t1.Credencedate<'+quotedstr(tomorrow)
         +'     and t1.Credencedate>='+quotedstr(Monthbegin)
         +iifstring(include=0,' ',' and t1.tallyflag=1 ')
         +'     where t2.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2)
         +'     group by t2.kmCode,t3.kmName,t3.kmproperty              ';
Executesql(AdoQry_tmp,sqltext,1);
//--*******************************************************************************************
//***************************************************************************************************88
//生成起初余额
sqltext:='select t2.kmCode,  '
         +'        t2.kmName,  '
         +'        t2.kmproperty,  '
         +'        t1.FirstBalance,  '
         +'        t1.Balancedirection   '
         +'  into #tt2  '
         +' from Gl_AccountSubjectBalance t1 '
         +' join Gl_AccountSubject t2 on t1.kmid=t2.kmid  '
         +' where t1.AccountperiodYear='+inttostr(stArtYear)
         +'  and  t1.AccountperiodMonth='+inttostr(stArtMonth) ;

 Executesql(AdoQry_tmp,sqltext,1);
//*********************************************************************************************************888
//************************************************************************************************************
//生成结果表
sqltext:='select t1.kmCode,'
        +'       t1.kmName, '
        +'       isnull(t2.Balancedirection1,t1.kmproperty) as Balancedirection1, '
        +'       isnull(t2.debitAmount1,0)-isnull(t2.creditAmount1,0)+isnull(t3.FirstBalance,0) as Balance1,'
        +'       isnull(t2.debitAmount2,0) as debitAmount2, '
        +'       isnull(t2.creditAmount2,0) as creditAmount2, '
        +'       isnull(t2.debitAmount2,0)-isnull(t2.creditAmount2,0)+isnull(t3.FirstBalance,0)+isnull(t2.debitAmount1,0)-isnull(t2.creditAmount1,0)+isnull(t3.FirstBalance,0) as Balance2,'
        +'       isnull(t2.Balancedirection2,t1.kmproperty) as Balancedirection2, '
        +'       isnull(t2.debitCount,0) as debitCount, '
        +'       isnull(t2.creditCount,0) as creditCount '
        +' into #ttttt '
        +' from Gl_AccountSubject t1 '
        +' left join #tt1 t2 on t1.kmCode=t2.kmCode '
        +' left join #tt2 t3 on t1.kmCode=t3.kmCode '
        +' where t1.kmCode between '+quotedstr(kmCode1)+' and '+quotedstr(kmCode2);
 Executesql(AdoQry_tmp,sqltext,1)        ;
//****************************************************************************************************88888888888888
 selectfromsql:='select * from #ttttt';
 if display=0 then 
  conditionuserDefine:=' ((debitCount<>0) or (creditCount<>0))';
 Orderbyfields:=' kmCode';
 Executesql(AdoQry_Main,'select * from #ttttt '+iifstring(display=1,' ',' where ((debitCount<>0) or (creditCount<>0))')+'Order by kmCode ',0);
 lbl_Condition.Caption:='制单日期： '+Credencedate+' /科目代码从 '+kmCode1+' 到 '+kmCode2+' / 包含未过帐凭证： '+ddd+' / 无发生额不显示： '+cre;

  
end;

procedure TFrm_Gl_Qry_DailyReport.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_DailyReport:=nil;
end;

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

end.