inv_monthsum.pas

一个MRPII系统源代码版本

      +',InvInBill.InvBillId'
      +',InvInBill.BillTypeCode'
      +',InvInBill.InvBillNo'
      +',InvInBill.VendorCode'
      +',InvInBill.OpBill'
      +' From InvInBill'
      +' Join Warehouse On InvInBill.WHCode=Warehouse.WHCode'
      +' Where InvInBill.InvBillMonth='''+UpdateMonth+''''
      +' And Warehouse.PriceType=0'
      +' And InvInBill.BillTypeCode In (''0101'',''0102'',''0199'',''0103'''
      +',''1201'',''1202'')';
    AdoQry_Tmp.ExecSQL;
    //插入出库数据
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Insert #CloseBook'
      +' Select InvOutBill.WHCode'
      +',InvOutBill.InvBillCreateTime'
      +',InvOutBill.InvBillId'
      +',InvOutBill.BillTypeCode'
      +',InvOutBill.InvBillNo'
      +',InvOutBill.VendorCode'
      +',InvOutBill.OpBill'
      +' From InvOutBill'
      +' Join Warehouse On InvOutBill.WHCode=Warehouse.WHCode'
      +' Where InvOutBill.InvBillMonth='''+UpdateMonth+''''
      +' And Warehouse.PriceType=0'
      +' And ((InvOutBill.BillTypeCode In (''0202'',''0299''))'
      +' Or ((InvOutBill.BillTypeCode In (''0201'',''0203'',''0204''))'
      +' And (InvOutBill.InvBillWHchck=1)))';
    AdoQry_Tmp.ExecSQL;
    {}
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Insert #CloseBook'
      +' Select InvOutBill.WHCode'
      +',InvOutBill.InvBillCreateTime'
      +',InvOutBill.InvBillId'
      +',InvOutBill.BillTypeCode'
      +',InvOutBill.InvBillNo'
      +',InvOutBill.VendorCode'
      +',InvOutBill.OpBill'
      +' From InvOutBill'
      +' Join Warehouse On InvOutBill.WHCode=Warehouse.WHCode'
      +' Where InvOutBill.InvBillMonth='''+UpdateMonth+''''
      +' And Warehouse.PriceType=1'
      +' And InvOutBill.BillTypeCode In (''0202'')';
    AdoQry_Tmp.ExecSQL;
    //打开临时表,按照时间顺序排序
    AdoQry_Temp.Close;
    AdoQry_Temp.SQL.Text:='Select #CloseBook.WHCode'
      +',#CloseBook.InvBillCreateTime'
      +',#CloseBook.InvBillId'
      +',#CloseBook.BillTypeCode'
      +',#CloseBook.InvBillNo'
      +',#CloseBook.VendorCode'
      +',#CloseBook.OpBill'
      +',BillType.BillTypeName'
      +',Warehouse.WHName'
      +',Warehouse.PriceType'
      +' From #CloseBook'
      +' Join BillType On #CloseBook.BillTypeCode=BillType.BillTypeCode'
      +' Join Warehouse On #CloseBook.WHCode=Warehouse.WHCode'
      +' Order By InvBillCreateTime';
    AdoQry_Temp.Open;
    AdoQry_Temp.First;
    while not AdoQry_Temp.Eof do
    begin
      //操作过程提示
      Label2.Caption:='[创建时间]'+AdoQry_Temp.fieldbyname('InvBillCreateTime').AsString
        +#10+#13+'[单 据 号]'+AdoQry_Temp.fieldbyname('InvBillNo').AsString
        +#10+#13+'[仓    库]'+AdoQry_Temp.fieldbyname('WHName').AsString
        +#10+#13+'[单据类型]'+AdoQry_Temp.fieldbyname('BillTypeName').AsString;
      Application.ProcessMessages;
      if AdoQry_Temp.fieldbyname ('PriceType').asstring='0' then
      begin
        if(AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0101')
          or(AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0102')
          or((AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0199')
          and(AdoQry_Temp.fieldbyname('OpBill').AsString<>'1')) then
        begin//普通/进口采购入库单,其他入库单处理   [处理整张单据]
          //插入还没有定义移动价的项目    ok
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Insert AveragePrice(WHCode,ItemCode)'
            +' Select Distinct '''+AdoQry_Temp.fieldbyname('WHCode').AsString+''',ItemCode'
            +' From InvInBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' And ItemCode Not In'
            +'(Select ItemCode'
            +' From AveragePrice'
            +' Where WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''')';
          AdoQry_Tmp.ExecSQL;
          //更新对应项目的数量和金额
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' ApQty=ApQty+l.Qty'
            +',ApAmount=ApAmount+l.Amount'
            +' From'
            +'(Select ItemCode,Sum(InvBillQty) As Qty,Sum(InvBillNoTaxAmount) As Amount'
            +' From InvInBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' Group By ItemCode) l'
            +' Where AveragePrice.WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' And AveragePrice.ItemCode=l.ItemCode';
          AdoQry_Tmp.ExecSQL;
          //计算对应项目的单价
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' AveragePrice=Case When ApQty=0 Then AveragePrice Else ApAmount/ApQty end'
            +' Where WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' And ItemCode In'
            +'(Select ItemCode'
            +' From InvInBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+')';
          AdoQry_Tmp.ExecSQL;
        end
        //其它出库剔除无订单委外发料
        else if (AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0299') and (AdoQry_Temp.fieldbyname('opBill').Asinteger<>1)then
        begin//其它出库单  处理整张单据
          //插入还没有定义移动价的项目
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Insert AveragePrice(WHCode,ItemCode)'
            +' Select Distinct '''+AdoQry_Temp.fieldbyname('WHCode').AsString+''',ItemCode'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' And ItemCode Not In'
            +'(Select ItemCode'
            +' From AveragePrice'
            +' Where WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''')';
          AdoQry_Tmp.ExecSQL;
          //更新对应项目的数量和金额
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' ApQty=ApQty-(l.Qty)'
            +',ApAmount=ApAmount-(l.Amount)'
            +' From'
            +'(Select ItemCode,Sum(InvBillQty) As Qty,Sum(InvBillNoTaxAmount) As Amount'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' Group By ItemCode) l'
            +' Where AveragePrice.WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' And AveragePrice.ItemCode=l.ItemCode';
          AdoQry_Tmp.ExecSQL;
          //计算对应项目的单价
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' AveragePrice=Case When ApQty=0 Then AveragePrice Else ApAmount/ApQty end'
            +' Where WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' And ItemCode In'
            +'(Select ItemCode'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+')';
          AdoQry_Tmp.ExecSQL;
          if(AdoQry_Temp.fieldbyname('OpBill').AsString='1') then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Insert OPAveragePrice(VendorCode,ItemCode)'
              +' Select Distinct '''+AdoQry_Temp.fieldbyname('VendorCode').AsString+''',ItemCode'
              +' From InvOutBillLine'
              +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
              +' And ItemCode Not In'
              +'(Select ItemCode'
              +' From OPAveragePrice'
              +' Where VendorCode='''+AdoQry_Temp.fieldbyname('VendorCode').AsString+''')';
            AdoQry_Tmp.ExecSQL;

            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='UpDate OpAveragePrice Set'
              +' OPApQty=OPApQty+l.Qty'
              +',OPApAmount=OPApAmount+l.Amount'
              +' From'
              +'(Select ItemCode,Sum(InvBillQty) As Qty,Sum(InvBillNoTaxAmount) As Amount'
              +' From InvOutBillLine'
              +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
              +' Group By ItemCode) l'
              +' Where OpAveragePrice.VendorCode='''+AdoQry_Temp.fieldbyname('VendorCode').AsString+''''
              +' And OpAveragePrice.ItemCode=l.ItemCode';
            AdoQry_Tmp.ExecSql;

            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='UpDate OpAveragePrice Set'
              +' OpAveragePrice=Case When OpApQty=0 Then 0'
              +' Else OPApAmount/OPApQty end'
              +' Where VendorCode='''+AdoQry_Temp.fieldbyname('VendorCode').AsString+''''
              +' And ItemCode In '
              +'(Select ItemCode From InvOutBillLine'
              +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+')';
            AdoQry_Tmp.ExecSql;
          end;
        end
        else if(AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0201')
          or(AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0203')
          or(AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0204')then
        begin//生产领料单,材料销售出库单,产品销售出库单   处理整张单据
          //插入还没有定义移动价的项目      ok
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Insert AveragePrice(WHCode,ItemCode)'
            +' Select Distinct '''+AdoQry_Temp.fieldbyname('WHCode').AsString+''',ItemCode'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' And ItemCode Not In'
            +'(Select ItemCode'
            +' From AveragePrice'
            +' Where WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''')';
          AdoQry_Tmp.ExecSQL;
          //更新单据的价格、金额
          //从平均价表获得价格（计算得出），更新单据的价格、金额
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update InvOutBillLine Set'
            +' InvBillNoTaxPrice=(Case When ApQty=0 Then AveragePrice'
            +' Else ApAmount/ApQty end)'
            +',InvBillNoTaxAmount=InvBillQty*(Case When ApQty=0 Then AveragePrice'
            +' Else ApAmount/ApQty end)'
            +' From InvOutBillLine'
            +' Join AveragePrice On InvOutBillLine.ItemCode=AveragePrice.ItemCode'
            +' And AveragePrice.WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' Where InvOutBillLine.InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+'';
          AdoQry_Tmp.ExecSQL;
          //更新对应项目的金额
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' ApAmount=ApAmount-(Case When ApQty=0 Then AveragePrice*l.Qty'
            +' Else (ApAmount/ApQty)*l.Qty end)'
            +' From'
            +'(Select ItemCode,Sum(InvBillQty) As Qty'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' Group By ItemCode) l'
            +' Where AveragePrice.WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' And AveragePrice.ItemCode=l.ItemCode';
          AdoQry_Tmp.ExecSQL;
          //更新对应项目的数量
          //还有realpice..
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' ApQty=ApQty-(l.Qty)'
            +' From'
            +'(Select ItemCode,Sum(InvBillQty) As Qty'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' Group By ItemCode) l'
            +' Where AveragePrice.WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''''
            +' And AveragePrice.ItemCode=l.ItemCode';
          AdoQry_Tmp.ExecSQL;
        end
        //无订单委外发料和普通委外发料一样处理
        else if ((AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0202')) or (((AdoQry_Temp.fieldbyname('BillTypeCode').AsString='0299') and (AdoQry_Temp.fieldbyname('opBill').Asinteger=1)))then
        begin//委外加工领料单
          //插入还没有定义移动价的项目
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Insert AveragePrice(WHCode,ItemCode)'
            +' Select Distinct '''+AdoQry_Temp.fieldbyname('WHCode').AsString+''',ItemCode'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' And ItemCode Not In'
            +'(Select ItemCode'
            +' From AveragePrice'
            +' Where WHCode='''+AdoQry_Temp.fieldbyname('WHCode').AsString+''')';
          AdoQry_Tmp.ExecSQL;

          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Insert OPAveragePrice(VendorCode,ItemCode)'
            +' Select Distinct '''+AdoQry_Temp.fieldbyname('VendorCode').AsString+''',ItemCode'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+''
            +' And ItemCode Not In'
            +'(Select ItemCode'
            +' From OPAveragePrice'
            +' Where VendorCode='''+AdoQry_Temp.fieldbyname('VendorCode').AsString+''')';
          AdoQry_Tmp.ExecSQL;
          //查询表体
          AdoQuery.Close;
          AdoQuery.SQL.Text:='Select ItemCode'
            +',InvBillQty'
            +',InvBillNoTaxAmount'
            +' From InvOutBillLine'
            +' Where InvBillId='+AdoQry_Temp.fieldbyname('InvBillId').AsString+'';
          AdoQuery.Open;
          AdoQuery.First;
          //一行一行循环
          while not AdoQuery.Eof do
          begin
            if (AdoQuery.fieldbyname('InvBillQty').AsFloat>0)
              or(AdoQry_Temp.fieldbyname('PriceType').AsString='1') then
            begin//按正常出库处理
              //更新对应行项目的数量和金额(供应商移动加权平均)
              if AdoQry_Temp.fieldbyname('PriceType').AsString='0' then
              begin
                AdoQry_Tmp.Close;
                AdoQry_Tmp.SQL.Text:='UpDate OpAveragePrice Set'