Added location page

2 files changed, 224 insertions, 1 deletions

diff --git a/index.py b/index.py
index 53b7fb3..a3babb2 100644
--- a/index.py
+++ b/index.py
@@ -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']