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

    <select id="getRoleCodeByUserId" resultType="java.lang.String" parameterType="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="getCompanyIdByUserId" resultType="java.lang.String" parameterType="java.lang.String">
        select company_id
        from `jeecg-boot`.user_company
        where user_id = #{userId} limit 1
    </select>
    <select id="getByteDanceTotalCharge" resultType="java.math.BigDecimal">
        select sum(cost) as 'totalCharge'
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_datetime >= #{start}
        and stat_datetime &lt;= #{end}
    </select>
    <select id="getByteDanceHelpInfo" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select count(1) as 'totalCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}) t1
        join
        (select count(1) as 'liveVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155) t2
        join
        (select count(1) as 'clipVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156) t3;
    </select>
    <select id="getByteDancePushMediaCount" resultType="java.lang.Integer">
        select count(1) as 'pushMediaCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
    </select>
    <select id="getByteDanceFirstChargeCount" resultType="java.lang.Integer">
        select count(1) as 'firstChargeCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
    </select>
    <select id="getByteDanceEffectCount" resultType="java.lang.Integer">
        select count(1) as 'effectCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
    </select>

    <select id="getKuaiShouTotalCharge" resultType="java.math.BigDecimal">
        select sum(charge) as 'totalCharge'
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_date >= #{start}
        and stat_date &lt;= #{end}
    </select>
    <select id="getKuaiShouHelpInfo" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from (select count(1) as 'totalCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}) t1
        join
        (select count(1) as 'liveVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155) t2
        join
        (select count(1) as 'clipVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156) t3;
    </select>
    <select id="getKuaiShouPushMediaCount" resultType="java.lang.Integer">
        select count(1) as 'pushMediaCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
    </select>
    <select id="getKuaiShouFirstChargeCount" resultType="java.lang.Integer">
        select count(1) as 'firstChargeCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
    </select>
    <select id="getKuaiShouEffectCount" resultType="java.lang.Integer">
        select count(1) as 'effectCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}

    </select>
    <select id="getByteDanceTotalChargeChart" resultType="com.alibaba.fastjson.JSONObject">
        select stat_datetime as 'date', sum(cost) as 'totalCharge'
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_datetime >= #{start}
        and stat_datetime &lt;= #{end}
        group by stat_datetime
        order by stat_datetime
    </select>
    <select id="getByteDanceHelpChart" resultType="com.alibaba.fastjson.JSONObject">
        select t1.help_time,t1.totalCount,t2.liveVideoCount,t3.clipVideoCount
        from (select help_time, count(1) as 'totalCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        group by help_time) t1
        left join
        (select help_time,count(1) as 'liveVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155 group by help_time) t2
        on t1.help_time = t2.help_time
        left join
        (select help_time,count(1) as 'clipVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156 group by help_time) t3
        on t1.help_time = t3.help_time order by t1.help_time asc
    </select>
    <select id="getByteDancePushMediaChart" resultType="com.alibaba.fastjson.JSONObject">
        select media_time as 'date',count(1) as 'pushMediaCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        group by media_time
        order by media_time asc
    </select>
    <select id="getByteDanceFirstChargeChart" resultType="com.alibaba.fastjson.JSONObject">
        select farst_cost_time as 'date', count(1) as 'firstChargeCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        group by farst_cost_time
        order by farst_cost_time asc

    </select>
    <select id="getByteDanceEffectChart" resultType="com.alibaba.fastjson.JSONObject">
        select media_effect_date as 'date',count(1) as 'effectCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        group by media_effect_date
        order by media_effect_date asc
    </select>


    <select id="getKuaiShouTotalChargeChart" resultType="com.alibaba.fastjson.JSONObject">
        select stat_date as 'date', sum(charge) as 'totalCharge'
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_date >= #{start}
        and stat_date &lt;= #{end}
        group by stat_date
        order by stat_date asc
    </select>
    <select id="getKuaiShouHelpChart" resultType="com.alibaba.fastjson.JSONObject">
        select t1.help_time,t1.totalCount,t2.liveVideoCount,t3.clipVideoCount
        from (select help_time, count(1) as 'totalCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        group by help_time) t1
        left join
        (select help_time,count(1) as 'liveVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155 group by help_time) t2
        on t1.help_time = t2.help_time
        left join
        (select help_time,count(1) as 'clipVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156 group by help_time) t3
        on t1.help_time = t3.help_time order by t1.help_time asc
    </select>
    <select id="getKuaiShouPushMediaChart" resultType="com.alibaba.fastjson.JSONObject">
        select media_time as 'date',count(1) as 'pushMediaCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        group by media_time
        order by media_time asc
    </select>
    <select id="getKuaiShouFirstChargeChart" resultType="com.alibaba.fastjson.JSONObject">
        select farst_cost_time as 'date', count(1) as 'firstChargeCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        group by farst_cost_time
        order by farst_cost_time asc

    </select>
    <select id="getKuaiShouEffectChart" resultType="com.alibaba.fastjson.JSONObject">
        select media_effect_date as 'date',count(1) as 'effectCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        group by media_effect_date
        order by media_effect_date asc
    </select>
    <select id="getByteDanceProjectData" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select t1.projectId,
        t1.projectName,
        IFNULL(t1.totalCount,0) as 'totalCount',
        IFNULL(t2.liveVideoCount,0) as 'liveVideoCount',
        IFNULL(t3.clipVideoCount,0) as 'clipVideoCount',
        IFNULL(t4.totalCharge,0) as 'totalCharge',
        IFNULL(t5.pushMediaCount,0) as 'pushMediaCount',
        IFNULL(t6.firstChargeCount,0) as 'firstChargeCount',
        IFNULL(t7.effectCount,0) as 'effectCount',
        IFNULL(t8.realPersonCost,0) as 'realPersonCost',
        IFNULL(ROUND(t8.realPersonCost / t9.realPersonCount, 2),0) AS 'realPersonAverageCost',
        IFNULL(t10.clipCost,0) as 'clipCost',
        IFNULL(ROUND(t10.clipCost / t11.clipCount, 2),0) AS 'clipAverageCost',
        IFNULL(ROUND(t4.totalCharge / t12.allCount, 2),0) AS 'totalAverageCost',
        IFNULL(t13.realInvalidCount,0) as 'realInvalidCount',
        IFNULL(t14.clipInvalidCount,0) as 'clipInvalidCount'
        from (select project_id as 'projectId',
        project_name as 'projectName',
        count(1) as 'totalCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        group by project_id) t1
        left join
        (select project_id as 'projectId',
        ifnull(count(1), 0) as 'liveVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155
        group by project_id) t2 on t1.projectId = t2.projectId
        left join
        (select project_id as 'projectId',
        ifnull(count(1), 0) as 'clipVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156
        group by project_id) t3 on t1.projectId = t3.projectId
        left join
        (select project_id as 'projectId',
        ifnull(sum(cost), 0) as 'totalCharge'
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_datetime >= #{start}
        and stat_datetime &lt;= #{end}
        group by project_id) t4 on t1.projectId = t4.projectId
        left join
        (select project_id as 'projectId',
        ifnull(count(1), 0) as 'pushMediaCount',
        sum(ifnull(material_ad_count, 0))as 'materialAdCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        group by project_id) t5 on t1.projectId = t5.projectId
        left join (select project_id as 'projectId',
        ifnull(count(1), 0) as 'firstChargeCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        group by project_id) t6 on t1.projectId = t6.projectId
        left join (select project_id as 'projectId',
        ifnull(count(1), 0) as 'effectCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        group by project_id) t7 on t1.projectId = t7.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        IFNULL(SUM(cost),0) as 'realPersonCost'
        FROM
        application.bytedance_material_video_report_daily_dw
        WHERE stat_datetime >= #{start}
        AND stat_datetime &lt;= #{end}
        AND signature in (
        SELECT material_id FROM application.app_bytedance_material_info
        WHERE tag_id = 60155
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        )
        GROUP BY project_id
        ) t8 ON t1.projectId = t8.projectId
        LEFT JOIN (
        SELECT project_id as 'projectId',
        ifnull(count(1), 0) as 'realPersonCount'
        FROM application.app_bytedance_material_info
        WHERE tag_id = 60155
        and farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t9 ON t1.projectId = t9.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        IFNULL(SUM(cost),0) as 'clipCost'
        FROM
        application.bytedance_material_video_report_daily_dw
        WHERE stat_datetime >= #{start}
        AND stat_datetime &lt;= #{end}
        AND signature in (
        SELECT material_id FROM application.app_bytedance_material_info
        WHERE tag_id = 60156
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        )
        GROUP BY project_id
        ) t10 ON t1.projectId = t10.projectId
        LEFT JOIN (
        SELECT project_id as 'projectId',
        ifnull(count(1), 0) as 'clipCount'
        FROM application.app_bytedance_material_info
        WHERE tag_id = 60156
        and farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t11 ON t1.projectId = t11.projectId
        LEFT JOIN (
        SELECT project_id as 'projectId',
        ifnull(count(1), 0) as 'allCount'
        FROM application.app_bytedance_material_info
        WHERE farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t12 ON t1.projectId = t12.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        ifnull(count(1), 0) as 'realInvalidCount'
        FROM
        application.app_bytedance_material_info
        WHERE tag_id = 60155
        AND media_effect_flag = 0
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t13 ON t1.projectId = t13.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        ifnull(count(1), 0) as 'clipInvalidCount'
        FROM
        application.app_bytedance_material_info
        WHERE tag_id = 60156
        AND media_effect_flag = 0
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t14 ON t1.projectId = t14.projectId
        where t1.projectId is not null
        )t
        order by t.totalCharge desc

    </select>
    <select id="getKuaiShouProjectData" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        t1.projectId,
        t1.projectName,
        IFNULL(t1.totalCount,0) as 'totalCount',
        IFNULL(t2.liveVideoCount,0) as 'liveVideoCount',
        IFNULL(t3.clipVideoCount,0) as 'clipVideoCount',
        IFNULL(t4.totalCharge,0) as 'totalCharge',
        IFNULL(t5.pushMediaCount,0) as 'pushMediaCount',
        IFNULL(t6.firstChargeCount,0) as 'firstChargeCount',
        IFNULL(t7.effectCount,0) as 'effectCount',
        IFNULL(t8.realPersonCost,0) as 'realPersonCost',
        IFNULL(ROUND(t8.realPersonCost / t9.realPersonCount, 2),0) AS 'realPersonAverageCost',
        IFNULL(t10.clipCost,0) as 'clipCost',
        IFNULL(ROUND(t10.clipCost / t11.clipCount, 2),0) AS 'clipAverageCost',
        IFNULL(ROUND(t4.totalCharge / t12.allCount, 2),0) AS 'totalAverageCost',
        IFNULL(t13.realInvalidCount,0) as 'realInvalidCount',
        IFNULL(t14.clipInvalidCount,0) as 'clipInvalidCount'
        from (select project_id as 'projectId',
        project_name as 'projectName',
        count(1) as 'totalCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        group by project_id) t1
        left join
        (select project_id as 'projectId',
        ifnull(count(1), 0) as 'liveVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155
        group by project_id) t2 on t1.projectId = t2.projectId
        left join
        (select project_id as 'projectId',
        ifnull(count(1), 0) as 'clipVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156
        group by project_id) t3 on t1.projectId = t3.projectId
        left join
        (select project_id as 'projectId',
        ifnull(sum(charge), 0) as 'totalCharge'
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_date >= #{start}
        and stat_date &lt;= #{end}
        group by project_id) t4 on t1.projectId = t4.projectId
        left join
        (select project_id as 'projectId',
        ifnull(count(1), 0) as 'pushMediaCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        group by project_id) t5 on t1.projectId = t5.projectId
        left join (select project_id as 'projectId',
        ifnull(count(1), 0) as 'firstChargeCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        group by project_id) t6 on t1.projectId = t6.projectId
        left join (select project_id as 'projectId',
        ifnull(count(1), 0) as 'effectCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        group by project_id) t7 on t1.projectId = t7.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        IFNULL(SUM(charge),0) as 'realPersonCost'
        FROM
        application.kuaishou_material_video_report_daily_dw
        WHERE stat_date >= #{start}
        AND stat_date &lt;= #{end}
        AND signature in (
        SELECT material_id FROM application.app_kuaishou_material_info
        WHERE tag_id = 60155
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        )
        GROUP BY project_id
        ) t8 ON t1.projectId = t8.projectId
        LEFT JOIN (
        SELECT project_id as 'projectId',
        ifnull(count(1), 0) as 'realPersonCount'
        FROM application.app_kuaishou_material_info
        WHERE tag_id = 60155
        and farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t9 ON t1.projectId = t9.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        IFNULL(SUM(charge),0) as 'clipCost'
        FROM
        application.kuaishou_material_video_report_daily_dw
        WHERE stat_date >= #{start}
        AND stat_date &lt;= #{end}
        AND signature in (
        SELECT material_id FROM application.app_kuaishou_material_info
        WHERE tag_id = 60156
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        )
        GROUP BY project_id
        ) t10 ON t1.projectId = t10.projectId
        LEFT JOIN (
        SELECT project_id as 'projectId',
        ifnull(count(1), 0) as 'clipCount'
        FROM application.app_kuaishou_material_info
        WHERE tag_id = 60156
        and farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t11 ON t1.projectId = t11.projectId
        LEFT JOIN (
        SELECT project_id as 'projectId',
        ifnull(count(1), 0) as 'allCount'
        FROM application.app_kuaishou_material_info
        WHERE farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t12 ON t1.projectId = t12.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        ifnull(count(1), 0) as 'realInvalidCount'
        FROM
        application.app_kuaishou_material_info
        WHERE tag_id = 60155
        AND media_effect_flag = 0
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t13 ON t1.projectId = t13.projectId
        LEFT JOIN (
        SELECT
        project_id as 'projectId',
        ifnull(count(1), 0) as 'clipInvalidCount'
        FROM
        application.app_kuaishou_material_info
        WHERE tag_id = 60156
        AND media_effect_flag = 0
        AND farst_cost_time >= #{start}
        AND farst_cost_time &lt;= #{end}
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        GROUP BY project_id
        ) t14 ON t1.projectId = t14.projectId
        where t1.projectId is not null
        ) t
        order by t.totalCharge desc
    </select>
    <select id="getByteDanceTeamData" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.companyName,
        t1.deptName,
        t1.leaderId,
        t1.leaderName,
        t1.totalCount,
        t2.liveVideoCount,
        t3.clipVideoCount,
        t4.totalCharge,
        t5.pushMediaCount,
        t6.firstChargeCount,
        t7.effectCount
        from (select leader_id as 'leaderId',
        leader_name as 'leaderName',
        (case when company_name like '%北京汇创%'
        then '汇创'
        when company_name like '%石家庄%'
        then '石家庄'
        when company_name like '%聚意%'
        then '聚意'
        when company_name like '%北京骄阳%'
        then '骄阳'
        else '-'
        end
        ) as 'companyName',
        dept_name as 'deptName',
        count(1) as 'totalCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        group by leader_id) t1
        left join
        (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'liveVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155
        group by leader_id) t2 on t1.leaderId = t2.leaderId
        left join
        (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'clipVideoCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156
        group by leader_id) t3 on t1.leaderId = t3.leaderId
        left join
        (select leader_id as 'leaderId',
        ifnull(sum(cost), 0) as 'totalCharge'
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        and stat_datetime >= #{start}
        and stat_datetime &lt;= #{end}
        group by leader_id) t4 on t1.leaderId = t4.leaderId
        left join
        (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'pushMediaCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        group by leader_id) t5 on t1.leaderId = t5.leaderId
        left join (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'firstChargeCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        group by leader_id) t6 on t1.leaderId = t6.leaderId
        left join (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'effectCount'
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        group by leader_id) t7 on t1.leaderId = t7.leaderId
        where t1.leaderId is not null
        order by t1.totalCount desc
    </select>
    <select id="getKuaiShouTeamData" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.companyName,
        t1.deptName,
        t1.leaderId,
        t1.leaderName,
        t1.totalCount,
        t2.liveVideoCount,
        t3.clipVideoCount,
        t4.totalCharge,
        t5.pushMediaCount,
        t6.firstChargeCount,
        t7.effectCount
        from (select leader_id as 'leaderId',
        leader_name as 'leaderName',
        (case when company_name like '%北京汇创%'
        then '汇创'
        when company_name like '%石家庄%'
        then '石家庄'
        when company_name like '%聚意%'
        then '聚意'
        when company_name like '%北京骄阳%'
        then '骄阳'
        else '-'
        end
        ) as 'companyName',
        dept_name as 'deptName',
        count(1) as 'totalCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        group by leader_id) t1
        left join
        (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'liveVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155
        group by leader_id) t2 on t1.leaderId = t2.leaderId
        left join
        (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'clipVideoCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156
        group by leader_id) t3 on t1.leaderId = t3.leaderId
        left join
        (select leader_id as 'leaderId',
        ifnull(sum(charge), 0) as 'totalCharge'
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and stat_date >= #{start}
        and stat_date &lt;= #{end}
        group by leader_id) t4 on t1.leaderId = t4.leaderId
        left join
        (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'pushMediaCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        group by leader_id) t5 on t1.leaderId = t5.leaderId
        left join (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'firstChargeCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        group by leader_id) t6 on t1.leaderId = t6.leaderId
        left join (select leader_id as 'leaderId',
        ifnull(count(1), 0) as 'effectCount'
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        group by leader_id) t7 on t1.leaderId = t7.leaderId
        where t1.leaderId is not null
        order by t1.totalCount desc
    </select>
    <select id="getByteDanceUserData" resultType="com.alibaba.fastjson.JSONObject">
        select t1.userId,
        t1.companyName,
        t1.deptName,
        t1.userName,
        t1.leaderName,
        t1.totalCount,
        t2.liveVideoCount,
        t3.clipVideoCount,
        t4.totalCharge,
        t5.pushMediaCount,
        t6.firstChargeCount,
        t7.effectCount
        from (select
        count(1) as 'totalCount',
        leader_name as 'leaderName',
        (case when company_name like '%北京汇创%'
        then '汇创'
        when company_name like '%石家庄%'
        then '石家庄'
        when company_name like '%聚意%'
        then '聚意'
        when company_name like '%北京骄阳%'
        then '骄阳'
        else '-'
        end
        ) as 'companyName',
        dept_name as 'deptName',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t1
        left join
        (select
        ifnull(count(1), 0) as 'liveVideoCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t2 on t1.userId = t2.userId
        left join
        (select
        ifnull(count(1), 0) as 'clipVideoCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t3 on t1.userId = t3.userId
        left join
        (select
        ifnull(sum(cost), 0) as 'totalCharge',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_datetime >= #{start}
        and stat_datetime &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>) t4 on t1.userId = t4.userId
        left join
        (select
        ifnull(count(1), 0) as 'pushMediaCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t5 on t1.userId = t5.userId
        left join (select
        ifnull(count(1), 0) as 'firstChargeCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>) t6 on t1.userId = t6.userId
        left join (select
        ifnull(count(1), 0) as 'effectCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_bytedance_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>) t7 on t1.userId = t7.userId
        where t1.userId != ''
        order by t1.totalCount desc

    </select>
    <select id="getKuaiShouUserData" resultType="com.alibaba.fastjson.JSONObject">
        select t1.userId,
        t1.companyName,
        t1.deptName,
        t1.userName,
        t1.leaderName,
        t1.totalCount,
        t2.liveVideoCount,
        t3.clipVideoCount,
        t4.totalCharge,
        t5.pushMediaCount,
        t6.firstChargeCount,
        t7.effectCount
        from (select
        count(1) as 'totalCount',
        leader_name as 'leaderName',
        (case when company_name like '%北京汇创%'
        then '汇创'
        when company_name like '%石家庄%'
        then '石家庄'
        when company_name like '%聚意%'
        then '聚意'
        when company_name like '%北京骄阳%'
        then '骄阳'
        else '-'
        end
        ) as 'companyName',
        dept_name as 'deptName',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t1
        left join
        (select
        ifnull(count(1), 0) as 'liveVideoCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60155
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t2 on t1.userId = t2.userId
        left join
        (select
        ifnull(count(1), 0) as 'clipVideoCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and help_time >= #{start}
        and help_time &lt;= #{end}
        and tag_id = 60156
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t3 on t1.userId = t3.userId
        left join
        (select
        ifnull(sum(charge), 0) as 'totalCharge',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_date >= #{start}
        and stat_date &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>) t4 on t1.userId = t4.userId
        left join
        (select
        ifnull(count(1), 0) as 'pushMediaCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_time >= #{start}
        and media_time &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>
        ) t5 on t1.userId = t5.userId
        left join (select
        ifnull(count(1), 0) as 'firstChargeCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and farst_cost_time >= #{start}
        and farst_cost_time &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>) t6 on t1.userId = t6.userId
        left join (select
        ifnull(count(1), 0) as 'effectCount',
        <if test="role == 'clip'">
            clip_id as 'userId',
            clip_name as 'userName'
        </if>
        <if test="role == 'plan'">
            plan_id as 'userId',
            plan_name as 'userName'
        </if>
        <if test="role == 'shot'">
            shot_id as 'userId',
            shot_name as 'userName'
        </if>
        from application.app_kuaishou_material_info
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and media_effect_date >= #{start}
        and media_effect_date &lt;= #{end}
        <if test="role == 'clip'">
            and clip_id != ''
            group by clip_id
        </if>
        <if test="role == 'plan'">
            and plan_id != ''
            group by plan_id
        </if>
        <if test="role == 'shot'">
            and shot_id != ''
            group by shot_id
        </if>) t7 on t1.userId = t7.userId
        where t1.userId != ''
        order by t1.totalCount desc
    </select>
    <select id="getByteDanceVideoData" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        (case when company_name like '%北京汇创%'
        then '汇创'
        when company_name like '%石家庄%'
        then '石家庄'
        when company_name like '%聚意%'
        then '聚意'
        when company_name like '%北京骄阳%'
        then '骄阳'
        else '-'
        end
        ) as 'companyName',
        signature as 'signature',
        leader_name as leaderName,
        clip_name as 'clipName',
        plan_name 'planName',
        shot_name 'shotName',
        sum(cost) as 'totalCharge',
        sum(`show`) as 'totalShow',
        sum(`click`) as 'totalClick',
        project_name as 'projectName',
        video_name as 'videoName',
        cover_url as 'coverUrl',
        video_url as 'videoUrl'
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_datetime >= #{start}
        and stat_datetime &lt;= #{end}
        and signature is not null
        and clip_id is not null
        group by signature
        ) t order by t.totalCharge desc

    </select>
    <select id="getKuaiShouVideoData" resultType="com.alibaba.fastjson.JSONObject">
        select * from (
        select
        (case when company_name like '%北京汇创%'
        then '汇创'
        when company_name like '%石家庄%'
        then '石家庄'
        when company_name like '%聚意%'
        then '聚意'
        when company_name like '%北京骄阳%'
        then '骄阳'
        else '-'
        end
        ) as 'companyName',
        signature as 'signature',
        leader_name as leaderName,
        clip_name as 'clipName',
        plan_name 'planName',
        shot_name 'shotName',
        sum(charge) as 'totalCharge',
        sum(`aclick`) as 'totalShow',
        sum(`bclick`) as 'totalClick',
        project_name as 'projectName',
        video_name as 'videoName',
        cover_url as 'coverUrl',
        photo_url as 'videoUrl'
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        and stat_date >= #{start}
        and stat_date &lt;= #{end}
        and signature is not null
        and clip_id is not null
        group by signature
        ) t order by t.totalCharge desc

    </select>
</mapper>
