<?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.material.mapper.ProductMaterialMapper">

    <insert id="replaceBatch">
        replace into ctop_product_material(
        product_id,
        product_name,
        trade_name,
        trade_type,
        video_url,
        signature,
        start_month,
        end_month,
        user_id,
        uploader_id
        )
        values
        <foreach collection="list" item="pro" separator=",">
            (
            #{pro.productId},
            #{pro.productName},
            #{pro.tradeName},
            #{pro.tradeType},
            #{pro.videoUrl},
            #{pro.signature},
            #{pro.startMonth},
            #{pro.endMonth},
            #{pro.userId},
            #{pro.uploaderId}
            )
        </foreach>
    </insert>

    <select id="queryPageListTotal" resultType="java.lang.Long">
        SELECT
        IFNULL(sum(1),0)
        from (
        select
        trade_name
        FROM ctop_product_material
        <where>
            <if test="productId!=null">
                and product_id = #{productId}
            </if>
            <if test="tradeName!=null">
                and trade_name like CONCAT(CONCAT('%', #{tradeName}), '%')
            </if>
            <if test="startMonth!=null">
                and start_month >= #{startMonth}
            </if>
            <if test="endMonth!=null">
                AND (end_month &lt;= #{endMonth} OR (end_month IS NULL AND start_month &lt;= #{endMonth}))
            </if>
            <if test="itemRun!=null">
                and item_run = #{itemRun}
            </if>
        </where>
        group by product_id,trade_name
        ) t
    </select>


    <select id="queryPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.product_id as 'productId',
        t1.product_name as 'productName',
        t1.trade_name as 'tradeName',
        t1.trade_type as 'tradeType',
        t1.item_run as 'itemRun',
        IF(t4.videoCount is null ,'-',IFNULL(sum(t2.charge),0.00)) as 'charge',
        CONCAT_WS(' ~ ',t1.start_month,t1.end_month) AS 'time',
        IFNULL(t3.realname,'-') as 'user',
        IFNULL(t4.videoCount ,'-') as 'videoCount'
        FROM ctop_product_material t1
        LEFT JOIN
        (
        SELECT signature , sum(charge) as 'charge' FROM application.kuaishou_material_video_report_daily_dw
        <where>
            <if test="productId!=null">
                and product_id = #{productId}
            </if>
            <if test="startDate!=null">
                AND stat_date >=DATE_FORMAT(#{startDate}, '%Y%m%d' )
            </if>
            <if test="endDate!=null">
                AND stat_date &lt;=DATE_FORMAT(#{endDate}, '%Y%m%d' )
            </if>
        </where>
        GROUP BY signature
        ) t2 ON t1.signature = t2.signature
        LEFT JOIN (select id, realname from sys_user) t3 on t1.user_id =t3.id
        LEFT JOIN ( SELECT product_id, trade_name, sum(1)AS 'videoCount' FROM ctop_product_material
        WHERE video_url is not null
        GROUP BY product_id,trade_name) t4 ON t1.product_id = t4.product_id AND t1.trade_name= t4.trade_name
        <where>
            <if test="productId!=null">
                and t1.product_id = #{productId}
            </if>
            <if test="tradeName!=null">
                and t1.trade_name like CONCAT(CONCAT('%', #{tradeName}), '%')
            </if>
            <if test="startMonth!=null">
                and t1.start_month >= #{startMonth}
            </if>
            <if test="endMonth!=null">
                AND (t1.end_month &lt;= #{endMonth} OR (t1.end_month IS NULL AND t1.start_month &lt;= #{endMonth}))
            </if>
            <if test="itemRun!=null">
                and t1.item_run = #{itemRun}
            </if>
        </where>
        group by t1.product_id,t1.trade_name
        ORDER BY t1.create_time desc
    </select>

    <select id="videoListTotal" resultType="java.lang.Long">
        SELECT IFNULL(sum(1), 0)
        from (
        select trade_name
        FROM ctop_product_material
        WHERE video_url is not null
        and product_id = #{productId}
        and trade_name = #{tradeName}
        and start_month >= #{startMonth}
        <if test="endMonth!=null">
            and end_month &lt;= #{endMonth}
        </if>
        group by signature
        ) t
    </select>

    <select id="videoList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.id,
        t1.product_name as 'productName',
        t1.trade_name as 'tradeName',
        t1.video_url as 'videoUrl',
        IFNULL(t2.charge, 0.00) as 'charge',
        IFNULL(t3.realname, '-') as 'user'
        FROM ctop_product_material t1
        LEFT JOIN (
        SELECT signature, sum(charge) as 'charge'
        FROM application.kuaishou_material_video_report_daily_dw
        where product_id = #{productId}
        AND stat_date >= DATE_FORMAT(#{startDate}, '%Y%m%d')
        AND stat_date &lt;= DATE_FORMAT(#{endDate}, '%Y%m%d')
        GROUP BY signature
        ) t2 ON t1.signature = t2.signature
        LEFT JOIN (select id, realname from sys_user) t3 on t1.uploader_id = t3.id
        WHERE video_url is not null
        and product_id = #{productId}
        and trade_name = #{tradeName}
        and start_month >= #{startMonth}
        <if test="endMonth!=null">
            and end_month &lt;= #{endMonth}
        </if>
        ORDER BY t1.create_time desc
    </select>

    <select id="getVideoDetailsReportList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.product_name as 'productName',
        t1.trade_name as 'tradeName',
        t1.start_month as 'startMonth',
        t1.end_month as 'endMonth',
        t1.video_url as 'videoUrl',
        IFNULL(t2.charge, 0.00) as 'charge',
        case t1.item_run
        WHEN 1 THEN '是'
        WHEN 0 THEN '否'
        ELSE '-' END as 'itemRun',
        IFNULL(t3.realname, '-') as 'user'
        FROM ctop_product_material t1
        LEFT JOIN
        ( SELECT product_id,signature , sum(charge) as 'charge' FROM application.kuaishou_material_video_report_daily_dw
        <where>
            <if test="productId!=null">
                and product_id = #{productId}
            </if>
            <if test="startDate!=null">
                AND stat_date >=DATE_FORMAT(#{startDate}, '%Y%m%d' )
            </if>
            <if test="endDate!=null">
                AND stat_date &lt;=DATE_FORMAT(#{endDate}, '%Y%m%d' )
            </if>
        </where>
        GROUP BY signature
        ) t2 ON t1.signature = t2.signature AND t1.product_id=t2.product_id
        LEFT JOIN (select id, realname from sys_user) t3 on t1.uploader_id =t3.id
        where video_url is not null
        <if test="productId!=null">
            and t1.product_id = #{productId}
        </if>
        <if test="tradeName!=null">
            and t1.trade_name like CONCAT(CONCAT('%', #{tradeName}), '%')
        </if>
        <if test="startMonth!=null">
            and t1.start_month >= #{startMonth}
        </if>
        <if test="endMonth!=null">
            AND (t1.end_month &lt;= #{endMonth} OR (t1.end_month IS NULL AND t1.start_month &lt;= #{endMonth}))
        </if>
        <if test="itemRun!=null">
            and t1.item_run = #{itemRun}
        </if>
        ORDER BY t1.create_time desc
    </select>

</mapper>