wlyy2.0/common/common-entity/src/db/2023.sql

-- 2023-05-04
ALTER table base_menu_dict add COLUMN `author` varchar(255) DEFAULT NULL COMMENT '来源、作者';
ALTER table base_menu_dict add COLUMN `doctors` varchar(255) DEFAULT NULL COMMENT '推荐医生 多个逗号间隔';
ALTER table base_menu_dict add COLUMN `music_url` varchar(255) DEFAULT NULL COMMENT '音频';
ALTER table base_menu_dict add COLUMN `release_type` varchar(255) DEFAULT NULL COMMENT '发布类型 1图文 2视频 3音频';

INSERT INTO `base`.`wlyy_hospital_sys_dict` (`id`, `saas_id`, `dict_name`, `dict_code`, `dict_value`, `py_code`, `sort`, `hospital`, `create_time`, `create_user`, `create_user_name`, `update_time`, `update_user`, `update_user_name`, `img_url`, `model_name`) VALUES ('releaseType1', NULL, 'releaseType', '1', '图文', NULL, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `base`.`wlyy_hospital_sys_dict` (`id`, `saas_id`, `dict_name`, `dict_code`, `dict_value`, `py_code`, `sort`, `hospital`, `create_time`, `create_user`, `create_user_name`, `update_time`, `update_user`, `update_user_name`, `img_url`, `model_name`) VALUES ('releaseType2', NULL, 'releaseType', '2', '视频', NULL, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `base`.`wlyy_hospital_sys_dict` (`id`, `saas_id`, `dict_name`, `dict_code`, `dict_value`, `py_code`, `sort`, `hospital`, `create_time`, `create_user`, `create_user_name`, `update_time`, `update_user`, `update_user_name`, `img_url`, `model_name`) VALUES ('releaseType3', NULL, 'releaseType', '3', '音频', NULL, '3', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

CREATE TABLE `base_menu_dict_user` (
     `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
     `user_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
     `fabulous` int DEFAULT NULL COMMENT '1已赞0未赞',
     `is_read` int DEFAULT NULL COMMENT '1已读0未读',
     `is_share` int DEFAULT NULL COMMENT '1已分享0未分享',
     `collection` int DEFAULT NULL COMMENT '是否收藏1是0否',
     `used` int DEFAULT NULL COMMENT '1常用0不常用',
     `del` int DEFAULT '1' COMMENT '是否删除，1正常，0删除',
     `relation_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `relation_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
     `relation_type` int DEFAULT NULL COMMENT '1、文章2、问卷',
     PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;




-- 2023-04-24
CREATE TABLE `base_team_relation` (
   `id` varchar(50) NOT NULL,
   `team_id` varchar(50) DEFAULT NULL COMMENT '专科团队id',
   `admin_team_id` int DEFAULT NULL COMMENT '社区团队id',
   `create_time` datetime DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='专科团队关联社区团队表';




-- 2023-04-20
ALTER table base_team add COLUMN `intro` varchar(2000) DEFAULT NULL COMMENT '团队简介';
ALTER table base_team add COLUMN `recommend` varchar(1) DEFAULT NULL COMMENT '推荐 1是0否';
ALTER table base_team add COLUMN `recommend_doctor` varchar(50) DEFAULT NULL COMMENT '推荐医生code';
ALTER table base_team add COLUMN `recommend_doctor_name` varchar(50) DEFAULT NULL COMMENT '推荐医生姓名';
ALTER table base_team add COLUMN `dept` varchar(50) DEFAULT NULL COMMENT '科室code';
ALTER table base_team add COLUMN `dept_name` varchar(50) DEFAULT NULL COMMENT '科室名称';
ALTER table base_team add COLUMN `img` varchar(255) DEFAULT NULL COMMENT '图片';

-- 2023-02-27
base_patient_doctor 暂时用来代替 wlyy_sign_family



==========================开始==================================
ALTER TABLE `base`.`wlyy_rehabilitation_plan_template`
ADD COLUMN `disease_id` varchar(50) NULL COMMENT '病种code' AFTER `update_time`,
ADD COLUMN `disease_name` varchar(50) NULL COMMENT '病种name' AFTER `update_time`,
ADD COLUMN `status` varchar(10) NULL COMMENT '状态是否生效' AFTER `disease_id`;

ALTER TABLE `base`.`wlyy_rehabilitation_plan_template`
ADD COLUMN `type` varchar(255) NULL COMMENT 'template表示模板，plan表示计划' AFTER `status`;

ALTER TABLE `base`.`wlyy_rehabilitation_template_detail`
ADD COLUMN `assess_type` varchar(10) NULL COMMENT '项目评价' AFTER `expense`,
ADD COLUMN `booking_type` varchar(10) NULL COMMENT '是否预约' AFTER `assess_type`,
ADD COLUMN `late_serve_type` varchar(10) NULL COMMENT '是否运行逾期服务' AFTER `booking_type`,
ADD COLUMN `plan_start_time_type` varchar(10) NULL COMMENT '计划开始时间类型' AFTER `late_serve_type`,
ADD COLUMN `server_time_type` varchar(10) NULL COMMENT '服务方法' AFTER `plan_start_time_type`,
ADD COLUMN `server_count` varchar(10) NULL COMMENT '服务次数' AFTER `server_time_type`,
ADD COLUMN `pricing_units_code` varchar(10) NULL COMMENT '计价单位' AFTER `server_count`;
ADD COLUMN `pricing_units_name` varchar(10) NULL COMMENT '计价单位名称' AFTER `pricing_units_name`;




ALTER TABLE `base`.`base_service_package_item`
ADD COLUMN `leased_code` varchar(100) NULL COMMENT '租户code' AFTER `topic_item`,
ADD COLUMN `leased_name` varchar(50) NULL COMMENT '租户name' AFTER `leased_code`,
ADD COLUMN `sub_item_type` varchar(10) NULL COMMENT '是否包含子项目' AFTER `leased_name`;
ALTER TABLE `base`.`base_service_package_item`
ADD COLUMN `template_type` varchar(20) NULL COMMENT '模板的类型-目前是随访为1' AFTER `sub_item_type`,
ADD COLUMN `template_code` varchar(255) NULL COMMENT '对应模板的id' AFTER `template_type`;



CREATE TABLE `base`.`base_service_item_config`  (
  `id` varchar(100) NOT NULL COMMENT '主键',
  `package_id` varchar(100) NULL COMMENT '服务包id',
  `server_item_id` varchar(100) NULL COMMENT '服务项id',
  `assess_type` varchar(100) NULL COMMENT '项目评价',
  `booking_type` varchar(100) NULL COMMENT '是否预约',
  `late_serve_type` varchar(100) NULL COMMENT '是否运行逾期服务',
  `plan_start_time_type` varchar(100) NULL COMMENT '计划开始时间类型',
  `server_time_type` varchar(100) NULL COMMENT '服务方法',
  `server_count` varchar(100) NULL COMMENT '服务次数',
  `template_type` varchar(100) NULL COMMENT '模板的类型-目前是随访为1',
  `template_code` varchar(100) NULL COMMENT '对应模板的id',
  PRIMARY KEY (`id`)
);


CREATE TABLE `template_json` (
  `id`  varchar(100)  NOT NULL COMMENT '主键',
  `template_id` varchar(100) DEFAULT NULL COMMENT '模板id',
  `template_name` varchar(50) DEFAULT NULL COMMENT '模板名称',
  `template_json` text COMMENT '模板json',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` varchar(255) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `base`.`base_service_package`
ADD COLUMN `leased_code` varchar(50) NULL COMMENT '租户code' AFTER `org_name`,
ADD COLUMN `leased_name` varchar(50) NULL COMMENT '租户name' AFTER `leased_code`,
ADD COLUMN `server_time_type` varchar(20) NULL COMMENT '服务时间类型' AFTER `leased_name`,
ADD COLUMN `server_time` varchar(50) NULL COMMENT '服务期限' AFTER `server_time_type`,
ADD COLUMN `server_time_description` varchar(255) NULL COMMENT '服务时间说明' AFTER `server_time`,
ADD COLUMN `create_user` varchar(50) NULL COMMENT '创建人code' AFTER `server_time_description`,
ADD COLUMN `create_user_name` varchar(20) NULL COMMENT '创建人name' AFTER `create_user`,
ADD COLUMN `update_user` varchar(50) NULL COMMENT '更新人code' AFTER `create_user_name`,
ADD COLUMN `update_user_name` varchar(50) NULL COMMENT '更新人name' AFTER `update_user`,
ADD COLUMN `update_time` datetime NULL COMMENT '更新时间' AFTER `create_time`;


CREATE TABLE `base`.`base_service_package_sub_item`  (
  `id` varchar(100) NOT NULL COMMENT '主键',
  `name` varchar(100) NULL COMMENT '名称',
  `item_id` varchar(100) NULL COMMENT '项目的id',
  `introduce` varchar(255) NULL COMMENT '说明',
  `status` varchar(10) NULL COMMENT '状态',
  `sort` varchar(10) NULL,
  `leased_code` varchar(100) NULL,
  `leased_name` varchar(100) NULL,
  `org_code` varchar(100) NULL COMMENT '机构code',
  `org_name` varchar(100) NULL COMMENT '机构name',
  `photo` varchar(100) NULL COMMENT '照片',
  `create_time` varchar(100) NULL COMMENT '照片',
  `create_time` datetime ;
  PRIMARY KEY (`id`)
);

ALTER TABLE `base`.`base_service_package_sub_item`
ADD COLUMN `item_id` varchar(100) NULL COMMENT '父项目id' AFTER `id`;


CREATE TABLE `base`.`base_service_package_item_relational`  (
  `id` varchar(100) NOT NULL COMMENT '主键',
  `item_id` varchar(100) NULL COMMENT '项目id',
  `sub_item_id` varchar(100) NULL COMMENT '子项目id',
  PRIMARY KEY (`id`)
);



CREATE TABLE `base`.`base_followup_template`  (
  `id` varchar(100) NOT NULL,
  `template_name` varchar(255) NULL,
  `followup_statrt_time_type` varchar(100) NULL,
  `followup_count` varchar(10) NULL,
  `description` varchar(255) NULL,
  `template_detail_json` text NULL,
  `status` varchar(10) NULL,
  `leased_code` varchar(100) NULL,
  `leased_name` varchar(100) NULL,
  `org_code` varchar(100) NULL,
  `org_name` varchar(255) NULL,
  `create_time` datetime NULL,
  `update_time` datetime NULL,
  `create_user` varchar(100) NULL,
  `create_user_name` varchar(100) NULL,
  `update_user` varchar(100) NULL,
  `update_user_name` varchar(100) NULL,
  PRIMARY KEY (`id`)
);


ALTER TABLE `base`.`base_disease_hospital`
ADD COLUMN `rehabilitation_type` varchar(100) NULL COMMENT '是否开通诊后康复 0否 1是' AFTER `sort`,
ADD COLUMN `rehabilitation_down_type` varchar(100) NULL COMMENT '康复下转 1自动 2手动' AFTER `rehabilitation_type`,
ADD COLUMN `rehabilitation_template_id` varchar(100) NULL COMMENT '康复计划模板id' AFTER `rehabilitation_down_type`;


ALTER TABLE `base`.`base_service_package_sub_item`
CHANGE COLUMN `item_id` `dict_item_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '字典配置的服务项的id' AFTER `id`;

ALTER TABLE `base`.`base_service_package_item`
ADD COLUMN `service_package_item_id` varchar(50) NULL COMMENT '创建服务项的id。也是本表的id。system那些数据' AFTER `service_package_id`;


ALTER TABLE `base`.`wlyy_rehabilitation_template_detail`
ADD COLUMN `template_type` varchar(100) NULL COMMENT '随访模板类型' AFTER `pricing_units_name`,
ADD COLUMN `template_code` varchar(100) NULL COMMENT '随访模板id' AFTER `template_type`;


ALTER TABLE `base`.`base_service_item_config`
ADD COLUMN `pricing_units_code` varchar(100) NULL COMMENT '计价单位' AFTER `template_code`,
ADD COLUMN `pricing_units_name` varchar(100) NULL COMMENT '计价单位名称' AFTER `pricing_units_code`;


ALTER TABLE `base`.`base_service_item_config`
ADD COLUMN `frequency_code` varchar(100) NULL COMMENT '频次' AFTER `pricing_units_name`;
===========================结束=================================

-- 2023-10-31 ysj 计算百分比函数
CREATE FUNCTION percentage(numerator FLOAT, denominator FLOAT) RETURNS VARCHAR(255)
    NO SQL
BEGIN
  DECLARE result VARCHAR(255);
	IF numerator IS NULL OR numerator=0 THEN
    SET result = '0%';
  ELSEIF (denominator = 0 OR denominator is null) AND numerator > 0 THEN
    SET result = '100%';
  ELSEIF denominator IS NULL THEN
    SET result = '0%';
ELSE
    SET result = CONCAT(ROUND((numerator / denominator) * 100, 2), '%');
END IF;
RETURN result;
END;