<?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="org.wfc.user.mapper.UClientMapper">

    <select id="getCurrentClients" resultType="org.wfc.user.domain.vo.UClientCurrentVo">
        SELECT
            c.id,
            c.client_mac,
            c.client_name,
            c.client_device_type,
            cdr.traffic_up,
            cdr.traffic_down,
            cdr.up_time,
            cdr.activity
        FROM
            u_cdr cdr
                LEFT JOIN ( SELECT ch.cdr_id, max( ch.end_time ) end_time FROM u_cdr_history ch WHERE ch.del_flag = 0 GROUP BY ch.cdr_id ) ch ON cdr.id = ch.cdr_id
                LEFT JOIN u_client c ON cdr.client_id = c.id
                AND c.del_flag = 0
        WHERE
            cdr.del_flag = 0
            <if test="userId != null and userId != ''">
                AND cdr.user_id = #{userId}
            </if>
          AND cdr.last_seen_time > ifnull( ch.end_time, 0 )
    </select>

    <select id="getHistoryByUser" resultType="org.wfc.user.domain.vo.UClientHistoryUserVo">
        SELECT
            cdr.client_id id,
            c.client_name,
            c.client_mac,
            c.client_device_type,
            min( ch.start_time ) start_time,
            max( ch.end_time ) end_time,
            sum(
                    ifnull( ch.duration, 0 )) duration,
            sum(
                    ifnull( ch.traffic_down, 0 )) traffic_down,
            sum(
                    ifnull( ch.traffic_up, 0 )) traffic_up
        FROM
            u_cdr cdr
                LEFT JOIN u_cdr_history ch ON cdr.id = ch.cdr_id
                AND ch.del_flag = 0
                LEFT JOIN u_client c ON cdr.client_id = c.id
                AND c.del_flag = 0
        WHERE
            cdr.del_flag = 0
            AND ch.id is not null
            <if test="userId != null and userId != ''">
                AND cdr.user_id = #{userId}
            </if>
        GROUP BY
            cdr.user_id,
            cdr.client_id
        ORDER BY
            min( ch.start_time ) DESC
    </select>

</mapper>
