668 lines
13 KiB
SQL
668 lines
13 KiB
SQL
-- ============================================
|
||
-- 科室预约工作时间维护 - 字段修改及测试数据
|
||
-- 生成日期: 2025-01-06
|
||
-- 说明: 字段统一命名 + 基于系统科室管理生成测试数据
|
||
-- ============================================
|
||
|
||
-- ============================================
|
||
-- 第一步：修改表结构（如果尚未执行）
|
||
-- ============================================
|
||
|
||
-- 1. 添加新字段
|
||
ALTER TABLE dept_appointment_hours ADD COLUMN IF NOT EXISTS institution VARCHAR(100);
|
||
ALTER TABLE dept_appointment_hours ADD COLUMN IF NOT EXISTS department VARCHAR(100);
|
||
ALTER TABLE dept_appointment_hours ADD COLUMN IF NOT EXISTS quota INTEGER;
|
||
ALTER TABLE dept_appointment_hours ADD COLUMN IF NOT EXISTS operator VARCHAR(50);
|
||
ALTER TABLE dept_appointment_hours ADD COLUMN IF NOT EXISTS created_time TIMESTAMP;
|
||
ALTER TABLE dept_appointment_hours ADD COLUMN IF NOT EXISTS updated_time TIMESTAMP;
|
||
|
||
-- 2. 将旧字段数据复制到新字段
|
||
UPDATE dept_appointment_hours SET institution = institution_name WHERE institution IS NULL AND institution_name IS NOT NULL;
|
||
UPDATE dept_appointment_hours SET department = dept_name WHERE department IS NULL AND dept_name IS NOT NULL;
|
||
UPDATE dept_appointment_hours SET quota = limit_count WHERE quota IS NULL AND limit_count IS NOT NULL;
|
||
UPDATE dept_appointment_hours SET operator = create_by WHERE operator IS NULL AND create_by IS NOT NULL;
|
||
UPDATE dept_appointment_hours SET created_time = create_time WHERE created_time IS NULL AND create_time IS NOT NULL;
|
||
UPDATE dept_appointment_hours SET updated_time = update_time WHERE updated_time IS NULL AND update_time IS NOT NULL;
|
||
|
||
-- 3. 删除旧字段
|
||
ALTER TABLE dept_appointment_hours DROP COLUMN IF EXISTS institution_name;
|
||
ALTER TABLE dept_appointment_hours DROP COLUMN IF EXISTS dept_name;
|
||
ALTER TABLE dept_appointment_hours DROP COLUMN IF EXISTS limit_count;
|
||
ALTER TABLE dept_appointment_hours DROP COLUMN IF EXISTS create_by;
|
||
ALTER TABLE dept_appointment_hours DROP COLUMN IF EXISTS create_time;
|
||
ALTER TABLE dept_appointment_hours DROP COLUMN IF EXISTS update_time;
|
||
|
||
-- 4. 重置序列，使id从1开始
|
||
SELECT setval('dept_appointment_hours_id_seq', 1, false);
|
||
|
||
-- ============================================
|
||
-- 第二步：更新列注释
|
||
-- ============================================
|
||
|
||
COMMENT ON COLUMN dept_appointment_hours.institution IS '所属机构';
|
||
COMMENT ON COLUMN dept_appointment_hours.department IS '科室名称';
|
||
COMMENT ON COLUMN dept_appointment_hours.morning_start IS '上午开始时间';
|
||
COMMENT ON COLUMN dept_appointment_hours.morning_end IS '上午结束时间';
|
||
COMMENT ON COLUMN dept_appointment_hours.afternoon_start IS '下午开始时间';
|
||
COMMENT ON COLUMN dept_appointment_hours.afternoon_end IS '下午结束时间';
|
||
COMMENT ON COLUMN dept_appointment_hours.quota IS '限号数量';
|
||
COMMENT ON COLUMN dept_appointment_hours.operator IS '操作人';
|
||
COMMENT ON COLUMN dept_appointment_hours.created_time IS '创建时间';
|
||
COMMENT ON COLUMN dept_appointment_hours.updated_time IS '更新时间';
|
||
|
||
-- ============================================
|
||
-- 第三步：清空现有数据并插入测试数据
|
||
-- ============================================
|
||
|
||
-- 清空表数据（如果需要保留现有数据，请注释此行）
|
||
TRUNCATE TABLE dept_appointment_hours RESTART IDENTITY;
|
||
|
||
-- ============================================
|
||
-- 插入测试数据 - 基于系统管理-基础数据-科室管理中的科室
|
||
-- ============================================
|
||
|
||
-- 1. 内科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'内科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
80,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 2. 外科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'外科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
60,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 3. 妇产科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'妇产科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
50,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 4. 儿科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'儿科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:30',
|
||
'17:30',
|
||
70,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 5. 眼科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'眼科门诊',
|
||
'08:30',
|
||
'11:30',
|
||
'14:00',
|
||
'17:00',
|
||
40,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 6. 耳鼻喉科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'耳鼻喉科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
45,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 7. 口腔科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'口腔科门诊',
|
||
'08:00',
|
||
'11:30',
|
||
'14:00',
|
||
'17:00',
|
||
35,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 8. 皮肤科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'皮肤科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
55,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 9. 中医科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'中医科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
40,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 10. 心内科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'心内科门诊',
|
||
'08:00',
|
||
'11:30',
|
||
'14:00',
|
||
'17:00',
|
||
50,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 11. 神经内科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'神经内科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
45,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 12. 呼吸科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'呼吸科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
55,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 13. 消化内科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'消化内科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
50,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 14. 骨科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'骨科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
45,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 15. 泌尿外科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'泌尿外科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
40,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 16. 精神科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'精神科门诊',
|
||
'09:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
30,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 17. 感染科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'感染科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
50,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 18. 急诊科
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'急诊科',
|
||
'00:00',
|
||
'23:59',
|
||
'00:00',
|
||
'23:59',
|
||
100,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 19. 康复科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'康复科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
35,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 20. 体检中心
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'体检中心',
|
||
'07:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
100,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 21. 血液科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'血液科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
35,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 22. 内分泌科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'内分泌科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
45,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 23. 普外科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'普外科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:30',
|
||
55,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 24. 神经外科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'神经外科门诊',
|
||
'08:30',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
40,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- 25. 烧伤科门诊
|
||
INSERT INTO dept_appointment_hours (
|
||
institution,
|
||
department,
|
||
morning_start,
|
||
morning_end,
|
||
afternoon_start,
|
||
afternoon_end,
|
||
quota,
|
||
operator,
|
||
created_time
|
||
) VALUES (
|
||
'演示医院',
|
||
'烧伤科门诊',
|
||
'08:00',
|
||
'12:00',
|
||
'14:00',
|
||
'17:00',
|
||
30,
|
||
'admin',
|
||
NOW()
|
||
);
|
||
|
||
-- ============================================
|
||
-- 验证插入的数据
|
||
-- ============================================
|
||
SELECT
|
||
id,
|
||
institution AS "所属机构",
|
||
department AS "科室名称",
|
||
morning_start AS "上午开始",
|
||
morning_end AS "上午结束",
|
||
afternoon_start AS "下午开始",
|
||
afternoon_end AS "下午结束",
|
||
quota AS "限号数量",
|
||
operator AS "操作人",
|
||
created_time AS "创建时间"
|
||
FROM dept_appointment_hours
|
||
ORDER BY id;
|
||
|
||
-- 统计总记录数
|
||
SELECT
|
||
COUNT(*) AS "总记录数"
|
||
FROM dept_appointment_hours;
|
||
|
||
-- 按科室统计
|
||
SELECT
|
||
department AS "科室",
|
||
COUNT(*) AS "数量"
|
||
FROM dept_appointment_hours
|
||
GROUP BY department
|
||
ORDER BY "数量" DESC;
|