inoutdata.pas

1. 纯粹的合同管理,不涉及其它业务,独立成系统2. 简单明了,流程清析3. 合同条款可预定制4. 丰富强大的打印预览系统5. 实用的导入导出功能,可与excel交互使用6. 时刻追踪合同执行情况,包

unit InOutData;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, ComCtrls, GradeColorImage, Buttons;

type
  TInOutDataForm = class(TForm)
    GCImg1: TGCImg;
    psb: TProgressBar;
    Label1: TLabel;
    Label2: TLabel;
    adoCon: TADOConnection;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Label3: TLabel;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
    OpType:integer;
    DatafilePath:string;
    startDate,endDate:TDateTime;
  end;

var
  InOutDataForm: TInOutDataForm;

implementation

uses data;

{$R *.dfm}

procedure TInOutDataForm.BitBtn1Click(Sender: TObject);
var
  adoDelTable:TADOCommand;
  adoOnlineNote:TADOQuery;
  adoOutPutTable:TADOQuery;
  adoFrom:TADOQuery;
  adoTo:TADOQuery;
begin
try
  if adoCon.Connected then adoCon.Connected :=false;
  case OpType of
  0:  begin
        adoCon.ConnectionString :='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='
          +DatafilePath+';Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";'
          +'Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="982702";Jet OLEDB:Engine Type=5;'
          +'Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;'
          +'Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";'
          +'Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;'
          +'Jet OLEDB:Don'+''''+'t Copy Locale on Compact=False;'
          +'Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False';
        adoCon.Connected :=true;
        if MessageBox(handle,'如果旧数据库中的记录在新数据库中已存在，'+#13
          +'系统将略过该记录，是否进行？','导入确认',MB_OKCANCEL or MB_ICONWARNING)=IDOK then
        begin
          BitBtn1.Enabled :=false;
          BitBtn2.Enabled :=false;
          adoFrom:=TADOQuery.Create(nil);
          adoFrom.Connection :=ADOCon;
          adoTo:=TADOQuery.Create(nil);
          adoTo.Connection :=DM.adoCon;
          //导入账户表
          adoFrom.SQL.Add('select * from UserList');
          adoFrom.Active :=true;
          adoTo.SQL.Add('select * from UserList');
          adoTo.Active :=true;
          psb.Max :=adoFrom.RecordCount;
          psb.Position :=0;
          while not adoFrom.Eof do
          begin
            if not adoTo.Locate('stuNo',Trim(adoFrom.FieldValues['stuNo']),[loCaseInsensitive])then
            begin
              adoTo.Append;
              adoTo.FieldValues['stuNo']:=adoFrom.FieldValues['stuNo'];
              adoTo.FieldValues['stuName']:=adoFrom.FieldValues['stuName'];
              adoTo.FieldValues['stuSex']:=adoFrom.FieldValues['stuSex'];
              adoTo.FieldValues['stuClass']:=adoFrom.FieldValues['stuClass'];
              adoTo.FieldValues['stuLoginDate']:=adoFrom.FieldValues['stuLoginDate'];
              adoTo.FieldValues['stuUnLoginDate']:=adoFrom.FieldValues['stuUnLoginDate'];
              adoTo.FieldValues['stuValidity']:=adoFrom.FieldValues['stuValidity'];
              adoTo.FieldValues['stuPrepTime']:=adoFrom.FieldValues['stuPrepTime'];
              adoTo.FieldValues['stuAllUsedTime']:=adoFrom.FieldValues['stuAllUsedTime'];
              adoTo.FieldValues['stuMemo']:=adoFrom.FieldValues['stuMemo'];
              adoTo.Post;
              psb.Position :=psb.Position+1;
            end;
              adoFrom.next;
          end;  //end while
          //导入上机记录表
          with adoFrom do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from OnlineNote');
            Active :=true;
            psb.Max :=RecordCount;
          end;
          with adoTo do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from OnlineNote');
            Active :=true;
          end;
          psb.Position :=0;
          while not adoFrom.Eof do
          begin
            adoTo.Append;
            adoTo.FieldValues['stuNo']:=adoFrom.FieldValues['stuNo'];
            adoTo.FieldValues['upDate']:=adoFrom.FieldValues['upDate'];
            adoTo.FieldValues['upTime']:=adoFrom.FieldValues['upTime'];
            adoTo.FieldValues['downDate']:=adoFrom.FieldValues['downDate'];
            adoTo.FieldValues['downTime']:=adoFrom.FieldValues['downTime'];
            adoTo.FieldValues['usedTime']:=adoFrom.FieldValues['usedTime'];
            adoTo.FieldValues['orderlyMan']:=adoFrom.FieldValues['orderlyMan'];
            adoTo.Post;
            psb.Position :=psb.Position+1;
            adoFrom.next;
          end;  //end while
          //导入班级表
          with adoFrom do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from ClassList');
            Active :=true;
            psb.Max :=RecordCount;
          end;
          with adoTo do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from ClassList');
            Active :=true;
          end;
          psb.Position :=0;
          while not adoFrom.Eof do
          begin
            if not adoTo.Locate('clsname',Trim(adoFrom.FieldValues['clsname']),[loCaseInsensitive])then
            begin
              adoTo.Append;
              adoTo.FieldValues['clsName']:=adoFrom.FieldValues['clsname'];
              adoTo.Post;
              psb.Position :=psb.Position+1;
            end;
              adoFrom.next;
          end;  //end while
          //导入管理员表
          with adoFrom do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from AdminList');
            Active :=true;
            psb.Max :=RecordCount;
          end;
          with adoTo do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from AdminList');
            Active :=true;
          end;
          psb.Position :=0;
          while not adoFrom.Eof do
          begin
            if not adoTo.Locate('adLoginName',Trim(adoFrom.FieldValues['adLoginName']),[loCaseInsensitive])then
            begin
              adoTo.Append;
              adoTo.FieldValues['adLoginName']:=adoFrom.FieldValues['adLoginName'];
              adoTo.FieldValues['adPassword']:=adoFrom.FieldValues['adPassword'];
              adoTo.FieldValues['adName']:=adoFrom.FieldValues['adName'];
              adoTo.FieldValues['adSex']:=adoFrom.FieldValues['adSex'];
              adoTo.FieldValues['popedom']:=adoFrom.FieldValues['popedom'];
              adoTo.FieldValues['sysSetup']:=adoFrom.FieldValues['sysSetup'];
              adoTo.FieldValues['fillTime']:=adoFrom.FieldValues['fillTime'];
              adoTo.FieldValues['award']:=adoFrom.FieldValues['award'];
              adoTo.FieldValues['online']:=adoFrom.FieldValues['online'];
              adoTo.FieldValues['member']:=adoFrom.FieldValues['member'];
              adoTo.Post;
              psb.Position :=psb.Position+1;
            end;
              adoFrom.next;
          end;  //end while
          //导入充值记录表
          with adoFrom do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from FillNote');
            Active :=true;
            psb.Max :=RecordCount;
          end;
          with adoTo do
          begin
            Active :=false;
            SQL.Clear;
            SQL.Add('select * from FillNote');
            Active :=true;
          end;
          psb.Position :=0;
          while not adoFrom.Eof do
          begin
            adoTo.Append;
            adoTo.FieldValues['stuNo']:=adoFrom.FieldValues['stuNo'];
            adoTo.FieldValues['fillDate']:=adoFrom.FieldValues['fillDate'];
            adoTo.FieldValues['fillTime']:=adoFrom.FieldValues['fillTime'];
            adoTo.FieldValues['orderlyMan']:=adoFrom.FieldValues['orderlyMan'];
            adoTo.Post;
            psb.Position :=psb.Position+1;
            adoFrom.next;
          end;  //end while
        end;    //end if
      end;      //end case 0
  1  :begin
        adoCon.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='
          +DatafilePath+';Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";'
          +'Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=982702;'
          +'Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;'
          +'Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;'
          +'Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;'
          +'Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don'+''''+'t Copy Locale on Compact=False;'
          +'Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False';
        adoCon.Connected :=true;
        if MessageBox(handle,'是否开始导出？','导出确认',MB_OKCANCEL or MB_ICONWARNING)=IDOK then
        begin
          BitBtn1.Enabled :=false;
          BitBtn2.Enabled :=false;
          adoOnlineNote:=TADOQuery.Create(nil);
          adoOnlineNote.Connection :=DM.ADOCon;
          adoOnlineNote.SQL.Add('select * from OnlineNote');
          adoOnlineNote.SQL.Add('Where [upDate] between #'+DateToStr(StartDate)
            +'# and #'+DateToStr(EndDate)+'#');
          adoOnlineNote.Active :=true;
          psb.Max :=adoOnlineNote.RecordCount;
          psb.Position :=0;
          adoOutPutTable:=TADOQuery.Create(nil);
          adoOutPutTable.Connection :=adoCon;
          adoOutPutTable.SQL.Add('select * from OnlineNote');
          adoOutPutTable.Active :=true;
          while not adoOnlineNote.Eof do
          begin
            adoOutPutTable.Append;
            adoOutPutTable.FieldValues ['stuNo']:=adoOnlineNote.FieldValues['stuNo'];
            adoOutPutTable.FieldValues ['upDate']:=adoOnlineNote.FieldValues['upDate'];
            adoOutPutTable.FieldValues ['upTime']:=adoOnlineNote.FieldValues['upTime'];
            adoOutPutTable.FieldValues ['downDate']:=adoOnlineNote.FieldValues['downDate'];
            adoOutPutTable.FieldValues ['downTime']:=adoOnlineNote.FieldValues['downTime'];
            adoOutPutTable.FieldValues ['usedTime']:=adoOnlineNote.FieldValues['usedTime'];
            adoOutPutTable.FieldValues ['orderlyMan']:=adoOnlineNote.FieldValues['orderlyMan'];
            adoOutPutTable.Post;
            psb.Position :=psb.Position+1;
            adoOnlineNote.Next;
          end;  //end while
          adoOnlineNote.Free;
          adoOutPutTable.Free;
          if MessageBox(handle,'数据导出完成，是否册除原库中的记录？','删除确认',MB_OKCANCEL or MB_ICONWARNING)=IDOK then
          begin
            adoDelTable:=TADOCommand.Create(nil);
            adoDelTable.Connection :=DM.ADOCon;
            adoDelTable.CommandText :='delete from OnlineNote Where [upDate] between #'
              +DateToStr(StartDate)+'# and #'+DateToStr(EndDate)+'#';
            adoDelTable.Execute;
            adoDelTable.Free;
          end;  //end if
        end;    //end if
      end;      //end case 1
  2  :begin
        adoCon.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='
          +DatafilePath+';Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";'
          +'Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=982702;'
          +'Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;'
          +'Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;'
          +'Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;'
          +'Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don'+''''+'t Copy Locale on Compact=False;'
          +'Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False';
        adoCon.Connected :=true;
        if MessageBox(handle,'是否开始导入？','导入确认',MB_OKCANCEL or MB_ICONWARNING)=IDOK then
        begin
          BitBtn1.Enabled :=false;
          BitBtn2.Enabled :=false;
          adoOnlineNote:=TADOQuery.Create(nil);
          adoOnlineNote.Connection :=DM.ADOCon;
          adoOnlineNote.SQL.Add('select * from OnlineNote');
          adoOnlineNote.Active :=true;
          adoOutPutTable:=TADOQuery.Create(nil);
          adoOutPutTable.Connection :=adoCon;
          adoOutPutTable.SQL.Add('select * from OnlineNote');
          adoOutPutTable.Active :=true;
          psb.Max :=adoOutPutTable.RecordCount;
          psb.Position :=0;
          while not adoOutPutTable.Eof do
          begin
            adoOnlineNote.Append;
            adoOnlineNote.FieldValues ['stuNo']:=adoOutPutTable.FieldValues['stuNo'];
            adoOnlineNote.FieldValues ['upDate']:=adoOutPutTable.FieldValues['upDate'];
            adoOnlineNote.FieldValues ['upTime']:=adoOutPutTable.FieldValues['upTime'];
            adoOnlineNote.FieldValues ['downDate']:=adoOutPutTable.FieldValues['downDate'];
            adoOnlineNote.FieldValues ['downTime']:=adoOutPutTable.FieldValues['downTime'];
            adoOnlineNote.FieldValues ['usedTime']:=adoOutPutTable.FieldValues['usedTime'];
            adoOnlineNote.FieldValues ['orderlyMan']:=adoOutPutTable.FieldValues['orderlyMan'];
            adoOnlineNote.Post;
            psb.Position :=psb.Position+1;
            adoOutPutTable.Next;
          end;  //end while
          adoOnlineNote.Free;
          adoOutPutTable.Free;
        end;    //end if
      end;      //end case 2
  end;  //end case
  ShowMessage('操作完成');
  ModalResult:=mrOK;
except
  ShowMessage('操作错误，可能要导入（导出）的数据库格式不一致！');
  Bitbtn1.Enabled :=true;
  Bitbtn2.Enabled :=true;
  ModalResult:=mrCancel;
end;
end;

procedure TInOutDataForm.FormShow(Sender: TObject);
begin
  case OpType of
  0:  Label1.Caption :='准备导入旧数据库文件......';
  1:  Label1.Caption :='准备导出上机记录......';
  2:  Label1.Caption :='准备导入上机记录......';
  end;
end;

procedure TInOutDataForm.BitBtn2Click(Sender: TObject);
begin
  ModalResult:=mrCancel;
end;

procedure TInOutDataForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;

end.