<?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;
    //Obtener todas las categorias registradas
    $resultados = []; 

    $sqluserstotal  ="SELECT
        scormvars.numempl,
        user.nombre as nomempl,
        user.apellido,
        centrotrabajo.nombrecentro AS nomcentro,
        distrito.nombredistrito AS nomdistrito,
        categoria.nombre as catnom,
        scormpkg.nombre,
        scormvars.fecha
        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
        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.lesson_status' AND scormvars.numempl <> ''";
    $resultsqluserstotal = mysqli_query($link, $sqluserstotal);
    

$sqlusers  ="SELECT
            *
            FROM(
                SELECT
                    scormvars.numempl AS numempl,
                    user.nombre as nomempl,
                    user.apellido,
                    centrotrabajo.nombrecentro AS nomcentro,
                    distrito.nombredistrito AS nomdistrito,
                    categoria.nombre as catnom,
                    scormpkg.nombre,
                    scormvars.varValue as calificacion, 
                     DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha 
                    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
                    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 <> '' 
                ) AS RS2 ";


if(!empty($request['search']['value'])){
    $sqlusers.=" WHERE numempl Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR nomempl Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR apellido Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR nomdistrito Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR nomcentro Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR catnom Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR nombre Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR calificacion = '".$request['search']['value']."%' ";
    $sqlusers.=" OR fecha Like '".$request['search']['value']."%' ";
}

//Order
$col = [];
$col[0]  = 'numempl';
$col[1]  = 'nomempl';
$col[2]  = 'apellido';
$col[3]  = 'nomcentro';
$col[4]  = 'nomdistrito';
$col[5]  = 'catnom';
$col[6]  = 'nombre';
$col[7]  = 'calificacion';
$col[8]  = 'fecha';

$sqlusers   .=" ORDER BY ". $col[$request['order'][0]['column']]."   ".$request['order'][0]['dir']; 
$sqlusers .= "  LIMIT ". $request['start']."  ,".$request['length'];

//echo $sqlusers;

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

if( mysqli_num_rows( $resultusers) > 0 ){

    while ($usuario = mysqli_fetch_assoc ($resultusers)) {
        
       $cal = floatval($usuario['calificacion']);
        if( $cal >= 80){
            $estado = "completed";
        }else{
            $estado = "incomplete";
        }
        
        $obj = [
            $usuario['numempl'],
            $usuario['nomempl'],
            $usuario['apellido'],
            $usuario['nomcentro'],
            $usuario['nomdistrito'],
            $usuario['catnom'],
            $usuario['nombre'],
            $usuario['calificacion'],
            $usuario['fecha']
           
            
        ];
        array_push($resultados,$obj);

    }

}

function getScore( $numempl, $SCOInstanceID ){
    global $link;
    $sql = "select varValue from scormvars where numempl = '$numempl' and SCOInstanceID = $SCOInstanceID and varName = 'cmi.core.score.raw'";
    $result = mysqli_query($link, $sql);
    if ( mysqli_num_rows($result) <= 0 ) {
        return '';
    }else{
        $score = mysqli_fetch_array($result);
        return $score['varValue'];
    }
}

$totalFilter =     mysqli_num_rows($resultsqluserstotal);
$totalData =   sizeof($resultados);

$json_data=array(
    "draw"              =>  intval($request['draw']),
    "recordsTotal"      =>  intval($totalData),
    "recordsFiltered"   =>  intval($totalFilter),
    "data"              =>  $resultados
);
echo json_encode($json_data);

?>