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

    <!--  素材产出占比 快手 master-->
    <select id="getMeterialProduceRateKuaiShou" resultType="java.util.LinkedHashMap">
        SELECT a.*,
        sum(a.meterial) meterialNum,
        CONCAT(ROUND(sum(a.meterial) / a.total * 100,2) ,'%')as rate
        from (
        SELECT t.*
        from (
        SELECT
        COUNT( material_id ) meterial ,
        CASE company_name
        WHEN '北京汇创思拓数字科技有限公司' then '华北'
        WHEN '广州汇创思拓数字科技有限公司' then '华南'
        WHEN '上海汇创思拓数字科技有限公司' then '华东'
        WHEN '杭州妙构思数字科技有限公司' then '杭州'
        WHEN '北京骄阳数字科技有限公司' then '骄阳'
        WHEN '北京聚意创量文化传媒有限公司' then '聚意'
        else '其他'
        end area,
        company_name as companyName,
        (SELECT COUNT(material_id) as num
        FROM application.app_kuaishou_material_info
            <where>
                <if test="startTime !=null and startTime != ''">
                    AND media_time &gt;= #{startTime}
                </if>
                <if test="endTime !=null and endTime != ''">
                    AND media_time &lt;= #{endTime}
                </if>
            </where>
            ) as total
        FROM
        application.app_kuaishou_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND media_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND media_time &lt;= #{endTime}
            </if>
        </where>
        GROUP BY
        company_id
        ) t
        )a
        GROUP BY a.area
        ORDER BY a.area DESC
    </select>


    <!--  素材产出占比 头条 -->
    <select id="getMeterialProduceRateBytedance" resultType="java.util.LinkedHashMap">
        SELECT a.*,
        sum(a.meterial) meterialNum,
        CONCAT(ROUND(sum(a.meterial) / a.total * 100,2) ,'%')as rate
        from (
        SELECT t.*
        from (
        SELECT
        COUNT( material_id ) meterial,
        CASE company_name
        WHEN '北京汇创思拓数字科技有限公司' then '华北'
        WHEN '广州汇创思拓数字科技有限公司' then '华南'
        WHEN '上海汇创思拓数字科技有限公司' then '华东'
        WHEN '杭州妙构思数字科技有限公司' then '杭州'
        WHEN '北京骄阳数字科技有限公司' then '骄阳'
        WHEN '北京聚意创量文化传媒有限公司' then '聚意'
        else '其他'
        end area,
        company_name as companyName,
        (SELECT COUNT(material_id) as num
            FROM application.app_bytedance_material_info
            <where>
                <if test="startTime !=null and startTime != ''">
                    AND media_time &gt;= #{startTime}
                </if>
                <if test="endTime !=null and endTime != ''">
                    AND media_time &lt;= #{endTime}
                </if>
            </where>
            ) as total
        FROM
        application.app_bytedance_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND media_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND media_time &lt;= #{endTime}
            </if>
        </where>
        GROUP BY
        company_id) t
        )a
        GROUP BY a.area
        ORDER BY a.area DESC
    </select>

    <!--  分公司素材消耗 头条 -->
    <select id="getCompanyMeterialConsumeBytedance" resultType="java.util.LinkedHashMap">
        SELECT
        t.*,
        SUM(t.tcost) cost
        FROM
        (
        SELECT
        company_id,
        company_name,
        SUM(cost) tcost,
        CASE
        company_id
        WHEN 'd57fecdcf7a94d009736d9c850731582' THEN '华北'
        WHEN '6608ed13c0dd42de93c790dbdf124234' THEN '华南'
        WHEN '4b10089775c040119e139087517aed88' THEN '华东'
        WHEN '5e46b5dea3d740eeb8ff1a2895015a4b' THEN '骄阳'
        WHEN '1648c7ceb4d449a9a13fbed7a8d9976c' THEN '杭州'
        WHEN 'ace886fa51a244818e918b5d6d1bcc62' then '聚意'
        ELSE '其他'
        END area,
        ( SELECT SUM(cost) FROM application.bytedance_material_video_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_datetime &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_datetime &lt;= #{endTime}
            </if>
        </where>
            ) totalCost
        FROM
        application.bytedance_material_video_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_datetime &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_datetime &lt;= #{endTime}
            </if>
        </where>
        GROUP BY
        company_id ) t
        WHERE t.tcost > 0
        GROUP BY t.area
        ORDER BY t.area DESC;
    </select>

    <!--  分公司素材消耗 快手 -->
    <select id="getCompanyMeterialConsumeKuaishou" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        SUM(t.tcost) as cost
        from (
        SELECT company_id,company_name,SUM(charge) tcost,
        CASE company_id
        WHEN 'd57fecdcf7a94d009736d9c850731582' then '华北'
        WHEN '6608ed13c0dd42de93c790dbdf124234' then '华南'
        WHEN '4b10089775c040119e139087517aed88' then '华东'
        WHEN '5e46b5dea3d740eeb8ff1a2895015a4b' then '骄阳'
        WHEN '1648c7ceb4d449a9a13fbed7a8d9976c' then '杭州'
        WHEN 'ace886fa51a244818e918b5d6d1bcc62' then '聚意'
        else '其他'
        end area,
        (SELECT SUM(charge) from application.kuaishou_material_video_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_date &lt;= #{endTime}
            </if>
        </where>) totalCost
        FROM
        application.kuaishou_material_video_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_date &lt;= #{endTime}
            </if>
        </where>
        GROUP BY
        company_id
        ) t
        where t.tcost > 0
        GROUP BY t.area
        ORDER BY t.area DESC;
    </select>

    <!--  素材相关===素材使用率===头条 -->
    <select id="getMeterialUseRateBytedanc" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        ROUND(t.bytedanceTotal / t.total * 100,2) as useRate
        from (
        SELECT COUNT(material_id) total,
        (SELECT COUNT(material_id) total from application.app_bytedance_material_info
        where sync_bytedance = 2
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
            ) as bytedanceTotal
        FROM
        application.app_bytedance_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
        </where>
        ) t
    </select>


    <!--  素材相关===素材使用率===快手 -->
    <select id="getMeterialUseRateKuaishou" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        ROUND(t.kuaiShouTotal / t.total * 100,2) as useRate
        from (
        SELECT COUNT(material_id) total,
        (SELECT COUNT(material_id) total from application.app_kuaishou_material_info
        where sync_kuaishou = 2
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
            ) as kuaiShouTotal
        FROM
        application.app_kuaishou_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
        </where>
        ) t
    </select>

<!--  素材相关===素材爆款率===头条 -->
    <select id="getMeterialFaddishRateBytedance" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        ROUND(t.faddish / t.total * 100,2) as faddishRate
        FROM
        (
        SELECT COUNT(material_id) total,
        (SELECT COUNT(material_id) total from application.app_bytedance_material_info
            <where>
                <if test="startTime !=null and startTime != ''">
                    AND faddish_date &gt;= #{startTime}
                </if>
                <if test="endTime !=null and endTime != ''">
                    AND faddish_date &lt;= #{endTime}
                </if>
            </where>
            ) as faddish
        FROM
        application.app_bytedance_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND media_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND media_time &lt;= #{endTime}
            </if>
        </where>
        ) t
    </select>


<!--  素材相关===素材爆款率===快手 -->
    <select id="getMeterialFaddishRateKuaishou" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        ROUND(t.faddish / t.total * 100,2) as faddishRate
        FROM
        (
        SELECT COUNT(material_id) total,
        (SELECT COUNT(material_id) total from application.app_kuaishou_material_info
            <where>
                <if test="startTime !=null and startTime != ''">
                    AND faddish_date &gt;= #{startTime}
                </if>
                <if test="endTime !=null and endTime != ''">
                    AND faddish_date &lt;= #{endTime}
                </if>
            </where>
            ) as faddish
        FROM
        application.app_kuaishou_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND media_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND media_time &lt;= #{endTime}
            </if>
        </where>
        ) t
    </select>

<!--  素材相关===素材创新率===头条 -->
    <select id="getMeterialInnovateRateBytedance" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        ROUND(t.innovate / t.total * 100,2) as innovateRate
        FROM
        (
        SELECT COUNT(material_id) total,
        (SELECT COUNT(material_id) total from application.app_bytedance_material_info
        where material_innovate = 2
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
            ) as innovate
        FROM
        application.app_bytedance_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
        </where>
        ) t
    </select>

<!--  素材相关===素材创新率===快手 -->
    <select id="getMeterialInnovateRateKuaishou" resultType="java.util.LinkedHashMap">
        SELECT t.*,
        ROUND(t.innovate / t.total * 100,2) as innovateRate
        FROM
        (
        SELECT COUNT(material_id) total,
        (SELECT COUNT(material_id) total from application.app_kuaishou_material_info
        where material_innovate = 2
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
            ) as innovate
        FROM
        application.app_kuaishou_material_info
        <where>
            <if test="startTime !=null and startTime != ''">
                AND help_time &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND help_time &lt;= #{endTime}
            </if>
        </where>
        ) t
    </select>

<!--  分公司投放数据-账户-头条 -->
    <select id="getCompanyAccountDataBytedance" resultType="java.util.LinkedHashMap">
        SELECT
        t.*,
        SUM(t.tcost) cost,
        CONCAT(ROUND(SUM(t.tcost) / t.totalCost * 100,2) ,'%')as rate
        FROM
        (
        SELECT
        company_id,
        company_name,
        SUM(cost) tcost,
        CASE
        company_id
        WHEN 'd57fecdcf7a94d009736d9c850731582' THEN '华北'
        WHEN '6608ed13c0dd42de93c790dbdf124234' THEN '华南'
        WHEN '4b10089775c040119e139087517aed88' THEN '华东'
        WHEN '5e46b5dea3d740eeb8ff1a2895015a4b' THEN '骄阳'
        WHEN '1648c7ceb4d449a9a13fbed7a8d9976c' THEN '杭州'
        ELSE '其他'
        END area,
        ( SELECT SUM(cost) FROM application.bytedance_advertiser_report_daily_dw
            <where>
                <if test="startTime !=null and startTime != ''">
                    AND stat_datetime &gt;= #{startTime}
                </if>
                <if test="endTime !=null and endTime != ''">
                    AND stat_datetime &lt;= #{endTime}
                </if>
            </where>
            ) totalCost
        FROM
        application.bytedance_advertiser_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_datetime &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_datetime &lt;= #{endTime}
            </if>
        </where>
        GROUP BY
        company_id ) t
        where t.tcost > 0
        GROUP BY
        t.area;
    </select>


<!--  分公司投放数据-账户-快手 -->
    <select id="getCompanyAccountDataKuaishou" resultType="java.util.LinkedHashMap">
        SELECT
        t.*,
        SUM(t.tcost) cost,
        CONCAT(ROUND(SUM(t.tcost) / t.totalCost * 100,2) ,'%')as rate
        FROM
        (
        SELECT
        company_id,
        company_name,
        SUM(charge) tcost,
        CASE
        company_id
        WHEN 'd57fecdcf7a94d009736d9c850731582' THEN '华北'
        WHEN '6608ed13c0dd42de93c790dbdf124234' THEN '华南'
        WHEN '4b10089775c040119e139087517aed88' THEN '华东'
        WHEN '5e46b5dea3d740eeb8ff1a2895015a4b' THEN '骄阳'
        WHEN '1648c7ceb4d449a9a13fbed7a8d9976c' THEN '杭州'
        ELSE '其他'
        END area,
        ( SELECT SUM(charge) FROM application.kuaishou_account_report_daily_dw
            <where>
                <if test="startTime !=null and startTime != ''">
                    AND stat_date &gt;= #{startTime}
                </if>
                <if test="endTime !=null and endTime != ''">
                    AND stat_date &lt;= #{endTime}
                </if>
            </where>
            ) totalCost
        FROM
        application.kuaishou_account_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_date &lt;= #{endTime}
            </if>
        </where>
        GROUP BY
        company_id ) t
        where t.tcost > 0
        GROUP BY
        t.area;
    </select>




<!--  分媒体投放数据-账户维度 -->
    <select id="getMediaTypeAccountData" resultType="java.util.LinkedHashMap">
        SELECT
            t.stat_datetime as time,
            t.bCost as bytedanceCost,
            k.KCost as kuaishouCost
        from
            (
                SELECT
                stat_datetime,
                SUM(cost) bCost
                FROM
                application.bytedance_advertiser_report_daily_dw
                <where>
                    <if test="firstDay !=null and firstDay != ''">
                        AND stat_datetime &gt;= #{firstDay}
                    </if>
                </where>
                GROUP BY stat_datetime
                ORDER BY stat_datetime
            ) t
            LEFT JOIN
            (
                SELECT
                stat_date,
                SUM(charge) KCost
                FROM
                application.kuaishou_account_report_daily_dw
                <where>
                    <if test="firstDay !=null and firstDay != ''">
                        AND stat_date &gt;= #{firstDay}
                    </if>
                </where>
                GROUP BY stat_date
                ORDER BY stat_date
            ) k
        on t.stat_datetime = k.stat_date
    </select>





<!--  分媒体公司消耗-账户维度-分日数据 -折线图-头条-->
    <select id="getMediaTypeAccountTrendBytedance" resultType="org.jeecg.ctop.material.entity.AccountTrendPojo">
        SELECT
            DATE_FORMAT(stat_datetime,'%Y-%m-%d') as time,
            SUM(cost) as cost,
            CASE
                company_id
                WHEN 'd57fecdcf7a94d009736d9c850731582' THEN '华北'
                WHEN '6608ed13c0dd42de93c790dbdf124234' THEN '华南'
                WHEN '4b10089775c040119e139087517aed88' THEN '华东'
                WHEN '5e46b5dea3d740eeb8ff1a2895015a4b' THEN '骄阳'
                ELSE '其他'
                END area
        FROM
            application.bytedance_advertiser_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_datetime &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_datetime &lt;= #{endTime}
            </if>
        </where>
        GROUP BY stat_datetime,company_id
        ORDER BY stat_datetime
    </select>

<!--  分媒体公司消耗-账户维度-分日数据-折线图-快手 -->
    <select id="getMediaTypeAccountTrendKuaishou" resultType="org.jeecg.ctop.material.entity.AccountTrendPojo">
        SELECT
        DATE_FORMAT(stat_date,'%Y-%m-%d') as time,
        SUM(charge) as cost,
        CASE
            company_id
            WHEN 'd57fecdcf7a94d009736d9c850731582' THEN '华北'
            WHEN '6608ed13c0dd42de93c790dbdf124234' THEN '华南'
            WHEN '4b10089775c040119e139087517aed88' THEN '华东'
            WHEN '5e46b5dea3d740eeb8ff1a2895015a4b' THEN '骄阳'
            ELSE '其他'
            END area
        FROM
        application.kuaishou_account_report_daily_dw
        <where>
            <if test="startTime !=null and startTime != ''">
                AND stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND stat_date &lt;= #{endTime}
            </if>
        </where>
        GROUP BY stat_date,company_id
        ORDER BY stat_date
    </select>





    <!--  项目消耗排行 top10 头条-->
    <select id="getMediaTypeProjectCostBytedance" resultType="java.util.LinkedHashMap">
        SELECT
        a.project_id as projectId,
        a.project_name as projectName,
        SUM(a.cost) as totalCost
        FROM
        application.bytedance_advertiser_report_daily_dw a
        <where>
            <if test="startTime !=null and startTime != ''">
                AND a.stat_datetime &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND a.stat_datetime &lt;= #{endTime}
            </if>
        </where>
        GROUP BY a.project_id
        ORDER BY totalCost DESC
        limit 10
    </select>
    <!--  项目消耗排行 top10 头条-基建数-->
    <select id="getMediaTypeProjectCostBytedanceBase" resultType="java.util.LinkedHashMap">
        SELECT
        b.project_id as projectId,
        IFNULL(SUM(b.new_ad_num),0) as baseNum
        FROM
        application.app_bytedance_account_base_info_d b
        <where>
            <if test="projectId !=null and projectId != ''">
                AND b.project_id &gt;= #{projectId}
            </if>
            <if test="startTime !=null and startTime != ''">
                AND b.stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND b.stat_date &lt;= #{endTime}
            </if>
        </where>

    </select>

    <!--  项目消耗排行 top10 快手-->
    <select id="getMediaTypeProjectCostKuaishou" resultType="java.util.LinkedHashMap">
        SELECT
        a.project_id as projectId,
        a.project_name as projectName,
        SUM(a.charge) as totalCost
        FROM
        application.kuaishou_account_report_daily_dw a
        <where>
            <if test="startTime !=null and startTime != ''">
                AND a.stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND a.stat_date &lt;= #{endTime}
            </if>
        </where>
        GROUP BY a.project_id
        ORDER BY totalCost DESC
        limit 10
    </select>
    <!--  项目消耗排行 top10 快手 基建数-->
    <select id="getMediaTypeProjectCostKuaishouBase" resultType="java.util.LinkedHashMap">
        SELECT
        b.project_id projectId,
        IFNULL(SUM(b.new_unit_num),0) as baseNum
        FROM
        application.app_kuaishou_account_base_info_d b
        <where>
            <if test="projectId !=null and projectId != ''">
                AND b.project_id = #{projectId}
            </if>
            <if test="startTime !=null and startTime != ''">
                AND b.stat_date &gt;= #{startTime}
            </if>
            <if test="endTime !=null and endTime != ''">
                AND b.stat_date &lt;= #{endTime}
            </if>
        </where>

    </select>






</mapper>
