import mysql.connector
from itertools import groupby
import json
from datetime 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,SUM(IFNULL(Int_Dom,'0')) FROM meta.itineraryPlaces WHERE PNR IN (SELECT PNR FROM meta.toShow WHERE toShow='0') AND (segmentOrder IS NOT NULL) AND Origins IS NOT  NULL AND segmentOrder = segmentOrderFromSD GROUP BY PNR ORDER BY  `DatesOfDeparture` ASC,PNR ASC")
prows = cursor.fetchall()

# prows = [['OGYWZR','0']]

for prow in prows :
    pnr = str(prow[0])
    cursor.execute("SELECT PNR,GROUP_CONCAT( DISTINCT SegmentOrder SEPARATOR ','),GROUP_CONCAT(DISTINCT IFNULL( Connections,'')  SEPARATOR ','),GROUP_CONCAT(DISTINCT Origins),GROUP_CONCAT(DISTINCT Destinations),GROUP_CONCAT(DISTINCT DatesOfDeparture),GROUP_CONCAT(DISTINCT DatesOfArrival) FROM meta.itineraryPlaces WHERE PNR='"+pnr+"' AND DatesOfArrival IS NOT NULL GROUP BY PNR")
    rows = cursor.fetchall()
    legs =[]
    print cursor.statement
    for row in rows:
        try:
            pnr = str(row[0])
            itinsList = str(row[1].split(','))
            itinsList = [x[0] for x in groupby(itinsList)]
            itinString = ",".join(itinsList)
            print itinsList
            
            cmapsList = str(row[2].split(','))
            print cmapsList
            
            originsList = str(row[3]).split(',')
            originsList = [x[0] for x in groupby(originsList)]
            print originsList
            destinationsList = str(row[4]).split(',')
            destinationsList = [x[0] for x in groupby(destinationsList)]
            print destinationsList
            datesofdepartureList = str(row[5]).split(',')
            datesofdepartureList = [x[0] for x in groupby(datesofdepartureList)]
            print datesofdepartureList
            datesofarrivalList = str(row[6]).split(',')
            datesofarrivalList = [x[0] for x in groupby(datesofarrivalList)]
            print datesofarrivalList
            
            for j in range(len(originsList)):
                origins = originsList[j]
                destinations = destinationsList[j]
                datesofdeparture = datesofdepartureList[j]
                datesofarrival = datesofarrivalList[j]
                cmaps = cmapsList[j]
                itins = itinsList[j]
                for i in range(len(origins.split(","))):
                    leg = dict()
                    leg["origin"] = origins.split(",")[i]
                    leg["destination"] = destinations.split(",")[i]
                    try:
                        leg["depDateTime"] = datetime.strptime(datesofdeparture.split(",")[i],"%Y-%m-%d %H%M").strftime("%d%b %H%m").upper()
                    except:
                        leg["depDateTime"] = datetime.strptime(datesofdeparture.split(",")[i],"%Y-%m-%d ").strftime("%d%b %H%m").upper()
                    leg["arrDateTime"] = datetime.strptime(datesofarrival.split(",")[i],"%Y-%m-%d %H%M").strftime("%d%b %H%m").upper()
                    
                    try:
                        connectionString =  itinString.split(leg["origin"],1)[1].split(leg["destination"],1)[0]
                        itinString = itinString.split(leg["origin"],1)[1].split(leg["destination"],1)[1]
                    except:
                        itinString = ""
                    
                    Empty = itinString.replace(",","")
                    if len(Empty) == 0:
                        leg["connections"] = 'DIRECT'
                    else:
                        leg["connections"] = itinString
                    
                    cursor.execute("SELECT Airline FROM meta.segmentDetails WHERE PNR='"+pnr+"' AND Origin='"+leg["origin"]+"' AND Destination='"+leg["destination"]+"'")
                    result = cursor.fetchall()
                    try:
                        leg["airlines"]= str(result[0][0])
                    except:
                        leg["airlines"] = "--"
                    cursor.execute(" SELECT Country FROM Masters.Airport_Code_State WHERE Code='"+leg["origin"]+"'")
                    result = cursor.fetchall()
                    leg["originCountry"] = str(result[0][0])
                    cursor.execute(" SELECT Country FROM Masters.Airport_Code_State WHERE Code='"+leg["destination"]+"'")
                    result = cursor.fetchall()
                    leg["destinationCountry"] = str(result[0][0])
                    legs.append(leg)

            legString = "pnrObject = {legs:["
            for leg in legs:
                legString += json.dumps(leg) + ","
            legString += "]}"
            print legString

            with open("../../data/pnr/itinerary/"+pnr+".js","w") as outfile:
                outfile.write(legString)
        except Exception,e:
            print e