<?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.CustomInfoDao">
    <select id="listCustom"
            parameterType="com.crm.rely.backend.core.entity.custom.info.CustomSearchEntity"
            resultType="com.crm.rely.backend.core.pojo.view.CustomView">
        select custom.id,first_name,middle
        middle,last_name,custom.email,custom.country,custom.country_name,custom.country_en_name,
        custom.name_en,custom.head_picture,custom.nationality,custom.login_number_limit,custom.phone,
        custom.source_code,
        custom.source_name,
        custom.source_name_en,custom.pay_comment_valid,
        custom.add_ip, custom.approve_time, custom.
        last_time,custom.birth,custom.valid,custom.add_time,custom.last_ip,custom.`status`,ifnull(custom.apply_real_status,0)
        apply_real_status,custom.apply_real_time,custom.approve_desc,custom.approve_time,custom.c_id,agent.ib_no as
        p_ib_no,custom.ib_invalid,custom.com_point,custom.sales_setting_level,custom.hide,
        custom.day_withdraw_limit,custom.exclude_show_login_types,custom.domain_name,custom.cent_login_number_limit,custom.login_number_limit_mt5
        from custom_info custom
        left join user_info agent on agent.id = custom.agent_id
        <where>
            <if test="comPoint != null">
                and custom.com_point =#{comPoint}
            </if>
            <if test="salesSettingLevel != null">
                and custom.sales_setting_level =#{salesSettingLevel}
            </if>
            <if test="stamp != null and stamp!=''">
                and agent.stamp like binary concat(#{stamp},'%')
            </if>
            <if test="ibNo != null and ibNo!=''">
                and agent.ib_no like concat('%',#{ibNo},'%')
            </if>
            <!--            <if test="groupId != null and groupId > 0">-->
            <!--                and agent.group_id = #{groupId}-->
            <!--            </if>-->
            <if test="platform != null and platform!=''">
                and custom.platform =#{platform}
            </if>
            <if test="email != null and email !=''">
                and custom.email =#{email}
            </if>
            <if test="identity != null and identity !=''">
                and custom.identity like concat('%',#{identity},'%')
            </if>
            <if test="cId != null">
                and custom.c_id =#{cId}
            </if>
            <if test="startDate != null">
                <if test="status==2">
                    and custom.approve_time <![CDATA[>=]]>#{startDate}
                </if>
                <if test="status!=2">
                    and custom.add_time <![CDATA[>=]]>#{startDate}
                </if>
            </if>
            <if test="endDate != null">
                <if test="status==2">
                    and custom.approve_time <![CDATA[<=]]>#{endDate}
                </if>
                <if test="status!=2">
                    and custom.add_time <![CDATA[<=]]>#{endDate}
                </if>
            </if>
            <if test="sourceCode != null and sourceCode!=''">
                and custom.source_code =#{sourceCode}
            </if>
            <if test="status!=null">
                <if test="status==0">
                    <if test="potentialStatus==null">
                        and (custom.status in (1,3) and ( custom.apply_real_status in(0,3) or custom.apply_real_status
                        is null))
                    </if>
                    <if test="potentialStatus!=null">
                        <if test="potentialStatus==0">
                            and (custom.apply_real_status =0 or custom.apply_real_status is null)
                        </if>
                        <if test="potentialStatus==1">
                            and (custom.status = 3 or custom.apply_real_status=3)
                        </if>
                    </if>
                </if>
                <if test="status==1">
                    and (custom.status =1 and custom.apply_real_status=1)
                </if>
                <if test="status==2">
                    and (custom.status =2 )
                </if>
                <if test="status==3">
                    and (custom.status = 3 or custom.apply_real_status=3)
                </if>
            </if>
        </where>
        <if test="status!=null">
            <if test="status==1">
                order by custom.apply_real_time desc,custom.status asc
            </if>
            <if test="status==2">
                order by custom.approve_time desc, custom.add_time,custom.status asc
            </if>
            <if test="status!=1 and status!=2">
                order by custom.add_time desc,custom.status asc
            </if>
        </if>
        <if test="status==null">
            order by custom.approve_time desc, custom.add_time desc,custom.status asc
        </if>
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="listCustomEmail"
            parameterType="com.crm.rely.backend.core.entity.custom.info.CustomSearchGetEmailEntity"
            resultType="com.crm.rely.backend.core.pojo.view.CustomInfoGetEmailView">
        select DISTINCT custom.id,first_name,middle
        middle_name,last_name,custom.email,custom.country,custom.country_name,custom.country_en_name
        from custom_info custom
        <if test="type == 2">
            join custom_login_demo cld on custom.id = cld.custom_id
        </if>
        <if test="type == 3">
            join custom_login cl on custom.id = cl.custom_id
        </if>
        <if test="type == 4 or (stamp!=null and stamp!='')">
            join user_info agent on agent.id = custom.agent_id
        </if>
        <if test="type == 5">
            join custom_activity_apply caa on custom.id = caa.custom_id
        </if>
        <where>
            <if test="stamp!=null and stamp!=''">
                and agent.stamp like binary concat(#{stamp},'%')
            </if>
            <if test="countrys!=null">
                and country in
                <foreach collection="countrys" index="idx" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="type == 5">
                and caa.status=2 and caa.complete_status = 0
            </if>
            <if test="status!=null">
                <if test="status==0">
                    <if test="potentialStatus==null">
                        and (custom.status in (1,3) and ( custom.apply_real_status in(0,3) or custom.apply_real_status
                        is null))
                    </if>
                    <if test="potentialStatus!=null">
                        <if test="potentialStatus==0">
                            and (custom.apply_real_status =0 or custom.apply_real_status is null)
                        </if>
                        <if test="potentialStatus==1">
                            and (custom.status = 3 or custom.apply_real_status=3)
                        </if>
                    </if>
                </if>
                <if test="status==1">
                    and (custom.status =1 and custom.apply_real_status=1)
                </if>
                <if test="status==2">
                    and (custom.status =2 )
                </if>
                <if test="status==3">
                    and (custom.status = 3 or custom.apply_real_status=3)
                </if>
            </if>
        </where>
    </select>

    <select id="countCustom"
            parameterType="com.crm.rely.backend.core.entity.custom.info.CustomSearchEntity"
            resultType="Integer">
        select count(*)
        from custom_info custom
        <if test="stamp != null or (groupId!=null and groupId > 0) or ibNo != null">
            left join user_info agent on agent.id = custom.agent_id
        </if>
        <where>
            <if test="comPoint != null">
                and custom.com_point =#{comPoint}
            </if>
            <if test="salesSettingLevel != null">
                and custom.sales_setting_level =#{salesSettingLevel}
            </if>
            <if test="stamp != null and stamp!=''">
                and agent.stamp like binary concat(#{stamp},'%')
            </if>
            <if test="ibNo != null and ibNo!=''">
                and agent.ib_no like concat('%',#{ibNo},'%')
            </if>
            <!--            <if test="groupId != null and groupId > 0">-->
            <!--                and agent.group_id = #{groupId}-->
            <!--            </if>-->
            <if test="platform != null and platform!=''">
                and custom.platform =#{platform}
            </if>
            <if test="email != null and email !=''">
                and custom.email =#{email}
            </if>
            <if test="identity != null and identity !=''">
                and custom.identity like concat('%',#{identity},'%')
            </if>
            <if test="cId != null">
                and custom.c_id =#{cId}
            </if>
            <if test="startDate != null">
                <if test="status==2">
                    and custom.approve_time <![CDATA[>=]]>#{startDate}
                </if>
                <if test="status!=2">
                    and custom.add_time <![CDATA[>=]]>#{startDate}
                </if>
            </if>
            <if test="endDate != null">
                <if test="status==2">
                    and custom.approve_time <![CDATA[<=]]>#{endDate}
                </if>
                <if test="status!=2">
                    and custom.add_time <![CDATA[<=]]>#{endDate}
                </if>
            </if>
            <if test="sourceCode != null and sourceCode!=''">
                and custom.source_code =#{sourceCode}
            </if>
            <if test="status!=null">
                <if test="status==0">
                    <if test="potentialStatus==null">
                        and (custom.status in (1,3) and ( custom.apply_real_status in(0,3) or custom.apply_real_status
                        is null))
                    </if>
                    <if test="potentialStatus!=null">
                        <if test="potentialStatus==0">
                            and (custom.apply_real_status =0 or custom.apply_real_status is null)
                        </if>
                        <if test="potentialStatus==1">
                            and (custom.status = 3 or custom.apply_real_status=3)
                        </if>
                    </if>
                </if>
                <if test="status==1">
                    and (custom.status =1 and custom.apply_real_status=1)
                </if>
                <if test="status==2">
                    and (custom.status =2 )
                </if>
                <if test="status==3">
                    and (custom.status = 3 or custom.apply_real_status=3)
                </if>
            </if>
        </where>
    </select>

    <select id="getAgentIdById" parameterType="Long" resultType="Long">
        select agent_id
        from custom_info
        where id = #{id}
    </select>

    <select id="getAgentIdListByIds" parameterType="java.util.List" resultType="Long">
        select agent_id
        from custom_info
        where id in
        <foreach collection="list" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="getSalesIdByEmail" resultType="Long">
        select agent.sales_id
        from custom_info
                 left join user_info agent
                           on agent.id = custom_info.agent_id
        where custom_info.email = #{email}
    </select>

    <delete id="deleteInBatch" parameterType="java.util.List">
        delete from custom_info
        where id in
        <foreach collection="list" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </delete>

    <select id="getOneByCId" resultType="com.crm.rely.backend.core.pojo.view.CustomView">
        SELECT custom_info.id,
               custom_info.ib_id,
               custom_info.first_name,
               custom_info.middle,
               custom_info.last_name,
               custom_info.name_en,
               custom_info.head_picture,
               custom_info.email,
               custom_info.`password`,
               custom_info.lang,
               custom_info.nationality,
               custom_info.area_code,
               custom_info.phone,
               custom_info.agent_id,
               custom_info.c_id,
               custom_info.ib_id,
               custom_info.gender,
               custom_info.birth,
               custom_info.identity,
               custom_info.country,
               custom_info.country_name,
               custom_info.country_en_name,
               custom_info.state,
               custom_info.city,
               custom_info.zip_code,
               custom_info.address_line,
               custom_info.tax_number,
               custom_info.check_email,
               custom_info.check_phone,
               custom_info.`status`,
               custom_info.add_time,
               custom_info.add_ip,
               custom_info.apply_real_status,
               custom_info.apply_real_time,
               custom_info.valid,
               custom_info.approve_desc,
               custom_info.hide,
               custom_info.login_number_limit,
               custom_info.custom_type,
               custom_info.company_name,
               custom_info.company_id,
               custom_info.source_code,
               custom_info.com_point,
               custom_info.sales_setting_level,
               custom_info.source_name,
               custom_info.source_name_en,
               custom_info.balance,
               custom_info.pay_comment_valid,
               custom_info.login_number_limit_mt5,
               user_agent.ib_no,
               agent.ib_no         as p_ib_no,
               IF(
                       agent.id IS NULL,
                       '',
                       concat(agent.ib_no, '-', agent.NAME)
                   )               AS agent,
               custom_survey.`LEVEL`,
               custom_survey.employment_status,
               custom_survey.occupation,
               custom_survey.trading_objectives,
               custom_survey.gross_annual_income,
               custom_survey.source_funding,
               custom_survey.total_new_worth,
               custom_survey.experience_trading_derivative,
               custom_survey.experience_trading_forex,
               custom_survey.derivative_products,
               custom_survey.experience_qualification,
               IF(
                       user_agent.id IS NULL,
                       0,
                       1
                   )               AS isAgent,
               user_agent.agent_level,
               agent_level.name    as agent_level_name,
               agent_level.en_name as agent_level_en_name,
               user_agent.balance     agent_balance,
               user_agent.unresolved,
               user_agent.frozen,
               user_agent.all_commission,
               custom_info.day_withdraw_limit
        FROM custom_info custom_info
                 LEFT JOIN user_info user_agent ON user_agent.c_id = custom_info.c_id
                 LEFT JOIN agent_level ON user_agent.agent_level = agent_level.id
                 LEFT JOIN user_info agent ON custom_info.agent_id = agent.id
                 LEFT JOIN custom_survey ON custom_survey.custom_id = custom_info.id
        WHERE custom_info.c_id = #{id}
    </select>

    <select id="getUcardCustomList"
            parameterType="com.crm.rely.backend.core.entity.custom.info.CustomSearchEntity"
            resultType="com.crm.rely.backend.core.pojo.view.CustomView">
        select *
        from custom_info
        where email like concat(#{email}, '%')
           or c_id like concat(#{email}, '%')
    </select>
</mapper>