# Archivo: ValidarScoreSQL.py # Ruta: Consultas_SQL\SupYCtrol\IngDeControl\ValidarScoreSQL.py # Descripción: Módulo para validar el Score descargado desde SharePoint # Autor: Equipo de Desarrollo IGSA # Fecha: 2025 """ Consultas SQL para el módulo de Validación del Score """ from Consultas_SQL.conexion import get_connection, get_connectionERP import pandas as pd def obtener_catalogo_validacion(): """ Obtiene el catálogo de tipos de datos desde CM_DataTypeValidator Returns: dict: Diccionario con ColumnName como clave y DataType como valor Ejemplo: {'OrderNum': 'INT', 'PartNum': 'NVARCHAR(50)', ...} """ try: conn = get_connection() query = """ SELECT ColumnName, DataType FROM CM_DataTypeValidator WHERE Origin = 'Manual' AND Active = 1 ORDER BY ColumnName """ df = pd.read_sql(query, conn) conn.close() # Convertir a diccionario catalogo = dict(zip(df['ColumnName'], df['DataType'])) return catalogo except Exception as e: print(f"Error al obtener catálogo de validación: {str(e)}") return None def guardar_log_validacion(total_filas, total_errores, errores_por_tipo, timestamp, estado): """ Guarda el log de la validación en la base de datos Args: total_filas (int): Total de filas procesadas total_errores (int): Total de errores encontrados errores_por_tipo (dict): Diccionario con tipos de error y su cantidad timestamp (str): Fecha y hora de la validación estado (str): 'SUCCESS' o 'FAILED' Returns: bool: True si se guardó correctamente, False en caso contrario """ try: conn = get_connection() cursor = conn.cursor() # Convertir errores_por_tipo a string JSON import json errores_json = json.dumps(errores_por_tipo, ensure_ascii=False) query = """ INSERT INTO CM_ValidationLog ( TotalFilas, TotalErrores, ErroresPorTipo, FechaValidacion, Estado, CreatedBy ) VALUES (?, ?, ?, ?, ?, 'SYSTEM') """ cursor.execute(query, (total_filas, total_errores, errores_json, timestamp, estado)) conn.commit() cursor.close() conn.close() return True except Exception as e: print(f"Error al guardar log de validación: {str(e)}") return False def obtener_cantidades_componentes(): """ Consulta que devuelve cantidades de motores, generadores y radiadores para validar que no excedan el máximo permitido (1 de cada uno) Returns: columnas: - OrderNum: Número de orden - OrderNum&Line: Concatenación de OrderNum + OrderLine - JobNum2: Número de trabajo asignado - CantidadDeMotores: Cantidad de motores detectados - CantidadDeGeneradores: Cantidad de generadores detectados - CantidadDeRadiadores: Cantidad de radiadores detectados """ try: conn = get_connectionERP() query = """ SELECT CAST(Erp.OrderHed.OrderNum AS INT) AS OrderNum, CAST(Erp.OrderDtl.OrderLine AS INT) AS OrderLine, CAST(CONCAT(Erp.OrderHed.OrderNum, Erp.OrderDtl.OrderLine) AS INT) AS 'OrderNum&Line', CAST(Erp.JobProd.JobNum AS NVARCHAR(14)) AS 'JobNum2', -- ============================================================ -- CANTIDAD DE MOTORES -- ============================================================ Cast(Case When Erp.OrderDtl.PartNum not like 'EP%' then 0 When Erp.JobProd.JobNum is null then 0 When (Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A') then (select Count(Erp.JobMtl.PartNum) from Erp.JobMtl where Erp.JobMtl.Company='IGSA' and Erp.JobMtl.PartNum like 'EM%' and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1) Group by Erp.JobMtl.JobNum ) else (select Count(Erp.JobMtl.PartNum) from Erp.JobMtl where Erp.JobMtl.Company='IGSA' and Erp.JobMtl.PartNum like 'EM%' and Erp.JobMtl.JobNum = Erp.JobProd.JobNum Group by Erp.JobMtl.JobNum ) End as INT) as CantidadDeMotores, -- ============================================================ -- CANTIDAD DE GENERADORES -- ============================================================ Cast(Case When Erp.OrderDtl.PartNum not like 'EP%' then 0 When Erp.JobProd.JobNum is null then 0 When (Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A') then (select Count(Erp.JobMtl.PartNum) from Erp.JobMtl where Erp.JobMtl.Company='IGSA' and Erp.JobMtl.PartNum like 'EG%' and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1) Group by Erp.JobMtl.JobNum ) else (select Count(Erp.JobMtl.PartNum) from Erp.JobMtl where Erp.JobMtl.Company='IGSA' and Erp.JobMtl.PartNum like 'EG%' and Erp.JobMtl.JobNum = Erp.JobProd.JobNum Group by Erp.JobMtl.JobNum ) End as INT) as CantidadDeGeneradores, -- ============================================================ -- CANTIDAD DE RADIADORES (NUEVO) -- ============================================================ CAST( ISNULL( (select Count(Erp.JobMtl.PartNum) from Erp.JobMtl left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode where Erp.JobMtl.Company='IGSA' and Erp.Part.ProdCode like 'RR1%' and Erp.JobMtl.JobNum = Erp.JobProd.JobNum Group by Erp.JobMtl.JobNum ), 0 ) AS INT ) as CantidadDeRadiadores FROM Erp.OrderHed INNER JOIN Erp.OrderHed_UD ON Erp.OrderHed.SysRowID = Erp.OrderHed_UD.ForeignSysRowID RIGHT JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum LEFT JOIN Erp.SalesCat ON Erp.OrderDtl.Company = Erp.SalesCat.Company AND Erp.OrderDtl.SalesCatID = Erp.SalesCat.SalesCatID AND Erp.OrderHed_UD.ShortChar01 = Erp.SalesCat.SalesCatID LEFT JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company AND Erp.OrderHed.CustNum = Erp.Customer.CustNum LEFT JOIN Erp.ProdGrup ON Erp.OrderDtl.Company = Erp.ProdGrup.Company AND Erp.OrderDtl.ProdCode = Erp.ProdGrup.ProdCode LEFT JOIN Erp.SalesRep ON Erp.OrderHed.Company = Erp.SalesRep.Company AND Erp.OrderHed.SalesRepList = Erp.SalesRep.SalesRepCode LEFT JOIN Erp.SerialNo ON Erp.OrderDtl.Company = Erp.SerialNo.Company AND Erp.OrderDtl.OrderNum = Erp.SerialNo.OrderNum AND Erp.OrderDtl.OrderLine = Erp.SerialNo.OrderLine LEFT JOIN Erp.JobProd ON Erp.OrderDtl.Company = Erp.JobProd.Company AND Erp.OrderDtl.OrderNum = Erp.JobProd.OrderNum AND Erp.OrderDtl.OrderLine = Erp.JobProd.OrderLine LEFT JOIN Erp.JobHead ON Erp.JobProd.Company = Erp.JobHead.Company AND Erp.JobProd.JobNum = Erp.JobHead.JobNum LEFT JOIN Erp.Part ON Erp.JobProd.Company = Erp.Part.Company AND Erp.JobProd.PartNum = Erp.Part.PartNum WHERE Erp.OrderHed.Company='IGSA' AND Erp.OrderHed.OpenOrder='1' AND Erp.OrderHed.VoidOrder='0' AND Erp.OrderHed.OrderHeld='0' AND Erp.OrderDtl.VoidLine ='0' AND Erp.OrderDtl.OpenLine='1' AND (Erp.OrderDtl.PartNum like 'EP1%' OR (Erp.OrderDtl.PartNum like 'EC%' AND Erp.OrderDtl.LineDesc like '%CONTENEDOR%') OR (Erp.OrderDtl.PartNum like 'EC1%') OR Erp.OrderDtl.PartNum like 'ET1%' OR Erp.OrderDtl.PartNum like 'RB1%' OR Erp.OrderDtl.PartNum like 'RS1%') AND ((Erp.SalesCat.SalesCatID <> 'VDAE') OR (Erp.OrderDtl.SalesCatID <> 'VDPR')) ORDER BY Erp.OrderHed.OrderNum DESC """ df = pd.read_sql(query, conn) conn.close() print(f"✅ Consulta de cantidades ejecutada: {len(df)} registros obtenidos") return df except Exception as e: print(f"❌ Error al ejecutar consulta de cantidades: {str(e)}") import traceback traceback.print_exc() return pd.DataFrame()