SELECT 
    t.Trab_ID,
    mv.Salario AS Ultimo_Salario,
    t.Tipo_Periodo,
    t.Concepto_ID,
    t.Descripcion, 
    t.Gravado_Julio, t.Exento_Julio, t.Concepto601_Julio,  t.Concepto426_Julio,  t.Concepto627_Julio

FROM (

    SELECT
        nc.trab_id AS Trab_ID,
        nc.concepto_id AS Concepto_ID,
        c.descripcion AS Descripcion,
        CASE 
            WHEN SUM(CASE WHEN nc.periodo_id = 1 THEN 1 ELSE 0 END) > 0 THEN 'Semanal'
            ELSE 'Quincenal'
        END AS Tipo_Periodo,


        SUM(
            (CASE 
                WHEN nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1 THEN nc.gravado
                WHEN nc.periodo IN (13,14) AND nc.periodo_id = 2 THEN nc.gravado
                ELSE 0
             END)
            *
            (CASE WHEN nc.concepto_id BETWEEN 601 AND 799 THEN -1 ELSE 1 END)
        ) AS Gravado_Julio,

        SUM(
            (CASE 
                WHEN nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1 THEN nc.excento
                WHEN nc.periodo IN (13,14) AND nc.periodo_id = 2 THEN nc.excento
                ELSE 0
             END)
            *
            (CASE WHEN nc.concepto_id BETWEEN 601 AND 799 THEN -1 ELSE 1 END)
        ) AS Exento_Julio,

        SUM(CASE WHEN nc.concepto_id in( 601,627) 
                 AND ( (nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1) OR (nc.periodo IN (13,14) AND nc.periodo_id = 2) ) 
                 THEN nc.importe ELSE 0 END) AS Concepto601_Julio,

SUM(CASE WHEN nc.concepto_id = 426
AND ( (nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1) OR (nc.periodo IN (13,14) AND nc.periodo_id = 2) ) 
                 THEN nc.importe ELSE 0 END) AS Concepto426_Julio,

SUM(CASE WHEN nc.concepto_id = 627 
AND ( (nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1) OR (nc.periodo IN (13,14) AND nc.periodo_id = 2) ) 
                 THEN nc.importe ELSE 0 END) AS Concepto627_Julio

    FROM nomcalculohistorico nc
    LEFT JOIN Conceptos c ON c.concepto_id = nc.concepto_id
    WHERE nc.ano = 2025
      AND (nc.concepto_id BETWEEN 400 AND 799)
      AND (nc.gravado <> 0 OR nc.excento <> 0 OR nc.importe <> 0)
    GROUP BY nc.trab_id, nc.concepto_id, c.descripcion

    UNION ALL

    SELECT
        nc.trab_id AS Trab_ID,
        NULL AS Concepto_ID,
        'TOTAL' AS Descripcion,
        CASE 
            WHEN SUM(CASE WHEN nc.periodo_id = 1 THEN 1 ELSE 0 END) > 0 THEN 'Semanal'
            ELSE 'Quincenal'
        END AS Tipo_Periodo,

        SUM(
            (CASE 
                WHEN nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1 THEN nc.gravado
                WHEN nc.periodo IN (13,14) AND nc.periodo_id = 2 THEN nc.gravado
                ELSE 0
             END)
            *
            (CASE WHEN nc.concepto_id BETWEEN 601 AND 799 THEN -1 ELSE 1 END)
        ) AS Gravado_Julio,

        SUM(
            (CASE 
                WHEN nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1 THEN nc.excento
                WHEN nc.periodo IN (13,14) AND nc.periodo_id = 2 THEN nc.excento
                ELSE 0
             END)
            *
            (CASE WHEN nc.concepto_id BETWEEN 601 AND 799 THEN -1 ELSE 1 END)
        ) AS Exento_Julio,

        SUM(CASE WHEN nc.concepto_id in( 601,627)  
                 AND ( (nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1) OR (nc.periodo IN (13,14) AND nc.periodo_id = 2) ) 
                 THEN nc.importe ELSE 0 END) AS Concepto601_Julio,

SUM(CASE WHEN nc.concepto_id = 426
AND ( (nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1) OR (nc.periodo IN (13,14) AND nc.periodo_id = 2) ) 
                 THEN nc.importe ELSE 0 END) AS Concepto426_Julio,

SUM(CASE WHEN nc.concepto_id = 627 
AND ( (nc.periodo IN (27,28,29,30) AND nc.periodo_id = 1) OR (nc.periodo IN (13,14) AND nc.periodo_id = 2) ) 
                 THEN nc.importe ELSE 0 END) AS Concepto627_Julio


    FROM nomcalculohistorico nc
    WHERE nc.ano = 2026
      AND (nc.concepto_id BETWEEN 400 AND 799)
      AND (nc.gravado <> 0 OR nc.excento <> 0 OR nc.importe <> 0)
    GROUP BY nc.trab_id
) t
LEFT JOIN (
    SELECT 
        M1.Trab_ID,
        M1.Salario,
        M1.PTR AS Ultimo_PTR
    FROM Movimiento M1
    INNER JOIN (
        SELECT Trab_ID, MAX(PTR) AS Ultimo_PTR
        FROM Movimiento
        GROUP BY Trab_ID
    ) M2 ON M1.Trab_ID = M2.Trab_ID AND M1.PTR = M2.Ultimo_PTR
) mv ON mv.Trab_ID = t.Trab_ID
ORDER BY 
    t.Trab_ID,
    CASE WHEN t.Concepto_ID IS NULL THEN 1 ELSE 0 END,
    t.Concepto_ID;