<?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.kuaishou.modules.batch.mapper.KuaishouVideoRelateCreativesMapper">

    <insert id="insertBatch">
        replace into ctop_kuaishou_video_relate_creatives(
        account_id,
        photo_id,
        creative_count,
        stat_date,
        channel_type
        )
        values
        <foreach collection="videolist" item="video" separator=",">
            (
            #{video.accountId},
            #{video.photoId},
            #{video.creativeCount},
            #{video.statDate},
            #{video.channelType}
            )
        </foreach>
    </insert>


    <select id="getVideoListByMap" resultType="com.alibaba.fastjson.JSONObject">
        select
        t1.photo_id as 'photo_id',
        t1.stat_date as 'stat_date',
        t1.creative_count as 'creative_count'
        FROM ctop_kuaishou_video_relate_creatives AS t1
        WHERE 1 = 1
        <if test="accountId != null and accountId != '' ">
            and t1.account_id = #{accountId}
        </if>
        <if test="createCount != null and createCount != '' ">
            AND t1.creative_count &lt;= #{createCount}
        </if>
        <if test="startTime != null and startTime != '' ">
            and t1.stat_date &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != '' ">
            and t1.stat_date &lt;= #{endTime}
        </if>
        <if test="channelType != null and channelType != '' ">
            and t1.channel_type = #{channelType}
        </if>
        <if test="appVersion != null and appVersion != '' ">
            and t1.photo_id in (
            select photo_id from ctop_kuaishou_video_get
            where account_id = #{accountId}
            and photo_name like CONCAT(#{appVersion},'%')
            and status = 0
            )
        </if>
        LIMIT #{num}
    </select>

    <select id="getVideoZeroListByMap" resultType="com.alibaba.fastjson.JSONObject">
        select
        photo_id as 'photo_id',
        stat_date as 'stat_date',
        creative_count as 'creative_count'
        FROM ctop_kuaishou_video_relate_creatives
        where 1 = 1
        <if test="accountId != null and accountId != '' ">
            and account_id = #{accountId}
        </if>
        AND creative_count = 0
        <if test="startTime != null and startTime != '' ">
            AND stat_date &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != '' ">
            AND stat_date &lt;= #{endTime}
        </if>
        <if test="channelType != null and channelType != '' ">
            and channel_type = #{channelType}
        </if>
        <if test="appVersion != null and appVersion != '' ">
            and photo_id in (
            select photo_id from ctop_kuaishou_video_get
            where account_id = #{accountId}
            and photo_name like concat(#{appVersion},'%')
            and status = 0
            )
        </if>
        ORDER BY stat_date ASC
        LIMIT #{videoCnt}
    </select>

    <select id="getHistoryTopVideoList" resultType="com.alibaba.fastjson.JSONObject">
        select t3.* from (
        select
        t2.photo_id,
        t1.charge
        FROM
        (
        SELECT
        sum(charge) as 'charge',
        signature
        FROM
        ctop_kuaishou_report_daily_material
        WHERE
        signature IN (
        SELECT DISTINCT
        (signature)
        FROM
        ctop_kuaishou_video_get
        WHERE
        account_id = #{accountId}
        and stat_date &gt;= #{startDate}
        and stat_date &lt;= #{endDate}
        <if test="appVersion != null and appVersion != '' ">
            and photo_name like concat(#{appVersion},'%')
        </if>
        <if test="channelType != null and channelType != '' ">
            and channel_type = #{channelType}
        </if>
        and status = 0
        and signature is not null
        )
        GROUP BY
        signature
        ORDER BY
        sum(charge) DESC
        LIMIT #{num}
        ) t1
        LEFT JOIN (
        SELECT
        photo_id,
        signature
        FROM
        ctop_kuaishou_video_get
        WHERE
        account_id = #{accountId}
        ) t2 ON t1.signature = t2.signature
        ORDER BY t1.charge DESC
        ) t3 left join
        ( SELECT id,photo_id from ctop_ai_creative_limit WHERE account_id = #{accountId} ) t4 on t3.photo_id = t4.photo_id
        where t4.id is null
    </select>
</mapper>