def menu(email, CONN_STRING):
# create view available_flights which shows flights information
# with empty seats if view does not exist
sql = """
create view available_flights(flightno, dep_date, src,dst,
dep_time,arr_time,fare,seats, price)
as
select f.flightno, sf.dep_date, f.src, f.dst,
f.dep_time+(trunc(sf.dep_date)-trunc(f.dep_time)),
f.dep_time+(trunc(sf.dep_date)-trunc(f.dep_time))+
(f.est_dur/60+a2.tzone-a1.tzone)/24, fa.fare,
fa.limit-count(tno), fa.price
from flights f, flight_fares fa, sch_flights sf, bookings b,
airports a1, airports a2
where f.flightno=sf.flightno and f.flightno=fa.flightno
and f.src=a1.acode and f.dst=a2.acode
and fa.flightno=b.flightno(+) and fa.fare=b.fare(+)
and sf.dep_date=b.dep_date(+)
group by f.flightno, sf.dep_date, f.src, f.dst, f.dep_time,
f.est_dur,a2.tzone, a1.tzone, fa.fare, fa.limit,
fa.price
having fa.limit-count(tno) > 0
"""
try:
sqlWithNoReturn(sql, CONN_STRING)
except:
pass
# create view good_connections gives good connection flights
# information if view does not exist
sql = """
create view good_connections (src,dst,dep_time,arr_time,
dep_date,flightno1,flightno2,stops,layover,price,
seats1, seats2, fare1, fare2)
as
select a1.src, a2.dst, a1.dep_time, a2.arr_time,
a1.dep_date, a1.flightno, a2.flightno, 2 stops,
a2.dep_time-a1.arr_time, min(a1.price+a2.price),
a1.seats as seats1, a2.seats as seats2,
a1.fare as fare1, a2.fare as fare2
from available_flights a1, available_flights a2
where a1.dst=a2.src and a1.arr_time +1.5/24 <=a2.dep_time
and a1.arr_time +5/24 >=a2.dep_time
group by a1.src, a2.dst, a1.dep_date, a1.flightno, a2.flightno,
a2.dep_time, a1.arr_time, a1.dep_time, a2.arr_time,
a1.seats, a2.seats, a1.fare, a2.fare
"""
try:
sqlWithNoReturn(sql, CONN_STRING)
except:
pass
# print options
print("===============================================")
print("1. Search For And Book Flights")
print("2. List Or Cancel Existing Bookings")
print("3. Logout")
print("4. Search For Round Trip Flights")
# check if the user is an airline agent
sql = "select * from airline_agents where email = '{0}'".format(email)
isAgent = sqlWithReturn(sql, CONN_STRING)
# if user is an agent, provides two more options of
# record a flight departure or arrival
if len(isAgent) > 0:
print("5. Record a flight departure")
print("6. Record a flight arrival")
# call method according to the user's option
# print error message if not a valid input
option = input()
if len(isAgent) > 0 and option == "5":
return agents.recordDepart(email, CONN_STRING)
if len(isAgent) > 0 and option == "6":
return agents.recordArr(email, CONN_STRING)
if option == "1":
return search.search(email, CONN_STRING)
elif option == "2":
return existing.existing(email, CONN_STRING)
elif option == "3":
return logout(email, CONN_STRING)
elif option == "4":
return roundtrip.roundTrip(email, CONN_STRING)
else:
print("not a valid number")
return menu(email, CONN_STRING)
def menu(email, CONN_STRING):
sql = """
create view available_flights(flightno, dep_date, src,dst,
dep_time,arr_time,fare,seats, price)
as
select f.flightno, sf.dep_date, f.src, f.dst,
f.dep_time+(trunc(sf.dep_date)-trunc(f.dep_time)),
f.dep_time+(trunc(sf.dep_date)-trunc(f.dep_time))+
(f.est_dur/60+a2.tzone-a1.tzone)/24, fa.fare,
fa.limit-count(tno), fa.price
from flights f, flight_fares fa, sch_flights sf, bookings b,
airports a1, airports a2
where f.flightno=sf.flightno and f.flightno=fa.flightno
and f.src=a1.acode and f.dst=a2.acode
and fa.flightno=b.flightno(+) and fa.fare=b.fare(+)
and sf.dep_date=b.dep_date(+)
group by f.flightno, sf.dep_date, f.src, f.dst, f.dep_time,
f.est_dur,a2.tzone, a1.tzone, fa.fare, fa.limit,
fa.price
having fa.limit-count(tno) > 0
"""
try:
sqlWithNoReturn(sql, CONN_STRING)
except:
pass
sql = """
create view good_connections (src,dst,dep_time,arr_time,
dep_date,flightno1,flightno2,stops,layover,price,
seats1, seats2, fare1, fare2)
as
select a1.src, a2.dst, a1.dep_time, a2.arr_time,
a1.dep_date, a1.flightno, a2.flightno, 2 stops,
a2.dep_time-a1.arr_time, min(a1.price+a2.price),
a1.seats as seats1, a2.seats as seats2,
a1.fare as fare1, a2.fare as fare2
from available_flights a1, available_flights a2
where a1.dst=a2.src and a1.arr_time +1.5/24 <=a2.dep_time
and a1.arr_time +5/24 >=a2.dep_time
group by a1.src, a2.dst, a1.dep_date, a1.flightno, a2.flightno,
a2.dep_time, a1.arr_time, a1.dep_time, a2.arr_time,
a1.seats, a2.seats, a1.fare, a2.fare
"""
try:
sqlWithNoReturn(sql, CONN_STRING)
except:
pass
print("===============================================")
print("1. Search For And Book Flights")
print("2. List Or Cancel Existing Bookings")
print("3. Logout")
sql = "select * from airline_agents where email = '{0}'".format(email)
try:
isAgent = sqlWithReturn(sql, CONN_STRING)
except:
print("An unexpected error occurred while trying to see if you're an airline agent. Please try again.")
return init(CONN_STRING)
print("4. Search For Round Trip Flights")
if len(isAgent) > 0:
print("5. Record a flight departure")
print("6. Record a flight arrival")
option = input()
if len(isAgent) > 0 and option == "5":
agents.recordDepart(email, CONN_STRING)
if len(isAgent) > 0 and option == "6":
agents.recordArr(email, CONN_STRING)
if option == "1":
search.search(email, CONN_STRING)
elif option == "2":
existing.existing(email, CONN_STRING)
elif option == "3":
sql = "update users set last_login = sysdate where email = '" + email + "'"
try:
sqlWithNoReturn(sql, CONN_STRING)
except:
print("An error occurred while trying to log you out.")
init(CONN_STRING)
# logout(email, CONN_STRING)
elif option == "4":
roundtrip.roundTrip(email, CONN_STRING)
else:
print("not a valid number")
menu(email, CONN_STRING)