cga_v2/sql/数据对接.sql

DROP TABLE IF EXISTS `dock_dept`;
CREATE TABLE `dock_dept`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '表ID（自增）',
  `dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '科室名称',
  `dept_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编码',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `sync` varchar(20) NULL DEFAULT '0' COMMENT '是否同步 0否 1是',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '三方部门表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for dock_empl
-- ----------------------------
DROP TABLE IF EXISTS `dock_empl`;
CREATE TABLE `dock_empl`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '表ID（自增）',
  `empl_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `empl_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工号或者账号',
  `empl_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '医生或者护士身份判断标识',
  `dept_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '所属科室编码',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `sync` varchar(20) NULL DEFAULT '0' COMMENT '是否同步 0否 1是',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '三方员工表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of dock_empl
-- ----------------------------

-- ----------------------------
-- Table structure for dock_patient_base_info
-- ----------------------------
DROP TABLE IF EXISTS `dock_patient_base_info`;
CREATE TABLE `dock_patient_base_info`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '表ID（自增）',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `patient_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '患者唯一编号',
  `id_card` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证件号码（身份证号）',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '联系电话',
  `sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别：0女、1男',
  `birthday` date NULL DEFAULT NULL COMMENT '出生日期',
  `educational_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '受教育程度：0文盲、1小学、2初中、3高中/技校/中专、4大学/专科及以上',
  `career` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职业类型：1：农林牧渔水利生产人员 2：教师 3：医务工作者 4：专业技术人员 5：生产、运输设备操作人员及有关人员6：商业、服务业人员7：国家机关、事业单位、企业负责人8：国家机关、事业单位、企业办事人员和有关人员9：军人 10：媒体、文体类工作人员 11：在校学生 12：未就业 13：家务 14：其他',
  `marital_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '婚姻状况：0已婚、1未婚、2离婚、3丧偶、4未知',
  `nation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '民族',
  `native_place` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '籍贯',
  `address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '现住址',
  `dwelling_state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '居住状态：0独居、1夫妻同住、2多代加入同住、3养老院、4其他',
  `contact_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系人姓名',
  `contact_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系人电话',
  `contact_relation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '与联系人关系',
  `abo_blood_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'ABO血型',
  `rh_blood_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Rh血型',
  `belief` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '信仰',
  `hobby` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '爱好',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者的用户名',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT ' 创建时间 ',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者的用户名',
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP COMMENT ' 修改时间 ',
  `del_flag` tinyint NULL DEFAULT 0 COMMENT '记录状态 0-正常 1-删除',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `sync` varchar(20) NULL DEFAULT '0' COMMENT '是否同步 0否 1是',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_id_card`(`id_card` ASC) USING BTREE,
  INDEX `idx_phone`(`phone` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '患者基础信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for dock_patient_diagnosis
-- ----------------------------
DROP TABLE IF EXISTS `dock_patient_diagnosis`;
CREATE TABLE `dock_patient_diagnosis`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '表ID（自增）',
  `visit_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '就诊流水号，关联就诊信息表的就诊号',
  `diagnosis_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '诊断类型（如门诊诊断、出院诊断）',
  `is_main_diagnosis` tinyint(1) NULL DEFAULT NULL COMMENT '是否主要诊断：0=否，1=是',
  `diagnosis_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '诊断名称（如高血压2级）',
  `diagnosis_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '诊断编码（如ICD-10编码）',
  `diagnosis_date` date NULL DEFAULT NULL COMMENT '诊断日期',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者的用户名',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT ' 创建时间 ',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者的用户名',
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP COMMENT ' 修改时间 ',
  `del_flag` tinyint NULL DEFAULT 0 COMMENT '记录状态 0-正常 1-删除',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `sync` varchar(20) NULL DEFAULT '0' COMMENT '是否同步 0否 1是',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_diagnosis_visit_no`(`visit_no` ASC) USING BTREE,
  INDEX `idx_diagnosis_code`(`diagnosis_code` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '患者诊断信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for dock_patient_medication_info
-- ----------------------------
DROP TABLE IF EXISTS `dock_patient_medication_info`;
CREATE TABLE `dock_patient_medication_info`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '表ID（自增）',
  `visit_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '门诊/住院号，关联就诊信息表的就诊号',
  `drug_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '药物名称',
  `dose` decimal(10, 2) NULL DEFAULT NULL COMMENT '剂量',
  `unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位（如mg、ml、片）',
  `frequency` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用药频率（如每日3次、每8小时1次）',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者的用户名',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT ' 创建时间 ',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者的用户名',
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP COMMENT ' 修改时间 ',
  `del_flag` tinyint NULL DEFAULT 0 COMMENT '记录状态 0-正常 1-删除',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `sync` varchar(20) NULL DEFAULT '0' COMMENT '是否同步 0否 1是',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_med_visit_no`(`visit_no` ASC) USING BTREE,
  INDEX `idx_drug_name`(`drug_name` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '患者用药信息表' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for dock_patient_visit_info
-- ----------------------------
DROP TABLE IF EXISTS `dock_patient_visit_info`;
CREATE TABLE `dock_patient_visit_info`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '表ID（自增）',
  `id_card` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '证件号码',
  `patient_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '患者唯一编号',
  `visit_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '就诊类型',
  `visit_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '门诊/住院号',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `department` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '就诊/住院科室',
  `doctor` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '就诊/主治医师',
  `admission_date` datetime NULL DEFAULT NULL COMMENT '就诊/入院日期',
  `admission_count` int NULL DEFAULT NULL COMMENT '住院次数',
  `bed_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '床位号',
  `discharge_date` datetime NULL DEFAULT NULL COMMENT '出院日期',
  `admission_method` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '入院途径',
  `discharge_method` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '离院方式',
  `height` decimal(5, 2) NULL DEFAULT NULL COMMENT '身高，单位：厘米',
  `weight` decimal(5, 2) NULL DEFAULT NULL COMMENT '体重，单位：千克',
  `tz` decimal(5, 2) NULL DEFAULT NULL COMMENT 'T值',
  `temperature` decimal(4, 2) NULL DEFAULT NULL COMMENT '体温，单位：摄氏度',
  `blood_pressure_shrink` int NULL DEFAULT NULL COMMENT '收缩压',
  `blood_pressure_diastole` int NULL DEFAULT NULL COMMENT '舒张压',
  `pulse` int NULL DEFAULT NULL COMMENT '脉搏',
  `creatinine` decimal(6, 2) NULL DEFAULT NULL COMMENT '肌酐',
  `oxygen_saturation` decimal(5, 2) NULL DEFAULT NULL COMMENT '血氧饱和度',
  `albumin` decimal(5, 2) NULL DEFAULT NULL COMMENT '白蛋白',
  `total_protein` decimal(5, 2) NULL DEFAULT NULL COMMENT '总蛋白',
  `vitamin_d3` decimal(5, 2) NULL DEFAULT NULL COMMENT '维生素D3测定',
  `hematocrit` decimal(5, 2) NULL DEFAULT NULL COMMENT '凝血酶原时间',
  `dimer` decimal(6, 2) NULL DEFAULT NULL COMMENT 'D-二聚体',
  `smoking_history` tinyint(1) NULL DEFAULT NULL COMMENT '是否吸烟，0=否，1=是',
  `smoking_year` int NULL DEFAULT NULL COMMENT '吸烟年限',
  `smoking_quit` tinyint(1) NULL DEFAULT NULL COMMENT '是否戒烟，0=否，1=是',
  `smoking_quit_year` int NULL DEFAULT NULL COMMENT '戒烟年限',
  `drink_history` tinyint(1) NULL DEFAULT NULL COMMENT '是否饮酒，0=否，1=是',
  `drink_year` int NULL DEFAULT NULL COMMENT '饮酒年限',
  `drink_quit` tinyint(1) NULL DEFAULT NULL COMMENT '是否戒酒，0=否，1=是',
  `drink_quit_year` int NULL DEFAULT NULL COMMENT '戒酒年限',
  `has_allergy` tinyint(1) NULL DEFAULT NULL COMMENT '是否过敏，0=否，1=是',
  `allergy_drug` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '过敏药',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者的用户名',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT ' 创建时间 ',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者的用户名',
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP COMMENT ' 修改时间 ',
  `del_flag` tinyint NULL DEFAULT 0 COMMENT '记录状态 0-正常 1-删除',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `sync` varchar(20) NULL DEFAULT '0' COMMENT '是否同步 0否 1是',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_visit_no`(`visit_no` ASC) USING BTREE,
  INDEX `idx_id_card`(`id_card` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '患者就诊信息表' ROW_FORMAT = Dynamic;

-- 患者表添加字段
ALTER TABLE `pms_patient`
ADD COLUMN `patient_no` varchar(50) COMMENT '患者院内唯一编号';

-- 主库从库都需要修改部门和用户表，加字段
ALTER TABLE `ums_user`
ADD COLUMN `empl_code` varchar(30) DEFAULT '' COMMENT '工号' AFTER `nick_name`;

ALTER TABLE `ums_dept`
ADD COLUMN `dept_code` varchar(30) DEFAULT '' COMMENT '科室编码' AFTER `dept_name`;

CREATE TABLE `ems_evaluation_scale_cognitive_relevance` (
  `id` bigint(20) NOT NULL,
  `evaluation_id` bigint(20) DEFAULT 0 COMMENT '测评id',
  `scale_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT ' 量表code ',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '认知域父级CODE',
  `cognitive_code` varchar(20) DEFAULT '' COMMENT '认知域code',
  `score` decimal(11,1) DEFAULT NULL COMMENT '得分',
  `total_score` decimal(10,1) DEFAULT NULL COMMENT '总分',
  `combo_id` bigint(20) DEFAULT NULL COMMENT '套餐ID',
  `sort` int(11) DEFAULT 0 COMMENT ' 排序',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建者的用户名',
  `create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT ' 创建时间 ',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新者的用户名',
  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT ' 修改时间 ',
  `del_flag` tinyint(3) unsigned DEFAULT 0 COMMENT ' 是否删除（1：是 0：否） ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=DYNAMIC COMMENT='测评量表认知域得分表';

ALTER TABLE `ruisi_cga_yf1`.`ems_evaluation_scale_relevance` 
ADD COLUMN `combo_id` bigint NULL AFTER `scale_code`;