import mysql.connector
from itertools import groupby
import json
import datetime

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 PNR,GROUP_CONCAT(TktNumber),GROUP_CONCAT(IF(ClassOfBooking ='','ECONOMY',ClassOfBooking)) FROM meta.itineraryPlaces WHERE PNR IN (SELECT PNR FROM meta.toShow WHERE toShow='0') GROUP BY PNR")

rows = cursor.fetchall()
print "--",len(rows)
for row in rows:
    try:
        pnr = str(row[0])
        ticketString = "pnrObject = "
        tickets = []
        tktnumbers = str(row[1]).split(",")
        pnrTicketJson = dict()
        pnrTicketJson["ticketList"] = dict()
        for i in range(len(tktnumbers)):
            tktnumber = tktnumbers[i]
            ticket = dict()
            ticket["ticketNumber"] = tktnumber
            ticket["class"] = str(row[2]).split(",")[i]
            if ticket["class"] == "":
                ticket["class"] = 'ECONOMY'
            ticket["passengerType"] = "ADT"
            cursor.execute("""SELECT 
            Pax_Name,IFNULL(Refundable,'No'),
            Tot_Amt,Itinerary,
            Base_Fare_amt,Fare_Basis,
            Baggage,
            IFNULL(AFQ,0),IFNULL(HFQ,0),
            IFNULL(ARC,'') FROM PNR_Trigger.PNR_Ticket_Details WHERE TktNumber='""" + tktnumber + """' AND CouponNum = '1' LIMIT 1""")
            trow = cursor.fetchall()[0]
            ticket["passengerName"] = str(trow[0])
            
            if str(trow[1]) == 'Yes':
                ticket["fareType"] = "REFUNDABLE"
            else:
                ticket["fareType"] = "NONREFUNDABLE"
            ticket["amt"] = "INR " + str(trow[2])
            cursor.execute("SELECT GROUP_CONCAT(DISTINCT IFNULL( Itinerary,'') SEPARATOR '<br />') FROM PNR_Trigger.PNR_Ticket_Details WHERE TktNumber='"+tktnumber+"'  GROUP BY TktNumber") 
            print "--", cursor.statement
            itinrows = cursor.fetchall()
            itinrow = str(itinrows[0][0])  
            itinrow = [x[0] for x in groupby(itinrow.split("<br />"))]
            itinrow = "<br />".join(itinrow)
            print "--", tktnumber,itinrow
            ticket["itin"] = itinrow
            ticket["baseFare"] = str(trow[4])
            ticket["farebasis"] = str(trow[5])
            ticket["baggage"] = str(trow[6])
            cursor.execute("SELECT Connections,segmentOrder FROM meta.itineraryPlaces WHERE TktNumber = '"+tktnumber+"'")
            ttrows = cursor.fetchall()
            connectionmap = ttrows[0][0].split(",")
            segmentOrder = ttrows[0][1].split(",")
            itinD = [x[1] for x in zip(connectionmap,segmentOrder) if x[0] == '0']
            itinDString = ",".join(itinD)
            ticket["marker"] = itinDString
            afq = str(trow[7])
            hfq = str(trow[8])
            ticket["afq"] = "AFQ " + afq + "/ " + "HFQ " + hfq
            ticket["arc"] = "ARC " + str(trow[9])
            try:
                pnrTicketJson["ticketList"][str(trow[0])]["tickets"].append(ticket)
            except:
                pnrTicketJson["ticketList"][str(trow[0])] = dict()
                pnrTicketJson["ticketList"][str(trow[0])]["tickets"] = []
                pnrTicketJson["ticketList"][str(trow[0])]["tickets"].append(ticket)
            

        ticketString += json.dumps(pnrTicketJson) 
        
        with open("../../data/pnr/ticketInfo/"+pnr+".js","w") as outfile:
            outfile.write(ticketString)
    except:
        pass