<?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.commandLog.mapper.CommandLogMapper">

    <resultMap type="CommandLogVO" id="CommandLogResult" autoMapping="true"/>

    <sql id="selectCommandLogVo">
        select
            bcl.id,
            bcl.type,
            bcl.mac,
            bcl.reason,
            bcl.command,
            bcl.result,
            bcl.user_id,
            bcl.user_name,
            bcl.opera_time,
            bcl.iot_code,
            bcl.longitude,
            bcl.latitude
        from bst_command_log bcl
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bcl.id = #{query.id}</if>
        <if test="query.type != null  and query.type != ''"> and bcl.type = #{query.type}</if>
        <if test="query.mac != null  and query.mac != ''"> and bcl.mac like concat('%', #{query.mac}, '%')</if>
        <if test="query.eqMac != null  and query.eqMac != ''"> and bcl.mac = #{query.eqMac}</if>
        <if test="query.reason != null  and query.reason != ''"> and bcl.reason like concat('%', #{query.reason}, '%')</if>
        <if test="query.command != null  and query.command != ''"> and bcl.command like concat('%', #{query.command}, '%')</if>
        <if test="query.result != null  and query.result != ''"> and bcl.result like concat('%', #{query.result}, '%')</if>
        <if test="query.userId != null "> and bcl.user_id = #{query.userId}</if>
        <if test="query.userName != null  and query.userName != ''"> and bcl.user_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.iotCode != null "> and bcl.iot_code = #{query.iotCode}</if>
        ${query.params.dataScope}
    </sql>

    <select id="selectCommandLogList" parameterType="CommandLogQuery" resultMap="CommandLogResult">
        <include refid="selectCommandLogVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectCommandLogById" parameterType="Long" resultMap="CommandLogResult">
        <include refid="selectCommandLogVo"/>
        where bcl.id = #{id}
    </select>

    <insert id="batchInsert" parameterType="CommandLog" useGeneratedKeys="true" keyProperty="id">
        insert into bst_command_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            type,
            mac,
            reason,
            command,
            result,
            user_id,
            user_name,
            opera_time,
            iot_code,
            longitude,
            latitude
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.type != null  and i.type != ''">#{i.type},</if>
                <if test="i.type == null  or i.type == ''">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.reason != null ">#{i.reason},</if>
                <if test="i.reason == null ">default,</if>
                <if test="i.command != null ">#{i.command},</if>
                <if test="i.command == null ">default,</if>
                <if test="i.result != null ">#{i.result},</if>
                <if test="i.result == null ">default,</if>
                <if test="i.userId != null ">#{i.userId},</if>
                <if test="i.userId == null ">default,</if>
                <if test="i.userName != null ">#{i.userName},</if>
                <if test="i.userName == null ">default,</if>
                <if test="i.operaTime != null ">#{i.operaTime},</if>
                <if test="i.operaTime == null ">default,</if>
                <if test="i.iotCode != null ">#{i.iotCode},</if>
                <if test="i.iotCode == 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>
            </trim>
        </foreach>
    </insert>

    <insert id="insertCommandLog" parameterType="CommandLog" useGeneratedKeys="true" keyProperty="id">
        insert into bst_command_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="type != null and type != ''">type,</if>
            <if test="mac != null and mac != ''">mac,</if>
            <if test="reason != null">reason,</if>
            <if test="command != null">command,</if>
            <if test="result != null">result,</if>
            <if test="userId != null">user_id,</if>
            <if test="userName != null">user_name,</if>
            <if test="operaTime != null">opera_time,</if>
            <if test="iotCode != null">iot_code,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="type != null and type != ''">#{type},</if>
            <if test="mac != null and mac != ''">#{mac},</if>
            <if test="reason != null">#{reason},</if>
            <if test="command != null">#{command},</if>
            <if test="result != null">#{result},</if>
            <if test="userId != null">#{userId},</if>
            <if test="userName != null">#{userName},</if>
            <if test="operaTime != null">#{operaTime},</if>
            <if test="iotCode != null">#{iotCode},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
         </trim>
    </insert>

    <update id="updateCommandLog" parameterType="CommandLog">
        update bst_command_log
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.type != null and data.type != ''">type = #{data.type},</if>
        <if test="data.mac != null and data.mac != ''">mac = #{data.mac},</if>
        <if test="data.reason != null">reason = #{data.reason},</if>
        <if test="data.command != null">command = #{data.command},</if>
        <if test="data.result != null">result = #{data.result},</if>
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.userName != null">user_name = #{data.userName},</if>
        <if test="data.operaTime != null">opera_time = #{data.operaTime},</if>
        <if test="data.iotCode != null">iot_code = #{data.iotCode},</if>
        <if test="data.longitude != null">longitude = #{data.longitude},</if>
        <if test="data.latitude != null">latitude = #{data.latitude},</if>
    </sql>

    <delete id="deleteCommandLogById" parameterType="Long">
        delete from bst_command_log where id = #{id}
    </delete>

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

    <delete id="clearBeforeDateLater">
        delete from bst_command_log where date(opera_time) &lt; #{date}
    </delete>
</mapper>
