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,CONCAT_WS(',',TktNumber), CONCAT_WS(',',Origins),CONCAT_WS(',',Destinations),CONCAT_WS(',',DatesOfDeparture),CONCAT_WS(',',ClassOfBooking),SUM(Int_Dom) FROM meta.itineraryPlaces WHERE DatesOfDeparture > '2018-06-28' and DatesOfArrival IS NOT NULL and IFNULL(fareDrop,'0') < '1' GROUP BY PNR ORDER BY DatesOfDeparture DESC,PNR ASC,TktNumber ASC ")

rows = cursor.fetchall()

cursor.close()
cnx.close()


for row in rows:
    pnr = str(row[0])
    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(",")
    classofbookings = str(row[5]).split(",")
    intdom = 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 = '2'
            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
    file_Fare(pnr,tktnumber,datesofdeparture, timewindows, departures, arrivals, cabinList,True)
    
    cnx = mysql.connector.connect(user='rt_bot', password='mp.work.',
                              host='tripnomicsdb.cloudapp.net',
                              database='meta')
    cnx.autocommit = True
    cursor = cnx.cursor()
    cursor.execute("UPDATE meta.itineraryPlaces SET fareDrop='1' WHERE PNR='"+pnr+"'")

    cursor.close()
    cnx.close()