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)