inv_oldinvinput.pas

一个MRPII系统源代码版本

//
unit Inv_OldInvInput;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, OleCtnrs, Db, AdODB, ExtCtrls,OleServer,comobj, Mask, variants;

Type
  TFrm_Inv_OldInvInput = Class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    edt_fileName: TEdit;
    Btn_Brower: TButton;
    Btn_ok: TButton;
    Btn_Cancel: TButton;
    Opendlg_File: TOpendialog;
    AdoQry_Tmp: TAdoQuery;
    OleContainer1: TOleContainer;
    AdoQry_tmp1: TAdoQuery;
    Label3: TLabel;
    procedure Btn_BrowerClick(Sender: TObject);
    procedure Btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Btn_CancelClick(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormActivate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);

  private
      aa:string;
      DBConnect:TAdOConnection;
    { Private declarations }
  public
      procedure  SetDBConnect(AdOConnection:TAdOConnection);
    { Public declarations }
  end;

var
  Frm_Inv_OldInvInput: TFrm_Inv_OldInvInput;

implementation

uses  Sys_Global, Inv_ShowInfo;

{$R *.DFM}

procedure TFrm_Inv_OldInvInput.Btn_BrowerClick(Sender: TObject);
begin
  If  Opendlg_File.Execute Then
  begin
    Edt_FileName.Text:=Opendlg_File.FileName;
  end;
end;

procedure TFrm_Inv_OldInvInput.Btn_okClick(Sender: TObject);
var
  Excel,Sheet:variant;
  row:integer;
  SqlText,stext:string;
begin
  if edt_fileName.Text ='' then
  begin
    DispInfo('文件名不能为空',3);
    edt_fileName.SetFocus ;
    exit;
  end;
  If Not FileExists(Trim(Edt_FileName.Text)) Then
  begin
    DispInfo('文件"'+Trim(Edt_FileName.Text)+'"不存在,请重新输入!',1);
    Edt_FileName.SelectAll;
    Edt_FileName.SetFocus;
    Abort;
  end;
  Frm_Inv_ShowInfo.Lbl_Show.Caption:='正在引入数据,请稍候... ';
  Frm_Inv_ShowInfo.Visible:=True;
  Screen.Cursor:=crHourGlass;
  Application.ProcessMessages;
  Try
    Excel:=UnAssigned;
    Excel:=CreateOleObject('Excel.Application');
    Excel.visible:=False;
    Excel.WorkBooks.Open(Edt_FileName.text);
  Except
    Excel:=UnAssigned;
    Frm_Inv_ShowInfo.Visible :=False;
    DispInfo('本机未安装Excel,本功能必须在安装有Excel的电脑上才能运行!',3);
    Screen.Cursor:=crArrow;
    Abort;
  end;
  Frm_Inv_ShowInfo.Lbl_Show.Caption:='正在引入数据....';
  Application.ProcessMessages ;
  If varIsempty(Excel)=False then
  begin
    Sheet:=Excel.WorkSheets[1];
  end;
  IF (string(Sheet.cells[1,1])<>'仓库代码')
     or (string(Sheet.cells[1,2])<>'旧物料代码')
     or (string(Sheet.cells[1,3])<>'库存数量')  then

  begin
     Frm_Inv_ShowInfo.Visible :=False;
     Excel.Quit;
     DispInfo('所选的文件的版式与数据引入格式模板不一样,请修改后再引入!',3);
     Screen.Cursor:=crArrow;
     Abort;
  end;
  with AdoQry_tmp1  do
  begin
    sql.clear ;
    stext:='delete from oldSysInv ';
    sql.Add(stext);
    ExecSQL;
  end;
  Frm_Inv_ShowInfo.Visible:=True;
  Frm_Inv_ShowInfo.Lbl_Show.Caption :='正在引入数据...';
  Row:=2;
  While ((string(Sheet.cells[row,2]))<>'') do
  begin
      SqlText:='insert oldSysInv (whCode,ItemCode2,InvQty) Values '+
               '('''+string(Sheet.cells[row,1])+''','''+string(Sheet.cells[row,2])+''','+
               string(Sheet.cells[row,3])+')';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=SqlText;
      try
      AdoQry_Tmp.ExecSQL;
      except
        DispInfo('错误的数据格式,请修改后再引入!',3);
        Screen.Cursor:=crArrow;
        Abort;
      end;
      row:=row+1;
  end;
  Frm_Inv_ShowInfo.Visible :=False;
  with AdoQry_tmp1 do
  begin
    sql.clear;
    sql.Add('update oldSysInv set ItemCode=a.ItemCode from Item a where a.ItemCode2=oldSysInv.ItemCode2');
    sql.Add(' update oldSysInv set ItemCode=i.ItemCode from multoldItemCode m,Item i'+
            ' where oldSysInv.ItemCode2=m.f_Bm2 and m.f_Bm1=i.ItemCode2');
    execsql;

  end;
  DispInfo('数据引入成功!',3);
  Screen.Cursor:=crArrow;   
  Excel.Quit;
  Excel:=UnAssigned;
end;



procedure TFrm_Inv_OldInvInput.SetDBConnect(AdOConnection: TAdOConnection);
begin
  DBConnect:=AdOConnection;
  AdoQry_Tmp.Connection:=AdOConnection;
  AdoQry_tmp1.Connection :=AdOConnection;
end;
procedure TFrm_Inv_OldInvInput.FormCreate(Sender: TObject);
begin
  Frm_Inv_ShowInfo:=TFrm_Inv_ShowInfo.Create(self);
//  SetDBConnect(AdOConnection1);
end;

procedure TFrm_Inv_OldInvInput.Btn_CancelClick(Sender: TObject);
begin
  Close;
end;

procedure TFrm_Inv_OldInvInput.FormKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin

  case key of
    vk_eScApe:
      if Btn_Cancel.visible then
        Btn_CancelClick(Sender);
    vk_return:
    begin
      if ((activecontrol is tcombobox) and ((sSalt in shift) or (activecontrol as tcombobox).droppeddown)) then
        exit;
      selectnext(activecontrol as twincontrol,True,True);
      key:=0;
    end;
    vk_Down,vk_up:
    begin
      if (activecontrol is tmemo) then exit;
      if ((activecontrol is tcombobox) and ((sSalt in shift) or (activecontrol as tcombobox).droppeddown)) then
        exit;
      if (activecontrol is tListbox) then
        if ((key=vk_up) and ((activecontrol as tListbox).Itemindex>0)) or ((key=vk_Down) and ((activecontrol as tListbox).Items.Count-1<>(activecontrol as tListbox).Itemindex)) then
          exit;
      selectnext(activecontrol as twincontrol,key=vk_Down,True);
      key:=0;
    end;
  end;
end;

procedure TFrm_Inv_OldInvInput.FormActivate(Sender: TObject);
begin
 edt_fileName.SetFocus ;
end;

procedure TFrm_Inv_OldInvInput.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  action:=cafree;
end;

end.