<?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.yeejoin.amos.boot.module.jcs.api.mapper.AlertCalledMapper">

    <select id="queryAlertStatusCount" resultType="java.util.Map">
        SELECT
        count( 1 ) calledCount,
        sum( CASE WHEN alert_status = 1 THEN 1 ELSE 0 END ) finishedCount,
        sum( CASE WHEN alert_type_code = 230 THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        jc_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= #{beginDate}
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> #{endDate}
        </if>
    </select>

    <!-- //判断是否航空器救援 如果为航空器救援则保存冗余字段 response_level_code bug2542 bykongfm  -->
    <select id="alertCalledListByAlertStatus" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.AlertCalledZhDto">
        SELECT
        a.sequence_nbr sequenceNbr,
        a.alert_stage alertStage,
        a.call_time callTime,
        a.rescue_grid rescueGrid,
        a.alert_type alertType,
        a.alert_type_code alarmTypeCode,
        a.unit_involved unitInvolved,
        a.trapped_num trappedNum,
        a.casualties_num casualtiesNum,
        a.address ,
        a.coordinate_x longitude,
        a.coordinate_y latitude,
        CASE a.response_level_code
        WHEN '1164' THEN '紧急出动'
        WHEN '1165' THEN '紧急集结'
        WHEN '1166' THEN '原地待命'
        ELSE '' END responseLevelCode
        FROM jc_alert_called a
        where a.is_delete=0
        AND a.coordinate_x IS NOT NULL
        AND a.coordinate_y IS NOT NULL
        <if test='par.status==0'>
            and a.alert_status =0
        </if>
        <if test='par.status==1'>
            and a.alert_status =1
        </if>
        <if test='par.address!=null and  par.address!="" '>
            and a.address like CONCAT('%',#{par.address},'%') ||  a.alert_type like CONCAT('%',#{par.address},'%')
        </if>

        <if test='par.whether24!=false'>
            and a.call_time &gt;= (NOW() - interval 24 hour)
        </if>
        ORDER BY a.call_time DESC
        <if test='pageNum!=null and pageSize !=null'>
            limit #{pageNum},#{pageSize}
        </if>
    </select>


    <select id="alertCalledListByAlertStatusCount" resultType="Integer">
        SELECT
        COUNT(*)
        FROM jc_alert_called a
        where a.is_delete=0
        AND a.coordinate_x IS NOT NULL
        AND a.coordinate_y IS NOT NULL
        <if test='par.status==0'>
            and a.alert_status =0
        </if>
        <if test='par.status==1'>
            and a.alert_status =1
        </if>
        <if test='par.address!=null and  par.address!="" '>
            and a.address like CONCAT('%',#{par.address},'%')
        </if>
        <if test='par.whether24!=false'>
            and a.call_time &gt;= (NOW() - interval 24 hour)
        </if>

    </select>


    <select id="selectAllPage" resultType="com.yeejoin.amos.boot.module.jcs.api.entity.AlertCalled">
        SELECT
        a.sequence_nbr,
        a.alert_status,
        a.type,
        a.alert_stage,
        a.alarm_type,
        a.alarm_type_code,
        a.call_record_id,
        a.father_alert,
        a.response_level_code,
        a.contact_user,
        a.contact_phone,
        a.call_time,
        a.call_time_num,
        a.alert_source,
        a.alert_source_code,
        a.alert_type,
        a.alert_type_code,
        a.unit_involved,
        a.trapped_num,
        a.casualties_num,
        a.address,
        a.rescue_grid,
        a.coordinate_x,
        a.coordinate_y,
        a.response_level
        FROM
        jc_alert_called a
        <where>
            a.is_delete = 0
            <if test="alertStatus!= null ">
                and alert_status = #{alertStatus}
            </if>
            <if test="startTime!= null and endTime != null">
                and call_time between #{startTime} and #{endTime}
            </if>
            <if test="alertTypeCode!= null ">
                and alarm_type_code = #{alertTypeCode}
            </if>
            <if test="alertSourceCode!= null ">
                and alert_source_code = #{alertSourceCode}
            </if>
            <if test="data == 'address' and data != null and lift == 'ascend'">
                order by  CONVERT(address USING 'gbk') ASC  limit #{current},#{size}
            </if>
            <if test="data == 'address' and data != null and lift == 'descend'">
                order by  CONVERT(address USING 'gbk') DESC  limit #{current},#{size}
            </if>
            <if test="data != null and data != 'address' and lift == 'ascend'">
                order by #{data} ASC  limit #{current},#{size}
            </if>
            <if test="data != null and data != 'address' and lift == 'descend'">
                order by #{data} DESC  limit #{current},#{size}
            </if>
            <if test="data == null ">
                order by call_time DESC  limit #{current},#{size}
            </if>
        </where>
    </select>


    <select id="AlertCalledcountTime" resultType="Integer">

        select COUNT(*) from jc_alert_called where is_delete=0

        <if test='type==1'>
            and YEAR(call_time)=YEAR(NOW())
        </if>
        <if test='type==2'>
            and DATE_FORMAT( call_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
        </if>
        <if test='type==3'>
            and to_days(call_time) = to_days(now());
        </if>
        <if test='type==4'>
            and TO_DAYS( NOW( ) ) - TO_DAYS( call_time) = 1
        </if>

    </select>


    <select id="getTodayAlertCalled" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.AlertCalledTodyDto">

        select jc_alert_called.alert_type alarmType,
               jc_alert_called.address,
               jc_alert_called.call_time  callTime
        from jc_alert_called
        where is_delete = 0
          and to_days(call_time) = to_days(now())
        ORDER BY call_time DESC

    </select>


    <!--统计未结束的警情数量-->

    <select id="AlertCalledcount" resultType="Integer">

         select COUNT(*) from jc_alert_called where is_delete=0

        <if test="alertStatus!= null ">
            and alert_status = #{alertStatus}
        </if>

    </select>

    <select id="getContactName" resultType="string">
        SELECT
            jc_alert_called.contact_user
        FROM
            jc_alert_called
        WHERE
            jc_alert_called.is_delete = 0 AND
            jc_alert_called.contact_user IS NOT NULL AND
            jc_alert_called.contact_user != ''
        GROUP BY
            jc_alert_called.contact_user
    </select>

    <select id="getAddress" resultType="string">
        SELECT
            jc_alert_called.address
        FROM
            jc_alert_called
        WHERE
            jc_alert_called.is_delete =0 AND
            jc_alert_called.address IS NOT NULL AND
            jc_alert_called.address != ''
        GROUP BY
            jc_alert_called.address
    </select>

     <!--  未结束警情列表  -->
    <select id="AlertCalledStatusPage" resultType="com.yeejoin.amos.boot.module.jcs.api.entity.AlertCalled">

        select * from jc_alert_called where is_delete=0 and alert_status = 0 ORDER BY response_level_code desc
	,call_time  limit #{current},#{size}

    </select>








</mapper>
