<?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.toutiao.modules.report.mapper.ByteDanceVideoReportDailyMapper">

    <select id="getVideoReportDailyByDateAndSignature" resultType="java.math.BigDecimal">
        select
        sum(cost)
        from
        ctop_bytedance_video_report_daily
        where
        stat_datetime &gt;= #{startDate} and
        stat_datetime &lt;= #{endDate} and
        <if test="accountIds != null">
            account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            and
        </if>
        signature = #{signature}
    </select>

    <update id="updateHuanbiByDateAndSignature">
        update
            ctop_bytedance_video_report_daily
        set huanbi = #{huanbi}
        where stat_datetime = #{date}
          and signature = #{signature}
    </update>

    <sql id="selectSql">
        a
        .
        project_id
        as projectId,
        a.project_name                           as projectName,
        a.advertiser_id                          as advertiserId,
        a.advertiser_name                        as advertiserName,
        a.stat_datetime                          as statDatetime,
        sum(a.cost)                              as cost,
        a.signature                              as signature,
        a.video_url                              as videoUrl,
        '-'                                      as materialDesc,
        (select info.url from ctop_material_info info where info.code = a.signature limit 1)                                    as url,
        (select info.cover_url from ctop_material_info info where info.code = a.signature limit 1)                                    as cover_url,
        (select
            case when video.material_upload_time is null then '-'
            else video.material_upload_time
            end as materialUploadTime
        from ctop_bytedance_video_info video where video.signature = a.signature limit 1)           as materialUploadTime,
        1                                        as appType,
        sum(a.click)                             as clickMaterial,

        case
        when sum(a.total_play) != 0
        then sum(a.play25_feed_break) / sum(a.total_play)
        else 0
        end                           as play25FeedBreakRate,

        case
        when sum(a.total_play) != 0
        then sum(a.play_over) / sum(a.total_play)
        else 0
        end                           as playOverRate,

        case
        when sum(a.click) != 0
        then sum(a.convert_material) / sum(a.click)
        else 0
        end                           as convertRate,
        sum(a.active)                            as active,
        sum(a.download_finish)                   as downloadFinish,
        sum(a.download_start)                    as downloadStart,
        sum(a.install_finish)                    as installFinish,
        sum(a.register)                          as register,
        sum(a.pay_count)                         as payCount,
        sum(a.form)                              as form,
        sum(a.total_play)                        as totalPlay,
        sum(a.valid_play)                        as validPlay,
        sum(a.wifi_play)                         as wifiPlay,
        sum(a.play25_feed_break)                 as play25FeedBreak,
        sum(a.play50_feed_break)                 as play50FeedBreak,
        sum(a.play75_feed_break)                 as play75FeedBreak,
        sum(a.play100_feed_break)                as play100FeedBreak,
        sum(a.share_material)                    as shareMaterial,
        sum(a.follow)                            as follow,
        sum(a.next_day_open)                     as nextDayOpen,
        case
        when sum(a.active) != 0
        then sum(a.next_day_open) / sum(a.active)
        else 0
        end                           as nextDayOpenRate,

        case
        when sum(a.next_day_open) != 0
        then sum(a.cost) / sum(a.next_day_open)
        else 0
        end                           as nextDayOpenCost,

        sum(a.active_pay_amount)                 as activePayAmount,

        case
        when sum(a.valid_play) != 0
        then sum(a.cost) / sum(a.valid_play)
        else 0
        end                              validPlayCost,
        sum(a.convert_material)                  as convertMaterial,

        case
        when sum(a.active_pay_amount) != 0
        then sum(a.cost) / sum(a.active_pay_amount)
        else 0
        end                           as activePayCost,
        case
        when sum(a.click) != 0
        then sum(a.cost) / sum(a.click)
        else 0
        end                           as cpc,
        case
        when sum(a.show_material) != 0
        then sum(a.click) / sum(a.show_material)
        else 0
        end                           as ctr,

        case
        when sum(a.show_material) != 0
        then sum(a.cost) / sum(a.show_material) * 1000
        else 0
        end                              cpm,
        sum(a.wifi_play_rate)                    as wifiPlayRate,
        sum(a.like_material)                     as likeMaterial,


        case
        when sum(a.active) != 0
        then sum(a.cost) / sum(a.active)
        else 0
        end                              activeCost,

        case
        when sum(a.game_addiction) != 0
        then sum(a.cost) / sum(a.game_addiction)
        else 0
        end                           as gameAddictionCost,
        sum(a.game_addiction)                    as gameAddiction,

        case
        when sum(a.click) != 0
        then sum(a.active) / sum(a.click)
        else 0
        end                           as activeRate,


        case
        when sum(a.active) != 0
        then sum(a.game_addiction) / sum(a.active)
        else 0
        end                              gameAddictionRate,

        case
        when sum(a.active) != 0
        then sum(a.register) / sum(a.active)
        else 0
        end                           as activeRegisterRate,
        case
        when sum(a.download_finish) != 0
        then sum(a.cost) / sum(a.download_finish)
        else 0
        end                           as downloadFinishCost,
        case
        when sum(a.register) != 0
        then sum(a.cost) / sum(a.register)
        else 0
        end                           as activeRegisterCost,
        sum(a.show_material)                     as showMaterial,
        case
        when sum(a.download_start) != 0
        then sum(a.download_finish) / sum(a.download_start)
        else 0
        end                           as downloadFinishRate,
        case
        when sum(a.download_finish) != 0
        then sum(a.install_finish) / sum(a.download_finish)
        else 0
        end                           as installFinishRate,
        sum(a.play_over)                         as playOver,
        case
        when sum(a.download_start) != 0
        then sum(a.cost) / sum(a.download_start)
        else 0
        end                           as downloadStartCost,
        case
        when sum(a.show_material) != 0
        then sum(a.valid_play) / sum(a.show_material)
        else 0
        end                           as validPlayRate,
        case
        when sum(a.valid_play) != 0
        then sum(a.average_play_time_per_play*a.valid_play)/sum(a.valid_play)
        else 0
        end as averagePlayTimePerPlay,
        case
        when sum(a.convert_material) != 0
        then sum(a.cost) / sum(a.convert_material)
        else 0
        end                           as convertCost,
        case
        when sum(a.install_finish) != 0
        then sum(a.cost) / sum(a.install_finish)
        else 0
        end                           as installFinishCost,
        case
        when sum(a.click) != 0
        then sum(a.download_start) / sum(a.click)
        else 0
        end                           as downloadStartRate
    </sql>

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

    <select id="getCompanyIdByUserId" resultType="string">
        select company_id
        from user_company
        where user_id = #{userId}
    </select>

    <select id="getJsonByUserIdAndColumnType" resultType="string">
        select json as json
        from ctop_column_load
        where column_type = #{columnType}
          and user_id = #{userId}
        order by create_time desc limit 1
    </select>

    <select id="getJsonByAccountIdAndColumnType" resultType="string">
        select json as json
        from ctop_column_load
        where column_type = #{columnType}
          and account_id = #{accountId}
    </select>

    <insert id="insertColumnLoad">
        insert into ctop_column_load
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                user_id,
            </if>
            <if test="json != null">
                json,
            </if>
            <if test="columnType != null">
                column_type,
            </if>
            <if test="accountId != null">
                account_id,
            </if>
            status
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                #{userId},
            </if>
            <if test="json != null">
                #{json},
            </if>
            <if test="columnType != null">
                #{columnType},
            </if>
            <if test="accountId != null">
                #{accountId},
            </if>
            1
        </trim>
    </insert>


    <update id="updateJsonByUserIdAndType">
        update
        ctop_column_load
        set
        json = #{json}
        where column_type = #{columnType}
        <if test="userId != null">
            and user_id = #{userId}
        </if>
        <if test="accountId != null">
            and account_id = #{accountId}
        </if>
    </update>

    <select id="videoInfoListDetail" resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        a.project_id as projectId,
        a.project_name as projectName,
        a.account_id as accountId,
        a.advertiser_id as advertiserId,
        a.advertiser_name as advertiserName,
        a.stat_datetime as statDatetime,
        sum(a.cost) as cost,
        a.video_url as videoUrl,
        (select info.url from ctop_material_info info where info.code = a.signature limit 1) as url,
        signature as signature

        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where
        stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        and f.material_id = #{signature}
        group by stat_datetime
        order by stat_datetime desc
    </select>

    <select id="videoInfoListDetailOperator"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        a.project_id as projectId,
        a.project_name as projectName,
        a.account_id as accountId,
        a.advertiser_id as advertiserId,
        a.advertiser_name as advertiserName,
        a.stat_datetime as statDatetime,
        sum(a.cost) as cost,
        a.video_url as videoUrl,
        (select info.url from ctop_material_info info where info.code = a.signature limit 1) as url,
        signature as signature

        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where
        stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        and f.material_id = #{signature}
        group by stat_datetime
        order by stat_datetime desc
    </select>

    <select id="videoInfoListDetailOtherRoles"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        a.project_id as projectId,
        a.project_name as projectName,
        a.account_id as accountId,
        a.advertiser_id as advertiserId,
        a.advertiser_name as advertiserName,
        a.stat_datetime as statDatetime,
        sum(a.cost) as cost,
        a.video_url as videoUrl,
        (select info.url from ctop_material_info info where info.code = a.signature limit 1) as url,
        signature as signature

        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where
        stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        and f.material_id = #{signature}
        group by stat_datetime
        order by stat_datetime desc
    </select>

    <select id="videoInfoListDetailDesign"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        a.project_id as projectId,
        a.project_name as projectName,
        a.account_id as accountId,
        a.advertiser_id as advertiserId,
        a.advertiser_name as advertiserName,
        a.stat_datetime as statDatetime,
        sum(a.cost) as cost,
        a.video_url as videoUrl,
        (select info.url from ctop_material_info info where info.code = a.signature limit 1) as url,
        signature as signature

        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where
        stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and a.company_id = #{companyId}

        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        and f.material_id = #{signature}
        group by stat_datetime
        order by stat_datetime desc
    </select>

    <select id="accountVideoMap" resultType="cn.com.ctop.toutiao.modules.report.DTO.AccountVideoDTO">
        select a.account_id,
               b.signature
        FROM ctop_bytedance_report_material_daily a
                 left join ctop_bytedance_video_info b on a.material_id = b.material_id
        where a.image_mode in ('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL')
          and b.signature is not null
        group by a.account_id, b.signature
    </select>

    <insert id="replaceIntoAccountVideoMapBatch">
        replace into
        ctop_bytedance_account_video_map
        (
        account_id,
        signature
        )
        values
        <foreach collection="accountVideoDTOList" item="accountVideo" separator=",">
            (
            #{accountVideo.accountId},
            #{accountVideo.signature}
            )
        </foreach>
    </insert>
    <insert id="insertBatch">
        insert into ctop_bytedance_video_report_daily(
        account_id,
        project_id,
        project_name,
        advertiser_id,
        advertiser_name,
        signature,
        video_url,
        url,
        huanbi,
        material_name,
        app_type,
        company_id,
        material_id,
        stat_datetime,
        click,
        cost,
        active,
        download_finish,
        download_start,
        install_finish,
        register,
        pay_count,
        in_app_uv,
        in_app_detail_uv,
        in_app_cart,
        in_app_order,
        in_app_pay,
        phone,
        form,
        map_search,
        view_material,
        qq,
        lottery,
        vote,
        redirect,
        shopping,
        consult,
        wechat,
        phone_confirm,
        phone_connect,
        consult_effective,
        total_play,
        valid_play,
        wifi_play,
        play25_feed_break_rate,
        play25_feed_break,
        play50_feed_break,
        play75_feed_break,
        play100_feed_break,
        advanced_creative_phone_click,
        advanced_creative_counsel_click,
        advanced_creative_form_click,
        share_material,
        comment_material,
        follow,
        home_visited,
        ies_challenge_click,
        ies_music_click,
        next_day_open,
        next_day_open_rate,
        next_day_open_cost,
        active_pay_amount,
        valid_play_cost,
        advanced_creative_coupon_addition,
        convert_material,
        active_pay_cost,
        download,
        cpc,
        location_click,
        play_over_rate,
        ctr,
        cpm,
        wifi_play_rate,
        like_material,
        active_cost,
        game_addiction_cost,
        game_addiction,
        active_rate,
        game_addiction_rate,
        active_register_rate,
        download_finish_cost,
        active_register_cost,
        convert_rate,
        download_finish_rate,
        install_finish_rate,
        coupon,
        coupon_single_page,
        play_over,
        download_start_cost,
        message,
        valid_play_rate,
        average_play_time_per_play,
        convert_cost,
        install_finish_cost,
        download_start_rate,
        show_material
        )
        values
        <foreach collection="mlist" item="image" separator=",">
            (#{image.accountId},
            #{image.projectId},
            #{image.projectName},
            #{image.advertiserId},
            #{image.advertiserName},
            #{image.signature},
            #{image.videoUrl},
            #{image.url},
            #{image.huanbi},
            #{image.materialName},
            #{image.appType},
            #{image.companyId},
            #{image.materialId},
            #{image.statDatetime},
            #{image.click},
            #{image.cost},
            #{image.active},
            #{image.downloadFinish},
            #{image.downloadStart},
            #{image.installFinish},
            #{image.register},
            #{image.payCount},
            #{image.inAppUv},
            #{image.inAppDetailUv},
            #{image.inAppCart},
            #{image.inAppOrder},
            #{image.inAppPay},
            #{image.phone},
            #{image.form},
            #{image.mapSearch},
            #{image.viewMaterial},
            #{image.qq},
            #{image.lottery},
            #{image.vote},
            #{image.redirect},
            #{image.shopping},
            #{image.consult},
            #{image.wechat},
            #{image.phoneConfirm},
            #{image.phoneConnect},
            #{image.consultEffective},
            #{image.totalPlay},
            #{image.validPlay},
            #{image.wifiPlay},
            #{image.play25FeedBreakRate},
            #{image.play25FeedBreak},
            #{image.play50FeedBreak},
            #{image.play75FeedBreak},
            #{image.play100FeedBreak},
            #{image.advancedCreativePhoneClick},
            #{image.advancedCreativeCounselClick},
            #{image.advancedCreativeFormClick},
            #{image.shareMaterial},
            #{image.commentMaterial},
            #{image.follow},
            #{image.homeVisited},
            #{image.iesChallengeClick},
            #{image.iesMusicClick},
            #{image.nextDayOpen},
            #{image.nextDayOpenRate},
            #{image.nextDayOpenCost},
            #{image.activePayAmount},
            #{image.validPlayCost},
            #{image.advancedCreativeCouponAddition},
            #{image.convertMaterial},
            #{image.activePayCost},
            #{image.download},
            #{image.cpc},
            #{image.locationClick},
            #{image.playOverRate},
            #{image.ctr},
            #{image.cpm},
            #{image.wifiPlayRate},
            #{image.likeMaterial},
            #{image.activeCost},
            #{image.gameAddictionCost},
            #{image.gameAddiction},
            #{image.activeRate},
            #{image.gameAddictionRate},
            #{image.activeRegisterRate},
            #{image.downloadFinishCost},
            #{image.activeRegisterCost},
            #{image.convertRate},
            #{image.downloadFinishRate},
            #{image.installFinishRate},
            #{image.coupon},
            #{image.couponSinglePage},
            #{image.playOver},
            #{image.downloadStartCost},
            #{image.message},
            #{image.validPlayRate},
            #{image.averagePlayTimePerPlay},
            #{image.convertCost},
            #{image.installFinishCost},
            #{image.downloadStartRate},
            #{image.showMaterial}
            )
        </foreach>
    </insert>

    <select id="getAccountIdByprojectIds" resultType="long">
        select
        account_id
        from
        ctop_user_allocation
        where
        project_id in
        <foreach collection="projectIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        group by account_id
    </select>

    <select id="getAccountIdsByOperatorIdAndMediaId" resultType="long">
        select account_id
        from ctop_user_allocation
        where user_id = #{userId}
          and media_id in (1, 3)
    </select>

    <sql id="videoInfoSql">
        a
        .
        account_id
        as accountId,
        b.project_id as projectId,
        b.project_name as projectName,
        b.advertiser_id as advertiserId,
        b.advertiser_name as advertiserName,
        a.stat_datetime as statDatetime,
        sum(a.cost) as cost,
        d.signature as signature,
        d.video_url as videoUrl,
        e.url as url,
        '-' as materialDesc,
        1 as appType,
        sum(a.click) as click,
        case
        when sum(a.total_play) != 0
        then sum(a.play25_feed_break)/sum(a.total_play)
        else 0
        end as play25FeedBreakRate,

        case
        when sum(a.total_play) != 0
        then sum(a.play_over)/sum(a.total_play)
        else 0
        end as playOverRate,
        case
        when sum(a.click) != 0
        then sum(a.convert_material)/sum(a.click)
        else 0
        end as convertRate,
        sum(a.active) as active,
        sum(a.download_finish) as downloadFinish,
        sum(a.download_start) as downloadStart,
        sum(a.install_finish) as installFinish,
        sum(a.register) as register,
        sum(a.pay_count) as payCount,
        sum(a.in_app_uv) as inAppUv,
        sum(a.in_app_detail_uv) as inAppDetailUv,
        sum(a.in_app_cart) as inAppCart,
        sum(a.in_app_order) as inAppOrder,
        sum(a.in_app_pay) as inAppPay,
        sum(a.phone) as phone,
        sum(a.form) as form,
        sum(a.map_search) as mapSearch,
        sum(a.button) as button,
        sum(a.view_material) as viewMaterial,
        sum(a.qq) as qq,
        sum(a.lottery) as lottery,
        sum(a.vote) as vote,
        sum(a.redirect) as redirect,
        sum(a.shopping) as shopping,
        sum(a.consult) as consult,
        sum(a.wechat) as wechat,
        sum(a.phone_confirm) as phoneConfirm,
        sum(a.phone_connect) as phoneConnect,
        sum(a.consult_effective) as consultEffective,
        sum(a.total_play) as totalPlay,
        sum(a.valid_play) as validPlay,
        sum(a.wifi_play) as wifiPlay,
        sum(a.play25_feed_break) as play25FeedBreak,
        sum(a.play50_feed_break) as play50FeedBreak,
        sum(a.play75_feed_break) as play75FeedBreak,
        sum(a.play100_feed_break) as play100FeedBreak,
        sum(a.advanced_creative_phone_click) as advancedCreativePhoneClick,
        sum(a.advanced_creative_counsel_click) as advancedCreativeCounselClick,
        sum(a.advanced_creative_form_click) as advancedCreativeFormClick,
        sum(a.share_material) as shareMaterial,
        sum(a.comment_material) as commentMaterial,
        sum(a.follow) as follow,
        sum(a.home_visited) as homeVisited,
        sum(a.ies_challenge_click) as iesChallengeClick,
        sum(a.ies_music_click) as iesMusicClick,
        sum(a.next_day_open) as nextDayOpen,
        case
        when sum(a.active) != 0
        then sum(a.next_day_open)/sum(a.active)
        else 0
        end as nextDayOpenRate,

        case
        when sum(a.next_day_open) != 0
        then sum(a.cost)/sum(a.next_day_open)
        else 0
        end as nextDayOpenCost,

        case when sum(a.pay_count) != 0
        then sum(a.cost)/sum(a.pay_count)
        else 0
        end
        as activePayCost,

        sum(a.active_pay_amount) as activePayAmount,

        case
        when sum(a.valid_play) != 0
        then sum(a.cost)/sum(a.valid_play)
        else 0
        end validPlayCost,
        sum(a.advanced_creative_coupon_addition) as advancedCreativeCouponAddition,
        sum(a.convert_material) as convertMaterial,

        case
        when sum(a.active_pay_amount) != 0
        then sum(a.cost)/sum(a.active_pay_amount)
        else 0
        end as activePayCost,
        sum(a.download) as download,
        case
        when sum(a.click) != 0
        then sum(a.cost) / sum(a.click)
        else 0
        end                           as cpc,
        sum(a.location_click) as locationClick,

        case
        when sum(a.show_material) != 0
        then sum(a.click)/sum(a.show_material)
        else 0
        end as ctr,

        case
        when sum(a.show_material) != 0
        then sum(a.cost)/sum(a.show_material)*1000
        else 0
        end cpm,
        sum(a.wifi_play_rate) as wifiPlayRate,
        sum(a.like_material) as likeMaterial,

        case
        when sum(a.active) != 0
        then sum(a.cost)/sum(a.active)
        else 0
        end activeCost,

        case
        when sum(a.game_addiction) != 0
        then sum(a.cost)/sum(a.game_addiction)
        else 0
        end as gameAddictionCost,
        sum(a.game_addiction) as gameAddiction,

        case
        when sum(a.click) != 0
        then sum(a.active)/sum(a.click)
        else 0
        end as activeRate,

        case
        when sum(a.active) != 0
        then sum(a.game_addiction)/sum(a.active)
        else 0
        end gameAddictionRate,
        case
        when sum(a.active) != 0
        then sum(a.register)/sum(a.active)
        else 0
        end as activeRegisterRate,

        case
        when sum(a.download_finish) != 0
        then sum(a.cost)/sum(a.download_finish)
        else 0
        end as downloadFinishCost,


        case
        when sum(a.register) != 0
        then sum(a.cost)/sum(a.register)
        else 0
        end as activeRegisterCost,
        sum(a.show_material) as showMaterial,

        case
        when sum(a.download_start) != 0
        then sum(a.download_finish)/sum(a.download_start)
        else 0
        end as downloadFinishRate,

        case
        when sum(a.download_finish) != 0
        then sum(a.install_finish)/sum(a.download_finish)
        else 0
        end as installFinishRate,
        sum(a.coupon) as coupon,
        sum(a.coupon_single_page) as couponSinglePage,
        sum(a.play_over) as playOver,

        case
        when sum(a.download_start) != 0
        then sum(a.cost)/sum(a.download_start)
        else 0
        end as downloadStartCost,
        sum(a.message) as message,
        case
        when sum(a.show_material) != 0
        then sum(a.valid_play)/sum(a.show_material)
        else 0
        end as validPlayRate,

        case
        when sum(a.valid_play) != 0
        then sum(a.average_play_time_per_play*a.valid_play)/sum(a.valid_play)
        else 0
        end as averagePlayTimePerPlay,
        case
        when sum(a.convert_material) != 0
        then sum(a.cost)/sum(a.convert_material)
        else 0
        end as convertCost,

        case
        when sum(a.install_finish) != 0
        then sum(a.cost)/sum(a.install_finish)
        else 0
        end as installFinishCost,

        case
        when sum(a.click) != 0
        then sum(a.download_start)/sum(a.click)
        else 0
        end as downloadStartRate,
    </sql>

    <select id="videoInfoList" resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        a.account_id as accountId,
        b.project_id as projectId,
        b.project_name as projectName,
        b.advertiser_id as advertiserId,
        b.advertiser_name as advertiserName,
        a.stat_datetime as statDatetime,
        sum(a.cost) as cost,
        d.signature as signature,
        d.video_url as videoUrl,
        (select mat.url from ctop_material_info mat where mat.code = d.signature order by id desc limit 1) as url,
        '-' as materialDesc,
        1 as appType,
        sum(a.click) as click,
        case
        when sum(a.total_play) != 0
        then sum(a.play25_feed_break)/sum(a.total_play)
        else 0 end as play25FeedBreakRate,

        case
        when sum(a.total_play) != 0
        then sum(a.play_over)/sum(a.total_play)
        else 0 end as playOverRate,

        case
        when sum(a.click) != 0
        then sum(a.convert_material)/sum(a.click)
        else 0 end as convertRate,
        sum(a.active) as active,
        sum(a.download_finish) as downloadFinish,
        sum(a.download_start) as downloadStart,
        sum(a.install_finish) as installFinish,
        sum(a.register) as register,
        sum(a.pay_count) as payCount,
        sum(a.in_app_uv) as inAppUv,
        sum(a.in_app_detail_uv) as inAppDetailUv,
        sum(a.in_app_cart) as inAppCart,
        sum(a.in_app_order) as inAppOrder,
        sum(a.in_app_pay) as inAppPay,
        sum(a.phone) as phone,
        sum(a.form) as form,
        sum(a.map_search) as mapSearch,
        sum(a.button) as button,
        sum(a.view_material) as viewMaterial,
        sum(a.qq) as qq,
        sum(a.lottery) as lottery,
        sum(a.vote) as vote,
        sum(a.redirect) as redirect,
        sum(a.shopping) as shopping,
        sum(a.consult) as consult,
        sum(a.wechat) as wechat,
        sum(a.phone_confirm) as phoneConfirm,
        sum(a.phone_connect) as phoneConnect,
        sum(a.consult_effective) as consultEffective,
        sum(a.total_play) as totalPlay,
        sum(a.valid_play) as validPlay,
        sum(a.wifi_play) as wifiPlay,
        sum(a.play25_feed_break) as play25FeedBreak,
        sum(a.play50_feed_break) as play50FeedBreak,
        sum(a.play75_feed_break) as play75FeedBreak,
        sum(a.play100_feed_break) as play100FeedBreak,
        sum(a.advanced_creative_phone_click) as advancedCreativePhoneClick,
        sum(a.advanced_creative_counsel_click) as advancedCreativeCounselClick,
        sum(a.advanced_creative_form_click) as advancedCreativeFormClick,
        sum(a.share_material) as shareMaterial,
        sum(a.comment_material) as commentMaterial,
        sum(a.follow) as follow,
        sum(a.home_visited) as homeVisited,
        sum(a.ies_challenge_click) as iesChallengeClick,
        sum(a.ies_music_click) as iesMusicClick,
        sum(a.next_day_open) as nextDayOpen,
        case
        when sum(a.active) != 0
        then sum(a.next_day_open)/sum(a.active)
        else 0 end as nextDayOpenRate,

        case
        when sum(a.next_day_open) != 0
        then sum(a.cost)/sum(a.next_day_open)
        else 0 end as nextDayOpenCost,

        case when sum(a.pay_count) != 0
        then sum(a.cost)/sum(a.pay_count)
        else 0 end
        as activePayCost,

        sum(a.active_pay_amount) as activePayAmount,

        case
        when sum(a.valid_play) != 0
        then sum(a.cost)/sum(a.valid_play)
        else 0 end validPlayCost,
        sum(a.advanced_creative_coupon_addition) as advancedCreativeCouponAddition,
        sum(a.convert_material) as convertMaterial,

        case
        when sum(a.active_pay_amount) != 0
        then sum(a.cost)/sum(a.active_pay_amount)
        else 0 end as activePayCost,
        sum(a.download) as download,

        case
        when sum(a.click) != 0
        then sum(a.cost) / sum(a.click)
        else 0 end as cpc,
        sum(a.location_click) as locationClick,

        case
        when sum(a.show_material) != 0
        then sum(a.click)/sum(a.show_material)
        else 0 end as ctr,

        case
        when sum(a.show_material) != 0
        then sum(a.cost)/sum(a.show_material)*1000
        else 0 end cpm,
        sum(a.wifi_play_rate) as wifiPlayRate,
        sum(a.like_material) as likeMaterial,

        case
        when sum(a.active) != 0
        then sum(a.cost)/sum(a.active)
        else 0 end activeCost,

        case
        when sum(a.game_addiction) != 0
        then sum(a.cost)/sum(a.game_addiction)
        else 0 end as gameAddictionCost,
        sum(a.game_addiction) as gameAddiction,

        case
        when sum(a.click) != 0
        then sum(a.active)/sum(a.click)
        else 0 end as activeRate,

        case
        when sum(a.active) != 0
        then sum(a.game_addiction)/sum(a.active)
        else 0 end gameAddictionRate,

        case
        when sum(a.active) != 0
        then sum(a.register)/sum(a.active)
        else 0 end as activeRegisterRate,


        case
        when sum(a.download_finish) != 0
        then sum(a.cost)/sum(a.download_finish)
        else 0 end as downloadFinishCost,


        case
        when sum(a.register) != 0
        then sum(a.cost)/sum(a.register)
        else 0 end as activeRegisterCost,
        sum(a.show_material) as showMaterial,

        case
        when sum(a.download_start) != 0
        then sum(a.download_finish)/sum(a.download_start)
        else 0 end as downloadFinishRate,

        case
        when sum(a.download_finish) != 0
        then sum(a.install_finish)/sum(a.download_finish)
        else 0 end as installFinishRate,
        sum(a.coupon) as coupon,
        sum(a.coupon_single_page) as couponSinglePage,
        sum(a.play_over) as playOver,

        case
        when sum(a.download_start) != 0
        then sum(a.cost)/sum(a.download_start)
        else 0 end as downloadStartCost,
        sum(a.message) as message,
        case
        when sum(a.show_material) != 0
        then sum(a.valid_play)/sum(a.show_material)
        else 0 end as validPlayRate,

        case
        when sum(a.valid_play) != 0
        then sum(a.average_play_time_per_play*a.valid_play)/sum(a.valid_play)
        else 0 end as averagePlayTimePerPlay,

        case
        when sum(a.convert_material) != 0
        then sum(a.cost)/sum(a.convert_material)
        else 0 end as convertCost,
        case
        when sum(a.install_finish) != 0
        then sum(a.cost)/sum(a.install_finish)
        else 0 end as installFinishCost,

        case
        when sum(a.click) != 0
        then sum(a.download_start)/sum(a.click)
        else 0 end as downloadStartRate,
        c.company_id as companyId
        from ctop_bytedance_report_material_daily a
        left join ctop_user_allocation b on a.account_id = b.account_id
        left join user_company c on b.user_id = c.user_id
        left join (select signature,video_url,material_id from ctop_bytedance_video_info group by material_id) d on
        a.material_id = d.material_id
        where c.company_id = #{companyId}
        and stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        <if test="accountId != null">
            and a.account_id = #{accountId}
        </if>
        group by `statDatetime`,`accountId`,`signature`
    </select>

    <select id="getCompanyIdByAccountId" resultType="string">
        select c.company_id
        from ctop_user_allocation b
                 left join user_company c on b.user_id = c.user_id
        where b.account_id = #{accountId} limit 1
    </select>

    <select id="videoInfoListGroupOperator"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id from ctop_material_ascription group by material_id) f on a.signature =
        f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and f.material_id is not null
        and a.account_id in
        <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="videoInfoListGroupOtherRoles"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id from ctop_material_ascription group by material_id) f on a.signature =
        f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and f.material_id is not null
        and a.account_id in
        <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
            #{item}
        </foreach>
        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="videoInfoListGroupShot"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and f.material_id is not null
        and f.shot_id = #{userId}
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="videoInfoListGroupAdmin"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and f.material_id is not null
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>
    </select>

    <select id="videoInfoListGroupdesignTeamLeader"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and a.company_id = #{companyId}
        and f.material_id is not null
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="videoInfoListGroupPlaneAndPlaneLeader"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}

        and f.material_id is not null
        and f.plane_id = #{userId}
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="videoInfoListGroupPlan"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and f.material_id is not null
        and f.plan_id = #{userId}
        <if test="accountIds != null">
            and a.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="videoInfoListGroupClip"
            resultType="cn.com.ctop.toutiao.modules.report.entity.ByteDanceVideoReportDaily">
        select
        <include refid="selectSql"></include>
        from ctop_bytedance_video_report_daily a
        left join (select material_id,clip_id,shot_id,plan_id,plane_id from ctop_material_ascription group by
        material_id) f on a.signature = f.material_id
        where stat_datetime &gt;= #{startDate}
        and stat_datetime &lt;= #{endDate}
        and f.material_id is not null
        and f.clip_id = #{userId}
        <if test="accountIds != null">
            and account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="signature != null and signature != '' ">
            and a.signature = #{signature}
        </if>
        group by a.signature
        <if test="cost != null">
            having sum(a.cost) &gt;= #{cost}
        </if>

    </select>

    <select id="getAccountIds" resultType="long">
        select account_id
        from ctop_bytedance_video_report_daily
        where signature = #{signature}
          and company_id = #{companyId}
    </select>

    <select id="getAccountIdsByUserIdAndMediaIds" resultType="long">
        select
        account_id
        from
        ctop_user_allocation
        where
        project_id in (
        select
        project_id
        from ctop_project_member member
        left join ctop_project pro on member.project_id = pro.id
        where member.user_id =#{userId}
        and pro.media_id in
        <foreach collection="mediaIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        )
    </select>
</mapper>
