Activity 工作流任务回退、切换流程定义

Activity 工作流任务回退sql

 

 

1、回退任务涉及的表

# 历史运行实例表
SELECT * FROM act_hi_procinst WHERE BUSINESS_KEY_ = ?
# 历史审批节点（包含当前正在执行的节点数据）
SELECT ID_,PROC_DEF_ID_,TASK_DEF_KEY_,NAME_,ASSIGNEE_ 
    FROM act_hi_taskinst
    where PROC_INST_ID_ = ? and TASK_DEF_KEY_ is not null

# 数据来源参考 act_hi_taskinst
SELECT * FROM act_ru_task WHERE PROC_INST_ID_ = ?
    
# 数据来源 参考 act_hi_procinst    表数据
SELECT * FROM act_ru_execution WHERE  PROC_INST_ID_ = ? 
# 历史变量表
select * from act_hi_varinst where PROC_INST_ID_ = ?

# 运行时变量表
select * from act_ru_variable where EXECUTION_ID_= ?
# 历史用户信息
select * from act_hi_identitylink WHERE PROC_INST_ID_ = ?;
# 运行时用户信息
select * from act_ru_identitylink WHERE PROC_INST_ID_ = ?;

 

2、回退的存储过程

2.1、任务已经结束回退

# 工作流已经结束， 回退到上一个节点，参数为：BUSINESS_KEY_(业务唯一Key), 下面的sql中的 "usertask6"、 "复核中" 为工作流中的节点信息可以按需求修改
CREATE PROCEDURE workflow_end_go_back(IN business_key VARCHAR(64))
BEGIN
    DECLARE v_id_ VARCHAR(64);
    DECLARE v_proc_def_id_ VARCHAR(64);
    DECLARE v_task_def_key_ VARCHAR(255);
    DECLARE v_name_ VARCHAR(255);
    DECLARE v_assignee_ VARCHAR(255);
    DECLARE v_proc_inst_id_ VARCHAR(64);
    DECLARE cur_act_hi_taskinst CURSOR FOR SELECT ID_,PROC_DEF_ID_,TASK_DEF_KEY_,NAME_,ASSIGNEE_ 
    FROM act_hi_taskinst
    where PROC_INST_ID_ = (SELECT PROC_INST_ID_ FROM act_hi_procinst WHERE BUSINESS_KEY_ = business_key) and TASK_DEF_KEY_ is not null
    order by START_TIME_ desc ;
    select PROC_INST_ID_ into v_proc_inst_id_ from act_hi_procinst WHERE BUSINESS_KEY_ = business_key;
OPEN cur_act_hi_taskinst;
        set @i = 0;
        label: LOOP
        SET @i = @i + 1;
        FETCH cur_act_hi_taskinst INTO v_id_, v_proc_def_id_, v_task_def_key_, v_name_, v_assignee_;
        IF @i = 1 THEN
        -- 1、处理历史任务 将当前任务设置为完成状态
        UPDATE ACT_HI_TASKINST SET END_TIME_ = now(),duration_ = 1 ,DELETE_REASON_ = 'completed' where id_ = v_id_;
        ELSEIF @i = 2 THEN
        -- 2、处理历史任务 添加当前新任务
        INSERT INTO ACT_HI_TASKINST (id_    ,proc_def_id_    ,task_def_key_,proc_inst_id_,execution_id_
        ,name_,assignee_,start_time_,priority_) 
        values     (uuid(),v_proc_def_id_,v_task_def_key_,v_proc_inst_id_,v_proc_inst_id_
        ,v_name_,v_assignee_,now(),50);
        LEAVE label;
        ELSE
        LEAVE label;
        END IF;
        END LOOP label;
CLOSE cur_act_hi_taskinst;
-- 3、修改当前执行信息
UPDATE ACT_RU_EXECUTION SET rev_ = rev_ + 1 , ACT_ID_ = v_task_def_key_   where  PROC_INST_ID_ = v_proc_inst_id_;
-- 4、修改当前任务信息    
UPDATE act_ru_task SET name_ = v_name_,TASK_DEF_KEY_ = v_task_def_key_,ASSIGNEE_ = v_assignee_ where PROC_INST_ID_ = v_proc_inst_id_;

# 回退运行实例
INSERT INTO `xcx_workorder`.`act_ru_execution`(`ID_`, `REV_`, `PROC_INST_ID_`, `BUSINESS_KEY_`, `PARENT_ID_`, `PROC_DEF_ID_`, `SUPER_EXEC_`, `ROOT_PROC_INST_ID_`, `ACT_ID_`, `IS_ACTIVE_`, `IS_CONCURRENT_`, `IS_SCOPE_`, `IS_EVENT_SCOPE_`, `IS_MI_ROOT_`, `SUSPENSION_STATE_`, `CACHED_ENT_STATE_`, `TENANT_ID_`, `NAME_`, `START_TIME_`, `START_USER_ID_`, `LOCK_TIME_`, `IS_COUNT_ENABLED_`, `EVT_SUBSCR_COUNT_`, `TASK_COUNT_`, `JOB_COUNT_`, `TIMER_JOB_COUNT_`, `SUSP_JOB_COUNT_`, `DEADLETTER_JOB_COUNT_`, `VAR_COUNT_`, `ID_LINK_COUNT_`) 
SELECT PROC_INST_ID_, 1, PROC_INST_ID_, BUSINESS_KEY_, NULL, PROC_DEF_ID_, NULL,  PROC_INST_ID_, NULL, 1, 0,1,0,0,1,NULL,NULL,NULL, START_TIME_, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0  FROM act_hi_procinst WHERE BUSINESS_KEY_ = business_key;

INSERT INTO `xcx_workorder`.`act_ru_execution`(`ID_`, `REV_`, `PROC_INST_ID_`, `BUSINESS_KEY_`, `PARENT_ID_`, `PROC_DEF_ID_`, `SUPER_EXEC_`, `ROOT_PROC_INST_ID_`, `ACT_ID_`, `IS_ACTIVE_`, `IS_CONCURRENT_`, `IS_SCOPE_`, `IS_EVENT_SCOPE_`, `IS_MI_ROOT_`, `SUSPENSION_STATE_`, `CACHED_ENT_STATE_`, `TENANT_ID_`, `NAME_`, `START_TIME_`, `START_USER_ID_`, `LOCK_TIME_`, `IS_COUNT_ENABLED_`, `EVT_SUBSCR_COUNT_`, `TASK_COUNT_`, `JOB_COUNT_`, `TIMER_JOB_COUNT_`, `SUSP_JOB_COUNT_`, `DEADLETTER_JOB_COUNT_`, `VAR_COUNT_`, `ID_LINK_COUNT_`) 
SELECT CONCAT(PROC_INST_ID_, 1) , 6, PROC_INST_ID_, NULL, PROC_INST_ID_, PROC_DEF_ID_, NULL,  PROC_INST_ID_, 'usertask6', 1, 0, 0,0,0,1,NULL,NULL,NULL, START_TIME_, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0  FROM act_hi_procinst WHERE BUSINESS_KEY_ = business_key;

# 回退运行时任务
INSERT INTO `xcx_workorder`.`act_ru_task`(`ID_`, `REV_`, `EXECUTION_ID_`, `PROC_INST_ID_`, `PROC_DEF_ID_`, `NAME_`, `PARENT_TASK_ID_`, `DESCRIPTION_`, `TASK_DEF_KEY_`, `OWNER_`, `ASSIGNEE_`, `DELEGATION_`, `PRIORITY_`, `CREATE_TIME_`, `DUE_DATE_`, `CATEGORY_`, `SUSPENSION_STATE_`, `TENANT_ID_`, `FORM_KEY_`, `CLAIM_TIME_`) 
SELECT ID_, 1, CONCAT(v_proc_inst_id_, 1), v_proc_inst_id_, PROC_DEF_ID_, NAME_, NULL, NULL, TASK_DEF_KEY_, NULL, ASSIGNEE_, NULL, 50, NOW(), NULL, NULL, 1, NULL, NULL, NULL FROM act_hi_taskinst where PROC_INST_ID_ = v_proc_inst_id_ AND NAME_ = '复核中' and TASK_DEF_KEY_ is not null ORDER BY ID_ DESC LIMIT 1;

# 回退运行时变量
INSERT INTO `xcx_workorder`.`act_ru_variable`(`ID_`, `REV_`, `TYPE_`, `NAME_`, `EXECUTION_ID_`, `PROC_INST_ID_`, `TASK_ID_`, `BYTEARRAY_ID_`, `DOUBLE_`, `LONG_`, `TEXT_`, `TEXT2_`) SELECT ID_, 1, VAR_TYPE_, NAME_, EXECUTION_ID_, PROC_INST_ID_, TASK_ID_, BYTEARRAY_ID_, DOUBLE_, LONG_, TEXT_, TEXT2_ FROM act_hi_varinst WHERE PROC_INST_ID_ = v_proc_inst_id_;
# 回退运行时用户信息
INSERT INTO `xcx_workorder`.`act_ru_identitylink`(`ID_`, `REV_`, `GROUP_ID_`, `TYPE_`, `USER_ID_`, `TASK_ID_`, `PROC_INST_ID_`, `PROC_DEF_ID_`) SELECT ID_, 1, NULL, TYPE_, USER_ID_, TASK_ID_, PROC_INST_ID_, NULL FROM act_hi_identitylink WHERE PROC_INST_ID_ = v_proc_inst_id_;

END

 

2.2、工作流未结束回退任务

CREATE PROCEDURE workflow_end_go_back(IN business_key VARCHAR(64))
BEGIN
    DECLARE v_id_ VARCHAR(64);
    DECLARE v_proc_def_id_ VARCHAR(64);
    DECLARE v_task_def_key_ VARCHAR(255);
    DECLARE v_name_ VARCHAR(255);
    DECLARE v_assignee_ VARCHAR(255);
    DECLARE v_proc_inst_id_ VARCHAR(64);
    DECLARE cur_act_hi_taskinst CURSOR FOR SELECT ID_,PROC_DEF_ID_,TASK_DEF_KEY_,NAME_,ASSIGNEE_ 
    FROM act_hi_taskinst
    where PROC_INST_ID_ = (SELECT PROC_INST_ID_ FROM act_hi_procinst WHERE BUSINESS_KEY_ = business_key) and TASK_DEF_KEY_ is not null
    order by START_TIME_ desc ;
    select PROC_INST_ID_ into v_proc_inst_id_ from act_hi_procinst WHERE BUSINESS_KEY_ = business_key;
OPEN cur_act_hi_taskinst;
        set @i = 0;
        label: LOOP
        SET @i = @i + 1;
        FETCH cur_act_hi_taskinst INTO v_id_, v_proc_def_id_, v_task_def_key_, v_name_, v_assignee_;
        IF @i = 1 THEN
        -- 1、处理历史任务 将当前任务设置为完成状态
        UPDATE ACT_HI_TASKINST SET END_TIME_ = now(),duration_ = 1 ,DELETE_REASON_ = 'completed' where id_ = v_id_;
        ELSEIF @i = 2 THEN
        -- 2、处理历史任务 添加当前新任务
        INSERT INTO ACT_HI_TASKINST (id_    ,proc_def_id_    ,task_def_key_,proc_inst_id_,execution_id_
        ,name_,assignee_,start_time_,priority_) 
        values     (uuid(),v_proc_def_id_,v_task_def_key_,v_proc_inst_id_,v_proc_inst_id_
        ,v_name_,v_assignee_,now(),50);
        LEAVE label;
        ELSE
        LEAVE label;
        END IF;
        END LOOP label;
CLOSE cur_act_hi_taskinst;
-- 3、修改当前执行信息
UPDATE ACT_RU_EXECUTION SET rev_ = rev_ + 1 , ACT_ID_ = v_task_def_key_   where  PROC_INST_ID_ = v_proc_inst_id_;
-- 4、修改当前任务信息    
UPDATE act_ru_task SET name_ = v_name_,TASK_DEF_KEY_ = v_task_def_key_,ASSIGNEE_ = v_assignee_ where PROC_INST_ID_ = v_proc_inst_id_;

END

 

3、调用示例

CALL workflow_end_go_back(677813249634205732);

 

4、切换流程定义

# 运行中的工作流数据，切换其他的流程定义

#根据流程定义Key查询流程的部署时间和，流程定义ID
select a.DEPLOY_TIME_, b.ID_ from act_re_deployment a, ACT_RE_PROCDEF b where a.ID_ = b.DEPLOYMENT_ID_ and b.KEY_ = 'workorderOfUninstallProcess'

# 根据业务KEY, 修改流程定义ID
update ACT_RU_EXECUTION set PROC_DEF_ID_ = 'workorderOfUninstallProcess:8:8330022' where BUSINESS_KEY_ IN (
"898482888129908828"
); 

# ACT_RU_EXECUTION 中的BUSINESS_KEY_只有第一次启动的时候会存，后面每个节点流转的时候为空，需要根据启动的流程实例ID去清洗。ACT_ID_为流程定义文件中定义的节点ID
UPDATE ACT_RU_EXECUTION SET PROC_DEF_ID_ = 'workorderOfUninstallProcess:8:8330022', ACT_ID_ = 'Activity_03o5dt1' WHERE PROC_INST_ID_ IN (
SELECT * FROM (select PROC_INST_ID_ from ACT_RU_EXECUTION where BUSINESS_KEY_ IN (
"898482888129908828"
)) a
);

update ACT_RU_TASK a set PROC_DEF_ID_ = 'workorderOfUninstallProcess:8:8330022', NAME_ = '待排期' where a.PROC_INST_ID_ IN (
 select PROC_INST_ID_ from ACT_RU_EXECUTION where BUSINESS_KEY_ IN(
 "898482888129908828"
 )
);

update act_hi_procinst set PROC_DEF_ID_ = 'workorderOfUninstallProcess:8:8330022' where BUSINESS_KEY_ IN(
"898482888129908828"
);

update act_hi_taskinst a set PROC_DEF_ID_ = 'workorderOfUninstallProcess:8:8330022', NAME_ = '待排期' where a.PROC_INST_ID_ IN (
 select PROC_INST_ID_ from ACT_RU_EXECUTION where BUSINESS_KEY_ IN(
 "898482888129908828"
 )
);