adcfi/export/excel.php

<?php
require_once "{$_SERVER['DOCUMENT_ROOT']}/dependencies.php";

// Exportar a archivo separado por comas
// Must receive from POST associative array with keys and values
use Respect\Validation\Validator as v;

$query = json_decode(file_get_contents('php://input'), true);
$queries = array(
    'usuarios' => <<<SQL
    SELECT 
        u.id AS user_id,
        u.username AS username,
        CONCAT(u.firstname, ' ', u.lastname) AS full_name,
        u.email AS email,
        u.lastaccess AS last_access,
        c.fullname AS course_name,
        cc.name AS category_name
    FROM 
        mdl_user u
    LEFT JOIN 
        mdl_user_enrolments ue ON u.id = ue.userid
    LEFT JOIN 
        mdl_enrol e ON ue.enrolid = e.id
    LEFT JOIN 
        mdl_course c ON e.courseid = c.id
    LEFT JOIN 
        mdl_course_categories cc ON c.category = cc.id
    WHERE 
        u.deleted = 0
    ORDER BY 
        u.id
    SQL,

    'alumnos' => <<<SQL
    SELECT u.id, u.username, u.firstname, u.lastname, u.email
    FROM mdl_user u
    JOIN mdl_role_assignments ra ON ra.userid = u.id
    WHERE username LIKE 'al%';
    SQL,

    'usuarios_temporales' => <<<SQL
    SELECT id, username, firstname, lastname, email, deleted
    FROM mdl_user
    WHERE deleted = 1 or confirmed = 0;
    SQL,

    'calificaciones' => <<<SQL
    SELECT  
    c.id AS courseid,
    c.shortname,
    COALESCE(cc2.name, cc.name) AS AREA,
    CASE 
        WHEN cc2.name IS NULL THEN NULL 
        ELSE cc.name 
    END AS GRUPO,
    prof.username AS profesor_clave,
    CONCAT(prof.firstname, ' ', prof.lastname) AS profesor_nombre,
    c.fullname AS course_fullname,
    MAX(mgi.calculation) AS fórmula,
    CASE 
        WHEN COALESCE(MIN(mgi.calculation) <> '', false) THEN 'Sí' 
        ELSE 'No' 
    END AS formula,
    CASE 
        WHEN MAX(mgi.AGGREGATIONCOEF) > 0  THEN 'Sí' 
        ELSE 'No' 
    END AS ponderacion,
    CASE 
        WHEN MAX(mgi.calculation) IS NOT NULL THEN (
        SELECT SUM(val::numeric) 
        FROM (
            SELECT 
            unnest(regexp_matches(MAX(mgi.calculation), '\d?\.\d+', 'g')) AS val
        ) AS subquery
        )
        ELSE NULL 
    END AS suma_numeros,
JSONB_AGG(JSONB_BUILD_OBJECT(COALESCE(gc.fullname, '-'), mgi.aggregationcoef)) FILTER (WHERE mgi.aggregationcoef >0 ) ,
CASE WHEN MAX(MGI.GRADEMAX) > 0 THEN
sum(mgi.aggregationcoef) / max(mgi.grademax) FILTER (WHERE mgi.itemtype = 'course')
ELSE 
0 END AS "Suma categorías",
MAX(MGI.GRADEMAX)
    FROM 
    mdl_course c 
    JOIN mdl_grade_items mgi ON c.id = mgi.courseid 
	LEFT JOIN mdl_grade_categories gc ON mgi.iteminstance = gc.id AND mgi.itemtype = 'category' -- Asegúrate de que el itemtype sea 'category'

    JOIN mdl_course_categories cc ON cc.id = c.category 
    LEFT JOIN mdl_course_categories cc2 ON cc.parent = cc2.id 
    JOIN mdl_context ctx ON ctx.instanceid = c.id 
    JOIN mdl_role_assignments ra ON ra.contextid = ctx.id AND ra.roleid = 3 
    JOIN mdl_user prof ON ra.userid = prof.id 
    WHERE 
    mgi.itemtype IN ('course', 'category') 
    GROUP BY 
    c.id, c.shortname, cc.name, cc2.name, c.fullname, prof.firstname, prof.lastname, prof.username 
    ORDER BY 
    AREA, GRUPO, profesor_nombre;
SQL,

'calificaciones_brutas' => <<<SQL
SELECT
    gi.itemname AS item_name,
    c.id AS course_id,
    c.fullname AS course_name,
    u.id AS user_id,
    u.username,
    u.firstname,
    u.lastname,
    gg.finalgrade
FROM
    mdl_grade_grades gg
JOIN
    mdl_grade_items gi ON gg.itemid = gi.id
JOIN
    mdl_course c ON gi.courseid = c.id
JOIN
    mdl_user u ON gg.userid = u.id
WHERE
    gi.itemtype = 'mod' and gi.itemmodule <> 'attendance' and username like 'al%'
	
order by username, item_name;
SQL,
);

methods(['POST' => v::keySet(
    v::key('query', v::in(array_keys($queries))),
)]);

// method must be POST
if (!isset($_SESSION['user'], $moodle_db)) {
    serverError(title: 'Error de conexión', message: 'No se ha iniciado sesión o no se ha establecido una conexión con la base de datos de Moodle');
    exit();
}
$data = $moodle_db->query($queries[$query['query']]);
$filename = 'test.csv';

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
echo "\xEF\xBB\xBF"; // Añade el BOM de UTF-8 al inicio del archivo para indicar su codificación

$fp = fopen('php://output', 'w'); // 'wb' también es válido en este contexto
// insert header
$headers = array_keys($data[0]);

switch ($query['query']) {
    case 'calificaciones':
        $headers[] = 'Tiene calificación'; // Añade el nombre de la nueva columna al final del encabezado
        $headers[] = 'Calificación Máxima';
        $headers[] = 'grupo_numerico'; // Añade el nombre de la nueva columna al final del encabezado
        break;
}
// todos los headers en Mayúsculas incluyendo los acentos
$headers = array_map(function ($header) {
    return mb_convert_case($header, MB_CASE_UPPER, 'UTF-8');
}, $headers);

fputcsv($fp, $headers);

// insert data
foreach ($data as $line) {
    switch ($query['query']) {
        case 'calificaciones':
            $penultimaColumna = $line['formula']; // Asume que 'formula' es la penúltima columna
            $ultimaColumna = $line['ponderacion']; // Asume que 'ponderacion' es la última columna

            // Determina el valor de la nueva columna
            $nuevoValor = ($penultimaColumna === 'Sí' || $ultimaColumna === 'Sí') ? 'Sí' : 'No';

            // Añade el nuevo valor al final de la línea
            $line['Tiene calificación'] = $nuevoValor;
            $line['Calificación Máxima'] = $line['SUMA_NUMEROS'] +  $line['Suma categorías'];


            $grupoNumerico = '';
            if (preg_match('/\d+/', $line['grupo'], $matches)) {
                $grupoNumerico = $matches[0]; // El primer match contiene la parte numérica
            }

            // Añade el valor numérico extraído al final de la línea
            $line['grupo_numerico'] = $grupoNumerico;
            break;
    }


    // Convertir cada elemento de la línea a UTF-8 antes de escribir al archivo
    $line_utf8 = array_map(function ($elem) {
        return mb_convert_encoding($elem, 'UTF-8', 'UTF-8');
    }, $line);

    fputcsv($fp, $line_utf8);
}
fclose($fp);

exit();