import sqlite3 import streamlit as st # Create an in-memory SQLite database conn = sqlite3.connect(':memory:') cursor = conn.cursor() # Create table for products cursor.execute(''' CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT, quantity INTEGER NOT NULL, price REAL NOT NULL ); ''') # Create table for sales transactions cursor.execute(''' CREATE TABLE IF NOT EXISTS sales ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER, quantity INTEGER, total_price REAL, sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(id) ); ''') # Commit changes conn.commit() # Function to add a new product def add_product(name, description, quantity, price): cursor.execute(''' INSERT INTO products (name, description, quantity, price) VALUES (?, ?, ?, ?) ''', (name, description, quantity, price)) conn.commit() st.success(f"Product '{name}' added successfully.") # Function to search a product by ID def search_product_by_id(product_id): cursor.execute('SELECT * FROM products WHERE id = ?', (product_id,)) product = cursor.fetchone() return product # Function to search a product by name def search_product_by_name(name): cursor.execute('SELECT * FROM products WHERE name LIKE ?', ('%' + name + '%',)) product = cursor.fetchone() return product # Function to update the stock quantity of a product def update_stock(product_id, quantity): cursor.execute('SELECT quantity FROM products WHERE id = ?', (product_id,)) current_quantity = cursor.fetchone() if current_quantity: new_quantity = current_quantity[0] + quantity cursor.execute('UPDATE products SET quantity = ? WHERE id = ?', (new_quantity, product_id)) conn.commit() st.success(f"Stock updated. New quantity: {new_quantity}") else: st.error("Product not found for updating stock.") # Function to record a sale def record_sale(product_id, quantity): cursor.execute('SELECT quantity, price FROM products WHERE id = ?', (product_id,)) product = cursor.fetchone() if product and product[0] >= quantity: total_price = product[1] * quantity cursor.execute(''' INSERT INTO sales (product_id, quantity, total_price) VALUES (?, ?, ?) ''', (product_id, quantity, total_price)) cursor.execute('UPDATE products SET quantity = quantity - ? WHERE id = ?', (quantity, product_id)) conn.commit() st.success(f"Sale recorded: {quantity} unit(s) sold for a total of ${total_price:.2f}.") else: st.error("Not enough stock to complete the sale.") # Function to view all available items def view_all_items(): cursor.execute('SELECT id, name, description, quantity, price FROM products') products = cursor.fetchall() if products: st.write("### All Available Items:") for product in products: st.write(f"ID: {product[0]}, Name: {product[1]}, Description: {product[2]}, Quantity: {product[3]}, Price: ${product[4]:.2f}") else: st.write("No products available.") # Function to generate a sales report def sales_report(): cursor.execute(''' SELECT p.name, s.quantity, s.total_price, s.sale_date FROM sales s JOIN products p ON s.product_id = p.id ORDER BY s.sale_date DESC ''') sales = cursor.fetchall() st.write("### Sales Report:") if sales: for sale in sales: st.write(f"Product: {sale[0]}, Quantity: {sale[1]}, Total: ${sale[2]:.2f}, Date: {sale[3]}") else: st.write("No sales recorded.") # Streamlit UI for the app def main(): st.title("Inventory Management System") # Sidebar for navigation menu = ["Home", "View Products", "Add Product", "Update Stock", "Record Sale", "Sales Report"] choice = st.sidebar.selectbox("Select an option", menu) if choice == "Home": st.write("Welcome to the Inventory Management System! Use the sidebar to navigate.") elif choice == "View Products": view_all_items() elif choice == "Add Product": st.write("### Add a New Product") name = st.text_input("Product Name") description = st.text_input("Product Description") try: quantity = st.number_input("Quantity", min_value=0) price = st.number_input("Price", min_value=0.0) except ValueError: quantity, price = 0, 0.0 if st.button("Add Product"): if name and description and quantity > 0 and price > 0: add_product(name, description, quantity, price) else: st.error("Please fill all fields with valid data.") elif choice == "Update Stock": st.write("### Update Stock") search_method = st.radio("Search by", ("ID", "Name")) if search_method == "ID": product_id = st.number_input("Enter Product ID", min_value=1) quantity = st.number_input("Enter Quantity to Add", min_value=0) if st.button("Update Stock"): product = search_product_by_id(product_id) if product: update_stock(product_id, quantity) else: st.error("Product not found.") elif search_method == "Name": product_name = st.text_input("Enter Product Name") quantity = st.number_input("Enter Quantity to Add", min_value=0) if st.button("Update Stock"): product = search_product_by_name(product_name) if product: update_stock(product[0], quantity) else: st.error("Product not found.") elif choice == "Record Sale": st.write("### Record a Sale") search_method = st.radio("Search by", ("ID", "Name")) if search_method == "ID": product_id = st.number_input("Enter Product ID", min_value=1) quantity = st.number_input("Enter Quantity to Sell", min_value=1) if st.button("Record Sale"): product = search_product_by_id(product_id) if product: record_sale(product_id, quantity) else: st.error("Product not found.") elif search_method == "Name": product_name = st.text_input("Enter Product Name") quantity = st.number_input("Enter Quantity to Sell", min_value=1) if st.button("Record Sale"): product = search_product_by_name(product_name) if product: record_sale(product[0], quantity) else: st.error("Product not found.") elif choice == "Sales Report": sales_report() if __name__ == "__main__": main()