Apr-18-2018, 07:44 PM
I've added some comments... "line <number<=> so where you find #line 128 that means that comment line is on line 128
added #line numbers for 128, before the variable is set, #line 441 where GetEFTDBConnection() is defined and #line 572 where the error originates from.
G
Error stack
* Running on http://0.0.0.0:5003/ (Press CTRL+C to quit)
[2018-04-18 21:39:21,207] ERROR in app: Exception on /eftdashboard [GET]
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
response = self.full_dispatch_request()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
rv = self.dispatch_request()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "opswatchflask.py", line 573, in eftdashboard
if gotEFTConnection == False:
UnboundLocalError: local variable 'gotEFTConnection' referenced before assignment
127.0.0.1 - - [18/Apr/2018 21:39:21] "GET /eftdashboard HTTP/1.1" 500 -
added #line numbers for 128, before the variable is set, #line 441 where GetEFTDBConnection() is defined and #line 572 where the error originates from.
G
Error stack
* Running on http://0.0.0.0:5003/ (Press CTRL+C to quit)
[2018-04-18 21:39:21,207] ERROR in app: Exception on /eftdashboard [GET]
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
response = self.full_dispatch_request()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
rv = self.dispatch_request()
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "opswatchflask.py", line 573, in eftdashboard
if gotEFTConnection == False:
UnboundLocalError: local variable 'gotEFTConnection' referenced before assignment
127.0.0.1 - - [18/Apr/2018 21:39:21] "GET /eftdashboard HTTP/1.1" 500 -
#
#
#
from flask import Flask, request, render_template
import cx_Oracle
import os
import time
import sys
import datetime
EXIT_FAILURE=1 # Exit variable for sys.exit
# Set these to defaults, we'll get the actual values from OS environment in a second, (line 131 onwards)
eft_connectString = '' # Empty variable to be used for connection string
FLASK_DEBUG = False # FLASK Debug mode by default disabled, it can either be enabled by changing this or by setting DEBUG > 0
DEBUGLEVEL = 1 # My Debug level/s -> To Screen/terminal
# > 0 print basic connection and cursor details out
# >= 2 print sql statements to be executed
# >= 3 print variable values from queries to screen,
# ... not all data though as the data sets can be large, so only single row results sets
PORT=5000 # Default port - we change this below depending on environment argument passed in.
# these are Global variables, values from =>: OPS_WATCHER_PARAMETERS, populated in eft_refresettings()
delivery_date = ''
next_output_date = ''
timeC = ''
# line 128
gotEFTConnection = False # PLANNED - We start with no connection for all the sub pages, as the page is called the first time then
# we connect to the relevant target database
########## EFT Queries
# 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, refreshtime from ops_watcher_parameters"
# EFT Delivery - Prepare - ( Get todays processing date from ops_system_parameters.process_active_ind = 'Y' )
statement_date = "Select max(to_char(process_date, 'DD/MON/YYYY')) from ops_system_parameters"
# EFT Delivery - ( xmit_ind = 'Y' 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 trim(queue_file_name) in ('INQUE', 'ONLREC', 'ONLUNP') order by created_date asc)"
# 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)"
# 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'), to_char(validate_start_time, 'YYYY/MM/DD HH24:MM:SS'), 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)"
# EFT Aggregates
statement_aggregates = "select workunit_ref_number as wrkunit, dataset_ref_number as dtset, installation_code as icode from Ops_user_hdrs where user_limits_exceeded_indicator in ('Y','B','C','D') order by installation_code asc"
# 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' and TRANSMIT_INDICATOR = 'Y'"
# EFT Extract - Prepare
statement_nextoutputdate = "Select max(to_char(process_date, 'DD/MON/YY')) from ops_system_parameters"
# 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_efval001 = "select 'efval001',count(1) from ops_installation_hdrs where process_status = '3'"
# 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 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"
statement_aggregates_cnt = "select count(1) from Ops_user_hdrs where user_limits_exceeded_indicator in ('Y','B','C','D')"
def correctusage():
print ''
print 'Welcome to OPSWatcher'
print ''
print ' OPSWatcher needs to be run with a input parameter specifying the desired environment to dashboard:'
print ''
print ' ./opswacthflask.py <environment>'
print ''
print ' Valid environment inputs are: prd uat iat dev'
print ''
print ''
sys.exit(EXIT_FAILURE)
# end correctusage()
def getAppEnvVariables(opswatchenv):
if opswatchenv == 'prd':
eft_connectString = os.getenv('DBCON_EFTP') # Whats the database connection string
FLASK_DEBUG = os.getenv('OPSW_EFTP_FLASK_DEBUG') # Flask Debug True/False
DEBUGLEVEL = os.getenv('OPSW_EFTP_DEBUGLEVEL') # Screen based Debug level
PORT = os.getenv('OPSW_EFTP_PORT') # Flask port to listen on
opswatchenv = 'Production'
elif opswatchenv =='uat':
eft_connectString = os.getenv('DBCON_EFTU')
FLASK_DEBUG = os.getenv('OPSW_EFTU_FLASK_DEBUG')
DEBUGLEVEL = os.getenv('OPSW_EFTP_DEBUGLEVEL')
PORT = os.getenv('OPSW_EFTU_PORT')
opswatchenv = 'UAT'
elif opswatchenv =='iat':
eft_connectString = os.getenv('DBCON_EFTI')
FLASK_DEBUG = os.getenv('OPSW_EFTI_FLASK_DEBUG')
DEBUGLEVEL = os.getenv('OPSW_EFTP_DEBUGLEVEL')
PORT = os.getenv('OPSW_EFTI_PORT')
opswatchenv = 'IAT'
elif opswatchenv =='dev':
eft_connectString = os.getenv('DBCON_EFTD')
FLASK_DEBUG = os.getenv('OPSW_EFTD_FLASK_DEBUG')
DEBUGLEVEL = os.getenv('OPSW_EFTP_DEBUGLEVEL')
PORT = os.getenv('OPSW_EFTD_PORT')
opswatchenv = 'Development'
elif opswatchenv =='orcl':
eft_connectString = os.getenv('DBCON_ORCL')
FLASK_DEBUG = os.getenv('OPSW_ORCL_FLASK_DEBUG')
DEBUGLEVEL = os.getenv('OPSW_ORCL_DEBUGLEVEL')
PORT = os.getenv('OPSW_ORCL_PORT')
opswatchenv = 'George : orcl'
else:
print correctusage()
if FLASK_DEBUG == 'True': FLASK_DEBUG = True
else: FLASK_DEBUG = False
DEBUGLEVEL = int(DEBUGLEVEL)
PORT = int(PORT)
# end opswatchenv == 'prd'
return opswatchenv, eft_connectString, FLASK_DEBUG, DEBUGLEVEL, PORT
# end getAppEnvVariables():
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
def eft_delivery(eft_cursor, delivery_date, configurationsettings):
try:
eft_cursor.execute(statement_delivery_cnt)
results = eft_cursor.fetchone()
results_delivery_cnt = results[0]
eft_cursor.execute(statement_delivery, { 'eftdelivery_elapse_alert': configurationsettings[0], 'eftdelivery_elapse_warning': configurationsettings[1]})
results_delivery = eft_cursor.fetchall()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_delivery_cnt, results_delivery
#end eft_delivery
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()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_load_cnt, results_load
#end eft_load
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()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_validate_cnt, results_validate
#end eft_validate
def eft_aggregates(eft_cursor):
try:
eft_cursor.execute(statement_aggregates_cnt)
results = eft_cursor.fetchone()
results_aggregates_cnt = results[0]
eft_cursor.execute(statement_aggregates)
results_aggregates = eft_cursor.fetchall()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_aggregates_cnt, results_aggregates
#end eft_finance
def eft_efvetrpt(eft_cursor):
try:
eft_cursor.execute(statement_efvetrpt)
results_efvetrpt = eft_cursor.fetchall()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_efvetrpt
#end eft_efvetrpt
def eft_efpdistrib(eft_cursor):
try:
eft_cursor.execute(statement_efpdistrib)
results_efpdistrib = eft_cursor.fetchall()
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.fetchall()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_efpaxd020
#end eft_efpaxd020
def eft_efconman(eft_cursor):
try:
eft_cursor.execute(statement_efconman)
results_efconman = eft_cursor.fetchall()
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.fetchall()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_efextract
#end eft_efextract
def eft_validate_efval001(eft_cursor):
try:
eft_cursor.execute(statement_efval001)
results_validate_efval001 = eft_cursor.fetchall()
except cx_Oracle.DatabaseError as e:
error, = e.args
print 'Oracle Error:', error
return results_validate_efval001
#end eft_validate_tobeval
#line 441
def GetEFTDBConnection(eft_connectString, opswatchenv):
# Get the connection to the database
try:
########## EFT Database Connection and Cursor
eft_connection = cx_Oracle.connect(eft_connectString)
# Query all rows
eft_cursor = eft_connection.cursor()
if DEBUGLEVEL > 0:
print "** ", opswatchenv, " - EFT ConnectString :", eft_connectString
print "** ", opswatchenv, " - Connected to EFT DB :", eft_connection
print "** ", opswatchenv, " - EFT Query Cursor :", eft_cursor
# end if DEBUG > 0:
return eft_cursor
except Exception, e:
print "** ", opswatchenv, "Exception :", e
print ''
# end DEBUG > 0:
# end try
# end GetEFTDBConnection():
###################################################################################################
# OK Lets START
###################################################################################################
if len(sys.argv) <> 2:
correctusage()
# end if len(sys.argv) <> 2
opswatchenv = sys.argv[1]
opswatchenv, eft_connectString, FLASK_DEBUG, DEBUG, PORT = getAppEnvVariables(opswatchenv)
if DEBUGLEVEL > 0:
os.system('clear')
print '*******************************************'
print '* *'
print '* Welcome to OPSWatcher: *'
print '* *'
print '* ', time.strftime('%Y/%m/%d %H:%M:%S'),' *'
print '* *'
print '* by [email protected] *'
print '*******************************************'
print ''
print "** " , opswatchenv , " - FLASK_DEBUG : " , FLASK_DEBUG
print "** " , opswatchenv , " - OPSW_*_DEBUGLEVEL : " , DEBUGLEVEL
print "** " , opswatchenv , " - PORT : " , PORT
print ""
# end if DEBUGLEVEL > 0
if DEBUGLEVEL >= 2:
print ""
print "******************************"
print "* Defined Queries *"
print "******************************"
print ""
print "** " , opswatchenv , " - statement_globalvars :", statement_globalvars
print ''
print "** " , opswatchenv , " - statement_date :", statement_date
print ''
print "** " , opswatchenv , " - statement_delivery : +delivery_date ", statement_delivery
print ''
print "** " , opswatchenv , " - statement_load :", statement_load
print ''
print "** " , opswatchenv , " - statement_validate :", statement_validate
print ''
print "** " , opswatchenv , " - statement_aggregates :", statement_aggregates
print ''
print "** " , opswatchenv , " - statement_efvetrpt :", statement_efvetrpt
print ''
print "** " , opswatchenv , " - statement_efpdistrib :", statement_efpdistrib
print ''
print "** " , opswatchenv , " - statement_efpaxd020 :", statement_efpaxd020
print ''
print "** " , opswatchenv , " - statement_nextoutputdate :", statement_nextoutputdate
print ''
print "** " , opswatchenv , " - statement_efconman :", statement_efconman
print ''
print "** " , opswatchenv , " - statement_efextract :", statement_efextract
print ''
print "** " , opswatchenv , " - statement_efval001 :", statement_efval001
print ''
print "** " , opswatchenv , " - statement_delivery_cnt : +delivery_date ", statement_delivery_cnt
print ''
print "** " , opswatchenv , " - statement_load_cnt :", statement_load_cnt
print ''
print "** " , opswatchenv , " - statement_validate_cnt :", statement_validate_cnt
print ''
print "** " , opswatchenv , " - statement_aggregates_cnt :", statement_aggregates_cnt
print ''
# end DEBUGLEVEL >= 2:
# Start the FLASK Application
app = Flask(__name__)
# Little waste of time for now... this will always fire at this point, idea is to later move this so that it only fires
# once for the first time the EFT Dashboard is called.
#if gotEFTConnection == False:
# # eft_cursor = GetEFTDBConnection(eft_connectString, opswatchenv)
# gotEFTConnection = True
# end gotEFTConnection == False:
@app.route("/")
def main():
return render_template("index.html")
#end main
# line 567
@app.route("/eftdashboard")
def eftdashboard( ):
# One Single Dashboard with all the EFT information
if gotEFTConnection == False:
eft_cursor = GetEFTDBConnection(eft_connectString, opswatchenv)
gotEFTConnection = True
# end if gotEFTConnection:
configurationsettings, delivery_date, next_output_date = eft_refreshsettings(eft_cursor)
timeC = time.strftime('%Y/%m/%d %H:%M:%S')
# Refresh the settings to use before we continue, just in case somethign has changed in the parameter tables.
configurationsettings, delivery_date, next_output_date = eft_refreshsettings(eft_cursor)
# Delivery
results_delivery_cnt, results_delivery = eft_delivery(eft_cursor, delivery_date, configurationsettings)
# Load
results_load_cnt, results_load = eft_load(eft_cursor, configurationsettings)
# Validate
results_validate_cnt, results_validate = eft_validate(eft_cursor, configurationsettings)
# Finance
results_aggregates_cnt, results_aggregates = eft_aggregates(eft_cursor)
# 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_efval001 = eft_validate_efval001(eft_cursor)
return render_template( "eftdashboard.html",
dt=timeC,
eftenvironment=opswatchenv,
configsettings=configurationsettings,
delivery_date=delivery_date,
next_output_date=next_output_date,
delivery_result=results_delivery,
load_result=results_load,
validate_result=results_validate,
aggregates_result=results_aggregates,
efvetrpt_result=results_efvetrpt,
efpdistrib_result=results_efpdistrib,
efpaxd020_result=results_efpaxd020,
efconman_result=results_efconman,
efextract_result=results_efextract,
validate_result_efval001=results_validate_efval001,
delivery_result_cnt=results_delivery_cnt,
load_result_cnt=results_load_cnt,
validate_result_cnt=results_validate_cnt,
aggregates_result_cnt=results_aggregates_cnt)
#end eftdashboard
if __name__ == "__main__":
app.run(host='0.0.0.0', debug=FLASK_DEBUG, port=PORT)