<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.EventStatistics" name="事件统计">

    <select id="eventCategoryStatisticsServiceImpl" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.eventCategoryStatistics">
        SELECT
            @rownum := @rownum + 1 AS xuhao,
            t.name,
            t.total_count,
            t.c2,
            t.c3,
            t.c4,
            t.c5,
            t.c6,
            t.c7,
            t.c8,
            t.c9,
            t.c10,
            t.c11,
            t.c12,
            t.c13,
            t.c14,
            t.c15,
            t.c16,
            t.c17,
            t.c18,
            t.c19,
            t.c20,
            t.c21,
            t.c22,
            t.c23,
            t.c24,
            t.c25,
            t.c26
        FROM (
            SELECT
                b.name,
                COUNT(e.id) AS total_count,
                SUM(CASE WHEN e.status = '1' THEN 1 ELSE 0 END) AS c2,
                SUM(CASE WHEN e.status = '2' THEN 1 ELSE 0 END) AS c3,
                SUM(CASE WHEN e.event_category_id = '2' THEN 1 ELSE 0 END) AS c4,
                SUM(CASE WHEN e.event_category_id = '5' THEN 1 ELSE 0 END) AS c5,
                SUM(CASE WHEN e.event_category_id = '8' THEN 1 ELSE 0 END) AS c6,
                SUM(CASE WHEN e.event_category_id = '4' THEN 1 ELSE 0 END) AS c7,
                SUM(CASE WHEN e.event_category_id = '7' THEN 1 ELSE 0 END) AS c8,
                SUM(CASE WHEN e.event_category_id = '6' THEN 1 ELSE 0 END) AS c9,
                SUM(CASE WHEN e.event_category_id = '3' THEN 1 ELSE 0 END) AS c10,
                SUM(CASE WHEN e.event_category_id = '10' THEN 1 ELSE 0 END) AS c11,
                SUM(CASE WHEN e.event_category_id = '9' THEN 1 ELSE 0 END) AS c12,
                SUM(CASE WHEN e.event_category_id = '1' THEN 1 ELSE 0 END) AS c13,
                SUM(CASE WHEN e.event_category_id = '11' THEN 1 ELSE 0 END) AS c14,
                SUM(CASE WHEN e.event_category_id = '12' THEN 1 ELSE 0 END) AS c15,
                SUM(CASE WHEN e.event_category_id = '13' THEN 1 ELSE 0 END) AS c16,
                SUM(CASE WHEN e.event_category_id = '14' THEN 1 ELSE 0 END) AS c17,
                SUM(CASE WHEN e.event_category_id = '15' THEN 1 ELSE 0 END) AS c18,
                SUM(CASE WHEN e.event_category_id = '16' THEN 1 ELSE 0 END) AS c19,
                SUM(CASE WHEN e.event_category_id = '17' THEN 1 ELSE 0 END) AS c20,
                SUM(CASE WHEN e.event_category_id = '18' THEN 1 ELSE 0 END) AS c21,
                SUM(CASE WHEN e.event_category_id = '19' THEN 1 ELSE 0 END) AS c22,
                SUM(CASE WHEN e.event_category_id = 'event_category20' THEN 1 ELSE 0 END) AS c23,
                SUM(CASE WHEN e.event_category_id = '562fc004-3084-11f0-a505-0242ac110002' THEN 1 ELSE 0 END) AS c24,
                SUM(CASE WHEN e.event_category_id = '8731d067-35e8-11f0-bbaa-0242ac110002' THEN 1 ELSE 0 END) AS c25,
                SUM(CASE WHEN e.event_category_id = 'c9259321-3083-11f0-a505-0242ac110002' THEN 1 ELSE 0 END) AS c26
            FROM
                sys_area b
            LEFT JOIN
                jl_event e
                <if test="_user.company_grade=='2'.toString()">
                    ON e.city_id = b.code
                </if>
                <if test="_user.company_grade=='3'.toString()">
                    ON e.area_id = b.code
                </if>
                <if test="_user.company_grade=='4'.toString()">
                    ON e.street_id = b.code
                </if>
                <if test="_user.company_grade=='5'.toString()">
                    ON e.community_id = b.code
                </if>
                AND e.del_flag = '0'
            WHERE
                b.del_flag = '0'
                AND b.parent_id = #{_user.company_id}
                AND b.code IS NOT NULL
                <if test="occurrence_time_s!=null and occurrence_time_s!=''">
                    and e.occurrence_time>=concat(#{occurrence_time_s}, ' 00:00:00')
                </if>
                <if test="occurrence_time_e!=null and occurrence_time_e!=''">
                    and e.occurrence_time&lt;=concat(#{occurrence_time_e}, ' 23:59:59')
                </if>
            GROUP BY
                b.id, b.name
            ORDER BY
                b.sort
        ) t, (SELECT @rownum := 0) r
    </select>

    <select id="eventResolveStatisticsServiceImpl" parameterType="map" resultType="com.scpyun.platform.jilinsscgsdp.bean.entity.eventResolveStatistics">
        SELECT
            @rownum := @rownum + 1 AS xuhao,
            t.name,
            t.total_count,
            t.c2,
            t.c3,
            t.c4,
            t.c5,
            t.c6,
            t.c7,
            t.c8,
            t.c9,
            t.c10,
            t.c11,
            t.c12,
            t.c13,
            t.c14
        FROM (
            SELECT
                b.name,
                COUNT(e.id) AS total_count,
                SUM(CASE WHEN e.status = '1' THEN 1 ELSE 0 END) AS c2,
                SUM(CASE WHEN e.status = '2' THEN 1 ELSE 0 END) AS c3,
                SUM(CASE WHEN f.resolve_way = '1' THEN 1 ELSE 0 END) AS c4,
                SUM(CASE WHEN f.resolve_way = '2' THEN 1 ELSE 0 END) AS c5,
                SUM(CASE WHEN f.resolve_way = '3' THEN 1 ELSE 0 END) AS c6,
                SUM(CASE WHEN f.resolve_way = '4' THEN 1 ELSE 0 END) AS c7,
                SUM(CASE WHEN f.resolve_way = '5' THEN 1 ELSE 0 END) AS c8,
                SUM(CASE WHEN f.resolve_way = '6' THEN 1 ELSE 0 END) AS c9,
                SUM(CASE WHEN f.resolve_way = '7' THEN 1 ELSE 0 END) AS c10,
                SUM(CASE WHEN f.resolve_way = '8' THEN 1 ELSE 0 END) AS c11,
                SUM(CASE WHEN f.resolve_way = '9' THEN 1 ELSE 0 END) AS c12,
                SUM(CASE WHEN f.resolve_way = '10' THEN 1 ELSE 0 END) AS c13,
                SUM(CASE WHEN f.resolve_way = '11' THEN 1 ELSE 0 END) AS c14
            FROM
                sys_area b
            LEFT JOIN jl_event e
            <if test="_user.company_grade=='2'.toString()">
                ON e.city_id = b.code
            </if>
            <if test="_user.company_grade=='3'.toString()">
                ON e.area_id = b.code
            </if>
            <if test="_user.company_grade=='4'.toString()">
                ON e.street_id = b.code
            </if>
            <if test="_user.company_grade=='5'.toString()">
                ON e.community_id = b.code
            </if>
            AND e.del_flag = '0'
            LEFT JOIN (
                SELECT *
                FROM jl_event_flow_log f1
                WHERE f1.del_flag = '0' AND f1.is_resolve = '1'
                AND (f1.create_time, f1.event_id) IN (
                    SELECT MAX(f2.create_time), f2.event_id
                    FROM jl_event_flow_log f2
                    WHERE f2.del_flag = '0' AND f2.is_resolve = '1' and f2.resolve_way is not null and f2.log_result is not null
                    GROUP BY f2.event_id
                )
            ) f ON e.id = f.event_id
            WHERE
                b.del_flag = '0'
                <!--AND b.parent_id = '220000000000'-->
                AND b.parent_id = #{_user.company_id}
                AND b.code IS NOT NULL
                <if test="complete_time_s!=null and complete_time_s!=''">
                    and e.complete_time>=concat(#{complete_time_s}, ' 00:00:00')
                </if>
                <if test="complete_time_e!=null and complete_time_e!=''">
                    and e.complete_time&lt;=concat(#{complete_time_e}, ' 23:59:59')
                </if>
            GROUP BY
                b.id, b.name
            ORDER BY
                b.sort
        ) t, (SELECT @rownum := 0) r
    </select>

</mapper>
