ims / app.py
Student3331's picture
Update app.py
4484bda verified
Raw History Blame Contribute Delete
6.95 kB
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()