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 _totalUsedTimeDOUBLE)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 FROMt_working_dayWHERE 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### 打开OPENcur_workingday;
## 赋值FETCH cur_workingday INTOworkingDayId,workingDayCurrentDate;
###循环判断WHILE wkDayIndex <> 1DOBEGIN##重置变量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; #结束标识
## 打开游标OPENcur_workingTimeSpan;
## 赋值FETCH cur_workingTimeSpan INTOstartTimeHour,startTimeMinute,endTimeHour,endTimeMinute;SET wkTiemSpanIndex=0; --重置timeSpan循环标识
--set @oneDayRealWorkHours=0; -- 工作日当天实际工作的小时数 (小时数/天工作总小时 =工作天数)
SET oneDayNeedWorkHours=0; --工作日当天需要工作的小时数 (小时数/天工作总小时 =工作天数)
WHILE wkTiemSpanIndex <> 1DOBEGIN
--遍历 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;
ELSEIFDATEDIFF(_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##请假结束在下班后,加这个时间段的workTimeSpanSET totalUsedHours=totalUsedHours+@workTimeSpanHours;END IF;END;END IF;END;FETCH cur_workingTimeSpan INTOstartTimeHour,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 INTOworkingDayId,workingDayCurrentDate;END WHILE;
## 关闭CLOSEcur_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进1SET @totalUsed=CEILING(@totalUsed / 0.5) * 0.5;
##for log
SELECT oneDayNeedWorkHours , totalUsedDays AS DAY,totalUsedHours AS HOUR, @totalUsed AStotal;SET _totalUsedTime=@totalUsed;END$$
DELIMITER ;