#!/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