import streamlit as st import pandas as pd import sqlite3 import matplotlib.pyplot as plt from dataBaseSetup import create_connection # Funciones de conexión a la base de datos def create_connection(): conn = sqlite3.connect('bike_store.db') return conn # Funciones de consulta def get_stocks_by_category_store(category_name, store_name): conn = create_connection() sql = ''' SELECT categories.category_name, stores.store_name, SUM(stocks.quantity) as total_stock FROM stocks JOIN products ON stocks.product_id = products.product_id JOIN categories ON products.category_id = categories.category_id JOIN stores ON stocks.store_id = stores.store_id WHERE categories.category_name = ? AND stores.store_name = ? GROUP BY categories.category_name, stores.store_name; ''' df = pd.read_sql_query(sql, conn, params=(category_name, store_name)) conn.close() return df def get_order_items_by_category_store(category_name, store_name): conn = create_connection() sql = ''' SELECT c.category_name, s.store_name, COUNT(oi.item_id) as total_items FROM order_items oi JOIN orders o ON oi.order_id = o.order_id JOIN products p ON oi.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id JOIN stores s ON o.store_id = s.store_id WHERE c.category_name = ? AND s.store_name = ? GROUP BY c.category_name, s.store_name; ''' df = pd.read_sql_query(sql, conn, params=(category_name, store_name)) conn.close() return df def get_total_sales_by_store_year_month(store_name, year_month): conn = create_connection() sql = ''' SELECT strftime('%Y-%m', o.order_date) as year_month, SUM(oi.quantity * oi.list_price) as total_sales FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE strftime('%Y-%m', o.order_date) = ? AND o.store_id IN ( SELECT store_id FROM stores WHERE store_name = ? ) GROUP BY year_month; ''' df = pd.read_sql_query(sql, conn, params=(year_month, store_name)) conn.close() return df def get_staff_order_counts(desc=True): conn = create_connection() sql = ''' SELECT s.staff_id, s.first_name || ' ' || s.last_name AS staff_name, COUNT(o.order_id) as order_count FROM orders o JOIN staffs s ON o.staff_id = s.staff_id GROUP BY s.staff_id ORDER BY order_count {} LIMIT 1; '''.format('DESC' if desc else 'ASC') df = pd.read_sql_query(sql, conn) conn.close() return df # STREAMLIT def app(): st.title("Bike Store Management System") # Opciones de consulta en la barra lateral query_options = [ "Query 1: Get Stocks", "Query 2: Get Order Items", "Query 3: Total Sales in Santa Cruz Bikes", "Query 4: Total Sales in Baldwin Bikes", "Query 5: Total Sales in Rowlett Bikes", "Query 6: Staff with the Highest Number of Orders", "Query 7: Staff with the Lowest Number of Orders" ] selected_query = st.sidebar.radio("Seleccione una consulta para ejecutar", query_options) # Mostrar inputs y ejecutar la consulta basada en la selección if selected_query == "Query 1: Get Stocks": st.write("### Query 1: Stocks by Category and Store") category_name_1 = st.text_input("Category Name for Stocks", key='1') store_name_1 = st.text_input("Store Name for Stocks", key='2') if st.button("Execute Query 1", key='3'): df = get_stocks_by_category_store(category_name_1, store_name_1) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='barh', x='category_name', y='total_stock', ax=ax) st.pyplot(fig) elif selected_query == "Query 2: Get Order Items": st.write("### Query 2: Order Items by Category and Store") category_name_2 = st.text_input("Category Name for Order Items", key='4') store_name_2 = st.text_input("Store Name for Order Items", key='5') if st.button("Execute Query 2", key='6'): df = get_order_items_by_category_store(category_name_2, store_name_2) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='bah', x='category_name', y='total_items', ax=ax) st.pyplot(fig) elif selected_query == "Query 3: Total Sales in Santa Cruz Bikes": st.write("### Query 3: Total Sales in Santa Cruz Bikes") year_month_3 = st.text_input("Year-Month (YYYY-MM) for Santa Cruz Bikes", key='7') if st.button("Execute Query 3", key='8'): df = get_total_sales_by_store_year_month("Santa Cruz Bikes", year_month_3) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='barh', x='year_month', y='total_sales', ax=ax) st.pyplot(fig) elif selected_query == "Query 4: Total Sales in Baldwin Bikes": st.write("### Query 4: Total Sales in Baldwin Bikes") year_month_4 = st.text_input("Year-Month (YYYY-MM) for Baldwin Bikes", key='9') if st.button("Execute Query 4", key='10'): df = get_total_sales_by_store_year_month("Baldwin Bikes", year_month_4) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='barh', x='year_month', y='total_sales', ax=ax) st.pyplot(fig) elif selected_query == "Query 5: Total Sales in Rowlett Bikes": st.write("### Query 5: Total Sales in Rowlett Bikes") year_month_5 = st.text_input("Year-Month (YYYY-MM) for Rowlett Bikes", key='11') if st.button("Execute Query 5", key='12'): df = get_total_sales_by_store_year_month("Rowlett Bikes", year_month_5) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='barh', x='year_month', y='total_sales', ax=ax) st.pyplot(fig) elif selected_query == "Query 6: Staff with the Highest Number of Orders": st.write("### Query 6: Staff with the Highest Number of Orders") if st.button("Execute Query 6", key='13'): df = get_staff_order_counts(desc=True) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='barh', x='staff_name', y='order_count', ax=ax) st.pyplot(fig) elif selected_query == "Query 7: Staff with the Lowest Number of Orders": st.write("### Query 7: Staff with the Lowest Number of Orders") if st.button("Execute Query 7", key='14'): df = get_staff_order_counts(desc=False) st.write(df) if not df.empty: fig, ax = plt.subplots() df.plot(kind='barh', x='staff_name', y='order_count', ax=ax) st.pyplot(fig) if __name__ == "__main__": app()