summaryrefslogtreecommitdiffstats
path: root/dash/pages/total.py
diff options
context:
space:
mode:
Diffstat (limited to 'dash/pages/total.py')
-rw-r--r--dash/pages/total.py254
1 files changed, 254 insertions, 0 deletions
diff --git a/dash/pages/total.py b/dash/pages/total.py
new file mode 100644
index 0000000..eb7a63c
--- /dev/null
+++ b/dash/pages/total.py
@@ -0,0 +1,254 @@
+#!/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,
+ persistence=True,
+ persistence_type='memory'
+ )
+ ]),
+ dbc.Col(width=12, lg=3, children=[
+ dcc.DatePickerRange(
+ id='total-range',
+ start_date=(dt.datetime.now() - dt.timedelta(days=365)).date(),
+ end_date=dt.datetime.now().date(),
+ display_format='DD-MM-YYYY',
+ initial_visible_month=dt.datetime.now(),
+ first_day_of_week=1,
+ persistence=True,
+ persistence_type='memory'
+ ),
+ ]),
+ 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',
+ persistence=True,
+ persistence_type='memory'
+ )
+ ])
+ ]),
+ 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
generated by cgit v1.2.3-55-g7522 (git 2.39.0) at 2024-05-15 20:16:17 +0200