957 lines
35 KiB
PHP
957 lines
35 KiB
PHP
<?php
|
|
require_once __DIR__.'/auth_guard.php';
|
|
require_once("../config.php");
|
|
require_once("../runtime/encrypt.php");
|
|
|
|
dbConnect();
|
|
global $link;
|
|
$return = new stdClass();
|
|
|
|
$request = $_REQUEST;
|
|
$fechainicio = $_GET['fechaini'];
|
|
$fechafin = $_GET['fechafin'];
|
|
$estado = $_GET['estado'];
|
|
|
|
|
|
if ($fechainicio == '' && $fechafin == '' && $estado == 'all' ) {
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( scormvars.fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
}else if ($fechainicio == '' && $fechafin == '' && $estado != 'all') {
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
AND user.habilitado = $estado
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
}else if ($fechainicio != '' && $fechafin != '' && $estado == 'all') {
|
|
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
|
|
|
|
|
|
}else if ($fechainicio != '' && $fechafin == '' && $estado == 'all') {
|
|
$fechafin = date('Y-m-d');
|
|
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
|
|
|
|
}else if ($fechainicio == '' && $fechafin != '' && $estado == 'all') {
|
|
$fechainicio = '2020-07-01';
|
|
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
}else if ($fechainicio != '' && $fechafin != '' && $estado != 'all') {
|
|
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
|
AND user.habilitado = $estado
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
}else if($fechainicio != '' && $fechafin == '' && $estado != 'all'){
|
|
$fechafin = date('Y-m-d');
|
|
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
ANS scormpkg.habilitado = 1
|
|
AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
|
AND user.habilitado = $estado
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
}elseif($fechainicio == '' && $fechafin != '' && $estado != 'all'){
|
|
$fechainicio = '2020-07-01';
|
|
|
|
$sqlusers = "SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
rs1.fecha,
|
|
rs1.estado_empleado,
|
|
rs1.numempl,
|
|
rs1.nombre,
|
|
rs1.apellidos,
|
|
rs1.posicion,
|
|
rs1.sociedad,
|
|
rs1.division,
|
|
rs1.unidad_organizativa,
|
|
rs1.ceco,
|
|
rs1.nomdistrito,
|
|
rs1.nomcentro,
|
|
rs1.supervisor,
|
|
c.nombre as nomcat,
|
|
CASE
|
|
WHEN ( COUNT( rs1.cat) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado1
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre as nombre,
|
|
user.apellido as apellidos,
|
|
user.distritonom AS nomcentro,
|
|
user.centrotrabajonom AS nomdistrito,
|
|
user.posicion,
|
|
user.sociedad,
|
|
user.division,
|
|
user.unidad_organizativa,
|
|
user.ceco,
|
|
user.supervisor,
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
|
categoria.padre as cat,
|
|
categoria.nombre AS nomcurso,
|
|
CASE
|
|
|
|
WHEN Round( SUM( scormvars.varValue ), 2 ) >= 0 THEN
|
|
ROUND(
|
|
(
|
|
Round( SUM( scormvars.varValue ), 2 ) / ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id )
|
|
),
|
|
2
|
|
) ELSE '--'
|
|
END AS calificacion,
|
|
CASE
|
|
|
|
WHEN ( COUNT( scormvars.varValue ) = ( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1) ) THEN
|
|
'Completado' ELSE 'No Completado'
|
|
END AS estado,
|
|
CASE
|
|
WHEN scormvars.numempl <> '' THEN
|
|
CONCAT(
|
|
COUNT( scormvars.varValue ),
|
|
' de ',
|
|
( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id AND scormpkg.habilitado = 1)
|
|
)
|
|
END AS visitado,
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha
|
|
FROM
|
|
scormvars
|
|
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
|
|
INNER JOIN categoria ON scormpkg.idcategoria = categoria.id
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
|
WHERE
|
|
scormvars.varName = 'cmi.core.lesson_status'
|
|
AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
|
|
AND scormvars.numempl <> ''
|
|
AND scormpkg.habilitado = 1
|
|
AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
|
AND user.habilitado = $estado
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
ORDER BY
|
|
scormpkg.idcategoria ASC
|
|
) AS rs1
|
|
INNER JOIN categoria as c ON c.id = rs1.cat
|
|
WHERE rs1.estado = 'Completado'
|
|
GROUP BY rs1.numempl,rs1.cat
|
|
ORDER BY c.nombre,rs1.cat
|
|
) AS rs2
|
|
";
|
|
|
|
|
|
}else{
|
|
$sqlusers = "SELECT * FROM viewReporteCategorias;";
|
|
}
|
|
|
|
|
|
//echo $sqlusers;
|
|
|
|
|
|
|
|
if(!empty($request['search']['value'])){
|
|
$sqlusers.=" WHERE (rs2.numempl Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.nombre Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.apellido Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.email Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.centrotrabajonom Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.distritonom Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.posicion Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.sociedad Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.division Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.unidad_organizativa Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR rs2.ceco Like '".$request['search']['value']."%' ";
|
|
$sqlusers .= ") ORDER BY rs2.numempl ASC";
|
|
}
|
|
|
|
|
|
|
|
//Order
|
|
$col = [];
|
|
$col[0] = 'fecha';
|
|
$col[1] = 'estado_empleado';
|
|
$col[2] = 'numempl';
|
|
$col[3] = 'nomempl';
|
|
$col[4] = 'apellidos';
|
|
$col[5] = 'posicion';
|
|
$col[6] = 'sociedad';
|
|
$col[7] = 'division';
|
|
$col[8] = 'unidad_organizativa';
|
|
$col[9] = 'ceco';
|
|
$col[10] = 'distritonom';
|
|
$col[11] = 'centrotrabajonom';
|
|
$col[12] = 'supervisor';
|
|
|
|
|
|
|
|
$sqltotal = $sqlusers;
|
|
|
|
//$sqlusers .=" ORDER BY rs2.". $col[$request['order'][0]['column']]." ".$request['order'][0]['dir'];
|
|
$sqltotal .= " LIMIT ". $request['start']." ,".$request['length'];
|
|
|
|
|
|
|
|
//echo $sqlusers;
|
|
|
|
$resultusers = mysqli_query($link, $sqlusers);
|
|
$resulttotal = mysqli_query($link, $sqltotal);
|
|
$datos = [''];
|
|
|
|
|
|
if( $resultusers ){
|
|
if (mysqli_num_rows($resultusers) <= 0 ) {
|
|
$data = [];
|
|
}else{
|
|
mysqli_data_seek($resultusers, 0);
|
|
while ($row = mysqli_fetch_assoc ($resultusers)){
|
|
$data[] = $row;
|
|
}
|
|
}
|
|
}
|
|
|
|
$sqlcolumns = "SELECT
|
|
categoria.id,
|
|
categoria.clave,
|
|
categoria.nombre
|
|
FROM
|
|
categoria
|
|
WHERE
|
|
padre = 0 AND habilitado = 1
|
|
ORDER BY categoria.id";
|
|
|
|
|
|
|
|
$resultcolumn= mysqli_query($link, $sqlcolumns);
|
|
|
|
if( $resultcolumn ){
|
|
mysqli_data_seek($resultcolumn, 0);
|
|
while ($row = mysqli_fetch_assoc ($resultcolumn)){
|
|
$categorias[] = $row;
|
|
}
|
|
}
|
|
|
|
|
|
$columnasvisibles= array(
|
|
'Estado del empleado',
|
|
'Número de empleado',
|
|
'Nombres(s)',
|
|
'Apellidos',
|
|
'Posición',
|
|
'Sociedad',
|
|
'División',
|
|
'Unidad organizativa',
|
|
'CeCo',
|
|
'Centro de trabajo',
|
|
'Distrito',
|
|
'Supervisor'
|
|
);
|
|
|
|
foreach ($categorias as $key2 => $cat) {
|
|
array_push($columnasvisibles, $cat['nombre']);
|
|
}
|
|
|
|
for ($i=0; $i < sizeof($columnasvisibles) ; $i++) {
|
|
$columnasvisibles[$i] = array('title' => $columnasvisibles[$i]);
|
|
}
|
|
|
|
$return->columnasvisibles = $columnasvisibles;
|
|
|
|
|
|
|
|
if (!empty($data)) {
|
|
$anterior = '';
|
|
$users = 0;
|
|
foreach ($data as $key1 => $col) {
|
|
//print_r($col);
|
|
//echo $col[2]."<br>";
|
|
if ($col['numempl'] == $anterior) {
|
|
//echo $col['numempl']." igual - ".$col['nomcat']."- ".$users." <br>";
|
|
$datos[($users-1)][$col['nomcat']] = $col['estado1'];
|
|
}else{
|
|
//echo $col['numempl']." nuevo - ".$col['nomcat']." - ".$users." <br>";
|
|
|
|
$datos[$users] = ['estado_empleado' => $col['estado_empleado'],'numempl' => $col['numempl'],'nombre' => $col['nombre'],'apellidos' =>$col['apellidos'],'posicion'=>$col['posicion'],'sociedad'=> $col['sociedad'],'division'=> $col['division'],'unidad_organizativa' => $col['unidad_organizativa'],'ceco'=>$col['ceco'],'nomdistrito'=>$col['nomdistrito'], 'nomcentro' => $col['nomcentro'],'supervisor'=>$col['supervisor']];
|
|
|
|
foreach ($categorias as $key2 => $cat) {
|
|
if ($cat['nombre'] == $col['nomcat']) {
|
|
$datos[$users][$cat['nombre']] = $col['estado1'];;
|
|
}else{
|
|
$datos[$users][$cat['nombre']] = 'No completado';
|
|
}
|
|
}
|
|
$users++;
|
|
}
|
|
$anterior = $col['numempl'];
|
|
}
|
|
|
|
|
|
//print_r($datos[0]);
|
|
foreach ($datos as $col => $columnas) {
|
|
$fill = [];
|
|
foreach ($columnas as $key => $value) {
|
|
array_push($fill, $value);
|
|
}
|
|
$datos[$col] = $fill;
|
|
}
|
|
}else{
|
|
$datos = '';
|
|
}
|
|
|
|
|
|
$query = $sqlusers;
|
|
|
|
function RemoveSpecialChar($str)
|
|
{
|
|
$res = preg_replace("/\r?\n/", " ", $str);
|
|
$res = preg_replace("/LIMIT 0 ,10/", " ", $res);
|
|
|
|
return $res;
|
|
}
|
|
|
|
if (!isset($datos)) {
|
|
$str = $query;
|
|
$str1 = RemoveSpecialChar($str);
|
|
$query = $str1;
|
|
$query = encrypt_decrypt('encrypt', $query);
|
|
|
|
$totalData = sizeof($datos);
|
|
$totalFilter = sizeof($datos);
|
|
}else{
|
|
$str = $query;
|
|
$str1 = RemoveSpecialChar($str);
|
|
$query = $str1;
|
|
$query = encrypt_decrypt('encrypt', $query);
|
|
|
|
$totalData = 0;
|
|
$totalFilter = 0;
|
|
}
|
|
|
|
|
|
$json_data=array(
|
|
"draw" => intval($request['draw']),
|
|
"recordsTotal" => intval($totalFilter),
|
|
"recordsFiltered" => intval($totalData),
|
|
"data" => $datos,
|
|
"query" => $query
|
|
);
|
|
echo json_encode($json_data);
|
|
|
|
?>
|