unit1.pas

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

   if Flistdate1.Count=0 then
   begin
   ok:=true;
   end else
   begin
   ok:=ReadTree(flist.Strings[i]);
   //Flistdate.Add(trim(flist.Strings[i]));
   //ListBox1.Items.Add(Flist1.Strings[i]+' '+flist.Strings[i]);

  end;
  if ok then
   begin
   Flistdate1.Add(trim(flist.Strings[i]));
   //Edit1.Text:=inttostr(Flistdate.Count);
   //Edit2.Text:=inttostr(ListBox1.Items.count);

   ListBox1.Items.Add(Flist1.Strings[i]+' ' +flist.Strings[i] );
   end;
  end;

   end;

     //str:=flist.Strings[i];
 //    snode:=Treeview1.items.addchild(tnode,str);
 //    ReadTree(snode,s);
   end;


 //flist.free;
 //flist1.free;

//ListBox1.Items.Add(Flist1.Strings[TreeView1.Selected.Item]);


procedure TForm1.ListBox1DblClick(Sender: TObject);
//var
//i:integer;
begin
//for i:= 0 to Flist1.Count-1 do
//   begin
//if ListBox1.Items.Text=Flist1.Strings[i] then
//   begin
  //Edit2.Text:=inttostr(ListBox1.ItemIndex)+'::'+inttostr(ListBox1.Items.count);

  //Edit1.Text:=inttostr(ListBox1.ItemIndex+1)+'::'+inttostr(Flistdate.count);
  //Edit1.Text:= Flistdate.Strings[ListBox1.ItemIndex];
  Flistdate1.Delete(ListBox1.ItemIndex);
  ListBox1.Items.Delete(ListBox1.ItemIndex);


  //Memo1.Lines.Add(Flistdate.Strings[ListBox1.ItemIndex]);
//   end;
//   end;

end;


procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
 //Edit1.Text:=Column.FieldName;//字段名称
 //Edit2.Text:=Column.Title.Caption;//显示名称

end;

procedure TForm1.DBGrid1DblClick(Sender: TObject);

var
//i:integer;
sProductID:String;
ok:Boolean;
begin
sProductID:=Query1.fieldbyname('PersonID').asstring;
   ok:=false;
if Flistdate1.Count=0 then
   begin
 ok:=true;
 //Edit1.Text:=inttostr(Flistdate1.Count);

   end else
      begin
   ok:=ReadDbGrid(sProductID);
      end;

  if ok then
  begin
  ListBox2.Items.Add('['+Treeview2.Selected.Text+']  {'+sProductID+'}  '+Query1.fieldbyname('A0101').asstring+'  --'+Query1.fieldbyname('A0217').asstring);
  Flistdate1.Add(sProductID);
  end;

end;

procedure TForm1.PageControl1Change(Sender: TObject);
begin

Label8.Visible:=false;

//Flistdate.Clear;
Flistdate1.Clear;
ListBox1.Clear;
ListBox2.Clear;
Edit1.Text:=inttostr(PageControl1.TabIndex);

if TabSheet4.Visible then
begin
ADOConnection3.Connected:=false;
ADOConnection4.Connected:=true;
ADOTable2.Active:=false;
ADOTable3.Active:=true;
end;
if TabSheet3.Visible then
begin
Memo1.Lines.Clear;
ADOConnection3.Connected:=true;
ADOConnection4.Connected:=false;
ADOTable2.Active:=true;
ADOTable3.Active:=false;
end;

//if TabSheet2.Visible then
//edit1.Text:='ko';
end;

procedure TForm1.ListBox2DblClick(Sender: TObject);
begin
  //Edit1.Text:= Flistdate1.Strings[ListBox2.ItemIndex];
  Flistdate1.Delete(ListBox2.ItemIndex);
  ListBox2.Items.Delete(ListBox2.ItemIndex);
end;

procedure TForm1.TreeView2Click(Sender: TObject);
var strsql:string;
    i:integer;
begin
for i:= 0 to flist.Count-1 do
   begin
  if Treeview2.Selected.Text=flist1.Strings[i] then
   begin

strsql:='select A01.PersonID as PersonID,max(A01.A0117) as A0117,max(A01.A0101) as A0101,max(case A01.A0104 WHEN '''+'1'+''' then '''+'男'+''' else '''+'女'+''' end) as A0104 ,max(A02.A0217) AS A0217 FROM A02 INNER JOIN A01 ON A02.PersonID = A01.PersonID WHERE A01.B0001='''+flist.Strings[i]+''' GROUP BY A01.PersonID';
Query1.SQL.Clear;
Query1.SQL.Add(strsql);
Query1.Open;
//DBGrid1.Action:=True;
DBGrid1.Columns[0].FieldName:='PersonID';
DBGrid1.Columns[1].FieldName:='A0101';
DBGrid1.Columns[2].FieldName:='A0104';
DBGrid1.Columns[3].FieldName:='A0217';
DBGrid1.Columns[4].FieldName:='A0117';
end;
end;
end;


 procedure TForm1.instGrid(Fvalue: String);
 var
 i,p,coun:integer;
 stwhere:string;
 strsql1,strsql2,instsql,instsql1:string;
 begin
 try
   strsql1:='SELECT A01.PersonID as BH,max(A01.A0101) as xm,max(A01.A0104) as xb,'+
 'max(A01.A0107) as cs,max(A01.A0111) as jg,max(A01.A0111A) as jc ,max(A01.A0114) as cd'+
 ',max(A01.A0117) as mz,'+'max(''健康'')'+' as jk,max(A01.A0134) as gs,max(A01.A0141) as dp ,'+
 'max(A01.A0144) as ds,max(A01.Z0221) as dy,max(A01.A01J01) as js,'+
 'max(A01.A0157A) as dw,max(A06.A0601) as zc,max(A06.A0601A) as zm,max(A06.A0604) as zr,'+
 'max((case WHEN A01.A0108B is null then A01.A0109B else A01.A0108B end)) AS wh'+
 ' ,max(A08.A0804) as rx,max(A08.A0807) as bs,max(A08.A0814) as bx,'+
 'max(A08.A0824) as zh,'+'max(''结转'')'+' as hx,'+'max(''20061001'')'+' as hd FROM A01 INNER JOIN A06 ON A01.PersonID = A06.PersonID INNER JOIN '+
 ' A08 ON A01.PersonID = A08.PersonID and A01.PClassID=''00001''';
 //Edit1.Text:=strsql1;
 ADOQuery1.SQL.Clear;
 ADOQuery1.SQL.Add('delete from sbzk1');
 ADOQuery1.ExecSQL;
 coun:=0;
 for i:=0 to Flistdate1.Count-1 do
 begin
 strsql2:='';
 stwhere:='';
 stwhere:=' where A01.'+Fvalue+'=';
 stwhere:=stwhere+''''+Flistdate1.Strings[i]+''' group by A01.PersonID';
 strsql2:=strsql1+stwhere;
 Query1.SQL.Clear;
 Query1.SQL.Add(strsql2);
 Query1.Open;
 //edit1.Text:=strsql2;

 if Query1.Eof then
 begin
 strsql2:='';
 Query1.Close;
 strsql2:='SELECT A01.PersonID as BH,max(A01.A0101) as xm,max(A01.A0104) as xb,'+
 'max(A01.A0107) as cs,max(A01.A0111) as jg,max(A01.A0111A) as jc ,max(A01.A0114) as cd'+
 ',max(A01.A0117) as mz,'+'max(''健康'')'+' as jk,max(A01.A0134) as gs,max(A01.A0141) as dp ,'+
 'max(A01.A0144) as ds,max(A01.Z0221) as dy,max(A01.A01J01) as js,'+
 'max(A01.A0157A) as dw,max(A06.A0601) as zc,max(A06.A0601A) as zm,max(A06.A0604) as zr,'+
 'max((case WHEN A01.A0108B is null then A01.A0109B else A01.A0108B end)) AS wh'+
 ' ,max(A08.A0804) as rx,max(A08.A0807) as bs,max(A08.A0814) as bx,'+
 'max(A08.A0824) as zh,'+'max(''结转'')'+' as hx,'+'max(''20061001'')'+' as hd FROM A01 INNER JOIN A06 ON A01.PersonID = A06.PersonID INNER JOIN '+
 ' A08 ON A01.PersonID = A08.PersonID and A01.PClassID=''00001'' group by A01.PersonID';
   Query1.SQL.Clear;
 Query1.SQL.Add(strsql2);
 Query1.Open;

 end else
 begin
 while not Query1.Eof do
begin
 Query2.SQL.Clear;
 Query2.SQL.Add('select CodeItemName from CodeItems where CodeID='''+'AI'+''' and CodeItemID='''+Query1.fieldbyname('wh').asstring+'''');
 Query2.Open;
 //edit1.Text:=Query1.fieldbyname('BH').asstring;
//p:=strtoint(Query1.fieldbyname('BH').asstring);
ADOQuery1.SQL.Clear;
instsql:='insert into sbzk1 (bh,xm,xb,cs,jg,jc,cd,mz,jk,gs,dp,ds,db,dy,js,dw,zc,zm,zr,wh,xc,rx,bs,bx,zh,zy,xw,xd,xr,gd,zd,qd,ed,es,by,bz,bm,hx,hd)';
ADOQuery1.SQL.Add(instsql);
ADOQuery1.SQL.Add('values(:bh,:xm,:xb,:cs,:jg,:jc,:cd,:mz,:jk,:gs,:dp,:ds,:db,:dy,:js,:dw,:zc,:zm,:zr,:wh,:xc,:rx,:bs,:bx,:zh,:zy,:xw,:xd,:xr,:gd,:zd,:qd,:ed,:es,:by,:bz,:bm,:hx,:hd)');
ADOQuery1.Parameters.ParamByName('bh').Value:=strtoint(Query1.fieldbyname('BH').asstring);//strtofloat(Query1.fieldbyname('BH').asstring);
ADOQuery1.Parameters.ParamByName('xm').value:=Query1.fieldbyname('xm').asstring;
ADOQuery1.Parameters.ParamByName('xb').value:=Query1.fieldbyname('xb').asstring;
ADOQuery1.Parameters.ParamByName('cs').value:=delstring(Query1.fieldbyname('cs').asstring);
ADOQuery1.Parameters.ParamByName('jg').value:=Query1.fieldbyname('jg').asstring;
ADOQuery1.Parameters.ParamByName('jc').value:=Query1.fieldbyname('jc').asstring;
ADOQuery1.Parameters.ParamByName('cd').value:=Query1.fieldbyname('cd').asstring;
ADOQuery1.Parameters.ParamByName('mz').value:=Query1.fieldbyname('mz').asstring;
ADOQuery1.Parameters.ParamByName('jk').value:='健康';
ADOQuery1.Parameters.ParamByName('gs').value:=delstring(Query1.fieldbyname('gs').asstring);
ADOQuery1.Parameters.ParamByName('dp').value:=Query1.fieldbyname('dp').asstring;
ADOQuery1.Parameters.ParamByName('ds').value:=delstring(Query1.fieldbyname('ds').asstring);
ADOQuery1.Parameters.ParamByName('db').value:=''; //64564564565654565
ADOQuery1.Parameters.ParamByName('dy').value:=Query1.fieldbyname('dy').asstring;
ADOQuery1.Parameters.ParamByName('js').value:=delstring(Query1.fieldbyname('js').asstring);
ADOQuery1.Parameters.ParamByName('dw').value:=Query1.fieldbyname('dw').asstring;
ADOQuery1.Parameters.ParamByName('zc').value:=Query1.fieldbyname('zc').asstring;
ADOQuery1.Parameters.ParamByName('zm').value:=Query1.fieldbyname('zm').asstring;
ADOQuery1.Parameters.ParamByName('zr').value:=Query1.fieldbyname('zr').asstring;
ADOQuery1.Parameters.ParamByName('wh').value:=Query1.fieldbyname('wh').asstring;
ADOQuery1.Parameters.ParamByName('xc').value:=Query2.fieldbyname('CodeItemName').asstring;
ADOQuery1.Parameters.ParamByName('rx').value:=delstring(Query1.fieldbyname('rx').asstring);
ADOQuery1.Parameters.ParamByName('bs').value:=delstring(Query1.fieldbyname('bs').asstring);
ADOQuery1.Parameters.ParamByName('bx').value:=Query1.fieldbyname('bx').asstring;
ADOQuery1.Parameters.ParamByName('zh').value:=Query1.fieldbyname('zh').asstring;
ADOQuery1.Parameters.ParamByName('zy').value:='';
ADOQuery1.Parameters.ParamByName('xw').value:='';
ADOQuery1.Parameters.ParamByName('xd').value:='';
ADOQuery1.Parameters.ParamByName('xr').value:='';
ADOQuery1.Parameters.ParamByName('gd').value:='';
ADOQuery1.Parameters.ParamByName('zd').value:='';
ADOQuery1.Parameters.ParamByName('qd').value:='';
ADOQuery1.Parameters.ParamByName('ed').value:='';
ADOQuery1.Parameters.ParamByName('es').value:='';
ADOQuery1.Parameters.ParamByName('by').value:='';
ADOQuery1.Parameters.ParamByName('bz').value:='';
ADOQuery1.Parameters.ParamByName('bm').value:='';
ADOQuery1.Parameters.ParamByName('hx').value:='结转';
ADOQuery1.Parameters.ParamByName('hd').value:='20061001';
ADOQuery1.ExecSQL;
inc(coun);
ListBox1.Items.Add('第'+inttostr(coun)+'记录： | 编号：' + Query1.fieldbyname('BH').asstring + ' | 姓名:'+Query1.fieldbyname('xm').asstring);
ListBox2.Items.Add('第'+inttostr(coun)+'记录： | 编号：' + Query1.fieldbyname('BH').asstring + ' | 姓名:'+Query1.fieldbyname('xm').asstring);
Query1.Next;
  end;
  end;
  end;
 application.MessageBox('基本情况表操作成功！','提示信息',mb_ok);
except
    raise Exception.Create('基本情况表操作失败！');
 end;
 end;


 procedure TForm1.instsbjt1(Fvalue: String);
 var
 i:integer;
 stwhere:string;
 strsql1,strsql2,instsql,instsql1:string;
 begin
 try
   strsql1:='SELECT A01.PersonID AS bh, A36.A3601 AS xm, A36.A3604B AS gx, A36.A3604A AS gm,'+
      'A36.A3607 AS cs, A36.A3611A AS dd, A36.A3621 AS mz, A36.A3624 AS wh,'+
      'A36.A3627 AS dp FROM A36 INNER JOIN A01 ON A36.PersonID = A01.PersonID and A01.PClassID=''00001''';
 instsql:='insert into sbjt1 (bh,xm,gx,gm,cs,dw,dd,zw,zj,zq,mz,wh,dp,sf,xz,bz,hx,hd)';
 ADOQuery1.SQL.Clear;
 ADOQuery1.SQL.Add('delete from sbjt1');
 ADOQuery1.ExecSQL;
 for i:=0 to Flistdate1.Count-1 do
 begin
 strsql2:='';
 stwhere:='';
 stwhere:=' where A01.'+Fvalue+'=';
 stwhere:=stwhere+''''+Flistdate1.Strings[i]+''' ORDER BY A36.PersonID,A36.Disporder';
 strsql2:=strsql1+stwhere;
 Query1.SQL.Clear;
 Query1.SQL.Add(strsql2);
 Query1.Open;
 while not Query1.Eof do
 begin

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(instsql);
ADOQuery1.SQL.Add('values(:bh,:xm,:gx,:gm,:cs,:dw,:dd,:zw,:zj,:zq,:mz,:wh,:dp,:sf,:xz,:bz,:hx,:hd)');
ADOQuery1.Parameters.ParamByName('bh').Value:=strtofloat(Query1.fieldbyname('BH').asstring);
ADOQuery1.Parameters.ParamByName('xm').value:=Query1.fieldbyname('xm').asstring;
ADOQuery1.Parameters.ParamByName('gx').value:=Query1.fieldbyname('gx').asstring;
ADOQuery1.Parameters.ParamByName('gm').value:=Query1.fieldbyname('gm').asstring;
ADOQuery1.Parameters.ParamByName('cs').value:=delstring(Query1.fieldbyname('cs').asstring);
ADOQuery1.Parameters.ParamByName('dw').value:='';
ADOQuery1.Parameters.ParamByName('dd').value:=Query1.fieldbyname('dd').asstring;
ADOQuery1.Parameters.ParamByName('zw').value:='';
ADOQuery1.Parameters.ParamByName('zj').value:='';
ADOQuery1.Parameters.ParamByName('zq').value:='';
ADOQuery1.Parameters.ParamByName('mz').value:=Query1.fieldbyname('mz').asstring;
ADOQuery1.Parameters.ParamByName('wh').value:=Query1.fieldbyname('wh').asstring;
ADOQuery1.Parameters.ParamByName('dp').value:=Query1.fieldbyname('dp').asstring;
ADOQuery1.Parameters.ParamByName('sf').value:='';
ADOQuery1.Parameters.ParamByName('xz').value:='';
ADOQuery1.Parameters.ParamByName('bz').value:='';
ADOQuery1.Parameters.ParamByName('hx').value:='结转';
ADOQuery1.Parameters.ParamByName('hd').value:='20061001';
ADOQuery1.ExecSQL;
Query1.Next;
end;
  end;
 application.MessageBox('家庭成员表操作成功！','提示信息',mb_ok);
except
    raise Exception.Create('家庭成员表操作失败！');
 end;
 end;

procedure TForm1.instsbzl1(Fvalue: String);
 var
 i:integer;
 stwhere:string;
 strsql1,strsql2,instsql,instsql1:string;
 begin
 try
   strsql1:='SELECT A01.PersonID AS bh, A16.A1601 AS qs, A16.A1604 AS js, A16.A1607 AS sz,'+
'A16.A1614 AS sf, A16.A1617 AS jb, '''+''' AS lb FROM A16 INNER JOIN A01 ON A16.PersonID = A01.PersonID and A01.PClassID=''00001''';
 instsql:='insert into sbzl1 (bh,qs,js,sz,sf,jb,lb,hx,hd)';
 ADOQuery1.SQL.Clear;
 ADOQuery1.SQL.Add('delete from sbzl1');
 ADOQuery1.ExecSQL;
 for i:=0 to Flistdate1.Count-1 do
 begin
 strsql2:='';
 stwhere:='';
 stwhere:=' where A01.'+Fvalue+'=';
 stwhere:=stwhere+''''+Flistdate1.Strings[i]+''' ORDER BY A16.PersonID, A16.A1601';
 strsql2:=strsql1+stwhere;
 //edit1.Text:=strsql2;
 Query1.SQL.Clear;
 Query1.SQL.Add(strsql2);
 Query1.Open;
 while not Query1.Eof do
 begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(instsql);
ADOQuery1.SQL.Add('values(:bh,:qs,:js,:sz,:sf,:jb,:lb,:hx,:hd)');
ADOQuery1.Parameters.ParamByName('bh').Value:=strtofloat(Query1.fieldbyname('BH').asstring);
ADOQuery1.Parameters.ParamByName('qs').value:=delstring(Query1.fieldbyname('qs').asstring);
ADOQuery1.Parameters.ParamByName('js').value:=delstring(Query1.fieldbyname('js').asstring);
ADOQuery1.Parameters.ParamByName('sz').value:=Query1.fieldbyname('sz').asstring;
ADOQuery1.Parameters.ParamByName('sf').value:=Query1.fieldbyname('sf').asstring;
ADOQuery1.Parameters.ParamByName('jb').value:=Query1.fieldbyname('jb').asstring;
ADOQuery1.Parameters.ParamByName('lb').value:=Query1.fieldbyname('lb').asstring;
ADOQuery1.Parameters.ParamByName('hx').value:='结转';
ADOQuery1.Parameters.ParamByName('hd').value:='20061001';
ADOQuery1.ExecSQL;
Query1.Next;
end;
  end;
 application.MessageBox('履历表操作成功！','提示信息',mb_ok);
except
    raise Exception.Create('履历表操作失败！');
 end;
 end;


procedure TForm1.instsbrz1(Fvalue: String);
  var
 i:integer;
 stwhere:string;
 strsql1,strsql2,instsql,instsql1:string;
 begin
 try
   strsql1:='SELECT distinct A02.PersonID AS bh, OrganizationToDwk.[no] AS rd, OrganizationToDwk.dz AS rb, A02.A0215 AS rz,'+
  'A02.A0217 AS zm, A01.Z0219 AS j2, A02.A0221 AS j1, '''+''' AS zc, A02.A0255 AS xh, '''+''' AS zg, '''+''' AS zl,'+
  ''''+''' AS cz, '''+''' AS ts, '''+''' AS th, A02.A0243 AS rs, '''+''' AS rh, '''+''' AS ms,'+
  ''''+''' AS g1, '''+''' AS g2, '''+''' AS g3, '''+''' AS g4, '''+''' AS px'+
  ' FROM A02 INNER JOIN A01 ON A02.PersonID = A01.PersonID INNER JOIN '+
  'OrganizationToDwk ON A02.A0201B = OrganizationToDwk.CodeItemID and A01.PClassID=''00001''';
 //edit1.Text:=strsql1;
 instsql:='insert into sbrz1 (bh,rd,rb,rz,zm,j2,j1,zc,xh,zg,zl,cz,ts,th,rs,rh,ms,dl,ds,dj,dx,hy,g1,g2,g3,g4,px,hx,hd)';
 ADOQuery1.SQL.Clear;
 ADOQuery1.SQL.Add('delete from sbrz1');
 ADOQuery1.ExecSQL;
 for i:=0 to Flistdate1.Count-1 do
 begin
 strsql2:='';
 stwhere:='';
 stwhere:=' where A01.'+Fvalue+'=';
 stwhere:=stwhere+''''+Flistdate1.Strings[i]+''' ORDER BY A02.PersonID';
 strsql2:=strsql1+stwhere;
 Query1.SQL.Clear;
 Query1.SQL.Add(strsql2);
 Query1.Open;

 while not Query1.Eof do
 begin