<?php
// This file is part of the Studyplan plugin for Moodle
//
// 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 <https://www.gnu.org/licenses/>.

/**
 * Collect, process and display information about gradable items
 * @package    local_treestudyplan
 * @copyright  2023 P.M. Kuipers
 * @license    https://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

namespace local_treestudyplan;
defined('MOODLE_INTERNAL') || die();

require_once($CFG->libdir.'/externallib.php');
require_once($CFG->libdir.'/gradelib.php');
require_once($CFG->dirroot.'/course/lib.php');

/*  Include the required completion libraries
    Test existence of one of the files here to avoid issues when moodle
    moves these classes to a proper autoloading location.
    (Hasn't happened in 4.5 yet)
*/
if (file_exists($CFG->dirroot.'/completion/completion_criteria_completion.php')) {
    require_once($CFG->dirroot.'/completion/completion_aggregation.php');
    require_once($CFG->dirroot.'/completion/completion_completion.php');
    require_once($CFG->dirroot.'/completion/completion_criteria_completion.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_activity.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_course.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_date.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_duration.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_grade.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_role.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_self.php');
    require_once($CFG->dirroot.'/completion/criteria/completion_criteria_unenrol.php');
}

use completion_criteria;
use completion_aggregation;
use completion_criteria_completion;
use completion_completion;
use completion_criteria_activity;
use completion_criteria_course;
use completion_criteria_date;
use completion_criteria_duration;
use completion_criteria_grade;
use completion_criteria_role;
use completion_criteria_self;
use completion_criteria_unenrol;
use grade_item;
use grade_grade;
use grade_scale;
use dml_exception;
use stdClass;
use cm_info;
use moodle_exception;

/**
 * Preload, cache and return gradable objects.
 */
class preloader_core {

    /**
     * @var grade_item[]
     */
    private static $coursecollection = [];

    /**
     * @var completion_aggregation[]
     */

    private static $aggrmethods = [];
    /**
     * @var completion_criteria[]
     */
    private static $criteria = [];

    /**
     * @var completion_criteria_completion[]
     */
    private static $criteriacompletions = [];

    /**
     * @var completion_completion[]
     */
    private static $coursecompletions = [];

    /**
     * @var cm_info[]
     */
    private static $coursemodules = [];

    /**
     * @var stdClass[]
     */
    private static $cmcompletions = [];
    /**
     * @var int[][]
     */
    private static $cmcompletionmap = [];

    /**
     * @var gradE_item[]
     */
    private static $gradeitems = [];
    /**
     * @var int[]
     */
    private static $gradeitemmap = [];

    /**
     * @var grade_grade[]
     */
    private static $gradegrades = [];
    /**
     * @var int[][]
     */
    private static $gradegrademap = [];

    /**
     * @var grade_scale[]
     */
    private static $scales;


    /**
     * Return an object containing id's for a specific course
     *
     * @param int $courseid Id of the course to get lists of collected data for.
     * @return object
     */
    public static function coursecollection($courseid) {
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::$coursecollection[$courseid] = new stdClass();
            self::$coursecollection[$courseid]->aggr_methods = [];
            self::$coursecollection[$courseid]->criteria = [];
            self::$coursecollection[$courseid]->critera_completions = [];
            self::$coursecollection[$courseid]->course_completions = [];
            self::$coursecollection[$courseid]->user_critera_completions = [];
            self::$coursecollection[$courseid]->coursemodules = [];
        }
        return self::$coursecollection[$courseid];
    }

    /**
     * Preload grades and grade items for all grades and users in the plan.
     *
     * @param studyplan $plan The studyplan to preload for
     * @param int[] $userids Ids of users in plan
     * @return void
     */
    public static function preload_all(studyplan $plan, $userids = []) {
        if (empty($userids)) {
            $userids = $plan->find_linked_userids();
        }
        self::preload_criteria($plan);
        self::preload_criteria_results($plan, $userids);
    }

    /**
     * Preload grades and grade items for all grades and users in the plan.
     *
     * @param studyplan $plan The studyplan to preload for
     * @return void
     */
    public static function preload_map(studyplan $plan) {
        self::preload_criteria($plan);
    }

    /**
     * Preload grades and grade items for all grades and users in the plan.
     *
     * @param studyplan $plan The studyplan to preload for
     * @param int[]|int $userids Userid or Array of userids to preload for
     * @return void
     */
    public static function preload_for_users(studyplan $plan, $userids) {
        if (!is_array($userids)) {
            $userids = [$userids];
        }

        self::preload_criteria($plan);
        self::preload_criteria_results($plan, $userids);
    }

    /**
     * Retrieve course module from cache or db
     *
     * @param int $id Record ID
     * @return cm_info
     */
    public static function get_cm(int $id): cm_info {
        global $DB;
        if ($id == 0) {
            throw new moodle_exception("Invalid course module id 0");
        }
        if (array_key_exists($id, self::$coursecompletions)) {
            // Return from cache.
            return self::$coursemodules[$id];
        } else {
            // Retrieve from database and cache it.
            $r = $DB->get_record('course_modules', ["id" => $id], '*', MUST_EXIST);
            $o = cm_info::create($r);
            if (!$o) {
                throw new moodle_exception("Could not convert record into cm_info object");
            }
            self::$coursemodules[$id] = $o;
            return $o;
        }
    }

    /**
     * Retrieve course module completion from cache or db
     *
     * @param int $id Record ID
     * @return stdClass
     */
    public static function get_cm_completion(int $id) {
        global $DB;
        if ($id == 0) {
            // Create dummy record if not found.
            $o = new stdClass();
            $o->id = 0;
            $o->coursemoduleid = 0;
            $o->userid = 0;
            $o->completionstate = COMPLETION_INCOMPLETE;
            $o->overrideby = null;
            $o->timemodified = 0;
            return $o;
        }
        if (array_key_exists($id, self::$coursecompletions)) {
            // Return from cache.
            return self::$cmcompletions[$id];
        } else {
            // Retrieve from database and cache it.
            $r = $DB->get_record('course_modules_completion', ["id" => $id], '*', MUST_EXIST);
            self::$cmcompletions[$id] = $r;
            return $r;
        }
    }

    /**
     * Retrieve course completion from cache or db
     *
     * @param int $id Record ID
     * @return completion_completion
     */
    public static function get_course_completion(int $id): completion_completion {
        if ($id == 0) {
            return new completion_completion(null, false);
        }
        if (array_key_exists($id, self::$coursecompletions)) {
            // Return from cache.
            return self::$coursecompletions[$id];
        } else {
            // Retrieve from database and cache.
            $o = new completion_completion(["id" => $id]);
            if (!is_object($o)) {
                throw new dml_exception("Cannot retrieve grade_item", null, "Item id is '{$id}'");
            }
            self::$coursecompletions[$id] = $o;
            return $o;
        }
    }

    /**
     * Retrieve course completion criteria from cache or db
     *
     * @param int $id Record ID
     * @return completion_criteria
     */
    public static function get_criterion(int $id): completion_criteria {
        global $DB;
        if ($id == 0) {
            return new completion_criteria(null, false);
        }
        if (array_key_exists($id, self::$criteria)) {
            // Return from cache.
            return self::$criteria[$id];
        } else {
            // Retrieve from database and cache.
            $r = $DB->get_record('course_completion_criteria', ["id" => $id], '*', MUST_EXIST );
            $o = self::completion_criterion_from_record($r);
            self::$criteria[$id] = $o;
            return $o;
        }
    }

    /**
     * Create a proper completion_criteria object from a db record
     *
     * @param stdClass $r Database record
     * @return completion_criteria
     */
    private static function completion_criterion_from_record($r) {
        if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_SELF) {
            return new completion_criteria_self((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_DATE) {
            return new completion_criteria_date((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_UNENROL) {
            return new completion_criteria_unenrol((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_ACTIVITY) {
            return new completion_criteria_activity((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_DURATION) {
            return new completion_criteria_duration((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_GRADE) {
            return new completion_criteria_grade((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_ROLE) {
            return new completion_criteria_role((array)$r, false);
        } else if ($r->criteriatype == COMPLETION_CRITERIA_TYPE_COURSE) {
            return new completion_criteria_course((array)$r, false);
        } else {
            throw new \moodle_exception("Cannot create completion criterion from record", '', '', null, $r);
        }
    }

    /**
     * Retrieve course completion criteria completion from cache or db
     *
     * @param int $id Record ID
     * @return completion_criteria_completion
     */
    public static function get_criteria_completion(int $id): completion_criteria_completion {
        global $DB;
        if ($id == 0) {
            return new completion_criteria_completion(null, false);
        }
        if (array_key_exists($id, self::$criteriacompletions)) {
            // Return from cache.
            $o = self::$criteriacompletions[$id];
        } else {
            // Retrieve from database and cache.
            $r = $DB->get_record('course_completion_crit_comp', ["id" => $id], '*', MUST_EXIST );
            $o = new completion_criteria_completion((array)$r, false);
            self::$criteriacompletions[$id] = $o;
        }
        $o->attach_criteria(self::get_criterion($o->criteriaid));
        return $o;
    }

    /**
     * Retrieve course completion criteria completion from cache or db
     *
     * @param int $id Record ID
     * @return completion_aggregation
     */
    public static function get_completion_aggregation(int $id): completion_aggregation {
        global $DB;
        if ($id == 0) {
            $ag = new completion_aggregation(null, false);
            $ag->method = COMPLETION_AGGREGATION_ALL;
            return $ag;
        }
        if (array_key_exists($id, self::$aggrmethods)) {
            // Return from cache.
            return self::$aggrmethods[$id];
        } else {
            // Retrieve from database and cache.
            $r = $DB->get_record('course_completion_aggr_methd', ["id" => $id], '*', MUST_EXIST );
            $o = new completion_aggregation((array)$r, false);
            self::$criteriacompletions[$id] = $o;
            return $o;
        }
    }

    /**
     * Retrieve grade_scale from cache or db
     *
     * @param int $id Record ID
     * @return grade_scale
     */
    public static function get_grade_scale(int $id) {
        if (array_key_exists($id, self::$scales)) {
            // Return from cache.
            return self::$scales[$id];
        } else {
            // Retrieve from database and cache.
            $o = grade_scale::fetch(["id" => $id]);
            if (!is_object($o)) {
                throw new dml_exception("Cannot retrieve grade_scale", null, "Item id is '{$id}'");
            }
            self::$scales[$id] = $o;
            return $o;
        }
    }

    /**
     * Retrieve grade item from cache or db
     *
     * @param int $id Record ID
     * @return grade_item
     */
    public static function get_grade_item(int $id): grade_item {
        if (array_key_exists($id, self::$gradeitems)) {
            // Return from cache.
            return self::$gradeitems[$id];
        } else {
            // Retrieve from database and cache.
            $o = grade_item::fetch(["id" => $id]);
            if (!is_object($o)) {
                throw new dml_exception("Cannot retrieve grade_item", null, "Item id is '{$id}'");
            }
            self::$gradeitems[$id] = $o;
            return $o;
        }
    }

    /**
     * Retrieve grade grade from cache or db
     *
     * @param int $id Record ID
     * @return grade_grade
     * @throws dml_exception if record not found in database
     */
    public static function get_grade_grade(int $id) {
        if (array_key_exists($id, self::$gradegrades)) {
            // Return from cache.
            return self::$gradegrades[$id];
        } else {
            // Retrieve from database and cache.
            $o = grade_grade::fetch(["id" => $id]);
            if (!is_object($o)) {
                throw new dml_exception("Cannot retrieve grade_grade", null, "Item id is '{$id}'");
            }
            self::$gradegrades[$id] = $o;
            return $o;
        }
    }


    /**
     * Get final result for grade item by user id
     *
     * @param grade_item $gi Grade item
     * @param int $userid User id
     * @throws dml_exception if record not found in database
     * @return grade_grade
     */
    public static function find_grade_item_final(grade_item $gi, int $userid) {
        $itemid = $gi->id;
        if (array_key_exists($itemid, self::$gradegrademap) && array_key_exists($userid, self::$gradegrademap[$itemid])) {
            $ggid = self::$gradegrademap[$itemid][$userid];
            if (!empty($ggid)) {
                return self::get_grade_grade($ggid);
            } else {
                // Was preloaded, but no record. Return null like grade_item::get_final() does.
                return null;
            }
        } else {
            // Get from database and store in map.
            $gg = $gi->get_final($userid);
            if (is_array($gg)) {
                $gg = $gg[0];
            }
            if (!isset(self::$gradegrademap[$itemid])) {
                self::$gradegrademap[$itemid] = [];
            }
            self::$gradegrademap[$itemid][$userid] = $gg->id;
            self::$gradegrades[$gg->id] = $gg;
            return $gg;
        }
    }

    /**
     * Retrieve course modules for course from cache or db
     *
     * @param int $courseid Course id
     * @return cm_info[]
     */
    public static function find_cms(int $courseid) {
        $list = [];
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }
        // Return the results. Empty list means no criteria.
        foreach (self::coursecollection($courseid)->coursemodules as $id) {
            $cm = self::get_cm($id);
            $list[] = $cm;
        }
        return $list;
    }

    /**
     * Retrieve course modules for course from cache or db
     *
     * @param int $cmid Course module id
     * @param int $userid User id
     * @return stdClass
     */
    public static function find_cm_completion(int $cmid, int $userid) {
        global $DB;
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($cmid, self::$cmcompletionmap)) {
            self::$cmcompletionmap[$cmid] = [];
        }
        if (!array_key_exists($cmid, self::$cmcompletionmap[$cmid])) {
            $r = $DB->get_record('course_modules_completion', ["coursemoduleid" => $cmid, "userid" => $userid]);
            if ($r) {
                self::$cmcompletions[$r->id] = $r;
                self::$cmcompletionmap[$cmid][$userid] = $r->id;
            } else {
                self::$cmcompletionmap[$cmid][$userid] = 0;
            }
        }

        return self::get_cm_completion(self::$cmcompletionmap[$cmid][$userid]);
    }

    /**
     * Retrieve completion criteria for course from cache or db
     *
     * @param int $courseid Course id
     * @param int $criteriatype Filter on criteriatype leave empty or 0 to find all criteria.
     * @return completion_criteria[]
     */
    public static function find_criteria(int $courseid, int $criteriatype = 0) {
        $list = [];
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }
        // Return the results. Empty list means no criteria.
        foreach (self::coursecollection($courseid)->criteria as $id) {
            $c = self::get_criterion($id);
            if ($criteriatype == 0 || $c->criteriatype == $criteriatype) {
                $list[] = $c;
            }
        }

        return $list;
    }

    /**
     * Retrieve usercompetency from cache or db
     *
     * @param int $courseid Course id
     * @return completion_aggregation[]
     */
    public static function find_aggregations(int $courseid) {
        $list = [];
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }
        // Return the results. Empty list means no criteria.
        foreach (self::coursecollection($courseid)->aggr_methods as $type => $id) {
            $list[$type] = self::get_criterion($id);
        }
        return $list;
    }

    /**
     * Retrieve completion aggregation from cache or db
     *
     * @param int $courseid Course id
     * @param int $criteriatype Type of critera (use 0 for course type)
     * @return completion_aggregation
     */
    public static function find_aggregation(int $courseid, $criteriatype = 0) {
        $criteriatype = intval($criteriatype); // Ensure int.

        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }

        $cc = self::coursecollection($courseid);
        if (!array_key_exists($criteriatype, $cc->aggr_methods)) {
            $cc->aggr_methods[$criteriatype] = 0;
        }
        return self::get_completion_aggregation($cc->aggr_methods[$criteriatype]);
    }

    /**
     * Retrieve completion aggregation for a course from cache or db
     *
     * @param int $courseid Course id
     * @return completion_aggregation
     */
    public static function find_course_aggregation(int $courseid) {
        return self::find_aggregation($courseid, 0);
    }

    /**
     * Retrieve usercompetency from cache or db
     *
     * @param int $courseid Course id
     * @param int $userid User id
     * @return completion_completion
     */
    public static function find_course_completion(int $courseid, int $userid) {
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }
        // Preload completions if completions were not loaded.
        $cc = self::coursecollection($courseid);
        if (!array_key_exists($userid, self::coursecollection($courseid)->course_completions)) {
            self::preload_criteria_results_course($courseid, $userid);
            // Set to default 0 if record (still) not found.
            if (!array_key_exists($userid, $cc->course_completions)) {
                $cc->course_completions[$userid] = 0;
            }
        }
        return self::get_course_completion($cc->course_completions[$userid]);
    }

    /**
     * Retrieve criteria completion from cache or db
     *
     * @param int $courseid Course id
     * @param int $criteriaid Criterion id
     * @param int $userid User id
     * @return criteria_completion
     */
    public static function find_criteria_completion(int $courseid, int $criteriaid, int $userid) {
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }
        $cc = self::coursecollection($courseid);
        // Preload completions if completions were not loaded (for this user).
        if (!array_key_exists($criteriaid, $cc->critera_completions)
            || !array_key_exists($userid, $cc->critera_completions[$criteriaid])) {
            self::preload_criteria_results_course($courseid, $userid);

            $cc = self::coursecollection($courseid);
            // Create array for criteria id if it does not exist yet.
            if (!array_key_exists($criteriaid, $cc->critera_completions)) {
                $cc->critera_completions[$criteriaid] = [];
            }
            // Set to default if record (still) not found and store.
            if (!array_key_exists($userid, $cc->critera_completions[$criteriaid])) {
                $completion = new completion_criteria_completion([
                    'course'        => $courseid,
                    'userid'        => $userid,
                    'criteriaid'    => $criteriaid], false); // Creat dummy object.
                $completion->attach_criteria(self::get_criterion($criteriaid));
                $cc->critera_completions[$criteriaid][$userid] = $completion;
            }
        }
        // Below checks if object, since dummy objects cannot be mapped to things.
        if (is_object($cc->critera_completions[$criteriaid][$userid])) {
            return $cc->critera_completions[$criteriaid][$userid];
        } else {
            return self::get_criteria_completion($cc->critera_completions[$criteriaid][$userid]);
        }
    }

    /**
     * Retrieve usercompetency from cache or db
     *
     * @param int $courseid Course id
     * @param int $userid User id
     * @param int $criteriatype Filter on criteriatype leave empty or 0 to find all criteria.
     * @return criteria_completion[]
     */
    public static function find_criteria_completions(int $courseid, int $userid, int $criteriatype = 0) {
        // Preload if a cache record does not exist for the course.
        if (!array_key_exists($courseid, self::$coursecollection)) {
            self::preload_criteria_course($courseid);
        }
        $cc = self::coursecollection($courseid);
        // Preload completions if completions were not loaded (for this user).
        if (!array_key_exists($userid, $cc->user_critera_completions)) {
            self::preload_criteria_results_course($courseid, $userid);
            // Set to empty array if still not loaded - means not found.
            if (!array_key_exists($userid, $cc->user_critera_completions)) {
                $cc->user_critera_completions[$userid] = [];
            }
        }
        $list = [];
        foreach ($cc->user_critera_completions[$userid] as $id) {
            $ccrit = self::get_criteria_completion($id);
            if ($criteriatype == 0) {
                $list[] = $ccrit;
            } else {
                $crit = self::get_criterion($ccrit->criteriaid);
                if ($crit->criteriatype == $criteriatype) {
                    $list[] = $ccrit;
                }
            }
        }
        return $list;
    }

    /**
     * Retrieve usercompetency from cache or db
     *
     * @param int $cmid Course module id
     * @return grade_item
     */
    public static function find_cm_grade_item(int $cmid) {
        global $DB;
        // Preload if a cache record does not exist for the gradeitem.
        if (!array_key_exists($cmid, self::$gradeitemmap)) {
            $sql = "SELECT DISTINCT gi.*, cm.id as cmid
                    FROM {grade_items} gi
                    INNER JOIN (
                        SELECT cm1.*, m.name AS modname
                        FROM {course_modules} cm1
                        INNER JOIN {modules} m ON cm1.module = m.id
                        ) cm ON cm.modname = gi.itemmodule AND cm.instance = gi.iteminstance
                    WHERE cm.id = :cmid
                    ";
            $r = $DB->get_record_sql($sql, ["cmid" => $cmid], MUST_EXIST);
            $o = new grade_item($r, false);
            self::$gradeitems[$r->id] = $o; // Store in cache.
            self::$gradeitemmap[$r->cmid] = $r->id;
        }
        return self::get_grade_item(self::$gradeitemmap[$cmid]);
    }

    /**
     * Preload all grade items associated with a studyplan
     *
     * @param studyplan $plan The studyplan to preload for
     * @return void
     */
    public static function preload_criteria(studyplan $plan) {
        global $DB;
        $params = ["studyplanid" => $plan->id()];
        // Preload criteria.
        $sql = "SELECT DISTINCT crit.*
                FROM {course_completion_criteria} crit
                INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = crit.course
                INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                WHERE p.id = :studyplanid
                ORDER BY crit.course ASC, crit.criteriatype ASC
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = self::completion_criterion_from_record($r); // Create new item from record.
            self::$criteria[$r->id] = $o; // Store in cache.
            self::coursecollection($r->course)->criteria[] = $r->id; // Add id to course's id list.
        }
        $rs->close();

        // Preload completion aggregation.
        $sql = "SELECT DISTINCT am.*
                FROM {course_completion_aggr_methd} am
                INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = am.course
                INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                WHERE p.id = :studyplanid
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = new completion_aggregation((array)$r, false); // Create new item from record.
            self::$aggrmethods[$r->id] = $o; // Store in cache.

            $type = intval($r->criteriatype); // Converts NULL to 0.
            self::coursecollection($r->course)->aggr_methods[$type] = $r->id; // Add id to course's id list.
        }
        $rs->close();

        // Preload course modules.
        $sql = "SELECT DISTINCT cm.*
                FROM {course_modules} cm
                INNER JOIN {course_completion_criteria} crit ON crit.moduleinstance = cm.id
                INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = crit.course
                INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                WHERE p.id = :studyplanid
                AND crit.moduleinstance IS NOT NULL
                ORDER BY cm.course ASC
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = cm_info::create($r); // Create new item from record.
            if ($o) {
                self::$coursemodules[$r->id] = $o; // Store in cache.
                self::coursecollection($r->course)->coursemodules[] = $r->id; // Add id to course's id list.
            }
        }
        $rs->close();

        // Preload grade_items.
        $sql = "SELECT DISTINCT gi.*, cm.id as cmid
                FROM {grade_items} gi
                INNER JOIN (
                    SELECT cm1.*, m.name AS modname
                    FROM {course_modules} cm1
                    INNER JOIN {modules} m ON cm1.module = m.id
                    ) cm ON cm.modname = gi.itemmodule AND cm.instance = gi.iteminstance
                INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = cm.course
                INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                WHERE p.id = :studyplanid
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = new grade_item($r, false);
            self::$gradeitems[$r->id] = $o; // Store in cache.
            self::$gradeitemmap[$r->cmid] = $r->id;
        }
        $rs->close();

        // Preload grade scales.
        $sql = "SELECT DISTINCT s.*
                FROM {scale} s
                INNER JOIN {grade_items} gi ON gi.scaleid = s.id
                INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = gi.courseid
                INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                WHERE p.id = :studyplanid
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $gs = new grade_scale($r, false); // Create new item from record.
            self::$scales[$gs->id] = $gs; // Store in cache.
        }
        $rs->close();

    }

    /**
     * Preload all grade items associated with a course
     *
     * @param int $courseid The course to preload for
     * @return void
     */
    public static function preload_criteria_course(int $courseid) {
        global $DB;
        $cc = self::coursecollection($courseid);
        $params = ["courseid" => $courseid];

        // Preload criteria.
        $sql = "SELECT DISTINCT crit.*
                FROM {course_completion_criteria} crit
                WHERE crit.course = :courseid
                ORDER BY crit.course ASC, crit.criteriatype ASC
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = self::completion_criterion_from_record($r); // Create new item from record.
            self::$criteria[$r->id] = $o; // Store in cache.
            $cc->criteria[] = $r->id; // Add id to course's id list.
        }
        $rs->close();

        // Preload completion aggregation.
        $sql = "SELECT DISTINCT am.*
                FROM {course_completion_aggr_methd} am
                WHERE am.course = :courseid
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = new completion_aggregation((array)$r, false); // Create new item from record.
            self::$aggrmethods[$r->id] = $o; // Store in cache.
            $type = intval($r->criteriatype); // Converts NULL to 0.
            $cc->aggr_methods[$type] = $r->id; // Add id to course's id list.
        }
        $rs->close();

        // Preload course modules.
        $sql = "SELECT DISTINCT cm.*
                FROM {course_modules} cm
                INNER JOIN {course_completion_criteria} crit ON crit.moduleinstance = cm.id
                WHERE crit.course = :courseid
                AND crit.moduleinstance IS NOT NULL
                ORDER BY cm.course ASC
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = cm_info::create($r); // Create new item from record.
            if ($o) {
                self::$coursemodules[$r->id] = $o; // Store in cache.
                self::coursecollection($r->course)->coursemodules[] = $r->id; // Add id to course's id list.
            }
        }
        $rs->close();

        // Preload grade_items.
        $sql = "SELECT DISTINCT gi.*, cm.id as cmid
                FROM {grade_items} gi
                INNER JOIN (
                    SELECT cm1.*, m.name AS modname
                    FROM {course_modules} cm1
                    INNER JOIN {modules} m ON cm1.module = m.id
                    ) cm ON cm.modname = gi.itemmodule AND cm.instance = gi.iteminstance
                WHERE cm.course = :courseid
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $o = new grade_item($r, false);
            self::$gradeitems[$r->id] = $o; // Store in cache.
            self::$gradeitemmap[$r->cmid] = $r->id;
        }
        $rs->close();

        // Preload grade scales.
        $sql = "SELECT DISTINCT s.*
                FROM {scale} s
                INNER JOIN {grade_items} gi ON gi.scaleid = s.id
                WHERE gi.courseid = :courseid
                ";
        $rs = $DB->get_recordset_sql($sql, $params);
        foreach ($rs as $r) {
            $gs = new grade_scale($r, false); // Create new item from record.
            self::$scales[$gs->id] = $gs; // Store in cache.
        }
        $rs->close();
    }

    /**
     * Preload all grades for the specified users
     *
     * @param studyplan $plan The studyplan to preload for
     * @param array|int $userids Id's of the user to load the data for
     * @return void
     */
    public static function preload_criteria_results(studyplan $plan, $userids) {
        global $DB;
        if (is_numeric($userids)) {
            $userids = [$userids];
        }
        if (count($userids) > 0) {
            [$insql, $params] = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED, 'uid');
            $params["studyplanid"] = $plan->id();

            // Preload criteria.
            $sql = "SELECT DISTINCT cc.*
                    FROM {course_completion_crit_compl} cc
                    INNER JOIN {course_completion_criteria} crit ON crit.id = cc.criteriaid
                    INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = crit.course
                    INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                    INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                    INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                    WHERE p.id = :studyplanid
                    AND cc.userid {$insql}
                    ORDER BY cc.course ASC
                    ";
            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                $o = new completion_criteria_completion((array)$r, false); // Create new item from record.
                self::$criteriacompletions[$r->id] = $o; // Store in cache.

                $cc = self::coursecollection($r->course);
                if (!array_key_exists($r->criteriaid, $cc->critera_completions)) {
                    $cc->critera_completions[$r->criteriaid] = [];
                };
                $cc->critera_completions[$r->criteriaid][$r->userid] = $r->id; // Add id to course's id list.
                $cc->user_critera_completions[$r->userid][$r->criteriaid] = $r->id; // Add id to courses user is list.
            }
            $rs->close();

            // Preload criteria.
            $sql = "SELECT DISTINCT cc.*
                    FROM {course_completions} cc
                    INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = cc.course
                    INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                    INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                    INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                    WHERE p.id = :studyplanid
                    AND cc.userid {$insql}
                    ORDER BY cc.course ASC
                    ";
            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                $o = new completion_completion((array)$r, false); // Create new item from record.
                self::$coursecompletions[$r->id] = $o; // Store in cache.
                self::coursecollection($r->course)->course_completions[$r->userid] = $r->id; // Add id to course's id list.
            }
            $rs->close();

            // Preload course module completion.
            $sql = "SELECT DISTINCT cmc.*, cc.course
                    FROM {course_modules_completion} cmc
                    INNER JOIN {course_completions} cc ON cc.id = cmc.coursemoduleid
                    INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = cc.course
                    INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                    INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                    INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                    WHERE p.id = :studyplanid
                    AND cc.userid {$insql}
                    ORDER BY cc.course ASC
                    ";
            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                self::$cmcompletions[$r->id] = $r; // Store in cache.
                if (!array_key_exists($r->coursemoduleid, self::$cmcompletionmap)) {
                    self::$cmcompletionmap[$r->coursemoduleid] = [];
                }
                self::$cmcompletionmap[$r->coursemoduleid][$r->userid] = $r->id; // Add id to cm's result list.
            }
            $rs->close();

            // Preload grade grades.
            $sql = "SELECT gg.*, gi.courseid AS courseid
                    FROM {grade_grades} gg
                    INNER JOIN {grade_items} gi ON gg.itemid = gi.id
                    INNER JOIN {local_treestudyplan_item} ti ON ti.course_id = gi.courseid
                    INNER JOIN {local_treestudyplan_line} tl ON tl.id = ti.line_id
                    INNER JOIN {local_treestudyplan_page} tp ON tp.id = tl.page_id
                    INNER JOIN {local_treestudyplan} p ON p.id = tp.studyplan_id
                    WHERE p.id = :studyplanid
                    AND gg.userid {$insql}
                    ORDER BY gi.courseid ASC, gi.sortorder ASC
                    ";

            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                $gg = new grade_grade($r, false); // Create new item from record.
                self::$gradegrades[$gg->id] = $gg; // Store in cache.

                // Add to user result map for itemids.
                if (!array_key_exists($r->itemid, self::$gradegrademap)) {
                    self::$gradegrademap[$r->itemid] = [];
                }
                self::$gradegrademap[$r->itemid][$r->userid] = $gg->id;
            }
            $rs->close();
        }
    }

    /**
     * Preload all grades for the specified users
     *
     * @param int $courseid The course to preload for
     * @param array|int $userids Id's of the user to load the data for
     * @return void
     */
    public static function preload_criteria_results_course(int $courseid, $userids) {
        global $DB;
        if (is_numeric($userids)) {
            $userids = [$userids];
        }

        if (count($userids) > 0) {
            [$insql, $params] = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED, 'uid');
            $params["courseid"] = $courseid;

            // Preload criteria.
            $sql = "SELECT DISTINCT cc.*
                    FROM {course_completion_crit_compl} cc
                    INNER JOIN {course_completion_criteria} crit ON crit.id = cc.criteriaid
                    WHERE crit.course = :courseid
                    AND cc.userid {$insql}
                    ORDER BY cc.course ASC
                    ";

            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                $o = new completion_criteria_completion((array)$r, false); // Create new item from record.
                self::$criteriacompletions[$r->id] = $o; // Store in cache.

                $cc = self::coursecollection($courseid);
                if (!array_key_exists($r->criteriaid, $cc->critera_completions)) {
                    $cc->critera_completions[$r->criteriaid] = [];
                };
                $cc->critera_completions[$r->criteriaid][$r->userid] = $r->id; // Add id to course's criteria id list.
                $cc->user_critera_completions[$r->userid][$r->criteriaid] = $r->id; // Add id to courses user is list.
            }
            $rs->close();

            // Preload criteria.
            $sql = "SELECT DISTINCT cc.*
                    FROM {course_completions} cc
                    WHERE cc.course = :courseid
                    AND cc.userid {$insql}
                    ORDER BY cc.course ASC
                    ";
            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                $o = new completion_completion((array)$r, false); // Create new item from record.
                self::$coursecompletions[$r->id] = $o; // Store in cache.
                self::coursecollection($courseid)->course_completions[$r->userid] = $r->id; // Add id to course's id list.
            }
            $rs->close();

            // Preload criteria.
            $sql = "SELECT DISTINCT cmc.*, cc.course
                    FROM {course_modules_completion} cmc
                    INNER JOIN {course_completions} cc ON cc.id = cmc.coursemoduleid
                    WHERE cc.course = :courseid
                    AND cc.userid {$insql}
                    ORDER BY cc.course ASC
                    ";
            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                self::$cmcompletions[$r->id] = $r; // Store in cache.
                if (!array_key_exists($r->coursemoduleid, self::$cmcompletionmap)) {
                    self::$cmcompletionmap[$r->coursemoduleid] = [];
                }
                self::$cmcompletionmap[$r->coursemoduleid][$r->userid] = $r->id; // Add id to cm's result list.
            }
            $rs->close();

            // Preload grade grades.
            $sql = "SELECT gg.*, gi.courseid AS courseid
                    FROM {grade_grades} gg
                    INNER JOIN {grade_items} gi ON gg.itemid = gi.id
                    WHERE gi.courseid = :courseid
                    AND gg.userid {$insql}
                    ORDER BY gi.courseid ASC, gi.sortorder ASC
                    ";

            $rs = $DB->get_recordset_sql($sql, $params);
            foreach ($rs as $r) {
                $gg = new grade_grade($r, false); // Create new item from record.
                self::$gradegrades[$gg->id] = $gg; // Store in cache.

                // Add to user result map for itemids.
                if (!array_key_exists($r->itemid, self::$gradegrademap)) {
                    self::$gradegrademap[$r->itemid] = [];
                }
                self::$gradegrademap[$r->itemid][$r->userid] = $gg->id;
            }
            $rs->close();
        }
    }

}
