<?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.device.mapper.DeviceInfoMapper">
    <resultMap type="com.bonus.canteen.core.device.domain.DeviceInfo" id="DeviceInfoResult">
        <result property="deviceId"    column="device_id"    />
        <result property="deviceName"    column="device_name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="parentType"    column="parent_type"    />
        <result property="tenantId"    column="tenant_id"    />
        <result property="onlineState"    column="online_state"    />
        <result property="versionCode"    column="version_code"    />
        <result property="versionName"    column="version_name"    />
        <result property="consumeMode"    column="consume_mode"    />
        <result property="communicateMode"    column="communicate_mode"    />
        <result property="deviceIp"    column="device_ip"    />
        <result property="deviceMask"    column="device_mask"    />
        <result property="deviceMac"    column="device_mac"    />
        <result property="deviceGateway"    column="device_gateway"    />
        <result property="dnsPrimary"    column="dns_primary"    />
        <result property="dnsBackup"    column="dns_backup"    />
        <result property="deviceSn"    column="device_sn"    />
        <result property="deviceNumber"    column="device_number"    />
        <result property="deviceModel"    column="device_model"    />
        <result property="deviceType"    column="device_type"    />
        <result property="devicePwd"    column="device_pwd"    />
        <result property="deviceAddr"    column="device_addr"    />
        <result property="deviceKey"    column="device_key"    />
        <result property="deviceServiceIp"    column="device_service_ip"    />
        <result property="deviceServicePort"    column="device_service_port"    />
        <result property="ifQuotaLimit"    column="if_quota_limit"    />
        <result property="ifDiscount"    column="if_discount"    />
        <result property="ifTimeLimit"    column="if_time_limit"    />
        <result property="ifUseCallNum"    column="if_use_call_num"    />
        <result property="ifOffLinePay"    column="if_off_line_pay"    />
        <result property="ifUse"    column="if_use"    />
        <result property="imgUrl"    column="img_url"    />
        <result property="dataTransferStatus"    column="data_transfer_status"    />
        <result property="printStatus"    column="print_status"    />
        <result property="printKey"    column="print_key"    />
        <result property="deviceState"    column="device_state"    />
        <result property="deviceProtocol"    column="device_protocol"    />
        <result property="heartBeatTime"    column="heart_beat_time"    />
        <result property="lastUpdateTime"    column="last_update_time"    />
        <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="areaId"    column="area_id"    />
        <result property="areaName"    column="area_name"    />
        <result property="canteenId"    column="canteen_id"    />
        <result property="canteenName"    column="canteen_name"    />
        <result property="stallId"    column="stall_id"    />
        <result property="stallName"    column="stall_name"    />
        <result property="recipeName"    column="recipe_name"    />
        <result property="recipeId"    column="recipe_id"    />
    </resultMap>

    <sql id="selectDeviceInfoVo">
        select di.device_id, device_name, di.parent_id, parent_type, tenant_id, version_code,
               version_name, consume_mode, communicate_mode, device_ip, device_mask, device_mac,
               device_gateway, dns_primary, dns_backup, device_sn, device_number, device_model,
               device_type, device_pwd, device_addr, device_key, device_service_ip, device_service_port,
               if_quota_limit, if_discount, if_time_limit, if_use_call_num, if_off_line_pay, if_use,
               di.img_url, data_transfer_status, print_status, print_key, device_state, device_protocol,
               heart_beat_time, last_update_time, di.create_by, di.create_time, di.update_by, di.update_time,
               db.area_id, db.canteen_id, bc.canteen_name, db.stall_id, bs.stall_name,
               if((UNIX_TIMESTAMP() - di.last_update_time) > 120, '2','1' ) AS online_state,
               CONCAT(aaa.area_name, '/', ba.area_name) AS area_name,
               cr.recipe_name, crbd.recipe_id
        from device_info di
        left join device_bind db on di.device_id = db.device_id
        left join basic_area ba on db.area_id = ba.area_id
        left join basic_area aaa on ba.parent_id = aaa.area_id
        left join basic_canteen bc on db.canteen_id = bc.canteen_id
        left join basic_stall bs on db.stall_id = bs.stall_id
        left join cook_recipe_bind_device crbd on di.device_id = crbd.device_id
        left join cook_recipe cr on crbd.recipe_id = cr.recipe_id
    </sql>

    <select id="selectDeviceInfoList" parameterType="com.bonus.canteen.core.device.domain.DeviceInfo" resultMap="DeviceInfoResult">
        <include refid="selectDeviceInfoVo"/>
        <where>
            <if test="deviceType != null and deviceType != ''"> and di.device_type = #{deviceType}</if>
            <if test="areaId != null and areaId != ''"> and db.area_id = #{areaId}</if>
            <if test="canteenId != null and canteenId != ''"> and db.canteen_id = #{canteenId}</if>
            <if test="stallId != null and stallId != ''"> and db.stall_id = #{stallId}</if>
            <if test="searchValue != null and searchValue != ''">
                and (di.device_name like concat('%', #{searchValue}, '%')
                or di.device_sn like concat('%', #{searchValue}, '%')
                or di.device_number like concat('%', #{searchValue}, '%'))
            </if>
        </where>
        order by di.create_time desc
    </select>
    
    <select id="selectDeviceInfoByDeviceId" parameterType="Long" resultMap="DeviceInfoResult">
        <include refid="selectDeviceInfoVo"/>
        where di.device_id = #{deviceId}
    </select>
    <select id="checkDeviceNameUnique" resultType="java.lang.Integer">
        select count(1) from device_info where device_name = #{deviceName}
        <if test="deviceId != null">and device_id != #{deviceId}</if>
    </select>
    <select id="checkDeviceNumberUnique" resultType="java.lang.Integer">
        select count(1) from device_info where device_number = #{deviceNumber}
        <if test="deviceId != null">and device_id != #{deviceId}</if>
    </select>
    <select id="checkDeviceSnUnique" resultType="java.lang.Integer">
        select count(1) from device_info where device_sn = #{deviceSn}
        <if test="deviceId != null">and device_id != #{deviceId}</if>
    </select>
    <select id="selectDeviceRecipeBinding" resultType="java.lang.String">
        select recipe_id from cook_recipe_bind_device where stall_id = #{stallId} and recipe_id is not null
        limit 1
        </select>

    <insert id="insertDeviceInfo" parameterType="com.bonus.canteen.core.device.domain.DeviceInfo" useGeneratedKeys="true" keyProperty="deviceId">
        insert into device_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceName != null and deviceName != ''">device_name,</if>
            <if test="parentId != null">parent_id,</if>
            <if test="parentType != null">parent_type,</if>
            <if test="tenantId != null">tenant_id,</if>
            <if test="onlineState != null">online_state,</if>
            <if test="versionCode != null">version_code,</if>
            <if test="versionName != null">version_name,</if>
            <if test="consumeMode != null">consume_mode,</if>
            <if test="communicateMode != null">communicate_mode,</if>
            <if test="deviceIp != null and deviceIp != ''">device_ip,</if>
            <if test="deviceMask != null and deviceMask != ''">device_mask,</if>
            <if test="deviceMac != null and deviceMac != ''">device_mac,</if>
            <if test="deviceGateway != null and deviceGateway != ''">device_gateway,</if>
            <if test="dnsPrimary != null and dnsPrimary != ''">dns_primary,</if>
            <if test="dnsBackup != null and dnsBackup != ''">dns_backup,</if>
            <if test="deviceSn != null">device_sn,</if>
            <if test="deviceNumber != null and deviceNumber != ''">device_number,</if>
            <if test="deviceModel != null and deviceModel != ''">device_model,</if>
            <if test="deviceType != null">device_type,</if>
            <if test="devicePwd != null">device_pwd,</if>
            <if test="deviceAddr != null">device_addr,</if>
            <if test="deviceKey != null">device_key,</if>
            <if test="deviceServiceIp != null">device_service_ip,</if>
            <if test="deviceServicePort != null">device_service_port,</if>
            <if test="ifQuotaLimit != null">if_quota_limit,</if>
            <if test="ifDiscount != null">if_discount,</if>
            <if test="ifTimeLimit != null">if_time_limit,</if>
            <if test="ifUseCallNum != null">if_use_call_num,</if>
            <if test="ifOffLinePay != null">if_off_line_pay,</if>
            <if test="ifUse != null">if_use,</if>
            <if test="imgUrl != null">img_url,</if>
            <if test="dataTransferStatus != null">data_transfer_status,</if>
            <if test="printStatus != null">print_status,</if>
            <if test="printKey != null">print_key,</if>
            <if test="deviceState != null">device_state,</if>
            <if test="deviceProtocol != null">device_protocol,</if>
            <if test="heartBeatTime != null">heart_beat_time,</if>
            <if test="lastUpdateTime != null">last_update_time,</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="deviceName != null and deviceName != ''">#{deviceName},</if>
            <if test="parentId != null">#{parentId},</if>
            <if test="parentType != null">#{parentType},</if>
            <if test="tenantId != null">#{tenantId},</if>
            <if test="onlineState != null">#{onlineState},</if>
            <if test="versionCode != null">#{versionCode},</if>
            <if test="versionName != null">#{versionName},</if>
            <if test="consumeMode != null">#{consumeMode},</if>
            <if test="communicateMode != null">#{communicateMode},</if>
            <if test="deviceIp != null and deviceIp != ''">#{deviceIp},</if>
            <if test="deviceMask != null and deviceMask != ''">#{deviceMask},</if>
            <if test="deviceMac != null and deviceMac != ''">#{deviceMac},</if>
            <if test="deviceGateway != null and deviceGateway != ''">#{deviceGateway},</if>
            <if test="dnsPrimary != null and dnsPrimary != ''">#{dnsPrimary},</if>
            <if test="dnsBackup != null and dnsBackup != ''">#{dnsBackup},</if>
            <if test="deviceSn != null">#{deviceSn},</if>
            <if test="deviceNumber != null and deviceNumber != ''">#{deviceNumber},</if>
            <if test="deviceModel != null and deviceModel != ''">#{deviceModel},</if>
            <if test="deviceType != null">#{deviceType},</if>
            <if test="devicePwd != null">#{devicePwd},</if>
            <if test="deviceAddr != null">#{deviceAddr},</if>
            <if test="deviceKey != null">#{deviceKey},</if>
            <if test="deviceServiceIp != null">#{deviceServiceIp},</if>
            <if test="deviceServicePort != null">#{deviceServicePort},</if>
            <if test="ifQuotaLimit != null">#{ifQuotaLimit},</if>
            <if test="ifDiscount != null">#{ifDiscount},</if>
            <if test="ifTimeLimit != null">#{ifTimeLimit},</if>
            <if test="ifUseCallNum != null">#{ifUseCallNum},</if>
            <if test="ifOffLinePay != null">#{ifOffLinePay},</if>
            <if test="ifUse != null">#{ifUse},</if>
            <if test="imgUrl != null">#{imgUrl},</if>
            <if test="dataTransferStatus != null">#{dataTransferStatus},</if>
            <if test="printStatus != null">#{printStatus},</if>
            <if test="printKey != null">#{printKey},</if>
            <if test="deviceState != null">#{deviceState},</if>
            <if test="deviceProtocol != null">#{deviceProtocol},</if>
            <if test="heartBeatTime != null">#{heartBeatTime},</if>
            <if test="lastUpdateTime != null">#{lastUpdateTime},</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>
    <insert id="insertDeviceBinding">
        insert into device_bind(device_id, area_id, canteen_id, stall_id,create_by, create_time)
        values (#{deviceId}, #{areaId}, #{canteenId}, #{stallId},
                #{createBy}, #{createTime})
    </insert>
    <insert id="insertDeviceRecipeBinding">
        insert into cook_recipe_bind_device(device_id, canteen_id, stall_id,recipe_id,create_by, create_time)
        values (#{deviceId}, #{canteenId}, #{stallId},#{recipeId},
                #{createBy}, #{createTime})
    </insert>

    <update id="updateDeviceInfo" parameterType="com.bonus.canteen.core.device.domain.DeviceInfo">
        update device_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceName != null and deviceName != ''">device_name = #{deviceName},</if>
            <if test="parentId != null">parent_id = #{parentId},</if>
            <if test="parentType != null">parent_type = #{parentType},</if>
            <if test="tenantId != null">tenant_id = #{tenantId},</if>
            <if test="onlineState != null">online_state = #{onlineState},</if>
            <if test="versionCode != null">version_code = #{versionCode},</if>
            <if test="versionName != null">version_name = #{versionName},</if>
            <if test="consumeMode != null">consume_mode = #{consumeMode},</if>
            <if test="communicateMode != null">communicate_mode = #{communicateMode},</if>
            <if test="deviceIp != null and deviceIp != ''">device_ip = #{deviceIp},</if>
            <if test="deviceMask != null and deviceMask != ''">device_mask = #{deviceMask},</if>
            <if test="deviceMac != null and deviceMac != ''">device_mac = #{deviceMac},</if>
            <if test="deviceGateway != null and deviceGateway != ''">device_gateway = #{deviceGateway},</if>
            <if test="dnsPrimary != null and dnsPrimary != ''">dns_primary = #{dnsPrimary},</if>
            <if test="dnsBackup != null and dnsBackup != ''">dns_backup = #{dnsBackup},</if>
            <if test="deviceSn != null">device_sn = #{deviceSn},</if>
            <if test="deviceNumber != null and deviceNumber != ''">device_number = #{deviceNumber},</if>
            <if test="deviceModel != null and deviceModel != ''">device_model = #{deviceModel},</if>
            <if test="deviceType != null">device_type = #{deviceType},</if>
            <if test="devicePwd != null">device_pwd = #{devicePwd},</if>
            <if test="deviceAddr != null">device_addr = #{deviceAddr},</if>
            <if test="deviceKey != null">device_key = #{deviceKey},</if>
            <if test="deviceServiceIp != null">device_service_ip = #{deviceServiceIp},</if>
            <if test="deviceServicePort != null">device_service_port = #{deviceServicePort},</if>
            <if test="ifQuotaLimit != null">if_quota_limit = #{ifQuotaLimit},</if>
            <if test="ifDiscount != null">if_discount = #{ifDiscount},</if>
            <if test="ifTimeLimit != null">if_time_limit = #{ifTimeLimit},</if>
            <if test="ifUseCallNum != null">if_use_call_num = #{ifUseCallNum},</if>
            <if test="ifOffLinePay != null">if_off_line_pay = #{ifOffLinePay},</if>
            <if test="ifUse != null">if_use = #{ifUse},</if>
            <if test="imgUrl != null">img_url = #{imgUrl},</if>
            <if test="dataTransferStatus != null">data_transfer_status = #{dataTransferStatus},</if>
            <if test="printStatus != null">print_status = #{printStatus},</if>
            <if test="printKey != null">print_key = #{printKey},</if>
            <if test="deviceState != null">device_state = #{deviceState},</if>
            <if test="deviceProtocol != null">device_protocol = #{deviceProtocol},</if>
            <if test="heartBeatTime != null">heart_beat_time = #{heartBeatTime},</if>
            <if test="lastUpdateTime != null">last_update_time = #{lastUpdateTime},</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 device_id = #{deviceId}
    </update>
    <update id="updateDeviceBinding">
        update device_bind
        <trim prefix="SET" suffixOverrides=",">
            <if test="areaId != null and areaId != ''">area_id = #{areaId},</if>
            <if test="canteenId != null and canteenId != ''">canteen_id = #{canteenId},</if>
            <if test="stallId != null and stallId != ''">stall_id = #{stallId},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where device_id = #{deviceId}
    </update>
    <update id="updateDeviceRecipeBinding">
        update cook_recipe_bind_device
        <trim prefix="SET" suffixOverrides=",">
            <if test="canteenId != null and canteenId != ''">canteen_id = #{canteenId},</if>
            <if test="stallId != null and stallId != ''">stall_id = #{stallId},</if>
            <if test="recipeId != null and recipeId != ''">recipe_id = #{recipeId},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where device_id = #{deviceId}
    </update>
    <update id="updateTimeBySn">
        update device_info
        set last_update_time = unix_timestamp()
        where device_sn = #{sn}
    </update>

    <delete id="deleteDeviceInfoByDeviceId" parameterType="Long">
        delete from device_info where device_id = #{deviceId}
    </delete>

    <delete id="deleteDeviceInfoByDeviceIds" parameterType="String">
        delete from device_info where device_id in 
        <foreach item="deviceId" collection="deviceIds" open="(" separator="," close=")">
            #{deviceId}
        </foreach>
    </delete>
    <delete id="deleteDeviceBinding">
        delete from device_bind where device_id in
        <foreach item="deviceId" collection="deviceIds" open="(" separator="," close=")">
            #{deviceId}
        </foreach>
    </delete>
    <delete id="deleteDeviceRecipeBinding">
        delete from cook_recipe_bind_device where device_id in
        <foreach item="deviceId" collection="deviceIds" open="(" separator="," close=")">
            #{deviceId}
        </foreach>
    </delete>
</mapper>