Class Database

java.lang.Object
Database
public class Database extends Object
The Database class provides methods for connecting to a PostgreSQL database, executing queries, and performing various database operations related to menu items, food items, employees, customers, and orders. It serves as the primary interface for interacting with a restaurant's database system. This class includes functionality for:
  • Establishing and disconnecting a connection to the database
  • Executing SQL queries and updates
  • Performing CRUD operations on menu items
  • Managing inventory and sales data
  • Handling orders, customers, and employees
  • Printing menu and food items to the console
Note: The class assumes the use of a PostgreSQL database and uses JDBC to interact with it.
Since:
2024-10-16
Version:
1.0

  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    Connection
    conn
    Represents a connection to the database.

  • Constructor Summary

    Constructors
    Constructor
    Description
    Database(String dbName, String dbUser, String dbPass)
    Constructor of the Database object, does not initialize connection

  • Method Summary

    Modifier and Type
    Method
    Description
    void
    addEmployee(String firstName, String lastName, float hoursWorked, Time[] schedule)
    Adds a new employee to the employees table.
    void
    addFoodItem(String foodItemName, String type, Integer[] inventoryItemIds, Double[] inventoryAmounts, Integer[] seasonal)
    Adds a new food item to the FoodItems table with a custom food item ID.
    void
    addInventoryItem(String inventoryItemName, double quantity, Date lastRestocked)
    Adds a new inventory item to the inventoryitems table with a custom inventory item ID.
    void
    addMenuItem(String menuItemName, double price, Integer[] foodItemIds, Integer[] inventoryItemIds)
    Adds a new menu item to the MenuItems table with a custom menu item ID.
    float
    averageDailySales()
    This method returns the daily average sales of the recorded orders.
    void
    connect()
    This method uses the database URL, username, and password to establish a connection.
    ResultSet
    createSalesReport(Date startTime, Date endTime)
    Creates a sales report for a specific date range, summarizing menu item sales.
    ResultSet
    createXReport(Date currDate)
    Creates an X report for a specific date, summarizing total sales per hour.
    ResultSet
    createZReport(Date currDate)
    Creates a Z report for a specific date, resetting the total sales for that day.
    void
    deleteEmployee(int employeeId)
    Deletes an employee from the database based on the provided employee ID.
    void
    deleteFoodItem(String foodItemName)
    Deletes a food item from the FoodItems table based on the food item name, and removes the food item ID from the foodItem_ids arrays of all MenuItems.
    void
    deleteInventoryItem(String inventoryItemName)
    Deletes an inventory item from the database based on the provided item name.
    void
    deleteMenuItem(String menuItemName)
    Deletes a menu item from the MenuItems table based on the menu item name.
    void
    disconnect()
    This method disconnects from the database and closes the connection between the database and the GUI.
    void
    editEmployee(ArrayList<String> edits)
    Updates the details of an employee in the Employees table based on the provided edits.
    void
    editFoodItem(ArrayList<String> edits)
    Updates the details of a food item in the FoodItems table based on the provided edits.
    void
    editInventoryItem(ArrayList<String> edits)
    Updates the details of an inventory item in the inventoryitems table based on the provided edits.
    void
    editMenuItem(ArrayList<String> edits)
    Updates the details of a menu item in the MenuItems table based on the provided edits.
    void
    editOrder(ArrayList<String> edits)
    Updates the details of an order in the Orders table based on the provided edits.
    int
    foodItemInventory(String name)
    This method returns the amount of ingredients a specified food item uses.
    ResultSet
    getAppetizers()
    Retrieves all items classified as appetizers from the database.
    int
    getCustomerId()
    This method returns a random customer ID.
    ResultSet
    getDesserts()
    Retrieves all items classified as desserts from the database.
    ResultSet
    getDrinks()
    Retrieves all items classified as drinks from the database.
    int
    getEmployeeId()
    This method returns a random employee ID.
    ResultSet
    getEntrees()
    Retrieves all items classified as entrees from the database.
    double
    getInventoryQuantity(int inventoryId)
    Retrieves the quantity of a specific inventory item from the database.
    ResultSet
    getSides()
    Retrieves all items classified as sides from the database.
    ResultSet
    orderHistory(int tableOffset)
    Retrieves a paginated history of orders from the database, ordered by the most recent first.
    void
    placeOrder(Cart cart, Timestamp orderedTime)
    Places an order based on the items in the provided cart and the specified order time.
    void
    printFoodItems(int menuItemId)
    Prints the food items associated with a specified menu item ID to the console.
    void
    printMenuItems()
    Prints the menu items, including their IDs, names, and prices, to the console.
    ResultSet
    query(String sqlQuery)
    This method executes the query that is given and returns the result set of said query.
    void
    updateEmployeeHours(int employeeId, float newHours)
    Updates the hours worked for a specific employee.
    void
    updateEmployeeSchedule(int employeeId, Time[] newSchedule)
    Updates the schedule for a specific employee.
    void
    updateFoodItemInventoryAmounts(String foodItemName, Double[] newInventoryAmounts)
    Updates the inventory amounts of a food item based on the provided name.
    void
    updateFoodItemInventoryItemIds(String foodItemName, Integer[] newInventoryItemIds)
    Updates the inventory item IDs of a food item based on the provided name.
    void
    updateFoodItemName(String currentName, String newName)
    Updates the name of a food item based on the provided current name.
    void
    updateFoodItemType(String foodItemName, String newType)
    Updates the type of a food item based on the provided current name.
    void
    updateInventoryItemLastRestocked(String inventoryItemName, Date newLastRestocked)
    Updates the last restocked date of an inventory item by its name.
    void
    updateInventoryItemQuantity(String inventoryItemName, double newQuantity)
    Updates the quantity of an inventory item by its name.
    void
    updateMenuItemFoodItems(String menuItemName, Integer[] newFoodItemIds)
    Updates the food item IDs of a given menu item.
    void
    updateMenuItemInventoryItems(String menuItemName, Integer[] newInventoryItemIds)
    Updates the inventory item IDs of a given menu item.
    void
    updateMenuItemName(String menuItemName, String newName)
    This method updates the price of a given menu item name
    void
    updateMenuItemPrice(String menuItemName, float newPrice)
    This method updates the price of a given menu item name
    void
    updateStockStatus(int inventoryItemId)
    Updates the stock status of menu and food items based on the specified inventory item ID.
    boolean
    validateFoodItems(int foodItemId)
    Validates whether a food item is in stock based on its ID.
    boolean
    validateMenuItems(int menuItemId)
    Validates whether a menu item is in stock based on its ID.
    ResultSet
    viewEmployees(int tableOffset)
    Retrieves all employees from the employees table, with optional pagination based on the provided offset.
    ResultSet
    viewFoodItems(int tableOffset)
    Retrieves food item names from the database, with optional pagination support.
    ResultSet
    viewInventoryItems(int tableOffset)
    Retrieves a list of inventory items from the database, with an option to offset the results for pagination purposes.
    ResultSet
    viewMenuItems(int tableOffset)
    Retrieves menu item names from the database, with optional pagination support.
    ResultSet
    zeroRemaining()
    This method returns all inventory items with a quantity of 0.

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

  • Field Details

    • conn

      public Connection conn
      Represents a connection to the database.

  • Constructor Details

    • Database

      public Database(String dbName, String dbUser, String dbPass)
      Constructor of the Database object, does not initialize connection
      Parameters:
      dbName - The name of the SQL database.
      dbUser - The username of the login.
      dbPass - The user's password.

  • Method Details

    • connect

      public void connect() throws SQLException
      This method uses the database URL, username, and password to establish a connection.
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • disconnect

      public void disconnect() throws SQLException
      This method disconnects from the database and closes the connection between the database and the GUI.
      Throws:
      SQLException - When disconnecting to the database is unsuccessful. This is caused if the server is unresponsive.

    • query

      public ResultSet query(String sqlQuery) throws SQLException
      This method executes the query that is given and returns the result set of said query.
      Parameters:
      sqlQuery - The query that is sent to the database.
      Returns:
      the result of the query sent to the database.
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • zeroRemaining

      public ResultSet zeroRemaining() throws SQLException
      This method returns all inventory items with a quantity of 0.
      Returns:
      the result set of all the current inventory items with a quantity of 0.
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • averageDailySales

      public float averageDailySales() throws SQLException
      This method returns the daily average sales of the recorded orders.
      Returns:
      the calculated average daily sales represented as a float
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • foodItemInventory

      public int foodItemInventory(String name) throws SQLException
      This method returns the amount of ingredients a specified food item uses.
      Parameters:
      name - the name of the food item where the number of ingredients used needs to be identified.
      Returns:
      the number of ingredents used for the sepcified item represented as an int
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • getCustomerId

      public int getCustomerId() throws SQLException
      This method returns a random customer ID.
      Returns:
      a random customer's ID represented as an int.
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • getEmployeeId

      public int getEmployeeId() throws SQLException
      This method returns a random employee ID.
      Returns:
      a random employee's ID represented as an int.
      Throws:
      SQLException - When connection to the database in unsuccessful. This is caused by either incorrect credentials or network conectivity failure.

    • printMenuItems

      public void printMenuItems() throws SQLException
      Prints the menu items, including their IDs, names, and prices, to the console.
      Throws:
      SQLException - When an error occurs while accessing the database or executing the query.

    • printFoodItems

      public void printFoodItems(int menuItemId) throws SQLException
      Prints the food items associated with a specified menu item ID to the console.
      Parameters:
      menuItemId - The ID of the menu item for which to retrieve and print food items.
      Throws:
      SQLException - When an error occurs while accessing the database or executing the query.

    • placeOrder

      public void placeOrder(Cart cart, Timestamp orderedTime) throws SQLException
      Places an order based on the items in the provided cart and the specified order time.
      Parameters:
      cart - The cart containing the items to be ordered.
      orderedTime - The timestamp of when the order was placed.
      Throws:
      SQLException - When an error occurs while accessing the database or executing the insert operation.

    • viewMenuItems

      public ResultSet viewMenuItems(int tableOffset) throws SQLException
      Retrieves menu item names from the database, with optional pagination support.
      Parameters:
      tableOffset - The offset for pagination; determines how many rows to skip before starting to retrieve menu item names. If the value is greater than 0, it skips rows based on the offset multiplied by 50 (e.g., for paging through results).
      Returns:
      A ResultSet containing the names of the menu items. If no items are found, the result set will be empty.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • getDrinks

      public ResultSet getDrinks() throws SQLException
      Retrieves all items classified as drinks from the database.
      Returns:
      A ResultSet containing the details of all drinks.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • getEntrees

      public ResultSet getEntrees() throws SQLException
      Retrieves all items classified as entrees from the database.
      Returns:
      A ResultSet containing the details of all entrees.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • getAppetizers

      public ResultSet getAppetizers() throws SQLException
      Retrieves all items classified as appetizers from the database.
      Returns:
      A ResultSet containing the details of all appetizers.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • getSides

      public ResultSet getSides() throws SQLException
      Retrieves all items classified as sides from the database.
      Returns:
      A ResultSet containing the details of all sides.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • getDesserts

      public ResultSet getDesserts() throws SQLException
      Retrieves all items classified as desserts from the database.
      Returns:
      A ResultSet containing the details of all desserts.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • viewFoodItems

      public ResultSet viewFoodItems(int tableOffset) throws SQLException
      Retrieves food item names from the database, with optional pagination support.
      Parameters:
      tableOffset - The offset for pagination; determines how many rows to skip before starting to retrieve food item names. If the value is greater than 0, it skips rows based on the offset multiplied by 50 (e.g., for paging through results).
      Returns:
      A ResultSet containing the names of the food items. If no items are found, the result set will be empty.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • updateMenuItemPrice

      public void updateMenuItemPrice(String menuItemName, float newPrice) throws SQLException
      This method updates the price of a given menu item name
      Parameters:
      menuItemName - The name of the menu item.
      newPrice - The new price of the menu item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateMenuItemName

      public void updateMenuItemName(String menuItemName, String newName) throws SQLException
      This method updates the price of a given menu item name
      Parameters:
      menuItemName - The name of the menu item.
      newName - The new name of the menu item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateMenuItemFoodItems

      public void updateMenuItemFoodItems(String menuItemName, Integer[] newFoodItemIds) throws SQLException
      Updates the food item IDs of a given menu item.
      Parameters:
      menuItemName - The name of the menu item.
      newFoodItemIds - The new array of food item IDs.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateMenuItemInventoryItems

      public void updateMenuItemInventoryItems(String menuItemName, Integer[] newInventoryItemIds) throws SQLException
      Updates the inventory item IDs of a given menu item.
      Parameters:
      menuItemName - The name of the menu item.
      newInventoryItemIds - The new array of inventory item IDs.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • addMenuItem

      public void addMenuItem(String menuItemName, double price, Integer[] foodItemIds, Integer[] inventoryItemIds) throws SQLException
      Adds a new menu item to the MenuItems table with a custom menu item ID.
      Parameters:
      menuItemName - The name of the menu item.
      price - The price of the menu item.
      foodItemIds - An array of food item IDs associated with the menu item.
      inventoryItemIds - An array of inventory item IDs associated with the menu item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • deleteMenuItem

      public void deleteMenuItem(String menuItemName) throws SQLException
      Deletes a menu item from the MenuItems table based on the menu item name.
      Parameters:
      menuItemName - The name of the menu item to be deleted.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateFoodItemName

      public void updateFoodItemName(String currentName, String newName) throws SQLException
      Updates the name of a food item based on the provided current name.
      Parameters:
      currentName - The current name of the food item.
      newName - The new name of the food item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateFoodItemType

      public void updateFoodItemType(String foodItemName, String newType) throws SQLException
      Updates the type of a food item based on the provided current name.
      Parameters:
      foodItemName - The name of the food item.
      newType - The new type of the food item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateFoodItemInventoryItemIds

      public void updateFoodItemInventoryItemIds(String foodItemName, Integer[] newInventoryItemIds) throws SQLException
      Updates the inventory item IDs of a food item based on the provided name.
      Parameters:
      foodItemName - The name of the food item.
      newInventoryItemIds - The new array of inventory item IDs.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateFoodItemInventoryAmounts

      public void updateFoodItemInventoryAmounts(String foodItemName, Double[] newInventoryAmounts) throws SQLException
      Updates the inventory amounts of a food item based on the provided name.
      Parameters:
      foodItemName - The name of the food item.
      newInventoryAmounts - The new array of inventory amounts.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • addFoodItem

      public void addFoodItem(String foodItemName, String type, Integer[] inventoryItemIds, Double[] inventoryAmounts, Integer[] seasonal) throws SQLException
      Adds a new food item to the FoodItems table with a custom food item ID.
      Parameters:
      foodItemName - The name of the food item.
      type - The type of the food item.
      inventoryItemIds - An array of inventory item IDs associated with the food item.
      inventoryAmounts - An array of inventory amounts corresponding to each inventory item ID.
      seasonal - An array of inventory items which are only avaliable for limited time duration
      Throws:
      SQLException - If an error occurs while accessing the database.

    • deleteFoodItem

      public void deleteFoodItem(String foodItemName) throws SQLException
      Deletes a food item from the FoodItems table based on the food item name, and removes the food item ID from the foodItem_ids arrays of all MenuItems.
      Parameters:
      foodItemName - The name of the food item to be deleted.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateInventoryItemQuantity

      public void updateInventoryItemQuantity(String inventoryItemName, double newQuantity) throws SQLException
      Updates the quantity of an inventory item by its name.
      Parameters:
      inventoryItemName - The name of the inventory item.
      newQuantity - The new quantity of the inventory item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateInventoryItemLastRestocked

      public void updateInventoryItemLastRestocked(String inventoryItemName, Date newLastRestocked) throws SQLException
      Updates the last restocked date of an inventory item by its name.
      Parameters:
      inventoryItemName - The name of the inventory item.
      newLastRestocked - The new last restocked date of the inventory item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • addInventoryItem

      public void addInventoryItem(String inventoryItemName, double quantity, Date lastRestocked) throws SQLException
      Adds a new inventory item to the inventoryitems table with a custom inventory item ID.
      Parameters:
      inventoryItemName - The name of the inventory item.
      quantity - The quantity of the inventory item.
      lastRestocked - The last restocked date of the inventory item.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • deleteInventoryItem

      public void deleteInventoryItem(String inventoryItemName) throws SQLException
      Deletes an inventory item from the database based on the provided item name.
      Parameters:
      inventoryItemName - The name of the inventory item to be deleted from the database. If no item with the specified name exists, no changes will be made.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL delete operation.

    • viewInventoryItems

      public ResultSet viewInventoryItems(int tableOffset) throws SQLException
      Retrieves a list of inventory items from the database, with an option to offset the results for pagination purposes.
      Parameters:
      tableOffset - The number of records to skip before starting to return the inventory items. This is useful for paginating results. If set to 0, the query will return all items without any offset.
      Returns:
      A ResultSet containing the inventory items retrieved from the database.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • addEmployee

      public void addEmployee(String firstName, String lastName, float hoursWorked, Time[] schedule) throws SQLException
      Adds a new employee to the employees table.
      Parameters:
      firstName - The first name of the employee.
      lastName - The last name of the employee.
      hoursWorked - The number of hours worked by the employee.
      schedule - An array of java.sql.Time representing the employee's schedule.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateEmployeeHours

      public void updateEmployeeHours(int employeeId, float newHours) throws SQLException
      Updates the hours worked for a specific employee.
      Parameters:
      employeeId - The ID of the employee to update.
      newHours - The new number of hours worked by the employee.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • updateEmployeeSchedule

      public void updateEmployeeSchedule(int employeeId, Time[] newSchedule) throws SQLException
      Updates the schedule for a specific employee.
      Parameters:
      employeeId - The ID of the employee to update.
      newSchedule - An array of java.sql.Time representing the new schedule for the employee.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • deleteEmployee

      public void deleteEmployee(int employeeId) throws SQLException
      Deletes an employee from the database based on the provided employee ID.
      Parameters:
      employeeId - The ID of the employee to be deleted from the database. If no employee with the specified ID exists, no changes will be made.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL delete operation.

    • viewEmployees

      public ResultSet viewEmployees(int tableOffset) throws SQLException
      Retrieves all employees from the employees table, with optional pagination based on the provided offset.
      Parameters:
      tableOffset - The number of records to skip before returning employee records. This is useful for paginating results, allowing you to control which subset of employees to retrieve. For example, a tableOffset of 1 would skip the first 50 employees.
      Returns:
      A ResultSet containing the employee IDs, first names, and last names.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • createXReport

      public ResultSet createXReport(Date currDate) throws SQLException
      Creates an X report for a specific date, summarizing total sales per hour.
      Parameters:
      currDate - The date for which to generate the report.
      Returns:
      A ResultSet containing the hours of the day and total sales.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • createZReport

      public ResultSet createZReport(Date currDate) throws SQLException
      Creates a Z report for a specific date, resetting the total sales for that day.
      Parameters:
      currDate - The date for which to generate the report.
      Returns:
      A ResultSet from the X report for the specified date.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • createSalesReport

      public ResultSet createSalesReport(Date startTime, Date endTime) throws SQLException
      Creates a sales report for a specific date range, summarizing menu item sales.
      Parameters:
      startTime - The start date for the report.
      endTime - The end date for the report.
      Returns:
      A ResultSet containing menu item names, total quantities sold, and total sales.
      Throws:
      SQLException - If an error occurs while accessing the database.

    • orderHistory

      public ResultSet orderHistory(int tableOffset) throws SQLException
      Retrieves a paginated history of orders from the database, ordered by the most recent first.
      Parameters:
      tableOffset - The page offset for retrieving order history. If greater than 0, results will be offset by the specified value.
      Returns:
      A ResultSet containing the details of the orders, limited to 50 results per page.
      Throws:
      SQLException - If there is an error accessing the database or executing the SQL query.

    • editMenuItem

      public void editMenuItem(ArrayList<String> edits) throws SQLException
      Updates the details of a menu item in the MenuItems table based on the provided edits. If any of the fields are empty, that specific attribute of the menu item will not be updated.
      Parameters:
      edits - An ArrayList of Strings containing the details of the menu item to update.
      Throws:
      SQLException - If a database access error occurs or if invalid values are provided for the SQL query.

    • editFoodItem

      public void editFoodItem(ArrayList<String> edits) throws SQLException
      Updates the details of a food item in the FoodItems table based on the provided edits. If any of the fields are empty, that specific attribute of the food item will not be updated.
      Parameters:
      edits - An ArrayList of Strings containing the details of the food item to update.
      Throws:
      SQLException - If a database access error occurs or if invalid values are provided for the SQL query.

    • editEmployee

      public void editEmployee(ArrayList<String> edits) throws SQLException
      Updates the details of an employee in the Employees table based on the provided edits. If any of the fields are empty, that specific attribute of the employee will not be updated.
      Parameters:
      edits - An ArrayList of Strings containing the details of the employee to update.
      Throws:
      SQLException - If a database access error occurs or if invalid values are provided for the SQL query.

    • editOrder

      public void editOrder(ArrayList<String> edits) throws SQLException
      Updates the details of an order in the Orders table based on the provided edits. If any of the fields are empty, that specific attribute of the order will not be updated.
      Parameters:
      edits - An ArrayList of Strings containing the details of the order to update.
      Throws:
      SQLException - If a database access error occurs or if invalid values are provided for the SQL query.

    • editInventoryItem

      public void editInventoryItem(ArrayList<String> edits) throws SQLException
      Updates the details of an inventory item in the inventoryitems table based on the provided edits. If any of the fieldsare empty, that specific attribute of the inventory item will not be updated.
      Parameters:
      edits - An ArrayList of Strings containing the details of the inventory item to update.
      Throws:
      SQLException - If a database access error occurs or if invalid values are provided for the SQL query.

    • validateMenuItems

      public boolean validateMenuItems(int menuItemId) throws SQLException
      Validates whether a menu item is in stock based on its ID.

      This method queries the database to check if a menu item, identified by its ID, is currently in stock. It retrieves the 'in_stock' status from the MenuItems table.

      Parameters:
      menuItemId - the ID of the menu item to validate
      Returns:
      true if the menu item is in stock; false otherwise
      Throws:
      SQLException - if there is an error executing the database query

    • validateFoodItems

      public boolean validateFoodItems(int foodItemId) throws SQLException
      Validates whether a food item is in stock based on its ID.

      This method queries the database to check if a food item, identified by its ID, is currently in stock. It retrieves the 'in_stock' status from the FoodItems table.

      Parameters:
      foodItemId - the ID of the food item to validate
      Returns:
      true if the food item is in stock; false otherwise
      Throws:
      SQLException - if there is an error executing the database query

    • getInventoryQuantity

      public double getInventoryQuantity(int inventoryId) throws SQLException
      Retrieves the quantity of a specific inventory item from the database.

      This method executes a SQL query to fetch the quantity of the inventory item corresponding to the provided inventory ID. It uses a PreparedStatement to safely handle the SQL query with a parameterized inventory ID.

      Parameters:
      inventoryId - the ID of the inventory item whose quantity is to be retrieved.
      Returns:
      the quantity of the specified inventory item.
      Throws:
      SQLException - if a database access error occurs, or if no inventory item is found with the given ID.

    • updateStockStatus

      public void updateStockStatus(int inventoryItemId) throws SQLException
      Updates the stock status of menu and food items based on the specified inventory item ID.

      This method queries the database for menu items and food items that are marked as out of stock (i.e., `in_stock = false`). It then processes these items using the provided inventory item ID to update their stock status accordingly.

      The queries target two tables: `MenuItems` and `FoodItems`, and the stock status is updated by invoking the processItems method for each result set.

      Parameters:
      inventoryItemId - the ID of the inventory item to use when updating stock status.
      Throws:
      SQLException - if a database access error occurs during the execution of queries.