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

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

cursor.execute("SELECT DISTINCT City_Code FROM Masters.Airports")
CityCode = []
for row in cursor.fetchall():
    CityCode.append(str(row[0]))

saved_column = CityCode


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


cursor = cnx.cursor()

cursor.execute("SELECT PNR,SUM(IFNULL(Int_Dom,'0')) FROM meta.itineraryPlaces WHERE (segmentOrder IS NOT NULL) AND Origins IS NOT  NULL AND segmentOrder = segmentOrderFromSD AND PNR IN (SELECT PNR FROM meta.toShow WHERE toShow ='0') GROUP BY PNR ORDER BY  DATE(`Timestamp`) DESC,Timestamp DESC,PNR ASC, TktNumber ASC")

rows = cursor.fetchall()

itindict = dict()
pnrList = "pnrList = ["
print "-- Processing", len(list(set(rows)))
for row in list(set(rows)):
    pnr = row[0]
    # print pnr
    int_dom = str(row[1]).upper()
    if float(int_dom) > 0:
        int_dom = "INTERNATIONAL"
    else:
        int_dom = "DOMESTIC"
    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'
        cursor.execute(
            "SELECT pnr,sum(total_fare) FROM PNR_Trigger.audit_report2 WHERE PNR='" + pnr + "' GROUP BY PNR")
        srows = cursor.fetchall()
        fileFare = srows[0][1]
        savings = str(float(tot_amt) - float(fileFare))
        cursor.execute(
            "SELECT pnr,CONCAT(IFNULL(Error_Codes,'')) FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='" + pnr + "' GROUP BY PNR")
        erows = cursor.fetchall()
        compliance = "false"
        error_codes = ""

        for erow in erows:
            error_codes += erow[1]

        errorCodes = error_codes.split(",")
        errorCodes = set(errorCodes)
        errorCodes = list(errorCodes)
        errorCodeText = ",".join(errorCodes)
        try:
            percent = str(int(100 * float(savings) / float(tot_amt)))
        except:
            percent = "0"

    else:
        result = 'pass'
        savings = "0"
        errorCodeText = "0"
        percent = "0"
        compliance = "true"
    cursor.execute("SELECT GROUP_CONCAT(segmentOrder SEPARATOR '<br />') FROM meta.itineraryPlaces WHERE PNR='"+pnr+"' GROUP BY PNR ORDER BY DatesOfDeparture")
    print "--", cursor.statement
    irows = cursor.fetchall()
    itinrow = str(irows[0][0])
    itinrow = [x[0] for x in groupby(itinrow.split("<br />"))]
    itinrow = ",".join(itinrow)
    itin = itinrow.split(",")
    itin = [x[0] for x in groupby(itin)]
    print "--",itin
    pnrdict = dict()
    pnrdict['pnr'] = pnr
    pnrdict['compliance'] = result
    pnrdict['ticketedAmount'] = "INR " + str(tot_amt)
    pnrdict['dom'] = int_dom
    pnrdict['savings'] = "INR " + str(savings)
    pnrdict['percentSavings'] = percent
    pnrdict['itinerary'] = ",".join(itin)
    print "REPLACE INTO `meta`.`pnrList` (`PNR`, `Itinerary`, `Compliance`, `Dom`, `percentSavings`, `ticketedAmount`, `savings`) VALUES ('"+pnrdict['pnr']+"', '"+pnrdict['itinerary']+"', '"+pnrdict['compliance']+"', '"+pnrdict['dom']+"', '"+ pnrdict['percentSavings'] +"', '"+pnrdict['ticketedAmount']+"', '"+pnrdict['savings']+"');"
    # pnrList += json.dumps(pnrdict) + ","


# pnrList += "]"

# with open("../../data/pnrList.js", 'w', ) as outfile:
#     outfile.write(pnrList)

cursor.close()
cnx.close()