inv_monthsum2.pas

一个MRPII系统源代码版本

     '  where a.WhCode=WhPMonthSum.WhCode'+
     '    and a.InvBillMonth=WhPMonthSum.InvMonth'+
     '    and a.ItemCode=WhPMonthSum.ItemCode'+
     '    and a.WhPositionCode=WhPMonthSum.WhPositionCode');

{  //将其它出库单据汇总后，保存在临时表中，下面会使用
  AdoCmdExecSql
    ('正在计算本厂仓库货位发出汇总数据......',
     'select sum(bl.InvBillQty) as InvOutQty,'+
     '    sum(bl.InvBillNoTaxAmount) as InvOutAmount,'+
     '    b.WHCode,bl.ItemCode '+
     '  into #Tmp0299Bill'+
     '  from InvOutBillLine bl,InvOutBill b '+
     '  where b.InvBillMonth='+quotedstr(CalcMonth)+
     '    and b.InvBillId=bl.InvBillId'+
     '    and b.BillTypeCode = ''0299'''+
     '  group by b.WhCode,bl.ItemCode');
}
end;

//本厂月末数据计算
procedure TFrm_Inv_MonthSum2.CalcBlncData;
begin
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.sql.text:='drop table #TmpAveragePrice';
    AdoQry_Tmp.execsql
  except
  end;
  //产生月平均价
  AdoCmdExecSql
    ('正在产生本厂物料月平均价......',
     'select s.WHCode,s.ItemCode, '+
     '    (InvLMAmount+InvInAmount)/(InvLMQty+InvInQty) Price '+
     '  into #TmpAveragePrice'+
     '  from InvMonthSum s'+
     '  where s.InvMonth='+quotedstr(CalcMonth)+
     '    and InvLMQty+InvInQty<>0');
  //计算结存金额、数量、发出金额、发出单价
  AdoCmdExecSql
    ('正在计算本厂物料月末结存数据......',
     'update InvMonthSum'+
     '  set InvMonthSum.InvOutAmount=InvMonthSum.InvOutQty*isnull(Ap.Price,0),'+
     '    InvBlncQty=InvLMQty+InvInQty-InvMonthSum.InvOutQty,'+
     '    InvBlncPrice=isnull(Ap.Price,0)'+
     '  from InvMonthSum'+
     '  left join #TmpAveragePrice Ap'+
     '    on InvMonthSum.WhCode=Ap.WhCode'+
     '      and InvMonthSum.ItemCode=Ap.ItemCode'+
     '  where InvMonthSum.InvMonth='+quotedstr(CalcMonth));
  AdoCmdExecSql
    ('正在计算本厂物料月末结存数据......',
     'update InvMonthSum'+
     '  set InvBlncAmount=InvLMAmount+InvInAmount-InvOutAmount,'+
     '    InvOutPrice=InvBlncPrice'+
     '  where InvMonth='+quotedstr(CalcMonth));
  AdoCmdExecSql
    ('正在计算本厂物料月末结存数据......',
     'update InvMonthSum'+
     '  set InvBlncPrice=InvBlncAmount/InvBlncQty'+
     '  where InvMonth='+quotedstr(CalcMonth)+
     '    and InvBlncQty<>0 and InvBlncPrice=0');
  //处理没有上月结存、收入，只有退库发生物料
  //从用户定义的退库价格中获取出库价格

  AdoCmdExecSql
    ('正在计算本厂仓库货位月末结存数量......',
     'update WhPMonthSum'+
     '  set InvBlncQty=InvLMQty+InvInQty-InvOutQty'+
     '  where InvMonth='+quotedstr(CalcMonth));
  //非计划价核算仓库、非0299其它出库、非1101货位移动的出库单据(后来取消了0299)
  //价格用月平均价替换

  AdoCmdExecSql
    ('正在用月平均价替换出库单据的价格......',
     'update InvOutBillLine'+
     '  set InvBillNoTaxPrice=Ap.Price,'+
     '    InvBillNoTaxAmount=Convert(decimal(12,2),InvBillQty*Ap.Price),'+
     '    InvBillPrice=Ap.Price,'+
     '    InvBillAmount=Convert(decimal(12,2),InvBillQty*Ap.Price) '+
     '  from InvOutBill B,#TmpAveragePrice Ap,Warehouse W '+
     '  where b.InvBillMonth='+quotedstr(CalcMonth)+
     '    and b.InvBillId=InvOutBillLine.InvBillId'+
     '    and b.AmountAdjust=0 '+
     '    and B.WHCode=W.WHCode'+
     '    and W.PriceType=0'+
     '    and b.BillTypeCode <>''1101'''+  //not in (''1101'',''0299'')'+
     '    and b.BillTypeCode not in (''0205'',''0206'')  '+
     '    and Ap.WhCode=b.WhCode'+
     '    and Ap.ItemCode=InvOutBillLine.ItemCode ');

  AdoCmdExecSql
    ('正在用退库价替换出库单据的价格......',
     'update InvOutBillLine'+
     '  set InvBillNoTaxPrice=Ap.Price,'+
     '    InvBillNoTaxAmount=Convert(decimal(12,2),InvBillQty*Ap.Price),'+
     '    InvBillPrice=Ap.Price,'+
     '    InvBillAmount=Convert(decimal(12,2),InvBillQty*Ap.Price) '+
     '  from InvOutBill B,BackPrice Ap '+
     '  where b.InvBillMonth='+quotedstr(CalcMonth)+
     '    and b.InvBillId=InvOutBillLine.InvBillId'+
     '    and b.AmountAdjust=0 '+
     '    and B.WHCode=Ap.WHCode'+
     '    and b.BillTypeCode <>''1101'''+  //not in (''1101'',''0299'')'+
     '    and Ap.WhCode=b.WhCode'+
     '    and b.BillTypeCode not in (''0205'',''0206'')  '+
     '    and Ap.ItemCode=InvOutBillLine.ItemCode ');

  AdoCmdExecSql
  ///入库金额也应该是InvBillOUTLINE中InvBillQTY>0的记录的金额 wxp
    ('正在进行委外加工材料收入方金额汇总......',
     'update OpMonthSum '+
     '  set InvInAmount=a.Amount,'+
     '     InvInPrice=case when InvInQty=0 then 0 else a.Amount/InvInQty end'+
     '  from'+
     '   (select sum(bl.InvBillNoTaxAmount) as Amount,'+
     '        b.VendorCode,'+
     '        bl.ItemCode,'+
     '        b.InvBillMonth '+
     '      from InvOutBillLine bl,InvOutBill b '+
     '      where b.InvBillMonth='+quotedstr(CalcMonth)+
     '        and bl.InvBillId=b.InvBillId and bl.InvBillqty>=0 '+
     '        and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1))'+
     '      group by b.InvBillMonth,b.VendorCode,bl.ItemCode) a'+
     '  where a.VendorCode=OpMonthSum.VendorCode'+
     '    and a.InvBillMonth=OpMonthSum.InvMonth'+
     '    and a.ItemCode=OpMonthSum.ItemCode');

  //处理委外材料收发存，此时的计算只是初次计算，如果委外材料本身是
  //委外加工产品，在计算加工费的过程中，将再次计算
  AdoCmdExecSql
    ('正在产生委外材料月平均价......',
     'select VendorCode,ItemCode, '+
     '    (InvLMAmount+InvInAmount)/(InvLMQty+InvInQty) Price '+
     '  into #TmpOPAveragePrice'+
     '  from OpMonthSum s'+
     '  where s.InvMonth='+quotedstr(CalcMonth)+
     '    and InvLMQty+InvInQty<>0');
  //计算结存金额、数量、发出金额、发出单价
  AdoCmdExecSql
    ('正在计算委外材料月末结存数据......',
     'update OpMonthSum'+
     '  set InvOutAmount=InvOutAmount+InvOutQty*isnull(Ap.Price,0),'+
     '    InvBlncQty=InvLMQty+InvInQty-InvOutQty,'+
     '    InvOutPrice=isnull(Ap.Price,0),'+
     '    InvBlncPrice=isnull(Ap.Price,0)'+
     '  from OpMonthSum'+
     '  left join #TmpOPAveragePrice Ap'+
     '    on OpMonthSum.VendorCode=Ap.VendorCode'+
     '      and OpMonthSum.ItemCode=Ap.ItemCode'+
     '  where OpMonthSum.InvMonth='+quotedstr(CalcMonth));
  AdoCmdExecSql
    ('正在计算委外材料月末结存数据......',
     'update OpMonthSum'+
     '  set InvBlncAmount=InvLMAmount+InvInAmount-InvOutAmount'+
     '  where InvMonth='+quotedstr(CalcMonth));
//   with AdoQry_tmp do
  AdoCmdExecSql
    ('正在删除临时表......',
     'drop table #TmpAveragePrice,#TmpOPAveragePrice');
end;

procedure TFrm_Inv_MonthSum2.CalcOPBill;
var
  InvBillId,InvBillLineNo:integer;
begin
  //数据校验，删除OPBill中与父项相同的记录
  with AdoQry_Tmp do
  begin
    Close;
    sql.text:='delete from RealOpQty where ite_ItemCode=ItemCode '+
              'delete  opBill from InvInBillline '+
              'where opBill.InvBillid=InvInBillline.InvBillid and '+
              'opBill.ItemCode=InvInBillline.ItemCode ';
    ExecSQL;
  end;

  AdoCmdExecSql('正在计算委外加工产品材料费......','');
  with AdoDS_OpBill do
  begin
    Close;
    //查询出所有委外入库单，逐行进行计算材料费
    //对于非历史数据来说，InitData总是=0的，使用它是为了找一个总为0，且能写的字段
    CommandText:=
      'select InitData flag,bl.InvBillID,bl.InvBillLineNo,b.VendorCode,b.WhCode,'+
      '    b.BillTypeCode,b.InvBillNo,bl.ItemCode,bl.InvBillQty'+
      '  into #tmpopBill'+
      '  from InvInBill b,InvInBillLine bl,'+
      '    (select distinct InvBillID from OPBill) o'+
      '  where b.InvBillID=bl.InvBillID'+
      '    and (b.BillTypeCode in (''0103'',''1202'') or (b.BillTypeCode=''0199'' and b.OpBill=1))'+
      '    and b.InvBillMonth='+quotedstr(CalcMonth)+
      '    and b.InvBillID=o.InvBillID'+
      '  Order by bl.ItemCode,b.WhCode '+
      'select * from #tmpOPBill Order by ItemCode,WhCode';
    open;
    First;
    while not eof do
    begin
      if fieldbyname('flag').asinteger=1 then
      begin
        next;
        continue;
      end;
      InvBillId:=fieldbyname('InvBillId').asinteger;
      InvBillLineNo:=fieldbyname('InvBillLineNo').asinteger;
      OpCalculate(fieldbyname('ItemCode').asstring);
      locate('InvBillId;InvBillLineNo',varArrayOf([InvBillId,InvBillLineNo]),[]);
      next;
    end;
  end;
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.sql.text:='drop table #TmpOPBill';
    AdoQry_Tmp.execsql;
  except
  end;
end;


//本厂月末数据最后计算 wxp
procedure TFrm_Inv_MonthSum2.CalcBlncendData;
var
  tmpwhCode,tmpItemCode:string;
  tmpPrice:real;
begin
  //用供应商的价格替换出库单据的价格 InvBillline.InvBillqty<0
  AdoCmdExecSql
    ('正在处理出库的价格......',
    'update InvOutBillLine'+
    '  set InvBillNoTaxPrice=op.InvblncPrice,'+
    '    InvBillNoTaxAmount=InvBillQty*op.InvblncPrice'+
    '  from InvOutBill b,OpMonthSum op,Warehouse W '+
    '  where InvOutBillLine.InvBillID=b.InvBillID and b.AmountAdjust=0 and InvOutBillline.InvBillqty<0 '+
    '    and b.InvBillMonth='+quotedstr(CalcMonth)+
    '    and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.opBill=1 and b.AmountAdjust=0))'+
    '    and b.VendorCode=op.VendorCode'+
    '    and b.whCode=w.whCode'+
    '    and w.PriceType=0'+
    '    and InvOutBillLine.ItemCode=op.ItemCode '+
    '    and op.InvMonth='+quotedstr(CalcMonth));

  //对于本厂上期结存+本期入库=0而且有出库的物料，
  //其计算月平均价的收入方应该是委外红冲退料部分（InvBillline.InvBillqty<0）
  AdoQry_Tmp.Close;
  AdoQry_Tmp.sql.text:='select Inv.whCode,Inv.ItemCode from InvMonthSum Inv,'+
    '(select b.whCode,bl.ItemCode,sum(isnull(InvBillqty,0)) qty '+
    ' from InvOutBillline bl,InvOutBill b'+
    ' where bl.InvBillID=b.InvBillID and b.InvBillMonth='+quotedstr(CalcMonth)+
    ' and InvBillqty>0 and b.BillTypeCode<>''1101'''+
    ' and b.BillTypeCode not in (''0205'',''0206'')' +
    ' group by whCode,ItemCode) a '+
    '  where Inv.InvMonth='+quotedstr(CalcMonth)+
    '  and Inv.ItemCode=a.ItemCode and Inv.whCode=a.whCode'+
    '  and Inv.InvInqty+Inv.Invlmqty=0 and a.qty>0 ';
  AdoQry_Tmp.open;
  AdoQry_Tmp.First;
  while not AdoQry_tmp.eof do
  begin
     tmpwhCode:=AdoQry_tmp.fieldbyname('whCode').asstring;
     tmpItemCode:=AdoQry_tmp.fieldbyname('ItemCode').asstring;
     AdoQry_Temp.Close;
     AdoQry_Temp.SQL.Text:='select sum(isnull(InvBillqty,0)) qty,sum(isnull(InvBillNoTaxAmount,0)) Amount'+
      '  from InvOutBillline bl,InvOutBill b '+
      ' where bl.InvBillID=b.InvBillID and b.InvBillMonth='+quotedstr(CalcMonth)+
      ' and InvBillqty<0 and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and opBill=1))'+
      ' and whCode='+quotedstr(tmpwhCode)+
      ' and ItemCode='+quotedstr(tmpItemCode);
     AdoQry_Temp.open;
     if AdoQry_Temp.fieldbyname('qty').Value=null then
     begin
       AdoQry_tmp.next;
       Continue;
     end;
     if AdoQry_Temp.fieldbyname('qty').Value<>0 then
        tmpPrice:=abs(AdoQry_Temp.fieldbyname('Amount').asfloat)/abs(AdoQry_Temp.fieldbyname('qty').AsFloat)
     else
     begin
        tmpPrice:=0;
        AdoQry_tmp.next;
        Continue;
     end;

     AdoCmdExecSql
      ('正在计算出库的价格......',
      'update InvOutBillline'+
      '  set InvBillNoTaxPrice='+floattostr(tmpPrice)+
      '    ,InvBillNoTaxAmount=InvBillQty*'+floattostr(tmpPrice)+
      '  from InvOutBill,Warehouse W '+
      ' where InvOutBill.InvBillMonth='+quotedstr(CalcMonth)+
      '    and InvOutBill.InvBillid=InvOutBillline.InvBillid '+
      '    and InvOutBill.AmountAdjust=0 '+
      '    and InvOutBillline.ItemCode='+quotedstr(tmpItemCode)+
      '    and InvOutBill.whCode=w.whCode'+
      '    and w.PriceType=0'+
      '    and InvOutBill.BillTypeCode not in (''0205'',''0206'')  '+
      '    and InvOutBillline.InvBillqty>0'+
      '    and InvOutBill.whCode='+quotedstr(tmpwhCode));

     AdoCmdExecSql
      ('正在计算供应商的结存......',
      'update OpMonthSum'+
      '  set InvInqty=a.qty,'+
      '    InvInAmount=a.Amount '+
      '  from (select i.VendorCode,sum(isnull(il.InvBillqty,0)) as qty,'+
      '  sum(isnull(il.InvBillnotaxAmount,0)) as Amount from InvOutBill i,InvOutBillline il '+
      '  where i.InvBillid=il.InvBillid and il.InvBillqty>=0 and i.InvBillMonth='+
      quotedstr(CalcMonth)+
      ' and il.ItemCode='+quotedstr(tmpItemCode)+
      ' and (i.BillTypeCode=''0202'' or (i.BillTypeCode=''0299'' and opBill=1))'+
      ' group by VendorCode ) a '+
      ' where OpMonthSum.InvMonth='+quotedstr(CalcMonth)+
      '    and OpMonthSum.VendorCode=a.VendorCode'+
      '    and OpMonthSum.ItemCode='+quotedstr(tmpItemCode));

    AdoCmdExecSql
    ('正在计算委外材料月末结存数据......',
     'update OpMonthSum'+
     '  set InvBlncAmount=InvLMAmount+InvInAmount-InvOutAmount,'+
     '      Invblncqty=Invlmqty+InvInqty-Invoutqty'+
     '  where InvMonth='+quotedstr(CalcMonth)+
     '  and ItemCode='+quotedstr(tmpItemCode));

    AdoCmdExecSql
    ('正在计算委外材料月末结存数据......',
     'update OpMonthSum'+