<?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' => "SELECT * FROM mdl_user",
    'calificaciones' => "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, 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 FROM mdl_course c JOIN mdl_grade_items mgi ON c.id = mgi.courseid 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;",
);

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=ANSI');
header("Content-Disposition: attachment; filename=$filename");
$fp = fopen('php://output', 'wb');
// insert header
fputcsv($fp, array_keys($data[0]));
// insert data
foreach ($data as $line) {
    fputcsv($fp, $line);
}
fclose($fp);
exit();