
    sz0i(                     2    S r SSKJrJr  SSKrS rS rS rg)u8   
Consultas SQL para el módulo de Validación del Score
    )get_connectionget_connectionERPNc                       [        5       n Sn[        R                  " X5      nU R                  5         [	        [        US   US   5      5      nU$ ! [         a!  n[        S[        U5       35         SnAgSnAff = f)u   
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)', ...}
z
        SELECT 
            ColumnName,
            DataType
        FROM CM_DataTypeValidator
        WHERE Origin = 'Manual'
          AND Active = 1
        ORDER BY ColumnName
        
ColumnNameDataTypeu+   Error al obtener catálogo de validación: N)	r   pdread_sqlclosedictzip	Exceptionprintstr)connquerydfcatalogoes        sC:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\Consultas_SQL\SupYCtrol\IngDeControl\ValidarScoreSQL.pyobtener_catalogo_validacionr      sw     [[%

 B|,bn=> ;CF8DEs   AA 
A<A77A<c                 J    [        5       nUR                  5       nSSKnUR                  USS9nSn	UR	                  XXX445        UR                  5         UR                  5         UR                  5         g! [         a!  n
[        S[        U
5       35         Sn
A
gSn
A
ff = f)u  
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
r   NF)ensure_asciiz
        INSERT INTO CM_ValidationLog (
            TotalFilas,
            TotalErrores,
            ErroresPorTipo,
            FechaValidacion,
            Estado,
            CreatedBy
        ) VALUES (?, ?, ?, ?, ?, 'SYSTEM')
        Tu%   Error al guardar log de validación: )
r   cursorjsondumpsexecutecommitr
   r   r   r   )total_filastotal_erroreserrores_por_tipo	timestampestador   r   r   errores_jsonr   r   s              r   guardar_log_validacionr$   -   s     	zz"2zG	 	uM[\

 5c!fX>?s   A4A7 7
B"BB"c                  J    [        5       n Sn[        R                  " X5      nU R                  5         [	        S[        U5       S35        U$ ! [         aJ  n[	        S[        U5       35        SSKnUR                  5         [        R                  " 5       s SnA$ SnAff = f)u  
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
a  
        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
        u&   ✅ Consulta de cantidades ejecutada: z registros obtenidosu.   ❌ Error al ejecutar consulta de cantidades: r   N)r   r   r	   r
   r   lenr   r   	traceback	print_exc	DataFrame)r   r   r   r   r'   s        r   obtener_cantidades_componentesr*   Y   s    ~ "n` [[%

6s2wi?STU	 >s1vhGH||~	s   AA 
B"?BB"B")	__doc__Consultas_SQL.conexionr   r   pandasr   r   r$   r*        r   <module>r0      s%    E @*XLr/   