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(+)

(limited to 'dash')

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