def delete_customer(): db_connection = connect_to_database() customer_id = request.get_json(force=True)["customer_id"] query = """DELETE FROM `Customers` WHERE `CustomerID` = %s;""" data = (customer_id,) execute_query(db_connection, query, data).fetchall() message = 'Customer with ID ' + customer_id + ' removed from the database' return make_response(message, 200)
def delete_order_item(): print("Deleting OrderItems from database", flush=True) db_connection = connect_to_database() OrderItemID = request.get_json(force=True)["info"] print("id:", OrderItemID) query = """DELETE FROM `OrderItems` WHERE `OrderItemID` = %s;""" data = (OrderItemID,) execute_query(db_connection, query, data) return make_response('OrderItem deleted!', 200)
def delete_inventory(): print("Deleting Inventory from database", flush=True) db_connection = connect_to_database() plu = request.get_json(force=True)["info"] print("plu:", plu) query = """DELETE FROM `Inventory` WHERE `PLU` = %s;""" data = (plu,) execute_query(db_connection, query, data) return make_response('Inventory deleted!', 200)
def insert_new_employee(): print('Inserting new employee into the database', flush=True) db_connection = connect_to_database() info = request.get_json(force=True) string_query = """INSERT INTO `Employees` (`Name`, `HourlyWage`, `Responsibilities`, `SickDays`) VALUES (%s, %s, %s, %s);""" data = (info["name"], info["wage"], info["duties"], info["sick_days"]) execute_query(db_connection, string_query, data) return make_response('Employee added!', 200)
def insert_new_customer(): print('Inserting new customer into the database', flush=True) db_connection = connect_to_database() info = request.get_json(force=True) query = """INSERT INTO `Customers` (`Name`, `PhoneNumber`, `RewardsPts`) VALUES (%s, %s, %s);""" data = (info["name"], info["phone"], info["points"]) execute_query(db_connection, query, data) return make_response('Customer added!', 200)
def insert_order(): print('Inserting new order into the database', flush=True) db_connection = connect_to_database() info = request.get_json(force=True) print("Info", info) query = """INSERT INTO `Orders` (`CustomerID`, `EmployeeID`) VALUES (%s, %s);""" data = (info["CustomerID"], info["EmployeeID"]) execute_query(db_connection, query, data) return make_response('Order added!', 200)
def insert_new_inventory(): print("Inserting new inventory into database", flush=True) db_connection = connect_to_database() 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) execute_query(db_connection, query, data) return make_response('Inventory added!', 200)
def update_customer(): db_connection = connect_to_database() 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;""" execute_query(db_connection, query, data) return make_response('Updated customer information', 200)
def update_shift(): print("Updating Shift in database", flush=True) db_connection = connect_to_database() 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;""" execute_query(db_connection, query, data) return make_response('Updated shift information', 200)
def update_employee(): db_connection = connect_to_database() 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;""" execute_query(db_connection, query, data) return make_response('Updated employee information', 200)
def place_order(): print("Place an Order and update OrderItems database", flush=True) db_connection = connect_to_database() info = request.get_json(force=True) for item in info: quantity = item['quantity'] order_id = item['OrderID'] plu = item['PLU'] query = """INSERT INTO `OrderItems` (`Quantity`, `OrderID`, `PLU`) VALUES (%s, %s, %s);""" data = (quantity, order_id, plu) execute_query(db_connection, query, data) return make_response('Order added!', 200)
def update_orders(): print("Updating OrderItems in database", flush=True) db_connection = connect_to_database() OrderItemID = request.get_json(force=True)['id'] quantity = int(request.get_json(force=True)['quantity']) # Update Quantity of items ordered query = """UPDATE `OrderItems` SET `Quantity` = %s WHERE `OrderItemID` = %s;""" data = (quantity, OrderItemID) execute_query(db_connection, query, data) return make_response('Inventory added!', 200)
def delete_shift(): db_connection = connect_to_database() shift_id = request.get_json(force=True)["shift_id"] # delete from the Employees table query = """DELETE FROM `Shifts` WHERE `ShiftID` = %s;""" data = (shift_id,) execute_query(db_connection, query, data) # delete rows with null foreign keys from the EmployeeShifts table query = """DELETE FROM `EmployeeShifts` WHERE `EmployeeID` IS NULL OR `ShiftID` IS NULL""" execute_query(db_connection, query) message = 'Shift with ID ' + shift_id + ' removed from the database' return make_response(message, 200)
def shifts_page(): print('Fetching and rendering Shifts page', flush=True) db_connection = connect_to_database() query = "SELECT ShiftID, Day, StartTime, EndTime FROM Shifts;" result = execute_query(db_connection, query).fetchall() print('Shifts table query returns:', result, flush=True) return render_template('shifts.html', rows=result)
def employees_page(): print('Fetching and rendering Employees page', flush=True) db_connection = connect_to_database() query = "SELECT EmployeeID, Name, HourlyWage, Responsibilities, SickDays FROM Employees;" result = execute_query(db_connection, query).fetchall() print('Employees table query returns:', result, flush=True) return render_template('employees.html', rows=result)
def cust_order_inv_dropdown(): print('Fetching List of Inventory names') db_connection = connect_to_database() query = """SELECT Name FROM Inventory;""" result = execute_query(db_connection, query).fetchall() print('Inventory name results') return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def employee_order_dropdown(): print('Fetching List of Employee names') db_connection = connect_to_database() query = """SELECT EmployeeID FROM `Employees`;""" result = execute_query(db_connection, query).fetchall() print('Employee name results') return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def get_order_id(): print('Get most recent order id from database', flush=True) db_connection = connect_to_database() query = """SELECT MAX(OrderID) FROM Orders;""" result = execute_query(db_connection, query).fetchall() print("result:",result) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def customers_page(): print('Fetching and rendering Customers page', flush=True) db_connection = connect_to_database() query = "SELECT CustomerID, Name, PhoneNumber, RewardsPts FROM Customers;" result = execute_query(db_connection, query).fetchall() print('Customers table query returns:', result, flush=True) return render_template('customers.html', rows=result)
def inventory_page(): print('Fetching and rendering Inventory page', flush=True) db_connection = connect_to_database() query = "SELECT PLU, Name, Description, UnitCost, Quantity FROM Inventory;" result = execute_query(db_connection, query).fetchall() print('Inventory table query returns: ', result, flush=True) return render_template('inventory.html', results=result)
def search_inventory_by_name(): db_connection = connect_to_database() search_term = request.get_json(force=True)["name"] query = """SELECT PLU, Name, Description, UnitCost, Quantity FROM Inventory WHERE Name LIKE %s;""" data = (["%" + search_term + "%"]) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def get_customer_id(): print('Fetching customer id', flush=True) db_connection = connect_to_database() search_term = request.get_json(force=True)["name"] query = """SELECT CustomerID FROM Customers WHERE Name = %s;""" data = (search_term,) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def search_customers_by_points(): db_connection = connect_to_database() 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"]) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def search_employees_by_sick_days(): db_connection = connect_to_database() info = request.get_json(force=True) query = """SELECT `EmployeeID`, `Name`, `HourlyWage`, `Responsibilities`, `SickDays` FROM `Employees` WHERE `SickDays` = %s;""" data = (info["sickdays"],) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def search_customers_by_phone_number(): db_connection = connect_to_database() search_term = request.get_json(force=True)["input"] query = """SELECT CustomerID, Name, PhoneNumber, RewardsPts FROM Customers WHERE PhoneNumber = %s;""" data = (search_term,) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def update_inventory(): print("Updating Inventory in database", flush=True) db_connection = connect_to_database() plu = request.get_json(force=True)['plu'] 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 = """UPDATE `Inventory` SET `Name` = %s, `Description` = %s, `UnitCost` = %s, `Quantity` = %s WHERE `PLU` = %s;""" data = (item, description, unit, quantity, plu) execute_query(db_connection, query, data) return make_response('Inventory added!', 200)
def insert_new_shift(): print('Inserting new shift into the database', flush=True) db_connection = connect_to_database() info = request.get_json(force=True) # check if the shift already exists query = """SELECT Day, StartTime, EndTime FROM `Shifts` WHERE Day = %s AND StartTime = %s AND EndTime = %s;""" data = (info["day"], info["start_time"], info["end_time"]) result = execute_query(db_connection, query, data).fetchall() print('result:', result, flush=True) if result: return make_response('Shift already exists!', 500) else: query = """INSERT INTO `Shifts` (`Day`, `StartTime`, `EndTime`) VALUES (%s, %s, %s);""" data = (info["day"], info["start_time"], info["end_time"]) execute_query(db_connection, query, data) return make_response('Shift added!', 200)
def orders_page(): print('Fetching and rendering Orders page', flush=True) db_connection = connect_to_database() 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 DESC;""" result = execute_query(db_connection, query).fetchall() print('Orders table query returns:', result, flush=True) return render_template('orders.html', results=result)
def search_order_item(): print('Fetching and rendering Order page', flush=True) db_connection = connect_to_database() search_term = request.get_json(force=True)["name"] query = """SELECT PLU, Name, Description, UnitCost FROM Inventory WHERE Name LIKE %s ORDER BY Name;""" data = (["%" + search_term + "%"]) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)
def search_orders_by_employee(): db_connection = connect_to_database() 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 DESC;""" data = (search_term,) result = execute_query(db_connection, query, data).fetchall() print('Query returns:', result, flush=True) return make_response(json.dumps(result, indent=4, sort_keys=True, default=str), 200)