Module 6: Inventory & Recipe Management | Bakery Management System Course

From Products to Ingredients

A robust management system doesn't just track finished products; it tracks the raw materials used to create them. This is crucial for managing costs, planning purchases, and preventing shortages.

In this module, we will build an inventory system for our raw ingredients (like flour, sugar, and eggs). Then, we'll create a recipe manager to link these ingredients to our final products. The most powerful part will be automating the inventory deduction: when a cake is sold, the system will automatically reduce the stock of the ingredients used to make it.

Step 1: Expanding the Database Schema

We need two new tables to support this functionality:

`ingredients`: This table will store our raw materials, their unit of measurement (e.g., kg, liters, units), and the current stock level.
`recipe_items`: This is a "linking" table that connects our `products` and `ingredients` tables. It will define how much of each ingredient is required for one unit of a product.

Open phpMyAdmin, select your `bakery_db` database, and run the following SQL script.

--
-- Table structure for table `ingredients`
--
CREATE TABLE `ingredients` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `unit` VARCHAR(20) NOT NULL,
  `stock` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `recipe_items`
--
CREATE TABLE `recipe_items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `product_id` INT(11) NOT NULL,
  `ingredient_id` INT(11) NOT NULL,
  `quantity` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ingredient_id`) REFERENCES `ingredients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 2: CRUD for Ingredients

Just like we did for categories and products, we need an admin page to manage our ingredients. Create a file named `manage_inventory.php` in the `admin/` folder. This page will allow you to add, view, update, and delete ingredients from your inventory.

admin/manage_inventory.php

<?php
session_start();
require_once '../includes/db.php';

// ... (Add your user authentication check here) ...

// Handle form submissions for adding/updating ingredients
// ... (Your CRUD logic for the 'ingredients' table goes here) ...

// Fetch all ingredients
$ingredients = $conn->query("SELECT * FROM ingredients ORDER BY name ASC");
?>
<h2>Manage Inventory</h2>

<!-- Form to add/edit ingredients -->
<form action="manage_inventory.php" method="post">
    <input type="text" name="name" placeholder="Ingredient Name" required>
    <input type="text" name="unit" placeholder="Unit (e.g., kg, l, pcs)" required>
    <input type="number" step="0.01" name="stock" placeholder="Current Stock" required>
    <button type="submit">Add Ingredient</button>
</form>

<!-- Table to display ingredients -->
<table border="1">
    <tr><th>Name</th><th>Stock</th><th>Unit</th><th>Actions</th></tr>
    <?php while ($row = $ingredients->fetch_assoc()): ?>
    <tr>
        <td><?php echo htmlspecialchars($row['name']); ?></td>
        <td><?php echo $row['stock']; ?></td>
        <td><?php echo htmlspecialchars($row['unit']); ?></td>
        <td>Edit / Delete</td>
    </tr>
    <?php endwhile; ?>
</table>

Step 3: Automating Stock Deduction in `process_order.php`

This is the integration step. We need to modify our existing `process_order.php` script. After an order is successfully placed, we will loop through each item sold, check its recipe, and deduct the corresponding ingredients from our inventory.

This logic should be placed inside the database transaction to ensure that an order is not processed unless the ingredient stock can also be successfully updated.

admin/process_order.php (Modified)

<?php
// ... (database connection and existing order processing code) ...

// Start a transaction
$conn->begin_transaction();

try {
    // ... (1. Insert into the orders table) ...
    // ... (2. Insert into order_items and update product stock) ...

    // 3. NEW: Update ingredient stock based on recipes
    $sql_get_recipe = "SELECT ingredient_id, quantity FROM recipe_items WHERE product_id = ?";
    $sql_update_ingredient = "UPDATE ingredients SET stock = stock - ? WHERE id = ?";
    
    foreach ($cart as $product_id => $item) {
        $product_quantity_sold = $item['quantity'];

        if ($stmt_recipe = $conn->prepare($sql_get_recipe)) {
            $stmt_recipe->bind_param("i", $product_id);
            $stmt_recipe->execute();
            $recipe = $stmt_recipe->get_result();

            while ($ingredient = $recipe->fetch_assoc()) {
                $ingredient_id = $ingredient['ingredient_id'];
                // Quantity needed per product * number of products sold
                $total_ingredient_needed = $ingredient['quantity'] * $product_quantity_sold;

                $stmt_update_ing = $conn->prepare($sql_update_ingredient);
                $stmt_update_ing->bind_param("di", $total_ingredient_needed, $ingredient_id);
                $stmt_update_ing->execute();
            }
        }
    }
    
    // If everything is successful, commit the transaction
    $conn->commit();
    echo "Success";

} catch (Exception $e) {
    // If any query fails, roll back the changes
    $conn->rollback();
    echo "Failed: " . $e->getMessage();
}
?>