ufilefielddefines.pas

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

              if (Pos('-', AValueStr) > 0) or (Pos('/', AValueStr) > 0) or (Pos(':', AValueStr) > 0) then
                sResultStr := 'TO_DATE(''' + AValueStr + ''', ''' + sYearFormat + ''')'
              else
                sResultStr := AValueStr;
            except
              sResultStr := 'NULL';
            end
        end;
      end;
      ftDate: 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 (Trim(AValueStr) = '') then
            sResultStr := 'NULL'
          else
            try
              if (Pos('-', AValueStr) > 0) or (Pos('/', AValueStr) > 0) then
                sResultStr := 'TO_DATE(''' + AValueStr + ''', ''' + sYearFormat + ''')'
              else
                sResultStr := AValueStr;
            except
              sResultStr := 'NULL';
            end;
        end;
      end;
      ftTime: begin
        if ADateFormat <> '' then
        begin
          try
            sResultStr := 'TO_DATE(''' + AValueStr + ''', ''' + ADateFormat + ''')'
          except
            sResultStr := 'NULL';
          end
        end
        else
        begin
          if (Trim(AValueStr) = '') then
            sResultStr := 'NULL'
          else
            try
              if Pos(':', AValueStr) > 0 then
                sResultStr := 'TO_DATE(''' + FormatDateTime('hh:nn:ss',StrToTime(AValueStr)) + ''', ''HH24:MI:SS'')'
              else
                sResultStr := AValueStr;
            except
              sResultStr := 'NULL';
            end;
        end;
      end;
      else begin
        if (AValueStr ='') then
          sResultStr := ''''''
        else
        begin
          try
            if ADataLength > 0 then
            begin
              sWide := AValueStr;
              sResultStr := sWide;
              while Length(sResultStr) > ADataLength do
              begin
                sWide := Copy(sWide, 1, Length(sWide) - 1);
                sResultStr := sWide;
              end;
            end
            else
              sResultSTr := AValueStr;
            sRightStr := sResultStr;
            TempI := Pos('''', sRightStr);
            while TempI > 0 do
            begin
              sLeftStr := Copy(sRightStr, 1, TempI);
              sRightStr := Copy(sRightStr, TempI + 1, Length(sRightStr));
              sResultStr := sLeftStr + '''' + sRightStr;
              TempI := Pos('''', sRightStr);
            end;
            sResultStr := '''' + sResultStr + '''';
          except
            sResultStr := 'NULL';
          end;
        end;
      end;
    end;
    if Trim(sResultStr) = '' then sResultStr := '''''';
    Result := sResultStr;
  end;
begin
  slData := TStringList.Create;
  try
    //将数据按照分隔符拆分到字符串列表中
    SplitStringToList(ADataLine, fSeparator, slData, True);
    //没有定义目标表名称和目标字段，直接返回空值
    if (fFileFields.Count = 0) or (fTableName = '') then
    begin
      AInsertSQL := '';
      AUpdateSQL := '';
      Exit;
    end
    //以下合成数据库操作语句
    else
    begin
      sInsertSQL := '';     //插入操作用的语句
      sUpdateSQL := '';     //更新操作用的语句
      sValuesSQL := '';     //值部分的语句，用于插入操作
      sWhereSQL := '';      //条件部分语句，用于更新操作
      for I := 0 to fFileFields.Count - 1 do
      begin
        //字段名称没有定义，忽略
        if TFileField(fFileFields.Items[I]^).fFieldName = '' then
          Continue;
        //合成语句
        sInsertSQL := sInsertSQL + TFileField(fFileFields.Items[I]^).fFieldName + ',';
        if fForceDefineFields then
        begin
          bIgnoreCoute := IgnoreCoute;
          sDateFormat := DateFormat;
        end
        else
        begin
          bIgnoreCoute := TFileField(fFileFields.Items[I]^).fIgnoreCoute;
          sDateFormat := TFileField(fFileFields.Items[I]^).fDateFormat;
        end;
        case TFileField(fFileFields.Items[I]^).fDataType of  //判断数据来源
          fdtFromFile: begin    //文件导入
            //此处要检查定义的文件索引范围是否合法
            if (TFileField(fFileFields.Items[I]^).fFileIndex < 0) or (TFileField(fFileFields.Items[I]^).fFileIndex >= slData.Count) then
            begin
              sFieldValue := GetFieldValue(TFileField(fFileFields.Items[I]^).fFieldType, TFileField(fFileFields.Items[I]^).fDataLength, '');
            end
            else
            begin
              sTemp := slData[TFileField(fFileFields.Items[I]^).fFileIndex];
              if bIgnoreCoute then
                sTemp := Copy(sTemp, 2, Length(sTemp) - 2);
              sFieldValue := GetFieldValue(TFileField(fFileFields.Items[I]^).fFieldType, TFileField(fFileFields.Items[I]^).fDataLength, sTemp, sDateFormat);
            end;
          end;
          fdtUseDefault:        //系统默认
            sFieldValue := GetFieldValue(TFileField(fFileFields.Items[I]^).fFieldType, TFileField(fFileFields.Items[I]^).fDataLength, TFileField(fFileFields.Items[I]^).fDefaultValue, sDateFormat);
          fdtAutoID:            //自增字段
            sFieldValue := '(select nvl(max('
                         + TFileField(fFileFields.Items[I]^).fFieldName + '),0)+1 from '
                         + TFileField(fFileFields.Items[I]^).fTableName + ')';
        end;
        sValuesSQL := sValuesSQL + sFieldValue + ',';
        //更新字段，判断数据的更新方式
        if TFileField(fFileFields.Items[I]^).fAdjustFlag then       //主键校验字段
          sWhereSQL := sWhereSQL + TFileField(fFileFields.Items[I]^).fFieldName + '=' + sFieldValue + ' and '
        else
          case TFileField(fFileFields.Items[I]^).fUpdateMode of     //非主键校验（更新）字段
            fumUpdate: sUpdateSQL := sUpdateSQL + TFileField(fFileFields.Items[I]^).fFieldName + '=' + sFieldValue + ',';
            fumAdd: sUpdateSQL := sUpdateSQL + TFileField(fFileFields.Items[I]^).fFieldName + '=' + TFileField(fFileFields.Items[I]^).fFieldName + '+' + sFieldValue + ',';
            fumUnion: sUpdateSQL := sUpdateSQL + TFileField(fFileFields.Items[I]^).fFieldName + '=' + TFileField(fFileFields.Items[I]^).fFieldName + '||' + sFieldValue + ',';
          end;
      end;
      if sWhereSQL <> '' then
        System.Delete(sWhereSQL, Length(sWhereSQL) - 4, 5);   //去掉最后一个“ AND ”
      if  sInsertSQL <> '' then
        sInsertSQL[Length(sInsertSQL)] := ')';                  //将最后一个“,”换成“)”
      if sUpdateSQL <> '' then
        sUpdateSQL[Length(sUpdateSQL)] := ' ';                  //将最后一个“,”换成“ ”
      if sValuesSQL <> '' then
        sValuesSQL[Length(sValuesSQL)] := ')';                  //将最后一个“,”换成“)”

      //返回最终的数据操作语句
      if (sInsertSQL <> '') and (sValuesSQL <> '') then
        AInsertSQL := 'insert into ' + fTableName + '(' + sInsertSQL + ' values(' + sValuesSQL
      else
        AInsertSQL := '';
      if (sUpdateSQL <> '') and (sWhereSQL <> '') then
        AUpdateSQL := 'update ' + fTableName + ' set ' + sUpdateSQL + ' where ' + sWhereSQL
      else
        AUpdateSQL := '';
    end;
  finally
    slData.Free;
  end;
end;

procedure TImpFile.SetFieldDefaultValue(AFieldName, AValue: string);
var
  I: Integer;
begin
  //设定某字段的默认值，按字段名称匹配，扫描字段定义列表
  for I := 0 to fFileFields.Count - 1 do
  begin
    if UpperCase(TFileField(fFileFields.Items[I]^).fFieldName) = UpperCase(AFieldName) then
    begin
      TFileField(fFileFields.Items[I]^).fDefaultValue := AValue;
      Break;
    end;
  end;
end;

procedure TImpFile.SetFileName(const Value: string);
begin
  fFileName := Value;
  fLogFileName := ExtractFilePath(fFileName);
  if fLogFileName[Length(fLogFileName)] <> '\' then
    fLogFileName := fLogFileName + '\';
  fLogFileName := fLogFileName + ExtractFileName(fFileName) + '.log';
end;

procedure TImpFile.SetTableName(const Value: string);
begin
  if (fTableName = '') or (fTableName <> Value) then
  begin
    FileDataCount := 0;
    AffectedCount := 0;
  end;
  fTableName := Value;
end;

{ TFileField }

constructor TFileField.Create;
begin
  fTableName := '';
  fFieldName := '';
  fFieldType := ftString;
  fAdjustFlag := False;
  fUpdateMode := fumNone;
  fDataType := fdtFromFile;
  fFileIndex := -1;
  fDefaultValue := '';
  fDateFormat := '';
  fIgnoreCoute := False;
end;

{ TExpFile }

constructor TExpFile.Create;
begin
  fSrcSQL := '';
  fFileName := '';
  fLogFileName := '';
  fSeperator := #9;
  fDataCount := 0;
  fExportedCount := 0;
  DateFormat := 'yyyy/mm/dd';
  fSaveLogFile := False;
  fProgressDialog := TfrmProgressDialog.Create(Application, '正在进行文件导出操作......');
  fShowProgress := False;
end;

destructor TExpFile.Destroy;
begin
  fProgressDialog.Free;
end;

function TExpFile.DoExportData(APrgb: TGauge; AMode: Integer): Integer;
var
  sCurLine, sCurFStr: string;
  tfData, tfLog: TextFile;
  OraDsTemp: TOracleDataSet;
  I, iExpCount: Integer;
  ggPrgb: TGauge;
begin
  DataCount := 0;
  ExportedCount := 0;
  //检查文本文件参数
  if SrcSQL = '' then
  begin
    Result := -1;
    Exit;
  end;
  //检查文本文件参数
  if FileName = '' then
  begin
    Result := -2;
    Exit;
  end;
  if LogFileName = '' then
    SetFileName(FileName);
  try
    AssignFile(tfData, FileName);
    case AMode of
      0:begin
        ReWrite(tfData);
      end;
      else begin
        Append(tfData);
      end;
    end;
    if fSaveLogFile then
    begin
      AssignFile(tfLog, LogFileName);
      ReWrite(tfLog);
    end;
  except
    Result := -88;
    Exit;
  end;
  //创建数据库操作对象
  OraDsTemp := TOracleDataSet.Create(nil);
  try
    if fShowProgress then
    begin
      fProgressDialog.Show;
      ggPrgb := fProgressDialog.ggPrgb;
    end
    else
      ggPrgb := APrgb;
    OraDsTemp.Session := dmData.OraSessionScm;
    OraDsTemp.Close;
    OraDsTemp.SQL.Text := SrcSQL;
    try
      OraDsTemp.Open;
    except
      Result := - 9;
      if fSaveLogFile then
        WriteLn(tfLog, '数据打开失败！语句：' + SrcSQL);
      Exit;
    end;
    //设定进度条
    DataCount := OraDsTemp.RecordCount;
    SetProgressMax(ggPrgb, DataCount);
    SetProgressPosition(ggPrgb, 0);
    if ggPrgb <> nil then
      ggPrgb.Visible := True;
    //打开导入文件和日志文件
    iExpCount := 0;
    //开始导出文本文件
    if ColNameHeader then
    begin
      sCurLine := '';
      for I := 0 to OraDsTemp.FieldCount - 1 do
      begin
        sCurLine := sCurLine + OraDsTemp.Fields[I].FieldName + Seperator;
      end;
      System.Delete(sCurLine, Length(sCurLine) - Length(Seperator) + 1, Length(Seperator));
      WriteLn(tfData, sCurLine);
    end;
    while not OraDsTemp.Eof  do
    begin
      try
        //刷新显示与进度
        Application.ProcessMessages;
        AddProgressPosition(ggPrgb, 1);
        sCurLine := '';
        for I := 0 to OraDsTemp.FieldCount - 1 do
        begin
          sCurFStr := '';
          case OraDsTemp.Fields[I].DataType of
            ftBoolean:begin
              if OraDsTemp.Fields[I].IsNull then sCurFStr := ''
              else if OraDsTemp.Fields[I].AsBoolean = True then sCurFStr := '1'
              else sCurFStr := '0';
            end;
            ftDate:begin
              if OraDsTemp.Fields[I].IsNull then sCurFStr := ''
              else sCurFStr := FormatDateTime(DateFormat, OraDsTemp.Fields[I].AsDateTime);
            end;
            ftTime:begin
              if OraDsTemp.Fields[I].IsNull then sCurFStr := ''
              else sCurFStr := FormatDateTime('hh:nn:ss', OraDsTemp.Fields[I].AsDateTime);
            end;
            ftDateTime:begin
              if OraDsTemp.Fields[I].IsNull then sCurFStr := ''
              else sCurFStr := FormatDateTime(DateFormat, OraDsTemp.Fields[I].AsDateTime);
            end;
            else begin
              if OraDsTemp.Fields[I].IsNull then sCurFStr := ''
              else sCurFStr := OraDsTemp.Fields[I].AsString;
            end;
          end;
          sCurLine := sCurLine + sCurFStr + Seperator;
        end;
        System.Delete(sCurLine, Length(sCurLine) - Length(Seperator) + 1, Length(Seperator));
        WriteLn(tfData, sCurLine);
        OraDsTemp.Next;
        iExpCount := iExpCount + 1;
      except
        on E: Exception do
        begin
          Result := -99;
          if fSaveLogFile then
            WriteLn(tfLog, sCurLine + '：' + E.Message)
        end;
      end; //try
    end; //while
    Result := iExpCount;
    ExportedCount := iExpCount;
    try
      if fSaveLogFile then
      begin
        WriteLn(tfLog, '');
        WriteLn(tfLog, fDataName + '(' + ExtractFileName(FileName) + ')：');
        WriteLn(tfLog, '时间：' + FormatDateTime('yyyy-mm-dd hh:nn:ss', Now));
        WriteLn(tfLog, '数据行数：' + IntToStr(DataCount));
        WriteLn(tfLog, '成功导出数据库行数：' + IntToStr(ExportedCount));
      end;
    except
    end;
  finally
    if ggPrgb <> nil then
      ggPrgb.Visible := False;
    //释放数据库操作对象，关闭打开的文件
    OraDsTemp.Close;
    OraDsTemp.Free;
    Close(tfData);
    //如果没有数据，删除文件（不生成）
    try
      if iExpCount = 0 then
        DeleteFile(FileName);
    except
    end;
    if fSaveLogFile then
      Close(tfLog);
    fProgressDialog.Close;
  end;
end;

procedure TExpFile.SetFileName(const Value: string);
begin
  fFileName := Value;
  fLogFileName := ExtractFilePath(fFileName);
  if fLogFileName[Length(fLogFileName)] <> '\' then
    fLogFileName := fLogFileName + '\';
  fLogFileName := fLogFileName  + ExtractFileName(fFileName) + '.log';
end;

end.