<?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="modelSuitIds" column="model_suit_ids" typeHandler="com.ruoyi.common.mybatis.typehandler.LongSplitListTypeHandler"/>
    </resultMap>

    <sql id="selectDeviceVo">
        select
            bd.id,
            bd.model_id,
            bd.picture,
            bd.mac,
            bd.sn,
            bd.vehicle_num,
            bd.area_id,
            bd.activation_time,
            bd.online_status,
            bd.create_time,
            bd.update_time,
            bd.remark,
            bd.status,
            bd.lock_status,
            bd.location,
            bd.remaining_power,
            bd.voltage,
            bd.longitude,
            bd.latitude,
            bd.last_time,
            bd.version,
            bd.signal_strength,
            bd.quality,
            bd.satellites,
            bd.gps,
            bd.last_location_time,
            bd.hardware_version_id,
            bd.mch_id,
            bd.iot_status,
            bd.is_sound,
            bd.deleted,
            bd.last_online_time,
            bd.order_device_id,
            mch.nick_name as mch_name,
            mch.point as mch_point,
            bm.name as model_name,
            bm.full_voltage as model_full_voltage,
            bm.low_voltage as model_low_voltage,
            bm.suit_ids as model_suit_ids,
            bm.full_endurance as model_full_endurance,
            ba.name as area_name,
            ba.user_id as area_user_id,
            ba.undercharge as area_undercharge,
            ba.authentication as area_authentication,
            basu.point as area_user_point,
            basu.nick_name as area_user_name,
            basu.agent_id as area_agent_id,
            basua.point as area_agent_point,
            basua.nick_name as area_agent_name,
            bo.status as order_status,
            bo.no as order_no,
            bod.status as order_device_status
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_device bd
            left join sys_user mch on bd.mch_id = mch.user_id
            left join bst_model bm on bd.model_id = bm.id
            left join bst_area ba on bd.area_id = ba.id
            left join sys_user basu on basu.user_id = ba.user_id
            left join sys_user basua on basua.user_id = basu.agent_id
            left join bst_order_device bod on bod.id = bd.order_device_id
            left join bst_order bo on bo.id = bod.order_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bd.id = #{query.id}</if>
        <if test="query.modelId != null "> and bd.model_id = #{query.modelId}</if>
        <if test="query.mac != null  and query.mac != ''"> and bd.mac like concat('%', #{query.mac}, '%')</if>
        <if test="query.sn != null  and query.sn != ''"> and bd.sn like concat('%', #{query.sn}, '%')</if>
        <if test="query.vehicleNum != null  and query.vehicleNum != ''"> and bd.vehicle_num like concat('%', #{query.vehicleNum}, '%')</if>
        <if test="query.areaId != null "> and bd.area_id = #{query.areaId}</if>
        <if test="query.onlineStatus != null  and query.onlineStatus != ''"> and bd.online_status = #{query.onlineStatus}</if>
        <if test="query.remark != null  and query.remark != ''"> and bd.remark like concat('%', #{query.remark}, '%')</if>
        <if test="query.status != null  and query.status != ''"> and bd.status = #{query.status}</if>
        <if test="query.lockStatus != null  and query.lockStatus != ''"> and bd.lock_status = #{query.lockStatus}</if>
        <if test="query.location != null  and query.location != ''"> and bd.location like concat('%', #{query.location}, '%')</if>
        <if test="query.gps != null "> and bd.gps = #{query.gps}</if>
        <if test="query.lastLocationTime != null "> and bd.last_location_time = #{query.lastLocationTime}</if>
        <if test="query.hardwareVersionId != null "> and bd.hardware_version_id = #{query.hardwareVersionId}</if>
        <if test="query.mchId != null "> and bd.mch_id = #{query.mchId}</if>
        <if test="query.iotStatus != null  and query.iotStatus != ''"> and bd.iot_status = #{query.iotStatus}</if>
        <if test="query.isSound != null "> and bd.is_sound = #{query.isSound}</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.eqSn != null  and query.eqSn != ''"> and bd.sn = #{query.eqSn}</if>
        <if test="query.eqMac != null  and query.eqMac != ''"> and bd.mac = #{query.eqMac}</if>
        <if test="query.orderId != null"> and bod.order_id = #{query.orderId}</if>
        <if test="query.areaUserId != null"> and basu.user_id = #{query.areaUserId}</if>
        <if test="query.areaAgentId != null"> and basu.agent_id = #{query.areaAgentId}</if>
        <if test="query.areaUserName != null and query.areaUserName != ''"> and basu.nick_name like concat('%', #{query.areaUserName}, '%')</if>
        <if test="query.areaAgentName != null and query.areaAgentName != ''"> and basua.nick_name like concat('%', #{query.areaAgentName}, '%')</if>
        <if test="query.orderNo != null and query.orderNo != ''"> and bo.no like concat('%', #{query.orderNo}, '%')</if>
        <if test="query.suitId != null">
            and find_in_set(#{query.suitId}, bm.suit_ids)
        </if>
        <if test="query.ids != null and query.ids.size() > 0">
            and bd.id in
            <foreach item="item" collection="query.ids" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.statusList != null and query.statusList.size() > 0">
            and bd.status in
            <foreach item="item" collection="query.statusList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.center != null and query.center.size() == 2 and query.radius != null">
            and #{query.radius} >= round(st_distance_sphere(point(#{query.center[0]}, #{query.center[1]}), point(bd.longitude, bd.latitude)))
        </if>
        <if test="query.powerRange != null and query.powerRange.size() == 2">
            and bd.remaining_power &gt;= #{query.powerRange[0]} 
            and bd.remaining_power &lt;= #{query.powerRange[1]}
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "bd.mch_id",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

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

    <select id="selectDeviceById" parameterType="Long" resultMap="DeviceResult">
        <include refid="selectDeviceVo"/>
        where bd.id = #{id}
    </select>

    <insert id="insertDevice" parameterType="Device" useGeneratedKeys="true" keyProperty="id">
        insert into bst_device
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="modelId != null">model_id,</if>
            <if test="picture != null">picture,</if>
            <if test="mac != null and mac != ''">mac,</if>
            <if test="sn != null and sn != ''">sn,</if>
            <if test="vehicleNum != null">vehicle_num,</if>
            <if test="areaId != null">area_id,</if>
            <if test="activationTime != null">activation_time,</if>
            <if test="onlineStatus != null">online_status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="status != null">status,</if>
            <if test="lockStatus != null">lock_status,</if>
            <if test="location != null">location,</if>
            <if test="remainingPower != null">remaining_power,</if>
            <if test="voltage != null">voltage,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
            <if test="lastTime != null">last_time,</if>
            <if test="version != null">version,</if>
            <if test="signalStrength != null">signal_strength,</if>
            <if test="quality != null">quality,</if>
            <if test="satellites != null">satellites,</if>
            <if test="gps != null">gps,</if>
            <if test="lastLocationTime != null">last_location_time,</if>
            <if test="hardwareVersionId != null">hardware_version_id,</if>
            <if test="mchId != null">mch_id,</if>
            <if test="iotStatus != null">iot_status,</if>
            <if test="isSound != null">is_sound,</if>
            <if test="lastOnlineTime != null">last_online_time,</if>
            <if test="orderDeviceId != null">order_device_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="modelId != null">#{modelId},</if>
            <if test="picture != null">#{picture},</if>
            <if test="mac != null and mac != ''">#{mac},</if>
            <if test="sn != null and sn != ''">#{sn},</if>
            <if test="vehicleNum != null">#{vehicleNum},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="activationTime != null">#{activationTime},</if>
            <if test="onlineStatus != null">#{onlineStatus},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="status != null">#{status},</if>
            <if test="lockStatus != null">#{lockStatus},</if>
            <if test="location != null">#{location},</if>
            <if test="remainingPower != null">#{remainingPower},</if>
            <if test="voltage != null">#{voltage},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
            <if test="lastTime != null">#{lastTime},</if>
            <if test="version != null">#{version},</if>
            <if test="signalStrength != null">#{signalStrength},</if>
            <if test="quality != null">#{quality},</if>
            <if test="satellites != null">#{satellites},</if>
            <if test="gps != null">#{gps},</if>
            <if test="lastLocationTime != null">#{lastLocationTime},</if>
            <if test="hardwareVersionId != null">#{hardwareVersionId},</if>
            <if test="mchId != null">#{mchId},</if>
            <if test="iotStatus != null">#{iotStatus},</if>
            <if test="isSound != null">#{isSound},</if>
            <if test="lastOnlineTime != null">#{lastOnlineTime},</if>
            <if test="orderDeviceId != null">#{orderDeviceId},</if>
         </trim>
    </insert>

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

    <sql id="updateColumns">
        <if test="data.modelId != null">model_id = #{data.modelId},</if>
        <if test="data.picture != null">picture = #{data.picture},</if>
        <if test="data.mac != null and data.mac != ''">mac = #{data.mac},</if>
        <if test="data.sn != null and data.sn != ''">sn = #{data.sn},</if>
        <if test="data.vehicleNum != null">vehicle_num = #{data.vehicleNum},</if>
        <if test="data.areaId != null">area_id = #{data.areaId},</if>
        <if test="data.activationTime != null">activation_time = #{data.activationTime},</if>
        <if test="data.onlineStatus != null">online_status = #{data.onlineStatus},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.updateTime != null">update_time = #{data.updateTime},</if>
        <if test="data.remark != null">remark = #{data.remark},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.lockStatus != null">lock_status = #{data.lockStatus},</if>
        <if test="data.location != null">location = #{data.location},</if>
        <if test="data.remainingPower != null">remaining_power = #{data.remainingPower},</if>
        <if test="data.voltage != null">voltage = #{data.voltage},</if>
        <if test="data.longitude != null">longitude = #{data.longitude},</if>
        <if test="data.latitude != null">latitude = #{data.latitude},</if>
        <if test="data.lastTime != null">last_time = #{data.lastTime},</if>
        <if test="data.version != null">version = #{data.version},</if>
        <if test="data.signalStrength != null">signal_strength = #{data.signalStrength},</if>
        <if test="data.quality != null">quality = #{data.quality},</if>
        <if test="data.satellites != null">satellites = #{data.satellites},</if>
        <if test="data.gps != null">gps = #{data.gps},</if>
        <if test="data.lastLocationTime != null">last_location_time = #{data.lastLocationTime},</if>
        <if test="data.hardwareVersionId != null">hardware_version_id = #{data.hardwareVersionId},</if>
        <if test="data.mchId != null">mch_id = #{data.mchId},</if>
        <if test="data.iotStatus != null">iot_status = #{data.iotStatus},</if>
        <if test="data.isSound != null">is_sound = #{data.isSound},</if>
        <if test="data.lastOnlineTime != null">last_online_time = #{data.lastOnlineTime},</if>
        <if test="data.orderDeviceId != null">order_device_id = #{data.orderDeviceId},</if>
    </sql>

    <delete id="deleteDeviceById" parameterType="Long">
        delete from bst_device where id = #{id}
    </delete>

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

    <!-- logicDel -->

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

    <!-- selectIdList -->

    <select id="selectIdList" parameterType="DeviceQuery" resultType="Long">
        select bd.id
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- updateByQuery -->

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

    <select id="selectSimpleForLocationLog" resultMap="DeviceResult">
        select
            bd.id,
            bd.mac,
            bd.sn,
            bd.status,
            bd.lock_status,
            bd.area_id,
            bd.order_device_id,
            bod.status as order_device_status,
            bod.order_id
        from bst_device bd
            left join bst_order_device bod on bod.id = bd.order_device_id
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <update id="clearCurrentOrderDevice">
        update bst_device
        set order_device_id = null
        where id = #{id} and order_device_id = #{orderDeviceId}
    </update>

    <!-- selectStatusCount -->

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

    <!-- selectOnlineStatusCount -->

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

    <!-- selectCount -->

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