<?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="com.ytpm.diary.dao.AdRecordMapper">

    <insert id="addOne">
        insert into yt_dyz_ad_record
        (
         record_id,
         user_id,
         nick_name,
         placement_id,
         ad_source_id,
         revenue,
         network_form_id,
         network_name,
         network_placement_id,
         begin_time,
         finish_time,
         result_json,
         ad_source_index,
         ad_source_type,
         ecpm,
         app_id
        )
        values
        (
         #{recordId},
         #{userId},
         #{nickName},
         #{placementId},
         #{adSourceId},
         #{revenue},
         #{networkFormId},
         #{networkName},
         #{networkPlacementId},
         ifnull(#{beginTime}, now()),
         ifnull(#{finishTime}, now()),
         #{resultJson},
         #{adSourceIndex},
         #{adSourceType},
         #{ecpm},
         #{appId}
        )
    </insert>
    <select id="countByAppIds" resultType="java.lang.Integer">
        select
            count(record_id)
        from yt_dyz_ad_record
        where ad_source_type = 1 and app_id in
        <foreach collection="appIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
    </select>
    <select id="getByUserId" resultType="com.ytpm.app.model.YtDyzAdRecord">
        select
            record_id, user_id,app_id, nick_name, placement_id, ad_source_id, revenue, network_form_id, network_name, network_placement_id, finish_time, begin_time,result_json,ad_source_type,ad_source_index,ecpm
        from yt_dyz_ad_record
        where user_id = #{userId}
          <if test="adsourceType != null and adsourceType!=888">
              and ad_source_type = #{adsourceType}
          </if>
        order by finish_time desc
    </select>
    <select id="getByUserIds" resultType="com.ytpm.app.model.YtDyzAdRecord">
        select
            record_id, user_id,app_id, nick_name, placement_id, ad_source_id, revenue, network_form_id, network_name, network_placement_id, finish_time, begin_time,result_json,ad_source_type,ad_source_index,ecpm
        from yt_dyz_ad_record
        where user_id in
              <foreach collection="userIds.split(',')" open="(" close=")" separator="," item="item">
                  #{item}
              </foreach>
        order by finish_time
    </select>
    <select id="getHourRevenue" resultType="com.ytpm.middle.view.AppRevenueHourVO">
        SELECT
            HOUR(finish_time) `hour`,
            DATE_FORMAT(finish_time,'%Y-%m-%d %H:00:00') as `time`,
            sum( revenue ) revenue
        FROM
            yt_dyz_ad_record
        WHERE
            app_id = #{appId}
            <if test="type != null and type ==1">
                and DATE(finish_time) = DATE(now())
            </if>
            <if test="type != null and type ==2">
                and DATE(finish_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
            </if>
            <if test="type != null and type ==3">
                and DATE_FORMAT(finish_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
            </if>
        GROUP BY
            `hour`
    </select>
    <select id="getRevenueByTime" resultType="java.math.BigDecimal">
        select
            sum(ar.revenue)
        from yt_dyz_ad_record ar
        join yt_dyz_user au on ar.user_id = au.user_id
        <where>
            <if test="userId != null and userId !=''">
                and ar.user_id = #{userId}
            </if>
            <if test="nickName != null and nickName !=''">
                and au.nick_name like concat('%',#{nickName},'%')
            </if>
            <if test="ditchId != null">
                and au.ditch_id = #{ditchId}
            </if>
            <if test="userStatus != null">
                and au.user_status = #{userStatus}
            </if>
            <if test="appIds != null and appIds != ''">
                and ar.app_id in
                <foreach collection="appIds.split(',')" separator="," item="item" open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="registryTimeBegin != null ">
                and DATE_FORMAT(au.registry_time,'%Y-%m-%d') <![CDATA[>=]]> #{registryTimeBegin}
            </if>
            <if test="registryTimeEnd != null ">
                and DATE_FORMAT(au.registry_time,'%Y-%m-%d') <![CDATA[<=]]> #{registryTimeEnd}
            </if>
        </where>
    </select>
    <select id="getTodayRecord" resultType="com.ytpm.app.model.YtDyzAdRecord">
        select
            record_id, user_id, app_id, nick_name, placement_id, ad_source_id, ad_source_type, ad_source_index, revenue, ecpm, network_form_id, network_name, network_placement_id, finish_time, begin_time, result_json
        from yt_dyz_ad_record
        where app_id in
        <foreach collection="appIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
        <if test="firstDay != null">
            and finish_time <![CDATA[>=]]> #{firstDay}
        </if>
        <if test="lastDay != null">
            and finish_time <![CDATA[<=]]> #{lastDay}
        </if>
    </select>
</mapper>
