unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, Grids, DBGrids;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    T: TADOQuery;
    DataSource1: TDataSource;
    Button1: TButton;
    Button2: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    function Q2S(sStr: widestring): String;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
//
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  sStr, s1, s2: widestring;
  I: Integer;
begin
  //ADOQuery1.UpdateBatch;
  with ADOQuery1 do
  begin
    Close;
    Open;
      First;
      while NOT EOF do
      begin
        for I := 0 to RecordCount - 1 do
        begin
          sStr := TRIM(Fields[2].AsString);
          s1 := TRIM(Fields[3].AsString);
          s2 := TRIM(Fields[7].AsString);
          //ShowMessage(Q2S(sStr) + ' <-> ' + sStr);
          Edit;
          Fields[2].Value := Q2S(sStr);
          Fields[3].Value := Q2S(s1);
          Fields[7].Value := Q2S(s2);
          Next;
        end;//for I
      end;//while
      UpdateBatch;
  end;//with
end;

function TForm1.Q2S(sStr: widestring): String;
var
  i : integer;
  s: String;
  w1, w2: integer;
  a: integer;
begin
  Result := '';
  for i := 1 to length(sStr) do
  begin
    s := sStr;
    w1 := ord(s[1]);
    w2 := ord(s[2]);

    // Enhanced by Gear1023
    // DateTimed: 20040605
    if ByteType(s, 1) = mbsingleByte then
    begin
      a := w1 xor $77;
      Result := Result + chr(a);
    end
    else
    begin
      a := w2 xor $77;
      Result := Result + chr(w1)+chr(a);
    end;
  end;
end;

end.

/*
名称pAll_Converts_Categories_Company
输入:
输出:
调用:
说明:转换原工商企业数据库到新的数据格式

作者ili J.F. Senders
邮件ili@wellknow.net
网站:http://www.wellknow.net
更新:20040621
*/
ALTER PROCEDURE spAll_Converts_Categories_Company
AS

 SET NOCOUNT ON

 DECLARE
  @NAME nvarchar(50),  --企业名称
  @ZIP nvarchar(255),  --邮政编码
  @ADDRESS nvarchar(255), --详细地址
  @TEL nvarchar(255),  --电话号码
  @FAX nvarchar(255),  --传真号码
  @HOMEPAGE nvarchar(255), --公司网站
  @EMAIL nvarchar(255),  --电子邮件
  @MAIN nvarchar(1000),  --经营范围
  @AREA nvarchar(255),  --所在镇区
  @ENO nvarchar(50),
  @CODE nvarchar(50),
  @KIND nvarchar(50),  --行业小类
  @CATEGORY nvarchar(50), --行业中类
  @BCATEGORY nvarchar(50), --行业大类
  @ADV nvarchar(50),  
  @MARK nvarchar(50),
  @LOGO nvarchar(50)

 DECLARE
  @AreaID INT,    --所属地区
  @CategoriesID INT   --所属行业
  
 DECLARE Converts CURSOR FOR
  SELECT * FROM xiao***
   WHERE ([NAME] <> '')
   ORDER BY [Name]
 OPEN Converts
 /*打开游标后分别保存各个字段值*/
 FETCH NEXT FROM Converts INTO @NAME, @ZIP, @ADDRESS, @TEL, @FAX, @HOMEPAGE, @EMAIL, @MAIN, @AREA, @ENO,@CODE, @KIND, @CATEGORY, @BCATEGORY, @ADV, @MARK, @LOGO
 WHILE @@FETCH_STATUS = 0
 BEGIN
  /*
  获取 所在区域
  */
  IF NOT EXISTS(SELECT TOP 1 * FROM all_Categories WHERE ([Name] = @AREA))
  BEGIN
   INSERT INTO all_Categories([Name], ParentID) VALUES(@AREA, 26)
   PRINT '新加 地区 记录：' + @BCATEGORY
  END
  SELECT @AreaID = ID FROM all_Categories WHERE ([Name] = @AREA)

  /*
  获取 所在企业类别
   大类
  */
  IF NOT EXISTS(SELECT TOP 1 * FROM all_Categories WHERE ([Name] = @BCATEGORY))
  BEGIN
   INSERT INTO all_Categories([Name], ParentID) VALUES(@BCATEGORY, 52)
   PRINT '新加 企业类别--大 记录：' + @BCATEGORY
  END
  SELECT @CategoriesID = ID FROM all_Categories WHERE ([Name] = @BCATEGORY)
  /*
  获取 所在企业类别
   中类
  */
  IF NOT EXISTS(SELECT TOP 1 * FROM all_Categories WHERE ([Name] = @CATEGORY))
  BEGIN
   INSERT INTO all_Categories([Name], ParentID) VALUES(@CATEGORY, @CategoriesID)
   PRINT '新加 企业类别--中 记录：' + @BCATEGORY
  END
  SELECT @CategoriesID = ID FROM all_Categories WHERE ([Name] = @CATEGORY)
  /*
  获取 所在企业类别
   小类
  */
  IF NOT EXISTS(SELECT TOP 1 * FROM all_Categories WHERE ([Name] = @KIND))
  BEGIN
   INSERT INTO all_Categories([Name], ParentID) VALUES(@KIND, @CategoriesID)
   PRINT '新加 企业类别--小 记录：' + @KIND
  END
  SELECT @CategoriesID = ID FROM all_Categories WHERE ([Name] = @KIND)
    
  /*
  将源表记录插入目标表中
  */
  IF NOT EXISTS(SELECT TOP 1 * FROM TABLE1 WHERE ([Name] = @NAME) AND ([ParentID] = @CategoriesID) AND ([pID]=@AreaID) )
  BEGIN
   INSERT INTO TABLE1([Name], [ParentID], [pID], [Source], [URLSource], [Tel], [Fax], [Address], [EMail])
      VALUES(@NAME, @CategoriesID, @AreaID, @MAIN, @HOMEPAGE, @TEL, @FAX, @ADDRESS, @EMAIL)
  END
  ELSE
  PRINT @NAME + ' 已经存在'   
    
  FETCH NEXT FROM Converts INTO @NAME, @ZIP, @ADDRESS, @TEL, @FAX, @HOMEPAGE, @EMAIL, @MAIN, @AREA, @ENO,@CODE, @KIND, @CATEGORY, @BCATEGORY, @ADV, @MARK, @LOGO
 END/*WHILE @@FETCH_STATUS = 0*/
 CLOSE Converts
 DEALLOCATE Converts
  
 SET NOCOUNT OFF