40 lines
2.6 KiB
SQL
40 lines
2.6 KiB
SQL
-- 医生排班表
|
||
CREATE TABLE IF NOT EXISTS `zyt_doctor_roster` (
|
||
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`doctor_id` int(11) NOT NULL COMMENT '医生ID（对应 zyt_admin 表的 id，role_id=1）',
|
||
`date` date NOT NULL COMMENT '日期',
|
||
`period` varchar(20) NOT NULL COMMENT '时段 morning-上午 afternoon-下午',
|
||
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态 1-出诊 2-停诊 3-休息 4-请假',
|
||
`quota` int(11) DEFAULT '0' COMMENT '号源数',
|
||
`max_patients` int(11) DEFAULT '0' COMMENT '最大接诊数',
|
||
`booked_count` int(11) DEFAULT '0' COMMENT '已预约数',
|
||
`remark` varchar(500) DEFAULT '' COMMENT '备注',
|
||
`create_time` int(11) NOT NULL COMMENT '创建时间',
|
||
`update_time` int(11) NOT NULL COMMENT '更新时间',
|
||
`delete_time` int(11) DEFAULT NULL COMMENT '删除时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_doctor_date_period` (`doctor_id`,`date`,`period`,`delete_time`),
|
||
KEY `idx_date` (`date`),
|
||
KEY `idx_doctor` (`doctor_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='医生排班表';
|
||
|
||
-- 注意：医生数据来自 zyt_admin 表，筛选条件为 role_id=1
|
||
-- 不需要单独的医生表、医院表、科室表
|
||
|
||
-- 插入示例排班数据（假设 zyt_admin 表中 id=1 和 id=2 的用户是医生，role_id=1）
|
||
INSERT INTO `la_doctor_roster` (`doctor_id`, `date`, `period`, `status`, `quota`, `max_patients`, `booked_count`, `create_time`, `update_time`) VALUES
|
||
-- 医生1本周排班
|
||
(1, CURDATE(), 'morning', 1, 20, 30, 5, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, CURDATE(), 'afternoon', 1, 20, 30, 3, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'morning', 1, 20, 30, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'morning', 1, 20, 30, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'afternoon', 1, 20, 30, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 3 DAY), 'morning', 1, 20, 30, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 4 DAY), 'morning', 1, 20, 30, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 4 DAY), 'afternoon', 1, 20, 30, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
-- 周末休息
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'morning', 3, 0, 0, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'afternoon', 3, 0, 0, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 6 DAY), 'morning', 3, 0, 0, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
|
||
(1, DATE_ADD(CURDATE(), INTERVAL 6 DAY), 'afternoon', 3, 0, 0, 0, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
|