<?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.yanzhu.flowable.mapper.FlowBusinessKeyMapper">

    <!--    fa.procInsId,-->
    <!--    fa.deployId,-->
    <!--    fa.createTime,-->
    <!--    fa.finishTime,-->
    <!--    fa.duration,-->
    <!--    fa.procDefKey,-->
    <!--    fa.procDefName,-->
    <!--    fa.procDefVersion,-->
    <!--    fa.category,-->
    <!--    fa.businessKey,-->
    <!--    fa.businessDeptId,-->
    <!--    fa.businessKeyName,-->
    <!--    fa.startUserId,-->
    <!--    fa.startUserName,-->
    <!--    fa.startDeptName,-->
    <!--    fa.taskId,-->
    <!--    fa.taskName,-->
    <!--    fa.assigneeId,-->
    <!--    fa.assigneeName,-->
    <!--    fa.assigneeDeptName-->
    <select id="selectAllFlowTaskByParams" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT * FROM
            vw_flow_all fa
        <where>
            <if test="taskName != null  and taskName != ''"> and fa.taskName like concat('%', #{taskName}, '%')</if>
            <if test="procDefName != null  and procDefName != ''"> and fa.procDefName like concat('%', #{procDefName}, '%')</if>
            <if test="procDefKey != null  and procDefKey != ''"> and fa.procDefKey = #{procDefKey}</if>
            <if test="businessKey != null  and businessKey != ''"> and fa.businessKey = #{businessKey}</if>
            <if test="startDeptName != null  and startDeptName != ''"> and fa.startDeptName like concat('%', #{startDeptName}, '%')</if>
            <if test="startUserName != null  and startUserName != ''"> and fa.startUserName like concat('%', #{startUserName}, '%')</if>
            <if test="businessKeyName != null  and businessKeyName != ''"> and fa.businessKeyName like concat('%', #{businessKeyName}, '%')</if>
            <if test="category != null  and category != ''"> and fa.category = #{category}</if>
            <if test="params.beginDate != null and params.beginDate != '' and params.endDate != null and params.endDate != ''"> and fa.createTime between #{params.beginDate} and #{params.endDate}</if>
            <!-- 查询条件-项目部门 -->
            <if test="projectDeptId != null "> and fa.businessDeptId = #{projectDeptId}</if>
            <!--子部门数据-->
            <if test='nowRole == "4"'> and fa.businessDeptId = #{nowDept}</if>
            <!--监理单位/总包公司/分包单位查询当前关联数据-->
            <if test='nowRole == "5" or nowRole == "6" or nowRole == "7"'> and fa.businessKey in (select DISTINCT projectId from sur_project_unit_info where unitId=#{nowDept} and del_flag=0)</if>
            <!--普通用户查询项目人员-->
            <if test='nowRole == "15" or nowRole == "16" or nowRole == "17" or nowRole == "99"'> and fa.startUserId = #{nowUser}</if>
            <if test='activeName == "await"'> and fa.finishTime is null</if>
            <if test='activeName == "finished"'> and fa.finishTime is not null</if>
        </where>
        order by fa.createTime desc
    </select>

    <!--查询工作流任务-->
    <select id="findFlowTaskByProcInsId" parameterType="string" resultType="FlowTaskEntity">
        select vf.* as startUserPhone from vw_flow_all vf
        where vf.procInsId = #{procInsId}
    </select>

    <!--查询工作流操作日志-->
    <select id="selectCommentByProcInsId" parameterType="string"  resultType="map">
        select * from vw_flow_comment where procInstId = #{procInstId} order by startTime DESC
    </select>

    <select id="selectLastCommentByProcInsId" parameterType="string"  resultType="map">
        select * from vw_flow_comment where procInstId = #{procInstId} order by startTime DESC limit 1
    </select>

    <!--查询工作流携带的参数-->
    <select id="selectFormDatasByProcInsId" parameterType="string"  resultType="map">
        select hv.NAME_ as `name`,hv.TEXT_ as text from act_hi_varinst hv where hv.PROC_INST_ID_ = #{procInstId}
    </select>

    <!--查询我的代办任务-->
    <select id="selectMyAwaitFlowTask" parameterType="FlowTaskEntity" resultType="map">
        select fa.* from vw_flow_await fa
        where
            1=1
            <if test="procDefName != null  and procDefName != ''"> and fa.procDefName like concat('%', #{procDefName}, '%')</if>
            <if test="businessKey != null  and businessKey != ''"> and fa.businessKey = #{businessKey}</if>
            <if test="businessKeyName != null  and businessKeyName != ''"> and fa.businessKeyName like concat('%', #{businessKeyName}, '%')</if>
            <if test="category != null  and category != ''"> and fa.category = #{category}</if>
            <if test="params.beginDate != null and params.beginDate != '' and params.endDate != null and params.endDate != ''"> and fa.createTime between #{params.beginDate} and #{params.endDate}</if>
            <!-- 查询条件-项目部门 -->
            <if test="projectDeptId != null "> and fa.businessDeptId = #{projectDeptId}</if>
            <!--子部门数据-->
            <if test='nowRole == "4"'> and fa.businessDeptId = #{nowDept}</if>
            <!--监理单位/总包公司/分包单位查询当前关联数据-->
            <if test='nowRole == "5" or nowRole == "6" or nowRole == "7"'> and fa.businessKey in (select DISTINCT projectId from sur_project_unit_info where unitId=#{nowDept} and del_flag=0)</if>
            <!--普通用户查询项目人员-->
            <if test='nowRole == "15" or nowRole == "16" or nowRole == "17" or nowRole == "99"'> and fa.businessKey in (select DISTINCT project_id from sur_project_userinfo where user_id=#{nowUser} and is_del=0)</if>
            AND (fa.ASSIGNEE_ = #{nowUser}
                OR (
                        fa.ASSIGNEE_ IS NULL
                    AND (
                        fa.USER_ID_ = #{nowUser}
                    OR (
                        fa.GROUP_ID_ IN
                        <foreach collection="roleIds" item="roleId" open="(" separator="," close=")">
                            #{roleId}
                        </foreach>
                        )
                    )
                )
            )
        order by fa.createTime desc
    </select>

    <!--根据条件查询我的代办分组数量统计-->
    <select id="findAwaitCountGroupByCategory" parameterType="FlowTaskEntity" resultType="map">
        select fa.category,count(1) as total from vw_flow_await fa
        where
        1=1
        <if test="procDefName != null  and procDefName != ''"> and fa.procDefName like concat('%', #{procDefName}, '%')</if>
        <if test="businessKey != null  and businessKey != ''"> and fa.businessKey = #{businessKey}</if>
        <if test="businessKeyName != null  and businessKeyName != ''"> and fa.businessKeyName like concat('%', #{businessKeyName}, '%')</if>
        <if test="params.beginDate != null and params.beginDate != '' and params.endDate != null and params.endDate != ''"> and fa.createTime between #{params.beginDate} and #{params.endDate}</if>
        <!-- 查询条件-项目部门 -->
        <if test="projectDeptId != null "> and fa.businessDeptId = #{projectDeptId}</if>
        <!--子部门数据-->
        <if test='nowRole == "4"'> and fa.businessDeptId = #{nowDept}</if>
        <!--监理单位/总包公司/分包单位查询当前关联数据-->
        <if test='nowRole == "5" or nowRole == "6" or nowRole == "7"'> and fa.businessKey in (select DISTINCT projectId from sur_project_unit_info where unitId=#{nowDept} and del_flag=0)</if>
        <!--普通用户查询项目人员-->
        <if test='nowRole == "15" or nowRole == "16" or nowRole == "17" or nowRole == "99"'> and fa.businessKey in (select DISTINCT project_id from sur_project_userinfo where user_id=#{nowUser} and is_del=0)</if>
        AND (fa.ASSIGNEE_ = #{nowUser}
        OR (
        fa.ASSIGNEE_ IS NULL
        AND (
        fa.USER_ID_ = #{nowUser}
        OR (
        fa.GROUP_ID_ IN
        <foreach collection="roleIds" item="roleId" open="(" separator="," close=")">
            #{roleId}
        </foreach>
        )
        )
        )
        )
        group by fa.category
    </select>

    <!--查询我的已办任务-->
    <select id="selectMyFinishedFlowTask" parameterType="FlowTaskEntity" resultType="map">
        select fa.* from vw_flow_finished fa
        where
        fa.ASSIGNEE_=#{nowUser}
        <if test="procDefName != null  and procDefName != ''"> and fa.procDefName like concat('%', #{procDefName}, '%')</if>
        <if test="businessKey != null  and businessKey != ''"> and fa.businessKey = #{businessKey}</if>
        <if test="businessKeyName != null  and businessKeyName != ''"> and fa.businessKeyName like concat('%', #{businessKeyName}, '%')</if>
        <if test="category != null  and category != ''"> and fa.category = #{category}</if>
        <if test="params.beginDate != null and params.beginDate != '' and params.endDate != null and params.endDate != ''"> and fa.endTime between #{params.beginDate} and #{params.endDate}</if>
        order by fa.endTime desc
    </select>

    <select id="groupByCategory" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT a.dict_label taskName,b.cnt assigneeId,c.cnt procDefVersion FROM
                ( SELECT * FROM sys_dict_data  WHERE dict_type = 'sys_process_category') a
                    LEFT JOIN (SELECT category,COUNT(1) cnt FROM vw_flow_all WHERE finishTime IS NOT NULL
                    <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                    <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
                    <if test="prjIds !=null and prjIds.size()>0">
                        and businessKey in
                        <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>

        GROUP BY category) b ON a.dict_value=b.category
                    LEFT JOIN (SELECT category,COUNT(1) cnt FROM vw_flow_all WHERE finishTime IS NULL and taskName!='提交申请'
                    <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                    <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
                    <if test="prjIds !=null and prjIds.size()>0">
                        and businessKey in
                        <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>

        GROUP BY category) c  ON a.dict_value=c.category
    </select>

    <select id="groupByUnit" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT cat taskName, COUNT(1) assigneeId FROM (
              SELECT *,'总包单位' cat FROM vw_flow_all WHERE finishTime IS NULL AND taskName LIKE '总包%'
                <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
              UNION
              SELECT *,'监理单位' cat FROM vw_flow_all WHERE finishTime IS NULL AND taskName LIKE '监理%'
                <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
              UNION
              SELECT *,'设计单位' cat FROM vw_flow_all WHERE finishTime IS NULL AND taskName LIKE '设计%'
                <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
              UNION
              SELECT *,'甲方代表' cat FROM vw_flow_all WHERE finishTime IS NULL AND taskName LIKE '甲方%'
                <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
              UNION
              SELECT *,'子公司' cat FROM vw_flow_all WHERE finishTime IS NULL AND taskName LIKE '工程%'
                <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
                <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
          )  a
        GROUP BY cat
    </select>

    <select id="groupByUnitFinish" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT cat taskName, COUNT(1) assigneeId FROM (
        SELECT *,'总包单位' cat FROM vw_flow_all WHERE  taskName LIKE '总包%' AND taskName!='提交申请' and finishTime IS not NULL
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'监理单位' cat FROM vw_flow_all WHERE  taskName LIKE '监理%' AND taskName!='提交申请'  and finishTime IS not NULL
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'设计单位' cat FROM vw_flow_all WHERE  taskName LIKE '设计%' AND taskName!='提交申请'  and finishTime IS not NULL
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'甲方代表' cat FROM vw_flow_all WHERE  taskName LIKE '甲方%' AND taskName!='提交申请'  and finishTime IS not NULL
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'子公司' cat FROM vw_flow_all WHERE  taskName LIKE '工程%' AND taskName!='提交申请'  and finishTime IS not NULL
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        )  a
        GROUP BY cat
    </select>

    <select id="groupByUnitTotal" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT cat taskName, COUNT(1) assigneeId FROM (
        SELECT *,'总包单位' cat FROM vw_flow_all WHERE  taskName LIKE '总包%' AND taskName!='提交申请'
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'监理单位' cat FROM vw_flow_all WHERE  taskName LIKE '监理%' AND taskName!='提交申请'
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'设计单位' cat FROM vw_flow_all WHERE  taskName LIKE '设计%' AND taskName!='提交申请'
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'甲方代表' cat FROM vw_flow_all WHERE  taskName LIKE '甲方%' AND taskName!='提交申请'
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        UNION
        SELECT *,'子公司' cat FROM vw_flow_all WHERE  taskName LIKE '工程%' AND taskName!='提交申请'
        <if test="projectDeptId !=null and projectDeptId!=''">and businessDeptId=#{projectDeptId}</if>
        <if test="businessKey !=null and businessKey!=''">and businessKey=#{businessKey}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        )  a
        GROUP BY cat
    </select>

    <select id="listByCategory" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT * FROM vw_flow_all
        <where>
            <if test="category != null  and category != ''"> and category = #{category}</if>
            <if test="deptId !=null and deptId>0">and businessDeptId=#{deptId}</if>
            <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
            <if test="projectId !=null and projectId>0">and businessKey=#{projectId}</if>
            <if test="prjIds !=null and prjIds.size()>0">
                and businessKey in
                <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="listByUnit" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT * FROM vw_flow_all WHERE finishTime IS NULL
        <if test="taskId!=null">
            <if test="taskId==1"> AND taskName LIKE '总包%' </if>
            <if test="taskId==2"> AND taskName LIKE '监理%' </if>
            <if test="taskId==3"> AND taskName LIKE '设计%' </if>
            <if test="taskId==4"> AND taskName LIKE '甲方%' </if>
            <if test="taskId==5"> AND taskName LIKE '工程%' </if>
        </if>
        <if test="deptId !=null and deptId>0">and businessDeptId=#{deptId}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        <if test="projectId !=null and projectId>0">and businessKey=#{projectId}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="listByState" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT * FROM vw_flow_all
        <where>
            <if test="taskId!=null">
                <if test="taskId==1"> AND taskName!='提交申请' </if>
                <if test="taskId==2"> AND finishTime IS NULL AND taskName!='提交申请' </if>
                <if test="taskId==3"> AND finishTime IS NOT NULL </if>
            </if>
            <if test="deptId !=null and deptId>0">and businessDeptId=#{deptId}</if>
            <if test="projectId !=null and projectId>0">and businessKey=#{projectId}</if>
            <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
            <if test="prjIds !=null and prjIds.size()>0">
                and businessKey in
                <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="groupByProject" resultType="FlowTaskEntity" parameterType="long">
        SELECT a.businessKey,a.cnt duration ,b.projectName businessKeyName FROM (
        SELECT businessKey,COUNT(1) cnt FROM vw_flow_all WHERE taskName!='提交申请'
        <if test="deptId !=null and deptId>0">and businessDeptId=#{deptId}</if>
        <if test="proType != null and proType != ''"> and businessType = #{proType}</if>
        GROUP BY businessKey ) a,sur_project b WHERE a.businessKey=b.id
        ORDER BY a.cnt DESC
    </select>

    <select id="findSafetyWorkList" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT
        fa.procInsId,
        fa.deployId,
        fa.createTime,
        fa.finishTime,
        fa.duration,
        fa.procDefKey,
        fa.procDefName,
        fa.procDefVersion,
        fa.category,
        fa.businessKey,
        fa.businessDeptId,
        fa.businessKeyName,
        fa.startUserId,
        fa.startUserName,
        fa.startDeptName,
        fa.taskId,
        fa.taskName,
        fa.assigneeId,
        fa.assigneeName,
        fa.assigneeDeptName
        FROM
        vw_flow_all fa
        <where>
            <if test="proType != null and proType != ''"> and fa.businessType = #{proType}</if>
            <if test="procDefKey != null  and procDefKey != ''"> and fa.procDefKey = #{procDefKey}</if>
            <if test="businessKey != null  and businessKey != ''"> and fa.businessKey = #{businessKey}</if>
            <!-- 查询条件-项目部门 -->
            <if test="projectDeptId != null and projectDeptId != ''"> and fa.businessDeptId = #{projectDeptId}</if>
        </where>
        order by fa.createTime desc
    </select>

    <select id="selectSurProjectById" parameterType="Long" resultType="Map">
        select * from sur_project where id = #{proId}
    </select>

    <select id="groupFlowBySubDeptType" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        select x.dict_label taskName,y.taskId from (
                                                       select dict_value,dict_label
                                                       from sys_dict_data where dict_type='flow_sub_dept_type'
                                                   ) x left join (
            SELECT d.dict_value, d.dict_label,count(1) taskId  FROM
                vw_flow_all a
                    LEFT JOIN act_hi_varinst b ON a.procInsId=b.PROC_INST_ID_ AND b.NAME_='subDeptType'
                    LEFT JOIN act_hi_varinst c ON a.procInsId=c.PROC_INST_ID_ AND c.NAME_='subDeptName'
                    LEFT JOIN sys_dict_data D ON b.TEXT_=d.dict_value and d.dict_type='flow_sub_dept_type'
                    left join sur_project sp on a.businesskey=sp.id
            where a.procDefKey='flow_fbzzsp_fbszzsp'
              AND (A.finishTime is null OR (A.finishTime is not null and A.taskComType =1))
            and sp.progressVisible=0 and sp.isDel=0
            <if test="deptId !=null and deptId>0">and a.businessDeptId=#{deptId}</if>
            <if test="projectId !=null and projectId>0">and a.businessKey=#{projectId}</if>
            <if test="prjIds !=null and prjIds.size()>0">
                and a.businessKey in
                <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="proType != null and proType != ''"> and sp.projectType = #{proType}</if>
            group by d.dict_value,d.dict_label
        ) y on x.dict_value=y.dict_value
    </select>
    <select id="listFlowBySubDeptType" parameterType="FlowTaskEntity" resultType="FlowTaskEntity">
        SELECT a.procInsId,a.deployId,a.createTime,a.finishTime,a.businessKey,a.businessKeyName,a.startDeptName,b.TEXT_ as taskId, c.TEXT_ as deptName,d.dict_label taskName FROM
            vw_flow_all a
                LEFT JOIN act_hi_varinst b ON a.procInsId=b.PROC_INST_ID_ AND b.NAME_='subDeptType'
                LEFT JOIN act_hi_varinst c ON a.procInsId=c.PROC_INST_ID_ AND c.NAME_='subDeptName'
                LEFT JOIN sys_dict_data D ON b.TEXT_=d.dict_value and d.dict_type='flow_sub_dept_type'
                left join sur_project sp on a.businesskey=sp.id
        where a.procDefKey='flow_fbzzsp_fbszzsp'
          AND (A.finishTime is null OR (A.finishTime is not null and A.taskComType =1))
          and sp.progressVisible=0 and sp.isDel=0
        <if test="deptId !=null and deptId>0">and a.businessDeptId=#{deptId}</if>
        <if test="projectId !=null and projectId>0">and a.businessKey=#{projectId}</if>
        <if test="prjIds !=null and prjIds.size()>0">
            and a.businessKey in
            <foreach collection="prjIds"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="proType != null and proType != ''"> and sp.projectType = #{proType}</if>
    </select>

    <select id="selectDeployList" resultType="FlowProcDefDto">
        SELECT
        rp.id_ as id,
        rp.deployment_id_ as deploymentId,
        rd.name_ as name,
        rd.category_ as category,
        rp.key_ as flowKey,
        rp.version_ as version,
        rp.suspension_state_ as suspensionState,
        rd.deploy_time_  as deploymentTime,
        rpd.DEPT_ID_ as deptId,
        sd.dept_name as deptName,
        rpd.PROJ_ID_ as projectId,
        spi.project_name as projectName
        FROM
        act_re_procdef rp
        LEFT JOIN act_re_deployment rd ON rp.deployment_id_ = rd.id_
        left join act_re_procdef_dept rpd on rp.id_ = rpd.PROCDEF_ID_
        left join sys_dept sd on sd.dept_id = rpd.DEPT_ID_
        left join pro_project_info spi on spi.id = rpd.PROJ_ID_
        <where>
            rp.SUSPENSION_STATE_ = 1
            <if test="deptId != null">
                and rpd.DEPT_ID_ = #{deptId}
            </if>
            <if test="deptName != null and deptName != ''">
                and sd.dept_name like concat('%', #{deptName}, '%')
            </if>
            <if test="projectId != null">
                and rpd.PROJ_ID_ = #{projectId}
            </if>
            <if test="projectName != null and projectName != ''">
                and spi.project_name like concat('%', #{projectName}, '%')
            </if>
            <if test="name != null and name != ''">
                and rd.name_ like concat('%', #{name}, '%')
            </if>
            <if test="category != null and category != ''">
                and rd.category_ = #{category}
            </if>
            <if test="flowKey != null and flowKey != ''">
                and rd.key_ = #{flowKey}
            </if>
        </where>
        order by rpd.SORT_
    </select>

    <select id="selectMyDeployList" resultType="FlowProcDefDto">
        SELECT * FROM (
        SELECT
        rp.id_ as id,
        rp.deployment_id_ as deploymentId,
        rd.name_ as name,
        rd.category_ as category,
        sdd.dict_label as categoryName,
        rp.key_ as flowKey,
        rp.version_ as version,
        rp.suspension_state_ as suspensionState,
        rd.deploy_time_  as deploymentTime,
        ROW_NUMBER () OVER (
        PARTITION BY rp.key_
        ORDER BY
        rp.version_ DESC
        ) AS rn
        FROM
        act_re_procdef rp
        LEFT JOIN act_re_deployment rd ON rp.deployment_id_ = rd.id_
        left join sys_dict_data sdd on sdd.dict_type='sys_process_category' and sdd.dict_value = rd.category_
        left join act_re_procdef_role rpr on rp.key_ = rpr.PROCDEF_KEY_
        left join sys_user_role sur on sur.role_id = rpr.ROLE_ID_
        left join sys_user su on su.user_id = sur.user_id
        <where>
            rp.SUSPENSION_STATE_ = 1 and su.user_name = #{username}
            <if test="name != null and name != ''">
                and rd.name_ like concat('%', #{name}, '%')
            </if>
            <if test="category != null and category != ''">
                and rd.category_ = #{category}
            </if>
        </where>
        order by rd.deploy_time_ desc
        ) v WHERE v.rn = 1
    </select>

    <delete id="deleteDeployByRoleId" parameterType="string">
        delete from act_re_procdef_role where ROLE_ID_ = #{roleId}
    </delete>

    <insert id="batchDeployRoleRole">
        insert into act_re_procdef_role( PROCDEF_KEY_, ROLE_ID_) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.key}, #{item.roleId})
        </foreach>
    </insert>

    <update id="updateProcKeyRoleSort">
        update act_re_procdef_role set SORT_=#{sort} where PROCDEF_KEY_=#{key} and ROLE_ID_=#{roleId}
    </update>

    <!--新增项目单位流程关系-->
    <insert id="insertActReProcdefDept" parameterType="FlowDeptVo">
        insert into act_re_procdef_dept
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="procdefId != null">PROCDEF_ID_,</if>
            <if test="prtype != null">TYPE_,</if>
            <if test="deptId != null">DEPT_ID_,</if>
            <if test="projId != null">PROJ_ID_,</if>
            <if test="sort != null">SORT_,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="procdefId != null">#{procdefId},</if>
            <if test="prtype != null">#{prtype},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="projId != null">#{projId},</if>
            <if test="sort != null">#{sort},</if>
        </trim>
    </insert>
    
</mapper>