inv_olddataimport.pas

一个MRPII系统源代码版本

//
unit Inv_OldDataImport;

Interface

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

Type
  TFrm_Inv_OldDataImport = 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;
    Label2: TLabel;
    medt_Date1: TMaskEdit;
    Label4: TLabel;
    medt_Date2: TMaskEdit;
    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 medt_Date1Exit(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormActivate(Sender: TObject);

  private
      aa:string;
      DBConnect:TAdOConnection;
      function checkBill(str:string):string;
      function checkBill2(str:string):boolean;

    { Private declarations }
  public
      procedure  SetDBConnect(AdOConnection:TAdOConnection);
    { Public declarations }
  end;

var
  Frm_Inv_OldDataImport: TFrm_Inv_OldDataImport;

implementation

uses  Sys_Global, Inv_ShowInfo;

{$R *.DFM}

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

procedure TFrm_Inv_OldDataImport.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 (length(Trim(medt_Date1.Text))=4) or (length(Trim(medt_Date2.Text))=4) then
  begin
    DispInfo('引入日期均不能为空',3);
    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;
  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);
    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])<>'单据类型')
     or (string(Sheet.cells[1,4])<>'数量')  then

  begin
     Frm_Inv_ShowInfo.Visible :=False;
     Excel.Quit;
     DispInfo('所选的文件的版式与数据引入格式模板不一样,请修改后再引入!',3);
     Abort;
  end;
  with AdoQry_tmp1  do
  begin
    sql.clear ;
    stext:='delete from Involddata where odate>='''+medt_Date1.text+''' and odate<='''+
             medt_Date2.Text+'''';
    sql.Add(stext);
    ExecSQL;
  end;
  Frm_Inv_ShowInfo.Visible:=True;
  Frm_Inv_ShowInfo.Lbl_Show.Caption :='正在检测数据是正确性...';
  Row:=2;
  While ((string(Sheet.cells[row,1]))<>'') do
  begin
    //判别客户客户是否有效
  if (string(Sheet.cells[row,1])>=medt_Date1.Text) and (string(Sheet.cells[row,1])<=medt_Date2.Text)
                                 and  checkBill2(string(Sheet.cells[row,3]))  then //当序号不为空时,即属于合同头部分
    begin
      aa:=checkBill(string(Sheet.cells[row,3]));
      SqlText:='insert Involddata (odate,ItemCode2,BillTypeCode,Billqty) Values '+
               '('''+string(Sheet.cells[row,1])+''','''+string(Sheet.cells[row,2])+''','''+
               aa+''','+string(Sheet.cells[row,4])+')';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=SqlText;
      try
      AdoQry_Tmp.ExecSQL;
      except
        DispInfo('错误的数据格式,请修改后再引入!',3);
        Abort;
      end;
      row:=row+1;
    end
    else
      row:=row+1;
  end;
  Frm_Inv_ShowInfo.Visible :=False;
  with AdoQry_tmp1 do
  begin
    sql.clear;
    sql.Add('update Involddata set ItemCode=a.ItemCode from Item a where a.ItemCode2=Involddata.ItemCode2');
    execsql;
  end;
  DispInfo('数据引入成功!',3);
  Excel.Quit;
  Excel:=UnAssigned;
end;



procedure TFrm_Inv_OldDataImport.SetDBConnect(AdOConnection: TAdOConnection);
begin
  DBConnect:=AdOConnection;
  AdoQry_Tmp.Connection:=AdOConnection;
  AdoQry_tmp1.Connection :=AdOConnection;
end;
procedure TFrm_Inv_OldDataImport.FormCreate(Sender: TObject);
begin
  Frm_Inv_ShowInfo:=TFrm_Inv_ShowInfo.Create(self);
  medt_Date2.text:=datetostr(now);
  medt_Date1.text:=Copy(medt_Date2.text,1,7)+'.01';
//  SetDBConnect(AdOConnection1);
end;

procedure TFrm_Inv_OldDataImport.Btn_CancelClick(Sender: TObject);
begin
  Close;
end;
procedure TFrm_Inv_OldDataImport.medt_Date1Exit(Sender: TObject);
begin
  if (sender is tmaskedit) and (activecontrol.TabOrder>(sender as tmaskedit).tabOrder) and (activecontrol.Name<>'Btn_Cancel') then
    begin
      try
        strtodatetime((sender as tmaskedit).text);
       except
        Application.MessageBox('输入日期有误，请重新输入','信息提示',MB_OK);
        (sender as tmaskedit).setfocus;
       end;
    end;
end;
function TFrm_Inv_OldDataImport.checkBill2(str:string):boolean;
begin
  if (str='采购入库')  then Result:=True else
  if (str='委外入库')  then Result:=True else
  if (str='生产领料')  then Result:=True else
  if (str='委外领料')  then Result:=True else
  Result:=False;
end;
function TFrm_Inv_OldDataImport.checkBill(str:string):string;
begin
  if (str='采购入库') then checkBill:='0101' ;
  if (str='委外入库') then checkBill:='0103' ;
  if (str='生产领料') then checkBill:='0201' ;
  if (str='委外领料') then checkBill:='0202' ;

end;
procedure TFrm_Inv_OldDataImport.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_OldDataImport.FormActivate(Sender: TObject);
begin
 edt_fileName.SetFocus ;
end;

end.