/**
* 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);
}
