125 lines
3.8 KiB
PHP
125 lines
3.8 KiB
PHP
<?php
|
|
require_once __DIR__.'/auth_guard.php';
|
|
require_once("../config.php");
|
|
ini_set('display_errors', 1);
|
|
ini_set('display_startup_errors', 1);
|
|
error_reporting(E_ALL);
|
|
$request = $_REQUEST;
|
|
dbConnect();
|
|
global $link;
|
|
$sqlusers = "
|
|
SELECT
|
|
*
|
|
FROM
|
|
(
|
|
SELECT
|
|
scormvars.numempl AS numempl,
|
|
user.nombre,
|
|
user.apellido as apellidos,
|
|
centrotrabajo.nombrecentro AS nomcentro,
|
|
distrito.nombredistrito AS nomdistrito,
|
|
categoria.clave AS clave,
|
|
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 ) ) 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 )
|
|
)
|
|
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
|
|
INNER JOIN centrotrabajo ON centrotrabajo.centroid = user.centrotrabajoid
|
|
INNER JOIN distrito ON distrito.distritoid = user.distritoid
|
|
WHERE
|
|
user.habilitado = 1
|
|
AND
|
|
scormvars.varName = 'cmi.core.score.raw'
|
|
AND scormvars.numempl <> ''
|
|
GROUP BY
|
|
scormvars.numempl,
|
|
categoria.clave
|
|
HAVING
|
|
max( fecha )
|
|
|
|
) AS rs1
|
|
";
|
|
|
|
|
|
$totalrs = $sqlusers;
|
|
$totales = mysqli_query($link, $totalrs);
|
|
|
|
if(!empty($request['search']['value'])){
|
|
$sqlusers.=" WHERE (numempl Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR nombre Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR apellidos Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR nomcentro Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR nomdistrito Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR clave Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR nomcurso Like '%".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR calificacion Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR estado Like '".$request['search']['value']."%' ";
|
|
$sqlusers.=" OR fecha Like '".$request['search']['value']."%' ";
|
|
$sqlusers .= " )";
|
|
}
|
|
|
|
|
|
|
|
//Order
|
|
$col = [];
|
|
$col[0] = 'numempl';
|
|
$col[1] = 'nombre';
|
|
$col[2] = 'apellidos';
|
|
$col[3] = 'nomcentro';
|
|
$col[4] = 'nomdistrito';
|
|
$col[5] = 'clave';
|
|
$col[6] = 'nomcurso';
|
|
$col[7] = 'calificacion';
|
|
$col[8] = 'estado';
|
|
$col[9] = 'visitado';
|
|
$col[10] = 'fecha';
|
|
|
|
$sqlusers .=" ORDER BY ". $col[$request['order'][0]['column']]." ".$request['order'][0]['dir'];
|
|
$sqlusers .= " LIMIT ". $request['start']." ,".$request['length'];
|
|
|
|
$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
|
|
);
|
|
echo json_encode($json_data);
|
|
|
|
?>
|