<?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.SalesRewardActivityMapper">
    <select id="getCustomDepositAmount" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        SELECT t.custom_id,
               t.depositAmount,
               cus.email,
               cus.first_name,
               cus.last_name,
               cus.name_en,
               cus.phone,
               cus.c_id,
               sales.id sales_id,
               sales.salary_login,
               sales.ib_no
        FROM (SELECT fd.custom_id,
                     SUM(fd.amount) AS depositAmount
              FROM finance_deposit fd
                       LEFT JOIN sales_reward_activity ac
                                 ON fd.custom_id = ac.custom_id
              WHERE fd.callback_status = 1
                AND fd.deposit_status = 2
                AND DATE_FORMAT(fd.callback_time, '%Y-%m-%d') = DATE_FORMAT(
                      DATE_SUB(
                              CASE
                                  WHEN #{nowTime} IS NULL OR #{nowTime} = '' THEN NOW()
                                  ELSE #{nowTime}
                                  END,
                              INTERVAL 1 DAY
            ),
                      '%Y-%m-%d'
                  )
                AND ac.custom_id IS NULL
              GROUP BY custom_id
              HAVING SUM(amount) >= 500) t
                 LEFT JOIN custom_info cus
                           ON t.custom_id = cus.id
                 LEFT JOIN user_info us
                           ON cus.agent_id = us.id
                 LEFT JOIN user_info sales
                           ON us.sales_id = sales.id;
    </select>

    <select id="getAddCustomCountAmount" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        SELECT sa.sales_id,
               sa.salary_login,
               sa.ib_no,
               COUNT(1) AS customNum
        FROM (SELECT t.custom_id,
                     t.depositAmount,
                     sales.id sales_id,
                     sales.salary_login,
                     sales.ib_no
              FROM (SELECT custom_id,
                           SUM(amount) AS depositAmount
                    FROM finance_deposit
                    WHERE callback_status = 1
                      AND deposit_status = 2
                      AND DATE_FORMAT(callback_time, '%Y-%m-%d') = DATE_FORMAT(
                            DATE_SUB(
                                    CASE
                                        WHEN #{nowTime} IS NULL OR #{nowTime} = '' THEN NOW()
                                        ELSE #{nowTime}
                                        END,
                                    INTERVAL 1 DAY
                ),
                            '%Y-%m-%d'
                        )
                    GROUP BY custom_id
                    HAVING SUM(amount) >= 200) t
                       LEFT JOIN custom_info cus
                                 ON t.custom_id = cus.id
                       LEFT JOIN user_info us
                                 ON cus.agent_id = us.id
                       LEFT JOIN user_info sales
                                 ON us.sales_id = sales.id
              WHERE DATE_FORMAT(cus.add_time, '%Y-%m-%d') = DATE_FORMAT(
                      DATE_SUB(
                              CASE
                                  WHEN #{nowTime} IS NULL OR #{nowTime} = '' THEN NOW()
                                  ELSE #{nowTime}
                                  END,
                              INTERVAL 1 DAY
            ),
                      '%Y-%m-%d'
                  )) sa
        GROUP BY sa.sales_id
        HAVING COUNT(1) >= 5;
    </select>

    <select id="salesRewardStatistics" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select sa.sales_id, usin.`name` salesName, count(1) customNum, sum(sa.amountSum) depositAmount
        from (select t.custom_id, t.amountSum, us.sales_id
              from (select custom_id, sum(amount) amountSum
                    from finance_deposit
                    where callback_status = 1
                      and deposit_status = 2
                      and DATE_FORMAT(callback_time, '%Y-%m-%d %H:%i:%s')  <![CDATA[>=]]> #{startTime}
                      and DATE_FORMAT(callback_time, '%Y-%m-%d %H:%i:%s')  <![CDATA[<=]]> #{endTime}
                    group by custom_id) t
                       left join custom_info cus on t.custom_id = cus.id
                       left join user_info us on cus.agent_id = us.id) sa
                 left join user_info usin on sa.sales_id = usin.id
        group by sa.sales_id
    </select>

    <select id="salesTradeStatistics" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select sa.sales_id,usin.`name` salesName,count(1) customNum from (
        select ts.custom_id,us.sales_id from (
        select login.custom_id from
        (
        select DISTINCT login from trade_history_mt4_${month}
        where DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[>=]]> #{startTime}
        and DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[<=]]> #{endTime}
        <if test="lastMonth != null and lastMonth != ''">
            union
            select DISTINCT login from trade_history_mt4_${lastMonth}
            where DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[>=]]> #{startTime}
            and DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[<=]]> #{endTime}
        </if>
        ) t
        left join custom_login login on t.login = login.login
        where login.platform = 'MT4'
        group by login.custom_id
        union
        select login.custom_id from
        (
        select DISTINCT login from trade_history_mt5_${month}
        where DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[>=]]> #{startTime}
        and DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[<=]]> #{endTime}
        <if test="lastMonth != null and lastMonth != ''">
            union
            select DISTINCT login from trade_history_mt5_${lastMonth}
            where DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[>=]]> #{startTime}
            and DATE_FORMAT(close_time,'%Y-%m-%d %H:%i:%s') <![CDATA[<=]]> #{endTime}
        </if>
        ) t
        left join custom_login login on t.login = login.login
        where login.platform = 'MT5'
        group by login.custom_id
        ) ts
        left join custom_info cus on ts.custom_id= cus.id
        left join user_info us on cus.agent_id= us.id
        ) sa left join user_info usin on sa.sales_id= usin.id
        group by sa.sales_id
    </select>

    <select id="salesRewardAmountStatistics" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select ac.sales_id, sum(ac.reward_amount) rewardAmount
        from sales_reward_activity ac
        where DATE_FORMAT(ac.reward_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startTime}, '%Y-%m-%d')
          and DATE_FORMAT(ac.reward_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endTime}, '%Y-%m-%d')
        group by ac.sales_id
    </select>

    <select id="salesDeductionAmountStatistics" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select ac.sales_id, sum(ac.deposit_amount) depositAmount
        from sales_reward_activity ac
        where ac.amount_type = 2
          and ac.reward_amount <![CDATA[<]]> 0
          and ac.deposit_amount <![CDATA[<]]> 0
          and DATE_FORMAT(ac.reward_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startTime}, '%Y-%m-%d')
          and DATE_FORMAT(ac.reward_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endTime}, '%Y-%m-%d')
        group by ac.sales_id
    </select>

    <select id="countSalesRewardStatisticsByMonth" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.sales.SelesRewardStatisticsEntity">
        select count(1) from sales_reward_statistics sr
        where DATE_FORMAT(sr.statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        and DATE_FORMAT(sr.statistics_date, '%Y-%m-%d')  <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        <choose>
            <when test="salesId != null">
                and sr.sales_id = #{salesId}
            </when>
            <otherwise>
                and sr.deposit_amount <![CDATA[>=]]> 100
            </otherwise>
        </choose>
    </select>

    <select id="salesRewardStatisticsByMonth" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardStatisticsDto"
            parameterType="com.crm.rely.backend.core.entity.sales.SelesRewardStatisticsEntity">
        select sr.*, info.ib_no salesIbNo, info.email salesEmail,
        (select sum(reward_amount) from sales_reward_statistics where sales_id = sr.sales_id or (sales_id is null and
        sales_name = sr.sales_name)) as rewardAmountSum
        from sales_reward_statistics sr
        left join user_info info on sr.sales_id = info.id
        where DATE_FORMAT(sr.statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        and DATE_FORMAT(sr.statistics_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
        <choose>
            <when test="salesId != null">
                and sr.sales_id = #{salesId}
            </when>
            <otherwise>
                and sr.deposit_amount <![CDATA[>=]]> 100
            </otherwise>
        </choose>
        order by sr.deposit_amount desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="salesSubIndexRewardStatisticsByMonth" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.sales.SelesRewardStatisticsEntity">
        select count(1)
        from sales_reward_statistics
        where DATE_FORMAT(statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
          and DATE_FORMAT(statistics_date, '%Y-%m-%d')  <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
          and deposit_amount <![CDATA[>=]]> #{depositAmount}
    </select>

    <select id="salesRewardWeekStatistics" resultType="com.crm.rely.backend.core.pojo.table.SalesRewardStatisticsTable">
        select * from sales_reward_statistics
        where DATE_FORMAT(statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startTime}, '%Y-%m-%d')
        <choose>
            <when test="salesId != null">
                and sales_id is not null
            </when>
            <otherwise>
                and sales_id is null
            </otherwise>
        </choose>
    </select>

    <select id="countList" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.sales.SelesRewardActivityEntity">
        select count(1) from sales_reward_activity sr
        left join user_info user on sr.sales_id = user.id
        <where>
            <if test="startDate!=null">
                and DATE_FORMAT(sr.reward_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
            </if>
            <if test="endDate!=null">
                and DATE_FORMAT(sr.reward_date, '%Y-%m-%d')  <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
            </if>
            <if test="salesName!=null and salesName!=''">
                and user.`name` like concat(#{salesName},"%")
            </if>
            <if test="ibNo!=null and ibNo!=''">
                and user.`ib_no` = #{ibNo}
            </if>
            <if test="cId!=null">
                and user.`c_id` = #{cId}
            </if>
        </where>
    </select>

    <select id="pageList" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardActivityDto"
            parameterType="com.crm.rely.backend.core.entity.sales.SelesRewardActivityEntity">
        select sr.*, user.`name` salesName, user.ib_no, cus.c_id
        from sales_reward_activity sr
        left join custom_info cus on sr.custom_id = cus.id
        left join user_info user on sr.sales_id = user.id
        <where>
            <if test="startDate!=null">
                and DATE_FORMAT(sr.reward_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
            </if>
            <if test="endDate!=null">
                and DATE_FORMAT(sr.reward_date, '%Y-%m-%d')  <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
            </if>
            <if test="salesName!=null and salesName!=''">
                and user.`name` like concat(#{salesName},"%")
            </if>
            <if test="ibNo!=null and ibNo!=''">
                and user.`ib_no` = #{ibNo}
            </if>
            <if test="cId!=null">
                and user.`c_id` = #{cId}
            </if>
        </where>
        order by sr.id desc
        limit #{page.offset},#{page.row}
    </select>

    <select id="getSalesDepositSum" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select sa.sales_id, usin.`name` salesName, sum(sa.amountSum) depositAmount
        from (select t.custom_id, t.amountSum, us.sales_id
              from (select custom_id, sum(amount) amountSum
                    from finance_deposit
                    where callback_status = 1
                      and deposit_status = 2
                      and DATE_FORMAT(callback_time, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
                    group by custom_id) t
                       left join custom_info cus on t.custom_id = cus.id
                       left join user_info us on cus.agent_id = us.id) sa
                 left join user_info usin on sa.sales_id = usin.id
        group by sa.sales_id
    </select>

    <select id="getSalesWithdrawSum" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select sa.sales_id, usin.`name` salesName, sum(sa.amountSum) withdrawAmount
        from (select t.custom_id, t.amountSum, us.sales_id
              from (select custom_id, sum(amount) amountSum
                    from finance_withdraw
                    where callback_status = 1
                      -- and withdraw_status = 1
                      and DATE_FORMAT(add_time, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
                    group by custom_id) t
                       left join custom_info cus on t.custom_id = cus.id
                       left join user_info us on cus.agent_id = us.id) sa
                 left join user_info usin on sa.sales_id = usin.id
        group by sa.sales_id
    </select>

    <select id="getSalesAgentWithdrawSum" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select sa.sales_id, usin.`name` salesName, sum(sa.amountSum) withdrawAmount
        from (select t.agent_id, t.amountSum, us.sales_id
              from (select agent_id, sum(amount) amountSum
                    from finance_agent_withdraw
                    where callback_status = 1
                      -- and withdraw_status = 1
                      and DATE_FORMAT(add_time, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
                    group by agent_id) t
                       left join user_info us on t.agent_id = us.id) sa
                 left join user_info usin on sa.sales_id = usin.id
        group by sa.sales_id
    </select>

    <select id="getSalesAddCustomSum" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select usin.sales_id, usin.`name`, count(1) customNum
        from custom_info cus
                 left join user_info usin on cus.agent_id = usin.id
        where usin.sales_id is not null
          and DATE_FORMAT(cus.add_time, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        group by usin.sales_id
    </select>

    <select id="getSalesRealCustomSum" resultType="com.crm.rely.backend.core.dto.sales.SalesRewardDto">
        select usin.sales_id, usin.`name`, count(1) customNum
        from custom_info cus
                 left join user_info usin on cus.agent_id = usin.id
        where usin.sales_id is not null
          and status = 2
          and DATE_FORMAT(cus.approve_time, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        group by usin.sales_id
    </select>

    <select id="findBySalesIdAndStatisticsDate"
            resultType="com.crm.rely.backend.core.pojo.table.SalesRewardStatisticsTable">
        select *
        from sales_reward_statistics
        where sales_id = #{salesId}
          and DATE_FORMAT(statistics_date, '%Y-%m-%d') = DATE_FORMAT(#{statisticsDate}, '%Y-%m-%d')
    </select>
</mapper>