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

    <select id="getUnitListByStudyStatus"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyExplore">
        SELECT account_id, unit_id
        FROM ctop_kuaishou_report_daily_explore
        WHERE explore_status IN (0, 1)
    </select>

    <select id="getTodaylistTotal" resultType="java.lang.Long">
        SELECT count(1)
        FROM ctop_kuaishou_group
        WHERE account_id = #{accountId}
          AND put_status = 1
          AND study_status = 1
          AND unit_id NOT IN (
            SELECT unit_id FROM ctop_kuaishou_report_daily_explore WHERE account_id = #{accountId}
        )

    </select>

    <select id="getTodaylist" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t2.unit_id,
        t2.unit_name,
        IFNULL(charge,'-') as 'charge',
        IFNULL(activation,'-') as 'activation',
        IFNULL(ROUND(charge/activation,2),'-') as 'activationCost',
        IFNULL(event_pay,'-') as 'eventPay',
        IFNULL(ROUND(charge/event_pay,2),'-') as 'eventPayCost',
        IFNULL(event_register,'-') as 'eventRegister',
        IFNULL(ROUND(charge/event_register,2),'-') as 'eventRegisterCost',
        IFNULL(aclick,'-') as 'aclick'
        FROM ctop_etl_kuaishou_group_daily_report t1
        Right JOIN (
        SELECT unit_id,unit_name FROM ctop_kuaishou_group WHERE account_id = #{accountId} AND unit_id NOT IN (
        SELECT unit_id FROM ctop_kuaishou_report_daily_explore WHERE account_id = #{accountId}
        )
        AND put_status = 1
        AND study_status = 1
        ) t2 ON t1.unit_id = t2.unit_id
        AND stat_date = DATE_FORMAT(now(),'%Y-%m-%d')
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode} ${sortType}
        </if>
    </select>

    <select id="getHistoryListTotal" resultType="java.lang.Long">
        SELECT count(1)
        FROM ctop_kuaishou_report_daily_explore
        WHERE account_id = #{request.accountId}
          AND stat_date &gt;= CONCAT(#{request.startTime}, ' 00:00:01')
          AND stat_date &lt;= CONCAT(#{request.endTime}, ' 23:59:59')
    </select>

    <select id="getHistoryList" resultType="cn.com.ctop.kuaishou.modules.report.entity.KuaishouReportDailyExplore">
        SELECT
        id,
        account_id,
        unit_id,
        unit_name,
        explore_budget,
        ocpx_action_type,
        total_charge,
        impression,
        photo_click,
        click,
        unified_conversion,
        unitfied_conversion_cost,
        stat_date,
        explore_status
        FROM ctop_kuaishou_report_daily_explore
        WHERE account_id = #{request.accountId}
        AND stat_date &gt;= CONCAT(#{request.startTime},' 00:00:01')
        AND stat_date &lt;= CONCAT(#{request.endTime},' 23:59:59')
        <if test="request.unitId != null">
            AND unit_id = #{request.unitId}
        </if>
        <if test="request.unitName != null">
            AND LOCATE(#{request.unitName},unit_name)
        </if>
        <if test="request.exploreStatus != null ">
            AND explore_status = #{request.exploreStatus}
        </if>
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode} ${sortType}
        </if>
    </select>

    <select id="getUnitIds" resultType="java.lang.Long">
        SELECT unit_id
        FROM ctop_kuaishou_group
        WHERE account_id = #{accountId}
          AND put_status = 1
          AND study_status = 1
          AND unit_id NOT IN (
            SELECT unit_id FROM ctop_kuaishou_report_daily_explore WHERE account_id = #{accountId}
        )
    </select>

</mapper>