php请假时间不能有记录,存储过程计算两个时间段的请假天数

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 ;