a
    §0iá(  ã                   @   s8   d Z ddlmZmZ ddlZdd„ Zdd„ Zdd	„ ZdS )
u8   
Consultas SQL para el mÃ³dulo de ValidaciÃ³n del Score
é    )Úget_connectionÚget_connectionERPNc               
   C   sx   z:t ƒ } d}t || ¡}|  ¡  tt|d |d ƒƒ}|W S  tyr } z tdt|ƒ› ƒ W Y d}~dS d}~0 0 dS )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
        Z
ColumnNameÚDataTypeu+   Error al obtener catÃ¡logo de validaciÃ³n: N)	r   ÚpdÚread_sqlÚcloseÚdictÚzipÚ	ExceptionÚprintÚstr)ÚconnÚqueryÚdfZcatalogoÚe© r   úI/var/www/html/src/Consultas_SQL/SupYCtrol/IngDeControl/ValidarScoreSQL.pyÚobtener_catalogo_validacion   s    
r   c              
   C   sš   z\t ƒ }| ¡ }ddl}|j|dd}d}	| |	| ||||f¡ | ¡  | ¡  | ¡  W dS  ty” }
 z tdt	|
ƒ› ƒ W Y d}
~
dS d}
~
0 0 dS )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   Zerrores_jsonr   r   r   r   r   Úguardar_log_validacion-   s    r   c               
   C   sŒ   z8t ƒ } d}t || ¡}|  ¡  tdt|ƒ› dƒ |W S  ty† } z6tdt|ƒ› ƒ ddl}| 	¡  t 
¡ W  Y d}~S d}~0 0 dS )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!   r   r   r   Úobtener_cantidades_componentesY   s    pr$   )	Ú__doc__ÚConsultas_SQL.conexionr   r   Úpandasr   r   r   r$   r   r   r   r   Ú<module>   s
    ,