import mysql.connector
from itertools import groupby
import json
import datetime
from time import strptime
import re

cnx = mysql.connector.connect(user='rt_bot', password='mp.work.',
                              host='tripnomicsdb.cloudapp.net',
                              database='Alternatefare')


cursor = cnx.cursor()
cnx.autocommit = True

cursor.execute("SELECT DISTINCT PNR FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR in (SELECT PNR FROM meta.toShow WHERE toShow ='0') AND Result='Fail'")

prows = cursor.fetchall()

pnrsToProcess = [ str(x[0]) for x in prows]


for pnr in pnrsToProcess:
    
    cursor.execute("SELECT DISTINCT TktNumber FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='"+pnr+"'")
    trows = cursor.fetchall()
    tktsToProcess = [str(x[0]) for x in trows]
    farePerTicket = dict()
    itinPerTicket = []
    cursor.execute("SELECT * FROM PNR_Trigger.audit_report2 WHERE PNR='"+pnr+"' ORDER BY total_fare ASC")
    tktsProcessed = []
    arows = cursor.fetchall()
    print arows
    fareType = ""
    
    for arow in arows:
        tkts = str(arow[2]).split(",")
        if tkts[0] not in tktsProcessed:
            farePerTicket[tkts[0]] = str(arow[8])
            itinPerTicket.append(str(arow[11]))
            fareType = str(arow[6])

        for tkt in tkts[1:]:
            if tkt not in tktsProcessed:
                cursor.execute("SELECT Tot_Amt FROM PNR_Trigger.PNR_Ticket_Details WHERE TktNumber ='"+tkt+"' GROUP BY TktNumber ORDER BY IFNULL(Tot_Amt,'0') DESC")
                trows = cursor.fetchall()
                farePerTicket[tkt] = str(trows[0][0])
        for tkt in tkts:
            if tkt not in tktsProcessed:
                tktsProcessed.append(tkt)
              
    for tkt in tktsToProcess:
        if tkt not in tktsProcessed:
            cursor.execute("SELECT Tot_Amt FROM PNR_Trigger.PNR_Ticket_Details WHERE TktNumber ='"+tkt+"' GROUP BY TktNumber ORDER BY IFNULL(Tot_Amt,'0') DESC")
            trows = cursor.fetchall()
            farePerTicket[tkt] = str(trows[0][0])
            cursor.execute("SELECT Itinerary FROM PNR_Trigger.PNR_Ticket_Details WHERE TktNumber ='"+tkt+"' GROUP BY Itinerary")
            irows = cursor.fetchall()
            irowText = [str(x[0]) for x in irows]
            for irow in irowText :
                itinPerTicket.append(irow) 
    
    itinDict = dict()
    print "##",itinPerTicket
    itinPerTicket = [ x for x in itinPerTicket if len(x)>0]
    for irow_ in itinPerTicket:        
        for irow in [x for x in irow_.split("\r\n") if len(x)>0]:
            parts = re.split('(\d+)',irow[3:])
            print pnr,irow
            parts = [irow[:2]] + parts
            # print "##",[x.strip() for x in parts]
            # print parts[5]
            try:
                key = str(strptime(parts[5].capitalize().strip().split(" ")[0],'%b').tm_mon)  +"-"+ parts[4] +"-"+ irow.split(" ")[-2] +"-"+ irow.split(" ")[-1]
                itinDict[key] = irow
            except:
                print "!",pnr,parts[5].capitalize()
        
    datesItin = itinDict.keys()

    datesItin.sort()

    itinOrder = [itinDict[x] for x in datesItin]
    itinOrder = [x[0] for x in groupby(itinOrder)]
    # print itinOrder
    print farePerTicket
    tsApprovedFare = sum([float(x) for x in farePerTicket.values() if x != ''])
    print pnr,tsApprovedFare, itinOrder, fareType
    cursor.execute("REPLACE INTO meta.TSApprovedFares(`PNR`,`TotalFare`,`Itinerary`,`fareType`) VALUES('"+str(pnr)+"','"+str(tsApprovedFare)+"','"+",".join(itinOrder)+"','"+str(fareType)+"');")

cursor.close()
cnx.close()