<?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>

	<select id="getTodayEmergencyCount" resultType="java.util.Map">
		SELECT
		COUNT(alarm_type_code = '960') as sleepyTncidentTotal,
		sum(
		CASE
		WHEN
		alarm_type_code = '960'
		AND alert_stage_code IN ('861', '862')
		THEN
		1
		ELSE
		0
		END
		) as rescueTotal ,
		sum(
		CASE
		WHEN alarm_type_code = '960'
		AND
		alert_stage_code in
		('864','865','866') THEN
		1
		ELSE
		0
		END
		) as
		rescueComplete
		FROM
		tz_alert_called where  is_delete=0
	</select>
	<select id="getImportantEventCount" resultType="java.util.Map">
		SELECT
		count(afv.alert_called_id) as importantEventCount
		FROM
		tz_alert_called ac
		LEFT JOIN (
		SELECT
		*
		FROM
		tz_alert_form_value
		WHERE
		tz_alert_form_value.field_value IS NOT NULL
		AND tz_alert_form_value.field_value != ''
		AND field_code IN ('injured_num', 'die_num') 
		and tz_alert_form_value.is_delete=0
		) afv ON ac.sequence_nbr = afv.alert_called_id
		WHERE
		ac.alarm_type_code = '960' and ac.is_delete=0
	</select>
	<select id="getImportantEventDetail" resultType="java.util.Map">
		SELECT
		ac.*
		FROM
		tz_alert_called ac
		LEFT JOIN (
		SELECT
		*
		FROM
		tz_alert_form_value
		WHERE
		tz_alert_form_value.field_value IS NOT NULL
		AND tz_alert_form_value.field_value != ''
		AND field_code IN ('injured_num', 'die_num') 
		and tz_alert_form_value.is_delete=0
		) afv ON ac.sequence_nbr = afv.alert_called_id
		WHERE
		ac.alarm_type_code = '960' and ac.is_delete=0
	</select>
</mapper>


