<?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.dao.nofeeds.NFAdRecordMapper">
    <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="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 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="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="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="getRevenueByIds" resultType="com.ytpm.middle.view.NetWorkRevenueVO">
        SELECT
        network_form_id networkId,
        network_name networkName,
        sum( revenue ) netWorkRevenue
        FROM
        yt_dyz_ad_record
        WHERE
        network_form_id in
        <foreach collection="networkIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
        and app_id in
        <foreach collection="appIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
        <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 networkId
    </select>
    <select id="getEcpmByIds" resultType="com.ytpm.middle.view.NetWorkEcpmVO">
        SELECT
        network_form_id netWorkId,
        network_name netWorkName,
        avg( ecpm ) networkEcpm
        FROM
        yt_dyz_ad_record
        WHERE
        network_form_id in
        <foreach collection="networkIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
        and app_id in
        <foreach collection="appIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
        <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 netWorkId
    </select>
    <resultMap id="AdGroupStaticsMap" type="com.ytpm.agent.view.AgentAdGroupStaticsVO">
        <id column="network_form_id" property="netowrkId" />
        <result column="network_name" property="netowrkName" />
        <collection property="networkEcpmGroups" ofType="com.ytpm.agent.view.AgentNetworkEcpmGroupVO">
            <result column="count" property="count" />
            <result column="hour" property="hour" />
            <result column="ecpm" property="ecpm" />
            <result column="ad_source_type" property="adSourceType" />
        </collection>
        <collection property="networkRevenueGroups" ofType="com.ytpm.agent.view.AgentNetworkRevenueGroupVO">
            <result column="count" property="count" />
            <result column="hour" property="hour" />
            <result column="revenue" property="revenue" />
            <result column="ad_source_type" property="adSourceType" />
        </collection>
    </resultMap>
    <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>
        and DATE_FORMAT(finish_time, '%Y-%m-%d') = current_date()
    </select>
    <select id="getAdGroupStatics" resultMap="AdGroupStaticsMap">
        SELECT
        network_form_id,
        network_name,
        count(*) count,
        DATE_FORMAT(finish_time,'%Y-%m-%d %H:00:00') as `hour`,
        sum( revenue ) revenue,
        sum( ecpm ) ecpm,
        ad_source_type
        FROM
        yt_dyz_ad_record
        WHERE
        app_id in
        <foreach collection="appIds.split(',')" separator="," item="item" open="(" close=")">
            #{item}
        </foreach>
        AND DATE( finish_time ) = DATE(now())
        group by network_form_id,`hour`
    </select>
</mapper>