<?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.KuaishouReportDailyImageMapper">

    <insert id="loadImageDailyReport">
        LOAD DATA local INFILE  #{localPath}
	     INTO TABLE ctop_kuaishou_report_daily_image
		FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY '\n'
		IGNORE 1 LINES
		(
		cover_id,
        image_token,
        cover_url,
        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="imageCost" resultType="cn.com.ctop.common.module.vo.KuaishouImageCostVO">
        select * from (select
        report.cover_url as 'imageUrl' ,
        report.cover_id as 'signature',
        group_concat(distinct p.project_name) AS 'projectName',
        sum(report.charge) AS 'cost',
        sum(report.photo_click) AS 'photoClick',
        (case when sum(report.photo_show)!=0 and sum(report.photo_click)!=0
        then round(sum(photo_click)/sum(photo_show)*100,2)
        else 0 end)as 'photoClickRate',
        sum(report.photo_show) AS 'photoShow',
        (case when sum(report.charge)!=0 and sum(report.photo_show)!=0
        then round(sum(charge)/sum(photo_show)*1000,2)
        else 0 end)as 'photoShowCost',
        sum(report.aclick) as 'aclick',
        sum(report.bclick) as 'bclick',
        (case when sum(report.bclick)!=0 and sum(report.aclick)!=0
        then round(sum(bclick)/sum(aclick)*100,2)
        else 0 end)as 'bclickRate',
        (case when sum(report.charge)!=0 and sum(report.aclick)!=0
        then round(sum(charge)/sum(aclick)*1000,2)
        else 0 end)as 'aclickCost',
        sum(report.activation) AS 'activation',
        sum(report.event_register) AS 'eventRegister',
        sum(report.event_pay) AS 'eventPay',
        sum(report.form_count) AS 'formCount',
        sum(report.event_jin_jian_app) AS 'eventJinJianApp',
        sum(report.event_credit_grant_app) AS 'eventCreditGrantApp'
        from ctop_kuaishou_report_daily_image report
        left join ctop_user_allocation allocation on allocation.account_id = report.account_id
        left join ctop_project p on p.id = allocation.project_id
        where
            1=1
        and
            report.cover_id is not null
        <if test="startDate!=null">
            and report.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null">
            and report.stat_date &lt;= #{endDate}
        </if>
        <if test="accountList!=null">
            and report.account_id in
            <foreach item="accountId" collection="accountList" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        <if test="code!=null">
            and report.cover_id = #{code}
        </if>
        group by report.cover_id)a
        order by a.${target} ${order}
    </select>

    <select id="imageCostTotal" resultType="cn.com.ctop.common.module.vo.KuaishouImageCostVO">
        select
        sum(report.charge) AS 'cost',
        sum(report.photo_click) AS 'photoClick',
        (case when sum(report.photo_show)!=0 and sum(report.photo_click)!=0
        then round(sum(photo_click)/sum(photo_show)*100,2)
        else 0 end)as 'photoClickRate',
        sum(report.photo_show) AS 'photoShow',
        (case when sum(report.charge)!=0 and sum(report.photo_show)!=0
        then round(sum(charge)/sum(photo_show)*1000,2)
        else 0 end)as 'photoShowCost',
        sum(report.aclick) as 'aclick',
        sum(report.bclick) as 'bclick',
        (case when sum(report.bclick)!=0 and sum(report.aclick)!=0
        then round(sum(bclick)/sum(aclick)*100,2)
        else 0 end)as 'bclickRate',
        (case when sum(report.charge)!=0 and sum(report.aclick)!=0
        then round(sum(charge)/sum(aclick)*1000,2)
        else 0 end)as 'aclickCost',
        sum(report.activation) AS 'activation',
        sum(report.event_register) AS 'eventRegister',
        sum(report.event_pay) AS 'eventPay',
        sum(report.form_count) AS 'formCount',
        sum(report.event_jin_jian_app) AS 'eventJinJianApp',
        sum(report.event_credit_grant_app) AS 'eventCreditGrantApp'
        from ctop_kuaishou_report_daily_image report
        left join ctop_user_allocation allocation on allocation.account_id = report.account_id
        left join ctop_project p on p.id = allocation.project_id
        where
        1=1
        and
        report.cover_id is not null
        <if test="startDate!=null">
            and report.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null">
            and report.stat_date &lt;= #{endDate}
        </if>
        <if test="accountList!=null">
            and report.account_id in
            <foreach item="accountId" collection="accountList" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        <if test="code!=null">
            and report.cover_id = #{code}
        </if>
    </select>

    <select id="imageCostEntity" resultType="cn.com.ctop.common.module.vo.KuaishouImageCostVOEntity">
        select * from (select
        report.cover_url as 'imageUrl' ,
        report.cover_id as 'signature',
        group_concat(distinct p.project_name) AS 'projectName',
        sum(report.charge) AS 'cost',
        sum(report.photo_click) AS 'photoClick',
        (case when sum(report.photo_show)!=0 and sum(report.photo_click)!=0
        then round(sum(photo_click)/sum(photo_show)*100,2)
        else 0 end)as 'photoClickRate',
        sum(report.photo_show) AS 'photoShow',
        (case when sum(report.charge)!=0 and sum(report.photo_show)!=0
        then round(sum(charge)/sum(photo_show)*1000,2)
        else 0 end)as 'photoShowCost',
        sum(report.aclick) as 'aclick',
        sum(report.bclick) as 'bclick',
        (case when sum(report.bclick)!=0 and sum(report.aclick)!=0
        then round(sum(bclick)/sum(aclick)*100,2)
        else 0 end)as 'bclickRate',
        (case when sum(report.charge)!=0 and sum(report.aclick)!=0
        then round(sum(charge)/sum(aclick)*1000,2)
        else 0 end)as 'aclickCost',
        sum(report.activation) AS 'activation',
        sum(report.event_register) AS 'eventRegister',
        sum(report.event_pay) AS 'eventPay',
        sum(report.form_count) AS 'formCount',
        sum(report.event_jin_jian_app) AS 'eventJinJianApp',
        sum(report.event_credit_grant_app) AS 'eventCreditGrantApp',
        report.realname as 'userName'
        from ctop_kuaishou_report_daily_image report
        left join ctop_user_allocation allocation on allocation.account_id = report.account_id
        left join ctop_project p on p.id = allocation.project_id
        where
        1=1
        and
        report.cover_id is not null
        <if test="startDate!=null">
            and report.stat_date &gt;= #{startDate}
        </if>
        <if test="endDate!=null">
            and report.stat_date &lt;= #{endDate}
        </if>
        <if test="accountList!=null">
            and report.account_id in
            <foreach item="accountId" collection="accountList" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        <if test="code!=null">
            and report.cover_id = #{code}
        </if>
        group by report.cover_id)a
        order by a.${target} ${order}
    </select>

    <update id="updateImageReportCode">
        update ctop_kuaishou_report_daily_image a inner join  ctop_kuaishou_image_get  image on image.image_token =a.image_token  set a.code = image.signature
        where a.code is null
    </update>
    <update id="updateImageReportUserId">
        update ctop_kuaishou_report_daily_image a inner join  ctop_material_image_info  image on image.code =a.code  set a.user_id = image.user_id
        where a.code is not null and a.user_id is null
    </update>
    <update id="updateImageReportRealname">
        update ctop_kuaishou_report_daily_image a inner join  sys_user  user on user.id =a.user_id  set a.realname = user.realname
        where a.user_id is not null and a.realname is null
    </update>
</mapper>
