<?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.KuaiShouReportDailyMaterialMapper">
    <insert id="batchReplace">
        replace into ctop_kuaishou_report_daily_material(
        account_id,
        photo_id,
        channel_type,
        signature,
        image_token,
        cover_url,
        photo_url,
        creative_material_type_string,
        ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        form_action_ratio,
        event_order_paid_cost,
        event_next_day_stay,
        form_cost,
        form_count,
        play_3s_ratio
        )
        values
        <foreach collection="addList" item="material" separator=",">
            (
            #{material.accountId},
            #{material.photoId},
            #{material.channelType},
            #{material.signature},
            #{material.imageToken},
            #{material.coverUrl},
            #{material.photoUrl},
            #{material.creativeMaterialTypeString},
            #{material.adScene},
            #{material.charge},
            #{material.photoShow},
            #{material.aclick},
            #{material.bclick},
            #{material.photoShare},
            #{material.photoComment},
            #{material.photoLike},
            #{material.follow},
            #{material.cancelFollow},
            #{material.report},
            #{material.block},
            #{material.negative},
            #{material.downloadStarted},
            #{material.downloadCompleted},
            #{material.activation},
            #{material.submit},
            #{material.statDate},
            #{material.photoClick},
            #{material.photoClickRatio},
            #{material.actionRatio},
            #{material.impression1kCost},
            #{material.photoClickCost},
            #{material.actionCost},
            #{material.eventPayFirstDay},
            #{material.eventPayPurchaseAmountFirstDay},
            #{material.eventPayFirstDayRoi},
            #{material.eventPay},
            #{material.eventPayPurchaseAmount},
            #{material.eventPayRoi},
            #{material.eventRegister},
            #{material.eventRegisterCost},
            #{material.eventRegisterRatio},
            #{material.eventJinJianApp},
            #{material.eventJinJianAppCost},
            #{material.eventCreditGrantApp},
            #{material.eventCreditGrantAppCost},
            #{material.eventCreditGrantAppRatio},
            #{material.eventOrderPaid},
            #{material.eventOrderPaidPurchaseAmount},
            #{material.eventJinJianLandingPage},
            #{material.eventJinJianLandingPageCost},
            #{material.eventCreditGrantLandingPage},
            #{material.eventCreditGrantLandingPageCost},
            #{material.eventCreditGrantLandingRatio},
            #{material.eventNextDayStayCost},
            #{material.eventNextDayStayRatio},
            #{material.formActionRatio},
            #{material.eventOrderPaidCost},
            #{material.eventNextDayStay},
            #{material.formCost},
            #{material.formCount},
            #{material.play3sRatio}
            )
        </foreach>

    </insert>


    <insert id="loadMaterialDailyReport">
		LOAD DATA local INFILE  #{localPath}
	    INTO TABLE ctop_kuaishou_report_daily_material
		FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY '\n'
		IGNORE 1 LINES
		(photo_id,
		image_token,
		cover_url,
		photo_url,
		@photo_caption,
		@creative_material_type_string,
        @ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        @stat_hour,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        @click_1k_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_order_paid_cost,
        form_count,
        form_cost,
        form_action_ratio,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        event_next_day_stay,
        play_3s_ratio,
        @event_valid_clues,
        @event_valid_clues_cost,
        @ad_product_cnt,
        @event_goods_view,
        @merchant_reco_fans,
        @event_order_amount_roi,
        @event_goods_view_cost,
        @merchant_reco_fans_cost,
        @event_new_user_pay,
        @event_new_user_pay_cost,
        @event_new_user_pay_ratio,
        @event_button_click,
        @event_button_click_cost,
        @event_button_click_ratio,
        @play_5s_ratio,
        @play_end_ratio,
        @event_order_paid_roi,
        @event_new_user_jinjian_app,
        @event_new_user_jinjian_app_cost,
        @event_new_user_jinjian_app_roi,
        @event_new_user_credit_grant_app,
        @event_new_user_credit_grant_app_cost,
        @event_new_user_credit_grant_app_roi,
        @event_new_user_jinjian_page,
        @event_new_user_jinjian_page_cost,
        @event_new_user_jinjian_page_roi,
        @event_new_user_credit_grant_page,
        @event_new_user_credit_grant_page_cost,
        @event_new_user_credit_grant_page_roi,
        @event_appoint_form,
        @event_appoint_form_cost,
        @event_appoint_form_ratio,
        @event_appoint_jump_click,
        @event_appoint_jump_click_cost,
        @event_appoint_jump_click_ratio
        )
          set account_id = #{accountId}
    </insert>

    <select id="getPhotoList" resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterialVo">
        select
        id as id,
        photo_id as photoId
        from
        ctop_kuaishou_report_daily_material
        where account_id = #{accountId}
        <if test="statDate != null and statDate != '' ">
            and stat_date = #{statDate}
        </if>
        group by photo_id

    </select>

    <select id="getPhotoListByDate"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaiShouReportDailyMaterialVo">
        select
        id as id,
        photo_id as photoId
        from
        ctop_kuaishou_report_daily_material
        WHERE stat_date = #{statDate}
        and signature is null
        group by photo_id
    </select>

    <select id="getVideoGetByPhotoId" resultType="cn.com.ctop.kuaishou.modules.report.entity.VideoGetvo">
      select
      signature as 'signature',
      channel_type as 'channelType'
      from ctop_kuaishou_video_get
      where photo_id = #{photoId}
      and signature is not null
      limit 1
    </select>


    <select id="getVideoList" parameterType="java.util.Map"
            resultType="cn.com.ctop.kuaishou.modules.batch.entity.vo.KuaiShouVideoGetVo">

        SELECT
        sum(t1.charge) as 'charge',
        t1.photo_id AS 'photoId',
        t1.photo_url AS 'url',
        t1.cover_url AS 'coverUrl',
        t1.signature AS 'signature',
        t2.material_type AS 'materialType'
        FROM
        ctop_kuaishou_report_daily_material t1
        LEFT JOIN (
        SELECT
        material_type,
        signature,
        status
        FROM
        ctop_kuaishou_video_get
        WHERE
        account_id = #{accountId} group by signature) t2
        ON t1.signature = t2.signature
        WHERE
        t1.account_id = #{accountId}
        AND t1.channel_type = #{channelType}
        AND t2.material_type = #{materialType}
        AND t2.status = 0
        <if test="startDate != null and startDate != '' ">
            and t1.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != '' ">
            and t1.stat_date &lt;= #{endDate}
        </if>

        GROUP BY
        signature
        ORDER BY
        sum(t1.charge) DESC
    </select>


</mapper>