<?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.ruoyi.system.mapper.house.HouseResourceMapper">
    <resultMap id="BaseResultMap" type="com.ruoyi.system.domain.house.HouseResource">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="relationId" column="relation_id" jdbcType="VARCHAR"/>
        <result property="two" column="two" jdbcType="VARCHAR"/>
        <result property="three" column="three" jdbcType="VARCHAR"/>
        <result property="four" column="four" jdbcType="VARCHAR"/>
        <result property="type" column="type" jdbcType="INTEGER"/>
        <result property="projectId" column="project_id" jdbcType="VARCHAR"/>
        <result property="houseResourceUrl" column="house_resource_url" jdbcType="VARCHAR"/>
        <result property="businessLicenseUrl" column="business_license_url" jdbcType="VARCHAR"/>
        <result property="houseNumber" column="house_number" jdbcType="VARCHAR"/>
        <result property="houseArea" column="house_area" jdbcType="DECIMAL"/>
        <result property="houseResourceAttribute" column="house_resource_attribute" jdbcType="INTEGER"/>
        <result property="houseResourceType" column="house_resource_type" jdbcType="INTEGER"/>
        <result property="houseResourceUse" column="house_resource_use" jdbcType="VARCHAR"/>
        <result property="houseResourceEquity" column="house_resource_equity" jdbcType="VARCHAR"/>
        <result property="businessStatus" column="business_status" jdbcType="VARCHAR" />
        <result property="houseResourceEquityTel" column="house_resource_equity_tel" jdbcType="VARCHAR"/>
        <result property="ownership" column="ownership" jdbcType="VARCHAR" />
        <result property="decorationCondition" column="decoration_condition" jdbcType="INTEGER"/>
        <result property="orientation" column="orientation" jdbcType="INTEGER"/>
        <result property="landingHeadFlag" column="landing_head_flag" jdbcType="BIT"/>
        <result property="commonAreaNumber" column="common_area_number" jdbcType="INTEGER"/>
        <result property="officeNumber" column="office_number" jdbcType="INTEGER"/>
        <result property="meetingNumber" column="meeting_number" jdbcType="INTEGER"/>
        <result property="stationNumber" column="station_number" jdbcType="INTEGER"/>
        <result property="setUp" column="set_up" jdbcType="VARCHAR"/>
        <result property="labelId" column="label_id" jdbcType="VARCHAR"/>
        <result property="unitPrice" column="unit_price" jdbcType="DECIMAL"/>
        <result property="rentalUnit" column="rental_unit" jdbcType="INTEGER"/>
        <result property="planeGraphUrl" column="plane_graph_url" jdbcType="VARCHAR"/>
        <result property="hireStatus" column="hire_status" jdbcType="INTEGER"/>
        <result property="address" column="address" jdbcType="VARCHAR"/>
        <result property="lon" column="lon" jdbcType="VARCHAR"/>
        <result property="lat" column="lat" jdbcType="VARCHAR"/>
        <result property="remark" column="remark" jdbcType="VARCHAR"/>
    </resultMap>

    <insert id="insertHouseResource" parameterType="com.ruoyi.system.domain.house.HouseResource">
        INSERT INTO house_resource
        (id, relation_id, two, three, four, type, project_id, house_resource_url, business_license_url, house_number, house_area,
         house_resource_attribute, house_resource_type, house_resource_use, house_resource_equity, business_status, house_resource_equity_tel, ownership,
         decoration_condition, orientation, landing_head_flag, common_area_number, office_number,
         meeting_number, station_number, set_up, label_id, unit_price, rental_unit, plane_graph_url, hire_status, address, lon, lat, remark)
        VALUES
            (#{id}, #{relationId}, #{two}, #{three}, #{four}, #{type}, #{projectId}, #{houseResourceUrl}, #{businessLicenseUrl}, #{houseNumber}, #{houseArea},
             #{houseResourceAttribute}, #{houseResourceType}, #{houseResourceUse}, #{houseResourceEquity}, #{businessStatus}, #{houseResourceEquityTel}, #{ownership},
             #{decorationCondition}, #{orientation}, #{landingHeadFlag}, #{commonAreaNumber}, #{officeNumber},
             #{meetingNumber}, #{stationNumber}, #{setUp}, #{labelId}, #{unitPrice}, #{rentalUnit}, #{planeGraphUrl}, #{hireStatus}, #{address}, #{lon}, #{lat}, #{remark})
    </insert>

    <select id="selectHouseResourceById" resultType="com.ruoyi.system.domain.house.HouseResource" parameterType="string">
        SELECT * FROM house_resource WHERE id = #{id}
    </select>

    <update id="updateHouseResource" parameterType="com.ruoyi.system.domain.house.HouseResource">
        UPDATE house_resource
        SET relation_id = #{relationId},
            two = #{two},
            three = #{three},
            four = #{four},
            type = #{type},
            project_id = #{projectId},
            house_resource_url = #{houseResourceUrl},
            business_license_url = #{businessLicenseUrl},
            house_number = #{houseNumber},
            house_area = #{houseArea},
            house_resource_attribute = #{houseResourceAttribute},
            house_resource_type = #{houseResourceType},
            house_resource_use = #{houseResourceUse},
            house_resource_equity = #{houseResourceEquity},
            business_status = #{businessStatus},
            house_resource_equity_tel = #{houseResourceEquityTel},
            ownership = #{ownership},
            decoration_condition = #{decorationCondition},
            orientation = #{orientation},
            landing_head_flag = #{landingHeadFlag},
            common_area_number = #{commonAreaNumber},
            office_number = #{officeNumber},
            meeting_number = #{meetingNumber},
            station_number = #{stationNumber},
            set_up = #{setUp},
            label_id = #{labelId},
            unit_price = #{unitPrice},
            rental_unit = #{rentalUnit},
            plane_graph_url = #{planeGraphUrl},
            hire_status = #{hireStatus},
            address = #{address},
            lon = #{lon},
            lat = #{lat},
            remark = #{remark}
        WHERE id = #{id}
    </update>

    <delete id="deleteHouseResourceById" parameterType="string">
        DELETE FROM house_resource WHERE id = #{id}
    </delete>

    <insert id="batchInsertHouseResources" parameterType="java.util.List">
        INSERT INTO house_resource
        (id, relation_id, two, three, four, type, project_id, house_resource_url, business_license_url, house_number, house_area,
        house_resource_attribute, house_resource_type, house_resource_use, house_resource_equity, business_status, house_resource_equity_tel, ownership,
        decoration_condition, orientation, landing_head_flag, common_area_number, office_number,
        meeting_number, station_number, set_up, label_id, unit_price, rental_unit, plane_graph_url, hire_status, address, lon, lat, remark)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.id}, #{item.relationId},  #{two}, #{three}, #{four}, #{item.type}, #{item.projectId}, #{item.houseResourceUrl}, #{item.businessLicenseUrl},
            #{item.houseNumber}, #{item.houseArea}, #{item.houseResourceAttribute}, #{item.houseResourceType},
            #{item.houseResourceUse}, #{item.houseResourceEquity}, #{businessStatus}, #{item.houseResourceEquityTel}, #{ownership}, #{item.decorationCondition},
            #{item.orientation}, #{item.landingHeadFlag}, #{item.commonAreaNumber}, #{item.officeNumber},
            #{item.meetingNumber}, #{item.stationNumber}, #{item.setUp}, #{item.labelId}, #{item.unitPrice}, #{item.rentalUnit},
            #{item.planeGraphUrl}, #{item.hireStatus}, #{item.address}, #{item.lon}, #{item.lat}, #{item.remark})
        </foreach>
    </insert>

    <resultMap id="pageResultMap" type="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        <result column="dayPrice" property="dayPrice" javaType="java.math.BigDecimal"/>
        <result column="income" property="income" javaType="java.math.BigDecimal"/>
    </resultMap>

    <select id="selectPage" resultMap="pageResultMap">
        SELECT
        t2.wg_name AS wgName4,
        t1.*,
        SUM(t5.year_sell) AS income,
        t4.nature,
        t4.industry_classification,
        ROUND(IFNULL(t1.unit_price,0) * 10000 / 365 / t1.house_area, 1) AS dayPrice,
        t4.name,
        t5.year
        FROM
        house_resource t1
        LEFT JOIN grid_region t2 ON t2.wg_code = t1.four
        LEFT JOIN house_resource_business_entity_info_mapping t3 ON t3.house_resource_id = t1.id
        LEFT JOIN business_entity_info t4 ON t3.business_entity_info_id = t4.id
        LEFT JOIN business_entity_sell t5 ON t5.business_entity_info_id = t4.id
        <where>
            <if test="query.wgType != null">
                and t1.type = #{query.wgType}
            </if>
            <if test="query.wgTypes != null and query.wgTypes.size() > 0">
                and t1.type in
                <foreach item="item" index="index" collection="query.wgTypes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.two != null and query.two != ''">
                and t1.two = #{query.two}
            </if>
            <if test="query.three != null and query.three != ''">
                and t1.three = #{query.three}
            </if>
            <if test="query.four != null and query.four != ''">
                and t1.four = #{query.four}
            </if>
            <if test="query.houseResourceAttribute != null">
                and t1.house_resource_attribute = #{query.houseResourceAttribute}
            </if>
            <if test="query.hireStatus != null">
                and t1.hire_status = #{query.hireStatus}
            </if>
            <if test="query.houseNumber != null and query.houseNumber != ''">
                and t1.house_number like concat('%', #{query.houseNumber}, '%')
            </if>
            <if test="query.houseAreaMin != null and query.houseAreaMin != ''">
                and t1.house_area <![CDATA[ >= ]]> #{query.houseAreaMin}
            </if>
            <if test="query.houseAreaMax != null and query.houseAreaMax != ''">
                and t1.house_area <![CDATA[ <= ]]> #{query.houseAreaMax}
            </if>
            <if test="query.houseResourceType != null">
                and t1.house_resource_type = #{query.houseResourceType}
            </if>
            <if test="query.name != null and query.name != ''">
                and t4.name like concat('%', #{query.name}, '%')
            </if>
            <if test="query.yearSellMin != null">
                and t5.year_sell <![CDATA[ >= ]]> #{query.yearSellMin}
            </if>
            <if test="query.yearSellMax != null">
                and t5.year_sell <![CDATA[ <= ]]> #{query.yearSellMax}
            </if>
            <if test="query.yearTaxMin != null">
                and t5.year_tax <![CDATA[ >= ]]> #{query.yearTaxMin}
            </if>
            <if test="query.yearTaxMax != null">
                and t5.year_tax <![CDATA[ <= ]]> #{query.yearTaxMax}
            </if>
            <if test="query.unitPriceMin != null">
                and t1.unit_price <![CDATA[ >= ]]> #{query.unitPriceMin}
            </if>
            <if test="query.unitPriceMax != null">
                and t1.unit_price <![CDATA[ <= ]]> #{query.unitPriceMax}
            </if>
            <if test="query.gsFlag != null">
                and t4.gs = ${query.gsFlag}
            </if>
            <if test="query.industryClassification != null and query.industryClassification != ''">
                and t4.industry_classification like concat('%', #{query.industryClassification}, '%')
            </if>
            <if test="query.year != null">
                and t5.`year` = #{query.year}
            </if>
            <if test="query.registrationPlaceFlag != null">
                and t4.registration_place_flag = #{query.registrationPlaceFlag}
            </if>
            <if test="query.address != null and query.address != ''">
                and t1.address like concat('%', #{query.address}, '%')
            </if>
            <if test="query.ownership != null and query.ownership != ''">
                and t1.ownership like concat('%', #{query.ownership}, '%')
            </if>
            <if test="query.houseResourceEquityTel != null and query.houseResourceEquityTel != ''">
                and t1.house_resource_equity_tel like concat('%', #{query.houseResourceEquityTel}, '%')
            </if>
            <if test="query.principal != null and query.principal != ''">
                and t4.principal like concat('%', #{query.principal}, '%')
            </if>
            <if test="query.principalTel != null and query.principalTel != ''">
                and t4.principal_tel like concat('%', #{query.principalTel}, '%')
            </if>
            <if test="query.business != null and query.business != ''">
                and t4.business like concat('%', #{query.business}, '%')
            </if>
            <if test="query.workerNumberMin != null">
                and t4.worker_number <![CDATA[ >= ]]> #{query.workerNumberMin}
            </if>
            <if test="query.workerNumberMax != null">
                and t4.worker_number <![CDATA[ <= ]]> #{query.workerNumberMax}
            </if>
            <if test="query.registeredAddress != null and query.registeredAddress != ''">
                and t4.registered_address like concat('%', #{query.registeredAddress}, '%')
            </if>
            <if test="query.nature != null and query.nature != ''">
                and t4.nature = #{query.nature}
            </if>
            <if test="query.wgCodes != null and query.wgCodes.size() > 0">
                and t1.two in
                <foreach item="item" index="index" collection="query.wgCodes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.aiQueries != null and query.aiQueries.size() > 0">
                and (
                <foreach collection="query.aiQueries" item="ai" separator=" or ">
                    (
                    <if test="ai.two != null and ai.two != ''">
                        t1.two = #{ai.two}
                    </if>
                    <if test="ai.three != null and ai.three != ''">
                        <if test="ai.two != null and ai.two != ''">
                            and
                        </if>
                        t1.three = #{ai.three}
                    </if>
                    <if test="ai.four != null and ai.four != ''">
                        <if test="(ai.two != null and ai.two != '') or (ai.three != null and ai.three != '')">
                            and
                        </if>
                        t1.four = #{ai.four}
                    </if>
                    <if test="ai.wgType != null">
                        <if test="(ai.two != null and ai.two != '') or (ai.three != null and ai.three != '') or (ai.four != null and ai.four != '')">
                            and
                        </if>
                        t1.type = #{ai.wgType}
                    </if>
                    )
                </foreach>
                )
            </if>
        </where>
        GROUP BY t1.id, t4.id
        <trim prefix="having" prefixOverrides="and">
            <if test="query.dayPriceMin != null">
                and dayPrice <![CDATA[ >= ]]> #{query.dayPriceMin}
            </if>
            <if test="query.dayPriceMax != null">
                and dayPrice <![CDATA[ <= ]]> #{query.dayPriceMax}
            </if>
        </trim>
        <if test="query.incomeSort != null and query.incomeSort != ''">
            ORDER BY income ${query.incomeSort}
        </if>
    </select>
    <select id="selectProfileGraph" resultType="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        SELECT
        t2.wg_name AS wgName4,
        t1.*,
        SUM(t5.year_sell) AS income,
        SUM(t5.year_tax) AS taxCount,
        t4.nature,
        t4.industry_classification,
        ROUND(IFNULL(t1.unit_price,0) * 10000 / 365 / t1.house_area, 1) AS dayPrice,
        t4.name,
        t5.year,
        t4.gs,
        t4.id AS businessEntityInfoId
        FROM
        house_resource t1
        LEFT JOIN grid_region t2 ON t2.wg_code = t1.four
        LEFT JOIN house_resource_business_entity_info_mapping t3 ON t3.house_resource_id = t1.id
        LEFT JOIN business_entity_info t4 ON t3.business_entity_info_id = t4.id
        LEFT JOIN business_entity_sell t5 ON t5.business_entity_info_id = t4.id
        <where>
            <if test="query.wgType != null">
                and t1.type = #{query.wgType}
            </if>
            <if test="query.wgTypes != null and query.wgTypes.size() > 0">
                and t1.type in
                <foreach item="item" index="index" collection="query.wgTypes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.two != null and query.two != ''">
                and t1.two = #{query.two}
            </if>
            <if test="query.three != null and query.three != ''">
                and t1.three = #{query.three}
            </if>
            <if test="query.four != null and query.four != ''">
                and t1.four = #{query.four}
            </if>
            <if test="query.houseResourceAttribute != null">
                and t1.house_resource_attribute = #{query.houseResourceAttribute}
            </if>
            <if test="query.hireStatus != null">
                and t1.hire_status = #{query.hireStatus}
            </if>
            <if test="query.houseNumber != null and query.houseNumber != ''">
                and t1.house_number like concat('%', #{query.houseNumber}, '%')
            </if>
            <if test="query.houseAreaMin != null and query.houseAreaMin != ''">
                and t1.house_area <![CDATA[ >= ]]> #{query.houseAreaMin}
            </if>
            <if test="query.houseAreaMax != null and query.houseAreaMax != ''">
                and t1.house_area <![CDATA[ <= ]]> #{query.houseAreaMax}
            </if>
            <if test="query.houseResourceType != null">
                and t1.house_resource_type = #{query.houseResourceType}
            </if>
            <if test="query.name != null and query.name != ''">
                and t4.name like concat('%', #{query.name}, '%')
            </if>
            <if test="query.yearSellMin != null">
                and t5.year_sell <![CDATA[ >= ]]> #{query.yearSellMin}
            </if>
            <if test="query.yearSellMax != null">
                and t5.year_sell <![CDATA[ <= ]]> #{query.yearSellMax}
            </if>
            <if test="query.yearTaxMin != null">
                and t5.year_tax <![CDATA[ >= ]]> #{query.yearTaxMin}
            </if>
            <if test="query.yearTaxMax != null">
                and t5.year_tax <![CDATA[ <= ]]> #{query.yearTaxMax}
            </if>
            <if test="query.unitPriceMin != null">
                and t1.unit_price <![CDATA[ >= ]]> #{query.unitPriceMin}
            </if>
            <if test="query.unitPriceMax != null">
                and t1.unit_price <![CDATA[ <= ]]> #{query.unitPriceMax}
            </if>
            <if test="query.gsFlag != null">
                and t4.gs = ${query.gsFlag}
            </if>
            <if test="query.industryClassification != null and query.industryClassification != ''">
                and t4.industry_classification like concat('%', #{query.industryClassification}, '%')
            </if>
            <if test="query.year != null">
                and t5.`year` = #{query.year}
            </if>
            <if test="query.registrationPlaceFlag != null">
                and t4.registration_place_flag = #{query.registrationPlaceFlag}
            </if>
            <if test="query.address != null and query.address != ''">
                and t1.address like concat('%', #{query.address}, '%')
            </if>
            <if test="query.ownership != null and query.ownership != ''">
                and t1.ownership like concat('%', #{query.ownership}, '%')
            </if>
            <if test="query.houseResourceEquityTel != null and query.houseResourceEquityTel != ''">
                and t1.house_resource_equity_tel like concat('%', #{query.houseResourceEquityTel}, '%')
            </if>
            <if test="query.principal != null and query.principal != ''">
                and t4.principal like concat('%', #{query.principal}, '%')
            </if>
            <if test="query.principalTel != null and query.principalTel != ''">
                and t4.principal_tel like concat('%', #{query.principalTel}, '%')
            </if>
            <if test="query.business != null and query.business != ''">
                and t4.business like concat('%', #{query.business}, '%')
            </if>
            <if test="query.workerNumberMin != null">
                and t4.worker_number <![CDATA[ >= ]]> #{query.workerNumberMin}
            </if>
            <if test="query.workerNumberMax != null">
                and t4.worker_number <![CDATA[ <= ]]> #{query.workerNumberMax}
            </if>
            <if test="query.registeredAddress != null and query.registeredAddress != ''">
                and t4.registered_address like concat('%', #{query.registeredAddress}, '%')
            </if>
            <if test="query.nature != null and query.nature != ''">
                and t4.nature = #{query.nature}
            </if>
            <if test="query.wgCodes != null and query.wgCodes.size() > 0">
                and t1.two in
                <foreach item="item" index="index" collection="query.wgCodes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.aiQueries != null and query.aiQueries.size() > 0">
                and (
                <foreach collection="query.aiQueries" item="ai" separator=" or ">
                    (
                    <if test="ai.two != null and ai.two != ''">
                        t1.two = #{ai.two}
                    </if>
                    <if test="ai.three != null and ai.three != ''">
                        <if test="ai.two != null and ai.two != ''">
                            and
                        </if>
                        t1.three = #{ai.three}
                    </if>
                    <if test="ai.four != null and ai.four != ''">
                        <if test="(ai.two != null and ai.two != '') or (ai.three != null and ai.three != '')">
                            and
                        </if>
                        t1.four = #{ai.four}
                    </if>
                    <if test="ai.wgType != null">
                        <if test="(ai.two != null and ai.two != '') or (ai.three != null and ai.three != '') or (ai.four != null and ai.four != '')">
                            and
                        </if>
                        t1.type = #{ai.wgType}
                    </if>
                    )
                </foreach>
                )
            </if>
        </where>
        GROUP BY t1.id, t4.id
        <trim prefix="having" prefixOverrides="and">
            <if test="query.dayPriceMin != null">
                and dayPrice <![CDATA[ >= ]]> #{query.dayPriceMin}
            </if>
            <if test="query.dayPriceMax != null">
                and dayPrice <![CDATA[ <= ]]> #{query.dayPriceMax}
            </if>
        </trim>
    </select>
    <select id="selectList" resultType="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        SELECT
        t2.wg_name AS wgName4,
        t1.*,
        SUM(t5.year_sell) AS income,
        t4.nature,
        t4.industry_classification,
        ROUND(IFNULL(t1.unit_price,0) * 10000 / 365 / t1.house_area, 1) AS dayPrice,
        t4.name,
        t5.year
        FROM
        house_resource t1
        LEFT JOIN grid_region t2 ON t2.wg_code = t1.four
        LEFT JOIN house_resource_business_entity_info_mapping t3 ON t3.house_resource_id = t1.id
        LEFT JOIN business_entity_info t4 ON t3.business_entity_info_id = t4.id
        LEFT JOIN business_entity_sell t5 ON t5.business_entity_info_id = t4.id
        <where>
            <if test="query.wgType != null">
                and t1.type = #{query.wgType}
            </if>
            <if test="query.wgTypes != null and query.wgTypes.size() > 0">
                and t1.type in
                <foreach item="item" index="index" collection="query.wgTypes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.two != null and query.two != ''">
                and t1.two = #{query.two}
            </if>
            <if test="query.three != null and query.three != ''">
                and t1.three = #{query.three}
            </if>
            <if test="query.four != null and query.four != ''">
                and t1.four = #{query.four}
            </if>
            <if test="query.houseResourceAttribute != null">
                and t1.house_resource_attribute = #{query.houseResourceAttribute}
            </if>
            <if test="query.hireStatus != null">
                and t1.hire_status = #{query.hireStatus}
            </if>
            <if test="query.houseNumber != null and query.houseNumber != ''">
                and t1.house_number like concat('%', #{query.houseNumber}, '%')
            </if>
            <if test="query.houseAreaMin != null and query.houseAreaMin != ''">
                and t1.house_area <![CDATA[ >= ]]> #{query.houseAreaMin}
            </if>
            <if test="query.houseAreaMax != null and query.houseAreaMax != ''">
                and t1.house_area <![CDATA[ <= ]]> #{query.houseAreaMax}
            </if>
            <if test="query.houseResourceType != null">
                and t1.house_resource_type = #{query.houseResourceType}
            </if>
            <if test="query.name != null and query.name != ''">
                and t4.name like concat('%', #{query.name}, '%')
            </if>
            <if test="query.yearSellMin != null">
                and t5.year_sell <![CDATA[ >= ]]> #{query.yearSellMin}
            </if>
            <if test="query.yearSellMax != null">
                and t5.year_sell <![CDATA[ <= ]]> #{query.yearSellMax}
            </if>
            <if test="query.yearTaxMin != null">
                and t5.year_tax <![CDATA[ >= ]]> #{query.yearTaxMin}
            </if>
            <if test="query.yearTaxMax != null">
                and t5.year_tax <![CDATA[ <= ]]> #{query.yearTaxMax}
            </if>
            <if test="query.unitPriceMin != null">
                and t1.unit_price <![CDATA[ >= ]]> #{query.unitPriceMin}
            </if>
            <if test="query.unitPriceMax != null">
                and t1.unit_price <![CDATA[ <= ]]> #{query.unitPriceMax}
            </if>
            <if test="query.gsFlag != null">
                and t4.gs = ${query.gsFlag}
            </if>
            <if test="query.industryClassification != null and query.industryClassification != ''">
                and t4.industry_classification like concat('%', #{query.industryClassification}, '%')
            </if>
            <if test="query.year != null">
                and t5.`year` = #{query.year}
            </if>
            <if test="query.registrationPlaceFlag != null">
                and t4.registration_place_flag = #{query.registrationPlaceFlag}
            </if>
            <if test="query.address != null and query.address != ''">
                and t1.address like concat('%', #{query.address}, '%')
            </if>
            <if test="query.ownership != null and query.ownership != ''">
                and t1.ownership like concat('%', #{query.ownership}, '%')
            </if>
            <if test="query.houseResourceEquityTel != null and query.houseResourceEquityTel != ''">
                and t1.house_resource_equity_tel like concat('%', #{query.houseResourceEquityTel}, '%')
            </if>
            <if test="query.principal != null and query.principal != ''">
                and t4.principal like concat('%', #{query.principal}, '%')
            </if>
            <if test="query.principalTel != null and query.principalTel != ''">
                and t4.principal_tel like concat('%', #{query.principalTel}, '%')
            </if>
            <if test="query.business != null and query.business != ''">
                and t4.business like concat('%', #{query.business}, '%')
            </if>
            <if test="query.workerNumberMin != null">
                and t4.worker_number <![CDATA[ >= ]]> #{query.workerNumberMin}
            </if>
            <if test="query.workerNumberMax != null">
                and t4.worker_number <![CDATA[ <= ]]> #{query.workerNumberMax}
            </if>
            <if test="query.registeredAddress != null and query.registeredAddress != ''">
                and t4.registered_address like concat('%', #{query.registeredAddress}, '%')
            </if>
            <if test="query.nature != null and query.nature != ''">
                and t4.nature = #{query.nature}
            </if>
            <if test="query.fourOrHouseNumber != null and query.fourOrHouseNumber != ''">
                and t2.wg_name like concat('%', #{query.fourOrHouseNumber}, '%')
            </if>
            <if test="query.wgCodes != null and query.wgCodes.size() > 0">
                and t1.two in
                <foreach item="item" index="index" collection="query.wgCodes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.aiQueries != null and query.aiQueries.size() > 0">
                and (
                <foreach collection="query.aiQueries" item="ai" separator=" or ">
                    (
                    <if test="ai.two != null and ai.two != ''">
                        t1.two = #{ai.two}
                    </if>
                    <if test="ai.three != null and ai.three != ''">
                        <if test="ai.two != null and ai.two != ''">
                            and
                        </if>
                        t1.three = #{ai.three}
                    </if>
                    <if test="ai.four != null and ai.four != ''">
                        <if test="(ai.two != null and ai.two != '') or (ai.three != null and ai.three != '')">
                            and
                        </if>
                        t1.four = #{ai.four}
                    </if>
                    <if test="ai.wgType != null">
                        <if test="(ai.two != null and ai.two != '') or (ai.three != null and ai.three != '') or (ai.four != null and ai.four != '')">
                            and
                        </if>
                        t1.type = #{ai.wgType}
                    </if>
                    )
                </foreach>
                )
            </if>
        </where>
        GROUP BY t1.id, t4.id
        <trim prefix="having" prefixOverrides="and">
            <if test="query.dayPriceMin != null">
                and dayPrice <![CDATA[ >= ]]> #{query.dayPriceMin}
            </if>
            <if test="query.dayPriceMax != null">
                and dayPrice <![CDATA[ <= ]]> #{query.dayPriceMax}
            </if>
        </trim>
    </select>
    <select id="exportList" resultType="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        SELECT
        t1.id,
        t4.id AS businessEntityInfoId,
        t1.two,
        t1.three,
        t1.four,
        t2.wg_name AS wgName4,
        t1.type,
        t1.house_number,
        t1.address,
        t1.house_area,
        t1.house_resource_attribute,
        t1.house_resource_type,
        t1.ownership,
        t1.house_resource_equity_tel,
        t1.unit_price,
        t4.nature,
        t4.NAME,
        t4.principal_tel,
        t4.business,
        t4.worker_number,
        t4.registration_place_flag,
        t4.registered_address,
        t4.industry_classification,
        t4.gs,
        t4.back_pay_flag,
        t4.stability_related,
        t4.investigation_date,
        t4.note,
        t1.remark,
        t4.name AS businessEntityInfoName,
        t4.principal,
        ROUND(IFNULL(t1.unit_price,0) * 10000 / 365 / t1.house_area, 1) AS dayPrice,
        t1.house_resource_url,
        t1.business_license_url
        FROM
        house_resource t1
        LEFT JOIN grid_region t2 ON t2.wg_code = t1.four
        LEFT JOIN house_resource_business_entity_info_mapping t3 ON t3.house_resource_id = t1.id
        LEFT JOIN business_entity_info t4 ON t3.business_entity_info_id = t4.id
        LEFT JOIN business_entity_sell t5 ON t5.business_entity_info_id = t4.id
        <where>
            <if test="query.wgType != null">
                and t1.type = #{query.wgType}
            </if>
            <if test="query.wgTypes != null and query.wgTypes.size() > 0">
                and t1.type in
                <foreach item="item" index="index" collection="query.wgTypes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.two != null and query.two != ''">
                and t1.two = #{query.two}
            </if>
            <if test="query.three != null and query.three != ''">
                and t1.three = #{query.three}
            </if>
            <if test="query.four != null and query.four != ''">
                and t1.four = #{query.four}
            </if>
            <if test="query.houseResourceAttribute != null">
                and t1.house_resource_attribute = #{query.houseResourceAttribute}
            </if>
            <if test="query.hireStatus != null">
                and t1.hire_status = #{query.hireStatus}
            </if>
            <if test="query.houseNumber != null and query.houseNumber != ''">
                and t1.house_number like concat('%', #{query.houseNumber}, '%')
            </if>
            <if test="query.houseAreaMin != null and query.houseAreaMin != ''">
                and t1.house_area <![CDATA[ >= ]]> #{query.houseAreaMin}
            </if>
            <if test="query.houseAreaMax != null and query.houseAreaMax != ''">
                and t1.house_area <![CDATA[ <= ]]> #{query.houseAreaMax}
            </if>
            <if test="query.houseResourceType != null">
                and t1.house_resource_type = #{query.houseResourceType}
            </if>
            <if test="query.name != null and query.name != ''">
                and t4.name like concat('%', #{query.name}, '%')
            </if>
            <if test="query.yearSellMin != null">
                and t5.year_sell <![CDATA[ >= ]]> #{query.yearSellMin}
            </if>
            <if test="query.yearSellMax != null">
                and t5.year_sell <![CDATA[ <= ]]> #{query.yearSellMax}
            </if>
            <if test="query.yearTaxMin != null">
                and t5.year_tax <![CDATA[ >= ]]> #{query.yearTaxMin}
            </if>
            <if test="query.yearTaxMax != null">
                and t5.year_tax <![CDATA[ <= ]]> #{query.yearTaxMax}
            </if>
            <if test="query.unitPriceMin != null">
                and t1.unit_price <![CDATA[ >= ]]> #{query.unitPriceMin}
            </if>
            <if test="query.unitPriceMax != null">
                and t1.unit_price <![CDATA[ <= ]]> #{query.unitPriceMax}
            </if>
            <if test="query.gsFlag != null">
                and t4.gs = ${query.gsFlag}
            </if>
            <if test="query.industryClassification != null and query.industryClassification != ''">
                and t4.industry_classification like concat('%', #{query.industryClassification}, '%')
            </if>
            <if test="query.year != null">
                and t5.`year` = #{query.year}
            </if>
            <if test="query.registrationPlaceFlag != null">
                and t4.registration_place_flag = #{query.registrationPlaceFlag}
            </if>
            <if test="query.address != null and query.address != ''">
                and t1.address like concat('%', #{query.address}, '%')
            </if>
            <if test="query.ownership != null and query.ownership != ''">
                and t1.ownership like concat('%', #{query.ownership}, '%')
            </if>
            <if test="query.houseResourceEquityTel != null and query.houseResourceEquityTel != ''">
                and t1.house_resource_equity_tel like concat('%', #{query.houseResourceEquityTel}, '%')
            </if>
            <if test="query.principal != null and query.principal != ''">
                and t4.principal like concat('%', #{query.principal}, '%')
            </if>
            <if test="query.principalTel != null and query.principalTel != ''">
                and t4.principal_tel like concat('%', #{query.principalTel}, '%')
            </if>
            <if test="query.business != null and query.business != ''">
                and t4.business like concat('%', #{query.business}, '%')
            </if>
            <if test="query.workerNumberMin != null">
                and t4.worker_number <![CDATA[ >= ]]> #{query.workerNumberMin}
            </if>
            <if test="query.workerNumberMax != null">
                and t4.worker_number <![CDATA[ <= ]]> #{query.workerNumberMax}
            </if>
            <if test="query.registeredAddress != null and query.registeredAddress != ''">
                and t4.registered_address like concat('%', #{query.registeredAddress}, '%')
            </if>
            <if test="query.nature != null and query.nature != ''">
                and t4.nature = #{query.nature}
            </if>
            <if test="query.wgCodes != null and query.wgCodes.size() > 0">
                and t1.two in
                <foreach item="item" index="index" collection="query.wgCodes" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY t1.id, t4.id
        <trim prefix="having" prefixOverrides="and">
            <if test="query.dayPriceMin != null">
                and dayPrice <![CDATA[ >= ]]> #{query.dayPriceMin}
            </if>
            <if test="query.dayPriceMax != null">
                and dayPrice <![CDATA[ <= ]]> #{query.dayPriceMax}
            </if>
        </trim>
        ORDER BY t1.four, t1.three, t1.two
    </select>
    <select id="selectAll" resultType="com.ruoyi.system.domain.house.HouseResource">
        SELECT * FROM house_resource
    </select>
    <select id="selectPageBusinessEntityStatistics" resultType="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        SELECT
            t1.*,
            SUM(t5.year_sell) AS income,
            SUM(t5.year_tax) AS taxCount
        FROM
            house_resource t1
                LEFT JOIN grid_region t2 ON t2.wg_code = t1.four
                LEFT JOIN house_resource_business_entity_info_mapping t3 ON t3.house_resource_id = t1.id
                LEFT JOIN business_entity_info t4 ON t3.business_entity_info_id = t4.id
                LEFT JOIN business_entity_sell t5 ON t5.business_entity_info_id = t4.id
        GROUP BY t1.id, t4.id
    </select>
    <select id="selectForContrast" resultType="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        SELECT
            t1.id,
            t1.two,
            t1.three,
            t2.wg_name AS wgName4,
            t1.type,
            t1.house_number,
            t1.address,
            t1.house_area,
            t1.house_resource_attribute,
            t1.house_resource_type,
            t1.ownership,
            t1.house_resource_equity_tel,
            t1.unit_price,
            t4.nature,
            t4.NAME,
            t4.principal_tel,
            t4.business,
            t4.worker_number,
            t4.registration_place_flag,
            t4.back_pay_flag,
            t4.investigation_date,
            t4.stability_related,
            t4.note,
            t4.registered_address,
            t4.industry_classification,
            t4.gs,
            t1.remark,
            t4.name AS businessEntityInfoName,
            t4.principal
        FROM
            house_resource t1
                LEFT JOIN grid_region t2 ON t2.wg_code = t1.four
                LEFT JOIN house_resource_business_entity_info_mapping t3 ON t3.house_resource_id = t1.id
                LEFT JOIN business_entity_info t4 ON t3.business_entity_info_id = t4.id
                LEFT JOIN business_entity_sell t5 ON t5.business_entity_info_id = t4.id
        WHERE t1.two = #{two} AND t4.NAME = #{name}
    </select>
    <select id="selectByIdList" resultType="com.ruoyi.system.domain.house.vo.HouseResourcePage">
        SELECT * FROM house_resource WHERE id IN
        <foreach item="item" index="index" collection="idList" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <select id="selectHouseResourceByCreateTime"
            resultType="com.ruoyi.system.domain.house.vo.HouseResourceDataCollectionSimpleObject">
        SELECT
        type,
        COUNT(*) AS count
        FROM
        house_resource
        <where>
            <if test="query.two != null and query.two != ''">
                AND two = #{query.two}
            </if>
            <if test="query.three != null and query.three != ''">
                AND three = #{query.three}
            </if>
            <if test="query.four != null and query.four != ''">
                AND four = #{query.four}
            </if>
            <if test="query.startDate != null">
                AND create_time <![CDATA[ >= ]]> #{query.startDate}
            </if>
            <if test="query.endDate != null">
                AND create_time <![CDATA[ <= ]]> #{query.endDate}
            </if>
        </where>
        GROUP BY
        type
    </select>
    <select id="selectHouseResourceByUpdateTime"
            resultType="com.ruoyi.system.domain.house.vo.HouseResourceDataCollectionSimpleObject">
        SELECT
        type,
        COUNT(*) AS count
        FROM
        house_resource
        <where>
            <if test="query.two != null and query.two != ''">
                AND two = #{query.two}
            </if>
            <if test="query.three != null and query.three != ''">
                AND three = #{query.three}
            </if>
            <if test="query.four != null and query.four != ''">
                AND four = #{query.four}
            </if>
            <if test="startDate != null">
                AND update_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                AND update_time <![CDATA[ <= ]]> #{endDate}
            </if>
        </where>
        GROUP BY
        type
    </select>
</mapper>
