Advanced Queries for Restaurant Management with SQLite (Part 2)

Streamlining Inventory, Payment Processing, and Reporting for Efficient Restaurant Operations

In Part 1, we set up SQLite for your restaurant management system, covering basic operations such as creating the database, managing orders, and handling inventory. Now, in Part 2, we’ll take it further by implementing advanced database queries and enhancements that will help streamline your system’s functionality. If you’ve jumped ahead, start from our Beginner’s Guide to Mastering SQLite.

In this blog, you’ll learn how to manage inventory more efficiently, process payments, generate reports, and even handle complex queries for better customer and order management.

By the end of this blog, you’ll know how to:

• Handle complex queries to manage inventory and orders.

• Process payments and update the database accordingly.

• Generate reports for administrators to monitor sales and inventory.

• Implement data validation and ensure data integrity.

Step 1: Managing Inventory with Complex Queries

As your restaurant grows, managing inventory effectively becomes more critical. We’ll start by exploring how to handle inventory levels with complex queries.

Checking Available Stock

Before processing an order, it’s important to check if the inventory has enough stock to fulfill it. This query helps you ensure that you don’t oversell items.

SELECT item_name, quantity_in_stock
FROM inventory
JOIN menu_item ON inventory.item_id = menu_item.item_id
WHERE menu_item.item_name = 'Pizza' AND inventory.quantity_in_stock >= 10;

Explanation:

• This query checks if there are at least 10 units of Pizza available in inventory before fulfilling an order.

• If the quantity is sufficient, the item can be added to the order.

Updating Inventory After an Order

When an order is placed, it’s crucial to update the inventory to reflect the reduced stock. Here’s a realistic example:

@app.route('/place_order', methods=['POST'])
def place_order():
    item_id = request.form['item_id']
    quantity = request.form['quantity']
    
    # Check if enough stock is available
    inventory = Inventory.query.filter_by(item_id=item_id).first()
    if inventory.quantity_in_stock < quantity:
        return "Insufficient stock. Please reduce quantity or choose another item."

    # Create the order
    new_order = Order(item_id=item_id, quantity=quantity)
    db.session.add(new_order)
    db.session.commit()

    # Update inventory
    inventory.quantity_in_stock -= quantity
    db.session.commit()

    return "Order placed successfully!"

Explanation:

• Before creating an order, we ensure that there’s sufficient stock in the inventory.

• If there’s enough stock, we reduce the available quantity and update the inventory.

Step 2: Processing Payments

When a customer places an order, processing the payment and updating the database accordingly is essential for keeping track of revenue.

Creating a Payment Record

@app.route('/process_payment', methods=['POST'])
def process_payment():
    order_id = request.form['order_id']
    amount_paid = request.form['amount_paid']

    # Create payment record
    payment = Payment(order_id=order_id, amount_paid=amount_paid)
    db.session.add(payment)
    db.session.commit()

    # Update the order status to 'Paid'
    order = Order.query.get(order_id)
    order.status = "Paid"
    db.session.commit()

    return "Payment processed successfully!"

Explanation:

• When a payment is processed, we create a Payment record and link it to the order.

• The order status is updated to "Paid", ensuring the payment has been received and recorded.

Step 3: Generating Reports for Administrators

As the restaurant grows, administrators need to track sales and inventory levels. SQLite allows you to generate custom reports to make data-driven decisions.

Generating a Daily Sales Report

SELECT order_date, SUM(amount_paid) AS total_sales
FROM payment
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY order_date;

Explanation:

• This query generates a report showing total sales for each day in January 2025.

• The query uses SUM(amount_paid) to get the total amount paid for all orders placed on each date.

Generating Monthly Sales Report

To make it even more useful, let’s generate a monthly report and group by item sold.

SELECT menu_item.item_name, SUM(order_items.quantity) AS total_sold, SUM(order_items.quantity * menu_item.price) AS revenue
FROM order_items
JOIN menu_item ON order_items.item_id = menu_item.item_id
WHERE order_items.order_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY menu_item.item_name; 

Explanation:

• This query helps to generate a monthly report by menu item. It gives insights into how much of each item was sold and the revenue generated for each.

• It joins the order_items and menu_item tables and groups results by item name, summing the quantities and revenue.

Step 4: Data Validation and Integrity

Ensuring that your data is valid and consistent is crucial for the integrity of the database. We will implement data validation to prevent invalid entries, such as duplicate orders or negative inventory.

Validating Unique Orders

@app.route('/create_order', methods=['POST'])
def create_order():
    item_id = request.form['item_id']
    quantity = request.form['quantity']

    # Ensure inventory is not negative
    inventory = Inventory.query.filter_by(item_id=item_id).first()
    if inventory.quantity_in_stock < quantity:
        return "Not enough stock available!"

    # Check for duplicate orders
    existing_order = Order.query.filter_by(item_id=item_id, status="Pending").first()
    if existing_order:
        return "This item has already been ordered. Please finalize the current order."

    new_order = Order(item_id=item_id, quantity=quantity)
    db.session.add(new_order)
    db.session.commit()

    # Update the inventory
    inventory.quantity_in_stock -= quantity
    db.session.commit()

    return "Order created successfully!"

Explanation:

• We first ensure that there is enough inventory before creating the order.

• We also check for duplicate orders to prevent the same order being placed multiple times for the same item.

• This helps maintain the integrity of both the orders and inventory.

Step 5: Handling Complex Queries with Multiple Tables

Sometimes you’ll need to run queries that join data from multiple tables to get more detailed information. For example, let’s create a query that provides a detailed order summary.

SELECT orders.order_id, menu_item.item_name, orders.quantity, orders.status
FROM orders
JOIN menu_item ON orders.item_id = menu_item.item_id
WHERE orders.status = 'Pending';

Explanation:

• This query shows a list of all pending orders with details on the menu item name, quantity, and order status.

• It is useful for restaurant managers to keep track of what orders are still in the queue and need attention.

Conclusion (Part 2)

In Part 2, we’ve enhanced our restaurant management system by implementing advanced queries for managing inventory and orders, processing payments, generating useful reports for restaurant admins, and ensuring data validation and integrity.

In Part 3, we’ll explore real-time updates for order tracking, automating inventory updates, and implementing role-based access control to ensure only authorized staff can access certain features of the system.

Subscribe Now

Stay updated with the latest tips and tutorials on optimizing your restaurant management system with SQLite! Subscribe now to receive expert advice, step-by-step guides, and updates directly in your inbox. Don’t miss out on the next parts of the Restaurant Management System series and more. Join our community at the SQLite Forum to ask questions, share experiences, and connect with fellow developers!