atdprocess.pas

来自「pasa人力资源考勤管理系统」· PAS 代码 · 共 1,026 行 · 第 1/5 页

            Query2.SQL.Add('Update '+tablename+' set factgo=left(b.startrq,4)+'''+dateseparator+'''+substring(b.startrq,5,2)+'''+dateseparator+'''+right(b.startrq,2)+'' ''+'+
                           ' left(b.outtime,2)+'''+timeseparator+'''+right(b.outtime,2),continuetime=1 from '+tablename+' as a,atd11010 as b '+
                           ' where b.items+1=a.itemtime and b.workno=a.workno and (a.rq between b.startrq and b.endrq) and a.rq='''+rq+''' ');
            Query2.ExecSQL;

            //查找时段
            Qry1.Close;
            Qry1.Sql.text:='select distinct * from atd25010 as a,atd09010 as b where cardno='''+cardno+''' and rq='''+rq+''' and  itemtime=case when ('''+state+'''=b.statego1) or ('''+state+'''=b.stateout1) then '+
                           ' 1 else case when ('''+state+'''=b.statego2) or ('''+state+'''=b.stateout2) then 2 else case when ('''+state+'''=b.statego3) or ('''+state+'''=b.stateout3) then 3 else '+
                           ' case when ('''+state+'''=b.statego4) or ('''+state+'''=b.stateout4) then 4 else case when ('''+state+'''=b.statego5) or ('''+state+'''=b.stateout5) then 5 else 0 end end end end end ';
            Qry1.Open;
            item:=qry1itemtime.value;
//先将错误档中的资料抓出比较
            Query1.close;
            query1.sql.text:='Select * from atdtemp_detail where cardno='''+cardno+''' and recdate='''+rq+''' and state='''+state+''' ';
            Query1.open;
            query1.first;
            while not query1.eof do
            begin
                if (item>0) then
                begin
                    if state=adoclock.fieldbyname('statego'+inttostr(item)).asstring then
                    begin
                      if qry1.fieldbyname('factgo').isnull then
                      begin
                        go1:=query1.fieldbyname('recdatetime').value;
                        g:=true;   //标识其被修改过
                        error:=true;
                      end
                      else
                        if qry1.fieldbyname('factgo').value>recdatetime then
                        begin
                           go1:=query1.fieldbyname('recdatetime').value;
                           g:=true;
                           error:=true;
                        end;
                    end;
                    if state=adoclock.fieldbyname('stateout'+inttostr(item)).asstring then
                    begin
                        //检查前日的下班是否为空
                        Query2.close;
                        Query2.sql.text:='select * from '+tablename+' as a where itemtime=(select max(itemtime) from '+tablename+' where cardno='''+cardno+''' and rq='''+datedec(rq,-1)+''' ) and factout is null ';
                        Query2.Open;
                        if not Query2.eof then
                        begin
                             if not Query2.fieldbyname('factgo').isnull then
                             begin
                                ADOQuery1.close;
                                adoquery1.sql.clear;
                                ADoquery1.sql.add('Update '+tablename+' set factout='''+Query1.fieldbyname('recdatetime').value+''' where itemtime=(select max(itemtime) from '+tablename+' where '+
                                                  ' cardno='''+cardno+''' and rq='''+datedec(rq,-1)+''' ) and factout is null ');
                                ADOQuery1.ExecSQL;
                                error:=true;
                             end
                        end;//检查前日
                        if qry1.fieldbyname('factout').isnull then
                        begin
                            out1:=Query1.fieldbyname('recdatetime').value;
                            o:=true;
                            error:=true;
                        end
                        else
                            if qry1.fieldbyname('factout').value>recdatetime then
                            begin
                               out1:=Query1.fieldbyname('recdatetime').value;
                               o:=true;
                               error:=true;
                            end;
                    end; //stateout1
                end; //items>0
               //更新记录
               Query2.Close;
               if g then
                  if o then
                     sql:='factgo='''+go1+''',factout='''+out1+''''
                  else
                     sql:='factgo='''+go1+''''
               else
                  if o then
                     sql:='factout='''+out1+'''';
               if length(sql)>0 then
               begin
                   Query2.SQL.Text:='Update '+tablename+' set '+sql+' where cardno='''+cardno+''' and rq='''+rq+''' and itemtime='+inttostr(item)+' ';
                   Query2.ExecSQL;
               end;
               //标识成已处理
               if error then
               begin
                  Query2.Close;
                  Query2.sql.text:='Delete from atdtemp_detail where cardno='''+cardno+''' and recdate='''+rq+''' and state='''+state+''' ';
                  Query2.execsql;
               end;
            end;  //atdtemp_detail

            //查找时段
            Qry1.Close;
            Qry1.Sql.text:='select distinct * from atd25010 as a,atd09010 as b where cardno='''+cardno+''' and rq='''+rq+''' and  itemtime=case when ('''+state+'''=b.statego1) or ('''+state+'''=b.stateout1) then '+
                           ' 1 else case when ('''+state+'''=b.statego2) or ('''+state+'''=b.stateout2) then 2 else case when ('''+state+'''=b.statego3) or ('''+state+'''=b.stateout3) then 3 else '+
                           ' case when ('''+state+'''=b.statego4) or ('''+state+'''=b.stateout4) then 4 else case when ('''+state+'''=b.statego5) or ('''+state+'''=b.stateout5) then 5 else 0 end end end end end ';
            Qry1.Open;
            item:=qry1itemtime.value;

            if (item>0) then
            begin
                //上班
                if State=adoclock.FieldByName('statego'+inttostr(item)).asstring then
                begin  //若重复打卡取小的值
                     if qry1.fieldbyname('factgo').isnull then
                     begin
                        go1:=recdatetime;
                        g:=true;   //标识其被修改过
                        readok:=true;
                     end
                     else
                        if qry1.fieldbyname('factgo').value>recdatetime then
                        begin
                           go1:=recdatetime;
                           g:=true;
                           readok:=true;
                           log:=ExtractFilePath(Application.ExeName)+'log\duplicateerr.txt';
                           if fileexists(log) then
                           begin
                                assignfile(logfile,log);
                                append(logfile);
                                writeln(logfile,pchar(formatdatetime('yyyy'+dateseparator+'mm'+dateseparator+'dd hh:mm:ss',now)+'读第'+inttostr(item)+'时段实际上班打卡记录时发现有重复打卡记录:'+cardno+rq+recdatetime));
                                closefile(logfile);
                           end else
                           begin
                                assignfile(logfile,log);
                                rewrite(logfile);
                                writeln(logfile,pchar(formatdatetime('yyyy'+dateseparator+'mm'+dateseparator+'dd hh:mm:ss',now)+'读第'+inttostr(item)+'时段实际上班打卡记录时发现有重复打卡记录:'+cardno+rq+recdatetime));
                                closefile(logfile);
                           end;
                        end;
                     readok:=true;
                end; //statego1
                //下班
                if state=adoclock.fieldbyname('stateout'+inttostr(item)).asstring then
                begin
                     //检查前日的下班是否为空
                     Query2.close;
                     Query2.sql.text:='select * from '+tablename+' as a where itemtime=(select max(itemtime) from '+tablename+' where cardno='''+cardno+''' and rq='''+datedec(rq,-1)+''' ) and factout is null ';
                     Query2.Open;
                     if not Query2.eof then
                     begin
                         if Query2.fieldbyname('factgo').isnull then
                         begin
                             ADOQuery1.close;
                             adoquery1.sql.clear;
                             ADoquery1.sql.add('Insert into atdtemp_detail (macname,cardno,recdate,rectime,state,strdatetime,recdatetime) '+
                                               ' select b.macname,b.cardno,b.recdate,b.rectime,b.state,b.strdatetime,b.recdatetime '+
                                               ' from atd15010 as b where b.cardno='''+cardno+''' and b.recdate='''+rq+''' and b.state='''+state+''' and b.readok=0 ');
                             ADOQuery1.ExecSQL;
                             readok:=true;
                             log:=ExtractFilePath(Application.ExeName)+'log\insertrecorderr.txt';
                             if fileexists(log) then
                             begin
                                  assignfile(logfile,log);
                                  append(logfile);
                                  writeln(logfile,pchar(formatdatetime('yyyy'+dateseparator+'mm'+dateseparator+'dd hh:mm:ss',now)+'读第'+inttostr(item)+'时段实际下班打卡记录时找不到实际上班打卡记录:'+cardno+rq+recdatetime));
                                  closefile(logfile);
                             end else
                             begin
                                  assignfile(logfile,log);
                                  rewrite(logfile);
                                  writeln(logfile,pchar(formatdatetime('yyyy'+dateseparator+'mm'+dateseparator+'dd hh:mm:ss',now)+'读第'+inttostr(item)+'时段实际下班打卡记录时找不到实际上班打卡记录:'+cardno+rq+recdatetime));
                                  closefile(logfile);
                             end;
                         end else
                         begin
                             ADOQuery1.close;
                             adoquery1.sql.clear;
                             ADoquery1.sql.add('Update '+tablename+' set factout='''+recdatetime+''' where itemtime=(select max(itemtime) from '+tablename+' where cardno='''+cardno+''' and rq='''+datedec(rq,-1)+''' ) and factout is null ');
                             ADOQuery1.ExecSQL;
                             readok:=true;
                         end
                     end;//检查前日
                     if qry1.fieldbyname('factgo').isnull then
                     begin
                        //上班为空时则放入错误档
                        ADOQuery1.Close;
                        ADOQuery1.SQL.clear;
                        ADOQuery1.SQL.Add('Insert into atdtemp_detail (macname,cardno,recdate,rectime,state,strdatetime,recdatetime) '+
                                          ' select b.macname,b.cardno,b.recdate,b.rectime,b.state,b.strdatetime,b.recdatetime '+
                                          ' from atd15010 as b where b.cardno='''+cardno+''' and b.recdate='''+rq+''' and b.state='''+state+''' and b.readok=0 ');
                        Adoquery1.execsql;
                        readok:=true;
                        log:=ExtractFilePath(Application.ExeName)+'log\insertrecorderr.txt';
                        if fileexists(log) then
                        begin
                             assignfile(logfile,log);
                             append(logfile);
                             writeln(logfile,pchar(formatdatetime('yyyy'+dateseparator+'mm'+dateseparator+'dd hh:mm:ss',now)+'读第'+inttostr(item)+'时段实际下班打卡记录时找不到实际上班打卡记录:'+cardno+rq+recdatetime));
                             closefile(logfile);
                        end else
                        begin
                             assignfile(logfile,log);
                             rewrite(logfile);
                             writeln(logfile,pchar(formatdatetime('yyyy'+dateseparator+'mm'+dateseparator+'dd hh:mm:ss',now)+'读第'+inttostr(item)+'时段实际下班打卡记录时找不到实际上班打卡记录:'+cardno+rq+recdatetime));
                             closefile(logfile);
                        end;
                     end else
                     if qry1.fieldbyname('factout').isnull then
                     begin
                        out1:=recdatetime;
                        o:=true;