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

    <select id="getDailyReport" parameterType="Map" resultType="cn.com.ctop.common.module.vo.DailyAccountReportVO">
        SELECT
        t1.*,
        truncate((t1.conversions / t1.bclick),4) conversionRate,

        truncate(t1.conversionPrice / #{discount},2) discountConversionPrice,

        (SELECT sum(contract_rebate_real_charged)
        from ctop_kuaishou_daily_flows
        <where>
            <if test="date != null">
                and date = #{date}
            </if>

            <if test="accountIds != null">
                and account_id in
                <foreach collection="accountIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>

            </if>
        </where>
        ) contractRebateRealCharged
        FROM (
        SELECT
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(aclick) aclick,
        sum(photo_click) photoClick,
        (
        CASE
        WHEN sum(photo_show) != 0 THEN
        (
        truncate( sum(photo_click) / sum(photo_show),4)
        )
        ELSE
        0
        END
        ) photoClickRatio,
        (
        CASE
        WHEN sum(charge) != 0 THEN
        (
        truncate(sum(photo_click) / sum(charge),4)
        )
        ELSE
        0
        END
        ) photoClickCost,

        sum(bclick) bclick,

        (
        CASE
        WHEN sum(aclick) != 0 THEN
        truncate(sum(bclick) / sum(aclick),4)
        ELSE
        0
        END
        ) actionRatio,
        (
        CASE
        WHEN sum(photo_show) != 0 THEN
        truncate( (sum(charge) / sum(photo_show)
        ) * 1000,2)
        ELSE
        0
        END
        ) impression1kCost,
        sum(form_count + activation) conversions,
        (
        CASE
        WHEN sum(form_count + activation) != 0 THEN
        (
        truncate(sum(charge) / sum(form_count + activation),2)
        )
        ELSE
        0
        END
        ) conversionPrice
        FROM
        ctop_kuaishou_report_daily_account
        <where>
            <if test="date != null">
                and stat_date = #{date}
            </if>

            <if test="accountIds != null">
                and account_id in
                <foreach collection="accountIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>

            </if>

        </where>
        ) t1

    </select>


    <select id="getDailyGroupReport" parameterType="Map" resultType="cn.com.ctop.common.module.vo.DailyGroupReportVO">
        SELECT
        t1.*,
        truncate(t1.conversions / t1.photoClick,4) clickConversionRate,
        truncate(t1.conversionPrice / #{discount},2) discountConversionPrice
        FROM
        (
        SELECT
        account_id accountId,
        (
        SELECT
        auth_name
        FROM
        ctop_user_allocation
        WHERE
        account_id = accountId
        ) authName,
        stat_date statDate,
        campaign_name campaignName,
        unit_name unitName,
        sum(charge) cost,
        truncate((sum(charge) / #{discount}),2) disCountCost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(bclick) bclick,
        (
        CASE
        WHEN sum(aclick) != 0 THEN
        truncate(sum(bclick) / sum(aclick),4)
        ELSE
        0
        END
        ) actionRatio,


        (
        CASE
        WHEN sum(aclick) != 0 THEN
        truncate(sum(photo_click) / sum(aclick),4)
        ELSE
        0
        END
        ) photoClickRatio,


        (
        CASE
        WHEN sum(photo_show) != 0
        THEN
        truncate((sum(charge) / sum(photo_show)
        ) * 1000,2)
        ELSE
        0
        END
        ) impression1kCost,
        (
        CASE
        WHEN sum(charge) != 0 THEN
        (
        truncate(sum(photo_click) / sum(charge),2)
        )
        ELSE
        0
        END
        ) photoClickCost,

        sum(form_count + activation) conversions,
        (
        CASE
        WHEN sum(form_count + activation) != 0 THEN
        (
        truncate(sum(charge) / sum(form_count + activation),2)
        )
        ELSE
        0
        END
        ) conversionPrice

        FROM
        ctop_kuaishou_report_daily_group
        <where>
            <if test="date != null">
                and stat_date = #{date}
            </if>

            <if test="accountIds != null">
                and account_id in
                <foreach collection="accountIds" item="item" separator=","
                         open="(" close=")">
                    #{item}
                </foreach>

            </if>
        </where>
        GROUP BY unit_id
        order by account_id

        ) t1
    </select>


</mapper>
