mrqc-sys/doc/037.20211117_2.1.4/qc_initv2.1.4.sql

use `qc`;
-- 执行前请看注意事项！
-- 此脚本为通版脚本，均执行病历doctorId、doctorName回查 @hospitalId =**：需要更新的医院hospitalId在下面调整即可SET @hospitalId =**

-- 如果其它医院不使用此次版本的数据分析报表，则需要手动到关闭数据分析报表
SET @hospitalId =35;
 UPDATE med_behospital_info a,
 (
	SELECT
		doctor_id,
		name
		NAME
	FROM
		bas_doctor_info b
	WHERE
		b.hospital_id = @hospitalId
	AND b.is_deleted = 'N'
) c
SET a.doctor_name = c. NAME
WHERE
	a.doctor_id = c.doctor_id
AND a.hospital_id = @hospitalId
AND (a.doctor_name is null or a.doctor_name = '' or a.doctor_name ='-' or a.doctor_name ='—' or LENGTH(a.doctor_name)>64);

-- med_behospital_info病历doctorId回查
UPDATE med_behospital_info a,
 (
	SELECT
		doctor_id,
		NAME
	FROM
		bas_doctor_info b
	WHERE
		b.hospital_id = @hospitalId
	AND b.is_deleted = 'N'
	GROUP BY
		b. NAME
	HAVING
		count(b.doctor_id) = 1
) c
SET a.doctor_id = c.doctor_id
WHERE
	a.doctor_name = c.NAME
AND a.hospital_id = @hospitalId
AND (a.doctor_id is null or a.doctor_id = '' or a.doctor_id ='-' or a.doctor_id ='—' or LENGTH(a.doctor_id)>64);


-- 对空数据初始化
UPDATE med_behospital_info a set a.doctor_name = '-' where (a.doctor_name is null or a.doctor_name = '') AND a.hospital_id = @hospitalId;
UPDATE med_behospital_info a set a.doctor_id = '-' where (a.doctor_id is null or a.doctor_id = '') AND a.hospital_id = @hospitalId;


DROP TABLE IF EXISTS `med_click_info`;
CREATE TABLE `med_click_info` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `hospital_id` tinyint(5) DEFAULT '0' COMMENT '医院ID',
  `dept_id` varchar(16) DEFAULT NULL COMMENT '科室ID',
  `dept_name` varchar(64) DEFAULT NULL COMMENT '科室名称',
  `doctor_id` varchar(16) DEFAULT NULL COMMENT '医生ID',
  `doctor_name` varchar(64) DEFAULT NULL COMMENT '医生姓名',
  `opt_type` tinyint(1) DEFAULT '1' COMMENT '0-单模块质控 1-全病历质控',
  `qcresult_info_id` int(16) DEFAULT NULL COMMENT '评分结果id',
  `behospital_code` varchar(16) DEFAULT NULL COMMENT '病人住院ID',
  `is_deleted` char(3) DEFAULT 'N' COMMENT '是否删除,N:未删除，Y:删除',
  `gmt_create` datetime DEFAULT '1970-01-01 12:00:00' COMMENT ' 记录创建时间',
  `gmt_modified` datetime DEFAULT '1970-01-01 12:00:00' COMMENT ' 记录修改时间,如果时间是1970年则表示纪录未修改',
  `creator` varchar(20) DEFAULT '0' COMMENT '创建人，0表示无创建人值',
  `modifier` varchar(20) DEFAULT '0' COMMENT '修改人,如果为0则表示纪录未修改',
  PRIMARY KEY (`id`),
  KEY `idx_all` (`hospital_id`,`behospital_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=62313 DEFAULT CHARSET=utf8 COMMENT='临床医生点击质控病历次数基本表';

DROP TABLE IF EXISTS `med_qcresult_click`;
CREATE TABLE `med_qcresult_click` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hospital_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '医院ID',
  `behospital_code` varchar(16) NOT NULL COMMENT '病人住院ID',
  `dept_id` varchar(16) DEFAULT NULL COMMENT '住院科室ID',
  `dept_name` varchar(64) DEFAULT NULL COMMENT '住院科室名称',
  `qcresult_info_id` bigint(20) NOT NULL COMMENT '评分结果id',
  `cases_entry_ids` varchar(1024) DEFAULT '0' COMMENT '触发埋点病历质控缺陷id集',
  `is_deleted` char(3) DEFAULT 'N' COMMENT '是否删除,N:未删除，Y:删除',
  `gmt_create` datetime DEFAULT '1970-01-01 12:00:00' COMMENT ' 记录创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_all` (`hospital_id`,`behospital_code`)
) ENGINE=InnoDB AUTO_INCREMENT=8587 DEFAULT CHARSET=utf8 COMMENT='全病历质控、单模块质控（触发埋点）缺陷详情';



/**
med_qcresult_detail表新增评分结果主表id字段
 */
ALTER TABLE `med_qcresult_detail` ADD COLUMN qcresult_info_id BIGINT (20) DEFAULT NULL COMMENT '评分结果id' AFTER `behospital_code`;

/**
med_qcresult_cases表新增评分结果主表id字段
 */
ALTER TABLE `med_qcresult_cases` ADD COLUMN qcresult_info_id BIGINT (20) DEFAULT NULL COMMENT '评分结果id' AFTER `behospital_code`;

-- 全院
SET @id =69;
INSERT INTO `sys_menu` ( `id`,`is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `parent_id`, `code`, `show_status`, `maintain_status`, `order_no`, `remark`) VALUES (@id,'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '数据分析', '-1', 'YH-SJFX', '1', '1', '12', '用户-数据分析');


INSERT INTO `sys_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `parent_id`, `code`, `show_status`, `maintain_status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '临床质控使用统计', @id , 'YH-ZKK-LCZKSYTJ', '1', '1', '1', '用户-质控科-临床质控使用统计');
SET @idSec =@@identity;
INSERT INTO `sys_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `parent_id`, `code`, `show_status`, `maintain_status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '条目缺陷改善统计', @id , 'YH-ZKK-TMQXGSTJ', '1', '1', '2', '用户-质控科-条目缺陷改善统计');
SET @idThr =@@identity;

INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @id, NULL);
INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @id, NULL);


INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idSec, NULL);
INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @idSec, NULL);
INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idThr, NULL);
INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @idThr, NULL);



INSERT INTO `sys_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `code`, `permissionUrl`, `method`, `descritpion`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '临床质控使用统计', '', '/qc/analysis/getQcClick', 'ALL', '临床质控使用统计', NULL);
SET @idperFir =@@identity;
INSERT INTO `sys_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `code`, `permissionUrl`, `method`, `descritpion`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '临床质控使用统计(内页)', '', '/qc/analysis/getQcClickInnerPage', 'ALL', '临床质控使用统计(内页)', NULL);
SET @idperSec =@@identity;
INSERT INTO `sys_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `code`, `permissionUrl`, `method`, `descritpion`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '条目缺陷改善统计', '', '/qc/analysis/getEntryDefectImprove', 'ALL', '条目缺陷改善统计', NULL);
SET @idperThr =@@identity;
INSERT INTO `sys_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `code`, `permissionUrl`, `method`, `descritpion`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '条目缺陷改善统计(内页)', '', '/qc/analysis/getEntryDefectImproveInner', 'ALL', '条目缺陷改善统计(内页)-病历列表', NULL);
SET @idperFou =@@identity;


INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idperFir, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idperSec, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idperThr, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idperFou, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @idperFir, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @idperSec, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @idperThr, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '1', @idperFou, NULL);

-- INSERT INTO `sys_menu_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `menu_id`, `permission_id`, `order_nu`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', @idSec, @idperFir, NULL, '临床质控使用统计');
-- INSERT INTO `sys_menu_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `menu_id`, `permission_id`, `order_nu`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', @idSec, @idperSec, NULL, '临床质控使用统计(内页)');
-- INSERT INTO `sys_menu_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `menu_id`, `permission_id`, `order_nu`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', @idThr, @idperThr, NULL, '条目缺陷改善统计');
-- INSERT INTO `sys_menu_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `menu_id`, `permission_id`, `order_nu`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', @idThr, @idperFou, NULL, '条目缺陷改善统计(内页)');


-- 科室
INSERT INTO `sys_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `parent_id`, `code`, `show_status`, `maintain_status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '数据分析(科室)', '-1', 'YH-SJFXKS', '1', '1', '13', '用户-数据分析(科室)');
SET @idks =@@identity;

INSERT INTO `sys_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `parent_id`, `code`, `show_status`, `maintain_status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '条目缺陷改善统计', @idks , 'YH-KSZR-TMQXGSTJ', '1', '1', '2', '用户-质控科-条目缺陷改善统计(科室)');
SET @idksThr =@@identity;

INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idks, NULL);
INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '2', @idks, NULL);

INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idksThr, NULL);
INSERT INTO `sys_role_menu` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `menu_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '2', @idksThr, NULL);



INSERT INTO `sys_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `code`, `permissionUrl`, `method`, `descritpion`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '条目缺陷改善统计', '', '/qc/analysisDept/getEntryDefectImproveDept', 'ALL', '条目缺陷改善统计-科室', NULL);
SET @idperFiv =@@identity;
INSERT INTO `sys_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `name`, `code`, `permissionUrl`, `method`, `descritpion`, `remark`) VALUES ('N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '条目缺陷改善统计(内页)', '', '/qc/analysisDept/getEntryDefectImproveInnerDept', 'ALL', '条目缺陷改善统计(内页)-科室', NULL);
SET @idperSix =@@identity;

INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idperFiv, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', @idperSix, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '2', @idperFiv, NULL);
INSERT INTO `sys_role_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `role_id`, `permission_id`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '2', @idperSix, NULL);


-- INSERT INTO `sys_menu_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `menu_id`, `permission_id`, `order_nu`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', @idksThr, @idperFiv, NULL, '条目缺陷改善统计');
-- INSERT INTO `sys_menu_permission` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `menu_id`, `permission_id`, `order_nu`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', @idksThr, @idperSix, NULL, '条目缺陷改善统计(内页)');