/*
 change.sql 说明
 1.数据库相关修改添加到对应的 change.sql 文件，无需调整其他 sql  文件
 2.sql 语句验证正确后再提交
*/

-- wanghb 2020-12-02 新增短链接管理表
DROP TABLE IF EXISTS `portal_shorturl_manage`;
CREATE TABLE `portal_shorturl_manage`  (
  `ID_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `URL_` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '原链接',
  `url_desc_` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原链接描述',
  `SHORT_URL_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '短链接',
  `create_by_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time_` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `lose_time_` datetime(0) NULL DEFAULT NULL COMMENT '失效时间',
  `create_org_id_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人组织id',
  `update_by_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time_` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `ENABLED_` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '是否启用.1:启用，2：禁用',
  `tenant_id_` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID_`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '短连接管理表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

-- zjq 2020-12-04 修改流程附件上传配置表的ALIYUN_OSS_ENDPOINT_字段注释
ALTER TABLE `portal_flow_upload_properties`
MODIFY COLUMN `ALIYUN_OSS_ENDPOINT_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '访问域名 endpoint' AFTER `TENANT_ID_`;

-- 赵祥云 2020-12-11 表单历史记录表新增 表单Json数据字段
ALTER TABLE `form_history_record` ADD COLUMN `FORM_EXPAND_` longtext DEFAULT NULL COMMENT '表单Json数据';


-- 曾善铜 2020-12-14 常用脚本、人员脚本迁移
insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338367741324824576, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getByCompanyRole', '找这个二级组织底下拥有这个角色的人员', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织ID","paraCt":"base:org-selector","paraCtBindKey":"code"},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"角色别名","paraCt":"base:role-selector","paraCtBindKey":"code"}]', 1, 2, null);
insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338375124944031744, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getLeaderStep', '获取逐级审批', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.Integer","paraDesc":"级别"},{"paraName":"arg1","paraType":"boolean","paraDesc":"是否需要上级（true，false）"}]', 1, 2, null);
insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338377219818524672, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getUserById', '根据ID获取审批人', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"ID"},{"paraName":"arg1","paraType":"java.util.List","paraDesc":"填写NULL"},{"paraName":"arg2","paraType":"java.lang.String","paraDesc":""}]', 1, 2, null);
insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338379764150439936, null, null, 'com.hotent.runtime.script.UserRelScript', 'userRelScript', 'getUserFromBusSql', 'sql查询用户ID', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"查询SQL，返回结果只有一列"}]', 1, 2, null);
insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338381406014935040, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getLeaderStepByOrgId', '根据组织ID获取逐级审批', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织ID","paraCt":"base:org-selector","paraCtBindKey":"id"},{"paraName":"arg1","paraType":"java.lang.Integer","paraDesc":"组织级别"}]', 1, 2, null);
insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338384186821709824, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getFgUsers', '获取分管审批人', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织Id","paraCt":"base:org-selector","paraCtBindKey":"id"},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"\\"组织扩展参数别名\\""},{"paraName":"arg2","paraType":"java.lang.String","paraDesc":"\\"用户扩展参数别名\\""}]', 1, 2, null);

insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338364268277534720, '判断当前用户是否拥有该角色', 'return scriptImpl.isCurrentInRole('''');', '系统脚本', '参数：角色别名', null, 1, '', null, null, null);
insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338334576715632640, '执行sql', 'return scriptImpl.executeSql('''');', '系统脚本', '参数：sql(String)，参数中加入<#id#>，在流程中会替换成表单数据主键。返回值只有一行一列(String)', null, 1, '', 1, null, null);
insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338326415497629696, '获取当前用户主岗位编码', 'return scriptImpl.getCurrentMainPostId();', '系统脚本', '无参数', null, 1, '', 1, null, null);
insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338326248547553280, '获取当前用户主岗位ID', 'return scriptImpl.getCurrentMainPostId();', '系统脚本', '无参数', null, 1, '', 1, null, null);
insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338326076463648768, '获取当前用户主岗位名称', 'return scriptImpl.getCurrentMainPostName();', '系统脚本', '无参数', null, 1, '', 1, null, null);
insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338321870394626048, '根据级别获取当前用户组织ID', 'return orgScript.getParamMainOrgId('''');', '系统脚本', 'param:grade(String)', null, 1, '', 1, null, null);
insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338310603730718720, '根据组织ID和参数判断组织是否有该参数', 'return orgScript.isOrgHasParamKey('''','''');', '系统脚本', '组织ID(String)，组织参数(String)', null, 1, '', 1, null, null);



-- zjq 2020-12-22 form_data_template表添加RESET_TEMP_字段
ALTER TABLE `form_data_template`
ADD COLUMN `RESET_TEMP_` smallint NULL COMMENT '是否需要初始化模板 1: 是 0: 否';

-- JJX 2020-12-23 删除一个初始化的用来显示新闻的配置（分库会报错，去掉不影响使用）
delete from form_custom_chart where ID_ = "1330698555119243264";


-- 雷健 2020-12-23 分库部署下执行 删除汇报线选择器的SQL
delete from FORM_CUSTOM_DIALOG where  ALIAS_='reportLine';
-- 分库部署调整汇报线相关的人员脚本
delete from bpm_multi_script where  ID_='12' or ID_='13';
INSERT INTO bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) VALUES ('12', null, null, 'com.hotent.runtime.script.UserRelScript', 'userRelScript', 'getByRelPreNode', '通过上一节点执行人获取汇报线上级 人员列表', 'java.util.Set', '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]', 1, 2, '-1');
INSERT INTO bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) VALUES ('13', null, null, 'com.hotent.runtime.script.UserRelScript', 'userRelScript', 'getByRelStartUser', '通过发起人获取汇报线上级 人员列表', 'java.util.Set', '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]', 1, 2, '-1');

-- zjq 2020-12-25 添加水印分配流程表
CREATE TABLE `bpm_watermark_process` (
  `ID_` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ID',
  `PROC_DEF_ID_` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '流程ID',
  `PROC_DEF_KEY_` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '流程Key',
  `PROC_DEF_NAME_` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '流程名称',
  `WATERMARK_ID_` varchar(64) DEFAULT NULL COMMENT '水印ID',
  `TENANT_ID_` varchar(64) DEFAULT NULL COMMENT '租户ID',
  PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='水印分配流程';


-- jjx 2021-1-13 删除多余的系统帮助配置
delete from PORTAL_SYS_COLUMN where ID = '1325681915189137408';


-- 2021-01-15 wanghb 新增丢失的流程自动发起配置表建表sql
CREATE TABLE BPM_AUTO_START_CONF
(
   ID_                  VARCHAR(64) CHARACTER SET UTF8 NOT NULL,
   DEF_KEY_             VARCHAR(64) CHARACTER SET UTF8,
   START_USER_          TEXT CHARACTER SET UTF8,
   FORM_DATA_           TEXT CHARACTER SET UTF8,
   TRIGGER_             VARCHAR(512) CHARACTER SET UTF8,
   TENANT_ID_           VARCHAR(64) CHARACTER SET UTF8,
   PRIMARY KEY (ID_)
)
ENGINE = INNODB CHARACTER SET = UTF8 COLLATE = UTF8_GENERAL_CI COMMENT = '' ROW_FORMAT = COMPACT;


-- lingpj 2021-01-15 添加催办表遗漏的字段
ALTER TABLE BPM_TASK_REMINDER ADD COLUMN DURATION_SCRIPT_ TEXT DEFAULT NULL COMMENT '时长脚本';


-- qiuxd 2021-1-16 催办表字段添加
ALTER TABLE uc_holiday_time ADD COLUMN type_ smallint NULL COMMENT '类型：0假期 1补班';
ALTER TABLE BPM_TASK_REMINDER ADD COLUMN DURATION_ int default NULL COMMENT '时长';
ALTER TABLE BPM_TASK_REMINDER ADD COLUMN SEND_DURATION_ int default NULL COMMENT '消息时长';
ALTER TABLE BPM_TASK_REMINDER ADD COLUMN DATE_TYPE_ varchar(20) default NULL COMMENT '类型 caltime日历日 worktime工作日';
ALTER TABLE BPM_TASK_REMINDER ADD COLUMN TYPE_SCRIPT_ varchar(1000) default NULL COMMENT '类型脚本';
ALTER TABLE BPM_TASK_REMINDER ADD COLUMN REL_TIME_SCRIPT_ varchar(1000) default NULL COMMENT '相对时间脚本';

-- jjx 2021-1-19 修改字段类型,解决多次修改对话框配置报错
ALTER TABLE form_custom_dialog MODIFY COLUMN `COMBINATION_RULE_` longtext;
