def add_booking():
form = request.form
booking_id = form.get("bookingID")
first_name = form.get("firstName")
last_name = form.get("lastName")
query = """
SELECT *
FROM booking
WHERE booking_id=%s and first_name=%s and last_name=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (booking_id, first_name, last_name))
booking = cursor.fetchone()
cursor.close()
cnx.close()
if booking is None:
flash("The booking you are trying to add doesn't exists!", "error")
return redirect(url_for("my_bookings"))
if booking_exists(booking_id, last_name):
flash("The booking you are trying to add already exists!", "error")
return redirect(url_for("my_bookings"))
cnx = create_connection()
booking["customer_id"] = session["customer_id"]
fields = []
values = []
for key in booking.keys():
fields.append(key)
values.append("%({key})s".format(key=key))
fields = ", ".join(fields)
values = ", ".join(values)
query = "INSERT INTO booking ({fields}) VALUES ({values})".format(
fields=fields, values=values)
cursor = cnx.cursor()
cursor.execute(query, booking)
cnx.commit()
cursor.close()
cnx.close()
flash("The booking has been successfully added to your account", "success")
return redirect(url_for("my_bookings"))
def edit_info():
form = request.form
first_name = form.get("firstName")
last_name = form.get("lastName")
mobile = form.get("mobile")
query = """
UPDATE customer
SET first_name=%s, last_name=%s, mobile=%s
WHERE customer_id=%s and email=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(
query,
(
first_name,
last_name,
mobile,
session.get("customer_id"),
session.get("email"),
),
)
cursor.close()
cnx.commit()
cnx.close()
flash("Profile updated successfully", "success")
return redirect(url_for("profile"))
def confirm_email(token=""):
next_url = request.args.get("next")
email = confirm_token(token)
query = """
UPDATE customer
SET status="Confirmed"
WHERE email=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (email))
cursor.close()
cnx.commit()
cnx.close()
recipient = "{fullname} <{email}>".format(
fullname=get_user_fullname(email),
email=email,
)
data = {
"recipient": recipient,
"subject": "Welcome on board!",
"template": "welcome",
"action_url": url_for("sign_in", _external=True),
}
send_email_mailgun(data=data)
flash("Your account has been confirmed. Thanks!", "success")
return redirect(url_for("sign_in", next=next_url))
def return_airports():
def _format_string(city, code):
return "{city} ({code})".format(city=city, code=code)
airports = get_airports()
airport = str(request.args.get("airport"))
if not airport:
return "OK"
airports = {airport["code"]: airport["city"] for airport in airports}
query = """
SELECT DISTINCT from_airport, GROUP_CONCAT(DISTINCT to_airport ORDER BY to_airport SEPARATOR ",") as to_airport
FROM flight
WHERE from_airport=%s
GROUP BY from_airport
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, airport)
route = cursor.fetchone()
cursor.close()
cnx.close()
destinations = [{
"value": code,
"text": _format_string(airports[code], code)
} for code in route["to_airport"].split(",")]
return jsonify(result=destinations)
def my_bookings():
customer_id = get_customer_id()
query = """
SELECT b.booking_id as id, b.last_name as last_name, a1.city as from_city, a2.city as to_city, DATE_FORMAT(b.booking_date, "%%d %%b %%Y") as date, b.flight_type as flight_type, b.status as status
FROM booking as b, flight as f, airport as a1, airport as a2
WHERE b.customer_id=%s and b.status in (%s, %s) and f.flight_id=b.depart_flight_id and f.dep_date=b.depart_flight_date and f.class=b.flight_class and a1.airport_code=f.from_airport and a2.airport_code=f.to_airport
ORDER BY date DESC
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (customer_id, "Active", "Upcoming"))
upcoming_bookings = cursor.fetchall()
cursor.close()
cursor = cnx.cursor()
cursor.execute(query, (customer_id, "Passed", "Canceled"))
passed_bookings = cursor.fetchall()
cursor.close()
cnx.close()
return render_template(
"customer/my-bookings.html",
upcoming_bookings=upcoming_bookings,
passed_bookings=passed_bookings,
)
def delete_account():
form = request.form
current_password = form.get("currentDeletePassword")
query = """
SELECT password
FROM customer
WHERE customer_id=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (session.get("customer_id")))
db_password = cursor.fetchone()["password"]
cursor.close()
if not chk_pw_hash(db_password, current_password):
cnx.close()
flash("You have entered a wrong password!", "error")
return redirect(url_for("profile"))
query = """
DELETE FROM customer
WHERE customer_id=%s and email=%s
"""
cursor = cnx.cursor()
cursor.execute(query, (session.get("customer_id"), session.get("email")))
cursor.close()
cnx.commit()
cnx.close()
clear_session_user()
return redirect(url_for("index"))
def sign_up_post():
use_mailgun = os.getenv("MAILGUN_ENABLED", False) == "True"
form = request.form
first_name = form.get("firstName")
last_name = form.get("lastName")
mobile = form.get("mobile")
gender = form.get("gender")
email = form.get("email")
password = gen_pw_hash(form.get("password"))
next_url = request.args.get("next")
current_date = datetime.now().date()
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute("SELECT email FROM customer WHERE email=%s", (email))
result = cursor.fetchone()
cursor.close()
if result is not None:
flash("The user already exists", "error")
return redirect(url_for("sign_up", next=next_url))
query = """
INSERT INTO customer (first_name, last_name, email, password, mobile, gender, joined_date, status, customer_type)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, "USER")
"""
cursor = cnx.cursor()
cursor.execute(
query,
(
first_name,
last_name,
email,
password,
mobile,
gender,
current_date,
"Unconfirmed" if use_mailgun else "Confirmed",
),
)
cursor.close()
cnx.commit()
cnx.close()
if use_mailgun:
send_confirm_account_email(email=email, next_url=next_url)
token = generate_token(email=email, TIMED=False)
return redirect(url_for("unconfirmed", token=token, next=next_url))
return redirect(url_for("sign_in", next=next_url))
def sign_in_post():
email = request.form.get("email")
password = request.form.get("password")
next_url = request.args.get("next")
query = """
SELECT customer_id, first_name, last_name, password, customer_type
FROM customer
WHERE email=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (email))
result = cursor.fetchone()
cursor.close()
cnx.close()
if result is None:
flash("The user does not exist!", "error")
return redirect(url_for("sign_in", next=next_url))
if not chk_pw_hash(result["password"], password):
flash("You have entered a wrong password!", "error")
return redirect(url_for("sign_in", next=next_url))
if not user_is_confirmed(email):
send_confirm_account_email(email=email, next_url=next_url)
flash(
"Your account needs to be confirmed first. A new email has been sent!",
"error",
)
return redirect(url_for("unconfirmed", email=email, next=next_url))
full_name = "{fname} {lname}".format(fname=result["first_name"],
lname=result["last_name"])
info = {
"customer_id": result["customer_id"],
"email": email,
"customer_type": result["customer_type"],
"first_name": result["first_name"],
"last_name": result["last_name"],
"full_name": full_name,
}
set_session_user(info)
return redirect(
urllib.parse.quote(next_url
) if next_url is not None else url_for("index"))
def return_date():
query = """
SELECT MAX(arr_date) as date
FROM flight
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query)
date = cursor.fetchone()["date"]
cursor.close()
cnx.close()
return jsonify(date=str(date))
def cancel_booking(booking_id="", booking_last_name=""):
customer_type = get_customer_type()
query = """
UPDATE booking
SET status=%s
WHERE booking_id=%s and last_name=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, ("Canceled", booking_id, booking_last_name))
cursor.close()
query = """
SELECT depart_flight_id, return_flight_id, total_passengers, flight_type
FROM booking
WHERE booking_id=%s and last_name=%s
"""
cursor = cnx.cursor()
cursor.execute(query, (booking_id, booking_last_name))
booking_info = cursor.fetchone()
cursor.close()
query = """
UPDATE flight
SET occupied_capacity=occupied_capacity-%s
WHERE flight_id=%s
"""
cursor = cnx.cursor()
cursor.execute(
query,
(booking_info["total_passengers"], booking_info["depart_flight_id"]))
if booking_info["flight_type"] == "Roundtrip":
cursor.execute(query, (booking_info["total_passengers"],
booking_info["return_flight_id"]))
cursor.close()
cnx.commit()
cnx.close()
if customer_type == "GUEST":
session["success"] = "canceled"
else:
flash("The booking has been successfully canceled", "success")
return redirect(
url_for("manage_booking_page") if customer_type ==
"GUEST" else url_for("my_bookings"))
def profile():
query = """
SELECT first_name, last_name, email, mobile, gender, DATE_FORMAT(joined_date, "%%d %%b %%Y") as joined_date
FROM customer
WHERE customer_id=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (session.get("customer_id")))
user_info = cursor.fetchone()
cursor.close()
cnx.close()
return render_template("customer/user-profile.html", user_info=user_info)
def reset_password(token=""):
use_mailgun = os.getenv("MAILGUN_ENABLED") == "True"
email = confirm_token(token)
if not email:
flash(
"The reset link is invalid or has expired!",
"error",
)
return redirect(url_for("index"))
if request.method == "GET":
return render_template("customer/reset-password.html")
password = gen_pw_hash(request.form.get("password"))
query = """
UPDATE customer
SET password=%s
WHERE email=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (password, email))
cursor.close()
cnx.commit()
cnx.close()
if use_mailgun:
recipient = "{fullname} <{email}>".format(
fullname=get_user_fullname(email),
email=email,
)
data = {
"recipient": recipient,
"subject": "Your password has been updated!",
"template": "updated_password",
"action_url": url_for("sign_in", _external=True),
}
send_email_mailgun(data=data)
flash("Your password has been updated! Please sign in.", "success")
return redirect(url_for("sign_in"))
def return_seats():
depart_flight_id = request.args.get("departFlightID")
return_flight_id = request.args.get("returnFlightID")
depart_flight_id = depart_flight_id if depart_flight_id is not None else ""
return_flight_id = return_flight_id if return_flight_id is not None else ""
query = """
SELECT phb.seat as id
FROM booking as b, pass_has_booking as phb
WHERE (b.depart_flight_id=%s or b.return_flight_id=%s) and phb.booking_id=b.booking_id
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (depart_flight_id, return_flight_id))
seats = cursor.fetchall()
cursor.close()
cnx.close()
return jsonify(result=seats)
def change_password():
form = request.form
current_password = form.get("currentPassword")
new_password = gen_pw_hash(form.get("newPassword"))
query = """
SELECT password
FROM customer
WHERE customer_id=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (session.get("customer_id")))
db_password = cursor.fetchone()["password"]
cursor.close()
if not chk_pw_hash(db_password, current_password):
cnx.close()
flash("You have entered a wrong password!", "error")
return redirect(url_for("profile"))
query = """
UPDATE customer
SET password=%s
WHERE customer_id=%s and email=%s
"""
cursor = cnx.cursor()
cursor.execute(
query,
(new_password, session.get("customer_id"), session.get("email")))
cursor.close()
cnx.commit()
cnx.close()
return redirect(url_for("sign_out"))
def picked_flight(
depart_flight_id="",
return_flight_id="",
depart_date="",
return_date="",
passenger_num=0,
price=0,
flight_class="",
is_roundtrip=False,
):
session.pop("is_guest", None)
customer_id = get_customer_id()
customer_type = get_customer_type()
is_roundtrip = is_roundtrip == "True"
if customer_type == "USER":
query = """
SELECT status
FROM booking
WHERE customer_id=%s and depart_flight_id=%s and status in ("Active", "Upcoming")
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(query, (customer_id, depart_flight_id))
result = cursor.fetchone()
cursor.close()
cnx.close()
if result is not None:
flash("You have already booked this flight route!", "error")
return redirect(url_for("index"))
WHERE = """
f.flight_id=%s and f.dep_date=%s and f.class=%s and al.airline_code=f.airline and ap.airplane_model=f.airplane and aprt1.airport_code=f.from_airport and aprt2.airport_code=f.to_airport
"""
params = (
depart_flight_id,
depart_date,
flight_class,
)
if is_roundtrip:
WHERE = """
f1.flight_id=%s and f1.dep_date=%s and f1.class=%s and f2.flight_id=%s and f2.dep_date=%s and f2.class=%s and al1.airline_code=f1.airline and ap1.airplane_model=f1.airplane and aprt1.airport_code=f1.from_airport and aprt2.airport_code=f1.to_airport and al2.airline_code=f2.airline and ap2.airplane_model=f2.airplane and aprt3.airport_code=f2.from_airport and aprt4.airport_code=f2.to_airport
"""
params += (
return_flight_id,
return_date,
flight_class,
)
_, flight = get_flights(
is_roundtrip=is_roundtrip, params=params, WHERE=WHERE, FETCH_ALL=False
)
price = int(price)
passenger_num = int(passenger_num)
total_price = price * passenger_num
depart_flight, return_flight = build_selected_flights(
flight=flight, is_roundtrip=is_roundtrip
)
picked_flight = depart_flight
if return_flight is not None:
picked_flight += '<hr class="my-2">'
picked_flight += return_flight
return render_template(
"booking/new-booking.html",
picked_flight=picked_flight,
flight_class=flight_class,
num_passenger=passenger_num,
price=price,
total_price=total_price,
is_roundtrip=is_roundtrip,
)
def new_booking():
customer_id = get_customer_id()
form = request.form
is_roundtrip = form.get("isRoundtrip") == "True"
flight_info = {
"depart_flight_id": form.get("DepartFlightID"),
"return_flight_id": form.get("ReturnFlightID", None),
"depart_flight_date": form.get("DepartDate"),
"return_flight_date": form.get("ReturnDate", None),
"flight_class": form.get("flightClass"),
"type": "Roundtrip" if is_roundtrip else "Oneway",
"total_passengers": int(form.get("numPassenger")),
"price_per_passenger": form.get("pricePerPassenger"),
"total_price": form.get("totalPrice"),
}
flight_info["depart_flight_date"] = datetime.strptime(
flight_info["depart_flight_date"], "%d %b %Y").date()
if flight_info["return_flight_date"]:
flight_info["return_flight_date"] = datetime.strptime(
flight_info["return_flight_date"], "%d %b %Y").date()
query = """
SELECT booking_id
FROM booking
WHERE booking_id=%s"""
cnx = create_connection()
cursor = cnx.cursor()
booking_id = fake.lexify(text="??????",
letters="ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")
cursor.execute(query, booking_id)
result = cursor.fetchone()
while result is not None:
booking_id = fake.lexify(
text="??????", letters="ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")
cursor.execute(query, booking_id)
result = cursor.fetchone()
cursor.close()
current_date = datetime.today().date()
passenger_info = []
for i in range(flight_info["total_passengers"]):
first_name = "firstNamePassenger-{num}".format(num=i + 1)
last_name = "lastNamePassenger-{num}".format(num=i + 1)
identifier = "idPassenger-{num}".format(num=i + 1)
seat = "seatPassenger-{num}".format(num=i + 1)
seat_class = "seatClassPassenger-{num}".format(num=i + 1)
seat_price = "seatPricePassenger-{num}".format(num=i + 1)
passenger = {
"id": form.get(identifier),
"first_name": form.get(first_name),
"last_name": form.get(last_name),
"seat": form.get(seat),
"seat_class": form.get(seat_class),
"seat_price": form.get(seat_price),
}
passenger_info.append(passenger)
contact_info = {
"first_name": form.get("contactFirstName"),
"last_name": form.get("contactLastName"),
"email": form.get("contactEmail"),
"mobile": form.get("contactMobile"),
}
booking = {
"booking_id": booking_id,
"customer_id": customer_id,
"depart_flight_id": flight_info["depart_flight_id"],
"return_flight_id": flight_info["return_flight_id"],
"depart_flight_date": flight_info["depart_flight_date"],
"return_flight_date": flight_info["return_flight_date"],
"flight_class": flight_info["flight_class"],
"first_name": contact_info["first_name"],
"last_name": contact_info["last_name"],
"email": contact_info["email"],
"mobile": contact_info["mobile"],
"booking_date": current_date,
"last_modify_date": current_date,
"total_passengers": flight_info["total_passengers"],
"price_per_passenger": flight_info["price_per_passenger"],
"total_price": flight_info["total_price"],
"flight_type": flight_info["type"],
"status": "Upcoming",
}
fields = []
values = []
for key in booking.keys():
fields.append(key)
values.append("%({key})s".format(key=key))
fields = ", ".join(fields)
values = ", ".join(values)
query = "INSERT INTO booking ({fields}) VALUES ({values})".format(
fields=fields, values=values)
cursor = cnx.cursor()
cursor.execute(query, booking)
cursor.close()
query = """
UPDATE flight
SET occupied_capacity=occupied_capacity+%s
WHERE flight_id=%s
"""
cursor = cnx.cursor()
cursor.execute(
query,
(flight_info["total_passengers"], flight_info["depart_flight_id"]))
if is_roundtrip:
cursor.execute(
query,
(flight_info["total_passengers"], flight_info["return_flight_id"]))
cursor.close()
query_p = """
INSERT INTO passenger (passenger_id, first_name, last_name)
VALUE(%s, %s, %s)
"""
query_pb = """
INSERT INTO pass_has_booking (passenger_id, booking_id, seat, seat_class, seat_price)
VALUE(%s, %s, %s, %s, %s)
"""
for passenger in passenger_info:
try:
cursor = cnx.cursor()
cursor.execute(
query_p,
(passenger["id"], passenger["first_name"],
passenger["last_name"]),
)
cursor.close()
except:
print("Passenger with id={id} exists".format(id=passenger["id"]))
cursor = cnx.cursor()
cursor.execute(
query_pb,
(
passenger["id"],
booking_id,
passenger["seat"],
passenger["seat_class"],
passenger["seat_price"],
),
)
cursor.close()
cnx.commit()
cnx.close()
return redirect(
url_for(
"view_booking",
booking_id=booking_id,
booking_last_name=contact_info["last_name"],
go_back=False,
))
def modify_booking_post():
form = request.form
booking_id = form.get("bookingID")
total_passengers = int(form.get("numPassengers"))
first_name = form.get("contactFirstName")
last_name = form.get("contactLastName")
email = form.get("contactEmail")
mobile = form.get("contactMobile")
old_last_name = form.get("oldContactLastName")
addtional_price = int(form.get("totalPrice"))
customer_type = get_customer_type()
passenger_info = []
for i in range(total_passengers):
identifier = "idPassenger-{num}".format(num=i + 1)
seat = "seatPassenger-{num}".format(num=i + 1)
seat_class = "seatClassPassenger-{num}".format(num=i + 1)
seat_price = "seatPricePassenger-{num}".format(num=i + 1)
passenger = {
"id": form.get(identifier),
"seat": form.get(seat),
"seat_class": form.get(seat_class),
"seat_price": form.get(seat_price),
}
passenger_info.append(passenger)
query = """
UPDATE booking
SET first_name=%s, last_name=%s, email=%s, mobile=%s, total_price=total_price+%s, last_modify_date=CURRENT_DATE
WHERE booking_id=%s and last_name=%s
"""
cnx = create_connection()
cursor = cnx.cursor()
cursor.execute(
query,
(
first_name,
last_name,
email,
mobile,
addtional_price,
booking_id,
old_last_name,
),
)
cursor.close()
query = """
UPDATE pass_has_booking
SET seat=%s, seat_class=%s, seat_price=%s
WHERE passenger_id=%s and booking_id=%s
"""
for passenger in passenger_info:
cursor = cnx.cursor()
cursor.execute(
query,
(
passenger["seat"],
passenger["seat_class"],
passenger["seat_price"],
passenger["id"],
booking_id,
),
)
cursor.close()
cnx.commit()
cnx.close()
go_back = False if customer_type == "GUEST" else True
return redirect(
url_for(
"view_booking",
booking_id=booking_id,
booking_last_name=last_name,
go_back=go_back,
))
import app
"""
Utility to add a new URL field to the Track table in the db
"""
table_name = 'Track' # name of the table
new_column = 'url' # name of the new column
column_type = 'TEXT'
default_val = ''
# Connecting to the database file
conn = app.create_connection()
cursor = conn.cursor()
# Adding a new column with a default row value
cursor.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'".format(
tn=table_name, cn=new_column, ct=column_type, df=default_val))
# Committing changes and closing the connection to the database file
conn.commit()
conn.close()
