<?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.locationLog.mapper.LocationLogMapper">

    <resultMap type="LocationLogVO" id="LocationLogResult" autoMapping="true"/>

    <sql id="selectLocationLogVo">
        select
            bll.id,
            bll.mac,
            bll.sn,
            bll.onenet_msg,
            bll.longitude,
            bll.latitude,
            bll.at,
            bll.status,
            bll.lock_status,
            bll.voltage,
            bll.signal,
            bll.satellites,
            bll.quality,
            bll.iot_status,
            bll.device_id,
            bll.order_id
        <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        from bst_location_log bll
            left join bst_device bd on bll.device_id = bd.id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bll.id = #{query.id}</if>
        <if test="query.mac != null  and query.mac != ''"> and bll.mac like concat('%', #{query.mac}, '%')</if>
        <if test="query.eqMac != null  and query.eqMac != ''"> and bll.mac = #{query.eqMac}</if>
        <if test="query.sn != null  and query.sn != ''"> and bll.sn like concat('%', #{query.sn}, '%')</if>
        <if test="query.status != null  and query.status != ''"> and bll.status = #{query.status}</if>
        <if test="query.lockStatus != null  and query.lockStatus != ''"> and bll.lock_status = #{query.lockStatus}</if>
        <if test="query.quality != null  and query.quality != ''"> and bll.quality = #{query.quality}</if>
        <if test="query.iotStatus != null  and query.iotStatus != ''"> and bll.iot_status = #{query.iotStatus}</if>
        <if test="query.deviceId != null "> and bll.device_id = #{query.deviceId}</if>
        <if test="query.orderId != null "> and bll.order_id = #{query.orderId}</if>
        <if test="query.startTime != null"> and bll.at &gt;= #{query.startTime}</if>
        <if test="query.endTime != null"> and bll.at &lt;= #{query.endTime}</if>
        <if test="query.timeRange != null and query.timeRange.size() > 1">
            and bll.at &gt;= #{query.timeRange[0]}
            and bll.at &lt;= #{query.timeRange[1]}
        </if>
        <if test="query.ids != null and query.ids.size() > 0">
            and bll.id in
            <foreach item="item" collection="query.ids" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "bd.mch_id",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

    <select id="selectLocationLogList" parameterType="LocationLogQuery" resultMap="LocationLogResult">
        <include refid="selectLocationLogVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectLocationLogById" parameterType="Long" resultMap="LocationLogResult">
        <include refid="selectLocationLogVo"/>
        where bll.id = #{id}
    </select>

    <insert id="insertLocationLog" parameterType="LocationLog" useGeneratedKeys="true" keyProperty="id">
        insert into bst_location_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="mac != null and mac != ''">mac,</if>
            <if test="sn != null">sn,</if>
            <if test="onenetMsg != null">onenet_msg,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
            <if test="at != null">at,</if>
            <if test="status != null">status,</if>
            <if test="lockStatus != null">lock_status,</if>
            <if test="voltage != null">voltage,</if>
            <if test="signal != null">signal,</if>
            <if test="satellites != null">satellites,</if>
            <if test="quality != null">quality,</if>
            <if test="iotStatus != null">iot_status,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="orderId != null">order_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="mac != null and mac != ''">#{mac},</if>
            <if test="sn != null">#{sn},</if>
            <if test="onenetMsg != null">#{onenetMsg},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
            <if test="at != null">#{at},</if>
            <if test="status != null">#{status},</if>
            <if test="lockStatus != null">#{lockStatus},</if>
            <if test="voltage != null">#{voltage},</if>
            <if test="signal != null">#{signal},</if>
            <if test="satellites != null">#{satellites},</if>
            <if test="quality != null">#{quality},</if>
            <if test="iotStatus != null">#{iotStatus},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="orderId != null">#{orderId},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="LocationLog" useGeneratedKeys="true" keyProperty="id">
        insert into bst_location_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            mac,
            sn,
            onenet_msg,
            longitude,
            latitude,
            `at`,
            `status`,
            lock_status,
            voltage,
            `signal`,
            satellites,
            quality,
            iot_status,
            device_id,
            order_id
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.mac != null  and i.mac != ''">#{i.mac},</if>
                <if test="i.mac == null  or i.mac == ''">default,</if>
                <if test="i.sn != null ">#{i.sn},</if>
                <if test="i.sn == null ">default,</if>
                <if test="i.onenetMsg != null ">#{i.onenetMsg},</if>
                <if test="i.onenetMsg == null ">default,</if>
                <if test="i.longitude != null ">#{i.longitude},</if>
                <if test="i.longitude == null ">default,</if>
                <if test="i.latitude != null ">#{i.latitude},</if>
                <if test="i.latitude == null ">default,</if>
                <if test="i.at != null ">#{i.at},</if>
                <if test="i.at == null ">default,</if>
                <if test="i.status != null ">#{i.status},</if>
                <if test="i.status == null ">default,</if>
                <if test="i.lockStatus != null ">#{i.lockStatus},</if>
                <if test="i.lockStatus == null ">default,</if>
                <if test="i.voltage != null ">#{i.voltage},</if>
                <if test="i.voltage == null ">default,</if>
                <if test="i.signal != null ">#{i.signal},</if>
                <if test="i.signal == null ">default,</if>
                <if test="i.satellites != null ">#{i.satellites},</if>
                <if test="i.satellites == null ">default,</if>
                <if test="i.quality != null ">#{i.quality},</if>
                <if test="i.quality == null ">default,</if>
                <if test="i.iotStatus != null ">#{i.iotStatus},</if>
                <if test="i.iotStatus == null ">default,</if>
                <if test="i.deviceId != null ">#{i.deviceId},</if>
                <if test="i.deviceId == null ">default,</if>
                <if test="i.orderId != null ">#{i.orderId},</if>
                <if test="i.orderId == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="updateLocationLog" parameterType="LocationLog">
        update bst_location_log
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.mac != null and data.mac != ''">mac = #{data.mac},</if>
        <if test="data.sn != null">sn = #{data.sn},</if>
        <if test="data.onenetMsg != null">onenet_msg = #{data.onenetMsg},</if>
        <if test="data.longitude != null">longitude = #{data.longitude},</if>
        <if test="data.latitude != null">latitude = #{data.latitude},</if>
        <if test="data.at != null">at = #{data.at},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.lockStatus != null">lock_status = #{data.lockStatus},</if>
        <if test="data.voltage != null">voltage = #{data.voltage},</if>
        <if test="data.signal != null">signal = #{data.signal},</if>
        <if test="data.satellites != null">satellites = #{data.satellites},</if>
        <if test="data.quality != null">quality = #{data.quality},</if>
        <if test="data.iotStatus != null">iot_status = #{data.iotStatus},</if>
        <if test="data.deviceId != null">device_id = #{data.deviceId},</if>
        <if test="data.orderId != null">order_id = #{data.orderId},</if>
    </sql>

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

    <!-- selectIdList -->

    <select id="selectIdList" parameterType="LocationLogQuery" resultType="Long">
        select bll.id
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectPositionList -->
    <select id="selectPositionList" parameterType="LocationLogQuery" resultType="LocationLogPositionVO">
        select
            bll.longitude,
            bll.latitude
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

</mapper>
