<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.artfess.bpm.persistence.dao.BpmProcessInstanceDao">
	<resultMap id="BpmProcessInstance" type="com.artfess.bpm.persistence.model.DefaultBpmProcessInstance">
		<id property="id" column="id_" jdbcType="VARCHAR"/>
		<result property="subject" column="subject_" jdbcType="VARCHAR"/>
		<result property="procDefId" column="proc_def_id_" jdbcType="VARCHAR"/>
		<result property="bpmnDefId" column="bpmn_def_id_" jdbcType="VARCHAR"/>
		<result property="procDefKey" column="proc_def_key_" jdbcType="VARCHAR"/>
		<result property="procDefName" column="proc_def_name_" jdbcType="VARCHAR"/>
		<result property="bizKey" column="biz_key_" jdbcType="VARCHAR"/>
		<result property="sysCode" column="sys_code_" jdbcType="VARCHAR"/>
		<result property="formKey" column="form_key_" jdbcType="VARCHAR"/>
		<result property="status" column="status_" jdbcType="VARCHAR"/>
		<result property="endTime" column="end_time_" jdbcType="TIMESTAMP"/>
		<result property="duration" column="duration_" jdbcType="NUMERIC"/>
		<result property="typeId" column="type_id_" jdbcType="VARCHAR"/>
		<result property="resultType" column="result_type_" jdbcType="VARCHAR"/>
		<result property="bpmnInstId" column="bpmn_inst_id_" jdbcType="VARCHAR"/>
		<result property="createBy" column="create_by_" jdbcType="VARCHAR"/>
		<result property="creator" column="creator_" jdbcType="VARCHAR"/>
		<result property="createTime" column="create_time_" jdbcType="TIMESTAMP"/>
		<result property="createOrgId" column="create_org_id_" jdbcType="VARCHAR"/>
		<result property="updateBy" column="update_by_" jdbcType="VARCHAR"/>
		<result property="updateTime" column="update_time_" jdbcType="TIMESTAMP"/>
		<result property="isFormmal" column="is_formmal_" jdbcType="VARCHAR"/>
		<result property="parentInstId" column="parent_inst_id_" jdbcType="VARCHAR"/>
		<result property="isForbidden" column="IS_FORBIDDEN_" jdbcType="NUMERIC"/>
		<result property="dataMode" column="DATA_MODE_" jdbcType="VARCHAR"/>
		<result property="supportMobile" column="SUPPORT_MOBILE_" jdbcType="NUMERIC"/>
		<result property="superNodeId" column="super_node_id_" jdbcType="VARCHAR"/>
		<result property="taskName" column="task_name_" jdbcType="VARCHAR"/>
        <result property="isDele" column="IS_DELE_" jdbcType="NUMERIC"/>
        <result property="urgentState" column="URGENT_STATE_" jdbcType="VARCHAR"/>
	</resultMap>

	<sql id="columns">
	    wfInst.id_ "id",wfInst.subject_ "subject",wfInst.proc_def_id_ "procDefId",wfInst.bpmn_def_id_ "bpmnDefId",
		wfInst.proc_def_key_ "procDefKey",wfInst.proc_def_name_ "procDefName",wfInst.biz_key_  "bizKey",wfInst.sys_code_  "sysCode",wfInst.form_key_ "formKey",
		wfInst.status_ "status",wfInst.end_time_ "endTime",wfInst.duration_ "duration",wfInst.result_type_ "resultType",
		wfInst.bpmn_inst_id_ "bpmnInstId",wfInst.create_by_ "createBy",wfInst.creator_ "creator",wfInst.create_time_ "createTime",
		wfInst.create_org_id_ "createOrgId",wfInst.update_by_ "updateBy",wfInst.update_time_ "updateTime",wfInst.is_formmal_ "isFormmal",
		wfInst.parent_inst_id_ "parentInstId",wfInst.IS_FORBIDDEN_ "isForbidden",wfInst.DATA_MODE_ "dataMode",wfInst.SUPPORT_MOBILE_ "supportMobile",
		wfInst.super_node_id_ "superNodeId",wfInst.IS_DELE_ "isDele",wfInst.URGENT_STATE_ "urgentState"
	</sql>

	<insert id="create" parameterType="com.artfess.bpm.persistence.model.DefaultBpmProcessInstance">
		INSERT INTO bpm_pro_inst
		(id_,subject_,proc_def_id_,bpmn_def_id_,
		proc_def_key_,proc_def_name_,biz_key_,sys_code_,form_key_,
		status_,end_time_,duration_,type_id_,
		result_type_,bpmn_inst_id_,create_by_,creator_,create_time_,
		create_org_id_,update_by_,update_time_,
		is_formmal_,parent_inst_id_,IS_FORBIDDEN_,DATA_MODE_,SUPPORT_MOBILE_,super_node_id_,URGENT_STATE_)
		VALUES 
		(#{id,jdbcType=VARCHAR}, #{subject,jdbcType=VARCHAR}, #{procDefId,jdbcType=VARCHAR}, #{bpmnDefId,jdbcType=VARCHAR},
		 #{procDefKey,jdbcType=VARCHAR}, #{procDefName,jdbcType=VARCHAR}, #{bizKey,jdbcType=VARCHAR}, #{sysCode,jdbcType=VARCHAR}, #{formKey,jdbcType=VARCHAR}, 
		 #{status,jdbcType=VARCHAR}, #{endTime,jdbcType=TIMESTAMP}, #{duration,jdbcType=NUMERIC}, #{typeId,jdbcType=VARCHAR}, 
		 #{resultType,jdbcType=VARCHAR}, #{bpmnInstId,jdbcType=VARCHAR}, #{createBy,jdbcType=VARCHAR},#{creator,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP},
		  #{createOrgId,jdbcType=VARCHAR}, #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}, 
		  #{isFormmal,jdbcType=VARCHAR}, #{parentInstId,jdbcType=VARCHAR}, #{isForbidden,jdbcType=NUMERIC},
		 #{dataMode,jdbcType=VARCHAR} ,#{supportMobile,jdbcType=NUMERIC}, #{superNodeId,jdbcType=VARCHAR}, #{urgentState,jdbcType=VARCHAR})
	</insert>

	<insert id="createHistory" parameterType="com.artfess.bpm.persistence.model.DefaultBpmProcessInstance">
		INSERT INTO bpm_pro_inst_hi
		(id_,subject_,proc_def_id_,bpmn_def_id_,
		proc_def_key_,proc_def_name_,biz_key_,sys_code_,form_key_,
		status_,end_time_,duration_,type_id_,
		result_type_,bpmn_inst_id_,create_by_,creator_,create_time_,
		create_org_id_,update_by_,update_time_,
		is_formmal_,parent_inst_id_,IS_FORBIDDEN_,DATA_MODE_,SUPPORT_MOBILE_,super_node_id_)
		VALUES 
		(#{id,jdbcType=VARCHAR}, #{subject,jdbcType=VARCHAR}, #{procDefId,jdbcType=VARCHAR}, #{bpmnDefId,jdbcType=VARCHAR},
		 #{procDefKey,jdbcType=VARCHAR}, #{procDefName,jdbcType=VARCHAR}, #{bizKey,jdbcType=VARCHAR}, #{sysCode,jdbcType=VARCHAR}, #{formKey,jdbcType=VARCHAR}, 
		 #{status,jdbcType=VARCHAR}, #{endTime,jdbcType=TIMESTAMP}, #{duration,jdbcType=NUMERIC}, #{typeId,jdbcType=VARCHAR}, 
		 #{resultType,jdbcType=VARCHAR}, #{bpmnInstId,jdbcType=VARCHAR}, #{createBy,jdbcType=VARCHAR},#{creator,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP},
		  #{createOrgId,jdbcType=VARCHAR}, #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}, 
		  #{isFormmal,jdbcType=VARCHAR}, #{parentInstId,jdbcType=VARCHAR}, #{isForbidden,jdbcType=NUMERIC},
		 #{dataMode,jdbcType=VARCHAR} ,#{supportMobile,jdbcType=NUMERIC}, #{superNodeId,jdbcType=VARCHAR})
	</insert>

	<select id="get"   parameterType="java.lang.String" resultMap="BpmProcessInstance">
		SELECT * FROM bpm_pro_inst 
		WHERE 
		id_=#{id}
	</select>

	<select id="getBpmnInstId"   parameterType="java.lang.String" resultMap="BpmProcessInstance">
		SELECT * FROM bpm_pro_inst 
		WHERE 
		bpmn_inst_id_=#{bpmnInstId}
	</select>

	<select id="query" parameterType="java.util.Map" resultMap="BpmProcessInstance">
		SELECT * FROM bpm_pro_inst
		<where>

			<if test="whereSql!=null">
				${whereSql}
			</if>
			<!-- 按用户授权过滤用户的流程 ：isAdmin为空时不需要按权限查询，否则按流程的分管授权情况查询-->
			<if test="isAdmin==0">
			 	<choose>
			        <when test="userRightMap!=null">
			           AND ID_ in(
							SELECT DISTINCT ID_ from bpm_pro_inst where ID_ is NULL
							   <foreach collection="userRightMap" index="key" item="value" open="" separator=" " close="">
									union
									select ID_ from bpm_pro_inst WHERE PROC_DEF_KEY_=#{key}
									<if test="value!=null">
										AND CREATE_ORG_ID_ IN(${value})
									</if>
					             </foreach>
						)
			        </when>
			        <otherwise>
			            AND 1 = 0
			        </otherwise>
		        </choose>
			</if>

		</where>

		<if test="orderBySql!=null">
			ORDER BY ${orderBySql}
		</if>
		<if test="orderBySql==null">
			ORDER BY create_time_ DESC
		</if>
	</select>

	<select id="getListByRightMap" parameterType="java.util.Map" resultMap="BpmProcessInstance">
		SELECT DISTINCT * from bpm_pro_inst where ID_ is NULL
		   <foreach collection="userRightMap" index="key" item="value" open="" separator=" " close="">
				union
				select * from bpm_pro_inst WHERE PROC_DEF_KEY_=#{key}
				<if test="value!=null">
					AND CREATE_ORG_ID_ IN(${value})
				</if>
	      	</foreach>
	</select>

	<select id="getStringListByRightMap" parameterType="java.util.Map" resultType="java.lang.String">
		SELECT DISTINCT id_ from bpm_pro_inst where ID_ is NULL
		<foreach collection="userRightMap" index="key" item="value" open="" separator=" " close="">
			union
			select id_ from bpm_pro_inst WHERE PROC_DEF_KEY_=#{key}
			<if test="value!=null">
				AND CREATE_ORG_ID_ IN(${value})
			</if>
		</foreach>
	</select>
	
	<select id="getIsInRightMap" parameterType="java.util.Map" resultType="java.lang.Integer">
		SELECT count(1) from bpm_pro_inst where ID_ = #{procInstId} AND CREATE_ORG_ID_ IN(${orgIds})
	</select>

	<delete id="remove" parameterType="java.lang.String">
		DELETE FROM bpm_pro_inst 
		WHERE
		id_=#{id}
	</delete>

	<!-- HISTORY -->
	<select id="getNowAndHistory"   parameterType="java.lang.String" resultMap="BpmProcessInstance">
		SELECT TEMP.ID_,DEF.TYPE_ID_ "TYPE_ID_", DEF.DEF_KEY_ PROC_DEF_KEY_ FROM (
			SELECT ID_,PROC_DEF_ID_ FROM BPM_PRO_INST_HI
			UNION ALL
			SELECT ID_,PROC_DEF_ID_ FROM BPM_PRO_INST) TEMP
			LEFT JOIN BPM_DEFINITION DEF ON DEF.DEF_ID_ = TEMP.PROC_DEF_ID_
			WHERE ID_ = #{instId}
	</select>

	<select id="getHistoryByBpmnInstId"   parameterType="java.lang.String" resultMap="BpmProcessInstance">
		SELECT * FROM bpm_pro_inst_hi 
		WHERE 
		BPMN_INST_ID_=#{id}
	</select>


	<update id="updateHistory" parameterType="com.artfess.bpm.persistence.model.DefaultBpmProcessInstance">
		UPDATE bpm_pro_inst_hi SET
		subject_=#{subject,jdbcType=VARCHAR},
		proc_def_id_=#{procDefId,jdbcType=VARCHAR},
		bpmn_def_id_=#{bpmnDefId,jdbcType=VARCHAR},
		proc_def_key_=#{procDefKey,jdbcType=VARCHAR},
		proc_def_name_=#{procDefName,jdbcType=VARCHAR},
		biz_key_=#{bizKey,jdbcType=VARCHAR},
		sys_code_=#{sysCode,jdbcType=VARCHAR},
		form_key_=#{formKey,jdbcType=VARCHAR},
		status_=#{status,jdbcType=VARCHAR},
		end_time_=#{endTime,jdbcType=TIMESTAMP},
		duration_=#{duration,jdbcType=NUMERIC},
		type_id_=#{typeId,jdbcType=VARCHAR},
		result_type_=#{resultType,jdbcType=VARCHAR},
		bpmn_inst_id_=#{bpmnInstId,jdbcType=VARCHAR},
		create_by_=#{createBy,jdbcType=VARCHAR},
		creator_=#{creator,jdbcType=VARCHAR},
		create_time_=#{createTime,jdbcType=TIMESTAMP},
		create_org_id_=#{createOrgId,jdbcType=VARCHAR},
		update_by_=#{updateBy,jdbcType=VARCHAR},
		update_time_=#{updateTime,jdbcType=TIMESTAMP},
		is_formmal_=#{isFormmal,jdbcType=VARCHAR},
		parent_inst_id_=#{parentInstId,jdbcType=VARCHAR},
		IS_FORBIDDEN_=#{isForbidden,jdbcType=NUMERIC},
		DATA_MODE_=#{dataMode,jdbcType=VARCHAR},
		SUPPORT_MOBILE_=#{supportMobile,jdbcType=NUMERIC},
		super_node_id_=#{superNodeId,jdbcType=VARCHAR},
		WHERE
		id_=#{id}
	</update>

	<select id="getByUserId"  parameterType="java.lang.String" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where create_by_=#{htqf.userId}
			<if test="htqf.subject!=null">
				and subject_ like #{htqf.subject}
			</if>
			<if test="htqf.procDefName!=null">
				and proc_def_name_ like #{htqf.procDefName}
			</if>
			<if test="htqf.creator!=null">
				and creator_ like #{htqf.creator}
			</if>
			<if test="htqf.status!=null">
				and status_ like #{htqf.status}
			</if>
			<if test="htqf.typeId!=null">
				and type_id_ = #{htqf.typeId}
			</if>
		 order by create_time_ desc
	</select>

	<update id="updateStatusByInstanceId" >
		update bpm_pro_inst set status_=#{status} where id_=#{processInstanceId}
	</update>

	<update id="updateStatusByBpmnInstanceId" >
		update bpm_pro_inst set status_=#{status} where bpmn_inst_id_=#{bpmnInstId}
	</update>

	<select id="getByUserIdGroupList" resultMap="BpmProcessInstance">
		select * from (
			select pro.* from bpm_task t right join bpm_pro_inst pro  on pro.bpmn_inst_id_=t.bpmn_inst_id_ where t.assignee_id_=#{userId}
			union select pro.* from bpm_task t right join bpm_pro_inst pro on pro.bpmn_inst_id_=t.bpmn_inst_id_ right join bpm_task_candidate tc on t.proc_inst_id_=tc.act_inst_id_ where t.assignee_id_='0' and tc.executor_=#{userId} and tc.type_='user'
			<foreach collection="groupList" index="index" item="group" open="" separator=" " close="">
				union select pro.* from bpm_task t right join bpm_pro_inst pro on pro.bpmn_inst_id_=t.bpmn_inst_id_ right join bpm_task_candidate tc on t.proc_inst_id_=tc.act_inst_id_ where t.assignee_id_='0' and tc.executor_=#{group.groupId} and tc.type_=#{group.dimKey}
			</foreach>
		 ) tmp
		order by create_time_ desc
	</select>

	<select id="getByAttendUserId" resultMap="BpmProcessInstance">
		select distinct ist.* from bpm_pro_inst ist
			LEFT JOIN bpm_check_opinion cp  on ist.id_ = cp.proc_inst_id_
	    	where cp.auditor_ =#{userId}
			<if test="subject!=null">
				and ist.subject_ like #{subject}
			</if>
			<if test="procDefName!=null">
				and ist.proc_def_name_ like #{procDefName}
			</if>
			<if test="creator!=null">
				and ist.creator_ like #{creator}
			</if>
			<if test="status!=null">
				and ist.status_ like #{status}
			</if>
			<if test="typeId!=null">
				and ist.type_id_ = #{typeId}
			</if>
		<if test="orderBySql==null">
			ORDER BY ist.create_time_ desc
		</if>

		<if test="orderBySql!=null">
			ORDER BY ${orderBySql}
		</if>

	</select>

	<select id="getBpmnByParentIdAndSuperNodeId" parameterType="java.util.Map" resultMap="BpmProcessInstance">
		select *  FROM bpm_pro_inst 
		WHERE parent_inst_id_= #{parentInstId} and super_node_id_= #{superNodeId} 
	</select>

    <select id="getHiBpmnByParentIdAndSuperNodeId" parameterType="java.util.Map" resultMap="BpmProcessInstance">
		select *  FROM bpm_pro_inst_hi 
		WHERE parent_inst_id_= #{parentInstId} and super_node_id_= #{superNodeId} 
	</select>


	<select id="getBpmnByInstList" resultType="java.lang.String">
		select bpmn_inst_id_  FROM bpm_pro_inst
		WHERE id_ in
		<foreach collection="list" index="index" item="instId" open="(" separator="," close=")">
            #{instId}
        </foreach>
      	order by create_time_ desc
	</select>

	<select id="getListByBpmnDefKey" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where proc_def_key_=#{defKey}
	</select>

	<select id="getMyRequestCount" parameterType="java.util.Map" resultType="java.util.Map">
		select count(*) as "count",type_id_ as "typeId" from bpm_pro_inst where IS_DELE_!=1 and create_by_=#{userId}
		group by type_id_
		order by create_time_ desc
	</select>

	<select id="getMyRequestCount" databaseId="oracle" parameterType="java.util.Map" resultType="java.util.Map">
		SELECT
		count( * ) AS "count",
		type_id_ AS "typeId"
		FROM
			( SELECT TYPE_ID_, CREATE_TIME_ FROM bpm_pro_inst WHERE IS_DELE_ != 1  and create_by_=#{userId} ORDER BY create_time_ DESC ) bpi
		GROUP BY
			bpi.type_id_
	</select>

	<select id="getMyRequestCount" databaseId="h2" parameterType="java.util.Map" resultType="java.util.Map">
		SELECT
				count( * ) AS "count",
				type_id_ AS "typeId"
		FROM
				( SELECT TYPE_ID_, CREATE_TIME_ FROM bpm_pro_inst WHERE IS_DELE_ != 1  and create_by_=#{userId} ORDER BY create_time_ DESC ) bpi
		GROUP BY
				bpi.type_id_
	</select>

	<select id="getMyRequestCount" databaseId="pg" parameterType="java.util.Map" resultType="java.util.Map">
		select count(*) as count,type_id_ as typeId from bpm_pro_inst where IS_DELE_!=1 and create_by_=#{userId}
		group by type_id_,create_time_
		order by create_time_ desc
	</select>

	<select id="getMyRequestCountByUserId" parameterType="java.util.Map" resultType="java.lang.Long">
		select count(*) from bpm_pro_inst where IS_DELE_!=1 and create_by_=#{userId}
	</select>

	<!-- 获取流程字段信息 -->
	<select id="getFlowFieldList" resultType="java.util.Map">
		select
		t1.ID_ as proc_inst_id_,
		t1.subject_,
		t1.proc_def_name_,
		t1.status_,
		t1.create_time_,
		t1.end_time_,
		t1.is_forbidden_,
		t1.creator_,
		t1.is_dele_,
		(case WHEN t2.BUSINESSKEY_STR_ is null then t2.BUSINESSKEY_ else t2.BUSINESSKEY_STR_ end) as id_
		from bpm_pro_inst t1 inner join bpm_bus_link t2 on t1.id_=t2.proc_inst_id_
		${ew.customSqlSegment}
	</select>

	<select id="getFlowFieldList" databaseId="oracle" resultType="java.util.Map">
		SELECT
			t1.ID_ AS "proc_inst_id_",
			t1.subject_ AS "subject_",
			t1.proc_def_name_ AS "proc_def_name_",
			t1.status_ AS "status_",
			t1.create_time_ AS "create_time_",
			t1.end_time_ AS "end_time_",
			t1.is_forbidden_ AS "is_forbidden_",
			t1.creator_ AS "creator_",
			t1.is_dele_ AS "is_dele_",
		CASE WHEN t2.BUSINESSKEY_STR_ IS NULL THEN TO_CHAR(t2.BUSINESSKEY_) ELSE t2.BUSINESSKEY_STR_ END AS "id_"
		FROM
			bpm_pro_inst t1
		INNER JOIN bpm_bus_link t2 ON t1.id_ = t2.proc_inst_id_
		${ew.customSqlSegment}
	</select>

	<select id="getFlowFieldList" databaseId="pg" resultType="java.util.Map">
		SELECT
			t1.ID_ AS proc_inst_id_,
			t1.subject_,
			t1.proc_def_name_,
			t1.status_,
			t1.create_time_,
			t1.end_time_,
			t1.is_forbidden_,
			t1.creator_,
			t1.is_dele_,
			( CASE WHEN t2.BUSINESSKEY_STR_ IS NULL THEN  CAST(t2.BUSINESSKEY_ AS text) ELSE t2.BUSINESSKEY_STR_ END ) AS id_
		FROM
			bpm_pro_inst t1
		INNER JOIN bpm_bus_link t2 ON t1.id_ = t2.proc_inst_id_
		${ew.customSqlSegment}
	</select>

	<!-- 获取我的办结 -->
	<select id="getMyCompletedByUserId" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where status_ in ('end','manualend') and create_by_=#{htqf.userId}
		<if test="htqf.whereSql!=null">
			 and ${htqf.whereSql}
		</if>
		<if test="htqf.orderBySql!=null">
			ORDER BY ${htqf.orderBySql}
		</if>
		<if test="htqf.orderBySql==null">
			 order by create_time_ desc
		</if>
	</select>
	
	<!-- 获取我的草稿 -->
	<select id="getDraftsByUserId" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where status_='draft' and create_by_=#{htqf.userId}
		<if test="htqf.whereSql!=null">
				and ${htqf.whereSql}
		</if>
		<if test="htqf.orderBySql!=null">
			ORDER BY ${htqf.orderBySql}
		</if>
		<if test="htqf.orderBySql==null">
			 order by create_time_ desc
		</if>
	</select>

	<!-- 获取已办事宜 -->
	<select id="getHandledByUserId" parameterType="java.util.Map" resultType="java.util.Map">
		select
		<include refid="columns" />
		,opinion.auditor_ "auditor",opinion.auditor_name_ "auditorName", opinion.task_name_ "taskName",opinion.status_ "opinionStatus", opinion.sign_type_ "signType", opinion.skip_type_ "skipType", opinion.task_key_ "taskKey"
		,due.due_time_ "dueTaskTime", due.EXPIRATION_DATE_ "dueExpDate", due.date_type_ "dueDateType",due.status_ "dueStatus", (due.due_time_ - due.remaining_time_) "dueUseTaskTime",  opinion.task_id_ "taskId"
		,opinion.create_time_ "taskCreateTime",opinion.complete_time_ "taskCompleteTime",opinion.form_data_ "formData",opinion.parent_task_id_ "parentTaskId"
		FROM  bpm_pro_inst wfInst
		INNER JOIN bpm_check_opinion opinion ON wfInst.id_ = opinion.proc_inst_id_
		LEFT JOIN bpm_task_due_time due ON opinion.task_id_=due.task_id_
		WHERE
		    wfInst.status_ !='draft'
		AND wfInst.IS_DELE_!=1
		AND (
		    opinion.auditor_ = #{ew.paramNameValuePairs.userId} or opinion.agent_leader_id_=#{ew.paramNameValuePairs.userId}
		)
		AND opinion.status_ NOT IN ('skip','copyto','copyto_reply','start_commu','lockTask','end_revoke','start','end','feedback','signRecoverCancel','signLineRetracted')
		<if test="@com.artfess.base.ognl.Ognl@isNotEmpty(ew.customSqlSegment)">
			AND
			${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
    	</if>
		order by opinion.complete_time_ desc
	</select>

	<!-- 获取已办的实例 -->
	<select id="getDoneInstList" resultType="java.util.Map">
		SELECT DISTINCT wfInst.id_, <include refid="columns" />
		FROM bpm_check_opinion bco
		LEFT JOIN bpm_pro_inst wfInst ON bco.PROC_INST_ID_ = wfInst.ID_
		WHERE
			(auditor_ = #{ew.paramNameValuePairs.userId} or agent_leader_id_=#{ew.paramNameValuePairs.userId}
			<if test="@com.artfess.base.ognl.Ognl@isNotEmpty(ew.paramNameValuePairs.leaders)">
				<foreach collection="ew.paramNameValuePairs.leaders" index="key" item="item" open="" separator=" " close="">
					or (auditor_ ='${key}'
					<if test="!item.isAll">
						and  wfInst.proc_def_key_ in (${item.defKeys})
					</if>
					)
				</foreach>
			</if>
			)
		AND bco.status_ NOT IN ('skip','copyto','copyto_reply','start_commu','lockTask','end_revoke','start','end','feedback','signRecoverCancel','signLineRetracted')
		AND wfInst.status_ != 'draft'
		AND wfInst.IS_DELE_ != 1
		<if test="@com.artfess.base.ognl.Ognl@isNotEmpty(ew.customSqlSegment)">
	    	AND ${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
	    </if>
		ORDER BY wfInst.create_time_ DESC
	</select>
	
	<!-- 获取已办的实例总数(带查询条件) -->
	<select id="getDoneInstListCount" resultType="java.lang.Long">
		SELECT count(DISTINCT wfInst.id_)
		FROM bpm_check_opinion bco
		LEFT JOIN bpm_pro_inst wfInst ON bco.PROC_INST_ID_ = wfInst.ID_
		WHERE
			(auditor_ = #{ew.paramNameValuePairs.userId} or agent_leader_id_=#{ew.paramNameValuePairs.userId}
			<if test="@com.artfess.base.ognl.Ognl@isNotEmpty(ew.paramNameValuePairs.leaders)">
				<foreach collection="ew.paramNameValuePairs.leaders" index="key" item="item" open="" separator=" " close="">
					or (auditor_ ='${key}'
					<if test="!item.isAll">
						and  wfInst.proc_def_key_ in (${item.defKeys})
					</if>
					)
				</foreach>
			</if>
			)
		AND bco.status_ NOT IN ('skip','copyto','copyto_reply','start_commu','lockTask','end_revoke','start','end','feedback','signRecoverCancel','signLineRetracted')
		AND wfInst.status_ != 'draft'
		AND wfInst.IS_DELE_ != 1
		<if test="@com.artfess.base.ognl.Ognl@isNotEmpty(ew.customSqlSegment)">
	    	AND ${@com.artfess.base.ognl.Ognl@withOutWhere(ew.customSqlSegment)}
	    </if>
	</select>
	
	<!-- 获取不同流程分类的已办数量分别是多少(不带查询条件) -->
	<select id="getDoneInstCount" parameterType="java.util.Map" resultType="java.util.Map">
		SELECT
		    count(DISTINCT wfInst.id_) AS "count",
			wfInst.type_id_ AS "typeId"
		FROM
			bpm_check_opinion bco
		LEFT JOIN bpm_pro_inst wfInst ON bco.PROC_INST_ID_ = wfInst.ID_
		WHERE
			(auditor_ = #{ew.paramNameValuePairs.userId} or agent_leader_id_=#{ew.paramNameValuePairs.userId}
			<if test="@com.artfess.base.ognl.Ognl@isNotEmpty(ew.paramNameValuePairs.leaders)">
				<foreach collection="ew.paramNameValuePairs.leaders" index="key" item="item" open="" separator=" " close="">
					or (auditor_ ='${key}'
					<if test="!item.isAll">
						and  wfInst.proc_def_key_ in (${item.defKeys})
					</if>
					)
				</foreach>
			</if>
			)
		AND bco.status_ NOT IN ('skip','copyto','copyto_reply','start_commu','lockTask','end_revoke','start','end','feedback','signRecoverCancel','signLineRetracted')
		AND wfInst.status_ != 'draft'
		AND wfInst.IS_DELE_ != 1
		GROUP BY
			wfInst.TYPE_ID_
	</select>

	<select id="getDoneInstCountByUserId" parameterType="java.util.Map" resultType="java.lang.Long">
		SELECT
		    count(DISTINCT wfInst.id_)
		FROM
			bpm_check_opinion bco
		LEFT JOIN bpm_pro_inst wfInst ON bco.PROC_INST_ID_ = wfInst.ID_
		WHERE
			( auditor_ = #{userId} OR agent_leader_id_ = #{userId} )
		AND bco.status_ NOT IN ('skip','copyto','copyto_reply','start_commu','lockTask','end_revoke','start','end','feedback','signRecoverCancel','signLineRetracted')
		AND wfInst.status_ != 'draft'
		AND wfInst.IS_DELE_ != 1
	</select>

	<!-- 获取办结事宜-->
	<select id="getCompletedByUserId" resultMap="BpmProcessInstance">
		<![CDATA[
		select DISTINCT wfInst.*,opinion.auditor_,opinion.auditor_name_
		from bpm_pro_inst wfInst INNER JOIN bpm_check_opinion opinion on wfInst.id_=opinion.proc_inst_id_
	     ]]>
		${ew.customSqlSegment}
	</select>

    <select id="getTestListByBpmnDefKey" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where proc_def_key_=#{defKey} and is_formmal_ = 'N'
	</select>

	<update id="updForbiddenByDefKey" >
		update bpm_pro_inst set IS_FORBIDDEN_=#{isForbidden,jdbcType=NUMERIC} where proc_def_key_=#{defKey}
	</update>

	<update id="updForbiddenByInstId" >
		update bpm_pro_inst set IS_FORBIDDEN_=#{isForbidden,jdbcType=NUMERIC} where id_=#{id}
	</update>


	<select id="getByDefKeyFormal" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where proc_def_key_=#{defKey} and is_formmal_=#{formal} 
	</select>


	<select id="getByParentId" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where parent_inst_id_=#{parentInstId,jdbcType=VARCHAR} 
	</select>

	<select id="getListByDefId" resultMap="BpmProcessInstance">
		select * from bpm_pro_inst where proc_def_id_=#{procDefId,jdbcType=VARCHAR} 
	</select>

	<select id="getByBusinessKey"   parameterType="java.lang.String" resultMap="BpmProcessInstance">
		SELECT * FROM bpm_pro_inst 
		WHERE 
		biz_key_=#{businessKey} 
	</select>

	<!-- 获取历史会议-->
	<select id="getMyHandledMeeting" parameterType="java.util.Map"  resultMap="BpmProcessInstance">
		select DISTINCT wfInst.*
		from bpm_pro_inst wfInst INNER JOIN bpm_check_opinion opinion on wfInst.id_=opinion.proc_inst_id_
		<![CDATA[ where wfInst.status_<>'draft']]>
		<if test="htqf.whereSql!=null">
				and ${htqf.whereSql}
		</if>

		<if test="htqf.orderBySql!=null">
			ORDER BY ${htqf.orderBySql}
		</if>
		<if test="orderBySql==null">
			 order by wfInst.create_time_ desc
		</if>
	</select>

    <select id="queryByuserId" parameterType="java.util.Map" resultMap="BpmProcessInstance">
        SELECT * FROM (SELECT * FROM bpm_pro_inst
        WHERE ID_ in (SELECT PROC_INST_ID_ FROM bpm_task where OWNER_ID_ = #{ew.paramNameValuePairs.userId})
        or ID_ IN (SELECT PROC_INST_ID_ FROM bpm_task_notice where OWNER_ID_ = #{ew.paramNameValuePairs.userId} or ASSIGNEE_ID_ = #{ew.paramNameValuePairs.userId})
        or ID_ IN (SELECT PROC_INST_ID_ FROM bpm_check_opinion where QUALFIEDS_ like '%${ew.paramNameValuePairs.userId}%')
        AND STATUS_ != 'end' AND STATUS_ != 'manualend' ORDER BY create_time_ DESC) text
        ${ew.customSqlSegment}
    </select>

    <select id="getById" resultMap="BpmProcessInstance">
		select * from (SELECT * FROM bpm_pro_inst
		WHERE
		(id_ IN (SELECT PROC_INST_ID_ FROM bpm_check_opinion
				WHERE auditor_ = #{ew.paramNameValuePairs.auditor} )
		OR id_ IN (SELECT PROC_INST_ID_ FROM bpm_check_opinion_hi
				WHERE auditor_ = #{ew.paramNameValuePairs.auditor} ))
		AND status_ = 'end') temp
		${ew.customSqlSegment}
    </select>

    <update id="isDeleInst" >
		update bpm_pro_inst set IS_DELE_=#{isDele} where ID_=#{instId}
	</update>

    <update id="isDeleOpinion" >
		update bpm_check_opinion set IS_DELE_=#{isDele} where PROC_INST_ID_=#{instId}
	</update>

    <update id="isDeleNotice" >
		update bpm_task_notice set IS_DELE_=#{isDele} where PROC_INST_ID_=#{instId}
	</update>

    <update id="isDeleTask" >
		update bpm_task set IS_DELE_=#{isDele} where PROC_INST_ID_=#{instId}
	</update>

    <select id="getBpmTaskIdByInstId" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT TASK_ID_ FROM bpm_task WHERE PROC_INST_ID_ = #{instId}
    </select>

    <delete id="deleteNotice" parameterType="java.lang.String">
        DELETE FROM bpm_task_notice WHERE PROC_INST_ID_=#{instId}
    </delete>

    <select id="getNodeIdByInstId" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT TASK_KEY_ FROM bpm_check_opinion WHERE PROC_INST_ID_ = #{instId} ORDER BY CREATE_TIME_ desc
    </select>

	<delete id="delVarByInstIdAndName" >
		DELETE FROM  act_ru_variable where PROC_INST_ID_  = #{bpmnInstId} and NAME_ in
       	<foreach collection="list" index="index" item="name" open="(" separator="," close=")"> 
          	#{name} 
       	</foreach> 
	</delete>

	<select id="getVarByInstIdAndName"  resultType="java.util.Map">
		select ID_ FROM  act_ru_variable where PROC_INST_ID_  = #{bpmnInstId} and NAME_ in
       	<foreach collection="list" index="index" item="name" open="(" separator="," close=")"> 
          	#{name} 
       	</foreach> 
	</select>


    <select id="getMyRequestByUserId" resultMap="BpmProcessInstance">
        SELECT
        bpm_pro_inst.id_,
        bpm_pro_inst.subject_,
        bpm_pro_inst.proc_def_id_,
        bpm_pro_inst.bpmn_def_id_,
        bpm_pro_inst.proc_def_key_,
        bpm_pro_inst.proc_def_name_,
        bpm_pro_inst.biz_key_,
        bpm_pro_inst.sys_code_,
        bpm_pro_inst.form_key_,
        bpm_pro_inst.status_,
        bpm_pro_inst.end_time_,
        bpm_pro_inst.duration_,
        bpm_pro_inst.type_id_,
        bpm_pro_inst.result_type_,
        bpm_pro_inst.bpmn_inst_id_,
        bpm_pro_inst.is_formmal_,
        bpm_pro_inst.parent_inst_id_,
        bpm_pro_inst.super_node_id_,
        bpm_pro_inst.urgent_state_,
        bpm_pro_inst.creator_,
        bpm_pro_inst.create_by_,
        bpm_pro_inst.create_time_,
        bpm_pro_inst.create_org_id_,
        bpm_pro_inst.update_by_,
        bpm_pro_inst.update_time_,
        bpm_pro_inst.is_forbidden_,
        bpm_pro_inst.create_org_path_,
        bpm_pro_inst.is_dele_,
        bpm_pro_inst.support_mobile_,
        bpm_pro_inst.data_mode_
        FROM
        bpm_pro_inst
        LEFT JOIN bpm_pro_inst_status ON bpm_pro_inst.STATUS_ = bpm_pro_inst_status.key_
        ${ew.customSqlSegment}
        ORDER BY
        bpm_pro_inst_status.VALUE_,create_time_ DESC
    </select>
    
    <select id="getInstanceCountByDefKeys" resultType="java.util.Map">
        SELECT
			PROC_DEF_KEY_,
			PROC_DEF_NAME_,
			COUNT(1) AS "totalCount",
			( SELECT COUNT(1) FROM BPM_PRO_INST subBpi WHERE subBpi.PROC_DEF_KEY_ = bpi.PROC_DEF_KEY_ AND subBpi.STATUS_ != 'end' ) AS "runningCount" 
		FROM
			BPM_PRO_INST bpi 
		WHERE
			PROC_DEF_KEY_ IN
			<foreach collection="defKeys" index="index" item="defKey" open="(" separator="," close=")"> 
        		#{defKey} 
    		</foreach> 
		GROUP BY
			PROC_DEF_KEY_,PROC_DEF_NAME_
    </select>
</mapper>