<?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.abnormal.mapper.AbnormalMapper">

    <resultMap type="AbnormalVO" id="AbnormalResult" autoMapping="true"/>

    <sql id="selectAbnormalVo">
        select
            sa.abnormal_id,
            sa.device_id,
            sa.content,
            sa.name,
            sa.mobile,
            sa.address,
            sa.status,
            sa.create_time,
            sa.create_by,
            sa.update_time,
            sa.update_by,
            sa.deleted,
            sa.user_id,
            su.user_name as user_name,
            sd.device_no as device_no
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        ss_abnormal sa
            left join sm_user su on su.user_id = sa.user_id
            left join sm_device sd on sd.device_id = sa.device_id
    </sql>

    <select id="selectAbnormalList" parameterType="AbnormalQuery" resultMap="AbnormalResult">
        <include refid="selectAbnormalVo"/>
        <where>
            <if test="abnormalId != null "> and sa.abnormal_id = #{abnormalId}</if>
            <if test="deviceNo != null and deviceNo != ''"> and sd.device_no like concat('%', #{deviceNo}, '%')</if>
            <if test="deviceId != null "> and sd.device_id = #{deviceId}</if>
            <if test="content != null  and content != ''"> and sa.content = #{content}</if>
            <if test="name != null  and name != ''"> and sa.name like concat('%', #{name}, '%')</if>
            <if test="mobile != null  and mobile != ''"> and sa.mobile = #{mobile}</if>
            <if test="address != null  and address != ''"> and sa.address = #{address}</if>
            <if test="status != null  and status != ''"> and sa.`status` = #{status}</if>
            <if test="userId != null"> and sa.`user_id` = #{userId}</if>
            <if test="deleted != null"> and sa.`deleted` = #{deleted}</if>
            <if test="deleted == null"> and sa.deleted = false</if>
            <if test="keyword != null">
                and (
                    sa.content like concat('%', #{keyword}, '%') or
                    sa.name like concat('%', #{keyword}, '%') or
                    sa.mobile like concat('%', #{keyword}, '%') or
                    sa.address like concat('%', #{keyword}, '%') or
                    sd.device_no like concat('%', #{keyword}, '%')
                )
            </if>
        </where>
    </select>

    <select id="selectAbnormalByAbnormalId" parameterType="Long" resultMap="AbnormalResult">
        <include refid="selectAbnormalVo"/>
        where abnormal_id = #{abnormalId} and sa.deleted = false
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(sa.abnormal_id)
        from <include refid="searchTables"/>

    </select>

    <insert id="insertAbnormal" parameterType="Abnormal" useGeneratedKeys="true" keyProperty="abnormalId">
        insert into ss_abnormal
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceId != null">device_id,</if>
            <if test="content != null and content != ''">content,</if>
            <if test="name != null">`name`,</if>
            <if test="mobile != null">mobile,</if>
            <if test="address != null">address,</if>
            <if test="status != null and status != ''">`status`,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="deleted != null">deleted,</if>
            <if test="userId != null">user_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deviceId != null">#{deviceId},</if>
            <if test="content != null and content != ''">#{content},</if>
            <if test="name != null">#{name},</if>
            <if test="mobile != null">#{mobile},</if>
            <if test="address != null">#{address},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="userId != null">#{userId},</if>
         </trim>
    </insert>

    <update id="updateAbnormal" parameterType="Abnormal">
        update ss_abnormal
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceId != null">device_no = #{deviceId},</if>
            <if test="content != null and content != ''">content = #{content},</if>
            <if test="name != null">`name` = #{name},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            <if test="address != null">address = #{address},</if>
            <if test="status != null and status != ''">`status` = #{status},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="userId != null">user_id = #{userId},</if>
        </trim>
        where abnormal_id = #{abnormalId}
    </update>

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

    <delete id="deleteAbnormalByAbnormalId" parameterType="Long">
        delete from ss_abnormal where abnormal_id = #{abnormalId}
    </delete>

    <delete id="deleteAbnormalByAbnormalIds" parameterType="String">
        delete from ss_abnormal where abnormal_id in
        <foreach item="abnormalId" collection="array" open="(" separator="," close=")">
            #{abnormalId}
        </foreach>
    </delete>
</mapper>
