mrp_enter_importmps.pas

一个MRPII系统源代码版本

unit Mrp_Enter_ImportMps;

Interface

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

Type
  TFrm_Mrp_Enter_ImportMps = Class(TFrm_Base_Condition)
    Edt_FileName: TEdit;
    Label1: TLabel;
    Btn_Brower: TButton;
    Opendlg_File: TOpendialog;
    Bevel1: TBevel;
    Label2: TLabel;
    Panel1: TPanel;
    OleContainer1: TOleContainer;
    Label3: TLabel;
    procedure Btn_BrowerClick(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure Edt_FileNameChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btn_CancelClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Mrp_Enter_ImportMps: TFrm_Mrp_Enter_ImportMps;

implementation

uses Sys_Global, Mrp_Enter_NormalMps, Public_Show;

{$R *.DFM}

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

procedure TFrm_Mrp_Enter_ImportMps.btn_okClick(Sender: TObject);
var
  Excel,Sheet:variant;
  Row,I:Integer;
  SqlText:String;
  MpsId:Integer;
begin
  inherited;
  If Not FileExists(Trim(Edt_FileName.Text)) Then
  begin
    DispInfo('文件"'+Trim(Edt_FileName.Text)+'"不存在，请重新输入!',1);
    Edt_FileName.SelectAll;
    Edt_FileName.SetFocus;
    Abort;
  end;
  Frm_Public_Show.Lbl_Show.Caption:='正在引入数据，请稍等.....';
  Frm_Public_Show.Visible:=True;  //_______________________________________________
  Application.ProcessMessages;
  Try
    Excel:=UnAssigned;
    Excel:=CreateOleObject('Excel.Application');
    Excel.Visible:=False;
    Excel.WorkBooks.Open(Edt_FileName.Text);
  Except
    Excel:=UnAssigned;
    Frm_Public_Show.Visible:=False;
    DispInfo('本机未安装EXCEL,本功能必须在安装有EXCEL的电脑上才能运行!',3);
    Abort;
  end;
  Frm_Public_Show.Lbl_Show.Caption:='正在引入数据，已完成20%....';
  Application.ProcessMessages;
  If varIsEmpty(Excel)=False Then
  begin
    Sheet:=Excel.WorkSheets[1];
  end;
  If (String(Sheet.Cells[2,1])<>'物料代码') Or (String(Sheet.Cells[2,2])<>'物料描述')
     Or (String(Sheet.Cells[2,3])<>'行状态') Or (String(Sheet.Cells[2,4])<>'生产订货量')
     Or (String(Sheet.Cells[2,5])<>'需用日期') Or (String(Sheet.Cells[2,6])<>'备注') Then
  begin
    Frm_Public_Show.Visible:=False;
    Excel.Quit;
    Excel:=UnAssigned;
    DispInfo('所选文件的版式与样版不一样，请修改后再引入!',3);
    Abort;
  end;
  Row:=3;
  While String(Sheet.Cells[Row,1])<>'' Do
  begin
    If String(Sheet.Cells[3,1])='' Then
    begin
      Frm_Public_Show.Visible:=False;
      Excel.Quit;
      Excel:=UnAssigned;
      DispInfo('主生产计划编制表中没有数据或数据错误，不能引入!',3);
      Abort;
    end;
    SqlText:='Select * From Item Where ItemCode='''+Trim(String(Sheet.Cells[Row,1]))+''' And PmCode In (0,2,3) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    If AdoQry_Tmp.Eof Then
    begin
      Frm_Public_Show.Visible:=False;
      Excel.Quit;
      Excel:=UnAssigned;
      DispInfo('第'+IntToStr(Row)+'行中物料代码不正确或类型不是制造型，请修改后再引入!',3);
      Abort;
    end;
    if Trim(string(Sheet.cells[row,3]))='' then
       Sheet.cells[row,3]:='2 准备';
    If Not (StrtoInt(Copy(String(Sheet.Cells[Row,3]),1,1)) In [2,3]) Then
    begin
      Frm_Public_Show.Visible:=False;
      Excel.Quit;
      Excel:=UnAssigned;
      DispInfo('第'+IntToStr(Row)+'行中行状态不正确或为关闭状态，请修改后再引入!',3);
      Abort;
    end;
    If StrtoInt(Sheet.Cells[Row,4])<0  Then
    begin
      Frm_Public_Show.Visible:=False;
      Excel.Quit;
      Excel:=UnAssigned;
      DispInfo('第'+IntToStr(Row)+'行中需用数据量必须大于等于零，请修改后再引入!',3);
      Abort;
    end;
    Try
      StrToDateTime(Trim(String(Sheet.Cells[Row,5])));
    Except
      Frm_Public_Show.Visible:=False;
      Excel.Quit;
      Excel:=UnAssigned;
      DispInfo('第'+IntToStr(Row)+'行中需用日期数据错误，请修改后再引入!',3);
      Abort;
    end;
    Row:=Row+1;
  end;
  I:=Row-1;
  Frm_Public_Show.Lbl_Show.Caption:='正在引入数据，已完成40%....';
  Application.ProcessMessages;
  Row:=3;
  dbConnect.beginTrans;
  Try
    While String(Sheet.Cells[Row,1])<>'' Do
    begin
      Sqltext:=' Select * '
              +' From Mps '
              +' Where ItemCode='''+Trim(String(Sheet.Cells[Row,1]))+''''
              +' And MpsDate='''+Trim(Sheet.Cells[Row,5])+'''';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=SqlText;
      AdoQry_Tmp.Open;
      If (Not AdoQry_Tmp.Eof) And (AdoQry_Tmp.fieldbyname('MpsStatus').AsInteger<>2)  Then
      begin
        If DbConnect.InTransaction Then
          DbConnect.RollBackTrans;
        DispInfo('第'+IntToStr(Row)+'行数据已经存在，且不处于准备状态，请修改后再引入!',3);
        Abort;
      end;
      If (Not AdoQry_Tmp.Eof) And (AdoQry_Tmp.fieldbyname('MpsStatus').AsInteger=2)  Then
         SqlText:='Update Mps '
                +' Set MpsQty='''+String(Sheet.Cells[Row,4])+''','
                +' MpsRemArk='''+String(Sheet.Cells[Row,6])+''','
                +' MpsStatus='''+Copy(String(Sheet.Cells[Row,3]),1,1)+''''
                +' Where ItemCode='''+Trim(Sheet.Cells[Row,1])+''''
                +' And MpsDate='''+Trim(Sheet.Cells[Row,5])+''''
      Else
        SqlText:=' Insert Mps '
                +' (MpsMonth,ItemCode,MpsStatus,MpsDATE,MpsQTY,MpsRemArk )'
                +' Values('
                +''''+Copy(FormatDateTime('yyyy.mm.dd',StrToDateTime(Sheet.Cells[Row,5])),1,7)+''''+','
                +''''+String(Sheet.Cells[Row,1])+''','
                +''''+Copy(String(Sheet.Cells[Row,3]),1,1)+''','
                +''''+String(Sheet.Cells[Row,5])+''','
                +''''+String(Sheet.Cells[Row,4])+''','
                +''''+String(Sheet.Cells[Row,6])+''')';
     AdoQry_Tmp.Close;
     AdoQry_Tmp.SQL.Text:=SqlText;
     AdoQry_Tmp.ExecSQL;
     SqlText:=' SELECT @@Identity AS xx From Mps ';
     AdoQry_Tmp.Close;
     AdoQry_Tmp.SQL.Text:=SqlText;
     AdoQry_Tmp.Open;
     MpsId:=AdoQry_Tmp.fieldbyname('xx').AsInteger;
     SqlText:='Insert MpsHistory '
            + ' (MpsMonth,ItemCode,Mpsdate,Mpsqty,Mpsstatus,'
            + ' MpsremArk,MpSchgEmployeeCode,MpSchgTime,MpsId,MpSchgType)'
            + ' Values('
            +''''+Copy(FormatDateTime('yyyy.mm.dd',StrToDateTime(Sheet.Cells[Row,5])),1,7)+''''+','
            +''''+String(Sheet.Cells[Row,1])+''','
            +''''+String(Sheet.Cells[Row,5])+''','
            +''''+String(Sheet.Cells[Row,4])+''','
            +''''+Copy(String(Sheet.Cells[Row,3]),1,1)+''','
            +''''+String(Sheet.Cells[Row,6])+''','
            +''''+UserCode+''''+','
            +'Getdate(),'
            +''''+IntToStr(MpsId)+''','''+'计划引入'+''')';
     AdoQry_Tmp.Close;
     AdoQry_Tmp.SQL.Text:=SqlText;
     AdoQry_Tmp.ExecSQL;  //保存历史记录
     Frm_Public_Show.Lbl_Show.Caption:='正在引入数据，已完成......'+'80'+'%!';
     Application.ProcessMessages;
     Row:=Row+1;
    end;
     Frm_Public_Show.Lbl_Show.Caption:='正在引入数据，已完成......'+'100'+'%!';

    DbConnect.CommitTrans;
    Btn_Ok.Enabled:=False;
    Frm_Public_Show.Visible:=False;//_______________________________________
    DispInfo('主生产计划数据引入成功!',3);
  Except
    If DbConnect.InTransaction Then
      Dbconnect.RollBackTrans;
    Frm_Public_Show.Visible:=False;
    Btn_Ok.Enabled:=False;
    Excel.Quit;
    Excel:=UnAssigned;
    DispInfo('保存数据时出错，请重试!',1);
    Abort;
  end;
  Excel.Quit;
  Excel:=UnAssigned;
  Frm_Mrp_Enter_ImportMps.Close;
  Frm_Mrp_Enter_NormalMps:=TFrm_Mrp_Enter_NormalMps.Create(Application);
  Frm_Mrp_Enter_NormalMps.InitForm(DbConnect,False);
  Frm_Mrp_Enter_NormalMps.Show;
end;

procedure TFrm_Mrp_Enter_ImportMps.FormActivate(Sender: TObject);
begin
  inherited;
  Caption:='主生产计划引入';
  Edt_FileName.SetFocus;
end;

procedure TFrm_Mrp_Enter_ImportMps.Edt_FileNameChange(Sender: TObject);
begin
  inherited;
  If Length(Trim(Edt_FileName.Text))<>0 Then
    Btn_Ok.Enabled:=True
  Else
    Btn_Ok.Enabled:=False;
end;

procedure TFrm_Mrp_Enter_ImportMps.FormCreate(Sender: TObject);
begin
  inherited;
  Frm_Public_Show:=TFrm_Public_Show.Create(Application);

end;

procedure TFrm_Mrp_Enter_ImportMps.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Frm_Public_Show.Close;
end;

procedure TFrm_Mrp_Enter_ImportMps.btn_CancelClick(Sender: TObject);
begin
  inherited;
  Self.Close;
end;

end.