unit1.pas

数据转换程序（mysql

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Controls, Forms,
  Dialogs, DB, DBTables, ComCtrls, StdCtrls;

type
  TForm1 = class(TForm)
    StatusBar1: TStatusBar;
    ProgressBar1: TProgressBar;
    qrXT: TQuery;
    DB_XT: TDatabase;
    qrTmp: TQuery;
    DB_Netit: TDatabase;
    qrNetit: TQuery;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    ComboBox1: TComboBox;
    Edit1: TEdit;
    Edit2: TEdit;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    ComboBox2: TComboBox;
    Edit3: TEdit;
    Edit4: TEdit;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Button1KeyPress(Sender: TObject; var Key: Char);
    procedure Button3Click(Sender: TObject);
  private
    aTableIniName: String;
    sCfgFileName: String;
    sXT_TableName: array [0..255] of Char;
    sNetit_TableName: array [0..255] of Char;
    sXT_KeyField: array [0..255] of Char;
    sNetit_KeyField: array [0..255] of Char;
    function ExtractLeftValue(const Source: String): String;
    function ExtractRightValue(const Source: String): String;
    function ExtractDataType(const Source: String): String;
    function GetValueFromDict(const FieldName, FValue: String; CVFlag: Byte): String;
    function EncodeMyDate(const S: String): TDateTime;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

{ TForm1 }

//取一个字符串表达式的右值部分
function TForm1.ExtractRightValue(const Source: String): String;
var iStartPos, i: Integer;
begin
    iStartPos := Pos('=', Source);
    if (iStartPos)<1 then   //未找到"="，认为项目无效
        Exit;
    Inc(iStartPos, 1);
    for i:= iStartPos to Length(Source) do
    begin
        if (Source[i] < '0') or (Source[i] = '|') then
            Break;  //认为遇到了无效字符，不再继续复制
    end;
    Result := Copy(Source, iStartPos, i - iStartPos);
end;

procedure TForm1.Button1Click(Sender: TObject);
var SL1: TStringList;
    i,j,iRecCount: Integer;
    aTmp, aDataType, aSrcField, aDestField: String;
    dtTmp: TDateTime;
begin
    i := 0;
    j := 0;
    if (ComboBox1.Text = '') or (ComboBox2.Text = '') then
    begin
        MessageBox(Handle, PChar('请选择源数据DSN和目标数据DSN，如果没有相应配置，'+
            #13+'请在ODBC配置工具里建立。'), '数据源', MB_ICONWARNING);
        Exit;
    end;

    try
        DB_XT.AliasName := ComboBox1.Text;
        DB_Netit.AliasName := ComboBox2.Text;
        DB_XT.Params.Clear;
        DB_XT.Params.Add('User Name='+Edit1.Text);
        DB_XT.Params.Add('Password='+Edit2.Text);
        DB_Netit.Params.Clear;
        DB_Netit.Params.Add('User Name='+Edit3.Text);
        DB_Netit.Params.Add('Password='+Edit4.Text);
        DB_XT.Open;
        DB_Netit.Open;
    except
        on E: Exception do
        begin
            MessageBox(Handle, PChar('连接数据库失败！'+#13+E.Message), '错误',
                MB_ICONERROR);
            Exit;
        end;
    end;


    SL1 := TStringList.Create;
    try
        SL1.LoadFromFile(sCfgFileName);
        //获取记录数
        qrXT.Close;
        qrXT.SQL.Clear;
        qrXT.SQL.Add(Format('Select Count(*) from %s', [sXT_TableName]));
        qrXT.Open;
        iRecCount := qrXT.Fields.Fields[0].AsInteger;

        qrXT.Close;
        qrXT.SQL.Clear;
        qrXT.SQL.Add(Format('Select * from %s', [sXT_TableName]));
        qrXT.Open;
        StatusBar1.Panels[1].Text := Format('共%d条记录', [iRecCount]);
        ProgressBar1.Max := iRecCount;
        ProgressBar1.Position := 0;

        qrNetit.RequestLive := True;
        qrNetit.Close;
        qrNetit.SQL.Clear;
        qrNetit.SQL.Add(Format('Select * from %s', [sNetit_TableName]));
        qrNetit.Open;

        if not qrNetit.Eof then
        begin
            case MessageBox(Handle, PChar(Format('%s表里已经有记录，要删除吗？',
                [sNetit_TableName])), '导入', MB_ICONINFORMATION + MB_YESNOCANCEL) of
                IDYES:
                begin
                    qrNetit.Close;
                    qrNetit.SQL.Clear;
                    qrNetit.SQL.Add(Format('Delete from %s', [sNetit_TableName]));
                    //qrNetit.SQL.Add(Format('Truncate table %s', [sNetit_TableName]));
                    qrNetit.ExecSQL;

                    qrNetit.SQL.Clear;
                    qrNetit.SQL.Add(Format('Select * from %s', [sNetit_TableName]));
                    qrNetit.Open;
                end;
                IDCANCEL:
                begin
                    qrXT.Close;
                    qrNetit.Close;
                    Exit;
                end;
            end; //end case;
        end; //end if not qrXT.Eof...

        try
            //DB_Netit.StartTransaction;
            for i:=0 to iRecCount-1 do
           // for i:=0 to 100 do
            begin
                qrNetit.Append;
                qrNetit.FieldByName('bmid').AsInteger := 1;
                qrNetit.FieldByName('人员类别').AsInteger := 0;
                qrNetit.FieldByName('考核分类').AsInteger := 0;
                qrNetit.FieldByName('人员等级').AsString := 'N/A';
                qrNetit.FieldByName('人员编号').AsString := FormatFloat('000000',
                    i+1);
                //逐条记录进行处理
                for j:=0 to SL1.Count-1 do
                begin
                    aTmp := Trim(SL1.Strings[j]);
                    if aTmp[1] = '#' then   //此项目被注释，跳过
                        Continue;
                    //逐个字段进行处理
                    aSrcField := ExtractLeftValue(aTmp);
                    aDestField := ExtractRightValue(aTmp);
                    aDataType := ExtractDataType(aTmp);
                    if (aSrcField = '') or (aDestField = '') then
                        Continue;   //配置文件中的此项无效，跳过此项
                    if (CompareText(aDataType, 'STRINGS')=0) then
                    begin
                        //此字段的值为多项字串，需要从字典表中取值
                        aTmp := GetValueFromDict(aSrcField,
                            qrXT.FieldByName(aSrcField).AsString, 0);
                        qrNetit.FieldByName(aDestField).AsString := aTmp;
                    end
                    else if (CompareText(aDataType, 'DATE')=0) then
                    begin
                        //此字段的值为日期字符串型，需要进行转换
                        dtTmp := EncodeMyDate(qrXT.FieldByName(aSrcField).AsString);
                        if dtTmp<>0 then
                            qrNetit.FieldByName(aDestField).AsDateTime := dtTmp;
                    end
                    else begin
                        //此字段的值是字符串或整型，可以直接赋值
                        qrNetit.FieldByName(aDestField).Value :=
                            qrXT.FieldByName(aSrcField).Value;
                    end;
                end; //end for j:=0...
                qrNetit.Post;
                qrXT.Next;
                ProgressBar1.StepBy(1);
                Application.ProcessMessages;
            end; //end for i:=0...
          // DB_Netit.Commit;
            MessageBox(Handle, '数据导入完成！', '导入', MB_ICONINFORMATION);
        except
            on E: Exception do
            begin
             //   if DB_Netit.InTransaction then
               //     DB_Netit.Rollback;
                MessageBox(Handle, PChar(Format('数据导入失败！记录编号：%d，错误信息：%s',
                    [i+1, #13+E.Message])), '导入', MB_ICONERROR);
            end;
        end;
    finally
        SL1.Free;
       
        qrNetit.RequestLive := False;
        DB_XT.Close;
        DB_Netit.Close;
    end;
end;

procedure TForm1.FormCreate(Sender: TObject);
var aPath: String;
begin
    aPath := ExtractFilePath(ParamStr(0));
    aTableIniName := aPath+'Tables.ini';
    sCfgFileName := aPath+'T2o.cfg';

    if (not FileExists(sCfgFileName)) or
        (not FileExists(aTableIniName)) then
    begin
        MessageBox(Handle, '没有找到系统配置文件“T2o.cfg”或“Tables.ini”，程序无法运行！',
            '错误', MB_ICONERROR);
        Exit;
    end;

    GetPrivateProfileString('TableName', 'XT', nil, sXT_TableName,
        SizeOf(sXT_TableName), PChar(aTableIniName));
    GetPrivateProfileString('TableName', 'Netit', nil, sNetit_TableName,
        SizeOf(sNetit_TableName), PChar(aTableIniName));
    GetPrivateProfileString('KeyField', 'XT_PrmKey', nil, sXT_KeyField,
        SizeOf(sXT_KeyField), PChar(aTableIniName));
    GetPrivateProfileString('KeyField', 'Netit_PrmKey', nil, sNetit_KeyField,
        SizeOf(sNetit_KeyField), PChar(aTableIniName));
    if (StrLen(sXT_TableName)=0) or (StrLen(sNetit_TableName)=0) then
    begin
        MessageBox(Handle, '读取人员信息表名称错误！',
            '错误', MB_ICONERROR);
        Application.Terminate;
    end;

    Session.ConfigMode := cmVirtual;
    Session.GetAliasNames(ComboBox1.Items);
    Session.GetAliasNames(ComboBox2.Items);
end;

function TForm1.GetValueFromDict(const FieldName, FValue: String;
    CVFlag: Byte): String;
    //CVFlag：转换标志。0：信通->网信；1: 网信->信通
var aDictName: String;
    szTmp: PChar;
    i,iPos: Integer;
    sl: TStringList;
    str:string;
begin
    Result := '';
    if (FValue = '') then
        Exit;
    iPos := 0;
    qrTmp.Close;
    qrTmp.SQL.Clear;
    //从字段名获得对应字典表名称
    qrTmp.SQL.Add('Select Dict from FieldDef where F_ID = :aID');
    qrTmp.Params[0].AsString := FieldName;
    qrTmp.Open;
    if qrTmp.Eof then   //该字段无对应字典表，直接从源表中取数据
    begin