<?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.SalesPerformanceMapper">
    <select id="getSalesDepositSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        SELECT
        sales.id AS sales_id,
        sales.`name` AS salesName,
        SUM(
        CASE
        WHEN ib_root.id IS NOT NULL THEN 0
        ELSE dw.deposit
        END
        ) AS depositAmount
        FROM trade_dw dw
        LEFT JOIN user_info agent ON dw.agent_id = agent.id
        LEFT JOIN user_info sales ON dw.sales_id = sales.id

        <!-- excludeIbNo -->
        <if test="excludeIbNo != null and excludeIbNo.size() > 0">
            LEFT JOIN (

            SELECT user_info.id, user_info.stamp,sales.ib_no salesNo
            FROM user_info
            LEFT JOIN user_info sales on sales.id=user_info.sales_id
            WHERE user_info.ib_no IN
            <foreach collection="excludeIbNo" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            ) ib_root ON agent.stamp LIKE CONCAT(ib_root.stamp, '%')and sales.ib_no=ib_root.salesNo
        </if>
        <if test="excludeIbNo == null or excludeIbNo.size() == 0">
            LEFT JOIN user_info ib_root ON 1=0
        </if>

        <!-- addIbNo stamps -->
        <if test="addIbNo != null and addIbNo.size() > 0">
            LEFT JOIN (
            SELECT ib_no, stamp,id_stamp
            FROM user_info
            WHERE ib_no IN
            <foreach collection="addIbNo" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            ) add_root ON dw.id_stamp LIKE CONCAT(add_root.id_stamp, '%') or dw.sales_no=add_root.ib_no or
            dw.ib_no=add_root.ib_no
        </if>

        WHERE dw.dw_type IN (1,6,9,15,18)
        AND sales.id IS NOT NULL
        AND dw.close_time <![CDATA[ >= ]]> #{startDate}
        AND dw.close_time <![CDATA[ < ]]> DATE_ADD(#{startDate}, INTERVAL 1 DAY)
        AND (
        sales.stamp LIKE CONCAT(#{stamp}, '%')
        OR sales.leader_id = #{leaderId}
        <if test="addIbNo != null and addIbNo.size() > 0">
            or add_root.ib_no is not null
        </if>

        )

        <if test="excludeIbNo != null and excludeIbNo.size() > 0">
            AND sales.ib_no NOT IN
            <foreach collection="excludeIbNo" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

        GROUP BY sales.id
    </select>

    <select id="getSalesWithdrawSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        SELECT
        sales.id AS sales_id,
        sales.`name` AS salesName,
        SUM(
        CASE
        WHEN ib_root.id IS NOT NULL THEN 0
        ELSE dw.withdrawal
        END
        ) AS withdrawAmount
        FROM trade_dw dw
        LEFT JOIN user_info agent ON dw.agent_id = agent.id
        LEFT JOIN user_info sales ON dw.sales_id = sales.id
        <if test="excludeIbNo != null and excludeIbNo.size() > 0">
            LEFT JOIN (

            SELECT user_info.id, user_info.stamp,sales.ib_no salesNo
            FROM user_info
            LEFT JOIN user_info sales on sales.id=user_info.sales_id
            WHERE user_info.ib_no IN
            <foreach collection="excludeIbNo" index="idx" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
            ) ib_root ON agent.stamp LIKE CONCAT(ib_root.stamp, '%')and sales.ib_no=ib_root.salesNo
        </if>
        <if test="excludeIbNo == null or excludeIbNo.size() == 0">
            LEFT JOIN user_info ib_root ON 1=0
        </if>
        <!-- addIbNo stamps -->
        <if test="addIbNo != null and addIbNo.size() > 0">
            LEFT JOIN (
            SELECT ib_no, stamp,id_stamp
            FROM user_info
            WHERE ib_no IN
            <foreach collection="addIbNo" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            ) add_root ON dw.id_stamp LIKE CONCAT(add_root.id_stamp, '%') or dw.sales_no=add_root.ib_no or
            dw.ib_no=add_root.ib_no
        </if>
        WHERE dw.dw_type IN (1, 6, 9, 15, 18)
        AND sales.id IS NOT NULL
        AND dw.close_time >= #{startDate}
        AND DATE_ADD(#{startDate}, INTERVAL 1 DAY) > dw.close_time
        AND (sales.stamp LIKE CONCAT(#{stamp}, '%') or sales.leader_id=#{leaderId}
        <if test="addIbNo != null and addIbNo.size() > 0">
            or add_root.ib_no is not null
        </if>
        )
        <if test="excludeIbNo != null and excludeIbNo.size >0">
            and sales.ib_no not in
            <foreach collection="excludeIbNo" index="idx" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY sales.id
    </select>

    <select id="getSalesAddCustomSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        select sales.id sales_id, sales.`name` salesName, count(1) customNum
        from custom_info cus
        left join user_info agent on cus.agent_id = agent.id
        left join user_info sales on agent.sales_id = sales.id
        <!-- addIbNo stamps -->
        <if test="addIbNo != null and addIbNo.size() > 0">
            LEFT JOIN (
            SELECT ib_no, stamp
            FROM user_info
            WHERE ib_no IN
            <foreach collection="addIbNo" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            ) add_root ON agent.stamp LIKE CONCAT(add_root.stamp, '%')
        </if>
        where agent.sales_id is not null
        and DATE_FORMAT(cus.add_time, '%Y-%m-%d') = DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        and (sales.stamp like concat(#{stamp}, '%') or sales.leader_id=#{leaderId}
        <if test="addIbNo != null and addIbNo.size() > 0">
            or add_root.ib_no is not null
        </if>
        )
        <if test="excludeIbNo != null and excludeIbNo.size >0">
            and agent.ib_no not in
            <foreach collection="excludeIbNo" index="idx" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
            and sales.ib_no not in
            <foreach collection="excludeIbNo" index="idx" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by sales.id
    </select>

    <select id="getSalesRealCustomSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        select sales.id sales_id, sales.`name` salesName, count(1) customNum
        from custom_info cus
        left join user_info agent on cus.agent_id = agent.id
        left join user_info sales on agent.sales_id = sales.id
        <!-- addIbNo stamps -->
        <if test="addIbNo != null and addIbNo.size() > 0">
            LEFT JOIN (
            SELECT ib_no, stamp
            FROM user_info
            WHERE ib_no IN
            <foreach collection="addIbNo" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
            ) add_root ON agent.stamp LIKE CONCAT(add_root.stamp, '%')
        </if>
        where agent.sales_id is not null
        and cus.status = 2
        and DATE_FORMAT(cus.approve_time, '%Y-%m-%d') = DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        and (sales.stamp like concat(#{stamp}, '%') or sales.leader_id=#{leaderId}
        <if test="addIbNo != null and addIbNo.size() > 0">
            or add_root.ib_no is not null
        </if>
        )
        <if test="excludeIbNo != null and excludeIbNo.size >0">
            and agent.ib_no not in
            <foreach collection="excludeIbNo" index="idx" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
            and sales.ib_no not in
            <foreach collection="excludeIbNo" index="idx" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by sales.id
    </select>

    <select id="countSearch" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.sales.SalesPerformanceListEntity">
        select count(1) from sales_leader saleader
        <where>
            <if test="salesNo != null and salesNo != ''">
                and saleader.sales_no = #{salesNo}
            </if>
        </where>
    </select>

    <select id="pageSearch" resultType="com.crm.rely.backend.core.pojo.view.TradeSalesPerformanceView"
            parameterType="com.crm.rely.backend.core.entity.sales.SalesPerformanceListEntity">
        select
        userleader.id leader_id,userleader.entry_date entryDate, userleader.name leaderName,
        userleader.name salesName,userleader.ib_no ib_no,userleader.c_id, userleader.resign_date resignDate,
        ifnull(salper.depositAmount,0) depositAmount, ifnull(salper.withdrawAmount,0) withdrawAmount,
        ifnull(salper.worthDepositAmount,0) worthDepositAmount, ifnull(salper.customNum,0) customNum,
        ifnull(salper.addCustomNum,0) addCustomNum,ifnull(cur.curMonthDepositAmount,0) curMonthDepositAmount,
        ifnull(cur.curMonthdWithdrawAmount,0) curMonthdWithdrawAmount,ifnull(cur.curMonthdWorthDepositAmount,0)
        curMonthdWorthDepositAmount, ifnull(cur.curAddCustomNum,0) curAddCustomNum,
        ifnull(ROUND(mon.monthDepositAmount,2),0) monthDepositAmount, ifnull(ROUND(mon.monthdWorthDepositAmount,2),0)
        monthdWorthDepositAmount
        from sales_leader saleader
        left join user_info userleader on saleader.sales_no = userleader.ib_no
        left join
        (select per.leader_id, leader.ib_no,
        sum(per.deposit_amount) depositAmount, sum(per.withdraw_amount) withdrawAmount,
        sum(per.worth_deposit_amount) worthDepositAmount, sum(per.custom_num) customNum,
        sum(per.add_custom_num) addCustomNum
        from sales_performance per
        left join user_info leader on per.leader_id = leader.id
        left join user_info sales on per.sales_id = sales.id
        where sales.role_id > -1
        <if test="startDate != null">
            and DATE_FORMAT(per.statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        </if>
        <if test="endDate != null">
            and DATE_FORMAT(per.statistics_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        </if>
        group by per.leader_id
        ) salper on saleader.sales_no = salper.ib_no
        left join (
        select sp.leader_id, leader.ib_no,sum(sp.deposit_amount) curMonthDepositAmount,
        sum(sp.withdraw_amount) curMonthdWithdrawAmount, sum(sp.worth_deposit_amount) curMonthdWorthDepositAmount,
        sum(sp.add_custom_num) curAddCustomNum
        from sales_performance sp
        left join user_info leader on sp.leader_id = leader.id
        left join user_info sales on sp.sales_id = sales.id
        where sales.role_id <![CDATA[>]]> -1
        and DATE_FORMAT(sp.statistics_date, '%Y-%m') = DATE_FORMAT(#{endDate}, '%Y-%m')
        group by sp.leader_id
        ) cur on saleader.sales_no = cur.ib_no
        left join ( select sp.leader_id,leader.ib_no,ifnull(sum(sp.deposit_amount)/3 ,0) monthDepositAmount,
        ifnull(sum(sp.worth_deposit_amount)/3 ,0) monthdWorthDepositAmount
        from sales_performance sp
        left join user_info leader on sp.leader_id = leader.id
        left join user_info sales on sp.sales_id = sales.id
        where sales.role_id  <![CDATA[>]]> -1
        and DATE_FORMAT(sp.statistics_date, '%Y-%m') <![CDATA[>=]]>  DATE_FORMAT(DATE_SUB(#{endDate}, interval 2 month),
        '%Y-%m')
        and DATE_FORMAT(sp.statistics_date, '%Y-%m') <![CDATA[<=]]>  DATE_FORMAT(#{endDate}, '%Y-%m')
        group by sp.leader_id
        ) mon on saleader.sales_no = mon.ib_no
        <if test="salesNo != null and salesNo != ''">
            where saleader.sales_no = #{salesNo}
        </if>
        order by saleader.sales_no
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="sumSearch" resultType="com.crm.rely.backend.core.pojo.view.TradeSalesPerformanceView"
            parameterType="com.crm.rely.backend.core.entity.sales.SalesPerformanceListEntity">
        select * from
        (
        select ifnull(sum(per.deposit_amount),0) depositAmount, ifnull(sum(per.withdraw_amount),0) withdrawAmount,
        ifnull(sum(per.worth_deposit_amount),0) worthDepositAmount, ifnull(sum(per.custom_num),0) customNum,
        ifnull(sum(per.add_custom_num),0) addCustomNum
        from sales_performance per
        left join user_info leader on per.leader_id = leader.id
        <where>
            <if test="salesNo != null and salesNo != ''">
                and leader.ib_no = #{salesNo}
            </if>
            <if test="startDate != null">
                and DATE_FORMAT(per.statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
            </if>
            <if test="endDate != null">
                and DATE_FORMAT(per.statistics_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
            </if>
        </where>
        ) sp
        join
        (
        select ifnull(sum(per.deposit_amount),0) curMonthDepositAmount,
        ifnull(sum(per.withdraw_amount),0) curMonthdWithdrawAmount, ifnull(sum(per.worth_deposit_amount),0)
        curMonthdWorthDepositAmount, ifnull(sum(per.add_custom_num), 0) curAddCustomNum
        from sales_performance per
        left join user_info leader on per.leader_id = leader.id
        where DATE_FORMAT(per.statistics_date, '%Y-%m') = DATE_FORMAT(#{endDate}, '%Y-%m')
        <if test="salesNo != null and salesNo != ''">
            and leader.ib_no = #{salesNo}
        </if>
        ) cur
        join
        (
        select ifnull(ROUND(sum(per.deposit_amount)/3,2) ,0) monthDepositAmount,
        ifnull(ROUND(sum(per.worth_deposit_amount)/3,2) ,0) monthdWorthDepositAmount
        from sales_performance per
        left join user_info leader on per.leader_id = leader.id
        where DATE_FORMAT(per.statistics_date, '%Y-%m') <![CDATA[>=]]>  DATE_FORMAT(DATE_SUB(#{endDate}, interval 2
        month), '%Y-%m')
        and DATE_FORMAT(per.statistics_date, '%Y-%m')  <![CDATA[<=]]>  DATE_FORMAT(#{endDate}, '%Y-%m')
        <if test="salesNo != null and salesNo != ''">
            and leader.ib_no = #{salesNo}
        </if>
        ) mon
    </select>

    <select id="salesPerformanceList" resultType="com.crm.rely.backend.core.pojo.view.TradeSalesPerformanceView"
            parameterType="com.crm.rely.backend.core.entity.sales.SalesPerformanceListEntity">
        select sales.entry_date entryDate, sales.resign_date resignDate, leader.name leaderName,
        sales.name salesName,sales.ib_no,sales.c_id,mon.sales_id,
        ifnull(saper.depositAmount,0) depositAmount, ifnull(saper.withdrawAmount,0) withdrawAmount,
        ifnull(saper.worthDepositAmount,0) worthDepositAmount, ifnull(saper.customNum,0) customNum,
        ifnull(saper.addCustomNum,0) addCustomNum, ifnull(cur.curMonthDepositAmount,0) curMonthDepositAmount,
        ifnull(cur.curMonthdWithdrawAmount,0) curMonthdWithdrawAmount, ifnull(cur.curMonthdWorthDepositAmount,0)
        curMonthdWorthDepositAmount,ifnull(cur.curAddCustomNum,0) curAddCustomNum,
        ifnull(ROUND(mon.monthDepositAmount,2),0) monthDepositAmount, ifnull(ROUND(mon.monthdWorthDepositAmount,0),2)
        monthdWorthDepositAmount
        from
        (select per.sales_id,per.leader_id,ifnull(sum(per.deposit_amount)/3 ,0) monthDepositAmount,
        ifnull(sum(per.worth_deposit_amount)/3 ,0) monthdWorthDepositAmount
        from sales_performance per
        where DATE_FORMAT(per.statistics_date, '%Y-%m') <![CDATA[>=]]> DATE_FORMAT(DATE_SUB(#{endDate}, interval 2
        month), '%Y-%m')
        and DATE_FORMAT(per.statistics_date, '%Y-%m') <![CDATA[<=]]>   DATE_FORMAT(#{endDate}, '%Y-%m')
        group by sales_id
        ) mon left join user_info sales on mon.sales_id = sales.id
        left join user_info leader on mon.leader_id = leader.id
        left join
        (select per.sales_id,sum(per.deposit_amount) depositAmount, sum(per.withdraw_amount) withdrawAmount,
        sum(per.worth_deposit_amount) worthDepositAmount, sum(per.custom_num) customNum,
        sum(per.add_custom_num) addCustomNum
        from sales_performance per
        where DATE_FORMAT(per.statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate},'%Y-%m-%d')
        AND DATE_FORMAT(per.statistics_date, '%Y-%m-%d') <![CDATA[<=]]>  DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        group by per.sales_id
        ) saper on mon.sales_id = saper.sales_id
        left join
        ( select sales_id,sum(deposit_amount) curMonthDepositAmount, sum(add_custom_num) curAddCustomNum,
        sum(withdraw_amount) curMonthdWithdrawAmount, sum(worth_deposit_amount) curMonthdWorthDepositAmount
        from sales_performance
        where DATE_FORMAT(statistics_date, '%Y-%m') = DATE_FORMAT(#{endDate}, '%Y-%m')
        group by sales_id
        ) cur on mon.sales_id = cur.sales_id
        <where>
            <if test="salesNo != null and salesNo != ''">
                and leader.ib_no = #{salesNo}
            </if>
        </where>
    </select>

    <select id="findAllSalesByStamp" resultType="com.crm.rely.backend.core.pojo.view.TradeSalesPerformanceView"
            parameterType="com.crm.rely.backend.core.entity.sales.SalesPerformanceListEntity">
        select sales.id         sales_id,
               sales.entry_date entryDate,
               sales.name       salesName,
               sales.ib_no,
               sales.c_id,
               sales.resign_date
        from user_info sales
                 left join user_role ur
                           on sales.role_id = ur.id
        where (
                (ur.`code` = 'ROLE_SALE'
                    and sales.stamp like binary concat(#{stamp}
                    , '%'))
                or
                sales.ib_no in
                (select sales_no
                 from sales_leader_extra
                 where leader_sales_no = #{salesNo}
                   and type = 1)
                or sales.leader_id = #{leaderId}
            )
          and sales.ib_no not in (select sales_no
                                  from sales_leader_extra
                                  where leader_sales_no = #{salesNo}
                                    and type = 2
                                    and (sales_date is null
                                      or DATE_FORMAT(#{startDate}, '%Y-%m-%d')
                                             >= DATE_FORMAT(DATE_ADD(sales_date
                                                                , INTERVAL 3 MONTH)
                                             , '%Y-%m-01 00:00:00')
                                      ))
    </select>

    <delete id="deleteSalesPerformanceByDate">
        delete
        from sales_performance
        <where>
            <if test="startDate != null">
                and DATE_FORMAT(statistics_date, '%Y-%m-%d') = DATE_FORMAT(#{startDate}, '%Y-%m-%d')
            </if>
            <if test="salesNo != null and salesNo != ''">
                and leader_id = (select id from user_info where ib_no = #{salesNo})
            </if>
        </where>
    </delete>

    <select id="getIbSalesDepositSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        select ib_root.id sales_id, ib_root.`name` salesName, sum(dw.deposit) depositAmount
        from trade_dw dw
        left join user_info sales on dw.agent_id = sales.id
        LEFT JOIN user_info ib_root
        ON sales.stamp LIKE CONCAT(ib_root.stamp, '%')
        LEFT JOIN user_info ibsales
        ON ibsales.id =ib_root.sales_id
        where dw.dw_type in (1, 6, 9, 15, 18)
        and sales.id is not null and deposit != 0
        AND dw.close_time >= #{startDate}
        AND DATE_ADD(#{startDate}, INTERVAL 1 DAY) > dw.close_time
        and ib_root.ib_no in
        <foreach collection="ibNoList" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        and dw.sales_no=ibsales.ib_no
        group by ib_root.id
    </select>

    <select id="getIbSalesWithdrawSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        select ib_root.id sales_id, ib_root.`name` salesName, sum(dw.withdrawal) withdrawAmount
        from trade_dw dw left join user_info sales on dw.agent_id = sales.id
        LEFT JOIN user_info ib_root
        ON sales.stamp LIKE CONCAT(ib_root.stamp, '%')
        LEFT JOIN user_info ibsales
        ON ibsales.id =ib_root.sales_id
        where dw.dw_type in (1, 6, 9, 15, 18)
        and sales.id is not null and withdrawal != 0
        AND dw.close_time >= #{startDate}
        AND DATE_ADD(#{startDate}, INTERVAL 1 DAY) > dw.close_time
        and ib_root.ib_no in
        <foreach collection="ibNoList" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        and dw.sales_no=ibsales.ib_no
        group by ib_root.id
    </select>

    <select id="getIbSalesAddCustomSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        select sales.id sales_id, sales.`name` salesName, count(1) customNum
        from custom_info cus
        left join user_info sales on cus.agent_id = sales.id
        where sales.sales_id is not null
        and DATE_FORMAT(cus.add_time, '%Y-%m-%d') = DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        and sales.ib_no in
        <foreach collection="ibNoList" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        group by sales.id
    </select>

    <select id="getIbSalesRealCustomSum" resultType="com.crm.rely.backend.core.dto.sales.SalesPerformanceDto">
        select sales.id sales_id, sales.`name` salesName, count(1) customNum
        from custom_info cus
        left join user_info sales on cus.agent_id = sales.id
        where sales.sales_id is not null
        and cus.status = 2
        and DATE_FORMAT(cus.approve_time, '%Y-%m-%d') = DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        and sales.ib_no in
        <foreach collection="ibNoList" index="idx" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        group by sales.id
    </select>

    <select id="getFirstDate" resultType="Date">
        select min(close_time)
        from trade_dw
    </select>
</mapper>