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

    <!-- 快手 时报  -->
    <select id="getKuaiShouCostHourlyByUserIdList" resultType="Map">
        SELECT
        t.`hour` as 'orders',
        CONCAT(t.`hour`,':00') `hour`,
        ROUND( SUM( charge ), 3 ) AS totalCost
        FROM
        application.kuaishou_account_report_hourly_dw t
        where
        stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.`hour`
        ORDER BY orders
    </select>

    <!-- 头条 时报  -->
    <select id="getBytedanceCostHourlyByUserIdList" resultType="Map">
        SELECT
        `hour` as 'orders',
        CONCAT( `hour`, ':00' ) `hour`,
        ROUND( SUM( cost ), 3 ) AS totalCost
        FROM
        application.bytedance_advertiser_report_hourly_dw
        where
        stat_datetime &gt;= #{startDate}
        AND stat_datetime &lt;= #{endDate}
        <if test='list!=null and list.size() > 0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY `hour`
        ORDER BY orders
    </select>


    <!-- 快手-查询时报 消耗 前5 -->
    <select id="getKuaiShouCostByTop5HourList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        user_id,
        SUM( charge ) totalCost,
        (SELECT realname from `jeecg-boot`.sys_user u where u.id = user_id) as userName
        FROM
        application.kuaishou_account_report_hourly_dw
        WHERE
        stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size() > 0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id ORDER BY SUM(charge) DESC LIMIT 5
    </select>


    <!-- 头条-查询时报 消耗 前5 -->
    <select id="getBytedanceCostByTop5HourList" resultType="com.alibaba.fastjson.JSONObject">

        SELECT
        user_id,
        SUM( cost ) AS totalCost,
        ( SELECT realname FROM `jeecg-boot`.sys_user u WHERE u.id = user_id ) AS userName
        FROM
        application.bytedance_advertiser_report_hourly_dw
        WHERE
        stat_datetime &gt;= #{startDate}
        AND stat_datetime &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id
        ORDER BY SUM( cost ) DESC
        LIMIT 5
    </select>


    <!--快手 -日报  -->
    <select id="getKuaiShouCostDailyByUserIdList" resultType="Map">
        SELECT DATE_FORMAT(t.stat_date,'%Y-%m-%d') as dateTime,
        ROUND(SUM(charge),2) as totalCost
        FROM application.kuaishou_account_report_daily_dw t
        WHERE
        t.stat_date &gt;= #{startDate}
        AND t.stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.stat_date
        ORDER BY t.stat_date;
    </select>


    <!--头条 -日报  -->
    <select id="getBytedanceCostDailyByUserIdList" resultType="Map">
        SELECT
        DATE_FORMAT( t.stat_datetime, '%Y-%m-%d' ) AS dateTime,
        ROUND( SUM( t.cost ), 2 ) AS totalCost
        FROM
        application.bytedance_advertiser_report_daily_dw t
        WHERE
        t.stat_datetime &gt;= #{startDate}
        AND t.stat_datetime &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.stat_datetime
        ORDER BY t.stat_datetime;
    </select>

    <select id="getAdqCostHourlyByUserIdList" resultType="Map">
        SELECT
        stat_hour AS 'orders',
        CONCAT(stat_hour, ':00') AS `hour`,
        ROUND(SUM(cost), 3) AS totalCost
        FROM application.adq_advertiser_report_hourly
        WHERE stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY stat_hour
        ORDER BY orders
    </select>

    <select id="getAdqCostDailyByUserIdList" resultType="Map">
        SELECT
        DATE_FORMAT(stat_date, '%Y-%m-%d') AS dateTime,
        ROUND(SUM(cost), 2) AS totalCost
        FROM application.adq_advertiser_report_daily
        WHERE stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY stat_date
        ORDER BY stat_date
    </select>


    <!-- 快手-查询日报 消耗 前5 -->
    <select id="getKuaiShouCostByTop5DailyList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        user_id,
        SUM( charge ) totalCost,
        (SELECT realname from `jeecg-boot`.sys_user u where u.id = user_id) as userName
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE
        stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id ORDER BY SUM(charge) DESC LIMIT 5
    </select>


    <!-- 头条-查询日报 消耗 前5 -->
    <select id="getBytedanceCostByTop5DailyList" resultType="com.alibaba.fastjson.JSONObject">

        SELECT
        SUM( t.cost ) AS totalCost,
        ( SELECT realname FROM `jeecg-boot`.sys_user u WHERE u.id = t.user_id ) AS userName
        FROM
        application.bytedance_advertiser_report_daily_dw t
        WHERE
        t.stat_datetime &gt;= #{startDate}
        AND t.stat_datetime &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id
        ORDER BY SUM(cost) DESC
        LIMIT 5
    </select>

    <select id="getAdqCostByTop5HourList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        user_id,
        SUM(cost) AS totalCost,
        ( SELECT realname FROM `jeecg-boot`.sys_user u WHERE u.id = t.user_id ) AS userName
        FROM application.adq_advertiser_report_hourly t
        WHERE stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id
        ORDER BY SUM(cost) DESC
        LIMIT 5
    </select>

    <select id="getAdqCostByTop5DailyList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        user_id,
        SUM(cost) AS totalCost,
        ( SELECT realname FROM `jeecg-boot`.sys_user u WHERE u.id = t.user_id ) AS userName
        FROM application.adq_advertiser_report_daily t
        WHERE stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id
        ORDER BY SUM(cost) DESC
        LIMIT 5
    </select>


    <!-- 快手 时报总消耗 及 账户数量 -->
    <select id="getKuaiShouTotalCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( charge ), 3 ) AS totalCost,
        COUNT(DISTINCT account_id) accountNum
        FROM
        application.kuaishou_account_report_hourly_dw
        where 1 = 1
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>


    <!-- 头条 时报总消耗 及 账户数量 -->
    <select id="getBytedanceTotalCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( cost ), 3 ) AS totalCost,
        COUNT(DISTINCT account_id) accountNum
        FROM
        application.bytedance_advertiser_report_hourly_dw
        where 1 = 1
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAdqTotalCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(cost), 3) AS totalCost,
        COUNT(DISTINCT account_id) accountNum
        FROM application.adq_advertiser_report_hourly
        WHERE 1 = 1
        <if test="startDate != null and startDate != '' ">
            AND stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND stat_date &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND stat_hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>


    <!-- 快手 日报总消耗 及 账户数量 -->
    <select id="getKuaiShouTotalCostDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( charge ), 3 ) AS totalCost,
        COUNT(DISTINCT account_id) accountNum
        FROM
        application.kuaishou_account_report_daily_dw
        where charge > 0
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <!-- 头条 日报总消耗 及 账户数量 -->
    <select id="getBytedanceTotalCostDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( cost ), 3 ) AS totalCost,
        COUNT(DISTINCT account_id) accountNum
        FROM
        application.bytedance_advertiser_report_daily_dw
        where cost > 0
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getKuaiShouTodayYesterdayCostSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN stat_date = #{today} THEN charge ELSE 0 END), 3) AS todayCost,
        ROUND(SUM(CASE WHEN stat_date = #{yesterday} THEN charge ELSE 0 END), 3) AS yesterdayCost
        FROM application.kuaishou_account_report_hourly_dw
        WHERE charge > 0
        AND stat_date IN (#{today}, #{yesterday})
        AND hour &lt;= #{nowHour}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getBytedanceTodayYesterdayCostSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN stat_datetime = #{today} THEN cost ELSE 0 END), 3) AS todayCost,
        ROUND(SUM(CASE WHEN stat_datetime = #{yesterday} THEN cost ELSE 0 END), 3) AS yesterdayCost
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE cost > 0
        AND stat_datetime IN (#{today}, #{yesterday})
        AND hour &lt;= #{nowHour}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAdqTodayYesterdayCostSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN stat_date = #{today} THEN cost ELSE 0 END), 3) AS todayCost,
        ROUND(SUM(CASE WHEN stat_date = #{yesterday} THEN cost ELSE 0 END), 3) AS yesterdayCost
        FROM application.adq_advertiser_report_hourly
        WHERE cost > 0
        AND stat_date IN (#{today}, #{yesterday})
        AND stat_hour &lt;= #{nowHour}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getKuaiShouPeriodCostSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN stat_date &gt;= #{currentStart} AND stat_date &lt;= #{currentEnd} THEN charge ELSE 0 END), 3) AS currentCost,
        ROUND(SUM(CASE WHEN stat_date &gt;= #{lastStart} AND stat_date &lt;= #{lastEnd} THEN charge ELSE 0 END), 3) AS lastCost
        FROM application.kuaishou_account_report_daily_dw
        WHERE charge > 0
        AND ((stat_date &gt;= #{currentStart} AND stat_date &lt;= #{currentEnd})
        OR (stat_date &gt;= #{lastStart} AND stat_date &lt;= #{lastEnd}))
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getBytedancePeriodCostSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN stat_datetime &gt;= #{currentStart} AND stat_datetime &lt;= #{currentEnd} THEN cost ELSE 0 END), 3) AS currentCost,
        ROUND(SUM(CASE WHEN stat_datetime &gt;= #{lastStart} AND stat_datetime &lt;= #{lastEnd} THEN cost ELSE 0 END), 3) AS lastCost
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE cost > 0
        AND ((stat_datetime &gt;= #{currentStart} AND stat_datetime &lt;= #{currentEnd})
        OR (stat_datetime &gt;= #{lastStart} AND stat_datetime &lt;= #{lastEnd}))
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAdqPeriodCostSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN stat_date &gt;= #{currentStart} AND stat_date &lt;= #{currentEnd} THEN cost ELSE 0 END), 3) AS currentCost,
        ROUND(SUM(CASE WHEN stat_date &gt;= #{lastStart} AND stat_date &lt;= #{lastEnd} THEN cost ELSE 0 END), 3) AS lastCost
        FROM application.adq_advertiser_report_daily
        WHERE cost > 0
        AND ((stat_date &gt;= #{currentStart} AND stat_date &lt;= #{currentEnd})
        OR (stat_date &gt;= #{lastStart} AND stat_date &lt;= #{lastEnd}))
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getKuaiShouOperateSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( charge ), 3 ) AS totalCost,
        COUNT(DISTINCT account_id) AS accountNum,
        COUNT(DISTINCT project_id) AS projectNum,
        COUNT(DISTINCT user_id) AS userNum
        FROM
        application.kuaishou_account_report_daily_dw
        where charge > 0
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getKuaiShouOperateProjectUserSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        COUNT(DISTINCT project_id) AS projectNum,
        COUNT(DISTINCT user_id) AS userNum
        FROM
        application.kuaishou_account_report_hourly_dw
        WHERE charge > 0
        <if test="startDate != null and startDate != '' ">
            AND stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND stat_date &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getBytedanceOperateSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( cost ), 3 ) AS totalCost,
        COUNT(DISTINCT account_id) AS accountNum
        FROM
        application.bytedance_advertiser_report_daily_dw
        where cost > 0
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getBytedanceOperateProjectUserSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        COUNT(DISTINCT project_id) AS projectNum,
        COUNT(DISTINCT user_id) AS userNum
        FROM
        application.bytedance_advertiser_report_hourly_dw
        where cost > 0
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getOperateTodayYesterdaySummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(CASE WHEN ${dateField} = #{startDate} THEN ${costField} ELSE 0 END), 3) AS todayCost,
        ROUND(SUM(CASE WHEN ${dateField} = #{yesterday} THEN ${costField} ELSE 0 END), 3) AS yesterdayCost
        FROM ${tableName}
        WHERE ${costField} > 0
        AND ${dateField} &gt;= #{yesterday}
        AND ${dateField} &lt;= #{endDate}
        <if test="hourField != null and hourField != '' and nowHour != null">
            AND ${hourField} &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAdqTotalCostDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(SUM(cost), 3) AS totalCost,
        COUNT(DISTINCT account_id) accountNum
        FROM application.adq_advertiser_report_daily
        WHERE cost > 0
        <if test="startDate != null and startDate != '' ">
            AND stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAdqOperatorProjectNum" resultType="String">
        SELECT COUNT(DISTINCT project_id)
        FROM application.adq_advertiser_report_daily
        WHERE cost > 0
        <if test="startDate != null and startDate != '' ">
            AND stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND stat_date &lt;= #{endDate}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="getAdqOperatorProjectNumHourly" resultType="String">
        SELECT COUNT(DISTINCT project_id)
        FROM application.adq_advertiser_report_hourly
        WHERE cost > 0
        <if test="startDate != null and startDate != '' ">
            AND stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND stat_date &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND stat_hour &lt;= #{nowHour}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <!-- 查看 快手 有效 计划 组-->
    <select id="getKuaiShouAccountBaseByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT abi.account_id ,
        IFNULL(SUM(abi.new_effect_num),0) as newEffectNum,
        IFNULL(SUM(abi.new_campaign_num),0) as newCampaignNum,
        IFNULL(SUM(abi.new_unit_num),0) as newUnitNum,
        IFNULL(SUM( abi.new_video_num ),0) AS newVideoNum,
        IFNULL(SUM( abi.new_hot_num ),0) AS newHotNum,
        IFNULL(SUM( abi.trust_unit_num ),0) AS trustUnitNum
        from application.app_kuaishou_account_base_info_d abi
        WHERE 1 =1
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>

        <if test='list!=null and list.size()>0'>
            AND abi.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

        <if test="projectId != null and projectId != '' ">
            and abi.project_id = #{projectId}
        </if>
        <if test="accountId != null and accountId != '' ">
            and abi.account_id = #{accountId}
        </if>

    </select>
    <!-- 查看 头条 有效 计划 组-->
    <select id="getBytedanceAccountBaseByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT abi.account_id ,
        IFNULL(SUM(abi.new_effect_num),0) as newEffectNum,
        IFNULL(SUM(abi.new_campaign_num),0) as newCampaignNum,
        IFNULL((SUM(abi.new_ad_num) - SUM(abi.trust_campaign_num)),0) as trustUnitNum,
        IFNULL(SUM( abi.new_video_num ),0) AS newVideoNum,
        IFNULL(SUM( abi.new_hot_num ),0) AS newHotNum,
        IFNULL(SUM( abi.new_cpd_project_num ),0) AS newCpdProjectNum,
        IFNULL(SUM( abi.new_ad_num ),0) AS newUnitNum,
        IFNULL(SUM( abi.promotion_num ),0) AS newUnitNum2
        from application.app_bytedance_account_base_info_d abi,
        `jeecg-boot`.ctop_user_allocation u
        WHERE abi.account_id = u.account_id
        and u.account_status = 0
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>

        <if test='list!=null and list.size()>0'>
            AND abi.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

        <if test="projectId != null and projectId != '' ">
            and abi.project_id = #{projectId}
        </if>
        <if test="accountId != null and accountId != '' ">
            and abi.account_id = #{accountId}
        </if>

    </select>

    <select id="getKuaiShouAccountBaseGroupByUserIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT abi.user_id AS userId,
        IFNULL(SUM(abi.new_effect_num),0) as newEffectNum,
        IFNULL(SUM(abi.new_campaign_num),0) as newCampaignNum,
        IFNULL(SUM(abi.new_unit_num),0) as newUnitNum,
        IFNULL(SUM( abi.new_video_num ),0) AS newVideoNum,
        IFNULL(SUM( abi.new_hot_num ),0) AS newHotNum,
        IFNULL(SUM( abi.trust_unit_num ),0) AS trustUnitNum
        from application.app_kuaishou_account_base_info_d abi
        WHERE 1 =1
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND abi.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY abi.user_id
    </select>

    <select id="getBytedanceAccountBaseGroupByUserIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT abi.user_id AS userId,
        IFNULL(SUM(abi.new_effect_num),0) as newEffectNum,
        IFNULL(SUM(abi.new_campaign_num),0) as newCampaignNum,
        IFNULL((SUM(abi.new_ad_num) - SUM(abi.trust_campaign_num)),0) as trustUnitNum,
        IFNULL(SUM( abi.new_video_num ),0) AS newVideoNum,
        IFNULL(SUM( abi.new_hot_num ),0) AS newHotNum,
        IFNULL(SUM( abi.new_cpd_project_num ),0) AS newCpdProjectNum,
        IFNULL(SUM( abi.new_ad_num ),0) AS newUnitNum,
        IFNULL(SUM( abi.promotion_num ),0) AS newUnitNum2
        from application.app_bytedance_account_base_info_d abi,
        `jeecg-boot`.ctop_user_allocation u
        WHERE abi.account_id = u.account_id
        and u.account_status = 0
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND abi.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY abi.user_id
    </select>

    <!-- 查看 快手 当天 有效 计划 组-->
    <select id="getKuaiShouAccountNowBaseByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT abi.account_id ,
        IFNULL((SUM(abi.new_all_unit_num) - SUM(abi.new_trush_unit_num)),0) as newUnitNum,
        IFNULL(SUM(abi.new_trush_unit_num),0) as trustUnitNum
        from application.app_kuaishou_account_today_base_info_d abi,
        `jeecg-boot`.ctop_user_allocation u
        WHERE abi.account_id = u.account_id
        and u.account_status = 0
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>

        <if test='list!=null and list.size()>0'>
            AND abi.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

        <if test="projectId != null and projectId != '' ">
            and abi.project_id = #{projectId}
        </if>
        <if test="accountId != null and accountId != '' ">
            and abi.account_id = #{accountId}
        </if>

    </select>

    <select id="getKuaiShouAccountNowBaseGroupByUserIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT abi.user_id AS userId,
        IFNULL((SUM(abi.new_all_unit_num) - SUM(abi.new_trush_unit_num)),0) as newUnitNum,
        IFNULL(SUM(abi.new_trush_unit_num),0) as trustUnitNum
        from application.app_kuaishou_account_today_base_info_d abi,
        `jeecg-boot`.ctop_user_allocation u
        WHERE abi.account_id = u.account_id
        and u.account_status = 0
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size()>0'>
            AND abi.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY abi.user_id
    </select>

    <!-- 查看 上传到助手、媒体的素材数量 -->
    <select id="getKuaiShouMaterailUploadType" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( SUM( material_num ), 0 ) 'num',
        'adspNum'
        FROM
        application.new_material_num_in_project
        <where>
            <if test="projectId != null and projectId != '' ">
                and project_id = #{projectId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
        </where>
        UNION
        SELECT
        IFNULL(SUM( material_upload_media_num ),0) 'num',
        'mediaNum'
        FROM
        application.today_material_upload_num
        WHERE
        type_id = 1
        <if test="projectId != null and projectId != '' ">
            and dimension = #{projectId}
        </if>
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>

    </select>


    <!-- 查看 快手 分版位数据 -->
    <select id="getKuaiShouAdSceneData" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(SUM(charge),0) 'adSceneCost',
        ad_scene as adSceneName
        FROM
        application.app_kuaishou_account_ad_scene_report_d
        <where>
            <if test="projectId != null and projectId != '' ">
                and project_id = #{projectId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
        </where>
        GROUP BY ad_scene


    </select>


    <!-- 快手 快手-查询账户数据信息 -->
    <select id="getKuaiShouOperateInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">
        SELECT
        SUM( d.charge ) totalCost,
        COUNT(DISTINCT d.account_id) accountNum,
        COUNT(DISTINCT d.project_id) projectNum,
        d.user_id as userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = d.user_id) as userName
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ORDER BY SUM( d.charge ) DESC
    </select>

    <!-- 快手or头条-查询账户数据信息 -->
    <select id="getKuaiShouOrBytedanceOperateInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">

        SELECT
        SUM( t.totalCost ) totalCost,
        SUM( t.accountNum ) accountNum,
        SUM( t.projectNum ) projectNum,
        t.userId userId,
        t.userName userName from (
        SELECT
        SUM( d.charge ) totalCost,
        COUNT(DISTINCT d.account_id) accountNum,
        COUNT(DISTINCT d.project_id) projectNum,
        d.user_id as userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = d.user_id) as userName
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        UNION
        SELECT
        SUM( d.cost ) totalCost,
        COUNT(DISTINCT d.account_id) accountNum,
        COUNT(DISTINCT d.project_id) projectNum,
        d.user_id as userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = d.user_id) as userName
        FROM
        application.bytedance_advertiser_report_hourly_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ) t
        GROUP BY t.userId
        ORDER BY t.totalCost DESC
    </select>

    <!-- 快手 快手-查询账户数据信息 -->
    <select id="getBytedanceOperateInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">
        SELECT
        SUM( d.cost ) totalCost,
        COUNT(DISTINCT d.account_id) accountNum,
        COUNT(DISTINCT d.project_id) projectNum,
        d.user_id as userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = d.user_id) as userName
        FROM
        application.bytedance_advertiser_report_daily_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ORDER BY SUM( d.cost ) DESC
    </select>

    <select id="getAdqOperateInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">
        SELECT
        ROUND(SUM(t.cost), 3) AS totalCost,
        COUNT(DISTINCT t.account_id) AS accountNum,
        COUNT(DISTINCT t.project_id) AS projectNum,
        t.user_id AS userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = t.user_id) as userName
        FROM
        application.adq_advertiser_report_daily t
        WHERE
        t.cost > 0
        <if test="startDate != null and startDate != '' ">
            and t.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.user_id
        ORDER BY SUM( t.cost ) DESC
    </select>


    <!-- 快手 快手-查询账户数据信息 -->
    <select id="getKuaiShouOperateInfoHourly" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">
        SELECT
        SUM( d.charge ) totalCost,
        COUNT(DISTINCT d.account_id) accountNum,
        COUNT(DISTINCT d.project_id) projectNum,
        d.user_id as userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = d.user_id) as userName
        FROM
        application.kuaishou_account_report_hourly_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND d.hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ORDER BY SUM( d.charge ) DESC
    </select>
    <!-- 头条 -查询账户数据信息 -->
    <select id="getBytedanceOperateInfoHourly" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">
        SELECT
        SUM( d.cost ) totalCost,
        COUNT(DISTINCT d.account_id) accountNum,
        COUNT(DISTINCT d.project_id) projectNum,
        d.user_id as userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = d.user_id) as userName
        FROM
        application.bytedance_advertiser_report_hourly_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND d.hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ORDER BY SUM( d.cost ) DESC
    </select>

    <select id="getAdqOperateInfoHourly" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateVo">
        SELECT
        ROUND(SUM(t.cost), 3) AS totalCost,
        COUNT(DISTINCT t.account_id) AS accountNum,
        COUNT(DISTINCT t.project_id) AS projectNum,
        t.user_id AS userId,
        (SELECT u.realname from `jeecg-boot`.sys_user u where u.id = t.user_id) as userName
        FROM
        application.adq_advertiser_report_hourly t
        WHERE
        t.cost > 0
        <if test="startDate != null and startDate != '' ">
            and t.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t.stat_date &lt;= #{endDate}
        </if>
        <if test="nowHour != null and nowHour != '' ">
            AND t.stat_hour &lt;= #{nowHour}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.user_id
        ORDER BY SUM( t.cost ) DESC
    </select>

    <!-- 快手 -查询项目数据信息 日报-->
    <select id="getKuaiShouOperateProjectInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateProjectVo">
        SELECT
        SUM( d.charge ) AS totalCost,
        COUNT( DISTINCT d.account_id ) AS accountNum,
        d.project_id AS projectId,
        d.project_name AS projectName,
        d.user_id AS userId,
        COUNT( DISTINCT d.user_id ) AS userNum,
        IFNULL(SUM( d.activation ),0) AS activation,
        IF(SUM( d.charge ) / SUM( d.activation ) > 0.0001,ROUND( SUM( d.charge ) / SUM( d.activation ), 2 ),0) AS
        activationPrice,
        IF(SUM( d.event_next_day_stay ) / SUM( d.activation ) > 0.0001,CONCAT(ROUND(SUM( d.event_next_day_stay ) / SUM(
        d.activation ) * 100, 2 ),'%'),0.00)AS 'leave',
        IFNULL(SUM( d.event_pay ),0) AS eventPay,
        IF(SUM( d.charge ) / SUM( d.event_pay ) > 0.0001,ROUND( SUM( d.charge ) / SUM( d.event_pay ), 2 ),0) AS
        eventPayCost,
        IF(SUM( d.event_pay ) / ( SUM( d.activation ) + SUM( d.form_count )) > 0.0001,CONCAT(ROUND( SUM( d.event_pay ) /
        ( SUM( d.activation ) + SUM( d.form_count )) * 100, 2 ),'%'),0.00) AS eventPayRate,
        IFNULL(SUM( d.event_new_user_pay ),0) AS eventNewUserPay,
        IF(SUM( d.charge ) / SUM( d.event_new_user_pay) > 0.0001,ROUND( SUM( d.charge ) / SUM( d.event_new_user_pay ), 2
        ),0)AS eventNewUserPayCost,
        IF(SUM( d.event_new_user_pay ) / ( SUM( d.activation ) + SUM( d.form_count )) > 0.0001 ,CONCAT(ROUND( SUM(
        d.event_new_user_pay ) / ( SUM( d.activation ) + SUM( d.form_count )) * 100, 2 ),'%'),0.00 ) AS
        eventNewUserPayRate,
        IFNULL(SUM( d.event_app_invoked ),0) AS eventAppInvoked,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_app_invoked ), 2 ),0) AS eventAppInvokedCost,
        IFNULL(SUM( d.event_register ),0) AS eventRegister,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_register ), 2 ),0) AS eventRegisterCost,
        IFNULL(SUM( d.key_action ),0) AS keyAction,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.key_action ), 2 ),0) AS keyActionCost,
        IF(SUM( d.key_action )/ SUM( d.activation ) > 0.0001,CONCAT( ROUND(SUM( d.key_action )/ SUM( d.activation ) *
        100 , 2 ), '%' ),0.00) AS keyActionRate,
        IFNULL(SUM( d.event_pay_purchase_amount_first_day ),0) AS eventPayPurchaseAmountFirstDay,
        IFNULL(SUM( d.event_pay_first_day_roi ),0) AS eventPayFirstDayRoi,
        IFNULL(SUM( d.form_count ),0) AS formCount,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.form_count ), 2 ),0) AS formCountPrice
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.project_id
        ORDER BY SUM( d.charge ) DESC
    </select>

    <!-- 快手 -查询项目数据信息 日报-->
    <select id="getBytedanceOperateProjectInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateProjectVo">
        SELECT
        COUNT( DISTINCT d.account_id ) AS accountNum,
        COUNT( DISTINCT d.user_id ) AS userNum,
        d.project_id AS projectId,
        d.project_name AS projectName,
        SUM( d.cost ) AS totalCost,
        t2.inventory,
        ( SELECT realname FROM `jeecg-boot`.sys_user u WHERE u.id = d.user_id ) AS userName
        FROM
        application.bytedance_advertiser_report_daily_dw d
        LEFT JOIN (select project_id,
        group_concat(concat(inventory_name,':',cost)) as 'inventory'
        from (
        select project_id,inventory_name,sum(cost) as cost from
        application.bytedance_advertiser_inventory_report_daily_dw
        where cost > 0
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

        group by project_id, inventory_name
        ORDER BY cost desc
        )t
        group by project_id
        ) t2 on d.project_id = t2.project_id WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.project_id
        ORDER BY SUM( d.cost ) DESC
    </select>

    <!-- 快手 -查询项目数据信息 时报-->
    <select id="getKuaiShouOperateProjectInfoHour"
            resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateProjectVo">
        SELECT
        SUM( d.charge ) AS totalCost
        FROM
        application.kuaishou_account_report_hourly_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND d.`hour` &lt;= #{hour}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        d.project_id
        ORDER BY
        SUM( d.charge ) DESC
    </select>

    <!-- 头条 -查询项目数据信息 时报-->
    <select id="getBytedanceOperateProjectInfoHour"
            resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateProjectVo">
        SELECT
        SUM( d.cost ) AS totalCost
        FROM
        application.bytedance_advertiser_report_hourly_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND d.`hour` &lt;= #{hour}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        d.project_id
        ORDER BY
        SUM( d.cost ) DESC
    </select>


    <!-- 快手 -查询 账户 数据信息  -->
    <select id="getKuaiShouOperateAccountIdPage" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        d.account_id as accountId,
        ROUND(SUM(d.charge), 3) AS totalCost
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY SUM(d.charge) DESC
    </select>

    <select id="getBytedanceOperateAccountIdPage" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        d.account_id as accountId,
        ROUND(SUM(d.cost), 3) AS totalCost
        FROM
        application.bytedance_advertiser_report_daily_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY SUM(d.cost) DESC
    </select>

    <select id="getAdqOperateAccountIdPage" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        t.account_id AS accountId,
        ROUND(SUM(t.cost), 3) AS totalCost
        FROM application.adq_advertiser_report_daily t
        WHERE t.cost > 0
        <if test="startDate != null and startDate != '' ">
            AND t.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND t.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="projectId != null and projectId != '' ">
            AND t.project_id = #{projectId}
        </if>
        GROUP BY t.account_id
        ORDER BY SUM(t.cost) DESC
    </select>

    <!-- 快手 -查询 账户 数据信息  -->
    <select id="getKuaiShouOperateAccountInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        SUM( d.charge ) AS totalCost,
        d.account_id as accountId,
        d.account_name as accountName,
        d.project_id AS projectId,
        d.project_name AS projectName,
        d.user_id AS userId,
        COUNT( DISTINCT d.user_id ) AS userNum,
        ( SELECT u.realname FROM `jeecg-boot`.sys_user u WHERE u.id = d.user_id ) AS userName,
        IFNULL(SUM( d.activation ),0) AS activation,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.activation ), 2 ),0) AS activationPrice,
        IF(SUM( d.event_next_day_stay ) / SUM( d.activation ) > 0.0001,CONCAT(ROUND(SUM( d.event_next_day_stay ) / SUM(
        d.activation ) * 100, 2 ),'%'),0.00) AS 'leave',
        IFNULL(SUM( d.event_pay ),0) AS eventPay,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_pay ), 2 ),0) AS eventPayCost,
        IF(SUM( d.event_pay ) / ( SUM( d.activation ) + SUM( d.form_count )) > 0.0001,CONCAT(ROUND( SUM( d.event_pay ) /
        ( SUM( d.activation ) + SUM( d.form_count )) * 100, 2 ),'%'),0.00) AS eventPayRate,
        IFNULL(SUM( d.event_new_user_pay ),0) AS eventNewUserPay,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_new_user_pay ), 2 ),0) AS eventNewUserPayCost,
        IFNULL(ROUND( SUM( d.event_new_user_pay ) / ( SUM( d.activation ) + SUM( d.form_count )), 2 ),0) AS
        eventNewUserPayRate,
        IFNULL(SUM( d.event_app_invoked ),0) AS eventAppInvoked,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_app_invoked ), 2 ),0) AS eventAppInvokedCost,
        IFNULL(SUM( d.event_register ),0) AS eventRegister,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_register ), 2 ),0) AS eventRegisterCost,
        IFNULL(SUM( d.key_action ),0) AS keyAction,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.key_action ), 2 ),0) AS keyActionCost,
        IF(SUM( d.key_action )/ SUM( d.activation ) > 0.0001,CONCAT( ROUND(SUM( d.key_action )/ SUM( d.activation ) *
        100 , 2 ), '%' ),0.00) AS keyActionRate,
        IFNULL(SUM( d.event_pay_purchase_amount_first_day ),0) AS eventPayPurchaseAmountFirstDay,
        IFNULL(SUM( d.event_pay_first_day_roi ),0) AS eventPayFirstDayRoi,
        IFNULL(SUM( d.form_count ),0) AS formCount,
        IFNULL(ROUND( SUM( d.charge ) / SUM( d.form_count ), 2 ),0) AS formCountPrice,
        IFNULL(SUM(d.`show`),0) as photoShow,
        IFNULL(SUM(d.photo_click),0) as photoClick
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test="accountId != null and accountId != '' ">
            and d.account_id = #{accountId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY SUM( d.charge ) DESC
    </select>


    <!-- 快手 -查询 账户 数据信息  -->
    <select id="getBytedanceOperateAccountInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        SUM( d.cost ) AS totalCost,
        d.account_id as accountId,
        d.account_name as accountName,
        d.project_id AS projectId,
        d.project_name AS projectName,
        d.user_id AS userId,
        COUNT( DISTINCT d.user_id ) AS userNum,
        ( SELECT u.realname FROM `jeecg-boot`.sys_user u WHERE u.id = d.user_id ) AS userName,
        (SELECT u.warning_amount FROM `jeecg-boot`.ctop_user_allocation u where u.account_id = d.account_id) as
        warningAmount,
        IFNULL(SUM( d.active ),0) AS active,
        IFNULL(ROUND( SUM( d.cost ) / SUM( d.active ), 2 ),0) AS activationPrice,
        IF(SUM( d.attribution_next_day_open_cnt ) / SUM( d.active ) > 0.0001,CONCAT(ROUND(SUM( d.attribution_next_day_open_cnt ) / SUM(
        d.active ) * 100, 2 ),'%'),0.00) AS 'leave',
        IFNULL(SUM( d.game_pay_count ),0) AS eventPay,
        IFNULL(ROUND( SUM( d.cost ) / SUM( d.game_pay_count ), 2 ),0) AS eventPayCost,
        IF(SUM( d.game_pay_count ) / ( SUM( d.active ) + SUM( d.form )) > 0.0001,CONCAT(ROUND( SUM( d.game_pay_count ) /
        ( SUM( d.active ) + SUM( d.form )) * 100, 2 ),'%'),0.00) AS eventPayRate,
        IFNULL(SUM( d.pay_count ),0) AS eventNewUserPay,
        IFNULL(ROUND( SUM( d.cost ) / SUM( d.pay_count ), 2 ),0) AS eventNewUserPayCost,
        IFNULL(ROUND( SUM( d.pay_count ) / ( SUM( d.active ) + SUM( d.form )), 2 ),0) AS
        eventNewUserPayRate,

        IFNULL(SUM( d.register ),0) AS eventRegister,
        IFNULL(ROUND( SUM( d.cost ) / SUM( d.register ), 2 ),0) AS eventRegisterCost,
        IFNULL(SUM( d.game_addiction ),0) AS keyAction,
        IFNULL(ROUND( SUM( d.cost ) / SUM( d.game_addiction ), 2 ),0) AS keyActionCost,
        IF(SUM( d.game_addiction )/ SUM( d.active ) > 0.0001,CONCAT( ROUND(SUM( d.game_addiction )/ SUM( d.active ) *
        100 , 2 ), '%' ),0.00) AS keyActionRate,
        IFNULL(SUM( d.stat_pay_amount ),0) AS eventPayPurchaseAmountFirstDay,
        IFNULL(ROUND(SUM( d.stat_pay_amount )/SUM( d.cost ),2),0) AS eventPayFirstDayRoi,
        IFNULL(SUM( d.form ),0) AS formCount,
        IFNULL(ROUND( SUM( d.cost ) / SUM( d.form ), 2 ),0) AS formCountPrice,
        IFNULL(SUM(d.`show`),0) as photoShow,
        IFNULL(SUM(d.`click`),0) as photoClick
        FROM
        application.bytedance_advertiser_report_daily_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test="accountId != null and accountId != '' ">
            and d.account_id = #{accountId}
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY SUM( d.cost ) DESC
    </select>

    <select id="getAdqOperateAccountInfo" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        t.account_id AS accountId,
        CAST(t.account_id AS CHAR) AS accountName,
        ROUND(SUM(t.cost), 3) AS totalCost,
        CAST(t.project_id AS CHAR) AS projectId,
        MAX(t.project_name) AS projectName,
        MAX(u.realname) AS userName,
        MAX(t.user_id) AS userId,
        COUNT(DISTINCT t.user_id) AS userNum
        FROM application.adq_advertiser_report_daily t
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = t.user_id
        WHERE t.cost > 0
        <if test="startDate != null and startDate != '' ">
            AND t.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND t.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="projectId != null and projectId != '' ">
            AND t.project_id = #{projectId}
        </if>
        <if test="accountId != null and accountId != '' ">
            AND t.account_id = #{accountId}
        </if>
        GROUP BY t.account_id
        ORDER BY SUM(t.cost) DESC
    </select>

    <select id="getKuaiShouOperateAccountPeriodBatch" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        d.account_id AS accountId,
        MAX(d.account_name) AS accountName,
        ROUND(SUM(CASE WHEN d.stat_date &gt;= #{startDate} AND d.stat_date &lt;= #{endDate} THEN d.charge ELSE 0 END), 3) AS totalCost,
        ROUND(SUM(CASE WHEN d.stat_date &gt;= #{lastTimeStart} AND d.stat_date &lt;= #{lastTimeEnd} THEN d.charge ELSE 0 END), 3) AS lastTotalCost,
        ROUND(SUM(CASE WHEN d.stat_date &gt;= #{startEqually} AND d.stat_date &lt;= #{endEqually} THEN d.charge ELSE 0 END), 3) AS equallyTotalCost,
        CAST(MAX(CASE WHEN d.stat_date &gt;= #{startDate} AND d.stat_date &lt;= #{endDate} THEN d.project_id END) AS CHAR) AS projectId,
        MAX(CASE WHEN d.stat_date &gt;= #{startDate} AND d.stat_date &lt;= #{endDate} THEN d.project_name END) AS projectName,
        MAX(CASE WHEN d.stat_date &gt;= #{startDate} AND d.stat_date &lt;= #{endDate} THEN u.realname END) AS userName,
        MAX(CASE WHEN d.stat_date &gt;= #{startDate} AND d.stat_date &lt;= #{endDate} THEN d.user_id END) AS userId
        FROM application.kuaishou_account_report_daily_dw d
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = d.user_id
        WHERE d.charge > 0
        AND d.stat_date &gt;= #{queryStartDate}
        AND d.stat_date &lt;= #{queryEndDate}
        <if test="projectId != null and projectId != '' ">
            AND d.project_id = #{projectId}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND d.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY totalCost DESC
    </select>

    <select id="getBytedanceOperateAccountPeriodBatch" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        d.account_id AS accountId,
        MAX(d.account_name) AS accountName,
        ROUND(SUM(CASE WHEN d.stat_datetime &gt;= #{startDate} AND d.stat_datetime &lt;= #{endDate} THEN d.cost ELSE 0 END), 3) AS totalCost,
        ROUND(SUM(CASE WHEN d.stat_datetime &gt;= #{lastTimeStart} AND d.stat_datetime &lt;= #{lastTimeEnd} THEN d.cost ELSE 0 END), 3) AS lastTotalCost,
        ROUND(SUM(CASE WHEN d.stat_datetime &gt;= #{startEqually} AND d.stat_datetime &lt;= #{endEqually} THEN d.cost ELSE 0 END), 3) AS equallyTotalCost,
        CAST(MAX(CASE WHEN d.stat_datetime &gt;= #{startDate} AND d.stat_datetime &lt;= #{endDate} THEN d.project_id END) AS CHAR) AS projectId,
        MAX(CASE WHEN d.stat_datetime &gt;= #{startDate} AND d.stat_datetime &lt;= #{endDate} THEN d.project_name END) AS projectName,
        MAX(CASE WHEN d.stat_datetime &gt;= #{startDate} AND d.stat_datetime &lt;= #{endDate} THEN u.realname END) AS userName,
        MAX(CASE WHEN d.stat_datetime &gt;= #{startDate} AND d.stat_datetime &lt;= #{endDate} THEN d.user_id END) AS userId
        FROM application.bytedance_advertiser_report_daily_dw d
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = d.user_id
        WHERE d.cost > 0
        AND d.stat_datetime &gt;= #{queryStartDate}
        AND d.stat_datetime &lt;= #{queryEndDate}
        <if test="projectId != null and projectId != '' ">
            AND d.project_id = #{projectId}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND d.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY totalCost DESC
    </select>

    <select id="getAdqOperateAccountPeriodBatch" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        t.account_id AS accountId,
        CAST(t.account_id AS CHAR) AS accountName,
        ROUND(SUM(CASE WHEN t.stat_date &gt;= #{startDate} AND t.stat_date &lt;= #{endDate} THEN t.cost ELSE 0 END), 3) AS totalCost,
        ROUND(SUM(CASE WHEN t.stat_date &gt;= #{lastTimeStart} AND t.stat_date &lt;= #{lastTimeEnd} THEN t.cost ELSE 0 END), 3) AS lastTotalCost,
        ROUND(SUM(CASE WHEN t.stat_date &gt;= #{startEqually} AND t.stat_date &lt;= #{endEqually} THEN t.cost ELSE 0 END), 3) AS equallyTotalCost,
        CAST(MAX(CASE WHEN t.stat_date &gt;= #{startDate} AND t.stat_date &lt;= #{endDate} THEN t.project_id END) AS CHAR) AS projectId,
        MAX(CASE WHEN t.stat_date &gt;= #{startDate} AND t.stat_date &lt;= #{endDate} THEN t.project_name END) AS projectName,
        MAX(CASE WHEN t.stat_date &gt;= #{startDate} AND t.stat_date &lt;= #{endDate} THEN u.realname END) AS userName,
        MAX(CASE WHEN t.stat_date &gt;= #{startDate} AND t.stat_date &lt;= #{endDate} THEN t.user_id END) AS userId
        FROM application.adq_advertiser_report_daily t
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = t.user_id
        WHERE t.cost > 0
        AND t.stat_date &gt;= #{queryStartDate}
        AND t.stat_date &lt;= #{queryEndDate}
        <if test="projectId != null and projectId != '' ">
            AND t.project_id = #{projectId}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND t.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.account_id
        ORDER BY totalCost DESC
    </select>

    <select id="getKuaiShouOperateAccountInfoBatch" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        d.account_id AS accountId,
        MAX(d.account_name) AS accountName,
        ROUND(SUM(d.charge), 3) AS totalCost,
        CAST(d.project_id AS CHAR) AS projectId,
        MAX(d.project_name) AS projectName,
        MAX(u.realname) AS userName,
        MAX(d.user_id) AS userId
        FROM application.kuaishou_account_report_daily_dw d
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = d.user_id
        WHERE d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND d.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY SUM(d.charge) DESC
    </select>

    <select id="getBytedanceOperateAccountInfoBatch" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        d.account_id AS accountId,
        MAX(d.account_name) AS accountName,
        ROUND(SUM(d.cost), 3) AS totalCost,
        CAST(d.project_id AS CHAR) AS projectId,
        MAX(d.project_name) AS projectName,
        MAX(u.realname) AS userName,
        MAX(d.user_id) AS userId
        FROM application.bytedance_advertiser_report_daily_dw d
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = d.user_id
        WHERE d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        <if test="projectId != null and projectId != '' ">
            and d.project_id = #{projectId}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND d.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.account_id
        ORDER BY SUM(d.cost) DESC
    </select>

    <select id="getAdqOperateAccountInfoBatch" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateAccountVo">
        SELECT
        t.account_id AS accountId,
        CAST(t.account_id AS CHAR) AS accountName,
        ROUND(SUM(t.cost), 3) AS totalCost,
        CAST(t.project_id AS CHAR) AS projectId,
        MAX(t.project_name) AS projectName,
        MAX(u.realname) AS userName,
        MAX(t.user_id) AS userId
        FROM application.adq_advertiser_report_daily t
        LEFT JOIN `jeecg-boot`.sys_user u ON u.id = t.user_id
        WHERE t.cost > 0
        <if test="startDate != null and startDate != '' ">
            AND t.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            AND t.stat_date &lt;= #{endDate}
        </if>
        <if test='list!=null and list.size() > 0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="projectId != null and projectId != '' ">
            AND t.project_id = #{projectId}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND t.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.account_id
        ORDER BY SUM(t.cost) DESC
    </select>

    <select id="getKuaiShouAccountBaseByAccountIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        abi.account_id AS accountId,
        IFNULL(SUM(abi.new_effect_num),0) as newEffectNum,
        IFNULL(SUM( abi.new_video_num ),0) AS newVideoNum,
        IFNULL(SUM( abi.new_hot_num ),0) AS newHotNum
        FROM application.app_kuaishou_account_base_info_d abi
        WHERE 1 = 1
        <if test="startDate != null and startDate != '' ">
            and abi.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and abi.stat_date &lt;= #{endDate}
        </if>
        <if test='accountIds!=null and accountIds.size()>0'>
            AND abi.account_id IN
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY abi.account_id
    </select>


    <!-- 快手 账户 余额 -->
    <select id="getKuaiShouAccountBalance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT advertiser_id as accountId,
               balance
        FROM media_api.kuaishou_advertiser_fund_get
        where advertiser_id = #{accountId}

    </select>


    <!-- 快手 账户 余额 以及状态 -->
    <select id="getKuaiShouAccountTotalCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT u.account_id                                 as accountId,
               u.auth_name                                  as accountName,
               u.account_status                             as accountStatus,
               IFNULL((SELECT SUM(h.charge)
                       from application.kuaishou_account_report_hourly_dw h
                       where u.account_id = h.account_id
                         and h.stat_date = #{statDate}), 0) as todayCost
        FROM `jeecg-boot`.`ctop_user_allocation` u
        WHERE u.account_id = #{accountId}
    </select>


    <!-- 快手-运营 月总消耗 -->
    <select id="getKuaiShouAccountCostMonth" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        SUM(d.cost) cost
        FROM
        application.bytedance_advertiser_report_daily_dw d
        <where>
            <if test="userId != null and userId != '' ">
                and d.user_id = #{userId}
            </if>
            <if test="statDate != null and statDate != '' ">
                and d.stat_datetime &gt;= #{statDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and d.stat_datetime &lt;= #{endDate}
            </if>
        </where>
    </select>

    <!-- bytedance-运营 月总消耗 -->
    <select id="getBytedanceAccountCostMonth" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        SUM( d.charge ) cost
        FROM
        application.kuaishou_account_report_daily_dw d
        <where>
            <if test="userId != null and userId != '' ">
                and d.user_id = #{userId}
            </if>
            <if test="statDate != null and statDate != '' ">
                and d.stat_date &gt;= #{statDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and d.stat_date &lt;= #{endDate}
            </if>

        </where>

    </select>


    <!-- 用户 -->
    <select id="getKuaiShouUserByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT realname as userName
        FROM `jeecg-boot`.sys_user
        WHERE id = #{userId}
    </select>


    <!-- 快手 账户趋势图-时报 -->
    <select id="getKuaiShouAccountInfoBrokenLineHour" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.*,y.*
        FROM
        (
        SELECT
        h.`hour` as tHour,
        <if test='type == "cost"'>
            h.charge AS tData,
        </if>
        <if test='type == "activation"'>
            IFNULL(h.activation,0) AS tData ,
        </if>
        <if test='type == "activationPrice"'>
            IFNULL(ROUND( (h.charge / h.activation) , 2 ),0) AS tData,
        </if>
        <if test='type == "leave"'>
            IF(( h.event_next_day_stay / h.activation ) > 0.0001,ROUND(( h.event_next_day_stay / h.activation ) * 100, 2
            ),0) AS tData,
        </if>
        <if test='type == "eventPay"'>
            IFNULL( h.event_pay ,0) AS tData,
        </if>
        <if test='type == "eventPayCost"'>
            IFNULL(ROUND( (h.charge / h.event_pay ), 2 ),0) AS tData,
        </if>
        <if test='type == "eventPayRate"'>
            IF(( h.event_pay / (h.activation + h.form_count) ) > 0.0001,ROUND( ( h.event_pay / (h.activation +
            h.form_count) ) * 100, 2 ),0) AS tData,
        </if>
        <if test='type == "eventNewUserPay"'>
            IFNULL( h.event_new_user_pay ,0) AS tData,
        </if>
        <if test='type == "eventNewUserPayCost"'>
            IFNULL(ROUND( ( h.charge / h.event_new_user_pay ), 2 ),0) AS tData,
        </if>
        <if test='type == "eventNewUserPayRate"'>
            IFNULL(ROUND( ( h.event_new_user_pay / (h.activation + h.form_count) ) * 100, 2 ),0) AS tData,
        </if>
        <if test='type == "eventAppInvoked"'>
            IFNULL( h.event_app_invoked ,0) AS tData,
        </if>
        <if test='type == "eventAppInvokedCost"'>
            IFNULL(ROUND( ( h.charge / h.event_app_invoked ), 2 ),0) AS tData,
        </if>
        <if test='type == "eventRegister"'>
            IFNULL( h.event_register ,0) AS tData,
        </if>
        <if test='type == "eventRegisterCost"'>
            IFNULL(ROUND( ( h.charge / h.event_register) , 2 ),0) AS tData,
        </if>
        <if test='type == "keyAction"'>
            IFNULL( h.key_action ,0) AS tData,
        </if>
        <if test='type == "keyActionCost"'>
            IFNULL(ROUND( ( h.charge / h.key_action ), 2 ),0) AS tData,
        </if>
        <if test='type == "keyActionRate"'>
            IF(( h.key_action / h.activation ) > 0.0001,CONCAT( ( h.key_action / h.activation ) * 100 , 2 ),0) AS tData,
        </if>
        <if test='type == "eventPayPurchaseAmountFirstDay"'>
            IFNULL( h.event_pay_purchase_amount_first_day ,0) AS tData,
        </if>
        <if test='type == "eventPayFirstDayRoi"'>
            IFNULL( h.event_pay_first_day_roi ,0) AS tData,
        </if>
        <if test='type == "formCount"'>
            IFNULL( h.form_count ,0) AS tData,
        </if>
        <if test='type == "formCountPrice"'>
            IFNULL(ROUND( ( h.charge / h.form_count ), 2 ),0) AS tData,
        </if>
        <if test='type == "photoShow"'>
            IFNULL(h.`show`,0) as tData,
        </if>
        <if test='type == "photoClick"'>
            IFNULL(h.photo_click,0) as tData,
        </if>
        h.account_id as tAccountId

        FROM
        application.kuaishou_account_report_hourly_dw h
        <where>
            <if test="accountId != null and accountId != '' ">
                and h.account_id = #{accountId}
            </if>
            <if test="statDate != null and statDate != '' ">
                and h.stat_date = #{statDate}
            </if>
        </where>
        ORDER BY
        h.hour) t
        RIGHT JOIN
        (
        SELECT
        h.`hour` as yHour,
        <if test='type == "cost"'>
            h.charge AS yData,
        </if>
        <if test='type == "activation"'>
            IFNULL(h.activation,0) AS yData ,
        </if>
        <if test='type == "activationPrice"'>
            IFNULL(ROUND( (h.charge / h.activation) , 2 ),0) AS yData,
        </if>
        <if test='type == "leave"'>
            IF(( h.event_next_day_stay / h.activation ) > 0.0001,ROUND(( h.event_next_day_stay / h.activation ) * 100, 2
            ),0) AS yData,
        </if>
        <if test='type == "eventPay"'>
            IFNULL( h.event_pay ,0) AS yData,
        </if>
        <if test='type == "eventPayCost"'>
            IFNULL(ROUND( (h.charge / h.event_pay ), 2 ),0) AS yData,
        </if>
        <if test='type == "eventPayRate"'>
            IF(( h.event_pay / (h.activation + h.form_count) ) > 0.0001,ROUND( ( h.event_pay / (h.activation +
            h.form_count) ) * 100, 2 ),0) AS yData,
        </if>
        <if test='type == "eventNewUserPay"'>
            IFNULL( h.event_new_user_pay ,0) AS yData,
        </if>
        <if test='type == "eventNewUserPayCost"'>
            IFNULL(ROUND( ( h.charge / h.event_new_user_pay ), 2 ),0) AS yData,
        </if>
        <if test='type == "eventNewUserPayRate"'>
            IFNULL(ROUND( ( h.event_new_user_pay / (h.activation + h.form_count) ) * 100, 2 ),0) AS yData,
        </if>
        <if test='type == "eventAppInvoked"'>
            IFNULL( h.event_app_invoked ,0) AS yData,
        </if>
        <if test='type == "eventAppInvokedCost"'>
            IFNULL(ROUND( ( h.charge / h.event_app_invoked ), 2 ),0) AS yData,
        </if>
        <if test='type == "eventRegister"'>
            IFNULL( h.event_register ,0) AS yData,
        </if>
        <if test='type == "eventRegisterCost"'>
            IFNULL(ROUND( ( h.charge / h.event_register) , 2 ),0) AS yData,
        </if>
        <if test='type == "keyAction"'>
            IFNULL( h.key_action ,0) AS yData,
        </if>
        <if test='type == "keyActionCost"'>
            IFNULL(ROUND( ( h.charge / h.key_action ), 2 ),0) AS yData,
        </if>
        <if test='type == "keyActionRate"'>
            IF(( h.key_action / h.activation ) > 0.0001, ROUND(( h.key_action / h.activation ) * 100 , 2 ),0) AS yData,
        </if>
        <if test='type == "eventPayPurchaseAmountFirstDay"'>
            IFNULL( h.event_pay_purchase_amount_first_day ,0) AS yData,
        </if>
        <if test='type == "eventPayFirstDayRoi"'>
            IFNULL( h.event_pay_first_day_roi ,0) AS yData,
        </if>
        <if test='type == "formCount"'>
            IFNULL( h.form_count ,0) AS yData,
        </if>
        <if test='type == "formCountPrice"'>
            IFNULL(ROUND( ( h.charge / h.form_count ), 2 ),0) AS yData,
        </if>
        <if test='type == "photoShow"'>
            IFNULL(h.`show`,0) as yData,
        </if>
        <if test='type == "photoClick"'>
            IFNULL(h.photo_click,0) as yData,
        </if>
        h.account_id as yAccountId
        FROM
        application.kuaishou_account_report_hourly_dw h
        <where>
            <if test="accountId != null and accountId != '' ">
                and h.account_id = #{accountId}
            </if>
            <if test="endDate != null and endDate != '' ">
                and h.stat_date = #{endDate}
            </if>
        </where>
        ORDER BY
        h.HOUR) y
        on t.tHour = y.yHour
    </select>


    <!-- 快手 账户趋势图-日报 -->
    <select id="getKuaiShouAccountInfoBrokenLineDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        DATE_FORMAT(d.stat_date,'%Y-%m-%d') as dateTime,
        <if test='type == "cost"'>
            SUM( d.charge ) as data,
        </if>
        <if test='type == "activation"'>
            IFNULL(SUM( d.activation ),0) AS data,
        </if>
        <if test='type == "activationPrice"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.activation ), 2 ),0) AS data,
        </if>
        <if test='type == "leave"'>
            IF(SUM( d.event_next_day_stay ) / SUM( d.activation ) > 0.0001,ROUND(SUM( d.event_next_day_stay ) / SUM(
            d.activation ) * 100, 2 ),0) AS data,
        </if>
        <if test='type == "eventPay"'>
            IFNULL(SUM( d.event_pay ),0) AS data,
        </if>
        <if test='type == "eventPayCost"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_pay ), 2 ),0) AS data,
        </if>
        <if test='type == "eventPayRate"'>
            IF(SUM( d.event_pay ) / ( SUM( d.activation ) + SUM( d.form_count )) > 0.0001,ROUND( SUM( d.event_pay ) / (
            SUM( d.activation ) + SUM( d.form_count )) * 100, 2 ),0) AS data,
        </if>
        <if test='type == "eventNewUserPay"'>
            IFNULL(SUM( d.event_new_user_pay ),0) AS data,
        </if>
        <if test='type == "eventNewUserPayCost"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_new_user_pay ), 2 ),0) AS data,
        </if>
        <if test='type == "eventNewUserPayRate"'>
            IFNULL(ROUND( (SUM( d.event_new_user_pay ) / ( SUM( d.activation ) + SUM( d.form_count ))) * 100, 2 ),0) AS
            data,
        </if>
        <if test='type == "eventAppInvoked"'>
            IFNULL(SUM( d.event_app_invoked ),0) AS data,
        </if>
        <if test='type == "eventAppInvokedCost"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_app_invoked ), 2 ),0) AS data,
        </if>
        <if test='type == "eventRegister"'>
            IFNULL(SUM( d.event_register ),0) AS data,
        </if>
        <if test='type == "eventRegisterCost"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.event_register ), 2 ),0) AS data,
        </if>
        <if test='type == "keyAction"'>
            IFNULL(SUM( d.key_action ),0) AS data,
        </if>
        <if test='type == "keyActionCost"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.key_action ), 2 ),0) AS data,
        </if>
        <if test='type == "keyActionRate"'>
            IF(SUM( d.key_action )/ SUM( d.activation ) > 0.0001, ROUND(SUM( d.key_action )/ SUM( d.activation ) * 100 ,
            2 ),0) AS data,
        </if>
        <if test='type == "eventPayPurchaseAmountFirstDay"'>
            IFNULL(SUM( d.event_pay_purchase_amount_first_day ),0) AS data,
        </if>
        <if test='type == "eventPayFirstDayRoi"'>
            IFNULL(SUM( d.event_pay_first_day_roi ),0) AS data,
        </if>
        <if test='type == "formCount"'>
            IFNULL(SUM( d.form_count ),0) AS data,
        </if>
        <if test='type == "formCountPrice"'>
            IFNULL(ROUND( SUM( d.charge ) / SUM( d.form_count ), 2 ),0) AS data,
        </if>
        <if test='type == "photoShow"'>
            IFNULL(SUM(d.`show`),0) as data
        </if>
        d.account_id as accountId
        FROM
        application.kuaishou_account_report_daily_dw d
        <where>
            <if test="accountId != null and accountId != '' ">
                and d.account_id = #{accountId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and d.stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and d.stat_date &lt;= #{endDate}
            </if>
        </where>
        GROUP BY
        d.stat_date
        ORDER BY
        d.stat_date
    </select>

    <!-- 快手 账户 素材上新-有效-爆款   searchType： 0-全部 1-上新 2-有效 3-爆款-->
    <select id="getKuaiShouAccountPieScene" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouAccountPieVo">
        SELECT
        <if test='type == "cost"'>
            SUM( d.charge ) AS cost,
        </if>
        <if test='type == "activation"'>
            SUM( d.activation ) AS activation,
        </if>
        <if test='type == "activationPrice"'>
            IFNULL( ROUND( SUM( d.charge ) / SUM( d.activation ), 2 ), 0 ) AS activationPrice,
        </if>
        <if test='type == "eventPayRoi"'>
            SUM( d.event_pay_roi ) AS eventPayRoi,
        </if>
        <if test='type == "eventPay"'>
            SUM( d.event_pay ) AS eventPay,
        </if>
        <if test='type == "eventPayCost"'>
            IFNULL( ROUND( SUM( d.charge ) / SUM( d.event_pay ), 2 ), 0 ) AS eventPayCost,
        </if>
        <if test='type == "eventPayPurchaseAmountFirstDay"'>
            SUM( d.event_pay_purchase_amount_first_day ) AS eventPayPurchaseAmountFirstDay,
        </if>
        <if test='type == "eventNextDayStay"'>
            SUM( d.event_next_day_stay ) AS eventNextDayStay,
        </if>
        <if test='type == "leave"'>
            IFNULL( ROUND( SUM( d.event_next_day_stay ) / SUM( d.activation ), 2 ), 0 ) AS 'leave',
        </if>
        d.ad_scene AS adScene
        FROM
        application.app_kuaishou_account_ad_scene_report_d d
        <where>
            <if test="accountId != null and accountId != '' ">
                and d.account_id = #{accountId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and d.stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and d.stat_date &lt;= #{endDate}
            </if>
        </where>
        GROUP BY
        d.ad_scene
        ORDER BY
        d.ad_scene
    </select>


    <!-- 快手 账户饼图图-分版位 -->
    <select id="getKuaiShouAccountMaterialNewAndEffectAndFaddish" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        m.cover_url as coverUrl,
        DATE_FORMAT(m.media_time,'%Y-%m-%d') as dateTime,
        vd.signature,
        m.material_name as videoName,
        m.video_url as videoUrl,
        SUM( vd.charge ) AS totalCost,
        SUM( vd.activation ) AS activation,
        ROUND(( SUM( vd.charge ) / SUM( vd.activation ) ), 2 ) AS activationPrice,
        vd.stat_date AS statDate,
        m.media_time AS mediaTime,
        SUM(vd.show) as showNum
        FROM
        application.kuaishou_material_video_report_daily_dw vd,
        application.app_kuaishou_material_info m
        WHERE
        vd.signature = m.material_id
        AND m.media_time is not null
        AND vd.account_id = #{accountId}
        <if test="searchType == '0'.toString()">
            AND m.media_time &gt;= #{startDate}
            AND m.media_time &lt;= #{endDate}
        </if>
        <if test="searchType == '1'.toString()">
            and material_innovate = 2
        </if>
        <if test="searchType == '2'.toString()">
            AND m.effect_flag = 2
            AND m.effect_date &gt;= #{startDate}
            AND m.effect_date &lt;= #{endDate}
        </if>
        <if test="searchType == '3'.toString()">
            and m.faddish_flag = 2
            and m.faddish_date &gt;= #{startDate}
            AND m.faddish_date &lt;= #{endDate}
        </if>
        GROUP BY
        vd.signature
        ORDER BY
        m.media_time DESC, SUM( vd.charge ) DESC
    </select>


    <!-- 快手 账户饼图图-分年龄 -->
    <select id="getKuaiShouAccountPieAge" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouAccountPieVo">
        SELECT
        <if test='type == "cost"'>
            SUM( d.charge ) AS cost,
        </if>
        <if test='type == "activation"'>
            SUM( d.activation ) AS activation,
        </if>
        <if test='type == "activationPrice"'>
            IFNULL( ROUND( SUM( d.charge ) / SUM( d.activation ), 2 ), 0 ) AS activationPrice,
        </if>
        <if test='type == "eventPayRoi"'>
            SUM( d.event_pay_roi ) AS eventPayRoi,
        </if>
        <if test='type == "eventPay"'>
            SUM( d.event_pay ) AS eventPay,
        </if>
        <if test='type == "eventPayCost"'>
            IFNULL( ROUND( SUM( d.charge ) / SUM( d.event_pay ), 2 ), 0 ) AS eventPayCost,
        </if>
        <if test='type == "eventPayPurchaseAmountFirstDay"'>
            SUM( d.event_pay_purchase_amount_first_day ) AS eventPayPurchaseAmountFirstDay,
        </if>
        <if test='type == "eventNextDayStay"'>
            SUM( d.event_next_day_stay ) AS eventNextDayStay,
        </if>
        <if test='type == "leave"'>
            IFNULL( ROUND( SUM( d.event_next_day_stay ) / SUM( d.activation ), 2 ), 0 ) AS 'leave',
        </if>
        d.age_segment AS ageSegment
        FROM
        media_api.kuaishou_audience_age_report_daily d
        <where>
            <if test="accountId != null and accountId != '' ">
                and d.advertiser_id = #{accountId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and d.stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and d.stat_date &lt;= #{endDate}
            </if>
        </where>
        GROUP BY
        d.age_segment
        ORDER BY
        d.age_segment
    </select>


    <!-- 快手 账户饼图图-分性别 -->
    <select id="getKuaiShouAccountPieGender" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouAccountPieVo">
        SELECT
        <if test='type == "cost"'>
            SUM( d.charge ) AS cost,
        </if>
        <if test='type == "activation"'>
            SUM( d.activation ) AS activation,
        </if>
        <if test='type == "activationPrice"'>
            IFNULL( ROUND( SUM( d.charge ) / SUM( d.activation ), 2 ), 0 ) AS activationPrice,
        </if>
        <if test='type == "eventPayRoi"'>
            SUM( d.event_pay_roi ) AS eventPayRoi,
        </if>
        <if test='type == "eventPay"'>
            SUM( d.event_pay ) AS eventPay,
        </if>
        <if test='type == "eventPayCost"'>
            IFNULL( ROUND( SUM( d.charge ) / SUM( d.event_pay ), 2 ), 0 ) AS eventPayCost,
        </if>
        <if test='type == "eventPayPurchaseAmountFirstDay"'>
            SUM( d.event_pay_purchase_amount_first_day ) AS eventPayPurchaseAmountFirstDay,
        </if>
        <if test='type == "eventNextDayStay"'>
            SUM( d.event_next_day_stay ) AS eventNextDayStay,
        </if>
        <if test='type == "leave"'>
            IFNULL( ROUND( SUM( d.event_next_day_stay ) / SUM( d.activation ), 2 ), 0 ) AS 'leave',
        </if>
        d.gender AS gender
        FROM
        media_api.kuaishou_audience_gender_report_daily d
        <where>
            <if test="accountId != null and accountId != '' ">
                and d.advertiser_id = #{accountId}
            </if>
            <if test="startDate != null and startDate != '' ">
                and d.stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and d.stat_date &lt;= #{endDate}
            </if>
        </where>
        GROUP BY
        d.gender
        ORDER BY
        d.gender
    </select>


    <!-- 快手 账户 框返 激励 消耗 -->
    <select id="getKuaiShouAccountCharged" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND( SUM( total_charged_in_yuan ), 3 ) AS totalCost,
        ROUND( SUM( direct_rebate_real_charged_in_yuan ), 3 ) AS directCharged,
        ROUND( SUM( contract_rebate_real_charged_in_yuan ), 3 ) AS contractCharged
        FROM application.app_kuaishou_agent_report_d
        <where>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
            <if test='list!=null and list.size()>0'>
                AND user_id IN
                <foreach collection="list" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getKuaiShouPersonalRankSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT ranked.serialNumber,
               totals.totalNum
        FROM (
                 SELECT user_id,
                        ROW_NUMBER() OVER ( ORDER BY SUM( total_charged_in_yuan ) DESC ) AS serialNumber
                 FROM application.app_kuaishou_agent_report_d
                 <where>
                     <if test="startDate != null and startDate != '' ">
                         and stat_date &gt;= #{startDate}
                     </if>
                     <if test="endDate != null and endDate != '' ">
                         and stat_date &lt;= #{endDate}
                     </if>
                     <if test="companyId != null and companyId != '' ">
                         and company_id = #{companyId}
                     </if>
                 </where>
                 GROUP BY user_id
             ) ranked
                 CROSS JOIN (
            SELECT COUNT(DISTINCT user_id) AS totalNum
            FROM application.app_kuaishou_agent_report_d
            <where>
                <if test="startDate != null and startDate != '' ">
                    and stat_date &gt;= #{startDate}
                </if>
                <if test="endDate != null and endDate != '' ">
                    and stat_date &lt;= #{endDate}
                </if>
                <if test="companyId != null and companyId != '' ">
                    and company_id = #{companyId}
                </if>
            </where>
        ) totals
        WHERE ranked.user_id = #{userId}
    </select>

    <!-- 快手 运营 组内消耗 排名 -->
    <select id="getKuaiShouPersonalGroupRank" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROW_NUMBER() OVER ( ORDER BY SUM( total_charged_in_yuan ) DESC ) AS serialNumber,
        ROUND( SUM( total_charged_in_yuan ), 3 ) AS totalCost,
        user_id,
        user_name
        FROM
        application.app_kuaishou_agent_report_d
        <where>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
            <if test="companyId != null and companyId != '' ">
                and company_id = #{companyId}
            </if>
        </where>
        GROUP BY user_id
        ORDER BY totalCost DESC
    </select>


    <!-- bytedance 运营 组内消耗 排名 -->
    <select id="getBytedancePersonalRankSummary" resultType="com.alibaba.fastjson.JSONObject">
        SELECT ranked.serialNumber,
               totals.totalNum
        FROM (
                 SELECT user_id,
                        ROW_NUMBER() OVER ( ORDER BY SUM(cost) DESC ) AS serialNumber
                 FROM application.app_bytedance_agent_report_d
                 <where>
                     <if test="startDate != null and startDate != '' ">
                         and stat_datetime &gt;= #{startDate}
                     </if>
                     <if test="endDate != null and endDate != '' ">
                         and stat_datetime &lt;= #{endDate}
                     </if>
                     <if test="companyId != null and companyId != '' ">
                         and company_id = #{companyId}
                     </if>
                 </where>
                 GROUP BY user_id
             ) ranked
                 CROSS JOIN (
            SELECT COUNT(DISTINCT user_id) AS totalNum
            FROM application.app_bytedance_agent_report_d
            <where>
                <if test="startDate != null and startDate != '' ">
                    and stat_datetime &gt;= #{startDate}
                </if>
                <if test="endDate != null and endDate != '' ">
                    and stat_datetime &lt;= #{endDate}
                </if>
                <if test="companyId != null and companyId != '' ">
                    and company_id = #{companyId}
                </if>
            </where>
        ) totals
        WHERE ranked.user_id = #{userId}
    </select>

    <!-- bytedance 运营 组内消耗 排名 -->
    <select id="getBytedancePersonalGroupRank" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROW_NUMBER() OVER ( ORDER BY SUM(cost) DESC ) AS serialNumber,
        ROUND( SUM(cost), 3 ) AS totalCost,
        user_id
        FROM
        application.app_bytedance_agent_report_d
        <where>
            <if test="startDate != null and startDate != '' ">
                and stat_datetime &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_datetime &lt;= #{endDate}
            </if>
            <if test="companyId != null and companyId != '' ">
                and company_id = #{companyId}
            </if>
        </where>
        GROUP BY user_id
        ORDER BY totalCost DESC
    </select>


    <!-- 快手 同公司所有下级运营人员 -->
    <select id="getKuaiShouAllOperators" resultType="java.lang.String">
        SELECT DISTINCT
        d.user_id
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        AND d.user_id in
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>

    <!-- 头条 同公司所有下级运营人员 -->
    <select id="getBytedanceAllOperators" resultType="java.lang.String">
        SELECT DISTINCT
        d.user_id
        FROM
        application.bytedance_advertiser_report_hourly_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        AND d.user_id in
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>


    <!-- 快手 项目数量 -->
    <select id="getKuaiShouOperatorProjectNum" resultType="java.lang.String">
        SELECT
        COUNT(DISTINCT d.project_id) as projectNum
        FROM
        application.kuaishou_account_report_daily_dw d
        WHERE
        d.charge > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_date &lt;= #{endDate}
        </if>
        AND d.user_id in
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>


    <!-- 头条 项目数量 -->
    <select id="getBytedanceOperatorProjectNum" resultType="java.lang.String">
        SELECT
        COUNT(DISTINCT d.project_id) as projectNum
        FROM
        application.bytedance_advertiser_report_hourly_dw d
        WHERE
        d.cost > 0
        <if test="startDate != null and startDate != '' ">
            and d.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and d.stat_datetime &lt;= #{endDate}
        </if>
        AND d.user_id in
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>


    <!-- 快手组实时数据查询-当天创建的组+当天有消耗的组 -->
    <select id="kuaiShouRealTimeUnitDateInfo" resultType="com.alibaba.fastjson.JSONObject">

        SELECT t.*,
               l.bid_type   bidType,
               l.bid,
               l.cpa_bid    cpaBid,
               l.put_status putStatus
        from (
                 SELECT IFNULL(a.advertiser_id, b.dAccountId) accountId,
                        IFNULL(a.uid, b.dunit_id)             unit_id,
                        a.*,
                        b.*
                 from (
                          SELECT ad.advertiser_id as advertiser_id,
                                 ad.unit_id          uid,
                                 ad.ocpx_action_type
                          FROM media_api.kuaishou_ad_unit_list ad
                          WHERE ad.advertiser_id = #{accountId}
                            AND DATE_FORMAT(ad.create_time, "%Y%m%d") = #{nowDate}
                      ) a
                          RIGHT JOIN
                      (
                          SELECT d.advertiser_id                                                   dAccountId,
                                 d.campaign_id                                                     campaignId,
                                 d.campaign_name                                                   campaignName,
                                 d.unit_id                                                         dunit_id,
                                 d.unit_name                                                       unitName,
                                 IFNULL(SUM(d.charge), 0)                                       AS charge,
                                 IFNULL(SUM(d.activation), 0)                                   AS activation,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.activation), 2), 0)         AS activationPrice,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.activation), 2), 0)         AS activationCost,
                                 IF(SUM(d.event_next_day_stay) / SUM(d.activation) > 0.0001,
                                    CONCAT(ROUND(SUM(d.event_next_day_stay) / SUM(d.activation) * 100, 2), '%'),
                                    0)                                                          AS `leave`,
                                 IFNULL(SUM(d.event_pay), 0)                                    AS eventPay,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.event_pay), 2), 0)          AS eventPayCost,
                                 IF(SUM(d.event_pay) / (SUM(d.activation) + SUM(d.form_count)) > 0.0001,
                                    CONCAT(ROUND(SUM(d.event_pay) / (SUM(d.activation) + SUM(d.form_count)) * 100, 2),
                                           '%'),
                                    0)                                                          AS eventPayRate,
                                 IFNULL(SUM(d.event_new_user_pay), 0)                           AS eventNewUserPay,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.event_new_user_pay), 2), 0) AS eventNewUserPayCost,
                                 IFNULL(ROUND(SUM(d.event_new_user_pay) / (SUM(d.activation) + SUM(d.form_count)), 2),
                                        0)                                                      AS eventNewUserPayRate,
                                 IFNULL(SUM(d.event_app_invoked), 0)                            AS eventAppInvoked,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.event_app_invoked), 2), 0)  AS eventAppInvokedCost,
                                 IFNULL(SUM(d.event_register), 0)                               AS eventRegister,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.event_register), 2), 0)     AS eventRegisterCost,
                                 IFNULL(SUM(d.key_action), 0)                                   AS keyAction,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.key_action), 2), 0)         AS keyActionCost,
                                 IF(SUM(d.key_action) / SUM(d.activation) > 0.0001,
                                    CONCAT(ROUND(SUM(d.key_action) / SUM(d.activation) * 100, 2), '%'),
                                    0)                                                          AS keyActionRate,
                                 IFNULL(SUM(d.event_pay_purchase_amount_first_day), 0)          AS eventPayPurchaseAmountFirstDay,
                                 IFNULL(SUM(d.event_pay_first_day_roi), 0)                      AS eventPayFirstDayRoi,
                                 IFNULL(SUM(d.form_count), 0)                                   AS formCount,
                                 IFNULL(ROUND(SUM(d.charge) / SUM(d.form_count), 2), 0)         AS formCountPrice,
                                 SUM(d.bclick)                                                     bClick
                          FROM media_api.kuaishou_unit_report_hourly d
                          WHERE d.advertiser_id = #{accountId}
                            AND d.stat_date = #{nowDate}
                          GROUP BY d.unit_id
                      ) b
                      on a.uid = b.dunit_id
             ) t
                 LEFT JOIN media_api.kuaishou_ad_unit_list l
                           on l.unit_id = t.unit_id
        WHERE l.advertiser_id = #{accountId}


    </select>


    <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="getCompanyAllUserId" resultType="java.lang.String">
        SELECT DISTINCT user_id
        FROM user_company
    </select>

    <select id="getUserCompanyByUserId" resultType="java.lang.String">
        SELECT company_id as companyId
        FROM user_company
        WHERE user_id = #{userId} LIMIT 1
    </select>

    <select id="getAffiliateId" resultType="java.lang.String">
        SELECT DISTINCT
        u.id
        FROM
        `jeecg-boot`.sys_user u,
        `jeecg-boot`.user_company c,
        `jeecg-boot`.sys_user_role r,
        `jeecg-boot`.sys_role sr
        WHERE
        u.id = c.user_id
        and c.user_id = r.user_id
        and r.role_id = sr.id
        AND sr.role_name LIKE '%运营%'
        <if test="leaderId != null and leaderId != '' ">
            AND u.leader_id = #{leaderId}
        </if>
        <if test="companyId != null and companyId != '' ">
            AND c.company_id = #{companyId}
        </if>
        AND u.STATUS = 1
        AND u.del_flag = 0
    </select>

    <select id="querySubordinateRecursive" resultType="java.lang.String">
        SELECT DISTINCT
        u.id
        FROM
        `jeecg-boot`.sys_user u,
        `jeecg-boot`.user_company c,
        `jeecg-boot`.sys_user_role r,
        `jeecg-boot`.sys_role sr
        WHERE
        u.id = c.user_id
        and c.user_id = r.user_id
        and r.role_id = sr.id
        AND sr.role_name LIKE '%运营%'
        AND u.STATUS = 1
        AND u.del_flag = 0
        <if test="companyId != null and companyId != '' ">
            AND c.company_id = #{companyId}
        </if>
        and u.leader_id in
        <foreach collection="leaderIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="getOperateSubordinateCountBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.manage_id AS managerId,
        COUNT(DISTINCT t1.user_id) AS subordinateCount
        FROM `application`.app_user_manage t1
                 LEFT JOIN `jeecg-boot`.sys_user t2 ON t1.user_id = t2.id
        WHERE t1.manage_id IN
        <foreach collection="managerIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        AND t1.type = 1
        AND t2.status = 1
        GROUP BY t1.manage_id
    </select>


    <select id="getProjectByUserIdAndMediaIdsSubordinate" resultType="com.alibaba.fastjson.JSONObject">
        select
        t2.id as 'projectId',
        t2.project_name as 'projectName',
        t2.product_id as 'productId',
        (select name from ctop_advertiser where id = t2.advertiser_id) as 'advertiserName'
        from ctop_project_member t1
        left join ctop_project t2
        on t1.project_id = t2.id
        where 1 = 1
        <if test="userIds != null and userIds.size() != 0 ">
            and t1.user_id in
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

        <if test="mediaIds != null and mediaIds.size() != 0 ">
            and t2.media_id in
            <foreach collection="mediaIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by t2.id
        order by t2.create_time desc
    </select>


    <select id="getAccountListByProjectListSubordinate" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.project_id as 'projectId',
        t1.account_id as 'accountId',
        (select realname from sys_user where id = t1.user_id ) as 'userName',
        t1.auth_name as 'authName',
        t1.account_status as 'accountStatus'
        from ctop_user_allocation t1
        where t1.account_status = 0

        <if test="list != null and list.size() != 0 ">
            and t1.project_id in
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>


        <if test="userIds != null and userIds.size() != 0 ">
            and t1.user_id in
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>

        order by t1.create_time desc
    </select>


    <select id="getColumnInfo" resultType="com.alibaba.fastjson.JSONObject">
        select json from ctop_column_load
        <where>
            <if test="userId != null and userId != '' ">
                AND user_id = #{userId}
            </if>
            <if test="columnType != null and columnType != '' ">
                AND column_type = #{columnType}
            </if>
        </where>
        ORDER BY create_time
        LIMIT 1
    </select>


    <!-- 查询运营经理角色的人员 -->
    <select id="getOperationManagerUser" resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateGroupVo">
        SELECT DISTINCT u.id        as userId,
                        u.username,
                        u.realname  as realname,
                        r.role_name as roleName
        FROM `jeecg-boot`.sys_user u
                 INNER JOIN `jeecg-boot`.sys_user_role sur ON u.id = sur.user_id
                 INNER JOIN `jeecg-boot`.sys_role r ON r.id = sur.role_id
        WHERE r.role_code like concat('%', #{roleCode}, '%')
          AND u.`status` = 1
          AND u.del_flag = 0

        ORDER BY u.create_time
    </select>


    <select id="queryKuaishouOperateTeamMember" resultType="String">
        select u.id
        from `jeecg-boot`.sys_user u
                 LEFT JOIN `application`.app_user_manage m ON u.id = m.user_id
                 LEFT JOIN `jeecg-boot`.sys_user_role ur ON u.id = ur.user_id
                 LEFT JOIN `jeecg-boot`.sys_role r ON ur.role_id = r.id
                 LEFT JOIN application.kuaishou_account_report_hourly_dw t ON t.user_id = u.id
        where m.manage_id = #{userId}
          AND r.role_name LIKE '%运营%'
          AND u.STATUS = 1
          AND u.del_flag = 0
          AND t.stat_date >= #{startDate}
          AND t.stat_date &lt;= #{endDate}
          AND t.charge > 0
        GROUP BY u.id
        UNION all
        SELECT DISTINCT user_id
        FROM application.kuaishou_account_report_hourly_dw t
        WHERE user_id = #{userId}
          AND t.stat_date >= #{startDate}
          AND t.stat_date &lt;= #{endDate}
          AND t.charge > 0
    </select>

    <select id="queryToutiaoOperateTeamMember" resultType="String">
        select u.id
        from `jeecg-boot`.sys_user u
                 LEFT JOIN `application`.app_user_manage m ON u.id = m.user_id
                 LEFT JOIN `jeecg-boot`.sys_user_role ur ON u.id = ur.user_id
                 LEFT JOIN `jeecg-boot`.sys_role r ON ur.role_id = r.id
                 LEFT JOIN application.bytedance_advertiser_report_hourly_dw t ON t.user_id = u.id
        where m.manage_id = #{userId}
          AND r.role_name LIKE '%运营%'
          AND u.STATUS = 1
          AND u.del_flag = 0
          AND t.stat_datetime >= #{startDate}
          AND t.stat_datetime &lt;= #{endDate}
          AND t.cost > 0
        GROUP BY u.id
        UNION ALL
        SELECT DISTINCT user_id
        FROM application.bytedance_advertiser_report_hourly_dw t
        WHERE user_id = #{userId}
          AND t.stat_datetime >= #{startDate}
          AND t.stat_datetime &lt;= #{endDate}
          AND t.cost > 0
    </select>

    <select id="queryAdqOperateTeamMember" resultType="String">
        select u.id
        from `jeecg-boot`.sys_user u
                 LEFT JOIN `application`.app_user_manage m ON u.id = m.user_id
                 LEFT JOIN `jeecg-boot`.sys_user_role ur ON u.id = ur.user_id
                 LEFT JOIN `jeecg-boot`.sys_role r ON ur.role_id = r.id
                 LEFT JOIN application.adq_advertiser_report_hourly t ON t.user_id = u.id
        where m.manage_id = #{userId}
          AND r.role_name LIKE '%运营%'
          AND u.STATUS = 1
          AND u.del_flag = 0
          AND t.stat_date >= #{startDate}
          AND t.stat_date &lt;= #{endDate}
          AND t.cost > 0
        GROUP BY u.id
        UNION ALL
        SELECT DISTINCT user_id
        FROM application.adq_advertiser_report_hourly t
        WHERE user_id = #{userId}
          AND t.stat_date >= #{startDate}
          AND t.stat_date &lt;= #{endDate}
          AND t.cost > 0
    </select>

    <select id="queryKuaishouOperateTeamMemberBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.managerId, t.userId
        FROM (
                 SELECT m.manage_id AS managerId, m.user_id AS userId
                 FROM `application`.app_user_manage m
                          INNER JOIN (
                     SELECT DISTINCT report.user_id
                     FROM application.kuaishou_account_report_hourly_dw report
                     WHERE report.stat_date >= #{startDate}
                       AND report.stat_date &lt;= #{endDate}
                       AND report.charge > 0
                 ) active_users ON active_users.user_id = m.user_id
                 WHERE m.manage_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND m.type = 1
                 UNION ALL
                 SELECT active_users.user_id AS managerId, active_users.user_id AS userId
                 FROM (
                          SELECT DISTINCT report.user_id
                          FROM application.kuaishou_account_report_hourly_dw report
                          WHERE report.stat_date >= #{startDate}
                            AND report.stat_date &lt;= #{endDate}
                            AND report.charge > 0
                      ) active_users
                 WHERE active_users.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
             ) t
        GROUP BY t.managerId, t.userId
    </select>

    <select id="queryToutiaoOperateTeamMemberBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.managerId, t.userId
        FROM (
                 SELECT m.manage_id AS managerId, m.user_id AS userId
                 FROM `application`.app_user_manage m
                          INNER JOIN (
                     SELECT DISTINCT report.user_id
                     FROM application.bytedance_advertiser_report_hourly_dw report
                     WHERE report.stat_datetime >= #{startDate}
                       AND report.stat_datetime &lt;= #{endDate}
                       AND report.cost > 0
                 ) active_users ON active_users.user_id = m.user_id
                 WHERE m.manage_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND m.type = 1
                 UNION ALL
                 SELECT active_users.user_id AS managerId, active_users.user_id AS userId
                 FROM (
                          SELECT DISTINCT report.user_id
                          FROM application.bytedance_advertiser_report_hourly_dw report
                          WHERE report.stat_datetime >= #{startDate}
                            AND report.stat_datetime &lt;= #{endDate}
                            AND report.cost > 0
                      ) active_users
                 WHERE active_users.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
             ) t
        GROUP BY t.managerId, t.userId
    </select>

    <select id="queryAdqOperateTeamMemberBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.managerId, t.userId
        FROM (
                 SELECT m.manage_id AS managerId, m.user_id AS userId
                 FROM `application`.app_user_manage m
                          INNER JOIN (
                     SELECT DISTINCT report.user_id
                     FROM application.adq_advertiser_report_hourly report
                     WHERE report.stat_date >= #{startDate}
                       AND report.stat_date &lt;= #{endDate}
                       AND report.cost > 0
                 ) active_users ON active_users.user_id = m.user_id
                 WHERE m.manage_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND m.type = 1
                 UNION ALL
                 SELECT active_users.user_id AS managerId, active_users.user_id AS userId
                 FROM (
                          SELECT DISTINCT report.user_id
                          FROM application.adq_advertiser_report_hourly report
                          WHERE report.stat_date >= #{startDate}
                            AND report.stat_date &lt;= #{endDate}
                            AND report.cost > 0
                      ) active_users
                 WHERE active_users.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
             ) t
        GROUP BY t.managerId, t.userId
    </select>

    <select id="getAllOperationByMediaId" resultType="String">
        SELECT DISTINCT u.id
        FROM `jeecg-boot`.sys_user u
                 INNER JOIN `jeecg-boot`.user_company c ON c.user_id = u.id
                 INNER JOIN `jeecg-boot`.sys_user_role ur ON u.id = ur.user_id
                 INNER JOIN `jeecg-boot`.sys_role r ON ur.role_id = r.id
                 INNER JOIN `jeecg-boot`.ctop_user_allocation ua ON u.id = ua.user_id
        WHERE r.role_name LIKE '%运营%'
          AND ua.media_id = #{mediaId}
          AND u.status = 1
          AND u.del_flag = 0
    </select>


    <!-- 所有下级运营人员 -->
    <select id="getAllOperatorsByLearderId" resultType="java.lang.String">
        SELECT user_id
        FROM application.`app_user_manage`
        WHERE manage_id = #{learderId}
          AND type = 1
        UNION ALL
        SELECT #{learderId}
    </select>


    <select id="getBytedanceOperateProjectInfoNew"
            resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateProjectVo">
        SELECT
        COUNT( DISTINCT t1.account_id ) AS 'accountNum',
        COUNT( DISTINCT t1.user_id ) AS 'userNum',
        t1.project_id AS 'projectId',
        t1.project_name AS 'projectName',
        SUM( t1.cost ) AS 'totalCost',
        t2.inventory,
        CONCAT(IFNULL(ROUND(((SUM( t1.cost )-t3.totalCost) / t3.totalCost) * 100,2),0),'%') AS 'yearOnYear',
        CONCAT(IFNULL(ROUND(((SUM( t1.cost )-t5.totalCost) / t5.totalCost) * 100,2),0),'%') AS 'equallyRate',
        IFNULL(t4.newEffectNum,0) AS 'newEffectNum',
        IFNULL(t4.newVideoNum,0) AS 'newVideoNum',
        IFNULL(t4.newHotNum,0) AS 'newHotNum',
        IFNULL(t4.newUnitNum,0) AS 'newUnitNum'
        FROM
        ${tableName} t1
        LEFT JOIN (
        SELECT project_id,
        group_concat(concat(inventory_name,':',cost)) as 'inventory'
        from (
        select project_id,inventory_name,sum(cost) as cost from
        application.bytedance_advertiser_inventory_report_daily_dw
        where cost > 0
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by project_id, inventory_name
        ORDER BY cost desc
        )t
        group by project_id
        ) t2 on t1.project_id = t2.project_id
        LEFT JOIN (
        SELECT
        project_id,
        SUM(cost) AS totalCost
        FROM
        ${tableName}
        WHERE cost > 0
        AND stat_datetime >= #{lastTimeStart}
        AND stat_datetime &lt;= #{lastTimeEnd}
        <if test="hour != null and hour != '' ">
            AND `hour` &lt;= #{hour}
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY project_id
        ) t3 ON t1.project_id = t3.project_id
        LEFT JOIN (
        SELECT tt1.project_id as 'pid' ,
        IFNULL(SUM(tt1.new_effect_num),0) as 'newEffectNum',
        IFNULL(SUM(tt1.new_video_num),0) AS 'newVideoNum',
        IFNULL(SUM(tt1.new_hot_num),0) AS 'newHotNum',
        IFNULL(SUM(tt1.new_ad_num),0)+IFNULL(SUM(tt1.promotion_num),0) AS 'newUnitNum'
        from application.app_bytedance_account_base_info_d tt1
        LEFT JOIN `jeecg-boot`.ctop_user_allocation tt2 ON tt1.account_id = tt2.account_id
        WHERE tt2.account_status = 0
        <if test="startDate != null and startDate != '' ">
            and tt1.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and tt1.stat_date &lt;= #{endDate}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND tt1.user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND tt1.project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY tt1.project_id
        ) t4 ON t1.project_id = t4.pid
        LEFT JOIN (
        SELECT
        project_id,
        SUM(cost) AS totalCost
        FROM
        ${tableName}
        WHERE cost > 0
        AND stat_datetime >= #{startEqually}
        AND stat_datetime &lt;= #{endEqually}
        <if test='projectIds!=null and projectIds.size()>0'>
            AND project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY project_id
        ) t5 ON t1.project_id = t5.project_id
        WHERE t1.cost > 0
        <if test="startDate != null and startDate != '' ">
            and t1.stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t1.stat_datetime &lt;= #{endDate}
        </if>
        <if test="hour != null and hour != '' ">
            AND `hour` &lt;= #{hour}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND t1.user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND t1.project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t1.project_id
        ORDER BY SUM(t1.cost) DESC
    </select>

    <select id="getBytedanceProjectList" resultType="Long">
        SELECT
        project_id
        FROM
        <choose>
            <when test="tableName != null and tableName != ''">
                ${tableName}
            </when>
            <otherwise>
                application.bytedance_advertiser_report_daily_dw
            </otherwise>
        </choose>
        WHERE cost > 0
        <if test="startDate != null and startDate != '' ">
            and stat_datetime &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_datetime &lt;= #{endDate}
        </if>
        <if test="hour != null and hour != '' ">
            AND `hour` &lt;= #{hour}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY project_id
        ORDER BY SUM(cost) DESC
    </select>

    <select id="getKuaishouProjectList" resultType="Long">
        SELECT
        project_id
        FROM
        <choose>
            <when test="tableName != null and tableName != ''">
                ${tableName}
            </when>
            <otherwise>
                application.kuaishou_account_report_daily_dw
            </otherwise>
        </choose>
        d
        WHERE charge > 0
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>
        <if test="hour != null and hour != '' ">
            AND `hour` &lt;= #{hour}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY project_id
        ORDER BY SUM(charge) DESC
    </select>


    <select id="getKuaiShouOperateProjectInfoNew"
            resultType="org.jeecg.ctop.material.entity.vo.KuaiShouOperateProjectVo">
        SELECT
        t1.project_name AS projectName,
        t1.project_id AS projectId,
        SUM(t1.charge) AS totalCost,
        COUNT(DISTINCT t1.account_id) AS accountNum,
        COUNT(DISTINCT t1.user_id) AS userNum,
        CONCAT(IFNULL(ROUND(((SUM(t1.charge )-t2.totalCost) / t2.totalCost) * 100,2),0),'%') AS 'yearOnYear',
        CONCAT(IFNULL(ROUND(((SUM(t1.charge )-t3.totalCost) / t3.totalCost) * 100,2),0),'%') AS 'equallyRate',
        IFNULL(newEffectNum,0) as 'newEffectNum',
        IFNULL(newUnitNum,0) as 'newUnitNum',
        IFNULL(newVideoNum,0) as 'newVideoNum',
        IFNULL(newHotNum,0) as 'newHotNum',
        IFNULL(trustUnitNum,0) as 'trustUnitNum',
        IFNULL(t5.adspNum,0) as 'adspNum',
        IFNULL(t6.mediaNum,0) as 'mediaNum',
        t7.adSceneDate as 'adSceneDateStr'
        FROM
        ${tableName} t1
        LEFT JOIN (
        SELECT
        project_id,
        SUM(charge) AS totalCost
        FROM
        ${tableName}
        WHERE charge > 0
        <if test="lastTimeStart != null and lastTimeStart != '' ">
            and stat_date &gt;= #{lastTimeStart}
        </if>
        <if test="lastTimeEnd != null and lastTimeEnd != '' ">
            and stat_date &lt;= #{lastTimeEnd}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="hour != null and hour != '' ">
            AND `hour` &lt;= #{hour}
        </if>
        GROUP BY project_id
        ) t2 ON t1.project_id = t2.project_id
        LEFT JOIN (
        SELECT
        project_id,
        SUM(charge) AS totalCost
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE charge > 0
        <if test="startEqually != null and startEqually != '' ">
            and stat_date &gt;= #{startEqually}
        </if>
        <if test="endEqually != null and endEqually != '' ">
            and stat_date &lt;= #{endEqually}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY project_id
        ) t3 ON t1.project_id = t3.project_id
        LEFT JOIN (
        SELECT project_id as 'pid',
        IFNULL(SUM(new_effect_num),0) as newEffectNum,
        IFNULL(SUM(new_unit_num),0) as newUnitNum,
        IFNULL(SUM(new_video_num),0) AS newVideoNum,
        IFNULL(SUM(new_hot_num),0) AS newHotNum,
        IFNULL(SUM(trust_unit_num),0) AS trustUnitNum
        from application.app_kuaishou_account_base_info_d
        <where>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
            <if test='userIds!=null and userIds.size()>0'>
                AND user_id IN
                <foreach collection="userIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='projectIds!=null and projectIds.size()>0'>
                AND project_id IN
                <foreach collection="projectIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY project_id
        )t4 ON t1.project_id = t4.pid
        LEFT JOIN (
        SELECT
        project_id,
        IFNULL( SUM( material_num ), 0 ) 'adspNum'
        FROM
        application.new_material_num_in_project
        <where>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
            <if test='projectIds!=null and projectIds.size()>0'>
                AND project_id IN
                <foreach collection="projectIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY project_id
        )t5 ON t1.project_id = t5.project_id
        LEFT JOIN (
        SELECT
        dimension,
        IFNULL( SUM( material_upload_media_num ), 0 ) 'mediaNum'
        FROM
        application.today_material_upload_num
        <where>
            <if test="startDate != null and startDate != '' ">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null and endDate != '' ">
                and stat_date &lt;= #{endDate}
            </if>
            <if test='projectIds!=null and projectIds.size()>0'>
                AND dimension IN
                <foreach collection="projectIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY dimension
        )t6 ON t1.project_id = t6.dimension
        LEFT JOIN (
        select project_id,CONCAT('[',group_concat(adSceneDate) ,']') as 'adSceneDate' from
        (
        SELECT
        project_id,
        json_object('adSceneCost',IFNULL(SUM(charge),0),'adSceneName',ad_scene) as 'adSceneDate'
        FROM
        application.app_kuaishou_account_ad_scene_report_d
        WHERE ad_scene!='全部'
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY project_id,ad_scene
        order by project_id
        )t
        group by project_id
        ) t7 ON t1.project_id = t7.project_id
        WHERE t1.charge > 0
        <if test="startDate != null and startDate != '' ">
            and t1.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t1.stat_date &lt;= #{endDate}
        </if>
        <if test='userIds!=null and userIds.size()>0'>
            AND t1.user_id IN
            <foreach collection="userIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test='projectIds!=null and projectIds.size()>0'>
            AND t1.project_id IN
            <foreach collection="projectIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="hour != null and hour != '' ">
            AND `hour` &lt;= #{hour}
        </if>
        GROUP BY t1.project_id
        ORDER BY SUM(t1.charge) DESC
    </select>

    <select id="getSaleDirctorItsTeamMember" resultType="String">
        select DISTINCT user_id as 'userId'
        from `application`.app_user_manage t1
                 LEFT JOIN sys_user t2 on t1.user_id = t2.id
        where manage_id = #{userId}
          and t1.type = 1
          and t2.status = 1
    </select>

    <select id="getOperatLeaderRole" resultType="String">
        SELECT t1.role_code
        FROM `jeecg-boot`.sys_role t1
                 LEFT JOIN `jeecg-boot`.sys_user_role t2 ON t1.id = t2.role_id
        WHERE t2.user_id = (
            SELECT leader_id
            FROM `jeecg-boot`.sys_user
            WHERE id = #{userId} LIMIT 1)
    </select>
    <select id="getUsersByLeaderId" resultType="java.lang.String">
        select id
        from `jeecg-boot`.sys_user
        where leader_id = #{userId}
          and status = 1
    </select>

</mapper>
