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

import getopt

from datetime import datetime

import TTConfig

import MySQLdb

from TroubleTicketParser import *

def get_unparsed_emails(cursor, table):

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

        FROM %s 

        WHERE IS_STANDARDISED IS NULL"""

#        "ORDER BY Date_Stored""" 

    cursor.execute(query % (table))

    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_datetime_str(raw_datetime):

    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, cursor, ttid):

    # 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.

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

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

    query = """SELECT ENOC_ID, MESSAGE_RNUMBER

        FROM %s 

        WHERE ORIGINAL_ID = %d AND PARTNER_ID = '%s'"""

    query = query % (destination_table, original_id, partner_id)

    cursor.execute(query)

    while (1):

        row = cursor.fetchone()

        if row == None:

            break

        enoc_id = row[0]

        message_rnumber = row[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 + """

        (ORIGINAL_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)"""

    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 = (

        original_id,

        # partner_id,

        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)

def get_query_mark_as_parsed(row_id, db, source_table):

    return str("UPDATE `%s`.`%s`  SET `IS_STANDARDISED` = '1' WHERE `%s`.`ID` =%d LIMIT 1 ;" % (str(db), str(source_table), str(source_table), row_id))

def overwrite_print(output_str, overwrite):

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

    return len(output_str)

def usage():

    print "python TroubleTicket_db2db.py -passwd1=password1 -passwd2=password2"

def main(argv):

    try:

        opts, args = getopt.getopt(argv, "", ["passwd1=", "passwd2="])

    except getopt.GetoptError:

        usage()

        sys.exit(2)

    for o, a in opts:

        if o == "--passwd1":

            TTConfig.TTdb['passwd'] = a

        elif o == "--passwd2":

            TTConfig.TTdb_v2['passwd'] = a

        else:

            assert False, "unhandled option: " + o

    try:

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

        db1_cursor = db1_conn.cursor()

    except:

        print "Error connecting to database " + str(TTConfig.TTdb['db'])

        print TTConfig.TTdb

        exit()

    try:

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

        db2_cursor = db2_conn.cursor()

    except:

        print "Error connecting to database " + str(TTConfig.TTdb_v2['db'])

        exit()

    print "Successfully connected to databases."

    print "Trying to read unparsed tickets from " + str(TTConfig.TTdb['db']) \

        + "..."

    emails_table = TTConfig.configuration['table_raw_emails']

    ttp = TroubleTicketParser()

    mails_read = 0

    rows_inserted = 0

    overwrite = 0

    for raw_mail in get_unparsed_emails(db1_cursor, emails_table):

        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 not 'ORIGINAL_ID' in parse_results:

            continue

        if not parse_results['ORIGINAL_ID'].isdigit():

            continue

        if num_found_keys >= TTConfig.configuration['min_keys_parsed']:

            insert_values(parse_results,

                          TTConfig.TTdb_v2['db'],

                          TTConfig.configuration['stdTickets_table'],

                          db2_cursor,

                          raw_mail['ID'])

            rows_inserted = rows_inserted + 1

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

                                    % (mails_read, rows_inserted),

                                    overwrite)

    db1_conn.close()            

    db2_conn.commit()

    db2_conn.close()

if __name__ == "__main__":

    main(sys.argv[1:])