path: root/dash/pages/locations.py

#!/usr/bin/env python3

import dash_core_components as dcc
import dash_html_components as html
import dash_bootstrap_components as dbc
from plotly.subplots import make_subplots
from dash.dependencies import Input, Output

import plotly.graph_objects as go

import datetime as dt
from natsort import natsorted
from prettytime import prettytime

import db
from app import app

def layout():
    return dbc.Container(fluid=True, children=[
        dbc.Row([
            dbc.Col(width=12, lg=1, children=[
                dcc.Dropdown(
                    id='locations-days',
                    options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]],
                    value=7,
                    clearable=False,
                    persistence=True,
                    persistence_type='memory'
                )
            ]),
            dbc.Col(width=12, lg=3, children=[
                dcc.DatePickerSingle(
                    id='locations-date',
                    date=get_newest_date(),
                    display_format='DD-MM-YYYY',
                    max_date_allowed=get_newest_date(),
                    min_date_allowed=get_oldest_date(),
                    initial_visible_month=get_newest_date(),
                    first_day_of_week=1,
                    persistence=True,
                    persistence_type='memory'
                ),
            ]),
            dbc.Col(width=12, lg=6, children=[
                dcc.Dropdown(
                    id='locations-satellite',
                    options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
                    value=None,
                    placeholder='All Satellites',
                    persistence=True,
                    persistence_type='memory'
                )
            ])
        ]),
        dcc.Loading(style={'marginTop': '200px'}, children=dbc.Row(id='locations-content'))
    ])

@app.callback(Output('locations-content', 'children'),
              [Input('locations-days', 'value'),
               Input('locations-date', 'date'),
               Input('locations-satellite', 'value')])
def make_content(days, date, satellite):
    if satellite == None:
        return make_content_all(days, date)
    else:
        return make_content_sat(days, date, satellite)

def make_content_all(days, date):
    dbcon= db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT r.ip, l.locationname, l.totalSessionTime
            FROM reports r
            JOIN perLocation l ON r.id = l.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
                    AND l.days = %s
        """
    cursor.execute(stmt, (date, days))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    satellites = natsorted(get_satellites(), key = lambda x: x[0])

    figures = []
    for sat, name in satellites:
        figure = go.Figure()
        figure.add_trace(go.Pie(
            labels=[item['locationname'][0:9] for item in data if item['ip'] == sat],
            values=[item['totalSessionTime'] for item in data if item['ip'] == sat],
            text=[prettytime(item['totalSessionTime']) for item in data if item['ip'] == sat],
            hole=0.3,
            textinfo='none',
            hoverinfo='label+text+percent',
            direction='clockwise'
        ))
        numElements = len([item['ip'] for item in data if item['ip'] == sat])
        figure.update_layout(
            title_text = '<sub>Sessiontime per Location (Total: {})</sub><br>{}'.format(numElements, name),
            showlegend=False
        )
        if len([item for item in data if item['ip'] == sat and item['totalSessionTime'] > 0]) > 0:
            figures.append(dbc.Col(width=12, md=6, xl=4, children=dcc.Graph(figure=figure)))

    return figures

def make_content_sat(days, date, satellite):
    return [
            dbc.Col(width=12, children=dcc.Graph(figure=item(days, date, satellite)))
            for item in [make_content_sat_sessiontime, make_content_sat_median, make_content_sat_sessions]
    ]

def make_content_sat_sessiontime(days, date, satellite):
    dbcon= db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT l.locationname, l.totalSessionTime
            FROM reports r
            JOIN perLocation l ON r.id = l.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
                    AND l.days = %s AND r.ip = %s
            ORDER BY l.locationname ASC
        """
    cursor.execute(stmt, (date, days, satellite))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
        x=[item['locationname'][0:9] for item in data],
        y=[int(item['totalSessionTime'] / 3600) for item in data],
        hovertext=[prettytime(item['totalSessionTime']) for item in data],
        hoverinfo='text'
    ))
    figure.update_layout(
        title_text = 'Total Session Time',
        yaxis_ticksuffix=' h',
    )

    return figure

def make_content_sat_median(days, date, satellite):
    dbcon= db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT l.locationname, l.medianSessionLength
            FROM reports r
            JOIN perLocation l ON r.id = l.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
                    AND l.days = %s AND r.ip = %s
            ORDER BY l.locationname ASC
        """
    cursor.execute(stmt, (date, days, satellite))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
        x=[item['locationname'][0:9] for item in data],
        y=[int(item['medianSessionLength'] / 60) for item in data],
        hovertext=[prettytime(item['medianSessionLength']) for item in data],
        hoverinfo='text'
    ))
    figure.update_layout(
        title_text = 'Median Session Time',
        yaxis_ticksuffix=' min',
    )

    return figure

def make_content_sat_sessions(days, date, satellite):
    dbcon= db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT l.locationname, l.shortSessions, l.longSessions
            FROM reports r
            JOIN perLocation l ON r.id = l.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
                    AND l.days = %s AND r.ip = %s
            ORDER BY l.locationname ASC
        """
    cursor.execute(stmt, (date, days, satellite))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
        x=[item['locationname'][0:9] for item in data],
        y=[item['shortSessions'] for item in data],
        name='Short Sessions'
    ))
    figure.add_trace(go.Bar(
        x=[item['locationname'][0:9] for item in data],
        y=[item['longSessions'] for item in data],
        name='Long Sessions'
    ))
    figure.update_layout(
        title_text = 'Sessions',
        barmode='stack',
        legend=dict(x=0.9, y=1.2)
    )

    return figure

def get_newest_date():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""SELECT date FROM reports ORDER BY date DESC LIMIT 1""")

    data = cursor.fetchall()

    db.closeConnection(dbcon)
    return data[0]['date']

def get_oldest_date():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""SELECT date FROM reports ORDER BY date ASC LIMIT 1""")

    data = cursor.fetchall()

    db.closeConnection(dbcon)
    return data[0]['date']

def get_satellites():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""
                SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
                LEFT OUTER JOIN names nm ON r.ip = nm.ip
                ORDER BY name ASC
            """)

    data = [(item['ip'], item['name']) for item in cursor.fetchall()]

    db.closeConnection(dbcon)
    return data