<?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="cn.com.ctop.kuaishou.modules.report.mapper.KuaishouOperationReportMapper">

    <select id="getTtMonthlyOnJobOperatorsCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.id AS 'userId',
        t.realname AS 'userName',
        IFNULL(ROUND(t1.totalCharge,3),0.0) as '1totalCharge',
        IFNULL(ROUND(t3.totalCharge,3),0.0) as '2totalCharge',
        IFNULL(ROUND(t5.totalCharge,3),0.0) as '3totalCharge'
        FROM (SELECT id,realname FROM sys_user WHERE id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        ) t
        LEFT JOIN
        (
        SELECT user_id,user_name, SUM(cost) as 'totalCharge'
        FROM application.bytedance_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate1}
        AND stat_date &lt;= #{endDate1}
        GROUP BY user_id
        ) t1 ON t.id=t1.user_id
        LEFT JOIN
        (SELECT user_id, SUM(cost) as 'totalCharge'
        FROM application.bytedance_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate2}
        AND stat_date &lt;= #{endDate2}
        GROUP BY user_id
        ) t3 ON t.id = t3.user_id
        LEFT JOIN
        (SELECT user_id, SUM(cost) as 'totalCharge'
        FROM application.bytedance_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate3}
        AND stat_date &lt;= #{endDate3}
        GROUP BY user_id
        ) t5 ON t.id = t5.user_id
        order by userId
    </select>

    <select id="getKsMonthlyOnJobOperatorsCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.id AS 'userId',
        t.realname AS 'userName',
        IFNULL( ROUND(t1.totalCharge,3),0.0) as '1totalCharge',
        IFNULL( ROUND(t1.totalCharge - t2.JLCharge - t2.KFCharge,3),0.0) as '1FWCharge',
        IFNULL( ROUND(t2.JLCharge,3),0.0) as '1JLCharge',
        IFNULL( ROUND(t2.KFCharge,3),0.0) as '1KFCharge',

        IFNULL( ROUND(t3.totalCharge,3),0.0) as '2totalCharge',
        IFNULL( ROUND(t3.totalCharge - t4.JLCharge - t4.KFCharge,3),0.0) as '2FWCharge',
        IFNULL( ROUND(t4.JLCharge,3),0.0) as '2JLCharge',
        IFNULL( ROUND(t4.KFCharge,3),0.0) as '2KFCharge',

        IFNULL( ROUND(t5.totalCharge,3),0.0) as '3totalCharge',
        IFNULL( ROUND(t5.totalCharge - t6.JLCharge - t6.KFCharge,3),0.0) as '3FWCharge',
        IFNULL( ROUND(t6.JLCharge,3),0.0) as '3JLCharge',
        IFNULL( ROUND(t6.KFCharge,3),0.0) as '3KFCharge'
        FROM (SELECT id,realname FROM sys_user WHERE id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        ) t
        LEFT JOIN
        (
        SELECT user_id,user_name, SUM(charge) as 'totalCharge'
        FROM application.kuaishou_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate1}
        AND stat_date &lt;= #{endDate1}
        GROUP BY user_id
        ) t1 ON t.id=t1.user_id
        LEFT JOIN
        (
        SELECT user_id,
        SUM(contract_rebate_real_charged_in_yuan) as 'KFCharge',
        SUM(direct_rebate_real_charged_in_yuan) as 'JLCharge'
        FROM application.kuaishou_operation_performance_back_charge_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate1}
        AND stat_date &lt;= #{endDate1}
        GROUP BY user_id
        ) t2 ON t.id = t2.user_id
        LEFT JOIN
        (SELECT user_id, SUM(charge) as 'totalCharge'
        FROM application.kuaishou_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate2}
        AND stat_date &lt;= #{endDate2}
        GROUP BY user_id
        ) t3 ON t.id = t3.user_id
        LEFT JOIN
        (
        SELECT user_id,
        SUM(contract_rebate_real_charged_in_yuan) as 'KFCharge',
        SUM(direct_rebate_real_charged_in_yuan) as 'JLCharge'
        FROM application.kuaishou_operation_performance_back_charge_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate2}
        AND stat_date &lt;= #{endDate2}
        GROUP BY user_id
        ) t4 ON t.id = t4.user_id
        LEFT JOIN
        (SELECT user_id, SUM(charge) as 'totalCharge'
        FROM application.kuaishou_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate3}
        AND stat_date &lt;= #{endDate3}
        GROUP BY user_id
        ) t5 ON t.id = t5.user_id
        LEFT JOIN
        (
        SELECT user_id,
        SUM(contract_rebate_real_charged_in_yuan) as 'KFCharge',
        SUM(direct_rebate_real_charged_in_yuan) as 'JLCharge'
        FROM application.kuaishou_operation_performance_back_charge_base
        WHERE user_id in
        <foreach collection="userIds" item="user" separator="," open="(" close=")">
            #{user.userId}
        </foreach>
        AND stat_date >= #{startDate3}
        AND stat_date &lt;= #{endDate3}
        GROUP BY user_id
        ) t6 ON t.id = t6.user_id
        order by userId
    </select>


    <select id="getKsQuarterlyOnJobOperatorsCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.id AS 'userId',
        t.realname AS 'userName',
        IFNULL(ROUND(t1.totalCharge,3),0.0) as 'totalCharge',
        IFNULL(ROUND(t1.totalCharge - t2.JLCharge - t2.KFCharge,3),0.0) as 'FWCharge',
        IFNULL(ROUND(t2.JLCharge,3),0.0) as 'JLCharge',
        IFNULL(ROUND(t2.KFCharge,3),0.0) as 'KFCharge'
        FROM (SELECT id,realname FROM sys_user WHERE id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        ) t
        LEFT JOIN
        (
        SELECT user_id,user_name, SUM(charge) as 'totalCharge'
        FROM application.kuaishou_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        GROUP BY user_id
        ) t1 ON t.id=t1.user_id
        LEFT JOIN
        (
        SELECT user_id,
        SUM(contract_rebate_real_charged_in_yuan) as 'KFCharge',
        SUM(direct_rebate_real_charged_in_yuan) as 'JLCharge'
        FROM application.kuaishou_operation_performance_back_charge_base
        WHERE user_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        GROUP BY user_id
        ) t2 ON t.id = t2.user_id
    </select>

    <select id="getTtQuarterlyOnJobOperatorsCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.id AS 'userId',
        t.realname AS 'userName',
        IFNULL(ROUND(t1.totalCharge,3),0.0) as 'totalCharge'
        FROM (SELECT id,realname FROM sys_user WHERE id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        ) t
        LEFT JOIN
        (
        SELECT user_id,user_name, SUM(cost) as 'totalCharge'
        FROM application.bytedance_operation_performance_base
        WHERE user_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        GROUP BY user_id
        ) t1 ON t.id=t1.user_id
    </select>

    <select id="getManageChargeCommission" resultType="java.math.BigDecimal">
        SELECT IFNULL(ROUND(t1.totalCharge - t2.JLCharge - t2.KFCharge, 3), 0.0)
        FROM (
        SELECT SUM(charge) AS 'totalCharge'
        FROM application.kuaishou_operation_performance_base
        WHERE leader_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND user_id NOT IN (
        SELECT assistant_id
        FROM sys_operate_rel
        WHERE operate_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND stat_month >= DATE_FORMAT(#{startDate}, '%Y-%m')
        AND stat_month &lt;= DATE_FORMAT(#{endDate}, '%Y-%m')
        )
        AND stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        ) t1
        LEFT JOIN (
        SELECT SUM(contract_rebate_real_charged_in_yuan) as 'KFCharge', SUM(direct_rebate_real_charged_in_yuan) as
        'JLCharge'
        FROM application.kuaishou_operation_performance_back_charge_base
        WHERE leader_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND user_id NOT IN (
        SELECT assistant_id
        FROM sys_operate_rel
        WHERE operate_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND stat_month >= DATE_FORMAT(#{startDate}, '%Y-%m')
        AND stat_month &lt;= DATE_FORMAT(#{endDate}, '%Y-%m')
        )
        AND stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        ) t2 ON 1 = 1
    </select>

    <select id="getTtManageChargeCommission" resultType="java.math.BigDecimal">
        SELECT IFNULL(ROUND(SUM(cost), 3), 0.0)
        FROM application.bytedance_operation_performance_base
        WHERE leader_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND user_id NOT IN (
        SELECT assistant_id
        FROM sys_operate_rel
        WHERE operate_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
        AND stat_month >= DATE_FORMAT(#{startDate}, '%Y-%m')
        AND stat_month &lt;= DATE_FORMAT(#{endDate}, '%Y-%m')
        )
        AND stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
    </select>

    <select id="getTtManagers" resultType="String">
        SELECT user_id
        FROM application.bytedance_operation_performance_base
        WHERE leader_id = #{userId}
          AND stat_date >= #{startDate}
          AND stat_date &lt;= #{endDate}
        GROUP BY user_id
    </select>

    <select id="getManagers" resultType="String">
        SELECT user_id
        FROM application.kuaishou_operation_performance_base
        WHERE leader_id = #{userId}
          AND stat_date >= #{startDate}
          AND stat_date &lt;= #{endDate}
        GROUP BY user_id
    </select>
</mapper>