<?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.KuaiShouVideoGetMapper">
    <insert id="replaceBatch">
        replace into ctop_kuaishou_video_get(
        id,
        account_id,
        status,
        photo_id,
        width,
        height,
        url,
        cover_url,
        material_type,
        channel_type,
        stat_date,
        signature,
        photo_name,
        is_kai_ping
        )
        values
        <foreach collection="videos" item="video" separator=",">
            (
            #{video.id},
            #{video.accountId},
            #{video.status},
            #{video.photoId},
            #{video.width},
            #{video.height},
            #{video.url},
            #{video.coverUrl},
            #{video.materialType},
            #{video.channelType},
            #{video.statDate},
            #{video.signature},
            #{video.photoName},
            #{video.isKaiPing}
            )
        </foreach>
    </insert>


    <select id="getVideoList" resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">
        select
        stat_date as 'statDate',
        photo_id as 'photoId',
        url as 'url',
        cover_url as 'coverUrl',
        signature as 'signature',
        material_type as 'materialType',
        photo_name as 'photoName',
        width,
        height,
        is_kai_ping as 'isKaiPing'
        from ctop_kuaishou_video_get
        where account_id = #{accountId}
        and material_type = #{materialType}
        and status = 0
        <if test="photoName != null and photoName != '' ">
            and photo_name like CONCAT('%',#{photoName},'%')
        </if>
        <if test="kaiPing != null ">
            and is_kai_ping = #{kaiPing}
        </if>
        <if test="startDate != null and startDate != '' ">
            and stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and stat_date &lt;= #{endDate}
        </if>
        <if test="signatureList != null and signatureList != '' ">
            and signature in
            <foreach collection="signatureList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by signature
        <if test="kaiPing != null ">
        order by photo_name, height
        </if>
        <if test="kaiPing == null ">
            order by stat_date desc
        </if>
    </select>


    <select id="getHighQualityVideo" parameterType="java.util.Map"
            resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaiShouVideoGet">
        select *
        from ctop_kuaishou_video_get
        where account_id = #{accountId}
          and signature in
              (
                  select daily_material.signature
                  from ctop_kuaishou_report_daily_material daily_material
                           inner join
                       ctop_user_allocation allocation
                       on daily_material.account_id = allocation.account_id
                  where allocation.project_id in
                        (select project_id from ctop_user_allocation where account_id = #{accountId})
                    and to_days(now()) - to_days(daily_material.stat_date) &lt;= #{days}
                    and daily_material.signature is not null
                  GROUP BY daily_material.signature
                  order by sum(daily_material.charge) desc
            limit #{num}
            )
    </select>

    <select id="selectPhotoIdsByAccountId" parameterType="java.lang.Long"
            resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaiShouVideoGet">
        select photo_id, stat_date, channel_type
        from ctop_kuaishou_video_get
        where account_id = #{accountId}
          and status = 0
    </select>

    <select id="getVideoListByMap" resultType="com.alibaba.fastjson.JSONObject">
        select
        photo_id as 'photo_id',
        signature as 'signature',
        stat_date as 'stat_date',
        material_type as 'material_type',
        channel_type as 'channel_type',
        url as 'video_url',
        photo_name as 'photo_name'
        from ctop_kuaishou_video_get
        where 1 = 1
        <if test="accountId != null and accountId != '' ">
            and account_id = #{accountId}
        </if>
        <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_name like concat(#{appVersion},'%')
        </if>
        and signature is not null
        and status = 0
        group by signature
        order by stat_date desc
    </select>
    <select id="getPhotoIdsByParams" resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaiShouVideoGet">
        select distinct photo_id
        from ctop_kuaishou_video_get
        where account_id = #{accountId}
          and signature = #{signature}
          and status = 0
    </select>

    <insert id="etlVideoBaseInfoByStaDate">
        replace
        INTO ctop_etl_kuaishou_video_base_info (
	signature,
	channel_type,
	photo_id,
    photo_name,
    material_type
    )
        SELECT signature,
               channel_type,
               photo_id,
               photo_name,
               material_type
        FROM ctop_kuaishou_video_get
        where stat_date &gt;= #{statDate}
          and signature is not null
        GROUP BY photo_id
    </insert>

    <select id="getVideoInfoByPhotoIds" resultType="com.alibaba.fastjson.JSONObject">
        select
        photo_id as 'photoId',
        signature as 'signature',
        channel_type as 'channelType',
        photo_name as 'photoName',
        material_type as 'materialType'
        from ctop_etl_kuaishou_video_base_info
        where photo_id in
        <foreach collection="photoIdList" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        and signature is not null
        group by photo_id
    </select>
    <select id="getTotalCountByAccount" resultType="java.lang.Integer">
        select count(distinct signature)
        from ctop_kuaishou_video_get
        where account_id = #{accountId}
    </select>
    <select id="getTotalCountByAccountAndStatDate" resultType="java.lang.Integer">
        SELECT count(DISTINCT signature)
        FROM ctop_kuaishou_video_get
        WHERE account_id = #{accountId}
          AND DATE_FORMAT(stat_date, '%Y-%m-%d') = #{statDate}
    </select>
    <select id="getEffectiveCountByAccountId" resultType="java.lang.Integer">
        SELECT count(1)
        FROM (
                 SELECT signature
                 FROM ctop_kuaishou_video_get
                 WHERE account_id = #{accountId}
                 GROUP BY signature
             ) t1
                 LEFT JOIN ctop_kuaishou_video_etl_info t2 ON t1.signature = t2.video_code
        WHERE t2.effect_date IS NOT NULL
          AND t2.effect_day_num &lt;= 7

    </select>
    <select id="getZeroVideoByParams" resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaiShouVideoGet">
        select
        video.*
        from
        ctop_kuaishou_video_get video left join ctop_kuaishou_video_relate_creatives creative on video.photo_id =
        creative.photo_id and video.account_id = creative.account_id
        where
        1=1
        <if test="relativeCnt!=null">
            and creative.creative_count &lt;= #{relativeCnt}
        </if>

        <if test="accountId!=null">
            and video.account_id = #{accountId}
        </if>
        <if test="startTime!=null">
            and video.stat_date &gt;= #{startTime}
        </if>
        <if test="endTime!=null">
            and video.stat_date &lt;= #{endTime}
        </if>
        <if test="channelType != null and channelType != '' ">
            and video.channel_type = #{channelType}
        </if>
        <if test="appVersion != null and appVersion != '' ">
            and video.photo_name like concat(#{appVersion},'%')
        </if>
        and video.status = 0
        group by video.signature
        order by video.stat_date desc
        <if test="videoCnt!=null">
            limit #{videoCnt}
        </if>
    </select>

    <select id="getHotMoneyCountByAccountId" resultType="java.lang.Integer">
        SELECT count(1)
        FROM (
                 SELECT signature
                 FROM ctop_kuaishou_video_get
                 WHERE account_id = #{accountId}
                 GROUP BY signature
             ) t1
                 LEFT JOIN ctop_kuaishou_video_etl_info t2 ON t1.signature = t2.video_code
        WHERE t2.faddish_date is not null
          and faddish_day_num &lt;= 14
    </select>
    <select id="getHistoryTopVideoByParams"
            resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaiShouVideoGet">
        select
        video.*
        from
        ctop_kuaishou_report_daily_material report
        left join (select * from ctop_kuaishou_video_get
        where
        status = 0
        and signature is not null
        <if test="accountId!=null">
            and account_id = #{accountId}
        </if>
        <if test="channelType!=null">
            and channel_type = #{channelType}
        </if>
        <if test="startTime!=null">
            and stat_date &gt;= #{startTime}
        </if>
        <if test="endTime!=null">
            and stat_date &lt;= #{endTime}
        </if>
        <if test="appVersion != null and appVersion != '' ">
            and photo_name like concat(#{appVersion},'%')
        </if>
        group by signature) video
        on video.signature = report.signature
        where video.id is not null
        and video.photo_id NOT IN (
        SELECT
        photo_id
        FROM
        ctop_ai_creative_limit
        WHERE
        account_id = #{accountId}
        )
        group by video.signature
        order by sum(report.charge) desc
        <if test="videoCnt!=null">
            limit #{videoCnt}
        </if>
    </select>

    <select id="selectProjectVideo" resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaishouProjectVideoGet">
        SELECT
        t1.id,
        t2.project_id,
        t1.status,
        t1.photo_id,
        t1.width,
        t1.height,
        t1.url,
        t1.cover_url,
        t1.material_type,
        t1.channel_type,
        t1.stat_date,
        t1.signature,
        t1.photo_name,
        t1.update_time,
        material_tag,
        supplier_account_id,
        supplier_account_name
        FROM ctop_kuaishou_video_get t1
        INNER JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        WHERE t2.project_id=#{projectId}
        <if test="updateTime!=null and updateTime!=''">
            AND t1.update_time &gt; #{updateTime}
        </if>
    </select>

    <select id="getAccountIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.account_id 'accountId',DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 15 MINUTE), '%Y-%m-%d %H:%i:%s') 'startDate'
        FROM ctop_kuaishou_video_get t1
                 LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        where channel_type = 1
          AND t2.account_status = 0
          AND t1.status = 0
          AND t1.stat_date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 DAY), '%Y-%m-%d')
        GROUP BY t1.account_id
    </select>

    <select id="queryPhotoIdsByProjectAndMd5" resultType="java.lang.String">
        SELECT t1.photo_id
        FROM ctop_kuaishou_video_get t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        where t1.status = 0
        AND t2.project_id = #{projectId}
        AND t1.signature =#{signature}
        <if test="accountId !=null">
            AND t1.account_id IN
            <foreach collection="accountId" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY photo_id
    </select>
    <select id="getVideoByPhotoIdAndAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select cover_url as 'coverUrl', url as 'photoUrl'
        from ctop_kuaishou_video_get
        where account_id = #{accountId}
          and photo_id = #{photoId} limit 1

    </select>

    <select id="getVideoListByDate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT account_id as 'accountId', signature as 'signature', photo_name as 'photoName', url as 'url', cover_url as 'coverUrl', channel_type as 'channelType'
        from ctop_kuaishou_video_get
        WHERE stat_date BETWEEN #{startDate} AND #{endDate}
        group by signature

    </select>
    <select id="getDateList" resultType="java.lang.String">
        SELECT *
        FROM (
                 SELECT DISTINCT (
                                     DATE_FORMAT(stat_date, '%Y-%m-%d')
                                     ) AS 'date'
                 FROM ctop_kuaishou_video_get
                 where stat_date is not null
             ) t1
        ORDER BY t1.date asc

    </select>

    <update id="updateBatch">
        UPDATE ctop_kuaishou_video_get SET
        material_tag = CASE id
        <foreach item="videoGet" index="index" collection="videoGetList">
            WHEN #{videoGet.id} THEN #{videoGet.materialTag}
        </foreach>
        END,
        supplier_account_id = CASE id
        <foreach item="videoGet" index="index" collection="videoGetList">
            WHEN #{videoGet.id} THEN #{videoGet.supplierAccountId}
        </foreach>
        END,
        supplier_account_name = CASE id
        <foreach item="videoGet" index="index" collection="videoGetList">
            WHEN #{videoGet.id} THEN #{videoGet.supplierAccountName}
        </foreach>
        END
        WHERE id IN
        <foreach item="videoGet" index="index" collection="videoGetList" open="(" separator="," close=")">
            #{videoGet.id}
        </foreach>
    </update>

    <select id="getVideoListByPhotoIds" resultType="cn.com.ctop.kuaishou.modules.batch.entity.KuaiShouVideoGet">
        SELECT
        photo_id,
        cover_url,
        material_type
        FROM ctop_kuaishou_video_get
        WHERE account_id = #{accountId}
        AND photo_id IN
        <foreach item="phtotId" index="index" collection="photoIds" open="(" separator="," close=")">
            #{phtotId}
        </foreach>
    </select>

</mapper>
