import requests
from lxml import etree
import datetime
import mysql.connector
from itertools import groupby

url = """https://apac.universal-api.travelport.com/B2BGateway/connect/uAPI/AirService"""

headers = {'Connection': 'close',
            'Accept-Encoding': 'gzip',
            'Content-Type': 'text/xml;charset=UTF-8',
            'Authorization': 'Basic VW5pdmVyc2FsIEFQSS91QVBJODM5NDQ0NTQwNS1hNzNlNTczZjo5bStGciVXNH0z'}
# 2018-05-01T01:45:00.000+05:30
def commit_alt_fare(tktnumber,
                    pnr,
                    timestamp, origin, destination, dep_date, marketing_airline_code, flight_number, dep_time, arr_time, fare, fare_basis, rbd, org, dest, fid, ddt, orderby):
    try:
        cnx = mysql.connector.connect(user='rt_bot', password='mp.work.',
                                      host='tripnomicsdb.cloudapp.net',
                                      database='Alternatefare')
        cursor = cnx.cursor()
        cnx.autocommit = True
        add_gen_row = """INSERT INTO Fare_Details(
            `TktNumber`,
            `PNR`,
            `Timestamp`,
            `GDS_Source`,
            `Origin`,
            `Destination`,
            `DateOfJourney`,
            `Airline`,
            `FlightNo`,
            `DepartureTime`,
            `ArrivalTime`,
            `Fare`,
            `FareBasis`,
            `FareClass`,
            `FareID`,
            `Dep_Date`
            ) """ + 'VALUES(' + '%s,' \
            * 15 + '%s)'
        gen_info_insert = tuple([
            tktnumber,
            pnr,
            timestamp,
            '1S',
            origin,
            destination,
            dep_date,
            marketing_airline_code,
            flight_number,
            dep_time,
            arr_time,
            fare,
            fare_basis,
            rbd,
            fid,
            ddt
        ])
        # if ((org == origin)&(dest == destination)):
        cursor.execute(add_gen_row, gen_info_insert)
        cnx.commit()
        # print "Committed"
        # else:
        #     print org + " " + dest + " vs " + origin + destination
        print cursor.statement
        cursor.close()
        cnx.close()

    except Exception, e:
        print e
        print "ERROR !!!!!! Can't commit alternate fare to database"
        return False
    return True



def indigoRequest(departures,arrivals,dateofdepartures,classofbookings):
    body = ""
    for i in range(len(departures)):
        departure = departures[i]
        arrival = arrivals[i]
        dateofdeparture = dateofdepartures[i]
        cabin = classofbookings[i]
        
        body += """<air:SearchAirLeg>
                <air:SearchOrigin>
                <com:Airport Code='"""+departure+"""' />
                </air:SearchOrigin>
                <air:SearchDestination>
                <com:Airport Code='"""+arrival+"""' />
                </air:SearchDestination>
                <air:SearchDepTime PreferredTime='"""+dateofdeparture+"""' />
            </air:SearchAirLeg>"""
    return body

def IndigoFileFare(pnr,tktnumber,departures,arrivals,dateofdepartures,classofbookings):
    requestBody = """<?xml version="1.0" encoding="utf-8"?>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Header />
    <soapenv:Body>
        <air:LowFareSearchReq xmlns:air="http://www.travelport.com/schema/air_v42_0" xmlns:com="http://www.travelport.com/schema/common_v42_0" AuthorizedBy="user" SolutionResult="true" TargetBranch='P3058954' TraceId="TESTPR">
        <com:BillingPointOfSaleInfo OriginApplication="UAPI" />
        """+indigoRequest(departures,arrivals,dateofdepartures,classofbookings)+"""
        <air:AirSearchModifiers>
            <air:PreferredProviders>
            <com:Provider Code="1G" />
            <com:Provider Code="ACH" />
            </air:PreferredProviders>
            <air:PermittedCarriers>
            <com:Carrier Code="6E" />
            </air:PermittedCarriers>
        </air:AirSearchModifiers>
        <com:SearchPassenger Code="ADT" />
        </air:LowFareSearchReq>
    </soapenv:Body>
    </soapenv:Envelope>
    """

    s = requests.session()
    s.keep_alive = False

    try:
        r = s.post(url, data=requestBody, headers=headers)
        responseXML = r.content
        print responseXML
        root = etree.XML(responseXML)
        AirReservations = root.xpath("//*[local-name()='AirPricingSolution']")
        index = 21;
        for AirReservation in AirReservations[:5]:
            fid = "Fare " + str(index)
            index = index + 1
            fare = AirReservation.get("TotalPrice")
            marketing_airline_code = '6E'
            flights = AirReservation.xpath(".//*[local-name()='AirSegmentRef']")
            FlightNumbers = ''
            print len(flights)
            for flight in flights:
                flightKey = flight.get("Key")
                flight = root.xpath("//*[local-name()='AirSegment'][@Key='" +flightKey + "']")[0]
                flight_number = flight.get("FlightNumber")
                origin = flight.get("Origin")
                destination = flight.get("Destination")
                departureTime = flight.get("DepartureTime")
                depdate = datetime.datetime.strptime(departureTime.split("+")[0].replace(".000",''), '%Y-%m-%dT%H:%M:%S')
                dep_date = depdate.strftime('%Y-%m-%d')
                dep_time = depdate.strftime('%H%M')
                arrivalTime = flight.get("ArrivalTime")
                arrdate = datetime.datetime.strptime(arrivalTime.split("+")[0].replace(".000",''), '%Y-%m-%dT%H:%M:%S')
                arr_time = arrdate.strftime('%H%M')
                timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                depdatetime = dep_date + " " + dep_time
                depdatetime = datetime.datetime.strptime(
                depdatetime, "%Y-%m-%d %H%M")
                depdatetime = depdatetime.strftime("%Y-%m-%d %H:%M:%S")
                ddt = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                commit_alt_fare(tktnumber,
                            pnr,
                            timestamp, origin, destination, dep_date, marketing_airline_code,
                            flight_number, dep_time, arr_time, fare, "", "X", "", "", fid, ddt, "")

    except Exception,e:
        print pnr,tktnumber,e


#                     timestamp, 
# fare_basis, rbd, 
#   orderby


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 > CONVERT(CURDATE(), CHAR(10)) and DatesOfArrival IS NOT NULL and IFNULL(fareFiled,'0') < '2' and IFNULL(Int_Dom,'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
    IndigoFileFare(pnr,tktnumber,departures,arrivals,datesofdeparture,classofbookings)
    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 fareFiled='2' WHERE PNR='"+pnr+"'")

    cursor.close()
    cnx.close()