<?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.TaPreselectionRecordMapper">

    <select id="listPreselectionRecord" resultType="com.huiju.estateagents.po.TaPreselectionRecordPO">
        SELECT
            t.*,
            t2.term_name,
            t2.block_name,
            t2.unit_name,
            t2.floor_name,
            t2.room_name,
            t2.price,
            t2.apartment_id,
            t2.building_id buildingId,
            t2.`status` housingStatus,
            t3.`status` saleBatchStatus,
            t3.display_house_price
        FROM
            ta_preselection_record t
        LEFT JOIN ta_housing_resources t2 ON t.house_id = t2.house_id
        LEFT JOIN ta_sales_batch t3 ON t.sales_batch_id = t3.sales_batch_id
        <if test = "source == 'raiseChoose'">
            LEFT JOIN ta_raise t4 ON t3.sales_batch_id = t4.sales_batch_id
        </if>
        WHERE
            t.person_id = #{personId}
            AND t.org_id = #{orgId}
            AND t.`status` = 1
        <if test="source == 'raiseChoose'">
            AND t2.`status` = 1
            AND t3.`status` = 1
            AND t4.raise_start_time &lt;= now( ) AND t4.raise_end_time &gt;= now( )
        </if>
        <if test="customerLocked == 'mine'">
            AND t2.house_locking_person = #{personId}
        </if>
        <if test="customerLocked == 'other'">
            AND t2.house_locking_person != #{personId}
        </if>
        <if test="buildingId != null and buildingId != ''">
            AND t2.building_id = #{buildingId}
        </if>
        <if test="salesBatchId != null and salesBatchId != ''">
            AND t2.sales_batch_id = #{salesBatchId}
        </if>
        <if test="apartmentId != null and apartmentId != ''">
            AND t2.apartment_id = #{apartmentId}
        </if>
        <if test="houseLockingStatus == 'locked'">
            AND t2.house_locking_status = 'locked'
        </if>
        <if test="houseLockingStatus == 'unlocked'">
            AND ( t2.house_locking_status != 'locked' OR t2.house_locking_status IS NULL )
        </if>
        GROUP BY
         t.preselection_record_id
        ORDER BY
          t.create_date DESC
    </select>

    <select id="getRecordByPersonIdAndHouseId" resultType="com.huiju.estateagents.entity.TaPreselectionRecord">
        SELECT
            t.*
        FROM
            ta_preselection_record t
        WHERE
            t.person_id = #{personId}
            AND t.house_id = #{houseId}
            AND t.status = 1
    </select>

    <select id="selectPreSelectRecordList" resultType="com.huiju.estateagents.po.TaPreselectionRecordPO">

        SELECT
          *
        FROM
          (
        SELECT
            t.preselection_record_id,
            t.house_id,
            a.block_name,
            a.unit_name,
            a.floor_name,
            a.room_name,
            a.apartment_id,
            b.nickname AS nameOrnick,
            b.`name`,
            b.avatarurl,
            b.phone,
            a.term_name,
            t.`status`,
            t.update_date,
            t.building_id,
            t.sales_batch_id,
            e.apartment_name,
            b.person_id
        FROM
            ta_preselection_record t
        LEFT JOIN ta_housing_resources a ON t.house_id = a.house_id
        LEFT JOIN ta_person b ON t.person_id = b.person_id
        LEFT JOIN ta_building_apartment e ON a.apartment_id = e.apartment_id
        WHERE
            b.org_id = #{bo.orgId} and t.status != -1
            AND a.sales_batch_id =  #{bo.salesBatchId}
            <if test="bo.termName != null and bo.termName != ''">
                AND a.term_name like CONCAT('%',#{bo.termName}, '%')
            </if>
            <if test="bo.blockName != null and bo.blockName != ''">
                AND a.block_name like CONCAT('%',#{bo.blockName}, '%')
            </if>
            <if test="bo.unitName != null and bo.unitName != ''">
                AND a.unit_name like CONCAT('%',#{bo.unitName}, '%')
            </if>
            <if test="bo.floorName != null and bo.floorName != ''">
                AND a.floor_name like CONCAT('%',#{bo.floorName}, '%')
            </if>
            <if test="bo.roomName != null and bo.roomName != ''">
                AND a.room_name like CONCAT('%',#{bo.roomName}, '%')
            </if>
            <if test="bo.houseId != null and bo.houseId !=''">
                AND t.house_id like CONCAT('%',#{bo.houseId}, '%')
            </if>
            <if test="bo.phone != null and bo.phone != ''">
                AND b.phone like CONCAT('%',#{bo.phone}, '%')
            </if>
            <if test="bo.apartmentId != null and bo.apartmentId != ''">
                AND a.apartment_id = #{bo.apartmentId}
            </if>
            <if test="bo.status != null">
                AND t.status = #{bo.status}
            </if>
            <if test="bo.startTime != null and bo.startTime != ''">
                AND STR_TO_DATE(t.update_date, '%Y-%m-%d %H:%i:%s') &gt;= STR_TO_DATE(#{bo.startTime}, '%Y-%m-%d %H:%i:%s')
            </if>
            <if test="bo.endTime != null and bo.endTime != ''">
                AND STR_TO_DATE(t.update_date, '%Y-%m-%d %H:%i:%s') &lt;= STR_TO_DATE(#{bo.endTime}, '%Y-%m-%d %H:%i:%s')
            </if>
        ORDER BY
            t.create_date DESC
        ) t
        GROUP BY
            t.house_id,
            t.person_id,
            t.status

    </select>

    <update id="batchDeletePreselectRecord">
        UPDATE ta_preselection_record t
          SET t.`status` = - 1,
                t.update_date = now(),
                t.update_user = #{userId}
        WHERE
            t.org_id = #{orgId}
            AND t.preselection_record_id IN
        <foreach collection="preselectionRecordList" item="record" open="(" close=")" separator=",">
            #{record.preselectionRecordId}
        </foreach>
    </update>

</mapper>
