<?php
require_once __DIR__.'/auth_guard.php';
include '../config.php';
session_name($CFG->sessionname);
session_start();
if(!isset($_SESSION['usersession'])){
    header("Location: ".$CFG->wwwroot);
}

dbConnect();


    global $link;
    
$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,
            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.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 c.id = rs1.cat
        ) as rs2
        ORDER BY rs2.numempl ASC";

$resultados = mysqli_query($link, $sqlusers);
function cleanData(&$str)
  {
    $str = mb_convert_encoding($str , 'UTF-16LE' ,'UTF-8');
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
    
  }


  // filename for download
$filename = "reporte recursos visitados Estafeta__" . date('Ymd') . ".csv";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: text/csv");
$out = fopen("php://output", 'w');
$flag = false;

$encabezados = ["Periodo","Estado del empleado","Número de empleado","Nombre(s)","Apellidos","Posición","Sociedad","División","Unidad Organizativa","CeCo","Centro de trabajo","Distrito","Supervisor","Categoría","Tema","Visitados"];
array_walk($encabezados, __NAMESPACE__ . '\cleanData');
fputcsv($out, array_values($encabezados), ',', '"');
while ($row = mysqli_fetch_array  ($resultados, MYSQLI_NUM)) {
    array_walk($row, __NAMESPACE__ . '\cleanData');
    fputcsv($out, array_values($row), ',', '"');
}
 
 fclose($out);
 exit;

?>