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

    <select id="selectAll" resultType="com.huiju.estateagents.entity.TaPersonIntentionRecord">
        SELECT
            *,
            tp.phone as phone,
            SUM(tpir.intention) as intentionCount
        FROM
            ta_person_intention_record tpir
            LEFT JOIN ta_person tp ON tpir.person_id = tp.person_id
            GROUP BY tpir.person_id
    </select>


    <select id="selectIntentionUser" resultType="map">
        SELECT
            tp.nickname as  personName,
            tp.phone as phone,
            tpir.building_name as buildingName,
            SUM(tpir.intention) as intention
        FROM
            ta_person_intention_record tpir
            LEFT JOIN ta_person tp on tpir.person_id = tp.person_id
            <trim prefix="where" prefixOverrides="and | or">
                <if test="buildingId != null and buildingId != ''">
                    tpir.building_id = #{buildingId}
                </if>
                <if test="orgId != null">
                    AND tpir.org_id = #{orgId}
                    AND tp.org_id = #{orgId}
                </if>
                <if test="personBuildingList != null and personBuildingList.size > 0">
                    AND tpir.building_id in
                    <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                        #{personBuilding.buildingId}
                    </foreach>
                </if>
            </trim>
        GROUP BY
            tpir.person_id, tpir.building_id
        ORDER BY SUM(tpir.intention) DESC
    </select>
    <select id="selectExportIntentionUser" resultType="com.huiju.estateagents.excel.IntentionUser">
        select t.*, b1.scene_alias as personFrom, e.nickname as recommendPerson
        From (

        SELECT
        tp.nickname as person_name,
        if(tp.gender = '1' , '男', if(tp.gender = '2', '女', '未知')) as gender,
        tp.phone as phone,
        tp.province,
        tpir.person_id,
        tpir.building_name,
        tpir.building_id,
        d.user_name as realtyConsultant,
        d.phone as realtyConsultantPhone,
        SUM(tpir.intention) as intention

        FROM ta_person_intention_record tpir
        LEFT JOIN ta_person tp ON tpir.person_id = tp.person_id
        left join ta_recommend_customer c on c.person_id = tpir.person_id and c.building_id = tpir.building_id
        left join ta_user d on c.realty_consultant = d.user_id
        <trim prefix="where" prefixOverrides="and | or">
            <if test="orgId != null">
                AND tpir.org_id = #{orgId}
                AND tp.org_id = #{orgId}
            </if>
        </trim>
        GROUP BY tpir.person_id, tpir.building_id

        ) as t
        left join ta_share_person_from a on t.person_id = a.person_id
        left join ta_person_from_record d on t.person_id = d.person_id and d.is_first_time = 1 and d.org_id = #{orgId}
        left join td_wx_dict b1 on d.scene_id = b1.scene_id
        left join ta_person e on d.share_person_id = e.person_id

        <trim prefix="where" prefixOverrides="and | or">
            <if test="buildingId != null and buildingId != ''">
                t.building_id = #{buildingId}
            </if>
            <if test="personBuildingList != null and personBuildingList.size > 0">
                AND t.building_id in
                <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">
                    #{personBuilding.buildingId}
                </foreach>
            </if>
        </trim>

        group by t.person_id, t.building_id
        order by t.intention desc



<!--        SELECT-->
<!--        tpir.person_name as  person_name,-->
<!--        tp.phone as phone,-->
<!--        tpir.building_name as building_name,-->
<!--        SUM(tpir.intention) as intention-->
<!--        FROM-->
<!--        ta_person_intention_record tpir-->
<!--        LEFT JOIN ta_person tp on tpir.person_id = tp.person_id-->
<!--        <trim prefix="where" prefixOverrides="and | or">-->
<!--            <if test="buildingId != null and buildingId != ''">-->
<!--                tpir.building_id = #{buildingId}-->
<!--            </if>-->
<!--            <if test="orgId != null">-->
<!--                AND tpir.org_id = #{orgId}-->
<!--                AND tp.org_id = #{orgId}-->
<!--            </if>-->
<!--            <if test="personBuildingList != null and personBuildingList.size > 0">-->
<!--                AND tpir.building_id in-->
<!--                <foreach collection="personBuildingList" item="personBuilding" open="(" close=")" separator=",">-->
<!--                    #{personBuilding.buildingId}-->
<!--                </foreach>-->
<!--            </if>-->
<!--        </trim>-->
<!--        GROUP BY-->
<!--        tpir.person_id, tpir.building_id-->
<!--        ORDER BY SUM(tpir.intention) DESC-->
    </select>
</mapper>
