<?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.CustomAllocationRosterMapper">
    <insert id="taskAddAllocation">
        INSERT INTO `custom_allocation_roster` (`custom_id`,
        `c_id`,
        `agent_id`,
        `allocation_agent_id`,
        `status`, `add_time`, `source_code`, `source_name`, `source_name_en`)
        SELECT custom_info.id,
        custom_info.c_id,
        custom_info.agent_id,
        null,
        0,
        custom_info.add_time,
        custom_info.source_code,
        custom_info.source_name,
        custom_info.source_name_en
        FROM custom_info
        LEFT JOIN custom_allocation_roster car on car.custom_id = custom_info.id
        LEFT JOIN (SELECT custom_id
        FROM finance_deposit
        where callback_status = 1
        and `status` = 2
        and deposit_status = 2
        GROUP BY custom_id) fcid ON fcid.custom_id = custom_info.id
        WHERE custom_info.agent_id = (SELECT `value`
        FROM sys_config
        WHERE `code` = 'COMPANY_ACCOUNT_SUPER')
        AND fcid.custom_id IS NULL
        AND car.id IS NULL
        <if test="country != null and  country!='' and country=='CN'">
            and DATE_ADD(NOW(), INTERVAL -72 HOUR) > custom_info.add_time
            and custom_info.country='CN'
        </if>
        <if test="country==null or country=='' or country!='CN'">
            and DATE_ADD(NOW(), INTERVAL -24 HOUR) > custom_info.add_time
            and custom_info.country!='CN'
        </if>
    </insert>
    <update id="taskUpdateAllocationRecovery">
        update custom_allocation_roster
        set `status`=0,
            allocation_time=NULL,
            allocation_agent_id=NULL,
            follow_time=NULL,
            follow_tag=NULL
        where `status` = 1
          and (((follow_time is null or follow_tag is null)
            and DATE_ADD(NOW(), INTERVAL -48 HOUR) > allocation_time)
            or ((follow_time is not null or follow_tag is not null)
                and DATE_ADD(NOW(), INTERVAL -6 MONTH) > allocation_time))
    </update>

    <update id="taskUpdateAllocationRecoveryByCN">
        update custom_allocation_roster
            LEFT JOIN custom_info
        on custom_allocation_roster.custom_id = custom_info.id
            set custom_allocation_roster.`status`=0,
                custom_allocation_roster.allocation_time= NULL,
                custom_allocation_roster.allocation_agent_id= NULL
        where custom_allocation_roster.`status` = 1
          and custom_info.country='CN'
          and DATE_ADD(NOW()
            , INTERVAL -48 HOUR)
            > custom_allocation_roster.allocation_time;
    </update>
    <update id="taskUpdateAllocationReviseAgent">
        UPDATE custom_allocation_roster
            JOIN custom_info
        ON custom_info.id = custom_allocation_roster.custom_id
            SET custom_allocation_roster.`status` = 2,
                custom_allocation_roster.allocation_agent_id = NULL,
                custom_allocation_roster.allocation_time = NULL
        WHERE
            custom_allocation_roster.agent_id != custom_info.agent_id
          AND (
            custom_info.agent_id != custom_allocation_roster.allocation_agent_id
           OR custom_allocation_roster.allocation_agent_id IS NULL
            )
          AND custom_allocation_roster.`status` != 2;
    </update>


    <select id="selectList" resultType="com.crm.rely.backend.core.pojo.view.CustomAllocationRosterView"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterSearchEntity">
        SELECT car.*,
        p.ib_no allocation_pib_no,
        sales.ib_no allocation_sales_ib_no,
        custom_info.email,
        custom_info.first_name,
        custom_info.middle,
        custom_info.last_name,
        custom_info.country,
        custom_info.country_name,
        custom_info.country_en_name,
        custom_info.area_code,
        custom_info.phone,
        agent.ib_no p_ib_no
        from custom_allocation_roster car
        LEFT JOIN custom_info on custom_info.id = car.custom_id
        LEFT JOIN user_info agent ON agent.id = custom_info.agent_id
        LEFT JOIN user_info p ON p.id = car.allocation_agent_id
        LEFT JOIN user_info sales ON p.sales_id = sales.id
        <include refid="selectCondition">
        </include>

        order by
        <if test="status!=null">
            <if test="status==1">
                car.allocation_time desc,
            </if>
            <if test="status==2">
                car.allocation_time desc,
            </if>
            <if test="status==3">
                car.modify_time desc,
            </if>
        </if>
        car.add_time desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="selectCount" resultType="java.lang.Integer"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterSearchEntity">
        SELECT count(*)
        from custom_allocation_roster car
        <if test="(email!=null and email!='') or areaId!=null or (country!=null and country!='')">
            LEFT JOIN custom_info on custom_info.id = car.custom_id
        </if>
        <if test="(allocationPibNo!=null and email!='') or (allocationPibNo!=null and allocationPibNo!='') or (allocationSalesIbNo!=null and allocationSalesIbNo!='')">
            LEFT JOIN user_info p ON p.id = car.allocation_agent_id
            LEFT JOIN user_info sales ON p.sales_id = sales.id
        </if>
        <include refid="selectCondition">
        </include>
    </select>
    <select id="selectCsvList" resultType="com.crm.rely.backend.core.pojo.view.CustomAllocationRosterView"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterSearchEntity">
        SELECT car.*,
        p.ib_no allocation_pib_no,
        p.email allocation_pib_email,
        sales.ib_no allocation_sales_ib_no,
        sales.email allocation_sales_email,
        custom_info.email,
        custom_info.first_name,
        custom_info.middle,
        custom_info.last_name,
        custom_info.area_code,
        custom_info.phone,
        agent.ib_no p_ib_no
        from custom_allocation_roster car
        LEFT JOIN custom_info on custom_info.id = car.custom_id
        LEFT JOIN user_info agent ON agent.id = custom_info.agent_id
        LEFT JOIN user_info p ON p.id = car.allocation_agent_id
        LEFT JOIN user_info sales ON p.sales_id = sales.id
        <include refid="selectCondition">
        </include>

        order by
        <if test="status!=null">
            <if test="status==1">
                car.allocation_time desc,
            </if>
            <if test="status==2">
                car.allocation_time desc,
            </if>
            <if test="status==3">
                car.modify_time desc,
            </if>
        </if>
        car.add_time desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <sql id="selectCondition">
        <where>
            <if test="customId!=null">
                and car.custom_id =#{customId}
            </if>
            <if test="cId!=null">
                and car.c_id =#{cId}
            </if>
            <if test="sourceCode!=null and sourceCode!=''">
                and car.source_code =#{sourceCode}
            </if>
            <if test="agentId!=null">
                and car.agent_id =#{agentId}
            </if>
            <if test="allocationAgentId!=null">
                and car.allocation_agent_id =#{allocationAgentId}
            </if>
            <if test="status!=null">
                and car.status =#{status}
            </if>
            <if test="email!=null and email!=''">
                and custom_info.email =#{email}
            </if>
            <if test="areaId!=null">
                and custom_info.area_id =#{areaId}
            </if>
            <if test="country!=null and country!=''">
                <if test="country=='CN'">
                    and custom_info.country ='CN'
                </if>
                <if test="country!='CN'">
                    and custom_info.country !='CN'
                </if>
            </if>
            <if test="allocationPibNo!=null and allocationPibNo!=''">
                and p.ib_no =#{allocationPibNo}
            </if>
            <if test="allocationSalesIbNo!=null and allocationSalesIbNo!=''">
                and sales.ib_no =#{allocationSalesIbNo}
            </if>
            <if test="startDate != null">
                and car.add_time <![CDATA[>=]]>#{startDate}
            </if>
            <if test="endDate != null">
                and car.add_time <![CDATA[<=]]>#{endDate}
            </if>
        </where>
    </sql>

    <update id="clickAllocationRoster"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterClickEntity">
        UPDATE custom_allocation_roster
            LEFT JOIN custom_info
        ON custom_info.id = custom_allocation_roster.custom_id
            SET custom_allocation_roster.allocation_agent_id = (
                SELECT
                user_info.id
                FROM
                user_info
                WHERE
                ib_no = #{ibNo}
                ),
                custom_allocation_roster.`status` = 1,
                custom_allocation_roster.number = number + 1,
                custom_allocation_roster.allocation_time = NOW()
        WHERE
            custom_allocation_roster.`status` = 0
        AND custom_info.country in (
            SELECT
            `code`
            FROM
            sys_country
            WHERE
            `code` in
            <foreach collection="country" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        <if test="numberMin != null">
            AND `number` <![CDATA[>=]]> #{numberMin}
        </if>
        <if test="numberMax != null">
            AND `number` <![CDATA[<=]]> #{numberMax}
        </if>
        <if test="ruleNumber != null">
            order by id desc limit #{ruleNumber}
        </if>
    </update>

    <update id="clickAllocationRosterByCountrys"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterClickByCountrysEntity">
        <if test="countrys != null and ibNo != null and ibNo!=''">
            UPDATE custom_allocation_roster
            LEFT JOIN custom_info
            ON custom_info.id = custom_allocation_roster.custom_id
            SET custom_allocation_roster.allocation_agent_id = (
            SELECT
            user_info.id
            FROM
            user_info
            WHERE
            ib_no = #{ibNo}
            ),
            custom_allocation_roster.`status` = 1,
            custom_allocation_roster.number = number + 1,
            custom_allocation_roster.allocation_time = NOW()
            WHERE
            custom_allocation_roster.`status` = 0
            AND custom_info.country in (
            SELECT
            `code`
            FROM
            sys_country
            WHERE
            `code` in
            <if test="countrys != null ">
                <foreach collection="countrys" index="idx" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            )
            <if test="ruleNumber != null">
                order by id desc limit #{ruleNumber}
            </if>
        </if>
    </update>

    <select id="findClickAllocationRoster" resultType="com.crm.rely.backend.core.pojo.table.CustomAllocationRosterTable"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterClickEntity">
        select cal.* from custom_allocation_roster cal
        LEFT JOIN custom_info cus
        ON cus.id = cal.custom_id
        WHERE
        cal.`status` = 0
        and cus.country in (
            SELECT `code` FROM sys_country WHERE `code` in
            <foreach collection="country" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        )
        <if test="numberMin != null">
            AND cal.`number` <![CDATA[>=]]> #{numberMin}
        </if>
        <if test="numberMax != null">
            AND cal.`number` <![CDATA[<=]]> #{numberMax}
        </if>
        <if test="ruleNumber != null">
            order by cal.id desc limit #{ruleNumber}
        </if>
    </select>

    <select id="findClickAllocationRosterByCountrys" resultType="com.crm.rely.backend.core.pojo.table.CustomAllocationRosterTable"
            parameterType="com.crm.rely.backend.core.entity.custom.allocation.roster.CustomAllocationRosterClickByCountrysEntity">
        select cal.* from custom_allocation_roster cal
        LEFT JOIN custom_info cus
        ON cus.id = cal.custom_id
        WHERE
        cal.`status` = 0
        AND cus.country in
        <foreach collection="countrys" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        <if test="ruleNumber != null">
            order by cal.id desc limit #{ruleNumber}
        </if>
    </select>

</mapper>