ENOC TT Handler

#!/usr/bin/env python

#-------------------------------------------------------------------------------

# Name:        TroubleTicketParser.py

# Purpose:     To parse Trouble Tickets mail messages stored in database

#

# Author:      Panos Louridas, from an initial version by Spyros Kopsidas

# Copyright:   (c) EGEE Project 2009

# Licence:     Apache Software License

#

#-------------------------------------------------------------------------------

import sys

from optparse import OptionParser

from datetime import datetime

import imaplib

from email import message_from_string

from email.header import decode_header

import TTConfig

import MySQLdb

from TroubleTicketParser import *

def get_unparsed_emails_from_imap(server, username, passwd):

    """A generator for returning unparsed emails from the IMAP server."""

    c = imaplib.IMAP4(server)

    c.login(username, passwd)

    c.select()

    typ, data = c.search(None, 'ALL')

    for num in data[0].split():

        typ, data = c.fetch(num, '(RFC822)')

        for response_part in data:

            if isinstance(response_part, tuple):

                msg = message_from_string(response_part[1])

                header = ""

                body = ""

                if msg.is_multipart():

                    for msg_part in msg.get_payload(decode=True):

                        body = body + msg_part.get_payload(decode=True)

                else:

                    body = msg.get_payload(decode=True)

                for header_key in msg.keys():

                    header = header + "\n" + header_key + ": " + msg[header_key]

                email = {

                    'ID': msg['Message-ID'],

                    'subject' : decode_header(msg['subject'])[0][0],

                    'header'  : header,

                    'body'    : body

                    }

                print email

                yield email

    c.close()

    c.logout()

    exit()

def get_unparsed_emails_from_db(cursor, table):

    """ A generator for returning unparsed emails from the unnormalised DB."""

    # Not using the more practical """ string because we want to

    # be able to add easily the following line for testing:

    # "AND header like '%grnet%' AND body like '%9966%' "

    query = "SELECT Message_ID, Subject, Header, Body " + \

        "FROM " + table + " " + \

        "WHERE IS_STANDARDISED IS NULL " + \

        "ORDER BY Date_Stored ASC"

    cursor.execute(query)

    print "Found %d raw mails." % cursor.rowcount

    while (1):

        row = cursor.fetchone()

        if row is None:

            return

        email = {

            'ID': row[0],

            'subject' : row[1],

            'header'  : row[2],

            'body'    : row[3]

            }

        yield email

def get_partner_original_id(partner_id, original_id):

    """Returns the partner_original_id.

    Composes the partner_original_id field in the normalised database

    by using the rule: partner_id + '_' + original_id.

    """

    return partner_id + '_' + str(original_id)

def get_datetime_str(raw_datetime):

    """A utility function for returning datetime objects out of strings.

    Tries to parse them according to the following rules:

    %d-%m-%Y

    %Y-%m-%d

    """

    try:

        dt = datetime.strptime(raw_datetime, "%d-%m-%Y")

    except ValueError:

        try:

            dt = datetime.strptime(raw_datetime, "%Y-%m-%d")

        except ValueError:

            return None

    return dt.isoformat()

def insert_values(values, db, destination_table, connection, ttid):

    """Insert a normalised message to the database.

    Inserts the normalised message represented by the values dictionary

    to the database at the given destination_table. ttid is the

    message id in the unnormalised email table.

    """

    # Find messages previously normalised with the same NREN / ID combination

    # as this will be an update. The previous ones will have their

    # MESSAGE_RNUMBER attribute increased by 1.

    cursor = connection.cursor()

    original_id = int(values.get("ORIGINAL_ID", ""))

    partner_id = values.get("NREN", "")

    partner_original_id = get_partner_original_id(partner_id, original_id)

    query = """SELECT ENOC_ID, MESSAGE_RNUMBER

        FROM %s 

        WHERE PARTNER_ORIGINAL_ID = '%s'"""

    query = query % (destination_table, partner_original_id)

    cursor.execute(query)

    # We need to fetchall to ensure that we have all the data in a

    # snapshot in our program, so that the update that follows

    # work correctly.

    previous_messages = cursor.fetchall()

    for message in previous_messages:

        enoc_id = message[0]

        message_rnumber = message[1]

        update_stmt = 'UPDATE ' + destination_table + ' ' + \

            'SET MESSAGE_RNUMBER = MESSAGE_RNUMBER + 1 ' + \

            'WHERE ENOC_ID  = ' + str(enoc_id)

        cursor.execute(update_stmt)

    statement_pattern = "INSERT INTO " + destination_table + """

        (PARTNER_ORIGINAL_ID,

        UNNORMALISED_ID,

        TT_OPEN_DATETIME, 

        OPEN_ENGINEER,

        CLOSE_ENGINEER, 

        TT_LASTUPDATE_DATETIME,

        TT_TYPE,

        NETWORK_NODE,

        NETWORK_LINK_CIRCUIT,

        TT_TITLE, 

        TT_SHORT_DESCRIPTION,

        TT_PRIORITY,

        TT_STATUS,

        TT_CLOSE_DATETIME,

        TT_LONG_DESCRIPTION,

        ADDITIONAL_DATA, 

        HISTORY,

        MESSAGE_RNUMBER) 

        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 

        %s, %s, %s, %s, %s, %s, %s, %s)"""

    raw_datetime = values.get("TT_OPEN_DATETIME", "")

    tt_open_datetime = get_datetime_str(raw_datetime)

    if tt_open_datetime is None:

        return

    raw_datetime = values.get("TT_CLOSE_DATETIME", "")

    tt_close_datetime = get_datetime_str(raw_datetime)

    raw_datetime = values.get("TT_LASTUPDATE_DATETIME", "")

    tt_lastupdate_datetime = get_datetime_str(raw_datetime)

    statement_params = (

        get_partner_original_id(partner_id, original_id),

        ttid,

        tt_open_datetime,

        values.get("OPEN_ENGINEER", ""),

        values.get("CLOSE_ENGINEER", ""),

        tt_lastupdate_datetime,

        values.get("TT_TYPE", ""),

        values.get("NETWORK_NODE", ""),

        values.get("NETWORK_LINK_CIRCUIT", ""),

        values.get("TT_TITLE", ""),

        values.get("TT_SHORT_DESCRIPTION", ""),

        values.get("TT_PRIORITY", ""),

        values.get("TT_STATUS", ""),

        tt_close_datetime,

        values.get("TT_LONG_DESCRIPTION", ""),

        values.get("ADDITIONAL_DATA", ""),

        values.get("TT_HISTORY", ""),

        1)

    # Insert the new normalised ticket.

    cursor.execute(statement_pattern, statement_params)

    cursor.close()

    connection.commit()

def mark_as_parsed(connection, db, source_table, ttid):

    """Marks the row with key ttid as processed in the email table."""

    update_stmt = "UPDATE %s.%s SET IS_STANDARDISED = 1 " + \

        "WHERE %s.Message_ID = '%s'"

    update_stmt = update_stmt % (db, source_table, source_table, ttid)

    cursor = connection.cursor()

    cursor.execute(update_stmt)

    cursor.close()

    connection.commit()

def overwrite_print(output_str, overwrite):

    """Outputs output_str on top of the current output by overwrite

    characters. Returns the number of characters output."""

    sys.stdout.write("%s%s\r" % (output_str, " " * overwrite))

    return len(output_str)

def main(argv):

    parser = OptionParser()

    parser.add_option("-m", "--mail", dest="mail",

                      action="store_true",

                      default=False,

                      help="read tickets from IMAP server")

    parser.add_option("-f", "--frompass", dest="frompass",

                      help="password of trouble tickets DB or IMAP server",

                      metavar="PASSWORD")

    parser.add_option("-t", "--topass", dest="topass",

                      help="password of normalised DB",

                      metavar="PASSWORD")    

    (options, args) = parser.parse_args()

    if options.frompass is None:

        print "Password of trouble tickets database is missing\n"

        parser.print_help()

        exit(-1)

    if options.topass is None:

        print "Password of normalised database is missing\n"

        parser.print_help()

        exit(-1)

    TTConfig.TTdb['passwd'] = options.frompass

    TTConfig.TTdb_v2['passwd'] = options.topass

    normalised_db = TTConfig.TTdb_v2['db']

    try:

        db2_conn = MySQLdb.connect(**TTConfig.TTdb_v2)

        print "Successfully connected to database " + normalised_db

    except:

        print "Error connecting to database " + normalised_db

        exit()

    emails_table = TTConfig.configuration['table_raw_emails']

    ttp = TroubleTicketParser()

    mails_read = 0

    rows_inserted = 0

    overwrite = 0

    if options.mail is True:

        print "Trying to read unparsed tickets from IMAP server..."

        host = TTConfig.IMAP_server['host']

        user = TTConfig.IMAP_server['user']

        unparsed_emails = get_unparsed_emails_from_imap(host,

                                                        user,

                                                        options.frompass)

    else:

        print "Trying to read unparsed tickets from " \

            + str(TTConfig.TTdb['db']) + "..."

        raw_db = TTConfig.TTdb['db']

        try:

            db1_conn = MySQLdb.connect(**TTConfig.TTdb)

            db1_cursor = db1_conn.cursor()

            print "Successfully connected to database " + raw_db

        except:

            print "Error connecting to database " + raw_db

            exit()

        unparsed_emails = get_unparsed_emails_from_db(db1_cursor, emails_table)

    for raw_mail in unparsed_emails:

        mails_read = mails_read + 1

        num_found_keys = 0

        ttp.parse(raw_mail)

        parse_results = ttp.resultset

        num_found_keys = len(parse_results.keys())

        if ('ORIGINAL_ID' in parse_results

            and parse_results['ORIGINAL_ID'].isdigit()

            and num_found_keys >= TTConfig.configuration['min_keys_parsed']):

            ttid = raw_mail['ID']

            insert_values(parse_results,

                          TTConfig.TTdb_v2['db'],

                          TTConfig.configuration['stdTickets_table'],

                          db2_conn,

                          ttid)

            mark_as_parsed(db1_conn, raw_db, emails_table, ttid)

            rows_inserted = rows_inserted + 1

        overwrite = overwrite_print("Mails read: %d; normalised: %d"

                                    % (mails_read, rows_inserted),

                                    overwrite)

    db1_conn.close()            

    db2_conn.close()

if __name__ == "__main__":

    main(sys.argv[1:])