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)