<?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="org.jeecg.ctop.material.mapper.ManagerSaleValueMapper">

    <select id="getRoleCodeByUserId" resultType="java.lang.String">
        select c.role_code
        from `jeecg-boot`.sys_user a
                 left join `jeecg-boot`.sys_user_role b on a.id = b.user_id
                 left join `jeecg-boot`.sys_role c on b.role_id = c.id
        where a.id = #{userId} LIMIT 1
    </select>


    <select id="queryUserCompany" resultType="com.alibaba.fastjson.JSONObject">
        select company_id as 'companyId',company_name as 'companyName'
        from `jeecg-boot`.user_company
        where user_id = #{userId}
    </select>

    <select id="getUserNameById" resultType="String">
        select realname
        from `jeecg-boot`.sys_user
        where id = #{userId}
    </select>

    <select id="queryList" resultType="com.alibaba.fastjson.JSONObject">
        select id as 'companyId',company_name as 'companyName'
        from `jeecg-boot`.sys_manager_company
    </select>

    <select id="querySaleDirctorItsTeamMember" resultType="String">
        select user_id as 'userId'
        from `application`.app_user_manage
        where manage_id = #{userId}
    </select>

    <select id="querySalesDirectorAccountIds" resultType="Long">
        SELECT
        account_id
        FROM
        `jeecg-boot`.ctop_user_allocation
        WHERE
        project_id IN (
        SELECT DISTINCT
        id
        FROM `jeecg-boot`.ctop_project
        WHERE 1=1
        <if test='mediaId ==2'>
            AND (media_id = 2 or media_id = 4)
        </if>
        <if test='mediaId ==1'>
            AND (media_id = 1 or media_id = 3)
        </if>
        <if test="userIds !=null and userIds.size()>0">
            AND sale_id IN
            <foreach collection="userIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        )
        GROUP BY
        account_id
    </select>

    <select id="querySalesDirectorAccountIds2" resultType="Long">
        SELECT
        account_id
        FROM
        `jeecg-boot`.ctop_user_allocation
        WHERE
        project_id IN (
        SELECT DISTINCT
        id
        FROM `jeecg-boot`.ctop_project
        WHERE company_id = #{companyId}
        <if test='mediaId ==2'>
            and media_id in(2,4)
        </if>
        <if test='mediaId ==1'>
            AND media_id in(1,3)
        </if>
        <if test="userIds !=null and userIds.size()>0">
            AND sale_id IN
            <foreach collection="userIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        )
        AND user_id in (
        select user_id
        from `application`.app_user_manage
        where manage_id IN ('4afe739502ae4258a18c36a000d7ff20','00d630a129e4487cba5427788a1f08ce')
        UNION ALL
        select '4afe739502ae4258a18c36a000d7ff20'
        UNION ALL
        select '00d630a129e4487cba5427788a1f08ce'
        )
        and user_id not in (select id
        from `jeecg-boot`.sys_user
        where sys_user.leader_id in
        ('b602f82bd3c24aa38b0fc7f187d47633',
        'd25c266dfab34277b9a44edacb2873b8','825c50296f794ca4b7757b4fae1c6b75','bedac9d87fc04470bb72bbbd82aaf1e0')
        union all
        select 'b602f82bd3c24aa38b0fc7f187d47633'
        union all
        select 'd25c266dfab34277b9a44edacb2873b8'
        union all
        select '825c50296f794ca4b7757b4fae1c6b75'
        union all
        select 'bedac9d87fc04470bb72bbbd82aaf1e0'
        )
        GROUP BY
        account_id
    </select>

    <select id="queryKsTodayCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(t.charge, 0.00) as 'totalCharge', CONCAT(
        CAST(IFNULL(ROUND((t.charge - y.charge) / y.charge * 100, 2), 0) as CHAR), '%') as 'chargeRoi'
        FROM (
        SELECT sum(charge) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        <where>
            <if test="saleTeaMber !=null and saleTeaMber.size()>0">
                AND sale_id in
                <foreach collection="saleTeaMber" item="id" separator=","
                         open="(" close=")">
                    #{id}
                </foreach>
            </if>
            and stat_date = DATE_FORMAT(#{statDay}, '%Y%m%d')
        </where>
        ) t
        LEFT JOIN
        (
        SELECT sum(charge) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        where stat_date = DATE_FORMAT(DATE_ADD(#{statDay},INTERVAL -1 day), '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) y on 1 = 1
    </select>


    <select id="queryKsTodayCharge2" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(t.charge, 0.00) as 'totalCharge', CONCAT(
        CAST(IFNULL(ROUND((t.charge - y.charge) / y.charge * 100, 2), 0) as CHAR), '%') as 'chargeRoi'
        FROM (
        SELECT sum(charge) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        <where>
            <if test="saleTeaMber !=null and saleTeaMber.size()>0">
                AND sale_id in
                <foreach collection="saleTeaMber" item="id" separator=","
                         open="(" close=")">
                    #{id}
                </foreach>
            </if>
            and stat_date >= DATE_FORMAT(#{statDay}, '%Y%m%d')
            and stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        </where>
        ) t
        LEFT JOIN
        (
        SELECT sum(charge) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        where stat_date >= DATE_FORMAT(DATE_ADD(#{statDay},INTERVAL -#{days} day), '%Y%m%d')
        AND stat_date &lt;= DATE_FORMAT(DATE_ADD(#{endTime},INTERVAL -#{days} day), '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) y on 1 = 1
    </select>

    <select id="queryKsTodayCharge3" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(t.charge, 0.00) as 'totalCharge', CONCAT(
        CAST(IFNULL(ROUND((t.charge - y.charge) / y.charge * 100, 2), 0) as CHAR), '%') as 'chargeRoi'
        FROM (
        SELECT sum(charge) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        where account_id in (
        SELECT
        account_id
        FROM
        `jeecg-boot`.ctop_user_allocation
        WHERE
        project_id IN (
        SELECT DISTINCT
        id
        FROM `jeecg-boot`.ctop_project
        WHERE company_id = #{companyId}
        <if test='mediaId ==2'>
            AND media_id in(2,4)
        </if>
        <if test='mediaId ==1'>
            AND media_id in(1,3)
        </if>
        )
        AND user_id in (select id
        from `jeecg-boot`.sys_user
        where sys_user.leader_id in
        ('b602f82bd3c24aa38b0fc7f187d47633',
        'd25c266dfab34277b9a44edacb2873b8','825c50296f794ca4b7757b4fae1c6b75','bedac9d87fc04470bb72bbbd82aaf1e0')
        union all
        select 'b602f82bd3c24aa38b0fc7f187d47633'
        union all
        select 'd25c266dfab34277b9a44edacb2873b8'
        union all
        select '825c50296f794ca4b7757b4fae1c6b75'
        union all
        select 'bedac9d87fc04470bb72bbbd82aaf1e0'
        )
        GROUP BY
        account_id
        )
        and company_id = #{companyId}
        and stat_date >= DATE_FORMAT(#{statDay}, '%Y%m%d')
        and stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        ) t
        LEFT JOIN
        (
        SELECT sum(charge) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        where stat_date >= DATE_FORMAT(DATE_ADD(#{statDay},INTERVAL -#{days} day), '%Y%m%d')
        AND stat_date &lt;= DATE_FORMAT(DATE_ADD(#{endTime},INTERVAL -#{days} day), '%Y%m%d')
        and company_id = #{companyId}
        AND account_id in(
        SELECT
        account_id
        FROM
        `jeecg-boot`.ctop_user_allocation
        WHERE
        project_id IN (
        SELECT DISTINCT
        id
        FROM `jeecg-boot`.ctop_project
        WHERE company_id = #{companyId}
        <if test='mediaId ==2'>
            AND in(2,4)
        </if>
        <if test='mediaId ==1'>
            AND media_id in(1,3)
        </if>
        )
        AND user_id in (select id
        from `jeecg-boot`.sys_user
        where sys_user.leader_id in
        ('b602f82bd3c24aa38b0fc7f187d47633',
        'd25c266dfab34277b9a44edacb2873b8','825c50296f794ca4b7757b4fae1c6b75','bedac9d87fc04470bb72bbbd82aaf1e0')
        union all
        select 'b602f82bd3c24aa38b0fc7f187d47633'
        union all
        select 'd25c266dfab34277b9a44edacb2873b8'
        union all
        select '825c50296f794ca4b7757b4fae1c6b75'
        union all
        select 'bedac9d87fc04470bb72bbbd82aaf1e0'
        )
        GROUP BY
        account_id
        )
        ) y on 1 = 1
    </select>

    <select id="queryTtTodayCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(t.charge, 0.00) as 'totalCharge', CONCAT(
        CAST(IFNULL(ROUND((t.charge - tt.charge) / tt.charge * 100, 2), 0) as CHAR), '%') as 'chargeRoi'
        FROM (
        SELECT sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        where stat_datetime = DATE_FORMAT(#{statDay}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) t
        LEFT JOIN
        (
        SELECT sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime = DATE_FORMAT(DATE_ADD(#{statDay},INTERVAL -1 day), '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) tt on 1 = 1
    </select>

    <select id="queryTtTodayCharge2" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(t.charge, 0.00) as 'totalCharge', CONCAT(
        CAST(IFNULL(ROUND((t.charge - tt.charge) / tt.charge * 100, 2), 0) as CHAR), '%') as 'chargeRoi'
        FROM (
        SELECT sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        where stat_datetime >= DATE_FORMAT(#{statDay}, '%Y%m%d')
        AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) t
        LEFT JOIN
        (
        SELECT sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT(DATE_ADD(#{statDay},INTERVAL -#{days} day), '%Y%m%d')
        AND stat_datetime &lt;=DATE_FORMAT(DATE_ADD(#{endTime},INTERVAL -#{days} day), '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) tt on 1 = 1
    </select>

    <select id="queryTtTodayCharge3" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(t.charge, 0.00) as 'totalCharge', CONCAT(
        CAST(IFNULL(ROUND((t.charge - tt.charge) / tt.charge * 100, 2), 0) as CHAR), '%') as 'chargeRoi'
        FROM (
        SELECT sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        where stat_datetime >= DATE_FORMAT(#{statDay}, '%Y%m%d')
        AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        and company_id = #{companyId}
        and account_id in
        (
        SELECT
        account_id
        FROM
        `jeecg-boot`.ctop_user_allocation
        WHERE
        project_id IN (
        SELECT DISTINCT
        id
        FROM `jeecg-boot`.ctop_project
        WHERE company_id = #{companyId}
        <if test='mediaId ==2'>
            AND media_id in(2,4)
        </if>
        <if test='mediaId ==1'>
            AND media_id in(1,3)
        </if>
        )
        AND user_id in (select id
        from `jeecg-boot`.sys_user
        where sys_user.leader_id in
        ('b602f82bd3c24aa38b0fc7f187d47633',
        'd25c266dfab34277b9a44edacb2873b8','825c50296f794ca4b7757b4fae1c6b75','bedac9d87fc04470bb72bbbd82aaf1e0')
        union all
        select 'b602f82bd3c24aa38b0fc7f187d47633'
        union all
        select 'd25c266dfab34277b9a44edacb2873b8'
        union all
        select '825c50296f794ca4b7757b4fae1c6b75'
        union all
        select 'bedac9d87fc04470bb72bbbd82aaf1e0'
        )
        GROUP BY
        account_id
        )
        ) t
        LEFT JOIN
        (
        SELECT sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT(DATE_ADD(#{statDay},INTERVAL -#{days} day), '%Y%m%d')
        AND stat_datetime &lt;=DATE_FORMAT(DATE_ADD(#{endTime},INTERVAL -#{days} day), '%Y%m%d')
        and account_id in
        (
        SELECT
        account_id
        FROM
        `jeecg-boot`.ctop_user_allocation
        WHERE
        project_id IN (
        SELECT DISTINCT
        id
        FROM `jeecg-boot`.ctop_project
        WHERE company_id = #{companyId}
        <if test='mediaId ==2'>
            AND media_id in(2,4)
        </if>
        <if test='mediaId ==1'>
            AND media_id in(1,3)
        </if>
        )
        AND user_id in (select id
        from `jeecg-boot`.sys_user
        where sys_user.leader_id in
        ('b602f82bd3c24aa38b0fc7f187d47633',
        'd25c266dfab34277b9a44edacb2873b8','825c50296f794ca4b7757b4fae1c6b75','bedac9d87fc04470bb72bbbd82aaf1e0')
        union all
        select 'b602f82bd3c24aa38b0fc7f187d47633'
        union all
        select 'd25c266dfab34277b9a44edacb2873b8'
        union all
        select '825c50296f794ca4b7757b4fae1c6b75'
        union all
        select 'bedac9d87fc04470bb72bbbd82aaf1e0'
        )
        GROUP BY
        account_id
        )
        ) tt on 1 = 1
    </select>

    <select id="queryTtManydaysTotalCharge" resultType="String">
        SELECT IFNULL(sum(cost), 0.00)
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT(#{startDay}, '%Y%m%d')
        and stat_datetime &lt;= DATE_FORMAT(#{endDay}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="queryTtManydaysCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DATE_FORMAT(stat_datetime,'%Y-%m-%d') as 'statDate', IFNULL(sum(cost), 0.00) as 'charge'
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d')
        and stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY stat_datetime
        order by stat_datetime
    </select>

    <select id="queryKsManydaysCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DATE_FORMAT(stat_date,'%Y-%m-%d') as 'statDate', IFNULL(sum(charge), 0.00) as 'charge'
        FROM application.kuaishou_account_report_daily_dw
        WHERE
        stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d')
        and stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY stat_date
        order by stat_date
    </select>

    <select id="queryKsManydaysTotalCharge" resultType="String">
        SELECT IFNULL(sum(charge), 0.00)
        FROM application.kuaishou_account_report_daily_dw
        WHERE
        stat_date >= DATE_FORMAT(#{startDay}, '%Y%m%d')
        and stat_date &lt;= DATE_FORMAT(#{endDay}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="queryKsRealCharged" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( thisMonth.realCharged, 0.00) AS 'realCharged',
        CONCAT( CAST( IFNULL( ROUND(( thisMonth.realCharged - lastMonth.realCharged ) / lastMonth.realCharged *
        100,2),0) AS CHAR),'%') AS 'realChargedRoi'
        FROM
        (
        SELECT
        IFNULL( sum( real_charged_in_yuan ), 0.00 ) AS 'realCharged'
        FROM
        application.app_kuaishou_agent_report_d
        WHERE stat_date >= DATE_FORMAT( #{monthFirstday}, '%Y%m%d' )
        AND stat_date &lt;= DATE_FORMAT( #{statDay}, '%Y%m%d' )
        <if test="accountIds !=null and accountIds.size()>0">
            AND account_id IN
            <foreach collection="accountIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) thisMonth
        LEFT JOIN (
        SELECT
        IFNULL( sum( real_charged_in_yuan ), 0.00 ) AS 'realCharged'
        FROM
        application.app_kuaishou_agent_report_d
        WHERE stat_date >= DATE_FORMAT(#{lastMonthFirstday}, '%Y%m%d' )
        AND stat_date &lt; DATE_FORMAT(#{monthFirstday}, '%Y%m%d' )
        <if test="accountIds !=null and accountIds.size()>0">
            AND
            account_id IN
            <foreach collection="accountIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) lastMonth ON 1=1
    </select>

    <select id="queryTtRealCharged" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( thisMonth.realCharged, 0.00) AS 'realCharged',
        CONCAT( CAST( IFNULL( ROUND(( thisMonth.realCharged - lastMonth.realCharged ) / lastMonth.realCharged *
        100,2),0) AS CHAR),'%') AS 'realChargedRoi'
        FROM
        (
        SELECT
        IFNULL( sum( cash_cost ), 0.00 ) AS 'realCharged'
        FROM
        application.app_bytedance_agent_report_d
        WHERE stat_datetime >= DATE_FORMAT( #{monthFirstday}, '%Y%m%d' )
        AND stat_datetime &lt;= DATE_FORMAT( #{statDay}, '%Y%m%d' )
        <if test="accountIds !=null and accountIds.size()>0">
            AND
            account_id IN
            <foreach collection="accountIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) thisMonth
        LEFT JOIN (
        SELECT
        IFNULL( sum( cash_cost ), 0.00 ) AS 'realCharged'
        FROM
        application.app_bytedance_agent_report_d
        WHERE stat_datetime >= DATE_FORMAT(#{lastMonthFirstday}, '%Y%m%d' )
        AND stat_datetime &lt; DATE_FORMAT(#{monthFirstday}, '%Y%m%d' )
        <if test="accountIds !=null and accountIds.size()>0">
            AND account_id IN
            <foreach collection="accountIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) lastMonth ON 1=1
    </select>


    <select id="getWeekly" resultType="Long">
        SELECT IFNULL(weekly_goal_value, 0)
        FROM `jeecg-boot`.sys_manager_sale_value
        WHERE user_id = #{userId}
          AND media_id = #{mediaId}
    </select>
    <select id="getMonthly" resultType="Long">
        SELECT IFNULL(monthly_goal_value, 0)
        FROM `jeecg-boot`.sys_manager_sale_value
        WHERE user_id = #{userId}
          AND media_id = #{mediaId}
    </select>


    <select id="querySalesDirectorProjectIds" resultType="Long">
        SELECT DISTINCT
        t1.project_id
        FROM
        `jeecg-boot`.ctop_project_member t1
        LEFT JOIN `jeecg-boot`.ctop_project t2 ON t1.project_id = t2.id
        WHERE t2.company_id =#{companyId}
        <if test='userIds !=null and userIds.size() > 0'>
            AND t1.user_id IN
            <foreach collection="userIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        <if test='mediaId ==2'>
            AND (t2.media_id = 2 or t2.media_id = 4)
        </if>
        <if test='mediaId ==1'>
            AND (t2.media_id = 1 or t2.media_id = 3)
        </if>
        AND t1.project_name IS NOT NULL
        AND t1.project_name != ''
    </select>


    <select id="queryKsHourList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT hour,sum(charge) as 'charge'
        FROM application.kuaishou_account_report_hourly_dw
        WHERE stat_date = DATE_FORMAT(#{statDay}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>

    <select id="queryTtHourList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT hour,sum(cost) as 'charge'
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE stat_datetime = DATE_FORMAT(#{statDay}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id in
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>


    <select id="queryTtManydaysChargeAndRoi" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( this.charge, 0.00) AS 'totalCharge',
        CONCAT( CAST( IFNULL( ROUND(( this.charge - last.charge ) / last.charge* 100,2),0) AS CHAR),'%') AS
        'chargeRoi'
        FROM
        (
        SELECT
        IFNULL( sum(cost), 0.00 ) AS 'charge'
        FROM
        application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) this
        LEFT JOIN (
        SELECT
        IFNULL( sum(cost), 0.00 ) AS 'charge'
        FROM
        application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT( #{lastTimeStart}, '%Y%m%d' )
        AND stat_datetime &lt;= DATE_FORMAT( #{lastTimeEnd}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) last ON 1=1
    </select>


    <select id="queryKsManydaysChargeAndRoi" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( this.charge, 0.00) AS 'totalCharge',
        CONCAT( CAST( IFNULL( ROUND(( this.charge - last.charge ) / last.charge* 100,2),0) AS CHAR),'%') AS
        'chargeRoi'
        FROM
        (
        SELECT
        IFNULL( sum(charge), 0.00 ) AS 'charge'
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) this
        LEFT JOIN (
        SELECT
        IFNULL( sum(charge), 0.00 ) AS 'charge'
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE stat_date >= DATE_FORMAT( #{lastTimeStart}, '%Y%m%d' )
        AND stat_date &lt;= DATE_FORMAT( #{lastTimeEnd}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            AND sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        ) last ON 1=1
    </select>


    <select id="queryKsHourProjectTopTotal" resultType="Long">
        SELECT count(1)
        FROM
        (select 1 from application.kuaishou_account_report_daily_dw t1
        where t1.stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d')
        and t1.stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY project_id
        ) t
    </select>

    <select id="queryTtHourProjectTopTotal" resultType="Long">
        SELECT count(1)
        FROM
        (select 1 from application.bytedance_advertiser_report_daily_dw t1
        where t1.stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d')
        and t1.stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d')
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY project_id
        ) t
    </select>

    <select id="queryKsHourProjectTop" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.project_id as 'projectId',
        t1.project_name as 'projectName',
        t4.realname AS 'saleName',
        t5.realname AS 'operateName',
        SUM( t1.charge ) AS 'charge',
        CONCAT( CAST( IFNULL( ROUND(( SUM( t1.charge ) - t3.charge ) / t3.charge * 100, 2 ), 0.00 ) AS CHAR ), '%' ) AS
        'chargeRoi',
        IFNULL(t2.realCharge,0.00) AS 'realCharge',
        IFNULL(t2.jlCharge,0.00) AS 'jlCharge',
        t6.newUnitNum,
        row_number() over(order by SUM( t1.charge ) desc) AS 'number'
        FROM
        application.kuaishou_account_report_daily_dw t1
        LEFT JOIN(
        SELECT project_id,
        SUM(real_charged_in_yuan) as 'realCharge',
        SUM(direct_rebate_real_charged_in_yuan) as 'jlCharge'
        FROM application.app_kuaishou_agent_report_d
        WHERE stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        <if test="accountIds !=null and accountIds.size()>0">
            and account_id IN
            <foreach collection="accountIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY project_id
        )t2 ON t1.project_id = t2.project_id
        LEFT JOIN (
        SELECT project_id, SUM( charge ) AS 'charge' FROM application.kuaishou_account_report_daily_dw
        WHERE 1=1
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        AND stat_date >= DATE_FORMAT(DATE_ADD(#{startTime}, INTERVAL - ${days} DAY ), '%Y%m%d' )
        AND stat_date &lt;= DATE_FORMAT(DATE_ADD(#{endTime}, INTERVAL - ${days} DAY ), '%Y%m%d' )
        GROUP BY project_id ) t3 ON t1.project_id = t3.project_id
        left join(select id,realname from `jeecg-boot`.sys_user )t4 on t1.sale_id=t4.id
        LEFT JOIN ( SELECT t1.id, realname FROM `jeecg-boot`.ctop_project t1 LEFT JOIN `jeecg-boot`.sys_user t2 ON
        t1.responsible_id = t2.id ) t5 ON t1.project_id = t5.id
        LEFT JOIN (
        select project_id,
        sum(ifnull(new_unit_num, 0)) as 'newUnitNum'
        from application.app_kuaishou_account_base_info_d
        where stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        and stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        group by project_id
            ) t6 ON t1.project_id = t6.project_id
        WHERE 1=1
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and t1.sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        AND stat_date >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        AND stat_date &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        GROUP BY t1.project_id
    </select>


    <select id="queryTtHourProjectTop" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.project_id as 'projectId',
        t1.project_name as 'projectName',
        t4.realname AS 'saleName',
        t5.realname AS 'operateName',
        SUM( t1.cost ) AS 'charge',
        CONCAT( CAST( IFNULL( ROUND(( SUM( t1.cost ) - t3.charge ) / t3.charge * 100, 2 ), 0 ) AS CHAR ), '%' ) AS
        'chargeRoi',
        IFNULL(t2.realCharge,0.00) AS 'realCharge',
        IFNULL(t2.jlCharge,0.00) AS 'jlCharge',
        t6.newUnitNum,
        row_number() over(order by SUM( t1.cost ) desc) AS 'number'
        FROM
        application.bytedance_advertiser_report_daily_dw t1
        LEFT JOIN(
        SELECT project_id,
        SUM(cash_cost) as 'realCharge',
        SUM(grants_cost) AS 'jlCharge'
        FROM application.app_bytedance_agent_report_d
        WHERE stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        AND stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        <if test="accountIds !=null and accountIds.size()>0">
            and account_id IN
            <foreach collection="accountIds" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY project_id
        )t2 ON t1.project_id = t2.project_id
        LEFT JOIN (
        SELECT project_id, SUM( cost ) AS 'charge' FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= DATE_FORMAT(DATE_ADD(#{startTime}, INTERVAL - ${days} DAY ), '%Y%m%d' )
        AND stat_datetime &lt;= DATE_FORMAT(DATE_ADD(#{endTime}, INTERVAL - ${days} DAY ), '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY project_id ) t3 ON t1.project_id = t3.project_id
        left join(select id,realname from `jeecg-boot`.sys_user )t4 on t1.sale_id=t4.id
        LEFT JOIN ( SELECT t1.id, realname FROM `jeecg-boot`.ctop_project t1 LEFT JOIN `jeecg-boot`.sys_user t2 ON
        t1.responsible_id = t2.id ) t5 ON t1.project_id = t5.id
        LEFT JOIN (
        select project_id,
        sum(ifnull(promotion_num,0)) as 'newUnitNum'
        from application.app_bytedance_account_base_info_d
        where stat_date>= DATE_FORMAT(#{startTime}, '%Y%m%d' )
          and stat_date&lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        group by project_id
        ) t6 ON t1.project_id = t6.project_id
        WHERE t1.stat_datetime >= DATE_FORMAT(#{startTime}, '%Y%m%d' )
        AND t1.stat_datetime &lt;= DATE_FORMAT(#{endTime}, '%Y%m%d' )
        <if test="saleTeaMber !=null and saleTeaMber.size()>0">
            and t1.sale_id IN
            <foreach collection="saleTeaMber" item="id" separator=","
                     open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY t1.project_id
    </select>

</mapper>