

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;

