Tkinter Shop Manager Code

import os
import sqlite3
import tempfile
from datetime import datetime
import tkinter as tk
from tkinter import ttk, messagebox

# =====================================================
# Small Shop Inventory and Billing Management System
# Python + Tkinter + SQLite
# Beginner-friendly student project
# =====================================================

DB_NAME = "shop_inventory.db"
SHOP_NAME = "Nimal Grocery Shop"
SHOP_ADDRESS = "Main Street, Colombo"
SHOP_CONTACT = "Contact: 077 123 4567"
LOW_STOCK_LIMIT = 5


def format_lkr(amount):
    """Format money values as Sri Lankan Rupees."""
    return f"LKR {amount:,.2f}"


class InventoryBillingApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Small Shop Inventory and Billing Management System")
        self.root.geometry("1150x700")
        self.root.configure(bg="#f4f6f8")

        # Variables used by the program
        self.selected_product_id = None
        self.bill_items = []
        self.current_receipt = ""

        # Start application
        self.create_database()
        self.create_widgets()
        self.load_products()
        self.update_bill_number_label()

    # =====================================================
    # DATABASE SETUP
    # =====================================================
    def create_database(self):
        """Create all required database tables if they do not already exist."""
        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                price REAL NOT NULL,
                quantity INTEGER NOT NULL
            )
        """)

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS bills (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bill_number TEXT NOT NULL UNIQUE,
                total_amount REAL NOT NULL,
                date TEXT NOT NULL
            )
        """)

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS bill_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bill_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL,
                subtotal REAL NOT NULL,
                FOREIGN KEY (bill_id) REFERENCES bills(id),
                FOREIGN KEY (product_id) REFERENCES products(id)
            )
        """)

        # Simple sales table requested in the assignment.
        # One row is saved for each product line sold.
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS sales (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bill_number TEXT NOT NULL,
                product_id INTEGER NOT NULL,
                product_name TEXT NOT NULL,
                quantity INTEGER NOT NULL,
                unit_price REAL NOT NULL,
                subtotal REAL NOT NULL,
                date TEXT NOT NULL,
                FOREIGN KEY (product_id) REFERENCES products(id)
            )
        """)

        conn.commit()
        conn.close()

    # =====================================================
    # GUI DESIGN
    # =====================================================
    def create_widgets(self):
        title = tk.Label(
            self.root,
            text="Small Shop Inventory and Billing Management System",
            font=("Arial", 18, "bold"),
            bg="#2c3e50",
            fg="white",
            pady=12
        )
        title.pack(fill=tk.X)

        main_frame = tk.Frame(self.root, bg="#f4f6f8")
        main_frame.pack(fill=tk.BOTH, expand=True, padx=12, pady=12)

        left_frame = tk.Frame(main_frame, bg="#f4f6f8")
        left_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=True, padx=(0, 8))

        right_frame = tk.Frame(main_frame, bg="#f4f6f8")
        right_frame.pack(side=tk.RIGHT, fill=tk.BOTH, expand=True, padx=(8, 0))

        # Product management section
        form_frame = tk.LabelFrame(left_frame, text="Product Management", font=("Arial", 12, "bold"), bg="#f4f6f8", padx=10, pady=10)
        form_frame.pack(fill=tk.X)

        tk.Label(form_frame, text="Product Name", bg="#f4f6f8").grid(row=0, column=0, sticky="w", pady=5)
        self.name_entry = tk.Entry(form_frame, width=30)
        self.name_entry.grid(row=0, column=1, pady=5, padx=5)

        tk.Label(form_frame, text="Price", bg="#f4f6f8").grid(row=1, column=0, sticky="w", pady=5)
        self.price_entry = tk.Entry(form_frame, width=30)
        self.price_entry.grid(row=1, column=1, pady=5, padx=5)

        tk.Label(form_frame, text="Quantity", bg="#f4f6f8").grid(row=2, column=0, sticky="w", pady=5)
        self.quantity_entry = tk.Entry(form_frame, width=30)
        self.quantity_entry.grid(row=2, column=1, pady=5, padx=5)

        button_frame = tk.Frame(form_frame, bg="#f4f6f8")
        button_frame.grid(row=3, column=0, columnspan=2, pady=10)

        tk.Button(button_frame, text="Add Product", width=14, bg="#27ae60", fg="white", command=self.add_product).grid(row=0, column=0, padx=4)
        tk.Button(button_frame, text="Update Product", width=14, bg="#2980b9", fg="white", command=self.update_product).grid(row=0, column=1, padx=4)
        tk.Button(button_frame, text="Delete Product", width=14, bg="#c0392b", fg="white", command=self.delete_product).grid(row=0, column=2, padx=4)
        tk.Button(button_frame, text="Clear Fields", width=14, command=self.clear_product_fields).grid(row=0, column=3, padx=4)

        # Product list table section
        table_frame = tk.LabelFrame(left_frame, text="Product List", font=("Arial", 12, "bold"), bg="#f4f6f8", padx=10, pady=10)
        table_frame.pack(fill=tk.BOTH, expand=True, pady=10)

        columns = ("id", "name", "price", "quantity", "status")
        self.product_tree = ttk.Treeview(table_frame, columns=columns, show="headings", height=14)
        self.product_tree.heading("id", text="ID")
        self.product_tree.heading("name", text="Product Name")
        self.product_tree.heading("price", text="Price")
        self.product_tree.heading("quantity", text="Quantity")
        self.product_tree.heading("status", text="Stock Status")

        self.product_tree.column("id", width=50, anchor="center")
        self.product_tree.column("name", width=180)
        self.product_tree.column("price", width=110, anchor="e")
        self.product_tree.column("quantity", width=90, anchor="center")
        self.product_tree.column("status", width=120, anchor="center")
        self.product_tree.pack(fill=tk.BOTH, expand=True)
        self.product_tree.bind("<<TreeviewSelect>>", self.on_product_select)

        # Billing area section
        billing_frame = tk.LabelFrame(right_frame, text="Billing Area", font=("Arial", 12, "bold"), bg="#f4f6f8", padx=10, pady=10)
        billing_frame.pack(fill=tk.X)

        self.bill_number_label = tk.Label(billing_frame, text="Bill No: BILL001", font=("Arial", 11, "bold"), bg="#f4f6f8")
        self.bill_number_label.grid(row=0, column=0, columnspan=2, sticky="w", pady=5)

        tk.Label(billing_frame, text="Selected Product", bg="#f4f6f8").grid(row=1, column=0, sticky="w", pady=5)
        self.selected_product_label = tk.Label(billing_frame, text="None", bg="#f4f6f8", font=("Arial", 10, "bold"))
        self.selected_product_label.grid(row=1, column=1, sticky="w", pady=5)

        tk.Label(billing_frame, text="Quantity", bg="#f4f6f8").grid(row=2, column=0, sticky="w", pady=5)
        self.bill_quantity_entry = tk.Entry(billing_frame, width=20)
        self.bill_quantity_entry.grid(row=2, column=1, sticky="w", pady=5)

        tk.Button(billing_frame, text="Add Item to Bill", bg="#8e44ad", fg="white", width=18, command=self.add_item_to_bill).grid(row=3, column=0, pady=10)
        tk.Button(billing_frame, text="Clear Bill", width=18, command=self.clear_bill).grid(row=3, column=1, pady=10, sticky="w")

        # Bill display / receipt section
        receipt_frame = tk.LabelFrame(right_frame, text="Bill Display", font=("Arial", 12, "bold"), bg="#f4f6f8", padx=10, pady=10)
        receipt_frame.pack(fill=tk.BOTH, expand=True, pady=10)

        self.receipt_text = tk.Text(receipt_frame, height=22, width=55, font=("Courier New", 10))
        self.receipt_text.pack(fill=tk.BOTH, expand=True)

        tk.Button(right_frame, text="Complete Transaction", font=("Arial", 12, "bold"), bg="#16a085", fg="white", command=self.complete_transaction).pack(fill=tk.X, pady=5)
        tk.Button(right_frame, text="Print Bill", font=("Arial", 12, "bold"), bg="#34495e", fg="white", command=self.print_bill).pack(fill=tk.X, pady=5)

        self.total_label = tk.Label(right_frame, text="Total Amount: LKR 0.00", font=("Arial", 14, "bold"), bg="#f4f6f8", fg="#2c3e50")
        self.total_label.pack(anchor="e", pady=5)

        self.refresh_receipt()

    # =====================================================
    # PRODUCT MANAGEMENT FUNCTIONS
    # =====================================================
    def add_product(self):
        name = self.name_entry.get().strip()
        price_text = self.price_entry.get().strip()
        quantity_text = self.quantity_entry.get().strip()

        if not name or not price_text or not quantity_text:
            messagebox.showerror("Input Error", "Please fill all product fields.")
            return

        try:
            price = float(price_text)
            quantity = int(quantity_text)
            if price <= 0 or quantity < 0:
                raise ValueError
        except ValueError:
            messagebox.showerror("Input Error", "Price must be greater than 0 and quantity must be 0 or more.")
            return

        try:
            conn = sqlite3.connect(DB_NAME)
            cursor = conn.cursor()
            cursor.execute("INSERT INTO products (name, price, quantity) VALUES (?, ?, ?)", (name, price, quantity))
            conn.commit()
            conn.close()
            messagebox.showinfo("Success", "Product added successfully.")
            self.clear_product_fields()
            self.load_products()
        except sqlite3.IntegrityError:
            messagebox.showerror("Database Error", "Product name already exists.")

    def update_product(self):
        if self.selected_product_id is None:
            messagebox.showerror("Selection Error", "Please select a product to update.")
            return

        name = self.name_entry.get().strip()
        price_text = self.price_entry.get().strip()
        quantity_text = self.quantity_entry.get().strip()

        if not name or not price_text or not quantity_text:
            messagebox.showerror("Input Error", "Please fill all product fields.")
            return

        try:
            price = float(price_text)
            quantity = int(quantity_text)
            if price <= 0 or quantity < 0:
                raise ValueError
        except ValueError:
            messagebox.showerror("Input Error", "Invalid price or quantity.")
            return

        try:
            conn = sqlite3.connect(DB_NAME)
            cursor = conn.cursor()
            cursor.execute("UPDATE products SET name=?, price=?, quantity=? WHERE id=?", (name, price, quantity, self.selected_product_id))
            conn.commit()
            conn.close()
            messagebox.showinfo("Success", "Product updated successfully.")
            self.clear_product_fields()
            self.load_products()
        except sqlite3.IntegrityError:
            messagebox.showerror("Database Error", "Another product already uses this name.")

    def delete_product(self):
        if self.selected_product_id is None:
            messagebox.showerror("Selection Error", "Please select a product to delete.")
            return

        if not messagebox.askyesno("Confirm Delete", "Are you sure you want to delete this product?"):
            return

        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        cursor.execute("DELETE FROM products WHERE id=?", (self.selected_product_id,))
        conn.commit()
        conn.close()

        messagebox.showinfo("Deleted", "Product deleted successfully.")
        self.clear_product_fields()
        self.load_products()

    def load_products(self):
        """Load all products into the Treeview table."""
        for row in self.product_tree.get_children():
            self.product_tree.delete(row)

        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        cursor.execute("SELECT id, name, price, quantity FROM products ORDER BY id DESC")
        products = cursor.fetchall()
        conn.close()

        for product in products:
            product_id, name, price, quantity = product
            if quantity == 0:
                status = "Out of Stock"
            elif quantity <= LOW_STOCK_LIMIT:
                status = "Low Stock"
            else:
                status = "Available"
            self.product_tree.insert("", tk.END, values=(product_id, name, format_lkr(price), quantity, status))

    def on_product_select(self, event):
        """Fill product fields when a product row is selected."""
        selected = self.product_tree.focus()
        if not selected:
            return

        values = self.product_tree.item(selected, "values")
        self.selected_product_id = int(values[0])
        self.name_entry.delete(0, tk.END)
        self.name_entry.insert(0, values[1])
        self.price_entry.delete(0, tk.END)
        self.price_entry.insert(0, values[2].replace("LKR", "").replace(",", "").strip())
        self.quantity_entry.delete(0, tk.END)
        self.quantity_entry.insert(0, values[3])
        self.selected_product_label.config(text=values[1])

    def clear_product_fields(self):
        self.selected_product_id = None
        self.name_entry.delete(0, tk.END)
        self.price_entry.delete(0, tk.END)
        self.quantity_entry.delete(0, tk.END)
        self.selected_product_label.config(text="None")

    # =====================================================
    # BILLING FUNCTIONS
    # =====================================================
    def get_next_bill_number(self):
        """Generate BILL001, BILL002, etc. using the highest existing bill id."""
        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        cursor.execute("SELECT MAX(id) FROM bills")
        last_id = cursor.fetchone()[0]
        conn.close()
        next_id = 1 if last_id is None else last_id + 1
        return f"BILL{next_id:03d}"

    def update_bill_number_label(self):
        self.bill_number_label.config(text=f"Bill No: {self.get_next_bill_number()}")

    def add_item_to_bill(self):
        """Add the selected product and quantity to the current bill list."""
        if self.selected_product_id is None:
            messagebox.showerror("Selection Error", "Please select a product from the product list.")
            return

        try:
            bill_quantity = int(self.bill_quantity_entry.get().strip())
            if bill_quantity <= 0:
                raise ValueError
        except ValueError:
            messagebox.showerror("Input Error", "Please enter a valid bill quantity.")
            return

        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        cursor.execute("SELECT name, price, quantity FROM products WHERE id=?", (self.selected_product_id,))
        product = cursor.fetchone()
        conn.close()

        if product is None:
            messagebox.showerror("Error", "Selected product not found.")
            return

        name, price, stock_quantity = product
        already_in_bill = sum(item["quantity"] for item in self.bill_items if item["product_id"] == self.selected_product_id)

        if bill_quantity + already_in_bill > stock_quantity:
            messagebox.showwarning("Stock Warning", f"Only {stock_quantity} unit(s) available in stock.")
            return

        item_total = price * bill_quantity
        self.bill_items.append({
            "product_id": self.selected_product_id,
            "name": name,
            "quantity": bill_quantity,
            "price": price,
            "total": item_total
        })

        if stock_quantity <= LOW_STOCK_LIMIT:
            messagebox.showwarning("Low Stock", f"Low stock warning for {name}. Only {stock_quantity} unit(s) left.")

        self.bill_quantity_entry.delete(0, tk.END)
        self.refresh_receipt()

    def refresh_receipt(self):
        """Display the current bill receipt in the bill display area."""
        self.receipt_text.delete("1.0", tk.END)
        bill_number = self.get_next_bill_number()
        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        total_amount = sum(item["total"] for item in self.bill_items)

        receipt = ""
        receipt += f"{SHOP_NAME:^54}
"
        receipt += f"{SHOP_ADDRESS:^54}
"
        receipt += f"{SHOP_CONTACT:^54}
"
        receipt += "=" * 54 + "
"
        receipt += f"Date & Time : {now}
"
        receipt += f"Bill Number : {bill_number}
"
        receipt += "-" * 54 + "
"
        receipt += f"{'Product':<20}{'Qty':>5}{'Price':>13}{'Total':>14}
"
        receipt += "-" * 54 + "
"

        for item in self.bill_items:
            receipt += f"{item['name'][:20]:<20}{item['quantity']:>5}{item['price']:>13,.2f}{item['total']:>14,.2f}
"

        receipt += "-" * 54 + "
"
        receipt += f"{'Grand Total:':<35}{format_lkr(total_amount):>19}
"
        receipt += "=" * 54 + "
"
        receipt += f"{'Thank You! Visit Again!':^54}
"

        self.current_receipt = receipt
        self.receipt_text.insert(tk.END, receipt)
        self.total_label.config(text=f"Total Amount: {format_lkr(total_amount)}")

    def print_bill(self):
        """Print the currently displayed bill receipt."""
        if not self.bill_items:
            messagebox.showerror("Print Error", "Please add at least one item before printing the bill.")
            return

        try:
            # Save receipt to a temporary text file and send it to the default printer.
            temp_file = tempfile.NamedTemporaryFile(delete=False, suffix=".txt", mode="w", encoding="utf-8")
            temp_file.write(self.current_receipt)
            temp_file.close()

            if os.name == "nt":
                os.startfile(temp_file.name, "print")
                messagebox.showinfo("Print", "Bill sent to printer.")
            else:
                messagebox.showinfo("Print File Created", f"Bill saved for printing:
{temp_file.name}")
        except Exception as error:
            messagebox.showerror("Print Error", f"Could not print the bill: {error}")

    def complete_transaction(self):
        """Save bill, save bill items/sales, reduce stock, and clear current bill."""
        if not self.bill_items:
            messagebox.showerror("Bill Error", "No items added to the bill.")
            return

        bill_number = self.get_next_bill_number()
        bill_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        total_amount = sum(item["total"] for item in self.bill_items)

        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()

        try:
            # Save main bill record
            cursor.execute("INSERT INTO bills (bill_number, total_amount, date) VALUES (?, ?, ?)", (bill_number, total_amount, bill_date))
            bill_id = cursor.lastrowid

            # Save each bill item, save a sales row, and reduce stock
            for item in self.bill_items:
                cursor.execute(
                    "INSERT INTO bill_items (bill_id, product_id, quantity, subtotal) VALUES (?, ?, ?, ?)",
                    (bill_id, item["product_id"], item["quantity"], item["total"])
                )
                cursor.execute(
                    "INSERT INTO sales (bill_number, product_id, product_name, quantity, unit_price, subtotal, date) VALUES (?, ?, ?, ?, ?, ?, ?)",
                    (bill_number, item["product_id"], item["name"], item["quantity"], item["price"], item["total"], bill_date)
                )
                cursor.execute("UPDATE products SET quantity = quantity - ? WHERE id=?", (item["quantity"], item["product_id"]))

            conn.commit()
            messagebox.showinfo("Success", f"Transaction completed.
Bill Number: {bill_number}
Total: {format_lkr(total_amount)}")
            self.clear_bill()
            self.clear_product_fields()
            self.load_products()
            self.update_bill_number_label()
        except sqlite3.Error as error:
            conn.rollback()
            messagebox.showerror("Database Error", f"Transaction failed: {error}")
        finally:
            conn.close()

    def clear_bill(self):
        """Clear all items from the current bill."""
        self.bill_items = []
        self.bill_quantity_entry.delete(0, tk.END)
        self.refresh_receipt()


# Run the application
if __name__ == "__main__":
    root = tk.Tk()
    app = InventoryBillingApp(root)
    root.mainloop()