SELECT
task.*
FROM
(
SELECT
bpm_task.ID_,
bpm_task.PROC_INST_ID_,
bpm_task.proc_def_key_,
bpm_task.NAME_,
bpm_task.ASSIGNEE_ID_,
bpm_task.ASSIGNEE_NAME_,
bpm_task.STATUS_,
bpm_task.subject_,
bpm_task.proc_def_name_,
bpm_task.create_time_,
bpm_task.owner_name_,
bpm_task.task_id_,
bpm_task.PROP1_ as PROP1_,
bpm_task.PROP2_ as PROP2_,
bpm_task.PROP3_ as PROP3_,
bpm_task.PROP4_ as PROP4_,
bpm_task.PROP5_ as PROP5_,
bpm_task.PROP6_ as PROP6_,
bpm_task.node_id_,
'1' AS isBpmTask
FROM
bpm_task LEFT JOIN BPM_PRO_INST inst ON bpm_task.PROC_INST_ID_ = inst.ID_
WHERE inst.STATUS_ != 'manualend' AND inst.IS_DELE_ = 0 UNION ALL
SELECT
bpm_task_notice.ID_,
bpm_task_notice.PROC_INST_ID_,
bpm_task_notice.proc_def_key_,
bpm_task_notice.NAME_,
bpm_task_notice.ASSIGNEE_ID_,
bpm_task_notice.ASSIGNEE_NAME_,
bpm_task_notice.STATUS_,
bpm_task_notice.subject_,
bpm_task_notice.proc_def_name_,
bpm_task_notice.create_time_,
bpm_task_notice.owner_name_,
bpm_task_notice.task_id_,
'' as PROP1_,
'' as PROP2_,
'' as PROP3_,
'' as PROP4_,
'' as PROP5_,
NULL as PROP6_,
bpm_task_notice.node_id_,
'0' AS isBpmTask
FROM
bpm_task_notice LEFT JOIN BPM_PRO_INST inst ON bpm_task_notice.PROC_INST_ID_ = inst.ID_
WHERE
bpm_task_notice.IS_READ_ != 1 AND inst.STATUS_ != 'manualend' AND inst.IS_DELE_ = 0
) task
${ew.customSqlSegment}
SELECT task.*,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
due.due_time_ dueTaskTime,
due.EXPIRATION_DATE_ dueExpDate,
due.date_type_ dueDateType,
due.status_ dueStatus
FROM (SELECT * from bpm_task UNION ALL SELECT * from bpm_task_notice ) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
LEFT JOIN (select * from bpm_task_due_time where is_new_ =1 ) due ON task.TASK_ID_ = due.TASK_ID_
select * from (
SELECT task.*,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
due.due_time_ dueTaskTime,
due.EXPIRATION_DATE_ dueExpDate,
due.date_type_ dueDateType,
due.status_ dueStatus,
inst.URGENT_STATE_ urgentStateValue
FROM (
SELECT * from bpm_task
INNER JOIN (
SELECT ut.ID_ utaskId, group_concat(ut.assignee_id_) as leaderIds from
(select ID_,'' as assignee_id_ from bpm_task where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task task where (task.assignee_id_=#{key}
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as 'ID_','${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task.ID_
UNION SELECT bpm_task_notice.*,bpm_task_notice.TASK_ID_ As uTaskId, '' As leaderIds from bpm_task_notice where assignee_id_=#{map.userId}
) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
LEFT JOIN (select * from bpm_task_due_time where is_new_ =1 ) due ON task.TASK_ID_ = due.TASK_ID_
) tmp
${ew.customSqlSegment}
and
where
tmp.status_ !='TRANSFORMING' and tmp.status_!='COPYTO' and tmp.IS_DELE_ = 0 and tmp.status_ !='APPROVELINEING' and tmp.status_!='SIGNSEQUENCEING' and tmp.status_!='SIGNLINEING' and tmp.instStatus != 'manualend' and tmp.instStatus != 'adminmanualend'
group by tmp.id_,tmp.IS_DELE_,tmp.name_,tmp.subject_,tmp.proc_inst_id_,tmp.task_id_,tmp.exec_id_,tmp.node_id_,tmp.proc_def_id_,tmp.proc_def_key_,tmp.proc_def_key_,
tmp.proc_def_name_,tmp.owner_id_,tmp.assignee_id_,tmp.status_,tmp.priority_,tmp.create_time_,tmp.due_time_,tmp.suspend_state_,tmp.parent_id_,
tmp.bpmn_inst_id_,tmp.bpmn_def_id_,tmp.type_id_,tmp.support_mobile_,tmp.OWNER_NAME_,tmp.ASSIGNEE_NAME_,tmp.instIsForbidden,tmp.IS_READ_,tmp.IS_REVOKE_,tmp.prop1_,tmp.prop2_,tmp.prop3_,tmp.prop4_,tmp.prop5_,tmp.prop6_,tmp.utaskId,
tmp.leaderIds,
procDefName,creatorId,creator,createDate, instStatus,typeId,dueTaskTime,dueExpDate,dueDateType,dueStatus,urgentStateValue
order by CREATE_TIME_ desc
select distinct * from (
SELECT task.*,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
due.due_time_ dueTaskTime,
due.EXPIRATION_DATE_ dueExpDate,
due.date_type_ dueDateType,
due.status_ dueStatus,
inst.URGENT_STATE_ urgentStateValue
FROM (
SELECT ID_,
NAME_,
SUBJECT_,
PROC_INST_ID_,
TASK_ID_,
EXEC_ID_,
NODE_ID_,
PROC_DEF_ID_,
PROC_DEF_KEY_,
PROC_DEF_NAME_,
OWNER_ID_,
OWNER_NAME_,
ASSIGNEE_ID_,
ASSIGNEE_NAME_,
STATUS_,
PRIORITY_,
CREATE_TIME_,
DUE_TIME_,
SUSPEND_STATE_,
PARENT_ID_,
BPMN_INST_ID_,
BPMN_DEF_ID_,
TYPE_ID_,
SUPPORT_MOBILE_,
IS_DELE_,
IS_READ_,
IS_REVOKE_,
PROP1_,
PROP2_,
PROP3_,
PROP4_,
PROP5_,
TO_CHAR( PROP6_ ) AS PROP6_,
TENANT_ID_,
UTASKID,
LEADERIDS from bpm_task
INNER JOIN (
SELECT ut.ID_ utaskId, wm_concat(ut.assignee_id_) as leaderIds from
(select ID_,'' as assignee_id_ from bpm_task where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task task where (task.assignee_id_=#{key}
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as ID_,'${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task.ID_
UNION SELECT bpm_task_notice.ID_,
bpm_task_notice.NAME_,
bpm_task_notice.SUBJECT_,
bpm_task_notice.PROC_INST_ID_,
bpm_task_notice.TASK_ID_,
bpm_task_notice.EXEC_ID_,
bpm_task_notice.NODE_ID_,
bpm_task_notice.PROC_DEF_ID_,
bpm_task_notice.PROC_DEF_KEY_,
bpm_task_notice.PROC_DEF_NAME_,
bpm_task_notice.OWNER_ID_,
bpm_task_notice.OWNER_NAME_,
bpm_task_notice.ASSIGNEE_ID_,
bpm_task_notice.ASSIGNEE_NAME_,
bpm_task_notice.STATUS_,
bpm_task_notice.PRIORITY_,
bpm_task_notice.CREATE_TIME_,
bpm_task_notice.DUE_TIME_,
bpm_task_notice.SUSPEND_STATE_,
bpm_task_notice.PARENT_ID_,
bpm_task_notice.BPMN_INST_ID_,
bpm_task_notice.BPMN_DEF_ID_,
bpm_task_notice.TYPE_ID_,
bpm_task_notice.SUPPORT_MOBILE_,
bpm_task_notice.IS_DELE_,
bpm_task_notice.IS_READ_,
bpm_task_notice.IS_REVOKE_,
bpm_task_notice.PROP1_,
bpm_task_notice.PROP2_,
bpm_task_notice.PROP3_,
bpm_task_notice.PROP4_,
bpm_task_notice.PROP5_,
to_char( bpm_task_notice.PROP6_ ) AS PROP6_,
bpm_task_notice.TENANT_ID_,
bpm_task_notice.TASK_ID_ AS uTaskId,
'' AS leaderIds
from bpm_task_notice where assignee_id_=${map.userId}
) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
LEFT JOIN (select * from bpm_task_due_time where is_new_ ='1' ) due ON task.TASK_ID_ = due.TASK_ID_
) tmp
${ew.customSqlSegment}
and
where
tmp.status_ !='TRANSFORMING' and tmp.status_!='COPYTO' and tmp.IS_DELE_ = 0 and tmp.status_ !='APPROVELINEING' and tmp.status_!='SIGNSEQUENCEING' and tmp.status_!='SIGNLINEING' and tmp.instStatus != 'manualend' and tmp.instStatus != 'adminmanualend'
order by CREATE_TIME_ desc
select * from (
SELECT task.*,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
due.due_time_ dueTaskTime,
due.EXPIRATION_DATE_ dueExpDate,
due.date_type_ dueDateType,
due.status_ dueStatus,
inst.URGENT_STATE_ urgentStateValue
FROM (
SELECT * from bpm_task
INNER JOIN (
SELECT ut.ID_ utaskId, string_agg(ut.assignee_id_,',') as leaderIds from
(select ID_,''::text as assignee_id_ from bpm_task where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task task where (task.assignee_id_='${key}'
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as "ID_",'${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task.ID_
UNION SELECT bpm_task_notice.*,bpm_task_notice.TASK_ID_ As uTaskId, ''::text As leaderIds from bpm_task_notice where assignee_id_='${map.userId}'
) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
LEFT JOIN (select * from bpm_task_due_time where is_new_ =1 ) due ON task.TASK_ID_ = due.TASK_ID_
) tmp
${ew.customSqlSegment}
and
where
tmp.status_ !='TRANSFORMING' and tmp.status_!='COPYTO' and tmp.IS_DELE_ = 0 and tmp.status_ !='APPROVELINEING' and tmp.status_!='SIGNSEQUENCEING' and tmp.status_!='SIGNLINEING' and tmp.instStatus != 'manualend' and tmp.instStatus != 'adminmanualend'
group by tmp.id_,tmp.IS_DELE_,tmp.name_,tmp.subject_,tmp.proc_inst_id_,tmp.task_id_,tmp.exec_id_,tmp.node_id_,tmp.proc_def_id_,tmp.proc_def_key_,tmp.proc_def_key_,
tmp.proc_def_name_,tmp.owner_id_,tmp.assignee_id_,tmp.status_,tmp.priority_,tmp.create_time_,tmp.due_time_,tmp.suspend_state_,tmp.parent_id_,
tmp.bpmn_inst_id_,tmp.bpmn_def_id_,tmp.type_id_,tmp.support_mobile_,tmp.OWNER_NAME_,tmp.ASSIGNEE_NAME_,tmp.instIsForbidden,tmp.IS_READ_,tmp.IS_REVOKE_,tmp.prop1_,tmp.prop2_,tmp.prop3_,tmp.prop4_,tmp.prop5_,tmp.prop6_,tmp.utaskId,
tmp.leaderIds,tmp.tenant_id_,
procDefName,creatorId,creator,createDate, instStatus,typeId,dueTaskTime,dueExpDate,dueDateType,dueStatus,urgentStateValue
order by CREATE_TIME_ desc
SELECT type_id_ AS "typeId",COUNT(1) AS "count" FROM
( SELECT task.status_ status_,inst.status_ instStatus,inst.type_id_,inst.IS_DELE_ IS_DELE_
FROM( SELECT * FROM bpm_task
INNER JOIN ( SELECT ut.ID_ utaskId FROM ( SELECT ID_ FROM bpm_task WHERE 1 = 0
UNION ALL select ID_ from bpm_task task where task.assignee_id_=#{key}
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select bt.ID_ from bpm_task_candidate c
INNER JOIN bpm_task bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_!='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut GROUP BY ut.ID_ ) ubpt ON ubpt.uTaskId = bpm_task.ID_
) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
) tmp
WHERE tmp.status_ != 'TRANSFORMING' AND tmp.status_ != 'COPYTO' AND tmp.IS_DELE_ = 0 AND tmp.status_ != 'APPROVELINEING'
AND tmp.status_ != 'SIGNSEQUENCEING' AND tmp.status_ != 'SIGNLINEING' AND tmp.instStatus != 'manualend'
GROUP BY
tmp.type_id_
SELECT ID_,NAME_,SUBJECT_,PROC_INST_ID_,TASK_ID_,EXEC_ID_,NODE_ID_,PROC_DEF_ID_,PROC_DEF_KEY_,PROC_DEF_NAME_,OWNER_ID_,OWNER_NAME_,ASSIGNEE_ID_,ASSIGNEE_NAME_,STATUS_,PRIORITY_,
CREATE_TIME_,DUE_TIME_,SUSPEND_STATE_,PARENT_ID_,BPMN_INST_ID_,BPMN_DEF_ID_,TYPE_ID_,SUPPORT_MOBILE_,IS_DELE_,IS_READ_,IS_REVOKE_,PROP1_,PROP2_,PROP3_,PROP4_,PROP5_,PROP6_,
TENANT_ID_,procDefName,creatorId,creator,createDate,instStatus,instIsForbidden,typeId,MIN(EXECUTE_DATE_) AS DEADLINE FROM (
SELECT
bt.*, inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,
inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
btr.EXECUTE_DATE_
FROM bpm_task bt
LEFT JOIN bpm_pro_inst inst ON bt.proc_inst_id_ = inst.id_
LEFT JOIN bpm_task_candidate c ON bt.id_ = c.TASK_ID_
LEFT JOIN bpm_reminder_history btr ON btr.TASK_ID_ = bt.ID_
WHERE
((bt.assignee_id_ = ${map.user} or (bt.OWNER_ID_ =${map.user} and bt.status_='AGENT'))
or (bt.assignee_id_='0' and c.executor_ in (${val}) and c.type_=#{key})
)
AND bt.status_ != 'TRANSFORMING'
AND bt.status_ != 'COPYTO'
AND bt.status_ != 'APPROVELINEING'
AND bt.status_ != 'SIGNSEQUENCEING'
AND bt.status_ != 'SIGNLINEING'
AND inst.IS_DELE_ = 0
AND inst.status_ != 'manualend'
AND inst.status_ != 'adminmanualend'
AND
${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
) tmp
group by ID_ order by support_mobile_ desc, ID_ desc
SELECT
ITMP.ID_,
ITMP.NAME_,
ITMP.SUBJECT_,
ITMP.PROC_INST_ID_,
ITMP.TASK_ID_,
ITMP.EXEC_ID_,
ITMP.NODE_ID_,
ITMP.PROC_DEF_ID_,
ITMP.PROC_DEF_KEY_,
ITMP.PROC_DEF_NAME_,
ITMP.OWNER_ID_,
ITMP.OWNER_NAME_,
ITMP.ASSIGNEE_ID_,
ITMP.ASSIGNEE_NAME_,
ITMP.STATUS_,
ITMP.PRIORITY_,
ITMP.CREATE_TIME_,
ITMP.DUE_TIME_,
ITMP.SUSPEND_STATE_,
ITMP.PARENT_ID_,
ITMP.BPMN_INST_ID_,
ITMP.BPMN_DEF_ID_,
ITMP.TYPE_ID_,
ITMP.SUPPORT_MOBILE_,
ITMP.IS_DELE_,
ITMP.IS_READ_,
ITMP.IS_REVOKE_,
ITMP.PROP1_,
ITMP.PROP2_,
ITMP.PROP3_,
ITMP.PROP4_,
ITMP.PROP5_,
ITMP.PROP6_,
ITMP.TENANT_ID_,
ITMP.procDefName,
ITMP.creatorId,
ITMP.creator,
ITMP.createDate,
ITMP.instStatus,
ITMP.instIsForbidden,
ITMP.typeId,
ITMP.DUE_DATE_ AS DEADLINE
FROM
(
SELECT
ROW_NUMBER ( ) OVER ( PARTITION BY tmp.ID_ ORDER BY DUE_DATE_ ASC ) rn,
tmp.*
FROM
(
SELECT
bt.*,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,
inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
btr.DUE_DATE_,
inst.CREATE_ORG_PATH_
FROM
bpm_pro_inst inst,
bpm_task bt
LEFT JOIN bpm_task_candidate c ON bt.id_ = c.TASK_ID_
LEFT JOIN bpm_task_reminder btr ON btr.TASK_ID_ = bt.ID_
WHERE
((bt.assignee_id_ = ${map.user} or (bt.OWNER_ID_ =${map.user} and bt.status_='AGENT'))
or (bt.assignee_id_ = '0' and c.executor_ in (${val}) and c.type_=#{key})
)
AND bt.proc_inst_id_ = inst.id_
AND bt.status_ != 'TRANSFORMING'
AND bt.status_ != 'COPYTO'
AND bt.status_ != 'APPROVELINEING'
AND bt.status_ != 'SIGNSEQUENCEING'
AND bt.status_ != 'SIGNLINEING'
AND inst.IS_DELE_ = 0
AND inst.status_ != 'manualend'
AND inst.status_ != 'adminmanualend'
AND
${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
) tmp
) ITMP
WHERE
rn = 1
order by support_mobile_ desc, ID_ desc
SELECT *,EXECUTE_DATE_ as DEADLINE FROM (
SELECT
bt.*, inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,
inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
btr.EXECUTE_DATE_
FROM
bpm_pro_inst inst,
bpm_task bt
LEFT JOIN bpm_task_candidate c ON bt.id_ = c.TASK_ID_
LEFT JOIN (SELECT task_id_,min(execute_date_) as execute_date_ FROM bpm_reminder_history GROUP BY task_id_) btr ON btr.task_id_ = bt.id_
WHERE
((bt.assignee_id_ = ${map.user} or (bt.OWNER_ID_ =${map.user} and bt.status_='AGENT'))
or ( bt.assignee_id_='0' and c.executor_ in (${val}) and c.type_=#{key})
)
AND bt.proc_inst_id_ = inst.id_
AND bt.status_ != 'TRANSFORMING'
AND bt.status_ != 'COPYTO'
AND inst.IS_DELE_ = 0
AND bt.status_ != 'APPROVELINEING'
AND bt.status_ != 'SIGNSEQUENCEING'
AND bt.status_ != 'SIGNLINEING'
AND inst.status_ != 'manualend'
AND inst.status_ != 'adminmanualend'
AND
${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
) tmp
order by support_mobile_ desc, ID_ desc
SELECT
count(1) as count
FROM
(
SELECT
tmp.*
FROM
bpm_task tmp
LEFT JOIN bpm_task_candidate c ON tmp.id_ = c.TASK_ID_
WHERE
((tmp.assignee_id_ = ${map.user} or (tmp.OWNER_ID_ =${map.user} and tmp.status_='AGENT'))
or ( tmp.assignee_id_ = '0' AND c.executor_ in (${val}) and c.type_=#{key})
)
AND tmp.status_ != 'TRANSFORMING'
AND tmp.status_ != 'COPYTO'
AND tmp.status_ != 'APPROVELINEING'
AND tmp.status_ != 'SIGNSEQUENCEING'
AND tmp.status_ != 'SIGNLINEING'
) bt
LEFT JOIN bpm_pro_inst inst ON bt.proc_inst_id_ = inst.id_
WHERE inst.IS_DELE_ = 0 AND inst.status_ != 'manualend' AND inst.status_ != 'adminmanualend'
and ${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
SELECT
type_id_ as "typeId",
count(1) as "count"
FROM
(
SELECT
bt.id_ AS taskId,
bt.proc_inst_id_ AS instId
FROM
bpm_task bt
LEFT JOIN bpm_task_candidate c ON bt.id_ = c.TASK_ID_
WHERE
((bt.assignee_id_ = ${map.user} or (bt.OWNER_ID_ =${map.user} and bt.status_='AGENT'))
or (c.executor_ in (${val}) and c.type_=#{key})
)
AND bt.status_ != 'TRANSFORMING'
AND bt.status_ != 'COPYTO'
AND bt.status_ != 'APPROVELINEING'
AND bt.status_ != 'SIGNSEQUENCEING'
AND bt.status_ != 'SIGNLINEING'
AND bt.SUPPORT_MOBILE_ = #{map.isMobile}
) tmp
LEFT JOIN bpm_pro_inst inst ON tmp.instId = inst.id_
WHERE
inst.IS_DELE_ = 0 AND inst.status_ != 'manualend' AND inst.status_ != 'adminmanualend'
GROUP BY
inst.type_id_
SELECT
count(1) as count
FROM
(
SELECT
bt.id_ AS taskId,
bt.proc_inst_id_ AS instId
FROM
bpm_task bt
LEFT JOIN bpm_task_candidate c ON bt.id_ = c.TASK_ID_
WHERE
((bt.assignee_id_ = ${map.user} or (bt.OWNER_ID_ =${map.user} and bt.status_='AGENT'))
or ( bt.assignee_id_ = '0' AND c.executor_ in (${val}) and c.type_=#{key})
)
AND bt.status_ != 'TRANSFORMING'
AND bt.status_ != 'COPYTO'
AND bt.status_ != 'APPROVELINEING'
AND bt.status_ != 'SIGNSEQUENCEING'
AND bt.status_ != 'SIGNLINEING'
AND bt.SUPPORT_MOBILE_ = 1
) tmp
LEFT JOIN bpm_pro_inst inst ON tmp.instId = inst.id_
WHERE
inst.IS_DELE_ = 0 AND inst.status_ != 'manualend' AND inst.status_ != 'adminmanualend'
select * from BPM_TASK t where t.proc_inst_id_=#{instId} order by create_time_ desc
select * from BPM_TASK t where t.exec_id_=#{instId} and node_id_ = #{nodeId} order by create_time_ desc
select * from (
select * from BPM_TASK t where t.assignee_id_=#{assigneeId} and t.proc_inst_id_=#{instId}
union all
select t.* from BPM_TASK t ,BPM_TASK_CANDIDATE tc where t.id_=tc.task_id_
and t.assignee_id_='0' and tc.executor_=#{assigneeId} and tc.type_='user'
and t.proc_inst_id_=#{instId}
) tmp
order by create_time_ desc
DELETE FROM bpm_task
WHERE
id_=#{id}
SELECT * FROM bpm_task
WHERE
task_id_=#{taskId,jdbcType=VARCHAR}
DELETE FROM bpm_task
WHERE
task_id_=#{taskId,jdbcType=VARCHAR}
DELETE FROM bpm_task WHERE id_ in (SELECT bmt.id_ from (SELECT id_ from bpm_task where parent_id_ = #{parentId,jdbcType=VARCHAR}) bmt)
select * from (
select t.* from bpm_task t where t.proc_inst_id_=#{bpmnInstId} and t.assignee_id_=#{userId}
UNION ALL select t.* from bpm_task t left join bpm_task_candidate c on t.task_id_=c.task_id_ where t.assignee_id_='0' and c.PROC_INST_ID_=#{bpmnInstId} and c.executor_=#{userId} and c.type_='user'
UNION ALL select t.* from bpm_task t left join bpm_task_candidate c on t.task_id_=c.task_id_ where t.assignee_id_='0' and c.PROC_INST_ID_=#{bpmnInstId} and c.executor_=#{group.groupId} and c.type_=#{group.groupType}
) tmp
update bpm_task set assignee_id_=#{assigneeId} where id_=#{id}
update bpm_task set assignee_id_=#{assigneeId} ,owner_id_=#{ownerId} where id_=#{id}
DELETE FROM bpm_task where proc_inst_id_ in
#{instId}
SELECT * FROM bpm_task
WHERE
parent_id_=#{parentId,jdbcType=VARCHAR}
SELECT id_ FROM bpm_task
WHERE
parent_id_=#{parentId,jdbcType=VARCHAR}
select * from BPM_TASK t where t.assignee_id_=#{userId} and t.status_='TRANSFORMING' order by create_time_ desc
select * from bpm_task a where exists (select 1 from bpm_task_reminder b where a.proc_def_id_=b.proc_def_id_ and a.node_id_=b.node_id_)
SELECT * FROM bpm_task
WHERE
proc_inst_id_ in
#{instId}
SELECT a.*,b.create_time_ as transDate FROM bpm_task a,bpm_task_trans b
WHERE a.id_=b.task_id_ and a.status_='TRANSFORMING' and assignee_id_=#{map.userId}
and a.proc_def_name_ like #{map.defName}
and a.subject_ like #{map.subject}
and b.create_time_ >=#{map.transTimeStart}
and b.create_time_ <=#{map.transTimeEnd_DG}
ORDER BY ${map.orderBySql}
ORDER BY b.create_time_ DESC
UPDATE bpm_task SET priority_= #{priority,jdbcType=NUMERIC} WHERE id_= #{taskId}
SELECT * from bpm_task t ,act_ru_execution b
where t.EXEC_ID_ =b.ID_ and b.PARENT_ID_=#{executeId} and node_id_=#{nodeId};
select * from BPM_TASK where tenant_id_=#{tenantId}
UPDATE bpm_task SET owner_id_= #{ownerId,jdbcType=VARCHAR},owner_name_= #{ownerName,jdbcType=VARCHAR} WHERE owner_id_= #{userId,jdbcType=VARCHAR}
AND proc_inst_id_ in
#{instId}
UPDATE bpm_task SET assignee_id_= #{assigneeId,jdbcType=VARCHAR},assignee_name_= #{assigneeName,jdbcType=VARCHAR} WHERE assignee_id_= #{userId,jdbcType=VARCHAR}
AND proc_inst_id_ in
#{instId}
UPDATE bpm_task SET assignee_id_= #{assigneeId,jdbcType=VARCHAR},assignee_name_= #{assigneeName,jdbcType=VARCHAR},status_= 'NORMAL' WHERE task_id_= #{taskId,jdbcType=VARCHAR}
select type_id_ as "typeId",count(1) as "count" from
(
select * from (
SELECT task.id_,task.IS_DELE_,task.name_,task.subject_,task.proc_inst_id_,task.task_id_,task.exec_id_,task.node_id_,task.proc_def_id_,task.proc_def_key_,
task.proc_def_name_,task.owner_id_,task.assignee_id_,task.status_,task.priority_,task.create_time_,task.due_time_,task.suspend_state_,task.parent_id_,
task.bpmn_inst_id_,task.bpmn_def_id_,task.type_id_,task.support_mobile_,task.TENANT_ID_,task.OWNER_NAME_,task.ASSIGNEE_NAME_,
task.IS_READ_,task.IS_REVOKE_,task.prop1_,task.prop2_,task.prop3_,task.prop4_,task.prop5_,to_char(task.prop6_) as prop6_,task.utaskId,task.leaderIds,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
due.due_time_ dueTaskTime,
due.EXPIRATION_DATE_ dueExpDate,
due.date_type_ dueDateType,
due.status_ dueStatus,
inst.URGENT_STATE_ urgentStateValue
FROM (
SELECT bpm_task.id_,bpm_task.IS_DELE_,bpm_task.name_,bpm_task.subject_,bpm_task.proc_inst_id_,bpm_task.task_id_,bpm_task.exec_id_,bpm_task.node_id_,bpm_task.proc_def_id_,bpm_task.proc_def_key_,
bpm_task.proc_def_name_,bpm_task.owner_id_,bpm_task.assignee_id_,bpm_task.status_,bpm_task.priority_,bpm_task.create_time_,bpm_task.due_time_,bpm_task.suspend_state_,bpm_task.parent_id_,
bpm_task.bpmn_inst_id_,bpm_task.bpmn_def_id_,bpm_task.type_id_,bpm_task.support_mobile_,bpm_task.TENANT_ID_,bpm_task.OWNER_NAME_,bpm_task.ASSIGNEE_NAME_,
bpm_task.IS_READ_,bpm_task.IS_REVOKE_,bpm_task.prop1_,bpm_task.prop2_,bpm_task.prop3_,bpm_task.prop4_,bpm_task.prop5_,to_char(bpm_task.prop6_) as prop6_,ubpt.utaskId,ubpt.leaderIds
from bpm_task
INNER JOIN (
SELECT ut.ID_ utaskId, wm_concat(ut.assignee_id_) as leaderIds from
(select ID_,'' as assignee_id_ from bpm_task where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task task where (task.assignee_id_=#{key}
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as ID_,'${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task.ID_
UNION
SELECT bpm_task_notice.id_,bpm_task_notice.IS_DELE_,bpm_task_notice.name_,bpm_task_notice.subject_,bpm_task_notice.proc_inst_id_,bpm_task_notice.task_id_,bpm_task_notice.exec_id_,bpm_task_notice.node_id_,bpm_task_notice.proc_def_id_,bpm_task_notice.proc_def_key_,
bpm_task_notice.proc_def_name_,bpm_task_notice.owner_id_,bpm_task_notice.assignee_id_,bpm_task_notice.status_,bpm_task_notice.priority_,bpm_task_notice.create_time_,bpm_task_notice.due_time_,bpm_task_notice.suspend_state_,bpm_task_notice.parent_id_,
bpm_task_notice.bpmn_inst_id_,bpm_task_notice.bpmn_def_id_,bpm_task_notice.type_id_,bpm_task_notice.support_mobile_,bpm_task_notice.TENANT_ID_,bpm_task_notice.OWNER_NAME_,bpm_task_notice.ASSIGNEE_NAME_,
bpm_task_notice.IS_READ_,bpm_task_notice.IS_REVOKE_,bpm_task_notice.prop1_,bpm_task_notice.prop2_,bpm_task_notice.prop3_,bpm_task_notice.prop4_,bpm_task_notice.prop5_,to_char(bpm_task_notice.prop6_) as prop6_,ubpt.utaskId,ubpt.leaderIds
from bpm_task_notice
INNER JOIN (
SELECT ut.ID_ utaskId, wm_concat(ut.assignee_id_) as leaderIds from
(select ID_,'' as assignee_id_ from bpm_task_notice where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task_notice task where (task.assignee_id_=#{key}
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as ID_,'${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task_notice bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task_notice.ID_
) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
LEFT JOIN (select * from bpm_task_due_time where is_new_ =1 ) due ON task.TASK_ID_ = due.TASK_ID_
) tmp
where
tmp.status_ !='TRANSFORMING' and tmp.status_!='COPYTO' and tmp.IS_DELE_ = 0 and tmp.status_ !='APPROVELINEING' and tmp.status_!='SIGNSEQUENCEING' and tmp.status_!='SIGNLINEING' AND tmp.instStatus != 'manualend' AND tmp.instStatus != 'adminmanualend'
group by tmp.id_,tmp.IS_DELE_,tmp.name_,tmp.subject_,tmp.proc_inst_id_,tmp.task_id_,tmp.exec_id_,tmp.node_id_,tmp.proc_def_id_,tmp.proc_def_key_,
tmp.proc_def_name_,tmp.owner_id_,tmp.assignee_id_,tmp.status_,tmp.priority_,tmp.create_time_,tmp.due_time_,tmp.suspend_state_,tmp.parent_id_,
tmp.bpmn_inst_id_,tmp.bpmn_def_id_,tmp.type_id_,tmp.support_mobile_,tmp.TENANT_ID_,tmp.OWNER_NAME_,tmp.ASSIGNEE_NAME_,tmp.instIsForbidden,tmp.IS_READ_,tmp.IS_REVOKE_,tmp.prop1_,tmp.prop2_,tmp.prop3_,tmp.prop4_,tmp.prop5_,tmp.prop6_,tmp.utaskId,
tmp.leaderIds,
procDefName,creatorId,creator,createDate, instStatus,typeId,dueTaskTime,dueExpDate,dueDateType,dueStatus,urgentStateValue
order by CREATE_TIME_ desc
) temp group by type_id_
select type_id_ as "typeId",count(1) as "count" from
(
select * from (
SELECT task.id_,task.IS_DELE_,task.name_,task.subject_,task.proc_inst_id_,task.task_id_,task.exec_id_,task.node_id_,task.proc_def_id_,task.proc_def_key_,
task.proc_def_name_,task.owner_id_,task.assignee_id_,task.status_,task.priority_,task.create_time_,task.due_time_,task.suspend_state_,task.parent_id_,
task.bpmn_inst_id_,task.bpmn_def_id_,task.type_id_,task.support_mobile_,task.TENANT_ID_,task.OWNER_NAME_,task.ASSIGNEE_NAME_,
task.IS_READ_,task.IS_REVOKE_,task.prop1_,task.prop2_,task.prop3_,task.prop4_,task.prop5_,task.prop6_,task.utaskId,task.leaderIds,
inst.proc_def_name_ procDefName,
inst.create_by_ creatorId,inst.CREATOR_ creator,
inst.create_time_ createDate,
inst.status_ instStatus,
inst.is_forbidden_ instIsForbidden,
inst.type_id_ typeId,
due.due_time_ dueTaskTime,
due.EXPIRATION_DATE_ dueExpDate,
due.date_type_ dueDateType,
due.status_ dueStatus,
inst.URGENT_STATE_ urgentStateValue
FROM (
SELECT bpm_task.id_,bpm_task.IS_DELE_,bpm_task.name_,bpm_task.subject_,bpm_task.proc_inst_id_,bpm_task.task_id_,bpm_task.exec_id_,bpm_task.node_id_,bpm_task.proc_def_id_,bpm_task.proc_def_key_,
bpm_task.proc_def_name_,bpm_task.owner_id_,bpm_task.assignee_id_,bpm_task.status_,bpm_task.priority_,bpm_task.create_time_,bpm_task.due_time_,bpm_task.suspend_state_,bpm_task.parent_id_,
bpm_task.bpmn_inst_id_,bpm_task.bpmn_def_id_,bpm_task.type_id_,bpm_task.support_mobile_,bpm_task.TENANT_ID_,bpm_task.OWNER_NAME_,bpm_task.ASSIGNEE_NAME_,
bpm_task.IS_READ_,bpm_task.IS_REVOKE_,bpm_task.prop1_,bpm_task.prop2_,bpm_task.prop3_,bpm_task.prop4_,bpm_task.prop5_,bpm_task.prop6_,ubpt.utaskId,ubpt.leaderIds
from bpm_task
INNER JOIN (
SELECT ut.ID_ utaskId, group_concat(ut.assignee_id_) as leaderIds from
(select ID_,'' as assignee_id_ from bpm_task where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task task where (task.assignee_id_=${key}
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as ID_,'${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task.ID_
UNION
SELECT bpm_task_notice.id_,bpm_task_notice.IS_DELE_,bpm_task_notice.name_,bpm_task_notice.subject_,bpm_task_notice.proc_inst_id_,bpm_task_notice.task_id_,bpm_task_notice.exec_id_,bpm_task_notice.node_id_,bpm_task_notice.proc_def_id_,bpm_task_notice.proc_def_key_,
bpm_task_notice.proc_def_name_,bpm_task_notice.owner_id_,bpm_task_notice.assignee_id_,bpm_task_notice.status_,bpm_task_notice.priority_,bpm_task_notice.create_time_,bpm_task_notice.due_time_,bpm_task_notice.suspend_state_,bpm_task_notice.parent_id_,
bpm_task_notice.bpmn_inst_id_,bpm_task_notice.bpmn_def_id_,bpm_task_notice.type_id_,bpm_task_notice.support_mobile_,bpm_task_notice.TENANT_ID_,bpm_task_notice.OWNER_NAME_,bpm_task_notice.ASSIGNEE_NAME_,
bpm_task_notice.IS_READ_,bpm_task_notice.IS_REVOKE_,bpm_task_notice.prop1_,bpm_task_notice.prop2_,bpm_task_notice.prop3_,bpm_task_notice.prop4_,bpm_task_notice.prop5_,bpm_task_notice.prop6_,ubpt.utaskId,ubpt.leaderIds
from bpm_task_notice
INNER JOIN (
SELECT ut.ID_ utaskId, group_concat(ut.assignee_id_) as leaderIds from
(select ID_,'' as assignee_id_ from bpm_task_notice where 1=0
UNION ALL
select ID_,'${key}' as assignee_id_ from bpm_task_notice task where (task.assignee_id_=${key}
or (task.OWNER_ID_='${key}' and task.STATUS_='AGENT')
)
and task.PROC_DEF_KEY_ in (${item.defKeys})
UNION ALL select c.TASK_ID_ as ID_,'${key}' as assignee_id_ from bpm_task_candidate c
INNER JOIN bpm_task_notice bt on bt.TASK_ID_ = c.TASK_ID_ and bt.STATUS_ !='DELIVERTO'
where c.executor_ in (${value}) and c.type_='${type}'
and bt.PROC_DEF_KEY_ in (${item.defKeys})
) ut group by ut.ID_
) ubpt on ubpt.uTaskId = bpm_task_notice.ID_
) task
LEFT JOIN BPM_PRO_INST inst ON task.proc_inst_id_ = inst.id_
LEFT JOIN (select * from bpm_task_due_time where is_new_ =1 ) due ON task.TASK_ID_ = due.TASK_ID_) tmp
where
tmp.status_ !='TRANSFORMING' and tmp.status_!='COPYTO' and tmp.IS_DELE_ = 0 and tmp.status_ !='APPROVELINEING' and tmp.status_!='SIGNSEQUENCEING' and tmp.status_!='SIGNLINEING' AND tmp.instStatus != 'manualend' AND tmp.instStatus != 'adminmanualend'
group by tmp.id_
) temp
group by type_id_