<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zzyl.nursing.mapper.RoomMapper">
    
    <resultMap type="Room" id="RoomResult">
        <result property="id"    column="id"    />
        <result property="code"    column="code"    />
        <result property="sort"    column="sort"    />
        <result property="typeName"    column="type_name"    />
        <result property="floorId"    column="floor_id"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="isDeleted"    column="is_deleted"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
        <result property="remark"    column="remark"    />
    </resultMap>

    <sql id="selectRoomVo">
        select id, code, sort, type_name, floor_id, create_time, update_time, is_deleted, create_by, update_by, remark from room
    </sql>

    <select id="selectRoomList" parameterType="Room" resultMap="RoomResult">
        <include refid="selectRoomVo"/>
        <where>  
            <if test="code != null  and code != ''"> and code = #{code}</if>
            <if test="sort != null "> and sort = #{sort}</if>
            <if test="typeName != null  and typeName != ''"> and type_name like concat('%', #{typeName}, '%')</if>
            <if test="floorId != null "> and floor_id = #{floorId}</if>
            <if test="isDeleted != null "> and is_deleted = #{isDeleted}</if>
        </where>
    </select>
    
    <select id="selectRoomById" parameterType="Long" resultMap="RoomResult">
        <include refid="selectRoomVo"/>
        where id = #{id}
    </select>


    <resultMap id="roomVoResultMap" type="com.zzyl.nursing.vo.RoomVo">
        <id property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="floorId" column="floor_id"/>
        <result property="floorName" column="fname"/>
        <result column="price" property="price"/>
        <collection property="bedVoList" ofType="com.zzyl.nursing.vo.BedVo">
            <id column="bid" property="id"/>
            <result column="bed_number" property="bedNumber"/>
            <result column="bed_status" property="bedStatus"/>
            <result column="room_id" property="roomId"/>
            <result column="ename" property="ename"/>
            <result column="eid" property="elderId"/>
        </collection>
    </resultMap>

    <select id="selectByFloorId" resultMap="roomVoResultMap">
        select r.*,
               f.name  as fname,
               rt.*,
               b.id    as bid,
               b.bed_number,
               b.sort,
               b.bed_status,
               b.room_id,
               b.create_by,
               b.update_by,
               b.remark,
               b.create_time,
               b.update_time,
               e.name  as ename,
               e.id    as eid
        from room r
                 left join floor f on f.id = r.floor_id
                 left join room_type rt on rt.name = r.type_name
                 left join bed b on b.room_id = r.id
                 left join elder e on b.id = e.bed_id
        where r.floor_id = #{floorId}
        order by r.sort, r.create_time desc
    </select>



    <resultMap id="roomVoWithNurResultMap" type="com.zzyl.nursing.vo.RoomVo">
        <id property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="floorId" column="floor_id"/>
        <result property="floorName" column="fname"/>
        <result column="price" property="price"/>
        <collection property="bedVoList" ofType="com.zzyl.nursing.vo.BedVo">
            <id column="bid" property="id"/>
            <result column="bed_number" property="bedNumber"/>
            <result column="bed_status" property="bedStatus"/>
            <result column="room_id" property="roomId"/>
            <result column="ename" property="ename"/>
            <result column="eid" property="elderId"/>
            <collection property="userVos" ofType="com.zzyl.common.core.domain.entity.SysUser" column="eid"
                        select="com.zzyl.nursing.mapper.NursingElderMapper.selectUserByElderId">
                <result column="nick_name" property="nickName"/>
                <result column="userId" property="userId"/>
            </collection>
        </collection>
    </resultMap>

    <select id="selectByFloorIdWithNur" resultMap="roomVoWithNurResultMap">
        select r.*,
               b.id   as bid,
               b.bed_number,
               b.sort,
               b.bed_status,
               b.room_id,
               e.name as ename,
               e.id   as eid
        from room r
                 left join bed b on b.room_id = r.id
                 left join elder e on e.bed_id = b.id
        where r.floor_id = #{floorId}
          and e.id is not null
        order by r.sort, r.create_time desc
    </select>

  <insert id="insertRoom" parameterType="Room" useGeneratedKeys="true" keyProperty="id">
        insert into room
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="code != null">code,</if>
            <if test="sort != null">sort,</if>
            <if test="typeName != null">type_name,</if>
            <if test="floorId != null">floor_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="isDeleted != null">is_deleted,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="code != null">#{code},</if>
            <if test="sort != null">#{sort},</if>
            <if test="typeName != null">#{typeName},</if>
            <if test="floorId != null">#{floorId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="isDeleted != null">#{isDeleted},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="remark != null">#{remark},</if>
         </trim>
    </insert>

    <update id="updateRoom" parameterType="Room">
        update room
        <trim prefix="SET" suffixOverrides=",">
            <if test="code != null">code = #{code},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="typeName != null">type_name = #{typeName},</if>
            <if test="floorId != null">floor_id = #{floorId},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="isDeleted != null">is_deleted = #{isDeleted},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteRoomById" parameterType="Long">
        delete from room where id = #{id}
    </delete>

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

  <select id="selectRoomPriceByRoomId" resultType="com.zzyl.nursing.vo.RoomVo">
    SELECT
      f.name floor_name,
      f.id floor_id,
      r.id room_id,
      r.code,
      rt.price
    FROM
      floor f LEFT JOIN room r ON f.id = r.floor_id
              LEFT JOIN room_type rt ON r.type_name = rt.name
    WHERE r.id = #{id};
  </select>

  <resultMap id="roomWithDeviceResultMap" type="com.zzyl.nursing.vo.RoomVo">
    <id property="id" column="id"/>
    <result property="code" column="code"/>
    <result property="floorId" column="floor_id"/>
    <collection property="bedVoList" ofType="com.zzyl.nursing.vo.BedVo">
      <id property="id" column="bid"/>
      <result property="bedNumber" column="bed_number"/>
      <result property="bedStatus" column="bed_status"/>
      <result property="roomId" column="room_id"/>
      <result property="ename" column="ename" />
      <result property="elderId" column="eid" />
      <collection property="deviceVos" ofType="com.zzyl.nursing.vo.DeviceInfo">
        <id column="db_id" property="id"/>
        <result column="b_iot_id" property="iotId"/>
        <result column="b_device_name" property="deviceName"/>
        <result column="b_product_key" property="productKey"/>
        <result column="b_product_name" property="productName"/>
      </collection>
    </collection>
    <collection property="deviceVos" ofType="com.zzyl.nursing.vo.DeviceInfo">
      <id property="id" column="dr_id"/>
      <result property="iotId" column="iot_id"/>
      <result property="deviceName" column="device_name"/>
      <result property="productKey" column="product_key"/>
      <result property="productName" column="product_name"/>
    </collection>
  </resultMap>

  <select id="getRoomsWithDeviceByFloorId" resultMap="roomWithDeviceResultMap">
    SELECT
      r.id, r.floor_id, r.code,
      b.id bid, b.bed_number, b.bed_status, b.room_id, e.name ename, e.id elderId,
      dr.id dr_id,
      dr.iot_id,
      dr.device_name,
      dr.product_key,
      dr.product_name,
      db.id b_did,
      db.iot_id b_iot_id,
      db.device_name b_device_name,
      db.product_key b_product_key,
      db.product_name b_product_name
    FROM room r
      LEFT JOIN bed b ON r.id = b.room_id
      LEFT JOIN elder e ON b.id = e.bed_id
      LEFT JOIN device dr
        ON (dr.binding_location = r.id OR dr.binding_location = r.floor_id) AND dr.location_type = 1 AND dr.physical_location_type = 1
      LEFT JOIN device db
        ON db.binding_location = b.id AND db.location_type = 1 AND db.physical_location_type = 2
    WHERE r.floor_id = #{floorId} AND (dr.id IS NOT NULL OR db.id IS NOT NULL)
  </select>

</mapper>