Reports-Visual/main.py

import streamlit as st
import pandas as pd
import mysql.connector
from datetime import datetime
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Set Page Name
st.set_page_config(page_title="Reporting")


# Load custom CSS
def load_css(file_name):
    with open(file_name) as f:
        st.markdown(f'<style>{f.read()}</style>', unsafe_allow_html=True)

load_css('style.css')

# Function to get filtered data from the database
def get_filtered_data(customer_id, service_id, start_date, end_date):
    mydb = mysql.connector.connect(
        host=os.getenv("MYSQL_HOST"),
        user=os.getenv("MYSQL_USER"),
        password=os.getenv("MYSQL_PASSWORD"),
        database=os.getenv("MYSQL_DATABASE")
    )

    # Prepare the query
    query = f"""
        SELECT DATE_FORMAT(sr.reportingdate, '%Y-%m') AS month, 
               COUNT(DISTINCT sr.username) as count
        FROM Kunden.`services.reporting` sr
        JOIN Kunden.services s ON sr.service_ID = s.service_ID
        WHERE sr.customer_ID = {customer_id}
          AND sr.service_ID = {service_id} 
          AND sr.username NOT LIKE '%admin%' 
          AND sr.username NOT LIKE '%test%' 
          AND sr.reportingdate BETWEEN '{start_date}' AND '{end_date}'
        GROUP BY DATE_FORMAT(sr.reportingdate, '%Y-%m')
        ORDER BY DATE_FORMAT(sr.reportingdate, '%Y-%m');
    """

    service_reporting = pd.read_sql_query(query, mydb)
    mydb.close()
    return service_reporting


# Fetch initial data for default selections
def get_initial_data():
    mydb = mysql.connector.connect(
        host=os.getenv("MYSQL_HOST"),
        user=os.getenv("MYSQL_USER"),
        password=os.getenv("MYSQL_PASSWORD"),
        database=os.getenv("MYSQL_DATABASE")
    )
    # Fetch unique service IDs and names
    service_id_query = """
        SELECT DISTINCT s.service_ID, s.name
        FROM Kunden.`services.reporting` sr
        JOIN Kunden.services s ON sr.service_ID = s.service_ID
    """
    service_ids = pd.read_sql_query(service_id_query, mydb)

    # Fetch customer information
    customer_query = """
        SELECT c.customer_ID, c.customer, co.companyname
        FROM Kunden.company co
        JOIN Kunden.customers c ON co.customer_ID = c.customer_ID
        JOIN Kunden.`services.reporting`sr ON sr.customer_ID = co.customer_ID
        GROUP BY c.customer_ID, c.customer, co.companyname;
    """
    customers = pd.read_sql_query(customer_query, mydb)

    # Fetch date range
    date_query = """
        SELECT MIN(reportingdate) AS min_date, MAX(reportingdate) AS max_date 
        FROM Kunden.`services.reporting`
        WHERE customer_ID = 5
    """
    date_range = pd.read_sql_query(date_query, mydb)
    mydb.close()

    return service_ids, customers, date_range


# Define page functions
def home():
    st.title("Home Page")
    st.write("Welcome to the Home Page!")


def services_reporting():
    st.title("Reporting :mag_right:")
    # Get initial data for widgets
    initial_service_ids, customers, initial_date_range = get_initial_data()
    # Combine service_ID and name for display
    service_options = initial_service_ids.apply(lambda row: f"{row['service_ID']} - {row['name']}", axis=1)

    # Add selection widget for customer ID
    selected_customer = st.selectbox(
        'Select Customer',
        customers.apply(lambda row: f"{row['customer_ID']} - {row['companyname']} - {row['customer']}", axis=1).tolist()
    )

    # Extract customer_ID from selected option
    selected_customer_id = int(selected_customer.split(' - ')[0])

    # Add selection widget for service ID
    selected_service = st.selectbox(
        'Select Service',
        service_options.tolist()
    )

    # Extract service_ID from selected option
    selected_service_id = int(selected_service.split(' - ')[0])

    # Convert date range to datetime objects
    min_date = initial_date_range['min_date'][0]
    max_date = initial_date_range['max_date'][0]

    # Add date range selection widget
    selected_date_range = st.date_input(
        'Select date range',
        value=[min_date, max_date],
        min_value=min_date,
        max_value=max_date
    )

    # Format the selected dates as 'YYYY-MM-DD'
    start_date_str = selected_date_range[0].strftime("%Y-%m-%d")
    end_date_str = selected_date_range[1].strftime("%Y-%m-%d")

    # Add a button to apply filters
    if st.button('Apply Filters'):
        # Fetch filtered data from the database
        filtered_data = get_filtered_data(selected_customer_id, selected_service_id, start_date_str, end_date_str)

        # Sort the data by month
        filtered_data = filtered_data.sort_values('month')

        # Create a bar chart with the filtered data
        if not filtered_data.empty:
            st.bar_chart(filtered_data.set_index('month')['count'])
        else:
            st.write("No data available for the selected filters.")




if 'page' not in st.session_state:
    st.session_state.page = 'Home'

# Sidebar navigation
st.sidebar.title("Navigation")
if st.sidebar.button('Home'):
    st.session_state.page = 'Home'
if st.sidebar.button('Services Reporting'):
    st.session_state.page = 'Services Reporting'

# Page display logic
if st.session_state.page == 'Home':
    home()
elif st.session_state.page == 'Services Reporting':
    services_reporting()