Untitled

from tabulate import tabulate
from email_message import send_email
from pandas import read_sql as pd_read_sql
import cx_Oracle
import configparser
from json import loads as json_loads
import logging
from datetime import datetime
import gc
import numpy as np


# For windows os
# cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\1772569.INDIA\Desktop\Notification CR\instantclient_21_6")
# cx_Oracle.init_oracle_client(lib_dir=r"C:\ETPI\Deployments\Notificationalert\instantclient_21_6")

class LossyMergerError(Exception):
    # print("The merge resulted in loss of rows from data frame")
    def __init__(self, message="The merge resulted in loss of rows from data frame. Please check query's output"):
        self.message = message
        super().__init__(self.message)


def df_to_pdf(df, filename):
    import matplotlib.pyplot as plt
    from matplotlib.backends.backend_pdf import PdfPages

    fig, ax = plt.subplots(figsize=(12, 4))
    ax.axis('tight')
    ax.axis('off')
    the_table = ax.table(cellText=df.values, colLabels=df.columns, loc='center')
    pp = PdfPages(filename)
    pp.savefig(fig, bbox_inches='tight')
    pp.close()


try:
    configProperties = configparser.ConfigParser(interpolation=None)
    # configProperties.read(r'C:\Users\1772569.INDIA\Desktop\DevOps\OM_Monitoring\om_monitoring.config')
    # configProperties.read(r'C:\ETPI\Deployments\Notificationalert\falloutnotification.config')
    configProperties.read(r'falloutnotification.config')
    username = configProperties['config']['username']
    password = configProperties['config']['password']
    connectstring = configProperties['config']['connectstring']

    # __dup_corelation_id_query=configProperties['config']['dup_corelation_id_query']
    # assigned_gp_query=configProperties['config']['assigned_gp_query']
    # csa_intra_query=configProperties['config']['csa_intra_query']
    # normal_order_query=configProperties['config']['normal_order_query']
    # crms_query=configProperties['config']['crms_query']
    # crms_query_completed=configProperties['config']['crms_query_completed']
    # crms_query_inprogress=configProperties['config']['crms_query_inprogress']

    __BILLING_FALLOUT_DETAILS = configProperties['config']['BILLING_FALLOUT_DETAILS']
    __NETWORK_FALLOUT = configProperties['config']['NETWORK_FALLOUT']
    __RESOURCE_FALLOUT = configProperties['config']['RESOURCE_FALLOUT']
    __BIN_Movement_Halt_Identification = configProperties['config']['BIN_Movement_Halt_Identification']
    _CSA_Intra_ERROR = configProperties['config']['CSA_Intra_ERROR']
    mailMap = json_loads(configProperties['config']['mailMap'])

    path = r"{}".format(configProperties['config']['pdf_path'])
    # Deafault logging level
    level = logging.INFO

    # need raw string for input file path
    logfilename = r"{}".format(configProperties['logging']['logfilename'])
    get_log_level = configProperties['logging']['level']
    if get_log_level == "INFO":
        level = level = logging.INFO
    elif get_log_level == "DEBUG":
        level = level = logging.DEBUG
    elif get_log_level == "ERROR":
        level = level = logging.ERROR

    logging.basicConfig(filename=logfilename, filemode='a', level=level,
                        format='%(levelname)s :: %(asctime)s :: %(message)s')

    # connectstring_uat="192.168.217.206:1596/HOBS2UAT"
    # conn_uat = cx_Oracle.connect(username,password,connectstring_uat)

    conn = cx_Oracle.connect(username, password, connectstring)

    __df_BILLING_FALLOUT = pd_read_sql(__BILLING_FALLOUT_DETAILS, con=conn)
    __df_NETWORK_FALLOUT = pd_read_sql(__NETWORK_FALLOUT, con=conn)
    __df_RESOURCE_FALLOUT = pd_read_sql(__RESOURCE_FALLOUT, con=conn)
    __df_BIN_Movement_Halt = pd_read_sql(__BIN_Movement_Halt_Identification, con=conn)
    _df_CSA_Intra_ERROR = pd_read_sql(_CSA_Intra_ERROR, con=conn)
    # __df_dup_corelation_id=pd_read_sql(__dup_corelation_id_query,con = conn)
    # __df_assigned_gp=pd_read_sql(assigned_gp_query,con = conn)
    # __df_normal_order=pd_read_sql(normal_order_query,con = conn)
    # __df_csa_intra=pd_read_sql(csa_intra_query,con = conn)

    # __df_crms_comp=pd_read_sql(crms_query_completed,con = conn)
    # __df_crms_inprog=pd_read_sql(crms_query_inprogress,con = conn)
    # __df_crms=__df_crms_comp.append(__df_crms_inprog).drop_duplicates()

    # __df_BILLING_FALLOUT=__df_BILLING_FALLOUT.drop_duplicates()
    # __df_NETWORK_FALLOUT=__df_NETWORK_FALLOUT.drop_duplicates()

    # for Problem ticket 4812
    '''if len(__df_NETWORK_FALLOUT)>0 or len(__df_BILLING_FALLOUT)>0:
        result=__df_NETWORK_FALLOUT.append(__df_BILLING_FALLOUT)

        result=result.drop_duplicates()
        result.index = np.arange(1, len(result)+1) 

        print("Query gave more than 0 rows . Problem ticket 4812 monitoring found "+str(len(result))+" orders . Proceeding for Execution ")
        #print(result)


        now = datetime.now()
        curr_date=now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Fallout orders")
        sub="Please find fallout orders  "+str(curr_date)

        content="Hi All,<br><br>Please see below for list of orders"+str(curr_date)+"<br>{table}<br><br>Regards<br>DevOps<br><br>".format(table=result.to_html())##tabulate(table, headers='firstrow', tablefmt='fancy_grid'))

        to=mailMap["to"]
        cc=mailMap['cc_id']
        logging.debug("Subject is "+str(sub))
        logging.debug("Content is "+str(sub))
        logging.debug("Recipient is "+str(to))
        send_email(content,sub,to,cc,body_type='html')
        logging.info("fallout orders monitoring email Execution Completed sucessfully")
        print("fallout orders monitoring email Execution Completed sucessfully")

    else:
        logging.info(" fallout orders monitoring Query gave 0 rows")
        print("fallout orders monitoring Query gave 0 rows")'''

    # NETWORK FALLOUT monitoring
    if len(__df_NETWORK_FALLOUT) > 0:
        print("Query gave more than 0 rows . Assigned gp found " + str(
            len(__df_NETWORK_FALLOUT)) + " itemIds . Proceeding for Execution ")

        # For generalised report
        now = datetime.now()
        curr_date = now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Procedind for sending email for network fallout")
        sub = "Fallout orders ERROR In UAT  " + str(curr_date)

        content = "Hi All,<br><br>Please see below for list of Fallout orders ERROR In UAT  " + str(
            curr_date) + "<br>{table}<br><br>Regards<br>DevOps<br><br>".format(
            table=__df_NETWORK_FALLOUT.to_html())  ##tabulate(table, headers='firstrow', tablefmt='fancy_grid'))

        to = mailMap["to"]
        cc = mailMap['cc_id']
        logging.debug("Subject is " + str(sub))
        logging.debug("Content is " + str(sub))
        logging.debug("Recipient is " + str(to))
        send_email(content, sub, to, cc, body_type='html')
        logging.info("NETWORK FALLOUT orders email Execution Completed sucessfully")
        print("NETWORK FALLOUT orders email Execution Completed sucessfully")

    else:
        logging.info("NETWORK FALLOUT orders Query gave 0 rows")
        print("NETWORK FALLOUT orders Query gave 0 rows")

    # Correlation gp monitoring
    if len(__df_BILLING_FALLOUT) > 0:
        print("Query gave more than 0 rows . Duplicate corelation_id found " + str(
            len(__df_BILLING_FALLOUT)) + " orderIds . Proceeding for Execution ")

        # For BILLING FALLOUT
        now = datetime.now()
        curr_date = now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Procedind for sending email for billing fallout")
        sub = "BILLING FALLOUT" + str(curr_date)

        content = "Hi All,<br><br>Please see below for list of order for BILLING FALLOUT " + str(
            curr_date) + "<br>{table}<br><br>Regards<br>DevOps<br><br>".format(
            table=__df_BILLING_FALLOUT.to_html())  ##tabulate(table, headers='firstrow', tablefmt='fancy_grid'))

        to = mailMap["to"]
        cc = mailMap['cc_id']
        logging.debug("Subject is " + str(sub))
        logging.debug("Content is " + str(sub))
        logging.debug("Recipient is " + str(to))
        send_email(content, sub, to, cc, body_type='html')
        logging.info("BILLING FALLOUT email Execution Completed sucessfully")
        print("BILLING FALLOUT email Execution Completed sucessfully")

    else:
        logging.info("BILLING FALLOUT Query gave 0 rows")
        print("BILLING FALLOUT Query gave 0 rows")

        # RESOURCE FALLOUT monitoring
    if len(__df_RESOURCE_FALLOUT) > 0:
        print("Query gave more than 0 rows . Duplicate corelation_id found " + str(
            len(__df_RESOURCE_FALLOUT)) + " orderIds . Proceeding for Execution ")

        # For generalised report
        now = datetime.now()
        curr_date = now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Procedind for sending email for RESOURCE FALLOUT")
        sub = "RESOURCE FALLOUT" + str(curr_date)

        content = "Hi All,<br><br>Please see below for list of order for RESOURCE FALLOUT " + str(
            curr_date) + "<br>{table}<br><br>Regards<br>DevOps<br><br>".format(
            table=__df_RESOURCE_FALLOUT.to_html())  ##tabulate(table, headers='firstrow', tablefmt='fancy_grid'))

        to = mailMap["to"]
        cc = mailMap['cc_id']
        logging.debug("Subject is " + str(sub))
        logging.debug("Content is " + str(sub))
        logging.debug("Recipient is " + str(to))
        send_email(content, sub, to, cc, body_type='html')
        logging.info("RESOURCE FALLOUT email Execution Completed sucessfully")
        print("RESOURCE FALLOUT email Execution Completed sucessfully")

    else:
        logging.info("RESOURCE FALLOUT Query gave 0 rows")
        print("RESOURCE FALLOUT Query gave 0 rows")

    if len(__df_BIN_Movement_Halt) > 0:
        print("Query gave more than 0 rows . Duplicate corelation_id found " + str(
            len(__df_BIN_Movement_Halt)) + " orderIds . Proceeding for Execution ")

        # For generalised report
        now = datetime.now()
        curr_date = now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Procedind for sending email for BIN_Movement_Halt")
        sub = "BIN_Movement_Halt" + str(curr_date)

        content = "Hi All,<br><br>Please see below for list of order for BIN_Movement_Halt " + str(
            curr_date) + "<br>{table}<br><br>Regards<br>DevOps<br><br>".format(
            table=__df_BIN_Movement_Halt.to_html())  ##tabulate(table, headers='firstrow', tablefmt='fancy_grid'))

        to = mailMap["to"]
        cc = mailMap['cc_id']
        logging.debug("Subject is " + str(sub))
        logging.debug("Content is " + str(sub))
        logging.debug("Recipient is " + str(to))
        send_email(content, sub, to, cc, body_type='html')
        logging.info("BIN_Movement_Halt email Execution Completed sucessfully")
        print("BIN_Movement_Halt email Execution Completed sucessfully")

    else:
        logging.info("BIN_Movement_Halt Query gave 0 rows")
        print("BIN_Movement_Halt Query gave 0 rows")

    if len(_df_CSA_Intra_ERROR) > 0:
        print("Query gave more than 0 rows . Duplicate corelation_id found " + str(
            len(_df_CSA_Intra_ERROR)) + " orderIds . Proceeding for Execution ")

        # For generalised report
        now = datetime.now()
        curr_date = now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Procedind for sending email for CSA_Intra_ERROR")
        sub = "CSA_Intra_ERROR" + str(curr_date)

        #content = "Hi All,<br><br>Please see below for list of order for ERRORED In CSA Intra " + str(
            #curr_date) + "<br>{table}<br><br>Regards<br>DevOps<br><br>".format(
            #table=_df_CSA_Intra_ERROR.to_html())  ##tabulate(table, headers='firstrow', tablefmt='fancy_grid'))
        content = """
                <html>
                <head>
                <style>
                table {
                    width: 100%;
                    border-collapse: collapse;
                }
                th, td {
                    padding: 10px; /* Adjust the padding as needed */
                    text-align: left;
                    border-bottom: 1px solid #ddd;
                }
                </style>
                </head>
                <body>
                Hi All,<br><br>Please see below for list of Fallout orders ERROR In UAT {curr_date}<br>
                <table>
                {table}
                </table>
                <br>Regards<br>DevOps<br><br>
                </body>
                </html>
            """.format(curr_date=curr_date, table=__df_NETWORK_FALLOUT.to_html())

        to = mailMap["to"]
        cc = mailMap['cc_id']
        logging.debug("Subject is " + str(sub))
        logging.debug("Content is " + str(sub))
        logging.debug("Recipient is " + str(to))
        send_email(content, sub, to, cc, body_type='html')
        logging.info("CSA_Intra_ERROR email Execution Completed sucessfully")
        print("CSA_Intra_ERROR email Execution Completed sucessfully")

    else:
        logging.info("CSA_Intra_ERROR Query gave 0 rows")
        print("CSA_Intra_ERROR Query gave 0 rows")

    # crms monitoring
    '''if len(__df_crms)>0:
        __df_crms.index = np.arange(1, len(__df_crms)+1) 

        print("CRMS Query gave more than 0 rows . CRMS issue found for "+str(len(__df_crms))+" . Proceeding for Execution for email notification ")


        #For generalised report
        now = datetime.now()
        curr_date=now.strftime('%Y-%b-%d %I:%M %p')

        logging.info("Procedind for sending email for CRMS issue")
        sub="CRMS monitoring issue found in Prod "+str(curr_date)

        content="Hi All,<br><br>Please see below for list of order for for CRMS  "+str(curr_date)+"<br>{table}<br><br>Regards<br>DevOps<br><br>".format(table=__df_crms.to_html())
        to=mailMap["crms_to"]
        cc=mailMap['cc_id']
        logging.debug("Subject is "+str(sub))
        logging.debug("Content is "+str(sub))
        logging.debug("Recipient is "+str(to))
        send_email(content,sub,to,cc,body_type='html')
        logging.info("CRMS issue monitoring email Execution Completed sucessfully")
        print("CRMS issue monitoring email Execution Completed sucessfully")
    else:
        logging.info("CRMS issue monitoring query gave 0 rows")
        print("CRMS issue monitoring Query gave 0 rows")

    conn.close()
    conn_uat.close()
    #print('comp'+str(len(__df_crms_comp)))
    #print('inprogress'+str(len(__df_crms_inprog)))
    #print('main'+str(len(__df_crms)))'''
except Exception as e:
    print("ERROR in Execution" + str(e))
    logging.error("ERROR in Execution" + str(e))

# invoke garbage collector for cleanup
gc.collect()