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

    <select id="channelCode" resultType="int">
        drop FUNCTION if exists roundNum;
        create function roundNum() returns int
        begin
        declare x int default 1;
        declare y int default 0;
        declare a int default 20;
        declare b int default 1;
        while (b <![CDATA[ < a ]]> ) do
        set x = (select (select FLOOR(100000 + RAND()*900000))  as num);
        set y = (select count(1) from ta_channel where channel_code = x);
        if y = 0
        then return x;
        END IF;
        set b = b + 1;
        end while;
        return 0;
        end;
        select roundNum();
    </select>
    
    <select id="selectChannelList" resultType="com.huiju.estateagents.entity.TaChannel">
        select * from ta_channel
        where org_id = #{orgId}
    </select>

    <select id="pageTaChannel" resultType="com.huiju.estateagents.entity.TaChannel">
        SELECT
        c.*,
        count(tp.person_id) AS brokerCount,
        (SELECT COUNT(p.recommend_agent ) from ta_person p WHERE cp.person_id = p.recommend_agent) AS inviteCount,
        IFNULL(ts.customerNum,0) as customerNum,
        cp.person_id
        FROM
        ta_channel c
        LEFT JOIN ta_channel_person cp ON c.channel_id = cp.channel_id
        LEFT JOIN ta_person tp ON cp.person_id = tp.person_id 	AND tp.person_type = 'estate agent' 	AND cp.STATUS = 1
        LEFT JOIN (
        SELECT sum(new_customers) as customerNum,qr_code_id,channel_id,org_id from ts_channel_daily where channel_id is not null and org_id = #{orgId} GROUP BY channel_id) ts on c.channel_id = ts.channel_id
        <where>
            <if test="channelId != null and channelId != ''">
                c.channel_id = #{channelId}
            </if>
            and c.org_id = #{orgId}
        </where>
        GROUP BY c.channel_id
    </select>

    <select id="recommendCount" resultType="com.huiju.estateagents.entity.TaChannel">
        SELECT
            c.channel_id,
            COUNT( rc.recommend_person ) AS recommendCount
        FROM
        ta_channel c
        LEFT JOIN ta_channel_person cp ON c.channel_id = cp.channel_id and cp.`status` = 1
        LEFT JOIN ta_person t on cp.person_id = t.person_id and t.person_type = 'estate agent'
        LEFT JOIN ta_recommend_customer rc ON t.person_id = rc.recommend_person AND rc.verify_status = 1
        <where>
            <if test="channelId != null and channelId != ''">
                c.channel_id = #{channelId}
            </if>
            and c.org_id = #{orgId}
        </where>
        GROUP BY c.channel_id
    </select>

    <select id="getChannelIntroductionList" resultType="com.huiju.estateagents.entity.TsActivityDaily">
        SELECT
            serial_no,
            statis_date,
            org_id,
            building_id,
            building_name,
            target_id,
            target_type,
            target_name,
            sum( share_num ) AS share_num,
            sum( share_persons ) AS share_persons,
            sum( visit_num ) AS visit_num,
            sum( visit_persons ) AS visit_persons,
            sum( new_persons ) AS new_persons,
            sum( new_customers ) AS new_customers,
            qr_code_id,
            channel_id
        FROM
            ts_channel_daily
        WHERE
            channel_id = #{channelId}
        AND org_id = #{orgId}
        <if test="startDate != null and endDate != null">
            AND statis_date BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="channelType != null and channelType != ''">
            and target_type = #{channelType}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and building_id = #{buildingId}
        </if>
        <if test="taPersonBuildings != null and taPersonBuildings.size > 0">
            and building_id in
            <foreach collection="taPersonBuildings" item="taPersonBuilding" open="(" close=")" separator=",">
                #{taPersonBuilding.buildingId}
            </foreach>
        </if>
        GROUP BY   target_id, target_type
        <if test="sortField != null and sortField != ''">
            ORDER BY ${sortField} ${orderType}
        </if>
    </select>
    <select id="getChannelNewuserList"
            resultType="com.huiju.estateagents.excel.ActivityStatistics.ChannelAddRegistNum">
        SELECT
        a.nickname, a.phone,
        if(a.gender = '1', '男', if(a.gender = '2', '女','未知') ) as gender,
        a.province,
        t.create_date
        FROM ta_customer_from t
        left join ta_person a on t.person_id = a.person_id
<!--        left join ta_person_from_record b on a.person_id = b.person_id and b.org_id = #{orgId} and b.is_first_time =1-->
<!--        left join td_wx_dict b1 on b.scene_id = b1.scene_id-->
<!--        left join ta_recommend_customer c on c.person_id = t.person_id and c.realty_consultant is not null-->
<!--        LEFT JOIN ta_user d ON c.realty_consultant = d.user_id-->
<!--        LEFT JOIN ta_person e ON t.share_person_id = e.person_id-->
        WHERE t.org_id = #{orgId}
        and t.is_org_first = 1
        and DATE_FORMAT(t.create_date, '%Y-%m-%d' )  >= DATE_FORMAT(#{startDate}, '%Y-%m-%d' ) and DATE_FORMAT(t.create_date, '%Y-%m-%d' ) &lt;= DATE_FORMAT(#{endDate},
        '%Y-%m-%d' )
        <if test="activityId != null and activityId != ''">
            and t.target_id = #{activityId}
        </if>
        <if test="activityType != null and activityType != ''">
            and t.target_type = #{activityType}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and t.building_id = #{buildingId}
        </if>
        <if test="channelId != null and channelId != ''">
            and t.channel_id = #{channelId}
        </if>
        group by t.person_id order by t.create_date desc
    </select>
    <select id="getChannelVisitNumList" resultType="com.huiju.estateagents.excel.ActivityStatistics.ChannelVisitNum">
        select
        a.nickname, a.phone,
        if(a.gender = '1', '男', if(a.gender = '2', '女','未知') ) as gender,
        a.province,
        b1.scene_alias as personFrom,
        GROUP_CONCAT(d.user_name) as realtyConsultant,
        GROUP_CONCAT(d.phone) as realtyConsultantPhone,
        e.nickname as sharePersonName,
        e.phone as sharePersonPhone,
        t.visit_time as visitDate
        From (
        SELECT
        t.person_id, t.visit_time, t.share_person_id
        FROM
        ta_person_visit_record t
        WHERE
        t.`event` in ( 'detail', 'house_list' , 'poster')
        AND t.event_type IN (
        'activity',
        'help',
        'group',
        'h5',
        'news',
        'building',
        'live',
        'house'
        )
        <if test="targetId != null and targetId != ''">
            and t.target_id = #{targetId}
        </if>
        <if test="targetType != null and targetType != ''">
            and (case when #{targetType} = 'activity' then t.target_type = 'dynamic'
                        when #{targetType} = 'h5' then t.event_type = 'h5'
                        when #{targetType} = 'news' then t.event_type = 'news'
                        when #{targetType} = 'building' then t.event_type = 'building'
                        when #{targetType} = 'house' then t.event_type = 'house'
            else t.target_type = #{targetType}
            end)
        </if>
        <if test="channelId != null and channelId != ''">
            and t.channel_id = #{channelId}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and t.building_id = #{buildingId}
        </if>
        AND t.org_id = #{orgId}
        AND DATE_FORMAT(t.visit_time, '%Y-%m-%d' )  >= DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        AND DATE_FORMAT(t.visit_time, '%Y-%m-%d' )  &lt;= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        ) t
        left join ta_person a on t.person_id = a.person_id
        left join ta_person_from_record b on a.person_id = b.person_id and b.org_id = #{orgId}
        left join td_wx_dict b1 on b.scene_id = b1.scene_id
        LEFT JOIN ta_recommend_customer c on c.person_id = t.person_id
        left join ta_user d on c.realty_consultant = d.user_id
        left join ta_person e on t.share_person_id = e.person_id
        group by t.visit_time
        order by t.visit_time desc
    </select>
    <select id="getChannelPersonNumList"
            resultType="com.huiju.estateagents.excel.ActivityStatistics.ChannelVisitPersonNum">
        select
        a.nickname, a.phone,
        if(a.gender = '1', '男', if(a.gender = '2', '女','未知') ) as gender,
        a.province,
        b1.scene_alias as personFrom,
        GROUP_CONCAT(d.user_name) as realtyConsultant,
        GROUP_CONCAT(d.phone) as realtyConsultantPhone,
        t.create_date as visitDate,
        e.nickName as sharePersonName,
        t.visitNum
        from (
        SELECT
        t.visit_time AS create_date,
        t.person_id,
        count(*) as visitNum
        FROM
        ta_person_visit_record t
        WHERE
        t.`event` in ( 'detail', 'house_list' , 'poster')
        AND t.event_type IN (
        'activity',
        'help',
        'group',
        'h5',
        'news',
        'building',
        'live',
        'house'
        )
        AND t.org_id = #{orgId}
        AND DATE_FORMAT(t.visit_time, '%Y-%m-%d' ) >= DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        AND DATE_FORMAT(t.visit_time, '%Y-%m-%d' ) &lt;= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        <if test="targetId != null and targetId != ''">
            and t.target_id = #{targetId}
        </if>
        <if test="targetType != null and targetType != ''">
            and (case when #{targetType} = 'activity' then t.target_type = 'dynamic'
            when #{targetType} = 'h5' then t.event_type = 'h5'
            when #{targetType} = 'news' then t.event_type = 'news'
            when #{targetType} = 'building' then t.event_type = 'building'
            when #{targetType} = 'house' then t.event_type = 'house'
            else t.target_type = #{targetType}
            end)
        </if>
        <if test="buildingId != null and buildingId != ''">
            and t.building_id = #{buildingId}
        </if>
        <if test="channelId != null and channelId != ''">
            and t.channel_id = #{channelId}
        </if>
        GROUP BY
        t.person_id ) as t
        left join ta_person a on t.person_id = a.person_id
        left join ta_person_from_record b on a.person_id = b.person_id and b.org_id = #{orgId} and b.is_first_time = 1
        left join td_wx_dict b1 on b.scene_id = b1.scene_id
        LEFT JOIN ta_recommend_customer c on c.person_id = t.person_id
        left join ta_user d on c.realty_consultant = d.user_id
        LEFT JOIN ta_person e ON e.person_id = b.share_person_id
        group by t.person_id
        order by t.create_date desc
    </select>
    <select id="getChannelIntroductionListExport"
            resultType="com.huiju.estateagents.excel.ActivityStatistics.TsChannelDaily">
        SELECT
        serial_no,
        statis_date,
        org_id,
        building_id,
        building_name,
        target_id,
        target_type,
        target_name,
        sum( share_num ) AS share_num,
        sum( share_persons ) AS share_persons,
        sum( visit_num ) AS visit_num,
        sum( visit_persons ) AS visit_persons,
        sum( new_persons ) AS new_persons,
        sum( new_customers ) AS new_customers,
        qr_code_id,
        channel_id
        FROM
        ts_channel_daily
        WHERE
        channel_id = #{channelId}
        AND org_id = #{orgId}
        <if test="startDate != null and endDate != null">
            AND statis_date BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="channelType != null and channelType != ''">
            and target_type = #{channelType}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and building_id = #{buildingId}
        </if>
        <if test="taPersonBuildings != null and taPersonBuildings.size > 0">
            and building_id in
            <foreach collection="taPersonBuildings" item="taPersonBuilding" open="(" close=")" separator=",">
                #{taPersonBuilding.buildingId}
            </foreach>
        </if>
        GROUP BY   target_id, target_type
    </select>
    <select id="getChannelNewuserListExport"
            resultType="com.huiju.estateagents.excel.ActivityStatistics.ChannelAddRegistNum">
        SELECT
        a.nickname, a.phone,
        if(a.gender = '1', '男', if(a.gender = '2', '女','未知') ) as gender,
        a.province,
        t.create_date
        FROM ta_customer_from t
        left join ta_person a on t.person_id = a.person_id
        <!--        left join ta_person_from_record b on a.person_id = b.person_id and b.org_id = #{orgId} and b.is_first_time =1-->
        <!--        left join td_wx_dict b1 on b.scene_id = b1.scene_id-->
        <!--        left join ta_recommend_customer c on c.person_id = t.person_id and c.realty_consultant is not null-->
        <!--        LEFT JOIN ta_user d ON c.realty_consultant = d.user_id-->
        <!--        LEFT JOIN ta_person e ON t.share_person_id = e.person_id-->
        WHERE t.org_id = #{orgId}
        and t.is_org_first = 1
                and DATE_FORMAT(t.create_date, '%Y-%m-%d' ) >= DATE_FORMAT(#{startDate}, '%Y-%m-%d' ) and DATE_FORMAT(t.create_date, '%Y-%m-%d' ) &lt;= DATE_FORMAT(#{endDate},
                '%Y-%m-%d' )
        <if test="activityId != null and activityId != ''">
            and t.target_id = #{activityId}
        </if>
        <if test="activityType != null and activityType != ''">
            and t.target_type = #{activityType}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and t.building_id = #{buildingId}
        </if>
        <if test="channelId != null and channelId != ''">
            and t.channel_id = #{channelId}
        </if>
        group by t.person_id order by t.create_date desc
    </select>
    <select id="getChannelVisitNumListExport"
            resultType="com.huiju.estateagents.excel.ActivityStatistics.ChannelVisitNum">
        select
        a.nickname, a.phone,
        if(a.gender = '1', '男', if(a.gender = '2', '女','未知') ) as gender,
        a.province,
        b1.scene_alias as personFrom,
        GROUP_CONCAT(d.user_name) as realtyConsultant,
        GROUP_CONCAT(d.phone) as realtyConsultantPhone,
        e.nickname as sharePersonName,
        e.phone as sharePersonPhone,
        t.visit_time as visitDate
        From (
        SELECT
        t.person_id, t.visit_time, t.share_person_id
        FROM
        ta_person_visit_record t
        WHERE
        t.`event` in ( 'detail', 'house_list', 'poster' )
        AND t.event_type IN (
        'activity',
        'help',
        'group',
        'h5',
        'news',
        'building',
        'live',
        'house'
        )
        <if test="targetId != null and targetId != ''">
            and t.target_id = #{targetId}
        </if>
        <if test="targetType != null and targetType != ''">
            and (case when #{targetType} = 'activity' then t.target_type = 'dynamic'
            when #{targetType} = 'h5' then t.event_type = 'h5'
            when #{targetType} = 'news' then t.event_type = 'news'
            when #{targetType} = 'building' then t.event_type = 'building'
            when #{targetType} = 'house' then t.event_type = 'house'
            else t.target_type = #{targetType}
            end)
        </if>
        <if test="channelId != null and channelId != ''">
            and t.channel_id = #{channelId}
        </if>
        <if test="buildingId != null and buildingId != ''">
            and t.building_id = #{buildingId}
        </if>
        AND t.org_id = #{orgId}
        AND DATE_FORMAT( t.visit_time, '%Y-%m-%d' ) >= DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        AND DATE_FORMAT( t.visit_time, '%Y-%m-%d' ) &lt;= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        ) t
        left join ta_person a on t.person_id = a.person_id
        left join ta_person_from_record b on a.person_id = b.person_id and b.org_id = #{orgId}
        left join td_wx_dict b1 on b.scene_id = b1.scene_id
        LEFT JOIN ta_recommend_customer c on c.person_id = t.person_id
        left join ta_user d on c.realty_consultant = d.user_id
        left join ta_person e on t.share_person_id = e.person_id
        group by t.visit_time
        order by t.visit_time desc
    </select>
    <select id="getChannelPersonNumListExport"
            resultType="com.huiju.estateagents.excel.ActivityStatistics.ChannelVisitPersonNum">
        select
        a.nickname, a.phone,
        if(a.gender = '1', '男', if(a.gender = '2', '女','未知') ) as gender,
        a.province,
        b1.scene_alias as personFrom,
        GROUP_CONCAT(d.user_name) as realtyConsultant,
        GROUP_CONCAT(d.phone) as realtyConsultantPhone,
        t.create_date as visitDate,
        e.nickName as sharePersonName,
        t.visitNum
        from (
        SELECT
        t.visit_time AS create_date,
        t.person_id,
        count(*) as visitNum
        FROM
        ta_person_visit_record t
        WHERE
        t.`event` in ( 'detail', 'house_list', 'poster' )
        AND t.event_type IN (
        'activity',
        'help',
        'group',
        'h5',
        'news',
        'building',
        'live',
        'house'
        )
        AND t.org_id = #{orgId}
        AND DATE_FORMAT( t.visit_time, '%Y-%m-%d' ) >= DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        AND DATE_FORMAT( t.visit_time, '%Y-%m-%d' ) &lt;= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        <if test="targetId != null and targetId != ''">
            and t.target_id = #{targetId}
        </if>
        <if test="targetType != null and targetType != ''">
            and (case when #{targetType} = 'activity' then t.target_type = 'dynamic'
            when #{targetType} = 'h5' then t.event_type = 'h5'
            when #{targetType} = 'news' then t.event_type = 'news'
            when #{targetType} = 'building' then t.event_type = 'building'
            when #{targetType} = 'house' then t.event_type = 'house'
            else t.target_type = #{targetType}
            end)
        </if>
        <if test="buildingId != null and buildingId != ''">
            and t.building_id = #{buildingId}
        </if>
        <if test="channelId != null and channelId != ''">
            and t.channel_id = #{channelId}
        </if>
        GROUP BY
        t.person_id ) as t
        left join ta_person a on t.person_id = a.person_id
        left join ta_person_from_record b on a.person_id = b.person_id and b.org_id = #{orgId} and b.is_first_time = 1
        left join td_wx_dict b1 on b.scene_id = b1.scene_id
        LEFT JOIN ta_recommend_customer c on c.person_id = t.person_id
        left join ta_user d on c.realty_consultant = d.user_id
        LEFT JOIN ta_person e ON e.person_id = b.share_person_id
        group by t.person_id
        order by t.create_date desc
    </select>

</mapper>
