#!/u01/app/python/current_version/bin/python3


from libs.ogg_libs import *

import os
import cx_Oracle
import ldap
import subprocess
import cherrypy
from jinja2 import Environment, FileSystemLoader
from cherrypy.lib import auth_basic
import requests
import json
import pytz
import dateutil.parser
import datetime
from email.utils import COMMASPACE, formatdate
from requests.packages.urllib3.exceptions import InsecureRequestWarning
from libs.ogg_libs import *
import time
from email.message import EmailMessage
import smtplib


DEBUG_MODE = False
TEMPLATE_DIR = OGG_SERVICE_DIR + "/www"
LDAP = "frru3dc4635.france.intra.corp"
OEMDB_EZ_STRING = "FRPIVSQL2418/OEMPRD"
ORA_USERNAME = "system"
ORA_PASSWORD = "plusdacces"
CONTROLMDB_EZ_STRING = "dmp01-scan/EMPRDEXA"
DRIVE_DATABASES = {"FR":"dmp01-scan/DRF1PRDEXA", "IT":"dmp01-scan/DRI1PRDEXA", "UK":"dmp01-scan/DRU1PRDEXA", "SP":"dmp01-scan/DRS1PRDEXA"}
DRIVE_DATABASES_DESCRIPTION = {"FR":"Drive FRANCE database", "IT":"Drive ITALY database", "UK":"Drive UK database", "SP":"Drive SPAIN database"}
ACTIVE_SESS_HIST_HOURS = 4
LOCK_HIST_HOURS = 4
DATGUARD_ACCEPTED_LAG_SECONDS = 0
DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES = 15


env=Environment(loader=FileSystemLoader(TEMPLATE_DIR), trim_blocks=True)
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
get_hostname()
logger = start_logging(OGG_SERVICE_DIR+"/log/www_actions.log")

def ad_validate(username, domain, password):
    # Delegate password check to Active Directory
    RC = False
    l = ldap.initialize("ldap://" + LDAP)
    try:
        l.protocol_version = ldap.VERSION3
        l.set_option(ldap.OPT_REFERRALS, 0)
        l.simple_bind_s(username + domain, password)
        RC = True
        logger.info("User " + username + " logged in")
    except ldap.LDAPError as e:
        # Reject if wrong password
        RC = False
    # Accept otherwise
    return RC

def load_user_dict():
    with open(OGG_SERVICE_DIR+"/etc/www_users.conf", 'r') as f:
        user_dict = json.load(f)
    return user_dict


def get_www_visible_oggsync():
    all_sync_array = []
    directory = OGG_SERVICE_DIR + "/sync.d/"
    for name in sorted(os.listdir(directory)):
        if os.path.isdir(os.path.join(directory, name)):
            try:
                (extract, replicat) = name.split("_")

                with open(OGG_SERVICE_DIR+"/etc/status.info") as f:
                    arr_status = json.load(f)

                for status in arr_status:
                    if arr_status[extract] == None:
                        arr_status[extract] = "UNKNOW"
                    if arr_status[replicat] == None:
                        arr_status[replicat] = "UNKNOW"

                ogg_sync = OGG_Sync(extract, replicat)
                ogg_sync.create_temporary_sync_directory()
                ogg_sync.fast_parse_prm_headers()
                env_info_filename = os.path.join(directory, name, "env.info")
                env_info_contents = open(env_info_filename, "r", encoding = "utf-8", errors = "replace").readlines()
                specific_params = {}
                for line in env_info_contents:
                            line = line.rstrip().lstrip()
                            if line !="":
                                param, value = line.split("=", 1)
                                param = param.rstrip().lstrip()
                                value = value.rstrip().lstrip()
                                specific_params[param] = value

                if specific_params["www_visible"] == "yes":
                    sync = (extract, replicat, specific_params["type_environement"], specific_params["www_title"], ogg_sync.database_source, ogg_sync.database_target, arr_status[extract], arr_status[replicat])
                    all_sync_array.append(sync)

            except:
                pass
    return all_sync_array

def get_target_instances(target_database):
    db_name = target_database[:-3]
    db_name_like_pattern = "PRD%" + db_name + "%"
    try:
        db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
        cursor = db.cursor()
        sql = "select TARGET_NAME from sysman.MGMT$TARGET where target_name like '" + db_name_like_pattern + "'"
        cursor.execute(sql)
        target_instances = []
        for row in cursor:
            target_instances.append(row[0])

        cursor.close()
        db.close()
    except cx_Oracle.DatabaseError as err:
        raise
    return target_instances


# OUR CONTROLLER
class Root(object):
    user_dict = load_user_dict()
    menu = {}
        # CherryPy published function

    @cherrypy.expose()
    def login_page(self):
        templateVars = {}
        template = env.get_template('login_page.tpl')
        templateVars = {
            "myvar1" : "myvar1"
        }
        output = template.render(templateVars)
        return output

    @cherrypy.expose()
    def select_sync(self):
        self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])

        www_visible_oggsync_array = get_www_visible_oggsync()

        status_time = time.ctime(os.path.getmtime(OGG_SERVICE_DIR+"/etc/status.info"))
        templateVars = {}
        template = env.get_template('select_sync.tpl')
        templateVars = {
            "www_visible_oggsync_array" : www_visible_oggsync_array,
            "status_time" : status_time
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    @cherrypy.expose()
    def oemtargetlist(self, *args, **kwargs):
        self.restrict_url_to_logged()
        # GET values
        try:
            hostlike = kwargs["hostlike"]
        except KeyError as e:
            hostlike=""

        try:
            instancelike = kwargs["instancelike"]
        except KeyError as e:
            instancelike=""

        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()
            sql = """
                WITH data_tab AS (
                select T.target_name, P1.property_value as DBAPPLICATION
                from sysman.mgmt$target T,
                     sysman.mgmt$target_properties P1
                where P1.TARGET_GUID(+) = T.TARGET_GUID
                and P1.property_name(+) = 'orcl_gtp_cost_center'
            """
            if hostlike !="":
                sql = sql + "and (T.target_name like '%" + hostlike + "%') "

            if instancelike !="":
                sql = sql + "and (T.target_name like '%" + instancelike + "%') "

            sql = sql + """
                and (T.target_type IN ('oracle_database'))
                )
                SELECT
                    regexp_substr (target_name, '[^_]+', 1, 1) DBENV,
                    regexp_substr (target_name, '[^_]+', 1, 2) DBHOST,
                    regexp_substr (target_name, '[^_]+', 1, 3) DBINST,
                    DBAPPLICATION
                FROM data_tab
                order by 2, 3, 4, 1
            """
            cursor.execute(sql)
            items = list(cursor.fetchall())

            if hostlike !="":
                templateVars = {}
                template = env.get_template('oemtargetlist_hostlike.tpl')
                templateVars = {
                    "selected_host": hostlike,
                    "items" : items
                }
                templateVars.update(Root.menu)

            if instancelike !="":
                templateVars = {}
                template = env.get_template('oemtargetlist_instancelike.tpl')
                templateVars = {
                    "instancelike": instancelike,
                    "items" : items
                }
                templateVars.update(Root.menu)

            output = template.render(templateVars)
            cursor.close()

        except cx_Oracle.DatabaseError as err:
            output = str(err)

        return output

    @cherrypy.expose()
    def oemhosts(self, *args, **kwargs):
        self.restrict_url_to_logged()
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()
            sql = """
                WITH data_tab AS (
                select T.target_name, P1.property_value as DBAPPLICATION
                from sysman.mgmt$target T,
                     sysman.mgmt$target_properties P1
                where P1.TARGET_GUID(+) = T.TARGET_GUID
                and P1.property_name(+) = 'orcl_gtp_cost_center'
                and (T.target_type IN ('oracle_database'))
                )
                SELECT distinct
                    regexp_substr (target_name, '[^_]+', 1, 2) DBHOST
                FROM data_tab
                order by 1 asc
            """
            cursor.execute(sql)
            items = list(cursor.fetchall())
            cursor.close()
        except cx_Oracle.DatabaseError as err:
            output = str(err)
            return output

        exaprodhosts=[]
        exapnonrodhosts=[]
        otherhosts=[]

        for item in items:
            dbhost = str(item[0])

            if dbhost.startswith("dmp"):
                exaprodhosts.append(dbhost)
            elif dbhost.startswith("dmt"):
                exapnonrodhosts.append(dbhost)
            else:
                otherhosts.append(dbhost)

        templateVars = {}
        template = env.get_template('oemhosts.tpl')
        templateVars = {
            "exaprodhosts" : exaprodhosts,
            "exapnonrodhosts" : exapnonrodhosts,
            "otherhosts" : otherhosts,
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    @cherrypy.expose()
    def drivesessions(self, *args, **kwargs):
        country = kwargs["country"]
        accepted_group = "HELPDESK_" + country
        self.restrict_url_to_groups(["SUPERUSER", accepted_group])
        try:
            db = cx_Oracle.connect(dsn=DRIVE_DATABASES[country])
            cursor = db.cursor()
            html_newline = "%20%0A"
            sql = """
                SELECT DISTINCT
                    S1.SID||'-'||S1.SERIAL#||'-@'||S1.INST_ID,
                    S1.USERNAME ||' - '||'<A HREF="mailto:'||a.email_address||'?subject=%s: Application blocking session(s)&body=Hello '|| a.full_name || ',%sPlease be advised that your session is currently blocking other users in %s on ' || S1.MODULE || ' screen with your session '|| a.user_name ||'.%sCan you please disconnect or complete the current actions underway to avoid blocking of other users.%sShould you require more information on this incident please feel free to contact your local help desk.%sKind regards">'||a.full_name||'</A>',
                    S1.MODULE,
                    S2.USERNAME ||' - '||'<A HREF=mailto:'||b.email_address||'>'||b.full_name||'</A>',
                    S2.MODULE,
                    S1.SQL_ID,
                    trunc(L1.ctime/60)
                FROM GV$LOCK L1,
                    GV$SESSION S1,
                    GV$LOCK L2,
                    GV$SESSION S2,
                    drive.drive_users a,
                    drive.drive_users b
                WHERE S1.SID=L1.SID AND S2.SID=L2.SID
                    AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
                    AND L1.BLOCK > 0 AND L2.REQUEST > 0
                    AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2
                    AND S1.username=a.user_name(+)
                    AND S2.username=b.user_name(+)
                    AND trunc(L1.ctime/60)>0
                    AND S1.username not in ('SYS','SYSTEM','OGG_USER','DRIVE','WSHANDLER')
            """ % ("Drive " + country, html_newline + html_newline, "Drive " + country,  html_newline, html_newline, html_newline)
            cursor.execute(sql)
            items = list(cursor.fetchall())
            cursor.close()
            db.close()
            title = DRIVE_DATABASES_DESCRIPTION[country]
            templateVars = {}
            template = env.get_template('drivesessions.tpl')
            templateVars = {
                "title" : title,
                "items" : items
            }
            templateVars.update(Root.menu)
            output = template.render(templateVars)
        except Exception  as err:
            output = str(DRIVE_DATABASES_DESCRIPTION[country])
            pass

        return output

    @cherrypy.expose()
    def drivekillsessions(self, *args, **kwargs):
        country = kwargs["country"]
        oracle_sessions_string = kwargs["sessions"]
        accepted_group = "HELPDESK_" + country
        self.restrict_url_to_groups(["SUPERUSER", accepted_group])
        oracle_sessions = oracle_sessions_string.split(",")
        try:
            db = cx_Oracle.connect(dsn=DRIVE_DATABASES[country])
            cursor = db.cursor()
            for oracle_session in oracle_sessions:
                (oracle_sid, oracle_serial, oracle_instance) = oracle_session.split("-")
                # before KILL session, grab informations about SQL_ID, user email etc.
                sql = "select username, sql_id, event, seconds_in_wait from gv$session where sid=" + oracle_sid + " and serial#=" + oracle_serial
                cursor.execute(sql)
                for row in cursor:
                    username, sql_id, event, seconds_in_wait = row

                sql = "select full_name, email_address from drive.drive_users where user_name='" + username + "'"
                cursor.execute(sql)
                for row in cursor:
                    full_name, email_address = row

                sql = "alter system disconnect session '" +  oracle_sid + "," + oracle_serial + "," + oracle_instance + "' immediate"
                logger.info("HELPDESK " + cherrypy.session.get("ad_uid") + " killed on " + DRIVE_DATABASES[country] + "  the user " + username + " having SID=" + str(oracle_sid) + ", SERIAL#=" + str(oracle_serial) + ", SQL_ID=" + str(sql_id) + ", EVENT=" + event + ", SECONDS_IN_WAIT=" + str(seconds_in_wait))
                cursor.execute(sql)

            cursor.close()
            db.close()
            # Send email
            email_body = """
            Hello %s,\n
            Your DRIVE application session has been killed because it was blocking other sessions.\n
            For more details, you can contact me by email: %s \n
            ----- \n
            Kind Regards,\n
            %s
            """ % (full_name, cherrypy.session.get("ad_uid") + "@arval.com", cherrypy.session.get("ad_uid") + "@arval.com")
            msg = EmailMessage()
            msg.set_content(email_body)
            msg['Subject'] = "DRIVE " + country + " -- session killed"
            if email_address == "" or email_address==None:
                email_address = str(username) + "@arval.com"
            print (email_address)
            msg['From'] = cherrypy.session.get("ad_uid") + "@arval.com"
            msg['To'] = email_address
            # Send the message via our own SMTP server.
            s = smtplib.SMTP('localhost')
            s.send_message(msg)
            s.quit()

        except cx_Oracle.DatabaseError as err:
            print (err)

        # Display information windows
        templateVars = {}
        template = env.get_template('box_wait.tpl')
        box_type = "box-primary"
        box_title = "Please wait..."
        redirect_url = "../drivesessions?country=" + country
        redirect_timeout = "3000"
        box_message1 = "Starting session KILL..."
        box_message2 = "This page will be <b>automaticly redirected</b> after JOB submission."
        templateVars = {
            "box_type" : box_type,
            "box_title" : box_title,
            "redirect_url" : redirect_url,
            "redirect_timeout" : redirect_timeout,
            "box_message1" : box_message1,
            "box_message2" : box_message2
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)

        return output

    @cherrypy.expose()
    def show_logfile(self, *args, **kwargs):
        self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])

        if cherrypy.session.get('ad_uid') != None:
            logfile = kwargs["logfile"]
            logfile_contents = open(OGG_SERVICE_DIR + "/log/www_executions/" + logfile, "r", encoding = "utf-8", errors = "replace").readlines()
            templateVars = {}
            template = env.get_template('view_log.tpl')
            templateVars = {
                "logfile" : logfile,
                "logfile_contents" : "".join(logfile_contents)
            }
            templateVars.update(Root.menu)
            output = template.render(templateVars)
        else:
            raise cherrypy.HTTPRedirect("/login_page")
        return output

    def build_menu(self):
        # Disable all by default
        Root.menu["show_ogg_menu"] = "NO"
        Root.menu["show_drive_it_sessions_menu"] = "NO"
        Root.menu["show_drive_fr_sessions_menu"] = "NO"
        Root.menu["show_drive_sp_sessions_menu"] = "NO"
        Root.menu["show_drive_uk_sessions_menu"] = "NO"
        # Enable by GROUP
        if self.user_is_in_groups(["SUPERUSER","OGGOPER"]):
            Root.menu["show_ogg_menu"] = "YES"
        if self.user_is_in_groups(["SUPERUSER","HELPDESK_IT"]):
            Root.menu["show_drive_it_sessions_menu"] = "YES"
        if self.user_is_in_groups(["SUPERUSER","HELPDESK_FR"]):
            Root.menu["show_drive_fr_sessions_menu"] = "YES"
        if self.user_is_in_groups(["SUPERUSER","HELPDESK_SP"]):
            Root.menu["show_drive_sp_sessions_menu"] = "YES"
        if self.user_is_in_groups(["SUPERUSER","HELPDESK_UK"]):
            Root.menu["show_drive_uk_sessions_menu"] = "YES"

        return

    @cherrypy.expose()
    def logout(self):
        cherrypy.session.pop('ad_uid', None)
        raise cherrypy.HTTPRedirect("/login_page")
        return

    @cherrypy.expose()
    def login(self, *args, **kwargs):
        ad_uid = kwargs["ad_uid"]
        ad_domain = kwargs["ad_domain"]
        ad_password = kwargs["ad_password"]
        if not ad_validate(ad_uid, ad_domain, ad_password):
            raise cherrypy.HTTPRedirect("/login_page")
        else:
            cherrypy.session["ad_uid"] = ad_uid.lower()
            Root.user_dict = load_user_dict()
            self.build_menu()
            raise cherrypy.HTTPRedirect("/index")
        return

    @cherrypy.expose()
    def index(self):
        self.restrict_url_to_logged()

        templateVars = {}
        template = env.get_template('start_page.tpl')
        templateVars = {}
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    def restrict_url_to_logged(self):
        # Check if user is logged in
        if cherrypy.session.get("ad_uid") == None:
            # Not logged in, redirect to login page
            raise cherrypy.HTTPRedirect("/login_page")
        return

    def user_is_in_groups(self, groups):
        is_in_groups = False
        try:
            user_groups = Root.user_dict[cherrypy.session.get("ad_uid")]
        except KeyError as e:
            user_groups = "GUEST"
        for group in groups:
            if group in user_groups:
                is_in_groups = True
                break
        return is_in_groups


    def restrict_url_to_groups(self, authorized_groups):
        self.restrict_url_to_logged()
        authorized = False

        if not self.user_is_in_groups(authorized_groups):
            raise cherrypy.HTTPRedirect("/unathorized")
        return

    @cherrypy.expose()
    def dash_start(self, *args, **kwargs):
        # self.restrict_url_to_logged()

        # Set to production cluster by default
        try:
                cluster = kwargs["cluster"]
        except:
                cluster = "dmp"

        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()

            # Database status counters and graph
            sql = """
                with exadata_db as (
                SELECT
                 mgmt$target.host_name
                 , mgmt$target.target_name
                 , mgmt$target.target_type
                 FROM sysman.mgmt$target
                 where mgmt$target.host_name like '%s'
                 and mgmt$target.target_type = 'rac_database'
                 )
                select count(*), A.AVAILABILITY_STATUS
                FROM sysman.mgmt$availability_current A, sysman.MGMT$TARGET B , exadata_db C
                WHERE B.target_name= C.target_name
                AND A.TARGET_GUID=B.TARGET_GUID
                group by AVAILABILITY_STATUS
            """ % (cluster + "%")

            cursor.execute(sql)

             # All that sequence for guarantee series color :)
            series_dict={}
            status_dict={}
            for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
                series_dict[status_name] = "{name: '" + status_name + "', data: [0]},"
                status_dict[status_name] = 0

            for row in cursor:
                (target_count, availability_status) = row
                for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
                    if availability_status == status_name:
                        series_dict[status_name] = "{ name: '" + availability_status + "', data: [" + str(target_count) + "]},"
                        status_dict[status_name] = target_count

            series = ""
            for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
                series = series + series_dict[status_name]



            # Dataguard status counters
            sql = """
              SELECT count(*)
              FROM sysman.mgmt$metric_current
              WHERE metric_column='dg_status'
              AND key_value     !=target_name
            """
            cursor.execute(sql)

            for row in cursor:
                dataguard_total_number = row[0]

            sql = """
                  with
                  drp_errors as (
                  SELECT DISTINCT entity_name,
                    value,
                    ROUND((sysdate-collection_time)*1440) Min_delta_collect,
                    CASE
                      WHEN ROUND((sysdate-collection_time)*1440)>=%s
                      THEN 'collect_error'
                      ELSE
                        CASE
                          WHEN value>%s
                          THEN 'unsync'
                        END
                    END drp_status
                  FROM sysman.GC_METRIC_VALUES_LATEST
                  WHERE metric_group_name ='dataguard_sperf_112'
                  AND metric_column_name IN ('dg_lag','dg_pdl')
                  AND (value              >%s
                  OR collection_time     <=sysdate-%s/1440)
                  ORDER BY value DESC,
                    Min_delta_collect DESC
                  )
                  select count(1), drp_status from drp_errors group by drp_status order by drp_status asc
            """ % (DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES, DATGUARD_ACCEPTED_LAG_SECONDS, DATGUARD_ACCEPTED_LAG_SECONDS, DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES)

            cursor.execute(sql)
            dataguard_status_dict = {"sync":0, "unsync":0, "collect_error":0}
            for row in cursor:
                (count, dataguard_status) = row
                dataguard_status_dict[dataguard_status] = count

            dataguard_status_dict['sync'] = dataguard_total_number

            cursor.close()
            db.close()

        except cx_Oracle.DatabaseError as err:
            raise

        # Build target status graph
        templateVars = {}
        template = env.get_template('target_status_column.graph')

        templateVars = {
            "chart_name": "target_status_column_graph",
            "series": series,
        }

        target_status_column_graph = template.render(templateVars)

        # Build target status table
        templateVars = {}
        template = env.get_template('target_status.table')

        templateVars = {
            "target_up": status_dict["Target Up"],
            "target_down": status_dict["Target Down"],
            "blackout": status_dict["Blackout"],
            "metric_error": status_dict["Metric Error"],
            "pending_unknow": status_dict["Pending/Unknown"],
        }

        target_status_table = template.render(templateVars)

        # Build dataguard status table
        template = env.get_template('dataguard_status.table')
        templateVars = {
            "collect_error": dataguard_status_dict['collect_error'],
            "unsync": dataguard_status_dict['unsync'],
            "sync": dataguard_status_dict['sync'],
        }

        dataguard_status_table = template.render(templateVars)


        # Drive boxes
        drive_box_content_dict={"FR":"", "UK":"", "SP":"", "IT":""}
        for country_key in drive_box_content_dict:
            drive_db_name = DRIVE_DATABASES[country_key].split("/")[1]
            try:
                db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
                cursor = db.cursor()

                # User session count metric
                sql = """
                      select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_USER_SESSION_COUNT'
                      and target_name='%s'
                """ % (drive_db_name)
                cursor.execute(sql)
                for row in cursor:
                    user_sessions = row[0]


                # Active session count metric
                sql = """
                      select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_ACTIVE_SESSION_COUNT'
                      and target_name='%s'
                """ % (drive_db_name)
                cursor.execute(sql)
                for row in cursor:
                    active_sessions = row[0]


                sql = """
                      select value from sysman.mgmt$metric_current where metric_name ='ME$LOCK_COUNT'
                      and target_name='%s'
                """ % (drive_db_name)
                cursor.execute(sql)
                for row in cursor:
                    locked_sessions = row[0]

                templateVars = {}
                template = env.get_template('drive_mini.table')
                templateVars = {
                    "user_sessions": user_sessions,
                    "active_sessions": active_sessions,
                    "locked_sessions": locked_sessions,
                }
                drive_box_content_dict[country_key] = template.render(templateVars)
            except:
                raise


        # Build dashboard
        templateVars = {}
        template = env.get_template('dash_start.tpl')
        if cluster == "dmp":
            title = "EXADATA PRODUCTION"
        elif cluster == "dmt":
            title = "EXADATA NON-PRODUCTION"

        templateVars = {
            "title": title,
            "cluster": cluster,
            "target_status_table": target_status_table,
            "dataguard_status_table": dataguard_status_table,
            "target_status_column_graph": target_status_column_graph,
            "drive_fr": drive_box_content_dict["FR"],
            "drive_uk": drive_box_content_dict["UK"],
            "drive_sp": drive_box_content_dict["SP"],
            "drive_it": drive_box_content_dict["IT"],
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)

        return output

    @cherrypy.expose()
    def dash_start2(self, *args, **kwargs):
        self.restrict_url_to_logged()
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()

            # Database status counters
            sql = """
                with exadata_db as (
                SELECT
                 mgmt$target.host_name
                 , mgmt$target.target_name
                 , mgmt$target.target_type
                 FROM sysman.mgmt$target
                 where mgmt$target.host_name like 'dmp01%'
                 and mgmt$target.target_type = 'rac_database'
                 )
                select count(*), A.AVAILABILITY_STATUS
                FROM sysman.mgmt$availability_current A, sysman.MGMT$TARGET B , exadata_db C
                WHERE B.target_name= C.target_name
                AND A.TARGET_GUID=B.TARGET_GUID
                group by AVAILABILITY_STATUS
            """
            cursor.execute(sql)

            status_dict={}

            for status_name in ["Target Up", "Target Down", "Blackout", "Metric Error","Pending/Unknown"]:
                status_dict[status_name] = 0

            for row in cursor:
                (target_count, availability_status) = row
                status_dict[availability_status] = target_count

            sql = """
                  select metric_column, key_value, value  from sysman.mgmt$metric_current
                  where
                  target_name='+ASM_cluster-dmp' and
                  target_type='osm_cluster' and
                  metric_name='DiskGroup_Usage' and key_value!='DBFS'
                  and metric_column in ('total_mb','usable_file_mb')
            """
            cursor.execute(sql)

            usable = {}
            total = {}
            used = {}

            for row in cursor:
                (metric_column, key_value, value) = row
                if metric_column == "usable_file_mb":
                    usable[key_value] = round (float(value)/1024/1024,2)
                else:
                    total[key_value] = round (float(value)/1024/1024,2)

            for key in total.keys():
                used[key] = round(total[key] - usable[key], 2)

            dg_data_series = "[%s,%s]" % (used["DATA"], usable["DATA"])
            dg_data_labels = "['Used','Free']"

            dg_reco_series = "[%s,%s]" % (used["RECO"], usable["RECO"])
            dg_reco_labels = "['Used','Free']"


            cursor.close()
            db.close()

        except cx_Oracle.DatabaseError as err:
            raise


        # Build target status table
        templateVars = {}
        template = env.get_template('target_status.table')

        templateVars = {
            "target_up": status_dict["Target Up"],
            "target_down": status_dict["Target Down"],
            "blackout": status_dict["Blackout"],
            "metric_issue": status_dict["Metric Error"] + status_dict["Pending/Unknown"],
        }
        target_status_table = template.render(templateVars)


        # Build Storage graph
        templateVars = {}
        template = env.get_template('asm_dg.graph')
        templateVars = {
            "chart_name": "ASM_DG_DATA",
            "chart_title": "",
            "series": dg_data_series,
            "labels": dg_data_labels,
        }
        asm_data_graph = template.render(templateVars)

        templateVars = {}
        template = env.get_template('asm_dg.graph')
        templateVars = {
            "chart_name": "ASM_DG_RECO",
            "chart_title": "",
            "series": dg_reco_series,
            "labels": dg_reco_labels,
        }
        asm_reco_graph = template.render(templateVars)

        # Drive boxes
        drive_box_content_dict={"FR":"", "UK":"", "SP":"", "IT":""}
        for country_key in drive_box_content_dict:
            drive_db_name = DRIVE_DATABASES[country_key].split("/")[1]
            try:
                db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
                cursor = db.cursor()

                # User session count metric
                sql = """
                      select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_USER_SESSION_COUNT'
                      and target_name='%s'
                """ % (drive_db_name)
                cursor.execute(sql)
                for row in cursor:
                    user_sessions = row[0]


                # Active session count metric
                sql = """
                      select value from sysman.mgmt$metric_current where metric_name ='ME$GLOBAL_ACTIVE_SESSION_COUNT'
                      and target_name='%s'
                """ % (drive_db_name)
                cursor.execute(sql)
                for row in cursor:
                    active_sessions = row[0]


                sql = """
                      select value from sysman.mgmt$metric_current where metric_name ='ME$LOCK_COUNT'
                      and target_name='%s'
                """ % (drive_db_name)
                cursor.execute(sql)
                for row in cursor:
                    locked_sessions = row[0]


                templateVars = {}
                template = env.get_template('drive_mini.table')
                templateVars = {
                    "user_sessions": user_sessions,
                    "active_sessions": active_sessions,
                    "locked_sessions": locked_sessions,
                    "workload": self.db_workload_mini_graph2(drive_db_name),
                }
                drive_box_content_dict[country_key] = template.render(templateVars)
            except:
                raise

        # Build dashboard
        templateVars = {}
        template = env.get_template('dash_start2.tpl')

        templateVars = {
            "title": "EXADATA PRODUCTION",
            "target_status_table": target_status_table,
            "asm_data_graph": asm_data_graph,
            "asm_reco_graph": asm_reco_graph,
            "drive_fr": drive_box_content_dict["FR"],
            "drive_uk": drive_box_content_dict["UK"],
            "drive_sp": drive_box_content_dict["SP"],
            "drive_it": drive_box_content_dict["IT"],
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)

        return output


    @cherrypy.expose()
    def dash_target_status_detail(self, *args, **kwargs):
        self.restrict_url_to_logged()
        # Set to production cluster by default
        try:
                cluster = kwargs["cluster"]
        except:
                cluster = "dmp"

        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()
            sql1 = """
                with exadata_db as (
                SELECT
                 mgmt$target.host_name
                 , mgmt$target.target_name
                 , mgmt$target.target_type
                 FROM sysman.mgmt$target
                 where mgmt$target.host_name like
                 """
            sql2 = "'" + cluster + '%' + "'"
            sql3 = """
                 and mgmt$target.target_type = 'rac_database'
                 )
                 select C.target_name, E.easy_name, A.AVAILABILITY_STATUS
                 FROM sysman.mgmt$availability_current A, sysman.MGMT$TARGET B , exadata_db C, system.t_target_easy_name E WHERE B.target_name= C.target_name
                 AND B.target_name= E.target_name (+)
                 AND A.TARGET_GUID=B.TARGET_GUID
                 order by E.display_priority ASC
            """
            sql = sql1 + sql2 + sql3
            cursor.execute(sql)
            items = list(cursor.fetchall())
            cursor.close()
            db.close()
        except cx_Oracle.DatabaseError as err:
            raise



        templateVars = {}
        template = env.get_template('target_status_details.table')

        if cluster == "dmp":
            title = "Exadata production database status"
        elif cluster == "dmt":
            title = "Exadata non-production database status"

        templateVars = {
            "title": title,
            "query_result": items,
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output


    @cherrypy.expose()
    def dash_dataguard_status_detail(self, *args, **kwargs):
        # self.restrict_url_to_logged()
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()
            sql = """
                  with
                  drp_errors as (
                  SELECT DISTINCT entity_name,
                    value,
                    ROUND((sysdate-collection_time)*1440) Min_delta_collect,
                    CASE
                      WHEN ROUND((sysdate-collection_time)*1440)>=%s
                      THEN 'Collect error'
                      ELSE
                        CASE
                          WHEN value>%s
                          THEN 'Unsync'
                          ELSE 'Sync'
                        END
                    END drp_status
                  FROM sysman.GC_METRIC_VALUES_LATEST
                  WHERE entity_name like '%s' and metric_group_name ='dataguard_sperf_112'
                  AND metric_column_name IN ('dg_lag','dg_pdl')
                  AND (value              >%s
                  OR collection_time     <=sysdate-%s/1440)
                  ORDER BY value DESC,
                    Min_delta_collect DESC
                  ),
                  drp_list as (
                    SELECT target_name,
                    key_value
                  FROM sysman.mgmt$metric_current
                  WHERE
                  metric_column='dg_status'
                  AND key_value     !=target_name
                  )
                  select n.easy_name, e.*, l.target_name from drp_errors e, drp_list l, system.t_target_easy_name n
                  where e.entity_name=l.key_value
                  and l.target_name = n.target_name (+)
            """ % (DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES, DATGUARD_ACCEPTED_LAG_SECONDS, "%EXA",DATGUARD_ACCEPTED_LAG_SECONDS, DATGUARD_ACCEPTED_COLLECT_DELAI_MINUTES)

            cursor.execute(sql)
            items = list(cursor.fetchall())
            cursor.close()
            db.close()
        except cx_Oracle.DatabaseError as err:
            raise


        templateVars = {}
        template = env.get_template('dataguard_status_details.table')


        templateVars = {
            "title": "Unsynchronised/suspicious Dataguards",
            "query_result": items,
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output


    def graph_simple_custom_metric(self, database, metric_name, hours_in_past, graph_title, line_color, line_description):
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, OEMDB_EZ_STRING)
            cursor = db.cursor()
            sql = """
                   SELECT collection_timestamp, value
                   FROM sysman.mgmt$metric_details
                   WHERE metric_name         ='%s'
                   AND target_name           ='%s'
                   AND collection_timestamp >=sysdate-%s/24
                   ORDER BY collection_timestamp ASC
                  """ % (metric_name, database, hours_in_past)

            cursor.execute(sql)
            items = list(cursor.fetchall())
            cursor.close()
            db.close()
        except cx_Oracle.DatabaseError as err:
            raise

        series_values=[]
        xaxis_values=[]
        for item in items:
            series_values.append(str(item[1]))
            xaxis_values.append('"' + item[0].strftime("%H:%M") + '"')

        series = 'series: [{name: "' + line_description + '", data: [' + ",".join(series_values) + '] }],'
        xaxis = 'xaxis: { categories: [' + ",".join(xaxis_values) +  '] } };'


        short_metric_name = metric_name.split("ME$")[1]
        templateVars = {}
        template = env.get_template('simple_custom_metric.graph')
        templateVars = {
            "chart_name": "%s_%s" % (database, short_metric_name),
            "chart_title": graph_title,
            "line_color": line_color,
            "series": series,
            "xaxis": xaxis,
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    def graph_ash_wait_class(self, database):
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, "dmp01-scan/" + database)
            cursor = db.cursor()
            sql = """
                select round(count(*)/60) MINUTES, DECODE(WAIT_CLASS,NULL,'CPU',WAIT_CLASS)
                from gv$active_session_history
                where SAMPLE_TIME between sysdate-1/24 and sysdate
                group by WAIT_CLASS having count(*)>=300 FETCH FIRST 5 ROWS ONLY
            """
            cursor.execute(sql)
            series_values=[]
            labels_values=[]

            for row in cursor:
                (secondes, wait_class) = row
                series_values.append(str(secondes))
                labels_values.append("'" + wait_class + "'")

            series = "[" + ",".join(series_values) + "]"
            labels = '[' + ",".join(labels_values) + "]"

            cursor.close()
            db.close()
        except cx_Oracle.DatabaseError as err:
            raise

        templateVars = {}
        template = env.get_template('ash_wait_class.graph')
        templateVars = {
            "chart_name": "graph_ash_wait_class",
            "chart_title": "Wait events profile for last hour",
            "series": series,
            "labels": labels,
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    @cherrypy.expose()
    def dashboard_db(self, *args, **kwargs):
        self.restrict_url_to_logged()
        db = kwargs["db"]
        try:
            (instance1, instance2) = get_target_instances(db)
        except ValueError as err:
            # Cannot identify database instances from OEM
            templateVars = {}
            template = env.get_template('box_message_with_back.tpl')
            box_type = "box-primary"
            box_title = db
            box_message = "Instances of %s database are not defined in the OEM repository"  % db
            templateVars = {
                "box_type" : box_type,
                "box_title" : box_title,
                "box_message" : box_message
            }
            output = template.render(templateVars)
            return output

        graph_active_sessions = self.graph_simple_custom_metric(db, "ME$GLOBAL_ACTIVE_SESSION_COUNT", ACTIVE_SESS_HIST_HOURS, "Active session history for the last %s hours" % ACTIVE_SESS_HIST_HOURS, "#f3f3f3", "Active sessions")
        if db in ["DRF1PRDEXA", "DRU1PRDEXA", "DRS1PRDEXA", "DRI1PRDEXA"]:
            graph_locks = self.graph_simple_custom_metric(db, "ME$LOCK_COUNT", LOCK_HIST_HOURS, "Locked session for the last %s hours" % LOCK_HIST_HOURS, "#f3f3f3", "Locked sessions")
        else:
            graph_locks = ""

        graph_ash_wait_class = self.graph_ash_wait_class(db)
        templateVars = {}
        template = env.get_template('dashboard_db.tpl')
        templateVars = {
            "title" : db + " dashboard",
            "graph_active_sessions" : graph_active_sessions,
            "graph_locks" : graph_locks,
            "graph_ash_wait_class" : graph_ash_wait_class,
        }

        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    @cherrypy.expose()
    def controlm_dash(self, *args, **kwargs):
        self.restrict_url_to_logged()
        # Identify the table name based on current hour
        now = datetime.datetime.now()
        current_year = now.strftime("%y")
        current_month = now.strftime("%m")
        current_day = now.strftime("%d")
        yesterday = str(int(current_day) - 1)
        if int(current_day) < 10:
            current_day = now.strftime("%d")

        if int(yesterday) < 10:
            yesterday = "0" + str(int(current_day) - 1)

        current_hour = now.strftime("%H")
        if int(current_hour) < 11:
            table_name = "EMUSER.A%s001_AJOB" % (current_year + current_month + yesterday)
        else:
            table_name = "EMUSER.A%s001_AJOB" % (current_year + current_month + current_day)

        # Assume that ration is 50 by default
        try:
            ratio = kwargs["ratio"]
        except:
            ratio = 50

        # Query the table and store result in items variable
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, CONTROLMDB_EZ_STRING)
            cursor = db.cursor()
            sql = """
                  SELECT application ,
                    group_name ,
                    job_name ,
                    memname ,
                    description ,
                    to_char(to_date(start_time, 'YYYYMMDDHH24MISS'),'DD/MM HH24:MI'),
                    SUBSTR(avg_runtime,1,2)
                    ||'h '
                    ||SUBSTR(avg_runtime,3,2)
                    ||'m '
                    || SUBSTR(avg_runtime,5,2)
                    ||'s',
                    TO_CHAR(TRUNC(((
                    (SELECT sysdate FROM dual
                    ) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/3600), '900')
                    || 'h'
                    || TO_CHAR(TRUNC(((
                    (SELECT sysdate FROM dual
                    ) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/60) - TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/3600)*60, '00')
                    || 'm'
                    || TO_CHAR(TRUNC(((
                    (SELECT sysdate
                    FROM dual
                    ) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600) - (TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/60)*60)), '00')
                    || 's'
                  FROM %s
                  WHERE status='Executing'
                  AND (trim(TO_CHAR(TRUNC(((
                    (SELECT sysdate FROM dual
                    ) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/3600), '900'))
                    || trim(TO_CHAR(TRUNC(((
                    (SELECT sysdate FROM dual
                    ) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600)/60) - TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/3600)*60, '00'))
                    || trim(TO_CHAR(TRUNC(((
                    (SELECT sysdate FROM dual
                    ) - to_date(start_time, 'YYYYMMDDHH24MISS' ))*24*3600) - (TRUNC(((sysdate - to_date(start_time, 'YYYYMMDDHH24MISS'))*24*3600)/60)*60)), '00') )) > avg_runtime * (1+%s/100)
                  ORDER BY start_time ASC
            """  % (table_name, ratio)
            cursor.execute(sql)
            items = list(cursor.fetchall())
            cursor.close()
            db.close()
        except cx_Oracle.DatabaseError as err:
            raise

        templateVars = {}
        template = env.get_template('controlm_jobs.table')
        templateVars = {
            "title" : " running jobs",
            "items" : items,
            "ratio" : ratio,
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)

        # controlm_jobs.table
        return output


    def db_workload_mini_graph(self, database):
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, "dmp01-scan/" + database)
            cursor = db.cursor()
            sql = """
                  select VALUE from v$parameter where NAME in ('cpu_count','parallel_threads_per_cpu')
            """
            cursor.execute(sql)

            # We assume that the compute capacity on each instance is cpu_count + parallel_threads_per_cpu
            compute_threads = 2 # for both instances
            for row in cursor:
                compute_threads = compute_threads * int(row[0])

            sql = """
                  select count(*) from gv$session where status='ACTIVE' and type != 'BACKGROUND'
            """
            cursor.execute(sql)

            for row in cursor:
                active_sessions = row[0]

            cursor.close()
            db.close()

        except cx_Oracle.DatabaseError as err:
            raise

        in_use_percent = int(100*active_sessions/compute_threads)
        if in_use_percent >= 100:
            in_use_percent = 100

        free_percent = 100-in_use_percent
        series="[%s,%s]" % (in_use_percent, free_percent)

        templateVars = {}
        template = env.get_template('drive_workload_mini.graph')
        templateVars = {
           "chart_name": "workload_" + database,
           "chart_title": str(compute_threads),
           "series": series,
           "labels": "labels",        }
        output = template.render(templateVars)

        return output

    def db_workload_mini_graph2(self, database):
        try:
            db = cx_Oracle.connect(ORA_USERNAME, ORA_PASSWORD, "dmp01-scan/" + database)
            cursor = db.cursor()
            sql = """
                  select VALUE from v$parameter where NAME in ('cpu_count','parallel_threads_per_cpu')
            """
            cursor.execute(sql)

            # We assume that the compute capacity on each instance is cpu_count + parallel_threads_per_cpu
            compute_threads = 2 # for both instances
            for row in cursor:
                compute_threads = compute_threads * int(row[0])

            sql = """
                  select count(*) from gv$session where status='ACTIVE' and type != 'BACKGROUND'
            """
            cursor.execute(sql)

            for row in cursor:
                active_sessions = row[0]

            cursor.close()
            db.close()

        except cx_Oracle.DatabaseError as err:
            raise

        in_use_percent = int(100*active_sessions/compute_threads)
        if in_use_percent >= 100:
            in_use_percent = 100

        series="[%s]" % in_use_percent

        templateVars = {}
        template = env.get_template('gauge_workload.graph')
        templateVars = {
           "chart_name": "workload_" + database,
           "chart_title": str(compute_threads),
           "series": series,
           "labels": "labels",        }
        output = template.render(templateVars)

        return output


    @cherrypy.expose()
    def unathorized(self, *args, **kwargs):
        templateVars = {}
        template = env.get_template('box_message_with_back.tpl')
        box_type = "box-primary"
        box_title = "Unauthorized"
        box_message = "You are not authorized to use this feature."
        templateVars = {
            "box_type" : box_type,
            "box_title" : box_title,
            "box_message" : box_message
        }
        output = template.render(templateVars)
        return output


    @cherrypy.expose()
    def show_sync_and_actions(self, *args, **kwargs):
        self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])

        cherrypy.session["sync_action_has_been_executed"] = "NO"
        templateVars = {}
        template = env.get_template('show_sync_and_actions.tpl')
        try:
            (extract, replicat) = kwargs["sync"].split("_")
        except KeyError as e:
            raise cherrypy.HTTPRedirect("/select_sync")

        cherrypy.session["extract"] = extract
        cherrypy.session["replicat"] = replicat
        ogg_sync = OGG_Sync(extract, replicat)
        ogg_sync.parse_prm_headers()
        ogg_sync.parse_prm_tables()
        ogg_sync.build_extract_prm()
        ogg_sync.build_replicat_prm()
        ogg_sync.update_sync_status()
        extract_file_contents = open(ogg_sync.extract_prm_filename, "r", encoding = "utf-8", errors = "replace").readlines()
        extract_file_contents = "".join(extract_file_contents).replace("ogguser01$", "*****")
        replicat_file_contents = open(ogg_sync.replicat_prm_filename, "r", encoding = "utf-8", errors = "replace").readlines()
        replicat_file_contents = "".join(replicat_file_contents).replace("ogguser01$", "*****")

        list_of_logfiles = []
        for root, dirs, files in os.walk(OGG_SERVICE_DIR + "/log/www_executions/"):
            for filename in files:
                if (extract in filename) and (replicat in filename):
                    list_of_logfiles.append(filename)

        list_of_logfiles.sort(key = str.lower, reverse = True)

        html_list_of_logfiles = "<ul>"
        for logfile in list_of_logfiles:
            html_list_of_logfiles = html_list_of_logfiles + '<li> <i class="fa fa-fw fa-file-text-o"></i> <a href=/show_logfile?logfile=' + logfile + '>' + logfile + '</a> </li>'
        html_list_of_logfiles = html_list_of_logfiles + "</ul>"

        if DEBUG_MODE:
            debug_info = "nothing"
            # debug_info = ogg_sync.get_class_attributes_as_json()
        else:
            debug_info = ""
        templateVars = {
            "extract_replicat" : extract + "_" + replicat,
            "extract_file_contents" : extract_file_contents,
            "replicat_file_contents" : replicat_file_contents,
            "extract" : ogg_sync.extract,
            "replicat" : ogg_sync.replicat,
            "extract_status" : ogg_sync.extract_status,
            "replicat_status" : ogg_sync.replicat_status,
            "list_of_logfiles" : html_list_of_logfiles,
            "debug_info": debug_info
        }
        templateVars.update(Root.menu)
        output = template.render(templateVars)
        return output

    @cherrypy.expose()
    def execute_sync_action(self, *args, **kwargs):
        self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])

        if cherrypy.session["sync_action_has_been_executed"] != "YES":
            action = kwargs["action"]
            extract = cherrypy.session.get("extract")
            replicat = cherrypy.session.get("replicat")
            if action == "stop" or action == "start" or action == "full":
                cherrypy.session["action"] = action
                templateVars = {}
                template = env.get_template('box_confirm.tpl')
                box_type = "box-primary"
                box_title = "Please confirm your action"
                if action != "full":
                    box_message = "Are you sure you want to <b>" + action.upper() + "</b> the extract <b>" +  extract + "</b> and  the replicat  <b>" + replicat + "</b> ? "
                else:
                    box_message = "Are you sure you want to start a <b>" + action.upper() + "</b> refresh of the OGG sync <b>" +  extract + "</b> => <b>" + replicat + "</b> ? "
                templateVars = {
                    "box_type" : box_type,
                    "box_title" : box_title,
                    "box_message" : box_message
                }
                templateVars.update(Root.menu)
                output = template.render(templateVars)
            if action == "incremental":
                extract_delta_file = kwargs["extract_delta_file"]
                replicat_delta_file = kwargs["replicat_delta_file"]

                if extract_delta_file.file == None or replicat_delta_file.file == None:
                    templateVars = {}
                    template = env.get_template('box_message_with_back.tpl')
                    box_type = "box-primary"
                    box_title = "No file selected"
                    box_message = "Please pick both Extract/Replicat delta files before to continue."
                    templateVars = {
                        "box_type" : box_type,
                        "box_title" : box_title,
                        "box_message" : box_message
                    }
                    templateVars.update(Root.menu)
                    output = template.render(templateVars)
                    return output

                # Create extract_delta.prm
                f = open(OGG_SERVICE_DIR + "/sync.d/" +  extract + "_" + replicat + "/" + extract + "_delta.prm", 'wb')
                while True:
                    data = extract_delta_file.file.read(8192)
                    if not data:
                        break
                    f.write(data)
                f.close()
                # Create replicat_delta.prm
                f = open(OGG_SERVICE_DIR + "/sync.d/" +  extract + "_" + replicat + "/" + replicat + "_delta.prm", 'wb')
                while True:
                    data = replicat_delta_file.file.read(8192)
                    if not data:
                        break
                    f.write(data)

                cherrypy.session["action"] = action
                templateVars = {}
                template = env.get_template('box_confirm.tpl')
                box_type = "box-primary"
                box_title = "Please confirm your action"
                box_message = "Are you sure you want to start a <b>" + action.upper() + "</b> refresh of the OGG sync <b>" +  extract + "</b> => <b>" + replicat + "</b> ? "
                templateVars = {
                    "box_type" : box_type,
                    "box_title" : box_title,
                    "box_message" : box_message
                }
                templateVars.update(Root.menu)
                output = template.render(templateVars)
        else:
            output = "Already executed"
        return output

    @cherrypy.expose()
    def start_ogg_sync_in_background(self, *args, **kwargs):
        self.restrict_url_to_groups(["SUPERUSER","OGGOPER"])

        if cherrypy.session["sync_action_has_been_executed"] != "YES":
            cherrypy.session["sync_action_has_been_executed"] = "YES"
            action = cherrypy.session.get("action")
            extract = cherrypy.session.get("extract")
            replicat = cherrypy.session.get("replicat")
            runid = datetime.datetime.now().strftime('%Y-%m-%d_%H_%M_%S_%f')
            logfile = OGG_SERVICE_DIR + "/log/www_executions/" + extract + "_" + replicat + "_" + runid + ".log"

            try:
            # Start GGSCI command in background
                shellcommand = 'echo "source /home/oracle/.bash_profile; /u01/app/oracle/admin/OGG_Service/ogg_sync.py -e ' + extract + ' -r ' + replicat + ' -s ' + action + '>' + logfile +'" | at now'
                logger.info("User " + cherrypy.session.get("ad_uid") + " run: " + shellcommand)
                cherrypy.log(shellcommand)
                cmd = subprocess.run(
                shellcommand,
                check=True,
                shell=True,
                stdout=subprocess.PIPE,
                )
            except:
                pass

            # Display information windows
            templateVars = {}
            template = env.get_template('box_wait.tpl')
            box_type = "box-primary"
            box_title = "Please wait..."
            redirect_url = "../select_sync"
            redirect_timeout = "5000"
            box_message1 = "Starting JOB: <b>" + action.upper() + "</b> the extract <b>" +  extract + "</b> and  the replicat  <b>" + replicat + "</b>"
            box_message2 = "This page will be <b>automaticly redirected</b> after JOB submission."
            templateVars = {
                "box_type" : box_type,
                "box_title" : box_title,
                "redirect_url" : redirect_url,
                "redirect_timeout" : redirect_timeout,
                "box_message1" : box_message1,
                "box_message2" : box_message2
            }
            templateVars.update(Root.menu)
            output = template.render(templateVars)
        else:
            output = "Already executed"
        return output


    @cherrypy.expose
    def other_url(self, *args, **kwargs):
        '''
        The name of the method is mapped to the URL. This url is /other_url
        Try calling this with /other_url/some/path
        Try calling this with /other_url?foo=Foo&bar=Bar
        Try calling this with POST data.
        '''
        return '''\
            Any sub-paths on the URL are available as args: {0}
            Query params _and_ POST data is available via kwargs: {1}
            Headers and the HTTP method and everything else is available via
            the thread-local cherrypy objects ``cherrypy.request`` and
            ``cherrypy.response``.
            You can get and set session values and cookies as though they are
            dictionaries:
            cherrypy.session['key'] = 'val'
            cherrypy.request.cookie['key'] = 'val'
            cherrypy.session.get('key', 'defaultval')
            cherrypy.request.cookie.get('key', 'defaultval')
            '''.format(args, kwargs)

    @cherrypy.expose
    def test_url(self, *args, **kwargs):
        '''
        The name of the method is mapped to the URL. This url is /other_url
        Try calling this with /other_url/some/path
        Try calling this with /other_url?foo=Foo&bar=Bar
        Try calling this with POST data.
        '''

        text1 = '''\
            Any sub-paths on the URL are available as args: {0}
            Query params _and_ POST data is available via kwargs: {1}
            Headers and the HTTP method and everything else is available via
            the thread-local cherrypy objects ``cherrypy.request`` and
            ``cherrypy.response``.
            You can get and set session values and cookies as though they are
            dictionaries:
            cherrypy.session['key'] = 'val'
            cherrypy.request.cookie['key'] = 'val'
            cherrypy.session.get('key', 'defaultval')
            cherrypy.request.cookie.get('key', 'defaultval')
            '''.format(args, kwargs)

        text2 = Root.user_dict["b11251"]

        return text2

if __name__ == '__main__':
    Root_conf = {
        '/': {
            'tools.staticdir.root': OGG_SERVICE_DIR + '/www'
        },
        '/AdminLTE': {
            'tools.staticdir.on': True,
            'tools.staticdir.dir': 'AdminLTE'
        },
        'global':{
                'server.socket_host' : "FRPIVSQL2418",
                        'server.socket_port' : 9026,
                        'server.thread_pool' : 4,
                        'tools.sessions.on' : True,
                        'tools.sessions.timeout': 60,
                        'tools.encode.encoding' : "Utf-8"
        }
    }
    # RUN
    cherrypy.quickstart(Root(), '/', config=Root_conf)
