use `qc`;

/**
执行脚本前请先看注意事项：
  
 */

/**
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`;


/**
新建 sys_region 病区表
 */
-- ----------------------------
-- Table structure for sys_region
-- ----------------------------
DROP TABLE IF EXISTS `sys_region`;
CREATE TABLE `sys_region` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `hospital_id` bigint(20) NOT NULL COMMENT '组织机构ID',
  `hospital_name` varchar(30) DEFAULT NULL COMMENT '医院名称',
  `code` varchar(32) DEFAULT NULL COMMENT '病区代码',
  `name` varchar(32) NOT NULL COMMENT '病区名称',
  `spell` varchar(64) DEFAULT NULL COMMENT '首字母拼音',
  `station` varchar(64) DEFAULT NULL COMMENT '区域类别',
  `order_no` varchar(8) DEFAULT NULL COMMENT '排序',
  `status` char(1) NOT NULL DEFAULT '1' COMMENT '状态 0：禁用，1：启用',
  `is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '是否删除,N:未删除，Y:删除',
  `gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录修改时间,如果时间是1970年则表示纪录未修改',
  `creator` varchar(32) NOT NULL DEFAULT '0' COMMENT '创建人，0表示无创建人值',
  `modifier` varchar(32) NOT NULL DEFAULT '0' COMMENT '修改人,如果为0则表示纪录未修改',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='病区表';


/**
新建 sys_region_dept 病区与科室关联表
 */
-- ----------------------------
-- Table structure for sys_region_dept
-- ----------------------------
DROP TABLE IF EXISTS `sys_region_dept`;
CREATE TABLE `sys_region_dept` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `hospital_id` bigint(20) NOT NULL COMMENT '组织机构ID',
  `region_code` varchar(20) NOT NULL COMMENT '病区ID',
  `dept_id` bigint(20) NOT NULL COMMENT '科室ID',
  `order_no` varchar(8) DEFAULT NULL COMMENT '排序',
  `is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '是否删除,N:未删除，Y:删除',
  `gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录修改时间,如果时间是1970年则表示纪录未修改',
  `creator` varchar(32) NOT NULL DEFAULT '0' COMMENT '创建人，0表示无创建人值',
  `modifier` varchar(32) NOT NULL DEFAULT '0' COMMENT '修改人,如果为0则表示纪录未修改',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='病区与科室关联表';



/**
新建 sys_medoup 医疗组表
 */
-- ----------------------------
-- Table structure for sys_medoup
-- ----------------------------
DROP TABLE IF EXISTS `sys_medoup`;
CREATE TABLE `sys_medoup` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `code` varchar(32) NOT NULL COMMENT '医疗组代码',
  `name` varchar(32) NOT NULL COMMENT '医疗组名称',
  `is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '是否删除,N:未删除，Y:删除',
  `gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录修改时间,如果时间是1970年则表示纪录未修改',
  `creator` varchar(32) NOT NULL DEFAULT '0' COMMENT '创建人，0表示无创建人值',
  `modifier` varchar(32) NOT NULL DEFAULT '0' COMMENT '修改人,如果为0则表示纪录未修改',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='医疗组表';




/**
新建 sys_dept_medoup 科室与医疗组关联表
 */
-- ----------------------------
-- Table structure for sys_dept_medoup
-- ----------------------------
DROP TABLE IF EXISTS `sys_dept_medoup`;
CREATE TABLE `sys_dept_medoup` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dept_id` varchar(20) NOT NULL COMMENT '科室ID',
  `medoup_code` varchar(32) NOT NULL COMMENT '医疗组Code',
  `is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '是否删除,N:未删除，Y:删除',
  `gmt_create` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 12:00:00' COMMENT '记录修改时间,如果时间是1970年则表示纪录未修改',
  `creator` varchar(32) NOT NULL DEFAULT '0' COMMENT '创建人，0表示无创建人值',
  `modifier` varchar(32) NOT NULL DEFAULT '0' COMMENT '修改人,如果为0则表示纪录未修改',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='科室与医疗组关联表';


/**
列设置，展示相关字段需要在列设置上恢复默认(初始化)然后勾选保存
 */
UPDATE `sys_user_pageset` SET  `order_no`='23', `remark`=NULL WHERE (`val`='behDeptName');
UPDATE `sys_user_pageset` SET  `order_no`='25', `remark`=NULL WHERE (`val`='gradeTime');
INSERT INTO `sys_user_pageset` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '病区', 'wardName', '1', '22', NULL);
INSERT INTO `sys_user_pageset` ( `is_deleted`, `gmt_create`, `gmt_modified`, `creator`, `modifier`, `user_id`, `page_type`, `name`, `val`, `status`, `order_no`, `remark`) VALUES ( 'N', '1970-01-01 12:00:00', '1970-01-01 12:00:00', '0', '0', '-1', '1', '医疗组', 'medoupName', '1', '24', NULL);

/**

关闭病历稽查报表
 */
UPDATE `sys_menu` SET  `is_deleted`='Y'  WHERE (`code`='YH-ZKK-YXBLJCB');
UPDATE `sys_menu` SET  `is_deleted`='Y'  WHERE (`code`='YH-ZKK-ZMBLJCB');
UPDATE `sys_menu` SET  `is_deleted`='Y'  WHERE (`code`='YH-KSZR-ZMBLJCS_XQ');
UPDATE `sys_menu` SET  `is_deleted`='Y'  WHERE (`code`='YH-KSZR-YXBLJCS_XQ');




/**以下脚本执行建议备份下原表med_behospital_info
doctorId回查
 */
 
 UPDATE med_behospital_info a,
 (
	SELECT
		doctor_id,
		name
		NAME
	FROM
		bas_doctor_info b
	WHERE
		b.hospital_id = 14
	AND b.is_deleted = 'N'
) c
SET a.doctor_name = c. NAME
WHERE
	a.doctor_id = c.doctor_id
AND a.hospital_id = 14
AND (a.doctor_name is null or a.doctor_name = '' or a.doctor_name ='-' or a.doctor_name ='—' or LENGTH(a.doctor_name)>64);

UPDATE med_behospital_info a,
 (
	SELECT
		doctor_id,
		NAME
	FROM
		bas_doctor_info b
	WHERE
		b.hospital_id = 14
	AND b.is_deleted = 'N'
) c
SET a.doctor_id = c.doctor_id
WHERE
	a.doctor_name = c. NAME
AND a.hospital_id = 14
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_id = '-' where a.hospital_id =14  and (a.doctor_id is null 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.hospital_id =14  and (a.doctor_name is null or a.doctor_name = '' or a.doctor_name = '—' or LENGTH(a.doctor_name)>64);

UPDATE med_behospital_info a set a.bed_code = '-' where a.hospital_id =14  and (a.bed_code is null or a.bed_code = '' or a.bed_code = '—' or LENGTH(a.bed_code)>64);

UPDATE med_behospital_info a set a.bed_name = '-' where a.hospital_id =14  and (a.bed_name is null or a.bed_name = '' or a.bed_name = '—' or LENGTH(a.bed_name)>64);

/**
七院生成核查任务，vip开房病区科室id配置
 */
INSERT INTO `sys_hospital_set` (`hospital_id`, `name`, `code`, `value`, `remark`) VALUES ('14', '病历核查（杭州七院，科室医生特殊病历）', 'check_order_info', '2015,2019', '科室核查任务除了本科室还要VIP、开放病区医生病历')