unit1.pas

数据转换程序（mysql

        qrTmp.Close;
        Exit;
    end
    else
        aDictName := qrTmp.Fields[0].AsString;
    qrTmp.Close;
    qrTmp.SQL.Clear;
    //从字典表中取关键字对应信息
    qrTmp.SQL.Add('Select txt from NetDict where ZDID = :aDictName');
    qrTmp.Params[0].AsString := aDictName;
    qrTmp.Open;
    if not qrTmp.Eof then
    begin
        szTmp := PChar(qrTmp.Fields[0].AsString);
        if CVFlag = 0 then
        begin   //信通->网信
            iPos := Pos(FValue+'=', szTmp)-1;
            if iPos>0 then
            begin
                szTmp := szTmp + iPos;
                Result := Trim(ExtractRightValue(szTmp));
            end;
        end
        else begin  //网信->信通
            sl := TStringList.Create;
            try
                sl.Text := qrTmp.Fields[0].AsString;
                //sl.Add(qrTmp.Fields[0].AsString);
                for i:=0 to sl.Count-1 do
                begin
                   // str:='云南盈江县电信局';
                   str:=fvalue;
                    szTmp := PChar(sl.Strings[i]);
                    iPos := Pos('='+str, szTmp)-1;
                  //  sztmp:='  JG251405=云南盈江县电信局|DW03||||t';
                    // iPos := Pos('='+str, szTmp)-1;
                    if iPos >=0 then
                    begin
                        Result := Trim(Copy(szTmp, 1, iPos));
                        Break;
                    end;
                end;
            finally
                sl.Free;
            end;
        end;
    end;
    qrTmp.Close;
end;

//取字符串表达式的数据类型
function TForm1.ExtractDataType(const Source: String): String;
var iStartPos, iEndPos: Integer;
begin
    iStartPos := Pos('{', Source)+1;
    iEndPos := Pos('}', Source);
    if (iStartPos > 0) and (iEndPos > iStartPos) then
        Result := Copy(Source, iStartPos, iEndPos - iStartPos);
end;

procedure TForm1.Button2Click(Sender: TObject);
var i: Integer;
begin
    if MessageBox(Handle, '即将更新人员编号，是否继续？', '', MB_ICONQUESTION+MB_YESNO)=IDNO then
        Exit;
    qrNetit.Close;
    qrNetit.SQL.Clear;
    qrNetit.SQL.Add(Format('Select * from %s', [sNetit_TableName]));
    qrNetit.Open;
    for i:=0 to qrNetit.RecordCount-1 do
    begin
        qrNetit.Edit;
        qrNetit.FieldByName('人员编号').AsString := FormatFloat('00000', i+1);
        qrNetit.Post;
        qrNetit.Next;
    end;
    qrNetit.Close;
end;

//取一个字符串表达式的左值部分
function TForm1.ExtractLeftValue(const Source: String): String;
var iEndPos: Integer;
begin
    iEndPos := Pos('{', Source);
    if iEndPos > 0 then
        Result := Copy(Source, 1, iEndPos - 1);
end;

//把字符串转换为日期
function TForm1.EncodeMyDate(const S: String): TDateTime;
var aYear, aMon, aDay: String;
    l: Integer;
    dtTmp: TDate;
begin
    l := Length(S);
    Result := 0;
    case l of
        6:      aDay := '01';
        8:      aDay := Copy(S, 7, 2);
        else    Exit;
    end;
    aYear := Copy(S, 1, 4);
    aMon := Copy(S, 5, 2);
    try
        dtTmp := StrToDate(Format('%s-%s-%s', [aYear, aMon, aDay]));
        Result := dtTmp;
    except
    end;
end;

procedure TForm1.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
    case Key of
        VK_RETURN:
            begin
                Key := 0;
                SelectNext(ActiveControl, True, True);
            end;
        VK_ESCAPE:
            Close;
    end;
end;

procedure TForm1.Button1KeyPress(Sender: TObject; var Key: Char);
begin
    if Key = #13 then
        Close;
end;

procedure TForm1.Button3Click(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;

    Button3.Enabled := False;
    SL1 := TStringList.Create;
    try
        SL1.LoadFromFile(sCfgFileName);
        //获取网信系统人员信息记录数
        qrNetit.Close;
        qrNetit.SQL.Clear;
        qrNetit.SQL.Add(Format('Select Count(*) from %s where 人员类别=0', [sNetit_TableName]));
        qrNetit.Open;
        iRecCount := qrNetit.Fields.Fields[0].AsInteger;

        qrNetit.Close;
        qrNetit.SQL.Clear;
        qrNetit.SQL.Add(Format('Select * from %s where 人员类别=0', [sNetit_TableName]));
        qrNetit.Open;
        StatusBar1.Panels[1].Text := Format('共%d条记录', [iRecCount]);
        ProgressBar1.Max := iRecCount;
        ProgressBar1.Position := 0;

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

                    qrXT.SQL.Clear;
                    qrXT.SQL.Add(Format('Select * from %s', [sXT_TableName]));
                    qrXT.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
                qrXT.Append;
                //逐个字段进行处理
                for j:=0 to SL1.Count-1 do
                begin
                    aTmp := Trim(SL1.Strings[j]);
                    if aTmp[1] = '#' then
                        Continue;
                    //逐个字段进行处理
                    aSrcField := ExtractRightValue(aTmp);
                    aDestField := ExtractLeftValue(aTmp);
                    aDataType := ExtractDataType(aTmp);
                    if (aSrcField = '') or (aDestField = '') then
                        Continue;   //配置文件中的此项无效，跳过此项
                    if (CompareText(aDataType, 'STRINGS')=0) then
                    begin
                        //此字段的值为多项字串，需要从字典表中取值
                            aTmp := GetValueFromDict(aDestField,
                            qrNetit.FieldByName(aSrcField).AsString, 1);

                        //导出部门单位用
                           if aTmp='全省' then aTmp:='省公司';

                             qrXT.FieldByName(aDestField).AsString := aTmp;
                           end
                           else if (CompareText(aDataType, 'DATE')=0) then
                                begin
                              //此字段的值为日期字符串型，需要进行转换
                                  dtTmp := qrNetit.FieldByName(aSrcField).AsDateTime;
                                  if dtTmp<>0 then
                                   qrXT.FieldByName(aDestField).AsString :=
                                   FormatDateTime('yyyymmdd', dtTmp);
                                  end
                                else begin
                        //此字段的值是字符串或整型，可以直接赋值
                               
                        qrXT.FieldByName(aDestField).asstring :=
                            qrNetit.FieldByName(aSrcField).asstring;
                    end;
                end; //end for j:=0...
                //如果此条记录对应的目标表的关键字段为空，就跳过此条记录，以免
                //产生主键重复异常
                if qrXT.FieldByName(sXT_KeyField).IsNull then
                    qrXT.Cancel
                else
                    qrXT.Post;
                qrNetit.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;
        Button3.Enabled := True;
        qrXT.RequestLive := False;
        DB_XT.Close;
        DB_Netit.Close;
    end;
end;

end.