<?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.SalesBudgetMapper">
    <insert id="adds" >
        insert into ctop_sale_project_budget_set (
        project_id,
        media_id,
        budget_key,
        budget_vale,
        max_value,
        user_id
        ) values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.projectId},
            #{item.mediaId},
            #{item.budgetKey},
            #{item.budgetVale},
            #{item.maxValue},
            #{item.userId}
            )
        </foreach>
    </insert>
    <delete id="deleteInfo" parameterType="java.lang.Long">
        delete
        from ctop_sale_project_budget_set
        where project_id = #{projectId}
    </delete>

    <select id="bytedanceSalesReport" resultType="com.alibaba.fastjson.JSONObject"
            parameterType="java.lang.Long">
        select t1.*,
        substring(t2.realname,1,3) as 'saleName',
        (select substring(realname,1,3) from `jeecg-boot`.sys_user where id = t3.responsible_id) as 'responsibleName'
        from (SELECT sale_id as 'saleId',
        project_id as 'projectId',
        project_name as 'projectName',
        sum(cost) as 'budget',
        sum(active) as 'active',
        ROUND(sum(cost) / sum(active), 3) as 'activeCost',
        sum(attribution_next_day_open_cnt) as 'attributionNextDayOpenCnt',
        ROUND(sum(attribution_next_day_open_cnt) / sum(active) * 100, 3) as 'attributionNextDayOpenRate',
        ROUND(sum(cost) / sum(attribution_next_day_open_cnt), 3) as 'attributionNextDayOpenCost',
        sum(pay_count) as 'payCount',
        ROUND(sum(cost) / sum(pay_count), 3) as 'payCost',
        sum(form) as 'form',
        ROUND(sum(cost) / sum(form), 3) as 'formCost',
        sum(register) as 'register',
        ROUND(sum(cost) / sum(register), 3) as 'registerCost',
        sum(game_addiction) as 'gameAddiction',
        ROUND(sum(cost) / sum(game_addiction), 3) as 'gameAddictionCost',
        sum(loan_completion) as 'loanCompletion',
        ROUND(sum(cost) / sum(loan_completion), 3) as 'loanCompletionCost',
        sum(loan_credit) as 'preLoanCredit',
        ROUND(sum(cost) / sum(loan_credit), 3) as 'preLoanCreditCost',
        sum(in_app_uv) as 'inAppUv',
        ROUND(sum(cost) / sum(in_app_uv), 3) as 'inAppUvCost',
        sum(in_app_pay) as 'inAppPay',
        ROUND(sum(cost) / sum(in_app_pay), 3) as 'inAppPayCost',
        ROUND(sum(cost) / sum(stat_pay_amount), 3) as 'statPayAmount'
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime = #{statDate}
        <if test="saleId != null and saleId != ''">
            and sale_id = #{saleId}
        </if>
        GROUP BY project_id) t1
        left join `jeecg-boot`.sys_user t2 on t1.saleId = t2.id
        left join `jeecg-boot`.ctop_project t3 on t1.projectId = t3.id
        order by t1.budget desc

    </select>
    <select id="kuaiShouSalesReport" resultType="com.alibaba.fastjson.JSONObject"
            parameterType="java.lang.Long">
        select t1.*,
        replace(t2.realname,'（汇创）','') as 'saleName',
        (select replace(realname,'（汇创）','') from `jeecg-boot`.sys_user where id = t3.responsible_id) as
        'responsibleName'
        from (SELECT sale_id as 'saleId',
        project_id as 'projectId',
        project_name as 'projectName',
        SUM(charge) AS 'budget',
        sum(activation) as 'activation',
        ROUND(sum(charge) / sum(activation), 3) AS 'activationCost',
        sum(event_next_day_stay) as 'eventNextDayStay',
        ROUND(sum(event_next_day_stay) / sum(activation) * 100, 3) as 'eventNextDayStayRate',
        ROUND(sum(charge) / sum(event_next_day_stay), 3) AS 'eventNextDayStayCost',
        sum(form_count) as 'formCount',
        ROUND(sum(charge) / sum(form_count), 3) AS 'formCountCost',
        sum(event_pay) as 'eventPay',
        ROUND(sum(charge) / sum(event_pay), 3) AS 'eventCost',
        sum(key_action) as 'keyAction',
        ROUND(sum(charge) / sum(key_action), 3) AS 'keyActionCost',
        sum(event_app_invoked) as 'eventAppInvoked',
        ROUND(sum(charge) / sum(event_app_invoked), 3) AS 'eventAppInvokedCost',
        ROUND(sum(charge) / sum(event_pay_purchase_amount_first_day), 3) AS 'eventPayPurchaseAmountFirstDayCost',
        ROUND(sum(charge) / sum(event_three_day_stay_by_conversion), 3) AS 'eventThreeDayStayByConversionCost',
        ROUND(sum(charge) / sum(event_week_stay_by_conversion), 3) AS 'eventWeekStayByConversionCost'
        FROM application.kuaishou_account_report_daily_dw
        WHERE stat_date = #{statDate}
        <if test="saleId != null and saleId != ''">
            and sale_id = #{saleId}
        </if>
        GROUP BY project_id) t1
        left join `jeecg-boot`.sys_user t2 on t1.saleId = t2.id
        left join `jeecg-boot`.ctop_project t3 on t1.projectId = t3.id
        order by t1.budget desc

    </select>
    <select id="ocpxList" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.String">
        select budget_key as 'ocpxActionType',budget_vale as 'ocpxActionName'
        from ctop_sale_budget_config
        where media_id = #{mediaId}
    </select>
    <select id="getBudget" resultType="java.math.BigDecimal" parameterType="java.lang.Long">
        select max_value as 'maxValue'  from ctop_sale_project_budget_set where project_id = #{projectId} and budget_key = 'budget'
    </select>
    <select id="getBudgetList" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">
        select  budget_key as  'ocpxCode',
                budget_vale as 'ocpxName',
                max_value as   'maxValue'
        from ctop_sale_project_budget_set
        where project_id = #{projectId}
          and budget_key != 'budget'
    </select>
    <select id="getConfigs" resultType="com.alibaba.fastjson.JSONObject" parameterType="java.lang.Long">
        select budget_key as  'ocpxCode', budget_vale as 'ocpxName', max_value as   'maxValue'
        from ctop_sale_project_budget_set
        where project_id = #{projectId}
    </select>
</mapper>
