pages/02-admin.py

# pages/admin.py

import os
import sys
import solara
import solara
import dataclasses
from typing import Any, Dict, Optional, cast

import random
import solara
import solara.lab
import pandas as pd

import json
import pandas as pd
from psycopg2 import pool


# Get the project root directory
project_root = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
module_path = os.path.join(project_root, 'public')
# Add the module path to the Python path
sys.path.append(module_path)
key_content = os.environ.get('DATABASE_SECRETS')
key_dict = json.loads(key_content)
connection_pool = pool.SimpleConnectionPool(1, 10, **key_dict)

import crud 
import  status 
from dataviz import DataVisualizer



crud_manager = crud.CRUDManager(connection_pool) 
# Instantiate the ClientManager
client_manager = crud.ClientManager(connection_pool)

overview_data = status.general_overview(crud_manager)
project_data = status.project_insights(crud_manager)
budget_data = status.budget_expenses_insights(crud_manager)
transactions_data = status.transactions_insights(crud_manager)
team_data = status.team_overview(crud_manager)

# Reactive states for the filter
selected_filter = solara.reactive("Choose a filter")
filter_value = solara.reactive("")



@solara.component
def Page():

    
    #### Data ####
    data_df =client_manager.fetch_data_as_dataframe('clientes_test')
    data, set_data = solara.use_state(data_df)

    proyectos_df =client_manager.fetch_data_as_dataframe('proyectos_test')
    proyectos_data, set_proyectos_data = solara.use_state(proyectos_df)

    presupuesto_df =client_manager.fetch_data_as_dataframe('presupuesto_test')
    presupuesto_data, set_presupuesto_data = solara.use_state(presupuesto_df)
    
    transacciones_df =client_manager.fetch_data_as_dataframe('transacciones_test')
    transacciones_data, set_transacciones_data = solara.use_state(transacciones_df)

    gastos_df =client_manager.fetch_data_as_dataframe('gastos_test')
    gastos_data, set_gastos_data = solara.use_state(gastos_df)

    flujo_df =client_manager.fetch_data_as_dataframe('flujo_test')
    flujo_data, set_flujo_data = solara.use_state(flujo_df)
    

    equipos_df =client_manager.fetch_data_as_dataframe('equipos_test')
    equipos_data, set_equipos_data = solara.use_state(equipos_df)     


    documentos_df =client_manager.fetch_data_as_dataframe('documentos_test')
    documentos_data, set_documentos_data = solara.use_state(documentos_df)        

    # List of available filters
    filters = ['cod_cliente', 'doc_identidad','nombre', 'apellido', 'departamento', 'distrito', 'cta_transaccion', 'feching', 'fechact']
    filtered_data, set_filtered_data = solara.use_state(data)

    # Reactive state for filters
    selected_filter, set_selected_filter = solara.use_state(filters[0])  # Default to the first filter
    filter_value, set_filter_value = solara.use_state("")
    	
   

    def apply_dropdown_filter():
        # Start with the original data
        filtered_data_temp = data
        # Remove whitespace and convert to lowercase
        cleaned_filter_value = filter_value.strip().lower()

        # Filter data based on the selected filter and filter value
        if cleaned_filter_value:
            # Convert column values to string and lowercase for comparison
            filtered_data_temp = filtered_data_temp[filtered_data_temp[selected_filter].str.lower() == cleaned_filter_value]
            filtered_data_temp = filtered_data_temp.reset_index(drop=True)

        # Update the filtered_data reactive state with the filtered data
        set_filtered_data(filtered_data_temp)


    def reset_filter():
        set_filtered_data(data)
        set_filter_value("")


    
    def on_action_cell(column, row_index):
        # Get the customer ID  from the selected row
        customer_id = filtered_data.iloc[row_index]['cod_cliente']

        # Filter the projects data based on the selected customer
        filtered_projects = proyectos_df[proyectos_df['cod_cliente'] == customer_id]
        
        # Update the reactive state for the projects data
        set_proyectos_data(filtered_projects)


    def on_project_selected(column, row_index):
        # Get the project ID from the selected row
        project_id = proyectos_data.iloc[row_index]['cod_proyecto'] 

        # Filter each dataframe based on the selected project
        filtered_presupuesto = presupuesto_data[presupuesto_data['cod_proyecto'] == project_id]
        filtered_transacciones = transacciones_data[transacciones_data['cod_proyecto'] == project_id]
        filtered_gastos = gastos_data[gastos_data['cod_proyecto'] == project_id]
        # Filter the flujo dataframe based on the selected project
        filtered_flujo = flujo_data[flujo_data['id_proyecto'] == project_id]

        # Extract unique id_usuario values from the filtered flujo dataframe
        user_ids = filtered_flujo['id_usuario'].unique()

        # Filter the equipos dataframe based on the extracted user_ids
        filtered_equipos = equipos_data[equipos_data['cod_usuario'].isin(user_ids)]

        # Filter the documentos dataframe based on the selected project
        filtered_documentos = documentos_data[documentos_data['id_proyecto'] == project_id]

        # Update the reactive states for each dataframe
        set_presupuesto_data(filtered_presupuesto)
        set_transacciones_data(filtered_transacciones)
        set_gastos_data(filtered_gastos)
        set_flujo_data(filtered_flujo)
        set_equipos_data(filtered_equipos)
        set_documentos_data(filtered_documentos)

    
    

    with solara.Column():
        cell_actions = [solara.CellAction(icon="mdi-file-document-box-search", name="Show Details", on_click=on_action_cell)]  
        project_cell_actions = [solara.CellAction(icon="mdi-file-document-box-search", name="Show Project Details", on_click=on_project_selected)]

        with solara.Card('Status Report',elevation=10, margin= 10):
            # Using the general overview visualization method
            DataVisualizer.visualize_general_overview(overview_data)
            DataVisualizer.visualize_budget_expenses_insights(budget_data)
            DataVisualizer.visualize_project_insights(project_data)
            with solara.Row():
                DataVisualizer.visualize_transactions_insights(transactions_data)
                DataVisualizer.visualize_team_overviews(team_data)
      
    # Place buttons in a horizontal row below the map
    with solara.Card('Clientes',elevation=10, margin= 10):
        
        with solara.Row(justify='center'):
            solara.Button("NUEVO",icon_name= 'mdi-plus-box-multiple',outlined=True,
                           style = {'padding': '5px 10px','border-radius': '15px','cursor': 'pointer',
                                    'transition': 'background-color 0.3s ease'})
            solara.Button("ACTUALIZAR", icon_name= 'mdi-account-edit',outlined=True,
                           style = {'padding': '5px 10px','border-radius': '15px','cursor': 'pointer',
                                    'transition': 'background-color 0.3s ease'})
        # Future
        '''def on_submit_button_click():
            client_manager.handle_submit(inputs)
            # Refresh the data
            set_data(client_manager.fetch_all('clientes_test'))
            client_manager.handle_update(inputs, selected_row)
            client_manager.handle_delete(selected_row)
            client_manager.handle_row_selection(row_index)'''
        


        # Dropdown Filter UI
        with solara.Row():
            solara.Select(label="Select Filter", value=selected_filter, values=filters, on_value=set_selected_filter)
            solara.InputText(label="Filter Value", value=filter_value, on_value=set_filter_value)
            solara.Button("Apply Filter", on_click=apply_dropdown_filter, icon_name='mdi-filter-menu', outlined=True)
            solara.Button("Reset", on_click=reset_filter, icon_name='mdi-refresh-circle', outlined = True)

        # DataFrame with filters applied
        solara.CrossFilterDataFrame(filtered_data, items_per_page=10, scrollable= True, cell_actions=cell_actions)
        # Place buttons in a horizontal row below the map
    with solara.Card('PROYECTOS',elevation=10, margin= 10):
        solara.CrossFilterDataFrame(proyectos_data, items_per_page=10, scrollable=True, cell_actions=project_cell_actions)
    

    with solara.Card('DETALLES'):
        with solara.Row():
            with solara.lab.Tabs():
                        with solara.lab.Tab('Datos Completos'):
                            with solara.Card('Presupuesto',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(presupuesto_data, items_per_page=10, scrollable= True)

                            with solara.Card('Transacciones',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(transacciones_data, items_per_page=10, scrollable= True)

                            with solara.Card('Gastos',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(gastos_data, items_per_page=10, scrollable= True)

                            with solara.Card('Flujo',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(flujo_df, items_per_page=10, scrollable= True)

                            with solara.Card('Equipos',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(equipos_data, items_per_page=10, scrollable= True)

                            with solara.Card('Documentos',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(documentos_data, items_per_page=10, scrollable= True)

                        with solara.lab.Tab('Presupuesto'):
                            with solara.Card('Presupuesto',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(presupuesto_data, items_per_page=10, scrollable= True)

                        with solara.lab.Tab('Transacciones'):
                            with solara.Card('Transacciones',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(transacciones_data, items_per_page=10, scrollable= True)

                        with solara.lab.Tab('Gastos'):
                            with solara.Card('Gastos',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(gastos_data, items_per_page=10, scrollable= True)
                            with solara.Card('Equipos',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(equipos_data, items_per_page=10, scrollable= True)

                        with solara.lab.Tab('Flujo+Equipos'):
                            with solara.Card('Flujo',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(flujo_data, items_per_page=10, scrollable= True)

                        with solara.lab.Tab('Documentos'):
                            with solara.Card('Documentos',elevation=10, margin= 10):
                                solara.CrossFilterDataFrame(documentos_data, items_per_page=10, scrollable= True)



    #  Banner image at the top
    with solara.Row():
        image_url = "https://huggingface.co/spaces/romero61/hendata/resolve/main/public/logo.png"
        solara.Image(image_url, width="100%")