Class DB

java.lang.Object
team75.model.DB
public class DB extends Object
Database access layer for the restaurant POS system. Provides CRUD operations for menu items, inventory, employees, and orders.
Author:
Raghu

  • Constructor Details

    • DB

      public DB()

  • Method Details

    • connect

      public static Connection connect() throws SQLException
      Opens a new connection to the PostgreSQL database.
      Returns:
      a live JDBC connection
      Throws:
      SQLException - if the connection cannot be established

    • getMenu

      public static List<DB.MenuItem> getMenu() throws SQLException
      Retrieves all menu items ordered by ID.
      Returns:
      list of all menu items
      Throws:
      SQLException - if the query fails

    • getMenuByCategory

      public static List<DB.MenuItem> getMenuByCategory(String category) throws SQLException
      Retrieves menu items filtered by category.
      Parameters:
      category - the category to filter by (e.g. "Classic Drink", "Food", "Add-on")
      Returns:
      list of matching menu items
      Throws:
      SQLException - if the query fails

    • getPrice

      public static BigDecimal getPrice(String itemName) throws SQLException
      Looks up the price of a menu item by name (case-insensitive).
      Parameters:
      itemName - the item name to look up
      Returns:
      the item's price, or null if not found
      Throws:
      SQLException - if the query fails

    • addMenuItem

      public static int addMenuItem(String name, String category, BigDecimal price, String description) throws SQLException
      Adds a new menu item with an auto-generated ID.
      Parameters:
      name - item display name
      category - item category
      price - item price
      description - item description
      Returns:
      the generated item ID
      Throws:
      SQLException - if the insert fails

    • updateMenuItem

      public static int updateMenuItem(int itemId, String name, String category, BigDecimal price, String description) throws SQLException
      Updates an existing menu item's fields.
      Parameters:
      itemId - ID of the item to update
      name - new item name
      category - new category
      price - new price
      description - new description
      Returns:
      number of rows affected
      Throws:
      SQLException - if the update fails

    • updateMenuPrice

      public static int updateMenuPrice(int itemId, BigDecimal newPrice) throws SQLException
      Updates only the price of a menu item.
      Parameters:
      itemId - ID of the item to update
      newPrice - the new price
      Returns:
      number of rows affected
      Throws:
      SQLException - if the update fails

    • deleteMenuItem

      public static int deleteMenuItem(int itemId) throws SQLException
      Deletes a menu item and its ingredient mappings in a transaction.
      Parameters:
      itemId - ID of the item to delete
      Returns:
      number of rows deleted from the menu table
      Throws:
      SQLException - if the delete fails (transaction is rolled back)

    • addMenuIngredientMapping

      public static void addMenuIngredientMapping(int itemId, int ingredientId, double quantity) throws SQLException
      Adds a single ingredient mapping to a menu item's recipe.
      Parameters:
      itemId - the menu item ID
      ingredientId - the inventory ingredient ID
      quantity - amount of ingredient consumed per order
      Throws:
      SQLException - if the insert fails

    • addMenuItemWithIngredients

      public static int addMenuItemWithIngredients(String name, String category, BigDecimal price, String description, List<DB.IngredientRecipe> recipe) throws SQLException
      Adds a new seasonal (or regular) menu item together with its full ingredient recipe in a single transaction. This is the primary method for adding items to the POS — it creates the menu row and all menuingredientsmap rows atomically, so the item is never left in a half-configured state.
      Parameters:
      name - item display name
      category - item category (e.g. "Classic Drink", "Fruit Drink")
      price - item price
      description - item description
      recipe - list of ingredient-quantity pairs defining the recipe; each ingredient must already exist in the inventory table
      Returns:
      the generated menu item ID
      Throws:
      SQLException - if the transaction fails (rolled back on error)

    • getInventory

      public static List<DB.InventoryItem> getInventory() throws SQLException
      Retrieves all inventory items ordered by ID.
      Returns:
      list of all inventory items
      Throws:
      SQLException - if the query fails

    • addInventoryItem

      public static void addInventoryItem(int ingredientId, String name, double quantity, String units) throws SQLException
      Adds a new inventory item.
      Parameters:
      ingredientId - unique ingredient ID
      name - ingredient name
      quantity - initial stock quantity
      units - unit of measurement
      Throws:
      SQLException - if the insert fails

    • updateInventoryItem

      public static int updateInventoryItem(int ingredientId, String name, double quantity, String units) throws SQLException
      Updates an inventory item's name, quantity, and units.
      Parameters:
      ingredientId - ID of the ingredient to update
      name - new ingredient name
      quantity - new quantity
      units - new unit of measurement
      Returns:
      number of rows affected
      Throws:
      SQLException - if the update fails

    • updateInventoryQuantity

      public static int updateInventoryQuantity(int ingredientId, double quantity) throws SQLException
      Updates only the quantity of an inventory item.
      Parameters:
      ingredientId - ID of the ingredient to update
      quantity - new quantity value
      Returns:
      number of rows affected
      Throws:
      SQLException - if the update fails

    • deleteInventoryItem

      public static int deleteInventoryItem(int ingredientId) throws SQLException
      Deletes an inventory item and its menu-ingredient mappings in a transaction.
      Parameters:
      ingredientId - ID of the ingredient to delete
      Returns:
      number of rows deleted from the inventory table
      Throws:
      SQLException - if the delete fails (transaction is rolled back)

    • getEmployees

      public static List<DB.Employee> getEmployees() throws SQLException
      Retrieves all employees ordered by ID.
      Returns:
      list of all employees
      Throws:
      SQLException - if the query fails

    • addEmployee

      public static void addEmployee(int employeeId, String name, String access, int age, String phone, String password) throws SQLException
      Adds a new employee to the database.
      Parameters:
      employeeId - unique employee ID
      name - employee full name
      access - access level ("manager" or "cashier")
      age - employee age
      phone - employee phone number
      password - employee login password
      Throws:
      SQLException - if the insert fails

    • updateEmployee

      public static int updateEmployee(int employeeId, String name, String access, int age, String phone) throws SQLException
      Updates an employee's name, access level, age, and phone.
      Parameters:
      employeeId - ID of the employee to update
      name - new name
      access - new access level
      age - new age
      phone - new phone number
      Returns:
      number of rows affected
      Throws:
      SQLException - if the update fails

    • deleteEmployee

      public static int deleteEmployee(int employeeId) throws SQLException
      Deletes an employee and their check-in records in a transaction.
      Parameters:
      employeeId - ID of the employee to delete
      Returns:
      number of rows deleted from the employees table
      Throws:
      SQLException - if the delete fails (transaction is rolled back)

    • getEmployeeCheckins

      public static List<DB.EmployeeCheckin> getEmployeeCheckins() throws SQLException
      Retrieves all employee check-in/check-out records.
      Returns:
      list of all check-in records
      Throws:
      SQLException - if the query fails

    • clockIn

      public static void clockIn(int employeeId) throws SQLException
      Clocks an employee in, setting timein to now and clearing timeout. Uses upsert so re-clocking-in overwrites the previous record.
      Parameters:
      employeeId - ID of the employee to clock in
      Throws:
      SQLException - if the upsert fails

    • clockOut

      public static void clockOut(int employeeId) throws SQLException
      Clocks an employee out by setting timeout to now.
      Parameters:
      employeeId - ID of the employee to clock out
      Throws:
      SQLException - if the update fails

    • submitOrder

      public static UUID submitOrder(int employeeId, List<DB.OrderItem> items, BigDecimal total) throws SQLException
      Inserts a new order into orderhistory and decrements inventory for every ingredient used by the ordered items and their add-ons. Runs as a single transaction so inventory stays consistent.
      Parameters:
      employeeId - ID of the employee placing the order
      items - list of order line-items
      total - total price for the order
      Returns:
      the generated order UUID
      Throws:
      SQLException - if the transaction fails (rolled back on error)

    • getOrders

      public static List<DB.Order> getOrders(Timestamp start, Timestamp end) throws SQLException
      Retrieves orders placed within a time window.
      Parameters:
      start - start of the time range (inclusive)
      end - end of the time range (inclusive)
      Returns:
      list of orders in descending time order
      Throws:
      SQLException - if the query fails

    • getSalesReport

      public static DB.SalesSummary getSalesReport(Timestamp start, Timestamp end) throws SQLException
      Overall sales summary for a time window: total orders, total revenue, and an hour-by-hour breakdown.
      Parameters:
      start - start of the time range (inclusive)
      end - end of the time range (inclusive)
      Returns:
      a SalesSummary with totals and hourly breakdown
      Throws:
      SQLException - if the query fails

    • getProductSalesReport

      public static List<DB.ProductSalesEntry> getProductSalesReport(Timestamp start, Timestamp end) throws SQLException
      Counts how many times each menu item was sold in a time window by parsing the JSONB orderdetails column.
      Parameters:
      start - start of the time range (inclusive)
      end - end of the time range (inclusive)
      Returns:
      list of product sales entries sorted by quantity descending
      Throws:
      SQLException - if the query fails

    • getProductUsageChart

      public static List<DB.ProductUsageEntry> getProductUsageChart(Timestamp start, Timestamp end) throws SQLException
      Calculates total ingredient consumption in a time window by joining items sold (from JSONB) with their recipes in menuingredientsmap.
      Parameters:
      start - start of the time range (inclusive)
      end - end of the time range (inclusive)
      Returns:
      list of ingredient usage entries sorted by amount descending
      Throws:
      SQLException - if the query fails

    • getEmployeeSalesReport

      public static List<DB.EmployeeSalesEntry> getEmployeeSalesReport(Timestamp start, Timestamp end) throws SQLException
      Per-employee sales performance in a time window: order count and total revenue for each employee who took orders.
      Parameters:
      start - start of the time range (inclusive)
      end - end of the time range (inclusive)
      Returns:
      list of employee sales entries sorted by revenue descending
      Throws:
      SQLException - if the query fails

    • hasZReportBeenRunToday

      public static boolean hasZReportBeenRunToday() throws SQLException
      Checks whether the Z-Report has already been generated for today.
      Returns:
      true if the Z-Report was already run today
      Throws:
      SQLException - if the query fails

    • getXReport

      public static DB.SalesSummary getXReport() throws SQLException
      X-Report: hourly sales breakdown for the current day of operation. Industry-standard register report that shows sales activity per hour so managers can identify rush and lull periods. This report has no side effects and can be run as often as desired.

      If the Z-Report has already been run today (day is closed), returns a zeroed-out summary since totals have been reset.

      Returns:
      today's sales summary with hourly breakdown, or zeros if day is closed
      Throws:
      SQLException - if the query fails

    • getZReport

      public static DB.SalesSummary getZReport() throws SQLException
      Z-Report: end-of-day report that totals all sales and resets counters for the next business day. Industry-standard close-out report that should only be run once per day when no more customers are expected.

      Includes the same hourly breakdown as the X-Report but covers the full day. After generation, the report date is logged so that subsequent X-Reports return zeros (totals are "reset") and the Z-Report cannot be generated again for the same day.

      Returns:
      today's complete sales summary, or null if already run today
      Throws:
      SQLException - if the query fails

    • main

      public static void main(String[] args)
      Connects to the DB and prints menu and employees.
      Parameters:
      args - command-line arguments (unused)