<?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.casic.missiles.modular.system.dao.AlarmNowViewMapper">

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        wellId AS wellId, deviceId AS deviceId, devcode as devcode, wellCode AS wellCode, alarmId AS alarmId, deptid AS deptid,wellType AS wellType,
        alarmType AS alarmType, alarmValue AS alarmValue, alarmContent AS alarmContent, alarmTimeDate AS alarmTimeDate,
        position AS position, coordinateX AS coordinateX, coordinateY AS coordinateY,
        lngBaidu AS lngBaidu, latBaidu AS latBaidu, lngGaode AS lngGaode, latGaode AS latGaode
    </sql>

    <sql id="Alarm_Column_List">
        deviceId AS deviceId, devcode as devcode, alarmId AS alarmId, alarmType AS alarmType, alarmValue AS alarmValue, alarmContent AS alarmContent, alarmTimeDate AS alarmTimeDate
    </sql>

    <select id="getNowAlarmRecords" resultType="com.casic.missiles.modular.system.dto.AlarmNowView">
        SELECT
        <include refid="Base_Column_List"/>
        FROM alarm_now_view w
        WHERE 1 = 1
        ORDER BY alarmTimeDate DESC
    </select>

    <select id="getAlarmsByWell" resultType="com.casic.missiles.modular.system.dto.AlarmNowView">
        SELECT
        <include refid="Alarm_Column_List"/>
        FROM alarm_now_view w
        WHERE   wellId = #{id}
    </select>


    <sql id="dateTime_toChar" databaseId="mysql">
       DATE_FORMAT(alarmTimeDate,'%Y-%m-%d %H:%i:%s')
    </sql>

    <sql id="dateTime_toChar" databaseId="pgsql">
       TO_CHAR(alarmTimeDate,'yyyy-mm-dd hh24:mi:ss')
    </sql>

    <sql id="dateTime_toChar" databaseId="oracle">
       TO_CHAR(alarmTimeDate,'yyyy-mm-dd hh24:mi:ss')
    </sql>


    <sql id="date_toChar" databaseId="mysql">
       DATE_FORMAT(alarmTimeDate,'%Y-%m-%d')
    </sql>

    <sql id="date_toChar" databaseId="pgsql">
       TO_CHAR(alarmTimeDate,'yyyy-mm-dd')
    </sql>

    <sql id="date_toChar" databaseId="oracle">
       TO_CHAR(alarmTimeDate,'yyyy-mm-dd')
    </sql>


    <select id="staticsAlarmsByDay" resultType="map">
        SELECT
        b.alarmTimeDate AS 'date' ,
        count( b.wellId ) AS alarmWells ,
        sum( alarmTimes ) AS alarmTimes
        FROM
        (
        SELECT alarmTimeDate, wellId, count( 1 ) AS alarmTimes
        FROM
        (
        SELECT wellId,
        <include refid="dateTime_toChar"/>
        AS alarmTimeDate
        FROM alarm_now_view
        WHERE alarmTimeDate <![CDATA[ > ]]> #{beginTime}
        AND alarmTimeDate <![CDATA[ < ]]> DATE_ADD( #{endTime}, INTERVAL 1 DAY )
        AND deptid IN (${deptIds})) a
        GROUP BY a.alarmTimeDate, a.wellId ) b
        GROUP BY b.alarmTimeDate
        ORDER BY b.alarmTimeDate
    </select>

    <select id="countAlarmsByDay" resultType="map">
        SELECT
        COUNT(*) AS alarmTimes,
        COUNT(DISTINCT wellCode) AS alarmWells
        FROM
        alarm_now_view
        WHERE
        <include refid="date_toChar"/> = #{date}
        AND deptid IN (${deptIds})
    </select>

</mapper>
