Beispiel #1
0
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)
Beispiel #2
0
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)