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)