Added machine information and some minor fixes

4 files changed, 214 insertions, 6 deletions

diff --git a/assets/20-style.css b/assets/20-style.css
index b909f70..85adf70 100644
--- a/assets/20-style.css
+++ b/assets/20-style.css
@@ -5,6 +5,7 @@ ul.navbar-nav {
 
 .table-responsive-lg {
     padding-left: 15px;
+    padding-right: 15px;
 }
 
 .DateInput_input {
diff --git a/index.py b/index.py
index 14316fa..ad9c77f 100644
--- a/index.py
+++ b/index.py
@@ -9,11 +9,12 @@ import plotly.graph_objects as go
 import plotly.express as px
 
 from app import app
-from pages import satellites, total
+from pages import satellites, total, machines
 
 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},
 ]
 
 pio.templates['custom'] = dict(
@@ -24,7 +25,8 @@ pio.templates['custom'] = dict(
             yanchor = 'top',
             font = dict(size = 25)
         ),
-        separators=',.'
+        separators=',.',
+        yaxis_tickformat = ',0f',
     )
 )
 pio.templates.default = 'custom'
diff --git a/pages/machines.py b/pages/machines.py
new file mode 100644
index 0000000..b750ddc
--- /dev/null
+++ b/pages/machines.py
@@ -0,0 +1,209 @@
+#!/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
+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='machines-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='machines-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='machines-satellites',
+                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    multi=True,
+                    value=[],
+                    placeholder='All Satellites'
+                )
+            ])
+        ]),
+        dbc.Row([dbc.Col([
+            dcc.Graph(id='machines-graph-location')
+        ])]),
+        dbc.Row([
+            dbc.Col(width=12, md=6, xl=4, children=[
+                dcc.Graph(id='machines-graph-ram')
+            ]),
+            dbc.Col(width=12, md=6, xl=4, children=[
+                dcc.Graph(id='machines-graph-realcores')
+            ]),
+            dbc.Col(width=12, md=6, xl=4, children=[
+                dcc.Graph(id='machines-graph-kvmstate')
+            ]),
+            dbc.Col(width=12, xl=6, children=[
+                dcc.Graph(id='machines-graph-cpumodel')
+            ]),
+            dbc.Col(width=12, xl=6, children=[
+                dcc.Graph(id='machines-graph-systemmodel')
+            ])
+        ])
+    ])
+
+@app.callback(Output('machines-graph-location', 'figure'),
+              [Input('machines-days', 'value'),
+               Input('machines-date', 'date'),
+               Input('machines-satellites', 'value')])
+def make_graph_location(days, date, satellites):
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    stmt = """
+            SELECT m.value, m.count
+            FROM reports r
+            JOIN machine m ON r.id = m.report
+            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
+            AND m.days = %s AND m.property = 'location'
+        """
+    stmt_end = """ ORDER BY m.count DESC"""
+
+    if len(satellites) > 0:
+        formatStrings = ','.join(['%s'] * len(satellites))
+        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days] + satellites))
+    else:
+        cursor.execute(stmt + stmt_end, (date, days))
+
+    data = cursor.fetchall()
+
+    db.closeConnection(dbcon)
+
+    figure = go.Figure()
+    figure.add_trace(go.Bar(
+        x=[item['value'][0:9] for item in data],
+        y=[item['count'] for item in data]
+    ))
+    figure.update_layout(
+        title_text = 'Locations'
+    )
+    return figure
+
+@app.callback(Output('machines-graph-cpumodel', 'figure'),
+              [Input('machines-days', 'value'),
+               Input('machines-date', 'date'),
+               Input('machines-satellites', 'value')])
+def make_graph_cpumodel(days, date, satellites):
+    return make_graph(days, date, satellites, 'cpumodel', 'CPU', typ='bar', height=600)
+
+@app.callback(Output('machines-graph-realcores', 'figure'),
+              [Input('machines-days', 'value'),
+               Input('machines-date', 'date'),
+               Input('machines-satellites', 'value')])
+def make_graph_realcores(days, date, satellites):
+    return make_graph(days, date, satellites, 'realcores', 'Cores', unit=' Cores')
+
+@app.callback(Output('machines-graph-ram', 'figure'),
+              [Input('machines-days', 'value'),
+               Input('machines-date', 'date'),
+               Input('machines-satellites', 'value')])
+def make_graph_ram(days, date, satellites):
+    return make_graph(days, date, satellites, 'ram', 'RAM', unit=' GB')
+
+@app.callback(Output('machines-graph-systemmodel', 'figure'),
+              [Input('machines-days', 'value'),
+               Input('machines-date', 'date'),
+               Input('machines-satellites', 'value')])
+def make_graph_systemmodel(days, date, satellites):
+    return make_graph(days, date, satellites, 'systemmodel', 'System Model', typ='bar', height=600)
+
+@app.callback(Output('machines-graph-kvmstate', 'figure'),
+              [Input('machines-days', 'value'),
+               Input('machines-date', 'date'),
+               Input('machines-satellites', 'value')])
+def make_graph_kvmstate(days, date, satellites):
+    return make_graph(days, date, satellites, 'kvmstate', 'KVM State')
+
+
+def make_graph(days, date, satellites, prop, title, unit='', typ='pie', height=450):
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    stmt = """
+            SELECT m.value, SUM(m.count) AS count
+            FROM reports r
+            JOIN machine m ON r.id = m.report
+            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
+            AND m.days = %s AND m.property = %s
+        """
+    stmt_end = """ GROUP BY m.value"""
+
+    if len(satellites) > 0:
+        formatStrings = ','.join(['%s'] * len(satellites))
+        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days, prop] + satellites))
+    else:
+        cursor.execute(stmt + stmt_end, (date, days, prop))
+
+    data = cursor.fetchall()
+    data = natsorted(data, key = lambda x: x['value' if typ=='pie' else 'count'])
+
+    db.closeConnection(dbcon)
+
+    figure = go.Figure()
+    if typ == 'pie':
+        figure.add_trace(go.Pie(
+            labels=[item['value'] + unit for item in data],
+            values=[item['count'] for item in data],
+            hole=0.3,
+            sort=False,
+            textinfo='label',
+            direction='clockwise'
+        ))
+    elif typ == 'bar':
+        figure.add_trace(go.Bar(
+            y=[item['value'] + unit for item in data],
+            x=[item['count'] for item in data],
+            orientation='h'
+        ))
+    figure.update_layout(
+        title_text = title,
+        height = height,
+        yaxis_automargin=True
+    )
+    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']
diff --git a/pages/total.py b/pages/total.py
index 843d581..f4d6c90 100644
--- a/pages/total.py
+++ b/pages/total.py
@@ -104,7 +104,6 @@ def make_graph_times(days, rangeStart, rangeEnd, satellites):
     ))
     figure.update_layout(
         yaxis_ticksuffix=' h',
-        yaxis_tickformat = ',0f',
         title_text = 'Usage Times'
     )
     return figure
@@ -145,7 +144,6 @@ def make_graph_session_length(days, rangeStart, rangeEnd, satellites):
         ))
     figure.update_layout(
         yaxis_ticksuffix=' min',
-        yaxis_tickformat = ',0f',
         title_text = 'Median Session Length'
     )
     return figure
@@ -190,7 +188,6 @@ def make_graph_sessions(days, rangeStart, rangeEnd, satellites):
         width=2*86400e3
     ))
     figure.update_layout(
-        yaxis_tickformat = ',0f',
         barmode='stack',
         title_text = 'Sessions',
     )
@@ -230,7 +227,6 @@ def make_graph_users(days, rangeStart, rangeEnd, satellites):
         name='Unique Users'
     ))
     figure.update_layout(
-        yaxis_tickformat = ',0f',
         showlegend=True,
         title_text = 'Unique Users',
     )