<?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.room.mapper.RoomMapper">

    <resultMap type="RoomVO" id="ERoomResult" autoMapping="true" >
        <result property="tags" column="tags" typeHandler="com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler"/>
        <result property="ruleIds" column="rule_ids" typeHandler="com.ruoyi.system.mapper.typehandler.LongSplitListTypeHandler"/>
        <result property="pictures" column="picture" typeHandler="com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler"/>
    </resultMap>

    <sql id="selectERoomVo">
        select
            r.room_id,
            r.code as qr_text,
            r.room_name,
            r.store_id,
            r.store_name,
            r.type,
            r.type2,
            r.code,
            r.picture,
            r.tags,
            r.specification,
            r.status,
            r.merchant_id,
            m.user_name merchantName,
            r.deleted,
            r.rule_ids,
            r.wifi,
            r.wifi_password,
            r.equ_type,
            r.create_time
        from ss_room r
        left join ss_user m on m.user_id = r.merchant_id
    </sql>

    <sql id="searchCondition">
        <if test="query.roomName != null  and query.roomName != ''"> and r.room_name like concat('%', #{query.roomName}, '%')</if>
        <if test="query.storeName != null  and query.storeName != ''"> and r.store_name like concat('%', #{query.storeName}, '%')</if>
        <if test="query.storeId != null  and query.storeId != ''"> and r.store_id = #{query.storeId}</if>
        <if test="query.type != null  and query.type != ''"> and r.type = #{query.type}</if>
        <if test="query.type2 != null  and query.type2 != ''"> and r.type2 = #{query.type2}</if>
        <if test="query.code != null  and query.code != ''"> and r.code like concat('%', #{query.code}, '%')</if>
        <if test="query.equType != null  and query.equType != ''"> and r.equ_type = #{query.equType}</if>
        <if test="query.picture != null  and query.picture != ''"> and r.picture = #{query.picture}</if>
        <if test="query.tags != null  and query.tags != ''"> and r.tags = #{query.tags}</if>
        <if test="query.specification != null  and query.specification != ''"> and r.specification = #{query.specification}</if>
        <if test="query.status != null  and query.status != ''"> and r.status = #{query.status}</if>
        <if test="query.merchantId != null  and query.merchantId != ''"> and r.merchant_id = #{query.merchantId}</if>
        <if test="query.deleted == null">and r.deleted = false</if>
        <if test="query.deleted != null">and r.deleted = #{query.deleted}</if>
    </sql>

    <select id="selectERoomList" parameterType="RoomQuery" resultMap="ERoomResult">
        <include refid="selectERoomVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by r.room_id desc
    </select>

    <select id="selectRoomIdsList" resultType="java.lang.Long">
        select
        room_id from ss_room r
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectERoomByRoomId" parameterType="Long" resultMap="ERoomResult">
        <include refid="selectERoomVo"/>
        where r.room_id = #{roomId}
    </select>

    <!--排除掉卫生间-->
    <select id="selectERoomListByStoreId" parameterType="Long" resultMap="ERoomResult">
        <include refid="selectERoomVo"/>
        where r.store_id = #{storeId}
    </select>

    <select id="selectIdleRoomNum" resultType="java.lang.Integer">
        select count(1) from ss_room where store_id = #{storeId} and deleted = 0 and status = 1  and type2 = '1'
    </select>

    <select id="selectToiletNum" resultType="java.lang.Integer">
        select count(1) from ss_room where store_id = #{storeId} and deleted = 0 and type2 = '3'
    </select>

    <select id="getTagListByStoreId" resultType="com.ruoyi.ss.room.domain.TagsVO">
        SELECT
            r.type,
            SUM( r.STATUS = 1 ) idleNum,
            CASE
                WHEN r.type = 1 THEN
                    '茶室'
                WHEN r.type = 2 THEN
                    '棋牌室'
                WHEN r.type = 3 THEN
                    '台球室' ELSE ''
                END AS tag
        FROM
            ss_room r
        WHERE
            store_id = #{storeId} and deleted = 0
        GROUP BY
            r.type
    </select>

    <!--是否存在套餐 -->
    <select id="selectIsExistRule" resultType="java.lang.String">
        select room_name from ss_room where FIND_IN_SET( #{ruleId}, rule_ids )
    </select>

    <select id="getCount" resultType="java.lang.Integer">
        select count(1) from ss_room r
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="getId" resultType="java.lang.Long">
        select room_id from ss_room where code = #{roomCode}
    </select>

    <select id="checkCodeIsExist" resultType="java.lang.Boolean">
        SELECT COUNT(1)
        FROM ss_room
        WHERE deleted = 0 AND code = #{code}
        <if test="roomId != null ">  AND room_id != #{roomId}</if>
    </select>

    <insert id="insertERoom" parameterType="Room" useGeneratedKeys="true" keyProperty="roomId">
        insert into ss_room
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="roomId != null">room_id,</if>
            <if test="roomName != null">room_name,</if>
            <if test="storeId != null">store_id,</if>
            <if test="storeName != null">store_name,</if>
            <if test="type != null">type,</if>
            <if test="type2 != null">type2,</if>
            <if test="code != null">code,</if>
            <if test="picture != null">picture,</if>
            <if test="tags != null">tags,</if>
            <if test="specification != null">specification,</if>
            <if test="status != null">status,</if>
            <if test="merchantId != null">merchant_id,</if>
            <if test="deleted != null">deleted,</if>
            <if test="ruleIds != null">rule_ids,</if>
            <if test="wifi != null">wifi,</if>
            <if test="wifiPassword != null">wifi_password,</if>
            <if test="equType != null">equ_type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="roomId != null">#{roomId},</if>
            <if test="roomName != null">#{roomName},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="storeName != null">#{storeName},</if>
            <if test="type != null">#{type},</if>
            <if test="type2 != null">#{type2},</if>
            <if test="code != null">#{code},</if>
            <if test="picture != null">#{picture},</if>
            <if test="tags != null">#{tags,typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
            <if test="specification != null">#{specification},</if>
            <if test="status != null">#{status},</if>
            <if test="merchantId != null">#{merchantId},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="ruleIds != null">#{ruleIds,typeHandler=com.ruoyi.system.mapper.typehandler.LongSplitListTypeHandler},</if>
            <if test="wifi != null">#{wifi},</if>
            <if test="wifiPassword != null">#{wifiPassword},</if>
            <if test="equType != null">#{equType},</if>
         </trim>
    </insert>

    <update id="updateERoom" parameterType="Room">
        update ss_room
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where room_id = #{data.roomId}
    </update>

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

    <sql id="updateColumns">
        <if test="data.roomName != null">room_name = #{data.roomName},</if>
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.storeName != null">store_name = #{data.storeName},</if>
        <if test="data.type != null">type = #{data.type},</if>
        <if test="data.type2 != null">type2 = #{data.type2},</if>
        <if test="data.code != null">code = #{data.code},</if>
        <if test="data.picture != null">picture = #{data.picture},</if>
        <if test="data.tags != null">tags = #{data.tags,typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
        <if test="data.specification != null">specification = #{data.specification},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.merchantId != null">merchant_id = #{data.merchantId},</if>
        <if test="data.deleted != null">deleted = #{data.deleted},</if>
        <if test="data.ruleIds != null">rule_ids = #{data.ruleIds,typeHandler=com.ruoyi.system.mapper.typehandler.LongSplitListTypeHandler},</if>
        <if test="data.wifi != null">wifi = #{data.wifi},</if>
        <if test="data.wifiPassword != null">wifi_password = #{data.wifiPassword},</if>
        <if test="data.equType != null">equ_type = #{data.equType},</if>
    </sql>

    <update id="deleteERoomByRoomId" parameterType="Long">
        update ss_room
        set deleted = 1
        where room_id = #{roomId}
    </update>

    <update id="deleteERoomByRoomIds" parameterType="List">
        update ss_room
        set deleted = 1
        where room_id in
        <foreach item="roomId" collection="array" open="(" separator="," close=")">
            #{roomId}
        </foreach>
    </update>

<!--    <update id="offlineDevice">-->
<!--        UPDATE ss_equipment-->
<!--        SET device_ids = TRIM(-->
<!--                BOTH ','-->
<!--                FROM-->
<!--                REPLACE ( CONCAT( ',', device_ids, ',' ), CONCAT( ',', #{deviceId}, ',' ), ',' ))-->
<!--        WHERE-->
<!--            FIND_IN_SET( #{deviceId}, device_ids ) > 0-->
<!--    </update>-->

    <update id="unBandRule">
        UPDATE ss_room
        SET rule_ids = TRIM(
                BOTH ','
                FROM
                REPLACE ( CONCAT( ',', rule_ids, ',' ), CONCAT( ',', #{ruleId}, ',' ), ',' ))
        WHERE
            FIND_IN_SET( #{ruleId}, rule_ids ) > 0 and room_id = #{roomId}
    </update>
</mapper>
