<?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.system.mapper.TDeviceListMapper">
    
    <resultMap type="TDeviceList" id="TDeviceListResult">
        <result property="id"    column="id"    />
        <result property="fid"    column="fid"    />
        <result property="deviceProper"    column="device_proper"    />
        <result property="userid"    column="userid"    />
        <result property="sfzid"    column="sfzid"    />
        <result property="telno"    column="telno"    />
        <result property="factory"    column="factory"    />
        <result property="deviceModel"    column="device_model"    />
        <result property="deviceId"    column="device_id"    />
        <result property="simCode"    column="sim_code"    />
        <result property="serialNumber"    column="serial_number"    />
        <result property="grantType"    column="grant_type"    />
        <result property="price"    column="price"    />
        <result property="cost"    column="cost"    />
        <result property="payment"    column="payment"    />
        <result property="status"    column="status"    />
        <result property="name"    column="name"    />
        <result property="tel_one"    column="tel_one"    />
        <result property="online_statis"    column="online_statis"    />
        <result property="electric_quantity"    column="electric_quantity"    />
        <result property="offlinetime"    column="offlinetime"    />
    </resultMap>

    <sql id="selectTDeviceListVo">
        select id, device_proper, userid, sfzid, telno, factory, device_model, device_id, sim_code, serial_number, grant_type, price, cost, payment, createtime from t_device_list
    </sql>

    <select id="selectTDeviceListList" parameterType="TDeviceList" resultMap="TDeviceListResult">
        select a.id, a.device_proper, a.userid, a.factory, a.device_model, a.device_id, a.sim_code, a.serial_number, a.grant_type, a.price, a.cost, a.payment, a.createtime, b.status, b.id as fid, c.name,c.sfzid,c.tel_one,d.online_statis,d.electric_quantity,d.offlinetime from t_device_list a
        left join t_facility b on a.device_id=b.code
        left join t_user_profile c on a.userid=c.id
        left join t_shouhuan_info d on a.device_id=d.device_id_code
        <where>  
            <if test="deviceProper != null "> and a.device_proper = #{deviceProper}</if>
            <if test="userid != null "> and a.userid = #{userid}</if>
            <if test="sfzid != null  and sfzid != ''"> and a.sfzid = #{sfzid}</if>
            <if test="telno != null  and telno != ''"> and a.telno = #{telno}</if>
            <if test="factory != null "> and a.factory = #{factory}</if>
            <if test="deviceModel != null "> and a.device_model = #{deviceModel}</if>
            <if test="deviceId != null  and deviceId != ''"> and a.device_id = #{deviceId}</if>
            <if test="simCode != null  and simCode != ''"> and a.sim_code = #{simCode}</if>
            <if test="serialNumber != null  and serialNumber != ''"> and a.serial_number = #{serialNumber}</if>
            <if test="grantType != null "> and a.grant_type = #{grantType}</if>
            <if test="price != null  and price != ''"> and a.price = #{price}</if>
            <if test="cost != null  and cost != ''"> and a.cost = #{cost}</if>
            <if test="payment != null "> and a.payment = #{payment}</if>
            <if test="createTime != null "> and a.createtime = #{createTime}</if>
        </where>
    </select>
    
    <select id="selectTDeviceListById" parameterType="Integer" resultMap="TDeviceListResult">
        <include refid="selectTDeviceListVo"/>
        where id = #{id}
    </select>
    <select id="selectTDeviceByDeviceId" resultType="com.ruoyi.system.domain.dto.RateDto">
        select b.content,b.createtime from t_device_list a
        left join t_shouhuan_receive_redocde b on a.device_id=b.device_id_code
        where b.command='heart' and device_id_code = #{device_id_code}
    </select>
    <select id="selectTDeviceByBlood" resultType="com.ruoyi.system.domain.dto.RateDto">
        select b.content,b.createtime from t_device_list a
        left join t_shouhuan_receive_redocde b on a.device_id=b.device_id_code
        where b.command='blood' and device_id_code = #{device_id_code}
    </select>
    <select id="selectTDeviceByOxygen" resultType="com.ruoyi.system.domain.dto.RateDto">
        select b.content,b.createtime from t_device_list a
        left join t_shouhuan_receive_redocde b on a.device_id=b.device_id_code
        where b.command='oxygen' and device_id_code = #{device_id_code}
    </select>
    <select id="selectTDeviceByTemp" resultType="com.ruoyi.system.domain.dto.RateDto">
        select b.content,b.createtime from t_device_list a
        left join t_shouhuan_receive_redocde b on a.device_id=b.device_id_code
        where b.command='temp' and device_id_code = #{device_id_code}
    </select>
    <select id="selectTDeviceByStep" resultType="com.ruoyi.system.domain.dto.RateDto">
        select b.content,b.createtime from t_device_list a
        left join t_shouhuan_receive_redocde b on a.device_id=b.device_id_code
        where b.command='KA' and device_id_code = #{device_id_code}
    </select>
    <select id="selectTDeviceByMessage" resultType="com.ruoyi.system.domain.dto.MessageDto">
        select alarm_type,createtime from t_shouhuan_alarm_list
        where device_id_code = #{device_id_code}
    </select>
    <select id="selectTDeviceByMessageLog" resultType="com.ruoyi.system.domain.dto.MessageLogDto">
        SELECT a.device_id,c.name,b.alarm_type,b.createtime FROM t_device_list a
       left join t_shouhuan_alarm_list b on a.device_id=b.device_id_code
       left join t_user_profile c on a.userid=c.id
        <where>
            <if test="device_id != null "> and a.device_id = #{device_id}</if>
            <if test="alarm_type != null "> and b.alarm_type = #{alarm_type}</if>
        </where>
    </select>
    <select id="selectshouhuan" resultType="java.lang.String">
        select use_value from t_setting
        where use_key = #{use_key}
    </select>
    <select id="selectDeviceListDto" resultType="com.ruoyi.system.domain.dto.DeviceTypeDto">
        select device_model,count(device_model) as count from t_device_list
        group by device_model
    </select>
    <select id="selectDeviceListCount" resultType="java.lang.Integer">
        select IFNULL(count(*),0) from t_device_list
    </select>
    <select id="selectAddDevice" resultType="com.ruoyi.system.domain.dto.AddDeviceDto">
        SELECT DATE_FORMAT(createtime, '%Y-%m') AS month, COUNT(*) AS count FROM t_device_list
        WHERE createtime > DATE_SUB(NOW(), INTERVAL 3 MONTH)
        GROUP BY month;
    </select>
    <select id="selectTDeviceListBandUser" resultType="java.lang.Integer">
        select count(distinct userid) from t_device_list
    </select>
    <select id="selectTDeviceListListByDeviceId" resultMap="TDeviceListResult">
        select a.id, a.device_proper, a.userid, a.factory, a.device_model, a.device_id, a.sim_code, a.serial_number, a.grant_type, a.price, a.cost, a.payment, a.createtime, b.status, b.id as fid, c.name,c.sfzid,c.tel_one,d.online_statis,d.electric_quantity,d.offlinetime from t_device_list a
        left join t_facility b on a.device_id=b.code
        left join t_user_profile c on a.userid=c.id
        left join t_shouhuan_info d on a.device_id=d.device_id_code
        where a.device_id = #{deviceId}
    </select>
    <select id="selectTDeviceList" parameterType="TDeviceList" resultMap="TDeviceListResult">
        <include refid="selectTDeviceListVo"/>
    </select>


    <insert id="insertTDeviceList" parameterType="TDeviceList" useGeneratedKeys="true" keyProperty="id">
        insert into t_device_list
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceProper != null">device_proper,</if>
            <if test="userid != null">userid,</if>
            <if test="sfzid != null">sfzid,</if>
            <if test="telno != null">telno,</if>
            <if test="factory != null">factory,</if>
            <if test="deviceModel != null">device_model,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="simCode != null">sim_code,</if>
            <if test="serialNumber != null">serial_number,</if>
            <if test="grantType != null">grant_type,</if>
            <if test="price != null">price,</if>
            <if test="cost != null">cost,</if>
            <if test="payment != null">payment,</if>
            <if test="createTime != null">createtime,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deviceProper != null">#{deviceProper},</if>
            <if test="userid != null">#{userid},</if>
            <if test="sfzid != null">#{sfzid},</if>
            <if test="telno != null">#{telno},</if>
            <if test="factory != null">#{factory},</if>
            <if test="deviceModel != null">#{deviceModel},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="simCode != null">#{simCode},</if>
            <if test="serialNumber != null">#{serialNumber},</if>
            <if test="grantType != null">#{grantType},</if>
            <if test="price != null">#{price},</if>
            <if test="cost != null">#{cost},</if>
            <if test="payment != null">#{payment},</if>
            <if test="createTime != null">#{createTime},</if>
         </trim>
    </insert>

    <update id="updateTDeviceList" parameterType="TDeviceList">
        update t_device_list
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceProper != null">device_proper = #{deviceProper},</if>
            <if test="userid != null">userid = #{userid},</if>
            <if test="sfzid != null">sfzid = #{sfzid},</if>
            <if test="telno != null">telno = #{telno},</if>
            <if test="factory != null">factory = #{factory},</if>
            <if test="deviceModel != null">device_model = #{deviceModel},</if>
            <if test="deviceId != null">device_id = #{deviceId},</if>
            <if test="simCode != null">sim_code = #{simCode},</if>
            <if test="serialNumber != null">serial_number = #{serialNumber},</if>
            <if test="grantType != null">grant_type = #{grantType},</if>
            <if test="price != null">price = #{price},</if>
            <if test="cost != null">cost = #{cost},</if>
            <if test="payment != null">payment = #{payment},</if>
            <if test="createTime != null">createtime = #{createTime},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateTSettingshouhuan_heart_max">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_heart_max'
    </update>
    <update id="updateTSettingshouhuan_heart_min">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_heart_min'
    </update>
    <update id="updateTSettingshouhuan_blood_high_max">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_blood_high_max'
    </update>
    <update id="updateTSettingshouhuan_blood_high_min">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_blood_high_min'
    </update>
    <update id="updateTSettingshouhuan_blood_low_max">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_blood_low_max'
    </update>
    <update id="updateTSettingshouhuan_blood_low_min">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_blood_low_min'
    </update>
    <update id="updateTSettingshouhuan_oxygen_max">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_oxygen_max'
    </update>
    <update id="updateTSettingshouhuan_oxygen_min">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_oxygen_min'
    </update>
    <update id="updateTSettingshouhuan_body_max">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_body_max'
    </update>
    <update id="updateTSettingshouhuan_body_min">
        update t_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="use_value != null">use_value = #{use_value},</if>
        </trim>
        where use_key = 'shouhuan_body_min'
    </update>

    <delete id="deleteTDeviceListById" parameterType="Integer">
        delete from t_device_list where id = #{id}
    </delete>

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