diff options
| -rw-r--r-- | index.py | 3 | ||||
| -rw-r--r-- | pages/locations.py | 222 |
2 files changed, 224 insertions, 1 deletions
@@ -9,12 +9,13 @@ import plotly.graph_objects as go import plotly.express as px from app import app -from pages import satellites, total, machines +from pages import satellites, total, machines, locations pages = [ {'name': 'Satellites', 'link': '/', 'id': 'satellites', 'layout': satellites.layout}, {'name': 'Total', 'link': '/total', 'id': 'total', 'layout': total.layout}, {'name': 'Machines', 'link': '/machines', 'id': 'machines', 'layout': machines.layout}, + {'name': 'Locations', 'link': '/locations', 'id': 'locations', 'layout': locations.layout}, ] pio.templates['custom'] = dict( diff --git a/pages/locations.py b/pages/locations.py new file mode 100644 index 0000000..9777769 --- /dev/null +++ b/pages/locations.py @@ -0,0 +1,222 @@ +#!/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 + +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 + ) + ]), + 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(), + initial_visible_month=get_newest_date(), + first_day_of_week=1 + ), + ]), + 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' + ) + ]) + ]), + 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=[int(item['totalSessionTime'] / 3600) for item in data if item['ip'] == sat], + text=[str(int(item['totalSessionTime'] / 3600)) + ' h' for item in data if item['ip'] == sat], + hole=0.3, + textinfo='none', + hoverinfo='label+text+percent', + direction='clockwise' + )) + figure.update_layout( + title_text = sat, + showlegend=False + ) + 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] + )) + 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] + )) + 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'] |