def assign_shift():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
shift_id = request.get_json(force=True)['shift_id']
employee_id = request.get_json(force=True)['employee_id']
# check if assignment already exists
query = """SELECT EmployeeID, ShiftID FROM EmployeeShifts
WHERE EmployeeID = %s AND ShiftID = %s;"""
data = (employee_id, shift_id)
cursor.execute(query, data)
result = cursor.fetchall()
if result:
cursor.close()
db_pool.putconn(db_conn)
return make_response('Employee already works this shift!', 500)
else:
query = """INSERT INTO EmployeeShifts (EmployeeID, ShiftID) VALUES (%s, %s);"""
data = (employee_id, shift_id)
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Assigned a shift to an employee!', 200)
def customers_page():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT CustomerID, Name, PhoneNumber, RewardsPts
FROM Customers ORDER BY CustomerID ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return render_template('customers.html', rows=result)
def get_order_id():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT MAX(OrderID) FROM Orders;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def assign_shifts_dropdown():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT ShiftID FROM Shifts ORDER BY ShiftID ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def cust_order_inv_dropdown():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT Name FROM Inventory;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def employees_page():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT EmployeeID, Name, HourlyWage, Responsibilities,
SickDays FROM Employees ORDER BY EmployeeID ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return render_template('employees.html', rows=result)
def shifts_page():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT ShiftID, Day, StartTime, EndTime
FROM Shifts ORDER BY ShiftID ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return render_template('shifts.html', rows=result)
def employee_order_dropdown():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT EmployeeID FROM Employees ORDER BY EmployeeID ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def inventory_page():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT PLU, Name, Description, UnitCost, Quantity
FROM Inventory ORDER BY PLU ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return render_template('inventory.html', results=result)
def delete_inventory():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
plu = request.get_json(force=True)["info"]
query = """DELETE FROM Inventory WHERE PLU = %s;"""
data = (plu, )
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Inventory deleted!', 200)
def insert_order():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
query = """INSERT INTO Orders (CustomerID, EmployeeID) VALUES (%s, %s);"""
data = (info["CustomerID"], info["EmployeeID"])
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Order added!', 200)
def delete_order_item():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
OrderItemID = request.get_json(force=True)["info"]
query = """DELETE FROM OrderItems WHERE OrderItemID = %s;"""
data = (OrderItemID, )
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('OrderItem deleted!', 200)
def delete_customer():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
customer_id = request.get_json(force=True)["customer_id"]
query = """DELETE FROM Customers WHERE CustomerID = %s;"""
data = (customer_id, )
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
message = 'Customer with ID ' + customer_id + ' removed from the database'
return make_response(message, 200)
def place_order():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
for item in info:
query = """INSERT INTO OrderItems (Quantity, OrderID, PLU) VALUES (%s, %s, %s);"""
data = (item['quantity'], item['OrderID'], item['PLU'])
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Order added!', 200)
def get_customer_id():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
search_term = request.get_json(force=True)["name"]
query = """SELECT CustomerID FROM Customers WHERE Name = %s;"""
data = (search_term, )
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def search_employees_by_sick_days():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
query = """SELECT EmployeeID, Name, HourlyWage, Responsibilities,
SickDays FROM Employees WHERE SickDays = %s;"""
data = (info["sickdays"], )
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def orders_page():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
query = """SELECT Orders.OrderID, Inventory.Name, Inventory.Description, Inventory.UnitCost,
OrderItems.Quantity, (Inventory.UnitCost * OrderItems.Quantity),
OrderItems.OrderItemID AS Total FROM Inventory
JOIN OrderItems on OrderItems.PLU = Inventory.PLU
JOIN Orders on OrderItems.OrderID = Orders.OrderID
ORDER BY Orders.OrderID ASC;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return render_template('orders.html', results=result)
def insert_new_customer():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
query = """INSERT INTO Customers
(Name, PhoneNumber, RewardsPts)
VALUES (%s, %s, %s);"""
data = (info["name"], info["phone"], info["points"])
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Customer added!', 200)
def search_customers_by_points():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
search_terms = request.get_json(force=True)
query = """SELECT CustomerID, Name, PhoneNumber, RewardsPts
FROM Customers WHERE RewardsPts >= %s AND RewardsPts <= %s;"""
data = (search_terms["lower"], search_terms["upper"])
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def search_customers_by_phone_number():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
search_term = request.get_json(force=True)["input"]
query = """SELECT CustomerID, Name, PhoneNumber, RewardsPts
FROM Customers WHERE PhoneNumber = %s;"""
data = (search_term, )
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def insert_new_employee():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
query = """INSERT INTO Employees
(Name, HourlyWage, Responsibilities, SickDays)
VALUES (%s, %s, %s, %s);"""
data = (info["name"], info["wage"], info["duties"], info["sick_days"])
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Employee added!', 200)
def search_inventory_by_name():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
search_term = request.get_json(force=True)["name"]
query = """SELECT PLU, Name, Description, UnitCost, Quantity
FROM Inventory WHERE Name LIKE %s;"""
data = (["%" + search_term + "%"])
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def update_shift():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
data = (info["day"], info["start_time"], info["end_time"], info["id"])
query = """UPDATE Shifts
SET Day = %s,
StartTime = %s,
EndTime = %s
WHERE ShiftID = %s;"""
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Updated shift information', 200)
def insert_new_inventory():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
item = request.get_json(force=True)['item']
description = request.get_json(force=True)['description']
unit = float(request.get_json(force=True)['unit'])
quantity = int(request.get_json(force=True)['quantity'])
query = """INSERT INTO Inventory (Name, Description, UnitCost, Quantity)
VALUES (%s, %s, %s, %s);"""
data = (item, description, unit, quantity)
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Inventory added!', 200)
def update_orders():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
OrderItemID = request.get_json(force=True)['id']
quantity = int(request.get_json(force=True)['quantity'])
data = (quantity, OrderItemID)
# Update Quantity of items ordered
query = """UPDATE OrderItems
SET Quantity = %s
WHERE OrderItemID = %s;"""
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Inventory added!', 200)
def update_customer():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
data = (info["name"], info["phone"], info["points"], info["id"])
query = """UPDATE Customers
SET Name = %s,
PhoneNumber = %s,
RewardsPts = %s
WHERE CustomerID = %s;"""
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Updated customer information', 200)
def get_employees_for_shift():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
# Get the ID of the employee to view shifts for
shift_id = request.get_json(force=True)['shift_id']
query = """SELECT Shifts.ShiftID, Employees.Name FROM Shifts
JOIN EmployeeShifts ON Shifts.ShiftID = EmployeeShifts.ShiftID
JOIN Employees ON EmployeeShifts.EmployeeID = Employees.EmployeeID
WHERE Shifts.ShiftID = %s;"""
data = (shift_id, )
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def update_employee():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
info = request.get_json(force=True)
data = (info["name"], info["wage"], info["duties"], info["sick_days"],
info["id"])
query = """UPDATE Employees
SET Name = %s,
HourlyWage = %s,
Responsibilities = %s,
SickDays = %s
WHERE EmployeeID = %s;"""
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
return make_response('Updated employee information', 200)
def delete_employee():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
# Delete from the Employees table
employee_id = request.get_json(force=True)["employee_id"]
query = """DELETE FROM Employees WHERE EmployeeID = %s;"""
data = (employee_id, )
cursor.execute(query, data)
db_conn.commit()
# delete rows with null foreign keys from the EmployeeShifts table
query = """DELETE FROM EmployeeShifts WHERE EmployeeID IS NULL OR ShiftID IS NULL"""
cursor.execute(query, data)
db_conn.commit()
cursor.close()
db_pool.putconn(db_conn)
message = 'Employee with ID ' + employee_id + ' removed from the database'
return make_response(message, 200)
def search_orders_by_employee():
db_conn = db_pool.getconn()
cursor = db_conn.cursor()
search_term = request.get_json(force=True)["employee"]
query = """SELECT Orders.OrderID, Inventory.Name, Inventory.Description, Inventory.UnitCost,
OrderItems.Quantity, (Inventory.UnitCost * OrderItems.Quantity) AS Total
FROM Inventory
JOIN OrderItems on OrderItems.PLU = Inventory.PLU
JOIN Orders on OrderItems.OrderID = Orders.OrderID
JOIN Employees on Orders.EmployeeID = Employees.EmployeeID
AND Employees.Name = %s
ORDER BY Orders.OrderID ASC;"""
data = (search_term, )
cursor.execute(query, data)
result = cursor.fetchall()
cursor.close()
db_pool.putconn(db_conn)
return make_response(
json.dumps(result, indent=4, sort_keys=True, default=str), 200)
