diff options
| -rw-r--r-- | index.py | 3 | ||||
| -rw-r--r-- | pages/dozmod.py | 192 |
2 files changed, 194 insertions, 1 deletions
@@ -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 |