208 lines
11 KiB
PL/PgSQL
208 lines
11 KiB
PL/PgSQL
-- 手术管理模块数据库表结构
|
||
-- 创建时间: 2025-12-30
|
||
-- 说明: 手术管理模块数据库表结构（PostgreSQL版本）
|
||
|
||
-- ----------------------------
|
||
-- Table structure for cli_surgery
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS cli_surgery CASCADE;
|
||
CREATE TABLE cli_surgery (
|
||
id bigint NOT NULL,
|
||
surgery_no varchar(50) NOT NULL,
|
||
patient_id bigint NOT NULL,
|
||
patient_name varchar(100),
|
||
encounter_id bigint,
|
||
surgery_name varchar(200) NOT NULL,
|
||
surgery_code varchar(100),
|
||
surgery_type_enum int2,
|
||
surgery_level int2,
|
||
status_enum int2 DEFAULT 0,
|
||
planned_time timestamp,
|
||
actual_start_time timestamp,
|
||
actual_end_time timestamp,
|
||
main_surgeon_id bigint,
|
||
main_surgeon_name varchar(100),
|
||
assistant_1_id bigint,
|
||
assistant_1_name varchar(100),
|
||
assistant_2_id bigint,
|
||
assistant_2_name varchar(100),
|
||
anesthetist_id bigint,
|
||
anesthetist_name varchar(100),
|
||
scrub_nurse_id bigint,
|
||
scrub_nurse_name varchar(100),
|
||
anesthesia_type_enum int2,
|
||
body_site varchar(200),
|
||
incision_level int2,
|
||
healing_level int2,
|
||
operating_room_id bigint,
|
||
operating_room_name varchar(100),
|
||
org_id bigint,
|
||
org_name varchar(100),
|
||
preoperative_diagnosis text,
|
||
postoperative_diagnosis text,
|
||
surgery_description text,
|
||
postoperative_advice text,
|
||
complications text,
|
||
surgery_fee numeric(10,2) DEFAULT 0.00,
|
||
anesthesia_fee numeric(10,2) DEFAULT 0.00,
|
||
total_fee numeric(10,2) DEFAULT 0.00,
|
||
remark varchar(500),
|
||
create_by bigint,
|
||
create_time timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
update_by varchar(64),
|
||
update_time timestamp,
|
||
delete_flag varchar(1) DEFAULT '0',
|
||
CONSTRAINT pk_cli_surgery PRIMARY KEY (id),
|
||
CONSTRAINT uk_cli_surgery_no UNIQUE (surgery_no)
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_cli_surgery_patient_id ON cli_surgery(patient_id);
|
||
CREATE INDEX idx_cli_surgery_encounter_id ON cli_surgery(encounter_id);
|
||
CREATE INDEX idx_cli_surgery_org_id ON cli_surgery(org_id);
|
||
CREATE INDEX idx_cli_surgery_status_enum ON cli_surgery(status_enum);
|
||
CREATE INDEX idx_cli_surgery_planned_time ON cli_surgery(planned_time);
|
||
|
||
-- 添加表注释
|
||
COMMENT ON TABLE cli_surgery IS '手术管理表';
|
||
COMMENT ON COLUMN cli_surgery.id IS 'ID';
|
||
COMMENT ON COLUMN cli_surgery.surgery_no IS '手术编号';
|
||
COMMENT ON COLUMN cli_surgery.patient_id IS '患者ID';
|
||
COMMENT ON COLUMN cli_surgery.patient_name IS '患者姓名';
|
||
COMMENT ON COLUMN cli_surgery.encounter_id IS '就诊ID';
|
||
COMMENT ON COLUMN cli_surgery.surgery_name IS '手术名称';
|
||
COMMENT ON COLUMN cli_surgery.surgery_code IS '手术编码';
|
||
COMMENT ON COLUMN cli_surgery.surgery_type_enum IS '手术类型编码 1-门诊手术 2-住院手术 3-急诊手术 4-择期手术';
|
||
COMMENT ON COLUMN cli_surgery.surgery_level IS '手术等级 1-一级 2-二级 3-三级 4-四级 5-特级';
|
||
COMMENT ON COLUMN cli_surgery.status_enum IS '手术状态 0-待排期 1-已排期 2-手术中 3-已完成 4-已取消 5-暂停';
|
||
COMMENT ON COLUMN cli_surgery.planned_time IS '计划手术时间';
|
||
COMMENT ON COLUMN cli_surgery.actual_start_time IS '实际开始时间';
|
||
COMMENT ON COLUMN cli_surgery.actual_end_time IS '实际结束时间';
|
||
COMMENT ON COLUMN cli_surgery.main_surgeon_id IS '主刀医生ID';
|
||
COMMENT ON COLUMN cli_surgery.main_surgeon_name IS '主刀医生姓名';
|
||
COMMENT ON COLUMN cli_surgery.assistant_1_id IS '助手1 ID';
|
||
COMMENT ON COLUMN cli_surgery.assistant_1_name IS '助手1 姓名';
|
||
COMMENT ON COLUMN cli_surgery.assistant_2_id IS '助手2 ID';
|
||
COMMENT ON COLUMN cli_surgery.assistant_2_name IS '助手2 姓名';
|
||
COMMENT ON COLUMN cli_surgery.anesthetist_id IS '麻醉医生ID';
|
||
COMMENT ON COLUMN cli_surgery.anesthetist_name IS '麻醉医生姓名';
|
||
COMMENT ON COLUMN cli_surgery.scrub_nurse_id IS '巡回护士ID';
|
||
COMMENT ON COLUMN cli_surgery.scrub_nurse_name IS '巡回护士姓名';
|
||
COMMENT ON COLUMN cli_surgery.anesthesia_type_enum IS '麻醉方式编码 0-无麻醉 1-局部麻醉 2-区域麻醉 3-全身麻醉 4-脊椎麻醉 5-硬膜外麻醉 6-表面麻醉';
|
||
COMMENT ON COLUMN cli_surgery.body_site IS '手术部位';
|
||
COMMENT ON COLUMN cli_surgery.incision_level IS '手术切口等级 1-I级切口 2-II级切口 3-III级切口 4-IV级切口';
|
||
COMMENT ON COLUMN cli_surgery.healing_level IS '手术切口愈合等级 1-甲级愈合 2-乙级愈合 3-丙级愈合';
|
||
COMMENT ON COLUMN cli_surgery.operating_room_id IS '手术室ID';
|
||
COMMENT ON COLUMN cli_surgery.operating_room_name IS '手术室名称';
|
||
COMMENT ON COLUMN cli_surgery.org_id IS '执行科室ID';
|
||
COMMENT ON COLUMN cli_surgery.org_name IS '执行科室名称';
|
||
COMMENT ON COLUMN cli_surgery.preoperative_diagnosis IS '术前诊断';
|
||
COMMENT ON COLUMN cli_surgery.postoperative_diagnosis IS '术后诊断';
|
||
COMMENT ON COLUMN cli_surgery.surgery_description IS '手术经过描述';
|
||
COMMENT ON COLUMN cli_surgery.postoperative_advice IS '术后医嘱';
|
||
COMMENT ON COLUMN cli_surgery.complications IS '并发症描述';
|
||
COMMENT ON COLUMN cli_surgery.surgery_fee IS '手术费用';
|
||
COMMENT ON COLUMN cli_surgery.anesthesia_fee IS '麻醉费用';
|
||
COMMENT ON COLUMN cli_surgery.total_fee IS '总费用';
|
||
COMMENT ON COLUMN cli_surgery.remark IS '备注信息';
|
||
COMMENT ON COLUMN cli_surgery.create_by IS '创建人ID';
|
||
COMMENT ON COLUMN cli_surgery.create_time IS '创建时间';
|
||
COMMENT ON COLUMN cli_surgery.update_by IS '更新人ID';
|
||
COMMENT ON COLUMN cli_surgery.update_time IS '更新时间';
|
||
COMMENT ON COLUMN cli_surgery.delete_flag IS '删除标记 0-正常 1-删除';
|
||
|
||
-- 创建自动更新时间的触发器函数
|
||
CREATE OR REPLACE FUNCTION update_modified_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.update_time = CURRENT_TIMESTAMP;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建触发器
|
||
CREATE TRIGGER update_cli_surgery_modtime
|
||
BEFORE UPDATE ON cli_surgery
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_modified_column();
|
||
|
||
-- ----------------------------
|
||
-- Dictionary data for surgery manage
|
||
-- ----------------------------
|
||
-- 手术状态字典
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark) VALUES
|
||
('手术状态', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '手术状态列表')
|
||
ON CONFLICT (dict_type) DO NOTHING;
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, status, create_by, create_time, remark) VALUES
|
||
(1, '待排期', '0', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '待排期状态'),
|
||
(2, '已排期', '1', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '已排期状态'),
|
||
(3, '手术中', '2', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '手术中状态'),
|
||
(4, '已完成', '3', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '已完成状态'),
|
||
(5, '已取消', '4', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '已取消状态'),
|
||
(6, '暂停', '5', 'surgery_status', '0', 'admin', CURRENT_TIMESTAMP, '暂停状态')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- 手术类型字典
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark) VALUES
|
||
('手术类型', 'surgery_type', '0', 'admin', CURRENT_TIMESTAMP, '手术类型列表')
|
||
ON CONFLICT (dict_type) DO NOTHING;
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, status, create_by, create_time, remark) VALUES
|
||
(1, '门诊手术', '1', 'surgery_type', '0', 'admin', CURRENT_TIMESTAMP, '门诊手术'),
|
||
(2, '住院手术', '2', 'surgery_type', '0', 'admin', CURRENT_TIMESTAMP, '住院手术'),
|
||
(3, '急诊手术', '3', 'surgery_type', '0', 'admin', CURRENT_TIMESTAMP, '急诊手术'),
|
||
(4, '择期手术', '4', 'surgery_type', '0', 'admin', CURRENT_TIMESTAMP, '择期手术')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- 手术等级字典
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark) VALUES
|
||
('手术等级', 'surgery_level', '0', 'admin', CURRENT_TIMESTAMP, '手术等级列表')
|
||
ON CONFLICT (dict_type) DO NOTHING;
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, status, create_by, create_time, remark) VALUES
|
||
(1, '一级手术', '1', 'surgery_level', '0', 'admin', CURRENT_TIMESTAMP, '一级手术'),
|
||
(2, '二级手术', '2', 'surgery_level', '0', 'admin', CURRENT_TIMESTAMP, '二级手术'),
|
||
(3, '三级手术', '3', 'surgery_level', '0', 'admin', CURRENT_TIMESTAMP, '三级手术'),
|
||
(4, '四级手术', '4', 'surgery_level', '0', 'admin', CURRENT_TIMESTAMP, '四级手术'),
|
||
(5, '特级手术', '5', 'surgery_level', '0', 'admin', CURRENT_TIMESTAMP, '特级手术')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- 麻醉方式字典
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark) VALUES
|
||
('麻醉方式', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '麻醉方式列表')
|
||
ON CONFLICT (dict_type) DO NOTHING;
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, status, create_by, create_time, remark) VALUES
|
||
(1, '无麻醉', '0', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '无麻醉'),
|
||
(2, '局部麻醉', '1', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '局部麻醉'),
|
||
(3, '区域麻醉', '2', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '区域麻醉'),
|
||
(4, '全身麻醉', '3', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '全身麻醉'),
|
||
(5, '脊椎麻醉', '4', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '脊椎麻醉'),
|
||
(6, '硬膜外麻醉', '5', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '硬膜外麻醉'),
|
||
(7, '表面麻醉', '6', 'anesthesia_type', '0', 'admin', CURRENT_TIMESTAMP, '表面麻醉')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- 切口等级字典
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark) VALUES
|
||
('切口等级', 'incision_level', '0', 'admin', CURRENT_TIMESTAMP, '切口等级列表')
|
||
ON CONFLICT (dict_type) DO NOTHING;
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, status, create_by, create_time, remark) VALUES
|
||
(1, 'I级切口', '1', 'incision_level', '0', 'admin', CURRENT_TIMESTAMP, 'I级切口'),
|
||
(2, 'II级切口', '2', 'incision_level', '0', 'admin', CURRENT_TIMESTAMP, 'II级切口'),
|
||
(3, 'III级切口', '3', 'incision_level', '0', 'admin', CURRENT_TIMESTAMP, 'III级切口'),
|
||
(4, 'IV级切口', '4', 'incision_level', '0', 'admin', CURRENT_TIMESTAMP, 'IV级切口')
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
-- 愈合等级字典
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark) VALUES
|
||
('愈合等级', 'healing_level', '0', 'admin', CURRENT_TIMESTAMP, '愈合等级列表')
|
||
ON CONFLICT (dict_type) DO NOTHING;
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, status, create_by, create_time, remark) VALUES
|
||
(1, '甲级愈合', '1', 'healing_level', '0', 'admin', CURRENT_TIMESTAMP, '甲级愈合'),
|
||
(2, '乙级愈合', '2', 'healing_level', '0', 'admin', CURRENT_TIMESTAMP, '乙级愈合'),
|
||
(3, '丙级愈合', '3', 'healing_level', '0', 'admin', CURRENT_TIMESTAMP, '丙级愈合')
|
||
ON CONFLICT DO NOTHING;
|