flowable 6.7.2 自定义mybatis.xml查询我的待办，我的已办，流程列表，流程日志等

由于公司系统使用的是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);

    <!--ASSIGNEE_为接收办理人，OWNER_为代理办理人-->
    <select id="selectWfTaskLogVOList" resultType="com.geline.cloud.workflow.domain.TaskLogVO">
        SELECT RES.ID_ as task_id, RES.TASK_DEF_KEY_, RES.NAME_ as task_name, RES.START_TIME_, RES.END_TIME_,
               RES.DURATION_, i.GROUP_ID_, SUBSTRING_INDEX(REPLACE(i.GROUP_ID_,'ROLE',''), '-', 1) as role_id,
               if(LOCATE('-', i.GROUP_ID_)>0, SUBSTRING_INDEX(i.GROUP_ID_, '-', -1), '') as deptId,
               RES.ASSIGNEE_, u1.nick_name as assignee_name, RES.OWNER_, u2.nick_name as owner_name, c.TYPE_, c.TIME_, c.MESSAGE_
        from ACT_HI_TASKINST RES
            left join act_hi_identitylink i on i.TASK_ID_=RES.ID_ and i.TYPE_='candidate'
            left join act_hi_comment c on c.TASK_ID_=RES.id_ and c.ACTION_='AddComment'
            left join sys_user_info u1 on u1.user_id=RES.ASSIGNEE_
            left join sys_user_info u2 on u2.user_id=RES.OWNER_
        WHERE 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 procInstId;
    @ApiModelProperty(value = "流程ID")
    private String procDefId;
    @ApiModelProperty(value = "流程key")
    private String procDefKey;
    @ApiModelProperty(value = "办理人")
    private String assignee;
    @ApiModelProperty(value = "任务名称")
    private String taskName;
    @ApiModelProperty(value = "发起人ID")
    private String startUserId;
    @ApiModelProperty(value = "发起人")
    private String startUserName;
    @ApiModelProperty(value = "业务ID")
    private String businessKey;
    @ApiModelProperty(value = "处理状态")
    private String businessStatus;
    @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 = "公文信息")
    @TransSQL(value = "select * from t_gongwe where id=#{businessKey}")
    private GwSendRow gongwe;

    @ApiModelProperty(value = "公文类别")
    private String gwKind;

    @ApiModelProperty(value = "公文类别(中文)")
    @TransDict(dictType = "document_type", source = "gwKind")
    private String gwKindDesc;

    public String getDurationTime() {
        if(startTime!=null) {
            return DateUtil.formatBetween(endTime==null ? new Date() : endTime, startTime);
        }else {
            return "";
        }
    }
}

@ApiModel("已办任务VO")
@Getter
@Setter
public class TaskFinishedVO {

    @ApiModelProperty(value = "任务ID")
    private String id;
    @ApiModelProperty(value = "流程实例ID")
    private String procInstId;
    @ApiModelProperty(value = "流程ID")
    private String procDefId;
    @ApiModelProperty(value = "任务节点key")
    private String taskDefKey;
    @ApiModelProperty(value = "办理人")
    private String assignee;
    @ApiModelProperty(value = "任务名称")
    private String taskName;
    @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 startTime;
    @ApiModelProperty(value = "结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date endTime;
    @ApiModelProperty(value = "持续时长")
    private Long duration;

    @ApiModelProperty(value = "公文信息")
    @TransSQL(value = "select * from t_gongwe where id=#{businessKey}")
    private GwSendRow gongwe;

    @ApiModelProperty(value = "公文类别")
    private String gwKind;

    @ApiModelProperty(value = "公文类别(中文)")
    @TransDict(dictType = "document_type", source = "gwKind")
    private String gwKindDesc;

    public String getDurationTime() {
        if(startTime!=null) {
            return DateUtil.formatBetween(endTime==null ? new Date() : endTime, startTime);
        }else {
            return "";
        }
    }
}

@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 = "开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date startTime;
    @ApiModelProperty(value = "结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date endTime;
    @ApiModelProperty(value = "持续时长")
    private Long duration;
    @ApiModelProperty(value = "办理人ID")
    private String assignee;
    @ApiModelProperty(value = "办理人")
    private String assigneeName;
    @ApiModelProperty(value = "代理人ID")
    private String owner;
    @ApiModelProperty(value = "代理人")
    private String ownerName;
    @ApiModelProperty("意见类别 0 正常意见  1 退回意见 2 驳回意见")
    private String type;
    @ApiModelProperty("意见时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date time;
    @ApiModelProperty("意见内容")
    private String message;

    @ApiModelProperty("候选组ID: ROLE128;ROLE133-129")
    private String groupId;
    @ApiModelProperty("角色ID")
    private String roleId;
    @ApiModelProperty("部门ID")
    private String deptId;
    @ApiModelProperty("角色名称")
    @TransSQL("select role_name from sys_role where role_id=#{roleId}")
    private String roleName;
    @ApiModelProperty("部门名称")
    @TransSQL("select dept_name from sys_dept where dept_id=#{deptId}")
    private String deptName;
    @ApiModelProperty("候选执行组(部门>角色)")
    private String candidate;

    public String getAssigneeName() {
        if(StrUtil.isNotBlank(ownerName)){
            return String.format("%s（%s代理）", assigneeName, ownerName);
        }
        return assigneeName;
    }

    public String getDurationTime() {
        if(startTime!=null) {
            return DateUtil.formatBetween(endTime==null ? new Date() : endTime, startTime);
        }else {
            return "";
        }
    }

    public String getCandidate() {
        StringBuffer sb = new StringBuffer();
        if(StrUtil.isNotBlank(deptName)){
            sb.append(deptName);
            sb.append(" > ");
        }
        if(StrUtil.isNotBlank(roleName)){
            sb.append(roleName);
        }
        return sb.toString();
    }
}

@ApiModel("已办任务:查询条件")
@Getter
@Setter
public class FinishedTaskQuery {

    @ApiModelProperty(value = "用户ID", hidden = true)
    @TableField(value = "t.ASSIGNEE_")
    private String userId;

    @ApiModelProperty(value = "公文标题")
    @TableField(value = "gw.title", condition = SqlConditions.LIKE)
    private String title;

    @ApiModelProperty(value = "流水号")
    @TableField(value = "gw.serial_number", condition = SqlConditions.LIKE)
    private String serialNumber;

    @ApiModelProperty(value = "公文编号")
    @TableField(value = "gw.gw_code", condition = SqlConditions.LIKE)
    private String gwCode;

    @ApiModelProperty(value = "主办单位")
    @TableField(value = "d.dept_name", condition = SqlConditions.LIKE)
    private String handleDeptName;

    @ApiModelProperty(value = "公文类型")
    @TableField(value = "gw.gw_kind")
    private String gwKind;

    @ApiModelProperty(value = "拟搞人")
    @TableField(value = "gw.drafted_user", condition = SqlConditions.LIKE)
    private String draftedUser;

    @ApiModelProperty(value = "拟搞日期(格式：draftedDate[0]=2020-01-01&draftedDate[1]=2020-02-01) 排序 gw.drafted_date")
    @TableField(value = "gw.drafted_date", condition = SqlConditions.BETWEEN_AND)
    private String[] draftedDate;

    @ApiModelProperty(value = "紧急程度")
    @TableField(value = "gw.grade")
    private String grade;

    @ApiModelProperty(value = "密级")
    @TableField(value = "gw.secret_level")
    private String secretLevel;

    @ApiModelProperty(value = "发送人")
    @TableField(value = "gw.from_user_name", condition = SqlConditions.LIKE)
    private String fromUserName;

    @ApiModelProperty(value = "发文日期(格式：receiveDate[0]=2020-01-01&receiveDate[1]=2020-02-01) 排序 gw.receive_date")
    @TableField(value = "gw.receive_date", condition = SqlConditions.BETWEEN_AND)
    private String[] receiveDate;

    @ApiModelProperty(value = "来文单位")
    @TableField(value = "gw.from_dept_name", condition = SqlConditions.LIKE)
    private String fromDeptName;
}