def printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy="1"): # query that selects suitable round trip flights sorted by price and # airport code sortByPrice = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' order by price asc)) t2 order by price asc """.format(source, dest, dep_date, ret_date) # query that selects suitable round trip flights sorted by first stops # then price and airport code sortByStops = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' order by price asc)) t2 order by stops, price asc """.format(source, dest, dep_date, ret_date) # use the corresponding query to sort the result if sortBy == "1": try: rs, desc = main.sqlWithReturnDesc(sortByPrice, CONN_STRING) except: print("no match found") return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, "1") else: try: rs, desc = main.sqlWithReturnDesc(sortByStops, CONN_STRING) except: print("no match found") return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, "1") # if there is result print column name and rows if len(rs) != 0: i = 1 for row in desc: print(row[0], end=" ") print("") for row in rs: print(str(i)+".",row) i+=1 # if no result then use implicit search else: # query that selects suitable round trip flights sorted by price and # similar aiport name or city name sortByPrice = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from airports a1, airports a2, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' order by price asc)) t2 where (lower(t1.src) = '{0}' and a1.acode = t1.src or (t1.src = a1.acode and lower(a1.city) like '%{0}%' or lower(a1.name) like '%{0}%')) and (lower(t1.dst) = '{1}' and a2.acode = t1.dst or (t1.dst = a2.acode and lower(a2.city) like '%{1}%' or lower(a2.name) like '%{1}%')) and t1.src = t2.dst and t1.dst = t2.src order by price asc """.format(source.lower(), dest.lower(), dep_date, ret_date) # query that selects suitable round trip flights sorted by stops # then price and similar aiport name or city name sortByStops = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from airports a1, airports a2, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' order by price asc)) t2 where (lower(t1.src) = '{0}' and a1.acode = t1.src or (t1.src = a1.acode and lower(a1.city) like '%{0}%' or lower(a1.name) like '%{0}%')) and (lower(t1.dst) = '{1}' and a2.acode = t1.dst or (t1.dst = a2.acode and lower(a2.city) like '%{1}%' or lower(a2.name) like '%{1}%')) and t1.src = t2.dst and t1.dst = t2.src order by stops, price asc """.format(source.lower(), dest.lower(), dep_date, ret_date) if sortBy == "1": rs, desc = main.sqlWithReturnDesc(sortByPrice, CONN_STRING) else: rs, desc = main.sqlWithReturnDesc(sortByStops, CONN_STRING) # print searching result if exists if len(rs) != 0: i = 1 for row in desc: print(row[0], end=" ") print("") for row in rs: print(str(i)+".", row) i+=1 # print error message and go back to menu else: print("no results") return main.menu(email, CONN_STRING) # print options print(str(len(rs)+1)+".", "Sort by number of connections") print(str(len(rs)+2)+".", "Make a booking") print(str(len(rs)+3)+".", "Go back to menu") # get options and call corresponding method option = input("Enter the number of an option: ") try: optNum = int(option) if optNum<0 or optNum>len(rs)+3: print("Not valid number") elif optNum == len(rs)+1: return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, "2") elif optNum == len(rs)+2: # get user selected number of flight flightno = int(input("Enter the number before the flight you want to book: ")) # go back to start of function if flightno is less than one # to prevent negative indexing issues if flightno < 1: return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy) # get a new result set of current data if sortBy == "1": newRs = main.sqlWithReturn(sortByPrice, CONN_STRING) else: newRs = main.sqlWithReturn(sortByStops, CONN_STRING) # compare each row in new result set with user selected row and # record the index selected = rs[flightno-1] i = 0 for row in newRs: if row[0] == selected[0] and row[1] == selected[1] and row[8] == selected[8] and row[9] == selected[9] and row[10] == selected[10] and row[11] == selected[11] and row[-5] == selected[-5] and row[-4] == selected[-4]: break else: i += 1 # if not found, print error message and go back if i == len(newRs): print("tickets for your selected flight has run out") return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy) # call booking function return roundBooking(email, CONN_STRING, i+1, newRs, dep_date, ret_date, source, dest) elif optNum == len(rs)+3: return main.menu(email, CONN_STRING) else: print("Not valid number") return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy) except: print("Not valid number") return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy)
def printInfo(email, CONN_STRING, source, dest, dep_date, sortBy="1"): sortByPrice = """ select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' order by price asc) """.format(source, dest, dep_date) sortByStops = """ select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' order by stops, price asc) """.format(source, dest, dep_date) try: if sortBy == "1": rs, desc = main.sqlWithReturnDesc(sortByPrice, CONN_STRING) else: rs, desc = main.sqlWithReturnDesc(sortByStops, CONN_STRING) except: print("There was an error in finding your flight. Please try again.") return main.menu(email, CONN_STRING) if len(rs) != 0: i = 1 for row in desc: print(row[0], end=" ") print("") for row in rs: print(str(i)+".",row) i+=1 else: sortByPrice = """ select x.flightno1, x.flightno2, x.src, x.dst, to_char(x.dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, x.stops, 60 * x.layover as layover, x.price, x.fare1, x.fare2, (x.seats1 + x.seats2) / 2 - abs(x.seats1 - x.seats2)/2 as seats from airports a1, airports a2, (select flightno1, flightno2, src, dst, dep_time, arr_time, stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}')) x where (lower(a1.city) like '%{0}%' or lower(a1.name) like '%{0}%') and a1.acode = x.src and a2.acode = x.dst and (lower(a2.city) like '%{1}%' or lower(a2.name) like '%{1}%') order by price asc """.format(source.lower(), dest.lower(), dep_date) # query that selects suitable flights sorted by stops then price and # similar aiport name or city name sortByStops = """ select x.flightno1, x.flightno2, x.src, x.dst, to_char(x.dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, x.stops, 60 * x.layover as layover, x.price, x.fare1, x.fare2, (x.seats1 + x.seats2) / 2 - abs(x.seats1 - x.seats2)/2 as seats from airports a1, airports a2, (select flightno1, flightno2, src, dst, dep_time, arr_time, stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}')) x where (lower(a1.city) like '%{0}%' or lower(a1.name) like '%{0}%') and a1.acode = x.src and a2.acode = x.dst and (lower(a2.city) like '%{1}%' or lower(a2.name) like '%{1}%') order by stops, price asc """.format(source.lower(), dest.lower(), dep_date) try: if sortBy == "1": rs, desc = main.sqlWithReturnDesc(sortByPrice, CONN_STRING) else: rs, desc = main.sqlWithReturnDesc(sortByStops, CONN_STRING) except: print("Unexpected error occurred while trying to find flights. Please try again.") return main.menu(email, CONN_STRING) if len(rs) != 0: i = 1 for row in desc: print(row[0], end=" ") print("") for row in rs: print(str(i)+".", row) i+=1 else: print("no results") main.menu(email, CONN_STRING) print(str(len(rs)+1)+".", "Sort by number of connections") print(str(len(rs)+2)+".", "Make a booking") print(str(len(rs)+3)+".", "Go back to menu") option = input("Enter the number of an option: ") try: optNum = int(option) if optNum<0 or optNum>len(rs)+3: print("Not valid number") elif optNum == len(rs)+1: printInfo(email, CONN_STRING, source, dest, dep_date, "2") elif optNum == len(rs)+2: flightno = int(input("Enter the number before the flight you want to book: ")) if sortBy == "1": rs = main.sqlWithReturn(sortByPrice, CONN_STRING) else: newRs = main.sqlWithReturn(sortByStops, CONN_STRING) # compare each row in new result set with user selected row and # record the index selected = rs[flightno-1] i = 0 for row in newRs: if row[0] == selected[0] and row[1] == selected[1] and row[-3] == selected[-3] and row[-2] == selected[-2]: break else: i += 1 # if not found, print error message and go back if i == len(newRs): print("tickets for your selected flight has run out") return printInfo(email, CONN_STRING, source, dest, dep_date, sortBy) # call booking function return booking(email, CONN_STRING, i+1, newRs, dep_date, source, dest) elif optNum == len(rs)+3: main.menu(email, CONN_STRING) else: print("Not valid number") printInfo(email, CONN_STRING, source, dest, dep_date, "1") except: print("Not valid number") printInfo(email, CONN_STRING, source, dest, dep_date, "1")
def printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy="1"): sortByPrice = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' order by price asc)) t2 order by price asc """.format(source, dest, dep_date, ret_date) sortByStops = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' and src='{0}' and dst='{1}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' and src='{1}' and dst='{0}' order by price asc)) t2 order by stops, price asc """.format(source, dest, dep_date, ret_date) if sortBy == "1": try: rs, desc = main.sqlWithReturnDesc(sortByPrice, CONN_STRING) except: print("no match found") return main.menu(email, CONN_STRING) else: try: rs, desc = main.sqlWithReturnDesc(sortByStops, CONN_STRING) except: print("no match found") return main.menu(email, CONN_STRING) if len(rs) != 0: i = 1 for row in desc: print(row[0], end=" ") print("") for row in rs: print(str(i)+".",row) i+=1 else: sortByPrice = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from airports a1, airports a2, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' order by price asc)) t2 where (lower(a1.city) like '%{0}%' or lower(a1.name) like '%{0}%') and a1.acode = t1.src and a2.acode = t1.dst and (lower(a2.city) like '%{1}%' or lower(a2.name) like '%{1}%') and t1.src = t2.dst and t1.dst = t2.src order by price asc """.format(source.lower(), dest.lower(), dep_date, ret_date) sortByStops = """ select t1.flightno1, t1.flightno2, t1.src, t1.dst, t1.dep_date, t1.dep_time, t1.arr_time, t1.layover, t1.fare1, t1.fare2, t2.flightno1 as flightno3, t2.flightno2 as flightno4, t2.dep_date as ret_date, t2.dep_time, t2.arr_time, t2.layover, t2.fare1 as fare3, t2.fare2 as fare4, t1.stops + t2.stops as stops, (t1.seats + t2.seats) / 2 - abs(t1.seats - t2.seats) / 2 as seats, t1.price + t2.price as price from airports a1, airports a2, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{2}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{2}' order by price asc)) t1, (select flightno1, flightno2, src, dst, to_char(dep_date) as dep_date, to_char(dep_time, 'HH24:MI') as dep_time, to_char(arr_time, 'HH24:MI') as arr_time, stops, 60 * layover as layover , price, fare1, fare2, (seats1 + seats2) / 2 - abs(seats1 - seats2) / 2 as seats from (select flightno1, flightno2, src, dst, dep_time, arr_time, 1 stops, layover, price, seats1, seats2, dep_date, fare1, fare2 from good_connections where to_char(dep_date,'DD/MM/YYYY')='{3}' union select flightno flightno1, '' flightno2, src, dst, dep_time, arr_time, 0 stops, 0 layover, price, seats seats1, seats+1 as seats2, dep_date, fare as fare1, null fare2 from available_flights where to_char(dep_date,'DD/MM/YYYY')='{3}' order by price asc)) t2 where (lower(a1.city) like '%{0}%' or lower(a1.name) like '%{0}%') and a1.acode = t1.src and a2.acode = t1.dst and (lower(a2.city) like '%{1}%' or lower(a2.name) like '%{1}%') and t1.src = t2.dst and t1.dst = t2.src order by price asc """.format(source.lower(), dest.lower(), dep_date, ret_date) if sortBy == "1": try: rs, desc = main.sqlWithReturnDesc(sortByPrice, CONN_STRING) except: print("There was an unexpected error while trying to find flights. Please try again.") return main.menu(email, CONN_STRING) else: try: rs, desc = main.sqlWithReturnDesc(sortByStops, CONN_STRING) except: print("There was an unexpected error while trying to find flights. Please try again.") return main.menu(email, CONN_STRING) if len(rs) != 0: i = 1 for row in desc: print(row[0], end=" ") print("") for row in rs: print(str(i)+".", row) i+=1 else: print("no results") return main.menu(email, CONN_STRING) print(str(len(rs)+1)+".", "Sort by number of connections") print(str(len(rs)+2)+".", "Make a booking") print(str(len(rs)+3)+".", "Go back to menu") option = input("Enter the number of an option: ") try: optNum = int(option) if optNum<0 or optNum>len(rs)+3: print("Not valid number") elif optNum == len(rs)+1: printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, "2") elif optNum == len(rs)+2: flightno = int(input("Enter the number before the flight you want to book: ")) if sortBy == "1": rs = main.sqlWithReturn(sortByPrice, CONN_STRING) else: newRs = main.sqlWithReturn(sortByStops, CONN_STRING) # compare each row in new result set with user selected row and # record the index selected = rs[flightno-1] i = 0 for row in newRs: if row[0] == selected[0] and row[1] == selected[1] and row[8] == selected[8] and row[9] == selected[9] and row[10] == selected[10] and row[11] == selected[11] and row[-5] == selected[-5] and row[-4] == selected[-4]: break else: i += 1 # if not found, print error message and go back if i == len(newRs): print("tickets for your selected flight has run out") return printRoundInfo(email, CONN_STRING, source, dest, dep_date, ret_date, sortBy) # call booking function return roundBooking(email, CONN_STRING, i+1, newRs, dep_date, ret_date, source, dest) elif optNum == len(rs)+3: main.menu(email, CONN_STRING) else: print("Not valid number") return main.menu(email, CONN_STRING) except: print("Not valid number") return main.menu(email, CONN_STRING)