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


$sqlData = "SELECT * FROM viewReporteDistritos;";


/*
SELECT
	rs2.nomdistrito,
	rs2.nomcat,
	COUNT(*) as total
FROM
	(
	SELECT
		rs1.numempl,
		rs1.distritoid,
		rs1.nomdistrito,
		rs1.cat,
		c.nombre as nomcat,
		COUNT(rs1.estado) as total,
		CASE
           WHEN ( COUNT( rs1.estado) = ( SELECT COUNT( * ) FROM categoria WHERE categoria.padre = rs1.cat AND categoria.habilitado = 1) ) THEN
           'Completado' ELSE 'No Completado' 
        END AS estado1
	FROM
		(
            SELECT
                scormvars.numempl AS numempl,
                user.nombre as user,
                user.apellido as apellidos,
                distrito.nombredistrito AS nomdistrito,
                user.distritoid,
                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
                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.varValue = 'completed'
                AND scormvars.numempl <> '' 
            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 rs1.estado = 'Completado'
        GROUP BY rs1.numempl,rs1.cat
        ORDER BY c.nombre
    ) AS rs2
	WHERE rs2.estado1 = 'Completado'
	GROUP BY rs2.nomdistrito,rs2.cat
    ORDER BY rs2.nomdistrito;
*/


$resultdata = mysqli_query($link, $sqlData);

if( $resultdata ){
    mysqli_data_seek($resultdata, 0);
    while ($row = mysqli_fetch_assoc ($resultdata)){
        $data_array[] = $row;
    }
}


//-----------------------------------------------------------------
//----------------------	COLUMNAS   ----------------------------
//-----------------------------------------------------------------
$sqlColumns = "SELECT * FROM viewReporteDistritoscolumnas;";

/*
SELECT
	asignacion.id,
	asignacion.uid,
	asignacion.catid,
	categoria.clave,
	categoria.nombre,
	categoria.imagen,
	categoria.descripcion 
FROM
	asignacion
	INNER JOIN categoria ON asignacion.catid = categoria.id 
WHERE
	asignacion.uid <> ''
	AND categoria.habilitado = 1
GROUP BY categoria.clave;
*/

$resultcolumns = mysqli_query($link, $sqlColumns);

if( $resultcolumns ){
    mysqli_data_seek($resultcolumns, 0);
    while ($row = mysqli_fetch_assoc ($resultcolumns)){
        $columns_array[] = $row;
    }
}

foreach ($columns_array as $key => $col) {
	//echo $col['nombre'];
	$categorias[$key] = $col['nombre'];
	$columns[$key] = array('title' => $col['nombre']);
}

//$periodo = array('title' => 'Periodo');
$distrito = array('title' => 'Distrito');
$total = array('title' => 'Total ingresos (activos)');

array_unshift($columns, $distrito);
//array_unshift($columns, $periodo);
array_push($columns, $total);


//-----------------------------------------------------------------
//----------------------	DISTRITOS   ----------------------------
//-----------------------------------------------------------------

$sqlDistritos = "SELECT * FROM viewReporteDistritosNombres;";

/*
CREATE VIEW viewReporteDistritosNombres AS SELECT
    *
FROM
    distrito
WHERE
    distrito.habilitado = 1;
*/

$resultdistritos = mysqli_query($link, $sqlDistritos);

if( $resultdistritos ){
    mysqli_data_seek($resultdistritos, 0);
    while ($row = mysqli_fetch_assoc ($resultdistritos)){
        $distritos[] = $row['nombredistrito'];
    }
}

//-----------------------------------------------------------------
//--------------	TOTALES POR DISTRITO  -------------------------
//-----------------------------------------------------------------

$sqltotal = "SELECT * FROM viewReporteDistritosTotal;";

/*
SELECT
    distrito.nombredistrito,
    COUNT(*) as total
FROM
    user
INNER JOIN distrito ON distrito.distritoid = user.distritoid
WHERE
    user.habilitado = 1
    AND
    distrito.habilitado = 1
GROUP BY user.distritoid;
*/

$resultTotal = mysqli_query($link, $sqltotal);

if( $resultTotal ){
    mysqli_data_seek($resultTotal, 0);
    while ($row = mysqli_fetch_assoc ($resultTotal)){
        $distritosTotal[] = $row;
    }
}


$data = [];
foreach ($distritos as $distrito => $dis) {
	$data[$distrito][] = $dis;
	foreach ($categorias as $categoria => $cat) {
		$flag = false;
		//echo $dis." - ".$cat;

		foreach ($data_array as $key => $da) {
			if($da['nomdistrito'] == $dis && $da['nomcat'] == $cat){
				$flag = true;
				$valor = $da['total'];
			}
		}
		
		if ($flag == true) {
			//echo "- Encontrado";
			$data[$distrito][] = $valor;
		}else{
			//echo "- No encontrado";
			$data[$distrito][] = 0;
		}


		//echo "<br><br>";
	}

	$data[$distrito][] = $distritosTotal[$distrito]['total'];
}


$Grafica = [];
foreach ($categorias as $numcat => $categoria) {
	$valores = [];
	$posicion = ($numcat+1);
	foreach ($distritos as $numdis => $dis) {
		array_push($valores, intval($data[$numdis][$posicion]));
	}

	$Grafica[$numcat] = array('name' => $categoria, 'data' => $valores);

}

$return->Graphic = $Grafica;
$return->distritos = $distritos;
$return->data = $data;
$return->columns[] = $columns;


mysqli_close($link);

echo json_encode($return, JSON_UNESCAPED_UNICODE);
exit;

?>