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

    <select id="recursiveQuerySubordinateByLeader" resultType="java.lang.String">
        SELECT distinct id
        FROM sys_user
        WHERE leader_id = #{leaderId}
          and status = 1
          and del_flag = 0
    </select>

    <select id="getDesignTeamList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id, depart_name as departName, leader_id as leaderId, leader_name as leaderName
        from sys_depart
        where parent_id = (
            SELECT t1.parent_id
            from sys_user t
                     LEFT JOIN sys_depart t1 on t.org_code = t1.org_code
            where t.id = #{userId})
    </select>

    <select id="getDesignTeamALLList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id, depart_name as departName, leader_id as leaderId, leader_name as leaderName
        from application.sys_depart_personal
    </select>

    <select id="recursiveQuerySubordinateByLeaders" resultType="java.lang.String">
        SELECT distinct id
        FROM sys_user
        WHERE
        status = 1
        and del_flag = 0
        and leader_id in
        <foreach collection="leaderIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="recursiveQuerySubordinateByUserId" resultType="java.lang.String">
        SELECT distinct id
        FROM sys_user
        WHERE leader_id = (select leader_id from sys_user where id = #{userId})
          and status = 1
          and del_flag = 0

    </select>

    <select id="queryProjectBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DISTINCT
        t1.project_id as projectId,
        t1.project_name as projectName
        FROM
        ctop_project_member t1
        LEFT JOIN ctop_project t2 on t1.project_id=t2.id
        WHERE
        t2.media_id IN
        <foreach collection="mediaIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        and t1.user_id IN
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        and t1.project_name is not null and t1.project_name!=''
        ORDER BY t2.create_time desc
    </select>

    <select id="queryProjectIdBy" resultType="java.lang.Long">
        SELECT DISTINCT
        t1.project_id as projectId
        FROM
        ctop_project_member t1
        LEFT JOIN ctop_project t2 on t1.project_id=t2.id
        WHERE
        t2.media_id IN
        <foreach collection="mediaIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        and t1.user_id IN
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>

    </select>

    <select id="queryBytedanceCost" resultType="java.math.BigDecimal">
        SELECT
        sum(cost)
        FROM
        application.bytedance_material_video_report_daily_dw
        <where>
            <if test='dimension=="project" and projects!=null'>
                AND project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test='dimension=="designTeam" and leaderId!=null'>
                and leader_id=#{leaderId}
            </if>

            <if test='dimension=="designer" and designerId!=null'>
                and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
            </if>
            AND stat_datetime &gt;= #{startDate}
            AND stat_datetime &lt;= #{endDate}
            AND company_id=#{companyId}
        </where>
    </select>

    <select id="queryKuaishouCost" resultType="java.math.BigDecimal">
        SELECT
        sum(charge)
        FROM
        application.kuaishou_material_video_report_daily_dw
        <where>
            <if test='dimension=="project" and projects!=null'>
                AND project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test='dimension=="designTeam" and leaderId!=null'>
                and leader_id=#{leaderId}
            </if>

            <if test='dimension=="designer" and designerId!=null'>
                and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
            </if>

            AND stat_date &gt;= #{startDate}
            AND stat_date &lt;= #{endDate}
            AND company_id=#{companyId}
        </where>
    </select>

    <select id="queryMaterialCount" resultType="long">
        SELECT
        count(id) from ctop_material_info
        WHERE
        1=1
        <if test="projects !=null">
            AND project_id IN
            <foreach collection="projects" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND DATE_FORMAT(create_time, '%Y-%m-%d')&gt;= #{startDate}
        AND DATE_FORMAT(create_time, '%Y-%m-%d')&lt;= #{endDate}
    </select>

    <!-- 查询素材 上新 数据  头条-->
    <select id="queryMaterialCountList" resultType="java.util.HashMap">
        SELECT
        count( tt.material_id) as count ,
        DATE_FORMAT(tt.media_time, '%Y-%m-%d') as time
        from (
        SELECT t.material_id ,t.media_time,leader_id,clip_id,shot_id,plan_id,company_id from
        application.app_bytedance_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id

        <where>
            t.media_time >= #{startDate}
            and t.media_time &lt;=#{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            AND t.company_id=#{companyId}
        </where>
        )tt
        GROUP BY time
        ORDER BY time
    </select>
    <!-- 查询素材 上新 数据  快手-->
    <select id="queryMaterialCountListKs" resultType="java.util.HashMap">
        SELECT
        count( tt.material_id) as count ,
        DATE_FORMAT(tt.media_time, '%Y-%m-%d') as time
        from (
        SELECT t.material_id ,t.media_time,leader_id,clip_id,shot_id,plan_id,company_id from
        application.app_kuaishou_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id
        <where>
            t.media_time >= #{startDate}
            and t.media_time &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            AND t.company_id=#{companyId}
        </where>
        )tt
        GROUP BY time
        ORDER BY time
    </select>

    <!-- 查询有消耗的素材 头条 -->
    <select id="queryMaterialCostList" resultType="long">
        select count(0) from (
        SELECT t.material_id ,t.media_time,leader_id,clip_id,shot_id,plan_id,company_id from
        application.app_bytedance_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id
        left join application.bytedance_material_farst_cost_time t2 on t.material_id =t2.signature
        <where>
            t.media_time >= #{startDate}
            and t.media_time &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            AND t.company_id=#{companyId}
            and t2.frast_cost_time is not null
        </where>
        ) tt
    </select>

    <!-- 查询有消耗的素材 快手 -->
    <select id="queryMaterialCostListKs" resultType="long">
        select count(0) from (
        SELECT t.material_id ,t.media_time,leader_id,clip_id,shot_id,plan_id,company_id from
        application.app_kuaishou_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id
        left join application.material_farst_cost_time t2 on t.material_id=t2.signature
        <where>
            t.media_time >= #{startDate}
            and t.media_time &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            AND t.company_id=#{companyId}
            and t2.frast_cost_time is not null
        </where>
        ) tt

    </select>

    <!-- 有效视频-头条 -->
    <select id="selectEffectiveMaterialVideoBytedance" resultType="java.util.HashMap">
        SELECT
        count( tt.material_id) as count ,
        DATE_FORMAT(tt.effect_date, '%Y-%m-%d') as time
        from (
        SELECT t.material_id ,t.effect_date,leader_id,clip_id,shot_id,plan_id,company_id from
        application.app_bytedance_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id
        <where>
            t.effect_date >= #{startDate}
            and t.effect_date &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>

            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            AND t.company_id=#{companyId}
        </where>
        )tt
        GROUP BY time
        ORDER BY time
    </select>


    <!-- 有效视频-快手 -->
    <select id="selectEffectiveMaterialVideoKs" resultType="java.util.HashMap">
        SELECT
        count( tt.material_id) as count ,
        DATE_FORMAT(tt.effect_date, '%Y-%m-%d') as time
        from (
        SELECT t.material_id ,t.effect_date,leader_id,clip_id,shot_id,plan_id,company_id from
        application.app_kuaishou_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id
        <where>
            t.effect_date >= #{startDate}
            and t.effect_date &lt;=#{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            AND t.company_id=#{companyId}
        </where>
        )tt
        GROUP BY time
        ORDER BY time
    </select>


    <!-- 爆款视频-头条 -->
    <select id="selectHotMaterialVideoBytedance" resultType="java.util.HashMap">

        SELECT
        count( tt.material_id) as count ,
        DATE_FORMAT(tt.faddish_date, '%Y-%m-%d') as time
        from (
        SELECT t.material_id,t.faddish_date from
        application.app_bytedance_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id

        <if test='type==1 or type==2'>
            left join application.app_material_tag_busi_info t2 on t.material_id=t2.material_id
        </if>

        <where>
            t.faddish_date >= #{startDate}
            and t.faddish_date &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
            <if test='type==1'>
                and t2.parent_id=60156
            </if>
            <if test='type==2'>
                and t2.parent_id=60155
            </if>
            AND t.company_id=#{companyId}
        </where>
        group by t.material_id
        )tt
        GROUP BY time
        ORDER BY time

    </select>


    <!-- 爆款视频-快手 -->
    <select id="selectHotMaterialVideoKs" resultType="java.util.HashMap">
        SELECT
        count( tt.material_id) as count ,
        DATE_FORMAT(tt.faddish_date, '%Y-%m-%d') as time
        from (
        SELECT t.material_id,t.faddish_date from
        application.app_kuaishou_material_info t
        left join
        ctop_user_allocation t1 on t.leader_id=t1.user_id

        <if test='type==1 or type==2'>
            left join application.app_material_tag_busi_info t2 on t.material_id=t2.material_id
        </if>

        <where>
            t.faddish_date >= #{startDate}
            and t.faddish_date &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>

            <if test='type==1'>
                and t2.parent_id=60156
            </if>
            <if test='type==2'>
                and t2.parent_id=60155
            </if>
            AND t.company_id=#{companyId}
        </where>
        group by t.material_id
        )tt
        GROUP BY time
        ORDER BY time
    </select>


    <select id="queryNewMaterialCount" resultType="long">
        SELECT
        count(id) from ctop_material_info
        WHERE
        DATE_FORMAT(create_time, '%Y-%m-%d')= #{statDate}
        <if test="projects !=null">
            AND project_id IN
            <foreach collection="projects" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="queryBytedanceChat" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        sum(cost) as cost,
        sum(`show`) as aclick,
        sum(active) as active,
        stat_datetime as statDate
        FROM
        application.bytedance_material_video_report_daily_dw
        <where>
            <if test='dimension=="project" and projects!=null'>
                AND project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
            </if>
            AND stat_datetime &gt;= #{startDate}
            AND stat_datetime &lt;= #{endDate}
            AND company_id=#{companyId}
        </where>
        group by stat_datetime
        order by stat_datetime asc
    </select>

    <select id="queryBytedanceChatGroupMonth" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        sum(cost) as cost,
        sum(`show`) as aclick,
        sum(active) as active,
        DATE_FORMAT(stat_datetime, '%Y-%m') as statDate
        FROM
        application.bytedance_material_video_report_daily_dw
        <where>
            <if test='dimension=="project" and projects!=null'>
                AND project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
            </if>
            AND stat_datetime &gt;= #{startDate}
            AND stat_datetime &lt;= #{endDate}
            AND company_id=#{companyId}
        </where>
        group by DATE_FORMAT(stat_datetime, '%Y-%m')
        order by stat_datetime asc
    </select>

    <select id="queryKuaishouChat" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        sum(charge) as cost,
        sum(aclick) as aclick,
        sum(activation) as active,
        stat_date as statDate
        FROM
        application.kuaishou_material_video_report_daily_dw
        <where>
            <if test='dimension=="project" and projects!=null'>
                AND project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
            </if>
            AND stat_date &gt;= #{startDate}
            AND stat_date &lt;= #{endDate}
            AND company_id=#{companyId}
        </where>
        group by stat_date
        order by stat_date asc
    </select>

    <select id="queryKuaishouChatGroupMonth" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        sum(charge) as cost,
        sum(aclick) as aclick,
        sum(activation) as active,
        DATE_FORMAT(stat_date, '%Y-%m') as statDate
        FROM
        application.kuaishou_material_video_report_daily_dw
        <where>
            <if test='dimension=="project" and projects!=null'>
                AND project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
            </if>
            AND stat_date &gt;= #{startDate}
            AND stat_date &lt;= #{endDate}
            AND company_id=#{companyId}
        </where>
        group by DATE_FORMAT(stat_date, '%Y-%m')
        order by stat_date asc
    </select>

    <select id="queryBytedanceTopMaterial" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.material_id,
        t.tag,
        ROUND(t1.charge,2) as 'charge',
        t1.video_name as        'materialName',
        t1.video_url  as 'url',
        t1.cover_url as 'coverUrl'
        from
        (
        select
        material_id,
        GROUP_CONCAT(tag_name) as tag
        from
        application.app_material_tag_busi_info
        where
        tag_category_id=1
        and company_id=#{companyId}
        <if test='dimension=="designTeam" and leaderId!=null'>
            and leader_id=#{leaderId}
        </if>
        group by material_id
        ) t
        left join
        (
        select
        signature,
        sum(cost) as charge,
        video_name,
        video_url,
        cover_url
        from
        application.bytedance_material_video_report_daily_dw
        WHERE cost>0
        <if test='dimension=="designTeam" and leaderId!=null'>
            and leader_id=#{leaderId}
        </if>
        <if test='dimension=="designer" and designerId!=null'>
            and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
        </if>
        <if test='dimension=="project" and projects!=null'>
            AND project_id IN
            <foreach collection="projects" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND stat_datetime >= #{startDate}
        and stat_datetime &lt;= #{endDate}
        GROUP BY signature

        ) t1 on t1.signature=t.material_id
        ORDER BY charge DESC LIMIT 5
    </select>

    <select id="queryKuaishouTopDesign" resultType="com.alibaba.fastjson.JSONObject">
        select t1.* from (
        select sum(charge) as 'cost',
        <if test="type == 1">shot_name as userName, "摄像" as roleName,shot_id as 'userId'</if>
        <if test="type == 2">clip_name as userName, "剪辑" as roleName,clip_id as 'userId'</if>
        <if test="type == 3">plan_name as userName, "编导" as roleName,plan_id as 'userId'</if>
        from application.kuaishou_material_video_report_daily_dw
        where 1 = 1
        and stat_date >= #{startDate}
        and stat_date &lt;= #{endDate}
        and company_id = #{companyId}
        <if test="type == 1">
            and shot_id is not null
            group by shot_id
        </if>
        <if test="type == 2">
            and clip_id is not null
            group by clip_id
        </if>
        <if test="type == 3">
            and plan_id is not null
            group by plan_id
        </if>
        ) t1 left join `jeecg-boot`.sys_user t2 on t1.userId = t2.id
        where t2.status = 1
        order by t1.cost desc
    </select>

    <select id="queryBytedanceTopDesign" resultType="com.alibaba.fastjson.JSONObject">
        select t1.* from (
        select sum(cost) as 'cost',
        <if test="type == 1">shot_name as userName, "摄像" as roleName,shot_id as 'userId'</if>
        <if test="type == 2">clip_name as userName, "剪辑" as roleName,clip_id as 'userId'</if>
        <if test="type == 3">plan_name as userName, "编导" as roleName,plan_id as 'userId'</if>
        from application.bytedance_material_video_report_daily_dw
        where 1 = 1
        and stat_datetime >= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and company_id = #{companyId}
        <if test="type == 1">
            and shot_id is not null
            group by shot_id
        </if>
        <if test="type == 2">
            and clip_id is not null
            group by clip_id
        </if>
        <if test="type == 3">
            and plan_id is not null
            group by plan_id
        </if>
        ) t1 left join `jeecg-boot`.sys_user t2 on t1.userId = t2.id
        where t2.status = 1
        order by t1.cost desc
    </select>

    <select id="queryBytedanceTopMaterialNew" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.*,
        IFNULL((SELECT GROUP_CONCAT(tag_name) from ctop_material_tag_info where code=signature and status=1),"塑造") as
        tag
        FROM
        (SELECT md5 signature,
        case when toutiao_url = '' or toutiao_url is null then url end url,
        cover_url AS coverUrl,
        material_name AS materialName,
        sum(cost) AS cost from etl_report_bytedance_video
        where 1=1
        <if test="projects !=null">
            AND project_id IN
            <foreach collection="projects" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND stat_datetime &gt;= #{startDate}
        AND stat_datetime &lt;= #{endDate}
        AND md5!=''
        GROUP BY md5
        ORDER BY cost DESC
        LIMIT 5) t1
        ORDER BY cost DESC
    </select>


    <select id="queryBytedanceMaterialReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.signature AS signature,
        IFNULL(t1.video_url, '') AS url,
        t1.cover_url AS coverUrl,
        t.material_name AS materialName,
        t1.project_name AS projectName,
        ${filed}
        FROM
        application.bytedance_material_video_report_daily_dw t1
        LEFT JOIN
        application.app_bytedance_material_info t on t.material_id=t1.signature
        <if test="materialType!=null and materialType!=''">
            LEFT JOIN
            (select material_id from application.app_material_tag_busi_info
            <if test='materialType==1'>
                where parent_id=60156
            </if>
            <if test='materialType==2'>
                where parent_id=60155
            </if>
            GROUP BY material_id) t2 on t.material_id=t2.material_id
        </if>

        <where>
            and t1.stat_datetime >= #{startDate} and t1.stat_datetime &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t1.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t1.clip_id=#{designerId} or t1.shot_id=#{designerId} or t1.plan_id=#{designerId})
            </if>
            <if test='materialQuality==0'>
                and t.faddish_date >= #{startDate} and t.faddish_date &lt;= #{endDate}
            </if>
            <if test='materialQuality==1'>
                and t.effect_date >= #{startDate} and t.effect_date &lt;= #{endDate}
            </if>
            <if test='innovative==0'>
                and t.material_innovate=2
            </if>
            <if test='innovative==1'>
                and t.material_innovate=1
            </if>
            <if test="materialType!=null and materialType!=''">
                and t2.material_id is not null
            </if>
            <if test="md5!=null and md5!=''">
                and t1.material_id =#{md5}
            </if>

            AND t1.company_id=#{companyId}
        </where>
        GROUP BY t1.signature
        ORDER BY ${target} ${order}

    </select>

    <select id="queryBytedanceMaterialReportCount" resultType="java.lang.Long">
        select count(1) from(
        SELECT
        count(1)
        FROM
        etl_report_bytedance_video t1
        WHERE
        1=1
        <if test="md5 !=null and md5 != ''">
            AND t1.md5= #{md5}
        </if>
        <if test="projects !=null">
            AND project_id IN
            <foreach collection="projects" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND stat_datetime &gt;= #{startDate}
        AND stat_datetime &lt;= #{endDate}
        GROUP BY t1.md5) t
    </select>


    <select id="queryKuaishouTopMaterial" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.material_id,
        t.tag,
        ROUND(t1.charge,2) as 'charge',
        t1.video_name as        'materialName',
        t1.photo_url  as 'url',
        t1.cover_url as 'coverUrl'
        from
        (
        select
        material_id,
        GROUP_CONCAT(tag_name) as tag
        from
        application.app_material_tag_busi_info
        where
        tag_category_id=1
        and company_id=#{companyId}
        <if test='dimension=="designTeam" and leaderId!=null'>
            and leader_id=#{leaderId}
        </if>
        group by material_id
        ) t
        left join
        (
        select
        signature,
        sum(charge) as charge,
        video_name,
        photo_url,
        cover_url from
        application.kuaishou_material_video_report_daily_dw
        WHERE charge>0
        <if test='dimension=="designTeam" and leaderId!=null'>
            and leader_id=#{leaderId}
        </if>
        <if test='dimension=="designer" and designerId!=null'>
            and (clip_id=#{designerId} or shot_id=#{designerId} or plan_id=#{designerId})
        </if>
        <if test='dimension=="project" and projects!=null'>
            AND project_id IN
            <foreach collection="projects" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND stat_date >= #{startDate}
        and stat_date &lt;= #{endDate}
        GROUP BY signature
        ) t1 on t1.signature=t.material_id
        ORDER BY charge DESC
        LIMIT 5
    </select>

    <select id="queryKuaishouMaterialReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(t1.photo_url, '') AS url,
        t1.cover_url AS coverUrl,
        t.material_name AS materialName,
        ${filed}
        FROM
        application.kuaishou_material_video_report_daily_dw t1
        LEFT JOIN
        application.app_kuaishou_material_info t on t1.signature=t.material_id
        <if test="materialType!=null and materialType!=''">
            LEFT JOIN
            (select material_id from application.app_material_tag_busi_info
            <if test='materialType==1'>
                where parent_id=60156
            </if>
            <if test='materialType==2'>
                where parent_id=60155
            </if>
            GROUP BY material_id) t2 on t.material_id=t2.material_id
        </if>

        <where>
            t1.stat_date >= #{startDate} and t1.stat_date &lt;= #{endDate}
            <if test='dimension=="project" and projects!=null'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t1.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t1.clip_id=#{designerId} or t1.shot_id=#{designerId} or t1.plan_id=#{designerId})
            </if>
            <choose>
                <when test='materialQuality==0'>
                    and t.faddish_date >= #{startDate} and t.faddish_date &lt;= #{endDate}
                </when>
                <when test='materialQuality==1'>
                    and t.effect_date >= #{startDate} and t.effect_date &lt;=#{endDate}
                </when>
            </choose>

            <if test='innovative==0'>
                and t.material_innovate=2
            </if>
            <if test='innovative==1'>
                and t.material_innovate=1
            </if>
            <if test="materialType!=null and materialType!=''">
                and t2.material_id is not null
            </if>
            <if test="md5!=null and md5!=''">
                and t1.signature =#{md5}
            </if>
            AND t1.company_id=#{companyId}
        </where>
        GROUP BY t1.signature
        ORDER BY ${target} ${order}
    </select>

    <select id="queryKuaishouMaterialReportCount" resultType="java.lang.Long">
        SELECT
        count(1)
        FROM
        (select t1.signature
        from ctop_etl_kuaishou_account_material_report_daily t1
        where
        1=1
        <if test="md5 !=null and md5 != ''">
            AND t1.signature= #{md5}
        </if>
        <if test="projects !=null">
            AND project_id IN
            <foreach collection="projects" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        AND t1.stat_date &gt;= #{startDate}
        AND t1.stat_date &lt;= #{endDate}
        GROUP BY t1.signature
        ) t1
        LEFT JOIN ctop_etl_kuaishou_video_info t2
        ON t1.signature = t2.video_code
        where 1=1
        <if test="channelType !=null and channelType != ''">
            AND t2.channel_type= #{channelType}
        </if>
    </select>

    <select id="queryTopDesign" resultType="com.alibaba.fastjson.JSONObject">
        SELECT rowNumber,
        userName,
        roleName,
        cost
        from(
        SELECT (@i:=@i+1) AS rowNumber,
        userName,
        roleName,
        cost
        from(
        SELECT
        user_name AS userName,
        role_name AS roleName,
        sum(cost) cost
        FROM
        etl_design_daily_report,(SELECT @i:=0) i
        where
        stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        AND user_id IN
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        GROUP BY
        user_id
        ORDER BY
        ${target} ${order}) t) t1
        where 1=1
        <if test="roleName != null">
            and t1.roleName = #{roleName}
        </if>
    </select>

    <select id="queryTopDesignCount" resultType="java.lang.Long">
        SELECT
        count(1)
        FROM
        (
        SELECT
        count(1)
        FROM
        etl_design_daily_report
        where
        stat_date &gt;= #{startDate}
        AND stat_date &lt;= #{endDate}
        AND user_id IN
        <foreach collection="userIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        GROUP BY
        user_id ) t
    </select>

    <select id="queryBytedanceVideoDetail" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.url,
               t.cover_url                                                                           as coverUrl,
               t.id                                                                                  as materialId,
               t.material_name                                                                       as materialName,
               t2.`second`,
               '头条'                                                                                  as madia,
               GROUP_CONCAT((SELECT project_name from ctop_project where id = t.project_id limit 1)) as projectName,
               t.create_time                                                                         as createTime,
               t2.size,
               '内部'                                                                                  as channelType,
               t.`code`,
               ''                                                                                    as materialType,
               t2.height,
               t2.width,
               '北京汇创思拓数字科技有限公司'                                                                      as supplier,
               IFNULL((SELECT leader_name from sys_user where id = t1.clip_id), '')                  as leaderName,
               IFNULL(t1.clip_name, '')                                                                 clipName,
               IFNULL(t1.shot_name, '')                                                                 shotName,
               IFNULL(t1.plan_name, '')                                                                 planName
        FROM ctop_material_info t
                 LEFT JOIN ctop_material_ascription t1 on t.`code` = t1.material_id
                 LEFT JOIN ctop_material_parameter t2 on t.`code` = t2.material_id
        where t.code = #{md5}
        GROUP BY t.code
    </select>

    <select id="queryKuaishouVideoDetail" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.url,
               t1.cover_url                                                                                   as coverUrl,
               t.id                                                                                           as materialId,
               t.material_name                                                                                as materialName,
               t2.`second`,
               '快手'                                                                                           as madia,
               GROUP_CONCAT(DISTINCT (SELECT project_name from ctop_project where id = t.project_id limit 1)) as projectName,
               t.create_time                                                                                  as createTime,
               t2.size,
               t1.channel_name                                                                                as channelType,
               t1.`video_code` as 'code',
               ''                                                                                             as materialType,
               t2.height,
               t2.width,
               '北京汇创思拓数字科技有限公司'                                                                               as supplier,
               IFNULL(t1.design_team_leader_name, '')                                                         as leaderName,
               IFNULL(t1.clip_name, '')                                                                          clipName,
               IFNULL(t1.shot_name, '')                                                                          shotName,
               IFNULL(t1.plan_name, '')                                                                          planName
        FROM ctop_etl_kuaishou_video_info t1
                 LEFT JOIN ctop_material_info t ON t.`code` = t1.video_code
                 LEFT JOIN ctop_material_parameter t2 on t.`code` = t2.material_id
        where t1.video_code = #{md5}
        GROUP BY t1.video_code
    </select>

    <select id="queryBytedanceVideoChat" resultType="com.alibaba.fastjson.JSONObject">
        SELECT ifnull(sum(cost), 0)                AS cost,
               ifnull(SUM(material_show), 0)       as aclick,
               ifnull(SUM(active), 0)              as activation,
               DATE_FORMAT(stat_datetime, '%Y-%m') AS statDate
        FROM etl_report_bytedance_video
        WHERE md5 = #{md5}
        GROUP BY DATE_FORMAT(stat_datetime, '%Y-%m')
        ORDER BY stat_datetime ASC
    </select>

    <select id="queryKuaishouVideoChat" resultType="com.alibaba.fastjson.JSONObject">
        SELECT ifnull(sum(charge), 0)          AS cost,
               ifnull(SUM(aclick), 0)          as aclick,
               ifnull(SUM(activation), 0)      as activation,
               DATE_FORMAT(stat_date, '%Y-%m') AS statDate
        FROM ctop_etl_kuaishou_account_material_report_daily
        WHERE signature = #{md5}
        GROUP BY DATE_FORMAT(stat_date, '%Y-%m')
        ORDER BY stat_date
    </select>

    <select id="queryBytedanceProjectIdByMd5" resultType="java.lang.Long">
        SELECT project_id
        FROM ctop_material_info
        WHERE code = #{md5}
          AND project_id IS NOT NULL LIMIT 1
    </select>

    <select id="queryKuaishouProjectIdByMd5" resultType="java.lang.Long">
        SELECT project_id
        FROM ctop_etl_kuaishou_account_material_report_daily
        WHERE signature = #{md5}
          AND project_id IS NOT NULL LIMIT 1
    </select>

    <select id="queryBytedanceCostByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select cost
        from (
                 SELECT ifnull(ROUND(sum(cost), 2), 0) cost,
                        1 as                           num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(ROUND(MAX(cost), 2), 2) top,
                        2 as                           num
                 FROM (
                          SELECT sum(cost) cost
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryBytedanceClickByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select click
        from (
                 SELECT ifnull(ROUND(sum(click), 2), 0) click,
                        1 as                            num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(ROUND(MAX(click), 2), 0) top,
                        2 as                            num
                 FROM (
                          SELECT sum(click) click
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryBytedanceMaterialShowByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select materialShow
        from (
                 SELECT ifnull(sum(material_show), 0) materialShow,
                        1 as                          num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(MAX(materialShow), 0) top,
                        2 as                         num
                 FROM (
                          SELECT sum(material_show) materialShow
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num

    </select>

    <select id="queryBytedancePlay100RateByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select play100Rate
        from (
                 SELECT ifnull(round(sum(play100_feed_break) / sum(material_show), 2), 0) play100Rate,
                        1 as                                                              num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(MAX(play100Rate), 0) top,
                        2 as                        num
                 FROM (
                          SELECT round(sum(play100_feed_break) / sum(material_show), 2) play100Rate
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryBytedanceLikeMaterialByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select likeMaterial
        from (
                 SELECT ifnull(sum(like_material), 0) likeMaterial,
                        1 as                          num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(MAX(likeMaterial), 0) top,
                        2 as                         num
                 FROM (
                          SELECT sum(like_material) likeMaterial
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryBytedanceCommentMaterialByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select commentMaterial
        from (
                 SELECT ifnull(sum(comment_material), 0) commentMaterial,
                        1 as                             num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(MAX(commentMaterial), 0) top,
                        2 as                            num
                 FROM (
                          SELECT sum(comment_material) commentMaterial
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryBytedanceShareMaterialByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select shareMaterial
        from (
                 SELECT ifnull(sum(share_material), 0) shareMaterial,
                        1 as                           num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(MAX(shareMaterial), 0) top,
                        2 as                          num
                 FROM (
                          SELECT sum(share_material) shareMaterial
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryBytedanceFollowByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select follow
        from (
                 SELECT ifnull(sum(follow), 0) follow,
                        1 as                   num
                 FROM etl_report_bytedance_video
                 WHERE md5 = #{md5}
                 union all
                 SELECT ifnull(MAX(follow), 0) top,
                        2 as                   num
                 FROM (
                          SELECT sum(follow) follow
                          FROM etl_report_bytedance_video t
                                   LEFT JOIN ctop_material_info t1 on t.md5 = t1.code
                          WHERE t1.project_id = #{project}
                          GROUP BY md5
                      ) t
             ) t
        order by num
    </select>

    <select id="queryKuaishouChargeByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select charge
        from (
                 SELECT ROUND(sum(charge), 2) charge,
                        1 as                  num
                 FROM application.kuaishou_material_video_report_daily_dw
                 WHERE signature = #{md5}
                 union all
                 SELECT ROUND(MAX(charge), 2) top,
                        2 as                  num
                 FROM (
                          SELECT sum(charge) charge
                          FROM application.kuaishou_material_video_report_daily_dw
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
             ) t
        order by num
    </select>

    <select id="queryKuaishouPhotoShowByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select photoShow
        from (
                 SELECT sum(`show`) photoShow,
                        1 as        num
                 FROM application.kuaishou_material_video_report_daily_dw
                 WHERE signature = #{md5}
                 union all
                 SELECT MAX(photoShow) top,
                        2 as           num
                 FROM (
                          SELECT sum(`show`) photoShow
                          FROM application.kuaishou_material_video_report_daily_dw
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
                 union all
                 SELECT ROUND(AVG(`show`), 0) average,
                        3 as                  num
                 FROM application.kuaishou_material_video_report_daily_dw
                 WHERE project_id = #{project}
             ) t
        order by num
    </select>

    <select id="queryKuaishouPhotoClickByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select photoClick
        from (
                 SELECT sum(photo_click) photoClick,
                        1 as             num
                 FROM ctop_etl_kuaishou_account_material_report_daily
                 WHERE signature = #{md5}
                 union all
                 SELECT MAX(photoClick) top,
                        2 as            num
                 FROM (
                          SELECT sum(photo_click) photoClick
                          FROM ctop_etl_kuaishou_account_material_report_daily
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
                 union all
                 SELECT ROUND(AVG(photo_click), 0) average,
                        3 as                       num
                 FROM ctop_etl_kuaishou_account_material_report_daily
                 WHERE project_id = #{project}
             ) t
        order by num
    </select>

    <select id="queryKuaishouAClickByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select aclick
        from (
                 SELECT sum(aclick) aclick,
                        1 as        num
                 FROM application.kuaishou_material_video_report_daily_dw
                 WHERE signature = #{md5}
                 union all
                 SELECT MAX(aclick) top,
                        2 as        num
                 FROM (
                          SELECT sum(aclick) aclick
                          FROM application.kuaishou_material_video_report_daily_dw
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
             ) t
        order by num
    </select>

    <select id="queryKuaishouBClickByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select bclick
        from (
                 SELECT sum(bclick) bclick,
                        1 as        num
                 FROM ctop_etl_kuaishou_account_material_report_daily
                 WHERE signature = #{md5}
                 union all
                 SELECT MAX(bclick) top,
                        2 as        num
                 FROM (
                          SELECT sum(bclick) bclick
                          FROM ctop_etl_kuaishou_account_material_report_daily
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
                 union all
                 SELECT ROUND(AVG(bclick), 0) average,
                        3 as                  num
                 FROM ctop_etl_kuaishou_account_material_report_daily
                 WHERE project_id = #{project}
             ) t
        order by num
    </select>

    <select id="queryKuaishouActivationByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select activation
        from (
                 SELECT sum(activation) activation,
                        1 as            num
                 FROM application.kuaishou_material_video_report_daily_dw
                 WHERE signature = #{md5}
                 union all
                 SELECT MAX(activation) top,
                        2 as            num
                 FROM (
                          SELECT sum(activation) activation
                          FROM application.kuaishou_material_video_report_daily_dw
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
             ) t
        order by num
    </select>

    <select id="queryKuaishouPlay3sRateByMd5" resultType="com.alibaba.fastjson.JSONObject">
        select play3sRate
        from (
                 SELECT round(sum(play_3s_ratio * aclick) / sum(aclick), 2) play3sRate,
                        1 as                                                num
                 FROM application.kuaishou_material_video_report_daily_dw
                 WHERE signature = #{md5}
                 union all
                 SELECT ROUND(MAX(play3sRate), 2) top,
                        2 as                      num
                 FROM (
                          SELECT sum(play_3s_ratio * aclick) / sum(aclick) play3sRate
                          FROM application.kuaishou_material_video_report_daily_dw
                          WHERE project_id = #{project}
                          GROUP BY signature
                      ) t
             ) t
        order by num
    </select>

    <select id="populationAnalysisList" resultType="java.util.Map">
        select t2.gender,
               CASE t2.age
                   WHEN '18-23岁|24-30岁|31-40岁' THEN '18-40'
                   WHEN '24-30岁|31-40岁|41-49岁' THEN '24-49'
                   WHEN '31-40岁|41-49岁|50+岁' THEN '31+'
                   WHEN '不限' THEN '不限'
                   ELSE null END as                                 age,
               case t2.target_type
                   when 'convert_ratio' then CONCAT(IFNULL(ROUND(t2.combine_estimate_prob * 100, 2), 0), '%')
                   else 0 end 'convertRatio',
               (SELECT case t.target_type
                           when 'action_ratio' then CONCAT(IFNULL(ROUND(t.combine_estimate_prob * 100, 2), 0), '%')
                           else 0 end 'actionRatio'
                from ctop_ai_kuaishou_signature_recommended_target_combine t
                where t.signature = #{signature}
                  and t.target_type = 'action_ratio'
                  and t.age = t2.age
                  and t.gender = t2.gender
                ORDER BY t.stat_date DESC, t.gender DESC, t.age ASC LIMIT 0,
               1 ) as actionRatio
        from
            ctop_ai_kuaishou_signature_recommended_target_combine t2
        where t2.signature=#{signature} and t2.target_type='convert_ratio'
        ORDER BY t2.stat_date DESC, t2.gender DESC, t2.age ASC LIMIT 0, 12
    </select>

    <select id="getActualProb" resultType="String">
        SELECT DISTINCT CONCAT(ROUND(actual_prob * 100, 2), '%')
        from ctop_ai_kuaishou_signature_recommended_target_combine t
        where t.signature = #{signature}
          and target_type = #{targetType}
        ORDER BY stat_date DESC LIMIT 0,1
    </select>

    <select id="populationAnalysisChart" resultType="java.util.Map">

        SELECT t2.crowd_coverage_cnt as 'crowdCoverageCnt',
               t2.gender,
               CASE t2.age
                   WHEN '18-23岁|24-30岁|31-40岁' THEN '18-40'
                   WHEN '24-30岁|31-40岁|41-49岁' THEN '24-49'
                   WHEN '31-40岁|41-49岁|50+岁' THEN '31+'
                   WHEN '不限' THEN '不限'
                   ELSE null END as                                 age,
               case t2.target_type
                   when 'convert_ratio' then CONCAT(IFNULL(ROUND(t2.combine_estimate_prob * 100, 2), 0), '%')
                   else 0 end 'convertRatio',
               (SELECT case t.target_type
                           when 'action_ratio' then CONCAT(IFNULL(ROUND(t.combine_estimate_prob * 100, 2), 0), '%')
                           else 0 end 'actionRatio'
                from ctop_ai_kuaishou_signature_recommended_target_combine t
                where t.signature = #{signature}
                  and t.target_type = 'action_ratio'
                  and t.age = t2.age
                  and t.gender = t2.gender
                ORDER BY t.stat_date DESC, t.gender DESC, t.age ASC LIMIT 0,
               1 ) as actionRatio
        from
            ctop_ai_kuaishou_signature_recommended_target_combine t2
        where t2.signature=#{signature} and t2.target_type='convert_ratio'
        ORDER BY t2.stat_date DESC, actionRatio ASC, convertRatio ASC LIMIT 0, 12
    </select>

    <select id="optimalCombination" resultType="java.util.Map">
        SELECT * from (
        select t2.gender,
        CASE t2.age WHEN '18-23岁|24-30岁|31-40岁' THEN '18-40岁'
        WHEN '24-30岁|31-40岁|41-49岁' THEN '24-49岁'
        WHEN '31-40岁|41-49岁|50+岁' THEN '31+岁'
        WHEN '不限' THEN '不限' ELSE null END as age,
        <if test='targetType=="convert_ratio"'>
            case t2.target_type when 'convert_ratio' then t2.combine_estimate_prob else 0 end 'convertRatio'
        </if>
        <if test='targetType=="action_ratio"'>
            case t2.target_type when 'action_ratio' then t2.combine_estimate_prob else 0 end 'actionRatio'
        </if>
        from
        ctop_ai_kuaishou_signature_recommended_target_combine t2 where t2.signature=#{signature} and
        t2.target_type=#{targetType}

        ORDER BY t2.stat_date DESC,t2.gender DESC,t2.age ASC LIMIT 0,12 ) t3 where
        <if test='targetType=="convert_ratio"'>
            t3.convertRatio >
        </if>
        <if test='targetType=="action_ratio"'>
            t3.actionRatio >
        </if>
        (
        SELECT MAX(tt.actual_prob) FROM(
        SELECT actual_prob FROM ctop_ai_kuaishou_signature_recommended_target_combine t
        WHERE t.signature=#{signature} and target_type=#{targetType} ORDER BY stat_date DESC LIMIT 0,12
        ) tt
        ) ORDER BY
        <if test='targetType=="convert_ratio"'>
            convertRatio desc LIMIT 0,1
        </if>
        <if test='targetType=="action_ratio"'>
            actionRatio desc LIMIT 0,1
        </if>

    </select>

    <select id="similarMaterialInfo" resultType="java.util.Map">
        SELECT IFNULL(t2.signature, '')            as signature,
               IFNULL(t2.photo_name, '')           as photoName,
               IFNULL(t2.url, '')                  as url,
               IFNULL(ROUND(SUM(t3.charge), 2), 0) as charge
        from ctop_kuaishou_video_get t2
                 LEFT JOIN ctop_kuaishou_report_daily_material t3 on t2.signature = t3.signature
        where t2.signature = #{signature} LIMIT 0,1
    </select>

    <select id="getSimilarMaterialList" resultType="java.util.Map">
        SELECT IFNULL(t.similar_signature_1, '') as s1,
               IFNULL(t.similar_signature_2, '') as s2,
               IFNULL(t.similar_signature_3, '') as s3,
               IFNULL(t.similar_signature_4, '') as s4,
               IFNULL(t.similar_signature_5, '') as s5
        from ctop_ai_kuaishou_signature_similar t
        where t.signature = #{signature}
        ORDER BY t.stat_date desc LIMIT 0,1
    </select>

    <select id="getCompanyId" resultType="String">
        select company_id
        from user_company
        where user_id = #{userId}
    </select>
    <!-- 头条组内人均消耗-->
    <select id="getBytedanceDesignTeamAvgCost" resultType="java.math.BigDecimal">
        SELECT IFNULL(ROUND(SUM(cost) /
                            ((SELECT COUNT(*) from sys_user where leader_id = #{leaderId} and status = 1) + 1), 2), 0)
        from application.bytedance_material_video_report_daily_dw t
        where t.stat_datetime &gt;= #{startDate}
          and t.stat_datetime &lt;= #{endDate}
          and t.leader_id = #{leaderId}
          and t.company_id = #{companyId}
    </select>
    <!-- 快手组内人均消耗-->
    <select id="getKuaishouDesignTeamAvgCost" resultType="java.math.BigDecimal">
        SELECT IFNULL(ROUND(SUM(charge) /
                            ((SELECT COUNT(*) from sys_user where leader_id = #{leaderId} and status = 1) + 1), 2), 0)
        from application.kuaishou_material_video_report_daily_dw t
        where t.stat_date &gt;= #{startDate}
          and t.stat_date &lt;= #{endDate}
          and t.leader_id = #{leaderId}
          and t.company_id = #{companyId}
    </select>

    <!-- 头条组人效-->
    <select id="getBytedanceDesignTeamGroupWork" resultType="java.math.BigDecimal">
        SELECT IFNULL(ROUND(COUNT(t.material_id) /
                            ((SELECT COUNT(*) from sys_user where leader_id = #{leaderId} and status = 1) + 1), 2), 0)
        from application.app_bytedance_material_info t
        where t.media_time >= #{startDate}
          and t.media_time &lt;= #{endDate}
          and leader_id = #{leaderId}
          and company_id = #{companyId}
    </select>

    <!-- 快手组人效-->
    <select id="getKuaishouDesignTeamGroupWork" resultType="java.math.BigDecimal">
        SELECT IFNULL(ROUND(COUNT(t.material_id) /
                            ((SELECT COUNT(*) from sys_user where leader_id = #{leaderId} and status = 1) + 1), 2), 0)
        from application.app_kuaishou_material_info t
        where t.media_time >= #{startDate}
          and t.media_time &lt;= #{endDate}
          and leader_id = #{leaderId}
          and company_id = #{companyId}
    </select>

    <!-- 头条创新视频数-->
    <select id="getBytedanceDesignTeaminnovativeVideoCount" resultType="int">
        SELECT ifnull(count(*), 0)
        from application.app_bytedance_material_info t
        where t.media_time >= #{startDate}
          and t.media_time &lt;= #{endDate}
          and t.material_innovate = 2
          and leader_id = #{leaderId}
          and company_id = #{companyId}
    </select>

    <!-- 快手创新视频数-->
    <select id="getKuaishouDesignTeaminnovativeVideoCount" resultType="int">
        SELECT ifnull(count(*), 0)
        from application.app_kuaishou_material_info t
        where t.media_time >= #{startDate}
          and t.media_time &lt;= #{endDate}
          and t.material_innovate = 2
          and leader_id = #{leaderId}
          and company_id = #{companyId}
    </select>

    <!-- 头条素材平均消耗-->
    <select id="getBytedanceMaterialCost" resultType="java.math.BigDecimal">
        select IFNULL(ROUND(
                                  (SELECT SUM(t1.cost)
                                   from application.app_bytedance_material_info t
                                            LEFT JOIN application.bytedance_material_video_report_daily_dw t1
                                                      on t.material_id = t1.signature
                                   where t.media_time >= #{startDate}
                                     and t.media_time &lt;= #{endDate}
                                     and t1.leader_id = #{leaderId}
                                     and t1.company_id = #{companyId}
                                     and t1.stat_datetime >= #{startDate}
                                     and t1.stat_datetime &lt;= #{endDate})
                                  /
                                  (SELECT ifnull(count(DISTINCT t.material_id), 0)
                                   from application.app_bytedance_material_info t
                                   where t.media_time >= #{startDate}
                                     and t.media_time &lt;= #{endDate}
                                     and t.leader_id = #{leaderId}
                                     and t.company_id = #{companyId})
                          , 2), 0)


    </select>

    <!-- 快手创新视频消耗-->
    <select id="getKuaishouDesignTeaminnovativeVideoCost" resultType="java.math.BigDecimal">
        SELECT ifnull(SUM(t1.charge), 0)
        from application.app_kuaishou_material_info t
                 LEFT JOIN application.kuaishou_material_video_report_daily_dw t1 on t.material_id = t1.signature
        where t.media_time >= #{startDate}
          and t.media_time &lt;= #{endDate}
          and t.material_innovate = 2
          and t.leader_id = #{leaderId}
          and t.company_id = #{companyId}
          and t1.stat_date >= #{startDate}
          and t1.stat_date &lt;= #{endDate}
    </select>


    <!-- 头条根据标签查询视频数量-->
    <select id="getBytedanceMaterialCountByLable" resultType="com.alibaba.fastjson.JSONObject">

        SELECT MAX(case tt.label WHEN 'making' then tt.size ELSE 0 end ) 'making' ,
        MAX( case tt.label WHEN 'reality' then tt.size ELSE 0 end ) 'reality',
        MAX( case tt.label WHEN 'other' then tt.size ELSE 0 end ) 'other'
        from
        (
        SELECT COUNT( distinct t.material_id) as 'size',
        case when t2.parent_name='其他' then 'other' when t2.parent_name='制作' then 'making' when t2.parent_name='真人' then
        'reality' end as 'label'
        from application.app_bytedance_material_info t
        left join application.app_material_tag_busi_info t2 on t.material_id=t2.material_id
        left join ctop_user_allocation t1 on t.leader_id = t1.user_id
        <where>
            t.media_time >= #{startDate} and t.media_time &lt;= #{endDate}
            and t2.tag_category_id=1
            and t.company_id=#{companyId}
            <if test='dimension=="project" and projects!=null and projects.size>0'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
        </where>
        GROUP BY t2.parent_id
        )tt
    </select>

    <!-- 头条根据标签查询消耗-->
    <select id="getBytedanceMaterialCountByCost" resultType="com.alibaba.fastjson.JSONObject">


        SELECT MAX(case tt.label WHEN 'making' then tt.cost ELSE 0 end ) 'making' ,
        MAX( case tt.label WHEN 'reality' then tt.cost ELSE 0 end ) 'reality',
        MAX( case tt.label WHEN 'other' then tt.cost ELSE 0 end ) 'other'
        from
        (
        SELECT ROUND(SUM(t1.cost),2) as 'cost',
        case when t2.parent_name='其他' then 'other' when t2.parent_name='制作' then 'making' when t2.parent_name='真人' then
        'reality' end as 'label'
        from application.app_bytedance_material_info t
        left join application.app_material_tag_busi_info t2 on t.material_id=t2.material_id
        left join application.bytedance_material_video_report_daily_dw t1 on t.material_id = t1.signature
        <where>
            t1.stat_datetime >= #{startDate} and t1.stat_datetime &lt;= #{endDate}
            and t2.tag_category_id=1
            and t1.company_id=#{companyId}
            <if test='dimension=="project" and projects!=null and projects.size>0'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
        </where>
        GROUP BY t2.parent_id
        ) tt
    </select>


    <!-- 快手根据标签查询视频数量-->
    <select id="getKuaishouMaterialCountByLable" resultType="com.alibaba.fastjson.JSONObject">
        SELECT MAX(case tt.label WHEN 'making' then tt.size ELSE 0 end ) 'making' ,
        MAX( case tt.label WHEN 'reality' then tt.size ELSE 0 end ) 'reality',
        MAX( case tt.label WHEN 'other' then tt.size ELSE 0 end ) 'other'
        from
        (
        SELECT COUNT( distinct t.material_id) as 'size',
        case when t2.parent_name='其他' then 'other' when t2.parent_name='制作' then 'making' when t2.parent_name='真人' then
        'reality' end as 'label'
        from application.app_kuaishou_material_info t
        left join application.app_material_tag_busi_info t2 on t.material_id=t2.material_id
        left join ctop_user_allocation t1 on t.leader_id = t1.user_id
        <where>
            t.media_time >= #{startDate} and t.media_time &lt;= #{endDate}
            and t2.tag_category_id=1
            and t.company_id=#{companyId}
            <if test='dimension=="project" and projects!=null and projects.size>0'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
        </where>
        GROUP BY t2.parent_id
        )tt
    </select>


    <!-- 快手根据标签查询消耗-->
    <select id="getKuaishouMaterialCountByCost" resultType="com.alibaba.fastjson.JSONObject">

        SELECT MAX(case tt.label WHEN 'making' then tt.cost ELSE 0 end ) 'making' ,
        MAX( case tt.label WHEN 'reality' then tt.cost ELSE 0 end ) 'reality',
        MAX( case tt.label WHEN 'other' then tt.cost ELSE 0 end ) 'other'
        from
        (
        SELECT ROUND(SUM(t1.charge),2) as 'cost',
        case when t2.parent_name='其他' then 'other' when t2.parent_name='制作' then 'making' when t2.parent_name='真人' then
        'reality' end as 'label'
        from application.app_kuaishou_material_info t
        left join application.app_material_tag_busi_info t2 on t.material_id=t2.material_id
        left join application.kuaishou_material_video_report_daily_dw t1 on t.material_id = t1.signature

        <where>
            t1.stat_date >= #{startDate} and t1.stat_date &lt;= #{endDate}
            and t2.tag_category_id=1
            and t1.company_id=#{companyId}
            <if test='dimension=="project" and projects!=null and projects.size>0'>
                AND t1.project_id IN
                <foreach collection="projects" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test='dimension=="designTeam" and leaderId!=null'>
                and t.leader_id=#{leaderId}
            </if>
            <if test='dimension=="designer" and designerId!=null'>
                and (t.clip_id=#{designerId} or t.shot_id=#{designerId} or t.plan_id=#{designerId})
            </if>
        </where>
        GROUP BY t2.parent_id
        ) tt
    </select>
    <select id="getRoleCodeByUserId" resultType="java.lang.String">
        select c.role_code
        from sys_user a
                 left join sys_user_role b on a.id = b.user_id
                 left join sys_role c on b.role_id = c.id
        where a.id = #{userId}
    </select>

</mapper>
