存储过程计算两个时间段的请假天数
我的第一个完整存储过程 /散花
根据日历计算两个时间段之间的请假天数。涉及游标，循环的基本使用，记录下。
调用:
CALL pro_get_usedtime_bycalendar('日历1','2019-09-01 9:00:00','2019-09-27 17:30:00',@total)
DELIMITER $$ USE `s2cloud`$$ DROP PROCEDURE IF EXISTS `pro_get_usedtime_bycalendar`$$ CREATE DEFINER=`sqluser`@`%` PROCEDURE `pro_get_usedtime_bycalendar`(IN _calendar_name VARCHAR(50), IN _startDate DATETIME, IN _endDate DATETIME, OUT _totalUsedTime DOUBLE ) BEGIN ### -- 总用(总用天数+总用小时/一天的工作时长) DECLARE totalUsedDays DOUBLE DEFAULT 0.0 ; -- 总用天数 DECLARE totalUsedHours DOUBLE DEFAULT 0.0 ; -- 总用小时 DECLARE oneDayNeedWorkHours DOUBLE DEFAULT 0.0 ; -- 一天的工作时长(小时) ##定义用到得变量 ######WorkingDay的变量 DECLARE workingDayId VARCHAR(50) DEFAULT '' ; -- 工作日objectId,对应t_working_timespan的parentObjectId DECLARE workingDayCurrentDate DATETIME; -- 工作日当前日期 DECLARE wkDayIndex INT DEFAULT 0; -- 定义workingDay循环判断变量 ######WorkingTimespan的变量 DECLARE startTimeHour INT DEFAULT 0; -- 开始时间(小时) DECLARE startTimeMinute INT DEFAULT 0; -- 开始时间(分) DECLARE endTimeHour INT DEFAULT 0; -- 结束时间(小时) DECLARE endTimeMinute INT DEFAULT 0; -- 结束时间(分) DECLARE startTimeDate DATETIME; -- 开始日期(年-月-日 时:分) DECLARE endTimeDate DATETIME; -- 结束日期(年-月-日 时:分) DECLARE wkTiemSpanIndex INT DEFAULT 0; -- 定义WorkingTimespan循环判断变量 ## 定义workingDay游标：工作日表 DECLARE cur_workingday CURSOR FOR SELECT ObjectID,CurrentDate FROM t_working_day WHERE CalendarId IN (SELECT ObjectID FROM t_working_calendar WHERE DisplayName=_calendar_name) AND isworkingDay=1 AND IF(IFNULL(_startDate,'')='' OR IFNULL(_endDate,'')='' , 1=0 ,(CurrentDate>=DATE_FORMAT(_startDate,'%Y-%m-%d') AND CurrentDate <=_endDate)) ORDER BY CurrentDate ASC; ### 循环赋初始值 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET wkDayIndex=1; ##前提_startDate，_endDate不能为空 IF IFNULL(_startDate,'')<>'' AND IFNULL(_endDate,'')<>'' THEN BEGIN ### 打开 OPEN cur_workingday; ## 赋值 FETCH cur_workingday INTO workingDayId,workingDayCurrentDate; ###循环判断 WHILE wkDayIndex <> 1 DO BEGIN ##重置变量 SET startTimeDate=NULL; SET endTimeDate=NULL; SET @startDiff=DATEDIFF(_startDate,workingDayCurrentDate); SET @endDiff=DATEDIFF(_endDate,workingDayCurrentDate); ## 请假开始 OR 请假结束那天 IF @startDiff = 0 OR (@startDiff<0 AND @endDiff=0) THEN BEGIN -- 遍历 t_working_day 工作日 -- 定义游标WorkingTimespan以及结束标识 DECLARE cur_workingTimeSpan CURSOR FOR SELECT ts.StartTimeHour,ts.StartTimeMinute,ts.EndTimeHour,ts.EndTimeMinute FROM t_working_timespan ts WHERE ts.ParentObjectID=workingDayId; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET wkTiemSpanIndex=1; #结束标识 ## 打开游标 OPEN cur_workingTimeSpan; ## 赋值 FETCH cur_workingTimeSpan INTO startTimeHour,startTimeMinute,endTimeHour,endTimeMinute; SET wkTiemSpanIndex=0; -- 重置timeSpan循环标识 -- set @oneDayRealWorkHours=0; -- 工作日当天实际工作的小时数 (小时数/天工作总小时 =工作天数) SET oneDayNeedWorkHours=0; -- 工作日当天需要工作的小时数 (小时数/天工作总小时 =工作天数) WHILE wkTiemSpanIndex <> 1 DO BEGIN -- 遍历 t_working_timeSpan 时间段 -- SELECT startTimeHour,startTimeMinute,endTimeHour,endTimeMinute; ## SET startTimeDate=DATE_ADD(DATE_ADD(workingDayCurrentDate, INTERVAL startTimeHour HOUR), INTERVAL startTimeMinute MINUTE); SET endTimeDate=DATE_ADD(DATE_ADD(workingDayCurrentDate, INTERVAL endTimeHour HOUR), INTERVAL endTimeMinute MINUTE); SET @workTimeSpanDiff=TIMEDIFF(endTimeDate,startTimeDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 SET @workTimeSpanHours=HOUR(@workTimeSpanDiff)+ MINUTE(@workTimeSpanDiff)/60; -- 转成小时小时数 SET oneDayNeedWorkHours=oneDayNeedWorkHours+@workTimeSpanHours; ##请假开始那天 IF @startDiff = 0 THEN BEGIN IF DATEDIFF(_endDate,_startDate)<>0 THEN ##开始和结束不为同一天 BEGIN IF TIMEDIFF(_startDate,startTimeDate)<=0 THEN ##开始时间前请假 SET totalUsedHours=totalUsedHours+@workTimeSpanHours; ELSEIF TIMEDIFF(_startDate,startTimeDate)>0 AND TIMEDIFF(_startDate,endTimeDate)<0 THEN ##开始时间后请假 SET @tempWorkDiff=TIMEDIFF(endTimeDate,_startDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 SET @tempWorkDiffHours=HOUR(@tempWorkDiff)+ MINUTE(@tempWorkDiff)/60; -- 转成小时小时数 SET totalUsedHours=totalUsedHours+@tempWorkDiffHours; END IF; END; ELSEIF DATEDIFF(_endDate,_startDate)=0 THEN ##开始和结束为同一天 BEGIN SET @tempWorkDiff=NULL; IF TIMEDIFF(_startDate,startTimeDate)<=0 AND TIMEDIFF(_endDate,startTimeDate)>=0 AND TIMEDIFF(_endDate,endTimeDate)<=0 THEN SET @tempWorkDiff=TIMEDIFF(_endDate,startTimeDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 ELSEIF TIMEDIFF(_startDate,startTimeDate)<=0 AND TIMEDIFF(_endDate,startTimeDate)>=0 AND TIMEDIFF(_endDate,endTimeDate)>=0 THEN SET @tempWorkDiff=TIMEDIFF(endTimeDate,startTimeDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 ELSEIF TIMEDIFF(_startDate,startTimeDate)>=0 AND TIMEDIFF(_endDate,startTimeDate)>=0 AND TIMEDIFF(_endDate,endTimeDate)<=0 THEN SET @tempWorkDiff=TIMEDIFF(_endDate,_startDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 ELSEIF TIMEDIFF(_startDate,startTimeDate)>=0 AND TIMEDIFF(_startDate,endTimeDate)<=0 AND TIMEDIFF(_endDate,endTimeDate)>=0 THEN SET @tempWorkDiff=TIMEDIFF(endTimeDate,_startDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 END IF; IF @tempWorkDiff<>NULL THEN SET @tempWorkDiffHours=HOUR(@tempWorkDiff)+ MINUTE(@tempWorkDiff)/60; -- 转成小时小时数 SET totalUsedHours=totalUsedHours+@tempWorkDiffHours; END IF; END; END IF; END; ##请假结束那天 ELSEIF (@startDiff<0 AND @endDiff=0) THEN BEGIN IF TIMEDIFF(_endDate,startTimeDate)>=0 AND TIMEDIFF(_endDate,endTimeDate)<=0 THEN ##请假结束在下班前，加中间的小时数 SET @tempWorkDiff=TIMEDIFF(_endDate,startTimeDate); -- 12:00-9:30 =03:30 3小时30分钟=3.5小时 SET @tempWorkDiffHours=HOUR(@tempWorkDiff)+ MINUTE(@tempWorkDiff)/60; -- 转成小时小时数 SET totalUsedHours=totalUsedHours+@tempWorkDiffHours; ELSEIF TIMEDIFF(_endDate,startTimeDate)>0 THEN ##请假结束在下班后,加这个时间段的workTimeSpan SET totalUsedHours=totalUsedHours+@workTimeSpanHours; END IF; END; END IF; END; FETCH cur_workingTimeSpan INTO startTimeHour,startTimeMinute,endTimeHour,endTimeMinute; END WHILE; CLOSE cur_workingTimeSpan;-- 关闭游标 END; ELSEIF @startDiff<0 AND @endDiff>0 THEN ##跨整天，天数+1 BEGIN SET totalUsedDays=totalUsedDays+1; END; END IF; END; ## 赋值下一个游标 FETCH cur_workingday INTO workingDayId,workingDayCurrentDate; END WHILE; ## 关闭 CLOSE cur_workingday; END; END IF; ## 结果： SET @totalUsed=totalUsedDays; ###将工作小时数折算成天 IF oneDayNeedWorkHours>0 AND totalUsedHours>0 THEN SET @totalUsed=@totalUsed+(totalUsedHours/oneDayNeedWorkHours); END IF; ## 精度为0.5 ：不足0.5进到0.5,大于0.5进1 SET @totalUsed=CEILING(@totalUsed / 0.5) * 0.5; ## for log SELECT oneDayNeedWorkHours , totalUsedDays AS DAY,totalUsedHours AS HOUR, @totalUsed AS total; SET _totalUsedTime=@totalUsed; END$$ DELIMITER ;
