<?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.EtlKuaishouAudienceAccountDailyMapper">
    <insert id="replaceBatch">
        replace into ctop_etl_kuaishou_audience_account_daily(
        stat_date,
        account_id,
        audience_type,
        charge,
        activation,
        event_pay,
        event_pay_purchase_amount,
        event_next_day_stay,
        aclick,
        bclick,
        photo_click,
        photo_show,
        gender,
        age_segment
        )
        values
        <foreach collection="list" item="appInfo" separator=",">
            (
            #{appInfo.statDate},
            #{appInfo.accountId},
            #{appInfo.audienceType},
            #{appInfo.charge},
            #{appInfo.activation},
            #{appInfo.eventPay},
            #{appInfo.eventPayPurchaseAmount},
            #{appInfo.eventNextDayStay},
            #{appInfo.aclick},
            #{appInfo.bclick},
            #{appInfo.photoClick},
            #{appInfo.photoShow},
            #{appInfo.gender},
            #{appInfo.ageSegment}
            )
        </foreach>
    </insert>

    <select id="queryAudienceTypeInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id as 'accountId',
        <if test="parType == 'alone'">
            SUM(${value}) as 'value',
        </if>
        <if test="parType == 'more'">
            <if test="value == 'activationCost'">
                IFNULL(ROUND( SUM(charge)/ SUM(activation),2),0) as 'value',
            </if>
            <if test="value == 'eventPayRoi'">
                IFNULL(ROUND( SUM(event_pay_purchase_amount)/ SUM(charge)*100,2),0) as 'value',
            </if>
            <if test="value == 'eventPayCost'">
                IFNULL(ROUND( SUM(charge)/ SUM(event_pay),2),0) as 'value',
            </if>
            <if test="value == 'eventNextDayStayCost'">
                IFNULL(ROUND(SUM(charge)/SUM(event_next_day_stay),2),0) as 'value',
            </if>
        </if>
        ${type} as 'type'
        FROM ctop_etl_kuaishou_audience_account_daily
        where stat_date &gt;= DATE_FORMAT(date_sub(now(), interval 8 day), '%Y-%m-%d')
        AND stat_date &lt;= DATE_FORMAT(date_sub(now(), interval 2 day), '%Y-%m-%d')
        AND audience_type = #{audienceType}
        AND account_id = #{accountId}
        group by ${type}
        ORDER BY ${type}
    </select>

    <select id="queryEveryDayInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id as 'accountId',
        stat_date as 'statDate',
        <if test="parType == 'alone'">
            ${value} as 'value',
        </if>
        <if test="parType == 'more'">
            <if test="value == 'activationCost'">
                IFNULL(ROUND( charge/ activation,2),0) as 'value',
            </if>
            <if test="value == 'eventPayRoi'">
                IFNULL(ROUND(event_pay_purchase_amount/charge*100,2),0) as 'value',
            </if>
            <if test="value == 'eventPayCost'">
                IFNULL(ROUND(charge / event_pay,2),0) as 'value',
            </if>
            <if test="value == 'eventNextDayStayCost'">
                IFNULL(ROUND(charge/ event_next_day_stay,2),0) as 'value',
            </if>
        </if>
        ${type} as 'type'
        FROM ctop_etl_kuaishou_audience_account_daily
        where stat_date = #{day}
        AND audience_type = #{audienceType}
        AND account_id = #{accountId}
        group by stat_date,${type}
        order by stat_date,${type}
    </select>

    <select id="getSceneEveryDayInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id as 'accountId',
        stat_date as 'statDate',
        <if test="parType == 'alone'">
            ${value} as 'value',
        </if>
        <if test="parType == 'more'">
            <if test="value == 'activationCost'">
                IFNULL(ROUND( charge/ activation,2),0) as 'value',
            </if>
            <if test="value == 'eventPayRoi'">
                IFNULL(ROUND(event_pay_purchase_amount/charge*100,2),0) as 'value',
            </if>
            <if test="value == 'eventPayCost'">
                IFNULL(ROUND(charge / event_pay,2),0) as 'value',
            </if>
            <if test="value == 'eventNextDayStayCost'">
                IFNULL(ROUND(charge/ event_next_day_stay,2),0) as 'value',
            </if>
        </if>
        IFNULL(ad_scene,'全部') as 'type'
        FROM ctop_kuaishou_report_daily_account_ad_scene
        where stat_date = #{day}
        AND account_id = #{accountId}
        group by stat_date,ad_scene
        order by stat_date,ad_scene
    </select>

    <select id="getSceneTypeList" resultType="java.lang.String">
        SELECT DISTINCT IFNULL(ad_scene, '全部')
        FROM ctop_kuaishou_report_daily_account_ad_scene
        WHERE LENGTH(trim(ad_scene))>0
        ORDER BY ad_scene
    </select>

    <select id="getTypeList" resultType="java.lang.String">
        SELECT ${type} as 'type'
        FROM ctop_etl_kuaishou_audience_account_daily
        where audience_type = #{audienceType}
        group by ${type}
        order by ${type}
    </select>

    <select id="getSceneInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        account_id as 'accountId',
        <if test="parType == 'alone'">
            SUM(${value}) as 'value',
        </if>
        <if test="parType == 'more'">
            <if test="value == 'activationCost'">
                IFNULL(ROUND( SUM(charge)/ SUM(activation),2),0) as 'value',
            </if>
            <if test="value == 'eventPayRoi'">
                IFNULL(ROUND( SUM(event_pay_purchase_amount)/ SUM(charge)*100,2),0) as 'value',
            </if>
            <if test="value == 'eventPayCost'">
                IFNULL(ROUND( SUM(charge)/ SUM(event_pay),2),0) as 'value',
            </if>
            <if test="value == 'eventNextDayStayCost'">
                IFNULL(ROUND(SUM(charge)/SUM(event_next_day_stay),2),0) as 'value',
            </if>
        </if>
        IFNULL(ad_scene,'全部')as 'type'
        FROM ctop_kuaishou_report_daily_account_ad_scene
        where stat_date &gt;= DATE_FORMAT(date_sub(now(), interval 8 day), '%Y-%m-%d')
        AND stat_date &lt;= DATE_FORMAT(date_sub(now(), interval 2 day), '%Y-%m-%d')
        AND account_id = #{accountId}
        group by ad_scene
        order by ad_scene
    </select>
</mapper>
