<?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.ai.mapper.AiKuaiShouStrategyListPageMapper">

    <select id="queryStrategyBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.id,
        t1.strategy_state as strategyState,
        t1.strategy_name as strategyName,
        t1.create_time as createTime,
        t1.account_id as accountId,
        (select auth_name from ctop_user_allocation where account_id=t1.account_id limit 1) as authName,
        '' as status,
        t1.scenes,
        t1.budget,
        IFNULL(sum(t3.charge),0) as cost,
        IFNULL(sum(t3.photo_show),0) as photoShow,
        IFNULL(sum(t3.photo_click),0) as photoClick,
        CASE WHEN sum(t3.photo_click) / sum(t3.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t3.photo_click) / sum(t3.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t3.aclick),0) as aclick,
        IFNULL(sum(t3.bclick),0) as bclick,
        CASE WHEN sum(t3.bclick) / sum(t3.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t3.bclick) / sum(t3.aclick)) * 100,2),'%') END AS bClickRate,
        CASE WHEN sum(t3.charge) / sum(t3.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t3.charge) / sum(t3.photo_show)) * 1000, 2 ) END AS cpm ,
        CASE WHEN sum(t3.charge) / sum(t3.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t3.charge) / sum(t3.photo_click)), 2) END AS cpc ,
        IFNULL(sum(t3.activation),0) as active,
        CASE WHEN sum(t3.charge) / sum(t3.activation) IS NULL THEN 0 ELSE sum(t3.charge) / sum(t3.activation) end AS activeCost ,
        CASE WHEN sum(t3.activation) / sum(t3.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t3.activation) / sum(t3.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t3.event_register),0) as register,
        CASE WHEN sum(t3.charge) / sum(t3.event_register) IS NULL THEN 0 ELSE round(sum(t3.charge)/sum(t3.event_register),3) end as activeRegisterCost ,
        CASE WHEN sum(t3.event_register) / sum(t3.activation) IS NULL THEN 0 ELSE concat(round(sum(t3.event_register) / sum(t3.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t3.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t3.event_next_day_stay) / sum(t3.activation) IS NULL THEN 0 ELSE concat(round(sum(t3.event_next_day_stay) / sum(t3.activation)*100,2),'%') end as nextDayOpenRate
        FROM (
        SELECT
        DISTINCT t2.campaign_id,
        t1.id,
        t1.strategy_state,
        t1.strategy_name,
        t1.create_time,
        t1.account_id,
        '' as status,
        t1.scenes,
        t1.budget
        from ctop_kuaishou_strategy t1
        LEFT JOIN ctop_ai_kuaishou_strategy_map_creative t2 on t1.id=t2.strategy_id
        where t1.account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        AND t1.strategy_name like '%${strategyName}%'
        <if test="strategyState != null">
            AND t1.strategy_state= #{strategyState}
        </if>
        <if test="id != null">
            AND t1.id=#{id}
        </if>
        ) t1 left join ctop_kuaishou_report_daily_campaign t3 on t1.campaign_id=t3.campaign_id
        group by t1.campaign_id
        order by t1.create_time desc
    </select>

    <select id="queryCampaignByStrategyId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.campaign_id as campaignId,
        t1.campaign_name as campaignName,
        t1.status,
        t3.id as strategyId,
        t3.strategy_name as strategyName,
        IFNULL(sum(t1.charge),0) as cost,
        IFNULL(sum(t1.photo_show),0) as photoShow,
        IFNULL(sum(t1.photo_click),0) as photoClick,
        CASE WHEN sum(t1.photo_click) / sum(t1.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t1.photo_click) /
        sum(t1.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t1.aclick),0) as aclick,
        IFNULL(sum(t1.bclick),0) as bclick,
        CASE WHEN sum(t1.bclick) / sum(t1.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t1.bclick) / sum(t1.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t1.charge) / sum(t1.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t1.charge) / sum(t1.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t1.charge) / sum(t1.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t1.charge) / sum(t1.photo_click)),
        2) END AS cpc ,
        IFNULL(sum(t1.activation),0) as active,
        CASE WHEN sum(t1.charge) / sum(t1.activation) IS NULL THEN 0 ELSE sum(t1.charge) / sum(t1.activation) end AS
        activeCost ,
        CASE WHEN sum(t1.activation) / sum(t1.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t1.activation) /
        sum(t1.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t1.event_register),0) as register,
        CASE WHEN sum(t1.charge) / sum(t1.event_register) IS NULL THEN 0 ELSE
        round(sum(t1.charge)/sum(t1.event_register),3) end as activeRegisterCost ,
        CASE WHEN sum(t1.event_register) / sum(t1.activation) IS NULL THEN 0 ELSE concat(round(sum(t1.event_register) /
        sum(t1.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t1.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t1.event_next_day_stay) / sum(t1.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t1.event_next_day_stay) / sum(t1.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        (
        SELECT
        DISTINCT t2.campaign_id,
        t1.id,
        t1.strategy_state,
        t1.strategy_name,
        t1.create_time,
        t1.account_id,
        '' as status,
        t1.scenes,
        t1.budget from ctop_kuaishou_strategy t1
        LEFT JOIN ctop_ai_kuaishou_strategy_map_creative t2 on t1.id=t2.strategy_id
        where t1.id=#{strategyId}
        ) t3 left join ctop_kuaishou_report_daily_campaign t1 on t3.campaign_id=t1.campaign_id
        WHERE
        1=1
        <if test="campaignName != null">
            and t1.campaign_name like '%${campaignName}}%'
        </if>
        <if test="campaignId != null">
            and t1.campaign_id=#{campaignId}
        </if>
        <if test="status != null">
            AND t1.status=#{status}
        </if>
        GROUP BY t1.campaign_id
        order by t1.create_time desc
    </select>

    <select id="queryUnitByCampaignId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.campaign_id as campaignId,
        t1.campaign_name as campaignName,
        t1.unit_id as unitId,
        t1.unit_name as unitName,
        t1.status,
        t3.id as strategyId,
        t3.strategy_name as strategyName,
        IFNULL(sum(t1.charge),0) as cost,
        IFNULL(sum(t1.photo_show),0) as photoShow,
        IFNULL(sum(t1.photo_click),0) as photoClick,
        CASE WHEN sum(t1.photo_click) / sum(t1.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t1.photo_click) /
        sum(t1.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t1.aclick),0) as aclick,
        IFNULL(sum(t1.bclick),0) as bclick,
        CASE WHEN sum(t1.bclick) / sum(t1.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t1.bclick) / sum(t1.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t1.charge) / sum(t1.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t1.charge) / sum(t1.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t1.charge) / sum(t1.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t1.charge) / sum(t1.photo_click)),
        2) END AS cpc ,
        IFNULL(sum(t1.activation),0) as active,
        CASE WHEN sum(t1.charge) / sum(t1.activation) IS NULL THEN 0 ELSE sum(t1.charge) / sum(t1.activation) end AS
        activeCost ,
        CASE WHEN sum(t1.activation) / sum(t1.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t1.activation) /
        sum(t1.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t1.event_register),0) as register,
        CASE WHEN sum(t1.charge) / sum(t1.event_register) IS NULL THEN 0 ELSE
        round(sum(t1.charge)/sum(t1.event_register),3) end as activeRegisterCost ,
        CASE WHEN sum(t1.event_register) / sum(t1.activation) IS NULL THEN 0 ELSE concat(round(sum(t1.event_register) /
        sum(t1.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t1.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t1.event_next_day_stay) / sum(t1.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t1.event_next_day_stay) / sum(t1.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        (
        SELECT
        DISTINCT
        t2.unit_id,
        t2.campaign_id,
        t1.id,
        t1.strategy_state ,
        t1.strategy_name ,
        t1.create_time ,
        t1.account_id,
        '' as status,
        t1.scenes,
        t1.budget from ctop_kuaishou_strategy t1
        LEFT JOIN ctop_ai_kuaishou_strategy_map_creative t2 on t1.id=t2.strategy_id
        where t2.campaign_id=#{campaignId}
        ) t3 left join ctop_kuaishou_report_daily_group t1 on t3.unit_id=t1.unit_id
        where
        1=1
        <if test="unitName != null">
            and t1.unit_name like '%${unitName}}%'
        </if>
        <if test="unitId != null">
            and t1.unit_id=#{unitId}
        </if>
        <if test="status != null">
            AND t1.status=#{status}
        </if>
        GROUP BY t1.unit_id
        order by t1.create_time desc
    </select>
</mapper>
