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

    <resultMap type="DeviceVO" id="DeviceResult" autoMapping="true">
        <result property="deviceId"    column="device_id"    />
        <result property="deviceNo"    column="device_no"    />
        <result property="boothId"    column="booth_id"    />
        <result property="userId"    column="user_id"    />
        <result property="modelId"    column="model_id"    />
        <result property="deviceName"    column="device_name"    />
        <result property="mac"    column="mac"    />
        <result property="mac2"    column="mac2"    />
        <result property="totalElectriQuantity"    column="total_electri_quantity"    />
        <result property="onlineStatus"    column="online_status"    />
        <result property="status"    column="status"    />
        <result property="realTimePower"    column="real_time_power"    />
        <result property="electricity"    column="electricity"    />
        <result property="voltage"    column="voltage"    />
        <result property="createTime"    column="create_time"    />
        <result property="remark"    column="remark"    />
        <result property="set"    column="set"    />
        <result property="wifi"    column="wifi"    />
        <result property="deleted"    column="deleted"    />
        <result property="lastPullTime"    column="last_pull_time"    />
        <result property="initReading"    column="init_reading"    />
        <result property="powerStatus"    column="power_status"    />
        <result property="expireTime"    column="expire_time"    />
        <result property="customPicture"    column="custom_picture"    />
        <result property="remainTime"    column="remain_time"    />
        <result property="surplusEle"    column="surplus_ele"    />
        <result property="lastOnlineTime"    column="last_online_time"    />
        <result property="onlineStatus1"    column="online_status1"    />
        <result property="onlineStatus2"    column="online_status2"    />
        <result property="version"    column="version"    />
        <result property="expireEle"    column="expire_ele"    />
        <result property="lastInitReading"    column="last_init_reading"    />
        <result property="vxs"    column="vxs"    />
        <result property="imsi"    column="imsi"    />
        <result property="wxs"    column="wxs"    />
        <result property="duration"    column="duration"    />
        <result property="lastRecoverTime"    column="last_recover_time"    />
        <result property="modelTags"    column="model_tags" typeHandler="com.ruoyi.common.mybatis.typehandler.StringSplitListTypeHandler"   />
    </resultMap>

    <sql id="selectDeviceVo">
        select
            bd.device_id,
            bd.device_no,
            bd.booth_id,
            bd.user_id,
            bd.model_id,
            bd.device_name,
            bd.mac,
            bd.mac2,
            bd.total_electri_quantity,
            bd.online_status,
            bd.status,
            bd.real_time_power,
            bd.electricity,
            bd.voltage,
            bd.create_time,
            bd.remark,
            bd.set,
            bd.wifi,
            bd.deleted,
            bd.last_pull_time,
            bd.duration,
            bd.init_reading,
            bd.power_status,
            bd.expire_time,
            bd.custom_picture,
            bd.remain_time,
            bd.surplus_ele,
            bd.last_online_time,
            bd.online_status1,
            bd.online_status2,
            bd.version,
            bd.expire_ele,
            bd.last_init_reading,
            bd.vxs,
            bd.imsi,
            bd.wxs,
            bd.last_recover_time,
            bm.model_name as model_name,
            bm.model,
            bm.product_id,
            bm.tags as model_tags,
            su.user_name,
            bs.store_name,
            bs.store_id,
            bb.booth_no as position
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_device bd
        left join bst_model bm on bd.model_id = bm.model_id
        left join sys_user su on bd.user_id = su.user_id
        left join bst_booth bb on bd.booth_id = bb.booth_id
        left join bst_store bs on bd.store_id = bs.store_id
    </sql>

    <sql id="searchCondition">
        <if test="query.deviceNo != null  and query.deviceNo != ''"> and bd.device_no = #{query.deviceNo}</if>
        <if test="query.deviceId != null  and query.deviceId != ''"> and bd.device_id = #{query.deviceId}</if>
        <if test="query.storeId != null  and query.storeId != ''"> and bd.store_id = #{query.storeId}</if>
        <if test="query.boothId != null "> and bd.booth_id = #{query.boothId}</if>
        <if test="query.boothNo != null and query.boothNo !=''"> and bb.booth_no = #{query.boothNo}</if>
        <if test="query.userId != null "> and bd.user_id = #{query.userId}</if>
        <if test="query.modelId != null "> and bd.model_id = #{query.modelId}</if>
        <if test="query.deviceName != null  and query.deviceName != ''"> and bd.device_name like concat('%', #{query.deviceName}, '%')</if>
        <if test="query.storeName != null  and query.storeName != ''"> and bs.store_name like concat('%', #{query.storeName}, '%')</if>
        <if test="query.mac != null  and query.mac != ''"> and bd.mac = #{query.mac}</if>
        <if test="query.mac2 != null  and query.mac2 != ''"> and bd.mac2 = #{query.mac2}</if>
        <if test="query.totalElectriQuantity != null "> and bd.total_electri_quantity = #{query.totalElectriQuantity}</if>
        <if test="query.onlineStatus != null  and query.onlineStatus != ''"> and bd.online_status = #{query.onlineStatus}</if>
        <if test="query.status != null  and query.status != ''"> and bd.status = #{query.status}</if>
        <if test="query.realTimePower != null "> and bd.real_time_power = #{query.realTimePower}</if>
        <if test="query.electricity != null "> and bd.electricity = #{query.electricity}</if>
        <if test="query.voltage != null "> and bd.voltage = #{query.voltage}</if>
        <if test="query.set != null  and query.set != ''"> and bd.set = #{query.set}</if>
        <if test="query.wifi != null  and query.wifi != ''"> and bd.wifi = #{query.wifi}</if>
        <if test="query.deleted != null "> and bd.deleted = #{query.deleted}</if>
        <if test="query.deleted == null "> and bd.deleted = false</if>
        <if test="query.lastPullTime != null "> and bd.last_pull_time = #{query.lastPullTime}</if>
        <if test="query.initReading != null "> and bd.init_reading = #{query.initReading}</if>
        <if test="query.powerStatus != null  and query.powerStatus != ''"> and bd.power_status = #{query.powerStatus}</if>
        <if test="query.expireTime != null "> and bd.expire_time = #{query.expireTime}</if>
        <if test="query.customPicture != null  and query.customPicture != ''"> and bd.custom_picture = #{query.customPicture}</if>
        <if test="query.remainTime != null "> and bd.remain_time = #{query.remainTime}</if>
        <if test="query.surplusEle != null "> and bd.surplus_ele = #{query.surplusEle}</if>
        <if test="query.lastOnlineTime != null "> and bd.last_online_time = #{query.lastOnlineTime}</if>
        <if test="query.onlineStatus1 != null  and query.onlineStatus1 != ''"> and bd.online_status1 = #{query.onlineStatus1}</if>
        <if test="query.onlineStatus2 != null  and query.onlineStatus2 != ''"> and bd.online_status2 = #{query.onlineStatus2}</if>
        <if test="query.version != null  and query.version != ''"> and bd.version = #{query.version}</if>
        <if test="query.expireEle != null "> and bd.expire_ele = #{query.expireEle}</if>
        <if test="query.lastInitReading != null "> and bd.last_init_reading = #{query.lastInitReading}</if>
        <if test="query.vxs != null "> and bd.vxs = #{query.vxs}</if>
        <if test="query.imsi != null  and query.imsi != ''"> and bd.imsi = #{query.imsi}</if>
        <if test="query.wxs != null "> and bd.wxs = #{query.wxs}</if>
        <if test="query.isUnbound != null and query.isUnbound "> and bd.booth_id IS NULL</if>
        <if test="query.lastRecoverTime != null "> and bd.last_recover_time = #{query.lastRecoverTime}</if>
        ${@com.ruoyi.framework.util.DataScopeUtil@create(query.scope)
       .userSetAlias("bd.user_id")
       .storeAlias("bd.store_id",query.storePermissions)
       .build()
        }
        ${query.params.dataScope}
    </sql>

    <select id="selectDeviceList" parameterType="DeviceQuery" resultMap="DeviceResult">
        <include refid="selectDeviceVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectDeviceByDeviceId" parameterType="Long" resultMap="DeviceResult">
        <include refid="selectDeviceVo"/>
        where bd.device_id = #{deviceId} and bd.deleted = false
    </select>

    <!--checkUnique-->
    <select id="checkUnique" resultType="java.lang.Integer">
        SELECT COUNT(1)
        FROM bst_device
        <where>
            <trim prefix="(" suffix=")" prefixOverrides="OR">
                <if test="mac != null and mac != ''">
                    OR mac = #{mac}
                </if>
                <if test="mac2 != null and mac2 != ''">
                    OR mac2 = #{mac2}
                </if>
                <if test="deviceNo != null and deviceNo != ''">
                    OR device_no = #{deviceNo}
                </if>
            </trim>
            <if test="deviceId != null">
                AND device_id != #{deviceId}
            </if>
        </where>
    </select>
    <select id="selectByDeviceNo" resultType="com.ruoyi.bst.device.domain.DeviceVO">
        <include refid="selectDeviceVo"/>
        where bd.device_no = #{deviceNo} and bd.deleted = false
    </select>

    <select id="selectIds" resultType="java.lang.Long">
        select device_id
        from bst_device bd
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectOne" resultMap="DeviceResult">
        <include refid="selectDeviceVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        limit 1
    </select>

    <select id="selectCountByStoreIds" resultType="com.ruoyi.bst.device.domain.DeviceCountVO">
        select
        bs.store_id,
        count(bd.device_id) as count
        from
        <include refid="searchTables"/>
        where bd.store_id in
        <foreach collection="storeIds" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
        and bd.deleted = false
        group by bs.store_id
    </select>

    <select id="selectCommonCountMap" resultType="com.ruoyi.common.domain.vo.LongIntegerVO">
        select
        bs.store_id as `key`,
        count(bd.device_id) as `value`
        from
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>


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

    <select id="selectStatusCount" resultType="com.ruoyi.common.domain.vo.StringIntegerVO">
        select
        bd.status as `key`,
        count(bd.device_id) as `value`
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectOnlineStatusCount" resultType="com.ruoyi.common.domain.vo.StringIntegerVO">
        select
        bd.online_status as `key`,
        count(bd.device_id) as `value`
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <insert id="insertDevice" parameterType="Device" useGeneratedKeys="true" keyProperty="deviceId">
        <selectKey resultType="Long" order="AFTER" keyProperty="deviceId">
            select LAST_INSERT_ID()
        </selectKey>
        insert into bst_device
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceNo != null">device_no,</if>
            <if test="boothId != null">booth_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="modelId != null">model_id,</if>
            <if test="deviceName != null and deviceName != ''">device_name,</if>
            <if test="mac != null and mac != ''">mac,</if>
            <if test="mac2 != null">mac2,</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="createTime != null">create_time,</if>
            <if test="remark != null">remark,</if>
            <if test="set != null">set,</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 and powerStatus != ''">power_status,</if>
            <if test="expireTime != null">expire_time,</if>
            <if test="customPicture != null">custom_picture,</if>
            <if test="remainTime != null">remain_time,</if>
            <if test="surplusEle != null">surplus_ele,</if>
            <if test="lastOnlineTime != null">last_online_time,</if>
            <if test="onlineStatus1 != null and onlineStatus1 != ''">online_status1,</if>
            <if test="onlineStatus2 != null and onlineStatus2 != ''">online_status2,</if>
            <if test="version != null">version,</if>
            <if test="expireEle != null">expire_ele,</if>
            <if test="lastInitReading != null">last_init_reading,</if>
            <if test="vxs != null">vxs,</if>
            <if test="imsi != null">imsi,</if>
            <if test="wxs != null">wxs,</if>
            <if test="lastRecoverTime != null">last_recover_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deviceNo != null">#{deviceNo},</if>
            <if test="boothId != null">#{boothId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="modelId != null">#{modelId},</if>
            <if test="deviceName != null and deviceName != ''">#{deviceName},</if>
            <if test="mac != null and mac != ''">#{mac},</if>
            <if test="mac2 != null">#{mac2},</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="createTime != null">#{createTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="set != null">#{set},</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 and powerStatus != ''">#{powerStatus},</if>
            <if test="expireTime != null">#{expireTime},</if>
            <if test="customPicture != null">#{customPicture},</if>
            <if test="remainTime != null">#{remainTime},</if>
            <if test="surplusEle != null">#{surplusEle},</if>
            <if test="lastOnlineTime != null">#{lastOnlineTime},</if>
            <if test="onlineStatus1 != null and onlineStatus1 != ''">#{onlineStatus1},</if>
            <if test="onlineStatus2 != null and onlineStatus2 != ''">#{onlineStatus2},</if>
            <if test="version != null">#{version},</if>
            <if test="expireEle != null">#{expireEle},</if>
            <if test="lastInitReading != null">#{lastInitReading},</if>
            <if test="vxs != null">#{vxs},</if>
            <if test="imsi != null">#{imsi},</if>
            <if test="wxs != null">#{wxs},</if>
            <if test="lastRecoverTime != null">#{lastRecoverTime},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="Device" useGeneratedKeys="true" keyProperty="deviceId">
        insert into bst_device
        <trim prefix="(" suffix=")" suffixOverrides=",">
            device_no,
            booth_id,
            user_id,
            model_id,
            device_name,
            mac,
            mac2,
            total_electri_quantity,
            online_status,
            status,
            real_time_power,
            electricity,
            voltage,
            create_time,
            remark,
            set,
            wifi,
            deleted,
            last_pull_time,
            init_reading,
            power_status,
            expire_time,
            custom_picture,
            remain_time,
            surplus_ele,
            last_online_time,
            online_status1,
            online_status2,
            version,
            expire_ele,
            last_init_reading,
            vxs,
            imsi,
            wxs,
            last_recover_time,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.deviceNo != null ">#{i.deviceNo},</if>
                <if test="i.deviceNo == null ">default,</if>
                <if test="i.boothId != null ">#{i.boothId},</if>
                <if test="i.boothId == null ">default,</if>
                <if test="i.userId != null ">#{i.userId},</if>
                <if test="i.userId == null ">default,</if>
                <if test="i.modelId != null ">#{i.modelId},</if>
                <if test="i.modelId == null ">default,</if>
                <if test="i.deviceName != null  and i.deviceName != ''">#{i.deviceName},</if>
                <if test="i.deviceName == null  or i.deviceName == ''">default,</if>
                <if test="i.mac != null  and i.mac != ''">#{i.mac},</if>
                <if test="i.mac == null  or i.mac == ''">default,</if>
                <if test="i.mac2 != null ">#{i.mac2},</if>
                <if test="i.mac2 == null ">default,</if>
                <if test="i.totalElectriQuantity != null ">#{i.totalElectriQuantity},</if>
                <if test="i.totalElectriQuantity == null ">default,</if>
                <if test="i.onlineStatus != null ">#{i.onlineStatus},</if>
                <if test="i.onlineStatus == null ">default,</if>
                <if test="i.status != null ">#{i.status},</if>
                <if test="i.status == null ">default,</if>
                <if test="i.realTimePower != null ">#{i.realTimePower},</if>
                <if test="i.realTimePower == null ">default,</if>
                <if test="i.electricity != null ">#{i.electricity},</if>
                <if test="i.electricity == null ">default,</if>
                <if test="i.voltage != null ">#{i.voltage},</if>
                <if test="i.voltage == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
                <if test="i.remark != null ">#{i.remark},</if>
                <if test="i.remark == null ">default,</if>
                <if test="i.set != null ">#{i.set},</if>
                <if test="i.set == null ">default,</if>
                <if test="i.wifi != null ">#{i.wifi},</if>
                <if test="i.wifi == null ">default,</if>
                <if test="i.deleted != null ">#{i.deleted},</if>
                <if test="i.deleted == null ">default,</if>
                <if test="i.lastPullTime != null ">#{i.lastPullTime},</if>
                <if test="i.lastPullTime == null ">default,</if>
                <if test="i.initReading != null ">#{i.initReading},</if>
                <if test="i.initReading == null ">default,</if>
                <if test="i.powerStatus != null  and i.powerStatus != ''">#{i.powerStatus},</if>
                <if test="i.powerStatus == null  or i.powerStatus == ''">default,</if>
                <if test="i.expireTime != null ">#{i.expireTime},</if>
                <if test="i.expireTime == null ">default,</if>
                <if test="i.customPicture != null ">#{i.customPicture},</if>
                <if test="i.customPicture == null ">default,</if>
                <if test="i.remainTime != null ">#{i.remainTime},</if>
                <if test="i.remainTime == null ">default,</if>
                <if test="i.surplusEle != null ">#{i.surplusEle},</if>
                <if test="i.surplusEle == null ">default,</if>
                <if test="i.lastOnlineTime != null ">#{i.lastOnlineTime},</if>
                <if test="i.lastOnlineTime == null ">default,</if>
                <if test="i.onlineStatus1 != null  and i.onlineStatus1 != ''">#{i.onlineStatus1},</if>
                <if test="i.onlineStatus1 == null  or i.onlineStatus1 == ''">default,</if>
                <if test="i.onlineStatus2 != null  and i.onlineStatus2 != ''">#{i.onlineStatus2},</if>
                <if test="i.onlineStatus2 == null  or i.onlineStatus2 == ''">default,</if>
                <if test="i.version != null ">#{i.version},</if>
                <if test="i.version == null ">default,</if>
                <if test="i.expireEle != null ">#{i.expireEle},</if>
                <if test="i.expireEle == null ">default,</if>
                <if test="i.lastInitReading != null ">#{i.lastInitReading},</if>
                <if test="i.lastInitReading == null ">default,</if>
                <if test="i.vxs != null ">#{i.vxs},</if>
                <if test="i.vxs == null ">default,</if>
                <if test="i.imsi != null ">#{i.imsi},</if>
                <if test="i.imsi == null ">default,</if>
                <if test="i.wxs != null ">#{i.wxs},</if>
                <if test="i.wxs == null ">default,</if>
                <if test="i.lastRecoverTime != null ">#{i.lastRecoverTime},</if>
                <if test="i.lastRecoverTime == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_device
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="device_no = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.deviceNo != null ">
                        WHEN #{item.device_id} THEN #{item.deviceNo}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `device_no`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="booth_id = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.boothId != null ">
                        WHEN #{item.device_id} THEN #{item.boothId}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `booth_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="user_id = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userId != null ">
                        WHEN #{item.device_id} THEN #{item.userId}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `user_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="model_id = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.modelId != null ">
                        WHEN #{item.device_id} THEN #{item.modelId}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `model_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="device_name = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.deviceName != null  and item.deviceName != ''">
                        WHEN #{item.device_id} THEN #{item.deviceName}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `device_name`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="mac = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.mac != null  and item.mac != ''">
                        WHEN #{item.device_id} THEN #{item.mac}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `mac`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="mac2 = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.mac2 != null ">
                        WHEN #{item.device_id} THEN #{item.mac2}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `mac2`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="total_electri_quantity = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.totalElectriQuantity != null ">
                        WHEN #{item.device_id} THEN #{item.totalElectriQuantity}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `total_electri_quantity`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="online_status = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.onlineStatus != null ">
                        WHEN #{item.device_id} THEN #{item.onlineStatus}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `online_status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="status = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.status != null ">
                        WHEN #{item.device_id} THEN #{item.status}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="real_time_power = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.realTimePower != null ">
                        WHEN #{item.device_id} THEN #{item.realTimePower}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `real_time_power`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="electricity = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.electricity != null ">
                        WHEN #{item.device_id} THEN #{item.electricity}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `electricity`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="voltage = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.voltage != null ">
                        WHEN #{item.device_id} THEN #{item.voltage}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `voltage`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.device_id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="remark = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.remark != null ">
                        WHEN #{item.device_id} THEN #{item.remark}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `remark`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="set = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.set != null ">
                        WHEN #{item.device_id} THEN #{item.set}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `set`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="wifi = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.wifi != null ">
                        WHEN #{item.device_id} THEN #{item.wifi}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `wifi`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="deleted = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.deleted != null ">
                        WHEN #{item.device_id} THEN #{item.deleted}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `deleted`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="last_pull_time = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.lastPullTime != null ">
                        WHEN #{item.device_id} THEN #{item.lastPullTime}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `last_pull_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="init_reading = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.initReading != null ">
                        WHEN #{item.device_id} THEN #{item.initReading}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `init_reading`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="power_status = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.powerStatus != null  and item.powerStatus != ''">
                        WHEN #{item.device_id} THEN #{item.powerStatus}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `power_status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="expire_time = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.expireTime != null ">
                        WHEN #{item.device_id} THEN #{item.expireTime}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `expire_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="custom_picture = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.customPicture != null ">
                        WHEN #{item.device_id} THEN #{item.customPicture}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `custom_picture`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="remain_time = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.remainTime != null ">
                        WHEN #{item.device_id} THEN #{item.remainTime}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `remain_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="surplus_ele = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.surplusEle != null ">
                        WHEN #{item.device_id} THEN #{item.surplusEle}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `surplus_ele`
                    </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.device_id} THEN #{item.lastOnlineTime}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `last_online_time`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="online_status1 = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.onlineStatus1 != null  and item.onlineStatus1 != ''">
                        WHEN #{item.device_id} THEN #{item.onlineStatus1}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `online_status1`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="online_status2 = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.onlineStatus2 != null  and item.onlineStatus2 != ''">
                        WHEN #{item.device_id} THEN #{item.onlineStatus2}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `online_status2`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="version = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.version != null ">
                        WHEN #{item.device_id} THEN #{item.version}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `version`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="expire_ele = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.expireEle != null ">
                        WHEN #{item.device_id} THEN #{item.expireEle}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `expire_ele`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="last_init_reading = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.lastInitReading != null ">
                        WHEN #{item.device_id} THEN #{item.lastInitReading}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `last_init_reading`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="vxs = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.vxs != null ">
                        WHEN #{item.device_id} THEN #{item.vxs}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `vxs`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="imsi = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.imsi != null ">
                        WHEN #{item.device_id} THEN #{item.imsi}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `imsi`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="wxs = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.wxs != null ">
                        WHEN #{item.device_id} THEN #{item.wxs}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `wxs`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="last_recover_time = CASE device_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.lastRecoverTime != null ">
                        WHEN #{item.device_id} THEN #{item.lastRecoverTime}
                    </when>
                    <otherwise>
                        WHEN #{item.device_id} THEN `last_recover_time`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where device_id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.deviceId}
        </foreach>
    </update>

    <update id="updateDevice" parameterType="Device">
        update bst_device bd
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where device_id = #{data.deviceId}
    </update>

    <!--logicDel-->
    <update id="logicDel">
            update bst_device bd
            set bd.deleted = true
            where bd.device_id in
            <foreach item="id" collection="ids" open="(" separator="," close=")">
                #{id}
            </foreach>
            and bd.deleted = false
    </update>

    <!--updateOnlineStatusByMac-->
    <update id="updateOnlineStatusByMac">
        update sm_device
        set online_status1 = if(#{mac} = mac, #{status}, online_status1),
            online_status2 = if(#{mac} = mac2, #{status}, online_status2),
            online_status  = if(#{mac} = mac,
                if(#{status} = '1' or online_status2 = '1', '1', '0'),
                if(#{status} = '1' or online_status1 = '1', '1', '0')
            ),
            last_online_time = if(#{mac} = mac,
                if(#{status} = '1' or online_status2 = '1', now(), last_online_time),
                if(#{status} = '1' or online_status1 = '1', now(), last_online_time)
            )
        where mac = #{mac}
           or mac2 = #{mac}
    </update>

    <!--initTotalEle-->
    <update id="initTotalEle">
        update bst_device
        set init_reading = total_electri_quantity,
            last_init_reading = now()
        where device_id = #{deviceId}
    </update>

    <update id="bind">
        update bst_device
        set user_id = #{userId}
        where device_id = #{deviceId} and user_id is null
    </update>

    <update id="unbindMch">
        update bst_device
        set booth_id = null,
            user_id = null,
            device_name = '未命名',
            custom_picture = null
        where device_id = #{deviceId}
    </update>

    <update id="unbindStore">
        update bst_device
        set store_id = null
        where device_id = #{deviceId}
    </update>

    <update id="bindBooth">
        update bst_device bd
        set bd.booth_id = #{date.boothId},bd.store_id = #{date.storeId}
        <where>
            <choose>
                <when test="deviceId != null">
                    device_id = #{deviceId}
                </when>
                <when test="deviceNo != null and deviceNo != ''">
                    device_no = #{deviceNo}
                </when>
            </choose>
        </where>
    </update>

    <update id="unbindBooth">
        update bst_device
        set booth_id = null,store_id = null
        where device_id = #{deviceId}
    </update>

    <update id="setTime">
        update bst_device
        set duration = #{lightingTime}
        where device_id = #{deviceId} and deleted = false
    </update>

    <update id="updateByQuery">
        update bst_device bd
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <update id="changeStore">
        update bst_device bd
        set bd.store_id = #{storeId},bd.booth_id = null
        where bd.device_id = #{deviceId}
    </update>


    <sql id="updateColumns">
        <if test="data.deviceNo != null">bd.device_no = #{data.deviceNo},</if>
        <if test="data.boothId != null">bd.booth_id = #{data.boothId},</if>
        <if test="data.storeId != null">bd.store_id = #{data.storeId},</if>
        <if test="data.userId != null">bd.user_id = #{data.userId},</if>
        <if test="data.modelId != null">bd.model_id = #{data.modelId},</if>
        <if test="data.deviceName != null and data.deviceName != ''">bd.device_name = #{data.deviceName},</if>
        <if test="data.mac != null and data.mac != ''">bd.mac = #{data.mac},</if>
        <if test="data.mac2 != null">bd.mac2 = #{data.mac2},</if>
        <if test="data.totalElectriQuantity != null">bd.total_electri_quantity = #{data.totalElectriQuantity},</if>
        <if test="data.onlineStatus != null">bd.online_status = #{data.onlineStatus},</if>
        <if test="data.status != null">bd.status = #{data.status},</if>
        <if test="data.realTimePower != null">bd.real_time_power = #{data.realTimePower},</if>
        <if test="data.electricity != null">bd.electricity = #{data.electricity},</if>
        <if test="data.voltage != null">bd.voltage = #{data.voltage},</if>
        <if test="data.createTime != null">bd.create_time = #{data.createTime},</if>
        <if test="data.remark != null">bd.remark = #{data.remark},</if>
        <if test="data.set != null">bd.set = #{data.set},</if>
        <if test="data.wifi != null">bd.wifi = #{data.wifi},</if>
        <if test="data.deleted != null">bd.deleted = #{data.deleted},</if>
        <if test="data.deleted == null">bd.deleted = false,</if>
        <if test="data.lastPullTime != null">bd.last_pull_time = #{data.lastPullTime},</if>
        <if test="data.initReading != null">bd.init_reading = #{data.initReading},</if>
        <if test="data.powerStatus != null and data.powerStatus != ''">bd.power_status = #{data.powerStatus},</if>
        <if test="data.expireTime != null">bd.expire_time = #{data.expireTime},</if>
        <if test="data.customPicture != null">bd.custom_picture = #{data.customPicture},</if>
        <if test="data.remainTime != null">bd.remain_time = #{data.remainTime},</if>
        <if test="data.surplusEle != null">bd.surplus_ele = #{data.surplusEle},</if>
        <if test="data.lastOnlineTime != null">bd.last_online_time = #{data.lastOnlineTime},</if>
        <if test="data.onlineStatus1 != null and data.onlineStatus1 != ''">bd.online_status1 = #{data.onlineStatus1},</if>
        <if test="data.onlineStatus2 != null and data.onlineStatus2 != ''">bd.online_status2 = #{data.onlineStatus2},</if>
        <if test="data.version != null">bd.version = #{data.version},</if>
        <if test="data.expireEle != null">bd.expire_ele = #{data.expireEle},</if>
        <if test="data.lastInitReading != null">bd.last_init_reading = #{data.lastInitReading},</if>
        <if test="data.vxs != null">bd.vxs = #{data.vxs},</if>
        <if test="data.imsi != null">bd.imsi = #{data.imsi},</if>
        <if test="data.duration != null">bd.duration = #{data.duration},</if>
        <if test="data.wxs != null">bd.wxs = #{data.wxs},</if>
        <if test="data.lastRecoverTime != null">bd.last_recover_time = #{data.lastRecoverTime},</if>
    </sql>

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

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