<?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.crm.manager.dao.mapper.UserReferralMapper">

    <select id="countList" resultType="integer" parameterType="com.crm.rely.backend.core.entity.user.info.UserReferralEntity">
        select count(1) from user_referral a left join user_info b on a.referral_user_id = b.id
        <where>
            <if test="referralName!=null and referralName!=''">
                and b.`name` like concat(#{referralName},"%")
            </if>
            <if test="email!=null and email!=''">
                and b.email like concat('%',#{email},'%')
            </if>
            <if test="ibNo!=null and ibNo!=''">
                and b.ib_no like concat('%',#{ibNo},'%')
            </if>
        </where>
    </select>

    <select id="getReferralList" resultType="com.crm.rely.backend.core.dto.user.info.UserReferralDto"
            parameterType="com.crm.rely.backend.core.entity.user.info.UserReferralEntity">
        select a.id,a.referral_user_id, a.referral_code,a.referral_name,a.email,a.calling_code,a.phone_number,
            a.referral_num,a.referral_success_num,b.ib_no ,b.username, b.ib_no_temp,b.valid
        from user_referral a left join user_info b on a.referral_user_id = b.id
        <where>
            <if test="referralName!=null and referralName!=''">
                and a.`referral_name` like concat(#{referralName},"%")
            </if>
            <if test="email!=null and email!=''">
                and b.email like concat('%',#{email},'%')
            </if>
            <if test="ibNo!=null and ibNo!=''">
                and b.ib_no like concat('%',#{ibNo},'%')
            </if>
        </where>
        order by a.id  desc
        <if test="type!=null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="getSuccessRefeeralCount" resultType="integer" parameterType="com.crm.rely.backend.core.entity.user.info.UserReferralEntity">
        select count(1) from (
            select a.id,a.email,'' english_name,a.add_time,b.manage_status from user_hire a
            left join user_entry b on a.id = b.hare_id where b.hare_id is null and a.referral_code = #{referralCode}
        union all
            select id,email,english_name,add_time,manage_status from user_entry where referral_code = #{referralCode}
        ) t
        <where>
            <if test="englishName!=null and englishName!=''">
                and t.english_name like concat('%',#{englishName},'%')
            </if>
            <if test="email!=null and email!=''">
                and t.email like concat('%',#{email},'%')
            </if>
            <if test="startDate != null">
                and t.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and t.add_time <![CDATA[ < ]]> #{endDate}
            </if>
            <choose>
                <when test="isSuccess == 0">
                    and t.manage_status != 3
                </when>
                <when test="isSuccess == 1">
                    and t.manage_status = 3
                </when>
            </choose>
        </where>
    </select>

    <select id="getSuccessRefeeralList" resultType="com.crm.rely.backend.core.dto.user.info.UserEntrySearchListDto"
            parameterType="com.crm.rely.backend.core.entity.user.info.UserReferralEntity">
        select * from (
            select a.first_name,a.last_name,'' english_name,a.calling_code,a.phone_numbe,a.email,a.add_time,b.manage_status from user_hire a
            left join user_entry b on a.id = b.hare_id where b.hare_id is null and a.referral_code = #{referralCode}
        union all
            select first_name,last_name,english_name,calling_code,phone_numbe,email,add_time, manage_status from user_entry where referral_code = #{referralCode}
        ) t
        <where>
            <if test="englishName!=null and englishName!=''">
                and t.english_name like concat('%',#{englishName},'%')
            </if>
            <if test="email!=null and email!=''">
                and t.email like concat('%',#{email},'%')
            </if>
            <if test="startDate != null">
                and t.add_time <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and t.add_time <![CDATA[ < ]]> #{endDate}
            </if>
            <choose>
                <when test="isSuccess == 0">
                    and (t.manage_status is null or t.manage_status != 3)
                </when>
                <when test="isSuccess == 1">
                    and t.manage_status = 3
                </when>
            </choose>
        </where>
        order by t.add_time desc
        limit #{page.offset},#{page.row}
    </select>


</mapper>