<?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.bonus.canteen.core.kitchen.mapper.KitchenDeviceSensorRecordMapper">
    <resultMap type="com.bonus.canteen.core.kitchen.domain.KitchenDeviceSensorRecord" id="KitchenDeviceSensorRecordResult">
        <result property="recordId"    column="record_id"    />
        <result property="batchNo"    column="batch_no"    />
        <result property="deviceId"    column="device_id"    />
        <result property="deviceType"    column="device_type"    />
        <result property="measureCode"    column="measure_code"    />
        <result property="measureData"    column="measure_data"    />
        <result property="gatewayDeviceId"    column="gateway_device_id"    />
        <result property="deviceTime"    column="device_time"    />
        <result property="subPlace"    column="sub_place"    />
        <result property="canteenId"    column="canteen_id"    />
        <result property="areaId"    column="area_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deviceName"    column="device_name"    />
        <result property="canteenName"    column="canteen_name"    />
        <result property="measureType"    column="measure_type"    />
        <result property="measureMaxValue"    column="measure_max_value"    />
        <result property="measureMinValue"    column="measure_min_value"    />
    </resultMap>

    <sql id="selectKitchenDeviceSensorRecordVo">
        select record_id, batch_no, kdsr.device_id, kdsr.device_type, kdsr.measure_code, measure_data,
               gateway_device_id, device_time, kdsr.sub_place, kdsr.canteen_id, kdsr.area_id, kdsr.create_by,
               kdsr.create_time, kdsr.update_by, kdsr.update_time, kdi.device_name, bc.canteen_name,
               kdsm.measure_type, kdsm.measure_max_value, kdsm.measure_min_value
        from kitchen_device_sensor_record kdsr
            left join kitchen_device_sensor_metric kdsm on kdsm.measure_code = kdsr.measure_code and kdsm.device_type = kdsr.device_type
        left join kitchen_device_info kdi on kdi.device_id = kdsr.device_id
        left join basic_canteen bc on bc.canteen_id = kdi.canteen_id
    </sql>

    <select id="selectKitchenDeviceSensorRecordList" parameterType="com.bonus.canteen.core.kitchen.domain.KitchenDeviceSensorRecord" resultMap="KitchenDeviceSensorRecordResult">
        <include refid="selectKitchenDeviceSensorRecordVo"/>
        <where>  
            <if test="batchNo != null "> and batch_no = #{batchNo}</if>
            <if test="deviceId != null "> and kdsr.device_id = #{deviceId}</if>
            <if test="deviceType != null "> and kdsr.device_type = #{deviceType}</if>
            <if test="measureCode != null  and measureCode != ''"> and kdsr.measure_code = #{measureCode}</if>
            <if test="measureData != null  and measureData != ''"> and measure_data = #{measureData}</if>
            <if test="gatewayDeviceId != null "> and gateway_device_id = #{gatewayDeviceId}</if>
            <if test="deviceTime != null "> and device_time = #{deviceTime}</if>
            <if test="subPlace != null "> and kdsr.sub_place = #{subPlace}</if>
            <if test="canteenId != null "> and kdsr.canteen_id = #{canteenId}</if>
            <if test="areaId != null "> and kdsr.area_id = #{areaId}</if>
            <if test="sensorResultTypeList != null and sensorResultTypeList.size() > 0">
                and (
                <foreach item="type" collection="sensorResultTypeList" separator=" or ">
                    <choose>
                        <when test="type == 1">
                            ((kdsm.measure_code = 'temperature'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data between kdsm.measure_min_value and kdsm.measure_max_value)
                             or (kdsm.measure_code = 'humidity'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data between kdsm.measure_min_value and kdsm.measure_max_value)
                             or (kdsm.measure_code = 'smoke'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data <![CDATA[ <= ]]> kdsm.measure_max_value)
                            )
                        </when>
                        <when test="type == 2">
                            (kdsm.measure_code = 'temperature'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data <![CDATA[ > ]]> kdsm.measure_max_value)
                        </when>
                        <when test="type == 3">
                            (kdsm.measure_code = 'temperature'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data <![CDATA[ < ]]> kdsm.measure_min_value)
                        </when>
                        <when test="type == 4">
                            (kdsm.measure_code = 'humidity'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data <![CDATA[ > ]]> kdsm.measure_max_value)
                        </when>
                        <when test="type == 5">
                            (kdsm.measure_code = 'humidity'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data <![CDATA[ < ]]> kdsm.measure_min_value)
                        </when>
                        <when test="type == 6">
                            (kdsm.measure_code = 'smoke'
                            and kdsm.measure_type = 1
                            and kdsr.measure_data <![CDATA[ > ]]> kdsm.measure_max_value)
                        </when>
                    </choose>
                </foreach>
                )
            </if>
            <if test="searchValue != null and searchValue != ''">
                and (kdsr.device_id like CONCAT('%',#{searchValue},'%')
                or kdi.device_name like CONCAT('%',#{searchValue},'%')
                )
            </if>
            <if test="startDateTime != null">
                and kdsr.device_time  <![CDATA[ >= ]]> #{startDateTime}
            </if>
            <if test="endDateTime != null">
                and kdsr.device_time  <![CDATA[ <= ]]> #{endDateTime}
            </if>
        </where>
    </select>
    
    <select id="selectKitchenDeviceSensorRecordByRecordId" parameterType="Long" resultMap="KitchenDeviceSensorRecordResult">
        <include refid="selectKitchenDeviceSensorRecordVo"/>
        where record_id = #{recordId}
    </select>
        
    <insert id="insertKitchenDeviceSensorRecord" parameterType="com.bonus.canteen.core.kitchen.domain.KitchenDeviceSensorRecord" useGeneratedKeys="true" keyProperty="recordId">
        insert into kitchen_device_sensor_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="batchNo != null">batch_no,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="deviceType != null">device_type,</if>
            <if test="measureCode != null">measure_code,</if>
            <if test="measureData != null">measure_data,</if>
            <if test="gatewayDeviceId != null">gateway_device_id,</if>
            <if test="deviceTime != null">device_time,</if>
            <if test="subPlace != null">sub_place,</if>
            <if test="canteenId != null">canteen_id,</if>
            <if test="areaId != null">area_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="batchNo != null">#{batchNo},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="deviceType != null">#{deviceType},</if>
            <if test="measureCode != null">#{measureCode},</if>
            <if test="measureData != null">#{measureData},</if>
            <if test="gatewayDeviceId != null">#{gatewayDeviceId},</if>
            <if test="deviceTime != null">#{deviceTime},</if>
            <if test="subPlace != null">#{subPlace},</if>
            <if test="canteenId != null">#{canteenId},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateKitchenDeviceSensorRecord" parameterType="com.bonus.canteen.core.kitchen.domain.KitchenDeviceSensorRecord">
        update kitchen_device_sensor_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="batchNo != null">batch_no = #{batchNo},</if>
            <if test="deviceId != null">device_id = #{deviceId},</if>
            <if test="deviceType != null">device_type = #{deviceType},</if>
            <if test="measureCode != null">measure_code = #{measureCode},</if>
            <if test="measureData != null">measure_data = #{measureData},</if>
            <if test="gatewayDeviceId != null">gateway_device_id = #{gatewayDeviceId},</if>
            <if test="deviceTime != null">device_time = #{deviceTime},</if>
            <if test="subPlace != null">sub_place = #{subPlace},</if>
            <if test="canteenId != null">canteen_id = #{canteenId},</if>
            <if test="areaId != null">area_id = #{areaId},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where record_id = #{recordId}
    </update>

    <delete id="deleteKitchenDeviceSensorRecordByRecordId" parameterType="Long">
        delete from kitchen_device_sensor_record where record_id = #{recordId}
    </delete>

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