1 files changed, 201 insertions, 0 deletions
diff --git a/dash/pages/dozmod.py b/dash/pages/dozmod.py
new file mode 100644
index 0000000..4d9fb24
--- /dev/null
+++ b/dash/pages/dozmod.py
@@ -0,0 +1,201 @@
+#!/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='dozmod-days',
+                    options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]],
+                    value=7,
+                    clearable=False,
+                    persistence=True,
+                    persistence_type='memory'
+                )
+            ]),
+            dbc.Col(width=12, lg=3, children=[
+                dcc.DatePickerRange(
+                    id='dozmod-range',
+                    start_date=(dt.datetime.now() - dt.timedelta(days=365)).date(),
+                    end_date=dt.datetime.now().date(),
+                    display_format='DD-MM-YYYY',
+                    initial_visible_month=dt.datetime.now(),
+                    first_day_of_week=1,
+                    persistence=True,
+                    persistence_type='memory'
+                ),
+            ]),
+            dbc.Col(width=12, lg=6, children=[
+                dcc.Dropdown(
+                    id='dozmod-satellites',
+                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    multi=True,
+                    value=[],
+                    placeholder='All Satellites',
+                    persistence=True,
+                    persistence_type='memory'
+                )
+            ])
+        ]),
+        dbc.Row([dbc.Col([
+            dcc.Loading(dcc.Graph(id='dozmod-graph-vms'))
+        ])]),
+        dbc.Row([dbc.Col([
+            dcc.Loading(dcc.Graph(id='dozmod-graph-lectures'))
+        ])]),
+        dbc.Row([dbc.Col([
+            dcc.Loading(dcc.Graph(id='dozmod-graph-users'))
+        ])])
+    ])
+
+@app.callback(Output('dozmod-graph-vms', 'figure'),
+              [Input('dozmod-days', 'value'),
+               Input('dozmod-range', 'start_date'),
+               Input('dozmod-range', 'end_date'),
+               Input('dozmod-satellites', 'value')])
+def make_graph_vms(days, rangeStart, rangeEnd, satellites):
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    stmt = """
+            SELECT r.date, SUM(d.vms_total) AS vms_total, SUM(d.vms_new) AS vms_new, SUM(d.vms_updated) AS vms_updated, SUM(d.vms_valid) AS vms_valid
+            FROM reports r
+            JOIN dozmod d ON r.id = d.report
+            WHERE d.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()
+    for field, title in [('vms_total', 'VMs Total'), ('vms_new', 'VMs New'), ('vms_updated', 'VMs Updated'), ('vms_valid', 'VMs Valid')]:
+        figure.add_trace(go.Scatter(
+            x=[item['date'] for item in data],
+            y=[item[field] for item in data],
+            mode='lines+markers',
+            name=title
+        ))
+    figure.update_layout(
+        title_text = 'VM Statistics',
+        uirevision=42
+    )
+    return figure
+
+@app.callback(Output('dozmod-graph-lectures', 'figure'),
+              [Input('dozmod-days', 'value'),
+               Input('dozmod-range', 'start_date'),
+               Input('dozmod-range', 'end_date'),
+               Input('dozmod-satellites', 'value')])
+def make_graph_lectures(days, rangeStart, rangeEnd, satellites):
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    stmt = """
+            SELECT r.date, SUM(d.lectures_total) AS lectures_total, SUM(d.lectures_new) AS lectures_new, SUM(d.lectures_updated) AS lectures_updated, SUM(d.lectures_valid) AS lectures_valid
+            FROM reports r
+            JOIN dozmod d ON r.id = d.report
+            WHERE d.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()
+    for field, title in [('lectures_total', 'Lectures Total'), ('lectures_new', 'Lectures New'), ('lectures_updated', 'Lectures Updated'), ('lectures_valid', 'Lectures Valid')]:
+        figure.add_trace(go.Scatter(
+            x=[item['date'] for item in data],
+            y=[item[field] for item in data],
+            mode='lines+markers',
+            name=title
+        ))
+    figure.update_layout(
+        title_text = 'Lecture Statistics',
+        uirevision=42
+    )
+    return figure
+
+@app.callback(Output('dozmod-graph-users', 'figure'),
+              [Input('dozmod-days', 'value'),
+               Input('dozmod-range', 'start_date'),
+               Input('dozmod-range', 'end_date'),
+               Input('dozmod-satellites', 'value')])
+def make_graph_users(days, rangeStart, rangeEnd, satellites):
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    stmt = """
+            SELECT r.date, SUM(d.users_total) AS users_total, SUM(d.users_organizations) AS users_organizations
+            FROM reports r
+            JOIN dozmod d ON r.id = d.report
+            WHERE d.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['users_total'] for item in data],
+        mode='lines+markers',
+        name='Users Total',
+        yaxis='y'
+    ))
+    figure.add_trace(go.Scatter(
+        x=[item['date'] for item in data],
+        y=[item['users_organizations'] for item in data],
+        mode='lines+markers',
+        name='Users Organizations',
+        yaxis='y2'
+    ))
+    figure.update_layout(
+        title_text = 'Users Statistics',
+        yaxis = dict(title='Users Total', side='left'),
+        yaxis2 = dict(title = 'Users Organizations', side='right', overlaying='y'),
+        uirevision=42
+    )
+    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