<?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.srs.dormitory.mapper.DmsDormitoryBuildingMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.srs.dormitory.domain.DmsDormitoryBuilding">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="park_id" property="parkId" />
        <result column="manager" property="manager" />
        <result column="manager_phone" property="managerPhone" />
        <result column="image" property="image" />
        <result column="status" property="status" />
        <result column="remark" property="remark" />
        <result column="create_by" property="createBy" />
        <result column="create_time" property="createTime" />
        <result column="update_by" property="updateBy" />
        <result column="update_time" property="updateTime" />
        <result column="del_flag" property="delFlag" />
    </resultMap>
    <select id="selectTotalOccupancy" resultType="Integer">
        SELECT
            COALESCE(SUM(d.occupancy), 0) AS totalOccupancy
        FROM  dms_dormitory_building b
                  LEFT JOIN dms_dormitory_floor f ON b.id = f.building_id
                  LEFT JOIN dms_dormitory d ON f.id = d.floor_id
        WHERE b.id = 1
    </select>
    <select id="selectBuildingById" resultType="java.util.Map">
        SELECT b.id,b.`name`,b.manager,b.manager_phone managerPhone,b.image,b.`status`,b.remark,
               b.create_by createBy,b.create_time createTime,b.create_by updateBy,b.update_time updateTime,
               i.floor_number floorNumber,i.bed_count bedCount,i.gender,p.`name` parkName
        FROM `dms_dormitory_building` b
                 LEFT JOIN dms_dormitory_building_info i ON b.id = i.building_id
                 LEFT JOIN dms_dormitory_park p ON b.park_id = p.id
        WHERE b.id = #{id}
    </select>
    <select id="getDmsDormitoryList" resultType="java.util.Map">
        SELECT b.id,b.`name`,b.park_id parkId,p.name parkName,b.manager,b.manager_phone managerPhone,
        b.image,b.`status`,b.remark,b.create_by createBy,b.create_time createTime,
        b.update_by updateBy,b.update_time updateTime,
        i.floor_number floorNumber,i.bed_count bedCount,
        case (
        select GROUP_CONCAT( DISTINCT( a.gender ))
        from view_dorm_info as a where a.building_id = b.id
        ) is null when true then '无' else (
        select GROUP_CONCAT( DISTINCT( a.gender ))
        from view_dorm_info as a where a.building_id = b.id
        ) end as gender
        FROM dms_dormitory_building b
        LEFT JOIN dms_dormitory_building_info i ON b.id = i.building_id
        LEFT JOIN dms_dormitory_park p ON b.park_id = p.id

        <where>
            <if test="parkId!=null">and b.park_id = #{parkId}</if>
            <if test="manager!=null">and b.manager = #{manager}</if>
            <if test="gender!=null">and i.gender = #{gender}</if>
        </where>
    </select>
    <select id="selectBuildingNameAndParkName" resultType="com.srs.dormitory.domain.DmsDormitoryBuilding">
        SELECT CONCAT(IF(p.`name` = 'default','',p.`name`),b.`name`) AS `name`,b.id
        FROM dms_dormitory_building b
        LEFT JOIN dms_dormitory_park p ON b.park_id = p.id
    </select>

    <update id="updateDormitoryGenderBatch" parameterType="com.srs.dormitory.domain.DmsDormitoryBuildingInfo">
        update    dms_dormitory   a
            left join  dms_dormitory_floor   b  on  a. floor_id  =  b.id
                left join dms_dormitory_building_info c on b.building_id = c.building_id  set a.gender=#{gender}
        where c.id=#{id}
    </update>

</mapper>
