表设计
受检人员表
| CREATE TABLE CheckedUser ( |
| ch_id varchar(20), |
| ch_account varchar(20), |
| ch_password varchar(20), |
| ch_check_status integer, |
| ch_health_status integer, |
| ch_name varchar(255), |
| ch_identity_type integer , |
| ch_identity varchar(50), |
| ch_face varchar(255), |
| ch_QRcode varchar(255) , |
| ch_entry_time datetime, |
| PRIMARY KEY (ch_id) |
| ) |
| |
| |
高级人员表
| CREATE TABLE StaffUser ( |
| st_id varchar(20), |
| st_account varchar(20), |
| st_password varchar(20), |
| st_post varchar(10), |
| st_status integer, |
| st_name varchar(255), |
| st_identity_type integer , |
| st_identity varchar(50), |
| st_phone integer, |
| st_address varchar(255), |
| PRIMARY KEY (st_id) |
| ) |
实验室表
| CREATE TABLE Laboratory ( |
| lab_id varchar(20), |
| lab_name varchar(255), |
| lab_address varchar(255), |
| PRIMARY KEY (lab_id) |
| ) |
| |
核酸检测点表
| CREATE TABLE PCRPoint ( |
| po_id varchar(20), |
| po_name varchar(255), |
| po_address varchar(255), |
| po_status integer, |
| PRIMARY KEY (po_id) |
| ) |
核酸安排表
| CREATE TABLE TaskArrangement ( |
| te_id varchar(20), |
| te_time varchar(255), |
| te_address varchar(255), |
| te_tube_numbser integer, |
| te_medical_id varchar(20), |
| te_manager_id varchar(20), |
| te_point_id varchar(20), |
| te_delivery_id varchar(20), |
| te_laboratory_id varchar(20), |
| PRIMARY KEY (te_id) |
| ) |
核酸安排与医护人员对照表
| CREATE TABLE TasktMedicalContrast ( |
| con_id varchar(20), |
| tesk_id varchar(20), |
| medical_id varchar(20), |
| PRIMARY KEY (con_id) |
| ) |
采样信息表
| CREATE TABLE PCRSample ( |
| sam_id varchar(20), |
| tube_id varchar(20), |
| tube_production_date date, |
| sam_type integer, |
| sam_mixed integer, |
| sam_test_number integer, |
| sam_collection_time datetime, |
| medical_id varchar(20), |
| sam_result integer , |
| sam_result_time datetime, |
| inspector_id varchar(20), |
| logistics_id varchar(20), |
| PRIMARY KEY (sam_id) |
| ) |
采样信息与人员对照表
| CREATE TABLE SampleCheckedContrast ( |
| con_id varchar(20), |
| ch_id varchar(20), |
| sam_id varchar(20), |
| PRIMARY KEY (con_id) |
| ) |
物流信息表
| CREATE TABLE Logistics ( |
| logi_id varchar(20), |
| logi_status varchar(20), |
| delivery_id varchar(20), |
| point_id varchar(20), |
| logi_point_time datetime, |
| laboratory_id varchar(20), |
| inspector_id varchar(20), |
| logi_lab_time datetime, |
| PRIMARY KEY (logi_id) |
| ) |
核酸统计结果表
| CREATE TABLE PCRStatisticalResults ( |
| stati_id varchar(20), |
| stati_date date, |
| stati_area varchar(255), |
| stati_total_number integer, |
| stati_positive_number integer, |
| PRIMARY KEY (stati_id) |
| ) |
消息表
| CREATE TABLE PCRMessages ( |
| mes_id varchar(20), |
| mes_manager_id varchar(20), |
| mes_time datetime, |
| mes_content varchar(255), |
| mes_status integer, |
| PRIMARY KEY (mes_id) |
| ) |
设置索引
消息表内容处
| use PCRWork |
| go |
| create index index_content on PCRMessages(mes_content) |
| go |
| exec sp_helpindex PCRMessages |
| go |
| |
核酸检测点名称处
| use PCRWork |
| go |
| create unique index index_PCRPoint_name on PCRPoint(po_name) |
| go |
| exec sp_helpindex PCRPoint |
| go |
| |
采样信息核酸结果处
| use PCRWork |
| go |
| create index index_PCRSample_res on PCRSample(sam_result) |
| go |
| exec sp_helpindex PCRSample |
| go |
| |
视图
阳性病例信息
| use PCRWork |
| go |
| create view V_positive_user |
| as |
| select u.ch_name,u.ch_identity,u.ch_identity_type,u.ch_check_status,u.ch_health_status |
| from CheckedUser u,PCRSample s |
| where s.sam_result=1 |
| go |
| |
| select *from V_positive_user |
| go |
政府系统管理人员的信息
| use PCRWork |
| go |
| create view V_manager_user |
| as |
| select *from StaffUser where st_post='1' |
| go |
| |
| select *from V_manager_user |
| go |
医护人员的信息
| create view V_medical_user |
| as |
| select *from StaffUser where st_post='2' |
物流人员的信息
| create view V_delivery_user |
| as |
| select *from StaffUser where st_post='3' |
| |
实验室检测人员的信息
| create view V_inspector_user |
| as |
| select *from StaffUser where st_post='4' |
| |
存储过程
查看今日的核酸结果为阳性的采样信息
| |
| |
| CREATE PROCEDURE Pstu_positive_sample |
| AS |
| SELECT *FROM PCRSample |
| WHERE CONVERT(varchar(10),sam_collection_time,120) =CONVERT(varchar(10),GETDATE(),120) |
| AND sam_result=1 |
| GO |
| |
| exec Pstu_positive_sample |
输入试管编号,查看该试管的受检人员
| CREATE PROCEDURE Pstu_sample_checked |
| @tube_id varchar(20) |
| AS |
| SELECT u.ch_name,u.ch_identity,u.ch_identity_type,u.ch_check_status,u.ch_health_status |
| FROM PCRSample s,SampleCheckedContrast c,CheckedUser u |
| WHERE c.sam_id=s.sam_id and c.ch_id=u.ch_id |
| and s.tube_id=@tube_id |
| go |
| |
| exec Pstu_sample_checked @tube_id='XXXXX' |
| |
触发器
| CREATE TRIGGER Tri_auto_update_positive |
| ON PCRSample |
| AFTER UPDATE |
| AS |
| BEGIN |
| IF UPDATE(sam_result) |
| BEGIN |
| DECLARE @sam_result integer |
| DECLARE @sam_test_number integer |
| DECLARE @sam_collection_time datetime |
| SELECT @sam_result=sam_result ,@sam_test_number=sam_test_number ,@sam_collection_time=sam_collection_time FROM inserted |
| IF @sam_result=1 |
| BEGIN |
| UPDATE PCRStatisticalResults |
| SET stati_total_number = stati_total_number+@sam_test_number |
| WHERE stati_date =CONVERT(varchar(10),@sam_collection_time,120) |
| END |
| END |
| END |
| |
禁止修改受检人员的个人二维码
| CREATE TRIGGER Tri_QRcode |
| ON CheckedUser |
| AFTER UPDATE |
| AS |
| IF UPDATE(ch_QRcode) |
| BEGIN |
| RAISERROR('不能修改个人信息二维码',16,2) |
| ROLLBACK |
| END |
| GO |
| |
数据库用户与角色
系统根据以下典型场景:
1.核酸检测点,2.受检者及物流人员个人设备,3.医院实验室,4.政府机关
将用户分为5类,角色及其权限如下:
- 受检人员:查看核酸信息、核酸检测点信息、消息发布记录,管理身份信息、受检人员表 。
- 政府机关管理人员:可以查看所有表,不可修改身份信息,样本人员关联表,物流信息表,其余表均可修改。
- 医护人员:管理采样信息表,管理医护人员表,查看核酸任务表,更新样本物流表。
- 物流人员:查看核酸安排表、检测站点表、实验室表、核酸安排和物流人员对照表,管理物流信息表、物流人员表。
- 实验室检测人员:查看实验室表、核酸统计结果表,管理采样信息表、实验室检测人员表,更新物流信息表。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
2021-12-18 JAVA 网络编程