Files

67 lines
2.9 KiB
Python

# ==============================================================================
# Repositorio de Workstations y Carriles en SQL Server (videoman)
# ==============================================================================
from typing import Optional, List, Dict, Any
from app.core.database import db_session
class WorkstationRepository:
@staticmethod
def obtener_workstation_por_nombre(name: str) -> Optional[Dict[str, Any]]:
"""Busca una estación de trabajo por nombre en la tabla `wks`."""
query = """
SELECT WorkstationID, Nombre, SystemParameter
FROM wks
WHERE LOWER(Nombre) = LOWER(%s)
"""
with db_session() as conn:
cursor = conn.cursor(as_dict=True)
cursor.execute(query, (name.strip(),))
return cursor.fetchone()
@staticmethod
def listar_modulos_por_workstation(workstation_id: int) -> List[Dict[str, Any]]:
"""Obtiene la lista de módulos de hardware / carriles vinculados a la estación."""
query = """
SELECT
m.ModuloID,
m.Nombre,
m.Address,
m.GrupoModulos,
m.ModuloEntradaID,
m.ModuloSalidaID,
m.duracion_pulso,
COALESCE(
(SELECT ValorID FROM MdlCatVal WHERE ModuloID = m.ModuloID AND CategoriaID = 15),
(SELECT ValorID FROM MdlCatVal WHERE ModuloID = m.ModuloEntradaID AND CategoriaID = 15),
(SELECT ValorID FROM MdlCatVal WHERE ModuloID = m.ModuloSalidaID AND CategoriaID = 15),
3
) AS tipo_acceso_id,
COALESCE(
(SELECT Nombre FROM CatVal cv WHERE cv.CategoriaID = 15 AND cv.ValorID = COALESCE(
(SELECT ValorID FROM MdlCatVal WHERE ModuloID = m.ModuloID AND CategoriaID = 15),
(SELECT ValorID FROM MdlCatVal WHERE ModuloID = m.ModuloEntradaID AND CategoriaID = 15),
(SELECT ValorID FROM MdlCatVal WHERE ModuloID = m.ModuloSalidaID AND CategoriaID = 15),
3
)),
'Barrera'
) AS tipo_acceso_nombre
FROM wksmdl wm
JOIN mdl m ON wm.ModuloID = m.ModuloID
WHERE wm.WorkstationID = %s
ORDER BY m.ModuloID
"""
with db_session() as conn:
cursor = conn.cursor(as_dict=True)
cursor.execute(query, (workstation_id,))
return cursor.fetchall()
@staticmethod
def listar_todas() -> List[Dict[str, Any]]:
"""Retorna todas las estaciones de trabajo registradas."""
query = "SELECT WorkstationID, Nombre FROM wks ORDER BY Nombre"
with db_session() as conn:
cursor = conn.cursor(as_dict=True)
cursor.execute(query)
return cursor.fetchall()