277 lines
10 KiB
Plaintext
277 lines
10 KiB
Plaintext
{
|
|
-------------------------------------------------------------------------------
|
|
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
|