frmmakeinventory.pas

物流供应链管理系统

unit frmMakeInventory;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Mask, DBCtrls, ExtCtrls, LCDBOperatTool, Grids,
  DBGrids, DB, ADODB, DBClient, Provider, DataSetToExcel;

type
  TFormMakeInventory = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    DBEdtId: TDBEdit;
    DBEdtOperatorId: TDBEdit;
    DBEdtAuditor: TDBEdit;
    BtnBegin: TButton;
    GroupBox1: TGroupBox;
    LCDBOperatTool1: TLCDBOperatTool;
    Button2: TButton;
    Button3: TButton;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    DBEdtBarCode: TDBEdit;
    DBEdtAccountAmount: TDBEdit;
    DBEdtRealAmount: TDBEdit;
    DBEdtGoodsPosition: TDBEdit;
    DBGrid1: TDBGrid;
    BtnEnd: TButton;
    ADOCon: TADOConnection;
    ADOQInventMaster: TADOQuery;
    ADOQInventSlave: TADOQuery;
    ADOQInfo: TADOQuery;
    dspInventMaster: TDataSetProvider;
    dspInventSlave: TDataSetProvider;
    cdsInventMaster: TClientDataSet;
    cdsInventSlave: TClientDataSet;
    DSInventMaster: TDataSource;
    DSInventSlave: TDataSource;
    Button1: TButton;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    SaveDlg: TSaveDialog;
    OpenDlg: TOpenDialog;
    ADOQGoodsMaster: TADOQuery;
    ADOQGoodsSlave: TADOQuery;
    cdsInventMasterId: TWideStringField;
    cdsInventMasterWareHouseId: TWideStringField;
    cdsInventMasterInventoryDate: TDateTimeField;
    cdsInventMasterOperatorId: TWideStringField;
    cdsInventMasterAuditor: TWideStringField;
    cdsInventSlaveMasterId: TWideStringField;
    cdsInventSlaveBarCode: TWideStringField;
    cdsInventSlaveGoodsPosition: TWideStringField;
    cdsInventSlaveAccountAmount: TLargeintField;
    cdsInventSlaveRealAmount: TLargeintField;
    Button7: TButton;
    Button8: TButton;
    Button9: TButton;
    Button10: TButton;
    cdsMoreLess: TClientDataSet;
    cdsMoreLessBarCode: TWideStringField;
    cdsMoreLessGoodsPostion: TWideStringField;
    cdsMoreLessAccountAmount: TLargeintField;
    cdsMoreLessRealAmount: TLargeintField;
    Button11: TButton;
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure Button2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure BtnBeginClick(Sender: TObject);
    procedure BtnEndClick(Sender: TObject);
    procedure LCDBOperatTool1ModeChange(AMode: TBtnMode);
    procedure Button6Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button10Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure Button11Click(Sender: TObject);
  private
    { Private declarations }
    FMasterId: string;
    function FGenerateInventId: string; //生成盘点编号
    function FIsBarCodeExist(ABarCode: string): Boolean;//判断条形码是否存在
    procedure RegfreshStock;//更新库存
    procedure GetMoreDataSet;//取得报溢表数据集
    procedure GetLessDataSet;//取得报损表数据集
    procedure SetAccountAmount;//根据条形码和货位设定帐面数量
  public
    { Public declarations }
  end;

var
  FormMakeInventory: TFormMakeInventory;

implementation
uses CommonFunc, untConfig, StrUtils, qrMakeInventory, qrInventMoreLess;
{$R *.dfm}

procedure TFormMakeInventory.FormKeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then
  Perform(WM_NEXTDLGCTL,0,0);
end;

procedure TFormMakeInventory.Button2Click(Sender: TObject);
begin
  cdsInventMaster.FieldByName('OperatorId').AsString := ShowFieldSelectDlg(ADOCon.ConnectionString,'t_Employee');
end;

procedure TFormMakeInventory.FormCreate(Sender: TObject);
begin
  ADOCon.Close;
  ADOCon.ConnectionString := GetConnectionString;
  try
    ADOCon.Open;
    cdsInventMaster.Open;
    cdsInventSlave.Open;
  except
    raise;
  end;
end;

procedure TFormMakeInventory.FormDestroy(Sender: TObject);
begin
  ADOCon.Close;
end;

procedure TFormMakeInventory.Button3Click(Sender: TObject);
begin
  cdsInventMaster.FieldByName('Auditor').AsString := ShowFieldSelectDlg(ADOCon.ConnectionString,'t_Employee');
end;

function TFormMakeInventory.FGenerateInventId: string;
var
  LOld: string;
  i: Integer;
  LDepId: string;
  LNew: string;
begin
  LDepId := RightStr(GetWareHouseId, 2);
  ADOQInfo.Close;
  ADOQInfo.SQL.Clear;
  ADOQInfo.SQL.Add('select top 1 id from t_MakeInventoryMaster ');
  ADOQInfo.SQL.Add('where substring(id,3,2)=:WareHouseId ');
  ADOQInfo.Parameters.ParamByName('WareHouseId').Value :=
    LDepId;//不用加单引号
  ADOQInfo.SQL.Add('order by Id desc');
  ADOQInfo.Open;
  if ADOQInfo.RecordCount > 0 then
    LOld := ADOQInfo.FieldByName('id').AsString
  else
    LOld := 'PD' + LDepId + '000000';
  i := StrToIntDef(RightStr(LOld,6),0);
  Inc(i);
  LNew := FormatFloat('000000',i);
  result := 'PD' + LDepId + LNew;
end;

function TFormMakeInventory.FIsBarCodeExist(ABarCode: string):Boolean;
begin
  ADOQInfo.Close;
  ADOQInfo.SQL.Clear;
  ADOQInfo.SQL.Add('select BarCode from t_GoodsMaster ');
  ADOQInfo.SQL.Add('where BarCode=:BarCode');
  ADOQInfo.Parameters.ParamByName('BarCode').Value := ABarCode;
  ADOQInfo.Open;
  if ADOQInfo.RecordCount > 0 then
    result := true
  else
    result := false;
end;

procedure TFormMakeInventory.RegfreshStock;
var
  i,n: Integer;
  LBarCode: string;
  LChangeNum: Integer;
begin
  n := cdsInventSlave.RecordCount - 1;
  //更新货位上摆放的商品名称与帐面相符，但数量不符的记录－－开始
  ADOQGoodsSlave.Close;
  ADOQGoodsSlave.SQL.Clear;
  ADOQGoodsSlave.SQL.Add('UPDATE t_GoodsSlave SET Amount=:Amount ');
  ADOQGoodsSlave.SQL.Add(' WHERE GoodsPosition=:GP and BarCode=:BarCode');
  ADOQGoodsMaster.Close;
  ADOQGoodsMaster.SQL.Clear;
  ADOQGoodsMaster.SQL.Add('UPDATE t_GoodsMaster SET Amount=Amount+:ChangAmount ');
  ADOQGoodsMaster.SQL.Add('WHERE BarCode=:BarCode');
  //更新货位上摆放的商品名称与帐面相符，但数量不符的记录－－结束
 {
  //更新货位上摆放的商品名称不符的记录－－开始
  ADOQGoodsSlave2.Close;
  ADOQGoodsSlave2.SQL.Clear;
  ADOQGoodsSlave2.SQL.Add('UPDATE t_GoodsSlave SET Amount=:Amount ');
  ADOQGoodsSlave2.SQL.Add(' WHERE GoodsPosition=:GP and BarCode<>:BarCode');
    //更新帐面上这个货位上的货物的数量 （减少）
  ADOQGoodsMaster2.Close;
  ADOQGoodsMaster2.SQL.Clear;
  ADOQGoodsMaster2.SQL.Add('UPDATE t_GoodsMaster SET Amount=Amount-:ChangAmount ');
  ADOQGoodsMaster2.SQL.Add('WHERE BarCode=:BarCode');
    // 更新实际上这个货位上的货物的数量 （增加）
  ADOQGoodsMaster3.Close;
  ADOQGoodsMaster3.SQL.Add('UPDATE t_GoodsMaster SET Amount=Amount+:ChangAmount ');
  ADOQGoodsMaster3.SQL.Add('WHERE BarCode=:BarCode');
  //更新货位上摆放的商品名称不符的记录－－结束
  }
  try
    ADOCon.BeginTrans;
    for i := 0 to n do
    begin
      if cdsInventSlave.FieldByName('AccountAmount').AsInteger
        <> cdsInventSlave.FieldByName('RealAmount').AsInteger then
      begin
        LBarCode := cdsInventSlave.FieldByName('BarCode').AsString;
        LChangeNum := cdsInventSlave.FieldByName('RealAmount').AsInteger -
                      cdsInventSlave.FieldByName('AccountAmount').AsInteger;

          ADOQGoodsSlave.Parameters.ParamByName('Amount').Value :=
            cdsInventSlave.FieldByName('RealAmount').AsInteger;
          ADOQGoodsSlave.Parameters.ParamByName('GP').Value :=
            cdsInventSlave.FieldByName('GoodsPosition').AsString;
          ADOQGoodsSlave.Parameters.ParamByName('BarCode').Value :=
             LBarCode;
          ADOQGoodsMaster.Parameters.ParamByName('ChangAmount').Value :=
             LChangeNum;
          ADOQGoodsMaster.Parameters.ParamByName('BarCode').Value :=
            LBarCode;
          ADOQGoodsSlave.ExecSQL;
          ADOQGoodsMaster.ExecSQL;
      end;
    end;
    ADOCon.CommitTrans;
  except
    ADOCon.RollbackTrans;
    LCShowMessage('库存更新失败！');
  end;
end;

procedure TFormMakeInventory.GetMoreDataSet;
var
  i,n: Integer;
begin
  cdsMoreLess.Data := cdsInventSlave.Data;
  n := cdsInventSlave.RecordCount - 1;
  cdsMoreLess.Open;
  cdsMoreLess.EmptyDataSet;
  
  for i := 0 to n do
  begin
    if cdsInventSlave.FieldByName('AccountAmount').AsInteger
       <cdsInventSlave.FieldByName('RealAmount').AsInteger then
    begin
      cdsMoreLess.Insert;
      cdsMoreLess.FieldByName('BarCode').AsString :=
        cdsInventSlave.FieldByName('BarCode').AsString;
      cdsMoreLess.FieldByName('AccountAmount').AsInteger :=
        cdsInventSlave.FieldByName('AccountAmount').AsInteger;
      cdsMoreLess.FieldByName('RealAmount').AsInteger :=
        cdsInventSlave.FieldByName('RealAmount').AsInteger;
      cdsMoreLess.FieldByName('GoodsPosition').AsString :=
        cdsInventSlave.FieldByName('GoodsPosition').AsString;
    end;
  end;
end;

procedure TFormMakeInventory.GetLessDataSet;
var
  i,n: Integer;
begin
  cdsMoreLess.Data := cdsInventSlave.Data;
  n := cdsInventSlave.RecordCount - 1;
  cdsMoreLess.Open;
  cdsMoreLess.EmptyDataSet;
  for i := 0 to n do
  begin
    if cdsInventSlave.FieldByName('AccountAmount').AsInteger
       >cdsInventSlave.FieldByName('RealAmount').AsInteger then
    begin
      cdsMoreLess.Insert;
      cdsMoreLess.FieldByName('BarCode').AsString :=
        cdsInventSlave.FieldByName('BarCode').AsString;
      cdsMoreLess.FieldByName('AccountAmount').AsInteger :=
        cdsInventSlave.FieldByName('AccountAmount').AsInteger;
      cdsMoreLess.FieldByName('RealAmount').AsInteger :=
        cdsInventSlave.FieldByName('RealAmount').AsInteger;
      cdsMoreLess.FieldByName('GoodsPostion').AsString :=
        cdsInventSlave.FieldByName('GoodsPostion').AsString;
    end;
  end;
end;

procedure TFormMakeInventory.SetAccountAmount;
begin
  if (DBEdtBarCode.Text<>'') and (DBEdtGoodsPosition.Text<>'') and
      (DBEdtAccountAmount.Text = '') then
  begin
    ADOQInfo.Close;
    ADOQInfo.SQL.Clear;
    ADOQInfo.SQL.Add('select Amount from t_GoodsSlave ');
    ADOQInfo.SQL.Add('where BarCode=:BarCode and GoodsPosition=:GP');
    ADOQInfo.Parameters.ParamByName('BarCode').Value :=
      Trim(DBEdtBarCode.Text);
    ADOQInfo.Parameters.ParamByName('GP').Value :=
      Trim(DBEdtGoodsPosition.Text);
    ADOQInfo.Open;
    if ADOQInfo.RecordCount > 0 then
      cdsInventSlave.FieldByName('AccountAmount').AsInteger :=
        ADOQInfo.FieldByName('Amount').AsInteger
    else
      cdsInventSlave.FieldByName('AccountAmount').AsInteger := 0;
  end;
end;


procedure TFormMakeInventory.BtnBeginClick(Sender: TObject);
begin
  FMasterId := FGenerateInventId;
  try
    cdsInventMaster.Open;
    cdsInventMaster.Insert;
    cdsInventMaster.FieldByName('Id').AsString := FMasterId;
    cdsInventMaster.FieldByName('InventoryDate').AsDateTime := Now();
    cdsInventMaster.FieldByName('WareHouseId').AsString := GetWareHouseId;
  except
    raise;
  end;
  LCDBOperatTool1.Enabled := true;
  DBEdtBarCode.Enabled := true;
  //DBEdtAccountAmount.Enabled := true;
  DBEdtRealAmount.Enabled := true;
  DBEdtGoodsPosition.Enabled := true;
  DBGrid1.Enabled := true;
  BtnEnd.Enabled := true;
  BtnBegin.Enabled := false;
end;

procedure TFormMakeInventory.BtnEndClick(Sender: TObject);
var
  LBarCode: string;
begin
  if (cdsInventMaster.State=dsInsert) or (cdsInventMaster.State=dsEdit) then
    cdsInventMaster.Post;

  if (cdsInventSlave.State=dsInsert) or (cdsInventSlave.State=dsEdit) then
    cdsInventSlave.Post;
  while not cdsInventSlave.Eof do
  begin
    LBarCode := cdsInventSlave.FieldByName('BarCode').AsString;
    if not FIsBarCodeExist(LBarCode) then
    begin
      LCShowMessage(Format('条形码%s不存在！',[LBarCode]));
      Exit;
    end;
    cdsInventSlave.Next;
  end;
  
  try
    ADOCon.BeginTrans;
      if cdsInventMaster.ApplyUpdates(-1)>0 then
        raise Exception.Create('cdsInventMaster更新错误！');
      if cdsInventSlave.ApplyUpdates(-1)>0 then
        raise Exception.Create('cdsInventSlave更新错误！');
    ADOCon.CommitTrans;
  except
    ADOCon.RollbackTrans;
    LCShowMessage('保存盘点单时发生错误！您可以重试或保存结果稍后再试！');
    Exit;
  end;

  LCDBOperatTool1.Enabled := false;
  DBEdtBarCode.Enabled := false;
  //DBEdtAccountAmount.Enabled := false;
  DBEdtRealAmount.Enabled := false;
  DBEdtGoodsPosition.Enabled := false;
  DBGrid1.Enabled := false;

  if LCConfirmDlg('是否更新库存情况？') = mrYES then
  begin
    RegfreshStock;//更新库存
  end;
  BtnBegin.Enabled := true;
  BtnEnd.Enabled := false;
end;

procedure TFormMakeInventory.LCDBOperatTool1ModeChange(AMode: TBtnMode);
begin
  if AMode = bmAdd then
    cdsInventSlave.FieldByName('MasterId').AsString := FMasterId;
end;

procedure TFormMakeInventory.Button6Click(Sender: TObject);
begin
  ExportDataToExcel(cdsInventSlave);
end;

procedure TFormMakeInventory.Button1Click(Sender: TObject);
begin
  if cdsInventSlave.RecordCount >0 then
  begin
    if SaveDlg.Execute then
      cdsInventSlave.SaveToFile(AddExtToFileName(SaveDlg.FileName,'.xml'),dfXML);
  end
    else
      LCShowMessage('没有数据！');
end;

procedure TFormMakeInventory.Button4Click(Sender: TObject);
begin
  if SaveDlg.Execute then
    cdsInventSlave.LoadFromFile(OpenDlg.FileName);
end;

procedure TFormMakeInventory.Button5Click(Sender: TObject);
var
  LQR: TMakeInventoryQR;
begin
  LQR := TMakeInventoryQR.Create(nil);
  LQR.PreviewModal;
  LQR.Free;
end;

procedure TFormMakeInventory.Button7Click(Sender: TObject);
begin
  GetMoreDataSet;
  ExportDataToExcel(cdsMoreLess);
end;

procedure TFormMakeInventory.Button8Click(Sender: TObject);
var
  LQR: TInventMoreLessQR;
begin
  GetMoreDataSet;
  LQR := TInventMoreLessQR.Create(nil);
  LQR.SetReportTitle('盘点报溢表');
  LQR.PreviewModal;
  LQR.Free;
end;

procedure TFormMakeInventory.Button10Click(Sender: TObject);
var
  LQR: TInventMoreLessQR;
begin
  GetLessDataSet;
  LQR := TInventMoreLessQR.Create(nil);
  LQR.SetReportTitle('盘点报损表');
  LQR.PreviewModal;
  LQR.Free;
end;


procedure TFormMakeInventory.Button9Click(Sender: TObject);
begin
  GetLessDataSet;
  ExportDataToExcel(cdsMoreLess);
end;

procedure TFormMakeInventory.Button11Click(Sender: TObject);
begin
  SetAccountAmount;
end;

end.