File size: 6,954 Bytes
b0fcc20
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4484bda
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
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()