<?php
require_once __DIR__.'/auth_guard.php';

require_once("../config.php");
require_once("../runtime/encrypt.php");

dbConnect();
global $link;
$return = new stdClass();
$request = $_REQUEST;
$fechainicio = $_GET['fechaini'];
$fechafin = $_GET['fechafin'];
$estado = $_GET['estado'];


$fechafin = date('Y-m-d',  strtotime($fechafin."+ 2 days") );

if ($fechainicio == '' && $fechafin == '' && $estado == 'all' ) {
    $sqlusers = "SELECT
*
FROM
(
    SELECT
            rs1.fecha,
            rs1.estado_empleado,
            rs1.user_creado,
            rs1.numempl,
            rs1.nomempl,
            rs1.apellidos,
            rs1.posicion,
            rs1.sociedad,
            rs1.division,
            rs1.unidad_organizativa,
            rs1.ceco,
            rs1.nomcentro,
            rs1.nomdistrito,
            rs1.supervisor,
            c.nombre as categoria,
            rs1.tema,
            rs1.subtema,
            rs1.estado
    FROM
        (
        SELECT
            DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
            IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
            scormvars.numempl AS numempl,
            user.nombre AS nomempl,
            user.apellido as apellidos,
            user.posicion,
            user.sociedad,
            user.division,
            user.unidad_organizativa,
            user.ceco,
            user.distritonom AS nomdistrito,
            user.centrotrabajonom AS nomcentro,
            user.supervisor,
            DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
            categoria.padre as categoria,
            categoria.nombre AS tema,
            scormpkg.nombre as subtema,
            IF(scormvars.varValue = 'completed' OR  scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
        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
        WHERE
            scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' 
    ) AS rs1
INNER JOIN categoria as c ON c.id = rs1.categoria
WHERE c.id = rs1.categoria
) as rs2";
}else if ($fechainicio == '' && $fechafin == '' && $estado != 'all') {
    $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                user.creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR  scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = $estado
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}else if ($fechainicio != '' && $fechafin != '' && $estado == 'all') {
        $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}else if ($fechainicio != '' && $fechafin == '' && $estado == 'all') {
    $fechafin = date('Y-m-d');
    $fechafin = date('Y-m-d',  strtotime($fechafin."+ 2 days") );
            $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}else if ($fechainicio == '' && $fechafin != '' && $estado == 'all') {
    $fechainicio = '2020-07-01';
                $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}else if ($fechainicio != '' && $fechafin != '' && $estado != 'all') {
                $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = '$estado' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}else if($fechainicio != '' && $fechafin == '' && $estado != 'all'){
    $fechafin = date('Y-m-d');
    $fechafin = date('Y-m-d',  strtotime($fechafin."+ 2 days") );
    $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = '$estado' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}elseif($fechainicio == '' && $fechafin != '' && $estado != 'all'){
    $fechainicio = '2020-07-01';
    $sqlusers = "
    SELECT
    *
    FROM
    (
        SELECT
                rs1.fecha,
                rs1.estado_empleado,
                rs1.user_creado,
                rs1.numempl,
                rs1.nomempl,
                rs1.apellidos,
                rs1.posicion,
                rs1.sociedad,
                rs1.division,
                rs1.unidad_organizativa,
                rs1.ceco,
                rs1.nomdistrito,
                rs1.nomcentro,
                rs1.supervisor,
                c.nombre as categoria,
                rs1.tema,
                rs1.subtema,
                rs1.estado
        FROM
            (
            SELECT
                DATE_FORMAT(scormvars.fecha, '%d-%m-%Y') AS fecha,
                IF(user.habilitado = 1, 'ACTIVO','BAJA') as estado_empleado,
                scormvars.numempl AS numempl,
                user.nombre AS nomempl,
                user.apellido as apellidos,
                user.posicion,
                user.sociedad,
                user.division,
                user.unidad_organizativa,
                user.ceco,
                user.distritonom AS nomdistrito,
                user.centrotrabajonom AS nomcentro,
                user.supervisor,
                DATE_FORMAT(user.creado, '%d-%m-%Y') AS user_creado,
                categoria.padre as categoria,
                categoria.nombre AS tema,
                scormpkg.nombre as subtema,
                IF(scormvars.varValue = 'completed' OR scormvars.varValue = 'passed', 'COMPLETADO','NO COMPLETADO') as estado
            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
            WHERE
                scormvars.varName = 'cmi.core.lesson_status' AND scormvars.numempl <> '' AND user.habilitado = '$estado' AND scormvars.fecha BETWEEN '$fechainicio' AND '$fechafin'
        ) AS rs1
    INNER JOIN categoria as c ON c.id = rs1.categoria
    WHERE c.id = rs1.categoria
    ) as rs2
    ";
}else{
    $sqlusers = "SELECT * FROM viewReporteGeneral;";
}

//echo $sqlusers;


if(!empty($request['search']['value'])){
    $sqlusers.=" WHERE (rs2.numempl Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR rs2.nomempl Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.apellidos Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.nomcentro Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.nomdistrito Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.posicion Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.sociedad Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.division Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.unidad_organizativa Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.ceco Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.tema Like '".$request['search']['value']."%' ";
    $sqlusers.=" OR rs2.subtema Like '".$request['search']['value']."%' "; 
    $sqlusers.=" OR rs2.estado Like '".$request['search']['value']."%' "; 
    $sqlusers .= ")"; 
}

$totalrs = $sqlusers;
$totales = mysqli_query($link, $totalrs);


//Order
$col = [];
$col[0]  = 'fecha';
$col[1]  = 'estado_empleado';
$col[2]  = 'user_creado';
$col[3]  = 'numempl';
$col[4]  = 'nomempl';
$col[5]  = 'apellidos';
$col[6]  = 'posicion';
$col[7]  = 'sociedad';
$col[8]  = 'division';
$col[9]  = 'unidad_organizativa';
$col[10]  = 'ceco';
$col[11]  = 'distritonom';
$col[12]  = 'centrotrabajonom';
$col[13]  = 'supervisor';
$col[14]  = 'categoria';
$col[15]  = 'tema';
$col[16]  = 'subtema';
$col[17]  = 'estado';

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


//echo $sqlusers;
$query = $sqlusers;

function RemoveSpecialChar($str)
{
    $res = preg_replace("/\r?\n/", " ", $str);
    $res = preg_replace("/LIMIT 0  ,10/", " ", $res);

    return $res;
}


$str = $query;
$str1 = RemoveSpecialChar($str);
$query = $str1;
$query = encrypt_decrypt('encrypt', $query);

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

$totalData   = mysqli_num_rows($totales);
$resultados = [];
if( $resultusers ){
    while ($fila = mysqli_fetch_array  ($resultusers, MYSQLI_NUM)) {
        $resultados[] = $fila;
    }
}
$totalFilter = sizeof($resultados);
$json_data=array(
    "draw"              =>  intval($request['draw']),
    "recordsTotal"      =>  intval($totalFilter),
    "recordsFiltered"   =>  intval($totalData),
    "data"              =>  $resultados,
    "query"             =>  $query
);
echo json_encode($json_data);

?>