自动计算请假时长

最新推荐文章于 2024-05-21 14:43:26 发布

Timeless_2

最新推荐文章于 2024-05-21 14:43:26 发布

阅读量3.2k

点赞数 2

本文链接：https://blog.csdn.net/Timeless_2/article/details/80175282

版权

MYSQL请假时长计算：
请假主要分为3种情况
情况一：在同一天并且同时是上午或下午
情况二：在同一天但开始在上午，结束在下午
情况三：跨天
计算：
倘若我们正常上班时间：09:00-12:00 13:00-18:00，首先我们将开始时间小于9点的设置为09:00，结束时间大于18点的设置为18:00，将12:00-13:00的设置为13:00
对于情况一：我们只需用mysql的TIMESTAMPDIFF()方法，
TIMESTAMPDIFF(MINUTE ,start_time,end_time)/60
对于情况二：我们只需在情况一的基础上减去1个小时
情况三是最麻烦的，我们需要将节假日存储在一张表中，然后遍历出去节假日计算，

我们除去首末两天，因为中间天都是乘以8小时，首末两天需考虑情况一和情况二

首先我们的节假日表设计很简单只需存储一个节假日日期即可

然后我们就开始写存储过程

DELIMITER $$
USE `zoa`$$
DROP PROCEDURE IF EXISTS `leaveCount`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `leaveCount`(IN startTime VARCHAR(20), #输入参数起始时间、结束时间
IN endTIme VARCHAR(20),
OUT lengths INT)
COMMENT '计算请假时长'
BEGIN
#定义变量
DECLARE kday INT DEFAULT 0;
SET lengths = 0;
#如果起始时间在00:00 至 09:00 设置起始时间为09:00:00
IF startTime >= CONCAT(DATE(startTime),' 00:00:00') AND startTime < CONCAT(DATE(startTime),' 09:00:00') THEN
SET startTime = CONCAT(DATE(startTime),' 09:00:00');
END IF;
#如果结束时间在18:00 至 23:59 设置结束时间为18:00:00
IF endTime > CONCAT(DATE(endTime),' 18:00:00') AND endTime <= CONCAT(DATE(endTime),' 23:59:00') THEN
SET endTime = CONCAT(DATE(endTime),' 18:00:00');
END IF;
#如果起始时间在12:00 至 13:00 设置起始时间为13:00:00
IF startTime > CONCAT(DATE(startTime),' 12:00:00') AND startTime < CONCAT(DATE(startTime),' 13:00:00') THEN
SET startTime = CONCAT(DATE(startTime),' 13:00:00');
END IF;
#如果结束时间在12:00 至 13:00 设置结束时间为12:00:00
IF endTime > CONCAT(DATE(endTime),' 12:00:00') AND endTime < CONCAT(DATE(endTime),' 13:00:00') THEN
SET endTime = CONCAT(DATE(endTime),' 12:00:00');
END IF;
#如果起始时间在18:00 至 23:59 设置起始时间为第二天09:00:00（一般不会出现这种情况防止万一）
IF startTIme >= CONCAT(DATE(startTime),' 18:00:00') AND startTIme <= CONCAT(DATE(startTime),' 23:59:00') THEN
SET startTIme = CONCAT(DATE_ADD(DATE(startTime),INTERVAL 1 DAY),' 09:00:00');
END IF;
#如果结束时间在00:00 至 09:00 设置结束时间为前一天18:00:00 （一般不会出现这种情况防止万一）
IF endTime >= CONCAT(DATE(endTime),' 00:00:00') AND endTime <= CONCAT(DATE(endTime),' 09:00:00') THEN
SET endTime = CONCAT(DATE_SUB(DATE(endTime),INTERVAL 1 DAY),' 18:00:00');
END IF;
#起始结束时间一样
IF startTime = endTime THEN
SET lengths=0;
END IF;
#同一天
IF DATE(startTIme) = DATE(endTIme) THEN
#同一时段（同是上午或下午）
IF (startTime >= CONCAT(DATE(startTime),' 09:00:00') AND endTime <= CONCAT(DATE(endTime),' 12:00:00')) || (startTime >= CONCAT(DATE(startTime),' 13:00:00') AND endTime <= CONCAT(DATE(endTime),' 18:00:00')) THEN
SET lengths = TIMESTAMPDIFF(MINUTE ,startTime,endTime)/60;
#不在同一时段
ELSE
SET lengths = TIMESTAMPDIFF(MINUTE ,startTime,endTime)/60-1;
END IF;
#跨天
ELSE
#判断开始日期是否为节假日
SELECT id INTO @id FROM tb_holiday_record WHERE holiday_day = DATE(startTime);
#若id为空则说明开始日期不是节假日
IF (@id IS NULL) THEN
#判断时段同一时段（同是上午或下午）
IF (startTime >= CONCAT(DATE(startTime),' 09:00:00') AND endTime <= CONCAT(DATE(endTime),' 12:00:00')) || (startTime >= CONCAT(DATE(startTime),' 13:00:00') AND endTime <= CONCAT(DATE(endTime),' 18:00:00')) THEN
SET lengths = lengths + TIMESTAMPDIFF(MINUTE ,startTime,endTime)/60;
#不在同一时段
ELSE
SET lengths = lengths + TIMESTAMPDIFF(MINUTE ,startTime,endTime)/60-1;
END IF;
END IF;
#判断结束日期是否是节假日
SELECT id INTO @id FROM tb_holiday_record WHERE holiday_day = DATE(startTime);
#若id为空则说明结束日期不是节假日
IF (@id IS NULL) THEN
#判断时段同一时段（同是上午或下午）
IF (startTime >= CONCAT(DATE(startTime),' 09:00:00') AND endTime <= CONCAT(DATE(endTime),' 12:00:00')) || (startTime >= CONCAT(DATE(startTime),' 13:00:00') AND endTime <= CONCAT(DATE(endTime),' 18:00:00')) THEN
SET lengths = TIMESTAMPDIFF(MINUTE ,startTime,endTime)/60;
#不在同一时段
ELSE
SET lengths = TIMESTAMPDIFF(MINUTE ,startTime,endTime)/60-1;
END IF;
END IF;

WHILE DATE(startTime)< DATE(endTime) DO
SELECT id INTO @id FROM tb_holiday_record WHERE holiday_day = DATE(startTime);
#若id不为空则说明该天是节假日
IF (@id IS NOT NULL) THEN
SET kday = kday + 1;
END IF;
SET startTime = CONCAT(DATE_ADD(DATE(startTime),INTERVAL 1 DAY),' 09:00:00');
END WHILE;
SET lengths = lengths +kday*8;