def free_parking_space():
space_id = request.json["space_id"]
connection_object = get_db("ticket_booth")
cursor = connection_object.cursor()
update_query = (
"""UPDATE ParkingSpace SET is_occupied = 0 WHERE space_id = %s""")
try:
cursor.execute(update_query, (space_id, ))
connection_object.commit()
response = {
"message": "Parking space freed.",
"data": {
"space_id": space_id
},
}
return make_response(jsonify(response), 201)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
except TypeError as err:
print("No valid json data received\n", err)
finally:
cursor.close()
def check_out_car_record():
license_plate = request.json["license_plate"]
check_out_time = datetime.now().replace(microsecond=0)
print(check_out_time)
connection_object = get_db("ticket_booth")
cursor = connection_object.cursor(named_tuple=True)
update_query = """UPDATE CarRecord SET check_out = %s WHERE license_plate = %s AND check_out IS NULL"""
select_query = """SELECT * FROM CarRecord WHERE check_out = %s AND license_plate = %s"""
try:
cursor.execute(update_query, (check_out_time, license_plate))
cursor.execute(select_query, (check_out_time, license_plate))
data = cursor.fetchone()
connection_object.commit()
response = {
"message": "Car record updated. Checked out.",
"data": {
"record_id": str(data.record_id),
"license_plate": data.license_plate,
"space_id": data.space_id,
"check_in": data.check_in,
"check_out": data.check_out,
"is_paid": data.is_paid,
},
}
return make_response(jsonify(response), 201)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
except TypeError as err:
print("No valid json data received\n", err)
finally:
cursor.close()
def login():
"""Login as a car owner
GET:
responses:
200 OK on success,
404 Not Found
POST:
parameters:
email: str
password: str
responses:
204 No Content on success
"""
if request.method == "POST":
email = request.form["email"]
password = request.form["password"]
connection_object = get_db("zernike_parking_app")
error = None
verify_email_query = """SELECT EXISTS(SELECT email FROM CarOwner WHERE email=%s) AS is_registered"""
get_pass_hash_query = """SELECT password FROM CarOwner WHERE email=%s"""
cursor = connection_object.cursor(named_tuple=True)
try:
cursor.execute(verify_email_query, (email,))
email_is_registered = cursor.fetchone().is_registered
if email_is_registered == 0:
error = "Incorrect email address."
else:
cursor.execute(get_pass_hash_query, (email,))
password_hash = cursor.fetchone().password
if not sha256_crypt.verify(password, password_hash):
error = "Incorrect password."
if error is None:
cursor.execute(
"SELECT BIN_TO_UUID(owner_id) AS owner_id FROM CarOwner WHERE email=%s ",
(email,),
)
user_id = cursor.fetchone().owner_id
session.clear()
session["user_id"] = user_id
return redirect(
url_for("billboard.get_parking_spaces_info"), code=204
)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
except ValueError as err:
print("login failure: ", err)
finally:
cursor.close()
if error is not None:
flash(error)
return render_template("auth/login.html")
def get_parking_spaces_info():
"""Gives information about the parking lots in terms of parking spaces
Returns
-------
rows : list
List of named tuples containing strings of the columns data,
representing the rows in the ParkingLot table of the MySQL database
i.e.
rows[n].name -> (str) name of the parking lot
rows[n].non_charging -> (str) number of available/occupied non-charging spaces
rows[n].charging -> (str) number of available/occupied charging spaces
is_occupied_arg : str
"0" or "1"
specifies whether the returned data represents available or occupied spaces
"""
is_occupied_field = request.args.get("is_occupied")
if is_occupied_field is None:
is_occupied_field = "0"
connection_object = get_db("billboard")
cursor = connection_object.cursor(named_tuple=True)
select_query = """SELECT name,
COUNT(IF(space_type = 'non_charging', 1, NULL)) 'non_charging',
COUNT(IF(space_type = 'charging', 1, NULL)) 'charging'
FROM (
SELECT pl.lot_id, pl.name, pl.location, pl.capacity_all, pl.capacity_charging, ps.space_id, ps.space_type, ps.is_occupied
FROM ParkingLot pl
INNER JOIN ParkingSpace ps
ON pl.lot_id = ps.lot_id) pl_ps
WHERE pl_ps.is_occupied = %s
GROUP BY name
"""
try:
cursor.execute(select_query, (is_occupied_field, ))
rows = cursor.fetchall()
if rows is None:
flash("No recorded data")
return render_template(
"billboard/info.html",
lots_info=rows,
is_occupied_arg=is_occupied_field,
)
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
abort(500)
def register_car():
owner_id = g.user.owner_id
if request.method == "POST":
license_plate = request.form["license_plate"]
brand_name = request.form["brand_name"]
fuel_type = request.form["fuel_type"]
connection_object = get_db("zernike_parking_app")
error = None
cursor = connection_object.cursor(named_tuple=True)
duplication_check_query = """SELECT EXISTS(SELECT license_plate FROM Car WHERE license_plate =%s) AS isRegistered"""
insert_query = """INSERT INTO Car (license_plate, owner_id, brand_name, fuel_type) VALUES (%s,%s,%s,%s)"""
try:
error = validate_car_data(license_plate, brand_name, fuel_type)
cursor.execute(duplication_check_query, (license_plate, ))
if cursor.fetchone().isRegistered == 1:
error = (
"Already registered car with this license plate (%s)." %
license_plate)
if error is None:
cursor.execute(
insert_query,
(license_plate, owner_id, brand_name, fuel_type),
)
connection_object.commit()
response = {
"message": "Car registered",
"data": {
"owner_id": str(owner_id),
"license_plate": license_plate,
"brand_name": brand_name,
"fuel_type": fuel_type,
},
}
return make_response(jsonify(response), 201)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
if error is not None:
flash(error)
return render_template("users/register_car.html")
def load_logged_in_user():
user_id = session.get("user_id")
if user_id is None:
g.user = None
else:
try:
cursor = get_db("zernike_parking_app").cursor(named_tuple=True)
cursor.execute(
"SELECT * FROM CarOwner WHERE owner_id=UUID_TO_BIN(%s) LIMIT 1",
(user_id,),
)
g.user = cursor.fetchone()
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
def check_in_car_record():
record_id = uuid1().bytes
license_plate = request.json["license_plate"]
space_id = request.json["space_id"]
check_in_time = datetime.now()
connection_object = get_db("ticket_booth")
cursor = connection_object.cursor(named_tuple=True)
duplication_check_query = """SELECT EXISTS(SELECT record_id FROM CarRecord WHERE license_plate=%s AND check_out IS NULL) AS isRegistered"""
insert_query = """INSERT INTO CarRecord (record_id, license_plate, space_id, check_in, is_paid) VALUES(%s, %s, %s, %s, 0)"""
try:
cursor.execute(duplication_check_query, (license_plate, ))
if cursor.fetchone().isRegistered == 1:
response = {
"message": "Car is already checked-in.",
"data": {
"license_plate": license_plate
},
}
return make_response(jsonify(response), 202)
cursor.execute(insert_query,
(record_id, license_plate, space_id, check_in_time))
connection_object.commit()
response = {
"message": "Car record created. Checked in.",
"data": {
"record_id": str(record_id),
"license_plate": license_plate,
"space_id": space_id,
"check_in": check_in_time,
},
}
return make_response(jsonify(response), 201)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
except TypeError as err:
print("No valid json data received\n", err)
finally:
cursor.close()
def get_cars_date_range():
start_date = request.args.get("start-date")
end_date = request.args.get("end-date")
connection_object = get_db("finance_app")
select_query = """SELECT c.owner_id, c.license_plate, c.brand_name, c.fuel_type
FROM
Car c
INNER JOIN CarRecord r ON c.license_plate = r.license_plate AND r.check_in >= %s AND r.check_in <= %s"""
cursor = connection_object.cursor(named_tuple=True)
try:
if start_date is not None and end_date is not None:
cursor.execute(select_query, (start_date, end_date))
rows = cursor.fetchall()
data = {}
for index, row in enumerate(rows):
invoice = {}
for j in [
{i[0]: str(i[1])} for i in list(row._asdict().items())
]:
invoice.update(j)
data.update({index: invoice})
response = {
"message": "List cars parked from %s until %s"
% (start_date, end_date),
"data": data,
}
return make_response(jsonify(response), 200)
else:
return redirect(url_for("maintenance.get_currently_parked_cars"))
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
def register_car():
license_plate = request.json["license_plate"]
connection_object = get_db("ticket_booth")
cursor = connection_object.cursor(named_tuple=True)
duplication_check_query = """SELECT EXISTS(SELECT license_plate FROM Car WHERE license_plate =%s) AS isRegistered"""
insert_query = """INSERT INTO Car (license_plate) VALUES (%s)"""
try:
cursor.execute(duplication_check_query, (license_plate, ))
if cursor.fetchone().isRegistered == 1:
response = {
"message": "Car already registered",
"data": {
"license_plate": license_plate
},
}
return make_response(jsonify(response), 202)
cursor.execute(insert_query, (license_plate, ))
connection_object.commit()
response = {
"message": "Car registered",
"data": {
"owner_id": "",
"license_plate": license_plate
},
}
return make_response(jsonify(response), 201)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
except TypeError as err:
print("No valid json data received\n", err)
finally:
cursor.close()
def get_monthly_invoices_per_owner():
connection_object = get_db("finance_app")
cursor = connection_object.cursor(named_tuple=True)
select_query = """SELECT
owner_id,
license_plate,
ps.space_id,
check_in,
check_out,
total_time,
total_time * ps.hourly_tariff AS parking_cost,
is_paid
FROM
(
SELECT
co.owner_id,
co.license_plate,
r.check_in,
r.check_out,
ROUND(
(
TIME_TO_SEC(
TIMEDIFF(r.check_out, r.check_in)
) / 3600
),
2
) AS total_time,
r.is_paid,
r.space_id
FROM
(
SELECT
Car.license_plate,
CarOwner.owner_id,
CarOwner.first_name,
CarOwner.surname,
CarOwner.student_employee_code,
CarOwner.discount_rate
FROM
Car
INNER JOIN CarOwner ON Car.owner_id = CarOwner.owner_id
) co
INNER JOIN CarRecord r ON
co.license_plate = r.license_plate
) cor
INNER JOIN ParkingSpace ps USING(space_id)"""
try:
cursor.execute(select_query)
data = {}
rows = cursor.fetchall()
invoices = {}
### Group by month
for index, row in enumerate(rows):
invoice = {}
for j in [{i[0]: str(i[1])} for i in list(row._asdict().items())]:
invoice.update(j)
invoices.update({index: invoice})
for invoice in invoices.values():
invoices_groupby_month = {}
for index, month in enumerate(month_name):
if index != 0:
monthly_invoice = {}
for k, v in invoices.items():
if (
datetime.strptime(
v["check_in"], "%Y-%m-%d %H:%M:%S"
).month
== index
and v["owner_id"] == invoice["owner_id"]
):
monthly_invoice.update({k: v})
invoices_groupby_month.update({month: monthly_invoice})
data.update({invoice["owner_id"]: invoices_groupby_month})
response = {"message": "Monthly invoices per car owner", "data": data}
return make_response(jsonify(response), 200)
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
abort(500)
def get_unpaid_records():
connection_object = get_db("finance_app")
cursor = connection_object.cursor(named_tuple=True)
select_query = """SELECT
record_id,
license_plate,
check_in,
check_out,
total_time,
total_time * ps.hourly_tariff AS parking_cost,
is_paid
FROM
(
SELECT
r.record_id,
co.license_plate,
r.check_in,
r.check_out,
ROUND(
(
TIME_TO_SEC(
TIMEDIFF(r.check_out, r.check_in)
) / 3600
),
2
) AS total_time,
r.is_paid,
r.space_id
FROM
(
SELECT
Car.license_plate,
CarOwner.owner_id,
CarOwner.first_name,
CarOwner.surname,
CarOwner.student_employee_code,
CarOwner.discount_rate,
CarOwner.payment_method
FROM
Car
INNER JOIN CarOwner ON Car.owner_id = CarOwner.owner_id
) co
INNER JOIN CarRecord r ON co.license_plate = r.license_plate AND r.is_paid = 0
) cor
INNER JOIN ParkingSpace ps USING(space_id)"""
try:
cursor.execute(select_query)
rows = cursor.fetchall()
data = {}
for index, row in enumerate(rows):
invoice = {}
for j in [{i[0]: str(i[1])} for i in list(row._asdict().items())]:
invoice.update(j)
data.update({index: invoice})
response = {"message": "Unpaid invoices", "data": data}
return make_response(jsonify(response), 200)
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
abort(500)
def get_user_invoice(email, month):
connection_object = get_db("finance_app")
cursor = connection_object.cursor(named_tuple=True)
### Reverse month name to month index
month_n = None
for index, i in enumerate(list(month_name)):
if str(i).lower() == str(month).lower():
month_n = index
if month_n is None:
abort(404)
select_query = """SELECT
license_plate,
check_in,
check_out,
total_time,
total_time * ps.hourly_tariff AS parking_cost,
is_paid
FROM
(
SELECT
co.license_plate,
r.check_in,
r.check_out,
ROUND(
(
TIME_TO_SEC(
TIMEDIFF(r.check_out, r.check_in)
) / 3600
),
2
) AS total_time,
r.is_paid,
r.space_id
FROM
(
SELECT
Car.license_plate,
CarOwner.owner_id,
CarOwner.first_name,
CarOwner.surname,
CarOwner.student_employee_code,
CarOwner.discount_rate,
CarOwner.payment_method
FROM
Car
INNER JOIN CarOwner ON Car.owner_id = CarOwner.owner_id AND CarOwner.email = %s
) co
INNER JOIN CarRecord r ON co.license_plate = r.license_plate AND MONTH(r.check_in) = %s
) cor
INNER JOIN ParkingSpace ps USING(space_id)"""
try:
cursor.execute(select_query, (email, month_n))
rows = cursor.fetchall()
data = {}
for index, row in enumerate(rows):
invoice = {}
for j in [{i[0]: str(i[1])} for i in list(row._asdict().items())]:
invoice.update(j)
data.update({index: invoice})
response = {"message": "Monthly invoice of a car owner", "data": data}
return make_response(jsonify(response), 200)
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
abort(500)
def get_invoice():
owner_id = g.user.owner_id
month = request.args.get("month")
connection_object = get_db("zernike_parking_app")
cursor = connection_object.cursor(named_tuple=True)
select_query = """SELECT
license_plate,
check_in,
check_out,
total_time,
total_time * ps.hourly_tariff AS parking_cost,
is_paid
FROM
(
SELECT
co.license_plate,
r.check_in,
r.check_out,
ROUND(
(
TIME_TO_SEC(
TIMEDIFF(r.check_out, r.check_in)
) / 3600
),
2
) AS total_time,
r.is_paid,
r.space_id
FROM
(
SELECT
Car.license_plate,
CarOwner.owner_id,
CarOwner.first_name,
CarOwner.surname,
CarOwner.student_employee_code,
CarOwner.discount_rate,
CarOwner.payment_method
FROM
Car
INNER JOIN CarOwner ON Car.owner_id = CarOwner.owner_id AND CarOwner.owner_id = %s
) co
INNER JOIN CarRecord r ON co.license_plate = r.license_plate AND MONTH(r.check_in) = %s
) cor
INNER JOIN ParkingSpace ps USING(space_id)"""
try:
cursor.execute(select_query, (owner_id, month))
rows = cursor.fetchall()
# Old way of extracrting data from the query result
# test_data = [
# {
# "license_plate": row.license_plate,
# "check_in": row.check_in,
# "check_out": row.check_out,
# "total_time": str(row.total_time),
# "parking_cost": str(row.parking_cost),
# }
# for index, row in enumerate(rows)
# ]
### New way utilizes list comprehension
data = {}
for index, row in enumerate(rows):
invoice = {}
for j in [{i[0]: str(i[1])} for i in list(row._asdict().items())]:
invoice.update(j)
data.update({index: invoice})
response = {
"message": "User invoice for month %s" % month_name[int(month)],
"data": data,
}
return make_response(jsonify(response), 200)
except Error as err:
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
abort(500)
def register():
"""Registers a new car owner in the database
GET:
responses:
200 OK on success,
404 Not Found
POST:
parameters:
email: str
password: str
customer_type: str
student_employee_code: str
first_name: str
surname: str
tel_number: str
payment_method: str
responses:
201 Created on success
"""
if request.method == "POST":
email = request.form["email"]
password = request.form["password"]
customer_type = request.form["customer_type"]
student_employee_code = request.form["student_employee_code"]
first_name = request.form["first_name"]
surname = request.form["surname"]
tel_number = request.form["tel_number"]
payment_method = request.form["payment_method"]
connection_object = get_db("zernike_parking_app")
error = None
cursor = connection_object.cursor(named_tuple=True)
duplication_check_query = """SELECT EXISTS(SELECT email FROM CarOwner WHERE email=%s) AS is_registered"""
insert_query = """INSERT INTO CarOwner (owner_id, customer_type, student_employee_code, first_name, surname, tel_number, email, password, payment_method)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
try:
error = validate_user_data(
email,
password,
student_employee_code,
first_name,
surname,
tel_number,
)
cursor.execute(duplication_check_query, (email,))
if cursor.fetchone().is_registered == 1:
error = "Already registered email address (%s)." % email
if error is None:
cursor.execute(
insert_query,
(
uuid1().bytes,
customer_type,
student_employee_code,
first_name,
surname,
tel_number,
email,
sha256_crypt.hash(password),
payment_method,
),
)
connection_object.commit()
return redirect(url_for("auth.login"), code=201)
except Error as err:
connection_object.rollback()
print("Error Code:", err.errno)
print("SQLSTATE:", err.sqlstate)
print("Message:", err.msg)
finally:
cursor.close()
if error is not None:
flash(error)
return render_template("auth/register.html")
