ufilefielddefines.pas

从文本文件象Oracle数据库导入数据

unit uFileFieldDefines;

interface

uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, DB,
     Oracle, OracleData, FileCtrl, comctrls, Gauges, ProgressDialog;

type

  TFieldUpdateMode = (fumNone, fumUpdate, fumAdd, fumUnion);
  {定义数据字段的更新模式：（不更新，替换，累加，合并），字段的 AdjustFlag 为 True 的时候有效
  }
  TFieldDataType = (fdtFromFile, fdtUseDefault, fdtAutoID);
  {定义数据字段的数据来源：（文件导入，系统默认，自增字段）
  }

  TFileField = class
  {文件字段的定义类
  }
  public
    fTableName: string;            //目标表名称
    fFieldName: string;            //字段名称
    fFieldType: TFieldType;        //字段类型
    fAdjustFlag: Boolean;          //是否为主键校验字段
    fUpdateMode: TFieldUpdateMode; //数据更新模式
    fDataType: TFieldDataType;     //数据来源
    fFileIndex: Integer;           //文件中对应的下标索引
    fDefaultValue: string;         //系统默认值
    fDataLength: Integer;
    fDateFormat: string;
    fIgnoreCoute: Boolean;
    constructor Create;
    {构造器
    }
  end;

  PFileField = ^TFileField;
  {文件字段的定义类的指针
  }

  TImpFile = class
  private
  {导入文件的定义类
  }
  protected
    fDataNo: string;                    //数据代号
    fDataName: string;                  //数据名称
    fTableName: string;                 //目标表名称
    fSeparator: string;                 //数据分隔符
    fCommitCount: Integer;              //每次批量提交行数，如果小于或等于零，则全部数据导入完成后进行一次提交
    fAdjustFlag: Boolean;               //导入过程是否进行主键校验
    fFileName: string;                  //文本文件名称（包含完整路径）
    fLogFileName: string;               //错误日志存放文件名称，默认为 fFileName + “.log”
    fDateFormat: string;
    fIgnoreCoute: Boolean;              //是否忽略文本两端的引号

    fProgressDialog: TfrmProgressDialog;

    procedure SetFileName(const Value: string);
    procedure SetTableName(const Value: string);
  public
    fFileFields: TList;                 //对应的字段定义指针列表
    fSaveLogFile: Boolean;
    fFirstLine: Boolean;                //首行是否为数据
    fFileDataCount: Integer;            //文本文件中数据行数
    fAffectedCount: Integer;            //成功影响数据库的数据行数
    fShowProgress: Boolean;             //
    fForceDefineFields: Boolean;        //是否强制规定字段的定义

    property DataNo: string           read fDataNo          write fDataNo         ;
    property DataName: string         read fDataName        write fDataName       ;
    property TableName: string        read fTableName       write SetTableName    ;
    property Separator: string        read fSeparator       write fSeparator      ;
    property CommitCount: Integer     read fCommitCount     write fCommitCount    ;
    property AdjustFlag: Boolean      read fAdjustFlag      write fAdjustFlag     ;
    property FileDataCount: Integer   read fFileDataCount   write fFileDataCount  ;
    property AffectedCount: Integer   read fAffectedCount   write fAffectedCount  ;
    property FileName: string         read fFileName        write SetFileName     ;
    property LogFileName: string      read fLogFileName     write fLogFileName    ;
    property DateFormat: string       read fDateFormat      write fDateFormat     ;
    property IgnoreCoute: boolean     read fIgnoreCoute     write fIgnoreCoute    ;

    constructor Create;
    {功能：构造器
    }
    destructor Destroy;
    {功能：解构器
    }
    procedure SetFieldDefaultValue(AFieldName, AValue: string);
    {功能：按照字段名称设定字段定义列表中对应的定义字段的默认值
     入参：AFieldName：要设定默认值的字段
           AValue：    要设定的值
    }
    procedure GetSQL(var AInsertSQL, AUpdateSQL: string; ADataLine: string);
    {功能：根据导入文本文件中的一行数据，生成对应的数据库操作语句
     入参：AInsertSQL：用于数据库记录插入的语句，按地址传人，可返回
           AUpdeteSQL：用于数据库记录更新的语句，按地址传人，可返回
           ADataLine： 文本文件中代表数据的一行文本
           ASeperator：文本中数据字段之间的分隔符，默认为#9（Tab键）
    }
    function DoImport(APrgb: TGauge = nil): Integer;
    {功能：进行文本文件数据的导入操作
     入参：APrgb：文件导入过程中用来显示进度的控件，可为空
    }
  end;

  PImpFile = ^TImpFile;
  {导入文件的定义类的指针
  }

  TExpFile = class
  {数据传出到文件定义类
  }
  protected
    fSrcSQL: string;
    fFileName: string;
    fLogFileName: string;
    fSeperator: string;
    fDataCount: Integer;
    fExportedCount: Integer;
    fColNameHeader: Boolean;
    fDateFormat: string;
    fProgressDialog: TfrmProgressDialog;
   procedure SetFileName(const Value: string);
  public
    fDataName: string;
    fSaveLogFile: Boolean;
    fShowProgress: Boolean;

    property SrcSQL: string           read fSrcSQL          write fSrcSQL          ;
    property FileName: string         read fFileName        write SetFileName      ;
    property LogFileName: string      read fLogFileName     write fLogFileName     ;
    property Seperator: string        read fSeperator       write fSeperator       ;
    property DataCount: Integer       read fDataCount       write fDataCount       ;
    property ExportedCount: Integer   read fExportedCount   write fExportedCount   ;
    property ColNameHeader: Boolean   read fColNameHeader   write fColNameHeader   ;
    property DateFormat: string       read fDateFormat      write fDateFormat      ;

    constructor Create;
    destructor Destroy;
    function DoExportData(APrgb: TGauge = nil; AMode: Integer = 0): Integer;
  end;

implementation

uses uAppStringUtilts, dData;

procedure SetProgressPosition(APrgb: TGauge; APosition: Integer);
//设定进度条的位置
begin
  if APrgb <> nil then
    APrgb.Progress := APosition;
end;

procedure SetProgressMax(APrgb: TGauge; AMax: Integer);
//设定进度条的上限值
begin
  if APrgb <> nil then
    APrgb.MaxValue := AMax;
end;

procedure AddProgressPosition(APrgb: TGauge; AValue: Integer);
//将进度条的进度加上一个值
begin
  if APrgb <> nil then
    APrgb.Progress := APrgb.Progress + AValue;
end;

{ TImpFile }

constructor TImpFile.Create;
begin
  fDataNo := '';
  fDataName := '';
  fTableName := '';
  fCommitCount := 1;
  fSeparator := #9;
  fAdjustFlag := True;
  //创建对应的字段定义对象
  fFirstLine := True;
  fFileFields := TList.Create;
  fFileDataCount := 0;
  fAffectedCount := 0;
  fSaveLogFile := True;
  fProgressDialog := TfrmProgressDialog.Create(Application, '正在进行文件导入操作......');
  fShowProgress := False;
  fIgnoreCoute := False;
  fForceDefineFields := True;
  DateFormat := '';
end;

destructor TImpFile.Destroy;
begin
  //释放对应的字段定义对象
  fFileFields.Free;
  fProgressDialog.Free;
end;

function TImpFile.DoImport(APrgb: TGauge): Integer;
var
  tfData, tfLog: TextFile;
  OraQryTemp: TOracleQuery;
  I, iImpCount: Integer;
  sInsertSQL, sUpdateSQL, sCurLine: string;
  ggPrgb: TGauge;

  function GetFileLineCount(FileName: string): Integer;
  //返回文件数据的行数
  var
    DataFile: TextFile;
    LineCount: Integer;
  begin
    try
      AssignFile(DataFile, FileName);
      Reset(DataFile);
      LineCount := 0;
      while not Eof(DataFile) do
      begin
        ReadLn(DataFile);
        LineCount := LineCount + 1;
      end;
      CloseFile(DataFile);
    except
      LineCount := 0;
    end;
    Result := LineCount;
  end;

begin
  FileDataCount := 0;
  AffectedCount := 0;
  //检查文本文件参数
  if FileName = '' then
  begin
    Result := -1;
    Exit;
  end;
  //检查文本文件是否存在
  if not FileExists(FileName) then
  begin
    Result := -2;
    Exit;
  end;
  if LogFileName = '' then
    SetFileName(FileName);
  //创建数据库操作对象
  OraQryTemp := TOracleQuery.Create(nil);
  try
    //设定进度条
    if fShowProgress then
    begin
      fProgressDialog.Show;
      ggPrgb := fProgressDialog.ggPrgb;
    end
    else
      ggPrgb := APrgb;
    FileDataCount := GetFileLineCount(FileName);
    SetProgressMax(ggPrgb, FileDataCount);
    SetProgressPosition(ggPrgb, 0);
    if APrgb <> nil then
      APrgb.Visible := True;
    //打开导入文件和日志文件
    try
      AssignFile(tfData, FileName);
      Reset(tfData);
      if fSaveLogFile then
      begin
        AssignFile(tfLog, LogFileName);
        ReWrite(tfLog);
      end;
    except
    end;
    OraQryTemp.Session := dmData.OraSessionScm;
    I := 0;
    iImpCount := 0;
    //如果首行不是数据，则首先读取一行
    if not fFirstLine then
      ReadLn(tfData, sCurLine);
    //开始导入文本文件
    while not Eof(tfData) do
    begin
      try
        //刷新显示与进度
        Application.ProcessMessages;
        AddProgressPosition(ggPrgb, 1);
        //读取一行
        ReadLn(tfData, sCurLine);
        //获得数据库操作语句
        GetSQL(sInsertSQL, sUpdateSQL, sCurLine);
        //导入数据，如果进行主键校验，先修改再添加（没有找到时），否则直接添加
        if fAdjustFlag and (sUpdateSQL <> '') then
        begin
          OraQryTemp.SQL.Text := sUpdateSQL;
          OraQryTemp.Execute;
          if OraQryTemp.RowsProcessed = 0 then
          begin
            if sInsertSQL <> '' then
            begin
              OraQryTemp.SQL.Text := sInsertSQL;
              OraQryTemp.Execute;
            end;
          end;
          iImpCount := iImpCount + 1;
        end
        else
        begin
          if sInsertSQL <> '' then
          begin
            OraQryTemp.SQL.Text := sInsertSQL;
            try
              OraQryTemp.Execute;
              iImpCount := iImpCount + 1;
            except
              on E: Exception do
              begin
                Result := -99;
                WriteLn(tfLog, sCurLine + '：' + E.Message + '：' + OraQryTemp.SQL.Text)
              end;
            end;
          end;
        end;
        I := I + 1;
        //判断是否进行提交
        if (fCommitCount > 0) and (I mod fCommitCount = 0) then
          dmData.OraSessionScm.Commit;
      except
        on E: Exception do
        begin
          Result := -99;
          if fSaveLogFile then
            WriteLn(tfLog, sCurLine + '：' + E.Message + '：' + OraQryTemp.SQL.Text);
        end;
      end; //try
    end; //while
    try
      //最终要提交一次
      dmData.OraSessionScm.Commit;
    except
      on E: Exception do
      begin
        //dmData.OraSessionScm.RollBack;
        //记录错误日志
        Result := -99;
      end;
    end;
    Result := iImpCount;
    AffectedCount := iImpCount;
    try
      if fSaveLogFile then
      begin
        WriteLn(tfLog, '');
        WriteLn(tfLog, '时间：' + FormatDateTime('yyyy-mm-dd hh:nn:ss', Now));
        WriteLn(tfLog, '数据文件名：' + FileName);
        WriteLn(tfLog, '数据库表名称：' + TableName);
        WriteLn(tfLog, '文件数据行数：' + IntToStr(FileDataCount));
        WriteLn(tfLog, '成功导入数据库行数：' + IntToStr(AffectedCount));
      end;
    except
    end;
  finally
    if ggPrgb <> nil then
      ggPrgb.Visible := False;
    //释放数据库操作对象，关闭打开的文件
    OraQryTemp.Close;
    OraQryTemp.Free;
    CloseFile(tfData);
    if fSaveLogFile then
      CloseFile(tfLog);
    fProgressDialog.Close;
  end;
end;

procedure TImpFile.GetSQL(var AInsertSQL, AUpdateSQL: string; ADataLine: string);
var
  sInsertSQL, sValuesSQL, sUpdateSQL, sWhereSQL: string;
  slData: TStringList;
  I: Integer;
  sFieldValue, sTemp: string;
  sDateFormat: string;
  bIgnoreCoute: Boolean;
  sWide: WideString;

  function GetFieldValue(AFieldType: TFieldType; ADataLength: Integer; AValueStr: string; ADateFormat: string = ''): string;
  //根据字段类型，代表字符串返回字段值（用于sql语句）
  var
    TempI: Integer;
    sResultStr: string;
    sLeftStr, sRightStr: string;
    sYearFormat: string;
  begin
    if Trim(AValueStr) = '' then
    begin
      Result := 'NULL';
      Exit;
    end;
    case AFieldType of
      ftWord,ftSmallint,ftInteger: begin
        if (Trim(AValueStr) = '') then
          sResultStr := 'NULL'
        else
          try
            sResultStr := AValueStr;
          except
            sResultStr := 'NULL';
          end;
      end;
      ftFloat, ftCurrency, ftBCD: begin
        if (Trim(AValueStr) = '') then
          sResultStr := 'NULL'
        else
          try
            sResultStr := AValueStr;
          except
            sResultStr := 'NULL';
          end;
      end;
      ftDateTime: begin
        if ADateFormat <> '' then
        begin
          try
            sResultStr := 'TO_DATE(''' + AValueStr + ''', ''' + ADateFormat + ''')'
          except
            sResultStr := 'NULL';
          end
        end
        else
        begin
          if Pos('-', AValueStr) > 0 then
            sYearFormat := 'yyyy-mm-dd'
          else if Pos('/', AValueStr) > 0 then
            sYearFormat := 'yyyy/mm/dd'
          else
            sYearFormat := 'yyyymmdd';
          if Pos(':', AValueStr) > 0 then
            sYearFormat := sYearFormat + ' hh24:mi:ss';
          if (Trim(AValueStr) = '') then
            sResultStr := 'NULL'
          else
            try