inv_oppurchinaudit_b.pas

一个MRPII系统源代码版本

unit Inv_OpPurchInAudit_B;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Entry_Body, Db, ActnList, AdODB, Grids, DBGridEh, ExtCtrls,
  ComCtrls, ToolWin, Mask, StdCtrls, ExtEdit, DBCtrls, ExtPrintReport,
  DBGrids, jpeg;

Type
  TFrm_Inv_OpPurchInAudit_B = Class(TFrm_Base_Entry_Body)
    Label1: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label10: TLabel;
    Lbl_WhCode: TLabel;
    lbl_WhPositionCode: TLabel;
    Label2: TLabel;
    Lbl_PoNo: TLabel;
    Label9: TLabel;
    Lbl_Vendor: TLabel;
    AdoQry_Material: TAdoQuery;
    DataSource1: TDataSource;
    Panel2: TPanel;
    Label3: TLabel;
    DBText1: TDBText;
    DBGridEh1: TDBGridEh;
    Lbl_BillNo: TLabel;
    Lbl_Date: TLabel;
    AdoQry_MaterialInvBillId: TIntegerField;
    AdoQry_MaterialInvBillLineNo: TStringField;
    AdoQry_MaterialIte_ItemCode: TStringField;
    AdoQry_MaterialItemCode: TStringField;
    AdoQry_MaterialItemName: TStringField;
    AdoQry_MaterialIte_ItemName: TStringField;
    AdoQry_MaterialRealOpQty: TFloatField;
    AdoQry_MaterialRealOPScrAp_Percent: TFloatField;
    AdoQry_MaterialRealOpQtyNew: TFloatField;
    AdoQry_MaterialRealOPScrAp_PercentNew: TFloatField;
    AdoQuery: TAdoQuery;
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure Act_ModifyExecute(Sender: TObject);
    procedure Act_InsertLineExecute(Sender: TObject);
    procedure Act_DeleteLineExecute(Sender: TObject);
    procedure DBGridEh1GetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure FormCreate(Sender: TObject);
    procedure Act_CheckExecute(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitControls;Override;
  end;

var
  Frm_Inv_OpPurchInAudit_B: TFrm_Inv_OpPurchInAudit_B;

implementation

uses Sys_Global,Inv_Global, Inv_OpPurchInAudit_D;

{$R *.DFM}

procedure TFrm_Inv_OpPurchInAudit_B.InitControls;
var
  Bill_id,jiezhang_Month:String;
begin//初始化窗体
  if ShowFlag=True then
    Exit;
  AdoQry_Material.Connection:=DBConnect;
  AmountFields:='InvBillNoTaxAmount,InvBillAmount,';
  PriceFields:='InvBillNoTaxPrice,InvBillPrice,';
  freeFields:='RealOpQty,RealOPScrAp_Percent,RealOpQtyNew,RealOPScrAp_PercentNew,';
  Bill_ID:=AdoQry_Head.fieldbyname('InvBillId').AsString;
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.text:=
      ' select left(convert(varchAr,Invstatus),7) as InvStatus '+
      '  from Invstatus '+
      '  where InvStatusName=''clsperiod'' ';
    open;
    jiezhang_Month:=Trim(fieldbyname('Invstatus').asstring);

    //如果结帐月份大于等于已经定额核定产生的差异单据的月份则不许再核定

    if (AdoQry_Head.fieldbyname('InvBillBomChck').asinteger=1) then
    begin
      Close;
      sql.clear;
      sql.text:=
        ' select InvBillMonth '+
        '  from InvInBill '+
        '  where ParentBillid='+
        quotedstr(AdoQry_Head.fieldbyname('InvBillid').asstring);
      open;
      if not eof then
      begin
        if jiezhang_Month>=Trim(fieldbyname('InvBillMonth').asstring) then
           Status:='ReadOnly'
        else
           Status:='Edit';
      end
      else
      begin
        if jiezhang_Month>=Trim(AdoQry_Head.fieldbyname('InvBillMonth').asstring) then
           Status:='ReadOnly'
        else
           Status:='Edit';
      end
    end
    else
         Status:='Edit';
  end;

  inherited;
  if Status='ReadOnly' then
    Act_Check.Enabled:=False
  else
    Act_Check.Enabled:=True;

  AdoQry_Body.Close;
  AdoQry_Body.SQL.Text:='Select InvInBillLine.InvBillId'
    +',InvInBillLine.InvBillLineNo'
    +',InvInBillLine.PoNo'
    +',InvInBillLine.PoLineNo'
    +',InvInBillLine.BatchNo'
    +',InvInBillLine.InvBillQty'
    +',InvInBillLine.InvBillSfcQty'
    +',InvInBillLine.ItemCode'
    +',InvInBillLine.BillLineRemArk'
    +',Item.ItemName'
    +',Uom.UomName'
    +',InvInBillLine.InvBillPriceC As InvBillPrice'
    +',InvInBillLine.InvBillAmountC As InvBillAmount'
    +',InvInBillLine.InvBillNoTaxPriceC As InvBillNoTaxPrice'
    +',InvInBillLine.InvBillNoTaxAmountC As InvBillNoTaxAmount'
    +',InvInBillLine.Valuation'
    +' From InvInBillLine'
    +' Join Item On InvInBillLine.ItemCode=Item.ItemCode '
    +' Join Uom On Item.UomCode=Uom.UomCode '
    +' Where InvInBillLine.InvBillId='+Bill_id;
  AdoQry_Body.Open;
  AdoQry_Body.Sort:='InvBillId,InvBillLineNo,ItemCode';

  AdoQry_Material.Close;
  AdoQry_Material.SQL.Text:='Select RealOpQty.InvBillId'
    +',RealOpQty.InvBillLineNo'
    +',RealOpQty.ItemCode'
    +',Item2.ItemName'
    +',RealOpQty.Ite_ItemCode'
    +',RealOpQty.Ite_ItemCode+'' ''+Item1.ItemName As Ite_ItemName'
    +',RealOpQty.RealOpQty'
    +',RealOpQty.RealOPScrAp_Percent'
    +',RealOpQty.RealOpQtyNew'
    +',RealOpQty.RealOPScrAp_PercentNew'
    +' From RealOpQty'
    +' Left Join Item Item1 On RealOpQty.Ite_ItemCode=Item1.ItemCode'
    +' Left Join Item Item2 On RealOpQty.ItemCode=Item2.ItemCode'
    +' Where RealOpQty.InvBillId='+Bill_id;
  AdoQry_Material.Open;
  AdoQry_Material.Sort:='Ite_ItemCode,ItemCode';

  with AdoQry_Head do
  begin
    Lbl_WhCode.Caption:=fieldbyname('WHCodeName').AsString;
    Lbl_WhPositionCode.Caption:=fieldbyname('WhPositionCodeName').AsString;
    Lbl_Vendor.Caption:=fieldbyname('VendorCode').AsString+' '
      +fieldbyname('VendorName').AsString;
    Lbl_BillNo.Caption:=fieldbyname('InvBillNo').AsString;
    Lbl_Date.Caption:=fieldbyname('InvBillDate').AsString;
    Lbl_PoNo.Caption:=fieldbyname('PoNo').AsString;
  end;
   freeFields:='RealOpQty,RealOPScrAp_Percent,RealOpQtyNew,RealOPScrAp_PercentNew,';
end;

procedure TFrm_Inv_OpPurchInAudit_B.DataSourceDataChange(Sender: TObject;
  Field: TField);
begin//使下面的Grid中的数据跟着上面Grid行变化
  inherited;
  AdoQry_Material.Filtered:=False;
  AdoQry_Material.Filter:='InvBillId='+AdoQry_Body.fieldbyname('InvBillId').AsString+''
    +' And InvBillLineNo='''+AdoQry_Body.fieldbyname('InvBillLineNo').AsString+'''';
  AdoQry_Material.Filtered:=True;
end;

procedure TFrm_Inv_OpPurchInAudit_B.Act_ModifyExecute(Sender: TObject);
begin//修改定额
  //inherited;
  if not AdoQry_Material.Eof  then
  begin
    Frm_Inv_OpPurchInAudit_D:=TFrm_Inv_OpPurchInAudit_D.Create(Application);
    Frm_Inv_OpPurchInAudit_D.SetFormParam(AdoQry_Body.fieldbyname('InvBillId').AsString,
      AdoQry_Body.fieldbyname('InvBillLineNo').AsString,
      AdoQry_Body.fieldbyname('ItemCode').AsString,'','','');
    Frm_Inv_OpPurchInAudit_D.InitForm(DBConnect,'Edit',AdoQry_Material);
    Frm_Inv_OpPurchInAudit_D.ShowModal;
    Frm_Inv_OpPurchInAudit_D.Release;
  end;
end;

procedure TFrm_Inv_OpPurchInAudit_B.Act_InsertLineExecute(Sender: TObject);
begin//新加定额项
  //inherited;
  Frm_Inv_OpPurchInAudit_D:=TFrm_Inv_OpPurchInAudit_D.Create(Application);
  Frm_Inv_OpPurchInAudit_D.InitForm(DBConnect,'Add',AdoQry_Material);
  Frm_Inv_OpPurchInAudit_D.SetFormParam(AdoQry_Body.fieldbyname('InvBillId').AsString,
    AdoQry_Body.fieldbyname('InvBillLineNo').AsString,
    AdoQry_Body.fieldbyname('ItemCode').AsString,'','','');
  Frm_Inv_OpPurchInAudit_D.ShowModal;
  Frm_Inv_OpPurchInAudit_D.Release;
end;

procedure TFrm_Inv_OpPurchInAudit_B.Act_DeleteLineExecute(Sender: TObject);
begin//删除定额项
  //inherited;
  with AdoQry_Material do
  begin
    Edit;
    fieldbyname('RealOpQtyNew').AsString:='0';
    fieldbyname('RealOPScrAp_PercentNew').AsString:='0';
    Post;
  end;
end;

procedure TFrm_Inv_OpPurchInAudit_B.DBGridEh1GetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin//标识出有改动的定额项
  DBGridEhGetCellParams(Sender,Column,AFont,Background,State);
  if (gdSelected in State)or(gdFocused in State) then
  begin
    Background:=clNavy;
    AFont.Color:=clWindow;
  end
  else
  begin
    if(AdoQry_Material.fieldbyname('RealOpQty').AsFloat<>
      AdoQry_Material.fieldbyname('RealOpQtyNew').AsFloat)  then
      AFont.Color:=clBlue
    else
      AFont.Color:=clBlack;
  end;
end;

procedure TFrm_Inv_OpPurchInAudit_B.FormCreate(Sender: TObject);
begin
  inherited;
  ToolButton2.Action:=Act_Check;
end;

procedure TFrm_Inv_OpPurchInAudit_B.Act_CheckExecute(Sender: TObject);
var
  ISChanged:Integer;
  WHCode,VendorCode,WhPositionCode,Bill_No,CurrentDate,Bill_Id,InvBillId,
  chck_Month,tmp_Head_Date,Tmp_Date,s_ParentBillid,sonBillid:String;
  DiffQty,OldPrice:Double;
begin
  inherited;
  DBConnect.beginTrans;
  try
    InvBillId:=AdoQry_Head.fieldbyname('InvBillId').AsString;
    WHCode:=GetCode(Lbl_WhCode.Caption);
    WhPositionCode:=GetCode(lbl_WhPositionCode.Caption);
    VendorCode:=GetCode(Lbl_Vendor.Caption);

    ///判断是否是再次核定，如果是，则将原先的差异补回去，然后删除差异单据
    //s_ParentBillid:=Trim(AdoQry_Head.fieldbyname('ParentBillid').asstring);
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='select InvBillid from InvInBill '
      +' where InvInBill.BillTypeCode=''1202'' and InvInBill.ParentBillid= '
      + InvBillId;
    AdoQry_Tmp.open;
    if not AdoQry_Tmp.eof then
    begin
      sonBillid:=AdoQry_tmp.fieldbyname('InvBillid').Value;

       //恢复供应商材料结存
      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='+sonBillid
        +' Group By ItemCode) OPBill'
        +' Where OPCurrentInv.VendorCode='''+VendorCode+''''
        +' And OPCurrentInv.ItemCode=OPBill.ItemCode';
      AdoQry_Tmp.ExecSQL;

      //恢复OPAveragePrice数据表，供应商的移动加权平均价
      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='+sonBillId
        +' 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='+sonBillId
        +')';
      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='+sonBillId
        +' Group By ItemCode) l'
        +' Where AveragePrice.WHCode='''+WHCode+''''
        +' And AveragePrice.ItemCode=l.ItemCode';
      AdoQry_Tmp.ExecSQL;