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)