{
-------------------------------------------------------------------------------
PROGRAMA             :  ADPRRP048
OBJETIVO             :  Horas de capacitacion por colaborador (indicador RRHH).
                        Por colaborador: cada capacitacion REQUERIDA con sus
                        horas y, debajo, las TOMADAS que la cubren; luego las
                        tomadas no planificadas. Totales por colaborador y
                        generales con % de cumplimiento.
                        Datos: adtb00060 (detalle) / adtb00059 (catalogo),
                        registrados por ADPRMT017 (Accion de Personal).
FECHA REALIZACION    :  Octubre 05, 2026
-------------------------------------------------------------------------------
}
GLOBALS "adprgb000.4gl"

DEFINE p RECORD
    fecha_ini    DATE,
    fecha_fin    DATE,
    departamento SMALLINT,
    num_emp      INTEGER,
    solo_pend    CHAR(1)
END RECORD
DEFINE filtros VARCHAR(200)

MAIN
    DEFER INTERRUPT
    CALL ARG_VAL(1) RETURNING usuarios
    CALL ARG_VAL(2) RETURNING clave
    CALL ARG_VAL(3) RETURNING impresor

    CONNECT TO "smarmotech" USER usuarios USING clave
    SELECT a.* INTO p_companias.* FROM companias a
    CALL adprrp048()
END MAIN

FUNCTION adprrp048()
    DEFINE x RECORD
        num_emp      INTEGER,
        empleado     VARCHAR(60),
        nom_dpto     VARCHAR(40),
        nom_puesto   VARCHAR(40),
        fila         VARCHAR(20),
        nom_cap      VARCHAR(100),
        tipo_evento  VARCHAR(15),
        fecha_ini    DATE,
        fecha_fin    DATE,
        h_req        DECIMAL(8,2),
        h_tom        DECIMAL(8,2),
        h_pend       DECIMAL(8,2),
        h_tom_t      DECIMAL(8,2),
        institucion  VARCHAR(100)
    END RECORD
    DEFINE cb     ui.ComboBox,
           xdep   SMALLINT,
           xnom   VARCHAR(40),
           nfilas INTEGER

    OPTIONS
        FORM LINE 8,
        ERROR LINE 23,
        COMMENT LINE 21

    OPEN FORM adfmrp048 FROM "adfmrp048"
    DISPLAY FORM adfmrp048

    LET cb = ui.ComboBox.forName("formonly.departamento")
    CALL cb.clear()
    CALL cb.addItem(0, "TODOS")
    DECLARE c_dep48 CURSOR FOR
        SELECT a.departamento, a.nom_dpto FROM adtb00001 a
         WHERE a.status_t IS NULL ORDER BY a.nom_dpto
    FOREACH c_dep48 INTO xdep, xnom
        CALL cb.addItem(xdep, xnom CLIPPED)
    END FOREACH

    LET p.fecha_ini    = MDY(1, 1, YEAR(TODAY))
    LET p.fecha_fin    = MDY(12, 31, YEAR(TODAY))
    LET p.departamento = 0
    LET p.num_emp      = NULL      -- variable de modulo: arranca en 0, no en NULL
    LET p.solo_pend    = "N"

    INPUT BY NAME p.* WITHOUT DEFAULTS ATTRIBUTE(UNBUFFERED)
        AFTER INPUT
            IF int_flag THEN
                EXIT INPUT
            END IF
            IF p.fecha_ini IS NULL OR p.fecha_fin IS NULL OR p.fecha_fin < p.fecha_ini THEN
                CALL fgl_winmessage("INFO","REVISE EL RANGO DE FECHAS","info")
                NEXT FIELD fecha_ini
            END IF
    END INPUT
    IF int_flag THEN
        LET int_flag = FALSE
        CALL msg(2)
        RETURN
    END IF

    ----------------------------------------------------------------------
    -- Filas: R (requerida, con lo tomado acumulado), T ligada (debajo de su
    -- requerida) y T no planificada (al final). El rango de fechas se mide
    -- sobre la fecha de inicio de la requerida (o de la tomada si no tiene).
    ----------------------------------------------------------------------
    LET selec =
      "SELECT e.num_emp, ",
      "       RTRIM(e.nom1_emp)+' '+RTRIM(e.apell1_emp)+ISNULL(' '+RTRIM(e.apell2_emp),''), ",
      "       ISNULL(d.nom_dpto,''), ISNULL(pu.nom_puesto,''), ",
      "       CASE WHEN r.tipo_reg = 'R' THEN 'REQUERIDA' ",
      "            WHEN r.id_requerida IS NOT NULL THEN '   TOMADA' ",
      "            ELSE 'NO PLANIFICADA' END, ",
      "       c.nom_cap, ",
      "       CASE c.evento WHEN 1 THEN 'SEMINARIO' WHEN 2 THEN 'CURSO' ",
      "                     WHEN 3 THEN 'TALLER' WHEN 4 THEN 'CONFERENCIA' ELSE '' END, ",
      "       r.fecha_ini, r.fecha_fin, ",
      "       CASE WHEN r.tipo_reg = 'R' THEN ISNULL(r.horas_req,0) END, ",
      "       CASE WHEN r.tipo_reg = 'R' THEN ISNULL(t.tom,0) ELSE ISNULL(r.horas_tom,0) END, ",
      "       CASE WHEN r.tipo_reg = 'R' THEN ",
      "            CASE WHEN ISNULL(r.horas_req,0) - ISNULL(t.tom,0) > 0 ",
      "                 THEN ISNULL(r.horas_req,0) - ISNULL(t.tom,0) ELSE 0 END END, ",
      "       CASE WHEN r.tipo_reg = 'T' THEN ISNULL(r.horas_tom,0) ELSE 0 END, ",
      "       ISNULL(r.institucion,'') ",
      "FROM adtb00060 r ",
      "     JOIN adtb00059 c ON c.cod_cap = r.cod_cap ",
      "     JOIN adtb00003 e ON e.num_emp = r.num_emp ",
      "     LEFT JOIN adtb00001 d ON d.departamento = e.departamento ",
      "     LEFT JOIN adtb00004 pu ON pu.cod_puesto = e.cod_puesto ",
      "     LEFT JOIN adtb00060 q ON q.id_cap = r.id_requerida ",
      "     LEFT JOIN (SELECT id_requerida, SUM(horas_tom) tom FROM adtb00060 ",
      "                 WHERE tipo_reg = 'T' AND status_t IS NULL AND id_requerida IS NOT NULL ",
      "                 GROUP BY id_requerida) t ON t.id_requerida = r.id_cap ",
      "WHERE r.status_t IS NULL ",
      "  AND ISNULL(q.fecha_ini, r.fecha_ini) BETWEEN ? AND ? "

    LET filtros = "Periodo: ", p.fecha_ini USING "dd/mm/yyyy", " al ", p.fecha_fin USING "dd/mm/yyyy"
    IF p.departamento IS NOT NULL AND p.departamento <> 0 THEN
        LET selec = selec, " AND e.departamento = ", p.departamento USING "<<<<&"
        SELECT a.nom_dpto INTO xnom FROM adtb00001 a WHERE a.departamento = p.departamento
        LET filtros = filtros CLIPPED, "   Departamento: ", xnom CLIPPED
    ELSE
        LET filtros = filtros CLIPPED, "   Departamento: TODOS"
    END IF
    IF p.num_emp IS NOT NULL AND p.num_emp <> 0 THEN
        LET selec = selec, " AND e.num_emp = ", p.num_emp USING "<<<<<<&"
        LET filtros = filtros CLIPPED, "   Ficha: ", p.num_emp USING "<<<<<<&"
    END IF
    IF p.solo_pend = "S" THEN
        LET selec = selec,
          " AND EXISTS (SELECT 1 FROM adtb00060 r2 ",
          "   LEFT JOIN (SELECT id_requerida, SUM(horas_tom) tom FROM adtb00060 ",
          "               WHERE tipo_reg = 'T' AND status_t IS NULL GROUP BY id_requerida) t2 ",
          "          ON t2.id_requerida = r2.id_cap ",
          "   WHERE r2.num_emp = r.num_emp AND r2.tipo_reg = 'R' AND r2.status_t IS NULL ",
          "     AND r2.fecha_ini BETWEEN ? AND ? ",
          "     AND ISNULL(r2.horas_req,0) - ISNULL(t2.tom,0) > 0) "
        LET filtros = filtros CLIPPED, "   Solo con pendientes"
    END IF
    LET selec = selec,
      " ORDER BY ISNULL(d.nom_dpto,''), 2, e.num_emp, ",
      "   CASE WHEN r.tipo_reg = 'T' AND r.id_requerida IS NULL THEN 1 ELSE 0 END, ",
      "   ISNULL(r.id_requerida, r.id_cap), ",
      "   CASE WHEN r.tipo_reg = 'R' THEN 0 ELSE 1 END, r.fecha_ini"

    PREPARE p_rep48 FROM selec
    DECLARE c_rep48 CURSOR FOR p_rep48

    -- Configuracion del motor de reportes (.4rp)
    LET r_filename = "adprrp048.4rp"
    LET preview    = TRUE
    IF NOT fgl_report_loadCurrentSettings(r_filename) THEN
        CALL fgl_winmessage("INFO","No se pudo cargar el diseno adprrp048.4rp","stop")
        RETURN
    END IF
    CALL seleccionarSalida() RETURNING r_output
    CALL fgl_report_selectDevice(r_output)
    CALL fgl_report_selectPreview(preview)
    IF r_output = "XLSX" THEN
        CALL fgl_report_configurexlsxdevice(NULL,NULL,NULL,FALSE,FALSE,NULL,1)
    END IF
    LET handler = fgl_report_commitCurrentSettings()
    IF handler IS NULL THEN
        RETURN
    END IF

    LET nfilas = 0
    START REPORT capacitacion TO XML HANDLER handler
    IF p.solo_pend = "S" THEN
        OPEN c_rep48 USING p.fecha_ini, p.fecha_fin, p.fecha_ini, p.fecha_fin
    ELSE
        OPEN c_rep48 USING p.fecha_ini, p.fecha_fin
    END IF
    WHILE TRUE
        FETCH c_rep48 INTO x.*
        IF STATUS = NOTFOUND THEN
            EXIT WHILE
        END IF
        LET nfilas = nfilas + 1
        OUTPUT TO REPORT capacitacion(x.*)
    END WHILE
    CLOSE c_rep48
    FINISH REPORT capacitacion

    IF nfilas = 0 THEN
        CALL fgl_winmessage("INFO","No hay capacitaciones registradas con esos parametros","info")
    END IF
END FUNCTION

FUNCTION pct48(a, b)
    DEFINE a, b DECIMAL(10,2)
    IF b IS NULL OR b = 0 THEN
        RETURN 0
    END IF
    RETURN (a / b) * 100
END FUNCTION

REPORT capacitacion(x)
    DEFINE x RECORD
        num_emp      INTEGER,
        empleado     VARCHAR(60),
        nom_dpto     VARCHAR(40),
        nom_puesto   VARCHAR(40),
        fila         VARCHAR(20),
        nom_cap      VARCHAR(100),
        tipo_evento  VARCHAR(15),
        fecha_ini    DATE,
        fecha_fin    DATE,
        h_req        DECIMAL(8,2),
        h_tom        DECIMAL(8,2),
        h_pend       DECIMAL(8,2),
        h_tom_t      DECIMAL(8,2),
        institucion  VARCHAR(100)
    END RECORD
    DEFINE fecha_rep  CHAR(10),
           hora       CHAR(8),
           fini, ffin CHAR(10),
           g_req, g_tom, g_pend, g_cumpl DECIMAL(10,2),
           t_req, t_tom, t_pend, t_cumpl DECIMAL(10,2),
           t_emp      INTEGER

    ORDER EXTERNAL BY x.num_emp

    FORMAT
        FIRST PAGE HEADER
            LET fecha_rep = TODAY USING "dd/mm/yyyy"
            LET hora      = CURRENT HOUR TO SECOND
            LET t_emp     = 0
            PRINTX p_companias.nombre, fecha_rep, hora, filtros

        BEFORE GROUP OF x.num_emp
            LET t_emp = t_emp + 1
            PRINTX x.num_emp, x.empleado, x.nom_dpto, x.nom_puesto

        ON EVERY ROW
            LET fini = x.fecha_ini USING "dd/mm/yyyy"
            LET ffin = x.fecha_fin USING "dd/mm/yyyy"
            PRINTX x.fila, x.nom_cap, x.tipo_evento, fini, ffin,
                   x.h_req, x.h_tom, x.h_pend, x.institucion

        AFTER GROUP OF x.num_emp
            LET g_req  = GROUP SUM(x.h_req)
            LET g_tom  = GROUP SUM(x.h_tom_t)
            LET g_pend = GROUP SUM(x.h_pend)
            IF g_req  IS NULL THEN LET g_req  = 0 END IF
            IF g_tom  IS NULL THEN LET g_tom  = 0 END IF
            IF g_pend IS NULL THEN LET g_pend = 0 END IF
            LET g_cumpl = pct48(g_req - g_pend, g_req)
            PRINTX g_req, g_tom, g_pend, g_cumpl

        ON LAST ROW
            LET t_req  = SUM(x.h_req)
            LET t_tom  = SUM(x.h_tom_t)
            LET t_pend = SUM(x.h_pend)
            IF t_req  IS NULL THEN LET t_req  = 0 END IF
            IF t_tom  IS NULL THEN LET t_tom  = 0 END IF
            IF t_pend IS NULL THEN LET t_pend = 0 END IF
            LET t_cumpl = pct48(t_req - t_pend, t_req)
            PRINTX t_emp, t_req, t_tom, t_pend, t_cumpl
END REPORT