576 lines
20 KiB
PHP
576 lines
20 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'];
|
||
|
|
|
||
|
|
|
||
|
|
$fechafin = date('Y-m-d', strtotime($fechafin."+ 2 days") );
|
||
|
|
|
||
|
|
if ($fechainicio == '' && $fechafin == '' && $estado == 'all' ) {
|
||
|
|
$sqlusers = "SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> ''
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2";
|
||
|
|
}else if ($fechainicio == '' && $fechafin == '' && $estado != 'all') {
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
user.creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = $estado
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}else if ($fechainicio != '' && $fechafin != '' && $estado == 'all') {
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}else if ($fechainicio != '' && $fechafin == '' && $estado == 'all') {
|
||
|
|
$fechafin = date('Y-m-d');
|
||
|
|
$fechafin = date('Y-m-d', strtotime($fechafin."+ 2 days") );
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}else if ($fechainicio == '' && $fechafin != '' && $estado == 'all') {
|
||
|
|
$fechainicio = '2020-07-01';
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}else if ($fechainicio != '' && $fechafin != '' && $estado != 'all') {
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = '$estado' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}else if($fechainicio != '' && $fechafin == '' && $estado != 'all'){
|
||
|
|
$fechafin = date('Y-m-d');
|
||
|
|
$fechafin = date('Y-m-d', strtotime($fechafin."+ 2 days") );
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = '$estado' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}elseif($fechainicio == '' && $fechafin != '' && $estado != 'all'){
|
||
|
|
$fechainicio = '2020-07-01';
|
||
|
|
$sqlusers = "
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
rs1.fecha,
|
||
|
|
rs1.estado_empleado,
|
||
|
|
rs1.user_creado,
|
||
|
|
rs1.numempl,
|
||
|
|
rs1.nomempl,
|
||
|
|
rs1.apellidos,
|
||
|
|
rs1.posicion,
|
||
|
|
rs1.sociedad,
|
||
|
|
rs1.division,
|
||
|
|
rs1.unidad_organizativa,
|
||
|
|
rs1.ceco,
|
||
|
|
rs1.nomdistrito,
|
||
|
|
rs1.nomcentro,
|
||
|
|
rs1.supervisor,
|
||
|
|
c.nombre as categoria,
|
||
|
|
rs1.tema,
|
||
|
|
rs1.subtema,
|
||
|
|
rs1.estado
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
|
||
|
|
IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
|
||
|
|
scormvars.numempl AS numempl,
|
||
|
|
user.nombre AS nomempl,
|
||
|
|
user.apellido as apellidos,
|
||
|
|
user.posicion,
|
||
|
|
user.sociedad,
|
||
|
|
user.division,
|
||
|
|
user.unidad_organizativa,
|
||
|
|
user.ceco,
|
||
|
|
user.distritonom AS nomdistrito,
|
||
|
|
user.centrotrabajonom AS nomcentro,
|
||
|
|
user.supervisor,
|
||
|
|
DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
|
||
|
|
categoria.padre as categoria,
|
||
|
|
categoria.nombre AS tema,
|
||
|
|
scormpkg.nombre as subtema,
|
||
|
|
IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
|
||
|
|
FROM
|
||
|
|
categoria
|
||
|
|
INNER JOIN scormpkg ON scormpkg.idcategoria = categoria.id
|
||
|
|
INNER JOIN scormvars ON scormpkg.id = scormvars.SCOInstanceID
|
||
|
|
INNER JOIN user ON user.numempl = scormvars.numempl
|
||
|
|
WHERE
|
||
|
|
scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = '$estado' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
|
||
|
|
) AS rs1
|
||
|
|
INNER JOIN categoria as c ON c.id = rs1.categoria
|
||
|
|
WHERE c.id = rs1.categoria
|
||
|
|
) as rs2
|
||
|
|
";
|
||
|
|
}else{
|
||
|
|
$sqlusers = "SELECT * FROM viewReporteGeneral;";
|
||
|
|
}
|
||
|
|
|
||
|
|
//echo $sqlusers;
|
||
|
|
|
||
|
|
|
||
|
|
if(!empty($request['search']['value'])){
|
||
|
|
$sqlusers.=" WHERE (rs2.numempl Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers.=" OR rs2.nomempl Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers.=" OR rs2.apellidos Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers.=" OR rs2.nomcentro Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers.=" OR rs2.nomdistrito 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.=" OR rs2.tema Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers.=" OR rs2.subtema Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers.=" OR rs2.estado Like '".$request['search']['value']."%' ";
|
||
|
|
$sqlusers .= ")";
|
||
|
|
}
|
||
|
|
|
||
|
|
$totalrs = $sqlusers;
|
||
|
|
$totales = mysqli_query($link, $totalrs);
|
||
|
|
|
||
|
|
|
||
|
|
//Order
|
||
|
|
$col = [];
|
||
|
|
$col[0] = 'fecha';
|
||
|
|
$col[1] = 'estado_empleado';
|
||
|
|
$col[2] = 'user_creado';
|
||
|
|
$col[3] = 'numempl';
|
||
|
|
$col[4] = 'nomempl';
|
||
|
|
$col[5] = 'apellidos';
|
||
|
|
$col[6] = 'posicion';
|
||
|
|
$col[7] = 'sociedad';
|
||
|
|
$col[8] = 'division';
|
||
|
|
$col[9] = 'unidad_organizativa';
|
||
|
|
$col[10] = 'ceco';
|
||
|
|
$col[11] = 'distritonom';
|
||
|
|
$col[12] = 'centrotrabajonom';
|
||
|
|
$col[13] = 'supervisor';
|
||
|
|
$col[14] = 'categoria';
|
||
|
|
$col[15] = 'tema';
|
||
|
|
$col[16] = 'subtema';
|
||
|
|
$col[17] = 'estado';
|
||
|
|
|
||
|
|
$sqlusers .=" ORDER BY rs2.". $col[$request['order'][0]['column']]." ".$request['order'][0]['dir'];
|
||
|
|
$sqlusers .= " LIMIT ". $request['start']." ,".$request['length'];
|
||
|
|
|
||
|
|
|
||
|
|
//echo $sqlusers;
|
||
|
|
$query = $sqlusers;
|
||
|
|
|
||
|
|
function RemoveSpecialChar($str)
|
||
|
|
{
|
||
|
|
$res = preg_replace("/\r?\n/", " ", $str);
|
||
|
|
$res = preg_replace("/LIMIT 0 ,10/", " ", $res);
|
||
|
|
|
||
|
|
return $res;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
$str = $query;
|
||
|
|
$str1 = RemoveSpecialChar($str);
|
||
|
|
$query = $str1;
|
||
|
|
$query = encrypt_decrypt('encrypt', $query);
|
||
|
|
|
||
|
|
$resultusers = mysqli_query($link, $sqlusers);
|
||
|
|
|
||
|
|
$totalData = mysqli_num_rows($totales);
|
||
|
|
$resultados = [];
|
||
|
|
if( $resultusers ){
|
||
|
|
while ($fila = mysqli_fetch_array ($resultusers, MYSQLI_NUM)) {
|
||
|
|
$resultados[] = $fila;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$totalFilter = sizeof($resultados);
|
||
|
|
$json_data=array(
|
||
|
|
"draw" => intval($request['draw']),
|
||
|
|
"recordsTotal" => intval($totalFilter),
|
||
|
|
"recordsFiltered" => intval($totalData),
|
||
|
|
"data" => $resultados,
|
||
|
|
"query" => $query
|
||
|
|
);
|
||
|
|
echo json_encode($json_data);
|
||
|
|
|
||
|
|
?>
|