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

-- wanghb 2020-12-02 新增短链接管理表
CREATE TABLE "PORTAL_SHORTURL_MANAGE" (
  "ID_" NVARCHAR2(255) NOT NULL,
  "URL_" NCLOB NOT NULL,
  "url_desc_" NCLOB,
  "SHORT_URL_" NVARCHAR2(255),
  "create_by_" NVARCHAR2(255),
  "create_time_" DATE,
  "lose_time_" DATE,
  "create_org_id_" NVARCHAR2(255),
  "update_by_" NVARCHAR2(255),
  "update_time_" DATE,
  "ENABLED_" NVARCHAR2(1),
  "tenant_id_" NVARCHAR2(64)
);

COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."ID_" IS '主键';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."URL_" IS '原链接';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."url_desc_" IS '原链接描述';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."SHORT_URL_" IS '短链接';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."create_by_" IS '创建人';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."create_time_" IS '创建时间';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."lose_time_" IS '失效时间';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."create_org_id_" IS '创建人组织id';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."update_by_" IS '更新人';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."update_time_" IS '更新时间';
COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."ENABLED_" IS '是否启用.1:启用，2：禁用';
COMMENT ON TABLE "PORTAL_SHORTURL_MANAGE" IS '短连接管理表';

ALTER TABLE "PORTAL_SHORTURL_MANAGE" ADD CONSTRAINT "SYS_C0038309" PRIMARY KEY ("ID_");

-- zjq 2020-12-04 修改流程附件上传配置表的ALIYUN_OSS_ENDPOINT_字段注释
COMMENT ON COLUMN "PORTAL_FLOW_UPLOAD_PROPERTIES"."ALIYUN_OSS_ENDPOINT_" IS '访问域名 endpoint';

-- 赵祥云 2020-12-11 表单历史记录表新增 表单Json数据字段
ALTER TABLE form_history_record ADD ( FORM_EXPAND_ CLOB DEFAULT NULL);
COMMENT ON COLUMN form_history_record.FORM_EXPAND_ IS '表单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 (RESET_TEMP_ NUMBER);
COMMENT ON COLUMN FORM_DATA_TEMPLATE.RESET_TEMP_ IS '是否需要初始化模板 1: 是 0: 否';

-- 雷健 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');

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

--zjq 2020-12-25 添加水印分配流程表
CREATE TABLE "BPM_WATERMARK_PROCESS" 
   (	
    "ID_" VARCHAR2(64) NOT NULL, 
	"PROC_DEF_ID_" VARCHAR2(64), 
	"PROC_DEF_KEY_" VARCHAR2(128), 
	"PROC_DEF_NAME_" VARCHAR2(128), 
	"WATERMARK_ID_" VARCHAR2(64), 
	"TENANT_ID_" VARCHAR2(64), 
	 PRIMARY KEY ("ID_")
   );
COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."ID_" IS 'ID';
COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."PROC_DEF_ID_" IS '流程ID';
COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."PROC_DEF_KEY_" IS '流程Key';
COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."PROC_DEF_NAME_" IS '流程名称';
COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."WATERMARK_ID_" IS '水印ID';
COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."TENANT_ID_" IS '租户ID';
COMMENT ON TABLE "BPM_WATERMARK_PROCESS" IS '水印分配流程';



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


-- 2021-01-15 wanghb 新增丢失的流程自动发起配置表建表sql
CREATE TABLE BPM_AUTO_START_CONF 
(
   ID_                  VARCHAR2(64)         NOT NULL,
   DEF_KEY_             VARCHAR2(64),
   START_USER_          CLOB,
   FORM_DATA_           CLOB,
   TRIGGER_             VARCHAR2(512),
   TENANT_ID_           VARCHAR2(64),
   CONSTRAINT PK_BPM_AUTO_START_CONF PRIMARY KEY (ID_)
);


-- zengst 2020-01-15 修改短链接管理表的列名(双引号会导致通过列名查找时报错)

alter table PORTAL_SHORTURL_MANAGE rename column "url_desc_" to URL_DESC_
/

alter table PORTAL_SHORTURL_MANAGE rename column "create_by_" to CREATE_BY_
/

alter table PORTAL_SHORTURL_MANAGE rename column "create_time_" to CREATE_TIME_
/

alter table PORTAL_SHORTURL_MANAGE rename column "lose_time_" to LOSE_TIME_
/

alter table PORTAL_SHORTURL_MANAGE rename column "create_org_id_" to CREATE_ORG_ID_
/

alter table PORTAL_SHORTURL_MANAGE rename column "update_by_" to UPDATE_BY_
/

alter table PORTAL_SHORTURL_MANAGE rename column "update_time_" to UPDATE_TIME_
/

alter table PORTAL_SHORTURL_MANAGE rename column "tenant_id_" to TENANT_ID_
/

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


-- qiuxd 2020-1-16 催办表字段添加
ALTER TABLE UC_HOLIDAY_TIME ADD TYPE_ INT;
COMMENT ON COLUMN UC_HOLIDAY_TIME.TYPE_ IS '假期类型';

ALTER TABLE BPM_TASK_REMINDER ADD DURATION_ INT;
COMMENT ON COLUMN BPM_TASK_REMINDER.DURATION_ IS '时长';
ALTER TABLE BPM_TASK_REMINDER ADD SEND_DURATION_ INT;
COMMENT ON COLUMN BPM_TASK_REMINDER.SEND_DURATION_ IS '发送时长';
ALTER TABLE BPM_TASK_REMINDER ADD DATE_TYPE_ VARCHAR2(20);
COMMENT ON COLUMN BPM_TASK_REMINDER.DATE_TYPE_ IS '日期类型';
ALTER TABLE BPM_TASK_REMINDER ADD TYPE_SCRIPT_ VARCHAR2(1000);
COMMENT ON COLUMN BPM_TASK_REMINDER.TYPE_SCRIPT_ IS '类型脚本';
ALTER TABLE BPM_TASK_REMINDER ADD REL_TIME_SCRIPT_ VARCHAR2(1000);
COMMENT ON COLUMN BPM_TASK_REMINDER.REL_TIME_SCRIPT_ IS '相对时间脚本';

-- jjx 待办部门信息编辑无响应
update portal_sys_column set data_param = '[{"name":"jsb","type":"string","mode":"0","value":"\"1306170252345872384\""}]' where id = '1311223536270053376';
update portal_sys_column set data_param = '[{"name":"pageBean","type":"string","mode":"0","value":"{\"page\":1,\"pageSize\":7,\"total\":0,\"showTotal\":false}"},{"name":"querys","type":"string","mode":"0","value":"[{\"property\": \"inst.CREATE_ORG_PATH_\", \"value\": \"1306170252345872384\", \"group\": \"orgId\", \"operation\": \"LIKE\"}]"}]' where id = '1311220566576992256';
