import mysql.connector
from itertools import groupby
from test import *


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 PNR,GROUP_CONCAT(TktNumber), 
GROUP_CONCAT(Origins),GROUP_CONCAT(Destinations),
GROUP_CONCAT(DatesOfDeparture),
GROUP_CONCAT(ClassOfBooking),SUM(Int_Dom) 
FROM meta.itineraryPlaces WHERE 
DatesOfArrival IS NOT NULL  AND DatesofDeparture > CONVERT(CURDATE(), CHAR(10))
and IFNULL(fareFiled,'0') < '1' and
PNR IN (SELECT PNR FROM meta.toShow WHERE toShow = 0)
GROUP BY PNR 
ORDER BY DatesOfDeparture ASC,PNR ASC
""")

rows = cursor.fetchall()
print len(rows)


for row in rows:
    pnr = str(row[0])
    cursor.execute("SELECT DISTINCT(Pax_Name) FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='"+pnr+"'")
    prows = cursor.fetchall()
    try:
        paxNumber = len(prows)
    except:
        paxNumber = 1
    print '#'*30, pnr 
    tktnumbers = str(row[1]).split(",")
    departures = str(row[2]).split(",")
    departures = [x[0] for x in groupby(departures)]
    arrivals = str(row[3]).split(",")
    arrivals = [x[0] for x in groupby(arrivals)]
    datesoftravel = str(row[4]).split(",")
    datesoftravel = [x[0] for x in groupby(datesoftravel)]
    classofbookings = str(row[5]).split(",")
    intdom = int(str(row[6]))
    if intdom > 0:
        intdom = "INT"
    else:
        intdom = "DOM"
    
    print "#"*20
    print departures
    print arrivals
    print datesoftravel
    print classofbookings
    print intdom
    
    tktnumber = tktnumbers[0]
    #2018-06-17T09:55:00
    datesofdeparture = []
    timewindows = []
    for dateoftravel in datesoftravel:
        hhmm = dateoftravel.split(" ")[1]
        datesofdeparture.append(dateoftravel.split(" ")[0]+"T"+ hhmm[:2] +":"+hhmm[2:]+":00")
        if intdom =="INT":
            timewindow = '4'
            hhMin = int(hhmm[:2])- 4
            mmMin = hhmm[2:]
            if hhMin < 0:
                hhMin = 0
                mmMin = 10
            if hhMin < 10:
                hhMin = "0"+str(hhMin)
            
            print "windowMin",str(hhMin) + str(mmMin)
            hhMax = int(hhmm[:2]) + 4
            mmMax = hhmm[2:]
            if hhMax > 24 :
                hhMax = 23
                mmMax = 55
            if hhMax < 10:
                hhMax = "0" + str(hhMax)
            print "windowMax",str(hhMax) + str(mmMax)
        else:
            timewindow = '1'
            hhMin = int(hhmm[:2])- 1
            mmMin = hhmm[2:]
            if hhMin < 0:
                hhMin = 0
                mmMin = 10
            if hhMin < 10:
                hhMin = "0"+str(hhMin)
            
            print "windowMin",str(hhMin) + str(mmMin)
            hhMax = int(hhmm[:2]) + 1
            mmMax = hhmm[2:]
            if hhMax > 24 :
                hhMax = 23
                mmMax = 55
            if hhMax < 10:
                hhMax = "0" + str(hhMax)
            print "windowMax",str(hhMax) + str(mmMax)

        timewindows.append(timewindow)
    
    cabinList = classofbookings
    
    
    
    try:
        file_Fare(pnr,tktnumber,datesofdeparture, timewindows, departures, arrivals, cabinList,False,paxNumber)
    except Exception,e:
        print "--",pnr,tktnumber,e
        print "--",pnr,tktnumber,datesofdeparture, timewindows, departures, arrivals, cabinList

    cursor.execute("UPDATE meta.itineraryPlaces SET fareFiled='1' WHERE PNR='"+pnr+"'")

cursor.close()
cnx.close()