#!/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 prettytime import prettytime
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='total-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='total-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='total-satellites',
options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
multi=True,
value=[],
placeholder='All Satellites',
persistence=True,
persistence_type='memory'
)
])
]),
dbc.Row([dbc.Col([
dcc.Loading(dcc.Graph(id='total-graph-times'))
])]),
dbc.Row([dbc.Col([
dcc.Loading(html.Pre(id='total-sum-times'))
])]),
dbc.Row([dbc.Col([
dcc.Loading(dcc.Graph(id='total-graph-session-length'))
])]),
dbc.Row([dbc.Col([
dcc.Loading(dcc.Graph(id='total-graph-sessions'))
])]),
dbc.Row([dbc.Col([
dcc.Loading(html.Pre(id='total-sum-sessions'))
])]),
dbc.Row([dbc.Col([
dcc.Loading(dcc.Graph(id='total-graph-users'))
])])
])
@app.callback(Output('total-graph-times', 'figure'),
[Input('total-days', 'value'),
Input('total-range', 'start_date'),
Input('total-range', 'end_date'),
Input('total-satellites', 'value')])
def make_graph_times(days, rangeStart, rangeEnd, satellites):
dbcon = db.getConnection()
cursor = dbcon.cursor()
stmt = """
SELECT r.date, SUM(t.totalTime) as totalTime, SUM(t.totalSessionTime) as totalSessionTime, SUM(t.totalOffTime) as totalOffTime
FROM reports r
JOIN total t ON r.id = t.report
WHERE t.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=[int(item['totalTime'] / 3600) for item in data],
hovertext=['({})'.format(prettytime(item['totalTime'])) for item in data],
mode='lines+markers',
name='Total Time'
))
figure.add_trace(go.Scatter(
x=[item['date'] for item in data],
y=[int(item['totalSessionTime'] / 3600) for item in data],
hovertext=['({})'.format(prettytime(item['totalSessionTime'])) for item in data],
mode='lines+markers',
name='Total Session Time'
))
figure.add_trace(go.Scatter(
x=[item['date'] for item in data],
y=[int(item['totalOffTime'] / 3600) for item in data],
hovertext=['({})'.format(prettytime(item['totalOffTime'])) for item in data],
mode='lines+markers',
name='Total Off Time'
))
figure.update_layout(
yaxis_ticksuffix=' h',
title_text = 'Usage Times',
uirevision=42
)
return figure
@app.callback(Output('total-graph-session-length', 'figure'),
[Input('total-days', 'value'),
Input('total-range', 'start_date'),
Input('total-range', 'end_date'),
Input('total-satellites', 'value')])
def make_graph_session_length(days, rangeStart, rangeEnd, satellites):
dbcon = db.getConnection()
cursor = dbcon.cursor()
stmt = """
SELECT
r.date,
r.ip,
COALESCE(nm.name, r.ip) AS name,
t.medianSessionLength
FROM reports r
JOIN total t ON r.id = t.report
LEFT OUTER JOIN names nm ON r.ip = nm.ip
WHERE t.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 ({})'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites))
else:
cursor.execute(stmt, (days, rangeStart, rangeEnd))
data = cursor.fetchall()
db.closeConnection(dbcon)
figure = go.Figure()
for sat, n in set([(item['ip'], item['name']) for item in data]):
figure.add_trace(go.Scatter(
x=[item['date'] for item in data if item['ip'] == sat],
y=[int(item['medianSessionLength'] / 60) for item in data if item['ip'] == sat],
mode='lines+markers',
name=n
))
figure.update_layout(
yaxis_ticksuffix=' min',
title_text = 'Median Session Length',
uirevision=42
)
return figure
@app.callback(Output('total-graph-sessions', 'figure'),
[Input('total-days', 'value'),
Input('total-range', 'start_date'),
Input('total-range', 'end_date'),
Input('total-satellites', 'value')])
def make_graph_sessions(days, rangeStart, rangeEnd, satellites):
dbcon = db.getConnection()
cursor = dbcon.cursor()
stmt = """
SELECT r.date, SUM(t.shortSessions) as shortSessions, SUM(t.longSessions) as longSessions
FROM reports r
JOIN total t ON r.id = t.report
WHERE t.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.Bar(
x=[item['date'] for item in data],
y=[item['shortSessions'] for item in data],
name='Short Sessions',
width=2*86400e3
))
figure.add_trace(go.Bar(
x=[item['date'] for item in data],
y=[item['longSessions'] for item in data],
name='Long Sessions',
width=2*86400e3
))
figure.update_layout(
barmode='stack',
title_text = 'Sessions',
uirevision=42
)
return figure
@app.callback(Output('total-graph-users', 'figure'),
[Input('total-days', 'value'),
Input('total-range', 'start_date'),
Input('total-range', 'end_date'),
Input('total-satellites', 'value')])
def make_graph_users(days, rangeStart, rangeEnd, satellites):
dbcon = db.getConnection()
cursor = dbcon.cursor()
stmt = """
SELECT r.date, SUM(t.uniqueUsers) as uniqueUsers
FROM reports r
JOIN total t ON r.id = t.report
WHERE t.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['uniqueUsers'] for item in data],
mode='lines+markers',
name='Unique Users'
))
figure.update_layout(
showlegend=True,
title_text = 'Unique Users',
uirevision=42
)
return figure
@app.callback(Output('total-sum-times', 'children'),
[Input('total-range', 'start_date'),
Input('total-range', 'end_date'),
Input('total-graph-times', 'relayoutData'),
Input('total-satellites', 'value')])
def make_sum_times(rangeStart, rangeEnd, layoutData, satellites):
dbcon = db.getConnection()
cursor = dbcon.cursor()
if layoutData != None and layoutData.get('xaxis.range[0]'):
rangeStart = layoutData['xaxis.range[0]'].split()[0]
rangeEnd = layoutData['xaxis.range[1]'].split()[0]
stmt = """
SELECT
SUM(t.totalTime) AS totalTime,
SUM(t.totalSessionTime) AS totalSessionTime,
SUM(t.totalOffTime) AS totalOffTime
FROM reports r
JOIN total t ON r.id = t.report
WHERE t.days = 7 AND r.date >= %s AND r.date <= %s
"""
if len(satellites) > 0:
formatStrings = ','.join(['%s'] * len(satellites))
cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings), tuple([rangeStart, rangeEnd] + satellites))
else:
cursor.execute(stmt, (rangeStart, rangeEnd))
data = cursor.fetchall()[0]
db.closeConnection(dbcon)
try:
strTimes = 'Total Time: {} Total Session Time: {} Total Off Time: {}'.format(
prettytime(data['totalTime']),
prettytime(data['totalSessionTime']),
prettytime(data['totalOffTime'])
)
except:
strTimes = "No Data available!"
return strTimes
@app.callback(Output('total-sum-sessions', 'children'),
[Input('total-range', 'start_date'),
Input('total-range', 'end_date'),
Input('total-graph-sessions', 'relayoutData'),
Input('total-satellites', 'value')])
def make_sum_sessions(rangeStart, rangeEnd, layoutData, satellites):
dbcon = db.getConnection()
cursor = dbcon.cursor()
if layoutData != None and layoutData.get('xaxis.range[0]'):
rangeStart = layoutData['xaxis.range[0]'].split()[0]
rangeEnd = layoutData['xaxis.range[1]'].split()[0]
stmt = """
SELECT
SUM(t.shortSessions) AS shortSessions,
SUM(t.longSessions) AS longSessions
FROM reports r
JOIN total t ON r.id = t.report
WHERE t.days = 7 AND r.date >= %s AND r.date <= %s
"""
if len(satellites) > 0:
formatStrings = ','.join(['%s'] * len(satellites))
cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings), tuple([rangeStart, rangeEnd] + satellites))
else:
cursor.execute(stmt, (rangeStart, rangeEnd))
data = cursor.fetchall()[0]
db.closeConnection(dbcon)
try:
strSessions = 'Total Short Sessions: {} Total Long Sessions: {}'.format(data['shortSessions'], data['longSessions'])
except:
strSessions = "No Data available!"
return strSessions
def get_satellites():
dbcon = db.getConnection()
cursor = dbcon.cursor()
cursor.execute("""
SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
LEFT OUTER JOIN names nm ON r.ip = nm.ip
ORDER BY name ASC
""")
data = [(item['ip'], item['name']) for item in cursor.fetchall()]
db.closeConnection(dbcon)
return data