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

$fechainicio = $_POST['fechaini'];
$fechafin = $_POST['fechafin'];
$estado = $_POST['estado'];


$sqlusers = "";


$sqlusers .= "SELECT
q.numempl,
q.nombre as nombre,
q.apellidos,
q.nomcentro,
q.nomdistrito,
q.posicion,
q.sociedad,
q.division,
q.unidad_organizativa,
q.ceco,
q.supervisor,
IF(q.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
categoria.nombre as nomcat,
q.nomcat as nomcurso,
q.visitado as visitado,
q.fecha as fecha
FROM
(
SELECT 
scormvars.numempl AS numempl,
user.nombre as nombre,
user.apellido as apellidos,
user.centrotrabajonom AS nomcentro,
user.distritonom AS nomdistrito,
user.posicion,
user.sociedad,
user.division,
user.unidad_organizativa,
user.ceco,
user.supervisor,
user.habilitado,
categoria.padre,
categoria.nombre as nomcat,
categoria.id as cat0,
scormpkg.nombre nomcurso,
scormpkg.idcategoria as idcategoria,
CASE
    WHEN ( COUNT( scormvars.numempl ) = ( 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.numempl ),' de ',( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id ) )END AS visitado,
scormvars.fecha
FROM scormvars
INNER JOIN user ON user.numempl = scormvars.numempl
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
INNER JOIN categoria ON categoria.id = scormpkg.idcategoria
WHERE (varValue = 'passed' OR varValue = 'completed')
AND scormvars.numempl <> ''
AND scormpkg.habilitado = 1";

if($estado != 'all' ){
    $sqlusers .= " AND user.habilitado = $estado";
}else{
    $sqlusers .= " AND user.habilitado IN (0,1)";
}

if ($fechainicio != '' && $fechafin != '') {
    $sqlusers .= " AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'";
}


$sqlusers .= " GROUP BY scormvars.numempl, scormpkg.idcategoria
ORDER BY scormvars.numempl, categoria.id DESC
) AS q
INNER JOIN categoria ON categoria.id = q.padre
ORDER BY q.numempl, q.cat0 ASC";


/*

if ($fechainicio == '' && $fechafin == '' && $estado == 'all' ) {
$sqlusers  = "SELECT * FROM viewReporteRecursos;";
}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,
            rs1.nomcurso,
            rs1.visitado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as nombre,
                user.apellido as apellidos,
                user.centrotrabajonom AS nomcentro,
                user.distritonom 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) ) 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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status'
                AND scormpkg.habilitado = 1
                AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                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 c.id = rs1.cat
        ) as rs2
        ORDER BY  rs2.numempl ASC, rs2.nomcat ASC;";
}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,
            rs1.nomcurso,
            rs1.visitado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as nombre,
                user.apellido as apellidos,
                user.centrotrabajonom AS nomcentro,
                user.distritonom 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) ) 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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status'
                AND scormpkg.habilitado = 1
                AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                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 c.id = rs1.cat
        ) as rs2
        ORDER BY  rs2.numempl ASC, rs2.nomcat ASC;";
}else if ($fechainicio != '' && $fechafin == '' && $estado == 'all') {
$fechafin = date('Y-m-d');
    $sqlusers = "SELECT
q.numempl,
q.nombre as nombre,
q.apellidos,
q.nomcentro,
q.nomdistrito,
q.posicion,
q.sociedad,
q.division,
q.unidad_organizativa,
q.ceco,
q.supervisor,
IF(q.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
categoria.nombre as nomcat,
q.nomcat as nomcurso,
q.visitado as visitado,
q.fecha as fecha
FROM
(
SELECT 
scormvars.numempl AS numempl,
user.nombre as nombre,
user.apellido as apellidos,
user.centrotrabajonom AS nomcentro,
user.distritonom AS nomdistrito,
user.posicion,
user.sociedad,
user.division,
user.unidad_organizativa,
user.ceco,
user.supervisor,
user.habilitado,
categoria.padre,
categoria.nombre as nomcat,
categoria.id as cat0,
scormpkg.nombre nomcurso,
scormpkg.idcategoria as idcategoria,
CASE
     WHEN scormvars.numempl <> '' THEN
         CONCAT(COUNT( scormvars.numempl ),' de ',( SELECT COUNT( * ) FROM scormpkg WHERE scormpkg.idcategoria = categoria.id ) )END AS visitado,
scormvars.fecha
FROM scormvars
INNER JOIN user ON user.numempl = scormvars.numempl
INNER JOIN scormpkg ON scormpkg.id = scormvars.SCOInstanceID
INNER JOIN categoria ON categoria.id = scormpkg.idcategoria
WHERE (varValue = 'passed' OR varValue = 'completed')
AND user.habilitado IN (1,0)
AND scormvars.numempl <> ''
AND scormvars.fecha BETWEEN '$fechainicio' AND 'fechafin'
GROUP BY scormvars.numempl, scormpkg.idcategoria
ORDER BY scormvars.numempl, categoria.id DESC
) AS q
INNER JOIN categoria ON categoria.id = q.padre
ORDER BY q.cat0 ASC";
}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,
            rs1.nomcurso,
            rs1.visitado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as nombre,
                user.apellido as apellidos,
                user.centrotrabajonom AS nomcentro,
                user.distritonom 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) ) 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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status'
                AND scormpkg.habilitado = 1
                AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                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 c.id = rs1.cat
        ) as rs2
        ORDER BY  rs2.numempl ASC, rs2.nomcat ASC;";
}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,
            rs1.nomcurso,
            rs1.visitado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as nombre,
                user.apellido as apellidos,
                user.centrotrabajonom AS nomcentro,
                user.distritonom 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) ) 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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status'
                AND scormpkg.habilitado = 1
                AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                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 c.id = rs1.cat
        ) as rs2
        ORDER BY  rs2.numempl ASC, rs2.nomcat ASC;";
}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,
            rs1.nomcurso,
            rs1.visitado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as nombre,
                user.apellido as apellidos,
                user.centrotrabajonom AS nomcentro,
                user.distritonom 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) ) 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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status'
                AND scormpkg.habilitado = 1
                AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                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 c.id = rs1.cat
        ) as rs2
        ORDER BY  rs2.numempl ASC, rs2.nomcat ASC;";
}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,
            rs1.nomcurso,
            rs1.visitado
        FROM
            (
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as nombre,
                user.apellido as apellidos,
                user.centrotrabajonom AS nomcentro,
                user.distritonom 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) ) 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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status'
                AND scormpkg.habilitado = 1
                AND (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                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 c.id = rs1.cat
        ) as rs2
        ORDER BY  rs2.numempl ASC, rs2.nomcat ASC;";

}else{
    $sqlusers  = "SELECT * FROM viewReporteRecursos;";
}
*/

//echo $sqlusers;

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

if( $resultusers ){
    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;
    }
}


$sqlTotales = "SELECT
  rs2.total,
  categoria.nombre
  FROM
  (
  SELECT
        COUNT(categoria.padre) AS total,
        categoria.padre as categoripadre
      FROM
      (
    SELECT
          scormpkg.idcategoria as tema,
          scormpkg.nombre as nombre_tema
      FROM
          scormpkg
      INNER JOIN categoria ON categoria.id = scormpkg.idcategoria
      WHERE
          scormpkg.habilitado = 1
          AND categoria.habilitado = 1
  ) AS rs1
  INNER JOIN categoria ON categoria.id = rs1.tema
  GROUP BY categoria.padre
  ) AS rs2
INNER JOIN categoria ON categoria.id = rs2.categoripadre";

$resultTotales = mysqli_query($link, $sqlTotales);

if( $resultTotales){
    mysqli_data_seek($resultTotales, 0);
    while ($row = mysqli_fetch_assoc ($resultTotales)){
        $totales[$row['nombre']] = $row['total'];
    }
}


    $columnasvisibles= array(
    'Estado del empleado',
    'Número de empleado',
    'Nombres(s)',
    'Apellidos',
    'Posición',
    'Sociedad',
    'División',
    'Unidad organizativa',
    'CeCo',
    'Distrito',
    'Centro de trabajo',
    'Supervisor'
    );

    foreach ($categorias as $key2 => $cat) {
        array_push($columnasvisibles, strip_tags($cat['nombre']));
    }

    for ($i=0; $i < sizeof($columnasvisibles) ; $i++) { 
        $columnasvisibles[$i] = array('title' => $columnasvisibles[$i]);
    }
    
    $return->columnasvisibles = $columnasvisibles;


    if (!empty($data)) {
        $anterior = '';
        $anteriorcat = '';
        $users = 0;
        $visitados = 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>";

                if ($anteriorcat == $col['nomcat']) {
                    $visitas = explode(" de ",$col['visitado']);
                    $visitados = $visitados+$visitas[0];
                    $total = $visitados." de ".$totales[$col['nomcat']];
                    $datos[($users-1)][$col['nomcat']] = $total;
                }else{
                    $visitados = 0;
                    $anteriorcat = $col['nomcat'];
                    $visitas = explode(" de ",$col['visitado']);
                    $visitados = $visitados+$visitas[0];
                    $total = $visitados." de ".$totales[$col['nomcat']];
                    $datos[($users-1)][$col['nomcat']] = $total;
                }

            }else{
                //echo $col['numempl']." nuevo - ".$col['nomcat']." - ".$users." <br>";
                $visitados = 0;
                $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']];


                $visitas = explode(" de ",$col['visitado']);
                $visitados = $visitas[0];
                $anteriorcat = $col['nomcat'];
                foreach ($categorias as $key2 => $cat) {
                    if ($anteriorcat == $cat['nombre']) {
                        $datos[$users][$cat['nombre']] = $visitados." de ".$totales[$cat['nombre']];
                    }else{
                        $datos[$users][$cat['nombre']] = "0 de ".$totales[$cat['nombre']];
                    }
                }

                $users++;
            }
            $anterior = $col['numempl'];
        }


    //print_r($datos[3]);
    //echo "<br>";
        foreach ($datos as $col => $columnas) {
            $fill = [];
            foreach ($columnas as $key => $value) {
                array_push($fill, $value);
            }
            $datos[$col] = $fill;
        }
    }else{
        $datos = '';
    }


$return->data = $datos;


mysqli_close($link);
echo json_encode($return, JSON_UNESCAPED_UNICODE);
exit;


?>