inv_saleproductaudit_b.pas

一个MRPII系统源代码版本

unit Inv_SaleProductAudit_B;

Interface

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

Type
  TFrm_Inv_SaleProductAudit_B = Class(TFrm_Base_Check_Body)
    Label1: TLabel;
    Label2: TLabel;
    Label7: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Lbl_CustomerName: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Lbl_Warehouse: TLabel;
    Lbl_CustomerCode: TLabel;
    Lbl_InvBillNo: TLabel;
    Lbl_WhPosition: TLabel;
    Lbl_InvBillRemArk: TLabel;
    MEdt_InvBillDate: TMaskEdit;
    AdoQuery: TAdoQuery;
    AdoQry_BodyInvBillLineNo: TStringField;
    AdoQry_BodyItemCode: TStringField;
    AdoQry_BodyItemName: TStringField;
    AdoQry_BodyUomName: TStringField;
    AdoQry_BodyMpsId: TBCDField;
    AdoQry_BodyInvBillQty: TFloatField;
    AdoQry_BodyInvBillSfcQty: TFloatField;
    AdoQry_BodyCurrentInvQty: TFloatField;
    AdoQry_BodyBillLineRemArk: TStringField;
    Label3: TLabel;
    Lbl_MpsSale: TLabel;
    procedure Act_CheckExecute(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_autoExecute(Sender: TObject);
    procedure AdoQry_BodyInvBillQtyChange(Sender: TField);
    procedure AdoQry_BodyBeforeInsert(DataSet: TDataSet);
    procedure DateCheck(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitControls; Override;
  end;

var
  Frm_Inv_SaleProductAudit_B: TFrm_Inv_SaleProductAudit_B;

implementation

uses Sys_Global,Inv_Global;

{$R *.DFM}

procedure TFrm_Inv_SaleProductAudit_B.InitControls;
var
  Bill_ID,InvFields:String;
begin//初始化控件值
  inherited;
  InvFields:=GetInvField(AdoQry_Tmp,AdoQry_Head.fieldbyname('WHCode').AsString
    ,AdoQry_Head.fieldbyname('WhPositionCode').AsString,'CurrentInv');
  if(AdoQry_Head.fieldbyname('InvBillWHChck').AsInteger<>1)then
  begin
    Act_Check.Enabled:=True;   
    MEdt_InvBillDate.Enabled:=True;
    Act_auto.Enabled:=True;
  end
  else
  begin
    Act_Check.Enabled:=False;
    MEdt_InvBillDate.Enabled:=False;
    Act_auto.Enabled:=False;
  end;
  Bill_ID:=AdoQry_Head.fieldbyname('InvBillId').AsString;
  AdoQry_Body.Close;
  AdoQry_Body.SQL.Text:='Select '
    +' InvOutBillLine.InvBillLineNo'
    +',InvOutBillLine.ItemCode'
    +',Item.ItemName'
    +',Uom.UomName'
    +',InvOutBillLine.MpsId'
    +',InvOutBillLine.InvBillQty'
    +',InvOutBillLine.BatchNo'
    +',InvOutBillLine.InvBillSfcQty'
    +',InvOutBillLine.BillLineRemArk'
    +',CurrentInv.'+InvFields+' As CurrentInvQty'
    +' From InvOutBillLine'
    +' Join Item On InvOutBillLine.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode'
    +' Left Join CurrentInv On InvOutBillLine.ItemCode=CurrentInv.ItemCode' 
    +' And CurrentInv.WHCode='''+AdoQry_Head.fieldbyname('WHCode').AsString+''''
    +' And CurrentInv.WhPositionCode='''+AdoQry_Head.fieldbyname('WhPositionCode').AsString+''''
    +' Where InvOutBillLine.InvBillId='+Bill_ID+'';
  AdoQry_Body.Open;
  AdoQry_Body.Sort:='InvBillLineNo';

  if(AdoQry_Head.fieldbyname('InvBillWHChck').AsInteger<>1)then
  begin//清零,制单的时候保存了临时数据
    AdoQry_Body.Last;
    while not AdoQry_Body.Bof do
    begin
      AdoQry_Body.Edit;
      if AdoQry_Body.fieldbyname('InvBillQty').AsFloat<0 then
        Act_auto.Enabled:=False;
      AdoQry_Body.fieldbyname('InvBillQty').Asstring:='0';
      AdoQry_Body.Post;
      AdoQry_Body.Prior;
    end;
  end;

  with AdoQry_Head do
  begin
    Lbl_CustomerCode.Caption:=fieldbyname('CustomerCode').AsString;
    Lbl_CustomerName.Caption:=fieldbyname('CustomerName').AsString;
    Lbl_InvBillNo.Caption:=fieldbyname('InvBillNo').AsString;
    MEdt_InvBillDate.Text:=fieldbyname('InvBillDate').AsString;
    Lbl_InvBillRemArk.Caption:=fieldbyname('InvBillRemArk').AsString;
    Lbl_Warehouse.Caption:=fieldbyname('WHCodeName').AsString;
    Lbl_WhPosition.Caption:=fieldbyname('WhPositionCodeName').AsString;
    if fieldbyname('MpsSale').AsString='0' then
      Lbl_MpsSale.Caption:='非主计划'
    else
      Lbl_MpsSale.Caption:='主计划';
  end;
end;

procedure TFrm_Inv_SaleProductAudit_B.Act_CheckExecute(Sender: TObject);
var
  DispStr,Tempstr,Bill_Count:String;
  Float_Temp1:Double;
  PriceType:Integer;
  NeedChangePrice:Boolean;
begin
  inherited;
  DateCheck(MEdt_InvBillDate);
  DBGridEh.SetFocus;
  if AdoQry_Body.State in [dsEdit] then
    AdoQry_Body.Post;
  DispStr:=' 单据核定失败,请稍后再试 ';
  DBConnect.beginTrans;
  //取计价方式
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select PriceType From Warehouse'
    +' Where WHCode='''+AdoQry_Head.fieldbyname('WHCode').AsString+'''';
  AdoQry_Tmp.Open;
  PriceType:=AdoQry_Tmp.fieldbyname('PriceType').AsInteger;

  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update InvOutBill Set '
      +' InvBillWHChck='+'1'+''
      +',WH_EmployeeCode='''+UserCode+''''
      +',InvBillDate='''+MEdt_InvBillDate.Text+''''
      +',InvBillMonth='''+Copy(MEdt_InvBillDate.Text,1,7)+''''
      +' Where InvBillId='+AdoQry_Head.fieldbyname('InvBillId').AsString+'';
    AdoQry_Tmp.ExecSQL;

    ChangeInvStatus(AdoQry_Tmp,AdoQry_Head.fieldbyname('WHCode').AsString,
      FormatDateTime('yy.mm',AdoQry_Head.fieldbyname('InvBillDate').AsDateTime));
    //看要不要更新库存
    NeedChangePrice:=NeedChangeAveragePrice(AdoQry_Tmp,FormatDateTime('yyyy.mm',
      AdoQry_Head.fieldbyname('InvBillDate').AsDateTime));

    AdoQry_Body.First;
    while(not AdoQry_Body.Eof)do
    begin
      if(PriceType=0)then
      begin
        if(True)then
        begin
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='If Not Exists'
            +' (Select ItemCode'
            +'  From AveragePrice'
            +'  Where WHCode='''+AdoQry_Head.fieldbyname('WHCode').AsString+''''
            +'  And ItemCode='''+AdoQry_Body.fieldbyname('ItemCode').AsString+''')'
            +' Insert AveragePrice(WHCode,ItemCode)'
            +' Values('
            +' '''+AdoQry_Head.fieldbyname('WHCode').AsString+''''
            +','''+AdoQry_Body.fieldbyname('ItemCode').AsString+''''
            +')';
          AdoQry_Tmp.ExecSQL;
          {单据保存，更改移动加权平均价：如果ApQty<>0，则使用原来的算法
          否则ApQty=ApQty-（出库数）ApAmount=ApAmount-出库数量*AveragePrice
          AveragePrice保持不变}
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' ApAmount=ApAmount-(Case When ApQty=0 Then AveragePrice'
            +' Else ApAmount/ApQty end)*('+AdoQry_Body.fieldbyname('InvBillQty').AsString+')'
            +' Where WHCode='''+AdoQry_Head.fieldbyname('WHCode').AsString+''''
            +' And ItemCode='''+AdoQry_Body.fieldbyname('ItemCode').AsString+'''';
          AdoQry_Tmp.ExecSQL;

          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update AveragePrice Set'
            +' ApQty=ApQty-('+AdoQry_Body.fieldbyname('InvBillQty').AsString+') '
            +' Where WHCode='''+AdoQry_Head.fieldbyname('WHCode').AsString+''''
            +' And ItemCode='''+AdoQry_Body.fieldbyname('ItemCode').AsString+'''';
          AdoQry_Tmp.ExecSQL;

          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Select Case When ApQty=0 Then AveragePrice'
            +' Else ApAmount/ApQty end As AveragePrice'
            +' From AveragePrice'
            +' Where WHCode='''+AdoQry_Head.fieldbyname('WHCode').AsString+''''
            +' And ItemCode='''+AdoQry_Body.fieldbyname('ItemCode').AsString+'''';
          AdoQry_Tmp.Open;
          Bill_Count:=AdoQry_Tmp.fieldbyname('AveragePrice').AsString;
        end
        else
        begin
          Bill_Count:='0';
        end
      end
      else
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Select SPPrice'
          +' From StandardPrice'
          +' Where SPStArtMonth='''+Copy(MEdt_InvBillDate.Text,1,7)+''''
          +' And ItemCode='''+AdoQry_Body.fieldbyname('ItemCode').AsString+'''';
        AdoQry_Tmp.Open;
        Bill_Count:=AdoQry_Tmp.fieldbyname('SPPrice').AsString;
      end;
      if Bill_Count='' then
        Bill_Count:='0';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update InvOutBillLine Set'
        +' InvBillNoTaxPrice='+Bill_Count+''
        +',InvBillQty='+AdoQry_Body.fieldbyname('InvBillQty').AsString+''
        +',BillLineRemArk='''+AdoQry_Body.fieldbyname('BillLineRemArk').AsString+''''
        +' Where InvBillLineNo='''+AdoQry_Body.fieldbyname('InvBillLineNo').AsString+''''
        +' And InvBillId='''+AdoQry_Head.fieldbyname('InvBillId').AsString+'''';
      AdoQry_Tmp.ExecSQL;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update InvOutBillLine Set'
        +' InvBillNoTaxAmount=Round(InvBillNoTaxPrice*InvBillQty,2)'
        +' Where InvBillLineNo='''+AdoQry_Body.fieldbyname('InvBillLineNo').AsString+''''
        +' And InvBillId='''+AdoQry_Head.fieldbyname('InvBillId').AsString+'''';
      AdoQry_Tmp.ExecSQL;
      //重点修改
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update RealSalePrice Set'
        +' InvBillSaleAmount=InvBillSalePrice*'+AdoQry_Body.fieldbyname('InvBillQty').AsString+''
        +',InvBillNoTaxSaleAmount=InvBillNoTaxSalePrice*'+AdoQry_Body.fieldbyname('InvBillQty').AsString+''
        +' Where InvBillLineNo='''+AdoQry_Body.fieldbyname('InvBillLineNo').AsString+''''
        +' And InvBillId='''+AdoQry_Head.fieldbyname('InvBillId').AsString+'''';
      AdoQry_Tmp.ExecSQL;

      Tempstr:=UpdateInv(AdoQry_Tmp,AdoQry_Head.fieldbyname('WHCode').AsString
        ,AdoQry_Head.fieldbyname('WhPositionCode').AsString
        ,AdoQry_Body.fieldbyname('ItemCode').AsString
        ,'(-('+AdoQry_Body.fieldbyname('InvBillQty').AsString+'))'
        ,NeedChangePrice,True);