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