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

    <resultMap id="BaseResultMap" type="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyAccount">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="account_id" property="accountId" jdbcType="BIGINT"/>
        <result column="charge" property="charge" jdbcType="DECIMAL"/>
        <result column="photo_show" property="photoShow" jdbcType="BIGINT"/>
        <result column="aclick" property="aclick" jdbcType="BIGINT"/>
        <result column="bclick" property="bclick" jdbcType="BIGINT"/>
        <result column="photo_share" property="photoShare" jdbcType="BIGINT"/>
        <result column="photo_comment" property="photoComment" jdbcType="BIGINT"/>
        <result column="photo_like" property="photoLike" jdbcType="BIGINT"/>
        <result column="follow" property="follow" jdbcType="BIGINT"/>
        <result column="cancel_follow" property="cancelFollow" jdbcType="BIGINT"/>
        <result column="report" property="report" jdbcType="BIGINT"/>
        <result column="block" property="block" jdbcType="BIGINT"/>
        <result column="negative" property="negative" jdbcType="BIGINT"/>
        <result column="download_started" property="downloadStarted" jdbcType="BIGINT"/>
        <result column="download_completed" property="downloadCompleted" jdbcType="BIGINT"/>
        <result column="activation" property="activation" jdbcType="BIGINT"/>
        <result column="submit" property="submit" jdbcType="BIGINT"/>
        <result column="stat_date" property="statDate" jdbcType="VARCHAR"/>
        <result column="photo_click" property="photoClick" jdbcType="BIGINT"/>
        <result column="photo_click_ratio" property="photoClickRatio" jdbcType="DOUBLE"/>
        <result column="action_ratio" property="actionRatio" jdbcType="DOUBLE"/>
        <result column="impression_1k_cost" property="impression1kCost" jdbcType="DECIMAL"/>
        <result column="photo_click_cost" property="photoClickCost" jdbcType="DECIMAL"/>
        <result column="action_cost" property="actionCost" jdbcType="DECIMAL"/>
        <result column="event_pay_first_day" property="eventPayFirstDay" jdbcType="BIGINT"/>
        <result column="event_pay_purchase_amount_first_day" property="eventPayPurchaseAmountFirstDay"
                jdbcType="DECIMAL"/>
        <result column="event_pay_first_day_roi" property="eventPayFirstDayRoi" jdbcType="DOUBLE"/>
        <result column="event_pay" property="eventPay" jdbcType="BIGINT"/>
        <result column="event_pay_purchase_amount" property="eventPayPurchaseAmount" jdbcType="DECIMAL"/>
        <result column="event_pay_roi" property="eventPayRoi" jdbcType="DOUBLE"/>
        <result column="event_register" property="eventRegister" jdbcType="BIGINT"/>
        <result column="event_register_cost" property="eventRegisterCost" jdbcType="DECIMAL"/>
        <result column="event_register_ratio" property="eventRegisterRatio" jdbcType="DOUBLE"/>
        <result column="event_jin_jian_app" property="eventJinJianApp" jdbcType="BIGINT"/>
        <result column="event_jin_jian_app_cost" property="eventJinJianAppCost" jdbcType="DECIMAL"/>
        <result column="event_credit_grant_app" property="eventCreditGrantApp" jdbcType="BIGINT"/>
        <result column="event_credit_grant_app_cost" property="eventCreditGrantAppCost" jdbcType="DECIMAL"/>
        <result column="event_credit_grant_app_ratio" property="eventCreditGrantAppRatio" jdbcType="DOUBLE"/>
        <result column="event_order_paid" property="eventOrderPaid" jdbcType="BIGINT"/>
        <result column="event_order_paid_purchase_amount" property="eventOrderPaidPurchaseAmount"
                jdbcType="DECIMAL"/>
        <result column="event_jin_jian_landing_page" property="eventJinJianLandingPage" jdbcType="BIGINT"/>
        <result column="event_jin_jian_landing_page_cost" property="eventJinJianLandingPageCost"
                jdbcType="DECIMAL"/>
        <result column="event_credit_grant_landing_page" property="eventCreditGrantLandingPage" jdbcType="BIGINT"/>
        <result column="event_credit_grant_landing_page_cost" property="eventCreditGrantLandingPageCost"
                jdbcType="DECIMAL"/>
        <result column="event_credit_grant_landing_ratio" property="eventCreditGrantLandingRatio"
                jdbcType="DOUBLE"/>
        <result column="event_next_day_stay_cost" property="eventNextDayStayCost" jdbcType="DECIMAL"/>
        <result column="event_next_day_stay_ratio" property="eventNextDayStayRatio" jdbcType="BIGINT"/>
        <result column="form_action_ratio" property="formActionRatio" jdbcType="DOUBLE"/>
        <result column="event_order_paid_cost" property="eventOrderPaidCost" jdbcType="DECIMAL"/>
        <result column="event_next_day_stay" property="eventNextDayStay" jdbcType="BIGINT"/>
        <result column="form_cost" property="formCost" jdbcType="DECIMAL"/>
        <result column="form_count" property="formCount" jdbcType="BIGINT"/>
        <result column="play_3s_ratio" property="play3sRatio" jdbcType="DOUBLE"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        id
        , account_id, charge, photo_show, aclick, bclick, photo_share, photo_comment, photo_like,
    follow, cancel_follow, report, block, negative, download_started, download_completed,
    activation, submit, stat_date, photo_click, photo_click_ratio, action_ratio, impression_1k_cost,
    photo_click_cost, action_cost, event_pay_first_day, event_pay_purchase_amount_first_day,
    event_pay_first_day_roi, event_pay, event_pay_purchase_amount, event_pay_roi, event_register,
    event_register_cost, event_register_ratio, event_jin_jian_app, event_jin_jian_app_cost,
    event_credit_grant_app, event_credit_grant_app_cost, event_credit_grant_app_ratio,
    event_order_paid, event_order_paid_purchase_amount, event_jin_jian_landing_page,
    event_jin_jian_landing_page_cost, event_credit_grant_landing_page, event_credit_grant_landing_page_cost,
    event_credit_grant_landing_ratio, event_next_day_stay_cost, event_next_day_stay_ratio,
    form_action_ratio, event_order_paid_cost, event_next_day_stay, form_cost, form_count,
    play_3s_ratio, create_time, update_time
    </sql>


    <insert id="replaceBatch">
        replace into ctop_kuaishou_report_daily_account(
        account_id,
        ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        form_action_ratio,
        event_order_paid_cost,
        event_next_day_stay,
        form_cost,
        form_count,
        play_3s_ratio,
        play_5s_ratio,
        play_end_ratio,
        event_app_invoked,
        event_new_user_pay,
        event_new_user_pay_cost,
        event_new_user_pay_ratio
        )
        values
        <foreach collection="addList" item="add" separator=",">
            (
            #{add.accountId},
            #{add.adScene},
            #{add.charge},
            #{add.photoShow},
            #{add.aclick},
            #{add.bclick},
            #{add.photoShare},
            #{add.photoComment},
            #{add.photoLike},
            #{add.follow},
            #{add.cancelFollow},
            #{add.report},
            #{add.block},
            #{add.negative},
            #{add.downloadStarted},
            #{add.downloadCompleted},
            #{add.activation},
            #{add.submit},
            #{add.statDate},
            #{add.photoClick},
            #{add.photoClickRatio},
            #{add.actionRatio},
            #{add.impression1kCost},
            #{add.photoClickCost},
            #{add.actionCost},
            #{add.eventPayFirstDay},
            #{add.eventPayPurchaseAmountFirstDay},
            #{add.eventPayFirstDayRoi},
            #{add.eventPay},
            #{add.eventPayPurchaseAmount},
            #{add.eventPayRoi},
            #{add.eventRegister},
            #{add.eventRegisterCost},
            #{add.eventRegisterRatio},
            #{add.eventJinJianApp},
            #{add.eventJinJianAppCost},
            #{add.eventCreditGrantApp},
            #{add.eventCreditGrantAppCost},
            #{add.eventCreditGrantAppRatio},
            #{add.eventOrderPaid},
            #{add.eventOrderPaidPurchaseAmount},
            #{add.eventJinJianLandingPage},
            #{add.eventJinJianLandingPageCost},
            #{add.eventCreditGrantLandingPage},
            #{add.eventCreditGrantLandingPageCost},
            #{add.eventCreditGrantLandingRatio},
            #{add.eventNextDayStayCost},
            #{add.eventNextDayStayRatio},
            #{add.formActionRatio},
            #{add.eventOrderPaidCost},
            #{add.eventNextDayStay},
            #{add.formCost},
            #{add.formCount},
            #{add.play3sRatio},
            #{add.play5sRatio},
            #{add.playEndRatio},
            #{add.eventAppInvoked},
            #{add.eventNewUserPay},
            #{add.eventNewUserPayCost},
            #{add.eventNewUserPayRatio}
            )
        </foreach>

    </insert>


    <insert id="replaceBatchAdScene">
        replace into ctop_kuaishou_report_daily_account_ad_scene(
        account_id,
        ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        form_action_ratio,
        event_order_paid_cost,
        event_next_day_stay,
        form_cost,
        form_count,
        play_3s_ratio,
        play_5s_ratio,
        play_end_ratio,
        event_app_invoked,
        event_new_user_pay,
        event_new_user_pay_cost,
        event_new_user_pay_ratio

        )
        values
        <foreach collection="addList" item="add" separator=",">
            (
            #{add.accountId},
            #{add.adScene},
            #{add.charge},
            #{add.photoShow},
            #{add.aclick},
            #{add.bclick},
            #{add.photoShare},
            #{add.photoComment},
            #{add.photoLike},
            #{add.follow},
            #{add.cancelFollow},
            #{add.report},
            #{add.block},
            #{add.negative},
            #{add.downloadStarted},
            #{add.downloadCompleted},
            #{add.activation},
            #{add.submit},
            #{add.statDate},
            #{add.photoClick},
            #{add.photoClickRatio},
            #{add.actionRatio},
            #{add.impression1kCost},
            #{add.photoClickCost},
            #{add.actionCost},
            #{add.eventPayFirstDay},
            #{add.eventPayPurchaseAmountFirstDay},
            #{add.eventPayFirstDayRoi},
            #{add.eventPay},
            #{add.eventPayPurchaseAmount},
            #{add.eventPayRoi},
            #{add.eventRegister},
            #{add.eventRegisterCost},
            #{add.eventRegisterRatio},
            #{add.eventJinJianApp},
            #{add.eventJinJianAppCost},
            #{add.eventCreditGrantApp},
            #{add.eventCreditGrantAppCost},
            #{add.eventCreditGrantAppRatio},
            #{add.eventOrderPaid},
            #{add.eventOrderPaidPurchaseAmount},
            #{add.eventJinJianLandingPage},
            #{add.eventJinJianLandingPageCost},
            #{add.eventCreditGrantLandingPage},
            #{add.eventCreditGrantLandingPageCost},
            #{add.eventCreditGrantLandingRatio},
            #{add.eventNextDayStayCost},
            #{add.eventNextDayStayRatio},
            #{add.formActionRatio},
            #{add.eventOrderPaidCost},
            #{add.eventNextDayStay},
            #{add.formCost},
            #{add.formCount},
            #{add.play3sRatio},
            #{add.play5sRatio},
            #{add.playEndRatio},
            #{add.eventAppInvoked},
            #{add.eventNewUserPay},
            #{add.eventNewUserPayCost},
            #{add.eventNewUserPayRatio}

            )
        </foreach>

    </insert>


    <insert id="insertSelective" parameterType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyAccount">
        replace into ctop_kuaishou_report_daily_account
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="accountId != null">
                account_id,
            </if>
            <if test="charge != null">
                charge,
            </if>
            <if test="photoShow != null">
                photo_show,
            </if>
            <if test="aclick != null">
                aclick,
            </if>
            <if test="bclick != null">
                bclick,
            </if>
            <if test="photoShare != null">
                photo_share,
            </if>
            <if test="photoComment != null">
                photo_comment,
            </if>
            <if test="photoLike != null">
                photo_like,
            </if>
            <if test="follow != null">
                follow,
            </if>
            <if test="cancelFollow != null">
                cancel_follow,
            </if>
            <if test="report != null">
                report,
            </if>
            <if test="block != null">
                block,
            </if>
            <if test="negative != null">
                negative,
            </if>
            <if test="downloadStarted != null">
                download_started,
            </if>
            <if test="downloadCompleted != null">
                download_completed,
            </if>
            <if test="activation != null">
                activation,
            </if>
            <if test="submit != null">
                submit,
            </if>
            <if test="statDate != null">
                stat_date,
            </if>
            <if test="photoClick != null">
                photo_click,
            </if>
            <if test="photoClickRatio != null">
                photo_click_ratio,
            </if>
            <if test="actionRatio != null">
                action_ratio,
            </if>
            <if test="impression1kCost != null">
                impression_1k_cost,
            </if>
            <if test="photoClickCost != null">
                photo_click_cost,
            </if>
            <if test="actionCost != null">
                action_cost,
            </if>
            <if test="eventPayFirstDay != null">
                event_pay_first_day,
            </if>
            <if test="eventPayPurchaseAmountFirstDay != null">
                event_pay_purchase_amount_first_day,
            </if>
            <if test="eventPayFirstDayRoi != null">
                event_pay_first_day_roi,
            </if>
            <if test="eventPay != null">
                event_pay,
            </if>
            <if test="eventPayPurchaseAmount != null">
                event_pay_purchase_amount,
            </if>
            <if test="eventPayRoi != null">
                event_pay_roi,
            </if>
            <if test="eventRegister != null">
                event_register,
            </if>
            <if test="eventRegisterCost != null">
                event_register_cost,
            </if>
            <if test="eventRegisterRatio != null">
                event_register_ratio,
            </if>
            <if test="eventJinJianApp != null">
                event_jin_jian_app,
            </if>
            <if test="eventJinJianAppCost != null">
                event_jin_jian_app_cost,
            </if>
            <if test="eventCreditGrantApp != null">
                event_credit_grant_app,
            </if>
            <if test="eventCreditGrantAppCost != null">
                event_credit_grant_app_cost,
            </if>
            <if test="eventCreditGrantAppRatio != null">
                event_credit_grant_app_ratio,
            </if>
            <if test="eventOrderPaid != null">
                event_order_paid,
            </if>
            <if test="eventOrderPaidPurchaseAmount != null">
                event_order_paid_purchase_amount,
            </if>
            <if test="eventJinJianLandingPage != null">
                event_jin_jian_landing_page,
            </if>
            <if test="eventJinJianLandingPageCost != null">
                event_jin_jian_landing_page_cost,
            </if>
            <if test="eventCreditGrantLandingPage != null">
                event_credit_grant_landing_page,
            </if>
            <if test="eventCreditGrantLandingPageCost != null">
                event_credit_grant_landing_page_cost,
            </if>
            <if test="eventCreditGrantLandingRatio != null">
                event_credit_grant_landing_ratio,
            </if>
            <if test="eventNextDayStayCost != null">
                event_next_day_stay_cost,
            </if>
            <if test="eventNextDayStayRatio != null">
                event_next_day_stay_ratio,
            </if>
            <if test="formActionRatio != null">
                form_action_ratio,
            </if>
            <if test="eventOrderPaidCost != null">
                event_order_paid_cost,
            </if>
            <if test="eventNextDayStay != null">
                event_next_day_stay,
            </if>
            <if test="formCost != null">
                form_cost,
            </if>
            <if test="formCount != null">
                form_count,
            </if>
            <if test="play3sRatio != null">
                play_3s_ratio,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=BIGINT},
            </if>
            <if test="accountId != null">
                #{accountId,jdbcType=BIGINT},
            </if>
            <if test="charge != null">
                #{charge,jdbcType=DECIMAL},
            </if>
            <if test="photoShow != null">
                #{photoShow,jdbcType=BIGINT},
            </if>
            <if test="aclick != null">
                #{aclick,jdbcType=BIGINT},
            </if>
            <if test="bclick != null">
                #{bclick,jdbcType=BIGINT},
            </if>
            <if test="photoShare != null">
                #{photoShare,jdbcType=BIGINT},
            </if>
            <if test="photoComment != null">
                #{photoComment,jdbcType=BIGINT},
            </if>
            <if test="photoLike != null">
                #{photoLike,jdbcType=BIGINT},
            </if>
            <if test="follow != null">
                #{follow,jdbcType=BIGINT},
            </if>
            <if test="cancelFollow != null">
                #{cancelFollow,jdbcType=BIGINT},
            </if>
            <if test="report != null">
                #{report,jdbcType=BIGINT},
            </if>
            <if test="block != null">
                #{block,jdbcType=BIGINT},
            </if>
            <if test="negative != null">
                #{negative,jdbcType=BIGINT},
            </if>
            <if test="downloadStarted != null">
                #{downloadStarted,jdbcType=BIGINT},
            </if>
            <if test="downloadCompleted != null">
                #{downloadCompleted,jdbcType=BIGINT},
            </if>
            <if test="activation != null">
                #{activation,jdbcType=BIGINT},
            </if>
            <if test="submit != null">
                #{submit,jdbcType=BIGINT},
            </if>
            <if test="statDate != null">
                #{statDate,jdbcType=VARCHAR},
            </if>
            <if test="photoClick != null">
                #{photoClick,jdbcType=BIGINT},
            </if>
            <if test="photoClickRatio != null">
                #{photoClickRatio,jdbcType=DOUBLE},
            </if>
            <if test="actionRatio != null">
                #{actionRatio,jdbcType=DOUBLE},
            </if>
            <if test="impression1kCost != null">
                #{impression1kCost,jdbcType=DECIMAL},
            </if>
            <if test="photoClickCost != null">
                #{photoClickCost,jdbcType=DECIMAL},
            </if>
            <if test="actionCost != null">
                #{actionCost,jdbcType=DECIMAL},
            </if>
            <if test="eventPayFirstDay != null">
                #{eventPayFirstDay,jdbcType=BIGINT},
            </if>
            <if test="eventPayPurchaseAmountFirstDay != null">
                #{eventPayPurchaseAmountFirstDay,jdbcType=DECIMAL},
            </if>
            <if test="eventPayFirstDayRoi != null">
                #{eventPayFirstDayRoi,jdbcType=DOUBLE},
            </if>
            <if test="eventPay != null">
                #{eventPay,jdbcType=BIGINT},
            </if>
            <if test="eventPayPurchaseAmount != null">
                #{eventPayPurchaseAmount,jdbcType=DECIMAL},
            </if>
            <if test="eventPayRoi != null">
                #{eventPayRoi,jdbcType=DOUBLE},
            </if>
            <if test="eventRegister != null">
                #{eventRegister,jdbcType=BIGINT},
            </if>
            <if test="eventRegisterCost != null">
                #{eventRegisterCost,jdbcType=DECIMAL},
            </if>
            <if test="eventRegisterRatio != null">
                #{eventRegisterRatio,jdbcType=DOUBLE},
            </if>
            <if test="eventJinJianApp != null">
                #{eventJinJianApp,jdbcType=BIGINT},
            </if>
            <if test="eventJinJianAppCost != null">
                #{eventJinJianAppCost,jdbcType=DECIMAL},
            </if>
            <if test="eventCreditGrantApp != null">
                #{eventCreditGrantApp,jdbcType=BIGINT},
            </if>
            <if test="eventCreditGrantAppCost != null">
                #{eventCreditGrantAppCost,jdbcType=DECIMAL},
            </if>
            <if test="eventCreditGrantAppRatio != null">
                #{eventCreditGrantAppRatio,jdbcType=DOUBLE},
            </if>
            <if test="eventOrderPaid != null">
                #{eventOrderPaid,jdbcType=BIGINT},
            </if>
            <if test="eventOrderPaidPurchaseAmount != null">
                #{eventOrderPaidPurchaseAmount,jdbcType=DECIMAL},
            </if>
            <if test="eventJinJianLandingPage != null">
                #{eventJinJianLandingPage,jdbcType=BIGINT},
            </if>
            <if test="eventJinJianLandingPageCost != null">
                #{eventJinJianLandingPageCost,jdbcType=DECIMAL},
            </if>
            <if test="eventCreditGrantLandingPage != null">
                #{eventCreditGrantLandingPage,jdbcType=BIGINT},
            </if>
            <if test="eventCreditGrantLandingPageCost != null">
                #{eventCreditGrantLandingPageCost,jdbcType=DECIMAL},
            </if>
            <if test="eventCreditGrantLandingRatio != null">
                #{eventCreditGrantLandingRatio,jdbcType=DOUBLE},
            </if>
            <if test="eventNextDayStayCost != null">
                #{eventNextDayStayCost,jdbcType=DECIMAL},
            </if>
            <if test="eventNextDayStayRatio != null">
                #{eventNextDayStayRatio,jdbcType=BIGINT},
            </if>
            <if test="formActionRatio != null">
                #{formActionRatio,jdbcType=DOUBLE},
            </if>
            <if test="eventOrderPaidCost != null">
                #{eventOrderPaidCost,jdbcType=DECIMAL},
            </if>
            <if test="eventNextDayStay != null">
                #{eventNextDayStay,jdbcType=BIGINT},
            </if>
            <if test="formCost != null">
                #{formCost,jdbcType=DECIMAL},
            </if>
            <if test="formCount != null">
                #{formCount,jdbcType=BIGINT},
            </if>
            <if test="play3sRatio != null">
                #{play3sRatio,jdbcType=DOUBLE},
            </if>
            <if test="createTime != null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>


    <insert id="loadAccountDailyReport">
        LOAD
        DATA local INFILE
        #{localPath}
        INTO
        TABLE
        ctop_kuaishou_report_daily_account
        FIELDS
        TERMINATED
        BY
        ','
        enclosed
        by
        '"'
        LINES
        TERMINATED
        BY
        '\n'
        IGNORE
        1
        LINES
        (
        ad_scene,
        charge,
        photo_show,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        stat_date,
        stat_hour,
        photo_click,
        photo_click_ratio,
        action_ratio,
        impression_1k_cost,
        photo_click_cost,
        click_1k_cost,
        action_cost,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay_first_day_roi,
        event_pay,
        event_pay_purchase_amount,
        event_pay_roi,
        event_register,
        event_register_cost,
        event_register_ratio,
        event_jin_jian_app,
        event_jin_jian_app_cost,
        event_credit_grant_app,
        event_credit_grant_app_cost,
        event_credit_grant_app_ratio,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_order_paid_cost,
        form_count,
        form_cost,
        form_action_ratio,
        event_jin_jian_landing_page,
        event_jin_jian_landing_page_cost,
        event_credit_grant_landing_page,
        event_credit_grant_landing_page_cost,
        event_credit_grant_landing_ratio,
        event_next_day_stay_cost,
        event_next_day_stay_ratio,
        event_next_day_stay,
        play_3s_ratio,
        event_valid_clues,
        event_valid_clues_cost,
        ad_product_cnt,
        event_goods_view,
        merchant_reco_fans,
        event_order_amount_roi,
        event_goods_view_cost,
        merchant_reco_fans_cost,
        event_new_user_pay,
        event_new_user_pay_cost,
        event_new_user_pay_ratio,
        event_button_click,
        event_button_click_cost,
        event_button_click_ratio,
        play_5s_ratio,
        play_end_ratio,
        event_order_paid_roi,
        event_new_user_jinjian_app,
        event_new_user_jinjian_app_cost,
        event_new_user_jinjian_app_roi,
        event_new_user_credit_grant_app,
        event_new_user_credit_grant_app_cost,
        event_new_user_credit_grant_app_roi,
        event_new_user_jinjian_page,
        event_new_user_jinjian_page_cost,
        event_new_user_jinjian_page_roi,
        event_new_user_credit_grant_page,
        event_new_user_credit_grant_page_cost,
        event_new_user_credit_grant_page_roi,
        event_appoint_form,
        event_appoint_form_cost,
        event_appoint_form_ratio,
        event_appoint_jump_click,
        event_appoint_jump_click_cost,
        event_appoint_jump_click_ratio
        )
        set
        account_id
        =
        #{accountId}
    </insert>


    <select id="selectCost" resultType="com.alibaba.fastjson.JSONObject">
        select sum(charge) cost,
               stat_date   statDate
        from ctop_kuaishou_report_daily_account
        where account_id = #{accountId}
          and stat_date &gt;= #{startDate}
          and stat_date &lt; #{endDate}
        group by stat_date
    </select>

    <select id="getKuaishouAccountInfoByProjectId"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyAccountDTO">
        select
        #{yn} as 'yn',
        concat(#{startDate}, '~' ,#{endDate}) as 'statDate',
        a.account_id as 'accountId',
        b.auth_name as 'authName',
        b.project_id as 'projectId',
        (select project_name from ctop_project where id = b.project_id) as 'projectName',
        round(sum(a.charge),2) as 'charge',
        sum(a.photo_show) as 'photoShow',
        sum(a.photo_click) as 'photoClick',
        sum(a.aclick) as 'aclick',
        sum(a.bclick) as 'bclick',
        sum(a.activation) as 'activation',
        sum(a.submit) as 'submit',
        sum(a.event_register) as 'eventRegister',
        sum(a.event_next_day_stay) as 'eventNextDayStay',
        ROUND(IFNULL(sum(a.charge)/sum(a.activation),0),2) as 'activationCost',
        case when photo_show!=0 then concat(round(sum(a.photo_click)/sum(a.photo_show) * 100 ,2),'%') else 0 end as
        'photoClickRatio',
        case when aclick!=0 then concat(round(sum(a.bclick)/sum(a.aclick) * 100 ,2),'%') else 0 end as 'actionRate',
        (case
        when sum(a.photo_show) != 0
        then round((sum(a.charge) / sum(a.photo_show)) * 1000,2)
        else 0
        end
        ) as 'impression1kCost',
        (case
        when sum(a.photo_click) != 0
        then round(sum(a.charge) / sum(a.photo_click),2)
        else 0
        end
        ) as 'photoClickCost',
        (CASE
        WHEN sum(a.bclick) != 0
        THEN round(sum(a.charge) / sum(a.bclick),2)
        ELSE 0
        end
        ) as 'actionCost',
        sum(a.photo_share) as 'photoShare',
        sum(a.photo_comment) as 'photoComment',
        sum(a.photo_like) as 'photoLike',
        sum(a.follow) as 'follow',
        sum(a.report) as 'report',
        sum(a.cancel_follow) as 'cancelFollow',
        sum(a.block) as 'block',
        sum(a.negative) as 'nagative',
        sum(a.download_started) as 'downloadStarted',
        sum(a.download_completed) as 'downloadCompleted',
        (CASE
        WHEN sum(a.event_next_day_stay) != 0
        THEN round(sum(a.charge) / sum(a.event_next_day_stay),2)
        ELSE 0
        end
        ) as 'eventNextDayStayCost'
        from
        ctop_kuaishou_report_daily_account a
        left join ctop_user_allocation b on a.account_id = b.account_id
        where
        a.stat_date &gt;= #{startDate}
        and
        a.stat_date &lt;= #{endDate}
        <if test="projectId !=null ">
            and
            b.project_id = #{projectId}
        </if>

        <if test="projectList !=null ">
            and
            b.project_id in
            <foreach collection="projectList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="accountIds !=null">
            and a.account_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by a.account_id
        order by sum(a.charge) desc
    </select>

    <select id="getKuaishouProjectInfo" resultType="cn.com.ctop.kuaishou.modules.report.entity.ProjectAccountDTO">
        select
        concat(#{startDate}, '~' , #{endDate}) as 'statDate',
        b.project_id as 'projectId',
        (SELECT project_name FROM ctop_project where id = b.project_id) as 'projectName',
        (select name from ctop_advertiser where id = b.advertiser_id) as 'advertiserName',
        (
        select realname from sys_user where id = (
        select responsible_id from ctop_project where id = b.project_id
        )
        ) as 'responsibleName',
        (
        select realname from sys_user where id = (
        select sale_id from ctop_project where id = b.project_id
        )
        ) as 'saleName',
        (
        select realname from sys_user where id = (
        select design_responsible_id from ctop_project where id = b.project_id
        )
        ) as 'designResponsibleName',
        ifnull(round(sum(a.charge),3),0) as 'charge',
        ifnull(sum(a.show),0) as 'photoShow',
        ifnull(sum(a.photo_click),0) as 'photoClick',
        ifnull(sum(a.aclick),0) as 'aclick',
        ifnull(sum(a.bclick),0) as 'bclick',
        ifnull(sum(a.activation),0) as 'activation',
        ifnull(sum(a.submit),0) as 'submit',
        ifnull(sum(a.event_register),0) as 'eventRegister',
        ifnull(sum(event_next_day_stay),0) as 'eventNextDayStay',
        ROUND(IFNULL(sum(a.charge)/sum(a.activation),0),2) as 'activationCost',
        case when (sum(a.show) is not null and sum(a.show)!=0) then
        concat(round(ifnull(sum(a.photo_click),0)/sum(a.show) * 100 ,2),'%') else 0 end as 'photoClickRate',
        case when (sum(a.aclick) is not null and sum(a.aclick)!=0) then
        concat(round(ifnull(sum(a.bclick),0)/sum(a.aclick) * 100 ,2),'%') else 0 end as 'bclickRate',
        (case
        when (sum(a.show) is not null and sum(a.show) != 0)
        then round((ifnull(sum(a.charge),0) / sum(a.show)) * 1000,2)
        else 0
        end
        ) as 'impression1kCost',
        (case
        when (sum(a.photo_click) is not null and sum(a.photo_click) != 0)
        then round( ifnull(sum(a.charge),0) / sum(a.photo_click),2)
        else 0
        end
        ) as 'photoClickCost',
        (CASE
        WHEN ( sum(a.bclick) is not null and sum(a.bclick) != 0)
        THEN round(sum(a.charge) / sum(a.bclick),2)
        ELSE 0
        end
        ) as 'actionCost',
        ifnull(sum(a.share),0) as 'photoShare',
        ifnull(sum(a.comment),0) as 'photoComment',
        ifnull(sum(a.like),0) as 'photoLike',
        ifnull(sum(a.follow),0) as 'follow',
        ifnull(sum(a.report),0) as 'report',
        ifnull(sum(a.cancel_follow),0) as 'cancelFollow',
        ifnull(sum(a.block),0) as 'block',
        ifnull(sum(a.negative),0) as 'nagative',
        ifnull(sum(a.download_started),0) as 'downloadStarted',
        ifnull(sum(a.download_completed),0) as 'downloadCompleted',
        (CASE
        WHEN ( sum(a.event_next_day_stay) is not null and sum(a.event_next_day_stay) != 0)
        THEN round( ifnull(sum(a.charge),0) / sum(a.event_next_day_stay) ,2)
        ELSE 0
        end
        ) as 'eventNextDayStayCost'
        from
        application.kuaishou_account_report_daily_dw a
        left join ctop_user_allocation b
        on a.account_id = b.account_id
        where
        a.stat_date &gt;= date_format(#{startDate}, '%Y%m%d')
        and
        a.stat_date &lt;= date_format(#{endDate}, '%Y%m%d')
        and
        b.project_id in
        <foreach collection="projectList" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        group by b.project_id order by sum(a.charge) desc
    </select>


    <select id="getKuaishouCampaignInfoByAccountId"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyAccountDTO">

        select
        #{yn} as 'yn',
        concat(#{startDate}, '~' ,#{endDate}) as 'statDate',
        b.project_id as 'projectId',
        (select project_name from ctop_project where id = b.project_id) as 'projectName',
        b.auth_name as 'authName',
        a.account_id as 'accountId',
        a.campaign_id as 'campaignId',
        a.campaign_name as 'campaignName',
        round(sum(a.charge),2) as 'charge',
        sum(a.photo_show) as 'photoShow',
        sum(a.photo_click) as 'photoClick',
        sum(a.aclick) as 'aclick',
        sum(a.bclick) as 'bclick',
        sum(a.activation) as 'activation',
        sum(a.submit) as 'submit',
        sum(a.event_register) as 'eventRegister',
        sum(event_next_day_stay) as 'eventNextDayStay',
        ROUND(IFNULL(sum(a.charge)/sum(a.activation),0),2) as 'activationCost',
        case when photo_show!=0 then concat(round(sum(a.photo_click)/sum(a.photo_show) * 100 ,2),'%') else 0 end as
        'photoClickRatio',
        case when aclick!=0 then concat(round(sum(a.bclick)/sum(a.aclick) * 100 ,2),'%') else 0 end as 'actionRate',
        (case
        when sum(a.photo_show) != 0
        then round((sum(a.charge) / sum(a.photo_show)) * 1000,2)
        else 0
        end
        ) as 'impression1kCost',
        (case
        when sum(a.photo_click) != 0
        then round(sum(a.charge) / sum(a.photo_click),2)
        else 0
        end
        ) as 'photoClickCost',
        (CASE
        WHEN sum(a.bclick) != 0
        THEN round(sum(a.charge) / sum(a.bclick),2)
        ELSE 0
        end
        ) as 'actionCost',
        sum(a.photo_share) as 'photoShare',
        sum(a.photo_comment) as 'photoComment',
        sum(a.photo_like) as 'photoLike',
        sum(a.follow) as 'follow',
        sum(a.report) as 'report',
        sum(a.cancel_follow) as 'cancelFollow',
        sum(a.block) as 'block',
        sum(a.negative) as 'nagative',
        sum(a.download_started) as 'downloadStarted',
        sum(a.download_completed) as 'downloadCompleted',
        (CASE
        WHEN sum(a.event_next_day_stay) != 0
        THEN round(sum(a.charge) / sum(a.event_next_day_stay),2)
        ELSE 0
        end
        ) as 'eventNextDayStayCost'
        from
        ctop_kuaishou_report_daily_campaign a
        left join ctop_user_allocation b on a.account_id = b.account_id
        where
        a.stat_date &gt;= #{startDate}
        and
        a.stat_date &lt;= #{endDate}
        <if test="accountId !=null and accountId != '' ">
            and
            a.account_id = #{accountId}
        </if>
        <if test="accountIds !=null">
            and a.account_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="projectList!=null">
            and
            b.project_id in
            <foreach collection="projectList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by a.campaign_id
        order by sum(a.charge) desc

    </select>


    <select id="getKuaishouUnitInfoByCampaignId"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyAccountDTO">

        select
        #{yn} as 'yn',
        concat(#{startDate}, '~' ,#{endDate}) as 'statDate',
        b.project_id as 'projectId',
        (select project_name from ctop_project where id = b.project_id) as 'projectName',
        a.account_id as 'accountId',
        b.auth_name as 'authName',
        a.campaign_id as 'campaignId',
        a.campaign_name as 'campaignName',
        a.unit_id as 'unitId',
        a.unit_name as 'unitName',
        round(sum(a.charge),2) as 'charge',
        sum(a.photo_show) as 'photoShow',
        sum(a.photo_click) as 'photoClick',
        sum(a.aclick) as 'aclick',
        sum(a.bclick) as 'bclick',
        sum(a.activation) as 'activation',
        sum(a.submit) as 'submit',
        sum(a.event_register) as 'eventRegister',
        sum(event_next_day_stay) as 'eventNextDayStay',
        ROUND(IFNULL(sum(a.charge)/sum(a.activation),0),2) as 'activationCost',
        case when photo_show!=0 then concat(round(sum(a.photo_click)/sum(a.photo_show) * 100 ,2),'%') else 0 end as
        'photoClickRatio',
        case when aclick!=0 then concat(round(sum(a.bclick)/sum(a.aclick) * 100 ,2),'%') else 0 end as 'actionRate',
        (case
        when sum(a.photo_show) != 0
        then round((sum(a.charge) / sum(a.photo_show)) * 1000,2)
        else 0
        end
        ) as 'impression1kCost',
        (case
        when sum(a.photo_click) != 0
        then round(sum(a.charge) / sum(a.photo_click),2)
        else 0
        end
        ) as 'photoClickCost',
        (CASE
        WHEN sum(a.bclick) != 0
        THEN round(sum(a.charge) / sum(a.bclick),2)
        ELSE 0
        end
        ) as 'actionCost',
        sum(a.photo_share) as 'photoShare',
        sum(a.photo_comment) as 'photoComment',
        sum(a.photo_like) as 'photoLike',
        sum(a.follow) as 'follow',
        sum(a.report) as 'report',
        sum(a.cancel_follow) as 'cancelFollow',
        sum(a.block) as 'block',
        sum(a.negative) as 'nagative',
        sum(a.download_started) as 'downloadStarted',
        sum(a.download_completed) as 'downloadCompleted',
        (CASE
        WHEN sum(a.event_next_day_stay) != 0
        THEN round(sum(a.charge) / sum(a.event_next_day_stay),2)
        ELSE 0
        end
        ) as 'eventNextDayStayCost'
        from
        ctop_kuaishou_report_daily_group a
        left join ctop_user_allocation b on a.account_id = b.account_id

        where
        a.stat_date &gt;= #{startDate}
        and
        a.stat_date &lt;= #{endDate}
        <if test="campaignId != null and campaignId != ''">
            and
            a.campaign_id = #{campaignId}
        </if>
        <if test="accountIds !=null">
            and a.account_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="projectList!=null">
            and
            b.project_id in
            <foreach collection="projectList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by unit_id
        order by sum(a.charge) desc

    </select>

    <select id="queryProjectMemberByUserId" resultType="long">
        select
        distinct c.id as projectId
        from
        ctop_project_member b
        left join ctop_project c on b.project_id = c.id
        where b.user_id = #{userId}
        and c.media_id in
        <foreach collection="mediaIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        order by c.create_time desc
    </select>

    <select id="queryProjectInfoBySaleId" resultType="long">
        select
        distinct c.id as projectId
        from
        ctop_project c
        left join sys_user u on c.sale_id = u.id
        where c.sale_id = #{userId}
        and c.media_id in
        <foreach collection="mediaIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        order by c.create_time desc
    </select>

    <select id="queryProjectMemberByIds" resultType="cn.com.ctop.kuaishou.modules.report.entity.ProjectAccountDTO">
        select
        distinct id as projectId,
        project_name as projectName,
        advertiser_name as advertiserName,
        responsible_name as responsibleName,
        design_responsible_name as designResponsibleName
        from ctop_project
        where
        id in
        <foreach collection="projectIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        order by create_time desc
    </select>

    <select id="getRoleCodeByUserId" resultType="string">
        select role_code
        from sys_user_role a
                 left join sys_role b on a.role_id = b.id
        where a.user_id = #{userId}
    </select>


    <select id="queryProjectMemberByAdmins" resultType="long">
        select
        id as projectId
        from
        ctop_project
        where
        media_id in
        <foreach collection="mediaIds" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        order by create_time desc
    </select>

    <select id="getKuaishouCreativeInfoByUnitId"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyAccountDTO">
        select
        #{yn} as 'yn',
        concat(#{startDate}, '~' ,#{endDate}) as 'statDate',
        b.project_id as 'projectId',
        (select project_name from ctop_project where id = b.project_id) as 'projectName',
        a.account_id as 'accountId',
        b.auth_name as 'authName',
        a.creative_id as 'creativeId',
        a.creative_name as 'creativeName',
        a.campaign_id as 'campaignId',
        a.campaign_name as 'campaignName',
        a.unit_id as 'unitId',
        a.unit_name as 'unitName',
        round(sum(a.charge),2) as 'charge',
        sum(a.photo_show) as 'photoShow',
        sum(a.photo_click) as 'photoClick',
        sum(a.aclick) as 'aclick',
        sum(a.bclick) as 'bclick',
        sum(a.activation) as 'activation',
        sum(a.submit) as 'submit',
        sum(a.event_register) as 'eventRegister',
        sum(event_next_day_stay) as 'eventNextDayStay',
        ROUND(IFNULL(sum(a.charge)/sum(a.activation),0),2) as 'activationCost',
        case when photo_show!=0 then concat(round(sum(a.photo_click)/sum(a.photo_show) * 100 ,2),'%') else 0 end as
        'photoClickRatio',
        case when aclick!=0 then concat(round(sum(a.bclick)/sum(a.aclick) * 100 ,2),'%') else 0 end as 'actionRate',
        (case
        when sum(a.photo_show) != 0 and sum(a.photo_show) is not null
        then round((sum(a.charge) / sum(a.photo_show)) * 1000,2)
        else 0
        end
        ) as 'impression1kCost',
        (case
        when sum(a.photo_click) != 0
        then round(sum(a.charge) / sum(a.photo_click),2)
        else 0
        end
        ) as 'photoClickCost',
        (CASE
        WHEN sum(a.bclick) != 0
        THEN round(sum(a.charge) / sum(a.bclick),2)
        ELSE 0
        end
        ) as 'actionCost',
        sum(a.photo_share) as 'photoShare',
        sum(a.photo_comment) as 'photoComment',
        sum(a.photo_like) as 'photoLike',
        sum(a.follow) as 'follow',
        sum(a.report) as 'report',
        sum(a.cancel_follow) as 'cancelFollow',
        sum(a.block) as 'block',
        sum(a.negative) as 'nagative',
        sum(a.download_started) as 'downloadStarted',
        sum(a.download_completed) as 'downloadCompleted',
        (CASE
        WHEN sum(a.event_next_day_stay) != 0
        THEN round(sum(a.charge) / sum(a.event_next_day_stay),2)
        ELSE 0
        end
        ) as 'eventNextDayStayCost',
        (select url from ctop_kuaishou_video_get where photo_id = c.photo_id limit 1) as url,
        (select cover_url from ctop_kuaishou_video_get where photo_id = c.photo_id limit 1) as coverUrl,
        (select material_type from ctop_kuaishou_video_get where photo_id = c.photo_id limit 1) as materialType
        from
        ctop_kuaishou_report_daily_creative a
        left join ctop_user_allocation b on a.account_id = b.account_id
        left join ctop_kuaishou_creative c on a.creative_id = c.creative_id
        where
        a.stat_date &gt;= #{startDate}
        and
        a.stat_date &lt;= #{endDate}

        <if test="unitId != null and unitId !='' ">
            and
            a.unit_id = #{unitId}
        </if>
        <if test="accountIds !=null">
            and a.account_id in
            <foreach collection="accountIds" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="projectList!=null">
            and
            b.project_id in
            <foreach collection="projectList" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        group by a.creative_id
        order by sum(a.charge) desc
    </select>


    <select id="getVideoUrl" resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouVideoInfoDTO">
        select url       as url,
               cover_url as coverUrl
        from ctop_kuaishou_video_get
        where photo_id = #{photoId} limit 1
    </select>

    <select id="getUserProjectAccountIds" resultType="long">
        select
        a.account_id
        from
        (select project_id,account_id,media_id from ctop_user_allocation group by account_id) a
        left join ctop_project_member b on a.project_id=b.project_id
        where
        a.media_id in (2,4)
        and user_id = #{userId}
        order by create_time desc
        <if test="yn==1">
            limit 50
        </if>
    </select>

    <select id="getUserProjectAccountIdsByAdmin" resultType="long">
        select
        a.account_id
        from
        ctop_user_allocation a
        left join ctop_project b on a.project_id = b.id
        where
        b.media_id in (2,4)
        <if test="yn==1">
            limit 50
        </if>
    </select>

    <select id="getSaleProjectAccountIds" resultType="long">
        select
        a.account_id
        from
        (select project_id,account_id,media_id,create_time from ctop_user_allocation group by account_id) a
        left join ctop_project b on a.project_id=b.id
        where
        b.sale_id = #{userId}
        and b.media_id in (2,4)
        order by a.create_time desc
        <if test="yn==1">
            limit 50
        </if>
    </select>

    <select id="getKuaishouProjectInfoEntity"
            resultType="cn.com.ctop.common.module.vo.KuaishouReportAccountDailyDTOEntity">
        select
        concat(#{startDate}, '~' , #{endDate}) as 'statDate',
        b.project_id as 'projectId',
        (SELECT project_name FROM ctop_project where id = b.project_id) as 'projectName',
        (select name from ctop_advertiser where id = b.advertiser_id) as 'advertiserName',
        (
        select realname from sys_user where id = (
        select responsible_id from ctop_project where id = b.project_id
        )
        ) as 'responsibleName',
        (
        select realname from sys_user where id = (
        select sale_id from ctop_project where id = b.project_id
        )
        ) as 'saleName',
        (
        select realname from sys_user where id = (
        select design_responsible_id from ctop_project where id = b.project_id
        )
        ) as 'designResponsibleName',
        ifnull(round(sum(a.charge),3),0) as 'charge',
        ifnull(sum(a.photo_show),0) as 'photoShow',
        ifnull(sum(a.photo_click),0) as 'photoClick',
        ifnull(sum(a.aclick),0) as 'aclick',
        ifnull(sum(a.bclick),0) as 'bclick',
        ifnull(sum(a.activation),0) as 'activation',
        ifnull(sum(a.submit),0) as 'submit',
        ifnull(sum(a.event_register),0) as 'eventRegister',
        ifnull(sum(event_next_day_stay),0) as 'eventNextDayStay',
        ROUND(IFNULL(sum(a.charge)/sum(a.activation),0),2) as 'activationCost',
        case when (sum(a.photo_show) is not null and sum(a.photo_show)!=0) then
        concat(round(ifnull(sum(a.photo_click),0)/sum(a.photo_show) * 100 ,2),'%') else 0 end as 'photoClickRate',
        case when (sum(a.aclick) is not null and sum(a.aclick)!=0) then
        concat(round(ifnull(sum(a.bclick),0)/sum(a.aclick) * 100 ,2),'%') else 0 end as 'bclickRate',
        (case
        when (sum(a.photo_show) is not null and sum(a.photo_show) != 0)
        then round((ifnull(sum(a.charge),0) / sum(a.photo_show)) * 1000,2)
        else 0
        end
        ) as 'impression1kCost',
        (case
        when (sum(a.photo_click) is not null and sum(a.photo_click) != 0)
        then round( ifnull(sum(a.charge),0) / sum(a.photo_click),2)
        else 0
        end
        ) as 'photoClickCost',
        (CASE
        WHEN ( sum(a.bclick) is not null and sum(a.bclick) != 0)
        THEN round(sum(a.charge) / sum(a.bclick),2)
        ELSE 0
        end
        ) as 'actionCost',
        ifnull(sum(a.photo_share),0) as 'photoShare',
        ifnull(sum(a.photo_comment),0) as 'photoComment',
        ifnull(sum(a.photo_like),0) as 'photoLike',
        ifnull(sum(a.follow),0) as 'follow',
        ifnull(sum(a.report),0) as 'report',
        ifnull(sum(a.cancel_follow),0) as 'cancelFollow',
        ifnull(sum(a.block),0) as 'block',
        ifnull(sum(a.negative),0) as 'nagative',
        ifnull(sum(a.download_started),0) as 'downloadStarted',
        ifnull(sum(a.download_completed),0) as 'downloadCompleted',
        (CASE
        WHEN ( sum(a.event_next_day_stay) is not null and sum(a.event_next_day_stay) != 0)
        THEN round( ifnull(sum(a.charge),0) / sum(a.event_next_day_stay) ,2)
        ELSE 0
        end
        ) as 'eventNextDayStayCost'
        from
        ctop_kuaishou_report_daily_account a
        left join ctop_user_allocation b
        on a.account_id = b.account_id
        where
        a.stat_date &gt;= #{startDate}
        and
        a.stat_date &lt;= #{endDate}
        and
        b.project_id in
        <foreach collection="projectList" item="item" separator=","
                 open="(" close=")">
            #{item}
        </foreach>
        group by b.project_id order by sum(a.charge) desc

    </select>

    <select id="queryEveryDayDataForAccount"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.EtlKuaishouReportAccountHourly">
        SELECT stat_date,
               account_id,
               SUM(charge) as 'charge',
               SUM(photo_show) as 'photoShow',
               SUM(photo_click) as 'photoClick',
               SUM(aclick) as 'aclick',
               SUM(bclick) as 'bclick',
               SUM(photo_share) as 'photoShare',
               SUM(photo_comment) as 'photoComment',
               SUM(photo_like) as 'photoLike',
               SUM(follow) as 'follow',
               SUM(cancel_follow) as 'cancelFollow',
               SUM(report) as 'report',
               SUM(block) as 'block',
               SUM(negative) as 'negative',
               SUM(download_started) as 'downloadStarted',
               SUM(download_completed) as 'downloadCompleted',
               SUM(activation) as 'activation',
               SUM(submit) as 'submit',
               SUM(event_pay_first_day) as 'eventPayFirstDay',
               SUM(event_pay_purchase_amount_first_day) as 'eventPayPurchaseAmountFirstDay',
               SUM(event_pay) as 'eventPay',
               SUM(event_pay_purchase_amount) as 'eventPayPurchaseAmount',
               SUM(event_register) as 'eventRegister',
               SUM(event_jin_jian_app) as 'eventJinJianApp',
               SUM(event_credit_grant_app) as 'eventCreditGrantApp',
               SUM(event_order_paid) as 'eventOrderPaid',
               SUM(event_order_paid_purchase_amount) as 'eventOrderPaidPurchaseAmount',
               SUM(event_jin_jian_landing_page) as 'eventJinJianLandingPage',
               SUM(event_credit_grant_landing_page) as 'eventCreditGrantLandingPage',
               SUM(event_next_day_stay) as 'eventNextDayStay',
               SUM(form_count) as 'formCount',
               SUM(play_3s_ratio * aclick) as 'play3sCount'
        FROM ctop_kuaishou_report_daily_account
        WHERE stat_date = DATE_FORMAT(date_sub(now(), interval 1 day), '%Y-%m-%d')
        GROUP BY account_id
    </select>
    <select id="getSumChargeByAccountIdAndStatDate" resultType="java.math.BigDecimal">
        select sum(charge)
        from ctop_kuaishou_report_daily_account
        where account_id = #{accountId}
          and stat_date = #{statDate}
    </select>
    <select id="getPositionData" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.ad_scene as 'adScene',
        ${filedAll}
        FROM ctop_kuaishou_report_daily_account_ad_scene t1
        WHERE t1.stat_date &lt;= #{endDate}
        AND t1.stat_date &gt;= #{startDate}
        AND t1.account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by t1.ad_scene
    </select>

</mapper>
