def createNewFlights():
# grabs information
airline_name = session['airline_name']
flight_num = request.form['flight_num']
departure_airport = request.form['departure_airport']
departure_time = request.form['departure_time']
arrival_airport = request.form['arrival_airport']
arrival_time = request.form['arrival_time']
price = request.form['price']
status = request.form['status']
airplane_id = request.form['airplane_id']
# check consistence of time
if departure_time >= arrival_time:
error = 'Error: wrong time format or inconsistent departure and arrival time!'
return render_template('airline_staff/update.html', result=error)
try:
msg = 'Create successfully!'
with conn.cursor() as cursor:
ins = 'INSERT INTO flight VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)'
cursor.execute(
ins,
(airline_name, flight_num, departure_airport, departure_time,
arrival_airport, arrival_time, price, status, airplane_id))
conn.commit()
except MySQLError as e:
msg = 'Got error {!r}, errno is {}'.format(e, e.args[0])
return render_template('airline_staff/update.html', result=msg)
def addNewAirport():
# grabs information
airport_name = request.form['airport_name']
airport_city = request.form['airport_city']
try:
msg = 'Add successfully!'
with conn.cursor() as cursor:
ins = 'INSERT INTO airport VALUES(%s, %s)'
cursor.execute(ins, (airport_name, airport_city))
conn.commit()
except MySQLError as e:
msg = 'Got error {!r}, errno is {}'.format(e, e.args[0])
return render_template('airline_staff/update.html', result=msg)
def changeFlightStatus():
# grabs information
airline_name = session['airline_name']
flight_num = request.form['flight_num']
status = request.form['status']
try:
msg = "Update successfully!"
with conn.cursor() as cursor:
query = '''
UPDATE flight
SET status = %s
WHERE airline_name = %s AND flight_num = %s '''
cursor.execute(query, (status, airline_name, flight_num))
conn.commit()
except MySQLError as e:
msg = 'Got error {!r}, errno is {}'.format(e, e.args[0])
return render_template('airline_staff/update.html', result=msg)
def purchaseTickets():
# grabs information
booking_agent_id = session['booking_agent_id']
customer_email = request.form['customer_email']
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
# cursor used to send queries
cursor = conn.cursor()
# check seat availability
query = '''
SELECT COUNT(*) as count, seats
FROM ticket NATURAL JOIN flight NATURAL JOIN airplane
WHERE airline_name = %s AND flight_num = %s
GROUP BY airline_name, flight_num '''
cursor.execute(query, (airline_name, flight_num))
data = cursor.fetchone()
count = data['count'] if data['count'] != None else 0
seat = data['seat'] if data['seat'] != None else 0
if count < seat:
msg = "Purchase successful!"
# generates ticket_id
query = 'SELECT COUNT(*) as count FROM ticket'
cursor.execute(query)
data = cursor.fetchone()
ticket_id = count + 1
# executes updates
ins_ticket = 'INSERT INTO ticket VALUES(%s, %s, %s)'
cursor.execute(ins_ticket, (ticket_id, airline_name, flight_num))
ins_purchases = 'INSERT INTO purchases VALUES(%s, %s, %s, CURDATE())'
cursor.execute(ins_purchases,
(ticket_id, customer_email, booking_agent_id))
conn.commit()
else:
msg = 'All tickets have been sold out!'
cursor.close()
return render_template('booking_agent/index.html',
message_purchaseTickets=msg)
def registerAuth():
# grabs usertype
usertype = request.form['usertype']
# cursor used to send queries
cursor = conn.cursor()
error = None
# if usertype is customer
if usertype == 'customer':
# grabs information from the forms
email = request.form['email']
password = request.form['password']
name = request.form['name']
building_number = request.form['building_number']
street = request.form['street']
city = request.form['city']
state = request.form['state']
phone_number = request.form['phone_number']
passport_number = request.form['passport_number']
passport_expiration = request.form['passport_expiration']
passport_country = request.form['passport_country']
date_of_birth = request.form['date_of_birth']
# executes query
query = 'SELECT * FROM customer WHERE email = %s'
cursor.execute(query, (email))
# stores the results in a variable
data = cursor.fetchone()
# authenticates the register information
if data:
# if the previous query returns data, then user exists
error = 'User alread exists!'
else:
# generates the hash value of the password
password_hash = pbkdf2_sha256.hash(password)
# inserts into the database
try:
ins = '''
INSERT INTO customer
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) '''
cursor.execute(
ins,
(email, name, password_hash, building_number, street, city,
state, phone_number, passport_number, passport_expiration,
passport_country, date_of_birth))
conn.commit()
except MySQLError as e:
error = 'Got error {!r}, errno is {}'.format(e, e.args[0])
# if usertype is booking_agent
elif usertype == 'booking_agent':
# grabs information from the forms
email = request.form['email']
password = request.form['password']
booking_agent_id = request.form['booking_agent_id']
# executes query
query = 'SELECT * FROM booking_agent WHERE email = %s'
cursor.execute(query, (email))
# stores the results in a variable
data = cursor.fetchone()
# authenticates the register information
if data:
# if the previous query returns data, then user exists
error = 'User already exists!'
else:
# generates the hash value of the password
password_hash = pbkdf2_sha256.hash(password)
# inserts into the database
try:
ins = 'INSERT INTO booking_agent VALUES(%s, %s, %s)'
cursor.execute(ins, (email, password_hash, booking_agent_id))
conn.commit()
except MySQLError as e:
error = 'Got error {!r}, errno is {}'.format(e, e.args[0])
# if usertype is airline staff
else:
# grabs information from the forms
username = request.form['username']
password = request.form['password']
first_name = request.form['first_name']
last_name = request.form['last_name']
date_of_birth = request.form['date_of_birth']
airline_name = request.form['airline_name']
# executes query
query = 'SELECT * FROM airline_staff WHERE username = %s'
cursor.execute(query, (username))
# stores the results in a variable
data = cursor.fetchone()
# authenticates the register information
if data:
# if the previous query returns data, then user exists
error = 'User already exists!'
else:
# generates the hash value of the password
password_hash = pbkdf2_sha256.hash(password)
# inserts into the database
try:
ins = 'INSERT INTO airline_staff VALUES(%s, %s, %s, %s, %s, %s)'
cursor.execute(ins, (username, password_hash, first_name,
last_name, date_of_birth, airline_name))
conn.commit()
except MySQLError as e:
error = 'Got error {!r}, errno is {}'.format(e, e.args[0])
# close the cursor
cursor.close()
# check register status and redirect url
if error:
return render_template('general/register.html', error=error)
else:
return redirect(url_for('general.login'))