gl_qry_cashreport.pas

一个MRPII系统源代码版本

unit Gl_Qry_CashReport;

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_CashReport = Class(TFrm_Base_Qry)
    AdoQry_MainprojectName: TStringField;
    AdoQry_Mainplineno: TIntegerField;
    AdoQry_MainprojectCode: TStringField;
    AdoQry_MainTotalsum: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    procedure initReport;Override;
    { Public declarations }
  end;
    function  getmin(Value1,Value2:integer):integer;

var
  Frm_Gl_Qry_CashReport: TFrm_Gl_Qry_CashReport;

implementation
uses Gl_Qry_CashReport_C,Sys_Global;
{$R *.DFM}

function  getmin(Value1,Value2:integer):integer;
begin
  Result:=Value1;
  if (Value1=0) and (Value2=0) then Result:=0
  else 
  if Value1=0 then Result:=Value2
  else 
  if Value2=0 then Result:=Value1
  else 
  if (Value1<Value2)  then Result:=Value1
  else 
  if (Value2<Value1)  then Result:=Value2;
end;

procedure TFrm_Gl_Qry_CashReport.initReport;
begin
  inherited;
  ExtprintReport.Subtitle2:='编制单位：Ext';
  ExtprintReport.Subtitle3:=lbl_Condition.Caption;
//  ExtprintReport.Subtitle4:='单位：元';
end;

procedure TFrm_Gl_Qry_CashReport.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Month,Month1,Month2:string;
    AdoQry:TAdoQuery;
    tmpproject:string;
    Apos,dpos:integer;
    astring,dstring:string;
    pretext:string;
begin
  Application.ProcessMessages;
  inherited;
  Month1:='1800.01';
  Month2:='1800.01';
  try
   with TFrm_Gl_Qry_CashReport_C.Create(self) do
    begin
      AdoQry_tmp.Connection:=dbconnect;
      showmodal;     
      if modalResult=mrok then 
        begin
          Month1:=medts.Text;
          Month2:=medte.text;
        end;
    end;
  finally
    Frm_Gl_Qry_CashReport_C.Free;
  end;
  if (Month1='1800.01') and (Month2='1800.01')  then 
  abort;
//  Month:=Month2;
//  Executesql(AdoQry_tmp,'select dateAdd(mm,1,'+quotedstr(Month2+'.01')+') as dd',0);
//  Month2:=AdoQry_tmp.fieldbyname('dd').asstring;
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
  AdoQry.EnableBCD:=False;

  sqltext:=' select CredenceDate,           '
           +'        ProjectCode,            '
           +'        DebitAmount,            '
           +'        CreditAmount,           '
           +'        FDebitAmount,           '
           +'        FCreditAmount           '
           +'  Into #TmpCredence             '
           +' from Gl_CredenceLine           '
           +' Join Gl_Credence on Gl_CredenceLine.CredenceId=Gl_Credence.CredenceId  '
           +'             and  Gl_Credence.assessflag=1  ';
           
  Executesql(AdoQry_tmp,sqltext,1);

  sqltext:=' select  projectCode,  '
           +'        projectName,  '
           +'        plineno,      '
           +'        Totalsum as Totalkm,  '
           +'        convert(decimal(20,8),0) as Totalsum '
           +' into #cashQry   '
           +'  from Gl_CashReport                       ';

  Executesql(AdoQry_tmp,sqltext,1);
  Executesql(AdoQry_tmp,'select * from #cashQry',0);
  while not AdoQry_tmp.Eof do
    begin
      pretext:='+';
      astring:=quotedstr('');
      dstring:=quotedstr('');
      tmpproject:=AdoQry_tmp.fieldbyname('Totalkm').asstring;
      while tmpproject<>'' do
        begin
          Apos:=pos('+',tmpproject);
          dpos:=pos('-',tmpproject);
          if (Apos=0) and (dpos=0) then
            begin
              if pretext='+'  then
                astring:=astring+iifstring(astring='','',',')+quotedstr(tmpproject)
              else 
                dstring:=dstring+iifstring(dstring='','',',')+quotedstr(tmpproject);            
              tmpproject:='';
              continue;
            end;
          if pretext='+'  then
            astring:=astring+iifstring(astring='','',',')+quotedstr(copy(tmpproject,1,getmin(Apos,dpos)-1))
          else 
            dstring:=dstring+iifstring(dstring='','',',')+quotedstr(copy(tmpproject,1,getmin(Apos,dpos)-1));            
          if ((Apos=getmin(Apos,dpos)) and (Apos<>0) ) or (dpos=0) then
            begin
              pretext:='+' ;
              tmpproject:=copy(tmpproject,Apos+1,length(tmpproject)-Apos);
            end
          else 
            begin
              pretext:='-' ;
              tmpproject:=copy(tmpproject,dpos+1,length(tmpproject)-dpos);
            end;
        end;
      
      sqltext:='update #cashQry'
              +'   set Totalsum=isnull((select sum(isnull(DebitAmount,0)-isnull(CreditAmount,0))  '
              +'                       from #tmpCredence    '
              +'                     where  #tmpCredence.CredenceDate>='+QuotedStr(Month1+'.01')
              +'                       and  #TmpCredence.CredenceDate<DateAdd(mm,1,'+QuotedStr(Month2+'.01')+')'
              +'                       and  #tmpCredence.ProjectCode in ('+astring+')),0.0) ' 
              +'                   -isnull((select sum(isnull(DebitAmount,0)-isnull(CreditAmount,0))  '
              +'                       from #tmpCredence     '
              +'                     where  #tmpCredence.CredenceDate>='+QuotedStr(Month1+'.01')
              +'                       and  #TmpCredence.CredenceDate<DateAdd(mm,1,'+QuotedStr(Month2+'.01')+')'
              +'                       and  #tmpCredence.ProjectCode in ('+dstring+')),0.0)'
              +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring);
      Executesql(AdoQry,sqltext,1)              ;
      AdoQry_tmp.Next;
    end;
  try
    AdoQry.Free;
  except

  end;
  selectfromsql:='select * from #cashQry';
  Executesql(AdoQry_Main,'select * from #cashQry Order by projectCode',0);
  lbl_Condition.Caption:='会计期间从：'+Month1+'　到　'+Month2;
end;

procedure TFrm_Gl_Qry_CashReport.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_CashReport:=nil;
end;

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

end.