<?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.huiju.estateagents.property.dao.TpRoomNoMapper">

    <select id="selectCommunityIdList" resultType="com.huiju.estateagents.property.model.TpRoomNo">
        SELECT
            t.*
        FROM
            tp_room_no t
            INNER JOIN tp_level s ON s.id = t.level_id
            INNER JOIN tp_unit m ON m.id = t.unit_id
            INNER JOIN tp_building n ON n.id = t.building_id
            INNER JOIN tp_phase q ON q.id = t.phase_id
        WHERE
            t.org_id = #{orgId}
            AND t.community_id = #{communityId}
        LIMIT #{pageNum}, #{pageSize}
    </select>
    <select id="selectCommunityIdCount" resultType="java.lang.Long">
        SELECT
            count(1)
        FROM
            tp_room_no t
            INNER JOIN tp_level s ON s.id = t.level_id
            INNER JOIN tp_unit m ON m.id = t.unit_id
            INNER JOIN tp_building n ON n.id = t.building_id
            INNER JOIN tp_phase q ON q.id = t.phase_id
        WHERE
            t.org_id = #{orgId}
        AND t.community_id = #{communityId}
    </select>
    <select id="selectCommunityIdAndAddressName" resultType="com.huiju.estateagents.property.model.TpRoomNo">
        SELECT
            t.*
        FROM
            tp_room_no t
            INNER JOIN tp_level s ON s.id = t.level_id
            INNER JOIN tp_unit m ON m.id = t.unit_id
            INNER JOIN tp_building n ON n.id = t.building_id
            INNER JOIN tp_phase q ON q.id = t.phase_id
        WHERE
            t.org_id = #{orgId}
            AND s.`name` = #{levelName}
            AND m.`name` = #{unitName}
            AND n.`name` = #{buildingName}
            AND q.`name` = #{phaseName}
            AND t.`name` = #{roomNoName}
    </select>
    <select id="selectByAddress" resultType="com.huiju.estateagents.property.model.TpRoomNo">
        SELECT
            t.*
        FROM
            tp_room_no t
            INNER JOIN tp_level s ON s.id = t.level_id
            INNER JOIN tp_unit m ON m.id = t.unit_id
            INNER JOIN tp_building n ON n.id = t.building_id
            INNER JOIN tp_phase q ON q.id = t.phase_id
        WHERE
            t.org_id = #{orgId}
            AND s.id = #{levelId}
            AND m.id = #{unitId}
            AND n.id = #{buildingId}
            AND q.id = #{phaseId}
    </select>
    <select id="selectComunityList" resultType="com.huiju.estateagents.property.model.TpRoomNo">
        SELECT
        t.*
        FROM
        tp_room_no t
        INNER JOIN tp_level s ON s.id = t.level_id
        INNER JOIN tp_unit m ON m.id = t.unit_id
        INNER JOIN tp_building n ON n.id = t.building_id
        INNER JOIN tp_phase q ON q.id = t.phase_id
        WHERE
        t.org_id = #{orgId}
        and t.community_id = #{communityId}
        <if test="phaseId != null and phaseId != ''" >
            and t.phase_id = #{phaseId,jdbcType=VARCHAR}
        </if>
        <if test="buildingId != null and buildingId != ''" >
            and t.building_id = #{buildingId,jdbcType=VARCHAR}
        </if>
        LIMIT #{pageNum}, #{pageSize}
    </select>
    <select id="getRoomList" resultType="com.huiju.estateagents.property.model.TpRoomNo">
        SELECT DISTINCT
            t.id,
            t.name,
            q.id as phase_id,
            q.name as phase_name,
            n.id as building_id,
            n.name as building_name,
            m.id as unit_id,
            m.name as unit_name,
            s.id as level_id,
            s.name as level_name,
            t.sort,
            t.create_date
        FROM
            tp_phase q
            LEFT JOIN tp_building n ON n.phase_id = q.id
            LEFT JOIN tp_unit m ON m.building_id = n.id
            LEFT JOIN tp_level s ON s.unit_id = m.id
            LEFT JOIN tp_room_no t ON t.level_id = s.id
        WHERE
            q.org_id = #{orgId}
        <if test="phaseId != null" >
            and q.id = #{phaseId}
        </if>
        <if test="buildingId != null" >
            and n.id = #{buildingId}
        </if>
        <if test="unitId != null" >
            and m.id = #{unitId}
        </if>
        <if test="levelId != null" >
            and s.id = #{levelId}
        </if>
        <if test="roomNoId != null" >
            and t.id = #{roomNoId}
        </if>
        ORDER BY q.id ASC, n.id ASC, m.id ASC, (s.name - 0) DESC
    </select>
    <select id="getRoomNum" resultType="java.lang.Integer">
        SELECT
            count(1)
        FROM
            tp_room_no t
            INNER JOIN tp_level s ON s.id = t.level_id
            INNER JOIN tp_unit m ON m.id = t.unit_id
            INNER JOIN tp_building n ON n.id = t.building_id
            INNER JOIN tp_phase q ON q.id = t.phase_id
        WHERE
            t.org_id = #{orgId}
            AND t.community_id = #{communityId}
    </select>
</mapper>
