<?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.toilet.mapper.ToiletMapper">

    <resultMap type="ToiletVO" id="ToiletResult" autoMapping="true" />

    <sql id="selectToiletVo">
        select
            t.toilet_id,
            t.code as qr_text,
            t.name,
            t.store_id,
            t.store_name,
            t.unlock_mode,
            t.unlock_condition,
            t.unlock_time,
            t.status,
            t.code,
            t.merchant_id,
            t.device_id,
            m.user_name,
            d.sn,
            m.create_time
        from ss_toilet t
        left join ss_device d on d.device_id = t.device_id
        left join ss_user m on m.user_id = t.merchant_id
    </sql>

    <sql id="searchCondition">
        <if test="query.name != null  and query.name != ''"> and t.name like concat('%', #{query.name}, '%')</if>
        <if test="query.storeId != null "> and t.store_id = #{query.storeId}</if>
        <if test="query.storeName != null  and query.storeName != ''"> and t.store_name like concat('%', #{query.storeName}, '%')</if>
        <if test="query.unlockMode != null  and query.unlockMode != ''"> and t.unlock_mode = #{query.unlockMode}</if>
        <if test="query.unlockCondition != null  and query.unlockCondition != ''"> and t.unlock_condition = #{query.unlockCondition}</if>
        <if test="query.unlockTime != null "> and t.unlock_time = #{query.unlockTime}</if>
        <if test="query.status != null  and query.status != ''"> and t.status = #{query.status}</if>
        <if test="query.code != null  and query.code != ''"> and t.code like concat('%', #{query.code}, '%')</if>
        <if test="query.merchantId != null "> and t.merchant_id = #{query.merchantId}</if>
        <if test="query.deviceId != null "> and t.device_id = #{query.deviceId}</if>
    </sql>

    <select id="selectToiletList" parameterType="ToiletQuery" resultMap="ToiletResult">
        <include refid="selectToiletVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectToiletByToiletId" parameterType="Long" resultMap="ToiletResult">
        <include refid="selectToiletVo"/>
        where t.toilet_id = #{toiletId}
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(1) from ss_toilet t
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="checkCodeIsExist" resultType="java.lang.Boolean">
        SELECT COUNT(1)
        FROM ss_toilet
        WHERE  code = #{code}
        <if test="toiletId != null ">  AND toilet_id != #{toiletId}</if>
    </select>

    <insert id="insertToilet" parameterType="Toilet" useGeneratedKeys="true" keyProperty="toiletId">
        insert into ss_toilet
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="storeId != null">store_id,</if>
            <if test="storeName != null">store_name,</if>
            <if test="unlockMode != null">unlock_mode,</if>
            <if test="unlockCondition != null">unlock_condition,</if>
            <if test="unlockTime != null">unlock_time,</if>
            <if test="status != null">status,</if>
            <if test="code != null">code,</if>
            <if test="merchantId != null">merchant_id,</if>
            <if test="deviceId != null">device_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="storeName != null">#{storeName},</if>
            <if test="unlockMode != null">#{unlockMode},</if>
            <if test="unlockCondition != null">#{unlockCondition},</if>
            <if test="unlockTime != null">#{unlockTime},</if>
            <if test="status != null">#{status},</if>
            <if test="code != null">#{code},</if>
            <if test="merchantId != null">#{merchantId},</if>
            <if test="deviceId != null">#{deviceId},</if>
         </trim>
    </insert>

    <update id="updateToilet" parameterType="Toilet">
        update ss_toilet
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where toilet_id = #{data.toiletId}
    </update>

    <update id="updateStoreName" parameterType="Toilet">
        update ss_toilet
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where store_id = #{data.storeId}
    </update>

    <update id="offlineDeviceByDeviceId">
        update ss_toilet
        set device_id = null
        where device_id = #{deviceId}
    </update>

    <sql id="updateColumns">
        <if test="data.name != null">name = #{data.name},</if>
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.storeName != null">store_name = #{data.storeName},</if>
        <if test="data.unlockMode != null">unlock_mode = #{data.unlockMode},</if>
        <if test="data.unlockCondition != null">unlock_condition = #{data.unlockCondition},</if>
        <if test="data.unlockTime != null">unlock_time = #{data.unlockTime},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.code != null">code = #{data.code},</if>
        <if test="data.merchantId != null">merchant_id = #{data.merchantId},</if>
        <if test="data.deviceId != null">device_id = #{data.deviceId},</if>
    </sql>

    <delete id="deleteToiletByToiletId" parameterType="Long">
        delete from ss_toilet where toilet_id = #{toiletId}
    </delete>

    <delete id="deleteToiletByToiletIds" parameterType="String">
        delete from ss_toilet where toilet_id in
        <foreach item="toiletId" collection="array" open="(" separator="," close=")">
            #{toiletId}
        </foreach>
    </delete>
</mapper>
