24 de ago. de 2024 11 min read

Optimización de la Gestión de Turnos: De una hoja Excel a un Sistema de Base de Datos SQL con Python

Transforma la gestión de turnos desde una simple hoja de Excel a un robusto sistema SQL utilizando Python. Descubre cómo automatizar y optimizar la administración de turnos para una mayor eficiencia y precisión en tu organización.

En la administración de una plantilla de turnos de trabajo, especialmente en entornos donde se manejan grandes volúmenes de datos y personal, es crucial contar con herramientas que permitan una gestión eficiente y precisa. Tradicionalmente, muchas empresas han utilizado Excel para planificar y monitorear los turnos de los empleados. Si bien Excel es una herramienta poderosa y flexible que nos sirve de gran ayuda con sus fórmulas, cuando el volumen de datos y la complejidad de la gestión aumentan, sus limitaciones se hacen evidentes. En este post, exploraremos cómo podemos pasar de una solución basada en Excel a un sistema más robusto utilizando una base de datos y scripts en Python.

El Problema Inicial: Gestionar Turnos con Excel

Inicialmente, la plantilla de turnos se gestionaba en un archivo Excel. Este enfoque, aunque efectivo en cierto grado, presentaba varios desafíos:

Escalabilidad: A medida que el número de empleados y la cantidad de turnos crecen, la gestión de datos en Excel se vuelve más difícil y propensa a errores.
Control de cambios: Es complicado mantener un registro de los cambios en los turnos de los empleados, lo que puede llevar a confusiones y disputas.
1. En este punto al digitalizar los cambios internos, también se ahorra papel.
Automatización: La automatización de tareas como la asignación de turnos o la generación de informes es limitada en Excel.

Solución Propuesta: Migrar a una Base de Datos

Para superar estas limitaciones, proponemos una solución que involucra la migración de la gestión de turnos a un sistema basado en bases de datos y a un sistema general modelado por la programación orientada a objetos. Este enfoque ofrece varios beneficios:

Integridad de datos: Las bases de datos permiten establecer reglas para asegurar que los datos sean consistentes y estén bien organizados.
Escalabilidad: A diferencia de Excel, una base de datos puede manejar grandes volúmenes de datos sin pérdida de rendimiento.
Automatización y control: Es posible automatizar procesos, como la asignación de turnos, y llevar un registro detallado de cualquier cambio realizado en la planificación.
Consultas y reportes: Las bases de datos facilitan la creación de consultas y reportes complejos que pueden ayudar a la toma de decisiones.

Descripción del Proceso

Paso 1: Análisis de la Estructura del Excel

El primer paso fue analizar la estructura del archivo Excel. Este archivo contenía varias hojas con la programación de turnos para diferentes empleados en diferentes servicios. Identificamos las filas y columnas relevantes, como la fila que contiene las fechas y las filas donde están los nombres de los empleados. Si bien es cierto que sin afectar a las celdas que contenían la información necesaria para la extracción se 'limpió' la hoja de cálculo para facilitar la lectura del script a la hora de crear los inserts para la base de datos.

Paso 2: Diseño de la Base de Datos

Diseñamos una base de datos relacional que incluye las siguientes tablas:

empleados: donde se almacena la información básica de cada empleado.
servicios: donde se definen los diferentes servicios o departamentos.
empleados_servicios: una tabla intermedia que asigna empleados a servicios específicos.
turnos: que almacena la información de los turnos de cada empleado.
log_cambios: que registra los cambios en los turnos y mantiene un historial detallado de todas las modificaciones.

Este diseño permite un control más granular sobre la gestión de turnos y facilita la generación de reportes. A continuación se muestra el script SQL utilizado para crear esta base de datos:

-- Crear la base de datos
CREATE DATABASE gestion_turnos;

-- Usar la base de datos creada
USE gestion_turnos;

-- Crear la tabla de empleados
CREATE TABLE empleados (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL,
    puesto VARCHAR(255) DEFAULT 'Puesto Desconocido',
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Crear la tabla de servicios
CREATE TABLE servicios (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL UNIQUE,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Crear la tabla de asignación de servicios a empleados
CREATE TABLE empleados_servicios (
    id SERIAL PRIMARY KEY,
    empleado_id INT REFERENCES empleados(id) ON DELETE CASCADE,
    servicio_id INT REFERENCES servicios(id) ON DELETE CASCADE,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (empleado_id, servicio_id)
);

-- Crear la tabla de turnos
CREATE TABLE turnos (
    id SERIAL PRIMARY KEY,
    empleado_id INT REFERENCES empleados(id) ON DELETE CASCADE,
    servicio_id INT REFERENCES servicios(id) ON DELETE CASCADE,
    fecha DATE NOT NULL,
    turno VARCHAR(2) NOT NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (empleado_id, servicio_id, fecha)
);

-- Crear la tabla de log de cambios, que también manejará los cambios de turnos
CREATE TABLE log_cambios (
    id SERIAL PRIMARY KEY,
    turno_id INT REFERENCES turnos(id) ON DELETE CASCADE,
    empleado_id INT REFERENCES empleados(id) ON DELETE CASCADE,
    servicio_id INT REFERENCES servicios(id) ON DELETE CASCADE,
    accion VARCHAR(255) NOT NULL, -- ej. 'CREADO', 'ACTUALIZADO', 'ELIMINADO', 'CAMBIO'
    valor_anterior VARCHAR(255), -- Ej. turno anterior o servicio anterior
    valor_nuevo VARCHAR(255), -- Ej. turno nuevo o servicio nuevo
    cambiado_por VARCHAR(255) NOT NULL, -- Usuario que realizó el cambio
    cambiado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    turno_id_original INT REFERENCES turnos(id), -- Turno que se intercambia, si aplica
    turno_id_nuevo INT REFERENCES turnos(id), -- Turno que se recibe, si aplica
    motivo VARCHAR(255) -- Opcional, para registrar el motivo del cambio si aplica
);

-- Crear Trigger para registrar INSERT en turnos
DELIMITER //

CREATE TRIGGER log_turno_insert
AFTER INSERT ON turnos
FOR EACH ROW
BEGIN
    INSERT INTO log_cambios (turno_id, empleado_id, servicio_id, accion, valor_nuevo, cambiado_por)
    VALUES (NEW.id, NEW.empleado_id, NEW.servicio_id, 'CREADO', NEW.turno, USER());
END;
//

DELIMITER ;

-- Crear Trigger para registrar UPDATE en turnos
DELIMITER //

CREATE TRIGGER log_turno_update
AFTER UPDATE ON turnos
FOR EACH ROW
BEGIN
    IF OLD.turno != NEW.turno THEN
        INSERT INTO log_cambios (turno_id, empleado_id, servicio_id, accion, valor_anterior, valor_nuevo, cambiado_por)
        VALUES (NEW.id, NEW.empleado_id, NEW.servicio_id, 'ACTUALIZADO', OLD.turno, NEW.turno, USER());
    END IF;
END;
//

DELIMITER ;

-- Crear Trigger para registrar DELETE en turnos
DELIMITER //

CREATE TRIGGER log_turno_delete
AFTER DELETE ON turnos
FOR EACH ROW
BEGIN
    INSERT INTO log_cambios (turno_id, empleado_id, servicio_id, accion, valor_anterior, cambiado_por)
    VALUES (OLD.id, OLD.empleado_id, OLD.servicio_id, 'ELIMINADO', OLD.turno, USER());
END;
//

DELIMITER ;

-- Procedimiento para registrar cambios de turnos (intercambios entre empleados)
DELIMITER //

CREATE PROCEDURE registrar_cambio_turno(
    IN p_turno_id_original INT,
    IN p_turno_id_nuevo INT,
    IN p_empleado_id INT,
    IN p_servicio_id INT,
    IN p_motivo VARCHAR(255),
    IN p_usuario VARCHAR(255)
)
BEGIN
    -- Insertar el cambio en log_cambios
    INSERT INTO log_cambios (
        turno_id, 
        empleado_id, 
        servicio_id, 
        accion, 
        valor_anterior, 
        valor_nuevo, 
        cambiado_por, 
        turno_id_original, 
        turno_id_nuevo, 
        motivo
    ) VALUES (
        p_turno_id_nuevo, 
        p_empleado_id, 
        p_servicio_id, 
        'CAMBIO', 
        (SELECT turno FROM turnos WHERE id = p_turno_id_original), 
        (SELECT turno FROM turnos WHERE id = p_turno_id_nuevo), 
        p_usuario, 
        p_turno_id_original, 
        p_turno_id_nuevo, 
        p_motivo
    );
END;
//

DELIMITER ;

-- Insertar registros iniciales en la tabla de servicios
INSERT INTO servicios (nombre) VALUES ('Puerta Principal'), ('Plantas'), ('RX'), ('Refuerzo');

Este esquema de base de datos fue fundamental para soportar las operaciones descritas en los pasos posteriores, incluyendo la extracción y transformación de datos, así como la automatización de tareas relacionadas con la gestión de turnos. De la misma forma se crearon entidades en Java usando Spring Boot aunque no serán descritas en este documento. Estas entidades con sus correspondientes clases repositorio, controladores y servicios son herramientas fundamentales para la comunicación de la aplicación tanto con la base de datos como con sus vistas.

Paso 3: Desarrollo de un Script en Python para la Extracción y Transformación de Datos

El núcleo de la solución más rápida fue un script en Python que automatiza la extracción de datos del archivo Excel y genera scripts SQL para insertar estos datos en la base de datos. Ahora que ya sabemos dónde están los datos en nuestro Excel lo único que nos queda es programar nuestro script generador de inserts a partir de la hoja de cálculo. Aquí va nuestra versión, lógicamente este códigodebe ser adaptado a la hoja de cada usuario para que funcione correctamente. Al menos que sirva para describir el proceso lógico:

import pandas as pd
import os

# Ruta al archivo Excel
file_path = 'Untitled_United.xlsx'
output_folder = 'output/'

# Cargar la primera hoja del Excel
df = pd.read_excel(file_path, sheet_name=0, header=None)

# Crear la carpeta de salida si no existe
os.makedirs(output_folder, exist_ok=True)

# Inicializar listas para almacenar los INSERT statements
insert_empleados = []
insert_empleados_servicios = []

# Definir los servicios disponibles y el mapeo de empleados a servicios
servicios_map = {
    'PUERTA PRINCIPAL': 'Puerta Principal',
    'PLANTAS': 'Plantas',
    'RX': 'RX',
    'REFUERZO': 'Refuerzo'
}

# Filas y columnas relevantes
start_row = 4  # La fila donde empiezan los empleados (fila 5 en Excel)
fecha_row = 1  # La fila donde están las fechas (fila 2 en Excel)
num_columns = df.shape[1]

# Iterar sobre las filas para cada empleado
for i in range(start_row, df.shape[0]):
    row = df.iloc[i]
    empleado = row[0]  # Nombre del empleado en la primera columna
    
    # Saltar filas en blanco o inválidas
    if pd.isna(empleado) or not isinstance(empleado, str):
        print(f"Saltando fila {i+1}: Celda vacía o no es un nombre válido")
        continue

    print(f"Leyendo empleado: {empleado}")
    
    # Buscar el servicio asociado al empleado
    servicio = None
    for key in servicios_map:
        if key in empleado.upper():  # Convertir a mayúsculas para evitar problemas de mayúsculas/minúsculas
            servicio = servicios_map[key]
            break

    if not servicio:
        print(f"  Servicio no encontrado para el empleado {empleado}. Usando 'Servicio Desconocido'")
        servicio = 'Servicio Desconocido'
    else:
        print(f"  Asignando servicio: {servicio}")

    # Insertar empleado
    insert_empleados.append(f"INSERT INTO empleados (nombre, puesto) VALUES ('{empleado}', 'Puesto Desconocido');")

    # Insertar relación entre empleado y servicio
    insert_empleados_servicios.append(
        f"INSERT INTO empleados_servicios (empleado_id, servicio_id) VALUES "
        f"((SELECT id FROM empleados WHERE nombre='{empleado}'), "
        f"(SELECT id FROM servicios WHERE nombre='{servicio}'));"
    )

    # Inicializar lista para los turnos de este empleado
    insert_turnos_empleado = []

    # Iterar sobre las columnas de las fechas y turnos
    for j in range(1, num_columns):
        fecha_cell = df.iloc[fecha_row, j]
        turno = row[j]
        
        if pd.notna(turno):
            print(f"  Turno detectado para {empleado} en columna {j}: {turno}")
            
            # Intentar convertir la celda a fecha
            try:
                fecha = pd.to_datetime(fecha_cell, errors='coerce')
                if pd.notna(fecha):
                    fecha = fecha.strftime('%Y-%m-%d')
                else:
                    print(f"  Celda de fecha inválida en columna {j}, se ignorará.")
                    continue

                # Crear el INSERT statement para turnos del empleado
                insert_turnos_empleado.append(
                    f"INSERT INTO turnos (empleado_id, servicio_id, fecha, turno) "
                    f"VALUES ((SELECT id FROM empleados WHERE nombre='{empleado}'), "
                    f"(SELECT id FROM servicios WHERE nombre='{servicio}'), "
                    f"'{fecha}', '{turno}');"
                )
            except Exception as e:
                print(f"  Error al procesar la fecha en columna {j}: {e}")
        else:
            print(f"  Celda vacía para {empleado} en columna {j}")

    # Guardar los INSERT statements de turnos en un archivo separado por empleado
    if insert_turnos_empleado:
        turnos_file = os.path.join(output_folder, f"insert_turnos_{empleado.replace(' ', '_')}.sql")
        with open(turnos_file, 'w') as f:
            for statement in insert_turnos_empleado:
                f.write(statement + '\n')
        print(f"  Turnos para {empleado} guardados en {turnos_file}")
    else:
        print(f"  No se generaron turnos para {empleado}")

# Guardar los INSERT statements generales (empleados y asignación de servicios)
with open(os.path.join(output_folder, 'insert_empleados.sql'), 'w') as f:
    for statement in insert_empleados:
        f.write(statement + '\n')

with open(os.path.join(output_folder, 'insert_empleados_servicios.sql'), 'w') as f:
    for statement in insert_empleados_servicios:
        f.write(statement + '\n')

print(f"Scripts generados en la carpeta '{output_folder}'.")

Paso 4: Ejecución de los Scripts SQL

Una vez que el script en Python ha generado los archivos SQL con las instrucciones INSERT para poblar las tablas de la base de datos, el siguiente paso es ejecutar estos scripts en la base de datos que hemos diseñado. Es importante que antes de ejecutar los scripts comprobar si se han generado correctamente.

Para llevar a cabo esta tarea, sigue los siguientes pasos:

Preparar el Entorno de la Base de Datos:
- Asegúrate de que la base de datos está correctamente configurada y en funcionamiento. Esto incluye la creación de las tablas necesarias (empleados, servicios, empleados_servicios, turnos, y log_cambios) según el diseño previamente mencionado.
- Puedes utilizar cualquier sistema de gestión de bases de datos (DBMS) como MySQL, PostgreSQL, SQL Server, o SQLite, dependiendo de tus necesidades. Para este ejemplo, asumiremos que estás utilizando MySQL.
Conectar a la Base de Datos:
- Utiliza una herramienta de gestión de bases de datos, como MySQL Workbench, phpMyAdmin, o una conexión desde la línea de comandos, para conectarte a la base de datos donde deseas importar los datos.
Importar los Scripts SQL:
- Localiza los archivos SQL generados por el script de Python en la carpeta de salida (output/). Los archivos clave serán insert_empleados.sql, insert_empleados_servicios.sql, y los diferentes archivos insert_turnos_[nombre_empleado].sql.
- De igual manera, ejecuta cada uno de los archivos insert_turnos_[nombre_empleado].sql generados para insertar los datos correspondientes a los turnos de cada empleado.
Verificación de la Importación:
- Verifica que los datos de empleados, servicios, y turnos coincidan con los datos originales en el archivo Excel.
Gestión de Errores y Ajustes:
- Si encuentras errores durante la importación, revisa los scripts SQL generados para identificar cualquier problema. Esto podría incluir errores de formato, datos inconsistentes o fallas en la correspondencia entre los nombres de empleados y servicios.
- Realiza los ajustes necesarios y reejecuta los scripts problemáticos si es necesario.

Una vez ejecutados los scripts, es fundamental verificar que los datos han sido importados correctamente. Puedes hacer esto realizando consultas simples como:

SELECT * FROM empleados;
SELECT * FROM turnos;

Importa estos archivos a la base de datos ejecutando los siguientes comandos desde la línea de comandos de MySQL o desde la herramienta de gestión de tu elección:

SOURCE /ruta/completa/al/archivo/insert_empleados.sql;
SOURCE /ruta/completa/al/archivo/insert_empleados_servicios.sql;

Si estás utilizando MySQL desde la línea de comandos, el comando SOURCE te permitirá ejecutar un archivo SQL directamente.

Paso 5: Automatización y Programación de Actualizaciones

Una vez que los datos han sido importados y verificados, es importante establecer un proceso para mantener actualizada la base de datos con cualquier cambio en los turnos o la plantilla de empleados.

Desarrollar un Proceso Automatizado:
- Utiliza el mismo script de Python para realizar actualizaciones periódicas de la base de datos. Por ejemplo, si se actualiza el archivo Excel con nuevos turnos o cambios, simplemente ejecuta el script nuevamente para generar nuevos archivos SQL y actualiza la base de datos.
Programación de Tareas:
- Implementa una tarea programada (usando cron en sistemas Unix o el Programador de tareas en Windows) para ejecutar el script de Python en intervalos regulares, asegurando que la base de datos se mantenga actualizada automáticamente.

Aquí te presento la versión modificada del fragmento que incluye la generación de reportes anuales en PDF y la distribución diaria de turnos:

Paso 6: Generación de Reportes y Análisis de Datos

Con todos los datos ahora almacenados en una base de datos, puedes aprovechar el poder de SQL para generar reportes detallados y realizar análisis más complejos. A continuación, se detallan algunas de las capacidades que puedes implementar:

Consultas Personalizadas:
Crea consultas SQL para extraer información específica, como el número de horas trabajadas por empleado, la distribución de turnos entre diferentes servicios, o el análisis de patrones de ausencias. Estas consultas permiten a los gestores de recursos humanos y supervisores tomar decisiones informadas sobre la asignación de personal y la planificación de turnos futuros.

Visualización de Datos:
Utiliza herramientas de visualización de datos, como Tableau o Power BI, conectadas a la base de datos para crear dashboards interactivos. Estos dashboards pueden mostrar la información de turnos de manera clara y comprensible, facilitando la toma de decisiones para los responsables de la planificación y gestión del personal. Además, la visualización gráfica de datos permite identificar rápidamente tendencias y posibles problemas en la asignación de turnos.

Notificaciones Automáticas:
Desarrolla un sistema de notificaciones que envíe alertas a los supervisores cuando se detecten anomalías en los turnos, como solapamientos, ausencias no planificadas, o la necesidad de refuerzos en ciertos servicios. Estas notificaciones pueden enviarse por correo electrónico o a través de sistemas de mensajería interna, asegurando que los supervisores puedan reaccionar rápidamente a cualquier situación imprevista.

Reportes Anuales en PDF:
Implementa una función que permita generar reportes anuales en formato PDF para cada empleado. Estos reportes incluirán todos los turnos trabajados por el empleado a lo largo del año, facilitando el seguimiento de su historial laboral y ayudando en la evaluación de desempeño y en la planificación de turnos futuros.

Distribución Diaria de Turnos:
Además de los reportes anuales, genera reportes que muestren la distribución diaria de los turnos en las diferentes áreas de los servicios del hospital. Estos reportes pueden ser utilizados por los gestores de cada departamento para asegurar que cada área está adecuadamente cubierta y para identificar rápidamente si se necesita realizar ajustes en la planificación de turnos.

La migración de la gestión de turnos desde Excel a una base de datos mejora la eficiencia y precisión en la administración de personal, y también abre las puertas a un sinfín de posibilidades en términos de automatización, análisis avanzado de datos y toma de decisiones basada en información en tiempo real. Implementar esta solución no solo alivia la carga de trabajo manual, sino que también minimiza los errores y optimiza la operación del negocio.