def commission_default():
# grabs information
booking_agent_id = session['booking_agent_id']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT
SUM(price) * 0.1 as sum_commission,
AVG(price) * 0.1 as avg_commission,
COUNT(*) as num_tickets
FROM purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE booking_agent_id = %s AND
purchase_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH) '''
cursor.execute(query, (booking_agent_id))
# stores the results in a variable
data = cursor.fetchone()
cursor.close()
# check data
if data['num_tickets'] > 0:
return render_template('booking_agent/index.html',
result_commission_default=data)
else:
msg = 'You did not sell any tickets in the period!'
return render_template('booking_agent/index.html',
message_commission=msg)
def viewMyFlightsOption():
# grabs information
airline_name = session['airline_name']
start_date = request.form['start_date']
end_date = request.form['end_date']
departure_airport = request.form['departure_airport']
arrival_airport = request.form['arrival_airport']
# check consistence of dates
if start_date > end_date:
error = 'Error: end date is earlier than start date!'
return render_template('airline_staff/index.html', message=error)
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT *
FROM flight
WHERE airline_name = %s AND departure_airport = %s
AND arrival_airport = %s AND departure_time BETWEEN %s AND %s
ORDER BY departure_time DESC '''
cursor.execute(query, (airline_name, departure_airport, arrival_airport,
start_date, end_date))
# stores the results in a variable
data = cursor.fetchall()
cursor.close()
# check data
if data:
return render_template('airline_staff/index.html',
result_viewMyFlights=data)
else:
msg = 'No records are found!'
return render_template('airline_staff/index.html', message=msg)
def viewMyFlights():
# grabs information
booking_agent_email = session['username']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT *
FROM booking_agent NATURAL JOIN purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE email = %s AND departure_time > NOW()
ORDER BY departure_time '''
cursor.execute(query, (booking_agent_email))
# stores the results in a variable
data = cursor.fetchall()
cursor.close()
# check data
if data:
return render_template('booking_agent/index.html',
result_viewMyFlights=data)
else:
msg = 'No records are found!'
return render_template('booking_agent/index.html',
message_viewMyFlights=msg)
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 commission_option():
# grabs information
booking_agent_id = session['booking_agent_id']
start_date = request.form['start_date']
end_date = request.form['end_date']
# check consistence of dates
if start_date > end_date:
error = 'Error: start date is earlier than end date!'
return render_template('general/index.html', message_commission=error)
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT
SUM(price) * 0.1 as sum_commission,
AVG(price) * 0.1 as avg_commission,
COUNT(*) as num_tickets
FROM purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE booking_agent_id = %s AND
purchase_date BETWEEN %s AND %s '''
cursor.execute(query, (booking_agent_id, start_date, end_date))
# stores the results in a variable
data = cursor.fetchone()
cursor.close()
# check data
if data['num_tickets'] > 0:
return render_template('booking_agent/index.html',
result_commission_option=data)
else:
msg = 'You did not sell any tickets in the period!'
return render_template('booking_agent/index.html',
message_commission=msg)
def viewMyFlightsOption():
# grabs information
booking_agent_email = session['username']
start_date = request.form['start_date']
end_date = request.form['end_date']
# check consistence of dates
if start_date > end_date:
error = 'Error: end date is earlier than start date!'
return render_template('customer/index.html',
message_viewMyFlights=error)
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT *
FROM booking_agent NATURAL JOIN purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE email = %s AND DATE(departure_time) BETWEEN %s AND %s '''
cursor.execute(query, (booking_agent_email, start_date, end_date))
# stores the results in a variable
data = cursor.fetchall()
cursor.close()
# check data
if data:
return render_template('booking_agent/index.html',
result_viewMyFlights=data)
else:
msg = 'No records are found!'
return render_template('booking_agent/index.html',
message_viewMyFlights=msg)
def viewAllCustomers():
# grabs information
airline_name = session['airline_name']
flight_num = request.form['flight_num']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT ticket_id, customer_email, booking_agent_id, purchase_date
FROM ticket NATURAL JOIN purchases
WHERE airline_name = %s AND flight_num = %s
ORDER by purchase_date DESC '''
cursor.execute(query, (airline_name, flight_num))
data = cursor.fetchall()
# check data
if data:
return render_template('airline_staff/index.html',
airline_name=airline_name,
flight_num=flight_num,
result_viewAllCustomers=data)
else:
msg = 'No customers yet!'
return render_template('airline_staff/index.html', message=msg)
def checkStatus():
# grabs information from the forms
flight_num = request.form['flight_num']
departure_date = request.form['departure_date']
arrival_date = request.form['arrival_date']
# check consistence of dates
if departure_date > arrival_date:
error = 'Error: arrival date is earlier than departure date!'
return render_template('general/index.html', message_status=error)
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT *
FROM flight
WHERE flight_num = %s AND DATE(departure_time) = %s AND DATE(arrival_time) = %s
ORDER BY airline_name, flight_num '''
cursor.execute(query, (flight_num, departure_date, arrival_date))
# stores the results in a variable
data = cursor.fetchall()
cursor.close()
# check data
if data:
return render_template('general/index.html', result_status=data)
else:
msg = 'No records are found!'
return render_template('general/index.html', message_status=msg)
def checkUpcoming():
# grabs information from the forms
departure_airport = request.form['departure_airport']
departure_date = request.form['departure_date']
arrival_airport = request.form['arrival_airport']
arrival_date = request.form['arrival_date']
# check consistence of dates
if departure_date > arrival_date:
error = 'Error: arrival date is earlier than departure date!'
return render_template('general/index.html', message_upcoming=error)
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT *
FROM flight
WHERE departure_airport = %s AND DATE(departure_time) = %s AND
arrival_airport = %s AND DATE(arrival_time) = %s '''
cursor.execute(
query,
(departure_airport, departure_date, arrival_airport, arrival_date))
# stores the results in a variable
data = cursor.fetchall()
cursor.close()
# check data
if data:
return render_template('general/index.html', result_upcoming=data)
else:
msg = 'No records are found!'
return render_template('general/index.html', message_upcoming=msg)
def viewMyFlights():
# grabs information
airline_name = session['airline_name']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT *
FROM flight
WHERE airline_name = %s AND
departure_time BETWEEN CURDATE() AND DATE_ADD(NOW(), INTERVAL 30 DAY)
ORDER BY departure_time '''
cursor.execute(query, (airline_name))
# stores the results in a variable
data = cursor.fetchall()
cursor.close()
# check data
if data:
return render_template('airline_staff/index.html',
result_viewMyFlights=data)
else:
msg = 'No records are found!'
return render_template('airline_staff/index.html', message=msg)
def loginAuth():
# grabs information from the forms
usertype = request.form['usertype']
username = request.form['username']
password = request.form['password']
# cursor used to send queries
cursor = conn.cursor()
# executes query
attr_username = '******' if usertype == 'airline_staff' else 'email'
query = 'SELECT * FROM {} WHERE {} = %s'.format(usertype, attr_username)
cursor.execute(query, (username))
# stores the results in a variable
data = cursor.fetchone()
cursor.close()
error = None
# authenticates the login information
if data:
if pbkdf2_sha256.verify(password, data['password']):
# creates a session for the the user
session['username'] = username
session['usertype'] = usertype
# store booking_agent_id for booking_agent, airline_name for airline staff
if usertype == 'booking_agent':
session['booking_agent_id'] = data['booking_agent_id']
elif usertype == 'airline_staff':
session['airline_name'] = data['airline_name']
return redirect(url_for('{}.homepage'.format(usertype)))
else:
error = 'Incorrect password!'
else:
error = 'User does not exist!'
return render_template('general/login.html', error=error)
def viewReports():
# grabs information
airline_name = session['airline_name']
start_month = request.form['start_month']
end_month = request.form['end_month']
# check consistence of months
if start_month > end_month:
error = 'Error: end month is earlier than start month!'
return render_template('airline_staff/view.html',
message_viewReports=error)
# computes date
start_date = datetime.strptime(start_month, '%Y-%m').date()
start_date_str = start_date.strftime('%Y-%m-%d')
end_date = datetime.strptime(end_month,
'%Y-%m').date() + relativedelta(months=+1)
end_date_str = end_date.strftime('%Y-%m-%d')
diff = (end_date.year - start_date.year) * 12 + (end_date.month -
start_date.month)
# cursor used to send queries
cursor = conn.cursor()
# query
query = '''
SELECT COUNT(ticket_id) as total
FROM purchases NATURAL JOIN ticket
WHERE airline_name = %s AND purchase_date >= %s AND purchase_date < %s '''
# total
cursor.execute(query, (airline_name, start_date_str, end_date_str))
data = cursor.fetchone()
total = data['total'] if data['total'] != None else 0
# monthwise
monthwise_label = []
monthwise_total = []
end_date = start_date + relativedelta(months=+1)
for _ in range(diff):
start_date_str = start_date.strftime('%Y-%m-%d')
end_date_str = end_date.strftime('%Y-%m-%d')
cursor.execute(query, (airline_name, start_date_str, end_date_str))
monthwise = cursor.fetchone()
monthwise_label.append(start_date.strftime('%y/%m'))
monthwise_total.append(
monthwise['total'] if monthwise['total'] != None else 0)
start_date += relativedelta(months=+1)
end_date += relativedelta(months=+1)
cursor.close()
return render_template('airline_staff/view.html',
total=total,
monthwise_label=monthwise_label,
monthwise_total=monthwise_total)
def trackMySpendingOptional():
# grabs information
customer_email = session['username']
start_month = request.form['start_month']
end_month = request.form['end_month']
# check consistence of months
if start_month > end_month:
error = 'Error: end month is earlier than start month!'
return render_template('customer/index.html',
message_trackMySpendingOptional=error)
# computes date
start_date = datetime.strptime(start_month, '%Y-%m').date()
start_date_str = start_date.strftime('%Y-%m-%d')
end_date = datetime.strptime(end_month,
'%Y-%m').date() + relativedelta(months=+1)
end_date_str = end_date.strftime('%Y-%m-%d')
diff = (end_date.year - start_date.year) * 12 + (end_date.month -
start_date.month)
# cursor used to send queries
cursor = conn.cursor()
# query
query = '''
SELECT SUM(price) as total
FROM purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE customer_email = %s AND purchase_date >= %s AND purchase_date < %s'''
# total
cursor.execute(query, (customer_email, start_date_str, end_date_str))
data = cursor.fetchone()
total = data['total'] if data['total'] != None else 0
# monthwise
monthwise_label = []
monthwise_total = []
end_date = start_date + relativedelta(months=+1)
for _ in range(diff):
start_date_str = start_date.strftime('%Y-%m-%d')
end_date_str = end_date.strftime('%Y-%m-%d')
cursor.execute(query, (customer_email, start_date_str, end_date_str))
monthwise = cursor.fetchone()
monthwise_label.append(start_date.strftime('%y/%m'))
monthwise_total.append(
monthwise['total'] if monthwise['total'] != None else 0)
start_date += relativedelta(months=+1)
end_date += relativedelta(months=+1)
cursor.close()
return render_template('customer/index.html',
total_option=total,
monthwise_label_option=monthwise_label,
monthwise_total_option=monthwise_total)
def viewTop5BookingAgent():
# grabs information
airline_name = session['airline_name']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT booking_agent_id, COUNT(ticket_id) as count
FROM ticket NATURAL JOIN purchases
WHERE airline_name = %s AND booking_agent_id IS NOT NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND CURDATE()
GROUP BY booking_agent_id
ORDER by count DESC
LIMIT 5 '''
cursor.execute(query, (airline_name))
top5bycount_past_month = cursor.fetchall()
query = '''
SELECT booking_agent_id, COUNT(ticket_id) as count
FROM ticket NATURAL JOIN purchases
WHERE airline_name = %s AND booking_agent_id IS NOT NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND CURDATE()
GROUP BY booking_agent_id
ORDER by count DESC
LIMIT 5 '''
cursor.execute(query, (airline_name))
top5bycount_past_year = cursor.fetchall()
query = '''
SELECT booking_agent_id, SUM(price) * 0.1 as commission
FROM ticket NATURAL JOIN purchases NATURAL JOIN flight
WHERE airline_name = %s AND booking_agent_id IS NOT NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND CURDATE()
GROUP BY booking_agent_id
ORDER by commission DESC
LIMIT 5 '''
cursor.execute(query, (airline_name))
top5bycommission_past_year = cursor.fetchall()
cursor.close()
# check data
msg = None
if top5bycount_past_year == None or top5bycount_past_year == ():
msg = 'No records in the last year!'
elif top5bycount_past_month == None or top5bycount_past_month == ():
msg = 'No records in the last month!'
return render_template(
'airline_staff/view.html',
top5bycount_past_month=top5bycount_past_month,
top5bycount_past_year=top5bycount_past_year,
top5bycommission_past_year=top5bycommission_past_year,
message_viewTop5BookingAgent=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 viewFlightsTaken():
# grabs information
airline_name = session['airline_name']
customer_email = request.form['customer_email']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT customer_email, flight_num, purchase_date
FROM ticket NATURAL JOIN purchases
WHERE airline_name = %s AND customer_email = %s
ORDER by purchase_date DESC '''
cursor.execute(query, (airline_name, customer_email))
data = cursor.fetchall()
return render_template('airline_staff/view.html',
result_viewFlightsTaken=data)
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 trackMySpendingDefault():
# grabs information
customer_email = session['username']
# computes date
end_date = (date.today() + relativedelta(months=+1)).replace(day=1)
end_date_str = end_date.strftime('%Y-%m-%d')
start_date = end_date - relativedelta(months=+6)
start_date_str = start_date.strftime('%Y-%m-%d')
# cursor used to send queries
cursor = conn.cursor()
# query
query = '''
SELECT SUM(price) as total
FROM purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE customer_email = %s AND purchase_date >= %s AND purchase_date < %s'''
# total
cursor.execute(query, (customer_email, start_date_str, end_date_str))
data = cursor.fetchone()
total = data['total'] if data['total'] != None else 0
# monthwise
monthwise_label = []
monthwise_total = []
end_date = start_date + relativedelta(months=+1)
for _ in range(6):
start_date_str = start_date.strftime('%Y-%m-%d')
end_date_str = end_date.strftime('%Y-%m-%d')
cursor.execute(query, (customer_email, start_date_str, end_date_str))
monthwise = cursor.fetchone()
monthwise_label.append(start_date.strftime('%y/%m'))
monthwise_total.append(
monthwise['total'] if monthwise['total'] != None else 0)
start_date += relativedelta(months=+1)
end_date += relativedelta(months=+1)
cursor.close()
return render_template('customer/index.html',
total=total,
monthwise_label=monthwise_label,
monthwise_total=monthwise_total)
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 viewTopCustomers():
# grabs information
booking_agent_id = session['booking_agent_id']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT customer_email, COUNT(*) as count
FROM purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE booking_agent_id = %s AND
purchase_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY customer_email
ORDER BY count DESC
LIMIT 5 '''
cursor.execute(query, (booking_agent_id))
top5_by_count = cursor.fetchall()
query = '''
SELECT customer_email, SUM(price) * 0.1 as commission
FROM purchases NATURAL JOIN ticket NATURAL JOIN flight
WHERE booking_agent_id = %s AND
purchase_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY customer_email
ORDER BY commission DESC
LIMIT 5 '''
cursor.execute(query, (booking_agent_id))
top5_by_commission = cursor.fetchall()
cursor.close()
# check status
msg = None
if top5_by_commission == None or top5_by_commission == ():
msg = 'No records in the last year!'
elif top5_by_count == None or top5_by_count == ():
msg = 'No records in the last 6 months!'
return render_template('booking_agent/index.html',
top5_by_count=top5_by_count,
top5_by_commission=top5_by_commission,
message_viewTopCustomers=msg)
def viewTop3Destinations():
#grabs information
airline_name = session['airline_name']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT arrival_airport, airport_city, COUNT(ticket_id) as count
FROM flight NATURAL JOIN ticket NATURAL JOIN purchases, airport
WHERE airline_name = %s AND arrival_airport = airport_name AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND CURDATE()
GROUP BY arrival_airport
ORDER BY count DESC
LIMIT 3 '''
cursor.execute(query, (airline_name))
top3_past3month = cursor.fetchall()
query = '''
SELECT arrival_airport, airport_city, COUNT(ticket_id) as count
FROM flight NATURAL JOIN ticket NATURAL JOIN purchases, airport
WHERE airline_name = %s AND arrival_airport = airport_name AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND CURDATE()
GROUP BY arrival_airport
ORDER BY count DESC
LIMIT 3 '''
cursor.execute(query, (airline_name))
top3_past1year = cursor.fetchall()
cursor.close()
# check data
msg = None
if top3_past1year == None or top3_past1year == ():
msg = 'No records in the last year!'
elif top3_past3month == None or top3_past3month == ():
msg = 'No records in the last 3 months!'
return render_template('airline_staff/view.html',
top3_past3month=top3_past3month,
top3_past1year=top3_past1year,
message_viewTop3Destinations=msg)
def viewFrequentCustomers():
# grabs information
airline_name = session['airline_name']
# cursor used to send queries
cursor = conn.cursor()
# executes query
query = '''
SELECT customer_email, COUNT(ticket_id) AS count
FROM ticket NATURAL JOIN purchases
WHERE airline_name = %s AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND CURDATE()
GROUP BY customer_email
ORDER by count DESC '''
cursor.execute(query, (airline_name))
data = cursor.fetchall()
if data != None and data != ():
return render_template('airline_staff/view.html',
result_viewFrequentCustomers=data)
else:
msg = 'No records are found!'
return render_template('airline_staff/view.html',
message_viewFrequentCustomers=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'))
def compareRevenue():
# grabs information
airline_name = session['airline_name']
# cursor used to send queries
cursor = conn.cursor()
# revenue in the last month
query = '''
SELECT SUM(price) as revenue
FROM flight NATURAL JOIN ticket NATURAL JOIN purchases
WHERE airline_name = %s AND booking_agent_id IS NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND CURDATE() '''
cursor.execute(query, (airline_name))
data = cursor.fetchone()
if data == None:
revenue_direct_sale_last_month = 0
elif data['revenue'] == None:
revenue_direct_sale_last_month = 0
else:
revenue_direct_sale_last_month = data['revenue']
query = '''
SELECT SUM(price) as revenue
FROM flight NATURAL JOIN ticket NATURAL JOIN purchases
WHERE airline_name = %s AND booking_agent_id IS NOT NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND CURDATE() '''
cursor.execute(query, (airline_name))
data = cursor.fetchone()
if data == None:
revenue_indirect_sale_last_month = 0
elif data['revenue'] == None:
revenue_indirect_sale_last_month = 0
else:
revenue_indirect_sale_last_month = data['revenue']
# revenue in the last year
query = '''
SELECT SUM(price) as revenue
FROM flight NATURAL JOIN ticket NATURAL JOIN purchases
WHERE airline_name = %s AND booking_agent_id IS NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND CURDATE() '''
cursor.execute(query, (airline_name))
data = cursor.fetchone()
if data == None:
revenue_direct_sale_last_year = 0
elif data['revenue'] == None:
revenue_direct_sale_last_year = 0
else:
revenue_direct_sale_last_year = data['revenue']
query = '''
SELECT SUM(price) as revenue
FROM flight NATURAL JOIN ticket NATURAL JOIN purchases
WHERE airline_name = %s AND booking_agent_id IS NOT NULL AND
purchase_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND CURDATE() '''
cursor.execute(query, (airline_name))
data = cursor.fetchone()
if data == None:
revenue_indirect_sale_last_year = 0
elif data['revenue'] == None:
revenue_indirect_sale_last_year = 0
else:
revenue_indirect_sale_last_year = data['revenue']
# check data
msg = None
if revenue_direct_sale_last_year * revenue_indirect_sale_last_year == 0:
msg = 'No sale in the last year!'
elif revenue_direct_sale_last_month * revenue_indirect_sale_last_month == 0:
msg = 'No sale in the last month!'
return render_template(
'airline_staff/compare.html',
revenue_direct_sale_last_month=revenue_direct_sale_last_month,
revenue_indirect_sale_last_month=revenue_indirect_sale_last_month,
revenue_direct_sale_last_year=revenue_direct_sale_last_year,
revenue_indirect_sale_last_year=revenue_indirect_sale_last_year,
message=msg)
