path: root/dash/pages/machines.py

#!/usr/bin/env python3

import dash_core_components as dcc
import dash_html_components as html
import dash_bootstrap_components as dbc
import dash_table as dtbl
from dash.dependencies import Input, Output

import plotly.graph_objects as go

import datetime as dt
from natsort import natsorted

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='machines-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.DatePickerSingle(
                    id='machines-date',
                    date=get_newest_date(),
                    display_format='DD-MM-YYYY',
                    max_date_allowed=get_newest_date(),
                    min_date_allowed=get_oldest_date(),
                    initial_visible_month=get_newest_date(),
                    first_day_of_week=1,
                    persistence=True,
                    persistence_type='memory'
                ),
            ]),
            dbc.Col(width=12, lg=6, children=[
                dcc.Dropdown(
                    id='machines-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='machines-graph-location'))
        ])]),
        dbc.Row([dbc.Col([
            dcc.Loading(html.Pre(id='machines-sum-location'))
        ])]),
        dbc.Row([
            dbc.Col(width=12, md=6, xl=4, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-ram'))
            ]),
            dbc.Col(width=12, md=6, xl=4, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-realcores'))
            ]),
            dbc.Col(width=12, md=6, xl=4, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-kvmstate'))
            ]),
            dbc.Col(width=12, xl=7, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-systemmodel')),
            ]),
            dbc.Col(width=12, xl=7, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-basesystem')),
            ]),
            dbc.Col(width=12, xl=4, id='machines-graph-systemmodel-locations-container', children=[
                html.H3(id='machines-graph-systemmodel-locations-head', style=dict(fontWeight='normal', fontSize='25px', fontFamily='Open Sans', marginTop='20px')),
                dcc.Loading(dtbl.DataTable(
                    id='machines-graph-systemmodel-locations',
                    columns=[
                        {'id': 'ip', 'name': 'Location', 'type': 'text'},
                        {'id': 'count', 'name': 'Count', 'type': 'numeric'}
                    ],
                    style_cell_conditional=[
                        {
                            'if': {'column_type': 'text'},
                            'textAlign': 'left'
                        }
                    ]
                ))
            ]),
            dbc.Col(width=12, xl=7, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-cpumodel')),
            ]),
            dbc.Col(width=12, xl=4, id='machines-graph-cpumodel-locations-container', children=[
                html.H3(id='machines-graph-cpumodel-locations-head', style=dict(fontWeight='normal', fontSize='25px', fontFamily='Open Sans', marginTop='20px')),
                dcc.Loading(dtbl.DataTable(
                    id='machines-graph-cpumodel-locations',
                    columns=[
                        {'id': 'ip', 'name': 'Location', 'type': 'text'},
                        {'id': 'count', 'name': 'Count', 'type': 'numeric'}
                    ],
                    style_cell_conditional=[
                        {
                            'if': {'column_type': 'text'},
                            'textAlign': 'left'
                        }
                    ]
                ))
            ])
        ])
    ])

@app.callback([Output('machines-graph-location', 'figure'),
               Output('machines-sum-location', 'children')],
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_location(days, date, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT m.value, m.count
            FROM reports r
            JOIN machine m ON r.id = m.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
            AND m.days = %s AND m.property = 'location'
        """
    stmt_end = """ ORDER BY m.count DESC"""

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days] + satellites))
    else:
        cursor.execute(stmt + stmt_end, (date, days))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
        x=[item['value'][0:9] for item in data],
        y=[item['count'] for item in data]
    ))
    figure.update_layout(
        title_text = 'Locations',
        yaxis_title = 'Machines'
    )

    totalStr = 'Total Locations: {}'.format(len(data))

    return figure, totalStr

@app.callback(Output('machines-graph-cpumodel', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_cpumodel(days, date, satellites):
    return make_graph(days, date, satellites, 'cpumodel', 'CPU<br><sub>Click Bar to view Details</sub>', typ='bar')

@app.callback(Output('machines-graph-realcores', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_realcores(days, date, satellites):
    return make_graph(days, date, satellites, 'realcores', 'Cores', unit=' Cores')

@app.callback(Output('machines-graph-ram', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_ram(days, date, satellites):
    return make_graph(days, date, satellites, 'ram', 'RAM', unit=' GB')

@app.callback(Output('machines-graph-systemmodel', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_systemmodel(days, date, satellites):
    return make_graph(days, date, satellites, 'systemmodel', 'System Model<br><sub>Click Bar to view Details</sub>', typ='bar')


#def make_graph_basesystem(days, date, satellites):
#   return make_graph(days, date, satellites, 'basesystem', 'BASE SYSTEMS<br><sub>Click Bar to view Details</sub>', typ='bar')

@app.callback(Output('machines-graph-kvmstate', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_kvmstate(days, date, satellites):
    return make_graph(days, date, satellites, 'kvmstate', 'KVM State')

@app.callback([Output('machines-graph-systemmodel-locations-container', 'style'),
               Output('machines-graph-systemmodel-locations', 'data'),
               Output('machines-graph-systemmodel-locations-head', 'children')],
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-graph-systemmodel', 'clickData')])
def make_graph_systemmodel_locations(days, date, clickData):
    return make_graph_locations(days, date, clickData, 'systemmodel')

@app.callback([Output('machines-graph-cpumodel-locations-container', 'style'),
               Output('machines-graph-cpumodel-locations', 'data'),
               Output('machines-graph-cpumodel-locations-head', 'children')],
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-graph-cpumodel', 'clickData')])
def make_graph_cpumodel_locations(days, date, clickData):
    return make_graph_locations(days, date, clickData, 'cpumodel')

def make_graph_locations(days, date, clickData, prop):
    if clickData == None:
        return dict(display='none'), [], ''

    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT r.ip, m.count
            FROM reports r
            JOIN machine m ON r.id = m.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
            AND m.days = %s
            AND m.property = %s
            AND m.value = %s
            ORDER BY m.count DESC
        """
    item = clickData['points'][0]['y']

    cursor.execute(stmt, (date, days, prop, item))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    return dict(display='block'), data, item


def make_graph(days, date, satellites, prop, title, unit='', typ='pie'):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT m.value, SUM(m.count) AS count
            FROM reports r
            JOIN machine m ON r.id = m.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
            AND m.days = %s AND m.property = %s
        """
    stmt_end = """ GROUP BY m.value"""

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days, prop] + satellites))
    else:
        cursor.execute(stmt + stmt_end, (date, days, prop))

    data = cursor.fetchall()
    data = natsorted(data, key = lambda x: x['value' if typ=='pie' else 'count'])

    db.closeConnection(dbcon)

    figure = go.Figure()
    if typ == 'pie':
        figure.add_trace(go.Pie(
            labels=[item['value'] + unit for item in data],
            values=[item['count'] for item in data],
            hole=0.3,
            sort=False,
            textinfo='label',
            direction='clockwise'
        ))
    elif typ == 'bar':
        figure.add_trace(go.Bar(
            y=[item['value'] + unit for item in data],
            x=[item['count'] for item in data],
            orientation='h'
        ))
    figure.update_layout(
        title_text = title,
        height = 450 if typ == 'pie' else max(20 * len(data) + 200,300),
        yaxis_automargin=True
    )
    return figure

@app.callback(Output('machines-graph-basesystem', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_basesystem(days, date, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT b.system, SUM(b.count) AS count
            FROM reports r
            JOIN basesystem b ON r.id = b.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
            AND b.days = %s
        """
    stmt_end = """ GROUP BY b.system"""
    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days] + satellites))
    else:
        print(stmt)
        cursor.execute(stmt + stmt_end, (date, days))

    data = cursor.fetchall()
    data = natsorted(data, key = lambda x: x['count'])
    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
            y=[item['system'] for item in data],
            x=[item['count'] for item in data],
            orientation='h'
    ))
    figure.update_layout(
        title_text = "BASE SYSTEMS",
        height = max(20 * len(data) + 200,300),
        yaxis_automargin=True
    )
    return figure

def get_newest_date():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""SELECT date FROM reports ORDER BY date DESC LIMIT 1""")

    data = cursor.fetchall()

    db.closeConnection(dbcon)
    return data[0]['date']

def get_oldest_date():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""SELECT date FROM reports ORDER BY date ASC LIMIT 1""")

    data = cursor.fetchall()

    db.closeConnection(dbcon)
    return data[0]['date']

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