<?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.api.manage.dao.basicArchives.CpmBuildingUnitEstateDao">
    <select id="list" parameterType="com.api.model.basicArchives.SearchCpmBuildingUnitEstate" resultType="com.api.vo.basicArchives.VoCpmBuildingUnitEstate">
        select a.id,a.building_id,a.building_name,a.building_no,a.building_unit_id,a.building_unit_no,a.room_number,a.status,a.type,a.construction_area,a.indoor_area,group_concat(b.name) as user_resident_name,group_concat(b.tel) as user_resident_tel
        from (
        select cbue.*,cbu.no as building_unit_no,cb.id as building_id, cb.name as building_name,cb.no as building_no
        from cpm_building_unit_estate cbue,cpm_building cb,cpm_building_unit cbu
        where cbue.building_unit_id = cbu.id and cbu.building_id = cb.id
        ) as a LEFT JOIN (
        select ur.name,ur.tel,cre.building_unit_estate_id
        from user_resident as ur,cpm_resident_estate as cre
        where ur.id = cre.resident_id
        ) as b
        on a.id = b.building_unit_estate_id
        <where>
            and a.is_delete = 1
            <if test="buildingNo != null and buildingNo != ''">
                and a.building_no like concat('%',#{buildingNo},'%')
            </if>
            <if test="unitNo != null">
                and a.building_unit_no like concat('%',#{unitNo},'%')
            </if>
            <if test="roomNumber != null and roomNumber != ''">
                and a.room_number like concat('%',#{roomNumber},'%')
            </if>
            <if test="status != null">
                and a.status = #{status}
            </if>
            <if test="residentName != null and residentName != ''">
                and b.name like concat('%',#{residentName},'%')
            </if>
            <if test="residentTel != null and residentTel != ''">
                and b.tel like concat('%',#{residentTel},'%')
            </if>
        </where>
        GROUP BY a.id
        order by a.create_date desc
    </select>


    <insert id="insert" parameterType="com.api.model.basicArchives.CpmBuildingUnitEstate">
        insert into cpm_building_unit_estate(
            building_unit_id,
            room_number,
            status,
            type,
            construction_area,
            indoor_area,
            create_id,
            create_date,
            <if test="deviceNumber != null and deviceNumber != ''">
                device_number,
            </if>
            is_delete
        )
        values (
            #{buildingUnitId},
            #{roomNumber},
            #{status},
            #{type},
            #{constructionArea},
            #{indoorArea},
            #{createId},
            #{createDate},
            <if test="deviceNumber != null and deviceNumber != ''">
                #{deviceNumber},
            </if>
            #{isDelete}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.model.basicArchives.CpmBuildingUnitEstate">
        select cbue.*,cbu.no as building_unit_no,cb.name as building_name
        from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where cbue.is_delete = 1 and cbue.id = #{id} and cbue.building_unit_id = cbu.id
        and cbu.building_id = cb.id
    </select>

    <update id="update" parameterType="com.api.model.basicArchives.CpmBuildingUnitEstate">
        update cpm_building_unit_estate
        <set>
            <if test="buildingUnitId != null">
                building_unit_id = #{buildingUnitId},
            </if>
            <if test="roomNumber != null and roomNumber != ''">
                room_number = #{roomNumber},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="type != null">
                type = #{type},
            </if>
            <if test="constructionArea != null">
                construction_area = #{constructionArea},
            </if>
            <if test="indoorArea != null">
                indoor_area = #{indoorArea},
            </if>
            <if test="createId != null">
                create_id = #{createId},
            </if>
            <if test="createDate != null">
                create_date = #{createDate},
            </if>
            <if test="modifyId != null">
                modify_id = #{modifyId},
            </if>
            <if test="modifyDate != null">
                modify_date = #{modifyDate},
            </if>
            <if test="deviceNumber != null and deviceNumber != ''">
                device_number = #{deviceNumber},
            </if>
        </set>
        where id =#{id}
    </update>

    <update id="delete" parameterType="java.lang.Integer">
        update cpm_building_unit_estate set is_delete = 0 where id = #{id}
    </update>

<!--    根据业主id查询房产信息-->
    <select id="findByResidentId" parameterType="java.lang.Integer" resultType="com.api.model.basicArchives.CpmBuildingUnitEstate">
        select cbue.id,cbue.building_unit_id,cbue.room_number from user_resident as ur,cpm_resident_estate as cre,cpm_building_unit_estate as cbue
		where ur.id = cre.resident_id and cbue.id =cre.building_unit_estate_id and ur.id =#{id}
    </select>

<!--    查询所有的房产的id和name-->
    <select id="findAll" resultType="com.api.vo.basicArchives.VoFindAll">
        select id as value,room_number as label
        from cpm_building_unit_estate
        where is_delete =1
    </select>
    <!--    根据租客id查询房产信息-->
    <select id="findByTenantId" parameterType="java.lang.Integer" resultType="com.api.vo.basicArchives.VoTenantCpmBuildingUnitEstate">
        select cbue.id,cbue.building_unit_id,cbue.room_number,cre.effective_time_start,cre.effective_time_end
        from user_resident as ur,cpm_resident_estate as cre,cpm_building_unit_estate as cbue
		where ur.id = cre.resident_id and cbue.id =cre.building_unit_estate_id and ur.id =#{id} and ur.type =3
    </select>

    <select id="findByBuildingUnitId" parameterType="java.lang.Integer" resultType="com.api.vo.basicArchives.VoFindAll">
        select id as value,room_number as label
        from cpm_building_unit_estate
        where is_delete =1 and building_unit_id = #{id}
    </select>

    <select id="findByRoomNumber" parameterType="java.lang.String" resultType="com.api.model.basicArchives.CpmBuildingUnitEstate">
        select * from cpm_building_unit_estate
        where room_number =#{roomNumber}
    </select>

    <select id="findResidentIdByEstateId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select resident_id from cpm_resident_estate where building_unit_estate_id =#{id}
    </select>

    <select id="findByBuildingId" parameterType="java.lang.Integer" resultType="com.api.vo.basicArchives.VoFindAll">
        select cbue.id as value,cbue.room_number as label
        from cpm_building_unit_estate as cbue,cpm_building_unit as cbu
        where cbue.building_unit_id = cbu.id and cbu.building_id = #{buildingId}
    </select>

    <select id="findTimeByEstateId" parameterType="com.api.model.basicArchives.ResidentIdAndEstateId" resultType="com.api.vo.basicArchives.VoEffectiveTimes">
        select * from cpm_resident_estate where building_unit_estate_id = #{estateId} and resident_id = #{residentId}
    </select>
    <select id="findDeviceNumberByEstateId" parameterType="java.lang.Integer" resultType="java.lang.String">
        select device_number from cpm_building_unit_estate where id = #{estateId}
    </select>
    <select id="countByBuildingUnitId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from cpm_building_unit_estate where building_unit_id = #{id}
    </select>
    <select id="findUnitByBuildingId" resultType="com.api.vo.basicArchives.VoFindAll">
        select id as value,no as label from cpm_building_unit where building_id = #{buildingId}
    </select>
    <select id="findEstateIdByUnitId" resultType="com.api.vo.basicArchives.VoFindAll">
        select id as value,room_number as label from cpm_building_unit_estate where building_unit_id = #{unitId}
    </select>
</mapper>