readrecord.pas

pasa人力资源考勤管理系统

unit readrecord;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ComCtrls, Buttons, StdCtrls, Mask, ToolEdit, ADODB, tread_readcard, Menus,
  Db;

type
  TFormreadrecord = class(TForm)
    SpeedButton2: TSpeedButton;
    Label1: TLabel;
    Animate1: TAnimate;
    PBar1: TProgressBar;
    ListBox1: TListBox;
    OpenDialog1: TOpenDialog;
    SpeedButton1: TSpeedButton;
    ComboBox1: TComboBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    Label2: TLabel;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    ADOCommand1: TADOCommand;
    Qryclock: TADOQuery;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormActivate(Sender: TObject);
    procedure FormKeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure SpeedButton1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure batchread;
    procedure onebyone;
  end;

var
  Formreadrecord: TFormreadrecord;
  logfile,f:TextFile;
  log,p:string;

implementation

uses datamol,main,clocklb;

{$R *.DFM}

procedure TFormreadrecord.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
     formreadrecord:=nil;
     action:=cafree;
end;

procedure TFormreadrecord.FormActivate(Sender: TObject);
begin
     left:=5;
     top:=3;
end;

procedure TFormreadrecord.FormKeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key=vk_f1 then
  begin
   Application.helpfile :=extractfilepath(application.exename) +'help\pasahelp.hlp';
   application.HelpJump('shm_contents0020');
  end;
end;

procedure TFormreadrecord.SpeedButton1Click(Sender: TObject);
begin
  if opendialog1.Execute then
  begin
     listbox1.items:=opendialog1.Files;
     if listbox1.items.count>0 then speedbutton2.Enabled:=true;
  end;
end;

procedure TFormreadrecord.FormCreate(Sender: TObject);
begin
  combobox1.items.clear;
  datamod.adoquery1.close;
  datamod.adoquery1.SQL.text:='Select * from atd09010 ';
  datamod.adoquery1.Open;
  if datamod.adoquery1.Eof then
  begin
     Application.messagebox('你还没有设定卡钟类别,请进入[参数设定]-->[考勤基本设定]中的[卡钟参数设定]进行设定.','Warning',mb_ok+mb_iconwarning);
     speedbutton1.enabled:=false;
  end else speedbutton1.Enabled:=true;
  while not datamod.adoquery1.eof do
  begin
     combobox1.items.add(datamod.adoquery1.fieldbyname('clockname').asstring);
     datamod.adoquery1.next;
  end;
  if combobox1.items.count>0 then combobox1.text:=combobox1.items[0];
end;

procedure TFormreadrecord.batchread;
var j:integer;
    filename,ctext:string;
    net:boolean;
begin
//首先检测读取的是否为网路上的文件,若是则先拷到本机上
with Datamod do
begin
  Animate1.Active:=True;
  pbar1.visible:=true;
  label1.visible:=true;
  speedbutton1.enabled:=false;
  speedbutton2.enabled:=false;
  try
  log:=ExtractFilePath(Application.ExeName)+'log\readcarderr.log';
  p:=extractfilepath(application.exename)+'log\readcard.log';
  assignfile(f,p);
  append(f);
  if not fileexists(log) then
  begin
     if handle>0 then
        Fileclose(Handle);
  end;
  assignfile(logfile,log);
  append(logfile);
  append(f);
  query1.close;
  query1.sql.clear;
  query1.sql.add('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[tt]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table [dbo].[tt]');
  query1.sql.add('create table tt ( t1 varchar(50))');
  query1.sql.add('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[readrecord]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1) drop procedure [dbo].[readrecord] ');
  query1.execsql;
  PBar1.Max:=listbox1.items.count;
  PBar1.Position:=0;
  PBar1.Step:=1;
  adoclock.Close;
  adoclock.CommandText:='select * from atd09010 where clockname='''+combobox1.text+''' ';
  adoclock.Open;
  net:=false;
  for j:=listbox1.items.count-1 downto 0 do
  begin  //for listbox
   net:=false;
   filename:=listbox1.items[j];
   if pos('\\',filename)>0 then
   begin
     net:=true;
     while pos('\',filename)>0 do
        delete(filename,1,pos('\',filename));
     copyfile(pchar(listbox1.items[j]),pchar(extractfilepath(application.exename)+'Test\'+filename),false);
   end;
   if net then filename:=extractfilepath(application.exename)+'Test\'+filename;
   if FileExists(filename) then
   begin//文件存在
    try
        label1.Caption:='正在读取原始资料文件.....';
        label1.update;
        writeln(f,'---------------------------------------------------------');
        writeln(f,'开始读取文件'+listbox1.items[j]+'中的打卡数据:'+formatdatetime('yyyy/mm/dd hh:mm:ss',now));
        if ctext<>'' then
           ctext:=ctext+#13#10+'BULK INSERT tt FROM '''+filename+''' WITH ( DATAFILETYPE = ''char'', FIELDTERMINATOR = '''', ROWTERMINATOR = ''\n'' ) '
        else
           ctext:='BULK INSERT tt FROM '''+filename+''' WITH ( DATAFILETYPE = ''char'', FIELDTERMINATOR = '''', ROWTERMINATOR = ''\n'' ) ';
        ctext:=ctext+#13#10+'select t1,substring(t1,'+inttostr(adoclockyearb.asinteger)+','+inttostr(adoclockyeare.asinteger)+')+''/''+substring(t1,'+inttostr(adoclockmonthb.asinteger)+',2)+''/''+substring(t1,'+inttostr(adoclockdayb.asinteger)+',2)+'' ''+'+
                       'substring(t1,'+inttostr(adoclocktimeb.asinteger)+',2)+'':''+substring(t1,'+inttostr(adoclocktimee.asinteger)+',2) recdatetime into #t'+inttostr(j)+' from tt where len(t1)='+inttostr(adoclocktextlength.asinteger)+' ';
        ctext:=ctext+#13#10+'delete tt from #t'+inttostr(j)+' where (left(recdatetime,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,2,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,3,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') '+
                       'or substring(recdatetime,4,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,6,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,7,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') '+
                       'or substring(recdatetime,9,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,10,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,12,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') '+
                       'or substring(recdatetime,13,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,15,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') or substring(recdatetime,16,1) not in (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') '+
                       'or convert(int,substring(recdatetime,6,2))>12 or convert(int,substring(recdatetime,9,2))>31 or convert(int,substring(recdatetime,12,2))>23 or convert(int,substring(recdatetime,15,2))>59) and #t'+inttostr(j)+'.t1=tt.t1 ';
        ctext:=ctext+#13#10+'insert into atd15010 (macname, cardno, recdate, rectime, state, recdatetime, readok) select substring(t1,'+inttostr(adoclockmacb.asinteger)+','+inttostr(adoclockmace.asinteger)+') macname, '+
                       'substring(t1,'+inttostr(adoclockcardb.asinteger)+','+inttostr(adoclockcarde.asinteger)+') cardno, substring(t1,'+inttostr(adoclockyearb.asinteger)+','+inttostr(adoclockyeare.asinteger)+')+''/''+substring(t1,'+inttostr(adoclockmonthb.asinteger)+',2)+''/''+'+
                       'substring(t1,'+inttostr(adoclockdayb.asinteger)+',2) recdate,substring(t1,'+inttostr(adoclocktimeb.asinteger)+',2)+'':''+substring(t1,'+inttostr(adoclocktimee.asinteger)+',2) rectime,substring(t1,'+inttostr(adoclockstateb.asinteger)+','+inttostr(adoclockstatee.asinteger)+') state, '+
                       'substring(t1,'+inttostr(adoclockyearb.asinteger)+','+inttostr(adoclockyeare.asinteger)+')+''/''+substring(t1,'+inttostr(adoclockmonthb.asinteger)+',2)+''/''+substring(t1,'+inttostr(adoclockdayb.asinteger)+',2)+'' ''+'+
                       'substring(t1,'+inttostr(adoclocktimeb.asinteger)+',2)+'':''+substring(t1,'+inttostr(adoclocktimee.asinteger)+',2) recdatetime,0 from tt where len(t1)='+inttostr(adoclocktextlength.asinteger)+' ';
        ctext:=ctext+#13#10+'delete tt';
        ctext:=ctext+#13#10+'drop table #t'+inttostr(j);

        //删除重复记录
        writeln(f,'从文件'+listbox1.items[j]+'中共读取了'+inttostr(pbar1.max)+'笔刷卡数据!');
        writeln(f,'结束读取文件'+listbox1.items[j]+'中的打卡数据:'+formatdatetime('yyyy/mm/dd hh:mm:ss',now));
        writeln(f,'---------------------------------------------------------');
    except
      writeln(f,pchar('读取文件'+listbox1.items[j])+'时出错'+formatdatetime('yyyy/mm/dd',now)+' ');
    end;
   end else  //if fileexist
   Application.MessageBox(pchar('文件'+filename+'不存在，请重新选择！'),'文件不存在',mb_iconstop+mb_ok);
   listbox1.Items.Delete(j);
   listbox1.update;
   pbar1.stepit;
   if net then deletefile(filename);
  end;  //for listbox

  try
     label1.Caption:='正在处理刷卡记录...';
     label1.Update;
     adoquery1.close;
     adoquery1.sql.text:='Create procedure readrecord '+#13#10+'AS'+#13#10+ctext;
     adoquery1.execsql;
     adocommand1.commandtext:='exec readrecord';
     adocommand1.execute;

  except
     application.messagebox('读卡钟资料失败!','齐协提示',mb_ok+mb_iconinformation);
  end;