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

    <select id="listPresecretByCondition" resultType="com.huiju.estateagents.entity.TaPreselection">
        SELECT
            t.*,
            t2.sales_number,t2.sales_batch_name,t2.remark,
            t2.sales_number,
            t2.remark,
            t2.status salesBatchStatus,
            t3.building_name,
            ( SELECT sum( th.real_heat ) FROM ta_housing_resources th WHERE th.sales_batch_id = t2.sales_batch_id AND th.`status` = 1 ) real_heat
        FROM
            ta_preselection t
        LEFT JOIN ta_sales_batch t2 ON t.sales_batch_id = t2.sales_batch_id
        LEFT JOIN ta_building t3 ON t.building_id = t3.building_id
        WHERE
            t.org_id = #{orgId}
            AND t.STATUS != - 1
            AND t2.STATUS != -1
        <if test="buildingId != null and buildingId != ''">
            and t2.building_id = #{buildingId}
        </if>
        <if test="salesBatchName != null and salesBatchName != ''">
            and t2.sales_batch_name like CONCAT('%',#{salesBatchName}, '%')
        </if>
        <if test="status != null">
            and t2.status = #{status}
        </if>
        <if test="personBuildingList != null and personBuildingList.size > 0">
            AND t2.building_id in
            <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                #{personBuilding.buildingId}
            </foreach>
        </if>
        ORDER BY
        t.create_date DESC
    </select>

    <select id="getPresecretById" resultType="com.huiju.estateagents.entity.TaPreselection">
        SELECT
            t3.*,
            t4.building_name
        FROM
            (
        SELECT
            t.*,
            t2.qr_code,
            t2.remark,
            t2.sales_batch_name
        FROM
            ta_preselection t,
            ta_sales_batch t2
        WHERE
            t.sales_batch_id = t2.sales_batch_id
            AND t.preselection_id = #{id}
            AND t.org_id = #{orgId}
            ) t3
            LEFT JOIN ta_building t4 ON t3.building_id = t4.building_id
    </select>

</mapper>
