app.py

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()