diff options
author | Lukas Metzger | 2020-05-22 18:43:17 +0200 |
---|---|---|
committer | Lukas Metzger | 2020-05-22 18:43:17 +0200 |
commit | fcc360620163d005986bb20e6792c7d8f71e7a3a (patch) | |
tree | 89b10d6078447ec570e05423088287b817609e5d /pages/total.py | |
download | bwlp-statistics-fcc360620163d005986bb20e6792c7d8f71e7a3a.tar.gz bwlp-statistics-fcc360620163d005986bb20e6792c7d8f71e7a3a.tar.xz bwlp-statistics-fcc360620163d005986bb20e6792c7d8f71e7a3a.zip |
First version
Diffstat (limited to 'pages/total.py')
-rw-r--r-- | pages/total.py | 248 |
1 files changed, 248 insertions, 0 deletions
diff --git a/pages/total.py b/pages/total.py new file mode 100644 index 0000000..dcc52ea --- /dev/null +++ b/pages/total.py @@ -0,0 +1,248 @@ +#!/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.Graph(id='graph-times') + ])]), + dbc.Row([dbc.Col([ + dcc.Graph(id='graph-session-length') + ])]), + dbc.Row([dbc.Col([ + dcc.Graph(id='graph-sessions') + ])]), + dbc.Row([dbc.Col([ + dcc.Graph(id='graph-users') + ])]) + ]) + +@app.callback(Output('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', + yaxis_tickformat = ',0f', + title_text = 'Usage Times' + ) + return figure + +@app.callback(Output('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', + yaxis_tickformat = ',0f', + title_text = 'Median Session Length' + ) + return figure + +@app.callback(Output('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( + yaxis_tickformat = ',0f', + barmode='stack', + title_text = 'Sessions', + ) + return figure + +@app.callback(Output('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( + yaxis_tickformat = ',0f', + showlegend=True, + title_text = 'Unique Users', + ) + 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 |