<?php
require_once __DIR__.'/auth_guard.php';
require_once("../config.php");
dbConnect();
global $link;
$return = new stdClass();

$sqlusers  = "
SELECT
   *
    FROM(
        SELECT
            rs1.fecha,
            rs1.estado_empleado,
            rs1.numempl,
            rs1.user,
            rs1.apellidos,
            rs1.posicion,
            rs1.sociedad,
            rs1.division,
            rs1.unidad_organizativa,
            rs1.ceco,
            rs1.nomcentro,
            rs1.nomdistrito,
            rs1.supervisor,
            c.nombre as nomcat,
            rs1.nomcurso,
            /*rs1.subtema,*/
            /*rs1.calificacion,*/
            /*rs1.visitado,*/
            rs1.estado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as user,
                user.apellido as apellidos,
                centrotrabajo.nombrecentro AS nomcentro,
                distrito.nombredistrito 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,
                scormpkg.nombre as subtema,
            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
                scormvars.varName = 'cmi.core.score.raw' 
                AND scormvars.numempl <> '' 
            GROUP BY
                scormvars.numempl,
                categoria.clave 
            HAVING
                max( fecha ) 
           
            ) AS rs1
        INNER JOIN categoria as c ON c.id = rs1.cat 
        WHERE c.id = rs1.cat
        ) as rs2
";


$resultusers = mysqli_query($link, $sqlusers);

if( $resultusers ){
    mysqli_data_seek($resultusers, 0);
    while ($row = mysqli_fetch_row ($resultusers)){
        $return->data[] = $row;
    }
}

echo json_encode($return, JSON_UNESCAPED_UNICODE);
exit;


?>