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'))