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

    <select id="searchByDate" resultType="com.crm.rely.backend.core.pojo.view.ControlPanelStatisticsView"
            parameterType="com.crm.rely.backend.core.entity.control.panel.statistics.ControlPanelStatisticsEntity">
        SELECT
        #{startDate} as `date`,
        (
        SELECT
        IFNULL( SUM(finance_deposit.amount),0)
        FROM
        finance_deposit
        WHERE
        finance_deposit.callback_status = 1
        <if test="startDate!=null">
            AND finance_deposit.approve_time >#{startDate}
        </if>
        <if test="endDate!=null">
            AND #{endDate}>finance_deposit.approve_time
        </if>
        ) AS deposit_amount,
        (
        SELECT
        IFNULL( SUM(finance_withdraw.amount),0)
        FROM
        finance_withdraw
        WHERE
        finance_withdraw.`status` = 2
        <if test="startDate!=null">
            AND finance_withdraw.approve_time >#{startDate}
        </if>
        <if test="endDate!=null">
            AND #{endDate}>finance_withdraw.approve_time
        </if>
        ) AS withdraw_amount,
        (
        SELECT
        IFNULL( SUM(finance_transfer.deposit_amount),0)
        FROM
        finance_transfer
        WHERE
        finance_transfer.`status` = 2
        <if test="startDate!=null">
            AND finance_transfer.approve_time >#{startDate}
        </if>
        <if test="endDate!=null">
            AND #{endDate}>finance_transfer.approve_time
        </if>
        ) AS transfer_amount,
        (
        SELECT
        IFNULL( SUM(finance_agent_withdraw.amount),0)
        FROM
        finance_agent_withdraw
        WHERE
        finance_agent_withdraw.`status` = 2
        <if test="startDate!=null">
            AND finance_agent_withdraw.approve_time >#{startDate}
        </if>
        <if test="endDate!=null">
            AND #{endDate}>finance_agent_withdraw.approve_time
        </if>
        ) AS commission_amount;

    </select>

    <select id="searchListByDate" resultType="com.crm.rely.backend.core.pojo.view.ControlPanelStatisticsView"
            parameterType="com.crm.rely.backend.core.entity.control.panel.statistics.ControlPanelStatisticsEntity">
        SELECT * FROM control_panel_statistics
        <where>
            <if test="startDate!=null">
                AND `date` >=#{startDate}
            </if>
            <if test="endDate!=null">
                AND #{endDate} > `date`
            </if>
        </where>
    </select>

    <select id="searchCustomNumber" resultType="com.crm.rely.backend.core.pojo.view.ControlPanelCustomNumberView">
        SELECT
(SELECT count(*) FROM custom_info WHERE `status`=2) as custom_real,
(SELECT count(*) FROM custom_info WHERE (`status`=1 AND apply_real_status is null ) OR (apply_real_status =3)) as custom_potential,
(SELECT count(*) FROM custom_info WHERE `status`=1 AND apply_real_status =1) as custom_verification,
(SELECT count(*) FROM account_apply WHERE `status`=1 ) as account_apply
    </select>

    <select id="searchFinanceBalancePendingNumber"
            resultType="com.crm.rely.backend.core.pojo.view.ControlPanelFinanceBalancePendingNUmberView">
 SELECT
(SELECT count(*) FROM finance_deposit WHERE `status`=1 AND callback_status=1) as deposit_apply,
(SELECT count(*) FROM finance_withdraw WHERE `status`=1) as withdraw_apply,
(SELECT count(*) FROM finance_agent_withdraw WHERE `status`=1 ) as commission_apply,
(SELECT count(*) FROM finance_transfer WHERE `status`=1) as transfer_apply,
(SELECT count(*) FROM custom_activity_apply WHERE `status`=1) as activity_apply
    </select>

    <select id="searchManagerApplyNumber"
            resultType="com.crm.rely.backend.core.pojo.view.ControlPanelManagerApplyNumberView">
 SELECT
(SELECT count(finance_deposit.id) FROM finance_deposit WHERE `status`=1 AND callback_status=1) as deposit_apply,
(SELECT count(finance_withdraw.id) FROM finance_withdraw WHERE `status`=1) as withdraw_apply,
(SELECT count(custom_info.id) FROM custom_info WHERE `status`=1 AND apply_real_status =1) as custom_verification,
(SELECT count(account_apply.id) FROM account_apply WHERE `status`=1 ) as account_apply,
(SELECT count(custom_activity_apply.id) FROM custom_activity_apply WHERE `status`=1) as activity_apply,
(SELECT count(agent_apply.id) FROM agent_apply WHERE `status` = 1)                           as agent_apply
    </select>

    <select id="searchManagerByData"
            parameterType="com.crm.rely.backend.core.entity.control.panel.statistics.ControlPanelStatisticsEntity"
            resultType="com.crm.rely.backend.core.pojo.view.ControlPanelManagerNumberView">
        SELECT
        (
        SELECT
        count(*)
        FROM
        custom_info
        <where>
            <if test="startDate!=null">
                AND custom_info.add_time >#{startDate}
            </if>
            <if test="endDate!=null">
                AND #{endDate}>custom_info.add_time
            </if>
        </where>
        ) AS custom_add,
        (
        SELECT
        count(*)
        FROM
        custom_login_demo
        <where>
            <if test="startDate!=null">
                AND custom_login_demo.add_time >#{startDate}
            </if>
            <if test="endDate!=null">
                AND #{endDate}>custom_login_demo.add_time
            </if>
        </where>
        ) AS custom_demo_add,
        activity.activity_custom,
        activity.activity_agent
        FROM
        (
        (
        SELECT
        (
        count(custom_activity_apply.id) - count(user_info.id)
        ) AS activity_custom,
        count(user_info.id) AS activity_agent
        FROM
        custom_activity_apply
        LEFT JOIN user_info ON user_info.custom_id = custom_activity_apply.custom_id
        where custom_activity_apply. STATUS in (1,2)
        AND (
        custom_activity_apply.complete_status IS NULL
        OR custom_activity_apply.complete_status = 0
        )
        ) AS activity
        )
    </select>

</mapper>