计算工作时长，排除双休、及午休时间

计算工作时长，排除双休、及午休时间

DROP PROCEDURE IF EXISTS CalculateActualTime;
DELIMITER //

CREATE PROCEDURE CalculateActualTime(
       IN workStartTime TIME, ## 工作开始时间 
    IN workEndTime TIME, ## 工作结束时间
    IN breakStartTime TIME, ## 休息开始时间
    IN breakEndTime TIME, ## 休息结束时间
    IN actualStartTime DATETIME, ## 任务开始时间
    IN actualEndTime DATETIME, ## 任务实际结束时间(如果实际结束时间为空就取当前时间)
    IN workPattern INT ## 	单双休模式 (0单休 1双休)
)
BEGIN
    DECLARE workHoursPerDay INT;
    DECLARE totalWorkHours INT;
    DECLARE totalWorkSeconds INT;
    DECLARE totalRestDays INT;
    DECLARE currentDay DATETIME;

    DECLARE start_time TIME;
    DECLARE end_time TIME;


    -- 计算一天的工作时长
    SET workHoursPerDay = ABS(TIMESTAMPDIFF(hour , workStartTime, workEndTime)) -
                          ABS(TIMESTAMPDIFF(hour , breakStartTime, breakEndTime));

    -- 如果实际结束时间为空，取当前时间
    IF actualEndTime IS NULL THEN
        SET actualEndTime = NOW();
    END IF;

    -- 初始化当前日期为实际开始日期
    SET currentDay = actualStartTime;

    -- 计算总的工作秒数
    SET totalWorkHours = 0;

    -- 当前日期增加一天
    SET currentDay = DATE_ADD(currentDay, INTERVAL 1 DAY);

    -- 循环遍历每一天，计算工作时长
    WHILE date_format(currentDay, '%Y-%m-%d') < date_format(actualEndTime, '%Y-%m-%d') DO
        -- 判断当前日期是否为休息日
        IF (workPattern = 0 AND DAYOFWEEK(currentDay) = 1) OR
           (workPattern = 1 AND DAYOFWEEK(currentDay) IN (1, 7)) THEN
            SET totalRestDays = totalRestDays + 1;
        ELSE
            SET totalWorkHours = totalWorkHours + workHoursPerDay;
        END IF;

        -- 当前日期增加一天
        SET currentDay = DATE_ADD(currentDay, INTERVAL 1 DAY);
    END WHILE;



    -- 如果实际开始时间和实际结束时间在同一天
    IF date_format(actualStartTime,'%Y-%m-%d') = date_format(actualEndTime,'%Y-%m-%d') THEN
        SET start_time = IF(time(actualStartTime) NOT BETWEEN breakStartTime AND breakEndTime,
                             IF(time(actualStartTime) < workStartTime, workStartTime, time(actualStartTime)),
                             breakEndTime);
        SET end_time = IF(time(actualEndTime) NOT BETWEEN breakStartTime AND breakEndTime,
                           IF(time(actualEndTime) > workEndTime, workEndTime, time(actualEndTime)),
                           breakStartTime);
        SET totalWorkSeconds = ABS(TIMESTAMPDIFF(SECOND, start_time, end_time));
    -- 如果实际开始时间和实际结束时间不在同一天
    ELSE
        -- 计算开始时间当天的工作时长（工作结束时间 - 实际开始时间）排除掉休息即午休时间
        -- 如果任务计划开始时间小于午休开始时间说明处于上午时间段 需要排除掉午休时间
        IF time(actualStartTime) <  breakStartTime THEN
            SET totalWorkSeconds = ABS(TIMESTAMPDIFF(SECOND, workEndTime,
                                                 IF(time(actualStartTime) <  workStartTime ,
                                                    workStartTime, time(actualStartTime)))) -
                                   ABS(TIMESTAMPDIFF(SECOND, breakStartTime, breakEndTime));
        ELSE
            SET totalWorkSeconds = ABS(TIMESTAMPDIFF(SECOND, workEndTime,
                                                 IF(time(actualStartTime) <  breakEndTime ,
                                                    breakEndTime, time(actualStartTime))));
        END IF;
        -- 计算结束时间当天的工作时长（实际结束时间 - 工作开始时间）排除掉休息即午休时间
        -- 如果实际结束时间大于休息结束时间说明在下午 需要排除掉午休时间
        IF  time(actualEndTime) > breakEndTime  THEN
            SET totalWorkSeconds = totalWorkSeconds
                                       + ABS(TIMESTAMPDIFF(SECOND, IF( time(actualEndTime) > workEndTime,
                                                                  workEndTime,  time(actualEndTime)),
                                                       workStartTime))
                - ABS(TIMESTAMPDIFF(SECOND, breakStartTime, breakEndTime));
        ELSE
            SET totalWorkSeconds = totalWorkSeconds
                                       + ABS(TIMESTAMPDIFF(SECOND, IF( time(actualEndTime) > breakStartTime,
                                                                  breakStartTime,  time(actualEndTime)),
                                                       workStartTime));
        END IF;

    END IF;

    -- 返回结果
    SELECT CONCAT(
    IF((totalWorkSeconds + totalWorkHours * 3600) >= 0,
        CONCAT(FLOOR((totalWorkSeconds + totalWorkHours * 3600) / 3600 / workHoursPerDay), 'd'),
        CONCAT('-', FLOOR(ABS(totalWorkSeconds + totalWorkHours * 3600) / 3600 / workHoursPerDay), 'd')),
    FLOOR((totalWorkSeconds + totalWorkHours * 3600) % (workHoursPerDay * 3600) / 3600), 'h',
    FLOOR((totalWorkSeconds + totalWorkHours * 3600) % 3600 / 60), 'm',
    (totalWorkSeconds + totalWorkHours * 3600) % 60, 's') AS result;

END //

DELIMITER ;

call CalculateActualTime('9:00', '18:00', '12:00', '13:00', '2023-11-10 15:00', null, 1);