unitkqsj.pas

适合行业为眼镜业

unit Unitkqsj;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, Grids, Wwdbigrd, Wwdbgrid, Buttons, StdCtrls, XPMenu,
  DBGrids, DB;

type
  Tfrmkqsj = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    cbqsbh: TComboBox;
    Label3: TLabel;
    cbjzbh: TComboBox;
    Label4: TLabel;
    spdcx: TSpeedButton;
    DBGrid1: TDBGrid;
    Panel3: TPanel;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    Label5: TLabel;
    cmbtop: TComboBox;
    dsview: TDataSource;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure spdcxClick(Sender: TObject);
  private
    { Private declarations }
    ygbh,xm,bm,icid,zw:string;
    zs1: array[1..8] of string;
    carddate:string;
    card:array [1..8] of string;
    tables,tempsql,tj:string;
    LocalCount:integer;
    procedure qz(ygbh:string;skrq:string); //取得刷卡具体时间
    procedure comptime(ygbh:string;skrq:string); //与班次表比较时间
    procedure readdata;
  public
    { Public declarations }
  end;

var
  frmkqsj: Tfrmkqsj;

implementation

uses func, untdatadm;


{$R *.dfm}
procedure tfrmkqsj.readdata;
var
  sqlpub:widestring;
  datas:olevariant;
begin
  tempsql:=' select top '+trim(cmbtop.Text);
  tables:=' id,person_no,converT(varchar(10),sktime,14) as sktime,skdate,p_name,p_workid,p_icno from vkqsj where 1=1';
  sqlpub:=tempsql+tables+tj;
  dmmain.cdsKQdoc.Close;
  dmmain.cdsKQdoc.Data:=null;
  try
    datas:=   adisp.GetRecord(sqlpub);
    if not varisnull(datas) then
    begin
    if tj<>'' then
    begin
      dmmain.cdsKQdoc.AppendData(datas,false);
    end else
    begin
      dmmain.cdsKQdoc.Data:=datas;
    end;
    dmmain.cdsKQdoc.Open;
    LocalCount := GetKeyFieldValue('id',dmmain.cdsKQdoc);   //传入具体字段
    datas:=null;
    end;
  except
  end;
end;
procedure tfrmkqsj.qz(ygbh:string;skrq:string);
var
  p:integer;
  temp:widestring;
  datas:olevariant;
begin
  dmmain.CDSexecsql.Close;
  dmmain.CDSexecsql.Data:=null;
  temp:='SELECT * from vkqsj where person_no='+''''+trim(ygbh)+''''+' and skdate='+''''+trim(skrq)+'''';
  try
    datas:=null;
    datas:=adisp.execSql(temp);
    if not varisnull(datas) then
    begin
      dmmain.CDSexecsql.Data:=datas;
      dmmain.CDSexecsql.Open;
      datas:=null;
    end;
  except
  end;
  if not dmmain.CDSexecsql.IsEmpty then
  begin
    xm:=trim(dmmain.CDSexecsql.fieldbyname('p_name').AsString);
    bm:=trim(dmmain.CDSexecsql.fieldbyname('p_workid').AsString);
    icid:=trim(dmmain.CDSexecsql.fieldbyname('p_ICno').AsString);
    zw:='';
    carddate:=trim(dmmain.CDSexecsql.fieldbyname('skdate').AsString); //  查找同一天刷卡记录；
    p:=1; //计数器赋初值
    if  trim(ygbh)<>'' then
    begin
      while not dmmain.CDSexecsql.Eof do
      begin
        if p<=8 then
        begin
          zs1[p]:=formatdatetime('hh'':''mm'':''ss',dmmain.CDSexecsql.FieldByName('sktime').AsDateTime);
        end;
        inc(p);
        dmmain.CDSexecsql.Next;
      end;
    end;
  end else
  begin
     xm:='';
    bm:='';
    icid:='';
    zw:='';
  end;
  dmmain.CDSexecsql.Close;
  dmmain.CDSexecsql.data:=null;
end;
procedure tfrmkqsj.comptime(ygbh:string;skrq:string);  //查找日期和时间对比;
var
  row,i,p:integer;
  yy,mm,dd:word;
  temp:widestring;
  datas:olevariant;
begin
  dmmain.CDSexecsql.Close;
  dmmain.CDSexecsql.Data:=null;
  temp:='select * from dp_bcap where person_no='+''''+trim(ygbh)+'''';
 try
    datas:=null;
    datas:=adisp.execSql(temp);
    if not varisnull(datas) then
    begin
      dmmain.CDSexecsql.Data:=datas;
      dmmain.CDSexecsql.Open;
      datas:=null;
    end;
  except
  end;
  if not (dmmain.CDSexecsql.IsEmpty ) then
  begin
    decodedate(strtodate(skrq),yy,mm,dd);
    p:=dd+4;//对应的天数的上班时间;
    if trim(dmmain.CDSexecsql.Fields.Fields[p].AsString)<>'' then
    begin
      dmmain.CDSquery2.Close;
      dmmain.CDSquery2.Data:=null;
      temp:='select * from dp_bcjg where bc_no='+''''+trim(dmmain.CDSexecsql.Fields.Fields[p].AsString)+'''';
      try
        datas:=null;
        datas:=adisp.resultrecord(temp);
        if not varisnull(datas) then
        begin
          dmmain.CDSquery2.Data:=datas;
          dmmain.CDSquery2.Open;
          datas:=null;
        end;
      except
      end;
      if not (dmmain.CDSquery2.IsEmpty) then
      begin
        card[1]:=timetostr(dmmain.CDSquery2.fieldbyname('swsb').AsDateTime);
        card[2]:=timetostr(dmmain.CDSquery2.fieldbyname('swxb').AsDateTime);
        card[3]:=timetostr(dmmain.CDSquery2.fieldbyname('xwsb').AsDateTime);
        card[4]:=timetostr(dmmain.CDSquery2.fieldbyname('xwxb').AsDateTime);
        card[5]:=timetostr(dmmain.CDSquery2.fieldbyname('wssb').AsDateTime);
        card[6]:=timetostr(dmmain.CDSquery2.fieldbyname('wsxb').AsDateTime);
        card[7]:=timetostr(dmmain.CDSquery2.fieldbyname('jbsb').AsDateTime);
        card[8]:=timetostr(dmmain.CDSquery2.fieldbyname('jbxb').AsDateTime);
      end else
      begin
        card[1]:='00:00:00';
        card[2]:='00:00:00';
        card[3]:='00:00:00';
        card[4]:='00:00:00';
        card[5]:='00:00:00';
        card[6]:='00:00:00';
        card[7]:='00:00:00';
        card[8]:='00:00:00';
      end;
      dmmain.CDSquery2.Close;
    end ;
  end else
  begin
    //application.MessageBox('请先设置【班次结构】或【班次安排】！',pchar(application.Title),mb_iconwarning);
    card[1]:='00:00:00';
    card[2]:='00:00:00';
    card[3]:='00:00:00';
    card[4]:='00:00:00';
    card[5]:='00:00:00';
    card[6]:='00:00:00';
    card[7]:='00:00:00';
    card[8]:='00:00:00';
    //exit;
  end;
  dmmain.CDSquery2.Close;
  dmmain.CDSexecsql.Close;
  dmmain.CDSquery2.Data:=null;
  dmmain.CDSexecsql.Data:=null;
end;
///////////////////////////////////////

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

procedure Tfrmkqsj.FormShow(Sender: TObject);
begin
  tj:='';
  cmbtop.ItemIndex:=0;
  readdata;
end;

procedure Tfrmkqsj.BitBtn7Click(Sender: TObject);
begin
  close;
end;

procedure Tfrmkqsj.BitBtn5Click(Sender: TObject);
var
  sqlpub:widestring;
begin
  if dmmain.cdsKQdoc.IsEmpty then
  begin
    application.MessageBox('目前没有可以删除的员工考勤信息！',pchar(application.Title),mb_iconwarning);
    exit;
  end else
  begin
    if application.MessageBox('是否确定删除该条信息？',pchar(application.Title),mb_iconquestion+mb_yesno)=idyes  then
    begin
      try
      sqlpub:='delete from dp_kqdata where id='+dmmain.cdsKQdoc.fieldbyname('id').AsString;
      adisp.updatesql(sqlpub);
      except

      end;
    end;
  end;
end;

procedure Tfrmkqsj.BitBtn4Click(Sender: TObject);
var
   i,j,p,ff:integer;
   swsb,swxb,xwsb,xwxb,wssb,wsxb,jbsb,jbxb,by1,by2,by3,bj1,bj2,bj3,bj4,skrq:string; //刷卡时间段
   hh,mm,ss,mes:word;//转换使用
   sqlpub:widestring;
   datas:olevariant;
begin
  if dmmain.cdsKQdoc.IsEmpty then
  begin
    application.MessageBox('没有可以导出的考勤数据',pchar(application.Title),mb_iconinformation);
    exit;
  end;
  if application.MessageBox('是否确定导出考勤机数据信息？',pchar(application.Title),mb_iconquestion+mb_yesno)=idyes  then
  begin
    try
    sqlpub:='delete from dp_KQdocument';
    ipubtemp.scrapsate(sqlpub);
    except
    end;
    j:=1;
    while j<=dmmain.cdsKQdoc.RecordCount  do
    begin
      ygbh:= dmmain.cdsKQdoc.fieldbyname('person_no').AsString;
      dmmain.CDSquery.Close;
      dmmain.CDSquery.Data:=null;
      sqlpub:='select skdate from vkqsj where person_no='+''''+trim(ygbh)+''''+' group by skdate ';
      try
        datas:=null;
        datas:=adisp.GetRecord(sqlpub);
        if not varisnull(datas) then
        begin
          dmmain.CDSquery.Data:=datas;
          dmmain.CDSquery.Open;
          datas:=null;
        end;
      except
      end;
      while not (dmmain.CDSquery.Eof) do
      begin
        skrq:=trim(dmmain.CDSquery.fieldbyname('skdate').AsString);
        qz(ygbh,skrq);
        comptime(ygbh,skrq);
        i:=1;
       //////判断刷卡时间段//////////////////
        while i<=8 do
        begin
          if card[2]<>'0:00:00' then  //上午下班
          begin
            decodetime(strtotime(card[2]),hh,mm,ss,mes);  //
            ff:=mm-30;
            if ff<0 then //满60分中换为小时;
            begin
              hh:=hh-1;
              ff:=ff+60;
            end;
            mm:=ff;
            bj1:=timetostr(encodetime(hh,mm,ss,mes)); //
          end else