Hbase+Phoenix
背景
MySQL 单表数据量超过 2000W，CRUD 性能会极具下降。需要 DBA 或自动化脚本定期归档（半年或一年数据迁移到归档数据库中），以起到缩表的目的。电商项目一两个月就能积累几千万条数据。在这种情况下，要么频繁的做归档，要么选择可以容乃数百 TB 而不需要归档的大数据平台（降低运维成本）。
HBase
HBase 是一种构建在 HDFS 之上的分布式、面向列（但不是列存储）的存储系统。
在需要实时读写 、随机访问 超大规模数据集时，可以使用 HBase。
HBase 可以通过线性方式增加节点来进行扩展 。HBase 不是关系型数据库，自身不支持 SQL 查询引擎，HBase 适合将大而稀疏的表放在分布式集群上。
HBase 是 Google Bigtable 的开源实现，利用 Hadoop HDFS 作为其文件存储系统；利用 Hadoop MapReduce（或其他计算引擎） 来处理 HBase 中的海量数据；使用 Zookeeper 作为协同服务。
HBase 的特点
- 容量大：一个表可以有上亿行，上百万列
- 面向列：面向列的存储（但不是列存储，参考 HBase 数据存储）和权限控制，支持列的独立检索
- 稀疏性：对于为空（NULL）的列，并不占用存储空间，因此，表可以设计的非常稀疏
- 无模式：每一行都有一个可以排序的主键和任意多的列，列可以根据需要动态增加，不同的行可以有不同的列
- 数据多版本：每个单元格（Cell）中的数据可以有多个版本，默认情况下，版本号自动分配，版本号就是单元格插入时的时间戳
- 数据类型单一：HBase 中的数据都是字符串（存入字节），没有类型
- 随机查询：针对 Rowkey 的查询能够达到毫秒级别
HBase 的高并发和实时处理数据
HBase 是可以提供实时计算的分布式数据存储，HBase 以 StoreFile（HFile）二进制流的形式存储在 HDFS 上，由 HDFS 保证高容错性 ，HDFS 并不知道 HBase 存的是什么，HBase 的存储数据对于 HDFS 文件系统是透明的。参考 HFile 文件格式。
HBase 的使用建议
HBase 的设计目标并不是替代 RDBMS，而是对 RDBMS 的一个重要补充，尤其是对大数据的场景。
首先，在有足够多数据，如果有上亿或上千亿行数据，HBase 才会是一个很好的备选。
其次，需要确信业务上可以不依赖 RDBMS 的额外特性，例如，列数据类型，二级索引，SQL 查询语言等（部分特性可以通过其他组件实现，如 Phoenix）。
最后，需要确保有一定大小的 HDFS 集群规模。
Phoenix
Phoenix 最早是 saleforce 的一个开源项目，后来成为 Apache 基金的顶级项目。
Phoenix 是构建在 HBase 上的一个 SQL 层，能让我们用标准的 JDBC APIs 而不是 HBase 客户端 APIs 来创建表，插入数据和对 HBase 数据进行查询。
put the SQL back in NoSQL
Phoenix 完全使用 Java 编写，作为 HBase 内嵌的 JDBC 驱动。Phoenix 查询引擎会将 SQL 查询转换为一个或多个 HBase 扫描，并编排执行以生成标准的 JDBC 结果集。直接使用 HBase API、协同处理器与自定义过滤器，对于简单查询来说，其性能量级是毫秒，对于百万级别的行数来说，其性能量级是秒。
HBase 的查询工具有很多，如：Hive、Tez、Impala、Spark SQL、Phoenix 等。
Phoenix 通过以下方式使我们可以少写代码，并且性能比我们自己写代码更好：
- 将 SQL 编译成原生的 HBase scans。
- 确定 scan 关键字的最佳开始和结束
- 让 scan 并行执行
- ...
使用 Phoenix 的公司
docker 部署（HBase+Phoenix）
1# 导入镜像
2[root@medical tmp]# docker load < phoenix.tar.gz
3
4# 创建 HBASE、Phoenix容器
5docker run -it -d -p 2181:2181 -p 8765:8765 -p 15165:15165 \
6-p 16000:16000 -p 16010:16010 -p 16020:16020 \
7-v /root/hbase/data:/tmp/hbase-root/hbase/data \
8--name phoenix \
9boostport/hbase-phoenix-all-in-one:2.0-5.0
10
11
12# 初始化Phoenix 设置环境变量
13docker exec -it phoenix bash
14export HBASE_CONF_DIR=/opt/hbase/conf/
15
16# 使用Phoenix客户端连接，运行SQL语句（进入）
17/opt/phoenix-server/bin/sqlline.py localhost
18
19# 退出
20!exit
创建逻辑库
1CREATE SCHEMA hospital;
2USE hospital;
3-- 患者端用户表 -----------------------------------------------
4DROP TABLE IF EXISTS hospital.patient_user;
5CREATE TABLE hospital.patient_user
6(
7 "id" INTEGER NOT NULL PRIMARY KEY,
8 "open_id" VARCHAR,
9 "nickname" VARCHAR(50),
10 "photo" VARCHAR,
11 "sex" VARCHAR(1),
12-- 状态：1代表正常，2代表禁用
13 "status" TINYINT,
14 "create_time" DATE
15);
16DROP SEQUENCE IF EXISTS hospital.patient_user_sequence;
17CREATE SEQUENCE hospital.patient_user_sequence START WITH 1 increment BY 1;
18CREATE INDEX patient_user_idx_1 ON hospital.patient_user ("open_id");
19CREATE INDEX patient_user_idx_2 ON hospital.patient_user ("status");
20
21
22-- 患者端用户基本信息表 -----------------------------------------------
23DROP TABLE IF EXISTS hospital.patient_user_info_card;
24CREATE TABLE hospital.patient_user_info_card
25(
26 "id" INTEGER NOT NULL PRIMARY KEY,
27 "user_id" INTEGER,
28 "uuid" CHAR(32),
29 "name" VARCHAR(20),
30 "sex" VARCHAR(1),
31 "pid" CHAR(18),
32 "tel" CHAR(11),
33 "birthday" DATE,
34 -- 疾病史
35 "medical_history" VARCHAR,
36 -- 保险类型
37 "insurance_type" VARCHAR,
38-- 是否人脸录入面部信息
39 "exist_face_model" BOOLEAN
40);
41
42DROP SEQUENCE IF EXISTS hospital.patient_user_info_card_sequence;
43CREATE SEQUENCE hospital.patient_user_info_card_sequence START WITH 1 increment BY 1;
44CREATE INDEX patient_user_info_card_idx_1 ON hospital.patient_user_info_card ("user_id");
45CREATE INDEX patient_user_info_card_idx_2 ON hospital.patient_user_info_card ("uuid");
46CREATE INDEX patient_user_info_card_idx_3 ON hospital.patient_user_info_card ("pid");
47
48-- 人脸认证记录表-----------------------------------------------
49DROP TABLE IF EXISTS hospital.patient_face_auth;
50CREATE TABLE hospital.patient_face_auth(
51 "id" INTEGER NOT NULL PRIMARY KEY ,
52 "patient_card_id" INTEGER,
53 "date" DATE
54);
55
56DROP SEQUENCE IF EXISTS hospital.patient_face_auth_sequence;
57CREATE SEQUENCE hospital.patient_face_auth_sequence START WITH 1 increment BY 1;
58CREATE INDEX patient_face_auth_idx_1 ON hospital.patient_face_auth ("patient_card_id");
59CREATE INDEX patient_face_auth_idx_2 ON hospital.patient_face_auth ("date");
60
61
62-- 科室表 --------------------------------------------------
63DROP TABLE IF EXISTS hospital.medical_dept;
64CREATE TABLE hospital.medical_dept
65(
66 "id" INTEGER NOT NULL PRIMARY KEY,
67 "name" VARCHAR(50),
68 "outpatient" BOOLEAN,
69 "description" VARCHAR(500),
70-- 推荐在首页科室列表中展示的
71 "recommended" BOOLEAN
72);
73
74DROP SEQUENCE IF EXISTS hospital.medical_dept_sequence;
75CREATE SEQUENCE hospital.medical_dept_sequence START WITH 1 increment BY 1;
76
77UPSERT
78INTO hospital.medical_dept("id","name","outpatient","description","recommended")
79VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'口腔科',true,
80 '目前已经成为在国内外具有一定影响力的大型医疗科室，科室现有医护人员近70人，教授6人，副教授3人，博士研究生导师3人，硕士研究生导师4人，每年培养博士、硕士研究生近20余人。',true);
81
82UPSERT
83INTO hospital.medical_dept("id","name","outpatient","description","recommended")
84VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'眼科',true,
85 '全科共有医护人员及技师共67人，其中教授6人，副教授9人，讲师10人，助教4人，在职的29名医生全部获得硕士、博士学位，其中博士14人。下设眼底病、青光眼、白内障、眼外伤、飞秒激光治疗近视、眼眶病、斜视等多个专业学组。开展各种外路视网膜脱离复位术，玻璃体视网膜手术治疗视网膜脱离手术、复杂及二次视网膜脱离手术、黄斑裂孔性视网膜脱离手术等。',true);
86
87UPSERT
88INTO hospital.medical_dept("id","name","outpatient","description","recommended")
89VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'耳鼻喉科',true,
90 '科室设有耳科、鼻科、咽喉头颈外科3个专业组，配备听力检测、前庭功能检查、喉功能检查、 内窥镜检查及多导睡眠检查5个检查室，配备手术显微镜、鼻内镜系统、耳鼻喉动力系统、CO2激光治疗机等一批先进诊疗设备，编制床位共计100张。年门急诊患者量近10万人次，年住院3500余人次，年手术3000余例，学科综合实力位于国内先进行列。',true);
91
92UPSERT
93INTO hospital.medical_dept("id","name","outpatient","description","recommended")
94VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'内科',true,
95 '目前已经成为在国内外具有一定影响力的大型医疗科室，科室现有医护人员近70人，教授6人，副教授3人，博士研究生导师3人，硕士研究生导师4人，每年培养博士、硕士研究生近20余人。呼吸科年门诊量5万余人次，年收治各种呼吸系统疾病住院患者2000余人次，现有床位75张，其中内科重症监护室床位23张，长期承担内科重症患者的诊治工作。长期承担七年制、本科、留学生等医学班的内科学、诊断学、临床实习等授课。',true);
96
97UPSERT
98INTO hospital.medical_dept("id","name","outpatient","description","recommended")
99VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'外科',true,
100 '科室现有医生52名，教授8人，副教授9人。年均开展各类手术1000余例。其中腔镜微创手术占手术量的40％以上。近年接连开展胸外科各项领先技术，如3切口食管癌手术，全腔镜食管癌手术，腔镜下胸部单操作孔、腹部单孔食管癌手术等。科室承担国家自然科学基金等国家和部省级各类科研课题10余项，发表国际核心医学期刊SCI收录英文论文10余篇，国家级核心期刊中文论文60余篇。',true);
101
102UPSERT
103INTO hospital.medical_dept("id","name","outpatient","description","recommended")
104VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'皮肤科',true,
105 '皮肤科成立于1977年，现共有医生11人(副主任医师3名)，护士2名，其中8人具有硕士学位、2人具有博士学位。目前门诊量120000人次/年左右，是皮肤疾病主要的诊断、治疗场所。诊治的主要疾病包括儿童湿疹、皮炎、荨麻疹、药疹等过敏变态反应性疾病;水痘、手足口病、猩红热、传染性单核细胞增多症、疣、毛囊炎、脓疱疮、念珠菌性皮炎、足癣、体癣、疥疮等感染性皮肤病。',true);
106
107UPSERT
108INTO hospital.medical_dept("id","name","outpatient","description","recommended")
109VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'妇科',true,
110 '妇科现有职工89人，其中医生42人（博士16人，硕士26人），包括正高职（教授、主任医师）14人，省管专家2人，享受政府特殊津贴2人，博士生导师3人，硕士生导师11人。在女性复杂生殖道畸形矫治（先天性无阴道阴道成形术、Robert子宫宫腔镜矫形术、阴道斜隔综合征矫治术、特殊类型外阴阴道畸形矫治术、子宫纵隔切除术）有丰富的治疗经验，2019年在女性生殖系统重建的基础研究与临床应用方面的突出成就获得河北省科技进步一等奖。 ',true);
111
112UPSERT
113INTO hospital.medical_dept("id","name","outpatient","description","recommended")
114VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'儿科',true,
115 '目前共有医护人员49 名，包括医师17人，护理人员32人。其中，主任医师2人，副主任医师2人，主治医师9人，具有硕士学位6人，在读硕士4人。儿科分成门诊及病区两个单元。儿科门诊常年24小时接诊，年门急诊量逐年提升，平均年门急诊量可达8万人次。每日输液量在80-100人次、最多时达到200-280人次。每日雾化量在50人次，最多时达到150人次。儿科始终围绕“以病人为中心，以质量为生命”这一主题，构建和谐科室，打造专业儿科团队，病人满意度不断提高，得到家长广泛的认可和尊重。',true);
116
117UPSERT
118INTO hospital.medical_dept("id","name","outpatient","description","recommended")
119VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'神经科',true,
120 '科室拥有一支专业的神经科诊断技术团队，常规开展神经影像学(3.0MRI、128排螺旋CT、DSA、TCD、ECT、OCT)、神经电生理（视频脑电图、动态脑电图、睡眠脑电图、肌电图、诱发电位、神经传导速度）、神经免疫、神经生化、神经病理、抗癫痫药物浓度检测、基因检测、脑脊液细胞学检查等工作，为神经系统疾病尤其是疑难杂症的诊断提供了良好的技术平台。每年开展颅内动脉瘤弹簧圈栓塞术200余例；颈内动脉支架植入术30余例；动脉取栓及支架植入20余例；微创颅内血肿清除引流术100余例。',true);
121
122UPSERT
123INTO hospital.medical_dept("id","name","outpatient","description","recommended")
124VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'肿瘤科',true,
125 '肿瘤内科建科于1964年，首批国家临床重点专科、卫计委癌痛规范化示范病房、国家临床药理机构肿瘤专业基地、中华医学会肿瘤学分会、中国临床肿瘤学会（CSCO）和中国研究型医院学会精准医学与肿瘤MDT专业委员会常委单位、教育部肿瘤学专业硕士及博士学位授予点。科室开展的治疗包括术前新辅助化疗、转化化疗、术后辅助化疗、姑息性化疗、根治性化疗、靶向治疗及免疫治疗等，并参与开展数十项国际、国内多中心临床试验。成立有肺癌、消化道肿瘤、乳腺癌、淋巴瘤、泌尿生殖系肿瘤、骨及皮肤软组织肉瘤、胃肠胰神经内分泌肿瘤和恶性黑色素瘤8个亚专业及相关MDT团队，重点是在MDT指导下依据组织或血液标本进行多基因检测，获得靶基因、基因多态性及通路基因的相关信息，实施规范化、个体化的综合治疗；治疗和学术水平达国内先进水平。',true);
126
127UPSERT
128INTO hospital.medical_dept("id","name","outpatient","description","recommended")
129VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'产科',true,
130 '产科现有专业医护人员50余名，其中高级职称11人，硕士生导师3人，硕士 10余人，开放床位3个普通病区+1个LDR病区，共124张，备有单人间、双人间的母婴同室病房，还有温馨家庭病房16间。本科室在自然分娩、阴道侧切术、新式剖宫产术等方面积累了丰富的临床经验，具备危重产科病人抢救的技术和设备，全面开展了围产期保健、产前筛查、出生缺陷监测、优生遗传咨询、孕妇学校、都哈营养指导、育儿指导、康乐待产、康乐分娩、无痛分娩、新生儿疾病筛查、新生儿抚触、新生儿游泳、产后恢复保健、院后随访等一系列整体化、人性化的治疗及护理措施，擅长各种高危妊娠的筛查、监护和治疗，妊娠合并症及妊娠并发症的诊断及治疗。',false);
131
132UPSERT
133INTO hospital.medical_dept("id","name","outpatient","description","recommended")
134VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'骨科',true,
135 '科室设置规范、布局合理，拥有专业医疗团队，本学科对骨科疾病的诊疗,特别是疑难、危重病人的诊治和抢救水平保持区内领先水平。科室拥有进口“C”臂机、全套进口关节镜器械、进口牵引床、进口电钻及磨钻、CPM关节康复器、骨折治疗仪等先进设备。目前开展复杂骨盆及髋臼的前后入路手术、颈胸腰椎的前后路手术、人工全髋全膝关节置换术、膝关节镜下滑膜清理、半月板成形、交叉韧带重建手术，经皮椎体后凸成形术（PKP术），闭合复位PFNA固定术，经皮插入钢板内固定治疗四肢骨折、微创腰椎融合术人工肱骨头、桡骨头置换术、断肢（指）再植术、脊柱侧弯后路矫形复位术、全髋关节翻修术等。',false);
136
137UPSERT
138INTO hospital.medical_dept("id","name","outpatient","description","recommended")
139VALUES(NEXT VALUE FOR hospital.medical_dept_sequence,'血液科',true,
140 '血液科经过几代人三四十年的奋斗，已发展成集医疗、教学、科研为一体的北京市重点专科。血液科聚焦于头颈部淋巴瘤的综合诊疗，尤其是鼻型结外NK/T细胞淋巴瘤、眼附属器淋巴瘤、原发眼内淋巴瘤、及好发于头颈部的其他类型造血系统肿瘤，同时覆盖其他专业患者面临的各种血液学问题，如贫血、血小板减少、出凝血障碍等。据统计，血液科每年初诊NK/T细胞淋巴瘤患者近100例，其他类型淋巴瘤患者200余例。',false);
141
142-- 科室门诊表 --------------------------------------------------
143DROP TABLE IF EXISTS hospital.medical_dept_sub;
144CREATE TABLE hospital.medical_dept_sub(
145 "id" INTEGER NOT NULL PRIMARY KEY ,
146 "name" VARCHAR(50),
147 "dept_id" INTEGER,
148 "location" VARCHAR(50)
149);
150
151DROP SEQUENCE IF EXISTS hospital.medical_dept_sub_sequence;
152CREATE SEQUENCE hospital.medical_dept_sub_sequence START WITH 1 increment BY 1;
153CREATE INDEX medical_dept_sub_idx_1 ON hospital.medical_dept_sub ("dept_id");
154
155UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
156VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'口腔颌面外科',1,'1号楼2层A区');
157
158UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
159VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'口腔颌面内科',1,'1号楼2层B区');
160
161UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
162VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'眼科门诊',2,'1号楼3层A区');
163
164UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
165VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'白内障诊疗中心',2,'1号楼3层B区');
166
167UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
168VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'屈光中心门诊',2,'1号楼3层C区');
169
170UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
171VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'眼激光门诊',2,'1号楼3层D区');
172
173UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
174VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'耳鼻喉门诊',3,'1号楼3层E区');
175
176UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
177VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'内分泌门诊',4,'1号楼4层A区');
178
179UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
180VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'呼吸内科门诊',4,'1号楼4层B区');
181
182UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
183VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'心血管门诊',4,'1号楼4层C区');
184
185UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
186VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'消化内科门诊',4,'1号楼4层D区');
187
188UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
189VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'糖尿病门诊',4,'1号楼5层A区');
190
191UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
192VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'肾内科门诊',4,'1号楼5层B区');
193
194UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
195VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'风湿免疫门诊',4,'1号楼5层C区');
196
197UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
198VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'普通外科门诊',5,'1号楼5层D区');
199
200UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
201VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'胸外科门诊',5,'1号楼5层E区');
202
203UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
204VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'泌尿外科门诊',5,'1号楼6层A区');
205
206UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
207VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'心脏外科门诊',5,'1号楼5层B区');
208
209UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
210VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'整形外科门诊',5,'1号楼5层C区');
211
212UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
213VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'皮肤病门诊',6,'1号楼5层D区');
214
215UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
216VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'妇科门诊',7,'1号楼6层A区');
217
218UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
219VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'不孕病门诊',7,'1号楼6层B区');
220
221UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
222VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'儿科门诊',8,'1号楼6层C区');
223
224UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
225VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'神经内科门诊',9,'1号楼7层A区');
226
227UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
228VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'神经外科门诊',9,'1号楼7层B区');
229
230UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
231VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'肿瘤科门诊',10,'2号楼2层A区');
232
233UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
234VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'产科门诊',11,'2号楼3层A区');
235
236UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
237VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'骨科门诊',12,'2号楼4层A区');
238
239UPSERT INTO hospital.medical_dept_sub("id","name","dept_id","location")
240VALUES(NEXT VALUE FOR hospital.medical_dept_sub_sequence,'血液科门诊',13,'2号楼4层B区');
241
242-- 医生表 --------------------------------------------------
243DROP TABLE IF EXISTS hospital.doctor;
244CREATE TABLE hospital.doctor
245(
246 "id" INTEGER NOT NULL PRIMARY KEY,
247 "name" VARCHAR(20),
248 "pid" CHAR(18),
249 "uuid" CHAR(32),
250 "sex" VARCHAR(1),
251 "photo" VARCHAR,
252 "birthday" DATE,
253 "school" VARCHAR(50),
254 "degree" VARCHAR(20),
255 "tel" CHAR(11),
256 "address" VARCHAR(200),
257 "email" VARCHAR(200),
258 "job" VARCHAR(20),
259 "remark" VARCHAR(50),
260 "description" VARCHAR,
261 "hiredate" DATE,
262 "tag" VARCHAR,
263 "recommended" BOOLEAN,
264 -- 1在职，2离职，3退休，4隐藏（逻辑删除）
265 "status" TINYINT,
266 "create_time" DATE
267);
268
269DROP SEQUENCE IF EXISTS hospital.doctor_sequence;
270CREATE SEQUENCE hospital.doctor_sequence START WITH 1 increment BY 1;
271CREATE INDEX doctor_idx_1 ON hospital.doctor ("pid");
272CREATE INDEX doctor_idx_2 ON hospital.doctor ("uuid");
273
274UPSERT
275INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
276VALUES (NEXT VALUE FOR hospital.doctor_sequence, '程淳美', '360201198609151112','2F0EB81AF9094277A958A41B59139DE1', '女', '/doctor/doctor-1.jpg',
277 '1968-05-03', '重庆医科大学', '博士', '13593812535', '北京市西城区北三环中路14-1号','chengchunmei@hospital.com', '主任医师', '首都医科大学博士生导师',
278 '擅长诊疗：心脏血管外科，包括风心病瓣膜替换，先心病，大血管疾病外科治疗。特色为冠状动脉外科，1990年以来在院内、外主做冠状动脉搭桥手术近千例，较早引进了世界最新的搭桥技术、非体外循环下冠状动脉搭桥术，获得了良好的效果 。',
279 '2004-02-15', '["从业41年","领域专家","温暖贴心"]', true,1,NOW());
280
281UPSERT
282INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
283VALUES (NEXT VALUE FOR hospital.doctor_sequence, '秦欣源', '460201197611302855','F1FDE764A9BB405596895722F1CCDB06', '男', '/doctor/doctor-2.jpg',
284 '1959-05-03', '中国医科大学', '博士', '15179382777', '北京市海淀区龙翔路9号', 'qinxinyuan@hospital.com', '主任医师', '陆军军医大学研究生导师',
285 '擅长诊疗：下肢静脉曲张的微创治疗，多种微创方法综合治疗下肢静脉曲张，包括大隐静脉激光治疗、腔内 射频治疗，Trivex透光旋切、泡沫硬化剂注射治疗，对不同曲张静脉特点选择针对性微创方法，创伤小，恢复快。',
286 '2004-12-11', '["从业46年","领域专家","快速回复"]', true,1,NOW());
287
288UPSERT
289INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
290VALUES (NEXT VALUE FOR hospital.doctor_sequence, '熊佳钰','370101197707304145', '2AE43F717E444031BC0CBB5878932B07', '男', '/doctor/doctor-3.jpg',
291 '1976-11-28', '北京协和医学院', '博士', '18658678090', '北京市朝阳区三里屯路北1楼', 'xiongjiayu@hospital.com', '主任医师', '国家远程医疗医学中心主任委员',
292 '擅长诊疗：慢性咳嗽、喘息性/呼吸困难性疾病如支气管哮喘、慢性阻塞性肺疾病、弥漫性肺疾病、肺部肿瘤、呼吸疑难危重症及胸膜疾病的诊断与治疗。主要临床研究方向：慢性气道炎症性疾病的基础与临床以及肺部疾病的介入性诊断与治疗。',
293 '2005-08-04', '["从业27年","领域专家","快速回复"]', true,1,NOW());
294
295UPSERT
296INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
297VALUES (NEXT VALUE FOR hospital.doctor_sequence, '孟明远','370101197707304145', '50595ADEF85C4B35A114A462B0FA0CDA', '男', '/doctor/doctor-4.jpg',
298 '1977-06-14', '北京协和医学院', '博士', '14580412494', '北京市海淀区花园东路8号院', 'mengmingyuan@hospital.com', '主任医师', '北京医科大学研究生导师',
299 '擅长诊疗：面神经修复与面部整形重建。在国内很早开展咬肌神经吻合、多面神经重建、游离股薄肌移植等面瘫治疗手术，填补面瘫治疗领域多项技术空白。在面部整形重建、显微外科组织瓣移植修复等领域积累了丰富经验。',
300 '2005-08-04', '["从业27年","领域专家","快速回复"]', true,1,NOW());
301
302UPSERT
303INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
304VALUES (NEXT VALUE FOR hospital.doctor_sequence, '方嘉怡','520201198509071764', 'B762C0BF9F994D23B5695EA78AE3F4F7', '女', '/doctor/doctor-5.jpg',
305 '1978-12-31', '北京协和医学院', '博士', '15597529530', '北京市西城区大乘巷1号','fangjiayi@hospital.com',
306 '主任医师', '北京医科大学、北京中医药大学研究生导师',
307 '擅长诊疗：泌尿系肿瘤，特别是肾肿瘤，肾上腺肿瘤，尿路上皮肿瘤(肾盂、输尿管、膀胱肿瘤)以及前列腺恶性肿瘤的微创治疗，膀胱及前列腺良性疾病，骶神经调控等。',
308 '2005-08-04', '["从业24年","领域专家","温暖贴心"]', true,1,NOW());
309
310UPSERT
311INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
312VALUES (NEXT VALUE FOR hospital.doctor_sequence, '黄涛','500101200212123472', '9718C444BE3646818DD264FB26EC8181', '男', '/doctor/doctor-6.jpg',
313 '1974-01-07', '北京协和医学院', '博士', '17723959830', '北京市西城区滨河里34号','huangtao@hospital.com',
314 '主任医师', '北京医科大学硕士研究生导师',
315 '擅长诊疗：临床常见恶性肿瘤的放射治疗：1.乳腺癌 2.消化系统恶性肿瘤如食管癌、直肠癌 3.头颈部恶性肿瘤 4.肺癌 5.妇科肿瘤。临床研究方向：乳腺癌个体化放射治疗，放射治疗后正常组织损伤的预防。',
316 '2005-08-04', '["从业26年","领域专家","温暖贴心"]', true,1,NOW());
317
318UPSERT
319INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
320VALUES (NEXT VALUE FOR hospital.doctor_sequence, '武萌萌','620101197707093458', '126A2D95DF2E42E4BD093FB9299623FB', '女', '/doctor/doctor-7.jpg',
321 '1977-04-04', '解放军第三军医大学', '博士', '18362319314', '北京市海淀区复兴路12号8楼','wumengmeng@hospital.com',
322 '主任医师', '中国医师协会微无创专业委员会委员',
323 '擅长诊疗：青光眼和白内障的临床诊断及治疗。对各型青光眼的诊断和疑难杂症的处理有独到见解，特别对青光眼的诊断、激光、药物和手术治疗有深入的研究。注重青光眼的早期发现和早期治疗，对青光眼患者的个体化治疗进行了深入的研究',
324 '2005-08-04', '["从业26年","领域专家","温暖贴心"]', true,1,NOW());
325
326UPSERT
327INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
328VALUES (NEXT VALUE FOR hospital.doctor_sequence, '田芳','130201200402256643', 'A1F9664A527F4DCBA48ADF312AFBC421', '女', '/doctor/doctor-8.jpg',
329 '1972-07-28', '广州医科大学', '博士', '18576200235', '北京市海淀区太平路22号','tianfang@hospital.com',
330 '主任医师', '中国医药教育协会肿瘤专家委员会委员',
331 '擅长诊疗：头颈肿瘤的外科及综合治疗（甲状腺癌、喉癌下咽癌、涎腺肿瘤、鼻腔鼻窦良恶性肿瘤、舌癌、咽旁颅底肿瘤）。对鼻窦内窥镜外科、喉内镜手术及声显微手术、耳显微手术、鼾症手术及综合治疗有丰富的临床经验。',
332 '2005-08-04', '["从业31年","领域专家","温暖贴心"]', true,1,NOW());
333
334UPSERT
335INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
336VALUES (NEXT VALUE FOR hospital.doctor_sequence, '马杰','420101199510078280', '3D3F7F2204204E30AD2F23C28A569B9A', '男', '/doctor/doctor-9.jpg',
337 '1977-02-14', '哈尔滨医科大学', '博士', '13822560280', '北京市西城区车站西街15号院-5号楼','majie@hospital.com',
338 '主任医师', '北京医师协会皮肤病专业专家委员会委员',
339 '擅长诊疗：以皮肤病理为专长，擅长常见皮肤病如：湿疹，药疹，蕁麻疹，银屑病，扁平苔癣等;色素性皮肤病如：各种色素斑、痣，白癜风等;感染性皮肤病及性病(梅毒、淋病、尖锐湿疣、非淋菌性尿道炎)等。',
340 '2005-08-04', '["从业22年","领域专家","温暖贴心"]', true,1,NOW());
341
342UPSERT
343INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
344VALUES (NEXT VALUE FOR hospital.doctor_sequence, '杜佳玉','510101198806215034', 'CD2C65C455564181ADFF84BD6A2F35C7', '女', '/doctor/doctor-10.jpg',
345 '1978-06-22', '南京医科大学', '研究生', '19738130796', '北京市丰台区望园东路2928号','dujiayu@hospital.com',
346 '主治医师', '参加多项国家级、省部级多项科研课题',
347 '擅长诊疗：应用中西医优势互补方法治疗糖尿病及其并发症(糖尿病心脑血管病、糖尿病肾病、糖尿病胃轻瘫、糖尿病周围神经病变、血糖难控因素)以及代谢综合征。',
348 '2005-08-04', '["从业17年","领域专家","温暖贴心"]', true,1,NOW());
349
350UPSERT
351INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
352VALUES (NEXT VALUE FOR hospital.doctor_sequence, '邓国栋','530201199301048406', 'FFBA296720C8495785E8A78B379C9B05', '男', '/doctor/doctor-11.jpg',
353 '1975-11-11', '天津医科大学', '博士', '13777571218', '北京市石景山区重聚路40号院-3号','dengguodong@hospital.com',
354 '副主任医师', '北京医师协会风湿免疫专科分会理事',
355 '擅长诊疗：系统性红斑狼疮、多发性肌炎、皮肌炎、类风湿关节炎、痛风、强直性脊柱炎、系统性血管炎等风湿免疫疾病，对疑难复杂危重风湿免疫疾病具备丰富诊疗经验。临床研究方向为多发性肌炎和皮肌炎，系统性红斑狼疮，痛风，风湿免疫疾病心血管系统受累等。',
356 '2005-08-04', '["从业19年","领域专家","温暖贴心"]', true,1,NOW());
357
358UPSERT
359INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
360VALUES (NEXT VALUE FOR hospital.doctor_sequence, '龙泽远','120201198705219290', '0255BFF8CCC1479C898E21D1D3B0A8E7', '男', '/doctor/doctor-12.jpg',
361 '1978-12-16', '中国医科大学', '研究生', '13069020752', '北京市海淀区玉泉路16号院','longzeyuan@hospital.com',
362 '副主治医师', '参与多项国家自然科学基金课题研究',
363 '擅长诊疗：多发性肌炎，皮肌炎，系统性红斑狼疮，类风湿关节炎等多种风湿免疫疾病诊治。对肌炎合并间质性肺疾病有深入研究。',
364 '2005-08-04', '["从业15年","领域专家","温暖贴心"]', true,1,NOW());
365
366UPSERT
367INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
368VALUES (NEXT VALUE FOR hospital.doctor_sequence, '宋秀英','650201198402246623', '0370428B5452441C9F64658F2B7BC7F1', '女', '/doctor/doctor-13.jpg',
369 '1970-12-16', '中国医科大学', '博士', '15977965686', '北京市西城区马连道南街1号院','songxiuying@hospital.com',
370 '主治医师', '中华医学会风湿病分会会员',
371 '擅长诊疗：从事风湿免疫疾病临床诊断、治疗工作20余年，有着丰富的临床经验，对风湿病重症、疑难症及长期不明原因的发热病例的诊治都具有较高的水平。',
372 '2005-08-04', '["从业28年","领域专家","温暖贴心"]', true,1,NOW());
373
374UPSERT
375INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
376VALUES (NEXT VALUE FOR hospital.doctor_sequence, '薛榕润','450201198007308399', '6BD7AB9AE6AD417A90042FF3536ECC6C', '男', '/doctor/doctor-14.jpg',
377 '1971-01-07', '中国医科大学', '博士', '15589198858', '北京市石景山区八角南路19号楼','xuerongrun@hospital.com',
378 '主治医师', '北京市泌尿外科分会结石感染组委员',
379 '擅长诊疗：经皮肾镜、输尿管镜微创治疗肾结石、输尿管结石、膀胱结石;肾上腺、肾、输尿管、膀胱肿瘤及疾病等微创治疗;肾盂输尿管狭窄,输尿管狭窄及尿道狭窄的微创治疗;前列腺增生、前列腺肿瘤的诊断治疗。',
380 '2005-08-04', '["从业26年","领域专家","温暖贴心"]', true,1,NOW());
381
382UPSERT
383INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
384VALUES (NEXT VALUE FOR hospital.doctor_sequence, '谭尚','610201197909271420', '6B4A32C097BA44F1B052B6F85C2D3E7B', '男', '/doctor/doctor-15.jpg',
385 '1968-01-07', '南京医科大学', '博士', '13923984769', '北京市丰台区久敬庄路乙1号','tanshang@hospital.com',
386 '主治医师', '北京医科大学硕士研究生导师',
387 '擅长诊疗：熟练掌握胸外科专业各类疾病的诊断、治疗，特别对肺癌的根治性手术，食管癌的根治性手术，纵隔肿瘤、胸壁恶性肿瘤的根治性手术及综合治疗。纤支镜、纵隔镜的检查及治疗，胸腔镜的各类微创手术治疗及目前开展胸腔镜下肺癌根治术。',
388 '2005-08-04', '["从业36年","领域专家","温暖贴心"]', true,1,NOW());
389
390UPSERT
391INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
392VALUES (NEXT VALUE FOR hospital.doctor_sequence, '任振国','420201198903179411', '43E06B95BD364ACD890C73D91D9881BF', '男', '/doctor/doctor-16.jpg',
393 '1972-03-17', '首都医科大学', '博士', '18068672244', '北京市朝阳区东三环北路辛2号','renzhenguo@hospital.com',
394 '主治医师', '北京口腔临床技术研究会理事',
395 '擅长诊疗：成人正畸、隐形正畸、牙周病正畸、多学科联合治疗、儿童错合畸形的早期正畸治疗、骨性错颌畸形的综合治疗。',
396 '2005-08-04', '["从业29年","领域专家","温暖贴心"]', true,1,NOW());
397
398UPSERT
399INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
400VALUES (NEXT VALUE FOR hospital.doctor_sequence, '许靖琪','220101200306063805', 'DDAF4F5F849B4D2AB6DB8CA442794A5C', '女', '/doctor/doctor-17.jpg',
401 '1973-05-08', '首都医科大学', '博士', '17267270501', '北京市东城区和平里北街21号','xujingqi@hospital.com',
402 '主治医师', '北京口腔临床技术研究会理事',
403 '擅长诊疗：擅长龋齿、牙髓病和根尖周病的诊断与系统治疗；牙周病及牙槽外科手术；牙体的美学修复。',
404 '2005-08-04', '["从业26年","领域专家","温暖贴心"]', true,1,NOW());
405
406UPSERT
407INTO hospital.doctor( "id", "name","pid" ,"uuid", "sex", "photo", "birthday", "school", "degree", "tel", "address", "email", "job", "remark", "description", "hiredate", "tag","recommended","status", "create_time")
408VALUES (NEXT VALUE FOR hospital.doctor_sequence, '吕成龙','630201198312155601', '6B946B8B0C4A42DA8DE05E62A6CDE8E6', '男', '/doctor/doctor-18.jpg',
409 '1974-12-24', '北京大学口腔医学院', '博士', '13773287399', '北京市东城区北新桥三条甲58号','lvchenglong@hospital.com',
410 '主治医师', '北京口腔临床技术研究会理事',
411 '擅长诊疗：口腔科常见病、多发病的诊疗工作，包括牙体牙髓病、牙周病、牙槽外科及口腔修复的相关疾病的全面设计与治疗。',
412 '2005-08-04', '["从业25年","领域专家","温暖贴心"]', true,1,NOW());
413
414-- 医生挂号费表 -----------------------------------------------
415DROP TABLE IF EXISTS hospital.doctor_price;
416CREATE TABLE hospital.doctor_price
417(
418 "id" INTEGER NOT NULL PRIMARY KEY,
419 "doctor_id" INTEGER,
420 "level" VARCHAR(20),
421 "price_1" DECIMAL(10,2),
422 "price_2" DECIMAL(10,2)
423);
424
425DROP SEQUENCE IF EXISTS hospital.doctor_price_sequence;
426CREATE SEQUENCE hospital.doctor_price_sequence START WITH 1 increment BY 1;
427CREATE INDEX doctor_price_idx_1 ON hospital.doctor_price ("doctor_id");
428CREATE INDEX doctor_price_idx_2 ON hospital.doctor_price ("level");
429
430UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
431VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 1, '主任医师',80,200);
432
433UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
434VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 2, '主任医师',80,200);
435
436UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
437VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 3, '主任医师',80,200);
438
439UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
440VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 4, '主任医师',80,200);
441
442UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
443VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 5, '主任医师',80,200);
444
445UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
446VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 6, '主任医师',80,200);
447
448UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
449VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 7, '主任医师',80,200);
450
451UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
452VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 8, '主任医师',80,200);
453
454UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
455VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 9, '主任医师',80,200);
456
457UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
458VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 10, '普通',50,200);
459
460UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
461VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 11, '副主任医师',60,200);
462
463UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
464VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 12, '副主任医师',60,150);
465
466UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
467VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 13, '普通',50,100);
468
469UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
470VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 14, '普通',50,100);
471
472UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
473VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 15, '普通',50,100);
474
475UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
476VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 16, '普通',50,100);
477
478UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
479VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 17, '普通',50,100);
480
481UPSERT INTO hospital.doctor_price("id","doctor_id","level","price_1","price_2")
482VALUES(NEXT VALUE FOR hospital.doctor_price_sequence, 18, '普通',50,100);
483
484-- 门诊与医生交叉表 -----------------------------------------------
485DROP TABLE IF EXISTS hospital.medical_dept_sub_and_doctor;
486CREATE TABLE hospital.medical_dept_sub_and_doctor
487(
488 "id" INTEGER PRIMARY KEY,
489 "dept_sub_id" INTEGER,
490 "doctor_id" INTEGER
491);
492
493DROP SEQUENCE IF EXISTS hospital.medical_dept_sub_and_doctor_sequence;
494CREATE SEQUENCE hospital.medical_dept_sub_and_doctor_sequence START WITH 1 increment BY 1;
495CREATE INDEX medical_dept_sub_and_doctor_idx_1 ON hospital.medical_dept_sub_and_doctor ("dept_sub_id");
496CREATE INDEX medical_dept_sub_and_doctor_idx_2 ON hospital.medical_dept_sub_and_doctor ("doctor_id");
497
498UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
499VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,18,1);
500
501UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
502VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,20,2);
503
504UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
505VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,9,3);
506
507UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
508VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,19,4);
509
510UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
511VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,26,5);
512
513UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
514VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,26,6);
515
516UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
517VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,4,7);
518
519UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
520VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,26,8);
521
522UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
523VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,20,9);
524
525UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
526VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,12,10);
527
528UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
529VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,14,11);
530
531UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
532VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,14,12);
533
534UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
535VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,14,13);
536
537UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
538VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,13,14);
539
540UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
541VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,26,15);
542
543UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
544VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,2,16);
545
546UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
547VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,2,17);
548
549UPSERT INTO hospital.medical_dept_sub_and_doctor("id","dept_sub_id","doctor_id")
550VALUES(NEXT VALUE FOR hospital.medical_dept_sub_and_doctor_sequence,2,18);
551
552-- 医生出诊表 -----------------------------------------------
553DROP TABLE IF EXISTS hospital.doctor_work_plan;
554CREATE TABLE hospital.doctor_work_plan(
555 "id" INTEGER NOT NULL PRIMARY KEY ,
556 "doctor_id" INTEGER,
557 "dept_sub_id" INTEGER,
558 "date" DATE,
559-- 当天挂号上限人数
560 "maximum" SMALLINT,
561-- 实际挂号人数
562 "num" SMALLINT
563);
564
565DROP SEQUENCE IF EXISTS hospital.doctor_work_plan_sequence;
566CREATE SEQUENCE hospital.doctor_work_plan_sequence START WITH 1 increment BY 1;
567CREATE INDEX doctor_work_plan_idx_1 ON hospital.doctor_work_plan ("doctor_id");
568CREATE INDEX doctor_work_plan_idx_2 ON hospital.doctor_work_plan ("dept_sub_id");
569CREATE INDEX doctor_work_plan_idx_3 ON hospital.doctor_work_plan ("date");
570
571UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
572VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,16,2,TO_DATE('2022-09-23'),45,0);
573
574UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
575VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,17,2,TO_DATE('2022-09-23'),45,0);
576
577UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
578VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,18,2,TO_DATE('2022-09-23'),45,0);
579
580UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
581VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,16,2,TO_DATE('2022-09-24'),45,0);
582
583UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
584VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,16,2,TO_DATE('2022-09-25'),45,0);
585
586UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
587VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,16,2,TO_DATE('2022-09-26'),45,0);
588
589UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
590VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,1,18,TO_DATE('2022-09-23'),45,0);
591
592UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
593VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,2,20,TO_DATE('2022-09-23'),45,0);
594
595UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
596VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,9,20,TO_DATE('2022-09-23'),45,0);
597
598UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
599VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,9,20,TO_DATE('2022-09-24'),45,0);
600
601UPSERT INTO hospital.doctor_work_plan("id","doctor_id","dept_sub_id","date","maximum","num")
602VALUES(NEXT VALUE FOR hospital.doctor_work_plan_sequence,9,20,TO_DATE('2022-09-25'),45,0);
603
604-- 医生出诊时间段表-----------------------------------------------
605DROP TABLE IF EXISTS hospital.doctor_work_plan_schedule;
606CREATE TABLE hospital.doctor_work_plan_schedule(
607 "id" INTEGER NOT NULL PRIMARY KEY ,
608 "work_plan_id" INTEGER,
609 "slot" TINYINT,
610 "maximum" SMALLINT,
611 "num" SMALLINT
612);
613
614DROP SEQUENCE IF EXISTS hospital.doctor_work_plan_schedule_sequence;
615CREATE SEQUENCE hospital.doctor_work_plan_schedule_sequence START WITH 1 increment BY 1;
616
617CREATE INDEX doctor_work_plan_schedule_idx_1 ON hospital.doctor_work_plan_schedule ("work_plan_id");
618
619UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
620VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,1,3,0);
621
622UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
623VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,2,3,0);
624
625UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
626VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,3,3,0);
627
628UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
629VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,4,3,0);
630
631UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
632VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,5,3,0);
633
634UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
635VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,6,3,0);
636
637UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
638VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,7,3,0);
639
640UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
641VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,8,3,0);
642
643UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
644VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,9,3,0);
645
646UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
647VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,10,3,0);
648
649UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
650VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,11,3,0);
651
652UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
653VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,12,3,0);
654
655UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
656VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,13,3,0);
657
658UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
659VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,14,3,0);
660
661UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
662VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,1,15,3,0);
663
664UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
665VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,2,1,3,0);
666
667UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
668VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,2,2,3,0);
669
670UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
671VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,2,4,3,0);
672
673UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
674VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,2,8,3,0);
675
676UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
677VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,5,1,3,0);
678
679UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
680VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,6,1,3,0);
681
682UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
683VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,3,8,3,0);
684
685UPSERT INTO hospital.doctor_work_plan_schedule("id","work_plan_id","slot","maximum","num")
686VALUES(NEXT VALUE FOR hospital.doctor_work_plan_schedule_sequence,3,1,3,0);
687
688
689-- 挂号表-----------------------------------------------
690DROP TABLE IF EXISTS hospital.medical_registration;
691CREATE TABLE hospital.medical_registration(
692 "id" INTEGER NOT NULL PRIMARY KEY ,
693 "patient_card_id" INTEGER,
694 "work_plan_id" INTEGER,
695 "doctor_schedule_id" INTEGER,
696 "doctor_id" INTEGER,
697 "dept_sub_id" INTEGER,
698 "date" DATE,
699 "slot" TINYINT,
700 "amount" DECIMAL(10,2),
701 "out_trade_no" CHAR(32),
702 "prepay_id" CHAR(64),
703 "transaction_id" CHAR(32),
704 -- 付款状态: 1.未付款，2.已付款，3.已退款，4.已过期
705 "payment_status" TINYINT,
706 "create_time" DATE
707);
708
709DROP SEQUENCE IF EXISTS hospital.medical_registration_sequence;
710CREATE SEQUENCE hospital.medical_registration_sequence START WITH 1 increment BY 1;
711
712CREATE INDEX medical_registration_idx_1 ON hospital.medical_registration ("patient_card_id");
713CREATE INDEX medical_registration_idx_2 ON hospital.medical_registration ("work_plan_id");
714CREATE INDEX medical_registration_idx_3 ON hospital.medical_registration ("doctor_schedule_id");
715CREATE INDEX medical_registration_idx_4 ON hospital.medical_registration ("doctor_id");
716CREATE INDEX medical_registration_idx_5 ON hospital.medical_registration ("dept_sub_id");
717CREATE INDEX medical_registration_idx_6 ON hospital.medical_registration ("date");
718CREATE INDEX medical_registration_idx_7 ON hospital.medical_registration ("out_trade_no");
719CREATE INDEX medical_registration_idx_8 ON hospital.medical_registration ("prepay_id");
720CREATE INDEX medical_registration_idx_9 ON hospital.medical_registration ("transaction_id");
721CREATE INDEX medical_registration_idx_10 ON hospital.medical_registration ("payment_status");
722
723
724-- 处方表-----------------------------------------------
725DROP TABLE IF EXISTS hospital.doctor_prescription;
726CREATE TABLE hospital.doctor_prescription(
727 "id" INTEGER NOT NULL PRIMARY KEY,
728 "uuid" VARCHAR(32),
729 "patient_card_id" INTEGER,
730 -- 诊断结果
731 "diagnosis" VARCHAR,
732 "sub_dept_id" INTEGER,
733 "doctor_id" INTEGER,
734 "registration_id" INTEGER,
735 --处方
736 "rp" VARCHAR
737);
738
739DROP SEQUENCE IF EXISTS hospital.doctor_prescription_sequence;
740CREATE SEQUENCE hospital.doctor_prescription_sequence START WITH 1 increment BY 1;
741
742CREATE INDEX doctor_prescription_idx_1 ON hospital.doctor_prescription ("uuid");
743CREATE INDEX doctor_prescription_idx_2 ON hospital.doctor_prescription ("patient_card_id");
744CREATE INDEX doctor_prescription_idx_3 ON hospital.doctor_prescription ("sub_dept_id");
745CREATE INDEX doctor_prescription_idx_4 ON hospital.doctor_prescription ("doctor_id");
746CREATE INDEX doctor_prescription_idx_5 ON hospital.doctor_prescription ("registration_id");
747
748-- 医生视频咨询表-----------------------------------------------
749DROP TABLE IF EXISTS hospital.doctor_consult;
750CREATE TABLE hospital.doctor_consult(
751 "id" INTEGER NOT NULL PRIMARY KEY,
752 "patient_card_id" INTEGER,
753 "sub_dept_id" INTEGER,
754 "doctor_id" INTEGER,
755 "start_time" DATE,
756 "end_time" DATE,
757 "out_trade_no" CHAR(32),
758 "amount" DECIMAL(10,2),
759 "prepay_id" CHAR(64),
760 "transaction_id" CHAR(32),
761-- 1.未付款，2.已付款，3.已退款，4.已过期
762 "payment_status" TINYINT,
763-- 1.未开始，2.进行中，3.已结束，4.已关闭
764 "status" TINYINT,
765 "files" VARCHAR,
766 "create_time" DATE
767);
768
769DROP SEQUENCE IF EXISTS hospital.doctor_consult_sequence;
770CREATE SEQUENCE hospital.doctor_consult_sequence START WITH 1 increment BY 1;
771
772CREATE INDEX doctor_consult_idx_1 ON hospital.doctor_consult ("patient_card_id");
773CREATE INDEX doctor_consult_idx_2 ON hospital.doctor_consult ("sub_dept_id");
774CREATE INDEX doctor_consult_idx_3 ON hospital.doctor_consult ("doctor_id");
775CREATE INDEX doctor_consult_idx_4 ON hospital.doctor_consult ("out_trade_no");
776CREATE INDEX doctor_consult_idx_5 ON hospital.doctor_consult ("prepay_id");
777CREATE INDEX doctor_consult_idx_6 ON hospital.doctor_consult ("transaction_id");
778CREATE INDEX doctor_consult_idx_7 ON hospital.doctor_consult ("status");
779CREATE INDEX doctor_consult_idx_8 ON hospital.doctor_consult ("create_time");
780
781
782-- MIS端行为表 -----------------------------------------------
783
784DROP TABLE IF EXISTS hospital.mis_action;
785
786CREATE TABLE hospital.mis_action
787(
788 "id" INTEGER NOT NULL PRIMARY KEY,
789 "action_code" VARCHAR,
790 "action_name" VARCHAR
791);
792
793DROP SEQUENCE IF EXISTS hospital.mis_action_sequence;
794CREATE SEQUENCE hospital.mis_action_sequence START WITH 1 increment BY 1;
795
796UPSERT
797INTO hospital.mis_action("id","action_code","action_name")
798VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'INSERT','添加');
799
800UPSERT
801INTO hospital.mis_action("id","action_code","action_name")
802VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'DELETE','删除');
803
804UPSERT
805INTO hospital.mis_action("id","action_code","action_name")
806VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'UPDATE','修改');
807
808UPSERT
809INTO hospital.mis_action("id","action_code","action_name")
810VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'SELECT','查询');
811
812UPSERT
813INTO hospital.mis_action("id","action_code","action_name")
814VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'APPROVAL','审批');
815
816UPSERT
817INTO hospital.mis_action("id","action_code","action_name")
818VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'EXPORT','导出');
819
820UPSERT
821INTO hospital.mis_action("id","action_code","action_name")
822VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'BACKUP','备份');
823
824UPSERT
825INTO hospital.mis_action("id","action_code","action_name")
826VALUES(NEXT VALUE FOR hospital.mis_action_sequence,'ARCHIVE','归档');
827
828
829-- MIS端模块表 -----------------------------------------------
830
831DROP TABLE IF EXISTS hospital.mis_module;
832
833CREATE TABLE hospital.mis_module
834(
835 "id" SMALLINT NOT NULL PRIMARY KEY,
836 "module_code" VARCHAR,
837 "module_name" VARCHAR
838);
839
840
841UPSERT INTO hospital.mis_module("id","module_code","module_name")
842VALUES(1,'MIS_USER','MIS端用户管理');
843
844UPSERT INTO hospital.mis_module("id","module_code","module_name")
845VALUES(2,'PATIENT_USER','患者端用户管理');
846
847
848UPSERT INTO hospital.mis_module("id","module_code","module_name")
849VALUES(3,'WORKER_USER','医护端用户管理');
850
851UPSERT INTO hospital.mis_module("id","module_code","module_name")
852VALUES(4,'DEPT','部门管理');
853
854UPSERT INTO hospital.mis_module("id","module_code","module_name")
855VALUES(5,'MEDICAL_DEPT','医疗科室管理');
856
857UPSERT INTO hospital.mis_module("id","module_code","module_name")
858VALUES(6,'MEDICAL_DEPT_SUB','医疗诊室管理');
859
860UPSERT INTO hospital.mis_module("id","module_code","module_name")
861VALUES(7,'SCHEDULE','出诊管理');
862
863UPSERT INTO hospital.mis_module("id","module_code","module_name")
864VALUES(8,'REGISTRATION','挂号管理');
865
866UPSERT INTO hospital.mis_module("id","module_code","module_name")
867VALUES(9,'VIDEO_DIAGNOSE','视频问诊管理');
868
869UPSERT INTO hospital.mis_module("id","module_code","module_name")
870VALUES(10,'DOCTOR','医生管理');
871
872UPSERT INTO hospital.mis_module("id","module_code","module_name")
873VALUES(11,'NURSE','护士管理');
874
875UPSERT INTO hospital.mis_module("id","module_code","module_name")
876VALUES(12,'NURSING_ASSISTANT','护工管理');
877
878UPSERT INTO hospital.mis_module("id","module_code","module_name")
879VALUES(13,'DOCTOR_PRICE','诊费管理');
880
881UPSERT INTO hospital.mis_module("id","module_code","module_name")
882VALUES(14,'SYSTEM','系统管理');
883
884-- MIS端行为表 -----------------------------------------------
885DROP TABLE IF EXISTS hospital.mis_action;
886
887CREATE TABLE hospital.mis_action
888(
889 "id" SMALLINT NOT NULL PRIMARY KEY,
890 "action_code" VARCHAR,
891 "action_name" VARCHAR
892);
893
894UPSERT INTO hospital.mis_action("id","action_code","action_name")
895VALUES(1,'INSERT','添加');
896
897UPSERT INTO hospital.mis_action("id","action_code","action_name")
898VALUES(2,'DELETE','删除');
899
900UPSERT INTO hospital.mis_action("id","action_code","action_name")
901VALUES(3,'UPDATE','修改');
902
903UPSERT INTO hospital.mis_action("id","action_code","action_name")
904VALUES(4,'SELECT','查询');
905
906UPSERT INTO hospital.mis_action("id","action_code","action_name")
907VALUES(5,'APPROVAL','审批');
908
909UPSERT INTO hospital.mis_action("id","action_code","action_name")
910VALUES(6,'IMPORT','导入');
911
912UPSERT INTO hospital.mis_action("id","action_code","action_name")
913VALUES(7,'EXPORT','导出');
914
915UPSERT INTO hospital.mis_action("id","action_code","action_name")
916VALUES(8,'BACKUP','备份');
917
918UPSERT INTO hospital.mis_action("id","action_code","action_name")
919VALUES(9,'ARCHIVE','归档');
920
921UPSERT INTO hospital.mis_action("id","action_code","action_name")
922VALUES(10,'DIAGNOSE','诊断');
923
924-- MIS端权限表 -----------------------------------------------
925DROP TABLE IF EXISTS hospital.mis_permission;
926
927CREATE TABLE hospital.mis_permission
928(
929 "id" SMALLINT NOT NULL PRIMARY KEY,
930 "permission_code" VARCHAR,
931 "module_id" SMALLINT,
932 "action_id" SMALLINT
933);
934
935UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
936VALUES(0,'ROOT',0,0);
937
938UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
939VALUES(1,'MIS_USER:INSERT',1,1);
940
941UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
942VALUES(2,'MIS_USER:DELETE',1,2);
943
944UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
945VALUES(3,'MIS_USER:UPDATE',1,3);
946
947UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
948VALUES(4,'MIS_USER:SELECT',1,4);
949
950UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
951VALUES(5,'PATIENT_USER:INSERT',2,1);
952
953UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
954VALUES(6,'PATIENT_USER:DELETE',2,2);
955
956UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
957VALUES(7,'PATIENT_USER:UPDATE',2,3);
958
959UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
960VALUES(8,'PATIENT_USER:SELECT',2,4);
961
962UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
963VALUES(9,'WORKER_USER:INSERT',3,1);
964
965UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
966VALUES(10,'WORKER_USER:DELETE',3,2);
967
968UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
969VALUES(11,'WORKER_USER:UPDATE',3,3);
970
971UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
972VALUES(12,'WORKER_USER:SELECT',3,4);
973
974UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
975VALUES(13,'DEPT:INSERT',4,1);
976
977UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
978VALUES(14,'DEPT:DELETE',4,2);
979
980UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
981VALUES(15,'DEPT:UPDATE',4,3);
982
983UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
984VALUES(16,'DEPT:SELECT',4,4);
985
986UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
987VALUES(17,'MEDICAL_DEPT:INSERT',5,1);
988
989UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
990VALUES(18,'MEDICAL_DEPT:DELETE',5,2);
991
992UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
993VALUES(19,'MEDICAL_DEPT:UPDATE',5,3);
994
995UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
996VALUES(20,'MEDICAL_DEPT:SELECT',5,4);
997
998UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
999VALUES(21,'MEDICAL_DEPT_SUB:INSERT',6,1);
1000
1001UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1002VALUES(22,'MEDICAL_DEPT_SUB:DELETE',6,2);
1003
1004UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1005VALUES(23,'MEDICAL_DEPT_SUB:UPDATE',6,3);
1006
1007UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1008VALUES(24,'MEDICAL_DEPT_SUB:SELECT',6,4);
1009
1010UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1011VALUES(25,'SCHEDULE:INSERT',7,1);
1012
1013UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1014VALUES(26,'SCHEDULE:DELETE',7,2);
1015
1016UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1017VALUES(27,'SCHEDULE:UPDATE',7,3);
1018
1019UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1020VALUES(28,'SCHEDULE:SELECT',7,4);
1021
1022UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1023VALUES(29,'REGISTRATION:INSERT',8,1);
1024
1025UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1026VALUES(30,'REGISTRATION:DELETE',8,2);
1027
1028UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1029VALUES(31,'REGISTRATION:UPDATE',8,3);
1030
1031UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1032VALUES(32,'REGISTRATION:SELECT',8,4);
1033
1034UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1035VALUES(33,'VIDEO_DIAGNOSE:INSERT',9,1);
1036
1037UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1038VALUES(34,'VIDEO_DIAGNOSE:DELETE',9,2);
1039
1040UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1041VALUES(35,'VIDEO_DIAGNOSE:UPDATE',9,3);
1042
1043UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1044VALUES(36,'VIDEO_DIAGNOSE:SELECT',9,4);
1045
1046UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1047VALUES(37,'VIDEO_DIAGNOSE:DIAGNOSE',9,5);
1048
1049UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1050VALUES(38,'DOCTOR:INSERT',10,1);
1051
1052UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1053VALUES(39,'DOCTOR:DELETE',10,2);
1054
1055UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1056VALUES(40,'DOCTOR:UPDATE',10,3);
1057
1058UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1059VALUES(41,'DOCTOR:SELECT',10,4);
1060
1061UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1062VALUES(42,'NURSE:INSERT',11,1);
1063
1064UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1065VALUES(43,'NURSE:DELETE',11,2);
1066
1067UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1068VALUES(44,'NURSE:UPDATE',11,3);
1069
1070UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1071VALUES(45,'NURSE:SELECT',11,4);
1072
1073UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1074VALUES(46,'NURSING_ASSISTANT:INSERT',12,1);
1075
1076UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1077VALUES(47,'NURSING_ASSISTANT:DELETE',12,2);
1078
1079UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1080VALUES(48,'NURSING_ASSISTANT:UPDATE',12,3);
1081
1082UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1083VALUES(49,'NURSING_ASSISTANT:SELECT',12,4);
1084
1085UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1086VALUES(46,'DOCTOR_PRICE:INSERT',13,1);
1087
1088UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1089VALUES(47,'DOCTOR_PRICE:DELETE',14,2);
1090
1091UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1092VALUES(48,'DOCTOR_PRICE:UPDATE',15,3);
1093
1094UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1095VALUES(49,'DOCTOR_PRICE:SELECT',16,4);
1096
1097UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1098VALUES(50,'SYSTEM:UPDATE',16,3);
1099
1100UPSERT INTO hospital.mis_permission("id","permission_code","module_id","action_id")
1101VALUES(51,'SYSTEM:SELECT',16,4);
1102
1103
1104-- MIS端角色表 -----------------------------------------------
1105DROP TABLE IF EXISTS hospital.mis_role;
1106
1107CREATE TABLE hospital.mis_role
1108(
1109 "id" INTEGER NOT NULL PRIMARY KEY,
1110 "role_name" VARCHAR(50),
1111 "remark" VARCHAR(50)
1112);
1113
1114DROP SEQUENCE IF EXISTS hospital.mis_role_sequence;
1115CREATE SEQUENCE hospital.mis_role_sequence START WITH 1 increment BY 1;
1116
1117UPSERT INTO hospital.mis_role("id","role_name","remark")
1118VALUES(0, '超级管理员','超级管理员');
1119
1120UPSERT INTO hospital.mis_role("id","role_name","remark")
1121VALUES(NEXT VALUE FOR hospital.mis_role_sequence, '医生','医生角色');
1122
1123UPSERT INTO hospital.mis_role("id","role_name","remark")
1124VALUES(NEXT VALUE FOR hospital.mis_role_sequence, '视频问诊医生','可以视频问诊的医生');
1125
1126-- MIS端角色权限表 -----------------------------------------------
1127DROP TABLE IF EXISTS hospital.mis_role_permission;
1128
1129CREATE TABLE hospital.mis_role_permission
1130(
1131 "id" INTEGER NOT NULL PRIMARY KEY,
1132 "role_id" INTEGER,
1133 "permission_id" SMALLINT
1134);
1135
1136DROP SEQUENCE IF EXISTS hospital.mis_role_permission_sequence;
1137CREATE SEQUENCE hospital.mis_role_permission_sequence START WITH 1 increment BY 1;
1138
1139-- 超级管理员角色拥有最高权限
1140UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1141VALUES(0, 0,0);
1142
1143-- 医生拥有查询MIS帐户权限（仅限自己）
1144UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1145VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 1,4);
1146
1147-- 医生拥有查询部门权限
1148UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1149VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 1,16);
1150
1151-- 医生拥有查询科室权限
1152UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1153VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 1,20);
1154
1155-- 医生拥有查询诊室权限
1156UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1157VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 1,24);
1158
1159-- 医生拥有查询出诊计划权限
1160UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1161VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 1,28);
1162
1163-- 医生拥有查询挂号记录权限（仅限自己的患者）
1164UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1165VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 1,32);
1166
1167-- 视频问诊医生拥有查询问诊记录权限（仅限自己的患者）
1168UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1169VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 2,36);
1170
1171-- 视频问诊医生拥有视频问诊权限
1172UPSERT INTO hospital.mis_role_permission("id","role_id","permission_id")
1173VALUES(NEXT VALUE FOR hospital.mis_role_permission_sequence, 2,37);
1174
1175-- MIS端部门表 -----------------------------------------------
1176DROP TABLE IF EXISTS hospital.mis_dept;
1177
1178CREATE TABLE hospital.mis_dept
1179(
1180 "id" INTEGER NOT NULL PRIMARY KEY,
1181 "name" VARCHAR
1182);
1183DROP SEQUENCE IF EXISTS hospital.mis_dept_sequence;
1184CREATE SEQUENCE hospital.mis_dept_sequence START WITH 1 increment BY 1;
1185
1186UPSERT INTO hospital.mis_dept("id","name")
1187VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '院长办公室');
1188
1189UPSERT INTO hospital.mis_dept("id","name")
1190VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '人力资源部');
1191
1192UPSERT INTO hospital.mis_dept("id","name")
1193VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '财务部');
1194
1195UPSERT INTO hospital.mis_dept("id","name")
1196VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '保卫部');
1197
1198UPSERT INTO hospital.mis_dept("id","name")
1199VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '后勤部');
1200
1201UPSERT INTO hospital.mis_dept("id","name")
1202VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '工程部');
1203
1204UPSERT INTO hospital.mis_dept("id","name")
1205VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '基建部');
1206
1207UPSERT INTO hospital.mis_dept("id","name")
1208VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '物资部');
1209
1210UPSERT INTO hospital.mis_dept("id","name")
1211VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '运营部');
1212
1213UPSERT INTO hospital.mis_dept("id","name")
1214VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '医务部');
1215
1216UPSERT INTO hospital.mis_dept("id","name")
1217VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '门诊部');
1218
1219UPSERT INTO hospital.mis_dept("id","name")
1220VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '护理部');
1221
1222UPSERT INTO hospital.mis_dept("id","name")
1223VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '科研教育部');
1224
1225UPSERT INTO hospital.mis_dept("id","name")
1226VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '国际合作部');
1227
1228UPSERT INTO hospital.mis_dept("id","name")
1229VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '信息中心');
1230
1231UPSERT INTO hospital.mis_dept("id","name")
1232VALUES(NEXT VALUE FOR hospital.mis_dept_sequence, '公共关系部');
1233
1234
1235-- MIS端用户表 -----------------------------------------------
1236DROP TABLE IF EXISTS hospital.mis_user;
1237
1238CREATE TABLE hospital.mis_user
1239(
1240 "id" INTEGER NOT NULL PRIMARY KEY,
1241 "username" VARCHAR(50),
1242 "password" VARCHAR,
1243 "name" VARCHAR(20),
1244 "sex" VARCHAR(1),
1245 "tel" VARCHAR(11),
1246 "email" VARCHAR(100),
1247 "dept_id" INTEGER,
1248 "job" VARCHAR(20),
1249 "ref_id" INTEGER,
1250-- 1有效，2离职，3禁用
1251 "status" TINYINT,
1252 "create_time" DATE
1253);
1254
1255DROP SEQUENCE IF EXISTS hospital.mis_user_sequence;
1256CREATE SEQUENCE hospital.mis_user_sequence START WITH 1 increment BY 1;
1257
1258CREATE INDEX mis_user_idx_1 ON hospital.mis_user ("username");
1259CREATE INDEX mis_user_idx_2 ON hospital.mis_user ("dept_id");
1260CREATE INDEX mis_user_idx_3 ON hospital.mis_user ("job");
1261CREATE INDEX mis_user_idx_4 ON hospital.mis_user ("dept_id");
1262CREATE INDEX mis_user_idx_5 ON hospital.mis_user ("status");
1263
1264UPSERT INTO hospital.mis_user(
1265 "id","username","password","name","sex","tel","email","dept_id","job","status","create_time"
1266)
1267VALUES(
1268 0,'admin','061575f43e456772015c0032c0531edf','超级管理员','男',NULL,NULL,NULL,NULL,1,NOW()
1269);
1270
1271
1272-- MIS端用户角色表 -----------------------------------------------
1273DROP TABLE IF EXISTS hospital.mis_user_role;
1274
1275CREATE TABLE hospital.mis_user_role
1276(
1277 "id" INTEGER NOT NULL PRIMARY KEY,
1278 "user_id" INTEGER,
1279 "role_id" INTEGER
1280);
1281
1282DROP SEQUENCE IF EXISTS hospital.mis_user_role_sequence;
1283CREATE SEQUENCE hospital.mis_user_role_sequence START WITH 1 increment BY 1;
1284
1285CREATE INDEX mis_user_role_idx_1 ON hospital.mis_user_role ("user_id");
1286CREATE INDEX mis_user_role_idx_2 ON hospital.mis_user_role ("role_id");
1287
1288-- 超级管理员MIS帐户关联超级管理员角色
1289UPSERT INTO hospital.mis_user_role(
1290 "id","user_id","role_id"
1291)
1292VALUES(0,0,0);
1293
1294
1295-- 视频问诊订单表 -----------------------------------------------
1296DROP TABLE IF EXISTS hospital.video_diagnose;
1297CREATE TABLE hospital.video_diagnose
1298(
1299 "id" INTEGER NOT NULL PRIMARY KEY,
1300 "patient_card_id" INTEGER,
1301 "doctor_id" INTEGER,
1302 "out_trade_no" CHAR(32),
1303 "amount" DECIMAL(10, 2),
1304 -- 付款状态：1.未付款，2.已付款，3.已退款，4.已过期
1305 "payment_status" TINYINT,
1306 "prepay_id" CHAR(64),
1307 "transaction_id" CHAR(32),
1308 "expect_start" DATE,
1309 "expect_end" DATE,
1310 "real_start" DATE,
1311 "real_end" DATE,
1312 -- 问诊状态：1.未开始，2.问诊中，3.已结束
1313 "status" TINYINT,
1314 "create_time" DATE
1315);
1316
1317DROP SEQUENCE IF EXISTS hospital.video_diagnose_sequence;
1318CREATE SEQUENCE hospital.video_diagnose_sequence START WITH 1 increment BY 1;
1319
1320CREATE INDEX video_diagnose_idx_1 ON hospital.video_diagnose ("patient_card_id");
1321CREATE INDEX video_diagnose_idx_2 ON hospital.video_diagnose ("doctor_id");
1322CREATE INDEX video_diagnose_idx_3 ON hospital.video_diagnose ("out_trade_no");
1323CREATE INDEX video_diagnose_idx_4 ON hospital.video_diagnose ("payment_status");
1324CREATE INDEX video_diagnose_idx_5 ON hospital.video_diagnose ("prepay_id");
1325CREATE INDEX video_diagnose_idx_6 ON hospital.video_diagnose ("transaction_id");
1326CREATE INDEX video_diagnose_idx_7 ON hospital.video_diagnose ("expect_start");
1327CREATE INDEX video_diagnose_idx_8 ON hospital.video_diagnose ("expect_end");
1328CREATE INDEX video_diagnose_idx_9 ON hospital.video_diagnose ("status");
1329
1330-- 视频问诊资料表 -----------------------------------------------
1331DROP TABLE IF EXISTS hospital.video_diagnose_file;
1332CREATE TABLE hospital.video_diagnose_file(
1333 "id" INTEGER NOT NULL PRIMARY KEY,
1334 "video_diagnose_id" INTEGER,
1335 "filename" VARCHAR(100),
1336 "path" VARCHAR(300),
1337 "create_time" DATE
1338);
1339
1340DROP SEQUENCE IF EXISTS hospital.video_diagnose_file_sequence;
1341CREATE SEQUENCE hospital.video_diagnose_file_sequence START WITH 1 increment BY 1;
1342
1343CREATE INDEX video_diagnose_file_idx_1 ON hospital.video_diagnose_file ("video_diagnose_id");
1344CREATE INDEX video_diagnose_file_idx_2 ON hospital.video_diagnose_file ("create_time");