U
    i                     @   s    d dl mZ d dlZdd ZdS )    )get_connectionNc               
      s   d} d}zz^t }|s(td g W W S | }||  dd |jD   fdd| D }|W W ~S  tjk
r } ztd|  g  W Y W NS d}~X Y n< t	k
r } ztd|  g  W Y W S d}~X Y nX W 5 |r|   X dS )	u   
    Obtiene la lista completa de tareas de cotización desde la BD.
    Esta función es llamada por el endpoint en CotizFormQueryIng.py
    u  
    SELECT 
        -- 🟦 Identificadores principales
        Q_SpQ_QuotationTasks.TaskID                               AS [NroTarea],
        Q_SpQ_QuotationTasks.Status                               AS [Estatus],
        Q_OpportunityCRM.CRM_OpportunityNumber                   AS [Oportunidad],
        Q_QuotationType.FrontES                                   AS [TipoFormulario],

        -- 🟩 Fechas clave (Con FORMATO)
        Q_SpQ_FormsHead.FormID                                 AS [NroFormulario],
        FORMAT(Q_SpQ_FormsHead.CreatedAt, 'dd-MM-yyyy HH:mm')      AS [FechaCreacion],

        -- 🟨 Información del cliente (CRM)
        Q_OpportunityCRM.CRM_ContactName                          AS [Contacto],

        -- 🟧 Información del vendedor (desde Profiles)
        Profiles.FirstName + ' ' + 
        ISNULL(Profiles.MiddleName, '') + ' ' + 
        Profiles.LastName + ' ' + 
        ISNULL(Profiles.SecondLastName, '')                       AS [Vendedor],
        Profiles.Email                                            AS [CorreoVendedor],
        Profiles.ContactPhone                                     AS [TelefonoVendedor],

        -- 🟥 Información de costeo (puede estar vacía si no se ha generado)
        Q_CostingHead.CostingNum                                  AS [NroCosteo],
        Q_CostingHead.CaseCost                                    AS [CasoCosteo],
        Q_CostingHead.DirectCost                                  AS [CostoDirecto],
        Q_CostingHead.IndirectPercent                             AS [PctIndirecto],
        Q_CostingHead.IndirectAmount                              AS [MontoIndirecto],
        Q_CostingHead.FinancePercent                              AS [PctFinanciamiento],
        Q_CostingHead.FinanceAmount                               AS [MontoFinanciamiento],
        Q_CostingHead.UtilityPercent                              AS [PctUtilidad],
        Q_CostingHead.UtilityAmount                               AS [MontoUtilidad],
        Q_CostingHead.OperationPercent                            AS [PctGtosOp],
        Q_CostingHead.OperationAmount                             AS [MontoGtosOp],
        Q_CostingHead.OvercostFactor                              AS [FactorSobrecosto],
        Q_CostingHead.SalePriceMin                                AS [PrecioVentaMin],
        Q_CostingHead.DiscountMaxPercent                          AS [PctDescMax],
        Q_CostingHead.DiscountMaxAmount                           AS [MontoDescuento],
        Q_CostingHead.SalePriceList                               AS [PrecioLista],

        -- 🟪 Tiempo de ejecución formateado
        LTRIM(RTRIM(CONCAT(
            ISNULL(CAST(Q_CostingHead.RunTimeNumber AS NVARCHAR(10)), ''),
            ' ',
            ISNULL(Q_CostingHead.RunTimeType, '')
        ))) AS [TiempoEjecucion]

    FROM Q_SpQ_QuotationTasks
    INNER JOIN Q_SpQ_FormsHead
        ON Q_SpQ_QuotationTasks.FormID = Q_SpQ_FormsHead.FormID
    INNER JOIN Q_QuotationType
        ON Q_SpQ_FormsHead.QuotationTypeID = Q_QuotationType.QuotationTypeID
    INNER JOIN Q_OpportunityCRM
        ON Q_SpQ_QuotationTasks.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
    LEFT JOIN Q_CostingHead
        ON Q_SpQ_QuotationTasks.CostingID = Q_CostingHead.CostingID
    LEFT JOIN Profiles
        ON Q_SpQ_QuotationTasks.SellerUserID = Profiles.UserID
    WHERE 
        Q_SpQ_QuotationTasks.Active = 1
    ORDER BY 
        Q_SpQ_QuotationTasks.Status,
        Q_SpQ_FormsHead.CreatedAt DESC;
    Nu@   Error SQL: No se pudo establecer conexión con la base de datos.c                 S   s   g | ]}|d  qS )r    ).0columnr   r   ~C:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\Consultas_SQL\Operaciones\Ingenieria\Cotiz\CotizFormQueryIngSQL.py
<listcomp>`   s     z"get_tasks_list.<locals>.<listcomp>c                    s   g | ]}t t |qS r   )dictzip)r   rowcolumnsr   r   r   c   s     z8[Error SQL pyodbc] Error obteniendo la lista de tareas: z,[Error inesperado] Error en get_tasks_list: )
closer   printcursorexecutedescriptionfetchallpyodbcError	Exception)queryconnr   resultser   r   r   get_tasks_list   s(    B


"r   )Consultas_SQL.conexionr   r   r   r   r   r   r   <module>   s   