DROP PROCEDURE IF EXISTS `prc_createTaskCountReport`; CREATE PROCEDURE `prc_createTaskCountReport`(IN prm_date VARCHAR(10), OUT prm_success VARCHAR(1), OUT prm_error VARCHAR(2000)) BEGIN /*** 执行步骤 1.查询昨天在有效外勤的人员插入外勤日统计人员临时表 2.在临时表更新昨天休息的人员为休息状态 3.临时表通过外勤设置的规则以及实际的打卡记录(游标循环)计算未休息人员的考勤状态 4.把计算后的临时表数据插入到外勤日统计表 5.删除临时表数据 6.查询前天存在次日打卡的人员 7.游标方式更新这些存在次日人员的日统计结果 注意:请假时长及加班时长通过审批来处理（业务完全剥离） /*****************定义变量***********************/ DECLARE v_comId BIGINT(20); -- 企业ID DECLARE v_recordCount INT; -- 记录数 DECLARE v_rowCount BIGINT(20); -- 考勤人员记录数 DECLARE v_comTaskId BIGINT(20); -- 外勤任务ID DECLARE v_perId BIGINT(20); -- 人员ID DECLARE v_beforeYestoday VARCHAR(10) ; -- 前天 DECLARE v_startTime DATETIME ; -- 开始执行时间 DECLARE v_error INTEGER DEFAULT 0; DECLARE done INT DEFAULT FALSE; /*****************定义游标***********************/ -- 查询昨天在外勤任务的人员 DECLARE cur_yesterdayAttendPer CURSOR FOR SELECT comId, comTaskId, perId FROM temp_count_per_task_date WHERE attendDate = prm_date AND isRest = '0' -- 排除休息 ORDER BY comId,comTaskId,perId; -- 查询前天的次日打卡的人员 DECLARE cur_beforeYesterdayAttendPer CURSOR FOR SELECT a.comId, -- 企业ID a.comTaskId, -- 外勤任务ID b.perId -- 人员ID FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.schedulDate = v_beforeYestoday AND e.isEndNextDay = '1' -- 下班为次日 ORDER BY a.comId,a.comTaskId,b.perId; -- 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ; /*****************定义异常***********************/ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = 1; -- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING SET v_error = 1; -- 千万别带SQLWARNING,会拦截部分语法正确的sql语句 /*****************获取数据库源***********************/ START TRANSACTION; -- 开启事务 label_pro:BEGIN -- 执行过程中出现错误则跳出此Label SET v_startTime = NOW(); SELECT COUNT(1) INTO v_recordCount FROM count_task_execute_record WHERE attendDate = prm_date ; IF v_recordCount = 0 THEN -- 1.生成考勤日统计人员临时表 INSERT INTO temp_count_per_task_date(perId, -- 人员ID attendDate, -- 考勤日期 createTime, -- 创建时间 createBy, -- 创建人ID normalAttendTimes, -- 正常考勤次数 isRest, -- 是否休息 isAbsenteeism, -- 是否缺勤 absenceTime, -- 缺勤时长(时) lateTimes, -- 迟到次数 totalLateTime, -- 总迟到时间(分钟) leaveEarlyTimes, -- 早退次数 totalLeaveEarlyTime, -- 总早退时间(分钟) missCardTimes, -- 缺卡次数 workHours, -- 总工时(时) firstRoundOnWork, -- 第一轮上班打卡结果 firstRoundOffWork, -- 第一轮下班打卡结果 secondRoundOnWork, -- 第二轮上班打卡结果 secondRoundOffWork, -- 第二轮下班打卡结果 thirdRoundOnWork, -- 第三轮上班打卡结果 thirdRoundOffWork, -- 第三轮下班打卡结果 fourthRoundOnWork, -- 第四轮上班打卡结果 fourthRoundOffWork, -- 第四轮下班打卡结果 fifthRoundOnWork, -- 第五轮上班打卡结果 fifthRoundOffWork, -- 第五轮下班打卡结果 comId, -- 企业ID departId, -- 部门ID comTaskId) -- 外勤任务ID SELECT b.perId, -- 人员ID prm_date AS attendDate, -- 考勤日期 NOW() AS createTime, -- 创建时间 b.perId AS createBy, -- 创建人ID 0 normalAttendTimes, -- 正常考勤次数 '0' AS isRest, -- 是否休息 0 AS isAbsenteeism, -- 是否缺勤 0 AS absenceTime, -- 缺勤时长(时) 0 AS lateTimes, -- 迟到次数 0 AS totalLateTime, -- 总迟到时间(分钟) 0 AS leaveEarlyTimes, -- 早退次数 0 AS totalLeaveEarlyTime, -- 总早退时间(分钟) 0 AS missCardTimes, -- 缺卡次数 0 AS workHours, -- 总工时(时) NULL AS firstRoundOnWork, -- 第一轮上班打卡结果 NULL AS firstRoundOffWork, -- 第一轮下班打卡结果 NULL AS secondRoundOnWork, -- 第二轮上班打卡结果 NULL AS secondRoundOffWork, -- 第二轮下班打卡结果 NULL AS thirdRoundOnWork, -- 第三轮上班打卡结果 NULL AS thirdRoundOffWork, -- 第三轮下班打卡结果 NULL AS fourthRoundOnWork, -- 第四轮上班打卡结果 NULL AS fourthRoundOffWork, -- 第四轮下班打卡结果 NULL AS fifthRoundOnWork, -- 第五轮上班打卡结果 NULL AS fifthRoundOffWork, -- 第五轮下班打卡结果 a.comId, -- 企业ID NULL AS departId, -- 部门ID a.comTaskId -- 外勤ID FROM com_task a , com_task_schedul b WHERE a.comTaskId = b.comTaskId AND a.comId = b.comId AND a.isDelete = '0' AND a.startDate <= prm_date AND a.endDate >= prm_date AND b.schedulDate = prm_date ; UPDATE temp_count_per_task_date a, per_org_relation b SET a.departId = IF(b.orgType = '2',b.orgId,NULL) -- (如果组织类型为公司则部门ID为空) WHERE a.comId = b.comId AND a.perId = b.perId AND a.attendDate = prm_date AND b.perOrgRelationId = (SELECT MAX(perOrgRelationId) -- 昨天在外勤任务的人员，不管是否离职都要生成报表 FROM per_org_relation WHERE perId = a.perId AND comId = a.comId) ; IF v_error = 1 THEN SET prm_success = '0' ; SET prm_error = '插入外勤日统计人员临时表出错' ; ROLLBACK; LEAVE label_pro ; END IF; -- 2.更新休息人员 UPDATE temp_count_per_task_date SET isRest = '1' WHERE attendDate = prm_date AND EXISTS (SELECT 1 FROM com_task_schedul WHERE comTaskId = temp_count_per_task_date.comTaskId AND isRest = '1' AND schedulDate = prm_date AND perId = temp_count_per_task_date.perId); IF v_error = 1 THEN SET prm_success = '0' ; SET prm_error = '更新休息人员失败' ; ROLLBACK; LEAVE label_pro ; END IF; -- 3.处理考勤人员 /**********************以下为生成报表逻辑**************************/ -- 打开游标 OPEN cur_yesterdayAttendPer; yesterday_loop:LOOP FETCH cur_yesterdayAttendPer INTO v_comId, v_comTaskId, v_perId; -- 声明结束的时候 IF done THEN LEAVE yesterday_loop; END IF; /************ 3.更新考勤统计指标 ************/ -- 3.1 更新考勤指标（不包含缺勤时长、工作时长） UPDATE temp_count_per_task_date h, (SELECT SUM(IF(k.attendStatus = '1',1,0)) AS normalAttendTimes, -- 正常考勤次数 IF(SUM(IF(k.attendStatus = '5',1,0)) = COUNT(1) ,1,0 )AS isAbsenteeism, -- 是否缺勤 (如果缺卡总次数等于考勤次数则为缺勤) SUM(IF(k.attendStatus = '2',1,0)) AS lateTimes, -- 迟到次数 SUM(IF(k.attendStatus = '2', TIMESTAMPDIFF(MINUTE,k.attendTime,DATE_FORMAT(k.clockInTime,'%Y-%m-%d %H:%i')), -- 因为设置的时间秒为0所以不需要转换 0)) AS totalLateTime, -- 总迟到时间(分钟) SUM(IF(k.attendStatus = '3',1,0)) AS leaveEarlyTimes, -- 早退次数 SUM(IF(k.attendStatus = '3', TIMESTAMPDIFF(MINUTE,k.clockInTime,DATE_FORMAT(k.attendTime,'%Y-%m-%d %H:%i')), -- 因为设置的时间秒为0所以不需要转换 0)) totalLeaveEarlyTime, -- 总早退时间(分钟) SUM(IF(k.attendStatus = '5',1,0)) AS missCardTimes, -- 缺卡次数 MAX(IF(k.commutRound = '1' AND k.clockInType = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS firstRoundOnWork, -- 第一轮上班打卡结果 MAX(IF(k.commutRound = '1' AND k.clockInType = '2', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS firstRoundOffWork, -- 第一轮下班打卡结果 MAX(IF(k.commutRound = '2' AND k.clockInType = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS secondRoundOnWork, -- 第二轮上班打卡结果 MAX(IF(k.commutRound = '2' AND k.clockInType = '2', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS secondRoundOffWork, -- 第二轮下班打卡结果 MAX(IF(k.commutRound = '3' AND k.clockInType = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS thirdRoundOnWork, -- 第三轮上班打卡结果 MAX(IF(k.commutRound = '3' AND k.clockInType = '2', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS thirdRoundOffWork, -- 第三轮下班打卡结果 MAX(IF(k.commutRound = '4' AND k.clockInType = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fourthRoundOnWork, -- 第四轮上班打卡结果 MAX(IF(k.commutRound = '4' AND k.clockInType = '2', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fourthRoundOffWork, -- 第四轮下班打卡结果 MAX(IF(k.commutRound = '5' AND k.clockInType = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fifthRoundOnWork, -- 第五轮上班打卡结果 MAX(IF(k.commutRound = '5' AND k.clockInType = '2', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN IF(DATE(k.clockInTime) = prm_date , DATE_FORMAT(k.clockInTime,'%H:%i'), CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i'))) ELSE '' END), (CASE WHEN k.attendStatus = '0' THEN '($未下班$)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fifthRoundOffWork -- 第五轮下班打卡结果 FROM (SELECT m.commutRound, -- 第几轮 m.clockInType, -- 打卡类型 m.attendTime, -- 考勤时间 n.clockInTime, -- 打卡时间 n.approveApplyId, -- 审批ID IF(n.attendStatus IS NULL, IF(m.attendTime >= NOW() , '0', -- 0 代表未下班 '5'), n.attendStatus) AS attendStatus -- 打卡状态 FROM (SELECT e.commutRound, CONCAT(prm_date,' ',e.startWorkTime) AS attendTime, '1' AS clockInType FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.perId = v_perId AND b.schedulDate = prm_date AND a.comTaskId = v_comTaskId UNION SELECT e.commutRound, IF(e.isEndNextDay = '1', CONCAT(DATE_ADD(prm_date,INTERVAL 1 DAY),' ',e.endWorkTime), CONCAT(prm_date,' ',e.endWorkTime)) AS attendTime, '2' AS clockInType FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.perId = v_perId AND b.schedulDate = prm_date AND a.comTaskId = v_comTaskId) m LEFT JOIN (SELECT a.clockInType, a.clockInRound, a.clockInTime , a.attendStatus, a.approveApplyId FROM per_task_card_record a WHERE a.perId = v_perId AND a.clockInDate = prm_date AND a.comTaskId = v_comTaskId) n ON m.commutRound = n.clockInRound AND m.clockInType = n.clockInType)k LEFT JOIN sys_dict_data f ON f.dict_type = 'attendStatus' AND k.attendStatus = f.dict_value) g SET h.normalAttendTimes = g.normalAttendTimes , -- 正常考勤次数 h.isAbsenteeism = g.isAbsenteeism, -- 是否缺勤 -- absenceTime, -- 缺勤时长(时) h.lateTimes = g.lateTimes, -- 迟到次数 h.totalLateTime = g.totalLateTime , -- 总迟到时间(分钟) h.leaveEarlyTimes = g.leaveEarlyTimes, -- 早退次数 h.totalLeaveEarlyTime = g.totalLeaveEarlyTime, -- 总早退时间(分钟) h.missCardTimes = g.missCardTimes, -- 缺卡次数 -- a.workHours, -- 总工时(时) h.firstRoundOnWork = g.firstRoundOnWork, -- 第一轮上班打卡结果 h.firstRoundOffWork = g.firstRoundOffWork, -- 第一轮下班打卡结果 h.secondRoundOnWork = g.secondRoundOnWork, -- 第二轮上班打卡结果 h.secondRoundOffWork = g.secondRoundOffWork, -- 第二轮下班打卡结果 h.thirdRoundOnWork = g.thirdRoundOnWork, -- 第三轮上班打卡结果 h.thirdRoundOffWork = g.thirdRoundOffWork, -- 第三轮下班打卡结果 h.fourthRoundOnWork = g.fourthRoundOnWork, -- 第四轮上班打卡结果 h.fourthRoundOffWork = g.fourthRoundOffWork, -- 第四轮下班打卡结果 h.fifthRoundOnWork = g.fifthRoundOnWork, -- 第五轮上班打卡结果 h.fifthRoundOffWork = g.fifthRoundOffWork -- 第五轮下班打卡结果 WHERE h.perId = v_perId AND h.attendDate = prm_date AND h.comTaskId = v_comTaskId; IF v_error = 1 THEN SET prm_success = '0' ; SET prm_error = CONCAT('计算考勤指标失败,人员ID: ',v_perId) ; ROLLBACK; LEAVE label_pro ; END IF; -- 3.2 更新缺勤情况 （缺勤时长） UPDATE temp_count_per_task_date SET absenceTime = (SELECT IFNULL(TRUNCATE(SUM(TIMESTAMPDIFF(MINUTE,CONCAT(prm_date,' ',DATE_FORMAT(e.startWorkTime,'%H:%i')), IF(e.isEndNextDay = '1', CONCAT(DATE_ADD(prm_date,INTERVAL 1 DAY),' ',DATE_FORMAT(e.endWorkTime,'%H:%i')), CONCAT(prm_date,' ',DATE_FORMAT(e.endWorkTime,'%H:%i')))))/60,2),0) -- 缺勤时长 FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.perId = v_perId AND b.schedulDate = prm_date AND a.comTaskId = v_comTaskId) WHERE perId = v_perId AND attendDate = prm_date AND comTaskId = v_comTaskId AND isAbsenteeism = '1'; IF v_error = 1 THEN SET prm_success = '0' ; SET prm_error = CONCAT('计算缺勤时长失败,人员ID: ',v_perId) ; ROLLBACK; LEAVE label_pro ; END IF; -- 3.3 更新非缺勤情况 （工作时长） UPDATE temp_count_per_task_date SET workHours = (SELECT IFNULL(TRUNCATE(SUM(CASE WHEN n.onDutyTime IS NOT NULL AND n.offDutyTime IS NOT NULL THEN IF(TIMESTAMPDIFF(MINUTE, GREATEST(n.onDutyTime,n.attendStartTime), LEAST(n.offDutyTime,n.attendEndTime))<0, 0, TIMESTAMPDIFF(MINUTE, GREATEST(n.onDutyTime,n.attendStartTime), -- 上班开始时间取考勤时间和打卡时间后面的时间 LEAST(n.offDutyTime,n.attendEndTime))) -- 下班结束时间取考勤时间和打卡时间前面的时间 ELSE 0 END)/60,2),0) -- 工作时长 FROM (SELECT MAX(IF(a.clockInType = '1',DATE_FORMAT(a.setClockInTime,'%Y-%m-%d %H:%i'),null)) AS attendStartTime, -- 上班设置的时间 MAX(IF(a.clockInType = '2',DATE_FORMAT(a.setClockInTime,'%Y-%m-%d %H:%i'),null)) AS attendEndTime, -- 下班设置的时间 MAX(IF(a.clockInType = '1',DATE_FORMAT(a.clockInTime,'%Y-%m-%d %H:%i'),null)) AS onDutyTime, -- 上班打卡时间 MAX(IF(a.clockInType = '2',DATE_FORMAT(a.clockInTime,'%Y-%m-%d %H:%i'),null)) AS offDutyTime, -- 下班打卡时间 MAX(IF(a.clockInType = '1',a.attendStatus,null)) AS onDutyStatus, -- 上班状态 MAX(IF(a.clockInType = '2',a.attendStatus,null)) AS offDutyStatus -- 下班状态 FROM per_task_card_record a WHERE a.perId = v_perId AND a.clockInDate = prm_date AND a.comTaskId = v_comTaskId GROUP by a.clockInRound) n) WHERE perId = v_perId AND attendDate = prm_date AND comTaskId = v_comTaskId AND isAbsenteeism = '0'; IF v_error = 1 THEN SET prm_success = '0' ; SET prm_error = CONCAT('计算工作时长失败,人员ID: ',v_perId) ; ROLLBACK; LEAVE label_pro ; END IF; END LOOP; -- 关闭游标 CLOSE cur_yesterdayAttendPer; -- 4.插入考勤日统计表 INSERT INTO count_per_task_date(perId, -- 人员ID attendDate, -- 考勤日期 createTime, -- 创建时间 createBy, -- 创建人ID normalAttendTimes, -- 正常考勤次数 isRest, -- 是否休息 isAbsenteeism, -- 是否缺勤 absenceTime, -- 缺勤时长(时) lateTimes, -- 迟到次数 totalLateTime, -- 总迟到时间(分钟) leaveEarlyTimes, -- 早退次数 totalLeaveEarlyTime, -- 总早退时间(分钟) missCardTimes, -- 缺卡次数 workHours, -- 总工时(时) firstRoundOnWork, -- 第一轮上班打卡结果 firstRoundOffWork, -- 第一轮下班打卡结果 secondRoundOnWork, -- 第二轮上班打卡结果 secondRoundOffWork, -- 第二轮下班打卡结果 thirdRoundOnWork, -- 第三轮上班打卡结果 thirdRoundOffWork, -- 第三轮下班打卡结果 fourthRoundOnWork, -- 第四轮上班打卡结果 fourthRoundOffWork, -- 第四轮下班打卡结果 fifthRoundOnWork, -- 第五轮上班打卡结果 fifthRoundOffWork, -- 第五轮下班打卡结果 comId, -- 企业ID departId, -- 部门ID comTaskId) -- 外勤任务ID SELECT perId, -- 人员ID attendDate, -- 考勤日期 createTime, -- 创建时间 createBy, -- 创建人ID normalAttendTimes, -- 正常考勤次数 isRest, -- 是否休息 isAbsenteeism, -- 是否缺勤 absenceTime, -- 缺勤时长(时) lateTimes, -- 迟到次数 totalLateTime, -- 总迟到时间(分钟) leaveEarlyTimes, -- 早退次数 totalLeaveEarlyTime, -- 总早退时间(分钟) missCardTimes, -- 缺卡次数 workHours, -- 总工时(时) firstRoundOnWork, -- 第一轮上班打卡结果 firstRoundOffWork, -- 第一轮下班打卡结果 secondRoundOnWork, -- 第二轮上班打卡结果 secondRoundOffWork, -- 第二轮下班打卡结果 thirdRoundOnWork, -- 第三轮上班打卡结果 thirdRoundOffWork, -- 第三轮下班打卡结果 fourthRoundOnWork, -- 第四轮上班打卡结果 fourthRoundOffWork, -- 第四轮下班打卡结果 fifthRoundOnWork, -- 第五轮上班打卡结果 fifthRoundOffWork, -- 第五轮下班打卡结果 comId, -- 企业ID departId, -- 部门ID comTaskId -- 外勤任务ID FROM temp_count_per_task_date ; SELECT row_count() INTO v_rowCount ; -- 4.1 清除临时表 TRUNCATE TABLE temp_count_per_task_date ; END IF ; /**********************以下为处理前天次日考勤情况**************************/ -- 5.处理前天的次日情况(只有最后一轮的下班时间可能存在次日) SELECT DATE_SUB(prm_date,INTERVAL 1 DAY) INTO v_beforeYestoday; -- 前天 -- 打开游标 SET done = FALSE; -- 因为之前游标关闭把done置为true了，所以这里需要重新设置 false才会打开游标 OPEN cur_beforeYesterdayAttendPer; beforeYesterday_loop:LOOP FETCH cur_beforeYesterdayAttendPer INTO v_comId,v_comTaskId,v_perId; -- 声明结束的时候 IF done THEN LEAVE beforeYesterday_loop; END IF; UPDATE count_per_task_date h, (SELECT SUM(IF(k.attendStatus = '1',1,0)) AS normalAttendTimes, -- 正常考勤次数 IF(SUM(IF(k.attendStatus = '5',1,0)) = COUNT(1) ,1,0 )AS isAbsenteeism, -- 是否缺勤 (如果缺卡总次数等于考勤次数则为缺勤) SUM(IF(k.attendStatus = '2',1,0)) AS lateTimes, -- 迟到次数 SUM(IF(k.attendStatus = '2', TIMESTAMPDIFF(MINUTE,k.attendTime,DATE_FORMAT(k.clockInTime,'%Y-%m-%d %H:%i')), 0)) AS totalLateTime, -- 总迟到时间(分钟) SUM(IF(k.attendStatus = '3',1,0)) AS leaveEarlyTimes, -- 早退次数 SUM(IF(k.attendStatus = '3', TIMESTAMPDIFF(MINUTE,k.clockInTime,DATE_FORMAT(k.attendTime,'%Y-%m-%d %H:%i')), 0)) totalLeaveEarlyTime, -- 总早退时间(分钟) SUM(IF(k.attendStatus = '5',1,0)) AS missCardTimes, -- 缺卡次数 MAX(IF(k.commutRound = '1' AND k.clockInType = '1' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS firstRoundOnWork, -- 第一轮上班打卡结果 MAX(IF(k.commutRound = '1' AND k.clockInType = '2' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS firstRoundOffWork, -- 第一轮下班打卡结果 MAX(IF(k.commutRound = '2' AND k.clockInType = '1' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS secondRoundOnWork, -- 第二轮上班打卡结果 MAX(IF(k.commutRound = '2' AND k.clockInType = '2' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS secondRoundOffWork, -- 第二轮下班打卡结果 MAX(IF(k.commutRound = '3' AND k.clockInType = '1' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS thirdRoundOnWork, -- 第三轮上班打卡结果 MAX(IF(k.commutRound = '3' AND k.clockInType = '2' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS thirdRoundOffWork, -- 第三轮下班打卡结果 MAX(IF(k.commutRound = '4' AND k.clockInType = '1' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fourthRoundOnWork, -- 第四轮上班打卡结果 MAX(IF(k.commutRound = '4' AND k.clockInType = '2' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fourthRoundOffWork, -- 第四轮下班打卡结果 MAX(IF(k.commutRound = '5' AND k.clockInType = '1' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fifthRoundOnWork, -- 第五轮上班打卡结果 MAX(IF(k.commutRound = '5' AND k.clockInType = '2' AND k.isNextDay = '1', CONCAT((CASE WHEN k.clockInTime IS NOT NULL THEN CONCAT('次日',DATE_FORMAT(k.clockInTime,'%H:%i')) ELSE '' END), (CASE WHEN k.attendStatus = '1' AND k.approveApplyId IS NULL THEN '(正常)' WHEN k.attendStatus = '1' AND k.approveApplyId IS NOT NULL THEN '($已补卡$)' ELSE CONCAT('($',f.dict_label,'$)') END)), NULL)) AS fifthRoundOffWork -- 第五轮下班打卡结果 FROM (SELECT m.commutRound, -- 第几轮 m.isNextDay, -- 是否次日 m.clockInType, -- 打卡类型 m.attendTime, -- 考勤时间 n.clockInTime, -- 打卡时间 n.approveApplyId, -- 审批ID IF(n.attendStatus IS NULL, IF(m.attendTime >= NOW() , '0', -- 0 代表未下班 '5'), n.attendStatus) AS attendStatus -- 打卡状态 FROM (SELECT e.commutRound, e.isStartNextDay AS isNextDay, CONCAT(v_beforeYestoday,' ',e.startWorkTime) AS attendTime, '1' AS clockInType FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.perId = v_perId AND b.schedulDate = v_beforeYestoday AND a.comTaskId = v_comTaskId UNION SELECT e.commutRound, e.isEndNextDay AS isNextDay, IF(e.isEndNextDay = '1', CONCAT(DATE_ADD(v_beforeYestoday,INTERVAL 1 DAY),' ',e.endWorkTime), CONCAT(v_beforeYestoday,' ',e.endWorkTime)) AS attendTime, '2' AS clockInType FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.perId = v_perId AND b.schedulDate = v_beforeYestoday AND a.comTaskId = v_comTaskId) m LEFT JOIN (SELECT a.clockInType, a.clockInRound, a.clockInTime , a.attendStatus, a.approveApplyId FROM per_task_card_record a WHERE a.perId = v_perId AND a.clockInDate = v_beforeYestoday AND a.comTaskId = v_comTaskId) n ON m.commutRound = n.clockInRound AND m.clockInType = n.clockInType)k LEFT JOIN sys_dict_data f ON f.dict_type = 'attendStatus' AND k.attendStatus = f.dict_value) g SET h.normalAttendTimes = g.normalAttendTimes , -- 正常考勤次数 h.isAbsenteeism = g.isAbsenteeism, -- 是否缺勤 -- h.absenceTime, -- 缺勤时长(时) h.lateTimes = g.lateTimes, -- 迟到次数 h.totalLateTime = g.totalLateTime , -- 总迟到时间(分钟) h.leaveEarlyTimes = g.leaveEarlyTimes, -- 早退次数 h.totalLeaveEarlyTime = g.totalLeaveEarlyTime, -- 总早退时间(分钟) h.missCardTimes = g.missCardTimes, -- 缺卡次数 -- h.workHours, -- 总工时(时) h.firstRoundOnWork = IF(g.firstRoundOnWork IS NULL, h.firstRoundOnWork, g.firstRoundOnWork), -- 第一轮上班打卡结果 h.firstRoundOffWork = IF(g.firstRoundOffWork IS NULL, h.firstRoundOffWork, g.firstRoundOffWork), -- 第一轮下班打卡结果 h.secondRoundOnWork = IF(g.secondRoundOnWork IS NULL, h.secondRoundOnWork, g.secondRoundOnWork), -- 第二轮上班打卡结果 h.secondRoundOffWork = IF(g.secondRoundOffWork IS NULL, h.secondRoundOffWork, g.secondRoundOffWork) , -- 第二轮下班打卡结果 h.thirdRoundOnWork = IF(g.thirdRoundOnWork IS NULL, h.thirdRoundOnWork, g.thirdRoundOnWork), -- 第三轮上班打卡结果 h.thirdRoundOffWork = IF(g.thirdRoundOffWork IS NULL, h.thirdRoundOffWork, g.thirdRoundOffWork), -- 第三轮下班打卡结果 h.fourthRoundOnWork = IF(g.fourthRoundOnWork IS NULL, h.fourthRoundOnWork, g.fourthRoundOnWork), -- 第四轮上班打卡结果 h.fourthRoundOffWork = IF(g.fourthRoundOffWork IS NULL, h.fourthRoundOffWork, g.fourthRoundOffWork), -- 第四轮下班打卡结果 h.fifthRoundOnWork = IF(g.fifthRoundOnWork IS NULL, h.fifthRoundOnWork, g.fifthRoundOnWork), -- 第五轮上班打卡结果 h.fifthRoundOffWork = IF(g.fifthRoundOffWork IS NULL,h.fifthRoundOffWork, g.fifthRoundOffWork), -- 第五轮下班打卡结果 h.updateTime = NOW(), h.updateBy = v_perId WHERE h.perId = v_perId AND h.attendDate = v_beforeYestoday AND h.comTaskId = v_comTaskId; -- 5.2 更新缺勤情况 （缺勤时长） UPDATE count_per_task_date SET absenceTime = (SELECT IFNULL(TRUNCATE(SUM(TIMESTAMPDIFF(MINUTE,CONCAT(v_beforeYestoday,' ',e.startWorkTime), IF(e.isEndNextDay = '1', CONCAT(DATE_ADD(v_beforeYestoday,INTERVAL 1 DAY),' ',e.endWorkTime), CONCAT(v_beforeYestoday,' ',e.endWorkTime))))/60,2),0) -- 缺勤时长 FROM com_task a , com_task_schedul b , com_shift d , com_shift_rule e WHERE a.comTaskId = b.comTaskId AND b.isRest = '0' -- 排除休息 AND b.shiftId = d.shiftId AND d.shiftId = e.shiftId AND b.perId = v_perId AND b.schedulDate = prm_date AND a.comTaskId = v_comTaskId), workHours = 0 WHERE perId = v_perId AND attendDate = v_beforeYestoday AND comTaskId = v_comTaskId AND isAbsenteeism = '1'; -- 5.3 更新非缺勤情况 （工作时长） UPDATE count_per_task_date SET workHours = (SELECT IFNULL(TRUNCATE(SUM(CASE WHEN n.onDutyTime IS NOT NULL AND n.offDutyTime IS NOT NULL THEN IF(TIMESTAMPDIFF(MINUTE, GREATEST(n.onDutyTime,n.attendStartTime), LEAST(n.offDutyTime,n.attendEndTime))<0, 0, TIMESTAMPDIFF(MINUTE, GREATEST(n.onDutyTime,n.attendStartTime), -- 上班开始时间取考勤时间和打卡时间后面的时间 LEAST(n.offDutyTime,n.attendEndTime))) -- 下班结束时间取考勤时间和打卡时间前面的时间 ELSE 0 END)/60,2),0) -- 工作时长 FROM (SELECT MAX(IF(a.clockInType = '1',a.setClockInTime,null)) AS attendStartTime, -- 上班设置的时间 MAX(IF(a.clockInType = '2',a.setClockInTime,null)) AS attendEndTime, -- 下班设置的时间 MAX(IF(a.clockInType = '1',a.clockInTime,null)) AS onDutyTime, -- 上班打卡时间 MAX(IF(a.clockInType = '2',a.clockInTime,null)) AS offDutyTime, -- 下班打卡时间 MAX(IF(a.clockInType = '1',a.attendStatus,null)) AS onDutyStatus, -- 上班状态 MAX(IF(a.clockInType = '2',a.attendStatus,null)) AS offDutyStatus -- 下班状态 FROM per_task_card_record a WHERE a.perId = v_perId AND a.clockInDate = prm_date AND a.comTaskId = v_comTaskId GROUP by a.clockInRound ) n), absenceTime = 0 WHERE perId = v_perId AND attendDate = v_beforeYestoday AND comTaskId = v_comTaskId AND isAbsenteeism = '0'; END LOOP; -- 关闭游标 CLOSE cur_beforeYesterdayAttendPer; -- 考勤统计执行记录 IF v_recordCount = 0 THEN INSERT INTO count_task_execute_record(attendDate, -- 考勤日期 startTime, -- 开始时间 endTime, -- 结束时间 timeCost, -- 耗时(秒) perNum) -- 考勤人数 SELECT prm_date AS attendDate, -- 考勤日期 v_startTime AS startTime, -- 开始时间 NOW() AS endTime, -- 结束时间 TIMESTAMPDIFF(SECOND,v_startTime,NOW()) AS timeCost, -- 耗时(秒) v_rowCount AS perNum -- 考勤人数 FROM DUAL ; END IF ; IF v_error = 1 THEN SET prm_success = '0' ; ROLLBACK; LEAVE label_pro ; END IF; END label_pro; IF v_error = 1 THEN SET prm_success = '0' ; ROLLBACK; ELSE SET prm_success = '1' ; COMMIT ; END IF ; END;