gl_qry_balancesheet.pas

一个MRPII系统源代码版本

            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 #tMpSheet'
              +'   set lastBalance=isnull((select sum(isnull(FirstBalance,0)'
              +'                                 +(Case when KmProperty=1 then DebitBalance '
              +'                                   else -DebitBalance end) '
              +'                                 -(case when KmProperty=1 then CreditBalance '
              +'                                   else -CreditBalance end )                     )  '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month2,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month2,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+astring+')),0) ' 
              +'                   -isnull((select sum(isnull(FirstBalance,0)'
              +'                                 +(Case when KmProperty=1 then DebitBalance '
              +'                                   else -DebitBalance end) '
              +'                                 -(case when KmProperty=1 then CreditBalance '
              +'                                   else -CreditBalance end )                     )  '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month2,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month2,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+dstring+')),0)'
              +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring)
     else 
       SqlText := 'update #TMpSheet '
                 +'   set  LastBalance=(select Sum(IsNull(LastBalance,0.0)) from #TMpSheet  '
                 +'                       where #TMpSheet.ProjectCode in ('+astring+'))'  
                 +'                    -(select Sum(IsNull(LastBalance,0.0)) from #TMpSheet  '
                 +'                       where #TMpSheet.ProjectCode in ('+dstring+'))' 
                 +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring);
              
//      showmessage(sqltext);
     Executesql(AdoQry,sqltext,1)              ;

//期初数1
      pretext:='+';
      astring:=quotedstr('');
      dstring:=quotedstr('');
      tmpproject:=AdoQry_tmp.fieldbyname('Firstproject1').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;
      If AdoQry_Tmp.fieldbyname('IsSum').AsInteger=0 then 
      sqltext:='update #tMpSheet'
              +'   set FirstBalance1=isnull((select sum(isnull(FirstBalance,0))  '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month1,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month1,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+astring+')),0) ' 
              +'                   -isnull((select sum(isnull(FirstBalance,0))  '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month1,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month1,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+dstring+')),0)'
              +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring)
      else 
        SqlText := 'update #TMpSheet '
         +'   set  FirstBalance1=(select Sum(IsNull(FirstBalance1,0.0)) from #TMpSheet  '
         +'                       where #TMpSheet.ProjectCode in ('+astring+'))'  
         +'                    -(select Sum(IsNull(FirstBalance1,0.0)) from #TMpSheet  '
         +'                       where #TMpSheet.ProjectCode in ('+dstring+'))' 
         +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring);
              
//      showmessage(sqltext);
      Executesql(AdoQry,sqltext,1)              ;

//期末数1
      pretext:='+';
      astring:=quotedstr('');
      dstring:=quotedstr('');
      tmpproject:=AdoQry_tmp.fieldbyname('lastproject1').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;
      If AdoQry_Tmp.fieldbyname('IsSum').AsInteger=0 then
      sqltext:='update #tMpSheet'
              +'   set lastBalance1=isnull((select sum(isnull(FirstBalance,0)'
              +'                                 +(Case when KmProperty=1 then DebitBalance '
              +'                                   else -DebitBalance end) '
              +'                                 -(case when KmProperty=1 then CreditBalance '
              +'                                   else -CreditBalance end )                     )  '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month2,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month2,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+astring+')),0) ' 
              +'                   -isnull((select sum(isnull(FirstBalance,0)'
              +'                                 +(Case when KmProperty=1 then DebitBalance '
              +'                                   else -DebitBalance end) '
              +'                                 -(case when KmProperty=1 then CreditBalance '
              +'                                   else -CreditBalance end )                     )  '
              +'                       from #tMpSubjectBalance     '
              +'                     where  #tMpSubjectBalance.AccountperiodYear='+copy(Month2,1,4)
              +'                       and  #tMpSubjectBalance.AccountperiodMonth='+copy(Month2,6,2)
              +'                       and  #tMpSubjectBalance.kmCode in ('+dstring+')),0)'
              +' where projectCode='+quotedstr(AdoQry_tmp.fieldbyname('projectCode').asstring)
       else
        SqlText := 'update #TMpSheet '
         +'   set  lastBalance1=(select Sum(IsNull(lastBalance1,0.0)) from #TMpSheet  '
         +'                       where #TMpSheet.ProjectCode in ('+astring+'))'  
         +'                    -(select Sum(IsNull(lastBalance1,0.0)) from #TMpSheet  '
         +'                       where #TMpSheet.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 #tMpSheet';
  Executesql(AdoQry_Main,'select * from #tMpSheet Order by projectCode',0);
  lbl_Condition.Caption:='会计期间从 '+Month1+' 到 '+Month;
  
end;

procedure TFrm_Gl_Qry_BalanceSheet.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_BalanceSheet:=nil;
end;

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

end.