unit1.pas

新旧人事软件数据结转程序 从sqlserver转至VF数据

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, ComCtrls, XmlConfig, ExtCtrls, ADODB, StdCtrls,
  Grids, DBGrids, DBCtrls;

type
  TForm1 = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Panel1: TPanel;
    TreeView1: TTreeView;
    Panel2: TPanel;
    Query1: TADOQuery;
    ADOConnection1: TADOConnection;
    Query2: TADOQuery;
    Query3: TADOQuery;
    ListBox1: TListBox;
    Panel3: TPanel;
    Panel4: TPanel;
    Button1: TButton;
    CheckBox1: TCheckBox;
    Panel5: TPanel;
    Panel6: TPanel;
    Panel7: TPanel;
    TreeView2: TTreeView;
    Panel8: TPanel;
    DBGrid1: TDBGrid;
    GroupBox1: TGroupBox;
    DataSource1: TDataSource;
    ListBox2: TListBox;
    ADOConnection2: TADOConnection;
    DBGrid2: TDBGrid;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    DataSource2: TDataSource;
    TabSheet3: TTabSheet;
    Panel9: TPanel;
    Label1: TLabel;
    Edit3: TEdit;
    Label2: TLabel;
    Label3: TLabel;
    Edit4: TEdit;
    Panel10: TPanel;
    Panel11: TPanel;
    TreeView3: TTreeView;
    DBGrid3: TDBGrid;
    Edit5: TEdit;
    Edit6: TEdit;
    Label4: TLabel;
    Label5: TLabel;
    GroupBox2: TGroupBox;
    Panel12: TPanel;
    Panel13: TPanel;
    Button2: TButton;
    ADOTable1: TADOTable;
    DataSource3: TDataSource;
    ADOConnection3: TADOConnection;
    DataSource4: TDataSource;
    ADOTable2: TADOTable;
    ADOQuery3: TADOQuery;
    Panel15: TPanel;
    Button3: TButton;
    Query4: TADOQuery;
    ADOQuery4: TADOQuery;
    Edit1: TEdit;
    TabSheet4: TTabSheet;
    ADOConnection4: TADOConnection;
    Panel16: TPanel;
    DBNavigator2: TDBNavigator;
    DBGrid5: TDBGrid;
    DataSource5: TDataSource;
    ADOTable3: TADOTable;
    Memo1: TMemo;
    Button4: TButton;
    Label8: TLabel;
    Label11: TLabel;
    Query5: TADOQuery;
    Query6: TADOQuery;
    procedure FormCreate(Sender: TObject);
    procedure Connect(ServeName,UserName, PassWord, DataSoure: string);
    procedure TreeView1DblClick(Sender: TObject);
    procedure ListBox1DblClick(Sender: TObject);
    procedure SetComCtrlStyle(WinCtrl: TWinControl; Value: Integer; UseStyle: Boolean);
    function ReadTree(Fvalue: String):Boolean;
    procedure CreateTreeView(TreeViewdb: TTreeView;ADOQuery1,ADOQuery2,ADOQuery3:TADOQuery);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure PageControl1Change(Sender: TObject);
    procedure ListBox2DblClick(Sender: TObject);
    function ReadDbGrid(Fvalue: String):Boolean;
    procedure TreeView2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure instGrid(Fvalue: String);
    procedure instsbjt1(Fvalue: String);
    procedure instsbzl1(Fvalue: String);
    procedure instsbrz1(Fvalue: String);
    function delstring(AStr: string): string;
    procedure TreeView3DblClick(Sender: TObject);
    procedure DBGrid3DblClick(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Edit3DblClick(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Label9MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Label10MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Label11MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure DBGrid5MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure Panel4MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure Panel16MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure TabSheet4MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure TreeView1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure ListBox1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure TreeView2MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure DBGrid1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure ListBox2MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure DBGrid2MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure TreeView3MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Edit6MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Edit5MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Edit3MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Edit4MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure DBGrid3MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Memo1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Panel9MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Label1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Button1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure Button4MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure PageControl1MouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
  private
    { Private declarations }
    FXMLObj : TXmlConfig;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  g_ServerName, g_User, g_PSW, g_DataSoure, VFDataSoure: string;
  Flist:TStringList;
  Flist1:TStringList;
  Flistdate:TStringList;
  Flistdate1:TStringList;
  Flistdate2:TStringList;
implementation

{$R *.dfm}

const
TVS_CHECKBOXES = $00000100;

procedure TForm1.CreateTreeView(TreeViewdb: TTreeView;ADOQuery1,ADOQuery2,ADOQuery3:TADOQuery);
var node,nodes,nodess:ttreenode;
//    num:integer;
    str,str1,str2:string;
begin
Flistdate:=TStringList.Create;
Flistdate1:=TStringList.Create;
//node:=nil;
//ReadTree(node,'1');
str:='SELECT * FROM Organization WHERE (LEN(CodeItemID) = 3)';
Flist:=TStringList.Create;
Flist1:=TStringList.Create;
Flistdate2:=TStringList.Create;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(str);
ADOQuery1.Open;
node:=nil;
//nodes:=nil;
while not ADOQuery1.Eof do
 begin

node:=TreeViewdb.Items.Add(node,ADOQuery1.fieldbyname('CodeItemName').asstring);
//node:=TreeView2.Items.Add(node,ADOQuery1.fieldbyname('CodeItemName').asstring);
Flist.Add(ADOQuery1.fieldbyname('CodeItemID').asstring);
Flist1.Add(ADOQuery1.fieldbyname('CodeItemName').asstring);
  node.ImageIndex:=0;
  node.SelectedIndex:=0;
nodes := node;


str1:='SELECT * FROM Organization WHERE (LEN(CodeItemID) = 6) AND Parent='+''''+ADOQuery1.fieldbyname('CodeItemID').asstring+'''';
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add(str1);

  //ADOQuery1.First;
  ADOQuery2.Open;
while not ADOQuery2.Eof do
begin
  node:=TreeViewdb.Items.Addchild(node,ADOQuery2.fieldbyname('CodeItemName').asstring);
  //node:=TreeView2.Items.Addchild(node,ADOQuery2.fieldbyname('CodeItemName').asstring);
  Flist.Add(ADOQuery2.fieldbyname('CodeItemID').asstring);
  Flist1.Add(ADOQuery2.fieldbyname('CodeItemName').asstring);
  node.ImageIndex:=1;
  node.SelectedIndex:=1;
  nodess:= node;
 str2:='SELECT * FROM Organization WHERE (LEN(CodeItemID)>=8) AND Parent='+''''+ADOQuery2.fieldbyname('CodeItemID').asstring+'''';
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add(str2);
ADOQuery3.open;
while not ADOQuery3.Eof do
begin
//TreeView1.Items.AddChild()
node:=TreeViewdb.Items.Addchild(node,ADOQuery3.fieldbyname('CodeItemName').asstring);
//node:=TreeView2.Items.Addchild(node,ADOQuery3.fieldbyname('CodeItemName').asstring);
Flist.Add(ADOQuery3.fieldbyname('CodeItemID').asstring);
Flist1.Add(ADOQuery3.fieldbyname('CodeItemName').asstring);
node.ImageIndex:=2;
node.SelectedIndex:=2;

Query4.SQL.Clear;
Query4.SQL.Add('SELECT * FROM Organization WHERE (LEN(CodeItemID)>=11) AND Parent='+''''+ADOQuery3.fieldbyname('CodeItemID').asstring+'''');
Query4.Open;
while not Query4.Eof do
begin
node:=TreeViewdb.Items.Addchild(node,Query4.fieldbyname('CodeItemName').asstring);
//node:=TreeView2.Items.Addchild(node,ADOQuery3.fieldbyname('CodeItemName').asstring);
Flist.Add(Query4.fieldbyname('CodeItemID').asstring);
Flist1.Add(Query4.fieldbyname('CodeItemName').asstring);
node.ImageIndex:=3;
node.SelectedIndex:=3;
node := nodess;
Query4.Next;
end;
node := nodess;
  ADOQuery3.Next;
end;
node := nodes;
ADOQuery3.Close;
ADOQuery2.Next;
 end;
ADOQuery2.Close;
//
ADOQuery1.Next;

 end;
 ADOQuery1.Close;
end;


procedure TForm1.SetComCtrlStyle(WinCtrl: TWinControl; Value: Integer; UseStyle: Boolean);
var
Style: Integer;
begin
  if WinCtrl.HandleAllocated then
  begin
    Style := GetWindowLong(WinCtrl.Handle, GWL_STYLE);
    if not UseStyle then
    Style := Style and not Value
    else Style := Style or Value;
    SetWindowLong(WinCtrl.Handle, GWL_STYLE, Style);
  end;
end;


function TForm1.ReadTree(Fvalue: String):Boolean;
Var
  i: integer;
  ok:Boolean;
begin
   ok:=true;
     for i:=0 to Flistdate1.Count-1 do
     begin
   if Flistdate1.Strings[i]=Fvalue then
    // begin
     ok :=false;
     //end else
    Result :=ok;
end;
end;


function TForm1.ReadDbGrid(Fvalue: String):Boolean;
Var
  i: integer;
  ok:Boolean;
begin
   ok:=true;
     for i:=0 to Flistdate1.Count-1 do
     begin
   if Flistdate1.Strings[i]=Fvalue then
    // begin
     ok :=false;
     //end else
    Result :=ok;
end;
end;

function TForm1.delstring(AStr: string):string;
begin
while Pos('.',AStr) > 0 do
Delete(AStr, Pos('.',AStr), 1);
Result:=AStr;
end;

procedure TForm1.FormCreate(Sender: TObject);

begin
  FXMLObj := TXmlConfig.create;
    g_ServerName := FXMLObj.GetServerName;
    g_User := FXMLObj.GetUserName;
    g_PSW := FXMLObj.GetPassWord;
    g_DataSoure:=FXMLObj.GetServerURL;
    VFDataSoure:=FXMLObj.GetPort;
Connect(g_ServerName,g_User,g_PSW,g_DataSoure);
CreateTreeView(TreeView1,Query1,Query2,Query3);
CreateTreeView(TreeView2,Query1,Query2,Query3);
CreateTreeView(TreeView3,Query1,Query2,Query3);

Label8.Visible:=false;

{ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('select * from DWK ORDER BY NO');
ADOQuery3.Open;}
ADOTable2.Active:=TRUE;
{for i:= 0 to flist.Count-1 do
    begin
      s:=flist1.Strings[i];
      s1:=flist.Strings[i];
      snode:=Treeview1.items.addchild(tnode,s1);
      ReadTree(snode,s);
    end;
  flist.free;
  flist1.free;
}
//SetComCtrlStyle(TreeView1, TVS_CHECKBOXES, True);
//edit1.Text:=str2;
//edit1.Text:='Provider=SQLOLEDB.1;Persist Security Info=False;User ID='+g_User+';Password='+g_PSW+';Initial Catalog='+g_ServerName+';Data Source='+g_DataSoure;
end;


procedure TForm1.Connect(ServeName,UserName, PassWord, DataSoure: string);
begin
  try
    with ADOConnection1 do
    begin
      ADOConnection1.ConnectionString:= 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID='+UserName+';Password='+PassWord+';Initial Catalog='+ServeName+';Data Source='+DataSoure;
      Provider := 'SQLOLEDB.1';
      LoginPrompt := false;
      Connected := True;
    end;

  with ADOConnection2 do
    begin
   ADOConnection2.ConnectionString:= 'Provider=VFPOLEDB.1;Data Source='+ExtractFilePath(Application.ExeName)+'wrssb;Password='''';Collating Sequence=MACHINE';
      Provider := '2VFPOLEDB.1';
      LoginPrompt := false;
      Connected := True;
    end;

    with ADOConnection3 do
    begin
   ADOConnection3.ConnectionString:= 'Provider=VFPOLEDB.1;Data Source='+ExtractFilePath(Application.ExeName)+'DATA;Password='''';Collating Sequence=MACHINE';
      Provider := '2VFPOLEDB.1';
      LoginPrompt := false;
      Connected := True;
    end;

     with ADOConnection4 do
    begin
   ADOConnection4.ConnectionString:= 'Provider=VFPOLEDB.1;Data Source='+ExtractFilePath(Application.ExeName)+'DATA;Password='''';Collating Sequence=MACHINE';
      Provider := '2VFPOLEDB.1';
      LoginPrompt := false;
      Connected := True;
    end;
   except
    raise Exception.Create('数据库连接失败！');
  end;
end;
procedure TForm1.TreeView1DblClick(Sender: TObject);
var
i,j: integer;
ok:Boolean;
begin
   ok:=false;
  for i:= 0 to flist.Count-1 do
   begin
  if Treeview1.Selected.Text=flist1.Strings[i] then
   begin