olimp_reports/reports_cert_and_dip.php

<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * enrol_teacherskey file description here.
 *
 * @package    enrol_teacherskey
 * @copyright  2022 alex sidorov <alex.sidorof@ya.ru>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

require_once(__DIR__ . '/../../config.php');
require "$CFG->libdir/tablelib.php";

$userid = optional_param('userid', 0, PARAM_INT);
$download = optional_param('download', '', PARAM_ALPHA);
$user = \core_user::get_user($userid ?: $USER->id, '*', MUST_EXIST);

if(isset($_GET['courseid'])){
    $courseid = $_GET['courseid'];
}else{
    redirect($CFG->wwwroot . "/my");
}

$context= \context_course::instance($courseid);
$PAGE->set_context($context);
$PAGE->set_url(new moodle_url('/local/olimp_reports/reports_cert_and_dip.php'));

$table = new \enrol_teacherskey\output\listteachers('teacherskey');

$name_downloadfile = "reports_olimp-".date('Y-m-d');
$table->is_downloading($download, '$name_downloadfile', 'reports_olimp');
//
//echo breadcrumbs
$course = get_course($courseid);
if (!$table->is_downloading()){
    $PAGE->set_pagelayout('standard');
    $PAGE->set_title(get_string('reports_olimp', 'local_olimp_reports'));
    $PAGE->set_heading(get_string('reports_olimp', 'local_olimp_reports'));
    $PAGE->navbar->add(get_string('courses'), new moodle_url('/course/index.php'));
    $PAGE->navbar->add($course->shortname, new moodle_url('/course/view.php', ['id' => $courseid]));
    $PAGE->navbar->add(get_string('reports_olimp', 'local_olimp_reports'), new moodle_url("/local/olimp_reports/reports_cert_and_dip.php", ['courseid' => $courseid]));
    echo $OUTPUT->header(get_string('reports_olimp', 'local_olimp_reports'));
}




$fields = "mtd.userid as id, concat(tu.lastname, ' ', tu.firstname, ' ', tu.middlename) as fullname,
       tu.email as email,
       (select data from mdl_user_info_data where userid=mtd.userid and fieldid=2) as name_obr,
       (select data from mdl_user_info_data where userid=mtd.userid and fieldid=1) as place_obr,
       round(mgg.finalgrade) as ball,
       mtct.name as dip_or_sap,
       (select data from mdl_user_info_data where userid=mtd.userid and fieldid=3) as fio,
       date(to_timestamp(mtci.timecreated)) as gdate,
       mtci.code as kod";
$from = "{teacherskey_data} mtd
            inner join {grade_items} mgi on mgi.courseid=mtd.courseid and mgi.itemmodule = 'quiz'
            inner join {grade_grades} mgg on mgg.userid=mtd.userid and mgg.itemid=mgi.id
            inner join {user} tu on tu.id = mtd.userid
            inner join {course} mc on mc.id = mtd.courseid
            inner join {tool_certificate_issues} mtci on mtci.userid = mtd.userid
            inner join {tool_certificate_templates} mtct on mtct.id = mtci.templateid";
$where = 'mtd.courseid = :courseid';
$params = array('courseid' => $courseid);


$table->set_sql($fields, $from, $where, $params);
$table->define_baseurl(new moodle_url("/local/olimp_reports/reports_cert_and_dip.php", ['courseid' => $courseid]));
$table->out(10, true);

if (!$table->is_downloading()) {
    echo $OUTPUT->footer();
}