import json 
import mysql.connector
import re
import pandas as pd
from itertools import groupby

df = pd.read_csv("../airports.csv")
saved_column = df.iata_code
countries = df.iso_country
saved_column = list(set(saved_column))

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


cursor = cnx.cursor()

cursor.execute("SELECT DISTINCT PNR FROM meta.itineraryPlaces WHERE PNR IN (SELECT PNR FROM meta.toShow WHERE toShow = 0)  ORDER BY PNR ASC")

rows = cursor.fetchall()
for row in list(set(rows)):
    
    pnr = row[0]
    print pnr
    cursor.execute("SELECT pnr,sum(Tot_Amt) FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='"+pnr+"' GROUP BY PNR")
    prows = cursor.fetchall()
    tot_amt = prows[0][1]
    cursor.execute("SELECT pnr,Result FROM PNR_Trigger.PNR_Ticket_Details WHERE Result = 'Fail' and PNR='"+pnr+"'")
    rrows = cursor.fetchall()
    if len(rrows) > 0:
        result = 'fail'
        compliance = "false"
    else:
        result = 'pass'    
        compliance = "true"

    detaildict = dict()
    detaildict["obj"] = dict()
    detaildict["obj"]["pnr"] = pnr
    detaildict["obj"]["complianceText"] = compliance
    detaildict["obj"]["ticketedAmt"] = "INR "+str(tot_amt)
    
    pnrDetail = "pnrObject ="+  json.dumps(detaildict) 
    

    with open('../../data/pnr/pnrDetails/'+pnr+'.js', 'w') as outfile:
        outfile.write(pnrDetail)