由于公司系统使用的是mybatis-plus操作数据库,研究flowable提供API封装不太好用,故分析最终执行的SQL,进行mybatis.xml改写;此处实现了查询流程定义,我的流程,我的待办,我的已办,流程日志等连表sql;
说明:系统中没有用 flowable用户表 act_id_user,用了自定义用户表 p_project_user。
1. 查询流程定义列表
Page<WfDefinitionVO> selectDefinitionList(Page page, String tenantId);
<select id="selectDefinitionList" resultType="com.geline.cloud.workflow.pojo.WfDefinitionVO"> SELECT RES.ID_ as DEFINITION_ID_, RES.KEY_ as process_key, RES.NAME_ as process_name, RES.CATEGORY_, f.form_id, f.form_name, RES.SUSPENSION_STATE_, RES.DEPLOYMENT_ID_, d.DEPLOY_TIME_ as deployment_time, RES.VERSION_ from ACT_RE_PROCDEF RES left join ACT_RE_DEPLOYMENT d on d.ID_=RES.DEPLOYMENT_ID_ left join p_wf_deploy_form f on f.deploy_id=RES.DEPLOYMENT_ID_ WHERE 1=1 and RES.TENANT_ID_ = #{tenantId} and RES.VERSION_ = (select max(VERSION_) from ACT_RE_PROCDEF where KEY_ = RES.KEY_ and TENANT_ID_ = #{tenantId}) order by RES.KEY_ asc </select>
2.查询已发布的流程版本列表
Page<WfDefinitionVO> selectPublishList(Page page, String processKey);
<select id="selectPublishList" resultType="com.geline.cloud.workflow.pojo.WfDefinitionVO">
SELECT ID_ as definition_id, KEY_ as process_key, NAME_ as process_name, CATEGORY_, VERSION_, DEPLOYMENT_ID_, SUSPENSION_STATE_
from ACT_RE_PROCDEF
WHERE KEY_ = #{processKey}
order by VERSION_ asc
</select>
3. 查询用户发起流程列表
Page<TaskOwnVO> selectOwnList(Page page, String userId);
<select id="selectOwnList" resultType="com.geline.cloud.workflow.pojo.TaskOwnVO">
SELECT p.NAME_ as PROC_DEF_NAME_, t.ACT_NAME_, p.PROC_INST_ID_, p.PROC_DEF_ID_, d.KEY_ as PROC_DEF_KEY_, d.DEPLOYMENT_ID_,
u.user_name as start_user_name, p.BUSINESS_KEY_, p.BUSINESS_STATUS_, p.START_TIME_, p.END_TIME_, p.DURATION_
from (
SELECT PROC_INST_ID_, ACT_NAME_, END_TIME_ FROM ACT_HI_ACTINST
WHERE START_TIME_ IN(SELECT MAX(START_TIME_) FROM ACT_HI_ACTINST GROUP BY PROC_INST_ID_)
) as t
left join ACT_HI_PROCINST p on t.PROC_INST_ID_=p.PROC_INST_ID_
left join ACT_RE_PROCDEF d on p.PROC_DEF_ID_ = d.ID_
left join p_project_user u on u.id=p.START_USER_ID_
WHERE p.DELETE_REASON_ IS NULL and p.START_USER_ID_=#{userId}
order by p.START_TIME_ desc
</select>
4.查询用户待办任务列表
Page<TaskTodoVO> selectTodoList(Page page, String userId);
<select id="selectTodoList" resultType="com.geline.cloud.workflow.domain.TaskTodoVO">
SELECT t.ID_, gw.title, t.NAME_ as task_name, t.ASSIGNEE_, a.START_TIME_, a.END_TIME_, a.DURATION_, p.START_USER_ID_,
u.user_name as start_user_name, a.ACT_TYPE_, p.BUSINESS_KEY_, p.BUSINESS_STATUS_, t.PROC_DEF_ID_, t.TASK_DEF_KEY_, p.PROC_INST_ID_
from ACT_RU_TASK t
left join ACT_HI_PROCINST p on t.PROC_INST_ID_=p.PROC_INST_ID_
left join act_ru_actinst a on a.PROC_INST_ID_=p.PROC_INST_ID_
left join sys_user u on u.user_id=p.START_USER_ID_
left join t_gongwe gw on p.BUSINESS_KEY_=gw.id
WHERE t.SUSPENSION_STATE_ = 1
and a.END_TIME_ is null
and a.ACT_TYPE_='userTask'
and (t.ASSIGNEE_ = #{ew.userId} or (
t.ID_ in(select TASK_ID_ from ACT_RU_IDENTITYLINK where TYPE_ = 'candidate' and (USER_ID_ = #{ew.userId} or GROUP_ID_ in(select concat('ROLE',role_id) from sys_user_role where user_id=#{ew.userId})))
))
<if test="ew.gwKind!=null and ew.gwKind!=''">
and gw.gw_kind =#{ew.gwKind}
</if>
order by a.START_TIME_ desc
</select>
5.查询用户已办任务列表
同一个流程查询用户所有相关记录
Page<TaskFinishedVO> selectFinishedList(Page page, @Param("ew") FinishedListQueryModel queryModel);
<select id="selectFinishedList" resultType="com.geline.cloud.workflow.domain.TaskFinishedVO">
SELECT t.ID_, gw.title, t.NAME_ as task_name, t.ASSIGNEE_, t.START_TIME_, t.END_TIME_, t.DURATION_, p.START_USER_ID_,
u.user_name as start_user_name, p.BUSINESS_KEY_, p.BUSINESS_STATUS_, t.PROC_DEF_ID_, t.TASK_DEF_KEY_, t.PROC_INST_ID_
from ACT_HI_TASKINST t
left join ACT_HI_PROCINST p on t.PROC_INST_ID_=p.PROC_INST_ID_
left join sys_user u on u.user_id=p.START_USER_ID_
left join t_gongwe gw on p.BUSINESS_KEY_=gw.id
WHERE t.ASSIGNEE_=#{ew.userId} and t.END_TIME_ is not null
<if test="ew.gwKind!=null and ew.gwKind!=''">
and gw.gw_kind =#{ew.gwKind}
</if>
<if test="ew.startUserName!=null and ew.startUserName!=''">
AND u.user_name like '%${ew.startUserName}%'
</if>
<if test="ew.startDate!=null">
<![CDATA[AND t.START_TIME_ >= #{ew.startDate}]]>
</if>
<if test="ew.endDate!=null">
<![CDATA[AND t.START_TIME_ <= #{ew.endDate}]]>
</if>
order by t.END_TIME_ desc
</select>
6.查询用户已办任务最新一条
同一个流程只保留用户最新一条记录
Page<TaskFinishedVO> selectFinishedListByLatestOne(Page page, @Param("ew") FinishedListQueryModel queryModel);
<select id="selectFinishedListByLatestOne" resultType="com.geline.cloud.workflow.pojo.TaskFinishedVO">
select t2.*
from (SELECT p.BUSINESS_KEY_, max(t.END_TIME_) as END_TIME_
from ACT_HI_TASKINST t
left join ACT_HI_PROCINST p on p.PROC_INST_ID_=t.PROC_INST_ID_
left join p_project_user u on u.id=p.START_USER_ID_
left join ACT_HI_COMMENT c on c.TASK_ID_=t.id_
WHERE c.ACTION_='AddComment' and t.ASSIGNEE_=#{ew.userId}
group by p.BUSINESS_KEY_ ) as t1
inner join (
SELECT t.PROC_INST_ID_, t.PROC_DEF_ID_, p.NAME_ as PROC_DEF_NAME_, t.ASSIGNEE_, t.NAME_ as task_name,
u.user_name as start_user_name, p.BUSINESS_KEY_, p.BUSINESS_STATUS_, c.TYPE_, t.START_TIME_, t.END_TIME_, t.DURATION_
from ACT_HI_TASKINST t
left join ACT_HI_PROCINST p on p.PROC_INST_ID_=t.PROC_INST_ID_
left join p_project_user u on u.id=p.START_USER_ID_
left join ACT_HI_COMMENT c on c.TASK_ID_=t.id_
WHERE c.ACTION_='AddComment' and t.ASSIGNEE_=#{ew.userId}
) as t2 on t1.BUSINESS_KEY_=t2.BUSINESS_KEY_ and t1.END_TIME_=t2.END_TIME_
<if test="ew.procDefName!=null and ew.procDefName!=''">
AND t2.PROC_DEF_NAME_ like '%${ew.procDefName}%'
</if>
<if test="ew.startUserName!=null and ew.startUserName!=''">
AND t2.start_user_name like '%${ew.startUserName}%'
</if>
<if test="ew.startDate!=null">
<![CDATA[AND t2.START_TIME_ >= #{ew.startDate}]]>
</if>
<if test="ew.endDate!=null">
<![CDATA[AND t2.START_TIME_ <= #{ew.endDate}]]>
</if>
order by t2.END_TIME_ desc
</select>
7.查询流程日志列表
List<WfTaskLogVO> selectWfTaskLogVOList(String procInstId);
<select id="selectWfTaskLogVOList" resultType="com.geline.cloud.workflow.pojo.TaskLogVO">
SELECT RES.ID_ as task_id, RES.TASK_DEF_KEY_, RES.NAME_ as task_name, RES.START_TIME_, RES.END_TIME_,
RES.DURATION_ as duration_time, RES.ASSIGNEE_ as assignee_id, u.user_name as assignee_name, c.TYPE_, c.MESSAGE_
from ACT_HI_TASKINST RES
left join act_hi_comment c on c.TASK_ID_=RES.id_
left join p_project_user u on u.id=RES.ASSIGNEE_
WHERE c.ACTION_='AddComment' and RES.PROC_INST_ID_ = #{procInstId}
order by RES.START_TIME_ desc
</select>
8.EntityVO 代码
@ApiModel("发起的任务VO")
@Getter
@Setter
public class TaskOwnVO {
@ApiModelProperty(value = "流程ID")
private String procDefId;
@ApiModelProperty(value = "流程名称")
private String procDefName;
@ApiModelProperty(value = "流程key")
private String procDefKey;
@ApiModelProperty(value = "节点名称")
private String actName;
@ApiModelProperty(value = "流程实例ID")
private String procInstId;
@ApiModelProperty(value = "部署ID")
private String deploymentId;
@ApiModelProperty(value = "发起用户")
private String startUserName;
@ApiModelProperty(value = "业务ID")
private String businessKey;
@ApiModelProperty(value = "处理状态")
private String businessStatus;
@ApiModelProperty(value = "开始时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date startTime;
@ApiModelProperty(value = "结束时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date endTime;
@ApiModelProperty(value = "持续时长")
private Long duration;
}
@ApiModel("待办任务VO")
@Getter
@Setter
public class TaskTodoVO {
@ApiModelProperty(value = "ID")
private String id;
@ApiModelProperty(value = "流程ID")
private String procDefId;
@ApiModelProperty(value = "流程名称")
private String procDefName;
@ApiModelProperty(value = "流程key")
private String procDefKey;
@ApiModelProperty(value = "任务名称")
private String taskName;
@ApiModelProperty(value = "流程实例ID")
private String procInstId;
@ApiModelProperty(value = "发起人ID")
private String startUserId;
@ApiModelProperty(value = "发起人")
private String startUserName;
@ApiModelProperty(value = "业务ID")
private String businessKey;
@ApiModelProperty(value = "处理状态")
private String businessStatus;
@ApiModelProperty(value = "创建时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
}
@ApiModel("已办任务VO")
@Getter
@Setter
public class TaskFinishedVO {
@ApiModelProperty(value = "流程实例ID")
private String procInstId;
@ApiModelProperty(value = "流程ID")
private String procDefId;
@ApiModelProperty(value = "流程名称")
private String procDefName;
@ApiModelProperty(value = "流程key")
private String procDefKey;
@ApiModelProperty(value = "办理人")
private String assignee;
@ApiModelProperty(value = "任务名称")
private String taskName;
@ApiModelProperty(value = "发起人")
private String startUserName;
@ApiModelProperty(value = "业务ID")
private String businessKey;
@ApiModelProperty(value = "办理状态")
private String businessStatus;
@ApiModelProperty(value = "类型")
private String type;
@ApiModelProperty(value = "开始时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date startTime;
@ApiModelProperty(value = "结束时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date endTime;
@ApiModelProperty(value = "持续时间")
private Long duration;
}
@ApiModel("流程日志VO")
@Getter
@Setter
public class TaskLogVO {
@ApiModelProperty(value = "任务ID")
private String taskId;
@ApiModelProperty(value = "任务key")
private String taskDefKey;
@ApiModelProperty(value = "任务名称")
private String taskName;
@ApiModelProperty(value = "开始时间")
private Date startTime;
@ApiModelProperty(value = "结束时间")
private Date endTime;
@ApiModelProperty(value = "持续时长")
private Long durationTime;
@ApiModelProperty(value = "办理人ID")
private String assigneeId;
@ApiModelProperty(value = "办理人")
private String assigneeName;
@ApiModelProperty(value = "类型")
private String type;
@ApiModelProperty(value = "日志内容")
private String message;
}
@ApiModel("任务查询条件")
@Getter
@Setter
public class FinishedTaskQuery {
@ApiModelProperty(value = "用户ID")
private String userId;
@ApiModelProperty(value = "流程定义名称")
private String procDefName;
@ApiModelProperty(value = "发起人名")
private String startUserName;
@ApiModelProperty(value = "开始时间")
private String startDate;
@ApiModelProperty(value = "结束时间")
private String endDate;
}