查询待办消息的语句

• 获取用户的所有流程待办

SELECT T.ID_,
           T.PROC_INST_ID_,
           E.CREATE_TIME_,
           E.Id_  TASKID,
           E.NAME_         STATENAME,
           D.NAME_         TASKNAME,
           D.FUNCTION_ID,
           C.OBJNAME FUNCTIONNAME,
           E.ASSIGNEE_
      FROM ACT_RU_EXECUTION T
      LEFT JOIN ACT_RE_PROCDEF A ON A.ID_ = T.PROC_DEF_ID_
      LEFT JOIN ACT_RE_MODEL D ON D.KEY_ = A.KEY_
      LEFT JOIN CORE_RES C ON C.RWID = D.FUNCTION_ID
      LEFT JOIN ACT_RU_TASK E ON E.PROC_INST_ID_ = T.PROC_INST_ID_ AND E.EXECUTION_ID_ = T.ID_
      WHERE T.IS_ACTIVE_ =1 AND EXISTS (SELECT 1
              FROM ACT_RU_IDENTITYLINK H
             WHERE H.PROC_INST_ID_ = T.PROC_INST_ID_ AND H.USER_ID_=#{USERID})
     ORDER BY E.CREATE_TIME_ DESC

• 获取各个功能待办的的总数

SELECT COUNT(*) AS CNT,E.FUNCTION_ID
FROM ACT_RU_TASK A
LEFT JOIN ACT_RU_IDENTITYLINK B ON A.PROC_INST_ID_ = B.PROC_INST_ID_ OR A.ID_ = B.TASK_ID_
INNER JOIN ACT_RU_EXECUTION C ON A.PROC_INST_ID_=C.PROC_INST_ID_  AND  C.ID_=C.PROC_INST_ID_
LEFT JOIN ACT_RE_PROCDEF d ON A.PROC_DEF_ID_ = d.id_
LEFT JOIN ACT_RE_MODEL E ON E.KEY_ = D.KEY_
WHERE (
A.ASSIGNEE_ IS NULL AND B.TASK_ID_=A.ID_ AND  B.USER_ID_=#{userid,jdbcType=VARCHAR} AND B.TYPE_='candidate'
OR
A.ASSIGNEE_=#{USERID} AND B.USER_ID_=A.ASSIGNEE_ AND B.TYPE_='participant'
OR (B.OWNER_TASKID = A.ID_ AND B.USER_ID_ = #{USERID} AND A.DELEGATION_='PENDING')
)
group by E.FUNCTION_ID

查询办理中

• 获取各个功能办理中的的总数

  SELECT COUNT(*) AS CNT, E.FUNCTION_ID
  FROM ACT_RU_EXECUTION A
  LEFT JOIN ACT_RU_TASK B ON A.PROC_INST_ID_ = B.PROC_INST_ID_ and B.EXECUTION_ID_ =A.ID_ AND B.ASSIGNEE_ != #{USERID}
  LEFT JOIN ACT_RU_IDENTITYLINK C ON C.PROC_INST_ID_ = A.PROC_INST_ID_ AND C.OWNER_TASKID != B.ID_ AND C.USER_ID_ = #{USERID}
  LEFT JOIN ACT_RE_PROCDEF D ON A.PROC_DEF_ID_ = D.ID_
  LEFT JOIN ACT_RE_MODEL E ON E.KEY_ = D.KEY_
  WHERE C.USER_ID_ = #{USERID}
  AND EXISTS (
    SELECT 1 FROM ACT_HI_TASKINST F
    WHERE F.PROC_INST_ID_ = A.PROC_INST_ID_ AND F.ASSIGNEE_ = #{USERID})
  GROUP BY E.FUNCTION_ID