<?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.ss.device.mapper.DeviceMapper">

    <resultMap type="DeviceVO" id="SmDeviceResult" autoMapping="true">
        <result property="totalElectriQuantity"    column="total_electri_quantity" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"   />
        <result property="initReading"    column="init_reading"  typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"   />
        <result property="modelTags"    column="model_tags" typeHandler="com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler"    />
    </resultMap>

    <sql id="selectVo">
        select
            sd.device_id,
            sd.device_no,
            sd.store_id,
            sd.device_name,
            sd.model_id,
            sd.mac,
            sd.mac2,
            sd.activation_time,
            sd.total_electri_quantity,
            sd.online_status,
            sd.online_status1,
            sd.online_status2,
            sd.status,
            sd.real_time_power,
            sd.electricity,
            sd.voltage,
            sd.nick_name,
            sd.create_by,
            sd.create_time,
            sd.update_by,
            sd.update_time,
            sd.remark,
            sd.outage_way,
            sd.wifi,
            sd.deleted,
            sd.last_pull_time,
            sd.init_reading,
            sd.power_status,
            sd.expire_time,
            sd.custom_picture,
            sd.service_rate,
            sd.remain_time,
            sd.user_id,
            sd.service_type,
            sd.start_time,
            sd.start_unit,
            sd.start_price,
            sd.over_time,
            sd.over_unit,
            sd.over_price,
            sd.rent_time,
            sd.lock_user_id,
            sd.surplus_ele,
            sd.limit_recharge_time,
            sd.limit_recharge_reason,
            sd.last_online_time,
            sd.agent_service_rate,
            sd.agent_id,
            sd.service_mode,
            sd.month_fee,
            sd.last_recover_time,
            sd.version,
            sm.model_name as model,
            sm.picture as picture,
            sm.tags as  model_tags,
            sm.product_id as model_product_id,
            ss.name as store_name,
            ss.contact_name as store_contact_name,
            ss.contact_mobile as store_contact_mobile,
            su.phonenumber as user_mobile,
            if(su.is_real, su.real_name, su.user_name) as user_name,
            su.service_rate as user_service_rate,
            su.show_bill_mobile as mch_show_bill_mobile,
            if(sua.is_real, sua.real_name, sua.user_name) as agent_name,
            sua.phonenumber as agent_mobile,
            sua.agent_service_rate as agent_user_service_rate,
            sua.agent_allow_mch_switch as agent_allow_mch_switch
        <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        from sm_device sd
            left join sm_model sm on sm.model_id = sd.model_id
            left join sm_store ss on ss.store_id = sd.store_id
            left join sm_user su on su.user_id = sd.user_id
            left join sm_user sua on sua.user_id = sd.agent_id
    </sql>

    <sql id="searchCondition">
        <if test="query.storeName != null  and query.storeName != ''"> and ss.name like concat('%', #{query.storeName}, '%')</if>
        <if test="query.serviceType != null  and query.serviceType != ''"> and sd.service_type = #{query.serviceType}</if>
        <if test="query.deviceName != null  and query.deviceName != ''"> and sd.device_name like concat('%', #{query.deviceName}, '%')</if>
        <if test="query.model != null  and query.model != ''"> and sm.model_name like concat('%', #{query.model}, '%')</if>
        <if test="query.mac != null  and query.mac != ''"> and sd.mac = #{query.mac}</if>
        <if test="query.mac2 != null  and query.mac2 != ''"> and sd.mac2 = #{query.mac2}</if>
        <if test="query.anyMac != null  and query.anyMac != ''">
            and (
                sd.mac = #{query.anyMac} or sd.mac2 = #{query.anyMac}
            )
        </if>
        <if test="query.likeAnyMac != null  and query.likeAnyMac != ''">
            and (
                sd.mac like concat('%', #{query.likeAnyMac}, '%') or sd.mac2 like concat('%', #{query.likeAnyMac}, '%')
            )
        </if>
        <if test="query.onlineStatus != null  and query.onlineStatus != ''"> and sd.online_status = #{query.onlineStatus}</if>
        <if test="query.onlineStatus1 != null  and query.onlineStatus1 != ''"> and sd.online_status1 = #{query.onlineStatus1}</if>
        <if test="query.onlineStatus2 != null  and query.onlineStatus2 != ''"> and sd.online_status2 = #{query.onlineStatus2}</if>
        <if test="query.status != null  and query.status != ''"> and sd.status = #{query.status}</if>
        <if test="query.userName != null  and query.userName != ''"> and if(su.is_real, su.real_name, su.user_name) like concat('%', #{query.userName}, '%')</if>
        <if test="query.nickName != null  and query.nickName != ''"> and sd.nick_name like concat('%', #{query.nickName}, '%')</if>
        <if test="query.userId != null"> and sd.user_id = #{query.userId}</if>
        <if test="query.storeId != null"> and sd.store_id = #{query.storeId}</if>
        <if test="query.deviceId != null"> and sd.device_id = #{query.deviceId}</if>
        <if test="query.startRentTime != null"> and sd.rent_time >= #{query.startRentTime}</if>
        <if test="query.endRentTime != null"> and sd.device_id &lt;= #{query.endRentTime}</if>
        <if test="query.deviceNo != null and query.deviceNo != ''"> and sd.device_no like concat('%', #{query.deviceNo}, '%')</if>
        <if test="query.eqDeviceNo != null and query.eqDeviceNo != ''"> and sd.device_no = #{query.eqDeviceNo}</if>
        <if test="query.lockUserId != null"> and sd.lock_user_id = #{query.lockUserId}</if>
        <if test="query.agentId != null "> and agent_id = #{query.agentId}</if>
        <if test="query.agentName != null  and query.agentName != ''"> and if(sua.is_real, sua.real_name, sua.user_name) like concat('%', #{query.agentName}, '%')</if>
        <if test="query.serviceMode != null  and query.serviceMode != ''"> and sd.service_mode = #{query.serviceMode}</if>
        <if test="query.version != null  and query.version != ''"> and sd.version like concat('%', #{query.version}, '%')</if>
        <if test="query.modelTag != null  and query.modelTag != ''"> and find_in_set(#{query.modelTag}, sm.tags)</if>
        <if test="query.excludeDeviceId != null"> and sd.device_id != #{query.excludeDeviceId}</if>
        <if test="query.inStaff != null">
            and sd.store_id
            <if test="!query.inStaff">not</if>
            in (<include refid="selectStaffViewStoreIds"/>)
        </if>
        <if test="query.keyword != null and query.keyword != ''">
            and (
                sd.device_name like concat('%', #{query.keyword}, '%') or
                sd.device_no like concat('%', #{query.keyword}, '%') or
                su.phonenumber like concat('%', #{query.keyword}, '%') or
                su.user_name like concat('%', #{query.keyword}, '%')
            )
        </if>
        <if test="query.isArrears != null">
            <if test="query.isArrears">
                and (sd.rent_time is null or sd.rent_time &lt;= now())
            </if>
            <if test="!query.isArrears">
                and (sd.rent_time is not null and sd.rent_time > now())
            </if>
        </if>
        <if test="query.deviceIds != null and query.deviceIds.size() > 0">
            and sd.device_id in
            <foreach collection="query.deviceIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.deviceNos != null and query.deviceNos.size() > 0">
            and sd.device_no in
            <foreach collection="query.deviceNos" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.isBind != null">
            <if test="query.isBind">
                and sd.user_id is not null
            </if>
            <if test="!query.isBind">
                and sd.user_id is null
            </if>
        </if>
        <if test="query.storeIds != null and query.storeIds.size() > 0">
            and sd.store_id in
            <foreach collection="query.storeIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.userIds != null and query.userIds.size() > 0">
            and sd.user_id in
            <foreach collection="query.userIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.modelIds != null and query.modelIds.size() > 0">
            and sd.model_id in
            <foreach collection="query.modelIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.macList != null and query.macList.size() > 0">
            and sd.mac in
            <foreach collection="query.macList" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.deleted == null">and sd.deleted = false</if>
        <if test="query.deleted != null">and sd.deleted = #{query.deleted}</if>
    </sql>


    <!--查询员工可查看的店铺ID列表-->
    <sql id="selectStaffViewStoreIds">
        select distinct sss.store_id
        from ss_store_staff sss
        <where>
            <if test="query.staffUserId != null">and sss.user_id = #{query.staffUserId}</if>
            <if test="query.staffPermission != null and query.staffPermission != ''">
                and find_in_set(#{query.staffPermission}, sss.permissions)
            </if>
        </where>
    </sql>


    <select id="selectSmDeviceList" parameterType="DeviceQuery" resultMap="SmDeviceResult">
        <include refid="selectVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSmDeviceByDeviceId" parameterType="Long" resultMap="SmDeviceResult">
        <include refid="selectVo"/>
        where sd.device_id = #{deviceId} and sd.deleted = false
    </select>

    <select id="selectByDeviceNo" resultMap="SmDeviceResult">
        <include refid="selectVo"/>
        where sd.device_no = #{deviceNo} and sd.deleted = false
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(sd.device_id)
        from sm_device sd
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSimpleList" resultMap="SmDeviceResult">
        select sd.*
        from sm_device sd
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSimpleSmDeviceByMac" resultMap="SmDeviceResult">
        select sd.*
        from sm_device sd
        where sd.mac = #{mac} and sd.deleted = false
    </select>

    <select id="selectCountByStoreIds" resultType="DeviceCountVO">
        select
            sd.store_id,
            count(sd.device_id)  as count
        from sm_device sd
        where sd.store_id in
        <foreach collection="storeIds" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
        and sd.deleted = false
        group by sd.store_id
    </select>

    <resultMap id="DeviceCount" type="DeviceCountVO" autoMapping="true">
        <result property="count" column="count" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullIntegerTyperHandler"/>
    </resultMap>

    <select id="selectCommonCount" resultMap="DeviceCount">
        <trim prefix="select" suffixOverrides=",">
            <if test="query.groupBy != null">
                <if test="query.groupByTable == null">
                    sd.${query.groupBy},
                </if>
                <if test="query.groupByTable != null">
                    ${query.groupByTable}.${query.groupBy},
                </if>
            </if>
            count(sd.device_id) as `count`
        </trim>
        from sm_device sd
            left join sm_store ss on ss.store_id = sd.store_id
            left join sm_user su on su.user_id = sd.user_id
        <where>
            <include refid="searchCondition"/>
        </where>
        <if test="query.groupBy != null">
            <if test="query.groupByTable == null">
                group by sd.${query.groupBy}
            </if>
            <if test="query.groupByTable != null">
                group by ${query.groupByTable}.${query.groupBy}
            </if>
        </if>
    </select>

    <select id="selectSimpleSmDeviceBySn" resultMap="SmDeviceResult">
        select sd.*
        from sm_device sd
        where sd.device_no = #{deviceNo} and sd.deleted = false
    </select>

    <select id="selectMacSnList" resultType="DeviceMacSnVO">
        select
            sd.mac,
            sd.mac2,
            sd.device_no as sn
        from sm_device sd
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectOne" resultMap="SmDeviceResult">
        <include refid="selectVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        limit 1
    </select>

    <select id="selectByAnyMac" resultMap="SmDeviceResult">
        <include refid="selectVo"/>
        where sd.deleted = false and (sd.mac = #{mac} or sd.mac2 = #{mac})
    </select>

    <select id="selectMaxAgentServiceRate" resultType="java.math.BigDecimal">
        select max(sd.agent_service_rate)
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <insert id="insertSmDevice" parameterType="Device" useGeneratedKeys="true" keyProperty="deviceId">
        <selectKey resultType="Long" order="AFTER" keyProperty="deviceId">
            select LAST_INSERT_ID()
        </selectKey>
        insert into sm_device
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="storeId != null">store_id,</if>
            <if test="deviceName != null">device_name,</if>
            <if test="modelId != null">model_id,</if>
            <if test="mac != null">mac,</if>
            <if test="mac2 != null">mac2,</if>
            <if test="activationTime != null">activation_time,</if>
            <if test="totalElectriQuantity != null">total_electri_quantity,</if>
            <if test="onlineStatus != null">online_status,</if>
            <if test="status != null">`status`,</if>
            <if test="realTimePower != null">real_time_power,</if>
            <if test="electricity != null">electricity,</if>
            <if test="voltage != null">voltage,</if>
            <if test="nickName != null and nickName != ''">nick_name,</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>
            <if test="remark != null">remark,</if>
            <if test="outageWay != null">outage_way,</if>
            <if test="wifi != null">wifi,</if>
            <if test="deleted != null">deleted,</if>
            <if test="lastPullTime != null">last_pull_time,</if>
            <if test="initReading != null">init_reading,</if>
            <if test="powerStatus != null">power_status,</if>
            <if test="deviceNo != null">device_no,</if>
            <if test="customPicture != null">custom_picture,</if>
            <if test="serviceRate != null">service_rate,</if>
            <if test="remainTime != null">remain_time,</if>
            <if test="userId != null">user_id,</if>
            <if test="serviceType != null">service_type,</if>
            <if test="startTime != null">start_time,</if>
            <if test="startUnit != null">start_unit,</if>
            <if test="startPrice != null">start_price,</if>
            <if test="overTime != null">over_time,</if>
            <if test="overUnit != null">over_unit,</if>
            <if test="overPrice != null">over_price,</if>
            <if test="rentTime != null">rent_time,</if>
            <if test="lockUserId != null">lock_user_id,</if>
            <if test="surplusEle != null">surplus_ele,</if>
            <if test="limitRechargeTime != null">limit_recharge_time,</if>
            <if test="limitRechargeReason != null">limit_recharge_reason,</if>
            <if test="lastOnlineTime != null">last_online_time,</if>
            <if test="agentServiceRate != null">agent_service_rate,</if>
            <if test="agentId != null">agent_id,</if>
            <if test="serviceMode != null">service_mode,</if>
            <if test="monthFee != null">month_fee,</if>
            <if test="lastRecoverTime != null">last_recover_time,</if>
            <if test="onlineStatus1 != null">online_status1,</if>
            <if test="onlineStatus2 != null">online_status2,</if>
            <if test="version != null">version,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="storeId != null">#{storeId},</if>
            <if test="deviceName != null">#{deviceName},</if>
            <if test="modelId != null">#{modelId},</if>
            <if test="mac != null">#{mac},</if>
            <if test="mac2 != null">#{mac2},</if>
            <if test="activationTime != null">#{activationTime},</if>
            <if test="totalElectriQuantity != null">#{totalElectriQuantity},</if>
            <if test="onlineStatus != null">#{onlineStatus},</if>
            <if test="status != null">#{status},</if>
            <if test="realTimePower != null">#{realTimePower},</if>
            <if test="electricity != null">#{electricity},</if>
            <if test="voltage != null">#{voltage},</if>
            <if test="nickName != null and nickName != ''">#{nickName},</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>
            <if test="remark != null">#{remark},</if>
            <if test="outageWay != null">#{outageWay},</if>
            <if test="wifi != null">#{wifi},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="lastPullTime != null">#{lastPullTime},</if>
            <if test="initReading != null">#{initReading},</if>
            <if test="powerStatus != null">#{powerStatus},</if>
            <if test="deviceNo != null">#{deviceNo},</if>
            <if test="customPicture != null">#{customPicture},</if>
            <if test="serviceRate != null">#{serviceRate},</if>
            <if test="remainTime != null">#{remainTime},</if>
            <if test="userId != null">#{userId},</if>
            <if test="serviceType != null">#{serviceType},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="startUnit != null">#{startUnit},</if>
            <if test="startPrice != null">#{startPrice},</if>
            <if test="overTime != null">#{overTime},</if>
            <if test="overUnit != null">#{overUnit},</if>
            <if test="overPrice != null">#{overPrice},</if>
            <if test="rentTime != null">#{rentTime},</if>
            <if test="lockUserId != null">#{lockUserId},</if>
            <if test="surplusEle != null">#{surplusEle},</if>
            <if test="limitRechargeTime != null">#{limitRechargeTime},</if>
            <if test="limitRechargeReason != null">#{limitRechargeReason},</if>
            <if test="lastOnlineTime != null">#{lastOnlineTime},</if>
            <if test="agentServiceRate != null">#{agentServiceRate},</if>
            <if test="agentId != null">#{agentId},</if>
            <if test="serviceMode != null">#{serviceMode},</if>
            <if test="monthFee != null">#{monthFee},</if>
            <if test="lastRecoverTime != null">#{lastRecoverTime},</if>
            <if test="onlineStatus1 != null">#{onlineStatus1},</if>
            <if test="onlineStatus2 != null">#{onlineStatus2},</if>
            <if test="version != null">#{version},</if>
         </trim>
    </insert>

    <update id="addTime">
        update sm_device
        set expire_time = IF (
            (expire_time is null) or (now() > expire_time),
            DATE_ADD(now(),INTERVAL #{num} SECOND),
            DATE_ADD(expire_time,INTERVAL #{num} SECOND)
        )
        where device_id = #{deviceId} and deleted = false
    </update>

    <update id="bindSn">
        update sm_device
        set device_no = if(device_id = #{deviceId}, #{sn}, null)
        where device_no = #{sn} and deleted = false
    </update>

    <update id="updateSmDevice" parameterType="Device">
        update sm_device
        <trim prefix="SET" suffixOverrides=",">
            <if test="storeId != null">store_id = #{storeId},</if>
            <if test="deviceName != null">device_name = #{deviceName},</if>
            <if test="modelId != null">model_id = #{modelId},</if>
            <if test="mac != null">mac = #{mac},</if>
            <if test="mac2 != null">mac2 = #{mac2},</if>
            <if test="deviceNo != null">device_no = #{deviceNo},</if>
            <if test="activationTime != null">activation_time = #{activationTime},</if>
            <if test="totalElectriQuantity != null">total_electri_quantity = #{totalElectriQuantity},</if>
            <if test="onlineStatus != null">online_status = #{onlineStatus},</if>
            <if test="status != null">`status` = #{status},</if>
            <if test="realTimePower != null">real_time_power = #{realTimePower},</if>
            <if test="electricity != null">electricity = #{electricity},</if>
            <if test="voltage != null">voltage = #{voltage},</if>
            <if test="nickName != null and nickName != ''">nick_name = #{nickName},</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>
            <if test="remark != null">remark = #{remark},</if>
            <if test="outageWay != null">outage_way = #{outageWay},</if>
            <if test="wifi != null">wifi = #{wifi},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
            <if test="lastPullTime != null">last_pull_time = #{lastPullTime},</if>
            <if test="initReading != null">init_reading = #{initReading},</if>
            <if test="powerStatus != null">power_status = #{powerStatus},</if>
            <if test="expireTime != null">expire_time = #{expireTime},</if>
            <if test="customPicture != null">custom_picture = #{customPicture},</if>
            <if test="serviceRate != null">service_rate = #{serviceRate},</if>
            <if test="remainTime != null">remain_time = #{remainTime},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="serviceType != null">service_type = #{serviceType},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="startUnit != null">start_unit = #{startUnit},</if>
            <if test="startPrice != null">start_price = #{startPrice},</if>
            <if test="overTime != null">over_time = #{overTime},</if>
            <if test="overUnit != null">over_unit = #{overUnit},</if>
            <if test="overPrice != null">over_price = #{overPrice},</if>
            <if test="rentTime != null">rent_time = #{rentTime},</if>
            <if test="lockUserId != null">lock_user_id = #{lockUserId},</if>
            <if test="surplusEle != null">surplus_ele = #{surplusEle},</if>
            <if test="limitRechargeTime != null">limit_recharge_time = #{limitRechargeTime},</if>
            <if test="limitRechargeReason != null">limit_recharge_reason = #{limitRechargeReason},</if>
            <if test="lastOnlineTime != null">last_online_time = #{lastOnlineTime},</if>
            <if test="agentServiceRate != null">agent_service_rate = #{agentServiceRate},</if>
            <if test="agentId != null">agent_id = #{agentId},</if>
            <if test="serviceMode != null">service_mode = #{serviceMode},</if>
            <if test="monthFee != null">month_fee = #{monthFee},</if>
            <if test="lastRecoverTime != null">last_recover_time = #{lastRecoverTime},</if>
            <if test="onlineStatus1 != null">online_status1 = #{onlineStatus1},</if>
            <if test="onlineStatus2 != null">online_status2 = #{onlineStatus2},</if>
            <if test="version != null">version = #{version},</if>
        </trim>
        where device_id = #{deviceId}
    </update>

    <update id="logicDel">
        update sm_device
        set deleted = true
        where device_id in
        <foreach collection="deviceIds" separator="," open="(" item="item" close=")">
            #{item}
        </foreach>
    </update>

    <update id="bind">
        update sm_device
        set store_id = #{storeId},
            user_id = #{userId},
            activation_time = now()
        where device_id = #{deviceId} and user_id is null
    </update>

    <update id="unbindStore">
        update sm_device
        set store_id = null,
            user_id = null,
            device_name = '未命名',
            activation_time = null,
            custom_picture = null
        where device_id = #{deviceId}
    </update>

    <update id="updateServiceRateByStores">
        update sm_device
        set service_rate = #{serviceRate}
        where store_id in
        <foreach collection="storeIds" separator="," open="(" item="item" close=")">
            #{item}
        </foreach>
    </update>

    <update id="updateSmDeviceByIot">
        update sm_device
        <trim prefix="SET" suffixOverrides=",">
            <if test="totalElectriQuantity != null">total_electri_quantity = #{totalElectriQuantity},</if>
            <if test="onlineStatus != null">online_status = #{onlineStatus},</if>
            <if test="realTimePower != null">real_time_power = #{realTimePower},</if>
            <if test="electricity != null">electricity = #{electricity},</if>
            <if test="voltage != null">voltage = #{voltage},</if>
            <if test="outageWay != null">outage_way = #{outageWay},</if>
            <if test="wifi != null">wifi = #{wifi},</if>
            <if test="lastPullTime != null">last_pull_time = #{lastPullTime},</if>
            <if test="powerStatus != null">power_status = #{powerStatus},</if>
            <if test="remainTime != null">remain_time = #{remainTime},</if>
            <if test="serviceType != null">service_type = #{serviceType},</if>
        </trim>
        where mac = #{mac}
    </update>

    <update id="changeTimeoutStatus">
        update sm_device
        set status = '1'
        where expire_time &lt;= now() and status = '2'
        <if test="deviceId != null">
            and device_id = #{deviceId}
        </if>
    </update>

    <!--批量更新在线状态-->
    <update id="batchUpdateOnlineStatus">
        update sm_device
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="online_status = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.onlineStatus != null">
                        WHEN #{item.deviceId} THEN #{item.onlineStatus}
                    </when>
                    <otherwise>
                        WHEN #{item.deviceId} THEN `online_status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="last_online_time = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.lastOnlineTime != null">
                        WHEN #{item.deviceId} THEN #{item.lastOnlineTime}
                    </when>
                    <otherwise>
                        WHEN #{item.deviceId} THEN `last_online_time`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where device_id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.deviceId}
        </foreach>
    </update>

    <update id="batchUpdateModel">
        update sm_device
        set model_id = #{modelId}
        where device_id in
        <foreach collection="deviceIds" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </update>

    <update id="updateServiceRate">
        update sm_device
        set service_rate = #{serviceRate}
        where device_id = #{deviceId}
    </update>

    <update id="renewalRentTime">
        update sm_device
        set rent_time = if(
                rent_time is null or now() > rent_time,
                date_add(now(), interval #{seconds} second),
                date_add(rent_time, interval #{seconds} second)
            )
        where device_id = #{deviceId}
    </update>

    <update id="unbindAgent">
        update sm_device
        set agent_id = null,
            agent_service_rate = null,
            service_mode = '1'
        where device_id = #{deviceId} and agent_id is not null
    </update>

    <update id="bindAgent">
        update sm_device
        set agent_id = #{agentId},
            agent_service_rate = #{agentServiceRate},
            service_mode = '2'
        where device_id = #{deviceId} and agent_id is null
    </update>

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

    <delete id="deleteSmDeviceByDeviceIds" parameterType="String">
        delete from sm_device where device_id in
        <foreach item="deviceId" collection="array" open="(" separator="," close=")">
            #{deviceId}
        </foreach>
    </delete>


</mapper>
