sfc_monthsumqrybcppj_c.pas

一个MRPII系统源代码版本

      +' insert #MonthSumQry2 select Dept as DeptCode,ItemCode,sum(Invlmqty) as Invlmqty,'
      +' sum(InvlmAmount) as InvlmAmount,sum(Invblncqty)+sum(Invoutqty)-sum(Invlmqty) as InvInqty,'
      +' 0,sum(Invoutqty) as Invoutqty,'
      +' 0,'
      +' sum(Invblncqty) as Invblncqty,0 as InvblncAmount  '
      +' from #MonthSumQry group by Dept,ItemCode' ;
    AdoQry_Tmp.ExecSQL;


  //计算本月生产金饿
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=' delete from #sonItemCode '
      +' insert #sonItemCode '
      +' select #MonthSumQry2.DeptCode,#MonthSumQry2.ItemCode,Bom.ItemCode,'
      +' Bom.Bomqty,Bom.BomScrAp_Percent,#MonthSumQry2.InvInqty*Bom.Bomqty*(1+Bom.BomScrAp_Percent/100),Bom.BomItemType,0 '
      +' from #MonthSumQry2,Bom '
      +' where Bom.ite_ItemCode=#MonthSumQry2.ItemCode ';
    AdoQry_Tmp.ExecSQL;

    with AdoQry_Tmp do
    begin
      Close;
      sql.text:='select DeptCode,ite_ItemCode,ItemCode from #sonItemCode'
        +' where (BomItemType=3 or BomItemType=6) and flag=0 ';
      open;
      First;
      while not eof do
      begin
        AdoQry_tmp1.Close;
        AdoQry_tmp1.sql.Text:='insert #sonItemCode '
                        +' select a.DeptCode,a.ite_ItemCode,a.ItemCode,'
          +' a.Bomqty,a.BomScrAp_Percent,a.sonqty,a.BomItemType,1 '
          +' from (select #sonItemCode.DeptCode,#sonItemCode.ite_ItemCode as ite_ItemCode,Bom.ItemCode, '
          +' Bom.Bomqty,Bom.BomScrAp_Percent,#sonItemCode.sonqty*Bom.Bomqty*(1+Bom.BomScrAp_Percent/100) as sonqty,'
          +' Bom.BomItemType from #sonItemCode,Bom '
          +' where (#sonItemCode.BomItemType=3 or #sonItemCode.BomItemType=6) '
          +' and #sonItemCode.flag=0 '
          +' and #sonItemCode.ItemCode=Bom.ite_ItemCode) a ';
        AdoQry_tmp1.ExecSQL;
        AdoQry_tmp1.Close;
        AdoQry_tmp1.sql.Text:='update #sonItemCode '
                +' set flag=2 where flag=0';
        AdoQry_tmp1.ExecSQL;
        AdoQry_tmp1.Close;
        AdoQry_tmp1.sql.Text:='update #sonItemCode '
                +' set flag=0 where flag=1';
        AdoQry_tmp1.ExecSQL;
        Close;
        sql.text:='select DeptCode,ite_ItemCode,ItemCode from #sonItemCode'
           +' where (BomItemType=3 or BomItemType=6) and flag=0 ';
        open;
        First;
      end;
    end;
    //系统自动分配原材料金额
    if CheckBox1.Checked then
    begin
      //删除WIPBCPAmount中的没有必要分配的记录
      with AdoQry_tmp do
      begin
        Close;
        sql.text:='delete from wipbcpAmount where wiPMonth='+quotedstr(MEdt_Month.Text)+
                ' and DeptCode='+quotedstr(Extedt_Dept.Text)+
                ' and ItemCode in ('+
                ' select distinct #MonthSumQry1.ItemCode '+
                ' from #MonthSumQry1 '+
                ' join Bom on Bom.ItemCode=#MonthSumQry1.ItemCode '+
                ' and Bom.ite_ItemCode in (select ItemCode from #MonthSumQry2))';
        execsql;
      end;
      //计算每个半成品的金饿,将材料耗用金额分滩到每个半成品上
      with AdoQry_tmp do
      begin
        Close;
        sql.text:='select DeptCode,ItemCode,InvoutAmount from #MonthSumQry1 ';
        open;
        First;
        while not eof do
        begin
          AdoQry_tmp1.Close;
          AdoQry_tmp1.sql.text:='select isnull(sum(sonqty),0) as AllBomqty from #sonItemCode where ItemCode='
                               +QuotedStr(AdoQry_tmp.fieldbyname('ItemCode').asstring)
                               +' and DeptCode='+QuotedStr(AdoQry_tmp.fieldbyname('DeptCode').asstring);
          AdoQry_tmp1.open;
          AllBomqty:=AdoQry_tmp1.fieldbyname('AllBomqty').Value;
          if AllBomqty<>0 then
          begin
            AdoQry_tmp1.Close;
            AdoQry_tmp1.sql.text:='update #MonthSumQry2 set InvInAmount=InvInAmount+#sonItemCode.sonqty/'
                            +floattostr(AllBomqty)+'*'+AdoQry_tmp.fieldbyname('InvoutAmount').asstring
                            +' from #sonItemCode where #sonItemCode.ite_ItemCode=#MonthSumQry2.ItemCode'
                            +' and #sonItemCode.ItemCode='+QuotedStr(AdoQry_tmp.fieldbyname('ItemCode').asstring)
                            +' and #sonItemCode.DeptCode=#MonthSumQry2.DeptCode';
            AdoQry_tmp1.ExecSQL;
            //记录分配结果
            AdoQry_tmp1.Close;
            AdoQry_tmp1.sql.text:='insert wipbcpAmount(wiPMonth,'
                                  +'DeptCode,'
                                  +'ItemCode,'
                                  +'ite_ItemCode,'
                                  +'wipqty,'
                                  +'wipAmount) '
                                  +'select '+QuotedStr(MEdt_Month.Text)+','
                                  +QuotedStr(ExtEdt_Dept.Text)+','
                                  +QuotedStr(AdoQry_tmp.fieldbyname('ItemCode').asstring)+','
                                  +'#MonthSumQry2.ItemCode,'
                                  +'#MonthSumQry2.InvInqty,'
                                  +'#sonItemCode.sonqty/'
                                  +floattostr(AllBomqty)+'*'+AdoQry_tmp.fieldbyname('InvoutAmount').asstring
                                  +' from #MonthSumQry2,#sonItemCode '
                                  +' where #sonItemCode.ite_ItemCode=#MonthSumQry2.ItemCode '
                                  +' and #sonItemCode.ItemCode='+QuotedStr(AdoQry_tmp.fieldbyname('ItemCode').asstring)
                                  +' and #sonItemCode.DeptCode=#MonthSumQry2.DeptCode';
            AdoQry_tmp1.ExecSQL;
          end;
          next;
        end;
      end;
    end;
    //计算分配给半成品的金额
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='update #MonthSumQry2 set InvInAmount=a.Amount from '+
                '(select ite_ItemCode,sum(wipAmount) as Amount from wipbcpAmount where wiPMonth='+quotedstr(MEdt_Month.Text)+
                ' and DeptCode='+quotedstr(Extedt_Dept.Text)+
                ' group by ite_ItemCode ) a '+
                ' where #MonthSumQry2.ItemCode=a.ite_ItemCode';
      execsql;
    end;
  end
  else
  begin
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=' delete from #MonthSumQry1 '
      +' insert #MonthSumQry1 select DeptCode as DeptCode,ItemCode,sum(Invlmqty) as Invlmqty,'
      +' sum(InvlmAmount) as InvlmAmount,sum(InvInqty) as InvInqty,'
      +' sum(InvInAmount) as InvInAmount,'
      +' sum(Invblncqty) as Invblncqty,null as InvblncAmount  '
      +' from #MonthSumQry group by DeptCode,ItemCode '
      +' update #MonthSumQry1 set InvblncAmount=Invblncqty*((InvInAmount+InvlmAmount)/(Invlmqty+InvInqty)) '
      +' where (Invlmqty+InvInqty)<>0 and Invblncqty<>0 '
      //对于上期没有结存，本期没有领入，但本期有结存的物料，其价格取wipPrice中的价格
      +' update #MonthSumQry1 set InvblncAmount=Invblncqty*wipPrice.Itemprice from wipPrice '
      +' where (InvInqty+Invlmqty)=0 and Invblncqty<>0 and  '
      +' #MonthSumQry1.ItemCode=wipPrice.ItemCode '
      //计算原材料的本月耗用
      +' update #MonthSumQry1 set InvoutAmount=InvlmAmount+InvInAmount-InvblncAmount, '
      +' Invoutqty=Invlmqty+InvInqty-Invblncqty';
    AdoQry_Tmp.ExecSQL;
  end;
  with AdoQry_tmp do
  begin
    Close;
    sql.text:='update #MonthSumQry2 set InvblncAmount=(InvlmAmount+InvInAmount)/(Invlmqty+InvInqty)*Invblncqty'
           +' where (Invlmqty+InvInqty)<>0 ';
    ExecSQL;
    //对于Invlmqty+InvInqty=0,则从wipPrice中取价格
    Close;
    sql.text:='update #MonthSumQry2 set InvblncAmount=wipPrice.Itemprice*Invblncqty from wipPrice '
           +' where (Invlmqty+InvInqty)=0 and #MonthSumQry2.ItemCode=wipPrice.ItemCode '
           +' and wipPrice.DeptCode=#MonthSumQry2.DeptCode and wipPrice.wiPMonth='
           +QuotedStr(MEdt_Month.Text);
    ExecSQL;
    Close;
    sql.text:=' delete from #MonthSumQry2 where Invlmqty=0 and InvlmAmount=0 '
             +' and InvInqty=0 and InvInAmount=0 and Invblncqty=0 and InvblncAmount=0 ';
    ExecSQL;
  end;
    Condition:='Select #MonthSumQry2.ItemCode'
    +',Item.ItemName'
    +',#MonthSumQry2.ItemCode+'' ''+Item.ItemName As ItemCodeName'
    +',#MonthSumQry2.DeptCode+'' ''+Dept.DeptName As DeptCodeName'
    +',Uom.UomName'
    +',case when isnull(#MonthSumQry2.InvLMQty,0)=0 then null '
    +' else #MonthSumQry2.InvLMQty end as InvLMQty'
    +',case when isnull(#MonthSumQry2.InvoutQty,0)=0 then null '
    +' else #MonthSumQry2.InvoutQty end as InvoutQty'
    +',case when IsNull(#MonthSumQry2.InvblncQty,0)+IsNull(#MonthSumQry2.InvoutQty,0)'
    +'-IsNull(#MonthSumQry2.InvlmQty,0)=0 then null '
    +' else IsNull(#MonthSumQry2.InvblncQty,0)+IsNull(#MonthSumQry2.InvoutQty,0)'
    +'-IsNull(#MonthSumQry2.InvlmQty,0) end  As InvInQty'
    +',case when isnull(#MonthSumQry2.InvBlncQty,0)=0 then null'
    +' else #MonthSumQry2.InvBlncQty end as InvBlncQty'
    +',Case When isnull(#MonthSumQry2.InvLMQty,0)=0 or isnull(#MonthSumQry2.InvLMAmount,0)=0 then null'
    +' else #MonthSumQry2.InvLMAmount/#MonthSumQry2.InvLMQty end As Price1'
    +',Case When isnull(#MonthSumQry2.InvInQty,0)=0 or isnull(#MonthSumQry2.InvInAmount,0)=0 then null'
    +' else #MonthSumQry2.InvInAmount/#MonthSumQry2.InvInqty end As Price2'        //车间的本月生产
    +',Case When IsNull(#MonthSumQry2.InvoutQty,0)=0 Then null'
    +' Else (IsNull(#MonthSumQry2.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry2.InvInAmount,0)'
    +'-IsNull(#MonthSumQry2.InvBlncAmount,0))'
    +'/#MonthSumQry2.InvoutQty end As Price3'
    +',Case When isnull(#MonthSumQry2.InvBlncQty,0)=0 '
    +' or isnull(#MonthSumQry2.InvBlncAmount,0)=0 then null'
    +' else #MonthSumQry2.InvBlncAmount/#MonthSumQry2.InvBlncQty end As Price4'

    +',case when isnull(#MonthSumQry2.InvLMAmount,0)=0 then null'
    +' else #MonthSumQry2.InvLMAmount end as aInvLMQty'
    +',case when isnull(#MonthSumQry2.InvInAmount,0)=0 then null'
    +' else #MonthSumQry2.InvInAmount end as aInvInQty'
    +',case when (IsNull(#MonthSumQry2.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry2.InvInAmount,0)'
    +'-IsNull(#MonthSumQry2.InvBlncAmount,0))=0 then null '
    +' else (IsNull(#MonthSumQry2.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry2.InvInAmount,0)'
    +'-IsNull(#MonthSumQry2.InvBlncAmount,0)) end As aInvOutQty'
    +',case when isnull(#MonthSumQry2.InvBlncAmount,0)=0 then null'
    +' else #MonthSumQry2.InvBlncAmount end as aInvBlncQty'
    +' From #MonthSumQry2'
    +' Left Join Item On #MonthSumQry2.ItemCode=Item.ItemCode'
    +' Left Join Dept On #MonthSumQry2.DeptCode=Dept.DeptCode'
    +' Left Join Uom On Item.UomCode=Uom.UomCode';

    AdoQry_tmp1.Connection:=AdoQry_Tmp.Connection;
    with AdoQry_Tmp do
    begin
      Close;
      sql.text:=Condition;
      open;
      First;
      while not eof do
      begin
        if fieldbyname('aInvBlncQty').AsFloat<>0 then
        begin
          AdoQry_tmp1.Close;
          AdoQry_tmp1.sql.Text:='update wipInv set wipAmount='+AdoQry_tmp.fieldbyname('aInvBlncQty').AsString
                               +' where wipInv.DeptCode='+QuotedStr(ExtEdt_Dept.Text)
                               +' and wipInvMonth='+QuotedStr(MEdt_Month.Text)
                               +' and wipInv.ItemCode='+QuotedStr(AdoQry_tmp.fieldbyname('ItemCode').AsString);
          AdoQry_tmp1.ExecSQL;
        end;
        Next;
      end;
    end;

    ConditionHint:='部门：'+Lbl_Dept.Caption+'/月份： '+MEdt_Month.Text;
    Frm_Sfc_MonthSumQrybcppj.DeptCode:=Extedt_Dept.text;
    Frm_Sfc_MonthSumQrybcppj.theMonth:=MEdt_Month.Text;
    ModalResult:=mrOk;
end;

procedure TFrm_Sfc_MonthSumQrybcppj_C.FormCreate(Sender: TObject);
begin
  inherited;
  MEdt_Month.Text:=FormatDateTime('yyyy.mm',IncMonth(Now,-1));
end;

end.