<?php
require_once __DIR__.'/auth_guard.php';
require_once("../config.php");
require_once("../runtime/encrypt.php");
dbConnect();
global $link;
$resultados = []; 
$fechainicio = $_GET['fechaini'];
$fechafin = $_GET['fechafin'];
$estado = $_GET['estado'];
    $headers = [
    'Estado del empleado',
    'Número de empleado',
    'Nombres(s)',
    'Apellidos',
    'Posición',
    'Sociedad',
    'División',
    'Unidad organizativa',
    'CeCo',
    'Centro de trabajo',
    'Distrito',
    'Supervisor'
    ];
    

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,
        CASE
           WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                user.apellido as apellidos,
                user.distritonom AS nomcentro,
                user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                    ) 
                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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                AND scormpkg.habilitado = 1
            GROUP BY
                scormvars.numempl,
                categoria.clave
            HAVING
                max( scormvars.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,rs1.cat
    ) AS rs2
     ORDER BY rs2.numempl 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,
        CASE
           WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                user.apellido as apellidos,
                user.distritonom AS nomcentro,
                user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                    ) 
                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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                AND scormvars.numempl <> ''
                AND scormpkg.habilitado = 1
                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 rs1.estado = 'Completado'
        GROUP BY rs1.numempl,rs1.cat
        ORDER BY c.nombre,rs1.cat
    ) AS rs2
     ORDER BY rs2.numempl 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,
            CASE
               WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                    user.apellido as apellidos,
                    user.distritonom AS nomcentro,
                    user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                        ) 
                    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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                    AND scormvars.numempl <> ''
                    AND scormpkg.habilitado = 1
                    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 rs1.estado = 'Completado'
            GROUP BY rs1.numempl,rs1.cat
            ORDER BY c.nombre,rs1.cat
        ) AS rs2
        ORDER BY rs2.numempl 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,
            CASE
               WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                    user.apellido as apellidos,
                    user.distritonom AS nomcentro,
                    user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                        ) 
                    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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                    AND scormvars.numempl <> ''
                    AND scormpkg.habilitado = 1
                    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 rs1.estado = 'Completado'
            GROUP BY rs1.numempl,rs1.cat
            ORDER BY c.nombre,rs1.cat
        ) AS rs2
         ORDER BY rs2.numempl 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,
            CASE
               WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                    user.apellido as apellidos,
                    user.distritonom AS nomcentro,
                    user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                        ) 
                    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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                    AND scormvars.numempl <> ''
                    AND scormpkg.habilitado = 1
                    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 rs1.estado = 'Completado'
            GROUP BY rs1.numempl,rs1.cat
            ORDER BY c.nombre,rs1.cat
        ) AS rs2
         ORDER BY rs2.numempl 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,
            CASE
               WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                    user.apellido as apellidos,
                    user.distritonom AS nomcentro,
                    user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                        ) 
                    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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                    AND scormvars.numempl <> ''
                    AND scormpkg.habilitado = 1
                    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 rs1.estado = 'Completado'
            GROUP BY rs1.numempl,rs1.cat
            ORDER BY c.nombre,rs1.cat
        ) AS rs2
        ORDER BY rs2.numempl 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,
            CASE
               WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                    user.apellido as apellidos,
                    user.distritonom AS nomcentro,
                    user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                        ) 
                    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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                    AND scormvars.numempl <> ''
                    ANS scormpkg.habilitado = 1
                    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 rs1.estado = 'Completado'
            GROUP BY rs1.numempl,rs1.cat
            ORDER BY c.nombre,rs1.cat
        ) AS rs2
        ORDER BY rs2.numempl 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,
            CASE
               WHEN ( COUNT( rs1.cat) = ( 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 nombre,
                    user.apellido as apellidos,
                    user.distritonom AS nomcentro,
                    user.centrotrabajonom 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 AND scormpkg.habilitado = 1) ) 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 AND scormpkg.habilitado = 1) 
                        ) 
                    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 (scormvars.varValue = 'completed' OR scormvars.varValue = 'passed')
                    AND scormvars.numempl <> ''
                    AND scormpkg.habilitado = 1
                    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 rs1.estado = 'Completado'
            GROUP BY rs1.numempl,rs1.cat
            ORDER BY c.nombre,rs1.cat
        ) AS rs2
        ORDER BY rs2.numempl ASC ";


}else{
   $sqlusers  = "SELECT * FROM viewReporteCategorias;"; 
}


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


if( $resultusers ){
    if (mysqli_num_rows($resultusers) <= 0 ) {
        $data = [];
    }else{
        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;
    }
}


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

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

    /*
    for ($i=0; $i < sizeof($headers) ; $i++) { 
        $headers[$i] = array('title' => $headers[$i]);
    }
    */


    array_push($resultados,$headers);

    if (!empty($data)) {
        $anterior = '';
        $users = 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>";
                $datos[($users-1)][$col['nomcat']] = $col['estado1'];
            }else{
                //echo $col['numempl']." nuevo - ".$col['nomcat']." - ".$users." <br>";

                $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']];

                foreach ($categorias as $key2 => $cat) {
                    if ($cat['nombre'] == $col['nomcat']) {
                        $datos[$users][$cat['nombre']] = $col['estado1'];;
                    }else{
                        $datos[$users][$cat['nombre']] = 'No completado';
                    }
                }
                $users++;
            }
            $anterior = $col['numempl'];
        }


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

    foreach ($datos as $key => $val) {
            $obj = [
            $val[0],
            $val[1],
            $val[2],
            $val[3],
            $val[4],
            $val[5],
            $val[6],
            $val[7],
            $val[8],
            $val[9],
            $val[10],
            $val[11],
            $val[12],
            $val[13],
            $val[14],
            $val[15],
            $val[16],
            $val[17],
            $val[18]
        ];
        array_push($resultados,$obj);
    }


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

  // filename for download
$filename = "reporte - Categorías completadas __" . date('Ymd') . ".csv";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: text/csv");
$out = fopen("php://output", 'w');
 $flag = false;
 
 foreach ($resultados as $row) {
     
    array_walk($row, __NAMESPACE__ . '\cleanData');
    fputcsv($out, array_values($row), ',', '"');
 }
 
 fclose($out);
 exit;

?>