From fb657d7462b0e67bc14c1358fd603bf7110857c3 Mon Sep 17 00:00:00 2001 From: Lukas Metzger Date: Thu, 11 Jun 2020 14:46:16 +0200 Subject: Added total aggregation --- dash/assets/20-style.css | 4 ++++ dash/pages/total.py | 46 ++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 50 insertions(+) diff --git a/dash/assets/20-style.css b/dash/assets/20-style.css index 85adf70..b94bdc8 100644 --- a/dash/assets/20-style.css +++ b/dash/assets/20-style.css @@ -12,3 +12,7 @@ ul.navbar-nav { padding-top: 4px; padding-bottom: 4px; } + +pre { + font-family: inherit; +} diff --git a/dash/pages/total.py b/dash/pages/total.py index eb7a63c..130c7f2 100644 --- a/dash/pages/total.py +++ b/dash/pages/total.py @@ -52,12 +52,18 @@ def layout(): dbc.Row([dbc.Col([ dcc.Loading(dcc.Graph(id='total-graph-times')) ])]), + dbc.Row([dbc.Col([ + dcc.Loading(html.Pre(id='total-sum-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(html.Pre(id='total-sum-sessions')) + ])]), dbc.Row([dbc.Col([ dcc.Loading(dcc.Graph(id='total-graph-users')) ])]) @@ -242,6 +248,46 @@ def make_graph_users(days, rangeStart, rangeEnd, satellites): ) return figure +@app.callback([Output('total-sum-times', 'children'), + Output('total-sum-sessions', 'children')], + [Input('total-range', 'start_date'), + Input('total-range', 'end_date'), + Input('total-satellites', 'value')]) +def make_sums(rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT + SUM(t.totalTime) AS totalTime, + SUM(t.totalSessionTime) AS totalSessionTime, + SUM(t.totalOffTime) AS totalOffTime, + SUM(t.shortSessions) AS shortSessions, + SUM(t.longSessions) AS longSessions + FROM reports r + JOIN total t ON r.id = t.report + WHERE t.days = 7 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, (rangeStart, rangeEnd)) + + data = cursor.fetchall()[0] + + db.closeConnection(dbcon) + + strTimes = 'Total Time: {}h Total Session Time: {}h Total Off Time: {}h'.format( + int(data['totalTime'] / 3600), + int(data['totalSessionTime'] / 3600), + int(data['totalOffTime'] / 3600) + ) + strSessions = 'Total Short Sessions: {} Total Long Sessions: {}'.format(data['shortSessions'], data['longSessions']) + + return strTimes, strSessions + def get_satellites(): dbcon = db.getConnection() cursor = dbcon.cursor() -- cgit v1.2.3-55-g7522