path: root/pages/total.py

#!/usr/bin/env python3

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

import plotly.graph_objects as go

import datetime as dt

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='total-days',
                    options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]],
                    value=7,
                    clearable=False
                )
            ]),
            dbc.Col(width=12, lg=3, children=[
                dcc.DatePickerRange(
                    id='total-range',
                    start_date=dt.datetime.now() - dt.timedelta(days=12*30),
                    end_date=dt.datetime.now(),
                    display_format='DD-MM-YYYY',
                    initial_visible_month=dt.datetime.now(),
                    first_day_of_week=1
                ),
            ]),
            dbc.Col(width=12, lg=6, children=[
                dcc.Dropdown(
                    id='total-satellites',
                    options=[{'label': s, 'value': s} for s in get_satellites()],
                    multi=True,
                    value=[],
                    placeholder='All Satellites'
                )
            ])
        ]),
        dbc.Row([dbc.Col([
            dcc.Loading(dcc.Graph(id='total-graph-times'))
        ])]),
        dbc.Row([dbc.Col([
            dcc.Loading(dcc.Graph(id='total-graph-session-length'))
        ])]),
        dbc.Row([dbc.Col([
            dcc.Loading(dcc.Graph(id='total-graph-sessions'))
        ])]),
        dbc.Row([dbc.Col([
            dcc.Loading(dcc.Graph(id='total-graph-users'))
        ])])
    ])

@app.callback(Output('total-graph-times', 'figure'),
              [Input('total-days', 'value'),
               Input('total-range', 'start_date'),
               Input('total-range', 'end_date'),
               Input('total-satellites', 'value')])
def make_graph_times(days, rangeStart, rangeEnd, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT r.date, SUM(t.totalTime) as totalTime, SUM(t.totalSessionTime) as totalSessionTime, SUM(t.totalOffTime) as totalOffTime
            FROM reports r
            JOIN total t ON r.id = t.report
            WHERE t.days = %s AND r.date >= %s AND r.date <= %s
        """

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites))
    else:
        cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Scatter(
        x=[item['date'] for item in data],
        y=[int(item['totalTime'] / 3600) for item in data],
        mode='lines+markers',
        name='Total Time'
    ))
    figure.add_trace(go.Scatter(
        x=[item['date'] for item in data],
        y=[int(item['totalSessionTime'] / 3600) for item in data],
        mode='lines+markers',
        name='Total Session Time'
    ))
    figure.add_trace(go.Scatter(
        x=[item['date'] for item in data],
        y=[int(item['totalOffTime'] / 3600) for item in data],
        mode='lines+markers',
        name='Total Off Time'
    ))
    figure.update_layout(
        yaxis_ticksuffix=' h',
        title_text = 'Usage Times',
        uirevision=42
    )
    return figure

@app.callback(Output('total-graph-session-length', 'figure'),
              [Input('total-days', 'value'),
               Input('total-range', 'start_date'),
               Input('total-range', 'end_date'),
               Input('total-satellites', 'value')])
def make_graph_session_length(days, rangeStart, rangeEnd, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT r.date, r.ip, t.medianSessionLength
            FROM reports r
            JOIN total t ON r.id = t.report
            WHERE t.days = %s AND r.date >= %s AND r.date <= %s
        """

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites))
    else:
        cursor.execute(stmt, (days, rangeStart, rangeEnd))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    for sat in set([item['ip'] for item in data]):
        figure.add_trace(go.Scatter(
            x=[item['date'] for item in data if item['ip'] == sat],
            y=[int(item['medianSessionLength'] / 60) for item in data if item['ip'] == sat],
            mode='lines+markers',
            name=sat
        ))
    figure.update_layout(
        yaxis_ticksuffix=' min',
        title_text = 'Median Session Length',
        uirevision=42
    )
    return figure

@app.callback(Output('total-graph-sessions', 'figure'),
              [Input('total-days', 'value'),
               Input('total-range', 'start_date'),
               Input('total-range', 'end_date'),
               Input('total-satellites', 'value')])
def make_graph_sessions(days, rangeStart, rangeEnd, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT r.date, SUM(t.shortSessions) as shortSessions, SUM(t.longSessions) as longSessions
            FROM reports r
            JOIN total t ON r.id = t.report
            WHERE t.days = %s AND r.date >= %s AND r.date <= %s
        """

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites))
    else:
        cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
        x=[item['date'] for item in data],
        y=[item['shortSessions'] for item in data],
        name='Short Sessions',
        width=2*86400e3
    ))
    figure.add_trace(go.Bar(
        x=[item['date'] for item in data],
        y=[item['longSessions'] for item in data],
        name='Long Sessions',
        width=2*86400e3
    ))
    figure.update_layout(
        barmode='stack',
        title_text = 'Sessions',
        uirevision=42
    )
    return figure

@app.callback(Output('total-graph-users', 'figure'),
              [Input('total-days', 'value'),
               Input('total-range', 'start_date'),
               Input('total-range', 'end_date'),
               Input('total-satellites', 'value')])
def make_graph_users(days, rangeStart, rangeEnd, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT r.date, SUM(t.uniqueUsers) as uniqueUsers
            FROM reports r
            JOIN total t ON r.id = t.report
            WHERE t.days = %s AND r.date >= %s AND r.date <= %s
        """

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites))
    else:
        cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Scatter(
        x=[item['date'] for item in data],
        y=[item['uniqueUsers'] for item in data],
        mode='lines+markers',
        name='Unique Users'
    ))
    figure.update_layout(
        showlegend=True,
        title_text = 'Unique Users',
        uirevision=42
    )
    return figure

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

    cursor.execute("""SELECT DISTINCT ip FROM reports""")

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

    db.closeConnection(dbcon)
    return data