/** * List the user competencies to review. * * The method returns values in this format: * * array( * 'competencies' => array( * (stdClass)( * 'usercompetency' => (user_competency), * 'competency' => (competency), * 'user' => (user) * ) * ), * 'count' => (int) * ) * * @param int $skip The number of records to skip. * @param int $limit The number of results to return. * @param int $userid The user we're getting the competencies to review for. * @return array Containing the keys 'count', and 'competencies'. The 'competencies' key contains an object * which contains 'competency', 'usercompetency' and 'user'. */ public static function list_user_competencies_to_review($skip = 0, $limit = 50, $userid = null) { global $DB, $USER; static::require_enabled(); if ($userid === null) { $userid = $USER->id; } $capability = 'moodle/competency:usercompetencyreview'; $ucfields = user_competency::get_sql_fields('uc', 'uc_'); $compfields = competency::get_sql_fields('c', 'c_'); $usercols = array('id') + get_user_fieldnames(); $userfields = array(); foreach ($usercols as $field) { $userfields[] = "u." . $field . " AS usr_" . $field; } $userfields = implode(',', $userfields); $select = "SELECT {$ucfields}, {$compfields}, {$userfields}"; $countselect = "SELECT COUNT('x')"; $sql = " FROM {" . user_competency::TABLE . "} uc\n JOIN {" . competency::TABLE . "} c\n ON c.id = uc.competencyid\n JOIN {user} u\n ON u.id = uc.userid\n WHERE (uc.status = :waitingforreview\n OR (uc.status = :inreview AND uc.reviewerid = :reviewerid))"; $ordersql = " ORDER BY c.shortname ASC"; $params = array('inreview' => user_competency::STATUS_IN_REVIEW, 'reviewerid' => $userid, 'waitingforreview' => user_competency::STATUS_WAITING_FOR_REVIEW); $countsql = $countselect . $sql; // Primary check to avoid the hard work of getting the users in which the user has permission. $count = $DB->count_records_sql($countselect . $sql, $params); if ($count < 1) { return array('count' => 0, 'competencies' => array()); } // TODO MDL-52243 Use core function. list($insql, $inparams) = self::filter_users_with_capability_on_user_context_sql($capability, $userid, SQL_PARAMS_NAMED); $params += $inparams; $countsql = $countselect . $sql . " AND uc.userid {$insql}"; $getsql = $select . $sql . " AND uc.userid {$insql} " . $ordersql; // Extracting the results. $competencies = array(); $records = $DB->get_recordset_sql($getsql, $params, $skip, $limit); foreach ($records as $record) { $objects = (object) array('usercompetency' => new user_competency(0, user_competency::extract_record($record, 'uc_')), 'competency' => new competency(0, competency::extract_record($record, 'c_')), 'user' => persistent::extract_record($record, 'usr_')); $competencies[] = $objects; } $records->close(); return array('count' => $DB->count_records_sql($countsql, $params), 'competencies' => $competencies); }