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


    <select id="selectSummaryByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        (sum(photo_click) / sum(photo_show)) photoClickRatio,
        (sum(form_count) / sum(bclick)) cvr,
        ((sum(charge) / (sum(photo_show)) * 1000)) cpm,
        sum(bclick) bClick,
        sum(form_count) formCount,
        (sum(charge) / sum(form_count)) formPrice,
        ((sum(charge) / sum(form_count)) / #{discount}) afterFormPrice,
        sum(activation) activationCount,
        (sum(charge) / sum(activation)) activationPrice,
        ((sum(charge) / sum(activation)) / #{discount}) afterActivationPrice,
        sum(event_register) eventRegister,

        sum(form_count + activation) conversionNum,

        (case
        when sum(activation +form_count) != 0
        then (sum(charge) / sum(activation +form_count))
        else 0
        end
        ) conversionPrice,

        (case
        when sum(activation +form_count) != 0
        then (sum(charge) / sum(activation +form_count)) / #{discount}
        else 0
        end
        ) afterConversionPrice,

        (case
        when sum(event_register) != 0
        then (sum(charge) / sum(event_register))
        else 0
        end
        ) eventRegisterCost
        from
        ctop_kuaishou_report_hourly_account
        where stat_date = #{date}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        and stat_hour &lt;= #{statHour}
    </select>


    <select id="selectAccountReport" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        (sum(photo_click) / sum(photo_show)) photoClickRatio,
        (sum(form_count) / sum(bclick)) cvr,
        ((sum(charge) / (sum(photo_show)) * 1000)) cpm,
        sum(bclick) bClick,
        sum(form_count) formCount,
        (sum(charge) / sum(form_count)) formPrice,
        ((sum(charge) / sum(form_count)) / #{discount}) afterFormPrice,
        sum(activation) activationCount,
        (sum(charge) / sum(activation)) activationPrice,
        ((sum(charge) / sum(activation)) / #{discount}) afterActivationPrice,
        account_id accountId,
        (select auth_name from ctop_user_allocation where account_id = t1.account_id limit 1) accountName,
        sum(event_next_day_stay) eventNextDayStay,
        (case
        when sum(activation) != 0
        then (sum(event_next_day_stay)/sum(activation))
        else 0
        end
        ) eventNextDayStayRatio
        from
        ctop_kuaishou_report_daily_account t1
        where account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by account_id;
    </select>


    <select id="selectAccountReportByDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        (sum(photo_click) / sum(photo_show)) photoClickRatio,
        (sum(form_count) / sum(bclick)) cvr,
        ((sum(charge) / (sum(photo_show)) * 1000)) cpm,
        sum(bclick) bClick,
        sum(form_count) formCount,
        (sum(charge) / sum(form_count)) formPrice,
        ((sum(charge) / sum(form_count)) / #{discount}) afterFormPrice,
        sum(activation) activationCount,
        (sum(charge) / sum(activation)) activationPrice,
        ((sum(charge) / sum(activation)) / #{discount}) afterActivationPrice,
        sum(event_register) eventRegister,
        sum(form_count + activation) conversionNum,
        (sum(charge) / sum(activation +form_count )) conversionPrice,
        (case
        when sum(event_register) != 0
        then (sum(charge) / sum(event_register))
        else 0
        end
        ) eventRegisterCost,
        account_id accountId,
        (select auth_name from ctop_user_allocation where account_id = = t1.account_id limit 1) accountName,
        sum(event_next_day_stay) eventNextDayStay,
        (case
        when sum(activation) != 0
        then (sum(event_next_day_stay)/sum(activation))
        else 0
        end
        ) eventNextDayStayRatio
        from
        ctop_kuaishou_report_daily_account t1
        where stat_date &lt;= #{startDate}
        and stat_date &gt;= #{endDate}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by account_id;
    </select>


    <select id="selectReportDetailGroupAccountId" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        (sum(photo_click) / sum(photo_show)) photoClickRatio,
        (sum(form_count) / sum(bclick)) cvr,
        ((sum(charge) / (sum(photo_show)) * 1000)) cpm,
        sum(bclick) bClick,
        sum(form_count) formCount,
        (sum(charge) / sum(form_count)) formPrice,
        sum(activation) activationCount,
        (sum(charge) / sum(activation)) activationPrice,
        sum(event_register) eventRegister,
        sum(form_count + activation) conversionNum,

        (
        case when sum(activation +form_count ) != 0
        then (sum(charge) / sum(activation +form_count))
        else 0
        end
        )conversionPrice,


        (case
        when sum(event_register) != 0
        then (sum(charge) / sum(event_register))
        else 0
        end
        ) eventRegisterCost,

        account_id accountId,
        (select auth_name from ctop_user_allocation where account_id = = t1.account_id limit 1) accountName
        from
        ctop_kuaishou_report_hourly_account t1
        where stat_date = #{date}
        and stat_hour &lt;= #{statHour}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by account_id;
    </select>


    <select id="selectReportDetailGroupAccountIdByDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        (sum(photo_click) / sum(photo_show)) photoClickRatio,
        (sum(form_count) / sum(bclick)) cvr,
        ((sum(charge) / (sum(photo_show)) * 1000)) cpm,
        sum(bclick) bClick,
        sum(form_count) formCount,
        (sum(charge) / sum(form_count)) formPrice,
        sum(activation) activationCount,
        (sum(charge) / sum(activation)) activationPrice,
        sum(event_register) eventRegister,
        sum(form_count + activation) conversionNum,
        (
        case when sum(activation +form_count ) != 0
        then (sum(charge) / sum(activation +form_count))
        else 0
        end
        )conversionPrice,

        (case
        when sum(event_register) != 0
        then (sum(charge) / sum(event_register))
        else 0
        end
        ) eventRegisterCost,
        account_id accountId,
        (select auth_name from ctop_user_allocation where account_id = = t1.account_id limit 1) accountName,
        sum(event_next_day_stay) eventNextDayStay,
        (case
        when sum(activation) != 0
        then ( sum(event_next_day_stay) / sum(activation) )
        else 0
        end
        ) eventNextDayStayRatio
        from
        ctop_kuaishou_report_daily_account t1
        where stat_date = #{statDate}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by account_id;
    </select>


    <select id="selectMaxHourByDate" resultType="java.lang.Integer">
        select
        max(stat_hour)
       from
        ctop_kuaishou_report_hourly_account
        where stat_date = #{date}

      </select>


    <select id="selectDayReport" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        max(stat_hour) maxHour
        from
        ctop_kuaishou_report_hourly_account
        where stat_date = #{date}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="selectDayReportByAccount" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick
        from
        ctop_kuaishou_report_daily_account
        where stat_date = #{date}
        and account_id = #{accountId}
    </select>

    <select id="selectByDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick
        from
        ctop_kuaishou_report_daily_account
        where stat_date &lt;= #{startDate}
        and stat_date &gt;= #{endDate}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="selectAllAccount" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick
        from
        ctop_kuaishou_report_daily_account
        where account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>


    <select id="selectReportDetail" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        stat_hour statHour,
        stat_date statDate
        from
        ctop_kuaishou_report_hourly_account
        where stat_date = #{date}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by stat_hour
        order by stat_hour asc
    </select>


    <select id="selectDayHourlyByHourAndDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick
        from
        ctop_kuaishou_report_hourly_account
        where stat_date = #{date}
        and stat_hour = #{statHour}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="selectSumDayHourlyByHourAndDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick
        from
        ctop_kuaishou_report_hourly_account
        where stat_date = #{date}
        and stat_hour &lt;= #{statHour}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>


    <select id="selectDayDetailByDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        stat_date statDate
        from
        ctop_kuaishou_report_daily_account
        where stat_date &lt;= #{startDate}
        and stat_date &gt;= #{endDate}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by stat_date
        order by stat_date asc
    </select>


    <select id="selectAllByAccounts" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        DATE_FORMAT(stat_date,'%Y-%m') statDate
        from
        ctop_kuaishou_report_daily_account
        where account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by DATE_FORMAT(stat_date,'%Y-%m')
        order by stat_date asc
    </select>


    <select id="selectDayDetailByMonth" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,
        DATE_FORMAT(stat_date,'%Y-%m') statDate
        from
        ctop_kuaishou_report_daily_account
        where stat_date &lt;= #{startDate}
        and stat_date &gt;= #{endDate}
        and account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by DATE_FORMAT(stat_date,'%Y-%m')
        order by stat_date asc
    </select>


    <select id="selectSummaryByMap" parameterType="Map" resultType="com.alibaba.fastjson.JSONObject">
        select
        sum(charge) cost,
        (case
        when sum(charge) != 0
        then (sum(charge) / #{discount})
        else 0
        end
        ) discountCost,

        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,

        (case
        when sum(photo_show) != 0
        then (sum(photo_click) / sum(photo_show))
        else 0
        end
        ) photoClickRatio,

        (case
        when sum(bclick) != 0
        then (sum(form_count) / sum(bclick))
        else 0
        end
        ) cvr,

        (case
        when sum(photo_show) != 0
        then ((sum(charge) / (sum(photo_show)) * 1000))
        else 0
        end
        ) cpm,

        sum(bclick) bClick,
        (
        CASE
        WHEN sum(form_count + activation) != 0 THEN
        sum(charge) / sum(form_count + activation)
        ELSE
        0
        END
        ) conversionPrice,
        sum(form_count + activation) conversions,
        (
        CASE
        WHEN sum(form_count + activation) != 0 THEN
        (sum(charge) / sum(form_count + activation)) / #{discount}
        ELSE
        0
        END
        ) discountConversionPrice,
        sum(event_register) eventRegister,
        (case
        when sum(event_register) != 0
        then (sum(charge) / sum(event_register))
        else 0
        end
        ) eventRegisterCost,
        sum(event_next_day_stay) eventNextDayStay,
        (case
        when sum(activation) != 0
        then (sum(event_next_day_stay)/sum(activation))
        else 0
        end
        ) eventNextDayStayRatio

        from
        ctop_kuaishou_report_daily_account

        <where>
            <if test="statDate != null">
                and stat_date = #{statDate}
            </if>
            <if test="startDate != null">
                and stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                and stat_date &lt;= #{endDate}
            </if>

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

            </if>
        </where>
    </select>


    <select id="selectHourSummaryByMap" parameterType="Map" resultType="com.alibaba.fastjson.JSONObject">

        select
        sum(charge) cost,
        (case
        when sum(charge) != 0
        then (sum(charge) / #{discount})
        else 0
        end
        ) discountCost,

        sum(photo_show) photoShow,
        sum(photo_click) photoClick,
        sum(aclick) aclick,

        (case
        when sum(photo_show) != 0
        then (sum(photo_click) / sum(photo_show))
        else 0
        end
        ) photoClickRatio,

        (case
        when sum(bclick) != 0
        then (sum(form_count) / sum(bclick))
        else 0
        end
        ) cvr,

        (case
        when sum(photo_show) != 0
        then ((sum(charge) / (sum(photo_show)) * 1000))
        else 0
        end
        ) cpm,
        sum(form_count + activation) conversions,
        sum(bclick) bClick,

        (
        CASE
        WHEN sum(activation + form_count) != 0 THEN
        sum(charge) / sum(activation + form_count)
        ELSE
        0
        END
        ) conversionPrice,
        (
        CASE
        WHEN sum(activation + form_count) != 0 THEN
        (sum(charge) / sum(activation + form_count)) / #{discount}
        ELSE
        0
        END
        ) discountConversionPrice,
        sum(event_register) eventRegister,
        (case
        when sum(event_register) != 0
        then (sum(charge) / sum(event_register))
        else 0
        end
        ) eventRegisterCost

        from
        ctop_kuaishou_report_hourly_account

        <where>
            <if test="statDate != null">
                and stat_date = #{statDate}
            </if>
            <if test="statHour != null">
                and stat_hour &lt;= #{statHour}
            </if>

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

            </if>
        </where>


    </select>


</mapper>
