gl_qry_gain.pas

一个MRPII系统源代码版本

unit Gl_Qry_Gain;

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_Gain = Class(TFrm_Base_Qry)
    AdoQry_MainprojectName: TStringField;
    AdoQry_Mainplineno: TIntegerField;
    AdoQry_MainMonthSum: TFloatField;
    AdoQry_MainYearsum: TFloatField;
    AdoQry_MainprojectCode: TStringField;
    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_Gain: TFrm_Gl_Qry_Gain;

implementation
uses Gl_Qry_Gain_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_Gain.initReport;
begin
  inherited;
  ExtprintReport.Subtitle2:='编制单位：Ext';
  ExtprintReport.Subtitle3:=lbl_Condition.Caption;
//  ExtprintReport.Subtitle4:='单位：元';
end;

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

  sqltext:='select Gl_AccountSubject.kmCode, Gl_AccountSubject.KmProperty,  '
           +'        Gl_AccountSubjectBalance.* '
           +' into #tMpSubjectBalance           '
           +' from Gl_AccountSubjectBalance     '
           +' join Gl_AccountSubject on Gl_AccountSubjectBalance.kmid=Gl_AccountSubject.kmid ';
  Executesql(AdoQry_tmp,sqltext,1);

  sqltext:=' select  projectCode,  '
           +'        projectName,  '
           +'        plineno,      '
           +'        IsSum, '
           +'        MonthSum as Monthkm,  '
           +'        convert(decimal(20,8),0) as MonthSum, '
           +'        Yearsum as Yearkm,    '
           +'        convert(decimal(20,8),0) as Yearsum   '
           +' into #GainQry   '
           +'  from Gl_Gain                       ';

  Executesql(AdoQry_tmp,sqltext,1);
  Executesql(AdoQry_tmp,'select * from #GainQry'
                       +' Order by IsSum,ProjectCode ',0);
  while not AdoQry_tmp.Eof do
    begin
      pretext:='+';
      astring:=quotedstr('');
      dstring:=quotedstr('');
      tmpproject:=AdoQry_tmp.fieldbyname('Monthkm').asstring;
      while tmpproject<>'' do
        begin
//          showmessage(tmpproject);
          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;
      If AdoQry_Tmp.fieldbyname('IsSum').AsInteger=0 then 
      sqltext:='update #GainQry'
              +'   set MonthSum=isnull((select sum( case when KmProperty=1 then isnull(DebitBalance,0)   '
              +'                                         else -IsNull(DebitBalance,0) '
              +'                                    end )  '
              +'                               -Sum( case when KmProperty=1 then IsNull(CreditBalance,0) '
              +'                                          else -IsNull(CreditBalance,0) '
              +'                                     end )        '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+astring+')),0.0) ' 
              +'                   -isnull((select sum( case when KmProperty=1 then isnull(DebitBalance,0)   '
              +'                                         else -IsNull(DebitBalance,0) '
              +'                                    end )  '
              +'                               -Sum( case when KmProperty=1 then IsNull(CreditBalance,0) '
              +'                                          else -IsNull(CreditBalance,0) '
              +'                                     end )          '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+dstring+')),0.0)'
              +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring)
      else 
        SQlText := 'Update #GainQry '
                  +'   set MonthSum = (select IsNull(MonthSum,0.0) from #GainQry '
                  +'                    where ProjectCode In ('+AString+'))'
                  +'                 -(select IsNull(MonthSum,0.0) from #GainQry '
                  +'                    where ProjectCode In ('+DString+'))'
                  +' where ProjectCode='+QuotedStr(AdoQry_Tmp.fieldbyname('ProjectCode').AsString);
              
//      showmessage(sqltext);
      Executesql(AdoQry,sqltext,1)              ;
//本年累计数
      pretext:='+';
      astring:=quotedstr('');
      dstring:=quotedstr('');
      tmpproject:=AdoQry_tmp.fieldbyname('Yearkm').asstring;
      while tmpproject<>'' do
        begin
 //         showmessage(tmpproject);
          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;
      If AdoQry_Tmp.fieldbyname('IsSum').AsInteger =0 then 
      sqltext:='update #GainQry'
              +'   set Yearsum=isnull((select sum( case when KmProperty=1 then isnull(DebitBalance,0)   '
              +'                                         else -IsNull(DebitBalance,0) '
              +'                                    end )  '
              +'                               -Sum( case when KmProperty=1 then IsNull(CreditBalance,0) '
              +'                                          else -IsNull(CreditBalance,0) '
              +'                                     end )        '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+inttostr(strtoint(copy(Month,1,4)))
//              +'                       and  #tMpSubjectBalance.AccountperiodMonth=1'
              +'                       and  #tMpSubjectBalance.kmCode in ('+astring+')),0.0) ' 
              +'                   -isnull((select sum( case when KmProperty=1 then isnull(DebitBalance,0)   '
              +'                                         else -IsNull(DebitBalance,0) '
              +'                                    end )  '
              +'                               -Sum( case when KmProperty=1 then IsNull(CreditBalance,0) '
              +'                                          else -IsNull(CreditBalance,0) '
              +'                                     end )   '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+inttostr(strtoint(copy(Month,1,4)))
  //            +'                       and  #tMpSubjectBalance.AccountperiodMonth=1'
              +'                       and  #tMpSubjectBalance.kmCode in ('+dstring+')),0.0)'
              +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring)
      else 
        SQlText := 'Update #GainQry '
                  +'   set YearSum = (select IsNull(YearSum,0.0) from #GainQry '
                  +'                    where ProjectCode In ('+AString+'))'
                  +'                 -(select IsNull(YearSum,0.0) from #GainQry '
                  +'                    where ProjectCode In ('+DString+'))'
                  +' where ProjectCode='+QuotedStr(AdoQry_Tmp.fieldbyname('ProjectCode').AsString);
  //      showmessage(sqltext);
      Executesql(AdoQry,sqltext,1)              ;
      AdoQry_tmp.Next;
    end;
  try
    AdoQry.Free;
  except

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

procedure TFrm_Gl_Qry_Gain.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_Gain:=nil;
end;

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

end.