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


    <insert id="loadProgramCreativeDailyReport">
		LOAD DATA local INFILE  #{localPath}
		INTO TABLE ctop_kuaishou_report_daily_program_creative
		FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY '\n'
		IGNORE 1 LINES
		(
		creative_id,
		unit_id,
		campaign_id,
		unit_name,
		campaign_name,
		photo_id,
		@photo_url,
		@image_token,
		@cover_url,
		@description,
		@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>


    <insert id="loadCreativeDailyReport">
		LOAD DATA local INFILE  #{localPath}
		INTO TABLE ctop_kuaishou_report_daily_creative
		FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY '\n'
		IGNORE 1 LINES
		(
		creative_id,
		creative_name,
		status,
		campaign_id,
		campaign_name,
		unit_id,
		unit_name,
		@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>
    <update id="formatCreativeDailyReportData">
        UPDATE
        ctop_kuaishou_report_daily_creative t1
        SET
        t1.signature = (
        SELECT
        DISTINCT t3.signature
        FROM
        ctop_kuaishou_creative t2
        LEFT JOIN
        ctop_kuaishou_video_get t3 ON t2.photo_id = t3.photo_id
        WHERE
        t2.creative_id = t1.creative_id
        limit 1
        )
        WHERE
        1=1
        <if test="accountId != null">
            and t1.account_id = #{accountId}
        </if>
        <if test="statDate != null">
            and t1.stat_date = #{statDate}
        </if>
    </update>

    <select id="selectCodeByDate" resultType="com.alibaba.fastjson.JSONObject">
   select
   GROUP_CONCAT(DISTINCT(account_id)) accountId,
   sum(charge) charge,
   signature signature
   from
   ctop_kuaishou_report_daily_creative
   where stat_date &gt;= #{startDate}
   and stat_date &lt;= #{endDate}
   and signature is not null
   group by signature
    </select>

    <select id="getSignatureList" resultType="java.lang.String">
    SELECT
      DISTINCT(signature)
      from ctop_kuaishou_report_daily_creative
      WHERE signature !=''
    </select>

    <select id="checkZombieProject" resultType="com.alibaba.fastjson.JSONObject">
      SELECT * FROM
		(SELECT
			count(t1.stat_date) AS dates,
			sum(t1.charge) AS charge,
			t1.account_id AS accountId,
			t1.creative_id AS creativeId,
			t1.creative_name AS creativeName,
			t1.campaign_id AS campaignId,
			t1.campaign_name AS campaignName,
			t1.unit_id AS unitId,
			t1.unit_name AS unitName,
			t3.id AS projectId,
			t3.project_name AS projectName,
			t2.user_id as userId
		FROM
			ctop_kuaishou_report_daily_creative t1
		LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
		LEFT JOIN ctop_project t3 ON t2.project_id = t3.id
        where stat_date >= #{date}
        GROUP BY t1.account_id,t1.creative_id) as t
      WHERE t.dates = 5 and t.charge = 0
    </select>
    <delete id="deleteByAccountId">
   delete  from ctop_kuaishou_report_daily_program_creative
   where  account_id = #{accountId}

    </delete>
    <delete id="deleteByAccountIdAndStatDate">
   delete  from ctop_kuaishou_report_daily_program_creative
   where account_id = #{accountId}
   and stat_date = #{statDate}

    </delete>


</mapper>
