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

    <select id="selectListByParams" resultType="com.huiju.estateagents.entity.TaPersonBuilding">
        select  a.building_id, t.user_name as consultantName, d.building_name, b.person_id, t.user_id From ta_user t
        left join ta_person_building a on t.user_id = a.user_id
        left join ta_person b on t.user_id = b.user_id
        left join ta_share_count c on b.person_id = c.person_id
        LEFT JOIN ta_building d on a.building_id = d.building_id
        where t.org_id = #{orgId} and t.is_consultant = 1
            <if test="buildingId != null and buildingId != ''">
              and a.building_id = #{buildingId}
            </if>
            <if test="personBuildingList != null and personBuildingList.size > 0">
                AND a.building_id in
                <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                    #{personBuilding.buildingId}
                </foreach>
            </if>
            <if test="startCreateDate != null and startCreateDate != ''">
                and  date_format(c.create_date,'%Y-%m-%d') >= date_format(#{startCreateDate},'%Y-%m-%d')
            </if>
            <if test="endCreateDate != null and endCreateDate != ''">
                and  date_format(c.create_date,'%Y-%m-%d') &lt;= date_format(#{endCreateDate},'%Y-%m-%d')
            </if>
        group by t.user_id
    </select>

    <select id="selectSalesList" resultType="com.huiju.estateagents.excel.SalesExport">
        SELECT
            a.building_id,
            t.user_name AS consultant_name,
            d.building_name,
            b.person_id,
            t.user_id ,
            count(c.person_id) as share_count
        FROM
            ta_user t
        LEFT JOIN ta_person_building a ON t.user_id = a.user_id
        LEFT JOIN ta_person b ON t.user_id = b.user_id
        LEFT JOIN ta_share_count c ON b.person_id = c.person_id
        LEFT JOIN ta_building d ON a.building_id = d.building_id
        WHERE
        t.org_id =  #{orgId}
        AND t.is_consultant = 1
        <if test="buildingId != null and buildingId != ''">
            and a.building_id = #{buildingId}
        </if>
        <if test="personBuildingList != null and personBuildingList.size > 0">
            AND a.building_id in
            <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                #{personBuilding.buildingId}
            </foreach>
        </if>
        <if test="startCreateDate != null and startCreateDate != ''">
            and  date_format(c.create_date,'%Y-%m-%d') >= date_format(#{startCreateDate},'%Y-%m-%d')
        </if>
        <if test="endCreateDate != null and endCreateDate != ''">
            and  date_format(c.create_date,'%Y-%m-%d') &lt;= date_format(#{endCreateDate},'%Y-%m-%d')
        </if>
        GROUP BY
        t.user_id
    </select>

    <select id="getBuildingsOf" resultType="com.huiju.estateagents.entity.TaBuilding">
        SELECT
            t.*
        FROM
        ta_person_building t
        JOIN ta_building s ON s.building_id = t.building_id
        WHERE
            t.person_id = #{personOrUser}
            OR t.user_id = #{personOrUser}
    </select>

</mapper>
