def auto_assign_waiter(table_num):
# check if there are waiters in the database
result = connector.execute_query("SELECT email FROM waiter")
if len(result) == 0:
error_msg = "No waiters in the database!"
return jsonify(error={"success": False, "message": error_msg})
# adding a check to ensure there is a waiter assigned to the table when a order is created
query = "SELECT waiter_id, table_number FROM table_details WHERE table_number = %s AND waiter_id IS NULL"
result = connector.execute_query(query, (table_num, ))
if len(result) == 0 or result[0][0] is None:
# first check if there are waiters that are not assinged to any tables
waiters = connector.execute_query("SELECT email FROM waiter")
assignedWaiters = connector.execute_query(
"SELECT waiter_id FROM table_details")
for waiter in waiters:
if waiter not in assignedWaiters:
query = "UPDATE table_details SET waiter_id = %s WHERE table_number = %s"
connector.execute_insert_query(query, (waiter[0], table_num))
return None
# Select the waiter with the least tables
query = "SELECT waiter_id, COUNT(table_number) "\
"FROM table_details "\
"WHERE waiter_id IS NOT NULL "\
"GROUP BY waiter_id "\
"ORDER BY count"
result = connector.execute_query(query)
# we ordered by count so the top result will be the waiter with minimal tables
waiter_email = result[0][0]
query = "UPDATE table_details SET waiter_id = %s WHERE table_number = %s"
connector.execute_insert_query(query, (waiter_email, table_num))
def validate(request):
error = vf.sent_expected_values(["customer", "items", "table_num"], request)
if error:
return error
table_num = int(request.json.get("table_num"))
items = request.json.get("items")
customer = request.json.get("customer")
if not isinstance(items, (list)):
error_msg = "expected 'items' to be list"
return jsonify(error={"success" : False, "message" : error_msg})
if len(items) > 100:
error_msg = "'items' is more than 100 items, please shorten it"
return jsonify(error={"success" : False, "message" : error_msg})
query = "SELECT * FROM table_details WHERE table_number = %s"
result = connector.execute_query(query, (table_num,))
if not result:
error_msg = "Given table number is not in table_detail"
return jsonify(error={"success" : False, "message" : error_msg})
query = "SELECT id FROM menu"
result = connector.execute_query(query)
res = []
for r in result:
res.append(int(r[0]))
for id in items:
if id not in res:
error_msg = "Invalid menu_item_id given in 'items' list"
return jsonify(error={"success" : False, "message" : error_msg})
return None
def validate_order_event(request):
error = vf.sent_expected_values(["order_id", "order_event"], request)
if error:
return error
order_id = request.json.get("order_id")
event = request.json.get("order_event")
if event not in valid_events:
error_msg = "given event is not a valid event type, see this objects 'valid_events'"
error_msg += " for a list of valid events"
return jsonify(error={"success" : False, "message" : error_msg, "valid_events" : valid_events})
result = connector.execute_query("SELECT * FROM orders WHERE id=%s", (order_id,))
if not result:
error_msg = "Invalid order_id, given order_id is not in orders table"
return jsonify(error={"success" : False, "message" : error_msg})
query = "SELECT state FROM orders WHERE id = %s"
result = connector.execute_query(query, (order_id,))
order_state = result[0]
query = "SELECT order_event_transition(%s, %s) AS new_state"
result = connector.execute_query(query, (order_state, event))
print("PRINTING FROM VALIDATE_ORDERS: ", result[0][0])
# database will return error if there is a movement from one state to another that is not valid
# or if the event sent is not valid, see func_schema.sql to see the inner workings
if result[0][0] == "error":
print("ERROR FOUND")
error_msg = "Given event cannot be performed on this order."
return jsonify(error={"success" : False, "message" : error_msg})
return None
def get_tables():
query = "SELECT table_number AS table_numbers FROM table_details ORDER BY table_number"
result = connector.execute_query(query)
output = []
for ele in result: # puts the tables in the array and sends the array as the result instead of a array of json objs
output.append(ele[0])
return jsonify(data={"tables": output})
def validate_waiter(request):
error = validate_user(request)
if error:
return error
error = vf.sent_expected_values(["phone_number"], request)
if error:
return error
phone_number = request.json.get('phone_number')
# if the phone number is not an integer and of length 11
if len(phone_number) != 11 and isinstance(phone_number, int):
error_msg = "Phone number was not a valid input must be 07 followed by 9 digits"
return jsonify(error={"success": False, "message": error_msg})
email = request.json.get('email')
query = "SELECT email FROM waiter WHERE email = %s;"
result = connector.execute_query(query, (email, ))
# if there is someone in the database with that email already
if len(result) == 1:
error_msg = "Email given is already in use"
return jsonify(error={"success": False, "message": error_msg})
return None
def login():
error = vf.sent_expected_values(["email", "password", "staff_login"],
request)
if error:
return error
email = request.json.get('email')
password = request.json.get('password')
staff_login = request.json.get('staff_login')
# select the user with the email inputted
if staff_login:
query = "SELECT email, password FROM waiter WHERE email = %s AND password = %s"
else:
query = "SELECT email, password FROM customer WHERE email = %s AND password = %s"
result = connector.execute_query(query, (email, password))
# if the result returns nothing return invalid response
if not result:
return jsonify(error={
"valid_credentials": False,
"message": "invalid email or password"
})
else:
email = result[0][0]
sessions.session.create_session(email, staff_login)
return jsonify(data={
"valid_credentials": True,
"username": email,
"is_staff": staff_login
})
def get_unassigned_tables():
query = "SELECT json_agg (order_list) FROM "\
"(SELECT table_number "\
"FROM table_details "\
"WHERE waiter_id IS NULL "\
"ORDER BY table_number)"\
"AS order_list;"
result = connector.execute_query(query)
return jsonify(data={"tables": result[0][0]})
def get_tables_and_waiters():
query = "SELECT json_agg (order_list) FROM "\
"(SELECT table_number, email, firstname, lastname "\
"FROM table_details, waiter "\
"WHERE waiter.email = waiter_id "\
"ORDER BY table_number)"\
"AS order_list;"
result = connector.execute_query(query)
return jsonify(data={"tables": result[0][0]})
def get_waiter_notifications():
error = vn.validate_get_waiter_notifications(request)
if error:
return(error)
waiter_email = request.json.get("waiter_email")
query = "SELECT * FROM waiter_notifications WHERE waiter_email=%s"
notifications = connector.execute_query(query, (waiter_email,))
return jsonify(data={"notifications" : notifications, "success" : True})
def get_waiter_assigned_to_table():
error = validate_tables.validate_table(request)
if error:
return error
table_id = request.json.get("table_id")
# assigns a waiter to the table if there is none
vf.auto_assign_waiter(table_id)
query = "SELECT waiter_id FROM table_details WHERE table_number = %s;"
result = connector.execute_query(query, (table_id, ))
return jsonify(data={"waiter_id": result[0][0]})
def validate_get_cust_order(request):
error = vf.sent_expected_values(["cust_id"], request)
if error:
return error
cust_id = request.json.get("cust_id")
query = "SELECT email FROM customer where email = %s"
result = connector.execute_query(query, (cust_id,))
if result is None or result == []:
error_msg = "No customer with id = " + cust_id
return jsonify({"success":False, "message": error_msg})
return None
def validate_customer(request):
error = validate_user(request)
if error:
return error
email = request.json.get('email')
query = "SELECT email FROM customer WHERE email = %s;"
result = connector.execute_query(query, (email, ))
# if there is someone in the database with that email already
if len(result) == 1:
error_msg = "Email given is already in use"
return jsonify(error={"success": False, "message": error_msg})
return None
def menu():
# try and get the variable from the json
try:
getAll = request.json.get("getAll")
if isinstance(getAll, bool):
jsonify(error={
"success": False,
"message": "getAll was not a boolean"
})
except AttributeError as error: # if getAll not provided handle error thrown
getAll = None
if getAll is None or getAll is False: # if there are no arguments select everything
# gets the whole menu from the database and gets the menu item type i.e. side, main ect
# this sql query returns the result as an already formatted json
query = "SELECT json_agg (menu) FROM (" \
"SELECT menu.id, name, description, vegan, " \
"gluten_free, vegetarian, calories, price, available, type, image " \
"FROM menu, item_type " \
"WHERE item_type.id = menu.food_type " \
"AND menu.available = true " \
") AS menu;"
result = connector.execute_query(query)
# gets the result from the database
return jsonify(data={"items": result[0][0]})
elif getAll is True:
query = "SELECT json_agg (menu) FROM (" \
"SELECT menu.id, name, description, vegan, " \
"gluten_free, vegetarian, calories, price, available, type, image " \
"FROM menu, item_type " \
"WHERE item_type.id = menu.food_type "\
"ORDER BY menu.id"\
") AS menu;"
result = connector.execute_query(query)
return jsonify(data={"items": result[0][0]})
def validate_table(request):
error = vf.sent_expected_values(["table_id"], request)
if error:
return error
table_id = request.json.get("table_id")
if table_id is None:
error_msg = "Nothing was given as the value of table_id"
return jsonify(error={"success": False, "message": error_msg})
query = "SELECT table_number FROM table_details WHERE table_number = %s"
result = connector.execute_query(query, (table_id, ))
if len(result) == 0:
error_msg = "Table number does not exist in the database"
return jsonify(error={"success": False, "message": error_msg})
return None
def validate_event(request):
error = validate_table(request)
if error:
return error
error = vf.sent_expected_values(["waiter_id"], request)
if error:
return error
waiter = request.json.get("waiter_id")
if waiter is not None:
result = connector.execute_query("SELECT * FROM waiter WHERE email=%s",
(waiter, ))
if len(result) == 0:
error_msg = "Given waiter email does not appear in waiter table"
return jsonify(error={"success": False, "message": error_msg})
return None
def validate_get_waiters_orders(request):
# using validate functions to do common checking, here we are checking if the user exists
error = validate_get_orders(request)
if error is not None:
return error
error = vf.sent_expected_values(["waiter_id"], request)
if error:
return error
waiter_id = request.json.get("waiter_id")
query = "SELECT email from waiter where email = %s"
result = connector.execute_query(query, (waiter_id,))
if len(result) is 0:
error_msg = "Given waiter email was not found in the table"
return jsonify(error={"success":False, "message":error_msg})
return None
def validate_get_order(request):
# using validate functions to do common checking, if the user exists
error = validate_get_cust_order(request)
if error is not None:
return error
error = vf.sent_expected_values(["order_id"], request)
if error:
return error
order_id = request.json.get("order_id")
query = "SELECT id from orders where id = %s"
result = connector.execute_query(query, (order_id,))
if result is None or result == []:
error_msg = "No order exists with id = " + order_id
return jsonify(error={"success": False, "message": error_msg})
return None
from common import connector
enum_list = connector.execute_query('''
SELECT type.typname,
enum.enumlabel AS value
FROM pg_enum AS enum
JOIN pg_type AS type
ON (type.oid = enum.enumtypid)
GROUP BY enum.enumlabel,
type.typname
''')
print(enum_list)
