<?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.ruoyi.ems.record.mapper.TWTempertureDataMapper">

    <resultMap type="TWTempertureData" id="TWTempertureDataResult">
        <result property="objid"    column="objid"    />
        <result property="monitorId"    column="monitorId"    />
        <result property="collectTime"    column="collectTime"    />
        <result property="tempreture"    column="tempreture"    />
        <result property="humidity"    column="humidity"    />
        <result property="illuminance"    column="illuminance"    />
        <result property="noise"    column="noise"    />
        <result property="concentration"    column="concentration"    />
        <result property="recodeTime"    column="recodeTime"    />
        <result property="alarmType"    column="alarmType"    />
        <result property="confirmPersonID"    column="confirmPersonID"    />
        <result property="confirmTime"    column="confirmTime"    />
        <result property="confirmFlag"    column="confirmFlag"    />
        <result property="monitorName"    column="monitor_name"   />
        <result property="monitorType" column="monitor_type" />

        <result property="pm1" column="pm1"/>
        <result property="pm2" column="pm2"/>
        <result property="pm10" column="pm10"/>
        <result property="standby" column="standby"/>

    </resultMap>

    <sql id="selectTWTempertureDataVo">
        select twtd.objid,
               twtd.monitorId,
               twtd.collectTime,
               twtd.tempreture,
               twtd.humidity,
               twtd.illuminance,
               twtd.noise,
               twtd.concentration,
               twtd.recodeTime,
               twtd.alarmType,
               twtd.confirmPersonID,
               twtd.confirmTime,
               twtd.confirmFlag,
               ebmi.monitor_name,

            ebmi.monitor_type,

            twtd.pm1,
            twtd.pm2,
            twtd.pm10,
            twtd.standby
        from T_W_TempertureData twtd
                left join ems_base_monitor_info ebmi on twtd.monitorId = ebmi.monitor_code
    </sql>

    <select id="selectTWTempertureDataList" parameterType="TWTempertureData" resultMap="TWTempertureDataResult">
        <include refid="selectTWTempertureDataVo"/>
        <where>
            <if test="monitorId != null  and monitorId != ''"> and twtd.monitorId = #{monitorId}</if>
            <if test="params.beginCollectTime != null and params.beginCollectTime != '' and params.endCollectTime != null and params.endCollectTime != ''">
                and twtd.collectTime between #{params.beginCollectTime} and #{params.endCollectTime}
            </if>
            <if test="tempreture != null "> and twtd.tempreture = #{tempreture}</if>
            <if test="humidity != null "> and twtd.humidity = #{humidity}</if>
            <if test="illuminance != null "> and twtd.illuminance = #{illuminance}</if>
            <if test="noise != null "> and twtd.noise = #{noise}</if>
            <if test="concentration != null "> and twtd.concentration = #{concentration}</if>
            <if test="recodeTime != null "> and twtd.recodeTime = #{recodeTime}</if>
            <if test="alarmType != null "> and twtd.alarmType = #{alarmType}</if>
            <if test="confirmPersonID != null "> and twtd.confirmPersonID = #{confirmPersonID}</if>
            <if test="params.beginRecordTime != null and params.beginRecordTime != '' and params.endRecordTime != null and params.endRecordTime != ''">
                and twtd.recodeTime between #{params.beginRecordTime} and #{params.endRecordTime}
            </if>
            <if test="confirmFlag != null "> and twtd.confirmFlag = #{confirmFlag}</if>
             <if test="monitorType != null "> and ebmi.monitor_type = #{monitorType}</if>
            <if test="pm1 != null "> and twtd.pm1 = #{pm1}</if>
            <if test="pm2 != null "> and twtd.pm2 = #{pm2}</if>
            <if test="pm10 != null "> and twtd.pm10 = #{pm10}</if>
            <if test="standby != null "> and twtd.standby = #{standby}</if>
        </where>
    </select>

    <select id="selectTWTempertureDataByObjid" parameterType="Long" resultMap="TWTempertureDataResult">
        <include refid="selectTWTempertureDataVo"/>
        where twtd.objid = #{objid}
    </select>
    <select id="selectIotInstantList" parameterType="TWTempertureData" resultMap="TWTempertureDataResult">
        <include refid="selectTWTempertureDataVo"></include>
        <where>
            <if test="monitorId != null  and monitorId != ''">and twtd.monitorId = #{monitorId}</if>
            <if test="params.beginCollectTime != null and params.beginCollectTime != '' and params.endCollectTime != null and params.endCollectTime != ''">
                and twtd.collectTime between #{params.beginCollectTime} and #{params.endCollectTime}</if>
            <if test="params.beginRecordTime != null and params.beginRecordTime != '' and params.endRecordTime != null and params.endRecordTime != ''">
                and twtd.recodeTime between #{params.beginRecordTime} and #{params.endRecordTime}</if>
        </where>
        order by twtd.collectTime
    </select>

    <select id="selectIotInstantMinitoringData" parameterType="TWTempertureData" resultMap="TWTempertureDataResult">
        <include refid="selectTWTempertureDataVo"></include>
        <where>
            <if test="monitorId != null  and monitorId != ''"> and twtd.monitorId = #{monitorId}</if>
        </where>
        order by twtd.recodeTime desc
    </select>

    <insert id="insertTWTempertureData" parameterType="TWTempertureData" useGeneratedKeys="true" keyProperty="objid">
        insert into T_W_TempertureData
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="monitorId != null">monitorId,</if>
            <if test="collectTime != null">collectTime,</if>
            <if test="tempreture != null">tempreture,</if>
            <if test="humidity != null">humidity,</if>
            <if test="illuminance != null">illuminance,</if>
            <if test="noise != null">noise,</if>
            <if test="concentration != null">concentration,</if>
            <if test="recodeTime != null">recodeTime,</if>
            <if test="alarmType != null">alarmType,</if>
            <if test="confirmPersonID != null">confirmPersonID,</if>
            <if test="confirmTime != null">confirmTime,</if>
            <if test="confirmFlag != null">confirmFlag,</if>
            <if test="pm1 != null">pm1,</if>
            <if test="pm2 != null">pm2,</if>
            <if test="pm10 != null">pm10,</if>
            <if test="standby != null">standby,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="monitorId != null">#{monitorId},</if>
            <if test="collectTime != null">#{collectTime},</if>
            <if test="tempreture != null">#{tempreture},</if>
            <if test="humidity != null">#{humidity},</if>
            <if test="illuminance != null">#{illuminance},</if>
            <if test="noise != null">#{noise},</if>
            <if test="concentration != null">#{concentration},</if>
            <if test="recodeTime != null">#{recodeTime},</if>
            <if test="alarmType != null">#{alarmType},</if>
            <if test="confirmPersonID != null">#{confirmPersonID},</if>
            <if test="confirmTime != null">#{confirmTime},</if>
            <if test="confirmFlag != null">#{confirmFlag},</if>

            <if test="pm1 != null">#{pm1},</if>
            <if test="pm2 != null">#{pm2},</if>
            <if test="pm10 != null">#{pm10},</if>
            <if test="standby != null">#{standby},</if>
         </trim>
    </insert>

    <update id="updateTWTempertureData" parameterType="TWTempertureData">
        update T_W_TempertureData
        <trim prefix="SET" suffixOverrides=",">
            <if test="monitorId != null">monitorId = #{monitorId},</if>
            <if test="collectTime != null">collectTime = #{collectTime},</if>
            <if test="tempreture != null">tempreture = #{tempreture},</if>
            <if test="humidity != null">humidity = #{humidity},</if>
            <if test="illuminance != null">illuminance = #{illuminance},</if>
            <if test="noise != null">noise = #{noise},</if>
            <if test="concentration != null">concentration = #{concentration},</if>
            <if test="recodeTime != null">recodeTime = #{recodeTime},</if>
            <if test="alarmType != null">alarmType = #{alarmType},</if>
            <if test="confirmPersonID != null">confirmPersonID = #{confirmPersonID},</if>
            <if test="confirmTime != null">confirmTime = #{confirmTime},</if>
            <if test="confirmFlag != null">confirmFlag = #{confirmFlag},</if>

            <if test="pm1 != null">pm1 = #{pm1},</if>
            <if test="pm2 != null">pm2 = #{pm2},</if>
            <if test="pm10 != null">pm10 = #{pm10},</if>
            <if test="standby != null">standby = #{standby},</if>
        </trim>
        where objid = #{objid}
    </update>

    <delete id="deleteTWTempertureDataByObjid" parameterType="Long">
        delete from T_W_TempertureData where objid = #{objid}
    </delete>

    <delete id="deleteTWTempertureDataByObjids" parameterType="String">
        delete from T_W_TempertureData where objid in
        <foreach item="objid" collection="array" open="(" separator="," close=")">
            #{objid}
        </foreach>
    </delete>

    <select id="selectLastTWTempertureDataList" parameterType="TWTempertureData" resultMap="TWTempertureDataResult">

        SELECT twtd.objid,
               twtd.monitorId,
               twtd.collectTime,
               twtd.tempreture,
               twtd.humidity,
               twtd.illuminance,
               twtd.noise,
               twtd.concentration,
               twtd.recodeTime,
               twtd.alarmType,
               twtd.pm1,
               twtd.pm2,
               twtd.pm10,
               twtd.standby,
             ebmi.monitor_name
        FROM (
            select objid,
                   monitorId,
                   collectTime,
                   tempreture,
                   humidity,
                   illuminance,
                   noise,
                   concentration,
                   recodeTime,
                   alarmType,
                   pm1,
                   pm2,
                  pm10,
                   standby
                   row_number() over(partition by monitorId order by objid desc) as rownum
            from T_W_TempertureData
             ) twtd
                 left join ems_base_monitor_info ebmi on twtd.monitorId = ebmi.monitor_code
        where twtd.rownum = 1;
    </select>

    <select id="selectLastTWTempertureDataByMonitorCode" parameterType="TWTempertureData" resultMap="TWTempertureDataResult">
        SELECT twtd.tempreture
        FROM T_W_TempertureData twtd
        WHERE twtd.monitorId = #{monitorId}
        ORDER BY twtd.objid DESC
            LIMIT 1
    </select>


    <select id="selectLastTWTempertureDataByMonitorCodes" parameterType="list" resultMap="TWTempertureDataResult">
    SELECT twtd.*
    FROM (
        SELECT *,
               ROW_NUMBER() OVER(PARTITION BY monitorId ORDER BY recodeTime DESC) as rownum
        FROM T_W_TempertureData
        WHERE monitorId IN
        <foreach item="monitorCode" collection="list" open="(" separator="," close=")">
            #{monitorCode}
        </foreach>
    ) twtd
    LEFT JOIN ems_base_monitor_info ebmi ON twtd.monitorId = ebmi.monitor_code
    WHERE twtd.rownum = 1;
</select>


    <select id="selectLastTWTempertureData" parameterType="TWTempertureData" resultMap="TWTempertureDataResult">
        select *
        from T_W_TempertureData
        ORDER BY objid DESC
            LIMIT 1000
    </select>
</mapper>
