355 lines
12 KiB
Plaintext
355 lines
12 KiB
Plaintext
{
|
|
-------------------------------------------------------------------------------
|
|
PROGRAMA : ADPRRP049
|
|
OBJETIVO : Rotacion laboral (indicador RRHH).
|
|
R = Resumen mensual: plantilla al inicio del mes,
|
|
ingresos, salidas (voluntarias / involuntarias /
|
|
otras) y % de rotacion; total y rotacion anual.
|
|
D = Detalle de salidas agrupado por causa.
|
|
Datos: vista vw_rotacion_mensual y adtb00062/adtb00061
|
|
(desvinculaciones registradas por ADPRMT017; historico
|
|
2022+ cargado con origen 'H').
|
|
Rotacion % = salidas que cuentan para rotacion /
|
|
plantilla al inicio (los traslados dentro del grupo
|
|
no cuentan). Anual = salidas del ano / plantilla
|
|
promedio de los meses del ano.
|
|
FECHA REALIZACION : Octubre 05, 2026
|
|
-------------------------------------------------------------------------------
|
|
}
|
|
GLOBALS "adprgb000.4gl"
|
|
|
|
DEFINE p RECORD
|
|
ano_ini SMALLINT,
|
|
ano_fin SMALLINT,
|
|
departamento SMALLINT,
|
|
tipo_emp VARCHAR(10),
|
|
formato 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 adprrp049()
|
|
END MAIN
|
|
|
|
FUNCTION adprrp049()
|
|
DEFINE cb ui.ComboBox,
|
|
xdep SMALLINT,
|
|
xnom VARCHAR(40)
|
|
|
|
OPTIONS
|
|
FORM LINE 8,
|
|
ERROR LINE 23,
|
|
COMMENT LINE 21
|
|
|
|
OPEN FORM adfmrp049 FROM "adfmrp049"
|
|
DISPLAY FORM adfmrp049
|
|
|
|
LET cb = ui.ComboBox.forName("formonly.departamento")
|
|
CALL cb.clear()
|
|
CALL cb.addItem(0, "TODOS")
|
|
DECLARE c_dep49 CURSOR FOR
|
|
SELECT a.departamento, a.nom_dpto FROM adtb00001 a
|
|
WHERE a.status_t IS NULL ORDER BY a.nom_dpto
|
|
FOREACH c_dep49 INTO xdep, xnom
|
|
CALL cb.addItem(xdep, xnom CLIPPED)
|
|
END FOREACH
|
|
|
|
LET p.ano_ini = YEAR(TODAY) - 1
|
|
LET p.ano_fin = YEAR(TODAY)
|
|
LET p.departamento = 0
|
|
LET p.tipo_emp = "TODOS"
|
|
LET p.formato = "R"
|
|
|
|
INPUT BY NAME p.* WITHOUT DEFAULTS ATTRIBUTE(UNBUFFERED)
|
|
AFTER INPUT
|
|
IF int_flag THEN
|
|
EXIT INPUT
|
|
END IF
|
|
IF p.ano_ini IS NULL OR p.ano_fin IS NULL OR p.ano_fin < p.ano_ini
|
|
OR p.ano_ini < 2022 THEN
|
|
CALL fgl_winmessage("INFO","REVISE LOS ANOS (EL HISTORICO EMPIEZA EN 2022)","info")
|
|
NEXT FIELD ano_ini
|
|
END IF
|
|
END INPUT
|
|
IF int_flag THEN
|
|
LET int_flag = FALSE
|
|
CALL msg(2)
|
|
RETURN
|
|
END IF
|
|
|
|
LET filtros = "Anos: ", p.ano_ini USING "<<<<", " a ", p.ano_fin USING "<<<<"
|
|
IF p.departamento IS NOT NULL AND p.departamento <> 0 THEN
|
|
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
|
|
LET filtros = filtros CLIPPED, " Tipo de empleado: ", p.tipo_emp CLIPPED
|
|
|
|
IF p.formato = "D" THEN
|
|
LET r_filename = "adprrp049d.4rp"
|
|
ELSE
|
|
LET r_filename = "adprrp049.4rp"
|
|
END IF
|
|
LET preview = TRUE
|
|
IF NOT fgl_report_loadCurrentSettings(r_filename) THEN
|
|
CALL fgl_winmessage("INFO","No se pudo cargar el diseno " || r_filename CLIPPED,"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
|
|
|
|
IF p.formato = "D" THEN
|
|
CALL detalle_salidas()
|
|
ELSE
|
|
CALL resumen_mensual()
|
|
END IF
|
|
END FUNCTION
|
|
|
|
## R: UNA FILA POR MES (SUMA DE LOS DEPARTAMENTOS / TIPOS FILTRADOS)
|
|
FUNCTION resumen_mensual()
|
|
DEFINE x RECORD
|
|
anio SMALLINT,
|
|
mes_num SMALLINT,
|
|
mes_nom VARCHAR(12),
|
|
plantilla INTEGER,
|
|
ingresos INTEGER,
|
|
salidas INTEGER,
|
|
salidas_rot INTEGER,
|
|
voluntarias INTEGER,
|
|
involuntarias INTEGER,
|
|
otras INTEGER,
|
|
rot_pct DECIMAL(8,2)
|
|
END RECORD
|
|
DEFINE n INTEGER
|
|
|
|
LET selec =
|
|
"SELECT anio, mes_num, SUM(plantilla_inicio), SUM(ingresos), SUM(salidas), ",
|
|
" SUM(salidas_rotacion), SUM(voluntarias), SUM(involuntarias), SUM(otras) ",
|
|
"FROM vw_rotacion_mensual WHERE anio BETWEEN ? AND ? "
|
|
IF p.departamento IS NOT NULL AND p.departamento <> 0 THEN
|
|
LET selec = selec, " AND departamento = ", p.departamento USING "<<<<&"
|
|
END IF
|
|
IF p.tipo_emp <> "TODOS" THEN
|
|
LET selec = selec, " AND tipo_empleado = '", p.tipo_emp CLIPPED, "'"
|
|
END IF
|
|
LET selec = selec, " GROUP BY anio, mes_num ORDER BY anio, mes_num"
|
|
|
|
PREPARE p_res49 FROM selec
|
|
DECLARE c_res49 CURSOR FOR p_res49
|
|
|
|
LET n = 0
|
|
START REPORT rotacion TO XML HANDLER handler
|
|
FOREACH c_res49 USING p.ano_ini, p.ano_fin
|
|
INTO x.anio, x.mes_num, x.plantilla, x.ingresos, x.salidas,
|
|
x.salidas_rot, x.voluntarias, x.involuntarias, x.otras
|
|
LET n = n + 1
|
|
LET x.mes_nom = nombre_mes(x.mes_num)
|
|
LET x.rot_pct = pct49(x.salidas_rot, x.plantilla)
|
|
OUTPUT TO REPORT rotacion(x.*)
|
|
END FOREACH
|
|
FINISH REPORT rotacion
|
|
IF n = 0 THEN
|
|
CALL fgl_winmessage("INFO","No hay datos con esos parametros","info")
|
|
END IF
|
|
END FUNCTION
|
|
|
|
## D: DETALLE DE SALIDAS AGRUPADO POR CAUSA
|
|
FUNCTION detalle_salidas()
|
|
DEFINE y RECORD
|
|
clase VARCHAR(15),
|
|
cod_causa SMALLINT,
|
|
desc_causa VARCHAR(60),
|
|
fecha_sal DATE,
|
|
num_emp INTEGER,
|
|
empleado VARCHAR(60),
|
|
nom_dpto VARCHAR(40),
|
|
nom_puesto VARCHAR(40),
|
|
tipo_emp VARCHAR(20),
|
|
meses INTEGER,
|
|
recont VARCHAR(3),
|
|
detalle VARCHAR(200)
|
|
END RECORD
|
|
DEFINE n INTEGER
|
|
|
|
LET selec =
|
|
"SELECT CASE c.clase WHEN 'V' THEN 'VOLUNTARIA' WHEN 'I' THEN 'INVOLUNTARIA' ELSE 'OTRA' END, ",
|
|
" c.cod_causa, c.desc_causa, d.fecha_salida, d.num_emp, ",
|
|
" RTRIM(e.nom1_emp)+' '+RTRIM(e.apell1_emp)+ISNULL(' '+RTRIM(e.apell2_emp),''), ",
|
|
" ISNULL(dp.nom_dpto,''), ISNULL(pu.nom_puesto,''), ISNULL(d.tipo_empleado,'NORMAL'), ",
|
|
" CASE WHEN d.fech_ingreso IS NULL THEN NULL ELSE DATEDIFF(month, d.fech_ingreso, d.fecha_salida) END, ",
|
|
" CASE d.recontratable WHEN 'S' THEN 'SI' WHEN 'N' THEN 'NO' ELSE '' END, ",
|
|
" ISNULL(d.detalle,'') ",
|
|
"FROM adtb00062 d ",
|
|
" JOIN adtb00061 c ON c.cod_causa = d.cod_causa ",
|
|
" JOIN adtb00003 e ON e.num_emp = d.num_emp ",
|
|
" LEFT JOIN adtb00001 dp ON dp.departamento = d.departamento ",
|
|
" LEFT JOIN adtb00004 pu ON pu.cod_puesto = d.cod_puesto ",
|
|
"WHERE d.status_t IS NULL AND YEAR(d.fecha_salida) BETWEEN ? AND ? "
|
|
IF p.departamento IS NOT NULL AND p.departamento <> 0 THEN
|
|
LET selec = selec, " AND d.departamento = ", p.departamento USING "<<<<&"
|
|
END IF
|
|
IF p.tipo_emp <> "TODOS" THEN
|
|
LET selec = selec, " AND ISNULL(d.tipo_empleado,'NORMAL') = '", p.tipo_emp CLIPPED, "'"
|
|
END IF
|
|
LET selec = selec,
|
|
" ORDER BY CASE c.clase WHEN 'V' THEN 1 WHEN 'I' THEN 2 ELSE 3 END, c.cod_causa, d.fecha_salida"
|
|
|
|
PREPARE p_det49 FROM selec
|
|
DECLARE c_det49 CURSOR FOR p_det49
|
|
|
|
LET n = 0
|
|
START REPORT salidas TO XML HANDLER handler
|
|
FOREACH c_det49 USING p.ano_ini, p.ano_fin INTO y.*
|
|
LET n = n + 1
|
|
OUTPUT TO REPORT salidas(y.*)
|
|
END FOREACH
|
|
FINISH REPORT salidas
|
|
IF n = 0 THEN
|
|
CALL fgl_winmessage("INFO","No hay salidas con esos parametros","info")
|
|
END IF
|
|
END FUNCTION
|
|
|
|
FUNCTION nombre_mes(m)
|
|
DEFINE m SMALLINT
|
|
CASE m
|
|
WHEN 1 RETURN "ENERO"
|
|
WHEN 2 RETURN "FEBRERO"
|
|
WHEN 3 RETURN "MARZO"
|
|
WHEN 4 RETURN "ABRIL"
|
|
WHEN 5 RETURN "MAYO"
|
|
WHEN 6 RETURN "JUNIO"
|
|
WHEN 7 RETURN "JULIO"
|
|
WHEN 8 RETURN "AGOSTO"
|
|
WHEN 9 RETURN "SEPTIEMBRE"
|
|
WHEN 10 RETURN "OCTUBRE"
|
|
WHEN 11 RETURN "NOVIEMBRE"
|
|
WHEN 12 RETURN "DICIEMBRE"
|
|
END CASE
|
|
RETURN NULL
|
|
END FUNCTION
|
|
|
|
FUNCTION pct49(a, b)
|
|
DEFINE a, b DECIMAL(12,2)
|
|
IF b IS NULL OR b = 0 OR a IS NULL THEN
|
|
RETURN 0
|
|
END IF
|
|
RETURN (a / b) * 100
|
|
END FUNCTION
|
|
|
|
REPORT rotacion(x)
|
|
DEFINE x RECORD
|
|
anio SMALLINT,
|
|
mes_num SMALLINT,
|
|
mes_nom VARCHAR(12),
|
|
plantilla INTEGER,
|
|
ingresos INTEGER,
|
|
salidas INTEGER,
|
|
salidas_rot INTEGER,
|
|
voluntarias INTEGER,
|
|
involuntarias INTEGER,
|
|
otras INTEGER,
|
|
rot_pct DECIMAL(8,2)
|
|
END RECORD
|
|
DEFINE fecha_rep CHAR(10),
|
|
hora CHAR(8),
|
|
a_plant DECIMAL(10,2),
|
|
a_ing, a_sal, a_rot, a_vol, a_inv, a_otr INTEGER,
|
|
a_pct DECIMAL(8,2),
|
|
t_ing, t_sal, t_vol, t_inv, t_otr INTEGER
|
|
|
|
ORDER EXTERNAL BY x.anio
|
|
|
|
FORMAT
|
|
FIRST PAGE HEADER
|
|
LET fecha_rep = TODAY USING "dd/mm/yyyy"
|
|
LET hora = CURRENT HOUR TO SECOND
|
|
PRINTX p_companias.nombre, fecha_rep, hora, filtros
|
|
|
|
BEFORE GROUP OF x.anio
|
|
PRINTX x.anio
|
|
|
|
ON EVERY ROW
|
|
PRINTX x.mes_nom, x.plantilla, x.ingresos, x.salidas, x.voluntarias,
|
|
x.involuntarias, x.otras, x.rot_pct
|
|
|
|
AFTER GROUP OF x.anio
|
|
LET a_plant = GROUP AVG(x.plantilla)
|
|
LET a_ing = GROUP SUM(x.ingresos)
|
|
LET a_sal = GROUP SUM(x.salidas)
|
|
LET a_rot = GROUP SUM(x.salidas_rot)
|
|
LET a_vol = GROUP SUM(x.voluntarias)
|
|
LET a_inv = GROUP SUM(x.involuntarias)
|
|
LET a_otr = GROUP SUM(x.otras)
|
|
LET a_pct = pct49(a_rot, a_plant)
|
|
PRINTX a_plant, a_ing, a_sal, a_vol, a_inv, a_otr, a_pct
|
|
|
|
ON LAST ROW
|
|
LET t_ing = SUM(x.ingresos)
|
|
LET t_sal = SUM(x.salidas)
|
|
LET t_vol = SUM(x.voluntarias)
|
|
LET t_inv = SUM(x.involuntarias)
|
|
LET t_otr = SUM(x.otras)
|
|
PRINTX t_ing, t_sal, t_vol, t_inv, t_otr
|
|
END REPORT
|
|
|
|
REPORT salidas(y)
|
|
DEFINE y RECORD
|
|
clase VARCHAR(15),
|
|
cod_causa SMALLINT,
|
|
desc_causa VARCHAR(60),
|
|
fecha_sal DATE,
|
|
num_emp INTEGER,
|
|
empleado VARCHAR(60),
|
|
nom_dpto VARCHAR(40),
|
|
nom_puesto VARCHAR(40),
|
|
tipo_emp VARCHAR(20),
|
|
meses INTEGER,
|
|
recont VARCHAR(3),
|
|
detalle VARCHAR(200)
|
|
END RECORD
|
|
DEFINE fecha_rep CHAR(10),
|
|
hora CHAR(8),
|
|
fsal CHAR(10),
|
|
g_cant, t_cant INTEGER
|
|
|
|
ORDER EXTERNAL BY y.cod_causa
|
|
|
|
FORMAT
|
|
FIRST PAGE HEADER
|
|
LET fecha_rep = TODAY USING "dd/mm/yyyy"
|
|
LET hora = CURRENT HOUR TO SECOND
|
|
PRINTX p_companias.nombre, fecha_rep, hora, filtros
|
|
|
|
BEFORE GROUP OF y.cod_causa
|
|
PRINTX y.desc_causa, y.clase
|
|
|
|
ON EVERY ROW
|
|
LET fsal = y.fecha_sal USING "dd/mm/yyyy"
|
|
PRINTX fsal, y.num_emp, y.empleado, y.nom_dpto, y.nom_puesto,
|
|
y.tipo_emp, y.meses, y.recont, y.detalle
|
|
|
|
AFTER GROUP OF y.cod_causa
|
|
LET g_cant = GROUP COUNT(*)
|
|
PRINTX g_cant
|
|
|
|
ON LAST ROW
|
|
LET t_cant = COUNT(*)
|
|
PRINTX t_cant
|
|
END REPORT
|