<?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.report.mapper.TencentMaterialVideoReportDailyDwMapper">

    <sql id="baseWhere">
        where 1 = 1
        <if test="accountIds != null and accountIds != ''">
            and account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </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="leaderId != null and leaderId != ''">
            and leader_id = #{leaderId}
        </if>
        <if test="channelType != null">
            and channel_type = #{channelType}
        </if>
        <if test="clipId != null and clipId != ''">
            and clip_id = #{clipId}
        </if>
        <if test="planId != null and planId != ''">
            and plan_id = #{planId}
        </if>
        <if test="shotId != null and shotId != ''">
            and shot_id = #{shotId}
        </if>
        <if test="companyId != null and companyId != ''">
            and clip_company_id = #{companyId}
        </if>
        <if test="materialType != null and materialType != ''">
            and material_type = #{materialType}
        </if>
    </sql>

    <select id="getTotalByMap" resultType="java.lang.Long">
        select count(1)
        from (
            select ifnull(nullif(signature, ''), material_id) as signature, material_type
            from application.adq_material_report_daily_dw
            <include refid="baseWhere"/>
            group by ifnull(nullif(signature, ''), material_id), material_type
        ) t
    </select>

    <select id="getTencentVideoReportByMap" resultType="com.alibaba.fastjson.JSONObject" parameterType="map">
        select t1.*
        from (
            select
                ifnull(clip_name, '-') as clip,
                ifnull(plan_name, '-') as plan,
                ifnull(shot_name, '-') as shot,
                ifnull(leader_name, '-') as designLeader,
                ifnull(video_name, material_id) as materialName,
                ifnull(key_frame_image_url, '-') as coverUrl,
                ifnull(photo_url, '-') as url,
                ifnull(channel_name, '-') as channel,
                ifnull(stat_date, '-') as createTime,
                ifnull(material_type, '-') as materialType,
                case material_type
                    when 'IMAGE' then '图片'
                    when 'VIDEO' then '视频'
                    else ifnull(material_type, '-')
                end as materialTypeName,
                ifnull(nullif(signature, ''), material_id) as signature,
                ifnull(project_name, '-') as projectName,
                ${filed}
            from application.adq_material_report_daily_dw
            <include refid="baseWhere"/>
            group by ifnull(nullif(signature, ''), material_id), material_type
            order by ${target} ${order}
        ) t1
    </select>

    <select id="queryVideoDetailBy" resultType="com.alibaba.fastjson.JSONObject">
        select
            t1.stat_date as statDate,
            t1.signature,
            t1.photo_url as url,
            t1.key_frame_image_url as coverUrl,
            ifnull(t1.material_type, '-') as materialType,
            case t1.material_type
                when 'IMAGE' then '图片'
                when 'VIDEO' then '视频'
                else ifnull(t1.material_type, '-')
            end as materialTypeName,
            ifnull(t1.video_name, t1.material_id) as materialName,
            t1.project_name as projectName,
            t1.advertiser_name as advertiserName,
            t1.stat_date as createTime,
            ${filed}
        from application.adq_material_report_daily_dw t1
        where ifnull(nullif(t1.signature, ''), t1.material_id) = #{signature}
          and t1.stat_date >= #{startDate}
          and t1.stat_date &lt;= #{endDate}
        <if test="materialType != null and materialType != ''">
            and t1.material_type = #{materialType}
        </if>
        <if test="accountIds != null">
            and t1.account_id in
            <foreach item="item" index="index" collection="accountIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        group by ifnull(nullif(t1.signature, ''), t1.material_id), t1.material_type, t1.stat_date
        order by t1.stat_date desc
    </select>
</mapper>
