inv_oppurchinaudit_b.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 656 行 · 第 1/2 页

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
        +' AveragePrice=Case When ApQty=0 Then AveragePrice'
        +' Else ApAmount/ApQty end'
        +' From (Select Distinct ItemCode'
        +' From InvInBillLine'
        +' Where InvBillId='+sonBillId
        +') l'
        +' Where AveragePrice.WHCode='''+WHCode+''''
        +' And AveragePrice.ItemCode=l.ItemCode';
      AdoQry_Tmp.ExecSQL;

      //删除上次核定产生的单据
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=' delete opBill where InvBillid='+sonBillid
         +' delete from InvInBillline where InvBillid='+sonBillid
         +' delete from InvInBill where InvBillid='+sonBillid;
      AdoQry_Tmp.ExecSQL;

    end;

    //设置修改标志
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update InvInBill Set InvBillBomChck=1'
      +' Where InvBillId='+InvBillId+'';
    AdoQry_Tmp.ExecSQL;

    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Delete RealOpQty'
      +' Where InvBillId='+InvBillId;
    AdoQry_Tmp.ExecSQL;

    ISChanged:=0;
    AdoQry_Material.DisableControls;
    AdoQry_Material.Filtered:=False;
    AdoQry_Material.First;
    while not AdoQry_Material.Eof do
    begin
      DiffQty{差异比率}:=AdoQry_Material.fieldbyname('RealOpQtyNew').AsFloat*
        (1+AdoQry_Material.fieldbyname('RealOPScrAp_PercentNew').AsFloat/100)-
        AdoQry_Material.fieldbyname('RealOpQty').AsFloat*
        (1+AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsFloat/100);
      if DiffQty<>0 then
        ISChanged:=1;
      //取未未税价
      if DiffQty<0 then
      begin
        //
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Select Case When OPBill.OPBillQty=0 Then 0'
          +' Else OPBill.OPBillNoTaxAmount/OPBill.OPBillQty end As OldPrice'
          +' From OPBill'
          +' Where InvBillLineNo='+AdoQry_Material.fieldbyname('InvBillLineNo').AsString+''
          +' And InvBillId='+AdoQry_Material.fieldbyname('InvBillId').AsString+''
          +' And ItemCode='''+AdoQry_Material.fieldbyname('ItemCode').AsString+'''';
        AdoQry_Tmp.Open;
        OldPrice:=AdoQry_Tmp.fieldbyname('OldPrice').AsFloat;
      end
      else
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Select Case When OPApQty=0 Then 0'
          +' Else OPApAmount/OPApQty end As OldPrice'
          +' From OPAveragePrice'
          +' Where ItemCode='''+AdoQry_Material.fieldbyname('ItemCode').AsString+''''
          +' And VendorCode='''+VendorCode+'''';
        AdoQry_Tmp.Open;
        OldPrice:=AdoQry_Tmp.fieldbyname('OldPrice').AsFloat;
      end;
      //保存差异数据
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert RealOpQty(InvBillId,InvBillLineNo'
        +',Ite_ItemCode,ItemCode,RealOpQty,RealOpQtyNew,RealOPScrAp_Percent'
        +',DiffQty,RealOPScrAp_PercentNew,OldPrice)'
        +' Values('
        +' '+AdoQry_Material.fieldbyname('InvBillId').AsString+''
        +','''+AdoQry_Material.fieldbyname('InvBillLineNo').AsString+''''
        +','''+AdoQry_Material.fieldbyname('Ite_ItemCode').AsString+''''
        +','''+AdoQry_Material.fieldbyname('ItemCode').AsString+''''
        +','+AdoQry_Material.fieldbyname('RealOpQty').AsString+''
        +','+AdoQry_Material.fieldbyname('RealOpQtyNew').AsString+''
        +','+AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString+''
        +','+FloatToStr(DiffQty)+''
        +','+AdoQry_Material.fieldbyname('RealOPScrAp_PercentNew').AsString
        +','+FloatToStr(OldPrice)
        +')';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Material.Next;
    end;
    AdoQry_Material.EnableControls;
    AdoQry_Material.Filtered:=True;

    //如果有差异的话
    if ISChanged=1 then
    begin
      //产生差异单据
      with AdoQry_tmp do
      begin
        Close;
        sql.clear;
        sql.text:=
         ' select left(convert(varchAr,Invstatus),7) as InvStatus '+
         '  from Invstatus '+
         '  where InvStatusName=''clsperiod'' ';
        open;
        if strtoint(copy(LoginDate,9,2))>20 then
           tmp_Date:=datetostr(incMonth(strtodatetime(Trim(fieldbyname('Invstatus').asstring)+copy(datetostr(strtodate(LoginDate)-5),8,3)),1))
        else
           tmp_Date:=datetostr(incMonth(strtodatetime(Trim(fieldbyname('Invstatus').asstring)+copy(LoginDate,8,3)),1));
//        tmp_Date:=datetostr(incMonth(strtodatetime(Trim(fieldbyname('Invstatus').asstring)+copy(LoginDate,8,3)),1));
      end;
      //单据头日期
      tmp_Head_Date:=Lbl_Date.Caption;
      if copy(tmp_Head_Date,1,7)<=copy(tmp_Date,1,7) then
         chck_Month:=copy(tmp_Date,1,7)
      else
         chck_Month:=copy(tmp_Head_Date,1,7);
      with AdoQry_tmp do
      begin
        Close;
        sql.clear;
        sql.Add('select convert(varchAr(10),getdate(),102) as Currentdate ');
        open;//取服务器日期
        CurrentDate:=fieldbyname('Currentdate').asstring;
      end;
      if chck_Month<copy(CurrentDate,1,7) then
         CurrentDate:=chck_Month+copy(datetostr(incMonth(strtodate(chck_Month+'.01'),1)-1),8,3)
      else
         CurrentDate:=chck_Month+copy(Currentdate,8,3);
      {AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Select GetDate() As CurrentDate';
      AdoQry_Tmp.Open;
      CurrentDate:=FormatDateTime('yyyy.mm.dd',AdoQry_Tmp.
        fieldbyname('CurrentDate').AsDateTime);
      if CurrentDate<Lbl_Date.Caption then
        CurrentDate:=Lbl_Date.Caption;}
      Bill_No:=GetNo(DBConnect,WHCode+'R'+
        FormatDateTime('yymm',StrToDateTime(CurrentDate)),'InvBill');
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert InvInBill'+
        '(InvBillNo,WHCode,WhPositionCode,InvBillDate,InvBillMonth,BillTypeCode'
        +',Stk_EmployeeCode,EmployeeCode,VendorCode,PoNo,ParentBillID)'+
        'Values('
        +''''+Bill_No+''''
        +','''+WHCode+''''
        +','''+WhPositionCode+''''
        +','''+CurrentDate+''''
        +','''+Copy(CurrentDate,1,7)+''''
        +','''+'1202'+''''
        +','''+UserCode+''''
        +','''+UserCode+''''
        +','''+VendorCode+''''
        +','''+Lbl_PoNo.Caption+''''
        +','+InvBillId+''
        +')';
      AdoQry_Tmp.ExecSQL;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Select @@identity as Bill_Id';
      AdoQry_Tmp.Open;
      Bill_Id:=AdoQry_Tmp.fieldbyname('Bill_Id').AsString;

      //产生差异单据行
      AdoQuery.Close;
      AdoQuery.Connection:=DBConnect;
      AdoQuery.SQL.Text:='Select POLineNo,ItemCode,InvBillLineNo'
        +' From InvInBillLine'
        +' Where InvBillId='+InvBillId+''
        +' And InvBillLineNo In'
        +' (Select Distinct InvBillLineNo'
        +' From RealOpQty'
        +' Where InvBillId='+InvBillId+''
        +' And DiffQty<>0)';
      AdoQuery.Open;
      AdoQuery.First;
      while not AdoQuery.Eof do
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Insert InvInBillLine'
          +' (InvBillId,InvBillLineNo,PoNo,POLineNo,ItemCode)'
          +' Values('
          +' '+Bill_Id+''
          +','+AdoQuery.fieldbyname('InvBillLineNo').AsString+''
          +','''+Lbl_PoNo.Caption+''''
          +','+AdoQuery.fieldbyname('POLineNo').AsString+''
          +','''+AdoQuery.fieldbyname('ItemCode').AsString+''''
          +')';
        AdoQry_Tmp.ExecSQL;

        AdoQry_Tmp.Close;//材料费
        AdoQry_Tmp.SQL.Text:='Insert OPBill'
          +'(InvBillId,InvBillLineNo,ItemCode,OPBillQty,OPBillNoTaxPrice,OPBillNoTaxAmount)'
          +' Select'
          +' '+Bill_Id+''
          +','+AdoQuery.fieldbyname('InvBillLineNo').AsString+''
          +',RealOpQty.ItemCode'+''
          +',RealOpQty.DiffQty*InvInBillLine.InvBillQty'+''
          +',RealOpQty.OldPrice'
          +',RealOpQty.DiffQty*InvInBillLine.InvBillQty*RealOpQty.OldPrice'
          +' From RealOpQty'
          +' Join InvInBillLine On RealOpQty.InvBillId=InvInBillLine.InvBillId'
          +' And RealOpQty.InvBillLineNo=InvInBillLine.InvBillLineNo'
          +' Where RealOpQty.InvBillId='+InvBillId+''
          +' And RealOpQty.InvBillLineNo='+AdoQuery.fieldbyname('InvBillLineNo').AsString+'';
        AdoQry_Tmp.ExecSQL;
        AdoQuery.Next;
      end;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert OPCurrentInv(VendorCode,ItemCode)'
        +' Select Distinct '''+VendorCode+''',ItemCode'
        +' From OPBill'
        +' Where InvBillId='+Bill_Id+''
        +' And ItemCode Not In'
        +'(Select ItemCode'
        +' From OPCurrentInv'
        +' Where VendorCode='''+VendorCode+''')';
      AdoQry_Tmp.ExecSQL;
      //更新供应商库存
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update OPCurrentInv'
        +' Set OPInv=OPInv-(OPBill.OPBillQty)'
        +' From (Select ItemCode,Sum(OPBillQty) As OPBillQty From OPBill'
        +' Where InvBillId='+Bill_Id
        +' Group By ItemCode) OPBill'
        +' Where OPCurrentInv.VendorCode='''+VendorCode+''''
        +' And OPCurrentInv.ItemCode=OPBill.ItemCode';
      AdoQry_Tmp.ExecSQL;
      //更新OPAveragePrice数据表，供应商的移动加权平均价
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert OPAveragePrice(VendorCode,ItemCode)'
        +' Select Distinct '''+VendorCode+''',ItemCode'
        +' From OPBill'
        +' Where InvBillId='+Bill_Id+''
        +' And ItemCode Not In'
        +'(Select ItemCode'
        +' From OPAveragePrice'
        +' Where VendorCode='''+VendorCode+''')';
      AdoQry_Tmp.ExecSQL;
      //更新供应商移动价
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update OPAveragePrice Set'
        +' OPApQty=OPApQty-(OPB.Qty)'
        +',OPApAmount=OPApAmount-(OPB.Amount)'
        +' From (Select ItemCode,Sum(OPBillQty) As Qty,Sum(OPBillNoTaxAmount) As Amount'
        +' From OPBill'
        +' Where InvBillId='+Bill_Id
        +' Group By ItemCode) OPB'
        +' Where OPAveragePrice.VendorCode='''+VendorCode+''''
        +' And OPAveragePrice.ItemCode=OPB.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 OPAveragePrice.VendorCode='''+VendorCode+''''
        +' And OPAveragePrice.ItemCode In'
        +' (Select Distinct ItemCode From OPBill'
        +' Where InvBillId='+Bill_Id
        +')';
      AdoQry_Tmp.ExecSQL;
      //计算材料费InvBillline
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update InvInBillLine Set'
        +' InvBillNoTaxAmount=OPB.Amount'
        +' From (Select InvBillId,InvBillLineNo,Sum(OPBillNoTaxAmount) As Amount'
        +' From OPBill'
        +' Where InvBillId='+Bill_Id
        +' Group By InvBillId,InvBillLineNo) OPB'
        +' Where InvInBillLine.InvBillId='+Bill_Id
        +' And InvInBillLine.InvBillLineNo=opb.InvBillLineNo';
      AdoQry_Tmp.ExecSQL;
      //更新本厂的移动加权平均
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert AveragePrice(WHCode,ItemCode)'
        +' Select Distinct '''+WHCode+''',ItemCode'
        +' From InvInBillLine'
        +' Where InvBillId='+Bill_Id+''
        +' And ItemCode Not In'
        +'(Select ItemCode'
        +' From AveragePrice'
        +' Where WHCode='''+WHCode+''')';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
        +' ApAmount=ApAmount+l.Amount'
        +' From (Select ItemCode,Sum(InvBillNoTaxAmount) As Amount'
        +' From InvInBillLine'
        +' Where InvBillId='+Bill_Id
        +' Group By ItemCode) l'
        +' Where AveragePrice.WHCode='''+WHCode+''''
        +' 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'
        +' From (Select Distinct ItemCode'
        +' From InvInBillLine'
        +' Where InvBillId='+Bill_Id
        +') l'
        +' Where AveragePrice.WHCode='''+WHCode+''''
        +' And AveragePrice.ItemCode=l.ItemCode';
      AdoQry_Tmp.ExecSQL;
      DispInfo('定额核定产生差异,单据号是: '+Bill_No,3);
    end
    else
      DispInfo('定额核定完毕,没有差异产生！',3);

    AdoQry_Head.Edit;
    AdoQry_Head.fieldbyname('InvBillBomChck').AsString:='1';
    AdoQry_Head.Post;
    Act_Check.Enabled:=False;
    Act_DeleteLine.Enabled:=False;
    Act_Modify.Enabled:=False;
    Act_InsertLine.Enabled:=False;
    DBConnect.CommitTrans;
  except
    on e:Exception do
    begin
      DBConnect.RollBackTrans;
      RecOrderror(Self,AdoQry_tmp,E.message);
      DispInfo('定额核定保存失败，请稍后再试',3);
    end;
  end;
end;

end.