pm_oppobalance_b.pas

一个MRPII系统源代码版本

unit Pm_OpPoBalance_B;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Check_Body, Db, ActnList, AdODB, Grids, DBGridEh, StdCtrls,
  ExtCtrls, ComCtrls, ToolWin, Buttons, jpeg, variants;

Type
  TFrm_Pm_OpPoBalance_B = Class(TFrm_Base_Check_Body)
    DBGridEh1: TDBGridEh;
    Panel1: TPanel;
    Label1: TLabel;
    Lbl_PoNo: TLabel;
    Label3: TLabel;
    Lbl_VendorCodeName: TLabel;
    Label5: TLabel;
    AdoQry_Material: TAdoQuery;
    DataSource1: TDataSource;
    AdoQry_BodyPoLineNo: TIntegerField;
    AdoQry_BodyItemCode: TStringField;
    AdoQry_BodyItemName: TStringField;
    AdoQry_BodyUomName: TStringField;
    AdoQry_BodyPoLineDate: TDateTimeField;
    AdoQry_BodyPOQty: TFloatField;
    AdoQry_BodyPoNoFinishQty: TFloatField;
    AdoQuery: TAdoQuery;
    SpeedButton1: TSpeedButton;
    procedure Act_CheckExecute(Sender: TObject);
    procedure Act_InsertLineExecute(Sender: TObject);
    procedure Act_DeleteLineExecute(Sender: TObject);
    procedure Act_ModifyExecute(Sender: TObject);
    procedure DBGridEh1GetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure FormCreate(Sender: TObject);
    procedure AdoQry_BodyAfterScroll(DataSet: TDataSet);
    procedure SpeedButton1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitControls;Override;
  end;

var
  Frm_Pm_OpPoBalance_B: TFrm_Pm_OpPoBalance_B;

implementation

uses Sys_Global, Pm_OpPoBalance_D, Inv_PriceAudit_PoBlnc_B;

{$R *.DFM}

procedure TFrm_Pm_OpPoBalance_B.InitControls;
begin
  Pnl_Title.Caption:=Caption;
  inherited;
  if AdoQry_Head.fieldbyname('PoTmpFlag').AsString<>'1' then
  begin
    Act_Check.Enabled:=True;
    Act_InsertLine.Enabled:=True;
    Act_Modify.Enabled:=True;
    Act_DeleteLine.Enabled:=True;
  end;
  AdoQry_Material.Close;
  AdoQry_Material.Connection:=DBConnect;
  AdoQry_Material.Close;
  AdoQry_Material.SQL.Text:='Select OpItemList.ItemCode'
    +',Item.ItemName'
    +',Uom.UomName'
    +',OpItemList.Ite_ItemCode'
    +',OpItemList.POCtrlQty'
    +',OpItemList.PORealQty'
    +',OpItemList.POOutQty'
    +',OpItemList.PoNo'
    +',OpItemList.PoLineNo'
    +',OPItemList.BomQty'
    +',OPItemList.BomScrAp_Percent'
    +',PORealOpQty.RealOpQty'
    +',PORealOpQty.RealOPScrAp_Percent'
    +' From OpItemList'
    +' Left Join PORealOpQty On OpItemList.PoNo=PORealOpQty.PoNo'
    +' And OpItemList.POLineNo=PORealOpQty.POLineNo'
    +' Join Item On OpItemList.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode'
    +' Where OpItemList.PoNo=''''';
  AdoQry_Material.Open;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select OpItemList.ItemCode'
    +',Item.ItemName'
    +',Uom.UomName'
    +',OpItemList.Ite_ItemCode'
    +',OpItemList.POCtrlQty'
    +',OpItemList.PORealQty'
    +',OpItemList.POOutQty'
    +',OpItemList.PoNo'
    +',OpItemList.PoLineNo'
    +',OPItemList.BomQty'
    +',OPItemList.BomScrAp_Percent'
    +' From OpItemList'
    +' Join Item On OpItemList.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode'
    +' Where OpItemList.PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+'''';
  AdoQry_Tmp.Open;
  AdoQry_Tmp.First;
  while not AdoQry_Tmp.Eof do
  begin
    AdoQry_Material.Append;
    AdoQry_Material.fieldbyname('ItemCode').AsString:=AdoQry_Tmp.fieldbyname('ItemCode').AsString;
    AdoQry_Material.fieldbyname('ItemName').AsString:=AdoQry_Tmp.fieldbyname('ItemName').AsString;
    AdoQry_Material.fieldbyname('UomName').AsString:=AdoQry_Tmp.fieldbyname('UomName').AsString;
    AdoQry_Material.fieldbyname('Ite_ItemCode').AsString:=AdoQry_Tmp.fieldbyname('Ite_ItemCode').AsString;
    AdoQry_Material.fieldbyname('POCtrlQty').AsString:=AdoQry_Tmp.fieldbyname('POCtrlQty').AsString;
    AdoQry_Material.fieldbyname('PORealQty').AsString:=AdoQry_Tmp.fieldbyname('PORealQty').AsString;
    AdoQry_Material.fieldbyname('POOutQty').AsString:=AdoQry_Tmp.fieldbyname('POOutQty').AsString;
    AdoQry_Material.fieldbyname('PoNo').AsString:=AdoQry_Tmp.fieldbyname('PoNo').AsString;
    AdoQry_Material.fieldbyname('PoLineNo').AsString:=AdoQry_Tmp.fieldbyname('PoLineNo').AsString;
    AdoQry_Material.fieldbyname('BomQty').AsString:=AdoQry_Tmp.fieldbyname('BomQty').AsString;
    AdoQry_Material.fieldbyname('BomScrAp_Percent').AsString:=AdoQry_Tmp.fieldbyname('BomScrAp_Percent').AsString;
    AdoQry_Material.fieldbyname('RealOpQty').AsString:=AdoQry_Tmp.fieldbyname('BomQty').AsString;
    AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString:=AdoQry_Tmp.fieldbyname('BomScrAp_Percent').AsString;
    AdoQry_Material.Post;
    AdoQry_Tmp.Next;
  end;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select PORealOpQty.ItemCode'
    +',Item.ItemName'
    +',Uom.UomName'
    +',PORealOpQty.Itm_ItemCode'
    +',PORealOpQty.PoNo'
    +',PORealOpQty.PoLineNo'
    +',PORealOpQty.RealOpQty'
    +',PORealOpQty.RealOPScrAp_Percent'
    +' From PORealOpQty'
    +' Join Item On PORealOpQty.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode'
    +' Where PORealOpQty.PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+'''';
  AdoQry_Tmp.Open;
  AdoQry_Tmp.First;
  while not AdoQry_Tmp.Eof do
  begin
    if AdoQry_Material.Locate('PoNo;PoLineNo;ItemCode',
      varArrayOf([AdoQry_Tmp.fieldbyname('PoNo').AsString,
      AdoQry_Tmp.fieldbyname('PoLineNo').AsString,
      AdoQry_Tmp.fieldbyname('ItemCode').AsString]),[]) then
    begin
      AdoQry_Material.Edit;
      AdoQry_Material.fieldbyname('RealOpQty').AsString:=AdoQry_Tmp.fieldbyname('RealOpQty').AsString;
      AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString:=AdoQry_Tmp.fieldbyname('RealOPScrAp_Percent').AsString;
      AdoQry_Material.Post;
    end
    else
    begin
      AdoQry_Material.Append;
      AdoQry_Material.fieldbyname('ItemCode').AsString:=AdoQry_Tmp.fieldbyname('ItemCode').AsString;
      AdoQry_Material.fieldbyname('ItemName').AsString:=AdoQry_Tmp.fieldbyname('ItemName').AsString;
      AdoQry_Material.fieldbyname('UomName').AsString:=AdoQry_Tmp.fieldbyname('UomName').AsString;
      AdoQry_Material.fieldbyname('Ite_ItemCode').AsString:=AdoQry_Tmp.fieldbyname('Itm_ItemCode').AsString;
      AdoQry_Material.fieldbyname('POCtrlQty').AsString:='0';
      AdoQry_Material.fieldbyname('PORealQty').AsString:='0';
      AdoQry_Material.fieldbyname('POOutQty').AsString:='0';
      AdoQry_Material.fieldbyname('PoNo').AsString:=AdoQry_Tmp.fieldbyname('PoNo').AsString;
      AdoQry_Material.fieldbyname('PoLineNo').AsString:=AdoQry_Tmp.fieldbyname('PoLineNo').AsString;
      AdoQry_Material.fieldbyname('BomQty').AsString:='0';
      AdoQry_Material.fieldbyname('BomScrAp_Percent').AsString:='0';
      AdoQry_Material.fieldbyname('RealOpQty').AsString:=AdoQry_Tmp.fieldbyname('RealOpQty').AsString;
      AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString:=AdoQry_Tmp.fieldbyname('RealOPScrAp_Percent').AsString;
      AdoQry_Material.Post;
    end;
    AdoQry_Tmp.Next;
  end;

  AdoQry_Material.Filtered:=False;
  AdoQry_Material.Sort:='ItemCode';
  
  Lbl_VendorCodeName.Caption:=AdoQry_Head.fieldbyname('VendorCode').AsString
    +' '+AdoQry_Head.fieldbyname('VendorName').AsString;
  Lbl_PoNo.Caption:=AdoQry_Head.fieldbyname('PoNo').AsString;

  AdoQry_Body.Close;
  AdoQry_Body.SQL.clear;
  AdoQry_Body.SQL.Text:='Select PoLine.PoLineNo'
    +',PoLine.ItemCode'
    +',Item.ItemName'
    +',Uom.UomName'
    +',PoLine.PoLineDate'
    +',PoLine.POQty'
    +',PoLine.PoNoFinishQty'
    +' From PoLine'
    +' Join Item On PoLine.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode'
    +' Where PoLine.PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+'''';
  AdoQry_Body.Open;
  AdoQry_Body.Sort:='PoLineNo';
end;

procedure TFrm_Pm_OpPoBalance_B.Act_CheckExecute(Sender: TObject);
var
  ISChanged,Bill_LineNo:Integer;
  WHCode,WhPositionCode,Bill_No,CurrentDate,Bill_Id,Tempstr,MaxMonth:String;
  DiffQty,OldPrice:Double;
begin
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select Max(InvBillMonth) As BillMonth'
    +' From InvInBill'
    +' Where BillTypeCode=''0103'''
    +' And PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+'''';
  AdoQry_Tmp.Open;
  MaxMonth:=AdoQry_Tmp.fieldbyname('BillMonth').AsString;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvStatus From InvStatus'
    +' Where InvStatusName=''clsperiod''';
  AdoQry_Tmp.Open;
  Tempstr:=FormatFloat('0000.00',AdoQry_Tmp.fieldbyname('InvStatus').AsFloat);
  Tempstr:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(Tempstr+'.01'),1));
  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 MaxMonth>Tempstr then
  begin
    if DispInfo('存在'+CurrentDate+'月份的入库单据,而'
      +Tempstr+'月份还没有结帐'
      +#13+#10+'是否进行订单结算?',2)='n' then
    begin
      Abort;
    end;
  end
  else
  begin
    if copy(CurrentDate,1,7)>Tempstr then
    begin
      CurrentDate:=FormatDateTime('yyyy.mm.dd',(IncMonth(StrToDateTime(Tempstr+'.01'),1)-1));
    end;
  end;

  DBConnect.beginTrans;
  try
    ISChanged:=0;

    AdoQry_Material.DisableControls;
    AdoQry_Material.Filtered:=False;
    AdoQry_Material.First;
    while not AdoQry_Material.Eof do
    begin
      //有差异的插入PORealOpQty中;
      if (AdoQry_Material.fieldbyname('BomQty').AsString<>
        AdoQry_Material.fieldbyname('RealOpQty').AsString)
        or (AdoQry_Material.fieldbyname('BomScrAp_Percent').AsString<>
        AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString) then
      begin
        ISChanged:=1;
        DiffQty:=AdoQry_Material.fieldbyname('RealOpQty').AsFloat*
          (1+AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsFloat/100)-
          AdoQry_Material.fieldbyname('BomQty').AsFloat*
          (1+AdoQry_Material.fieldbyname('BomScrAp_Percent').AsFloat/100);
        if DiffQty<0 then
        begin
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Select Case When sum(OPBill.OPBillQty)=0 Then 0'
          +' Else sum(OPBill.OPBillNoTaxAmount)/sum(OPBill.OPBillQty) end As OldPrice'
          +' From OPBill'
          +' Join (Select InvBillId,InvBillLineNo'
          +' From InvInBillLine'
          +' Where PoLineNo='+AdoQry_Material.fieldbyname('PoLineNo').AsString+''
          +' And InvBillId In(Select InvBillId From InvInBill'
          +' Where BillTypeCode=''0103'''
          +' And PoNo='''+AdoQry_Material.fieldbyname('PoNo').AsString+''''
          +')) bl On OPBill.InvBillId=bl.InvBillId'
          +' And OPBill.InvBillLineNo=bl.InvBillLineNo'
          +' Where OPBill.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='''+AdoQry_Head.fieldbyname('VendorCode').AsString+'''';
          AdoQry_Tmp.Open;
          OldPrice:=AdoQry_Tmp.fieldbyname('OldPrice').AsFloat;
        end;
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Insert PORealOpQty (PoNo,PoLineNo,Itm_ItemCode'
          +',ItemCode,RealOpQty,RealOPScrAp_Percent,DiffQty,OldPrice)'
          +' Values('
          +' '''+AdoQry_Material.fieldbyname('PoNo').AsString+''''
          +','+AdoQry_Material.fieldbyname('PoLineNo').AsString+''
          +','''+AdoQry_Material.fieldbyname('Ite_ItemCode').AsString+''''
          +','''+AdoQry_Material.fieldbyname('ItemCode').AsString+''''
          +','+AdoQry_Material.fieldbyname('RealOpQty').AsString+''
          +','+AdoQry_Material.fieldbyname('RealOPScrAp_Percent').AsString+''
          +','+FloatToStr(DiffQty)+''
          +','+FloatToStr(OldPrice)+''
          +')';
        AdoQry_Tmp.ExecSQL;
      end;