def ReadDeparture(conn, company_code, class_code, flight_number, flight_date):
"""Read departure and arrival city codes."""
RcSql = \
"""SELECT departure_airport,arrival_airport, city_pair,
departure_terminal, arrival_terminal,
departure_time, arrival_time
FROM flight_segment_dates
WHERE flight_number='%s' AND flight_date='%s'""" \
% (flight_number, flight_date.strftime("%Y-%m-%d"))
logger.debug(RcSql)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(RcSql)
n = 0
flights = []
for row in cur:
fltinfo = FlightData(class_code, flight_number, flight_date,
row['departure_time'], row['arrival_time'],
row['departure_airport'], row['arrival_airport'],
row['departure_terminal'],
row['arrival_terminal'], row['city_pair'],
company_code)
flights.append(fltinfo)
n += 1
logger.info("\tDepart %s arrive %s city pair %d" %
(fltinfo.departure_airport, fltinfo.arrival_airport,
fltinfo.city_pair))
return flights
def ReadFlightsDate(conn,
dts,
ndays,
departure_airport,
arrival_airport,
code_share=False,
class_code='Y',
company_code='ZZ'):
"""Flights for date."""
logger.info("Flights for date %s [flight_segment_dates]" %
dts.strftime("%Y-%m-%d"))
if code_share:
logger.info("With codeshare")
fdate = dts.strftime("%Y-%m-%d")
FsegSql=\
"SELECT DISTINCT flight_number, flight_date, departure_airport, arrival_airport, city_pair, departure_time, arrival_time, " \
"aircraft_code, schedule_period_no" \
" FROM flight_segment_dates" \
" WHERE (substring(flight_number from 1 for 2) = '%s' OR substring(flight_number from 1 for 3) = '%s')" \
" AND ( (flight_date = '%s' AND %d = 0) OR (flight_date >= DATE('%s') AND flight_date < (DATE('%s') + %d) AND %d > 0 ))" \
% (company_code, company_code, fdate, ndays, fdate, fdate, ndays, ndays)
if departure_airport is not None and arrival_airport is not None:
FsegSql += \
" AND departure_airport='%s' AND arrival_airport='%s'" % (departure_airport, arrival_airport)
FsegSql += \
" ORDER BY flight_date, departure_time"
logger.debug(FsegSql)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(FsegSql)
flights = []
for row in cur:
logger.info("Flight %-6s date %s depart %s arrive %s city pair %3d" \
% (row['flight_number'], row['flight_date'],
row['departure_airport'], row['arrival_airport'],
int(row['city_pair'])))
flight_number = row['flight_number']
departure_date = row['flight_date']
departure_time = row['departure_time']
#arrival_date = ReadDate(row['arrival_date'])
arrival_time = row['arrival_time']
if code_share:
cs = ReadCodeShare(conn, flight_number, fdate)
else:
cs = None
flights.append(FlightData(class_code, flight_number, departure_date, departure_time, arrival_time, \
row['departure_airport'], row['arrival_airport'], ' ', ' ',
int(row['city_pair']), aircraft_code=row['aircraft_code'],
schedule_period_no=row['schedule_period_no'], codeshare=cs))
logger.info("Found %d flights for date %s" %
(len(flights), dts.strftime("%Y-%m-%d")))
return flights
def ReadFlight(conn, flight_number, dts, class_code='Y'):
"""Read flight."""
logger.info("Read flight %s for date %s [flight_segment_dates]" %
(flight_number, dts.strftime("%Y-%m-%d")))
fdate = dts.strftime("%Y-%m-%d")
FsegSql=\
"SELECT flight_number,flight_date,departure_airport,arrival_airport," \
"city_pair, departure_time, arrival_time, aircraft_code," \
"schedule_period_no" \
" FROM flight_segment_dates" \
" WHERE flight_number = '%s'" \
" AND flight_date = '%s'" \
% (flight_number, fdate)
FsegSql += \
" ORDER BY flight_date, departure_time"
logger.debug(FsegSql)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(FsegSql)
flights = []
for row in cur:
flight_number = row['flight_number']
departure_date = row['flight_date']
departure_time = row['departure_time']
arrival_time = row['arrival_time']
logger.info("Flight %-6s date %s depart %s arrive %s city pair %3d" \
% (flight_number, row['flight_date'], row['departure_airport'], row['arrival_airport'], int(row['city_pair'])))
flights.append(
FlightData(class_code,
flight_number,
departure_date,
departure_time,
arrival_time,
row['departure_airport'],
row['arrival_airport'],
int(row['city_pair']),
aircraft_code=row['aircraft_code'],
schedule_period_no=row['schedule_period_no']))
logger.info(
"Found %d flights for date %s" %
(len(flights), dts.strftime("%Y-%m-%d")), 1)
if len(flights):
return flights[0]
else:
return None
def ReadFlightsDateLeg(conn,
dts,
ndays,
departure_airport,
arrival_airport,
class_code='Y',
company_code='ZZ'):
"""Flights for date."""
logger.info(
"Flights for date %s [flight_segment_dates]" %
dts.strftime("%Y-%m-%d"), 1)
fdate = dts.strftime("%Y-%m-%d")
FsegSql = \
"SELECT trim(flight_number) fn, flight_date, board_date, departure_time, departure_airport, arrival_airport, leg_number, schedule_period_no" \
" FROM flight_date_leg" \
" WHERE (flight_number[1,2] = '%s' OR flight_number[1,3] = '%s')" \
" AND board_date = '%s'" \
% (company_code, company_code, fdate)
if departure_airport is not None and arrival_airport is not None:
FsegSql += \
" AND departure_airport='%s' AND arrival_airport='%s'" % (departure_airport, arrival_airport)
FsegSql += \
" ORDER BY fn, flight_date"
logger.debug(FsegSql)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(FsegSql)
flights = []
city_pair = 0
arrival_airport = ''
aircraft_code = ''
arrival_time = None
for row in cur:
flight_number = row['fn']
departure_date = row['flight_date']
departure_time = row['departure_time']
logger.info("Flight %-6s date %s depart %s arrive %s" \
% (flight_number, departure_date, row['departure_airport'], row['arrival_airport']))
flights.append(FlightData(class_code, flight_number, departure_date, departure_time, arrival_time, \
row['departure_airport'], row['arrival_airport'], city_pair, aircraft_code,
schedule_period_no=row['schedule_period_no']))
logger.info("Found %d flights for date %s" %
(len(flights), dts.strftime("%Y-%m-%d")))
return flights
def ReadFlightDeparture(conn, class_code, flight_number, flight_date):
"""Read data for flight."""
logger.info("Read data for flight %s date %s [flight_segment_dates]" %
(flight_number, flight_date.strftime("%Y-%m-%d")))
RcSql = \
"""SELECT departure_airport,arrival_airport, city_pair,
departure_time,arrival_time,
departure_terminal, arrival_terminal,
flgt_sched_status,schedule_period_no
FROM flight_segment_dates
WHERE flight_number='%s' AND flight_date='%s'""" \
% (flight_number, flight_date.strftime("%Y-%m-%d"))
logger.debug(RcSql)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(RcSql)
n = 0
flight = None
for row in cur:
departure_airport = str(row['departure_airport'] or '')
arrival_airport = str(row['arrival_airport'] or '')
departure_terminal = str(row['departure_terminal'] or '')
arrival_terminal = str(row['arrival_terminal'] or '')
city_pair = int(row['city_pair'] or 0)
departure_time = row['departure_time']
arrival_time = row['arrival_time']
n += 1
logger.info("Flight %s date %s depart %s %s arrive %s %s status %s" \
% (flight_number, flight_date.strftime("%Y-%m-%d"), \
departure_airport, departure_time, arrival_airport, arrival_time,
str(row['flgt_sched_status'] or '?')))
flight = FlightData(class_code,
flight_number,
flight_date,
departure_time,
arrival_time,
departure_airport,
arrival_airport,
departure_terminal,
arrival_terminal,
city_pair,
schedule_period_no=int(row['schedule_period_no']
or 0))
return n, flight
def get_avail_flights(conn, fdate1, fdate2, city_pair, departure_airport,
arrival_airport, selling_class, company_code):
logger.info(
"Available flights depart %s arrive %s (%d) start %s end %s class %s company %s"
% (departure_airport, arrival_airport, city_pair, fdate1, fdate2,
selling_class, company_code))
AvlSql = \
"""
SELECT DISTINCT fsd.flight_number, fsd.board_date,
fsd.departure_time, fsd.city_pair,
isg.departure_city, isg.arrival_city,
fsd.departure_airport, fsd.arrival_airport,
fsd.flight_path_code, fsd.arrival_time,
fsd.date_change_ind, fsd.departure_terminal,
fsd.arrival_terminal, fsd.no_of_stops,
fsd.aircraft_code, fsd.flight_date,
sc.cabin_code, isg.leg_number,
isg.city_pair, isg.selling_class,
isg.limit_sale_level, isg.seat_capacity,
isg.overbooking_percnt, isg.nett_sngl_sold,
isg.nett_group_sold, isg.nett_nrev_sold,
isg.segm_sngl_sold, isg.segm_group_sold,
isg.segm_nrev_sold, isg.seat_protect_level,
isg.display_priority, fsd.flgt_sched_status,
isg.segment_closed_flag, fsd.flight_closed_flag,
fsd.flight_brdng_flag, isg.ob_profile_no,
fp.via_cities, fp.schedule_period_no
FROM flight_segment_dates fsd, flight_periods fp, inventry_segment isg, selling_conf sc
WHERE fsd.board_date >= '%s'
AND fsd.board_date <= '%s'
AND isg.flight_number = fsd.flight_number
AND isg.flight_date = fsd.flight_date
AND fsd.city_pair = '%s'
AND fsd.departure_airport = '%s'
AND fsd.arrival_airport = '%s'
AND isg.selling_class = '%s'
AND sc.company_code = '%s'
AND fp.flight_number = fsd.flight_number
AND fp.schedule_period_no = fsd.schedule_period_no
AND fsd.flight_number NOT IN
(
SELECT flight_number FROM flight_locked AS flck
WHERE flck.invalid_time IS NULL AND flck.flight_number = fsd.flight_number AND flck.flight_date = fsd.board_date
)
AND fsd.flgt_sched_status IN ('A', 'D', 'M', 'U', 'R')
AND fp.flgt_sched_status IN ('A', 'D', 'M', 'U', 'R')""" \
% (fdate1, fdate2, city_pair, departure_airport, arrival_airport, selling_class, company_code)
logger.debug("%s" % AvlSql)
cur = conn.cursor()
cur.execute(AvlSql)
aircraft_code = ''
cs = []
flights = []
logger.debug("Selected %d row(s)" % cur.rowcount)
for row in cur:
class_code = row[16]
flight_number = row[0]
departure_date = row[1]
departure_time = row[2]
arrival_time = row[9]
departure_airport = row[6]
arrival_airport = row[7]
city_pair_number = int(row[3])
schedule_period_no = int(row[37])
fd = FlightData(class_code, flight_number, departure_date,
departure_time, arrival_time, departure_airport,
arrival_airport, None, None, city_pair, company_code,
aircraft_code, schedule_period_no, cs)
flights.append(fd)
cur.close()
return flights
def ReadAvailDb(conn, company_code, lboard_date, city_pair_no, depr_airport,
arrv_airport):
logger.info("Available flights depart %s arrive %s date %s" %
(depr_airport, arrv_airport, lboard_date))
if city_pair_no is None:
city_pair_no = GetCityPair(conn, departAirport, arriveAirport)
AvlSql = """
SELECT fsd.board_date, fsd.flight_number,
fsd.city_pair, sc.cabin_code,
isg.leg_number, fsd.flight_date,
isg.city_pair, isg.departure_city,
isg.arrival_city,
fsd.departure_airport, fsd.arrival_airport,
fsd.flight_path_code, fsd.departure_time,
fsd.arrival_time, fsd.date_change_ind,
fsd.departure_terminal, fsd.arrival_terminal,
fsd.no_of_stops, fsd.aircraft_code,
isg.selling_class,
isg.limit_sale_level, isg.seat_capacity,
isg.overbooking_percnt, isg.nett_sngl_sold,
isg.nett_group_sold, isg.nett_nrev_sold,
isg.seat_protect_level, isg.display_priority,
fsd.flgt_sched_status, isg.segment_closed_flag,
fsd.flight_closed_flag, fsd.flight_brdng_flag,
isg.ob_profile_no, fsd.schedule_period_no
FROM flight_segment_dates fsd, inventry_segment isg, selling_conf sc
WHERE fsd.board_date = '%s'
AND isg.flight_number = fsd.flight_number
AND isg.flight_date = fsd.flight_date
AND fsd.city_pair = %d
AND fsd.departure_airport = '%s'
AND fsd.arrival_airport = '%s'
AND isg.selling_class = sc.selling_class
AND sc.company_code = '%s'""" \
% (lboard_date, city_pair_no, depr_airport, arrv_airport, company_code)
logger.debug("%s" % AvlSql)
cur = conn.cursor()
cur.execute(AvlSql)
logger.debug("Selected %d row(s)" % cur.rowcount)
flights = []
for row in cur:
logger.info("Flight %s date %s cabin %s depart %s arrive %s class %s schedule %d" \
% (row[1], row[0], row[3], str(row[12])[0:5], str(row[13])[0:5], row[19], row[33]))
class_code = row[19]
flight_number = row[1]
departure_date = row[0]
departure_time = row[12]
arrival_time = row[13]
departure_airport = row[9]
arrival_airport = row[10]
departure_terminal = row[15]
arrival_terminal = row[16]
aircraft_code = row[18]
city_pair_number = int(row[2])
schedule_period_no = int(row[33])
fd = FlightData(class_code, flight_number, departure_date,
departure_time, arrival_time, departure_airport,
arrival_airport, None, None, city_pair_number,
company_code, aircraft_code, schedule_period_no, None)
flights.append(fd)
cur.close()
return flights
def ReadFlights(conn,
flight_number,
dt1,
dt2,
departure_airport,
arrival_airport,
code_share=False,
class_code='Y',
company_code='ZZ'):
"""Read flights."""
FsegSql=\
"SELECT flight_number,flight_date,departure_airport,arrival_airport," \
"city_pair,departure_time,arrival_time,aircraft_code,schedule_period_no" \
" FROM flight_segment_dates WHERE 1=1"
if flight_number is not None:
logger.info("Flight number %s" % flight_number)
FsegSql += \
" AND flight_number='%s'" \
% (flight_number)
if dt1 is not None and dt2 is not None:
logger.info("Read flights from date %s to %s [flight_segment_dates]" % \
(dt1.strftime("%Y-%m-%d"), dt2.strftime("%Y-%m-%d")))
FsegSql += \
" AND flight_date BETWEEN '%s' AND '%s'" \
% (dt1.strftime("%Y-%m-%d"), dt2.strftime("%Y-%m-%d"))
elif dt1 is not None:
logger.info("Read flights for date %s [flight_segment_dates]" %
dt1.strftime("%Y-%m-%d"))
FsegSql += \
" AND flight_date = '%s'" % dt1.strftime("%Y-%m-%d")
if departure_airport is not None and arrival_airport is not None:
FsegSql += \
" AND departure_airport='%s' AND arrival_airport='%s'" \
% (departure_airport, arrival_airport)
FsegSql += \
" ORDER BY flight_date"
if code_share:
logger.info("With codeshare")
logger.debug(FsegSql)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(FsegSql)
flights = []
for row in cur:
logger.info("Flight %-6s date %s depart %s arrive %s city pair %3d" \
% (row['flight_number'], row['flight_date'], row['departure_airport'], row['arrival_airport'], int(row['city_pair'])))
flight_number = row['flight_number']
departure_date = row['flight_date']
departure_time = int(row['departure_time'])
arrival_time = int(row['arrival_time'])
if code_share:
cs = ReadCodeShare(conn, flight_number,
departure_date.strftime('%m/%d/%Y'))
else:
cs = None
flights.append(FlightData(class_code, flight_number, departure_date, departure_time, arrival_time, \
row['departure_airport'], row['arrival_airport'], int(row['city_pair']), aircraft_code=row['aircraft_code'],
schedule_period_no=row['schedule_period_no'], codeshare=cs))
#logger.info("Found %d flights for date %s" % (len(flights), dt1.strftime("%Y-%m-%d")))
return flights