<?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);

?>