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

    <insert id="etlKuaishouAccountMaterialReportDailyData">
        replace into ctop_etl_kuaishou_account_material_report_daily (
            company_id,
            company_name,
            project_id,
            project_name,
            account_id,
            signature,
            charge,
            photo_show,
            aclick,
            bclick,
            download_started,
            download_completed,
            activation,
            submit,
            stat_date,
            photo_click,
            event_pay_first_day ,
            event_pay_purchase_amount_first_day,
            event_pay,
            event_pay_purchase_amount,
            event_register,
            event_jin_jian_app,
            event_credit_grant_app,
            event_order_paid,
            event_order_paid_purchase_amount,
            event_jin_jian_landing_page,
            event_credit_grant_landing_page,
            event_next_day_stay,
            form_count,
            play_3s_count)
     select
     c.company_id,
     c.company_name,
     u.project_id,
     p.project_name,
     m.account_id,
     m.signature,
     sum(m.charge) as charge,
     sum(m.photo_show) as photo_show,
     sum(m.aclick) as aclick,
     sum(m.bclick) as bclick,
     sum(m.download_started) as download_started ,
     sum(m.download_completed) as download_completed,
     sum(m.activation) as activation,
     sum(m.submit) as submit,
     stat_date,
     sum(m.photo_click) as photo_click,
     sum(m.event_pay_first_day) as event_pay_first_day,
     sum(m.event_pay_purchase_amount_first_day) as event_pay_purchase_amount_first_day,
     sum(m.event_pay) as event_pay,
     sum(m.event_pay_purchase_amount) as event_pay_purchase_amount,
     sum(m.event_register) as event_register,
     sum(m.event_jin_jian_app) as event_jin_jian_app,
     sum(m.event_credit_grant_app) as event_credit_grant_app,
     sum(m.event_order_paid) as event_order_paid,
     sum(m.event_order_paid_purchase_amount) as event_order_paid_purchase_amount,
     sum(m.event_jin_jian_landing_page) as event_jin_jian_landing_page,
     sum(m.event_credit_grant_landing_page) as event_credit_grant_landing_page,
     sum(m.event_next_day_stay) as event_next_day_stay,
     sum(m.form_count)as form_count,
     round(sum(m.play_3s_ratio * m.photo_show),0) as play_3s_count
     from ctop_kuaishou_report_daily_material m left join ctop_user_allocation u on u.account_id = m.account_id
     left join ctop_project p on p.id = u.project_id
     left join user_company c on c.user_id = u.account_id
     where m.signature is not null
     and m.stat_date = #{date}
     group by m.account_id,m.signature
    </insert>


    <insert id="replaceBatch">
        replace into ctop_etl_kuaishou_account_material_report_daily(
        company_id ,
        company_name ,
        project_id ,
        project_name,
        account_id,
        signature,
        charge,
        photo_show ,
        aclick ,
        bclick ,
        download_started ,
        download_completed ,
        activation ,
        submit ,
        stat_date ,
        photo_click ,
        event_pay_first_day ,
        event_pay_purchase_amount_first_day ,
        event_pay ,
        event_pay_purchase_amount ,
        event_register ,
        event_jin_jian_app ,
        event_credit_grant_app ,
        event_order_paid ,
        event_order_paid_purchase_amount ,
        event_jin_jian_landing_page ,
        event_credit_grant_landing_page ,
        event_next_day_stay ,
        form_count,
        play_3s_count)
        values
        <foreach collection="records" item="record" separator=",">
            (
            #{record.companyId},
            #{record.companyName},
            #{record.projectId},
            #{record.projectName},
            #{record.accountId},
            #{record.signature},
            #{record.charge},
            #{record.photoShow},
            #{record.aclick},
            #{record.bclick},
            #{record.downloadStarted} ,
            #{record.downloadCompleted} ,
            #{record.activation},
            #{record.submit},
            #{record.statDate},
            #{record.photoClick},
            #{record.eventPayFirstDay},
            #{record.eventPayPurchaseAmountFirstDay},
            #{record.eventPay},
            #{record.eventPayPurchaseAmount},
            #{record.eventRegister},
            #{record.eventJinJianApp},
            #{record.eventCreditGrantApp},
            #{record.eventOrderPaid},
            #{record.eventOrderPaidPurchaseAmount},
            #{record.eventJinJianLandingPage},
            #{record.eventCreditGrantLandingPage} ,
            #{record.eventNextDayStay},
            #{record.formCount},
            #{record.play3sCount}
            )
        </foreach>
    </insert>
    <insert id="replace">
    replace into ctop_etl_kuaishou_account_material_report_daily(
        photo_id,
        photo_url,
        cover_url,
        company_id ,
        company_name ,
        project_id ,
        project_name,
        account_id,
        signature,
        charge,
        photo_show ,
        aclick ,
        bclick ,
        download_started ,
        download_completed ,
        activation ,
        submit ,
        stat_date ,
        photo_click ,
        event_pay_first_day ,
        event_pay_purchase_amount_first_day ,
        event_pay ,
        event_pay_purchase_amount ,
        event_register ,
        event_jin_jian_app ,
        event_credit_grant_app ,
        event_order_paid ,
        event_order_paid_purchase_amount ,
        event_jin_jian_landing_page ,
        event_credit_grant_landing_page ,
        event_next_day_stay ,
        form_count,
        play_3s_count)
        values
        (
       #{report.photoId},
    #{report.photoUrl},
    #{report.coverUrl},
            #{report.companyId},
            #{report.companyName},
            #{report.projectId},
            #{report.projectName},
            #{report.accountId},
            #{report.signature},
            #{report.charge},
            #{report.photoShow},
            #{report.aclick},
            #{report.bclick},
            #{report.downloadStarted} ,
            #{report.downloadCompleted} ,
            #{report.activation},
            #{report.submit},
            #{report.statDate},
            #{report.photoClick},
            #{report.eventPayFirstDay},
            #{report.eventPayPurchaseAmountFirstDay},
            #{report.eventPay},
            #{report.eventPayPurchaseAmount},
            #{report.eventRegister},
            #{report.eventJinJianApp},
            #{report.eventCreditGrantApp},
            #{report.eventOrderPaid},
            #{report.eventOrderPaidPurchaseAmount},
            #{report.eventJinJianLandingPage},
            #{report.eventCreditGrantLandingPage} ,
            #{report.eventNextDayStay},
            #{report.formCount},
            #{report.play3sCount}
        )
    </insert>

    <update id="etlKuaishouVideoReportStateDate">
        UPDATE ctop_etl_kuaishou_video_info info
            LEFT JOIN ( select video_code,min(report.stat_date) as minDate from ctop_kuaishou_report_daily_material report left join ctop_kuaishou_video_get ckvg on report.account_id = ckvg.account_id
            and report.photo_id = ckvg.photo_id
            left join ctop_etl_kuaishou_video_info cekvi on ckvg.signature = cekvi.video_code
            where cekvi.state_date is null group by video_code ) a ON a.video_code = info.video_code
            SET info.state_date = a.minDate
        WHERE
            a.minDate IS NOT NULL
          AND info.state_date IS NULL
    </update>
    <update id="etlKuaishouVideoCompanyInfo">
        update ctop_etl_kuaishou_video_info video left join user_company uc on video.clip_id = uc.user_id
            set video.company_name = uc.company_name
        where video.clip_id is not null
            and uc.company_name is not null
            and (video.company_name is null or video.company_name = '')
    </update>
    <update id="etlKuaishouVideoTypeZz">
        update ctop_material_tag_info cmti left join ctop_etl_kuaishou_video_info cbvei on cmti.code = cbvei.video_code
            set cbvei.video_type = #{videoType} where cmti.tag_code like 'Z01%' and cbvei.video_type is null
    </update>
    <update id="etlKuaishouVideoTypeZr">
        update ctop_material_tag_info cmti left join ctop_etl_kuaishou_video_info cbvei on cmti.code = cbvei.video_code
            set cbvei.video_type = #{videoType} where cmti.tag_code like 'Z02%' and cbvei.video_type is null
    </update>
    <update id="etlBytedanceVideoTypeZr">
        update ctop_material_tag_info cmti left join ctop_bytedance_video_etl_info cbvei on cmti.code = cbvei.video_code
            set cbvei.video_type = #{videoType} where cmti.tag_code like 'Z01%' and cbvei.video_type is null
    </update>
    <update id="etlBytedanceVideoTypeZz">
        update ctop_material_tag_info cmti left join ctop_bytedance_video_etl_info cbvei on cmti.code = cbvei.video_code
            set cbvei.video_type = #{videoType} where cmti.tag_code like 'Z02%' and cbvei.video_type is null
    </update>
    <select id="etlKuaishouAccountMaterialReportDailyList"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.EtlKuaishouAccountMaterialReportDaily">
    select
    m.photo_id,m.photo_url,m.cover_url,
    c.company_id ,
    c.company_name ,
    u.project_id ,
    p.project_name,
    m.account_id,
    case when m.signature is not null then m.signature else video.signature end as signature,
    sum(m.charge)as charge ,
    sum(m.photo_show)as photo_show ,
    sum(m.aclick)as aclick ,
    sum(m.bclick)as bclick ,
    sum(m.download_started)as download_started ,
    sum(m.download_completed)as download_completed,
    sum(m.activation)as activation ,
    sum(m.submit)as submit,
    m.stat_date,
    sum(m.photo_click)as photo_click,
    sum(m.event_pay_first_day)as event_pay_first_day,
    sum(m.event_pay_purchase_amount_first_day)as event_pay_purchase_amount_first_day,
    sum(m.event_pay)as event_pay,
    sum(m.event_pay_purchase_amount)as event_pay_purchase_amount,
    sum(m.event_register)as event_register,
    sum(m.event_jin_jian_app)as event_jin_jian_app,
    sum(m.event_credit_grant_app)as event_credit_grant_app,
    sum(m.event_order_paid)as event_order_paid,
    sum(m.event_order_paid_purchase_amount)as event_order_paid_purchase_amount,
    sum(m.event_jin_jian_landing_page)as event_jin_jian_landing_page,
    sum(m.event_credit_grant_landing_page )as event_credit_grant_landing_page,
    sum(m.event_next_day_stay)as event_next_day_stay,
    sum(m.form_count)as form_count,
     round(sum(m.play_3s_ratio*m.photo_show))as play_3s_count
    from ctop_kuaishou_report_daily_material m
        left join ctop_user_allocation u on u.account_id = m.account_id
        left  join ctop_kuaishou_video_get video on video.account_id = m.account_id and m.photo_id = video.photo_id
        left join ctop_project p on p.id = u.project_id
        left join user_company c on c.user_id = u.account_id
where 1=1
and m.stat_date = #{date} and m.account_id = #{accountId}
group by m.account_id,m.photo_id
    </select>
    <select id="getDateList" resultType="java.lang.String">
        SELECT DISTINCT stat_date  from  ctop_kuaishou_report_daily_material ORDER BY stat_date DESC
    </select>
    <select id="getAccountList" resultType="java.lang.Long">
       SELECT DISTINCT account_id  from  ctop_kuaishou_report_daily_material where stat_date = #{date}
    </select>


    <insert id="addBatch">
        replace into ctop_etl_kuaishou_account_material_report_daily(
        project_id,
        project_name,
        photo_id,
        account_id,
        signature,
        charge,
        photo_show,
        aclick,
        bclick,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        photo_click,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay,
        event_pay_purchase_amount,
        event_register,
        event_jin_jian_app,
        event_credit_grant_app,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_jin_jian_landing_page,
        event_credit_grant_landing_page,
        event_next_day_stay,
        form_count,
        play_3s_count)
        values
        <foreach collection="addList" item="report" separator=",">
            (
            #{report.projectId},
            #{report.projectName},
            #{report.photoId},
            #{report.accountId},
            #{report.signature},
            #{report.charge},
            #{report.photoShow},
            #{report.aclick},
            #{report.bclick},
            #{report.downloadStarted},
            #{report.downloadCompleted},
            #{report.activation},
            #{report.submit},
            #{report.statDate},
            #{report.photoClick},
            #{report.eventPayFirstDay},
            #{report.eventPayPurchaseAmountFirstDay},
            #{report.eventPay},
            #{report.eventPayPurchaseAmount},
            #{report.eventRegister},
            #{report.eventJinJianApp},
            #{report.eventCreditGrantApp},
            #{report.eventOrderPaid},
            #{report.eventOrderPaidPurchaseAmount},
            #{report.eventJinJianLandingPage},
            #{report.eventCreditGrantLandingPage},
            #{report.eventNextDayStay},
            #{report.formCount},
            #{report.play3sCount}
            )
        </foreach>

    </insert>


</mapper>
