<?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.tourism.webadmin.back.dao.TourHouseSaleInfoMapper">
    <resultMap id="BaseResultMap" type="com.tourism.webadmin.back.model.TourHouseSaleInfo">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="area_id" jdbcType="BIGINT" property="areaId"/>
        <result column="is_sale_by_landlord" jdbcType="TINYINT" property="isSaleByLandlord"/>
        <result column="house_source_type" jdbcType="TINYINT" property="houseSourceType"/>
        <result column="total_price" jdbcType="DECIMAL" property="totalPrice"/>
        <result column="office_type" jdbcType="INTEGER" property="officeType"/>
        <result column="area" jdbcType="INTEGER" property="area"/>
        <result column="house_type" jdbcType="INTEGER" property="houseType"/>
        <result column="orientation" jdbcType="INTEGER" property="orientation"/>
        <result column="current_floor" jdbcType="INTEGER" property="currentFloor"/>
        <result column="decoration" jdbcType="INTEGER" property="decoration"/>
        <result column="house_age" jdbcType="INTEGER" property="houseAge"/>
        <result column="community_name" jdbcType="VARCHAR" property="communityName"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="average_price" jdbcType="DECIMAL" property="averagePrice"/>
        <result column="currency" jdbcType="VARCHAR" property="currency"/>
        <result column="region" jdbcType="VARCHAR" property="region"/>
        <result column="cover_url" jdbcType="VARCHAR" property="coverUrl"/>
        <result column="is_on_sale" jdbcType="TINYINT" property="isOnSale"/>
        <result column="house_title" jdbcType="VARCHAR" property="houseTitle"/>
        <result column="total_floor" jdbcType="INTEGER" property="totalFloor"/>
        <result column="year_of_build" jdbcType="DATE" property="yearOfBuild"/>
        <result column="tag" jdbcType="VARCHAR" property="tag"/>
        <result column="year_of_finish" jdbcType="DATE" property="yearOfFinish"/>
        <result column="developers_id" jdbcType="BIGINT" property="developersId"/>
        <result column="code" jdbcType="VARCHAR" property="code"/>
        <result column="mobile" jdbcType="VARCHAR" property="mobile"/>
        <result column="property_right_nature" jdbcType="VARCHAR" property="propertyRightNature"/>
        <result column="term_of_ownership" jdbcType="VARCHAR" property="termOfOwnership"/>
        <result column="property_type" jdbcType="VARCHAR" property="propertyType"/>
        <result column="house_principal_life" jdbcType="VARCHAR" property="housePrincipalLife"/>
        <result column="is_sole_dwelling" jdbcType="TINYINT" property="isSoleDwelling"/>
        <result column="publishing_company" jdbcType="VARCHAR" property="publishingCompany"/>
        <result column="business_license" jdbcType="VARCHAR" property="businessLicense"/>
        <result column="release_date" jdbcType="DATE" property="releaseDate"/>
        <result column="opening_time" jdbcType="DATE" property="openingTime"/>
        <result column="handing_time" jdbcType="DATE" property="handingTime"/>
        <result column="surrounding_support" jdbcType="VARCHAR" property="surroundingSupport"/>
        <result column="plot_introduction" jdbcType="VARCHAR" property="plotIntroduction"/>
        <result column="data_state" jdbcType="TINYINT" property="dataState"/>
        <result column="hot_value" jdbcType="INTEGER" property="hotValue"/>
        <result column="create_user_id" jdbcType="BIGINT" property="createUserId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_user_id" jdbcType="BIGINT" property="updateUserId"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="house_url" jdbcType="VARCHAR" property="houseUrl"/>
        <result column="surroundings_url" jdbcType="VARCHAR" property="surroundingsUrl"/>
        <result column="introduction_url" jdbcType="VARCHAR" property="introductionUrl"/>
    </resultMap>

    <insert id="insertList">
        INSERT INTO tour_house_sale_info
            (id,
            area_id,
            is_sale_by_landlord,
            house_source_type,
            total_price,
            office_type,
            area,
            house_type,
            orientation,
            current_floor,
            decoration,
            house_age,
            community_name,
            address,
            average_price,
            currency,
            region,
            cover_url,
            is_on_sale,
            house_title,
            total_floor,
            year_of_build,
            tag,
            year_of_finish,
            developers_id,
            code,
            mobile,
            property_right_nature,
            term_of_ownership,
            property_type,
            house_principal_life,
            is_sole_dwelling,
            publishing_company,
            business_license,
            release_date,
            opening_time,
            handing_time,
            surrounding_support,
            plot_introduction,
            data_state,
            hot_value,
            create_user_id,
            create_time,
            update_user_id,
            update_time,
            house_url,
            surroundings_url,
            introduction_url)
        VALUES
        <foreach collection="list" index="index" item="item" separator="," >
            (#{item.id},
            #{item.areaId},
            #{item.isSaleByLandlord},
            #{item.houseSourceType},
            #{item.totalPrice},
            #{item.officeType},
            #{item.area},
            #{item.houseType},
            #{item.orientation},
            #{item.currentFloor},
            #{item.decoration},
            #{item.houseAge},
            #{item.communityName},
            #{item.address},
            #{item.averagePrice},
            #{item.currency},
            #{item.region},
            #{item.coverUrl},
            #{item.isOnSale},
            #{item.houseTitle},
            #{item.totalFloor},
            #{item.yearOfBuild},
            #{item.tag},
            #{item.yearOfFinish},
            #{item.developersId},
            #{item.code},
            #{item.mobile},
            #{item.propertyRightNature},
            #{item.termOfOwnership},
            #{item.propertyType},
            #{item.housePrincipalLife},
            #{item.isSoleDwelling},
            #{item.publishingCompany},
            #{item.businessLicense},
            #{item.releaseDate},
            #{item.openingTime},
            #{item.handingTime},
            #{item.surroundingSupport},
            #{item.plotIntroduction},
            #{item.dataState},
            #{item.hotValue},
            #{item.createUserId},
            #{item.createTime},
            #{item.updateUserId},
            #{item.updateTime},
            #{item.houseUrl},
            #{item.surroundingsUrl},
            #{item.introductionUrl})
        </foreach>
    </insert>

    <!-- 如果有逻辑删除字段过滤，请写到这里 -->
    <sql id="filterRef">
        <!-- 这里必须加上全包名，否则当filterRef被其他Mapper.xml包含引用的时候，就会调用Mapper.xml中的该SQL片段 -->
        <include refid="com.tourism.webadmin.back.dao.TourHouseSaleInfoMapper.inputFilterRef"/>
        AND tour_house_sale_info.data_state = ${@com.tourism.common.core.constant.GlobalDeletedFlag@NORMAL}
    </sql>

    <!-- 这里仅包含调用接口输入的主表过滤条件 -->
    <sql id="inputFilterRef">
        <if test="tourHouseSaleInfoFilter != null">
            <if test="tourHouseSaleInfoFilter.areaId != null">
                AND tour_house_sale_info.area_id = #{tourHouseSaleInfoFilter.areaId}
            </if>
            <if test="tourHouseSaleInfoFilter.isSaleByLandlord != null">
                AND tour_house_sale_info.is_sale_by_landlord = #{tourHouseSaleInfoFilter.isSaleByLandlord}
            </if>
            <if test="tourHouseSaleInfoFilter.houseSourceType != null">
                AND tour_house_sale_info.house_source_type = #{tourHouseSaleInfoFilter.houseSourceType}
            </if>
            <if test="tourHouseSaleInfoFilter.totalPriceStart != null">
                AND tour_house_sale_info.total_price &gt;= #{tourHouseSaleInfoFilter.totalPriceStart}
            </if>
            <if test="tourHouseSaleInfoFilter.totalPriceEnd != null">
                AND tour_house_sale_info.total_price &lt;= #{tourHouseSaleInfoFilter.totalPriceEnd}
            </if>
            <if test="tourHouseSaleInfoFilter.officeType != null">
                AND tour_house_sale_info.office_type = #{tourHouseSaleInfoFilter.officeType}
            </if>
            <if test="tourHouseSaleInfoFilter.areaStart != null">
                AND tour_house_sale_info.area &gt;= #{tourHouseSaleInfoFilter.areaStart}
            </if>
            <if test="tourHouseSaleInfoFilter.areaEnd != null">
                AND tour_house_sale_info.area &lt;= #{tourHouseSaleInfoFilter.areaEnd}
            </if>
            <if test="tourHouseSaleInfoFilter.houseType != null">
                AND tour_house_sale_info.house_type = #{tourHouseSaleInfoFilter.houseType}
            </if>
            <if test="tourHouseSaleInfoFilter.orientation != null">
                AND tour_house_sale_info.orientation = #{tourHouseSaleInfoFilter.orientation}
            </if>
            <if test="tourHouseSaleInfoFilter.currentFloorStart != null">
                AND tour_house_sale_info.current_floor &gt;= #{tourHouseSaleInfoFilter.currentFloorStart}
            </if>
            <if test="tourHouseSaleInfoFilter.currentFloorEnd != null">
                AND tour_house_sale_info.current_floor &lt;= #{tourHouseSaleInfoFilter.currentFloorEnd}
            </if>
            <if test="tourHouseSaleInfoFilter.decoration != null">
                AND tour_house_sale_info.decoration = #{tourHouseSaleInfoFilter.decoration}
            </if>
            <if test="tourHouseSaleInfoFilter.houseAgeStart != null">
                AND tour_house_sale_info.house_age &gt;= #{tourHouseSaleInfoFilter.houseAgeStart}
            </if>
            <if test="tourHouseSaleInfoFilter.houseAgeEnd != null">
                AND tour_house_sale_info.house_age &lt;= #{tourHouseSaleInfoFilter.houseAgeEnd}
            </if>
            <if test="tourHouseSaleInfoFilter.communityName != null and tourHouseSaleInfoFilter.communityName != ''">
                <bind name = "safeTourHouseSaleInfoCommunityName" value = "'%' + tourHouseSaleInfoFilter.communityName + '%'" />
                AND tour_house_sale_info.community_name LIKE #{safeTourHouseSaleInfoCommunityName}
            </if>
            <if test="tourHouseSaleInfoFilter.address != null and tourHouseSaleInfoFilter.address != ''">
                <bind name = "safeTourHouseSaleInfoAddress" value = "'%' + tourHouseSaleInfoFilter.address + '%'" />
                AND tour_house_sale_info.address LIKE #{safeTourHouseSaleInfoAddress}
            </if>
            <if test="tourHouseSaleInfoFilter.isOnSale != null">
                AND tour_house_sale_info.is_on_sale = #{tourHouseSaleInfoFilter.isOnSale}
            </if>
            <if test="tourHouseSaleInfoFilter.isSoleDwelling != null">
                AND tour_house_sale_info.is_sole_dwelling = #{tourHouseSaleInfoFilter.isSoleDwelling}
            </if>
            <if test="tourHouseSaleInfoFilter.dataState != null">
                AND tour_house_sale_info.data_state = #{tourHouseSaleInfoFilter.dataState}
            </if>
<!--            <if test="tourHouseSaleInfoFilter.searchString != null and tourHouseSaleInfoFilter.searchString != ''">-->
<!--                <bind name = "safeTourHouseSaleInfoSearchString" value = "'%' + tourHouseSaleInfoFilter.searchString + '%'" />-->
<!--                AND CONCAT(IFNULL(tour_house_sale_info.community_name,''), IFNULL(tour_house_sale_info.address,'')) LIKE #{safeTourHouseSaleInfoSearchString}-->
<!--            </if>-->
        </if>
    </sql>

    <select id="getTourHouseSaleInfoList" resultMap="BaseResultMap" parameterType="com.tourism.webadmin.back.model.TourHouseSaleInfo">
        SELECT * FROM tour_house_sale_info
        <where>
            <include refid="filterRef"/>
        </where>
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
        </if>
    </select>

    <!-- 支持基于一对一或者一对多从表字段过滤的SQL_ID -->
    <select id="getTourHouseSaleInfoListEx" resultMap="BaseResultMap" >
        SELECT
        tour_house_sale_info.*
        FROM
        tour_house_sale_info
        LEFT JOIN
        tour_house_developers_type ON tour_house_sale_info.developers_id = tour_house_developers_type.id AND tour_house_developers_type.data_state = ${@com.tourism.common.core.constant.GlobalDeletedFlag@NORMAL}
        <where>
            <include refid="filterRef"/>
            <include refid="com.tourism.webadmin.back.dao.TourHouseDevelopersTypeMapper.inputFilterRef"/>
            <if test="tourHouseFilesFilter != null">
                AND EXISTS (SELECT * FROM tour_house_files
                <where>
                    tour_house_sale_info.id = tour_house_files.tour_house_id
                    <include refid="com.tourism.webadmin.back.dao.TourHouseFilesMapper.filterRef"/>
                </where>)
            </if>
<!--            <if test="tourHouseAdvisorInfoFilter != null">-->
<!--                AND EXISTS (SELECT * FROM tour_house_advisor_info-->
<!--                <where>-->
<!--                    tour_house_sale_info.advisor_ids = tour_house_advisor_info.id-->
<!--                    <include refid="com.tourism.webadmin.back.dao.TourHouseAdvisorInfoMapper.filterRef"/>-->
<!--                </where>)-->
<!--            </if>-->
        </where>
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
        </if>
    </select>


    <!-- 区域条件&房屋条件混合查询 -->
    <select id="getTourHouseSaleInfoListWithArea" resultMap="BaseResultMap" parameterType="com.tourism.webadmin.back.model.TourHouseSaleInfo">
        SELECT tour_house_sale_info.*
        FROM tour_house_sale_info
        <where>
            <if test="tourHouseSaleInfoFilter != null">
                <if test="tourHouseSaleInfoFilter.areaIds != null and tourHouseSaleInfoFilter.areaIds.size > 0">
                    AND tour_house_sale_info.area_id IN
                    <foreach collection="tourHouseSaleInfoFilter.areaIds" item="areaId" open="(" close=")" separator=",">
                        #{areaId}
                    </foreach>
                </if>
            </if>

            <include refid="com.tourism.webadmin.back.dao.TourHouseSaleInfoMapper.inputFilterRef"/>
            AND tour_house_sale_info.data_state = ${@com.tourism.common.core.constant.GlobalDeletedFlag@NORMAL}
            <if test="tourHouseSaleInfoFilter.searchString != null and tourHouseSaleInfoFilter.searchString != ''">
                and
                (
                tour_house_sale_info.community_name like concat('%',#{tourHouseSaleInfoFilter.searchString},'%') or
                tour_house_sale_info.address like concat('%',#{tourHouseSaleInfoFilter.searchString},'%') or
                tour_house_sale_info.house_title like concat('%',#{tourHouseSaleInfoFilter.searchString},'%')
                )
            </if>
        </where>
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
        </if>
    </select>


</mapper>
