def handleRegister():
# ask for email and password
print("Please register your email and password.\n")
user_email = input("Email: ")
user_pw = getpass.getpass()
# queries
checkIfUserExists = "SELECT email FROM users WHERE email = '" + user_email + "'"
addToUsers = "INSERT INTO users VALUES('" + user_email + "', '" + user_pw + "', SYSDATE)"
# check if user exists
db = main.getDatabase()
db.execute(checkIfUserExists)
user_results = db.cursor.fetchall()
if len(user_results) > 0: # user exists!
print("User already exists. Please log in.\n")
main.showMainMenu()
# valid email check from: http://stackoverflow.com/questions/8022530/python-check-for-valid-email-address
if not re.match(r"[^@]+@[^@]+\.[^@]+", user_email):
print("Not a valid email. Please try again.")
handleRegister()
if (len(user_pw) > 4):
print("Password must NOT be more than 4 characters.")
handleRegister()
else: # everything is valid! go ahead and add to user table
db.execute(addToUsers)
db.execute("commit")
print("You've successfully registered! Please log in.\n")
def detailedBooking(self, tno, details):
"""
The details of the booking have been shown, the user can cancel the booking or return to their list of existing bookings.
"""
print("The details for ticket no " + tno + " are: \n")
# get headings, print details
db = main.getDatabase()
showDetails = "SELECT tno, flightno, fare, dep_date, seat FROM bookings WHERE tno = '" + tno + "'"
headings = db.cursor.description
headingsStr = "( "
for h in headings:
headingsStr += str(h[0]) + ", "
headingsStr = headingsStr.strip(", ")
headingsStr += ' )'
print(headingsStr)
for d in details:
print(d)
print('\n')
while True:
action = input("Cancel this booking (C) or Return to list of existing bookings (R): ")
if action == "C":
self.cancelBooking(tno)
elif action == "R":
self.showExistingBookings()
self.promptForBooking()
else: print("Please enter a valid input. ")
def getAssignedSeats(self):
getSeats = "SELECT DISTINCT seat from bookings"
db = main.getDatabase()
db.execute(getSeats)
seats = db.cursor.fetchall()
assignedSeats = [s[0] for s in seats]
return assignedSeats
def recordArr(self, fno, date, update):
record = "UPDATE sch_flights SET act_arr_time = TO_DATE('" + update + "', 'DD-MON-YYYY, hh24:mi') WHERE (flightno = '" + fno + "' AND dep_date = TO_DATE('" + date + "', 'DD-MON-YYYY'))"
db = main.getDatabase()
db.execute(record)
db.execute("commit")
print("Successfully recorded actual arrival time.")
self.showMenu()
def handleRegister():
# ask for email and password
print("Please register your email and password.\n")
user_email = input("Email: ")
user_pw = getpass.getpass()
# queries
checkIfUserExists = "SELECT email FROM users WHERE email = '" + user_email + "'"
addToUsers = "INSERT INTO users VALUES('" + user_email + "', '" + user_pw + "', SYSDATE)"
# check if user exists
db = main.getDatabase()
db.execute(checkIfUserExists)
user_results = db.cursor.fetchall()
if len(user_results) > 0: # user exists!
print("User already exists. Please log in.\n")
main.showMainMenu()
# valid email check from: http://stackoverflow.com/questions/8022530/python-check-for-valid-email-address
if not re.match(r"[^@]+@[^@]+\.[^@]+", user_email):
print("Not a valid email. Please try again.")
handleRegister()
if (len(user_pw) > 4):
print("Password must NOT be more than 4 characters.")
handleRegister()
else: # everything is valid! go ahead and add to user table
db.execute(addToUsers)
db.execute("commit")
print("You've successfully registered! Please log in.\n")
def handleLogin():
# ask user for email and password
user_email = input("Email: ")
user_pw = getpass.getpass()
# queries
checkIfUserExists = "SELECT email FROM users WHERE email = '" + user_email + "' AND pass = '******'"
checkIfAgent = "SELECT email FROM airline_agents WHERE email = '" + user_email + "'"
# check if user exists
db = main.getDatabase()
db.execute(checkIfUserExists)
user_results = db.cursor.fetchall()
if len(user_results) > 0: # user exists!
db.execute(checkIfAgent)
agent_results = db.cursor.fetchall()
if len(agent_results) > 0:
menu = agentMenu.AgentMenu(user_email) # agents should lead to agent menu
else:
menu = userMenu.UserMenu(user_email) # normal users go to user menu
menu.showMenu()
else:
print("User does not exist or password is incorrect.")
main.showMainMenu()
def isValidAcode(code):
"""
Checks to see if given airport code is valid by query to database.
"""
db = main.getDatabase()
db.execute('SELECT acode FROM airports')
return code.upper() in [code for result in db.cursor.fetchall() for code in result]
def detailedBooking(self, tno, details):
"""
The details of the booking have been shown, the user can cancel the booking or return to their list of existing bookings.
"""
print("The details for ticket no " + tno + " are: \n")
# get headings, print details
db = main.getDatabase()
showDetails = "SELECT tno, flightno, fare, dep_date, seat FROM bookings WHERE tno = '" + tno + "'"
headings = db.cursor.description
headingsStr = "( "
for h in headings:
headingsStr += str(h[0]) + ", "
headingsStr = headingsStr.strip(", ")
headingsStr += ' )'
print(headingsStr)
for d in details:
print(d)
print('\n')
while True:
action = input(
"Cancel this booking (C) or Return to list of existing bookings (R): "
)
if action == "C":
self.cancelBooking(tno)
elif action == "R":
self.showExistingBookings()
self.promptForBooking()
else:
print("Please enter a valid input. ")
def getAssignedSeats(self):
getSeats = "SELECT DISTINCT seat from bookings"
db = main.getDatabase()
db.execute(getSeats)
seats = db.cursor.fetchall()
assignedSeats = [s[0] for s in seats]
return assignedSeats
def isValidAcode(code):
"""
Checks to see if given airport code is valid by query to database.
"""
db = main.getDatabase()
db.execute('SELECT acode FROM airports')
return code.upper() in [
code for result in db.cursor.fetchall() for code in result
]
def setLastLogin(self):
"""
Logs out the user and updates last_login.
"""
logout = "UPDATE users SET last_login = SYSDATE " + "WHERE email = '" + self.email + "'"
db = main.getDatabase()
db.execute(logout)
db.execute("commit")
print("Successfully logged out.\n")
def setLastLogin(self):
"""
Logs out the user and updates last_login.
"""
logout = "UPDATE users SET last_login = SYSDATE " + "WHERE email = '" + self.email + "'"
db = main.getDatabase()
db.execute(logout)
db.execute("commit")
print("Successfully logged out.\n")
def searchFlightsSortedByConnections(src, dst, dep_date, groupSize=1, displayable=False):
# sort by number of connections and price
db = main.getDatabase()
db.execute(TRIP_QUERY_SORT_CONNECTIONS_PARTIES.format(dep_date, src, dst, groupSize))
if displayable:
db.execute(DISPLAYABLE_C)
else:
db.execute("SELECT * FROM temp ORDER BY numstops asc, price asc")
return db.cursor.fetchall()
def generateTicketNumber(self):
"""
Generates a ticket number by getting the max ticket number from the tickets table and incrementing by one.
"""
findMaxTno = "SELECT MAX(tno) FROM tickets"
db = main.getDatabase()
db.execute(findMaxTno)
maxTno = db.cursor.fetchall()
if len(maxTno) == 0: return 1
else: return int(maxTno[0][0]) + 1
def generateTicketNumber(self):
"""
Generates a ticket number by getting the max ticket number from the tickets table and incrementing by one.
"""
findMaxTno = "SELECT MAX(tno) FROM tickets"
db = main.getDatabase()
db.execute(findMaxTno)
maxTno = db.cursor.fetchall()
if len(maxTno) == 0: return 1
else: return int(maxTno[0][0]) + 1
def makeABooking(self, fullFlightDetails, num=1, roundTrip=False, user_name="", oneOfMany=False):
"""
Handles booking of flights. Default handles the core functionality as per specifications however, extra functionality is also handled in makeABooking for round trips and parties with size larger than one.
"""
db = main.getDatabase()
if roundTrip == True:
flightDetails = fullFlightDetails[0]
else: flightDetails = fullFlightDetails
flightno, flightno2, src, dst, dep_time, arr_time, layover, numStops, fare1, fare2, price, seats, dep_date = flightDetails
price = str(price)
seat = self.generateSeatNumber()
# get name of user, check if in passengers table
user_name = self.verifyPassenger(user_name)
tno = str(self.generateTicketNumber())
# check if seat is still available by searching for the flights again
flights = sf.searchFlights(src, dst, dep_date, num)
if len(flights) == 0:
print("Sorry, this seat is no longer available. Please try another flight.")
self.showMenu()
else:
# parties larger than one will go through this, as per the specifications
if oneOfMany:
cheap_flight = sf.getCheapestSpecificFlight(flightDetails)[0]
flightno, flightno2, src, dst, dep_time, arr_time, layover, numStops, fare1, fare2, price, seats = cheap_flight
print("The cheapest fare for your flight choice is ${0}".format(price))
try:
# executing queries
db.execute(INSERT_TICKET.format(tno, user_name, self.email, price))
db.execute(INSERT_BOOKING.format(tno, flightno, fare1, dep_date, seat))
db.execute("commit")
tix = tno # for printing purposes
if (flightno2 != None): # connecting flight, need two bookings
tno2 = str(self.generateTicketNumber())
tix = tno + ", " + tno2
seat2 = self.generateSeatNumber()
db.execute(INSERT_TICKET.format(tno2, user_name, self.email, price))
db.execute(INSERT_BOOKING.format(tno2, flightno2, fare2, dep_date, seat))
db.execute("commit")
print("Your flight has been booked with the ticket number(s): " + tix + ". \n")
if (roundTrip == True): # round trips will make another booking, user_name was already given
self.makeABooking(fullFlightDetails[1], num, False, user_name)
except cx_Oracle.DatabaseError as exc:
error = exc.args
print( sys.stderr, "Oracle code:", error.code)
print( sys.stderr, "Oracle message:", error.message)
if not oneOfMany: # go back to main menu
self.showMenu()
def cancelBooking(self, tno):
"""
Cancels selected tno. Called when user lists existing bookings and selects a booking for more details.
"""
dBook = "DELETE FROM bookings WHERE tno = " + tno
dTix = "DELETE FROM tickets WHERE tno = " + tno
db = main.getDatabase()
db.execute(dBook)
db.execute(dTix)
db.execute("commit")
print("Booking successfully cancelled. Returning to main menu.\n")
self.showMenu()
def cancelBooking(self, tno):
"""
Cancels selected tno. Called when user lists existing bookings and selects a booking for more details.
"""
dBook = "DELETE FROM bookings WHERE tno = " + tno;
dTix = "DELETE FROM tickets WHERE tno = " + tno;
db = main.getDatabase()
db.execute(dBook)
db.execute(dTix)
db.execute("commit")
print("Booking successfully cancelled. Returning to main menu.\n")
self.showMenu()
def findFlight(self):
while True:
flight = input("Enter the flight number and departure date (DD-MON-YYYY), separated using a space. (R) to return. ")
if flight == 'R': self.showMenu()
(fno, date) = flight.split(" ")
# check if flight exists
checkValidFlight = "SELECT * FROM sch_flights WHERE flightno = '" + fno + "' AND dep_date = TO_DATE('" + date + "', 'DD-MON-YYYY')"
db = main.getDatabase()
db.execute(checkValidFlight)
found = db.cursor.fetchall()
if len(found) > 0: return (fno, date)
else: print("\nNo flights found, try again.\n")
def searchFlights(src, dst, dep_date, groupSize=1, displayable=False):
"""
The main query for obtaining the flights available.
displayable is an atrribute for more easily displaying the data in a readable format as per the specification. When set to False, it returns everything from the temp table. Clearing the temp table is important before re-populating it.
"""
db = main.getDatabase()
db.execute(CLEAR_SEARCH_RESULTS)
db.execute(TRIP_QUERY_PARTIES.format(dep_date, src, dst))
if displayable:
db.execute(DISPLAYABLE)
else:
db.execute("SELECT * FROM temp ORDER BY PRICE asc")
return db.cursor.fetchall()
def searchFlights(src, dst, dep_date, groupSize=1, displayable=False):
"""
The main query for obtaining the flights available.
displayable is an atrribute for more easily displaying the data in a readable format as per the specification. When set to False, it returns everything from the temp table. Clearing the temp table is important before re-populating it.
"""
db = main.getDatabase()
db.execute(CLEAR_SEARCH_RESULTS)
db.execute(TRIP_QUERY_PARTIES.format(dep_date, src, dst))
if displayable:
db.execute(DISPLAYABLE)
else:
db.execute("SELECT * FROM temp ORDER BY PRICE asc")
return db.cursor.fetchall()
def promptForBooking(self):
"""
This prompts the user to enter a ticket number out of the existing bookings or return to the previous menu.
"""
while True:
ticket_no = input("Enter your ticket number for more details about that booking. R to return to previous menu. \n")
if ticket_no == "R": self.showMenu()
showDetails = "SELECT tno, flightno, fare, dep_date, seat FROM bookings WHERE tno = '" + ticket_no + "'"
db = main.getDatabase()
db.execute(showDetails)
details = db.cursor.fetchall()
if details:
self.detailedBooking(ticket_no, details)
else: print("Please enter a valid ticket number. ")
def showExistingBookings(self):
"""
This shows the user's existing bookings. The UserMenu class has self.bookings in order to facilitate this function easier.
"""
db = main.getDatabase()
db.execute(BOOKING_QUERY.format(self.email))
booking_results = db.cursor.fetchall()
if len(booking_results) == 0:
print("No existing bookings found.")
self.showMenu()
print ("Ticket # Name Departure Date Price\n")
self.bookings = []
for result in booking_results:
self.bookings.append(result[0])
print (str(result[0]) + " " + result[1] + " " + str(result[2]) + " " + str(result[3]) + "\n")
def getCheapestSpecificFlight(flightDetails):
"""
For the implementation of booking for parties larger than one, as per the specifications.
"""
flightno, flightno2, src, dst, dep_time, arr_time, layover, numStops, fare1, fare2, price, seats, dep_date = flightDetails
db = main.getDatabase()
if flightno2 == None: flightno2 = "IS NULL"
else: flightno2 = "= '" + flightno2 + "'"
db.execute(CHEAPEST_SPECIFC_FLIGHT.format(flightno, flightno2, dep_time))
cheap_flights = db.cursor.fetchall()
return cheap_flights
def searchFlightsSortedByConnections(src,
dst,
dep_date,
groupSize=1,
displayable=False):
# sort by number of connections and price
db = main.getDatabase()
db.execute(
TRIP_QUERY_SORT_CONNECTIONS_PARTIES.format(dep_date, src, dst,
groupSize))
if displayable:
db.execute(DISPLAYABLE_C)
else:
db.execute("SELECT * FROM temp ORDER BY numstops asc, price asc")
return db.cursor.fetchall()
def getCheapestSpecificFlight(flightDetails):
"""
For the implementation of booking for parties larger than one, as per the specifications.
"""
flightno, flightno2, src, dst, dep_time, arr_time, layover, numStops, fare1, fare2, price, seats, dep_date = flightDetails
db = main.getDatabase()
if flightno2 == None: flightno2 = "IS NULL"
else: flightno2 = "= '" + flightno2 + "'"
db.execute(CHEAPEST_SPECIFC_FLIGHT.format(flightno, flightno2, dep_time))
cheap_flights = db.cursor.fetchall()
return cheap_flights
def promptForBooking(self):
"""
This prompts the user to enter a ticket number out of the existing bookings or return to the previous menu.
"""
while True:
ticket_no = input(
"Enter your ticket number for more details about that booking. R to return to previous menu. \n"
)
if ticket_no == "R": self.showMenu()
showDetails = "SELECT tno, flightno, fare, dep_date, seat FROM bookings WHERE tno = '" + ticket_no + "'"
db = main.getDatabase()
db.execute(showDetails)
details = db.cursor.fetchall()
if details:
self.detailedBooking(ticket_no, details)
else:
print("Please enter a valid ticket number. ")
def getMatchingAirports(userInput):
"""
userInput is taken as text to partial match from airport names and/or airport cities. A list of airports that match (partial) the given input is returned.
"""
airport_query = "SELECT * FROM airports WHERE name LIKE '%{0}%' OR city LIKE '%{0}%'"
db = main.getDatabase()
db.execute(airport_query.format(userInput.title()))
airport_info = db.cursor.fetchall()
while not airport_info:
userInput = input("No airports found. Try entering the city or the name of the airport: ")
db.execute(airport_query.format(userInput.title()))
airport_info = db.cursor.fetchall()
print("")
print("Here are existing airports that matched your query: \n")
for airport in airport_info:
print(airport)
return [airportCode[0] for airportCode in airport_info]
def showExistingBookings(self):
"""
This shows the user's existing bookings. The UserMenu class has self.bookings in order to facilitate this function easier.
"""
db = main.getDatabase()
db.execute(BOOKING_QUERY.format(self.email))
booking_results = db.cursor.fetchall()
if len(booking_results) == 0:
print("No existing bookings found.")
self.showMenu()
print("Ticket # Name Departure Date Price\n")
self.bookings = []
for result in booking_results:
self.bookings.append(result[0])
print(
str(result[0]) + " " + result[1] + " " + str(result[2]) +
" " + str(result[3]) + "\n")
def getMatchingAirports(userInput):
"""
userInput is taken as text to partial match from airport names and/or airport cities. A list of airports that match (partial) the given input is returned.
"""
airport_query = "SELECT * FROM airports WHERE name LIKE '%{0}%' OR city LIKE '%{0}%'"
db = main.getDatabase()
db.execute(airport_query.format(userInput.title()))
airport_info = db.cursor.fetchall()
while not airport_info:
userInput = input(
"No airports found. Try entering the city or the name of the airport: "
)
db.execute(airport_query.format(userInput.title()))
airport_info = db.cursor.fetchall()
print("")
print("Here are existing airports that matched your query: \n")
for airport in airport_info:
print(airport)
return [airportCode[0] for airportCode in airport_info]
def verifyPassenger(self, user_name):
"""
Verify if user_name is in passenger table.
"""
if user_name == "":
user_name = input("Please enter your first and last name: ").title()
checkIfPassenger = "SELECT * FROM passengers WHERE email = '{0}' and name = '{1}'".format(self.email, user_name)
db = main.getDatabase()
db.execute(checkIfPassenger)
isPassenger = db.cursor.fetchall()
if len(isPassenger) == 0: # not in passengers table
country = self.promptForCountry()
db.execute(INSERT_PASSENGER.format(self.email, user_name, country))
db.execute("commit")
else:
country = isPassenger[0][2]
return user_name
def verifyPassenger(self, user_name):
"""
Verify if user_name is in passenger table.
"""
if user_name == "":
user_name = input(
"Please enter your first and last name: ").title()
checkIfPassenger = "SELECT * FROM passengers WHERE email = '{0}' and name = '{1}'".format(
self.email, user_name)
db = main.getDatabase()
db.execute(checkIfPassenger)
isPassenger = db.cursor.fetchall()
if len(isPassenger) == 0: # not in passengers table
country = self.promptForCountry()
db.execute(INSERT_PASSENGER.format(self.email, user_name, country))
db.execute("commit")
else:
country = isPassenger[0][2]
return user_name
def makeABooking(self,
fullFlightDetails,
num=1,
roundTrip=False,
user_name="",
oneOfMany=False):
"""
Handles booking of flights. Default handles the core functionality as per specifications however, extra functionality is also handled in makeABooking for round trips and parties with size larger than one.
"""
db = main.getDatabase()
if roundTrip == True:
flightDetails = fullFlightDetails[0]
else:
flightDetails = fullFlightDetails
flightno, flightno2, src, dst, dep_time, arr_time, layover, numStops, fare1, fare2, price, seats, dep_date = flightDetails
price = str(price)
seat = self.generateSeatNumber()
# get name of user, check if in passengers table
user_name = self.verifyPassenger(user_name)
tno = str(self.generateTicketNumber())
# check if seat is still available by searching for the flights again
flights = sf.searchFlights(src, dst, dep_date, num)
if len(flights) == 0:
print(
"Sorry, this seat is no longer available. Please try another flight."
)
self.showMenu()
else:
# parties larger than one will go through this, as per the specifications
if oneOfMany:
cheap_flight = sf.getCheapestSpecificFlight(flightDetails)[0]
flightno, flightno2, src, dst, dep_time, arr_time, layover, numStops, fare1, fare2, price, seats = cheap_flight
print(
"The cheapest fare for your flight choice is ${0}".format(
price))
try:
# executing queries
db.execute(
INSERT_TICKET.format(tno, user_name, self.email, price))
db.execute(
INSERT_BOOKING.format(tno, flightno, fare1, dep_date,
seat))
db.execute("commit")
tix = tno # for printing purposes
if (flightno2 != None): # connecting flight, need two bookings
tno2 = str(self.generateTicketNumber())
tix = tno + ", " + tno2
seat2 = self.generateSeatNumber()
db.execute(
INSERT_TICKET.format(tno2, user_name, self.email,
price))
db.execute(
INSERT_BOOKING.format(tno2, flightno2, fare2, dep_date,
seat))
db.execute("commit")
print(
"Your flight has been booked with the ticket number(s): " +
tix + ". \n")
if (
roundTrip == True
): # round trips will make another booking, user_name was already given
self.makeABooking(fullFlightDetails[1], num, False,
user_name)
except cx_Oracle.DatabaseError as exc:
error = exc.args
print(sys.stderr, "Oracle code:", error.code)
print(sys.stderr, "Oracle message:", error.message)
if not oneOfMany: # go back to main menu
self.showMenu()
