<?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="cn.iocoder.yudao.module.bpm.dal.mysql.task.BpmProcessInstanceExtMapper">

    <select id="getProcessInstancesGroupByModelName"
           resultType="cn.iocoder.yudao.module.bpm.controller.admin.task.vo.instance.BpmProcessInstanceStatisticsRespVO">
        SELECT name, COUNT(*) AS totalCount
        FROM bpm_process_instance_ext
        WHERE deleted = FALSE
        <if test="createTime != null and createTime.length > 0">
            AND create_time
            <foreach collection="createTime" item="time" index="index" open="BETWEEN" close="" separator="AND">
                #{time}
            </foreach>
        </if>
        <if test="userIds != null and userIds.length > 0">
            AND start_user_id in
            <foreach collection="userIds" item="id" index="index" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        AND process_definition_id like 'oa_%'
        GROUP BY name
    </select>

    <select id="getProcessInstancesGroupByResultStatus"
            resultType="cn.iocoder.yudao.module.bpm.controller.admin.task.vo.instance.BpmProcessInstanceStatisticsRespVO">
        SELECT result, COUNT(*) AS totalCount
        FROM bpm_process_instance_ext
        WHERE deleted = FALSE
        <if test="createTime != null and createTime.length > 0">
            AND create_time
            <foreach collection="createTime" item="time" index="index" open="BETWEEN" close="" separator="AND">
                #{time}
            </foreach>
        </if>
        <if test="name != null ">
            AND name = #{name}
        </if>
        <if test="userIds != null and userIds.length > 0">
            AND start_user_id in
            <foreach collection="userIds" item="id" index="index" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        AND process_definition_id like 'oa_%'
        GROUP BY result
    </select>

    <!-- 根据用户分组，统计查询平均审批耗时最长Top10  数据包括已完成数据和平均耗时 -->
    <select id="getUserProcessTpo10"
            resultType="cn.iocoder.yudao.module.bpm.controller.admin.task.vo.instance.BpmProcessFinishStatisticsRespVO">
        SELECT assignee_user_id as userId,
               COUNT(*) as finfishCount,
               (SUM(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(create_time))/60) /count(*) as userTime
        FROM bpm_task_ext
        WHERE end_time IS NOT NULL
        AND reason != '自动审批'
        AND deleted = FALSE
        AND process_definition_id like 'oa_%'
        GROUP BY assignee_user_id order by userTime desc limit 0, 10
    </select>

    <select id="selectUnfinishProcessCount"
            resultType="cn.iocoder.yudao.module.bpm.controller.admin.task.vo.instance.BpmProcessFinishStatisticsRespVO">
        select assignee_user_id as userId,
               count(*) as unFinfishCount
        FROM bpm_task_ext
        WHERE end_time IS NULL
        AND deleted = FALSE
        AND result = 1
        <if test="userIds != null and userIds.length > 0">
            AND assignee_user_id in
            <foreach collection="userIds" item="id" index="index" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        AND process_definition_id like 'oa_%'
        GROUP BY assignee_user_id
    </select>
    <select id="getProcessInstanceResultStatusStatisticsGroupTime"
            resultType="cn.iocoder.yudao.module.bpm.controller.admin.task.vo.instance.BpmProcessInstanceResultStatusStatisticsGroupTimeVO">
        SELECT
        name,
        result,
        DATE_FORMAT( create_time, '%Y-%m-%d' ) as time,
        COUNT(*) AS totalCount
        FROM bpm_process_instance_ext
        WHERE deleted = FALSE
        <if test="recentDays != null">
            AND create_time >= DATE_SUB( NOW(), INTERVAL #{recentDays} DAY )
        </if>
        <if test="createTime != null and createTime.length > 0">
            AND create_time
            <foreach collection="createTime" item="time" index="index" open="BETWEEN" close="" separator="AND">
                #{time}
            </foreach>
        </if>
        <if test="userIds != null and userIds.length > 0">
            AND start_user_id in
            <foreach collection="userIds" item="id" index="index" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        AND process_definition_id like 'oa_%'
        GROUP BY name,DATE_FORMAT( create_time, '%Y-%m-%d' );
    </select>


</mapper>
