<?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="cn.com.ctop.userefficientvideomap.mapper.UserEfficientVideoMapMapper">

    <!-- 获取有效视频以及视频和人的对应关系 -->
    <select id="getUserVideoMap" resultType="cn.com.ctop.userefficientvideomap.entity.EffiVideoDTO">
            select
            a.signature,
            sum(a.charge) as charge,
            d1.id as clip_id,
            d1.realname as clip_name,
            d2.id as shot_id,
            d2.realname as shot_name,
            d3.id as plan_id,
            d3.realname as plan_name,
            d4.id as plane_id,
            d4.realname as plane_name
        from ctop_kuaishou_report_daily_creative a
        left join (select id, create_time ,code from ctop_material_info group by code) b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on c.clip_id = d1.id
        left join sys_user d2 on c.shot_id = d2.id
        left join sys_user d3 on c.plan_id = d3.id
        left join sys_user d4 on c.plane_id = d4.id
        where a.signature is not null and b.id is not null
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &gt;= #{startCalcDate}
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &lt;= #{endCalcDate}
        group by a.signature
        having sum(a.charge) >= 5000
    </select>

    <!-- 根据md5获取有效视频是否存在 -->
    <select id="getEffiVideoCountBySignature" resultType="java.lang.Integer">
            select
            count(1)
        from ctop_user_efficient_video_map
        where efficient_video_signature = #{signature}
        and app_type = #{appType}
    </select>

    <!-- 根据年、季度、userId获取有效视频数量 -->
    <select id="getEffiVideoCountByUserId" resultType="java.lang.Integer">
        select
        count(1)
        from
        ctop_user_efficient_video_map
        where year = #{year}
        and quarter = #{quarter}
        and user_id = #{userId}
        and role_id = #{roleId}
        and app_type = #{appType}
    </select>

    <!-- 按人获取季度总视频数量 -->
    <select id="getQuarterMaterialCountByUserId" resultType="java.lang.Integer">
        select
        count(1)
        from ctop_material_info a
        left join ctop_material_ascription b on a.id = b.material_id
        where date_format(a.create_time,'%Y-%m-%d') &gt;= #{startTime}
        and date_format(a.create_time,'%Y-%m-%d') &lt;= #{endTime}
        and
        <choose>
            <when test=" roleCode == 'plan' ">
                b.plan_id = #{userId}
            </when>
            <when test=" roleCode == 'clip' ">
                b.clip_id = #{userId}
            </when>
            <otherwise>
                b.shot_id = #{userId}
            </otherwise>
        </choose>
    </select>

    <select id="getQuarterVideoCountByRoleCodeAndUserId" resultType="Integer">
        select
        count(1)
        from
        ctop_user_video_map
        where
        year = #{year}
        and quarter = #{quarter}
        and role_id = #{roleCode}
        and user_id = #{userId}
        and app_type = #{appType}
    </select>

    <select id="getQuarterVideoCount" resultType="integer">
            select
            count(1)
        from ctop_user_video_map
        where user_id = #{userId}
        and app_type = #{appType}
        and year = #{year}
        and quarter = #{quarter}
    </select>

    <select id="getQuarterEffiVideoCount" resultType="integer">
            select
            count(1)
        from ctop_user_efficient_video_map
        where user_id = #{userId}
        and app_type = #{appType}
        and year = #{year}
        and quarter = #{quarter}
    </select>

    <select id="getVideoCountByUserId" resultType="integer">
            select
            count(1)
        from
        ctop_user_video_map
        where user_id = #{userId}
            and DATE_FORMAT(calc_date,'%Y-%m-%d') &gt;= #{startDate}
            and DATE_FORMAT(calc_date,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <!-- 根据clip_id获取视频总消耗 -->
    <select id="getTotalCostByClipId" resultType="java.math.BigDecimal">
            select
            sum(a.charge) as charge
        from ctop_kuaishou_report_daily_creative a
        left join ctop_material_info b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on c.clip_id = d1.id
        left join sys_user d2 on c.shot_id = d2.id
        left join sys_user d3 on c.plan_id = d3.id
        left join sys_user d4 on c.plane_id = d4.id
        where a.signature is not null and b.id is not null
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and c.clip_id = #{userId}
    </select>

    <select id="getTotalCostByUserId" resultType="java.math.BigDecimal">
            select
            sum(a.charge) as charge
        from
        ctop_kuaishou_report_daily_creative a
        left join
        (
        select
        video_signature as signature
        from
        ctop_user_video_map
        where user_id = #{userId}
        and DATE_FORMAT(calc_date,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(calc_date,'%Y-%m-%d') &lt;= #{endDate}
        group by video_signature) b
        on a.signature = b.signature
        where
        DATE_FORMAT(a.stat_date,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <select id="getTotalSignatureByUserId" resultType="string">
        select
        video_signature as signature
        from
        ctop_user_video_map
        where user_id = #{userId}
        and DATE_FORMAT(calc_date,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(calc_date,'%Y-%m-%d') &lt;= #{endDate}
        group by video_signature
    </select>

    <select id="getToutiaoSumChargeBySignature" resultType="java.math.BigDecimal">
        select
            sum(a.cost) as charge
        from
        ctop_bytedance_report_creative_daily a
        where a.signature = #{signature}
        and
        DATE_FORMAT(a.stat_datetime,'%Y-%m-%d') &gt;= #{startDate}
        and
        DATE_FORMAT(a.stat_datetime,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <select id="getSumChargeBySignature" resultType="java.math.BigDecimal">
        select
            sum(a.charge) as charge
        from
        ctop_kuaishou_report_daily_creative a
        where a.signature = #{signature}
        and
        DATE_FORMAT(a.stat_date,'%Y-%m-%d') &gt;= #{startDate}
        and
        DATE_FORMAT(a.stat_date,'%Y-%m-%d') &lt;= #{endDate}
    </select>


    <!-- 根据shot_id获取视频总消耗 -->
    <select id="getTotalCostByShotId" resultType="java.math.BigDecimal">
            select
            sum(a.charge) as charge
        from ctop_kuaishou_report_daily_creative a
        left join ctop_material_info b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on c.clip_id = d1.id
        left join sys_user d2 on c.shot_id = d2.id
        left join sys_user d3 on c.plan_id = d3.id
        left join sys_user d4 on c.plane_id = d4.id
        where a.signature is not null and b.id is not null
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and c.shot_id = #{userId}
    </select>

    <select id="getQuarterSignature" resultType="string">
        select
            video_signature
            from
            ctop_user_video_map
            where year = #{year} and quarter = #{quarter}
            and user_id = #{userId} and role_id = #{roleCode}
    </select>

    <select id="getQuarterTotalCostByCode" resultType="java.math.BigDecimal">
        select
        sum(a.charge) as charge
        from ctop_kuaishou_report_daily_creative a
        where signature = #{signature}
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <select id="getToutiaoQuarterTotalCostByCode" resultType="java.math.BigDecimal">
        select
        sum(a.cost) as charge
        from ctop_bytedance_report_creative_daily a
        where signature = #{signature}
        and DATE_FORMAT(a.stat_datetime,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(a.stat_datetime,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <!-- 根据userId和角色名获取其在本季度所有视频总消耗 -->
    <select id="getQuarterTotalCostByRoleCode" resultType="java.math.BigDecimal">
        select
        sum(a.charge) as charge
        from ctop_kuaishou_report_daily_creative a
        left join  (
            select
            video_signature
            from
            ctop_user_video_map
            where year = #{year} and quarter = #{quarter}
            and user_id = #{userId} and role_id = #{roleCode}) b on a.signature = b.video_signature
        where b.video_signature is not null
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(a.stat_date,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <!-- 根据plan_id获取视频总消耗 -->
    <select id="getTotalCostByPlanId" resultType="java.math.BigDecimal">
            select
            sum(a.charge) as charge
        from ctop_kuaishou_report_daily_creative a
        left join ctop_material_info b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on c.clip_id = d1.id
        left join sys_user d2 on c.shot_id = d2.id
        left join sys_user d3 on c.plan_id = d3.id
        left join sys_user d4 on c.plane_id = d4.id
        where a.signature is not null and b.id is not null
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and c.plan_id = #{userId}
    </select>

    <!-- 根据plane_id获取视频总消耗 -->
    <select id="getTotalCostByPlaneId" resultType="java.math.BigDecimal">
            select
            sum(a.charge) as charge
        from ctop_kuaishou_report_daily_creative a
        left join ctop_material_info b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on c.clip_id = d1.id
        left join sys_user d2 on c.shot_id = d2.id
        left join sys_user d3 on c.plan_id = d3.id
        left join sys_user d4 on c.plane_id = d4.id
        where a.signature is not null and b.id is not null
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
        where c.plane_id = #{userId}
    </select>

    <select id="getProjectTotalCostByUserId" resultType="java.math.BigDecimal">
        select
        sum(dailiAccount.charge) as charge
        from
        ctop_kuaishou_report_daily_account dailiAccount
        left join
        ctop_user_allocation allocation on dailiAccount.account_id = allocation.account_id
        left join
        ctop_project_member projectMember on projectMember.project_id = allocation.project_id
        where
        projectMember.user_id = #{userId}
        AND
        dailiAccount.stat_date &gt;= #{startDate}
        AND
        dailiAccount.stat_date &lt; #{endDate}
    </select>

    <!-- 获取这个人所在的所有项目 -->
    <select id="getProjectsByUserId" resultType="cn.com.ctop.userefficientvideomap.entity.ProjectDTO">
        select
        project_id as projectId,
        project_name as projectName
        from
        ctop_project_member
        where
        user_id = #{userId}
    </select>

    <!-- 根据项目id获取平面设计师的数量 -->
    <select id="getPlaneCountByProjectId" resultType="java.lang.Integer">
        select
        count(1)
        from ctop_project_member
        where project_id = #{projectId}
        and role_code = 'plane';
    </select>

    <!-- 根据获取的id -->
    <select id="getPlaneCountByUserId" resultType="java.lang.Integer">
        select
        count(1)
        from sys_user as u
        left join sys_user_role ur where u.user_id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        where u.org_code = (select org_code from sys_user u2 where id = #{userId})
        and role_code = 'plane'
    </select>

    <select id="designMaterialInfoList"
            resultType="cn.com.ctop.userefficientvideomap.entity.DesignMaterialInfo">    <!-- 素材、素材跑量、封面点击数、封面曝光数、行为数 -->
        select
        b.id as id,
        b.url as videoUrl, <!-- 素材url -->
        sum(a.photo_show) as pageExposureCount, <!-- 封面曝光数 -->
        sum(a.photo_click) as pageClickCount, <!-- 封面点击数 -->
        sum(a.aclick) as materialExposureCount, <!-- 素材曝光数 -->
        sum(a.bclick) as behaviorCount,<!-- 行为数 -->
        sum(a.charge) as totalCost <!-- 素材跑量 -->
        from
        ctop_material_info b
        left join ctop_material_ascription c on b.id = c.material_id
        left join ctop_kuaishou_report_daily_creative a on b.code = a.signature
        where
        <choose>
            <when test=" roleCode == 'plan' ">
                c.plan_id = #{userId}
            </when>
            <when test=" roleCode == 'clip' ">
                c.clip_id = #{userId}
            </when>
            <otherwise>
                c.shot_id = #{userId}
            </otherwise>
        </choose>
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
        group by b.id
    </select>

    <!-- 根据material_info的id查询出素材账户、投放账户、跑量 -->
    <select id="getMaterialAccountInfoByMaterialId"
            resultType="cn.com.ctop.userefficientvideomap.entity.DesignMaterialInfo">
        select
        a.account_id as accountId,
        <!-- a.account_name as accountName, -->
        '快手' as platform,
        sum(charge) as totalCost
        from
        ctop_material_info b
        left join ctop_material_ascription c on b.id = c.material_id
        left join ctop_kuaishou_report_daily_creative a on b.code = a.signature
        where
        b.id = #{materialId}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(b.create_time,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <!-- 获取这个人所在的所有项目 -->
    <select id="getProjects" resultType="cn.com.ctop.userefficientvideomap.entity.ProjectDTO">
        select
        id as projectId,
        project_name as projectName
        from
        ctop_project
        where
        media_id = #{mediaId}
    </select>

    <!-- 根据projectId计算项目总消耗 -->
    <select id="getToutiaoProjectQuarterCostById" resultType="java.math.BigDecimal">
            select
            sum(dailiAccount.cost) as charge
            from
            ctop_bytedance_report_advertiser_daily dailiAccount
            left join
            ctop_user_allocation allocation on dailiAccount.advertiser_id = allocation.account_id
        where
        allocation.project_id = #{projectId}
        AND
        date_format(dailiAccount.stat_datetime,'%Y-%m-%d') &gt;= #{startDate}
        AND
        date_format(dailiAccount.stat_datetime,'%Y-%m-%d') &lt; #{endDate}
    </select>

    <!-- 根据projectId计算项目总消耗 -->
    <select id="getProjectQuarterCostById" resultType="java.math.BigDecimal">
            select
            sum(dailiAccount.charge) as charge
            from
            ctop_kuaishou_report_daily_creative dailiAccount
            left join
            ctop_user_allocation allocation on dailiAccount.account_id = allocation.account_id
        where
        allocation.project_id = #{projectId}
        AND
        dailiAccount.stat_date &gt;= #{startDate}
        AND
        dailiAccount.stat_date &lt; #{endDate}
    </select>

    <select id="getPlaneByProjectId" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        a.user_name as userName,
        b.id as roleId,
        b.role_name as roleName
        from
        ctop_project_member a
        left join sys_role b on a.role_code = b.role_code
        where
        a.project_id = #{projectId} and
        a.role_code in ('planeLeader','plane')
    </select>

    <!-- 获取快手设计师leader的信息 -->
    <select id="getKuaishouDesignLeaderList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        '8d3e2ed58bdedf17716f698ea674da27' as roleId,
        '设计组长' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = '8d3e2ed58bdedf17716f698ea674da27'   <!-- 设计师 -->
        )
        and b.parent_id = '4e939124a5f541c88838e49d2a3ea72b'  <!-- 快手设计部 -->
    </select>

    <!-- 获取快手编导列表 -->
    <select id="getKuaishouPlanList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        '0214283aa16f943efbb149ea4bb18f18' as roleId,
        '策划' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = '0214283aa16f943efbb149ea4bb18f18'    <!-- 编导 -->
        )
        and b.parent_id = '4e939124a5f541c88838e49d2a3ea72b'  <!-- 快手设计部 -->
    </select>

    <!-- 获取快手拍摄列表 -->
    <select id="getKuaishouShotList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        '7bff9afed625aeeabca6bffe3c189183' as roleId,
        '拍摄' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = '7bff9afed625aeeabca6bffe3c189183'   <!-- 拍摄 -->
        )
        and b.parent_id = '4e939124a5f541c88838e49d2a3ea72b'   <!-- 快手设计部 -->
    </select>

    <!-- 获取快手剪辑列表 -->
    <select id="getKuaishouClipList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        'f38d8d70cf7ec50d5357a749e4dbf8ee' as roleId,
        '剪辑' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = 'f38d8d70cf7ec50d5357a749e4dbf8ee'   <!-- 拍摄 -->
        )
        and b.parent_id = '4e939124a5f541c88838e49d2a3ea72b'   <!-- 快手设计部 -->
    </select>

    <!-- 根据部门id -->
    <select id="getClip" resultType="string">
        select
        a.user_id
        from
        sys_user_depart a
        left join sys_user_role b on a.user_id = b.user_id
        left join sys_role c on b.role_id = c.id
        where a.dep_id = #{depId}
        and c.role_code = 'clip'
    </select>

    <select id="getClipQuarterCost" resultType="java.math.BigDecimal">
            select
            total_cost
        from
        ctop_performance
        where
        app_type = #{appType}
        and quarter = #{quarter}
        and year = #{year}
        and user_id = #{userId}
        limit 1
    </select>

    <!-- 获取toutiao设计师leader的信息 -->
    <select id="getToutiaoDesignLeaderList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        '8d3e2ed58bdedf17716f698ea674da27' as roleId,
        '设计组长' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = '8d3e2ed58bdedf17716f698ea674da27'   <!-- 设计师 -->
        )
        and b.parent_id = '26c10ecb5e0d416bb01f8ff983f4588c'  <!-- 头条设计部 -->
    </select>

    <!-- 获取toutiao编导列表 -->
    <select id="getToutiaoPlanList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        '0214283aa16f943efbb149ea4bb18f18' as roleId,
        '策划' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = '0214283aa16f943efbb149ea4bb18f18'    <!-- 编导 -->
        )
        and b.parent_id = '26c10ecb5e0d416bb01f8ff983f4588c'  <!-- 头条设计部 -->
    </select>

    <!-- 获取toutiao拍摄列表 -->
    <select id="getToutiaoShotList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        '7bff9afed625aeeabca6bffe3c189183' as roleId,
        '拍摄' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = '7bff9afed625aeeabca6bffe3c189183'   <!-- 拍摄 -->
        )
        and b.parent_id = '26c10ecb5e0d416bb01f8ff983f4588c'   <!-- 头条设计部 -->
    </select>

    <!-- 获取toutiao剪辑列表 -->
    <select id="getToutiaoClipList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.user_id as userId,
        c.realname as userName,
        'f38d8d70cf7ec50d5357a749e4dbf8ee' as roleId,
        '剪辑' as roleName,
        a.dep_id as depId
        FROM sys_user_depart a
        left join sys_user c on a.user_id = c.id
        left join sys_depart b on a.dep_id = b.id
        WHERE a.user_id in(
        select
        user_id
        from sys_user_role
        WHERE role_id = 'f38d8d70cf7ec50d5357a749e4dbf8ee'   <!-- 拍摄 -->
        )
        and b.parent_id = '26c10ecb5e0d416bb01f8ff983f4588c'   <!-- 头条设计部 -->
    </select>

    <!-- 头条有效视频用户关系绑定 -->
    <select id="getToutiaoUserVideoMap" resultType="cn.com.ctop.userefficientvideomap.entity.EffiVideoDTO">
        select
        c.signature as signature,
        sum(a.cost) as charge, <!-- 总花费 -->
        f1.id as clipId,
        f1.realname as clipName,
        f2.id as shotId,
        f2.realname as shotName,
        f3.id as planId,
        f3.realname as planName,
        f4.id as planeId,
        f4.realname as planeName
        from
        ctop_bytedance_report_creative_daily a
        left join ctop_bytedance_creative b on a.creative_id = b.id
        left join ctop_bytedance_video_info c on b.video_id = c.id
        left join (select id, create_time ,code from ctop_material_info group by code) d on c.signature = d.code
        left join ctop_material_ascription e on d.id = e.material_id
        left join sys_user f1 on e.clip_id = f1.id
        left join sys_user f2 on e.shot_id = f2.id
        left join sys_user f3 on e.plan_id = f3.id
        left join sys_user f4 on e.plane_id = f4.id
        where
        c.signature is not null and d.id is not null and
        date_format(a.stat_datetime, '%Y-%m-%d') &gt;= #{startTime} and
        date_format(a.stat_datetime, '%Y-%m-%d') &lt;= #{endTime} and
        date_format(d.create_time, '%Y-%m-%d') &gt;= #{startDate} and
        date_format(d.create_time, '%Y-%m-%d') &lt;= #{endDate}
        group by c.signature
        having sum(a.cost) >= 5000
    </select>

    <select id="getToutiaoQuarterTotalCostByRoleCode" resultType="java.math.BigDecimal">
    select
    sum(a.cost) as charge
    from ctop_bytedance_report_creative_daily a
    left join  (
                select
                video_signature
                from
                ctop_user_video_map
                where year =#{year}
                and quarter =#{quarter}
                and user_id = #{userId}
                and role_id = #{roleCode}
                ) b on on a.signature = b.video_signature
    where b.video_signature is not null
    and DATE_FORMAT(a.stat_datetime,'%Y-%m-%d') &gt;= #{startDate}
    and DATE_FORMAT(a.stat_datetime,'%Y-%m-%d') &lt;= #{endDate}
    </select>

    <!-- 头条根据plan_id获取视频总消耗 -->
    <select id="toutiaoGetTotalCostByPlanId" resultType="java.math.BigDecimal">
            select
            sum(a.cost) as charge
        from    ctop_bytedance_report_creative_daily a
        left join ctop_bytedance_creative b on a.creative_id = b.id
        left join ctop_bytedance_video_info c on b.video_id = c.id
        left join ctop_material_info d on c.signature = d.code
        left join ctop_material_ascription e on d.id = e.material_id
        where c.signature is not null and d.id is not null
        and DATE_FORMAT(d.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(d.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and e.plan_id = #{userId}
    </select>

    <!-- 头条根据clip_id获取视频总消耗 -->
    <select id="toutiaoGetTotalCostByClipId" resultType="java.math.BigDecimal">
            select
            sum(a.cost) as charge
         from   ctop_bytedance_report_creative_daily a
        left join ctop_bytedance_creative b on a.creative_id = b.id
        left join ctop_bytedance_video_info c on b.video_id = c.id
        left join ctop_material_info d on c.signature = d.code
        left join ctop_material_ascription e on d.id = e.material_id
        where c.signature is not null and d.id is not null
        and DATE_FORMAT(d.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(d.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and e.clip_id = #{userId}
    </select>

    <!-- 头条根据shot_id获取视频总消耗 -->
    <select id="toutiaoGetTotalCostByShotId" resultType="java.math.BigDecimal">
            select
            sum(a.cost) as charge
           from ctop_bytedance_report_creative_daily a
        left join ctop_bytedance_creative b on a.creative_id = b.id
        left join ctop_bytedance_video_info c on b.video_id = c.id
        left join ctop_material_info d on c.signature = d.code
        left join ctop_material_ascription e on d.id = e.material_id
        where c.signature is not null and d.id is not null
        and DATE_FORMAT(d.create_time,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(d.create_time,'%Y-%m-%d') &lt;= #{endDate}
        and e.shot_id = #{userId}
    </select>

    <!-- 根据year、quarter、user_id获取季度总绩效 -->
    <select id="getQuarterPerformance" resultType="java.math.BigDecimal">
        select
        total_performance
        from
        ctop_performance
        where
        user_id = #{userId}
        and
        year = #{year}
        and
        quarter = #{quarter}
        and
        app_type = #{appType}
        limit 1
    </select>

    <!-- 根据year、quarter、user_id获取季度总消耗 -->
    <select id="getQuarterCost" resultType="java.math.BigDecimal">
        select
        total_cost
        from
        ctop_performance
        where
        user_id = #{userId}
        and
        year = #{year}
        and
        quarter = #{quarter}
        and
        app_type = #{appType}
        limit 1
    </select>

    <select id="getYunyingQuarterCost" resultType="java.math.BigDecimal">
            select
            total_cost
        from ctop_performance_optimizer
        where user_id = #{userId}
        and app_type = #{appType}
        and year = #{year}
        and quarter = #{quarter}
    </select>

    <select id="getYunyingQuarterPerformance" resultType="java.math.BigDecimal">
            select
            total_royalty
        from ctop_performance_optimizer
        where user_id = #{userId}
        and app_type = #{appType}
        and year = #{year}
        and quarter = #{quarter}
    </select>

    <select id="getOrgCodeByProjectId" resultType="string">
            select
            c.org_code
        from
        ctop_project a
        left join sys_user_depart b on a.design_responsible_id = b.user_id
        left join sys_depart c on b.dep_id = c.id
        where
        a.id = #{projectId}
    </select>

    <!-- 根据userId获取depart_id -->
    <select id="getDepartIdByUserId" resultType="string">
        select
        dep_id
        from
        sys_user_depart
        where
        user_id = #{userId}
    </select>

    <!-- 根据departId获取部门下所有的人 -->
    <select id="getUserListByDepartId" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
            select
            b.id as userId,
            b.realname as realname,
            d.id as roleId ,
            d.role_name as roleName,
			d.role_code as roleCode,
			e.parent_id as parentId,
			e.depart_name as departName
        from
        sys_user_depart a
        left join sys_user b on a.user_id = b.id
        left join sys_user_role c on b.id = c.user_id
        left join sys_role d on c.role_id = d.id
        left join sys_depart e on a.dep_id = e.id
        where a.dep_id = #{departId}
    </select>

    <!-- 根据userId获取departName -->
    <select id="getDepartNameByUserId" resultType="string">
        select
        b.depart_name
        from
        sys_user_depart a
        left join sys_depart b on a.dep_id = b.id
        where a.user_id = #{userId}
    </select>

    <!-- 获取部门的上一级部门 -->
    <select id="getParentIdByDepartId" resultType="string">
            select
            parent_id
        from
        sys_depart
        where
        id = #{departId}
    </select>

    <select id="getPlanePerformanceInfo" resultType="cn.com.ctop.userefficientvideomap.entity.PlaneProjectCostDTO">
        select
        b.project_name as projectName,
        a.year as year,
        a.quarter as quarter,
        a.app_type as appType,
        a.project_performance as projectPerformance,
        a.project_total_cost as projectTotalCost
        from
        ctop_plane_project_cost a
        left join ctop_project b on a.project_id = b.id
        where
        a.app_type = #{appType} and
        a.year = #{year} and
        a.quarter = #{quarter} and
        a.user_id = #{userId}
    </select>

    <select id="getUserPerformanceList" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
            select
            b.id as userId,
            b.realname as realname,
            c.id as roleId,
            c.role_name as roleName,
            a.year as year,
            a.quarter as quarter,
            sum(a.total_performance) as effiRate,
            e.depart_name as departName
        from
        ctop_performance a
        left join sys_user b on a.user_id = b.id
        left join sys_role c on a.role_id = c.id
        left join sys_user_depart d on a.user_id = d.user_id
        left join sys_depart e on d.dep_id = e.id
        where
        a.year = #{year} and
        a.quarter = #{quarter} and
        a.app_type = #{appType}
        group by a.user_id
    </select>

    <select id="getUserIdListByDepId" resultType="string">
            select
            user_id
        from
        sys_user_depart
        where
        dep_id = #{depId}
    </select>

    <!-- 更新快手的有效视频 -->
    <select id="updateKuaishouEffiType">
        update ctop_material_info
        set kuaishou_effi_type = #{kuaishouEffiType}
        where code = #{signature}
    </select>

    <!-- 更新头条的有效视频 -->
    <select id="updateToutiaoEffiType">
        update ctop_material_info
        set toutiao_effi_type = #{toutiaoEffiType}
        where code = #{signature}
    </select>

    <!-- 获取北京快手设计部所有编导、剪辑、拍摄的信息 -->
    <select id="getKuaishouDepartUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '4e939124a5f541c88838e49d2a3ea72b'  <!-- 快手设计部 -->
        and r.role_code in ('plan','clip','shot')
    </select>

    <!-- 获取北京快手设计部所有人员的信息 -->
    <select id="getKuaishouDepartAllUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '4e939124a5f541c88838e49d2a3ea72b'  <!-- 快手设计部 -->
    </select>

    <!-- 获取北京头条设计部所有编导、剪辑、拍摄的信息 -->
    <select id="getToutiaoDepartUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '26c10ecb5e0d416bb01f8ff983f4588c'  <!-- 头条设计部 -->
        and r.role_code in ('plan','clip','shot')
    </select>

    <!-- 获取北京头条设计部所有编导、剪辑、拍摄的信息 -->
    <select id="getToutiaoDepartAllUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '26c10ecb5e0d416bb01f8ff983f4588c'  <!-- 头条设计部 -->
    </select>

    <!-- 获取北京快手运营部所有优化师得信息 -->
    <select id="getKuaishouYunyingDepartUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '024b0dc0840045afb048022a8c61253f'  <!-- 快手运营部 -->
        and (r.role_code in ('operator','kuaishouOperationManager','operationAssistant')
        or realname = '郭浩')
    </select>

    <!-- 获取北京快手运营部所有人的信息 -->
    <select id="getAllKuaishouYunyingDepartUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '024b0dc0840045afb048022a8c61253f'  <!-- 快手运营部 -->
    </select>

    <!-- 获取北京头条运营部所有优化师得信息 -->
    <select id="getToutiaoYunyingDepartUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        <!-- select u.id as userId,
         u.realname as realname,
         r.role_name as roleName,
         r.role_code as roleCode,
         r.id as roleId
         FROM sys_user_depart ud
         left join sys_depart b on ud.dep_id = b.id
         left join sys_user u on ud.user_id = u.id
         left join sys_user_role ur on u.id = ur.user_id
         left join sys_role r on ur.role_id = r.id
         WHERE b.parent_id = 'c869de6bedc44a50803e45a4611df05e'
         and r.role_code = 'operator' -->

        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = 'c869de6bedc44a50803e45a4611df05e'  <!-- 头条运营部 -->
        and r.role_code in ('operator','kuaishouOperationManager','operationAssistant')
    </select>

    <!-- 获取北京头条运营部所有优化师得信息 -->
    <select id="getAllToutiaoYunyingDepartUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = 'c869de6bedc44a50803e45a4611df05e'  <!-- 头条运营部 -->
    </select>

    <!-- 获取快手单个优化师当前账户下的总消耗 -->
    <select id="getKuaishouYunyingTotalCost" resultType="java.math.BigDecimal">
        SELECT
            SUM(dailiAccount.charge) as totalCost
        FROM
            ctop_kuaishou_report_daily_account dailiAccount
        LEFT JOIN
            ctop_user_allocation allocation ON dailiAccount.account_id = allocation.account_id
        WHERE
            allocation.user_id = #{userId}
        AND
            dailiAccount.stat_date &gt;= #{startDate}
        AND
            dailiAccount.stat_date &lt;= #{endDate}
        AND
            allocation.media_id = #{appType}
    </select>

    <!-- 获取头条单个优化师当前账户下的总消耗 -->
    <select id="getToutiaoYunyingTotalCost" resultType="java.math.BigDecimal">
        SELECT
            SUM(dailiAccount.cost) as totalCost
        FROM
            ctop_bytedance_report_advertiser_daily dailiAccount
        LEFT JOIN
            ctop_user_allocation allocation ON dailiAccount.advertiser_id = allocation.account_id
        WHERE
            allocation.user_id = #{userId}
        AND
            dailiAccount.stat_datetime &gt;= #{startDate}
        AND
            dailiAccount.stat_datetime &lt;= #{endDate}
        AND
            allocation.media_id = #{appType}
    </select>

    <!-- 获取头条单个优化师其下所有的账户下当前的消耗 -->
    <select id="getToutiaoAccountTotalCost" resultType="cn.com.ctop.userefficientvideomap.entity.AccountDTO">
        select
        SUM(dailiAccount.cost) as totalCost,  <!-- 账户当前消耗 -->
        allocation.account_id as accountId,  <!-- 账户id -->
        allocation.user_name as userName,  <!-- 优化师 -->
        allocation.advertiser_name as advertiserName,  <!-- 广告主名称 -->
        allocation.project_name as projectName,  <!-- 项目名称 -->
        allocation.auth_name as authName <!-- 授权名称 -->
        from
        ctop_bytedance_report_advertiser_daily dailiAccount
        LEFT JOIN
        ctop_user_allocation allocation ON dailiAccount.advertiser_id = allocation.account_id
        WHERE
        allocation.user_id = #{userId}
        AND
        dailiAccount.stat_datetime &gt;= #{startDate}
        AND
        dailiAccount.stat_datetime &lt;= #{endDate}
        AND
        allocation.media_id = #{appType}
        group by dailiAccount.advertiser_id
    </select>

    <!-- 获取快手单个优化师其下所有账户下当前的消耗 -->
    <select id="getKuaishouAccountTotalCost" resultType="cn.com.ctop.userefficientvideomap.entity.AccountDTO">
        SELECT
        SUM(dailiAccount.charge) as totalCost,  <!-- 账户当前消耗 -->
        allocation.account_id as accountId,  <!-- 账户id -->
        allocation.user_name as userName,  <!-- 优化师 -->
        allocation.advertiser_name as advertiserName,  <!-- 广告主名称 -->
        allocation.project_name as projectName,  <!-- 项目名称 -->
        allocation.auth_name as authName <!-- 授权名称 -->
        FROM
        ctop_kuaishou_report_daily_account dailiAccount
        LEFT JOIN
        ctop_user_allocation allocation ON dailiAccount.account_id = allocation.account_id
        WHERE
        allocation.user_id = #{userId}
        AND
        dailiAccount.stat_date &gt;= #{startDate}
        AND
        dailiAccount.stat_date &lt;= #{endDate}
        AND
        allocation.media_id = #{appType}
        group by dailiAccount.account_id
    </select>

    <!-- 根据天数的区间获取userId获取有效视频数量 -->
    <select id="getEffiVideoCountByCalcDate" resultType="java.lang.Integer">
        select
        count(1)
        from
        ctop_user_efficient_video_map
        where user_id = #{userId}
        <!-- and role_id = #{roleId}  -->
        and app_type = #{appType}
        and calc_date &lt;= #{endDate}
        and calc_date &gt;= #{startDate}
    </select>

    <!-- 视频有效素材 -->
    <select id="getEffiVideoList" resultType="cn.com.ctop.common.module.entity.MaterialInfo">
        select
        i.id as id,
        i.code as code,
        i.url as url,
        i.material_name as materialName,
        i.watermark_material_name as watermarkMaterialName,
        m.role_id as videoRoleCode,  <!-- 拍摄时候担任的角色 -->
        r.role_name as originRoleName  <!-- 原本的角色 -->
        from
        ctop_user_efficient_video_map m
        left join (select id,code,url,material_name,watermark_material_name from ctop_material_info group by code) i on
        m.efficient_video_signature = i.code
        left join sys_user_role sr on m.user_id = sr.user_id
        left join sys_role r on sr.role_id = r.id
        where
        m.user_id = #{userId} and
        m.app_type =#{appType} and
        m.year = #{year} and
        m.quarter = #{quarter}
    </select>

    <!-- 视频周有效素材 -->
    <select id="getEffiWeekVideoList" resultType="cn.com.ctop.common.module.entity.MaterialInfo">
        select
        i.id as id,
        i.code as code,
        i.url as url,
        i.material_name as materialName,
        i.watermark_material_name as watermarkMaterialName,
        m.role_id as videoRoleCode,  <!-- 拍摄时候担任的角色 -->
        r.role_name as originRoleName,  <!-- 原本的角色 -->
        su.realname as realname
        from
        ctop_user_efficient_video_map m
        left join (select id,code,url,material_name,watermark_material_name from ctop_material_info group by code) i on
        m.efficient_video_signature = i.code
        left join sys_user_role sr on m.user_id = sr.user_id
        left join sys_role r on sr.role_id = r.id
        left join sys_user su on m.user_id = su.id
        where
        m.user_id = #{userId} and
        m.app_type =#{appType} and
        m.calc_date &lt;= #{endDate} and
        m.calc_date &gt;= #{startDate}
    </select>

    <!-- 获取有效视频第一周所有产生消耗的视频的md5 -->
    <select id="getWeekCostMd5" resultType="string">
        select
        signature
        from ctop_kuaishou_report_daily_creative
        where
        DATE_FORMAT(stat_date,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(stat_date,'%Y-%m-%d') &lt;= #{endDate}
        and signature is not null
        group by signature
    </select>

    <!-- 获取toutiao有效视频第一周所有产生消耗的视频的md5 -->
    <select id="toutiaoGetWeekCostMd5" resultType="string">
        select
        signature
        from ctop_bytedance_report_creative_daily
        where
        DATE_FORMAT(stat_datetime,'%Y-%m-%d') &gt;= #{startDate}
        and DATE_FORMAT(stat_datetime,'%Y-%m-%d') &lt;= #{endDate}
        and signature is not null
        group by signature
    </select>

    <!-- 获取有效视频第一周开始时间之前消耗次数 -->
    <select id="beforeWeekCostCount" resultType="integer">
        select
        count(1)
        from ctop_kuaishou_report_daily_creative
        where
        signature = #{md5} and
        DATE_FORMAT(stat_date,'%Y-%m-%d') &lt; #{date}
    </select>

    <!-- 获取有效视频第一周开始时间之前消耗次数 -->
    <select id="toutiaoBeforeWeekCostCount" resultType="integer">
        select
        count(1)
        from ctop_bytedance_report_creative_daily
        where
        signature = #{md5} and
        DATE_FORMAT(stat_datetime,'%Y-%m-%d') &lt; #{date}
    </select>

    <!-- 获取视频最早产生消耗的日期 -->
    <select id="getVideoEarlistCostDate" resultType="string">
        select
        DATE_FORMAT(stat_date,'%Y-%m-%d')
        from ctop_kuaishou_report_daily_creative
        where
        signature = #{md5}
        order by stat_date asc limit 1
    </select>

    <!-- 获取视频最早产生消耗的日期 -->
    <select id="getToutiaoVideoEarlistCostDate" resultType="string">
        select
        DATE_FORMAT(stat_datetime,'%Y-%m-%d')
        from ctop_bytedance_report_creative_daily
        where
        signature = #{md5}
        order by stat_datetime asc limit 1
    </select>

    <!-- 获取视频在时间范围内的总消耗 -->
    <select id="videoTotalCostInDates" resultType="java.math.BigDecimal">
        select
        sum(charge)
        from ctop_kuaishou_report_daily_creative
        where
        signature = #{md5}
        and DATE_FORMAT(stat_date,'%Y-%m-%d') &lt;= #{endDate}
        and DATE_FORMAT(stat_date, '%Y-%m-%d') &gt;= #{startDate}
    </select>

    <!-- 获取视频在时间范围内的总消耗 -->
    <select id="toutiaoVideoTotalCostInDates" resultType="java.math.BigDecimal">
        select
        sum(cost)
        from ctop_bytedance_report_creative_daily
        where
        signature = #{md5}
        and DATE_FORMAT(stat_datetime,'%Y-%m-%d') &lt;= #{endDate}
        and DATE_FORMAT(stat_datetime, '%Y-%m-%d') &gt;= #{startDate}
    </select>

    <select id="getDesignerInfoByCode" resultType="cn.com.ctop.userefficientvideomap.entity.EffiVideoDTO">
        select
        a.id,
        b.clip_id as clipId,
        u1.realname as clipName,
        b.shot_id as shotId,
        u2.realname as shotName,
        b.plan_id as planId,
        u3.realname as planName,
        b.plane_id as planeId,
        u4.realname as planeName
        from
        ctop_material_info a
        left join ctop_material_ascription b on a.id=b.material_id
        left join sys_user u1 on b.clip_id = u1.id
        left join sys_user u2 on b.shot_id = u2.id
        left join sys_user u3 on b.plan_id = u3.id
        left join sys_user u4 on b.plane_id = u4.id
        where a.code = #{code}
        limit 1
    </select>

    <!-- 根据user_id获取员工所属分公司的company_id -->
    <select id="getCompanyInfoByUserId" resultType="string">
        select
        company_id
        from
        user_company
        where user_id = #{userId}
    </select>

    <!-- 按时间段获取视频总量 -->
    <select id="getVideoByDates" resultType="string">
        <!-- select
         code
         from (
             select
             code,
             create_time
             from
             ctop_material_info
             where
             date_format(create_time, '%Y-%m-%d') &gt;= #{startDate} and
             date_format(create_time, '%Y-%m-%d') &lt;= #{endDate}
             order by create_time
             ) a
         group by code -->
        select
        b.signature
        from ctop_kuaishou_creative a
        left join ctop_kuaishou_video_get b on a.photo_id = b.photo_id
        where
        date_format(a.creative_create_time, '%Y-%m-%d') &gt;= #{startDate}
        and
        date_format(a.creative_create_time, '%Y-%m-%d') &lt;= #{endDate}
        and b.signature is not null
        group by b.signature
    </select>

    <select id="kuaishouCreativeLtCount" resultType="integer">
            select
            count(1)
        from ctop_kuaishou_creative a
        left join ctop_kuaishou_video_get b on a.photo_id = b.photo_id
        where
        b.signature = #{signature}
        and
        date_format(a.creative_create_time, '%Y-%m-%d') &lt; #{date}
    </select>

    <select id="kuaishouCreativeEarlistDate" resultType="string">
            select
            date_format(creative_create_time, '%Y-%m-%d') as date
        from ctop_kuaishou_creative a
        left join ctop_kuaishou_video_get b on a.photo_id = b.photo_id
        where
        b.signature = #{signature}
        order by a.creative_create_time
        limit 1
    </select>

    <select id="getVideoCountByCode" resultType="integer">
        select
        count(1)
        from
        ctop_user_video_map
        where video_signature = #{signature}
        and app_type = #{appType}
    </select>

    <select id="toutiaoCreativeEarlistDate" resultType="string">
            select
            date_format(creative_create_time, '%Y-%m-%d') as date
        from ctop_bytedance_creative a
        left join ctop_bytedance_video_info b on a.video_id = b.id
        where
        b.signature = #{signature}
        order by a.creative_create_time
        limit 1
    </select>

    <select id="toutiaoCreativeLtCount" resultType="integer">
            select
            count(1)
        from ctop_bytedance_creative a
        left join ctop_bytedance_video_info b on a.video_id = b.id
        where
        b.signature = #{signature}
        and
        date_format(a.creative_create_time, '%Y-%m-%d') &lt; #{date}
    </select>

    <select id="getToutiaoVideoByDates" resultType="string">
        select
        b.signature
        from ctop_bytedance_creative a
        left join ctop_bytedance_video_info b on a.video_id = b.id
        where
        date_format(a.creative_create_time, '%Y-%m-%d') &gt;= #{startDate}
        and
        date_format(a.creative_create_time, '%Y-%m-%d') &lt;= #{endDate}
        and b.signature is not null
        group by b.signature
    </select>

    <!-- 视频素材 -->
    <select id="getVideoList" resultType="cn.com.ctop.common.module.entity.MaterialInfo">
        select
        i.id as id,
        i.code as code,
        i.url as url,
        i.material_name as materialName,
        i.watermark_material_name as watermarkMaterialName,
        m.role_id as videoRoleCode,  <!-- 拍摄时候担任的角色 -->
        r.role_name as originRoleName  <!-- 原本的角色 -->
        from
        ctop_user_video_map m
        left join (
        select
        id,code,url,material_name,watermark_material_name
        from
        ctop_material_info group by code
        ) i on m.video_signature = i.code
        left join sys_user_role sr on m.user_id = sr.user_id
        left join sys_role r on sr.role_id = r.id
        where
        m.user_id = #{userId} and
        m.calc_date &gt;= #{startDate} and
        m.calc_date &lt;= #{endDate} and
        m.app_type = #{appType}
    </select>

    <select id="getAccountIdByUserId" resultType="string">
        select
        account_id
        from
        ctop_user_allocation
        where
        user_id = #{userId}
    </select>

    <select id="accountTransforerListByUserId"
            resultType="cn.com.ctop.userefficientvideomap.entity.TransferorAccountDTO">
            select
            account_id as accountId,
            project_id as projectId,
            transferor,
            assignee,
            transferor_date as transferorDate,
            assignee_date as assigneeDate
        from ctop_transfer_account
        where
        account_id = #{accountId}
        and assignee_date &gt;= #{startDate}
        and transferor_date &lt;= #{endDate}
        and status =1
        order by transferor_date asc
    </select>

    <select id="getAccountSumCharge" resultType="java.math.BigDecimal">
        select
        sum(charge)
        from
        ctop_kuaishou_report_daily_account
        where
        account_id = #{accountId}
        and
        stat_date &lt;= #{endDate}
        and
        stat_date &gt;= #{startDate}
    </select>

    <select id="getToutiaoAccountSumCharge" resultType="java.math.BigDecimal">
        select
        sum(cost)
        from
        ctop_bytedance_report_advertiser_daily
        where
        advertiser_id = #{accountId}
        and
        date_format(stat_datetime, '%Y-%m-%d') &lt;= #{endDate}
        and
        date_format(stat_datetime, '%Y-%m-%d') &gt;= #{startDate}
    </select>

    <!-- 获取北京快手运营部所有运营经理的信息 -->
    <select id="getKuaishouYunyingLeaderUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId,
        ud.dep_id as depId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = '024b0dc0840045afb048022a8c61253f'  <!-- 快手运营部 -->
        and r.role_code = 'kuaishouOperationManager'
    </select>

    <!-- 获取北京头条运营部所有运营经理的信息 -->
    <select id="getToutiaoYunyingLeaderUserInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId,
        ud.dep_id as depId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.parent_id = 'c869de6bedc44a50803e45a4611df05e'  <!-- 头条运营部 -->
        and r.role_code = 'toutiaoOperationManager'
    </select>

    <!-- 获取运营组下所有运营 -->
    <select id="getYunyingLeaderMember" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select u.id as userId,
        u.realname as realname,
        r.role_name as roleName,
        r.role_code as roleCode,
        r.id as roleId,
        ud.dep_id as depId
        FROM sys_user_depart ud
        left join sys_depart b on ud.dep_id = b.id
        left join sys_user u on ud.user_id = u.id
        left join sys_user_role ur on u.id = ur.user_id
        left join sys_role r on ur.role_id = r.id
        WHERE b.id = #{depId}
        and r.role_code = 'operator'
    </select>

    <!-- 获取运营绩效 -->
    <select id="getYunyingRoyalty" resultType="java.math.BigDecimal">
        select
        total_royalty
        from
        ctop_performance_optimizer
        where user_id = #{userId}
        and year = #{year}
        and quarter = #{quarter}
    </select>

    <!-- 获取单一媒体下所有已绑定的账户 -->
    <select id="getAccountListByMediaId" resultType="string">
    select
    account_id
    from ctop_user_allocation
    where
    media_id = #{mediaId}
    and user_id not in ('e9ca23d68d884d4ebb19d07889727dae')
    group by account_id
    </select>

    <!-- 获取账户的持有人 -->
    <select id="getAccountUserId" resultType="java.lang.String">
        select
        user_id
        from
        ctop_user_allocation
        where
        account_id = #{accountId}
        limit 1
    </select>

    <select id="getrealnameByUserId" resultType="java.lang.String">
        select
        realname
        from sys_user
        where
        id = #{userId}
    </select>

    <select id="getRoleCodeByUserId" resultType="java.lang.String">
        select
        role_code
        from
        sys_role a
        left join sys_user_role b on a.id = b.role_id
        where b.user_id = #{userId}
    </select>

    <select id="getYunyingQuarterDetailByUserId" resultType="cn.com.ctop.performanceaccount.entity.PerformanceAccount">
        select
        c.realname,
        a.account_id,
        b.auth_name,
        a.cost,
        a.start_date,
        a.end_date,
        b.advertiser_name,
        b.project_name
        from
        ctop_performance_account a
        left join ctop_user_allocation b on a.account_id = b.account_id
        left join sys_user c on a.user_id = c.id
        where
        media_type = #{appType}
        and a.user_id = #{userId}
        and a.year = #{year}
        and a.quarter = #{quarter}
    </select>

    <!-- 按平台获取其下所有项目信息以及消耗 -->
    <select id="getProjcetListByMediaType" resultType="cn.com.ctop.userefficientvideomap.entity.ProjectDTO">
        select
        id as projectId,
        project_name as projectName,
        advertiser_name as advertiserName,  <!-- 广告主 -->
        responsible_name as responsibleName,  <!-- 负责人名称 -->
        design_responsible_name as designResponsibleName, <!-- 设计负责人名称 -->
        (select sum(cost) from ctop_performance_account where account_id in (
        select
        account_id
        from
        ctop_user_allocation where project_id = projectId) and year = #{year} and quarter = #{quarter}
        ) as totalCost  <!-- 每个项目在季度内的消耗 -->
        from
        ctop_project
        where
        media_id = #{appType}
    </select>

    <!-- 根据projectId获取其下账户季度消耗信息 -->
    <select id="getQuarterAccountDetail" resultType="cn.com.ctop.userefficientvideomap.entity.AccountDTO">
            select
            a.account_id as accountId,
            b.auth_name as authName,
            a.cost as cost,
            a.user_id as userId,
            c.realname as userName,
            b.media_id as mediaId,
            b.advertiser_name as advertiserName,
            b.project_id as projectId,
            b.project_name as projectName,
            a.year as year,
            a.quarter as quarter,
            a.start_date as startDate,
            a.end_date as endDate
        from ctop_performance_account a
        left join (select * from ctop_user_allocation where project_id = #{projectId}) b on a.account_id = b.account_id
        left join sys_user c on a.user_id = c.id
        where a.account_id in (
                        select
                        account_id
                        from
                        ctop_user_allocation where project_id = #{projectId}) and a.year = #{year} and a.quarter = #{quarter}
        order by a.account_id,a.start_date
    </select>

    <select id="getProjectDesignResponsibleInfo" resultType="cn.com.ctop.userefficientvideomap.entity.UserDto2">
        select
        a.design_responsible_id as userId,
        a.design_responsible_name as userName,
        d.id as roleId,
        d.role_code as roleCode,
        d.role_name as roleName
            from
            ctop_project a
            left join sys_user b on a.design_responsible_id = b.id
            left join sys_user_role c on b.id = c.user_id
            left join sys_role d on c.role_id = d.id
            where
            a.id = #{projectId}
    </select>

    <!-- 快手根据账户获取其下视频制作信息以及消耗 -->
    <select id="getKuaishouQuarterVideoInfoByAccountId" resultType="cn.com.ctop.userefficientvideomap.entity.VideoDTO">
        select
        a.signature,
        a.charge,
        (select url from ctop_kuaishou_video_get where signature = a.signature limit 1) as url,
        b.url as creativeUrl,
        d1.realname as shot,
        d1.id as shotId,
        d2.realname as plan,
        d2.id as planId,
        d3.realname as plane,
        d3.id as planeid,
        d4.realname as clip,
        d4.id as clipId
        from
        (
        select signature, sum(charge) as charge from ctop_kuaishou_report_daily_creative where creative_id in (
        select creative_id from ctop_kuaishou_creative where photo_id != 0 and account_id = #{accountId}
        )
        and stat_date &gt;= #{startDate} and stat_date &lt;= #{endDate} <!-- and signature is not null --> group by
        signature
        ) a
        left join (select id,url,code from ctop_material_info group by code) b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on d1.id = c.shot_id
        left join sys_user d2 on d2.id = c.plan_id
        left join sys_user d3 on d3.id = c.plane_id
        left join sys_user d4 on d4.id = c.clip_id
        where b.url is not null <!-- TODO delete -->
    </select>

    <!-- 头条根据账户获取其下视频制作信息以及消耗 -->
    <select id="getToutiaoQuarterVideoInfoByAccountId" resultType="cn.com.ctop.userefficientvideomap.entity.VideoDTO">
        select
        a.signature,
        a.charge,
        (select video_url from ctop_bytedance_video_info where signature = a.signature limit 1) as creativeUrl,
        b.url,
        d1.realname as shot,
        d1.id as shotId,
        d2.realname as plan,
        d2.id as planId,
        d3.realname as plane,
        d3.id as planeid,
        d4.realname as clip,
        d4.id as clipId
        from
        (
        select creative_id,signature, sum(cost) as charge from ctop_bytedance_report_creative_daily where creative_id in
        (
        select id from ctop_bytedance_creative where account_id = #{accountId}
        )
        and date_format(stat_datetime,'%Y-%m-%d') &gt;= #{startDate}
        and date_format(stat_datetime,'%Y-%m-%d') &lt;= #{endDate}
        <!--and  signature is not null --> group by signature
        ) a
        left join (select id,url,code from ctop_material_info group by code) b on a.signature = b.code
        left join ctop_material_ascription c on b.id = c.material_id
        left join sys_user d1 on d1.id = c.shot_id
        left join sys_user d2 on d2.id = c.plan_id
        left join sys_user d3 on d3.id = c.plane_id
        left join sys_user d4 on d4.id = c.clip_id
        where b.url is not null <!-- TODO delete -->
    </select>

    <!-- 根据设计师组员姓名获取组长姓名 -->
    <select id="getDesignLeaderInfoByDesignUserId" resultType="cn.com.ctop.common.module.entity.SysUser">
        select
        a.id,
        a.realname
        from sys_user a
        left join sys_user_depart b on a.id = b.user_id
        left join sys_user_role c on a.id = c.user_id
        where b.dep_id = (select dep_id from sys_user_depart where user_id = #{userId})
        and c.role_id = '8d3e2ed58bdedf17716f698ea674da27' <!-- 设计组长roleid -->
    </select>

    <select id="getUserQuarterVideo" resultType="cn.com.ctop.userefficientvideomap.entity.VideoDTO">
          select
          a.video_signature as signature,
          a.role_id as roleId,
          d2.role_code as roleCode,
          a.quarter,
          a.year,
          a.month,
          a.week_start as weekStart,
          a.week_end as weekEnd,
          a.app_type as appType,
          b.url as creativeUrl
        from
        ctop_user_video_map a
        left join (select code, url from ctop_material_info group by code) b on a.video_signature = b.code
        left join sys_user_role c on c.user_id = a.user_id
        left join sys_role d2 on c.role_id = d2.id
        where
        a.user_id = #{userId}
        and a.year = #{year}
        and a.quarter = #{quarter}
        group by signature
    </select>

    <select id="getUserQuarterEffiVideo" resultType="cn.com.ctop.userefficientvideomap.entity.VideoDTO">
          select
          a.video_signature as signature,
          a.role_id as roleId,
          d2.role_code as roleCode,
          a.quarter,
          a.year,
          a.month,
          a.week_start as weekStart,
          a.week_end as weekEnd,
          a.app_type as appType,
          b.url as creativeUrl
        from
        ctop_user_video_map a
        left join (select code, url from ctop_material_info group by code) b on a.video_signature = b.code
        left join sys_user_role c on c.user_id = a.user_id
        left join sys_role d2 on c.role_id = d2.id
        where
        a.user_id = #{userId}
        and a.year = #{year}
        and a.quarter = #{quarter}
        group by signature
    </select>

    <select id="getAccountInfoByAppType" resultType="cn.com.ctop.userefficientvideomap.entity.AccountDTO">
            select
            account_id as accountId,
            auth_name as authName,
            project_id as projectId,
            project_name as projectName
        from
        ctop_user_allocation
        where
        media_id = #{appType}
        order by project_id
    </select>

</mapper>
