#!/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': s, 'value': s} for s 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())
figures = []
for sat 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 = 'Sessiontime per Location (Total: {})
{}'.format(numElements, sat),
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_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
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']