134 lines
4.5 KiB
PHP
134 lines
4.5 KiB
PHP
<?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;
|
|
|
|
?>
|