SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for hq_date 日期表
-- ----------------------------
DROP TABLE IF EXISTS `hq_date`;
CREATE TABLE `hq_date` (
`id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`date` date NOT NULL COMMENT '日期',
`day_of_week` tinyint NULL DEFAULT NULL COMMENT '星期几（周日开始，1-7）',
`holiday` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '是否为假期',
`name_of_holiday` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '假期名称',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `ix_hq_date_date`(`date`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5001 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '日期表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Function structure for CalcOverDays 计算超期天数
-- ----------------------------
DROP FUNCTION IF EXISTS `CalcOverDays`;
delimiter ;;
CREATE FUNCTION `CalcOverDays`(`begindate` date,`enddate` date)
RETURNS int(11)
BEGIN
DECLARE workdays INT DEFAULT 0; -- 工作日天数
DECLARE min_date date DEFAULT begindate; -- 日期表最小日期
DECLARE max_date date DEFAULT enddate; -- 日期表最大日期
DECLARE weekcnt INT DEFAULT 0; -- 两日期的周数差
DECLARE beginweek INT DEFAULT 1; -- 开始日期的星期
DECLARE endweek INT DEFAULT 1; -- 结束日期的星期
IF begindate < enddate THEN
SELECT min(date),max(date) into min_date,max_date FROM hq_date;
IF begindate < min_date OR enddate > max_date THEN
IF begindate < min_date THEN
SET beginweek = DAYOFWEEK(begindate);
SET endweek = DAYOFWEEK(IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY)));
SET weekcnt = timestampdiff(week,begindate,IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY)));
IF weekcnt = 0 THEN
SET workdays = workdays + endweek - beginweek + 1
- case
when (beginweek > 1 and endweek < 7) then 0
when (beginweek = 1 and endweek =7) then 2
else 1
end;
ELSE
SET workdays = workdays + (weekcnt - 1) * 5
+ case
when beginweek = 1 then 5
when beginweek = 7 then 0
else 7 - beginweek
end
+ case
when endweek = 1 then 0
when endweek = 7 then 5
else endweek - 1
end;
END IF;
END IF;
IF enddate > max_date THEN
SET beginweek = DAYOFWEEK(IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY)));
SET endweek = DAYOFWEEK(enddate);
SET weekcnt = timestampdiff(week,IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY)),enddate);
IF weekcnt = 0 THEN
SET workdays = workdays + endweek - beginweek + 1
- case
when (beginweek > 1 and endweek < 7) then 0
when (beginweek = 1 and endweek =7) then 2
else 1
end;
ELSE
SET workdays = workdays + (weekcnt - 1) * 5
+ case
when beginweek = 1 then 5
when beginweek = 7 then 0
else 7 - beginweek
end
+ case
when endweek = 1 then 0
when endweek = 7 then 5
else endweek - 1
end;
END IF;
END IF;
END IF;
SELECT workdays + count(1) into workdays FROM hq_date WHERE date>=begindate AND date<enddate AND holiday='0';
END IF;
RETURN workdays;
END
;;
delimiter ;
-- ----------------------------
-- Function structure for CalcWorkDays 计算工作日天数
-- ----------------------------
DROP FUNCTION IF EXISTS `CalcWorkDays`;
delimiter ;;
CREATE FUNCTION `CalcWorkDays`(`begindate` date,`enddate` date)
RETURNS int(11)
BEGIN
DECLARE workdays INT DEFAULT 0; -- 工作日天数
DECLARE min_date date DEFAULT begindate; -- 日期表最小日期
DECLARE max_date date DEFAULT enddate; -- 日期表最大日期
DECLARE weekcnt INT DEFAULT 0; -- 两日期的周数差
DECLARE beginweek INT DEFAULT 1; -- 开始日期的星期
DECLARE endweek INT DEFAULT 1; -- 结束日期的星期
IF begindate <= enddate THEN
SELECT min(date),max(date) into min_date,max_date FROM hq_date;
IF begindate < min_date OR enddate > max_date THEN
IF begindate < min_date THEN
SET beginweek = DAYOFWEEK(begindate);
SET endweek = DAYOFWEEK(IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY)));
SET weekcnt = timestampdiff(week,begindate,IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY)));
IF weekcnt = 0 THEN
SET workdays = workdays + endweek - beginweek + 1
- case
when (beginweek > 1 and endweek < 7) then 0
when (beginweek = 1 and endweek =7) then 2
else 1
end;
ELSE
SET workdays = workdays + (weekcnt - 1) * 5
+ case
when beginweek = 1 then 5
when beginweek = 7 then 0
else 7 - beginweek
end
+ case
when endweek = 1 then 0
when endweek = 7 then 5
else endweek - 1
end;
END IF;
END IF;
IF enddate > max_date THEN
SET beginweek = DAYOFWEEK(IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY)));
SET endweek = DAYOFWEEK(enddate);
SET weekcnt = timestampdiff(week,IF(begindate > max_date, begindate, DATE_ADD(max_date, INTERVAL 1 DAY)),enddate);
IF weekcnt = 0 THEN
SET workdays = workdays + endweek - beginweek + 1
- case
when (beginweek > 1 and endweek < 7) then 0
when (beginweek = 1 and endweek =7) then 2
else 1
end;
ELSE
SET workdays = workdays + (weekcnt - 1) * 5
+ case
when beginweek = 1 then 5
when beginweek = 7 then 0
else 7 - beginweek
end
+ case
when endweek = 1 then 0
when endweek = 7 then 5
else endweek - 1
end;
END IF;
END IF;
END IF;
SELECT workdays + count(1) into workdays FROM hq_date WHERE date>=begindate AND date<=enddate AND holiday='0';
END IF;
RETURN workdays;
END
;;
delimiter ;
-- ----------------------------
-- Function structure for GetBeginWorkDate 计算最迟开始日期
-- ----------------------------
DROP FUNCTION IF EXISTS `GetBeginWorkDate`;
delimiter ;;
CREATE FUNCTION `GetBeginWorkDate`(`deadline` date,`estimate` float)
RETURNS date
BEGIN
DECLARE workdays INT DEFAULT 0; -- 工作日天数
DECLARE dayexsists INT DEFAULT 1; -- 日期表是否存在
DECLARE beginday date DEFAULT deadline; -- 最迟开始工作日期
DECLARE isholiday CHAR DEFAULT '0'; -- 是否为假日
SET workdays = CEILING( estimate / 8 );
WHILE workdays > 0 DO
SELECT holiday, count(1) into isholiday, dayexsists FROM hq_date WHERE date = beginday;
IF dayexsists = 0 THEN
-- 日期表不存在的，周一到周五为工作日，周六周日为双休日
IF DAYOFWEEK(beginday) IN (1,7) = 0 THEN
SET workdays = workdays - 1;
END IF;
ELSE
-- 日期表存在的，以日期表的假日为准
IF isholiday = '0' THEN
SET workdays = workdays - 1;
END IF;
END IF;
IF workdays > 0 THEN
SET beginday = DATE_SUB( beginday, INTERVAL 1 DAY);
END IF;
END WHILE;
RETURN beginday;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
码农之家
我的工作记录