Flowable 历史审批查询

1、查询历史任务审批人

SELECT
	AHT.ID_,
	AHT.PROC_DEF_ID_,
	AHT.TASK_DEF_KEY_,
	AHT.EXECUTION_ID_,
	AHI.GROUP_ID_,
	AHI.TYPE_,
	SD.DEPT_NAME AS GNAME 
FROM
	ACT_HI_TASKINST AHT
	LEFT JOIN ACT_HI_IDENTITYLINK AHI ON AHI.TASK_ID_ = AHT.ID_
	LEFT JOIN SYS_DEPT SD ON CONCAT( SD.DEPT_ID, '' ) = SUBSTRING( AHI.GROUP_ID_, 5 ) 
WHERE
	SUBSTRING( AHI.GROUP_ID_, 1, 4 ) = 'DEPT' 
	AND AHT.PROC_INST_ID_ = '7f0f7bea-a227-11ef-a557-44af28c819ff' 
	AND AHI.TYPE_ = 'candidate' UNION ALL
SELECT
	AHT.ID_,
	AHT.PROC_DEF_ID_,
	AHT.TASK_DEF_KEY_,
	AHT.EXECUTION_ID_,
	AHI.GROUP_ID_,
	AHI.TYPE_,
	SR.ROLE_NAME AS GNAME 
FROM
	ACT_HI_TASKINST AHT
	LEFT JOIN ACT_HI_IDENTITYLINK AHI ON AHI.TASK_ID_ = AHT.ID_
	LEFT JOIN SYS_ROLE SR ON CONCAT( SR.ROLE_ID, '' ) = SUBSTRING( AHI.GROUP_ID_, 5 ) 
WHERE
	SUBSTRING( AHI.GROUP_ID_, 1, 4 ) = 'ROLE' 
	AND AHT.PROC_INST_ID_ = '7f0f7bea-a227-11ef-a557-44af28c819ff' 
	AND AHI.TYPE_ = 'candidate'

2、查询历史任务及审批人

SELECT
	RES.PROC_DEF_ID_,
	RES.ACT_ID_,
	RES.ACT_NAME_,
	RES.ACT_TYPE_,
	RES.START_TIME_,
	RES.END_TIME_,
	SU.NICK_NAME ,
	AHC.MESSAGE_
FROM
	ACT_HI_ACTINST RES
	LEFT JOIN ACT_HI_COMMENT AHC ON AHC.PROC_INST_ID_ = RES.PROC_INST_ID_
	LEFT JOIN SYS_USER SU ON SU.USER_ID = AHC.USER_ID_ 
WHERE
	AHC.TASK_ID_ = RES.TASK_ID_ 
	AND RES.PROC_INST_ID_ = '7f0f7bea-a227-11ef-a557-44af28c819ff' 
	AND RES.ACT_TYPE_ IN ( 'endEvent', 'startEvent', 'userTask' ) 
ORDER BY
	START_TIME_ DESC,
	END_TIME_ DESC

3、查询当前任务审批人

SELECT DISTINCT
	AST.ID_ AS ST_TASK_ID,
	AST.NAME_ AS ST_TASK_NAME,
	AST.CREATE_TIME_ AS DT_CREATE,
	'' AS ST_USER_NAME,
	SD.DEPT_NAME AS ST_DEPT 
FROM
	ACT_RU_TASK AST
	LEFT JOIN ACT_RU_IDENTITYLINK ARI ON ARI.TASK_ID_ = AST.ID_
	LEFT JOIN SYS_DEPT SD ON CONCAT( SD.DEPT_ID, '' ) = SUBSTRING( ARI.GROUP_ID_, 5 ) 
WHERE
	SUBSTRING( ARI.GROUP_ID_, 1, 4 ) = 'DEPT' 
	AND SD.DEPT_NAME IS NOT NULL 
	AND AST.PROC_INST_ID_ = '8b309e78-a24f-11ef-9bbc-4e796ed2cc88' 
	AND ARI.TYPE_ = 'candidate' UNION
SELECT DISTINCT
	AST.ID_ AS ST_TASK_ID,
	AST.NAME_ AS ST_TASK_NAME,
	AST.CREATE_TIME_ AS DT_CREATE,
	'' AS ST_USER_NAME,
	SR.ROLE_NAME AS ST_DEPT 
FROM
	ACT_RU_TASK AST
	LEFT JOIN ACT_RU_IDENTITYLINK ARI ON ARI.TASK_ID_ = AST.ID_
	LEFT JOIN SYS_ROLE SR ON CONCAT( SR.ROLE_ID, '' ) = SUBSTRING( ARI.GROUP_ID_, 5 ) 
WHERE
	SUBSTRING( ARI.GROUP_ID_, 1, 4 ) = 'ROLE' 
	AND SR.ROLE_NAME IS NOT NULL 
	AND AST.PROC_INST_ID_ = '8b309e78-a24f-11ef-9bbc-4e796ed2cc88' 
	AND ARI.TYPE_ = 'candidate' UNION
SELECT 
	AST.ID_ AS ST_TASK_ID,
	AST.NAME_ AS ST_TASK_NAME,
	AST.CREATE_TIME_ AS DT_CREATE,
	SU.NICK_NAME AS ST_USER_NAME,
	SD.DEPT_NAME AS ST_DEPT 
FROM
	ACT_RU_TASK AST 
		LEFT JOIN SYS_USER SU ON SU.USER_ID = AST.ASSIGNEE_
		LEFT JOIN SYS_DEPT SD ON SD.DEPT_ID= SU.DEPT_ID
WHERE
	AST.PROC_INST_ID_ = '8b309e78-a24f-11ef-9bbc-4e796ed2cc88' 
	AND AST.ASSIGNEE_ IS NOT NULL