qimport2common.pas

EMS Advanced.Import.Component.Suite.v2.43

unit QImport2Common;

{$I VerCtrl.inc}

{$IFDEF VCL6} {$WARN SYMBOL_PLATFORM OFF} {$ENDIF}

interface

uses Classes, IniFiles, QImport2, DB, XLSMapParser
     {$IFDEF WIN32}
       , Windows, Graphics {$IFNDEF NOGUI}, Grids, Controls, DBGrids, ComCtrls{$ENDIF}
     {$ENDIF}
     {$IFDEF LINUX}
       {$IFNDEF NOGUI}, QGraphics, QDBGrids, QComCtrls, QGrids, Types, QControls{$ENDIF}
     {$ENDIF};

const
  //---------------------
  //  Don't localize it !
  //---------------------
  QI_PALETTE_PAGE = 'EMS Advanced Import';
  QI_ABOUT = '(About EMS AdvancedImport)';
{$IFDEF CS}
  QI_REG_URL = 'http://www.componentsource.com';
{$ELSE}
  QI_REG_URL = 'http://www.sqlmanager.net/products/tools/advancedimport/buy';
{$ENDIF}

  QI_WIZARD_HELP = 'AIWizard.hlp';

  QI_FULL_PRODUCT_NAME = 'Advanced Data Import for Delphi(R) and C++ Builder(R) (%s)';
  QI_COPYRIGHT = '(C) 1999-2006  EMS Database Management Solutions, Ltd.  All rights reserved.';
  QI_VERSION = '2.43';

  QIW_FIRST_STEP = 'FIRST_STEP';
    QIW_FILE_NAME = 'FileName';
    QIW_IMPORT_TYPE = 'ImportType';
    QIW_GO_TO_LAST_PAGE = 'GoToLastPage';
    QIW_AUTO_SAVE_TEMPLATE = 'AutoSaveTemplate';
  QIW_XLS_MAP = 'XLS_MAP';
  QIW_XLS_OPTIONS = 'XLS_OPTIONS';
    QIW_XLS_SKIP_COLS = 'SkipCols';
    QIW_XLS_SKIP_ROWS = 'SkipRows';
  QIW_TXT_MAP = 'TXT_MAP';
  QIW_TXT_OPTIONS = 'ASCII_OPTIONS';
    QIW_TXT_SKIP_LINES = 'SkipLines';
  QIW_CSV_MAP = 'CSV_MAP';
  QIW_CSV_OPTIONS = 'CSV_OPTIONS';
    QIW_CSV_DELIMITER = 'Delimiter';
    QIW_CSV_SKIP_LINES = 'SkipLines';
    QIW_CSV_QUOTE = 'Quote';
  QIW_DBF_MAP = 'DBF_MAP';
  QIW_DBF_OPTIONS = 'DBF_OPTIONS';
    QIW_DBF_SKIP_DELETED = 'SkipDeleted';
  QIW_XML_MAP = 'XML_MAP';
  QIW_XML_OPTIONS = 'XML_OPTIONS';
    QIW_XML_WRITE_ON_FLY = 'WriteOnFly';
  QIW_MDB_MAP = 'ACCESS_MAP';
  QIW_MDB_OPTIONS = 'ACCESS_OPTIONS';
    QIW_MDB_PASSWORD = 'Password';
    QIW_MDB_SOURCETYPE = 'SourceType';
    QIW_MDB_TABLENAME = 'TableName';
  QIW_MDB_QUERY = 'ACCESS_SQL';
    QIW_MDB_SQL_LINE = 'Line_';
  QIW_BASE_FORMATS = 'BASE_FORMATS';
    QIW_BF_DECIMAL_SEPARATOR = 'DecimalSeparator';
    QIW_BF_THOUSAND_SEPARATOR = 'ThousandSeparator';
    QIW_BF_SHORT_DATE_FORMAT = 'ShortDateFormat';
    QIW_BF_LONG_DATE_FORMAT = 'LongDateFormat';
    QIW_BF_DATE_SEPARATOR = 'DateSeparator';
    QIW_BF_SHORT_TIME_FORMAT = 'ShortTimeFormat';
    QIW_BF_LONG_TIME_FORMAT = 'LongTimeFormat';
    QIW_BF_TIME_SEPARATOR = 'TimeSeparator';
    //QIW_BF_LEFT_QUOTATION = 'LeftQuotation';
    //QIW_BF_RIGHT_QUOTATION = 'RightQuotation';
    //QIW_BF_QUOTATION_ACTION = 'QuotationAction';
  QIW_BOOLEAN_TRUE = 'BOOLEAN_TRUE';
  QIW_BOOLEAN_FALSE = 'BOOLEAN_FALSE';
  QIW_NULL_VALUES = 'NULL_VALUES';
  QIW_DATA_FORMATS = 'DATA_FORMATS_';
    QIW_DF_GENERATOR_VALUE = 'GeneratorValue';
    QIW_DF_GENERATOR_STEP = 'GeneratorStep';
    QIW_DF_CONSTANT_VALUE = 'ConstantValue';
    QIW_DF_NULL_VALUE = 'NullValue';
    QIW_DF_DEFAULT_VALUE = 'DefaultValue';
    QIW_DF_LEFT_QUOTE = 'LeftQuote';
    QIW_DF_RIGHT_QUOTE = 'RightQuote';
    QIW_DF_QUOTE_ACTION = 'QuoteAction';
    QIW_DF_CHAR_CASE = 'CharCase';
    QIW_DF_CHAR_SET = 'CharSet';
  QIW_REPLACEMENTS = 'REPLACEMENTS_';
    QIW_RP_TEXT_TO_FIND = 'TextToFind';
    QIW_RP_REPLACE_WITH = 'ReplaceWith';
    QIW_RP_IGNORE_CASE = 'IgnoreCase';
  QIW_ITEM = 'ITEM_';
  QIW_LAST_STEP = 'LAST_STEP';
    QIW_COMMIT_AFTER_DONE = 'CommitAfterDone';
    QIW_COMMIT_REC_COUNT = 'CommitRecCount';
    QIW_IMPORT_REC_COUNT = 'ImportRecCount';
    QIW_CLOSE_AFTER_IMPORT = 'CloseAfterImport';
    QIW_ENABLE_ERROR_LOG = 'EnableErrorLog';
    QIW_ERROR_LOG_FILE_NAME = 'ErrorLogFileName';
    QIW_REWRITE_ERROR_LOG_FILE = 'RewriteErrorLogFile';
    QIW_SHOW_ERROR_LOG = 'ShowErrorLog';
    QIW_ENABLE_SQL_LOG = 'EnableSQLLog';
    QIW_SQL_LOG_FILE_NAME = 'SQLLogFileName';
    QIW_REWRITE_SQL_LOG_FILE = 'RewriteSQLLogFile';
    QIW_IMPORT_MODE = 'ImportMode';
    QIW_ADD_TYPE = 'AddType';
    QIW_KEY_COLUMNS = 'KeyColumns';


  QI_BASE = 'BASE';
    QI_FILE_NAME = 'FileName';
    QI_IMPORT_TYPE = 'ImportType';
    QI_IMPORT_DESTINATION = 'ImportDestination';
    QI_IMPORT_MODE = 'ImportMode';
    QI_MAP = 'MAP';
    QI_KEY_COLUMNS = 'KeyColumns';
    QI_GRID_CAPTION_ROW = 'GridCaptionRow';
    QI_GRID_START_ROW = 'GridStartRow';
    QI_COMMIT_AFTER_DONE = 'CommitAfterDone';
    QI_COMMIT_REC_COUNT = 'CommitRecCount';
    QI_IMPORT_REC_COUNT = 'ImportRecCount';
    QI_ENABLE_ERROR_LOG = 'EnableErrorLog';
    QI_ERROR_LOG_FILE_NAME = 'ErrorLogFileName';
    QI_REWRITE_ERROR_LOG_FILE = 'RewriteErrorLogFile';
    QI_SHOW_ERROR_LOG = 'ShowErrorLog';
    {QI_ENABLE_SQL_LOG = 'EnableSQLLog';
    QI_SQL_LOG_FILE_NAME = 'SQLLogFileName';
    QI_SQL_LOG_FILE_REWRITE = 'SQLLogFileRewrite';}
  BASE_FORMATS = 'BASE_FORMATS';
    BF_DECIMAL_SEPARATOR = 'DecimalSeparator';
    BF_THOUSAND_SEPARATOR = 'ThousandSeparator';
    BF_SHORT_DATE_FORMAT = 'ShortDateFormat';
    BF_LONG_DATE_FORMAT = 'LongDateFormat';
    BF_SHORT_TIME_FORMAT = 'ShortTimeFormat';
    BF_LONG_TIME_FORMAT = 'LongTimeFormat';
    //BF_LEFT_QUOTATION = 'LeftQuotation';
    //BF_RIGHT_QUOTATION = 'RightQuotation';
    BF_QUOTATION_ACTION = 'QuotationAction';
  BOOLEAN_TRUE = 'BOOLEAN_TRUE';
  BOOLEAN_FALSE = 'BOOLEAN_FALSE';
  NULL_VALUES = 'NULL_VALUES';
  DATA_FORMATS = 'DATA_FORMATS_';
    DF_GENERATOR_VALUE = 'GeneratorValue';
    DF_GENERATOR_STEP = 'GeneratorStep';
    DF_CONSTANT_VALUE = 'ConstantValue';
    DF_NULL_VALUE = 'NullValue';
    DF_DEFAULT_VALUE = 'DefaultValue';
    DF_LEFT_QUOTE = 'LeftQuote';
    DF_RIGHT_QUOTE = 'RightQuote';
    DF_QUOTE_ACTION = 'QuoteAction';
    DF_CHAR_CASE = 'CharCase';
    DF_CHAR_SET = 'CharSet';

  XLS_OPTIONS = 'XLS_OPTIONS';
    XLS_SKIP_COLS = 'SkipCols';
    XLS_SKIP_ROWS = 'SkipRows';
  ASCII_OPTIONS = 'ASCII_OPTIONS';
    ASCII_SKIP_LINES = 'SkipLines';
    ASCII_COMMA = 'Comma';
    ASCII_Quote = 'Quote';
  DBF_OPTIONS = 'DBF_OPTIONS';
    DBF_SKIP_LINES = 'SkipLines';
  XML_OPTIONS = 'XML_OPTIONS';
    XML_SKIP_LINES = 'SkipLines';
    XML_WRITE_ON_FLY = 'WriteOnFly';
  DATA_SET_OPTIONS = 'DATA_SET_OPTIONS';
    DATA_SET_SKIP_LINES = 'SkipLines';

  XLS_EXT = '.xls';
  DBF_EXT = '.dbf';
  DB_EXT  = '.db';
  XML_EXT = '.xml';
  TXT_EXT = '.txt';
  CSV_EXT = '.csv';
  MDB_EXT = '.mdb';

const
  PathDelim  = {$IFDEF WIN} '\'; {$ELSE} '/'; {$ENDIF}

  SShortMonthNameJan = 'Jan';
  SShortMonthNameFeb = 'Feb';
  SShortMonthNameMar = 'Mar';
  SShortMonthNameApr = 'Apr';
  SShortMonthNameMay = 'May';
  SShortMonthNameJun = 'Jun';
  SShortMonthNameJul = 'Jul';
  SShortMonthNameAug = 'Aug';
  SShortMonthNameSep = 'Sep';
  SShortMonthNameOct = 'Oct';
  SShortMonthNameNov = 'Nov';
  SShortMonthNameDec = 'Dec';

  SLongMonthNameJan = 'January';
  SLongMonthNameFeb = 'February';
  SLongMonthNameMar = 'March';
  SLongMonthNameApr = 'April';
  SLongMonthNameMay = 'May';
  SLongMonthNameJun = 'June';
  SLongMonthNameJul = 'July';
  SLongMonthNameAug = 'August';
  SLongMonthNameSep = 'September';
  SLongMonthNameOct = 'October';
  SLongMonthNameNov = 'November';
  SLongMonthNameDec = 'December';

  SShortDayNameSun = 'Sun';
  SShortDayNameMon = 'Mon';
  SShortDayNameTue = 'Tue';
  SShortDayNameWed = 'Wed';
  SShortDayNameThu = 'Thu';
  SShortDayNameFri = 'Fri';
  SShortDayNameSat = 'Sat';

  SLongDayNameSun = 'Sunday';
  SLongDayNameMon = 'Monday';
  SLongDayNameTue = 'Tuesday';
  SLongDayNameWed = 'Wednesday';
  SLongDayNameThu = 'Thursday';
  SLongDayNameFri = 'Friday';
  SLongDayNameSat = 'Saturday';

var
  DefShortMonthNames: array[1..12] of string = (SShortMonthNameJan,
    SShortMonthNameFeb, SShortMonthNameMar, SShortMonthNameApr,
    SShortMonthNameMay, SShortMonthNameJun, SShortMonthNameJul,
    SShortMonthNameAug, SShortMonthNameSep, SShortMonthNameOct,
    SShortMonthNameNov, SShortMonthNameDec);

  DefLongMonthNames: array[1..12] of string = (SLongMonthNameJan,
    SLongMonthNameFeb, SLongMonthNameMar, SLongMonthNameApr,
    SLongMonthNameMay, SLongMonthNameJun, SLongMonthNameJul,
    SLongMonthNameAug, SLongMonthNameSep, SLongMonthNameOct,
    SLongMonthNameNov, SLongMonthNameDec);

function StringToChar(const S: string; const Default: char): char;
function AddQuote(const S, LeftQuote, RightQuote: string): string;
function RemoveQuote(const S, LeftQuote, RightQuote: string): string;
function PadString(const S: string; Chr: char; Len: integer): string;
function AnsiUpperFirst(const S: string): string;
function AnsiUpperFirstWord(const S: string): string;

function Str2Char(const Str: string; Default: char): char;
function Char2Str(Chr: char): string;

{procedure CSVStringToStrings(const Str, LeftQuotation,
  RightQuotation, Separator: string; AStrings: TStrings);}
procedure CSVStringToStrings(const Str: string; Quote, Separator: char;
  AStrings: TStrings);

// StringGrid routines
{$IFNDEF NOGUI}
procedure GridDrawCell(Grid: TStringGrid; const SheetName: string;
  SheetNumber, ACol, ARow: integer; Rect: TRect; State: TGridDrawState;
  DefinedRanges: TMapRow; SkipCols, SkipRows: integer; IsEditing: boolean;
  Selection: TMapRow);
procedure GridMouseDown(Grid: TStringGrid; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer;  FirstCol, LastCol, FirstRow, LastRow,
  SkipRows, SkipCols: integer; var CurrSel: string);
procedure GridSelectCell(Grid: TStringGrid; ACol, ARow: integer;
  Shift: TShiftState; var CurrSel: string);
procedure GridMoveCurrCell(Grid: TStringGrid; ACol, ARow: integer);
procedure GridSelCell(Grid: TStringGrid; RowNo, ColNo: integer;
  NeedClear: boolean; var CurrSel: string);
procedure GridSelRow(Grid: TStringGrid; FirstCol, LastCol, RowNo, StartCol,
  SkipRows, SkipCols: integer; NeedClear: boolean; var CurrSel: string);
procedure GridSelCol(Grid: TStringGrid; FirstRow, LastRow, ColNo, StartRow,
  SkipRows, SkipCols: integer; NeedClear: boolean; var CurrSel: string);
procedure GridFillFixedCells(Grid: TStringGrid);
//procedure GridClearDataCell(Grid: TStringGrid);
{$ENDIF}

// ImportDestination routines
procedure QImportCheckDestination(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet
  {$IFNDEF NOGUI}; DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid{$ENDIF});
function QImportIsDestinationActive(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet
  {$IFNDEF NOGUI}; DBGrid: TDBGrid;
  ListView: TListView;
  StringGrid: TStringGrid{$ENDIF}): boolean;
procedure QImportIsDestinationOpen(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet
  {$IFNDEF NOGUI}; DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid{$ENDIF});
procedure QImportIsDestinationClose(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet
  {$IFNDEF NOGUI}; DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid{$ENDIF});
function QImportDestinationColCount(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet
  {$IFNDEF NOGUI}; DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid{$ENDIF}): integer;
function QImportDestinationColName(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet;
  {$IFNDEF NOGUI} DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid;
 GridCaptionRow,{$ENDIF} Index: integer): string;
function QImportDestinationAssigned(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet
  {$IFNDEF NOGUI}; DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid{$ENDIF}): boolean;
function QImportDestinationFindColumn(IsCSV: boolean;
  ImportDestination: TQImportDestination; DataSet: TDataSet;
  {$IFNDEF NOGUI}DBGrid: TDBGrid;
 ListView: TListView;
  StringGrid: TStringGrid; GridCaptionRow: integer;
{$ENDIF}
  const ColName: string): integer;

function GetListSeparator: Char;
procedure ClearIniFile(IniFile: TIniFile);
function IncludePathDelimiter(const S: string): string;
function GetNumericString(const Str: string): string;
function TryStrToDateTime(const Str: string; var DateTime: TDateTime): Boolean;

{$IFNDEF VCL5}
type
  TReplaceFlags = set of (rfReplaceAll, rfIgnoreCase);

function StringReplace(const S, OldPattern, NewPattern: string;
  Flags: TReplaceFlags): string;
{$ENDIF}

function DirExists(const Directory: string): Boolean;

{$IFDEF WIN32}
function FormatStrToDateTime(ADateTimeStr, AFormatStr: string): TDateTime;
{$ENDIF}

function MinimizeName(const Filename: string; Canvas: TCanvas;
  MaxLen: Integer): string;

implementation

uses SysUtils, XLSUtily
     {$IFDEF WIN32}
       , QImport2StrIDs
     {$ENDIF}
     {$IFDEF LINUX}
        , QImport2Consts
     {$ENDIF};

procedure GetMonthDayNames;
{$IFDEF WIN32}
var
  I: Integer;
  DefaultLCID: LCID;

  function LocalGetLocaleStr(LocaleType, Index: Integer;
    const DefValues: array of string): string;
  begin
    Result := GetLocaleStr(DefaultLCID, LocaleType, '');
    if Result = '' then
      Result := DefValues[Index];
  end;

begin
  DefaultLCID := GetThreadLocale;
  for I := 1 to 12 do
  begin
    ShortMonthNames[I] := LocalGetLocaleStr(LOCALE_SABBREVMONTHNAME1 + I - 1,
      I - Low(DefShortMonthNames), DefShortMonthNames);
    LongMonthNames[I] := LocalGetLocaleStr(LOCALE_SMONTHNAME1 + I - 1,
      I - Low(DefLongMonthNames), DefLongMonthNames);
  end;
end;
{$ELSE}
{$IFDEF LINUX}
  function GetLocaleStr(LocaleIndex, Index: Integer;
    const DefValues: array of string): string;
  var
    temp: PChar;
  begin
    temp := nl_langinfo(LocaleIndex);
    if (temp = nil) or (temp^ = #0) then
      Result := DefValues[Index]
    else
      Result := temp;
  end;

var
  I: Integer;
begin
  for I := 1 to 12 do
  begin
    ShortMonthNames[I] := GetLocaleStr(ABMON_1 + I - 1,
      I - Low(DefShortMonthNames), DefShortMonthNames);
    LongMonthNames[I] := GetLocaleStr(MON_1 + I - 1,
      I - Low(DefLongMonthNames), DefLongMonthNames);
  end;
end;
{$ELSE}
var
  I: Integer;
begin
  for I := 1 to 12 do
  begin
    ShortMonthNames[I] := DefShortMonthNames[I];
    LongMonthNames[I] := DefLongMonthNames[I];
  end;
end;
{$ENDIF}
{$ENDIF}

function StringToChar(const S: string; const Default: char): char;
var
  i: integer;
  tmp: string;
begin
  Result := Default;
  if Length(S) = 1 then Result := S[1]
  else if (Length(S) > 1) and (S[1] = '#') then begin
         i := -1;
         tmp := Copy(S, 2, Length(S) - 1);
         try
          i := StrToInt(tmp);
         except
         end;
         if i > -1 then Result := Chr(i);
       end
end;

function AddQuote(const S, LeftQuote, RightQuote: string): string;
begin
  Result := LeftQuote + S + RightQuote;
end;