<?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(cp.person_id) AS brokerCount,
        COUNT(rc.person_id) AS recommendCount,
        (SELECT COUNT(p.recommend_agent ) from ta_person p WHERE cp.person_id = p.recommend_agent) AS inviteCount,
        cp.person_id
        FROM
        ta_channel c
        LEFT JOIN ta_channel_person cp ON c.channel_id = cp.channel_id
        LEFT JOIN ta_recommend_customer rc ON cp.person_id = rc.person_id AND rc.`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>
</mapper>
