<?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.DuanJUReportMapper">
    <insert id="replaceDatas" >
        replace into kuaishou_duanju_stop_plan(
        account_id,
        campaign_id,
        campaign_name,
        update_message
        )
        values
        <foreach collection="adds" item="add" separator=",">
            (
            #{add.accountId},
            #{add.campaignId},
            #{add.campaignName},
            #{add.updateMessage}
            )
        </foreach>

    </insert>


    <select id="getTotalCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT SUM(charge) AS 'charge', COUNT(DISTINCT advertiser_id) AS 'accountNum', round(
                ifnull(SUM(event_pay_purchase_amount) / SUM(charge) * 100, 0), 2) AS roi,
               COUNT(DISTINCT substring_index(substring_index(campaign_name, '-', 2), '-', -1)) AS playCount
        FROM media_api.compass_bind_kuaishou_campaign_report_daily
        WHERE stat_date &gt;= #{start}
          AND stat_date &lt;= #{end}
    </select>
    <select id="getAccountList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (SELECT t1.advertiser_id                                                              AS accountId,
                     t2.advertiser_name AS 'accountName', SUM(t1.charge) AS charge,
                     round(ifnull(SUM(t1.event_pay_purchase_amount) / SUM(t1.charge) * 100, 0), 2) AS roi,
                     SUM(t1.event_pay_purchase_amount)                                             AS event_pay_purchase_amount,
                     SUM(t1.aclick)                                                                AS aclick,
                     SUM(t1.bclick)                                                                AS bclick,
                     concat(round(ifnull(SUM(t1.bclick) / SUM(t1.aclick) * 100, 0), 2), '%')       AS ctr,
                     round(SUM(t1.charge) / SUM(t1.bclick), 2)                                     AS cpc,
                     round(SUM(t1.charge) / SUM(t1.aclick) * 1000, 2)                              AS cpm,
                     SUM(t1.activation)                                                            AS activation,
                     round(SUM(t1.charge) / SUM(t1.activation), 2)                                 AS active_cost,
                     SUM(t1.event_pay)                                                             AS event_pay,
                     round(SUM(t1.charge) / SUM(t1.event_pay), 2)                                  AS pay_cost,
                     SUM(round(t1.event_new_user_pay))                                             AS event_new_user_pay,
                     round(SUM(t1.charge) / SUM(t1.event_new_user_pay), 2)                         AS new_pay_cost
              FROM media_api.compass_bind_kuaishou_campaign_report_daily t1
                       LEFT JOIN media_api.compass_bind_advertisers t2 ON t1.advertiser_id = t2.advertiser_id
              WHERE t1.stat_date &gt;= #{start}
                AND t1.stat_date &lt;= #{end}
              GROUP BY t1.advertiser_id) t
        ORDER BY t.charge DESC
    </select>
    <select id="getDuanJuList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (SELECT substring_index(campaign_name, '-', 1)                                               AS agent,
                     substring_index(substring_index(campaign_name, '-', 2), '-', -1)                     AS play_name,
                     SUM(charge)                                                                          AS charge,
                     SUM(event_pay_purchase_amount)                                                       AS event_pay_purchase_amount,
                     concat(round(ifnull(SUM(event_pay_purchase_amount) / SUM(charge) * 100, 0), 2), '%') AS roi,
                     SUM(aclick)                                                                          AS aclick,
                     SUM(bclick)                                                                          AS bclick,
                     concat(round(ifnull(SUM(bclick) / SUM(aclick) * 100, 0), 2), '%')                    AS ctr,
                     round(SUM(charge) / SUM(bclick), 2)                                                  AS cpc,
                     round(SUM(charge) / SUM(aclick) * 1000, 2)                                           AS cpm,
                     SUM(activation)                                                                      AS activation,
                     round(SUM(charge) / SUM(activation), 2)                                              AS active_cost,
                     SUM(event_pay)                                                                       AS event_pay,
                     round(SUM(charge) / SUM(event_pay), 2)                                               AS pay_cost,
                     SUM(round(event_new_user_pay))                                                       AS event_new_user_pay,
                     round(SUM(charge) / SUM(event_new_user_pay), 2)                                      AS new_pay_cost
              FROM media_api.compass_bind_kuaishou_campaign_report_daily
              WHERE stat_date &gt;= #{start}
                AND stat_date &lt;= #{end}
              GROUP BY agent, play_name) t
        ORDER BY t.charge DESC
    </select>
    <select id="getViodeoList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t.photo_id,
        t.photo_name,
        t.cover_url,
        t.photo_url,
        SUM(t.charge) AS charge,
        SUM(t.event_pay_purchase_amount) AS event_pay_purchase_amount,
        round(ifnull(SUM(t.event_pay_purchase_amount) / SUM(t.charge) * 100, 0), 2) AS roi,
        SUM(t.aclick) AS aclick,
        SUM(t.bclick) AS bclick,
        concat(round(ifnull(SUM(t.bclick) / SUM(t.aclick) * 100, 0), 2), '%') AS ctr,
        round(SUM(t.charge) / SUM(t.bclick), 2) AS cpc,
        round(SUM(t.charge) / SUM(t.aclick) * 1000, 2) AS cpm,
        SUM(t.activation) AS activation,
        round(SUM(t.charge) / SUM(t.activation), 2) AS active_cost,
        SUM(t.event_pay) AS event_pay,
        round(SUM(t.charge) / SUM(t.event_pay), 2) AS pay_cost,
        SUM(round(t.event_new_user_pay)) AS event_new_user_pay,
        round(SUM(t.charge) / SUM(t.event_new_user_pay), 2) AS new_pay_cost,
        concat(round(SUM(t.play_3s_count) / SUM(t.aclick) * 100, 2), '%') AS play_3s_rate,
        concat(round(SUM(t.play_5s_count) / SUM(t.aclick) * 100, 2), '%') AS play_5s_rate,
        concat(round(SUM(t.play_end_count) / SUM(t.aclick) * 100, 2), '%') AS play_end_rate
        FROM (SELECT CAST(t1.photo_id AS char) AS 'photo_id',
        t2.photo_name as 'photo_name',
        t1.cover_url AS 'cover_url',
        t1.photo_url AS 'photo_url',
        t1.charge AS 'charge',
        t1.event_pay_purchase_amount AS 'event_pay_purchase_amount',
        t1.aclick AS 'aclick',
        t1.bclick AS 'bclick',
        t1.activation AS 'activation',
        t1.event_pay AS 'event_pay',
        t1.event_new_user_pay AS 'event_new_user_pay',
        round(t1.play_3s_ratio * t1.aclick, 0) as 'play_3s_count',
        round(t1.play_5s_ratio * t1.aclick, 0) as 'play_5s_count',
        round(t1.play_end_ratio * t1.aclick, 0) as 'play_end_count'
        FROM media_api.compass_bind_kuaishou_material_video_report_daily t1
        left join media_api.compass_bind_kuaishou_video_list t2 on t1.photo_id = t2.photo_id
        WHERE t1.stat_date &gt;= #{start}
        AND t1.stat_date &lt;= #{end}
        and t1.charge > 0
        ) t
        where 1 = 1
        <if test="videoName!=null and videoName!=''">and t.photo_name like concat('%',#{videoName},'%')</if>
        group by t.photo_id
        ORDER BY sum(t.charge) DESC
    </select>
    <select id="getAgentToken" resultType="java.lang.String" parameterType="java.lang.Long">
        SELECT access_token
        FROM ctop_oauth_agent_token
        WHERE app_id = #{appId}
    </select>
    <select id="stopList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT account_id AS 'accountId', campaign_id AS 'campaignId', campaign_name AS 'campaignName', update_message AS 'updateMessage', CAST(create_time AS char) AS 'createTime'
        FROM kuaishou_duanju_stop_plan
        WHERE account_id = #{accountId}
        ORDER BY create_time DESC
    </select>
</mapper>
