frm_sjjh.pas

用于家庭养殖的财务进出

unit frm_sjjh;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, FileCtrl, ExtCtrls, CheckLst,db,ADOdb,
  Grids, DBGrids;

type
  Tfrm_jh = class(TForm)
    grp1: TGroupBox;
    GroupBox2: TGroupBox;
    DriveComboBox1: TDriveComboBox;
    DirectoryListBox1: TDirectoryListBox;
    GroupBox1: TGroupBox;
    ListBox1: TListBox;
    CheckListBox1: TCheckListBox;
    b_dc: TButton;
    b_cexit: TButton;
    OpenDialog1: TOpenDialog;
    grp2: TGroupBox;
    lbl1: TLabel;
    dlgOpen1: TOpenDialog;
    dlgOpen2: TOpenDialog;
    edt1: TEdit;
    b_browse: TButton;
    b_dr: TButton;
    dlgOpen3: TOpenDialog;
    b_exit: TButton;
    procedure b_cexitClick(Sender: TObject);//系统退出
    procedure FormCreate(Sender: TObject);  //界面，listbox1初始化
    procedure b_dcClick(Sender: TObject);  //数据导出
    procedure Button3Click(Sender: TObject); //
    procedure b_exitClick(Sender: TObject);  //系统退出
    procedure b_drClick(Sender: TObject);   // 数据导入
    procedure b_browseClick(Sender: TObject);//选择导入数据表的路径信息
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_jh: Tfrm_jh;

implementation
  uses data_module,unit_global,frmsjkdr, Unit3; //dmserver,globalvarunit,frmsjkdr;//
{$R *.dfm}

procedure Tfrm_jh.b_cexitClick(Sender: TObject);
begin
  close;
end;

procedure Tfrm_jh.FormCreate(Sender: TObject);
var
  filepath:string;
  i:integer;
begin
  //dm.ADOConnection1.GetTableNames(checklistbox1.Items,false);
  dm.ADOConnection1.GetTableNames(listbox1.Items,false);
  //checklistbox1.ItemIndex:=0;
  //checklistbox1.DeleteSelected;
  listbox1.ItemIndex:=0;
  listbox1.DeleteSelected;
  for i:=0 to listbox1.Items.Count-1 do
  begin
    if listbox1.Items[i]='A01' then
      checklistbox1.AddItem('基本信息表',checklistbox1);
    if listbox1.Items[i]='A02' then
      checklistbox1.AddItem('职务',checklistbox1);
    if listbox1.Items[i]='A03' then
      checklistbox1.AddItem('学历',checklistbox1);
    if listbox1.Items[i]='A04' then
      checklistbox1.AddItem('家庭',checklistbox1);
    if listbox1.Items[i]='A05' then
      checklistbox1.AddItem('简历',checklistbox1);
    if listbox1.Items[i]='A06' then
      checklistbox1.AddItem('重大事项',checklistbox1);
    if listbox1.Items[i]='A07' then
      checklistbox1.AddItem('后备历史',checklistbox1);
    if listbox1.Items[i]='A08' then
      checklistbox1.AddItem('其他信息',checklistbox1);
    //if listbox1.Items[i]='photo' then              //2006-03 zjx
      //checklistbox1.AddItem('照片',checklistbox1);
  end;
  drivecombobox1.Drive:='d';
  filepath:=apppath+'sjbf';
  directorylistbox1.Drive:=drivecombobox1.Drive;
  directorylistbox1.Directory:=filepath;
end;

procedure Tfrm_jh.b_dcClick(Sender: TObject);   //开始导出
var
  table_name:string;
  i,k:integer;
  companycode: string;
  temptable:string;
  sqlstr:string;
  count:integer;
  ifield:array of string;
  fiecount:integer;
  filepath,drivepath:string;
  tableboolean:boolean;
begin
 tableboolean:=false;
 drivepath:=directorylistbox1.Directory;
 if Application.Messagebox(pchar('是否将数据表导入到'+drivepath+'路径下!'),'注意!',MB_OKCANCEL+ mb_iconexclamation)=IDOK then
 begin
 { for i:=0 to checklistbox1.Items.Count-1 do
  begin
    if checklistbox1.Checked[i]=true then
    begin
      if i=0 then
        dctable_name:=checklistbox1.Items[i]
      else
        dctable_name:=dctable_name+','+checklistbox1.Items[i];
    end;
  end;
  if dctable_name='' then
  begin
    application.MessageBox('请选择要导出的表!','注意',mb_ok);
    exit;
  end;   }
//////////判断是否选择了表/////////////////////
  for i:=0 to checklistbox1.Items.Count-1 do
    begin
      if checklistbox1.Checked[i]=true then
      begin
        tableboolean:=true;
      end;
    end;
  if tableboolean=false then
  begin
    application.MessageBox('请选择要导出的表!','注意',mb_ok);
    exit;
  end;
///////////////////////////////////////////
  companycode:= InputBox('Input Box', '请输入公司代码*(例如:A,B,C)', 'Default string');
  ReadCursor;
  if companycode<>'Default string' then  //2006-03 zjx
  begin
    for i:=0 to checklistbox1.Items.Count-1 do
    begin
      if checklistbox1.Checked[i]=true then
      begin
        if checklistbox1.Items[i]='基本信息表' then
          table_name:='A01';
        if checklistbox1.Items[i]='职务' then
          table_name:='a02';
        if checklistbox1.Items[i]='学历' then
          table_name:='a03';
        if checklistbox1.Items[i]='家庭' then
          table_name:='a04';
        if checklistbox1.Items[i]='简历' then
          table_name:='a05';
        if checklistbox1.Items[i]='重大事项' then
          table_name:='a06';
        if checklistbox1.Items[i]='后备历史' then
          table_name:='a07';
        if checklistbox1.Items[i]='其他信息' then
          table_name:='a08';
        with dm.ADOQuery1 do
        begin
          close;
          sql.Clear;
          sql.Add('select * from '+table_name);
          open;
          fiecount:=fieldcount;
          setlength(ifield,FieldCount);
          for k:=0 to fieldcount-1 do
          begin
            ifield[k]:=fieldlist[k].FieldName;
          end;
        end;
        dm.ADOQuery1.First;
        temptable:=companycode+table_name;
        with dm.ADOQuery2 do
        begin
          close;
          sql.Clear;
          sql.Add('select * into '+temptable+' from '+table_name);
          execsql;
        end;
        with dm.ADOQuery2 do
        begin
          close;
          sql.Clear;
          sql.Add('select * from '+temptable);
          open;
          first;
          while not eof do
          begin
            with dm.ADOQuery1 do
            begin
              close;
              sql.Clear;
              sql.Add('update '+temptable+' set prescode='+quotedstr(companycode+dm.ADOQuery2.fieldbyname('prescode').AsString)+'where prescode='+quotedstr(dm.ADOQuery2.fieldbyname('prescode').AsString));
              execsql;
            end;
            next;
          end;
        end;
        dm.ADOQuery2.Close;
        dm.ADOQuery2.Open;
        if  directorylistbox1.ItemIndex=0 then
            //Path:=dirlst1.GetItemPath(dirlst1.ItemIndex)+FileName+'.xls'
          dm.ADOQuery2.SaveToFile(drivepath+checklistbox1.Items[i]+'.xml',pfXML)
        else
          dm.ADOQuery2.SaveToFile(drivepath+'\'+checklistbox1.Items[i]+'.xml',pfXML);
        with dm.ADOQuery1 do
        begin
          close;
          sql.Clear;
          sql.Add('drop table '+temptable);
          execsql;
        end;
       {dm.ADOQuery2.Close;
       dm.ADOQuery2.open;
       dm.ADOQuery2.First;
       showmessage(dm.ADOQuery2.fieldbyname('prescode').AsString);
       with dm.ADOQuery1 do
        begin
          close;
          sql.Clear;
          sql.Add('select prescode from '+temptable);//('update '+temptable+' set prescode='+quotedstr(companycode+fieldbyname('prescode').AsString));
          open;
          first;
          while not eof do
          begin
           // fieldbyname('prescode').Assign();
          end;
        end;  }
       { while dm.ADOQuery1.Eof=false do
        begin
          with dm.ADOQuery2 do
          begin
            close;
            sql.Clear;
            sql.Add('insert into '+temptable+'(');
            for k:=0 to fiecount-1 do
            begin
              sql.Add(ifield[k]);
              if k<>fiecount-1 then
                sql.Add(',');
            end;
            sql.Add(') values(');
            for k:=0 to fiecount-1 do
            begin
              if dm.ADOQuery1.Fields[k].DataType=ftinteger then
                sql.Add(inttostr(dm.ADOQuery1.fieldbyname(ifield[k]).AsInteger));
              if dm.ADOQuery1.Fields[k].DataType=ftdatetime then
                sql.Add(''''+datetostr(dm.ADOQuery1.fieldbyname(ifield[k]).AsDateTime)+'''');
              if dm.ADOQuery1.Fields[k].DataType=ftwidestring then
              begin
                if dm.ADOQuery1.fieldbyname(ifield[k]).AsString<>'' then
                begin
                  if ifield[k]='PRESCODE' then
                    sql.Add(''''+companycode+dm.ADOQuery1.fieldbyname(ifield[k]).AsString+'''')
                  else
                    sql.Add(''''+dm.ADOQuery1.fieldbyname(ifield[k]).AsString+'''');
                end
                else
                  sql.Add('null');
              end;
              if k<>fiecount-1 then
                sql.Add(',');
            end;
            sql.Add(')');
            //showmessage(sql.Text);
            execsql;
          end;
          dm.ADOQuery1.Next;
        end; }
        //dm.ADOConnection1.GetTableNames(listbox1.Items,false);
        //sqlstr:='select * into '+temptable+ ' from '+checklistbox1.Items[i];
        count:=0;
      end else
        count:=count+1;
      end;                                    //2006-03
      {
      if count=checklistbox1.Items.Count then
      begin
        application.MessageBox('请选择要导出的表!','注意',mb_ok);
        exit;
      end;                              //2006-03
      }
      ResumeCursor;
      application.MessageBox('数据导出成功！','信息提示',mb_ok);
    end else
    begin
      ResumeCursor;
      application.MessageBox('数据导出失败！','信息提示',mb_ok);
    end;
  end;
end;

procedure Tfrm_jh.Button3Click(Sender: TObject);
var
  constring:string;
  filepath:string;
begin
   frm_sjkdr.Show;
  {if  opendialog1.Execute  then
  begin
  filepath:=opendialog1.FileName;
  //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\程序\新建数据库\db3.mdb;Persist Security Info=False
  ConString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
             filepath+';Persist Security Info=False';
  if dm.ADOConnection2.Connected then
    dm.ADOConnection2.Connected:=false;
  dm.ADOConnection2.ConnectionString:=constring;
  dm.ADOConnection2.GetTableNames(listbox2.Items,false);
  end
  else
    showmessage('未选中任何数据库');  }
end;

procedure Tfrm_jh.b_exitClick(Sender: TObject);
begin
  close;
end;

procedure Tfrm_jh.b_drClick(Sender: TObject);
var
  filepath:string;
  fiecount,i:integer;
  fieldstr,valuestr:string;
  table_name:string;
  postion:integer;
  table:array[1..9] of string;
  sqlstr:string;
begin
  if edt1.Text='' then
  begin
    application.MessageBox('你还没有选中需要导入数据的数据库!','注意',mb_ok);
    exit;
  end;
  dlgopen3.InitialDir:=apppath+'sjbf';
  dlgopen3.Filter:='XML files(*.xml)|*.xml';
  table[1]:='基本信息表';
  table[2]:='职务';
  table[3]:='学历';
  table[4]:='家庭';
  table[5]:='简历';
  table[6]:='重大事项';
  table[7]:='后备历史';
  table[8]:='其他信息';
  //table[9]:='照片';     //2006-03 zjx
  if dlgopen3.Execute then
  begin
    filepath:=dlgopen3.FileName;
    for i:=1 to 8 do   //2006-03
    begin
      postion:=pos(table[i],dlgopen3.FileName);
      if postion<>0 then
        break;
    end;
    if i=10 then
    begin
      showmessage('当前数据无法导入!');
      exit;
    end;
    //if i=9 then           //2006-03
    //table_name:='photo'
    //else
    table_name:='a0'+inttostr(i);
    dm.ADOQy_dr.LoadFromFile(filepath);
    fiecount:=dm.ADOQy_dr.FieldCount;
    readcursor;
    for  i:=0 to fiecount-1 do
    begin
    fieldstr:=fieldstr+dm.ADOQy_dr.FieldList.Fields[i].FieldName;
    valuestr:=valuestr+':value'+inttostr(i);
      if i<>fiecount-1 then
      begin
        fieldstr:=fieldstr+',';
        valuestr:=valuestr+',';
      end;
    end;
    with dm.ADOQy_dr do
    begin
      first;
      while not eof do
      begin
        //sqlstr:='insert into '+table_name+' ('+fieldstr+') values('+valuestr+') where prescode='+quotedstr(dm.ADOQy_dr.fieldbyname('prescode').AsString);
        with dm.qry1 do
        begin
          close;
          sql.Clear;
          sql.Add('delete from '+table_name+' where prescode='+quotedstr(dm.ADOQy_dr.fieldbyname('prescode').AsString));
          execsql;
        end;
        with dm.qry1 do
        begin
          close;
          sql.Clear;
          sql.Add('insert into '+table_name+' ('+fieldstr+') values('+valuestr+')');// where prescode='+quotedstr(dm.ADOQy_dr.fieldbyname('prescode').AsString));
          for i:=0 to fiecount-1 do
            parameters[i].Value:=dm.ADOQy_dr.FieldList.Fields[i].Value;
          execsql;
        end;
        next;
      end;
    end;
  end
  else
  begin
    resumecursor;
    application.MessageBox('未选择任何需导入的文件','注意',mb_ok);
    exit;
  end;
  resumecursor;
end;


procedure Tfrm_jh.b_browseClick(Sender: TObject);
var
  filepath:string;
  ConString:string;
begin
  dlgopen2.InitialDir:=apppath;
  if dlgopen2.Execute then
  begin
    filepath:=dlgopen2.FileName;
    edt1.Text:=filepath;
    ConString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
                filepath+';Persist Security Info=False';
    if dm.ADOConnection2.Connected then
    dm.ADOConnection2.Connected:=false;
    dm.ADOConnection2.ConnectionString:=constring;
  end;
end;

end.