<?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.tzs.api.mapper.AlertCalledMapper">

    <select id="queryAlertStatusCount" resultType="java.util.Map">
        SELECT
        count( 1 ) calledCount,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount,
        sum( CASE WHEN alarm_type_code = '960' THEN 1 ELSE 0 END ) sleepyIncidentCount,
        sum( CASE WHEN alarm_type_code = '961'  THEN 1 ELSE 0 END ) faultRescueCount,
        sum( CASE WHEN alarm_type_code = '962'  THEN 1 ELSE 0 END ) suggestionsCount
        FROM
        tz_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>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
    </select>

    <select id="queryNearlyCount" resultType="java.util.Map">
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -7 DAY)  dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -7 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -7 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
        UNION ALL
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -6 DAY) dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -6 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -6 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
        UNION ALL
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -5 DAY) dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -5 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -5 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
        UNION ALL
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -4 DAY) dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -4 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -4 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
        UNION ALL
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -3 DAY) dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -3 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -3 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
        UNION ALL
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -2 DAY) dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -2 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -2 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
        UNION ALL
        SELECT
        DATE_ADD(#{beginDate},INTERVAL -1 DAY) dateTime,
        sum( CASE WHEN father_alert is null THEN 1 ELSE 0 END ) majorAlertCount
        FROM
        tz_alert_called
        WHERE 1 = 1
        <if test="beginDate != null and beginDate != ''">
            and call_time >= DATE_ADD(#{beginDate},INTERVAL -1 DAY)
        </if>
        <if test="endDate != null and endDate != ''">
            and call_time <![CDATA[ <= ]]> DATE_ADD(#{endDate},INTERVAL -1 DAY)
        </if>
        <if test="orgCode != null and orgCode != ''  and  recUserId != null and   recUserId != ''" >
            and org_code= #{orgCode}
            or rec_user_id = #{recUserId}
        </if>
    </select>

    <select id="queryAlertListByQueryDto" resultType="java.util.Map">
        SELECT
        a.work_order_number AS workOrderNumber,
        a.rec_user_name AS creator,
        a.emergency_person AS emergency,
        a.call_time AS emergencyTime,
        a.emergency_call AS emergencyCall,
        a.device_id AS deviceId,
        e.address AS elevatorAddress,
        a.region_code AS address,
        a.alarm_type AS alertType,
        a.alert_source AS alertSource,
        a.alert_stage AS alertStage,
        CASE a.alert_status
        WHEN 0 THEN
        '未完成'
        WHEN 1 THEN
        '完成'
        ELSE
        ''
        END AS alertStatus
        FROM
        tz_alert_called a
        LEFT JOIN tcb_elevator e ON e.rescue_code = a.device_id
        WHERE 1=1
        <if test="workOrderNumber != null and workOrderNumber != ''">
            AND a.work_order_number like CONCAT(CONCAT('%',#{workOrderNumber}),'%')
        </if>
        <if test="creator != null and creator != ''">
            AND a.rec_user_name like CONCAT(CONCAT('%',#{creator}),'%')
        </if>
        <if test="emergency != null and emergency != ''">
            AND a.emergency_person like CONCAT(CONCAT('%',#{emergency}),'%')
        </if>
        <if test="emergencyTimeStart != null ">
            and #{emergencyTimeStart} <![CDATA[ <= ]]>  a.call_time
        </if>
        <if test="emergencyTimeEnd != null ">
            and a.call_time <![CDATA[ <= ]]>  #{emergencyTimeEnd}
        </if>
        <if test="workOrderNumber != null and workOrderNumber != ''">
            and call_time <![CDATA[ <= ]]> #{endDate}
        </if>
        <if test="emergencyCall != null and emergencyCall != ''">
            AND a.emergency_call like CONCAT(CONCAT('%',#{emergencyCall}),'%')
        </if>
        <if test="deviceId != null and deviceId != ''">
            AND a.device_id like CONCAT(CONCAT('%',#{deviceId}),'%')
        </if>
        <if test="elevatorAddress != null and elevatorAddress != ''">
            AND e.address  like CONCAT(CONCAT('%',#{elevatorAddress}),'%')
        </if>
        <if test="address != null and address != ''">
            AND a.region_code  like CONCAT(CONCAT('%',#{address}),'%')
        </if>
        <if test="alertType != null and alertType != ''">
            AND a.alarm_type_code  = #{alertType}
        </if>
        <if test="alertSource != null and alertSource != ''">
            AND a.alert_source_code  = #{alertSource}
        </if>
        <if test="alertStage != null and alertStage != ''">
            AND a.alert_stage_code  = #{alertStage}
        </if>
        <if test="alertStatus != null and alertStatus != ''">
            AND a.alert_status  = #{alertStatus}
        </if>
 



    </select>

</mapper>


