pm_oppobalance_b.pas

一个MRPII系统源代码版本

      AdoQry_Material.Next;
    end;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update POLine Set POLineStatus=8'
      +' Where PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+'''';
    AdoQry_Tmp.ExecSQL;
    //取出曾经进行了委外入库的仓库,...s
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select WHCode,WhPositionCode'
      +' From InvInBill'
      +' Where BillTypeCode=''0103'''
      +' And PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+'''';
    AdoQry_Tmp.Open;
    WHCode:=AdoQry_Tmp.fieldbyname('WHCode').AsString;
    WhPositionCode:=AdoQry_Tmp.fieldbyname('WhPositionCode').AsString;
    //如果有差异的话
    if (ISChanged=1)and(WHCode<>'')and(WhPositionCode<>'') then
    begin
      //产生差异单据
      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)'+
        'Values('
        +''''+Bill_No+''''
        +','''+WHCode+''''
        +','''+WhPositionCode+''''
        +','''+CurrentDate+''''
        +','''+Copy(CurrentDate,1,7)+''''
        +','''+'1202'+''''
        +','''+UserCode+''''
        +','''+UserCode+''''
        +','''+AdoQry_Head.fieldbyname('VendorCode').AsString+''''
        +','''+AdoQry_Head.fieldbyname('PoNo').AsString+''''
        +')';
      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'
        +' From PoLine'
        +' Where PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+''''
        +' And POLineNo In'
        +' (Select Distinct POLineNo'
        +' From PORealOpQty'
        +' Where PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+''')';
      AdoQuery.Open;
      AdoQuery.First;
      Bill_LineNo:=1;
      while not AdoQuery.Eof do
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Insert InvInBillLine'
          +' (InvBillId,InvBillLineNo,PoNo,POLineNo,ItemCode)'
          +' Values('
          +' '+Bill_Id+''
          +','+IntToStr(Bill_LineNo)+''
          +','''+AdoQry_Head.fieldbyname('PoNo').AsString+''''
          +','+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+''
          +','+IntToStr(Bill_LineNo)+''
          +','+'PORealOpQty.ItemCode'+''
          +','+'PORealOpQty.DiffQty*POLine.PORealInQty'+''
          +','+'PORealOpQty.OldPrice'+''
          +','+'PORealOpQty.DiffQty*POLine.PORealInQty*PORealOpQty.OldPrice'+''
          +' From PORealOpQty'
          +' Join POLine On PORealOpQty.PoNo=POLine.PoNo'
          +' And PORealOpQty.POLineNo=POLine.POLineNo'
          +' Where PORealOpQty.PONo='''+AdoQry_Head.fieldbyname('PoNo').AsString+''''
          +' And PORealOpQty.POLineNo='+AdoQuery.fieldbyname('PoLineNo').AsString+'';
        AdoQry_Tmp.ExecSQL;
        AdoQuery.Next;
        Inc(Bill_LineNo);
      end;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert OPCurrentInv(VendorCode,ItemCode)'
        +' Select Distinct '''+AdoQry_Head.fieldbyname('VendorCode').AsString+''',ItemCode'
        +' From OPBill'
        +' Where InvBillId='+Bill_Id+''
        +' And ItemCode Not In'
        +'(Select ItemCode'
        +' From OPCurrentInv'
        +' Where VendorCode='''+AdoQry_Head.fieldbyname('VendorCode').AsString+''')';
      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='''+AdoQry_Head.fieldbyname('VendorCode').AsString+''''
        +' And OPCurrentInv.ItemCode=OPBill.ItemCode';
      AdoQry_Tmp.ExecSQL;

      {AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Select OPInv From OPCurrentInv'
        +' Where VendorCode='''+AdoQry_Head.fieldbyname('VendorCode').AsString+''''
        +' And OPInv<0'
        +' And ItemCode In (Select ItemCode From OPBill'
        +' Where InvBillId='+Bill_Id
        +')';
      AdoQry_Tmp.Open;
      if not AdoQry_Tmp.IsEmpty then
      begin
        DispInfo('供应商材料库存不足,不允许进行订单结算!',3);
        Abort;
      end;  }
      //更新OPAveragePrice数据表，供应商的移动加权平均价
      if NeedChangeAveragePrice(AdoQry_Tmp,Copy(CurrentDate,1,7)) then
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Insert OPAveragePrice(VendorCode,ItemCode)'
          +' Select Distinct '''+AdoQry_Head.fieldbyname('VendorCode').AsString+''',ItemCode'
          +' From OPBill'
          +' Where InvBillId='+Bill_Id+''
          +' And ItemCode Not In'
          +'(Select ItemCode'
          +' From OPAveragePrice'
          +' Where VendorCode='''+AdoQry_Head.fieldbyname('VendorCode').AsString+''')';
        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='''+AdoQry_Head.fieldbyname('VendorCode').AsString+''''
          +' 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='''+AdoQry_Head.fieldbyname('VendorCode').AsString+''''
          +' And OPAveragePrice.ItemCode In'
          +' (Select Distinct ItemCode From OPBill'
          +' Where InvBillId='+Bill_Id
          +')';
        AdoQry_Tmp.ExecSQL;
      end;
      //计算材料费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;
      //更新本厂的移动加权平均
      if NeedChangeAveragePrice(AdoQry_Tmp,Copy(CurrentDate,1,7)) then
      begin
        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'
          +',AveragePrice=Case When ApQty=0 Then AveragePrice'
          +' Else (ApAmount+l.Amount)/ApQty end'
          +' 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;
      end;
      DispInfo('订单结算产生差异,单据号是: '+Bill_No,3);
    end
    else
      DispInfo('订单结算完毕,没有差异产生！',3);
    AdoQry_Material.Filtered:=True;
    AdoQry_Material.EnableControls;
    Act_Check.Enabled:=False;
    Act_InsertLine.Enabled:=False;
    Act_Modify.Enabled:=False;
    Act_DeleteLine.Enabled:=False;
    AdoQry_Head.Edit;
    AdoQry_Head.fieldbyname('PoTmpFlag').AsString:='1';
    AdoQry_Head.Post;
    DBConnect.CommitTrans;
  except
    DBConnect.RollBackTrans;
    AdoQry_Material.Filtered:=True;
    AdoQry_Material.EnableControls;
    DispInfo('单据结算失败,请稍后再试!',1)
  end;
end;

procedure TFrm_Pm_OpPoBalance_B.Act_InsertLineExecute(Sender: TObject);
begin
  //inherited;
  Frm_Pm_OpPoBalance_D:=TFrm_Pm_OpPoBalance_D.Create(Application);
  Frm_Pm_OpPoBalance_D.Caption:=Caption;
  Frm_Pm_OpPoBalance_D.InitForm(DBConnect,'Add',AdoQry_Material);
  Frm_Pm_OpPoBalance_D.SetFormParam(AdoQry_Head.fieldbyname('PoNo').AsString,
    AdoQry_Body.fieldbyname('PoLineNo').AsString,
    AdoQry_Body.fieldbyname('ItemCode').AsString,'','','');
  Frm_Pm_OpPoBalance_D.ShowModal;
  Frm_Pm_OpPoBalance_D.Release;
end;

procedure TFrm_Pm_OpPoBalance_B.Act_DeleteLineExecute(Sender: TObject);
begin
  //inherited;
  with AdoQry_Material do
  begin
    Edit;
    fieldbyname('RealOpQty').AsString:='0';
    fieldbyname('RealOPScrAp_Percent').AsString:='0';
    Post;
  end;
end;

procedure TFrm_Pm_OpPoBalance_B.Act_ModifyExecute(Sender: TObject);
begin
  //inherited;
  if not AdoQry_Material.Eof  then
  begin
    Frm_Pm_OpPoBalance_D:=TFrm_Pm_OpPoBalance_D.Create(Application);
    Frm_Pm_OpPoBalance_D.Caption:=Caption;
    Frm_Pm_OpPoBalance_D.SetFormParam(AdoQry_Head.fieldbyname('PoNo').AsString,
      AdoQry_Body.fieldbyname('PoLineNo').AsString,
      AdoQry_Body.fieldbyname('ItemCode').AsString,'','','');
    Frm_Pm_OpPoBalance_D.InitForm(DBConnect,'Edit',AdoQry_Material);
    Frm_Pm_OpPoBalance_D.ShowModal;
    Frm_Pm_OpPoBalance_D.Release;
  end;
end;

procedure TFrm_Pm_OpPoBalance_B.DBGridEh1GetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  DBGridEhGetCellParams(Sender,Column,AFont,Background,State);
end;

procedure TFrm_Pm_OpPoBalance_B.FormCreate(Sender: TObject);
begin
  inherited;
  TlBtn_Preview.Action:=Act_InsertLine;
  TlBtn_Print.Action:=Act_Modify;
  TlBtn_Excel.Action:=self.Act_DeleteLine;
end;

procedure TFrm_Pm_OpPoBalance_B.AdoQry_BodyAfterScroll(DataSet: TDataSet);
begin
  inherited;
  AdoQry_Material.Filtered:=False;
  AdoQry_Material.Filter:='PoLineNo='+AdoQry_Body.fieldbyname('PoLineNo').AsString+'';
  AdoQry_Material.Filtered:=True;
end;

procedure TFrm_Pm_OpPoBalance_B.SpeedButton1Click(Sender: TObject);
begin
  inherited;
  Frm_Inv_PriceAudit_PoBlnc_B:=TFrm_Inv_PriceAudit_PoBlnc_B.Create(self);

  if(not AdoQry_Head.IsEmpty)then
  begin
    Frm_Inv_PriceAudit_PoBlnc_B.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
    Frm_Inv_PriceAudit_PoBlnc_B.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
   // if ReadOnly then
  //    Frm_Inv_PriceAudit_PoBlnc_B.InitForm(DBConnect,'Query',AdoQry_Head)
   // else
      Frm_Inv_PriceAudit_PoBlnc_B.InitForm(DBConnect,'Edit',AdoQry_Head);
    Frm_Inv_PriceAudit_PoBlnc_B.ShowModal;
  end;
  Frm_Inv_PriceAudit_PoBlnc_B.release;
end;

end.