Index menu link not going to page & Page Not not auto reloading

Hi guys,

Got 2 problems…

#1 from the index page I can get to page-2_layout (line 593) and page-3-layout (line 630), but page-1-layout only changes the url as per address bar but nothing happens on screen.

#2 original problem, when this page (page-1_layout, line 424) was stand alone without the menu… it did not want to auto refresh based on the dcc.interval component (lines 433…). Need it to refresh every RefreshRate (line 335)

#3 The table id=‘ID_Validate’, line 580 does not want to show the columns in the order i selected the column as in the sql… ?

Please help, what am I missing.

G

Project : opswatcher

George

georgelza@gmail.com

import dash
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd
import cx_Oracle
import dash_table_experiments as dt
import datetime
import time
import base64
from flask import send_from_directory
import os
from dash.dependencies import Input, Output

DEBUG=False

these are Global variables, values from =>: OPS_WATCHER_PARAMETERS, populated in eft_refresettings()

delivery_date = ‘’
next_output_date = ‘’
timeC = ‘’

try :

########## EFT Database Connection and Cursor
#eft_connectString = "eftwatch/eftwatch@192.168.0.31:1521/orcl"
eft_connectString = "eftwatch/eftwatch@192.168.56.3:1521/orcl"
if DEBUG == True: print "EFT ConnectString", eft_connectString

eft_connection = cx_Oracle.connect(eft_connectString)
if DEBUG == True: print "Connected to EFT DB"

# Query all rows
eft_cursor = eft_connection.cursor()
if DEBUG == True: print "EFT Query Cursor"

except Exception, e:
if DEBUG == True: print “Exception :”, e

end try

########## EFT Queries
if DEBUG == True: print “Define Queries”

Lets get our global variables/values

This is where we’re going to get the Warning and Alert values out of the ops_watcher_parameters table and then populate

the global variables with

statement_globalvars = “select eftdelivery_elapse_warning, eftdelivery_elapse_alert, eftload_elapse_warning, eftload_elapse_alert, eftvalidate_elapse_warning, eftvalidate_elapse_alert, textrecordsfetch, refreshtime from ops_watcher_parameters”
if DEBUG == True: print “statement_globalvars :”, statement_globalvars

EFT Delivery - Prepare - ( Get todays processing date from ops_system_parameters.process_active_ind = ‘Y’ )

statement_date = “Select to_char(process_date, ‘DD/MON/YYYY’) from ops_system_parameters where process_active_ind = ‘Y’”
if DEBUG == True: print “statement_date :”, statement_date

EFT Delivery - ( xmit_ind = ‘Y’ and to_char(created_Date,‘DD/MON/YYYY’) = :delivery_date and trim(queue_file_name) in (‘INQUE’, ‘ONLREC’, ‘ONLUNP’) )

statement_delivery = “select filename, to_char(created_date, ‘YYYY/MM/DD HH24:MM:SS’) CREATED_DATE, case when((extract(DAY from DIFF)) * 24 * 60 * 60 + (extract(HOUR from DIFF) ) *60 * 60 + (extract(MINUTE from DIFF))*60 + extract(SECOND from DIFF)) > :eftdelivery_elapse_alert then ‘ALERT’ when((extract(DAY from DIFF)) * 24 * 60 * 60 + (extract(HOUR from DIFF) ) *60 * 60 + (extract(MINUTE from DIFF))*60 + extract(SECOND from DIFF)) > :eftdelivery_elapse_warning then ‘WARNING’ else ‘’ end as STATUS from (select filename, created_date, (systimestamp - created_date) DIFF from del_delivery_files where filename is not null and xmit_ind = ‘Y’ and to_char(created_Date,‘DD/MON/YYYY’) = :delivery_date and trim(queue_file_name) in (‘INQUE’, ‘ONLREC’, ‘ONLUNP’) order by created_date asc)”
if DEBUG == True: print “statement_delivery :”, delivery_date, " ", statement_delivery

EFT Loading - ( process_status = ‘2’ )

statement_load = “Select workunit_ref_number, to_char(load_start_time, ‘YYYY/MM/DD HH24:MM:SS’), case when((extract(DAY from DIFF)) * 24 * 60 * 60 + (extract(HOUR from DIFF) ) *60 * 60 + (extract(MINUTE from DIFF))*60 + extract(SECOND from DIFF)) > :eftload_elapse_alert then ‘ALERT’ when((extract(DAY from DIFF)) * 24 * 60 * 60 + (extract(HOUR from DIFF) ) *60 * 60 + (extract(MINUTE from DIFF))*60 + extract(SECOND from DIFF)) > :eftload_elapse_warning then ‘WARNING’ else ‘’ end as STATUS from (select workunit_ref_number, load_start_time, (systimestamp - load_start_time) DIFF from ops_watcher_details where process_status = ‘2’ order by load_start_time desc)”
if DEBUG == True: print “statement_load :”, statement_load

EFT Validating - ( validate_start_date is not null and validate_end_date is null )

statement_validate = “select workunit_ref_number, process_status, to_char(load_end_time, ‘YYYY/MM/DD HH24:MM:SS’) loadend, to_char(validate_start_time, ‘YYYY/MM/DD HH24:MM:SS’) validatestart, record_count, case when((extract(DAY from DIFF)) * 24 * 60 * 60 + (extract(HOUR from DIFF) ) *60 * 60 + (extract(MINUTE from DIFF))*60 + extract(SECOND from DIFF)) > :eftvalidate_elapse_alert then ‘ALERT’ when((extract(DAY from DIFF)) * 24 * 60 * 60 + (extract(HOUR from DIFF) ) *60 * 60 + (extract(MINUTE from DIFF))*60 + extract(SECOND from DIFF)) > :eftvalidate_elapse_warning then ‘WARNING’ else ‘’ end as status from (select workunit_ref_number, process_status, load_end_time, validate_start_time, record_count, (systimestamp - nvl(validate_start_time, load_end_time )) DIFF from ops_watcher_details where validate_start_time is not null and validate_end_time is null order by validate_start_time asc)”
if DEBUG == True: print “statement_validate :”, statement_validate

EFT Reporting

statement_efvetrpt = “select ‘efvetrpt’, count() from ops_vet_report_parameters where vet_report_produced_ind = ‘N’"
statement_efpdistrib = "select ‘efpdistrib’, count(
) from ops_z1_z9_input_outputs where internal_indicator = ‘N’ and distributed_indicator = ‘N’”
statement_efpaxd020 = “select ‘efpaxd020’, count(*) from ops_z1_z9_input_outputs where internal_indicator = ‘N’ and distributed_indicator = ‘Y’ and axd_created_indicator = ‘N’”
if DEBUG == True:
print “statement_efvetrpt :”, statement_efvetrpt
print “statement_efpdistrib :”, statement_efpdistrib
print “statement_efpaxd020 :”, statement_efpaxd020

# EFT Extract - Prepare

statement_nextoutputdate = “Select to_char(process_date, ‘DD/MON/YY’) from ops_system_parameters where process_active_ind = ‘Y’”
if DEBUG == True: print “statement_nextoutputdate :”, statement_nextoutputdate

EFT Extract

statement_efconman = “select /+ INDEX_ASC(OPS_USER_HDRS OUH_PK)/ ‘efconman’, count(1) FROM OPS_USER_HDRS WHERE PROCESS_STATUS = ‘7’ AND OPS_STATUS = ‘A’ AND UNPAIDS_INCLUDED_INDICATOR = ‘Y’”
statement_efextract = “select ‘efextract’, count(*) from ops_user_hdrs where process_status = ‘A’ and ops_status != ‘F’ and next_output_date = :next_output_date”
statement_tobeval = “select ‘ToBeValidated’,count(1) from ops_installation_hdrs where process_status = ‘3’”
if DEBUG == True:
print “statement_efconman :”, statement_efconman
print “statement_efextract :”, statement_efextract
print “statement_tobeval :”, statement_tobeval

Number Files to process in each of the program stream

statement_delivery_cnt = "select count(1) from del_delivery_files where filename is not null and xmit_ind = ‘Y’ and to_char(created_Date,‘DD/MON/YYYY’) = :delivery_date and trim(queue_file_name) in (‘INQUE’, ‘ONLREC’, ‘ONLUNP’) "
statement_load_cnt = “select count(1) from ops_watcher_details where process_status = ‘2’”
statement_validate_cnt = “select count(1) from ops_watcher_details where validate_start_time is not null and validate_end_time is null”
if DEBUG == True: print “statement_delivery_cnt :”, delivery_date, " ", statement_delivery_cnt
if DEBUG == True: print “statement_load_cnt :”, statement_load_cnt
if DEBUG == True: print “statement_validate_cnt :”, statement_validate_cnt

Refresh our gloval settings

def eft_refreshsettings(eft_cursor):

# get values from ops_watcher_parameters
try:
    eft_cursor.execute(statement_globalvars)
    configurationsettings = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

# Next Delivery date from ops_system_parameters
try:
    eft_cursor.execute(statement_date)
    res = eft_cursor.fetchone()
    delivery_date = res[0]

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

# Next Delivery date from ops_system_parameters, same date as above, just formated different
try:
    eft_cursor.execute(statement_nextoutputdate)
    res = eft_cursor.fetchone()
    next_output_date = res[0]

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return configurationsettings, delivery_date, next_output_date

#end refreshsettings

Get file delivery data from ops_delivery_files

def eft_delivery(eft_cursor, delivery_date, configurationsettings):

try:

    eft_cursor.execute(statement_delivery_cnt, {'delivery_date': delivery_date})
    results = eft_cursor.fetchone()
    results_delivery_cnt = results[0]

    eft_cursor.execute(statement_delivery, {'delivery_date': delivery_date, 'eftdelivery_elapse_alert': configurationsettings[0], 'eftdelivery_elapse_warning': configurationsettings[1]})
    results_delivery = eft_cursor.fetchall()

    # Build a Pandas dataframe with desired heading tags.
    headers = ['FileName', 'Created Date', 'Status']
    df = pd.DataFrame(results_delivery, columns=headers)

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_delivery_cnt, df

#end eft_delivery

Get eftload data from ops_watcher_details

def eft_load(eft_cursor, configurationsettings):
try:

    eft_cursor.execute(statement_load_cnt )
    results = eft_cursor.fetchone()
    results_load_cnt = results[0]

    eft_cursor.execute(statement_load, {'eftload_elapse_warning':configurationsettings[2] ,'eftload_elapse_alert':configurationsettings[3]})
    results_load = eft_cursor.fetchall()

    # Build a Pandas dataframe with desired heading tags.
    headers = ['WorkUnit', 'Start Time', 'Status']
    df = pd.DataFrame(results_load, columns=headers)

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_load_cnt, df

#end eft_load

Get eftvalidate data from ops_watcher_details

def eft_validate(eft_cursor, configurationsettings):
try:

    eft_cursor.execute(statement_validate_cnt)
    results = eft_cursor.fetchone()
    results_validate_cnt = results[0]

    eft_cursor.execute(statement_validate, {'eftvalidate_elapse_warning':configurationsettings[4] ,'eftvalidate_elapse_alert':configurationsettings[5] })
    results_validate = eft_cursor.fetchall()

    # Build a Pandas dataframe with desired heading tags.
    headers = ['WorkUnit', 'PRStat', 'Load End', 'Validate Start', 'Record Cnt', 'Status']
    df = pd.DataFrame(results_validate, columns=headers)

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_validate_cnt, df

#end eft_validate

Reports

Get various ticker values/data from other tables

def eft_efvetrpt(eft_cursor):
try:

    eft_cursor.execute(statement_efvetrpt)
    results_efvetrpt = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_efvetrpt

def eft_efpdistrib(eft_cursor):
try:

    eft_cursor.execute(statement_efpdistrib)
    results_efpdistrib = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_efpdistrib

#end eft_efpdistrib

def eft_efpaxd020(eft_cursor):
try:

    eft_cursor.execute(statement_efpaxd020)
    results_efpaxd020 = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_efpaxd020

#end eft_efpaxd020

Extracts

def eft_efconman(eft_cursor):
try:

    eft_cursor.execute(statement_efconman)
    results_efconman = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_efconman

#end eft_efconman

def eft_efextract(eft_cursor, next_output_date):
try:

    eft_cursor.execute(statement_efextract, {'next_output_date': next_output_date})
    results_efextract = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_efextract

#end eft_efextract

def eft_validate_tobeval(eft_cursor):
try:

    eft_cursor.execute(statement_tobeval)
    results_validate_tobeval = eft_cursor.fetchone()

except cx_Oracle.DatabaseError as e:
    error, = e.args
    print 'Oracle Error:', error

return results_validate_tobeval

#end eft_validate_tobeval

Get our configuration data, what date we’re running on, what refresh rate etc.

configurationsettings, delivery_date, next_output_date = eft_refreshsettings(eft_cursor)

General settings from above call

LastRefresh = time.strftime( ‘%Y/%m/%d %H:%M:%S’) # Last Refresh time
RefreshRate = configurationsettings[7] # Every how often are we going to refresh the screen
DeliveryDate = delivery_date # For what date are we delivery
NextOutputDate = next_output_date # For what date are we going to Output

MyHorizontal Bar at the top

efvetrpt

results_efvetrpt = eft_efvetrpt(eft_cursor)

efpdistrib

results_efpdistrib = eft_efpdistrib(eft_cursor)

efpaxd020

results_efpaxd020 = eft_efpaxd020(eft_cursor)

efconman

results_efconman = eft_efconman(eft_cursor)

efextract

results_efextract = eft_efextract(eft_cursor, next_output_date)

validate_tobeval

results_validate_tobeval = eft_validate_tobeval(eft_cursor)

MyVerticals, 3 tables down the bottom.

results_delivery_cnt, df_delivery = eft_delivery(eft_cursor, delivery_date, configurationsettings)
results_load_cnt, df_load = eft_load(eft_cursor, configurationsettings)
results_validate_cnt, df_validate = eft_validate(eft_cursor, configurationsettings)

START It alllllll

app = dash.Dash()

We’re going to serve the stylesheet from local directory, for security purposes.

app.css.config.serve_locally = True
app.scripts.config.serve_locally = True

Lets build/include a top bar with the BSA Logo

image_filename = ‘image/mainlogo.png’ # replace with your own image
mainlogo_image = base64.b64encode(open(image_filename, ‘rb’).read())

app.config.suppress_callback_exceptions = True

app.layout = html.Div([
dcc.Location(id=‘url’, refresh=False),
html.Div(id=‘page-content’)

])

index_page = html.Div([
# Retrieve style sheet from
html.Link(
rel=‘stylesheet’,
href=’/static/bsa-style.css’
),

# Row 0 - HeadingBar
html.Div(
    style={'border': 'solid 0px black'},
    className="row",
    children=[
        html.Div(
            className="twelve columns",
            children=[
                html.Div([html.Img(src='data:image/png;base64,{}'.format(mainlogo_image))],
                         style={'backgroundColor': 'black'}
                         )
            ]
        ),

    ]
),

html.H1('Welcome to OPS Watcher'),

html.Br(),
html.Br(),
dcc.Link('Go to EFT Dashboard', href='/page-1'),
html.Br(),
dcc.Link('Go to EDO Dashboard', href='/page-2'),
html.Br(),
dcc.Link('Go to CCC Dashboard', href='/page-3'),
html.Br(),

])

page_1_layout =([
# Retrieve style sheet from
html.Link(
rel=‘stylesheet’,
href=’/static/bsa-style.css’
),

html.Div(id='page-eft-content'),

dcc.Interval(
    id='interval-component',
    interval=RefreshRate * 1000,  # in milliseconds
    n_intervals=0
),

# Row 0 - HeadingBar
html.Div(
    style={'border': 'solid 0px black'},
    className="row",
    children=[
        html.Div(
            className="twelve columns",
            children=[
                html.Div([html.Img(src='data:image/png;base64,{}'.format(mainlogo_image))],
                         style={'backgroundColor': 'black'}
                         )
            ]
        ),

    ]
),

html.H1('EFT Dashboard'),
html.Br(),
dcc.Link('Go to EDO Dashboard', href='/page-2'),
html.Br(),
dcc.Link('Go to CCC Dashboard', href='/page-3'),
html.Br(),
dcc.Link('Go back to home', href='/'),


# Row 1 - MyHorizontal
html.Div(
    id='ID_Horizontal',
    style={'border': 'solid 1px black'},
    className="row",
    children=[

        html.Div(
            className="twelve columns",
            children=[
                # ConfigurationSettings
                html.Div(
                    id='ID_ConfigurationSettings',
                    style={'border': 'solid 0px black'},
                    className="three columns",
                    children=
                        html.Table(
                            #[ html.Tr( [html.Th(""), html.Th(""), ] ) ] +
                            [
                                html.Tr([html.Td("Last Refresh"),     html.Td(LastRefresh)] ),
                                html.Tr([html.Td("Refresh Rate"),     html.Td(RefreshRate)]),
                                html.Tr([html.Td("Delivery Date"),    html.Td(DeliveryDate)]),
                                html.Tr([html.Td("Next Output Date"), html.Td(NextOutputDate)])
                            ]
                        )
                ),
                # Thresshold (Warning & Alert) Values
                html.Div(
                    id='ID_Thresshold',
                    style={'border': 'solid 0px black'},
                    className="four columns",
                    children=
                        html.Table(
                            [
                                html.Tr([html.Td("Delivery  warning"), html.Td(configurationsettings[0]),html.Td("Validate Warning"),html.Td(configurationsettings[4])]),
                                html.Tr([html.Td("Delivery  alert"), html.Td(configurationsettings[1]), html.Td("Validate Alert"),html.Td(configurationsettings[5])]),
                                html.Tr([html.Td("Load Warning"), html.Td(configurationsettings[2])]),
                                html.Tr([html.Td("Load Alert"), html.Td(configurationsettings[3])])
                            ]
                    )
                ),
                # ReportsAndExtract numbers
                html.Div(
                    id='ID_ReportsAndExtract',
                    style={'border': 'solid 0px black'},
                    className="four columns",
                    children=
                        html.Table(
                            [
                                html.Tr([html.Td(results_efvetrpt[0]),
                                         html.Td(results_efpdistrib[0]) ,
                                         html.Td(results_efpaxd020[0]),
                                         html.Td(results_efconman[0]),
                                         html.Td(results_efextract[0]),
                                         html.Td(results_validate_tobeval[0])
                                ]),
                                html.Tr([html.Td(results_efvetrpt[1]),
                                         html.Td(results_efpdistrib[1]),
                                         html.Td(results_efpaxd020[1]),
                                         html.Td(results_efconman[1]),
                                         html.Td(results_efextract[1]),
                                         html.Td(results_validate_tobeval[1])
                                ]),
                            ]
                        )
                )
            ]
        ),

    ]
),

# Row 2 - MyVerticals
html.Div(
    id='ID_Verticals',
    className="row",
    style={'border': 'solid 0px black'},
    children=[
        # EFT Delivery Table
        html.Div(
            id='ID_Delivery',
            className="four columns",
            children=html.Div([

                html.H4(children='EFT Delivery :'+ str(results_delivery_cnt)),
                dt.DataTable(
                    id='ID_Delivery-table',
                    rows=df_delivery.to_dict('records'),
                    # optional - sets the order of columns
                    columns=sorted(df_delivery.columns),
                    editable=False
                )])
        ),
        # EFT Load Table
        html.Div(
            id='ID_Load',
            className="three columns",
            children=html.Div([

                html.H4(children='EFT Load :'+ str(results_load_cnt)),
                dt.DataTable(
                    id='ID_Load-table',
                    rows=df_load.to_dict('records'),
                    # optional - sets the order of columns
                    columns=sorted(df_load.columns),
                    editable=False
                )])
        ),
        # EFT Validate
        html.Div(
            id='ID_Validate',
            className="five columns",
            children=html.Div([

                html.H4(children='EFT Validate :'+ str(results_validate_cnt)),
                dt.DataTable(
                    id='ID_Validate-table',
                    rows=df_validate.to_dict('records'),
                    # optional - sets the order of columns
                    columns=sorted(df_validate.columns),
                    editable=False,
                    resizable=True
                )])
        )
    ]
)

])

page_2_layout = html.Div([
# Retrieve style sheet from
html.Link(
rel=‘stylesheet’,
href=’/static/bsa-style.css’
),
# Row 0 - HeadingBar
html.Div(
style={‘border’: ‘solid 0px black’},
className=“row”,
children=[
html.Div(
className=“twelve columns”,
children=[
html.Div([html.Img(src=‘data:image/png;base64,{}’.format(mainlogo_image))],
style={‘backgroundColor’: ‘black’}
)
]
),

    ]
),
html.H1('EDO Dashboard'),
dcc.Dropdown(
    id='page-2-dropdown',
    options=[{'label': i, 'value': i} for i in ['LA', 'NYC', 'MTL']],
    value='LA'
),
html.Div(id='page-2-content'),
dcc.Link('Go to EFT Dashboard', href='/page-1'),
html.Br(),
dcc.Link('Go to CCC Dashboard', href='/page-3'),
html.Br(),
dcc.Link('Go back to home', href='/'),

])

page_3_layout = html.Div([
# Retrieve style sheet from
html.Link(
rel=‘stylesheet’,
href=’/static/bsa-style.css’
),

# Row 0 - HeadingBar
html.Div(
    style={'border': 'solid 0px black'},
    className="row",
    children=[
        html.Div(
            className="twelve columns",
            children=[
                html.Div([html.Img(src='data:image/png;base64,{}'.format(mainlogo_image))],
                         style={'backgroundColor': 'black'}
                         )
            ]
        ),

    ]
),

html.H1('CCC Dashboard'),
dcc.RadioItems(
    id='page-3-radios',
    options=[{'label': i, 'value': i} for i in ['Orange', 'Blue', 'Red']],
    value='Orange'
),
html.Div(id='page-3-content'),
html.Br(),
dcc.Link('Go to EFT Dashboard', href='/page-1'),
html.Br(),
dcc.Link('Go to EDO Dashboard', href='/page-2'),
html.Br(),
dcc.Link('Go back to home', href='/')

])

@app.callback(dash.dependencies.Output(‘page-2-content’, ‘children’),
[dash.dependencies.Input(‘page-2-dropdown’, ‘value’)])
def page_2_dropdown(value):
return ‘You have selected “{}”’.format(value)

@app.callback(dash.dependencies.Output(‘page-3-content’, ‘children’),
[dash.dependencies.Input(‘page-3-radios’, ‘value’)])
def page_3_radios(value):
return ‘You have selected “{}”’.format(value)

Update the index

@app.callback(dash.dependencies.Output(‘page-content’, ‘children’),
[dash.dependencies.Input(‘url’, ‘pathname’)])
def display_page(pathname):
if pathname == ‘/page-1’:
return page_1_layout

elif pathname == '/page-2':
    return page_2_layout

elif pathname == '/page-3':
    return page_3_layout

else:
    return index_page
# You could also return a 404 "URL not found" page here

@app.callback(Output(‘page-eft-content’, ‘children’),
[Input(‘interval-component’, ‘n_intervals’)])
def update_metrics(n):
return page_1_layout

@app.server.route(’/static/path:path’)
def static_file(path):
static_folder = os.path.join(os.getcwd(), ‘static’)
return send_from_directory(static_folder, path)

if name == ‘main’:
app.run_server(port=8051, debug=True)