<?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.SalesRewardWorthStatisticsMapper">

    <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') = 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') = 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') = 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') = 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') = DATE_FORMAT(#{startDate}, '%Y-%m-%d')
        group by usin.sales_id
    </select>

    <select id="countSearch" resultType="integer"
            parameterType="com.crm.rely.backend.core.entity.base.BaseIbReportListEntity">
        select count(1) from user_info info
        left join user_role role on info.role_id = role.id
        left join (
            select sales_id from sales_reward_worth_statistics
            <where>
                <if test="startDate != null">
                    and DATE_FORMAT(statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
                </if>
                <if test="endDate != null">
                    and DATE_FORMAT(statistics_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
                </if>
            </where>
            group by sales_id
        )t on info.id = t.sales_id
        where role.code = 'ROLE_SALE'
        <if test="ibNo != null and ibNo != ''">
            and info.ib_no like concat(#{ibNo},'%')
        </if>
        <if test="cId != null and cId>0">
            and info.c_id = #{cId}
        </if>
    </select>

    <select id="pageSearch" resultType="com.crm.rely.backend.core.pojo.view.TradeSalesRewardView"
            parameterType="com.crm.rely.backend.core.entity.base.BaseIbReportListEntity">
        select info.c_id,info.ib_no,info.name,
            ifnull(t.depositAmount,0) depositAmount, ifnull(t.withdrawAmount,0) withdrawAmount, ifnull(t.agentWithdrawAmount,0) agentWithdrawAmount,
            ifnull(t.worthDepositAmount,0) worthDepositAmount, ifnull(t.addCustomNum,0) addCustomNum, ifnull(t.realCustomNum,0) realCustomNum
        from user_info info
        left join user_role role on info.role_id = role.id
        left join (
            select sales_id,sum(deposit_amount) depositAmount, sum(withdraw_amount) withdrawAmount, sum(agent_withdraw_amount) agentWithdrawAmount,
                   sum(worth_deposit_amount) worthDepositAmount, sum(add_custom_num) addCustomNum, sum(real_custom_num) realCustomNum
            from sales_reward_worth_statistics
            <where>
                <if test="startDate != null">
                    and DATE_FORMAT(statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
                </if>
                <if test="endDate != null">
                    and DATE_FORMAT(statistics_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
                </if>
            </where>
            group by sales_id
        )t on info.id = t.sales_id
        where role.code = 'ROLE_SALE'
        <if test="ibNo != null and ibNo != ''">
            and info.ib_no like concat(#{ibNo},'%')
        </if>
        <if test="cId != null and cId>0">
            and info.c_id = #{cId}
        </if>
        order by t.worthDepositAmount desc
        <if test="page != null">
            limit #{page.offset},#{page.row}
        </if>
    </select>

    <select id="sumSearch" resultType="com.crm.rely.backend.core.pojo.view.TradeSalesRewardView"
            parameterType="com.crm.rely.backend.core.entity.base.BaseIbReportListEntity">
        select ifnull(sum(reward.deposit_amount),0) depositAmount, ifnull(sum(reward.withdraw_amount),0) withdrawAmount, ifnull(sum(reward.agent_withdraw_amount),0) agentWithdrawAmount,
               ifnull(sum(reward.worth_deposit_amount),0) worthDepositAmount, ifnull(sum(reward.add_custom_num),0) addCustomNum, ifnull(sum(reward.real_custom_num),0) realCustomNum
        from sales_reward_worth_statistics reward
        left join user_info info on reward.sales_id = info.id
        <where>
            <if test="startDate != null">
                and DATE_FORMAT(reward.statistics_date, '%Y-%m-%d') <![CDATA[>=]]> DATE_FORMAT(#{startDate}, '%Y-%m-%d')
            </if>
            <if test="endDate != null">
                and DATE_FORMAT(reward.statistics_date, '%Y-%m-%d') <![CDATA[<=]]> DATE_FORMAT(#{endDate}, '%Y-%m-%d')
            </if>
            <if test="ibNo != null and ibNo != ''">
                and info.ib_no like concat(#{ibNo},'%')
            </if>
            <if test="cId != null and cId>0">
                and info.c_id = #{cId}
            </if>
        </where>
    </select>

</mapper>