<?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.com.tenlion.systembase.dao.statistics.IStatisticsDao">

    <resultMap id="statisticsDTO" type="cn.com.tenlion.systembase.pojo.dtos.statistics.StatisticsDTO">
        <result column="dataType" property="dataType"/>
        <result column="count" property="count"/>
    </resultMap>

    <resultMap id="statisticsLineDTO" type="cn.com.tenlion.systembase.pojo.dtos.statistics.StatisticsLineDTO">
        <result column="dataType" property="dataType"/>
        <result column="count" property="count"/>
        <result column="year" property="year"/>
        <result column="month" property="month"/>
    </resultMap>

    <!-- 综治中心数量 -->
    <select id="comprehensiveCenterCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(t1.comprehensive_center_id)
        FROM
            data_comprehensive_center t1
        WHERE
            t1.is_delete = 0
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
        <if test="locationName != null and locationName != ''">
            AND t1.location_name = #{locationName}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
    </select>

    <!-- 学校数量 -->
    <select id="schoolCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(t1.school_id)
        FROM
            data_school t1
        WHERE
            t1.is_delete = 0
        <if test="location != null and location != ''">
            AND t1.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
    </select>

    <!-- 重点区域数量 -->
    <select id="keyAreaCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(t1.key_area_check_renovation_id)
        FROM
            data_key_area_check_renovation t1
        WHERE
            t1.is_delete = 0
        <if test="location != null and location != ''">
            AND t1.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
    </select>

    <!-- 重点场所数量 -->
    <select id="keyPlaceCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(t1.key_place_id)
        FROM
            data_key_place t1
        WHERE
            t1.is_delete = 0
        <if test="areaName != null and areaName != ''">
            AND t1.area_name = #{areaName}
        </if>
        <if test="areaCode != null and areaCode != ''">
            AND t1.area_code = #{areaCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
    </select>

    <!-- 学校类型占比 -->
    <select id="schoolInfo" parameterType="map" resultMap="statisticsDTO">
        SELECT
            t1.running_type_name dataType,
            COUNT(t1.school_id) count
        FROM
            data_school t1
        WHERE
            t1.is_delete = 0
        <if test="location != null and location != ''">
            AND t1.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
        GROUP BY
            t1.running_type_name
    </select>

    <!-- 学校重点人员(危害程度/是否关注) -->
    <select id="personKeyInfo" parameterType="map" resultMap="statisticsDTO">
        SELECT
            <if test="degreeOfHarmName != null and degreeOfHarmName != ''">
                t1.degree_of_harm_name dataType,
            </if>
            <if test="isFollow != null">
                (
                    CASE
                        WHEN t1.is_follow = 1 THEN
                            '已关注'
                        ELSE
                            '未关注'
                        END
                ) dataType,
            </if>
            COUNT(t1.school_key_id) count
        FROM
            data_school_key t1
        WHERE
            t1.is_delete = 0
        <if test="location != null and location != ''">
            AND t1.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
        <if test="degreeOfHarmName != null and degreeOfHarmName != ''">
        GROUP BY
            t1.degree_of_harm_name
        </if>
        <if test="isFollow != null">
        GROUP BY
            t1.is_follow
        </if>
    </select>

    <!-- 学校安全事件侦破情况数据信息 -->
    <select id="caseDetectInfo" parameterType="map" resultMap="statisticsDTO">
        SELECT
            (
                CASE
                    WHEN t1.is_solve = 1 THEN
                        '已侦破'
                    ELSE
                        '未侦破'
                    END
                ) dataType,
            COUNT(t1.stu_tea_safe_id) count
        FROM
            data_stu_tea_safe t1
        WHERE
            t1.is_delete = 0
        <if test="happenLocationName != null and happenLocationName != ''">
            AND t1.happen_location_name = #{happenLocationName}
        </if>
        <if test="happenLocationCode != null and happenLocationCode != ''">
            AND t1.happen_location_code = #{happenLocationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
        GROUP BY
            t1.is_solve
    </select>

    <!-- 重点区域排查占比(案件类型/案件发生场所) -->
    <select id="areaSecurityInfo" parameterType="map" resultMap="statisticsDTO">
        SELECT
            <if test="securityQuestionName != null and securityQuestionName != ''">
                t1.security_question_name dataType,
            </if>
            <if test="involveAreaTypeName != null and involveAreaTypeName != ''">
                t1.involve_area_type_name dataType,
            </if>
            COUNT(t1.key_area_check_renovation_id) count
        FROM
            data_key_area_check_renovation t1
        WHERE
            t1.is_delete = 0
            <if test="location != null and location != ''">
                AND t1.location = #{location}
            </if>
            <if test="locationCode != null and locationCode != ''">
                AND t1.location_code = #{locationCode}
            </if>
            <if test="grid != null and grid != ''">
                AND t1.grid = #{grid}
            </if>
            <if test="securityQuestionName != null and securityQuestionName != ''">
                GROUP BY
                t1.security_question_name
            </if>
            <if test="involveAreaTypeName != null and involveAreaTypeName != ''">
                GROUP BY
                t1.involve_area_type_name
            </if>
    </select>

    <!-- 重特大案(事)件 -->
    <select id="majorInfo" parameterType="map" resultMap="statisticsDTO">
        SELECT
            t1.location dataType,
            COUNT(t1.important_event_id) count
        FROM
            cg_important_event t1
        WHERE
            t1.is_delete = 0
        <if test="location != null and location != ''">
            AND t1.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
        GROUP BY
            t1.location
    </select>

    <!-- 年度重大事件统计数据信息 -->
    <select id="caseInfo" parameterType="map" resultMap="statisticsDTO">
        SELECT
            t1.event_type_text dataType,
            COUNT(t1.important_event_id) count
        FROM
            cg_important_event t1
        WHERE
            t1.is_delete = 0
          AND LEFT (t1.event_date, 6) = #{eventDate}
        <if test="eventTypeText != null and eventTypeText != ''">
            AND t1.event_type_text = #{eventTypeText}
        </if>
        <if test="location != null and location != ''">
            AND t1.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t1.grid = #{grid}
        </if>
    </select>

    <!-- 重点区域巡查记录 -->
    <select id="keyAreaPatrolCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(
                    t1.key_area_check_renovation_patrol_id
                ) count
        FROM
            data_key_area_check_renovation_patrol t1
            LEFT JOIN data_key_area_check_renovation t2
            ON t1.key_area_check_renovation_id = t2.key_area_check_renovation_id
            AND t2.is_delete = 0
        WHERE
            t1.is_delete = 0
        <if test="patrolTime != null and patrolTime != ''">
          AND LEFT(t1.patrol_time, 7) = #{patrolTime}
        </if>
        <if test="location != null and location != ''">
            AND t2.location = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t2.location_code = #{locationCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t2.grid = #{grid}
        </if>
    </select>

    <!-- 重点场所巡查记录 -->
    <select id="keyPlacePatrolCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(
                    t1.key_place_patrol_id
                ) count
        FROM
            data_key_place_patrol t1
            LEFT JOIN data_key_place t2 ON t1.key_place_id = t2.key_place_id
            AND t2.is_delete = 0
        WHERE
            t1.is_delete = 0
        <if test="patrolTime != null and patrolTime != ''">
          AND LEFT(t1.patrol_time, 7) = #{patrolTime}
        </if>
        <if test="areaName != null and areaName != ''">
            AND t2.area_name = #{areaName}
        </if>
        <if test="areaCode != null and areaCode != ''">
            AND t2.area_code = #{areaCode}
        </if>
        <if test="grid != null and grid != ''">
            AND t2.grid = #{grid}
        </if>
    </select>

    <!-- 非公有制经济组织数量 -->
    <select id="organizeCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(t1.organization_id)
        FROM
            org_organization t1
        WHERE
            is_delete = 0
        <if test="location != null and location != ''">
            AND t1.area_name = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.area_code = #{locationCode}
        </if>
        <if test="gridName != null and gridName != ''">
            AND t1.grid_name = #{gridName}
        </if>
        <if test="gridCode != null and gridCode != ''">
            AND t1.grid_code = #{gridCode}
        </if>
    </select>

    <!-- 社会组织数量 -->
    <select id="enterpriseCount" parameterType="map" resultType="Integer">
        SELECT
            COUNT(t1.enterprise_id)
        FROM
            org_enterprise t1
        WHERE
            is_delete = 0
        <if test="location != null and location != ''">
            AND t1.area_name = #{location}
        </if>
        <if test="locationCode != null and locationCode != ''">
            AND t1.area_code = #{locationCode}
        </if>
        <if test="gridName != null and gridName != ''">
            AND t1.grid_name = #{gridName}
        </if>
        <if test="gridCode != null and gridCode != ''">
            AND t1.grid_code = #{gridCode}
        </if>
    </select>

</mapper>