ncs-ms/sql/wdkl_open_plateform.sql

/*
 Navicat Premium Data Transfer

 Source Server         : localmysql
 Source Server Type    : MySQL
 Source Server Version : 50732
 Source Host           : localhost:3306
 Source Schema         : wdkl_open_plateform

 Target Server Type    : MySQL
 Target Server Version : 50732
 File Encoding         : 65001

 Date: 21/01/2024 15:16:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for advice_ref_board_item
-- ----------------------------
DROP TABLE IF EXISTS `advice_ref_board_item`;
CREATE TABLE `advice_ref_board_item` (
  `id` int(16) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `board_item_name_md5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '看板条目名称md5',
  `advice_content_md5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '医嘱名称md5',
  `part_id` int(16) DEFAULT NULL COMMENT '科室Id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT COMMENT='医嘱条目手动设置关联看板项目';

-- ----------------------------
-- Table structure for advice_ref_nursing_mark_option
-- ----------------------------
DROP TABLE IF EXISTS `advice_ref_nursing_mark_option`;
CREATE TABLE `advice_ref_nursing_mark_option` (
  `id` int(16) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `nurse_option_name_md5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '床头屏护理项目名称md5',
  `advice_content_md5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '医嘱名称md5',
  `nurse_category_name_md5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '床头屏护理项目分类名称MD5',
  `part_id` int(16) DEFAULT NULL COMMENT '科室id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT COMMENT='医嘱手动关联护理项目';

-- ----------------------------
-- Table structure for advice_unique_content
-- ----------------------------
DROP TABLE IF EXISTS `advice_unique_content`;
CREATE TABLE `advice_unique_content` (
  `content_md5` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '内容md5',
  `advice_content` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '医嘱内容',
  PRIMARY KEY (`content_md5`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='医嘱内容不重条目,用来选择医嘱匹配';

-- ----------------------------
-- Table structure for board_item_config
-- ----------------------------
DROP TABLE IF EXISTS `board_item_config`;
CREATE TABLE `board_item_config` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目名称,科室内唯一',
  `item_group` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目分组',
  `item_name_md5` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目名称md5',
  `for_part_id` int(16) DEFAULT NULL COMMENT '指定科室id',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目说明',
  `contains_keywords` text COLLATE utf8mb4_unicode_ci COMMENT '项目匹配关键字JSON数组',
  `copy_from` int(16) DEFAULT NULL COMMENT '拷贝源Id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT COMMENT='看板项目设置';

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `keyval` varchar(50) COLLATE utf8_bin NOT NULL,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `summary` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `tel` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`keyval`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='His系统的科室数据';

-- ----------------------------
-- Table structure for doctor_advice
-- ----------------------------
DROP TABLE IF EXISTS `doctor_advice`;
CREATE TABLE `doctor_advice` (
  `his_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'his主键',
  `advice_content` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '医嘱内容',
  `state` tinyint(1) DEFAULT NULL COMMENT '医嘱状态(1、有效,0、无效)',
  `patient_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'his患者主键',
  `patient_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '呼叫系统患者主键',
  `customer_bed_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '患者床位号',
  `his_part_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'his患者科室主键',
  `content_md5` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '内容加密字符串(作为内容唯一标记)',
  `advice_time` datetime DEFAULT NULL COMMENT '医嘱时间',
  `sync_state` tinyint(1) DEFAULT NULL COMMENT '同步状态,datax同步数据时,先设置此字段为0,同步语句中设置此字段为1,由此字段判断源数据库是否已删除记录',
  PRIMARY KEY (`his_key`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT COMMENT='His系统医嘱';

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `keyval` varchar(50) COLLATE utf8_bin NOT NULL,
  `part_keyval` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `pass_no` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `type` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `sex` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `birthday` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `id_no` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `head_pic` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `mobile` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`keyval`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='His系统的职员数据';

-- ----------------------------
-- Table structure for manual_input_board_item_confing
-- ----------------------------
DROP TABLE IF EXISTS `manual_input_board_item_confing`;
CREATE TABLE `manual_input_board_item_confing` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '手工输入看板项目名称',
  `item_name_md5` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '名称Md5',
  `remark` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '说明',
  `for_part_id` int(10) DEFAULT NULL COMMENT '所属科室,-1为所有科室',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;

-- ----------------------------
-- Table structure for nursing_mark_category_config
-- ----------------------------
DROP TABLE IF EXISTS `nursing_mark_category_config`;
CREATE TABLE `nursing_mark_category_config` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '护理分类名称,科室内必须唯一',
  `index_no` int(2) DEFAULT NULL COMMENT '显示排序',
  `critical` bit(1) DEFAULT NULL COMMENT '是否关键项目(主机标记该项目)',
  `for_part_id` int(16) DEFAULT NULL COMMENT '-1为默认模版,需要科室确定是否使用',
  `name_md5` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'md5加密分类名称字符串',
  `default_color` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '默认背景色',
  `bool_manual` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否为手动选择类目',
  `copy_from` int(16) DEFAULT NULL COMMENT '拷贝源记录Id',
  `bool_auto` bit(1) NOT NULL DEFAULT b'0' COMMENT '根据匹配内容自动生成子项目',
  `match_condition` text COLLATE utf8mb4_unicode_ci COMMENT '匹配规则JSON数组',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT COMMENT='床头分机上的护理项目分类';

-- ----------------------------
-- Table structure for nursing_mark_option_config
-- ----------------------------
DROP TABLE IF EXISTS `nursing_mark_option_config`;
CREATE TABLE `nursing_mark_option_config` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目名称,科室内必须唯一',
  `category_id` int(16) DEFAULT NULL COMMENT '分类名称',
  `item_name_md5` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目名称md5',
  `category_name_md5` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分类名称md5',
  `color` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '项目颜色',
  `for_part_id` int(16) DEFAULT NULL COMMENT '指定科室Id',
  `index_no` int(3) DEFAULT NULL COMMENT '显示顺序',
  `contains_keywords` text COLLATE utf8mb4_unicode_ci COMMENT '自动匹配包含的关键字数组',
  `copy_from` int(16) DEFAULT NULL COMMENT '拷贝源Id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=245 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT COMMENT='护理参数选择项目';

-- ----------------------------
-- Table structure for part_temp_beds
-- ----------------------------
DROP TABLE IF EXISTS `part_temp_beds`;
CREATE TABLE `part_temp_beds` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `bed_no` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '床位号',
  `device_type` int(16) DEFAULT NULL COMMENT '床位设备类型',
  `eth_mac` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '设备mac地址',
  `part_id` int(16) DEFAULT NULL COMMENT '科室id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='科室加床号设置,由护士站输入,确定床号为无呼叫设备的加床床位';

-- ----------------------------
-- Table structure for patient
-- ----------------------------
DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient` (
  `keyval` varchar(50) COLLATE utf8_bin NOT NULL,
  `part_keyval` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `card_no` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `bed_no` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `sex` char(1) COLLATE utf8_bin DEFAULT NULL,
  `age` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `age_unit` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `birthday` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `id_no` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `address` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `mobile` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  `indate` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `baby_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `baby_sex` char(1) COLLATE utf8_bin DEFAULT NULL,
  `baby_birthday` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `doctor_keyval` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `nurse_keyval` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `status` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '0-住院,1-出院',
  `outdate` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `illness_description` text COLLATE utf8_bin,
  `part_id` int(10) DEFAULT NULL COMMENT '呼叫系统科室Id,此Id有值,说明同步到呼叫系统中的customer表.无值则未同步',
  `sync_state` tinyint(1) DEFAULT NULL COMMENT '同步状态,datax同步数据时,先设置此字段为0,同步语句中设置此字段为1,由此字段判断源数据库是否已删除记录',
  PRIMARY KEY (`keyval`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='His患者数据,同步过来后用来统计';

SET FOREIGN_KEY_CHECKS = 1;