<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.fileupload.mapper.ExternalMaterialCollectionMapper">


    <insert id="replaceBatch">
        replace into ctop_external_material_collection (
        user_id,
        media_type,
        create_type,
        stat_date,
        md5,
        url,
        external_url,
        cover_url,
        material_source,
        industry,
        product,
        modality_tag,
        scene_tag,
        da_tag,
        plate_position,
        material_describe,
        material_name,
        `second`,
        format,
        width,
        height,
        `size`
        )
        values
        <foreach collection="list" item="item" separator=",">
            (
            #{item.userId},
            #{item.mediaType},
            #{item.createType},
            #{item.statDate},
            #{item.md5},
            #{item.url},
            #{item.externalUrl},
            #{item.coverUrl},
            #{item.materialSource},
            #{item.industry},
            #{item.product},
            #{item.modalityTag},
            #{item.sceneTag},
            #{item.daTag},
            #{item.platePosition},
            #{item.materialDescribe},
            #{item.materialName},
            #{item.second},
            #{item.format},
            #{item.width},
            #{item.height},
            #{item.size}
            )
        </foreach>
    </insert>

    <select id="getListTotal" resultType="java.lang.Long">
        SELECT
        IFNULL( SUM( 1 ), 0 )
        FROM
        ctop_external_material_collection
        <where>
            <if test="materialName != null">
                AND material_name like CONCAT('%',#{materialName}, '%')
            </if>
            <if test="mediaType != null">
                AND media_type =#{mediaType}
            </if>
            <if test="startTime != null">
                AND stat_date &gt;= #{startTime}
            </if>
            <if test="endTime != null">
                AND stat_date &lt;=#{endTime}
            </if>
            <if test="daTag != null">
                AND da_tag like CONCAT('%"', #{daTag}, '"%')
            </if>
            <if test="sceneTag != null">
                AND scene_tag like CONCAT('%"', #{sceneTag}, '"%')
            </if>
            <if test="modalityTag != null">
                AND modality_tag like CONCAT('%"', #{modalityTag}, '"%')
            </if>
            <if test="industry != null">
                AND industry = #{industry}
            </if>
            <if test="materialSource != null">
                AND material_source = #{materialSource}
            </if>
            <if test="platePosition != null">
                AND plate_position LIKE CONCAT('%',#{platePosition}, '%')
            </if>
        </where>
    </select>

    <select id="queryPageList" resultType="cn.com.ctop.common.module.utils.ExternalMaterialCollection">
        SELECT
        *
        FROM
        ctop_external_material_collection
        <where>
            <if test="materialName != null">
                AND material_name like CONCAT('%',#{materialName}, '%')
            </if>
            <if test="mediaType != null">
                AND media_type =#{mediaType}
            </if>
            <if test="startTime != null">
                AND stat_date &gt;= #{startTime}
            </if>
            <if test="endTime != null">
                AND stat_date &lt;=#{endTime}
            </if>
            <if test="daTag != null">
                AND da_tag like CONCAT('%"', #{daTag}, '"%')
            </if>
            <if test="sceneTag != null">
                AND scene_tag like CONCAT('%"', #{sceneTag}, '"%')
            </if>
            <if test="modalityTag != null">
                AND modality_tag like CONCAT('%"', #{modalityTag}, '"%')
            </if>
            <if test="industry != null">
                AND industry = #{industry}
            </if>
            <if test="materialSource != null">
                AND material_source = #{materialSource}
            </if>
            <if test="platePosition != null">
                AND plate_position LIKE CONCAT('%',#{platePosition}, '%')
            </if>
        </where>
        ORDER BY create_time DESC
    </select>


    <select id="getIndustryLabel" resultType="Map">
        select parent_id as 'parentId',name
        from ctop_external_material_industry
        where parent_id != 0
    </select>

    <select id="getParentIndustryLabel" resultType="com.alibaba.fastjson.JSONObject">
        select id, name
        from ctop_external_material_industry
        where parent_id = 0
    </select>


    <select id="getMd5ByMediaType" resultType="com.alibaba.fastjson.JSONObject">
        SELECT user_id as 'userId', signature as 'md5', form_tag_list as 'formTagList', mood_tag_list as 'moodTagList', scene_tag_list as 'sceneTagList', industry_id as 'industry', DATE_FORMAT(hot_date, '%Y-%m-%d') as 'statDate'
        FROM application.hot_material_tag_info
        where media_id = #{mediaType};
    </select>


    <select id="getKuaiShouInternalMaterial" resultType="cn.com.ctop.common.module.utils.ExternalMaterialCollection">
        SELECT
        t2.signature as 'md5',
        t2.photo_name as 'materialName',
        t2.url,
        t2.cover_url,
        6 as 'materialSource',
        t3.second,
        t3.format,
        IFNULL(t3.width,t2.width) as 'width',
        IFNULL(t3.height,t2.height) as 'height',
        t3.size,
        4 as 'createType',
        2 as 'mediaType'
        FROM ctop_kuaishou_video_get t2
        LEFT JOIN ctop_material_parameter t3 ON t2.signature= t3.material_id
        WHERE t2.signature IN
        <foreach collection="md5List" item="item" separator=","
                 open="(" close=")">
            #{item.md5}
        </foreach>
        group by t2.signature
    </select>

    <select id="getBytedanceInternalMaterial" resultType="cn.com.ctop.common.module.utils.ExternalMaterialCollection">
        SELECT
        t1.signature AS 'md5',
        t1.filename AS 'materialName',
        t1.video_url as 'url',
        if(t1.poster_url='素材所属主体与开发者主体不一致无法获取URL',t2.cover_url,t1.poster_url) as 'coverUrl',
        5 AS 'materialSource',
        ROUND(t1.duration) as 'second',
        t1.format,
        t1.width AS 'width',
        t1.height,
        t1.size,
        4 AS 'createType',
        1 AS 'mediaType'
        FROM
        ctop_bytedance_video_info t1
        LEFT JOIN ctop_material_info t2 ON t1.signature = t2.code
        WHERE
        signature IN
        <foreach collection="md5List" item="item" separator=","
                 open="(" close=")">
            #{item.md5}
        </foreach>
        group by signature
    </select>

</mapper>