220 lines
15 KiB
SQL
220 lines
15 KiB
SQL
-- ============================================================
|
||
-- 平台端排班模板（全局预制模板 + 自定义模板）
|
||
--
|
||
-- 执行顺序：
|
||
-- 1) 本脚本：创建 2 张模板表并写入 6 个系统预置模板
|
||
-- 2) alter_oa_schedule_plan_template.sql：租户排班方案增加模板/周期字段
|
||
-- 3) platform_sched_template_menu.sql：注册平台端「排班模板」菜单
|
||
-- 4) create_oa_scheduling.sql / oa_scheduling_menus.sql（若尚未执行）
|
||
--
|
||
-- 说明：
|
||
-- 1. 模板是全局数据（不按租户隔离），只有平台端可维护；
|
||
-- 系统预置模板（is_system=1）由平台锁定：不可编辑、不可删除、不可停用。
|
||
-- 2. 租户端只能「调用」模板：调用时把模板规则快照到自己的排班方案
|
||
-- （yz_backend_oa_schedule_plan.pattern），之后在租户端随意修改，
|
||
-- 模板本身的后续调整不会再影响已保存的方案。
|
||
-- 3. align_mode：1=按自然周（双休/单休/大小周） 2=从开始日滚动（三班倒/做一休一）
|
||
-- 3=按自然月（月休，按月计算休息日）
|
||
-- 4. day_index 表示周期内第几天，从 0 开始；按自然周时 0=周一、6=周日。
|
||
-- 5. 本脚本不使用会话变量 / PREPARE，可整段执行也可逐条执行，重复执行安全。
|
||
-- ============================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `yz_platform_sched_tpl` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`code` varchar(50) NOT NULL DEFAULT '' COMMENT '模板编码（系统模板固定）',
|
||
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '模板名称',
|
||
`template_type` tinyint NOT NULL DEFAULT 9 COMMENT '1双休 2单休 3大小周 4月休 5三班倒 6做一休一 9自定义',
|
||
`align_mode` tinyint NOT NULL DEFAULT 1 COMMENT '1按自然周 2按开始日滚动 3按自然月',
|
||
`cycle_days` smallint NOT NULL DEFAULT 7 COMMENT '周期天数（按自然月时为0）',
|
||
`week_offset` tinyint NOT NULL DEFAULT 0 COMMENT '大小周起始周偏移：0=开始周为第1周 1=第2周',
|
||
`month_rest_days` tinyint NOT NULL DEFAULT 0 COMMENT '月休：每月休息天数',
|
||
`month_rest_weekday` tinyint NOT NULL DEFAULT 0 COMMENT '月休固定休息周几（1-7，0=不固定）',
|
||
`summary` varchar(255) NOT NULL DEFAULT '' COMMENT '规则摘要（列表展示）',
|
||
`is_system` tinyint NOT NULL DEFAULT 0 COMMENT '1=系统预置（只读，禁止编辑/删除）',
|
||
`status` tinyint NOT NULL DEFAULT 1 COMMENT '1启用 0停用',
|
||
`sort` int NOT NULL DEFAULT 0 COMMENT '排序',
|
||
`creater` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '删除时间（软删除）',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_code` (`code`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台端排班模板（全局）';
|
||
|
||
CREATE TABLE IF NOT EXISTS `yz_platform_sched_tpl_day` (
|
||
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`template_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '模板ID',
|
||
`day_index` smallint NOT NULL DEFAULT 0 COMMENT '周期内第几天，从0开始',
|
||
`day_type` tinyint NOT NULL DEFAULT 1 COMMENT '1上班 2休息',
|
||
`shift_name` varchar(50) NOT NULL DEFAULT '' COMMENT '班次名称',
|
||
`shift_short` varchar(20) NOT NULL DEFAULT '' COMMENT '班次简称',
|
||
`color` varchar(20) NOT NULL DEFAULT '#409EFF' COMMENT '班次颜色',
|
||
`work_start` varchar(8) NOT NULL DEFAULT '' COMMENT '上班时间 HH:mm',
|
||
`work_end` varchar(8) NOT NULL DEFAULT '' COMMENT '下班时间 HH:mm',
|
||
`rest_start` varchar(8) NOT NULL DEFAULT '' COMMENT '休息开始 HH:mm',
|
||
`rest_end` varchar(8) NOT NULL DEFAULT '' COMMENT '休息结束 HH:mm',
|
||
`work_hours` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT '工时（小时）',
|
||
`remark` varchar(100) NOT NULL DEFAULT '' COMMENT '备注',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_tpl_day` (`template_id`,`day_index`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台端排班模板-周期每日规则';
|
||
|
||
-- ============================================================
|
||
-- 系统预置模板（6 个）
|
||
-- ============================================================
|
||
|
||
-- 1. 双休：周一至周五上班，周六日休息
|
||
INSERT INTO yz_platform_sched_tpl
|
||
(code, name, template_type, align_mode, cycle_days, week_offset, month_rest_days, month_rest_weekday, summary, is_system, status, sort, creater)
|
||
SELECT 'double_weekend', '双休', 1, 1, 7, 0, 0, 0, '周一至周五上班 09:00-18:00，周六日休息', 1, 1, 10, 'system'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_platform_sched_tpl WHERE code = 'double_weekend' AND delete_time IS NULL) t);
|
||
|
||
INSERT INTO yz_platform_sched_tpl_day
|
||
(template_id, day_index, day_type, shift_name, shift_short, color, work_start, work_end, rest_start, rest_end, work_hours)
|
||
SELECT tpl.id, d.idx, d.day_type, d.shift_name, d.shift_short, d.color, d.work_start, d.work_end, d.rest_start, d.rest_end, d.work_hours
|
||
FROM (
|
||
SELECT 0 AS idx, 1 AS day_type, '白班' AS shift_name, '白' AS shift_short, '#409EFF' AS color, '09:00' AS work_start, '18:00' AS work_end, '12:00' AS rest_start, '13:30' AS rest_end, 7.50 AS work_hours
|
||
UNION ALL SELECT 1, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 2, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 3, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 4, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 5, 2, '', '', '', '', '', '', '', 0.00
|
||
UNION ALL SELECT 6, 2, '', '', '', '', '', '', '', 0.00
|
||
) d
|
||
CROSS JOIN (SELECT id FROM yz_platform_sched_tpl WHERE code = 'double_weekend' AND delete_time IS NULL LIMIT 1) tpl
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT template_id, day_index FROM yz_platform_sched_tpl_day) x
|
||
WHERE x.template_id = tpl.id AND x.day_index = d.idx
|
||
);
|
||
|
||
-- 2. 单休：周一至周六上班，周日休息
|
||
INSERT INTO yz_platform_sched_tpl
|
||
(code, name, template_type, align_mode, cycle_days, week_offset, month_rest_days, month_rest_weekday, summary, is_system, status, sort, creater)
|
||
SELECT 'single_weekend', '单休', 2, 1, 7, 0, 0, 0, '周一至周六上班 09:00-18:00，周日休息', 1, 1, 20, 'system'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_platform_sched_tpl WHERE code = 'single_weekend' AND delete_time IS NULL) t);
|
||
|
||
INSERT INTO yz_platform_sched_tpl_day
|
||
(template_id, day_index, day_type, shift_name, shift_short, color, work_start, work_end, rest_start, rest_end, work_hours)
|
||
SELECT tpl.id, d.idx, d.day_type, d.shift_name, d.shift_short, d.color, d.work_start, d.work_end, d.rest_start, d.rest_end, d.work_hours
|
||
FROM (
|
||
SELECT 0 AS idx, 1 AS day_type, '白班' AS shift_name, '白' AS shift_short, '#409EFF' AS color, '09:00' AS work_start, '18:00' AS work_end, '12:00' AS rest_start, '13:30' AS rest_end, 7.50 AS work_hours
|
||
UNION ALL SELECT 1, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 2, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 3, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 4, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 5, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 6, 2, '', '', '', '', '', '', '', 0.00
|
||
) d
|
||
CROSS JOIN (SELECT id FROM yz_platform_sched_tpl WHERE code = 'single_weekend' AND delete_time IS NULL LIMIT 1) tpl
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT template_id, day_index FROM yz_platform_sched_tpl_day) x
|
||
WHERE x.template_id = tpl.id AND x.day_index = d.idx
|
||
);
|
||
|
||
-- 3. 大小周：第 1 周双休，第 2 周单休（周六上班），14 天一个周期
|
||
INSERT INTO yz_platform_sched_tpl
|
||
(code, name, template_type, align_mode, cycle_days, week_offset, month_rest_days, month_rest_weekday, summary, is_system, status, sort, creater)
|
||
SELECT 'big_small_week', '大小周', 3, 1, 14, 0, 0, 0, '第 1 周双休、第 2 周单休（周六上班），14 天循环', 1, 1, 30, 'system'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_platform_sched_tpl WHERE code = 'big_small_week' AND delete_time IS NULL) t);
|
||
|
||
INSERT INTO yz_platform_sched_tpl_day
|
||
(template_id, day_index, day_type, shift_name, shift_short, color, work_start, work_end, rest_start, rest_end, work_hours)
|
||
SELECT tpl.id, d.idx, d.day_type, d.shift_name, d.shift_short, d.color, d.work_start, d.work_end, d.rest_start, d.rest_end, d.work_hours
|
||
FROM (
|
||
SELECT 0 AS idx, 1 AS day_type, '白班' AS shift_name, '白' AS shift_short, '#409EFF' AS color, '09:00' AS work_start, '18:00' AS work_end, '12:00' AS rest_start, '13:30' AS rest_end, 7.50 AS work_hours
|
||
UNION ALL SELECT 1, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 2, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 3, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 4, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 5, 2, '', '', '', '', '', '', '', 0.00
|
||
UNION ALL SELECT 6, 2, '', '', '', '', '', '', '', 0.00
|
||
UNION ALL SELECT 7, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 8, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 9, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 10, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 11, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 12, 1, '白班', '白', '#409EFF', '09:00', '18:00', '12:00', '13:30', 7.50
|
||
UNION ALL SELECT 13, 2, '', '', '', '', '', '', '', 0.00
|
||
) d
|
||
CROSS JOIN (SELECT id FROM yz_platform_sched_tpl WHERE code = 'big_small_week' AND delete_time IS NULL LIMIT 1) tpl
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT template_id, day_index FROM yz_platform_sched_tpl_day) x
|
||
WHERE x.template_id = tpl.id AND x.day_index = d.idx
|
||
);
|
||
|
||
-- 4. 月休 4 天：每月固定休 4 天，优先安排在周日（周末不足时从月末补齐）
|
||
INSERT INTO yz_platform_sched_tpl
|
||
(code, name, template_type, align_mode, cycle_days, week_offset, month_rest_days, month_rest_weekday, summary, is_system, status, sort, creater)
|
||
SELECT 'month_4_rest', '月休 4 天', 4, 3, 0, 0, 4, 7, '每月固定休 4 天，优先安排周日，遇 5 个周日只休前 4 个', 1, 1, 40, 'system'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_platform_sched_tpl WHERE code = 'month_4_rest' AND delete_time IS NULL) t);
|
||
|
||
INSERT INTO yz_platform_sched_tpl_day
|
||
(template_id, day_index, day_type, shift_name, shift_short, color, work_start, work_end, rest_start, rest_end, work_hours)
|
||
SELECT tpl.id, d.idx, d.day_type, d.shift_name, d.shift_short, d.color, d.work_start, d.work_end, d.rest_start, d.rest_end, d.work_hours
|
||
FROM (
|
||
SELECT 0 AS idx, 1 AS day_type, '白班' AS shift_name, '白' AS shift_short, '#409EFF' AS color, '09:00' AS work_start, '18:00' AS work_end, '12:00' AS rest_start, '13:30' AS rest_end, 7.50 AS work_hours
|
||
) d
|
||
CROSS JOIN (SELECT id FROM yz_platform_sched_tpl WHERE code = 'month_4_rest' AND delete_time IS NULL LIMIT 1) tpl
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT template_id, day_index FROM yz_platform_sched_tpl_day) x
|
||
WHERE x.template_id = tpl.id AND x.day_index = d.idx
|
||
);
|
||
|
||
-- 5. 三班倒：早班 -> 中班 -> 夜班，各 1 天循环
|
||
INSERT INTO yz_platform_sched_tpl
|
||
(code, name, template_type, align_mode, cycle_days, week_offset, month_rest_days, month_rest_weekday, summary, is_system, status, sort, creater)
|
||
SELECT 'three_shift', '三班倒', 5, 2, 3, 0, 0, 0, '早班 08:00-16:00 → 中班 16:00-24:00 → 夜班 00:00-08:00，3 天循环', 1, 1, 50, 'system'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_platform_sched_tpl WHERE code = 'three_shift' AND delete_time IS NULL) t);
|
||
|
||
INSERT INTO yz_platform_sched_tpl_day
|
||
(template_id, day_index, day_type, shift_name, shift_short, color, work_start, work_end, rest_start, rest_end, work_hours)
|
||
SELECT tpl.id, d.idx, d.day_type, d.shift_name, d.shift_short, d.color, d.work_start, d.work_end, d.rest_start, d.rest_end, d.work_hours
|
||
FROM (
|
||
SELECT 0 AS idx, 1 AS day_type, '早班' AS shift_name, '早' AS shift_short, '#67C23A' AS color, '08:00' AS work_start, '16:00' AS work_end, '' AS rest_start, '' AS rest_end, 8.00 AS work_hours
|
||
UNION ALL SELECT 1, 1, '中班', '中', '#E6A23C', '16:00', '24:00', '', '', 8.00
|
||
UNION ALL SELECT 2, 1, '夜班', '夜', '#909399', '00:00', '08:00', '', '', 8.00
|
||
) d
|
||
CROSS JOIN (SELECT id FROM yz_platform_sched_tpl WHERE code = 'three_shift' AND delete_time IS NULL LIMIT 1) tpl
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT template_id, day_index FROM yz_platform_sched_tpl_day) x
|
||
WHERE x.template_id = tpl.id AND x.day_index = d.idx
|
||
);
|
||
|
||
-- 6. 做一休一：上 1 天休 1 天
|
||
INSERT INTO yz_platform_sched_tpl
|
||
(code, name, template_type, align_mode, cycle_days, week_offset, month_rest_days, month_rest_weekday, summary, is_system, status, sort, creater)
|
||
SELECT 'work_one_rest_one', '做一休一', 6, 2, 2, 0, 0, 0, '上 1 天休 1 天循环（09:00-18:00）', 1, 1, 60, 'system'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_platform_sched_tpl WHERE code = 'work_one_rest_one' AND delete_time IS NULL) t);
|
||
|
||
INSERT INTO yz_platform_sched_tpl_day
|
||
(template_id, day_index, day_type, shift_name, shift_short, color, work_start, work_end, rest_start, rest_end, work_hours)
|
||
SELECT tpl.id, d.idx, d.day_type, d.shift_name, d.shift_short, d.color, d.work_start, d.work_end, d.rest_start, d.rest_end, d.work_hours
|
||
FROM (
|
||
SELECT 0 AS idx, 1 AS day_type, '白班' AS shift_name, '白' AS shift_short, '#409EFF' AS color, '09:00' AS work_start, '18:00' AS work_end, '12:00' AS rest_start, '13:30' AS rest_end, 7.50 AS work_hours
|
||
UNION ALL SELECT 1, 2, '', '', '', '', '', '', '', 0.00
|
||
) d
|
||
CROSS JOIN (SELECT id FROM yz_platform_sched_tpl WHERE code = 'work_one_rest_one' AND delete_time IS NULL LIMIT 1) tpl
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT template_id, day_index FROM yz_platform_sched_tpl_day) x
|
||
WHERE x.template_id = tpl.id AND x.day_index = d.idx
|
||
);
|
||
|
||
-- ============================================================
|
||
-- 结果校验：6 行，day_count 分别为 7 / 7 / 14 / 1 / 3 / 2
|
||
-- ============================================================
|
||
SELECT t.id, t.code, t.name, t.template_type, t.align_mode, t.cycle_days,
|
||
t.month_rest_days, t.month_rest_weekday, t.is_system, t.status, t.sort,
|
||
COUNT(d.id) AS day_count
|
||
FROM yz_platform_sched_tpl t
|
||
LEFT JOIN yz_platform_sched_tpl_day d ON d.template_id = t.id
|
||
WHERE t.delete_time IS NULL
|
||
GROUP BY t.id
|
||
ORDER BY t.sort;
|