gl_qry_accountbook3.pas

一个MRPII系统源代码版本

         +' group by kmid                                  '
         +' update #tmpResult                              '
         +' set  thisdebitAmount=#tmpthisdebit.AmountBalance, '
         +'      thisdebitsum=#tmpthisdebit.debitBalance    '
         +' from #tmpResult,#tmpthisdebit                   '
         +' where #tmpResult.kmid*=#tmpthisdebit.kmid       ';
Executesql(AdoQry_tmp,sqltext,1);

sqltext:= ' select kmid,sum(AmountBalance) as AmountBalance, '
          +'         sum(creditBalance) as creditBalance      '
          +'  into #tmpthiScredit                             '
          +'  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=2       '
          +'  group by kmid                  '
          +'  update #tmpResult              '
          +'  set  thiScreditAmount=isnull(#tmpthiScredit.AmountBalance,0),  '
          +'       thiScreditsum=isnull(#tmpthiScredit.creditBalance,0)     '
         +'  from #tmpResult,#tmpthiScredit                             '
         +'   where #tmpResult.kmid*=#tmpthiScredit.kmid                ';
Executesql(AdoQry_tmp,sqltext,1)         ;

sqltext:=' select kmid,sum(AmountBalance) as AmountBalance, '
         +'          sum(debitBalance) as debitBalance '
         +'   into #tmpthisYeardebit         '
         +'   from Gl_AccountSubjectBalance  '
         +'   where AccountperiodYear='+copy(Month1,1,4)
         +'     and Balancedirection=1 '
         +'   group by kmid        '
         +'   update #tmpResult    '
         +'   set  thisYeardebitAmount=#tmpthisYeardebit.AmountBalance,'
         +'        thisYeardebitsum=#tmpthisYeardebit.debitBalance '
         +'   from #tmpResult,#tmpthisYeardebit              '
         +'   where #tmpResult.kmid*=#tmpthisYeardebit.kmid  ';
Executesql(AdoQry_tmp,sqltext,1)         ;

sqltext:=' select kmid,sum(AmountBalance) as AmountBalance, '
         +'         sum(creditBalance) as creditBalance   '
         +' into #tmpthisYearcredit                    '
         +' from Gl_AccountSubjectBalance              '
        + ' where AccountperiodYear=2001               '
        + '   and Balancedirection=2                   '
         +' group by kmid                              '
        + ' update #tmpResult                         '
        + ' set  thisYearcreditAmount=isnull(#tmpthisYearcredit.AmountBalance,0), '
        + '      thisYearcreditsum=isnull(#tmpthisYearcredit.creditBalance,0) '
        + ' from #tmpResult,#tmpthisYearcredit               '
        + ' where #tmpResult.kmid*=#tmpthisYearcredit.kmid   ';
Executesql(AdoQry_tmp,sqltext,1)         ;

Executesql(AdoQry_tmp,'select * from Gl_AccountParam',0);
stArtYear:=AdoQry_tmp.fieldbyname('AccountuseYear').asinteger;
stArtMonth:=AdoQry_tmp.fieldbyname('AccountuseMonth').asinteger;
nowYear:=strtoint(copy(Month,1,4));
if stArtYear<>nowYear then
 stArtMonth:=1;

sqltext:='select t1.kmid,     '
         +'          case when t1.Balancedirection<>t2.kmproperty then   '
         +'           (-1)*(sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth='+inttostr(stArtMonth)+' then FirstBalance else 0 end))  '
         +'          +sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then creditBalance else 0 end)       '
         +'          +(-1)*sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then debitBalance else 0 end)   '
         +'          else                                                                                                                 '
         +'         (sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth='+inttostr(stArtMonth)+' then FirstBalance else 0 end))        '
         +'          +sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then creditBalance else 0 end)       '
         +'          +(-1)*sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then debitBalance else 0 end)   '
         +'          end                                                                                     '
         +'         +sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' then debitBalance else 0 end )                  '
         +'         +(-1)*sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' then creditBalance else 0 end) as endBalance   '
         +'   into #tmpendBalance        '
         +'   from Gl_AccountSubjectBalance t1   '
         +'   join Gl_AccountSubject t2 on t1.kmid=t2.kmid     '
         +'    and t2.kmType=1                                 '
         +'   group by t1.kmid,t1.Balancedirection,t2.kmproperty  '

         +'   union                    '
         +'   select t1.kmid,          '
          +'         case when t1.Balancedirection<>t2.kmproperty then    '
          +'          (-1)*(sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth='+inttostr(stArtMonth)+' then FirstBalance else 0 end))    '
          +'         +sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then debitBalance else 0 end)         '
          +'         +(-1)*sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then creditBalance else 0 end)   '
          +'         else                                                                                                                  '
          +'        (sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth='+inttostr(stArtMonth)+' then FirstBalance else 0 end))          '
          +'         +sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then debitBalance else 0 end)         '
          +'         +(-1)*sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' and t1.AccountperiodMonth<='+inttostr(stArtMonth)+' then creditBalance else 0 end)   '
          +'         end                                                                           '
          +'        +sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' then creditBalance else 0 end )       '
          +'        +(-1)*sum(case when t1.AccountperiodYear='+inttostr(nowYear)+' then debitBalance else 0 end)    '
          +'  from Gl_AccountSubjectBalance t1               '
         + '  join Gl_AccountSubject t2 on t1.kmid=t2.kmid    '
         + '   and t2.kmType<>1                               '
         + '  group by t1.kmid,t1.Balancedirection,t2.kmproperty  ';
Executesql(AdoQry_tmp,sqltext,1);

sqltext:='update #tmpResult    '
         +'     set endAmount=stArtAmount+case when t2.kmproperty=1 then thisYeardebitAmount else thisYearcreditAmount end  '
         +'                  -case when t2.kmproperty=1 then thisYearcreditAmount else thisYeardebitAmount end,'
         +'         endsum=t3.endBalance                                 '
         + '    from #tmpResult,Gl_AccountSubject t2,#tmpendBalance t3    '
         +'     where #tmpResult.kmid=t2.kmid          '
         + '     and #tmpResult.kmid=t3.kmid                                                  '
         +'     update #tmpResult                                                              '
         +'     set endPrice=case when endAmount=0 then 0 else endsum/endAmount end,            '
         +'         enddir=case when t1.kmproperty=1 then (case when endsum>0 then 1 else 2 end) '
         +'                 when t1.kmproperty=2 then (case when endsum>0 then 2 else 1 end)   '
         +'                end                       '
         +'     from #tmpResult,Gl_AccountSubject t1 '
         +'     where #tmpResult.kmid=t1.kmid    ';
Executesql(AdoQry_tmp,sqltext,1);
selectfromsql:='select * from #tmpResult';
Orderbyfields:='kmCode';
Executesql(AdoQry_Main,'select * from #tmpResult Order by kmCode ',0);

lbl_Condition.Caption:='会计期间：'+Month1+' 到 '+Month+' /科目代码：'+kmCode1+' 到 '+kmCode2;

  
end;

procedure TFrm_Gl_Qry_AccountBook3.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Gl_Qry_AccountBook3:=nil;
end;

procedure TFrm_Gl_Qry_AccountBook3.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
try
 Executesql(AdoQry_tmp,'drop table #tmpResult,#tmpendBalance,#tmpthisYeardebit,#tmpthisYearcredit,#tmpthisdebit,#tmpthiScredit',1);
except
end;
end;

end.