<?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.equipment.mapper.EquipmentMapper">

    <resultMap type="EquipmentVO" id="EquipmentResult" autoMapping="true" />

    <sql id="selectEquipmentVo">
        select
            e.equipment_id,
            e.name,
            e.room_id,
            e.room_name,
            e.store_id,
            e.merchant_id,
            m.user_name merchantName,
            e.store_name,
            e.deleted,
            e.create_by,
            e.create_time,
            e.type,
            e.device_id,
            d.sn,
            e.status,
            e.unlock_mode,
            e.unlock_condition,
            e.unlock_time
        from ss_equipment e
        left join ss_user m on m.user_id = e.merchant_id
        left join ss_device d on d.device_id = e.device_id
    </sql>

    <sql id="searchCondition">
        <if test="query.name != null  and query.name != ''"> and e.name like concat('%', #{query.name}, '%')</if>
        <if test="query.storeId != null  and query.storeId != ''"> and e.store_id = #{query.storeId}</if>
        <if test="query.status != null  and query.status != ''"> and e.status = #{query.status}</if>
        <if test="query.roomId != null "> and e.room_id = #{query.roomId}</if>
        <if test="query.roomName != null  and query.roomName != ''"> and e.room_name like concat('%', #{query.roomName}, '%')</if>
        <if test="query.deviceId != null  and query.deviceId != ''"> and e.device_id = #{query.deviceId}</if>
        <if test="query.type != null  and query.type != ''"> and e.type = #{query.type}</if>
        <if test="query.merchantId != null  and query.merchantId != ''"> and e.merchant_id = #{query.merchantId}</if>
        <if test="query.deleted == null">and e.deleted = false</if>
        <if test="query.deleted != null">and e.deleted = #{query.deleted}</if>
        <if test="query.roomIds != null and query.roomIds.size() > 0">
            and e.room_id in
            <foreach collection="query.roomIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="query.isBind != null">
            <choose>
                <when test="query.isBind">
                    and e.device_id is not null
                </when>
                <otherwise>
                    and e.device_id is null
                </otherwise>
            </choose>
        </if>
    </sql>

    <select id="selectEquipmentList" parameterType="EquipmentQuery" resultMap="EquipmentResult">
        <include refid="selectEquipmentVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectRoomIdByDeviceId" resultType="java.lang.Long">
        select equipment_id from ss_equipment where find_in_set(#{deviceId}, device_id);
    </select>

    <select id="selectEquipmentListByRoomId" parameterType="Long" resultMap="EquipmentResult">
        <include refid="selectEquipmentVo"/>
        where e.room_id = #{roomId} and e.deleted = 0
    </select>

    <select id="selectEquipmentByEquipmentId" parameterType="Long" resultMap="EquipmentResult">
        <include refid="selectEquipmentVo"/>
        where e.equipment_id = #{equipmentId} and e.deleted = 0
    </select>

    <select id="selectDeviceIdListByRoomId" resultType="java.lang.Long">
        SELECT e.device_id
        FROM ss_equipment e where e.room_id =  #{roomId} and e.type != '1'
        GROUP BY e.device_id
    </select>

    <select id="selectAllEquipmentIdsByRoomId" resultType="java.lang.Long">
        SELECT e.equipment_id
        FROM ss_equipment e where e.room_id =  #{roomId} and e.deleted = 0
        GROUP BY e.equipment_id
    </select>

    <select id="selectAllEquipmentIdsByRoomIds" resultType="java.lang.Long">
        SELECT e.equipment_id
        FROM ss_equipment e
        where room_id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="uniqueGate" resultType="java.lang.Boolean">
          select count(1) from ss_equipment where store_id = #{storeId} and deleted = 0 and type = '6'
    </select>

<!--    <select id="selectGate" resultMap="EquipmentResult">-->
<!--        <include refid="selectEquipmentVo"/>-->
<!--                where e.store_id = #{storeId} and e.deleted = 0 and e.type = '6'-->
<!--    </select>-->

    <select id="selectStoreIdByDeviceId" resultType="java.lang.Long">
        select store_id from ss_equipment where device_id = #{deviceId} and deleted = 0 and type = '6' limit 1
    </select>

    <select id="selectByDeviceId" resultMap="EquipmentResult">
        <include refid="selectEquipmentVo"/>
        where e.device_id = #{deviceId} and e.deleted = 0 limit 1
    </select>

    <select id="selectIdleEquipmentNumByStoreId" resultType="java.lang.Integer">
        SELECT
            count( 1 )
        FROM
            ss_equipment e
                LEFT JOIN ss_room r ON r.room_id = e.room_id
        WHERE
            e.store_id = #{storeId}
          AND r.type2 = '2'
          AND e.`status` = '0'
          AND e.deleted = 0
    </select>

    <select id="selectDoorByRoomId" resultMap="EquipmentResult">
        <include refid="selectEquipmentVo"/>
        where e.room_id = #{roomId} and e.deleted = 0
        <if test="type != null and type != ''">
            AND e.type = #{type}
        </if>
    </select>

    <select id="selectUnifiedEquipmentList" resultType="com.ruoyi.ss.equipment.domain.unified.UnifiedEquipment">
        SELECT
            store_id storeId,
            store_name storeName,
            merchant_id merchantId,
            type,
            status,
            unlock_mode as unlockMode,
            unlock_condition as unlockCondition,
            unlock_time as unlockTime,
            device_id as deviceId,
            equipment_name as equipmentName,
            placement_status as placementStatus,
            obj_id as objId
        FROM v_unified_equipment
        <where>
            <if test="equipmentName != null and equipmentName != ''">
                AND equipment_name LIKE CONCAT('%', #{equipmentName}, '%')
            </if>
            <if test="type != null and type != ''">
                AND type = #{type}
            </if>
            <if test="merchantId != null">
                AND merchant_id = #{merchantId}
            </if>
            <if test="storeId != null">
                AND store_id = #{storeId}
            </if>
            <if test="placementStatus != null and placementStatus != ''">
                AND placement_status = #{placementStatus}
            </if>
        </where>
        ORDER BY type desc
    </select>

    <insert id="insertEquipment" parameterType="Equipment">
        insert into ss_equipment
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="equipmentId != null">equipment_id,</if>
            <if test="name != null">name,</if>
            <if test="storeId != null">store_id,</if>
            <if test="storeName != null">store_name,</if>
            <if test="roomId != null">room_id,</if>
            <if test="roomName != null">room_name,</if>
            <if test="merchantId != null">merchant_id,</if>
            <if test="deleted != null">deleted,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="type != null">type,</if>
            <if test="deviceId != null">device_id,</if>
            <if test="status != null">status,</if>
            <if test="unlockMode != null">unlock_mode,</if>
            <if test="unlockCondition != null">unlock_condition,</if>
            <if test="unlockTime != null">unlock_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="equipmentId != null">#{equipmentId},</if>
            <if test="name != null">#{name},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="storeName != null">#{storeName},</if>
            <if test="roomId != null">#{roomId},</if>
            <if test="roomName != null">#{roomName},</if>
            <if test="merchantId != null">#{merchantId},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="type != null">#{type},</if>
            <if test="deviceId != null">#{deviceId},</if>
            <if test="status != null">#{status},</if>
            <if test="unlockMode != null">#{unlockMode},</if>
            <if test="unlockCondition != null">#{unlockCondition},</if>
            <if test="unlockTime != null">#{unlockTime},</if>
         </trim>
    </insert>

    <update id="updateEquipment" parameterType="Equipment">
        update ss_equipment
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where equipment_id = #{data.equipmentId}
    </update>

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

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

<!--    <update id="offlineDevice" >-->
<!--        update ss_equipment-->
<!--        set device_id = null-->
<!--        where equipment_id = #{data.equipmentId}-->
<!--    </update>-->

    <update id="offlineDeviceByDeviceId" >
        update ss_equipment
        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.roomId != null">room_id = #{data.roomId},</if>
        <if test="data.roomName != null">room_name = #{data.roomName},</if>
        <if test="data.merchantId != null">merchant_id = #{data.merchantId},</if>
        <if test="data.deleted != null">deleted = #{data.deleted},</if>
        <if test="data.createBy != null">create_by = #{data.createBy},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.type != null">type = #{data.type},</if>
        <if test="data.deviceId != null">device_id = #{data.deviceId},</if>
        <if test="data.status != null">status = #{data.status},</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>
</sql>

    <delete id="deleteEquipmentByEquipmentId" parameterType="Long">
        update ss_equipment
        set deleted = 1,
        device_id = null
        where equipment_id = #{equipmentId}
    </delete>

    <delete id="deleteEquipmentByEquipmentIds" parameterType="String">
        update ss_equipment
        set deleted = 1,
        device_id = null
        where equipment_id in
        <foreach item="equipmentId" collection="array" open="(" separator="," close=")">
            #{equipmentId}
        </foreach>
    </delete>
</mapper>
