summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--index.py3
-rw-r--r--pages/dozmod.py192
2 files changed, 194 insertions, 1 deletions
diff --git a/index.py b/index.py
index 080e8fe..9b8db80 100644
--- a/index.py
+++ b/index.py
@@ -9,7 +9,7 @@ import plotly.graph_objects as go
import plotly.express as px
from app import app
-from pages import satellites, total, machines, locations, vms
+from pages import satellites, total, machines, locations, vms, dozmod
pages = [
{'name': 'Satellites', 'link': '/', 'id': 'satellites', 'layout': satellites.layout},
@@ -17,6 +17,7 @@ pages = [
{'name': 'Machines', 'link': '/machines', 'id': 'machines', 'layout': machines.layout},
{'name': 'Locations', 'link': '/locations', 'id': 'locations', 'layout': locations.layout},
{'name': 'VMs', 'link': '/vms', 'id': 'vms', 'layout': vms.layout},
+ {'name': 'Dozmod', 'link': '/dozmod', 'id': 'dozmod', 'layout': dozmod.layout},
]
pio.templates['custom'] = dict(
diff --git a/pages/dozmod.py b/pages/dozmod.py
new file mode 100644
index 0000000..af97bab
--- /dev/null
+++ b/pages/dozmod.py
@@ -0,0 +1,192 @@
+#!/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
+ )
+ ]),
+ dbc.Col(width=12, lg=3, children=[
+ dcc.DatePickerRange(
+ id='dozmod-range',
+ start_date=dt.datetime.now() - dt.timedelta(days=12*30),
+ end_date=dt.datetime.now(),
+ display_format='DD-MM-YYYY',
+ initial_visible_month=dt.datetime.now(),
+ first_day_of_week=1
+ ),
+ ]),
+ 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'
+ )
+ ])
+ ]),
+ 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'
+ )
+ 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'
+ )
+ 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')
+ )
+ 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