#
#
#  	Project     	: 	opswatcher
#
#	George
#	[email protected]
#
#
#

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, Event



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/[email protected]:1521/orcl"
    eft_connectString = "eftwatch/[email protected]: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(),
    dcc.Link('Go to EFT Dashboard', href='/page-eft'),
    html.Br(),
    dcc.Link('Go to EDO Dashboard', href='/page-edo'),
    html.Br(),
    dcc.Link('Go to CCC Dashboard', href='/page-ccc'),
    html.Br(),

])


page_eft_layout =([

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

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

        dcc.Interval(
            id='interval-eft_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.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_edo_layout = html.Div([

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

    # 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-edo-dropdown',
        options=[{'label': i, 'value': i} for i in ['LA', 'NYC', 'MTL']],
        value='LA'
    ),

    html.Br(),
    dcc.Link('Go back to home', href='/'),

])


page_ccc_layout = html.Div([

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

    # 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-ccc-radios',
        options=[{'label': i, 'value': i} for i in ['Orange', 'Blue', 'Red']],
        value='Orange'
    ),

    html.Br(),
    dcc.Link('Go back to home', href='/')
])


@app.callback(dash.dependencies.Output('page-edo-content', 'children'),
              [dash.dependencies.Input('page-edo-dropdown', 'value')])
def page_edo_dropdown(value):
    return 'You have selected "{}"'.format(value)


@app.callback(dash.dependencies.Output('page-ccc-content', 'children'),
              [dash.dependencies.Input('page-ccc-radios', 'value')])
def page_ccc_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-eft':
        return page_eft_layout

    elif pathname == '/page-edo':
        return page_edo_layout

    elif pathname == '/page-ccc':
        return page_ccc_layout

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


@app.callback(Output('page-eft-content', 'children')
              , events=[Event('interval-eft_component', 'n_intervals')])
def update_eft_page(n):
    return page_eft_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)