/**
* Initialise the iterator
* @return boolean success
*/
function init()
{
global $CFG;
$this->close();
grade_regrade_final_grades($this->course->id);
$course_item = grade_item::fetch_course_item($this->course->id);
if ($course_item->needsupdate) {
// can not calculate all final grades - sorry
return false;
}
if (strpos($CFG->gradebookroles, ',') !== false) {
$gradebookroles = " = {$CFG->gradebookroles}";
} else {
$gradebookroles = " IN ({$CFG->gradebookroles})";
}
$relatedcontexts = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $this->course->id));
if ($this->groupid) {
$groupsql = "INNER JOIN {$CFG->prefix}groups_members gm ON gm.userid = u.id";
$groupwheresql = "AND gm.groupid = {$this->groupid}";
} else {
$groupsql = "";
$groupwheresql = "";
}
$users_sql = "SELECT u.*\n FROM {$CFG->prefix}user u\n INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n {$groupsql}\n WHERE ra.roleid {$gradebookroles}\n AND ra.contextid {$relatedcontexts}\n {$groupwheresql}\n ORDER BY u.id ASC";
$this->users_rs = get_recordset_sql($users_sql);
if (!empty($this->grade_items)) {
$itemids = array_keys($this->grade_items);
$itemids = implode(',', $itemids);
$grades_sql = "SELECT g.*\n FROM {$CFG->prefix}grade_grades g\n INNER JOIN {$CFG->prefix}user u ON g.userid = u.id\n INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n {$groupsql}\n WHERE ra.roleid {$gradebookroles}\n AND ra.contextid {$relatedcontexts}\n AND g.itemid IN ({$itemids})\n {$groupwheresql}\n ORDER BY g.userid ASC, g.itemid ASC";
$this->grades_rs = get_recordset_sql($grades_sql);
}
return true;
}
/**
* Initialise the iterator
* @return boolean success
*/
public function init()
{
global $CFG, $DB;
$this->close();
grade_regrade_final_grades($this->course->id);
$course_item = grade_item::fetch_course_item($this->course->id);
if ($course_item->needsupdate) {
// can not calculate all final grades - sorry
return false;
}
$coursecontext = get_context_instance(CONTEXT_COURSE, $this->course->id);
$relatedcontexts = get_related_contexts_string($coursecontext);
list($gradebookroles_sql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
//limit to users with an active enrolment
list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext);
$params = array_merge($params, $enrolledparams);
if ($this->groupid) {
$groupsql = "INNER JOIN {groups_members} gm ON gm.userid = u.id";
$groupwheresql = "AND gm.groupid = :groupid";
// $params contents: gradebookroles
$params['groupid'] = $this->groupid;
} else {
$groupsql = "";
$groupwheresql = "";
}
if (empty($this->sortfield1)) {
// we must do some sorting even if not specified
$ofields = ", u.id AS usrt";
$order = "usrt ASC";
} else {
$ofields = ", u.{$this->sortfield1} AS usrt1";
$order = "usrt1 {$this->sortorder1}";
if (!empty($this->sortfield2)) {
$ofields .= ", u.{$this->sortfield2} AS usrt2";
$order .= ", usrt2 {$this->sortorder2}";
}
if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
// user order MUST be the same in both queries,
// must include the only unique user->id if not already present
$ofields .= ", u.id AS usrt";
$order .= ", usrt ASC";
}
}
// $params contents: gradebookroles and groupid (for $groupwheresql)
$users_sql = "SELECT u.* {$ofields}\n FROM {user} u\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$groupsql}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookroles_sql}\n AND ra.contextid {$relatedcontexts}\n ) rainner ON rainner.userid = u.id\n WHERE u.deleted = 0\n {$groupwheresql}\n ORDER BY {$order}";
$this->users_rs = $DB->get_recordset_sql($users_sql, $params);
if (!empty($this->grade_items)) {
$itemids = array_keys($this->grade_items);
list($itemidsql, $grades_params) = $DB->get_in_or_equal($itemids, SQL_PARAMS_NAMED, 'items');
$params = array_merge($params, $grades_params);
// $params contents: gradebookroles, enrolledparams, groupid (for $groupwheresql) and itemids
$grades_sql = "SELECT g.* {$ofields}\n FROM {grade_grades} g\n JOIN {user} u ON g.userid = u.id\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$groupsql}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookroles_sql}\n AND ra.contextid {$relatedcontexts}\n ) rainner ON rainner.userid = u.id\n WHERE u.deleted = 0\n AND g.itemid {$itemidsql}\n {$groupwheresql}\n ORDER BY {$order}, g.itemid ASC";
$this->grades_rs = $DB->get_recordset_sql($grades_sql, $params);
} else {
$this->grades_rs = false;
}
return true;
}
/**
* Initialise the iterator
* @return boolean success
*/
function init()
{
global $CFG;
$this->close();
grade_regrade_final_grades($this->course->id);
$course_item = grade_item::fetch_course_item($this->course->id);
if ($course_item->needsupdate) {
// can not calculate all final grades - sorry
return false;
}
if (strpos($CFG->gradebookroles, ',') === false) {
$gradebookroles = " = {$CFG->gradebookroles}";
} else {
$gradebookroles = " IN ({$CFG->gradebookroles})";
}
$relatedcontexts = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $this->course->id));
if ($this->groupid) {
$groupsql = "INNER JOIN {$CFG->prefix}groups_members gm ON gm.userid = u.id";
$groupwheresql = "AND gm.groupid = {$this->groupid}";
} else {
$groupsql = "";
$groupwheresql = "";
}
if (empty($this->sortfield1)) {
// we must do some sorting even if not specified
$ofields = ", u.id AS usrt";
$order = "usrt ASC";
} else {
$ofields = ", u.{$this->sortfield1} AS usrt1";
$order = "usrt1 {$this->sortorder1}";
if (!empty($this->sortfield2)) {
$ofields .= ", u.{$this->sortfield2} AS usrt2";
$order .= ", usrt2 {$this->sortorder2}";
}
if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
// user order MUST be the same in both queries, must include the only unique user->id if not already present
$ofields .= ", u.id AS usrt";
$order .= ", usrt ASC";
}
}
$users_sql = "SELECT u.* {$ofields}\n FROM {$CFG->prefix}user u\n INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n {$groupsql}\n WHERE ra.roleid {$gradebookroles}\n AND ra.contextid {$relatedcontexts}\n {$groupwheresql}\n ORDER BY {$order}";
$this->users_rs = get_recordset_sql($users_sql);
if (!empty($this->grade_items)) {
$itemids = array_keys($this->grade_items);
$itemids = implode(',', $itemids);
$grades_sql = "SELECT g.* {$ofields}\n FROM {$CFG->prefix}grade_grades g\n INNER JOIN {$CFG->prefix}user u ON g.userid = u.id\n INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n {$groupsql}\n WHERE ra.roleid {$gradebookroles}\n AND ra.contextid {$relatedcontexts}\n AND g.itemid IN ({$itemids})\n {$groupwheresql}\n ORDER BY {$order}, g.itemid ASC";
$this->grades_rs = get_recordset_sql($grades_sql);
} else {
$this->grades_rs = false;
}
return true;
}
public function find_users($search)
{
global $DB;
$context = context_course::instance($this->courseid);
list($wherecondition, $params) = $this->search_sql($search, 'u');
list($enrolledsql, $enrolledparams) = get_enrolled_sql($context, '', $this->groupid, true);
$params = array_merge($params, $enrolledparams);
$params['courseid'] = $this->courseid;
$fields = 'SELECT r.id AS roleid, ' . 'r.shortname AS roleshortname, ' . 'r.name AS rolename, ' . 'u.id AS userid, ' . $this->required_fields_sql('u');
$countfields = 'SELECT COUNT(1)';
$sql = ' FROM {user} u JOIN (' . $enrolledsql . ') e ON e.id = u.id' . ' LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid ' . get_related_contexts_string($context) . ')' . ' LEFT JOIN {role} r ON r.id = ra.roleid' . ' WHERE ' . $wherecondition;
$order = ' ORDER BY r.sortorder, u.lastname ASC, u.firstname ASC';
if (!$this->is_validating()) {
$count = $DB->count_records_sql($countfields . $sql, $params);
if ($count > 100) {
return $this->too_many_results($search, $count);
}
}
$rs = $DB->get_recordset_sql($fields . $sql . $order, $params);
$roles = groups_calculate_role_people($rs, $context);
return $this->convert_array_format($roles, $search);
}
public function find_users($search)
{
global $DB;
// Get list of allowed roles.
$context = get_context_instance(CONTEXT_COURSE, $this->courseid);
if ($validroleids = groups_get_possible_roles($context)) {
list($roleids, $roleparams) = $DB->get_in_or_equal($validroleids, SQL_PARAMS_NAMED, 'r');
} else {
$roleids = " = -1";
$roleparams = array();
}
// Get the search condition.
list($searchcondition, $searchparams) = $this->search_sql($search, 'u');
// Build the SQL
list($enrolsql, $enrolparams) = get_enrolled_sql($context);
$fields = "SELECT r.id AS roleid, r.shortname AS roleshortname, r.name AS rolename, u.id AS userid,\n " . $this->required_fields_sql('u') . ",\n (SELECT count(igm.groupid)\n FROM {groups_members} igm\n JOIN {groups} ig ON igm.groupid = ig.id\n WHERE igm.userid = u.id AND ig.courseid = :courseid) AS numgroups";
$sql = " FROM {user} u\n JOIN ({$enrolsql}) e ON e.id = u.id\n LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid " . get_related_contexts_string($context) . " AND ra.roleid {$roleids})\n LEFT JOIN {role} r ON r.id = ra.roleid\n WHERE u.deleted = 0\n AND u.id NOT IN (SELECT userid\n FROM {groups_members}\n WHERE groupid = :groupid)\n AND {$searchcondition}";
$orderby = "ORDER BY u.lastname, u.firstname";
$params = array_merge($searchparams, $roleparams, $enrolparams);
$params['courseid'] = $this->courseid;
$params['groupid'] = $this->groupid;
if (!$this->is_validating()) {
$potentialmemberscount = $DB->count_records_sql("SELECT COUNT(DISTINCT u.id) {$sql}", $params);
if ($potentialmemberscount > group_non_members_selector::MAX_USERS_PER_PAGE) {
return $this->too_many_results($search, $potentialmemberscount);
}
}
$rs = $DB->get_recordset_sql("{$fields} {$sql} {$orderby}", $params);
$roles = groups_calculate_role_people($rs, $context);
//don't hold onto user IDs if we're doing validation
if (empty($this->validatinguserids)) {
if ($roles) {
foreach ($roles as $k => $v) {
if ($v) {
foreach ($v->users as $uid => $userobject) {
$this->potentialmembersids[] = $uid;
}
}
}
}
}
return $this->convert_array_format($roles, $search);
}
/**
* This function returns an array of grades that were included in the import,
* but wherer the user does not currenly have a graded role on the course. These gradse
* are still stored in the database, but will not be visible in the gradebook unless
* this user subsequently enrols on the course in a graded roles.
*
* The returned objects have fields user firstname, lastname and useridnumber, and gradeidnumber.
*
* @param integer $importcode import batch identifier
* @param integer $courseid the course we are importing to.
* @return mixed and array of user objects, or false if none.
*/
function get_unenrolled_users_in_import($importcode, $courseid)
{
global $CFG, $DB;
$relatedctxcondition = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $courseid));
list($usql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles));
$sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber,\n COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber\n FROM\n {grade_import_values} giv\n JOIN {user} u ON giv.userid = u.id\n LEFT JOIN {grade_items} gi ON gi.id = giv.itemid\n LEFT JOIN {grade_import_newitem} gin ON gin.id = giv.newgradeitem\n LEFT JOIN {role_assignments} ra ON (giv.userid = ra.userid AND\n ra.roleid {$usql} AND\n ra.contextid {$relatedctxcondition})\n WHERE giv.importcode = ?\n AND ra.id IS NULL\n ORDER BY gradeidnumber, u.lastname, u.firstname";
$params[] = $importcode;
return $DB->get_records_sql($sql, $params);
}
public function find_users($search) {
global $DB;
// Get list of allowed roles.
$context = context_course::instance($this->courseid);
if ($validroleids = groups_get_possible_roles($context)) {
list($roleids, $roleparams) = $DB->get_in_or_equal($validroleids, SQL_PARAMS_NAMED, 'r');
} else {
$roleids = " = -1";
$roleparams = array();
}
// Get the search condition.
list($searchcondition, $searchparams) = $this->search_sql($search, 'u');
// Build the SQL
list($enrolsql, $enrolparams) = get_enrolled_sql($context);
$fields = "SELECT r.id AS roleid, u.id AS userid,
" . $this->required_fields_sql('u') . ",
(SELECT count(igm.groupid)
FROM {groups_members} igm
JOIN {groups} ig ON igm.groupid = ig.id
WHERE igm.userid = u.id AND ig.courseid = :courseid) AS numgroups";
$sql = " FROM {user} u
JOIN ($enrolsql) e ON e.id = u.id
LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid " . get_related_contexts_string($context) . " AND ra.roleid $roleids)
LEFT JOIN {role} r ON r.id = ra.roleid
LEFT JOIN {groups_members} gm ON (gm.userid = u.id AND gm.groupid = :groupid)
WHERE u.deleted = 0
AND gm.id IS NULL
AND $searchcondition";
list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
$orderby = ' ORDER BY ' . $sort;
$params = array_merge($searchparams, $roleparams, $enrolparams);
$params['courseid'] = $this->courseid;
$params['groupid'] = $this->groupid;
if (!$this->is_validating()) {
$potentialmemberscount = $DB->count_records_sql("SELECT COUNT(DISTINCT u.id) $sql", $params);
if ($potentialmemberscount > group_non_members_selector::MAX_USERS_PER_PAGE) {
return $this->too_many_results($search, $potentialmemberscount);
}
}
$rs = $DB->get_recordset_sql("$fields $sql $orderby", array_merge($params, $sortparams));
$roles = groups_calculate_role_people($rs, $context);
//don't hold onto user IDs if we're doing validation
if (empty($this->validatinguserids) ) {
if($roles) {
foreach($roles as $k=>$v) {
if($v) {
foreach($v->users as $uid=>$userobject) {
$this->potentialmembersids[] = $uid;
}
}
}
}
}
return $this->convert_array_format($roles, $search);
}
/**
* Search through course users
*
* If $coursid specifies the site course then this function searches
* through all undeleted and confirmed users
* @param int $courseid The course in question.
* @param string $searchtext the text to search for
* @param string $sort the column name to order by
* @param string $exceptions comma separated list of user IDs to exclude
* @return array An array of {@link $USER} records.
*/
function message_search_users($courseid, $searchtext, $sort='', $exceptions='') {
global $CFG, $USER, $DB;
$fullname = $DB->sql_fullname();
if (!empty($exceptions)) {
$except = ' AND u.id NOT IN ('. $exceptions .') ';
} else {
$except = '';
}
if (!empty($sort)) {
$order = ' ORDER BY '. $sort;
} else {
$order = '';
}
$ufields = user_picture::fields('u');
if (!$courseid or $courseid == SITEID) {
$params = array($USER->id, "%$searchtext%");
return $DB->get_records_sql("SELECT $ufields, mc.id as contactlistid, mc.blocked
FROM {user} u
LEFT JOIN {message_contacts} mc
ON mc.contactid = u.id AND mc.userid = ?
WHERE u.deleted = '0' AND u.confirmed = '1'
AND (".$DB->sql_like($fullname, '?', false).")
$except
$order", $params);
} else {
//TODO: add enabled enrolment join here (skodak)
$context = get_context_instance(CONTEXT_COURSE, $courseid);
$contextlists = get_related_contexts_string($context);
// everyone who has a role assignment in this course or higher
$params = array($USER->id, "%$searchtext%");
$users = $DB->get_records_sql("SELECT $ufields, mc.id as contactlistid, mc.blocked
FROM {user} u
JOIN {role_assignments} ra ON ra.userid = u.id
LEFT JOIN {message_contacts} mc
ON mc.contactid = u.id AND mc.userid = ?
WHERE u.deleted = '0' AND u.confirmed = '1'
AND ra.contextid $contextlists
AND (".$DB->sql_like($fullname, '?', false).")
$except
$order", $params);
return $users;
}
}
/**
* Display the report.
*/
public function display($game, $cm, $course)
{
global $CFG, $SESSION, $DB;
// Define some strings.
$strreallydel = addslashes(get_string('deleteattemptcheck', 'game'));
$strtimeformat = get_string('strftimedatetime');
$strreviewquestion = get_string('reviewresponse', 'quiz');
// Only print headers if not asked to download data.
if (!($download = optional_param('download', null))) {
$this->print_header_and_tabs($cm, $course, $game, $reportmode = "overview");
}
// Deal with actions.
$action = optional_param('action', '', PARAM_ACTION);
switch ($action) {
case 'delete':
// Some attempts need to be deleted.
$attemptids = optional_param('attemptid', array(), PARAM_INT);
foreach ($attemptids as $attemptid) {
if ($attemptid && ($todelete = get_record('game_attempts', 'id', $attemptid))) {
delete_records('game_attempts', 'id', $attemptid);
delete_records('game_queries', 'attemptid', $attemptid);
// Search game_attempts for other instances by this user.
// If none, then delete record for this game, this user from game_grades.
// else recalculate best grade.
$userid = $todelete->userid;
if (!record_exists('game_attempts', 'userid', $userid, 'gameid', $game->id)) {
delete_records('game_grades', 'userid', $userid, 'gameid', $game->id);
} else {
game_save_best_score($game, $userid);
}
}
}
break;
}
// Print information on the number of existing attempts.
if (!$download) {
// Do not print notices when downloading.
if ($attemptnum = count_records('game_attempts', 'gameid', $game->id)) {
$a = new stdClass();
$a->attemptnum = $attemptnum;
$a->studentnum = count_records_select('game_attempts', "gameid = '{$game->id}' AND preview = '0'", 'COUNT(DISTINCT userid)');
$a->studentstring = $course->students;
notify(get_string('numattempts', 'game', $a));
}
}
$context = get_context_instance(CONTEXT_MODULE, $cm->id);
// Find out current groups mode.
if ($groupmode = groupmode($course, $cm)) {
// Groups are being used.
if (!$download) {
$currentgroup = setup_and_print_groups($course, $groupmode, "report.php?id={$cm->id}&mode=overview");
} else {
$currentgroup = get_and_set_current_group($course, $groupmode);
}
} else {
$currentgroup = get_and_set_current_group($course, $groupmode);
}
// Set table options.
$noattempts = optional_param('noattempts', 0, PARAM_INT);
$detailedmarks = optional_param('detailedmarks', 0, PARAM_INT);
$pagesize = optional_param('pagesize', 10, PARAM_INT);
$hasfeedback = game_has_feedback($game->id) && $game->grade > 1.0E-7;
if ($pagesize < 1) {
$pagesize = 10;
}
// Now check if asked download of data.
if ($download) {
$filename = clean_filename("{$course->shortname} " . format_string($game->name, true));
$sort = '';
}
// Define table columns.
$tablecolumns = array('checkbox', 'picture', 'fullname', 'timestart', 'timefinish', 'duration');
$tableheaders = array(null, '', get_string('fullname'), get_string('startedon', 'game'), get_string('timecompleted', 'game'), get_string('attemptduration', 'game'));
if ($game->grade) {
$tablecolumns[] = 'grade';
$tableheaders[] = get_string('grade', 'game') . '/' . $game->grade;
}
if ($detailedmarks) {
// We want to display marks for all questions.
// Start by getting all questions.
$questionlist = game_questions_in_game($game->questions);
$questionids = explode(',', $questionlist);
$sql = "SELECT q.*, i.score AS maxgrade, i.id AS instance" . " FROM {question} q," . " {game_queries} i" . " WHERE i.gameid = '{$game->id}' AND q.id = i.questionid" . " AND q.id IN ({$questionlist})";
if (!($questions = get_records_sql($sql))) {
print_error('No questions found');
}
$number = 1;
foreach ($questionids as $key => $id) {
if ($questions[$id]->length) {
// Only print questions of non-zero length.
$tablecolumns[] = '$' . $id;
$tableheaders[] = '#' . $number;
$questions[$id]->number = $number;
$number += $questions[$id]->length;
} else {
// Get rid of zero length questions.
unset($questions[$id]);
unset($questionids[$key]);
}
}
}
if ($hasfeedback) {
$tablecolumns[] = 'feedbacktext';
$tableheaders[] = get_string('feedback', 'game');
}
if (!$download) {
// Set up the table.
$table = new flexible_table('mod-game-report-overview-report');
$table->define_columns($tablecolumns);
$table->define_headers($tableheaders);
$table->define_baseurl($CFG->wwwroot . '/mod/game/report.php?mode=overview&id=' . $cm->id . '&noattempts=' . $noattempts . '&detailedmarks=' . $detailedmarks . '&pagesize=' . $pagesize);
$table->sortable(true);
$table->collapsible(true);
$table->column_suppress('picture');
$table->column_suppress('fullname');
$table->column_class('picture', 'picture');
$table->set_attribute('cellspacing', '0');
$table->set_attribute('id', 'attempts');
$table->set_attribute('class', 'generaltable generalbox');
// Start working -- this is necessary as soon as the niceties are over.
$table->setup();
} else {
if ($download == 'ODS') {
require_once "{$CFG->libdir}/odslib.class.php";
$filename .= ".ods";
// Creating a workbook.
$workbook = new MoodleODSWorkbook("-");
// Sending HTTP headers.
$workbook->send($filename);
// Creating the first worksheet.
$sheettitle = get_string('reportoverview', 'game');
$myxls =& $workbook->add_worksheet($sheettitle);
// Format types.
$format =& $workbook->add_format();
$format->set_bold(0);
$formatbc =& $workbook->add_format();
$formatbc->set_bold(1);
$formatbc->set_align('center');
$formatb =& $workbook->add_format();
$formatb->set_bold(1);
$formaty =& $workbook->add_format();
$formaty->set_bg_color('yellow');
$formatc =& $workbook->add_format();
$formatc->set_align('center');
$formatr =& $workbook->add_format();
$formatr->set_bold(1);
$formatr->set_color('red');
$formatr->set_align('center');
$formatg =& $workbook->add_format();
$formatg->set_bold(1);
$formatg->set_color('green');
$formatg->set_align('center');
// Here starts workshhet headers.
$headers = array(get_string('fullname'), get_string('startedon', 'game'), get_string('timecompleted', 'game'), get_string('attemptduration', 'game'));
if ($game->grade) {
$headers[] = get_string('grade', 'game') . '/' . $game->grade;
}
if ($detailedmarks) {
foreach ($questionids as $id) {
$headers[] = '#' . $questions[$id]->number;
}
}
if ($hasfeedback) {
$headers[] = get_string('feedback', 'game');
}
$colnum = 0;
foreach ($headers as $item) {
$myxls->write(0, $colnum, $item, $formatbc);
$colnum++;
}
$rownum = 1;
} else {
if ($download == 'Excel') {
require_once "{$CFG->libdir}/excellib.class.php";
$filename .= ".xls";
// Creating a workbook.
$workbook = new MoodleExcelWorkbook("-");
// Sending HTTP headers.
$workbook->send($filename);
// Creating the first worksheet.
$sheettitle = get_string('reportoverview', 'game');
$myxls =& $workbook->add_worksheet($sheettitle);
// Format types.
$format =& $workbook->add_format();
$format->set_bold(0);
$formatbc =& $workbook->add_format();
$formatbc->set_bold(1);
$formatbc->set_align('center');
$formatb =& $workbook->add_format();
$formatb->set_bold(1);
$formaty =& $workbook->add_format();
$formaty->set_bg_color('yellow');
$formatc =& $workbook->add_format();
$formatc->set_align('center');
$formatr =& $workbook->add_format();
$formatr->set_bold(1);
$formatr->set_color('red');
$formatr->set_align('center');
$formatg =& $workbook->add_format();
$formatg->set_bold(1);
$formatg->set_color('green');
$formatg->set_align('center');
// Here starts workshhet headers.
$headers = array(get_string('fullname'), get_string('startedon', 'game'), get_string('timecompleted', 'game'), get_string('attemptduration', 'game'));
if ($game->grade) {
$headers[] = get_string('grade', 'game') . '/' . $game->grade;
}
if ($detailedmarks) {
foreach ($questionids as $id) {
$headers[] = '#' . $questions[$id]->number;
}
}
if ($hasfeedback) {
$headers[] = get_string('feedback', 'game');
}
$colnum = 0;
foreach ($headers as $item) {
$myxls->write(0, $colnum, $item, $formatbc);
$colnum++;
}
$rownum = 1;
} else {
if ($download == 'CSV') {
$filename .= ".txt";
header("Content-Type: application/download\n");
header("Content-Disposition: attachment; filename=\"{$filename}\"");
header("Expires: 0");
header("Cache-Control: must-revalidate,post-check=0,pre-check=0");
header("Pragma: public");
$headers = get_string('fullname') . "\t" . get_string('startedon', 'game') . "\t" . get_string('timecompleted', 'game') . "\t" . get_string('attemptduration', 'game');
if ($game->grade) {
$headers .= "\t" . get_string('grade', 'game') . "/" . $game->grade;
}
if ($detailedmarks) {
foreach ($questionids as $id) {
$headers .= "\t#" . $questions[$id]->number;
}
}
if ($hasfeedback) {
$headers .= "\t" . get_string('feedback', 'game');
}
echo $headers . " \n";
}
}
}
}
$contextlists = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $course->id));
// Construct the SQL.
$select = 'SELECT qa.id,' . sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')) . ' AS uniqueid, ' . 'qa.id as attemptuniqueid, qa.id AS attempt, u.id AS userid, u.firstname, u.lastname, u.picture, ' . 'qa.score, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
if ($course->id != SITEID) {
// This is too complicated, so just do it for each of the four cases.
if (!empty($currentgroup) && empty($noattempts)) {
// We want a particular group and we only want to see students WITH attempts.
// So join on groups_members and do an inner join on attempts.
$from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . groups_members_join_sql() . 'JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
$where = ' WHERE ra.contextid ' . $contextlists . ' AND ' . groups_members_where_sql($currentgroup) . ' AND qa.preview = 0';
} else {
if (!empty($currentgroup) && !empty($noattempts)) {
// We want a particular group and we want to do something funky with attempts.
// So join on groups_members and left join on attempts...
$from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . groups_members_join_sql() . 'LEFT JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
$where = ' WHERE ra.contextid ' . $contextlists . ' AND ' . groups_members_where_sql($currentgroup);
if ($noattempts == 1) {
// Noattempts = 1 means only no attempts, so make the left join ask.
// For only records where the right is null (no attempts).
$where .= ' AND qa.userid IS NULL';
// Show ONLY no attempts.
} else {
// We are including attempts, so exclude previews.
$where .= ' AND qa.preview = 0';
}
} else {
if (empty($currentgroup)) {
// We don't care about group, and we to do something funky with attempts.
// So do a left join on attempts.
$from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . ' LEFT JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
$where = " WHERE ra.contextid {$contextlists}";
if (empty($noattempts)) {
// Show ONLY students with attempts.
$where .= ' AND qa.userid IS NOT NULL AND qa.preview = 0';
} else {
if ($noattempts == 1) {
// The noattempts = 1 means only no attempts,.
// So make the left join ask for only records where the right is null (no attempts).
// Show ONLY students without attempts.
$where .= ' AND qa.userid IS NULL';
} else {
if ($noattempts == 3) {
// We want all attempts.
$from = 'FROM {user} u JOIN {game_attempts} qa ON u.id = qa.userid ';
$where = ' WHERE qa.gameid = ' . $game->id . ' AND qa.preview = 0';
}
}
}
// The noattempts = 2 means we want all students, with or without attempts.
}
}
}
$countsql = 'SELECT COUNT(DISTINCT(' . sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')) . ')) ' . $from . $where;
} else {
if (empty($noattempts)) {
$from = 'FROM {user} u JOIN {game_attempts} qa ON u.id = qa.userid ';
$where = ' WHERE qa.gameid = ' . $game->id . ' AND qa.preview = 0';
$countsql = 'SELECT COUNT(DISTINCT(' . sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')) . ')) ' . $from . $where;
}
}
if (!$download) {
// Add extra limits due to initials bar.
if ($table->get_sql_where()) {
$where .= ' AND ' . $table->get_sql_where();
}
// Count the records NOW, before funky question grade sorting messes up $from.
if (!empty($countsql)) {
$totalinitials = count_records_sql($countsql);
if ($table->get_sql_where()) {
$countsql .= ' AND ' . $table->get_sql_where();
}
$total = count_records_sql($countsql);
}
// Add extra limits due to sorting by question grade.
if ($sort = $table->get_sql_sort()) {
$sortparts = explode(',', $sort);
$newsort = array();
$questionsort = false;
foreach ($sortparts as $sortpart) {
$sortpart = trim($sortpart);
if (substr($sortpart, 0, 1) == '$') {
if (!$questionsort) {
$qid = intval(substr($sortpart, 1));
$select .= ', grade ';
$from .= ' LEFT JOIN {question_sessions} qns ON qns.attemptid = qa.id ' . 'LEFT JOIN {question_states} qs ON qs.id = qns.newgraded ';
$where .= ' AND (' . sql_isnull('qns.questionid') . ' OR qns.questionid = ' . $qid . ')';
$newsort[] = 'grade ' . (strpos($sortpart, 'ASC') ? 'ASC' : 'DESC');
$questionsort = true;
}
} else {
$newsort[] = $sortpart;
}
}
// Reconstruct the sort string.
$sort = ' ORDER BY ' . implode(', ', $newsort);
}
// Fix some wired sorting.
if (empty($sort)) {
$sort = ' ORDER BY qa.id';
}
$table->pagesize($pagesize, $total);
}
// If there is feedback, include it in the query.
if ($hasfeedback) {
$select .= ', qf.feedbacktext ';
$from .= " JOIN {game_feedback} qf ON " . "qf.gameid = {$game->id} AND qf.mingrade <= qa.score * {$game->grade} AND qa.score * {$game->grade} < qf.maxgrade";
}
// Fetch the attempts.
if (!empty($from)) {
// If we're in the site course and displaying no attempts, it makes no sense to do the query.
if (!$download) {
$attempts = get_records_sql($select . $from . $where . $sort, $table->get_page_start(), $table->get_page_size());
} else {
$attempts = get_records_sql($select . $from . $where . $sort);
}
} else {
$attempts = array();
}
// Build table rows.
if (!$download) {
$table->initialbars($totalinitials > 20);
}
if (!empty($attempts) || !empty($noattempts)) {
if ($attempts) {
foreach ($attempts as $attempt) {
$picture = print_user_picture($attempt->userid, $course->id, $attempt->picture, false, true);
/* Uncomment the commented lines below if you are choosing to show unenrolled users and
* have uncommented the corresponding lines earlier in this script
* if (in_array($attempt->userid, $unenrolledusers)) {
* $userlink = '<a class="dimmed" href="'.$CFG->wwwroot.
* '/user/view.php?id='.$attempt->userid.'&course='.$course->id.'">'.fullname($attempt).'</a>';
*}
*else {
* $userlink = '<a href="'.$CFG->wwwroot.'/user/view.php?id='.
* $attempt->userid.'&course='.$course->id.'">'.fullname($attempt).'</a>';
*}
*/
if (!$download) {
$row = array('<input type="checkbox" name="attemptid[]" value="' . $attempt->attempt . '" />', $picture, $userlink, empty($attempt->attempt) ? '-' : '<a href="review.php?q=' . $game->id . '&attempt=' . $attempt->attempt . '">' . userdate($attempt->timestart, $strtimeformat) . '</a>', empty($attempt->timefinish) ? '-' : '<a href="review.php?q=' . $game->id . '&attempt=' . $attempt->attempt . '">' . userdate($attempt->timefinish, $strtimeformat) . '</a>', empty($attempt->attempt) ? '-' : (empty($attempt->timefinish) ? get_string('unfinished', 'game') : format_time($attempt->duration)));
} else {
$row = array(fullname($attempt), empty($attempt->attempt) ? '-' : userdate($attempt->timestart, $strtimeformat), empty($attempt->timefinish) ? '-' : userdate($attempt->timefinish, $strtimeformat), empty($attempt->attempt) ? '-' : (empty($attempt->timefinish) ? get_string('unfinished', 'game') : format_time($attempt->duration)));
}
if ($game->grade) {
if (!$download) {
$row[] = $attempt->score === null ? '-' : '<a href="review.php?q=' . $game->id . '&attempt=' . $attempt->attempt . '">' . round($attempt->score * $game->grade, $game->decimalpoints) . '</a>';
} else {
$row[] = $attempt->score === null ? '-' : round($attempt->score * $game->grade, $game->decimalpoints);
}
}
if ($detailedmarks) {
if (empty($attempt->attempt)) {
foreach ($questionids as $questionid) {
$row[] = '-';
}
} else {
foreach ($questionids as $questionid) {
if ($gradedstateid = get_field('question_sessions', 'newgraded', 'attemptid', $attempt->attemptuniqueid, 'questionid', $questionid)) {
$grade = round(get_field('question_states', 'grade', 'id', $gradedstateid), $game->decimalpoints);
} else {
$grade = '--';
}
if (!$download) {
$row[] = link_to_popup_window('/mod/game/reviewquestion.php?state=' . $gradedstateid . '&number=' . $questions[$questionid]->number, 'reviewquestion', $grade, 450, 650, $strreviewquestion, 'none', true);
} else {
$row[] = $grade;
}
}
}
}
if ($hasfeedback) {
if ($attempt->timefinish) {
$row[] = $attempt->feedbacktext;
} else {
$row[] = '-';
}
}
if (!$download) {
$table->add_data($row);
} else {
if ($download == 'Excel' or $download == 'ODS') {
$colnum = 0;
foreach ($row as $item) {
$myxls->write($rownum, $colnum, $item, $format);
$colnum++;
}
$rownum++;
} else {
if ($download == 'CSV') {
$text = implode("\t", $row);
echo $text . " \n";
}
}
}
}
}
if (!$download) {
// Start form.
echo '<div id="tablecontainer">';
echo '<form id="attemptsform" method="post" action="report.php" ' . 'onsubmit="var menu = document.getElementById(\'menuaction\'); ' . 'return (menu.options[menu.selectedIndex].value == \'delete\' ? confirm(\'' . $strreallydel . '\') : true);">';
echo '<div>';
echo '<input type="hidden" name="id" value="' . $cm->id . '" />';
echo '<input type="hidden" name="mode" value="overview" />';
// Print table.
$table->print_html();
// Print "Select all" etc..
if (!empty($attempts)) {
echo '<table id="commands">';
echo '<tr><td>';
echo '<a href="javascript:select_all_in(\'DIV\',null,\'tablecontainer\');">' . get_string('selectall', 'game') . '</a> / ';
echo '<a href="javascript:deselect_all_in(\'DIV\',null,\'tablecontainer\');">' . get_string('selectnone', 'game') . '</a> ';
echo ' ';
$options = array('delete' => get_string('delete'));
echo choose_from_menu($options, 'action', '', get_string('withselected', 'game'), 'if(this.selectedIndex > 0) submitFormById(\'attemptsform\');', '', true);
echo '<noscript id="noscriptmenuaction" style="display: inline;"><div>';
echo '<input type="submit" value="' . get_string('go') . '" /></div></noscript>';
echo '<script type="text/javascript">' . "\n<!--\n" . 'document.getElementById("noscriptmenuaction").style.display = "none";' . "\n-->\n" . '</script>';
echo '</td></tr></table>';
}
// Close form.
echo '</div>';
echo '</form></div>';
if (!empty($attempts)) {
echo '<table class="boxaligncenter"><tr>';
$options = array();
$options["id"] = "{$cm->id}";
$options["q"] = "{$game->id}";
$options["mode"] = "overview";
$options['sesskey'] = sesskey();
$options["noheader"] = "yes";
$options['noattempts'] = $noattempts;
$options['detailedmarks'] = $detailedmarks;
echo '<td>';
$options["download"] = "ODS";
print_single_button("report.php", $options, get_string("downloadods", 'game'));
echo "</td>\n";
echo '<td>';
$options["download"] = "Excel";
print_single_button("report.php", $options, get_string("downloadexcel"));
echo "</td>\n";
echo '<td>';
$options["download"] = "CSV";
print_single_button('report.php', $options, get_string("downloadtext"));
echo "</td>\n";
echo "<td>";
helpbutton('overviewdownload', get_string('overviewdownload', 'quiz'), 'game');
echo "</td>\n";
echo '</tr></table>';
}
} else {
if ($download == 'Excel' or $download == 'ODS') {
$workbook->close();
exit;
} else {
if ($download == 'CSV') {
exit;
}
}
}
} else {
if (!$download) {
$table->print_html();
}
}
// Print display options.
echo '<div class="controls">';
echo '<form id="options" action="report.php" method="get">';
echo '<div>';
echo '<p>' . get_string('displayoptions', 'game') . ': </p>';
echo '<input type="hidden" name="id" value="' . $cm->id . '" />';
echo '<input type="hidden" name="q" value="' . $game->id . '" />';
echo '<input type="hidden" name="mode" value="overview" />';
echo '<input type="hidden" name="noattempts" value="0" />';
echo '<input type="hidden" name="detailedmarks" value="0" />';
echo '<table id="overview-options" class="boxaligncenter">';
echo '<tr align="left">';
echo '<td><label for="pagesize">' . get_string('pagesize', 'game') . '</label></td>';
echo '<td><input type="text" id="pagesize" name="pagesize" size="3" value="' . $pagesize . '" /></td>';
echo '</tr>';
echo '<tr align="left">';
echo '<td colspan="2">';
$options = array(0 => get_string('attemptsonly', 'game', $course->students));
if ($course->id != SITEID) {
$options[1] = get_string('noattemptsonly', 'game', $course->students);
$options[2] = get_string('allstudents', 'game', $course->students);
$options[3] = get_string('allattempts', 'game');
}
choose_from_menu($options, 'noattempts', $noattempts, '');
echo '</td></tr>';
echo '<tr align="left">';
echo '<td colspan="2"><input type="checkbox" id="checkdetailedmarks" name="detailedmarks" ' . ($detailedmarks ? 'checked="checked" ' : '') . 'value="1" /> <label for="checkdetailedmarks">' . get_string('showdetailedmarks', 'game') . '</label> ';
echo '</td></tr>';
echo '<tr><td colspan="2" align="center">';
echo '<input type="submit" value="' . get_string('go') . '" />';
echo '</td></tr></table>';
echo '</div>';
echo '</form>';
echo '</div>';
echo "\n";
return true;
}
/**
* Searches logs to find all enrolments since a certain date
*
* used to print recent activity
*
* @param int $courseid The course in question.
* @param int $timestart The date to check forward of
* @return object|false {@link $USER} records or false if error.
*/
function get_recent_enrolments($courseid, $timestart)
{
global $DB;
debugging('get_recent_enrolments() is deprecated as it returned inaccurate results.', DEBUG_DEVELOPER);
$context = context_course::instance($courseid);
$sql = "SELECT u.id, u.firstname, u.lastname, MAX(l.time)\n FROM {user} u, {role_assignments} ra, {log} l\n WHERE l.time > ?\n AND l.course = ?\n AND l.module = 'course'\n AND l.action = 'enrol'\n AND " . $DB->sql_cast_char2int('l.info') . " = u.id\n AND u.id = ra.userid\n AND ra.contextid " . get_related_contexts_string($context) . "\n GROUP BY u.id, u.firstname, u.lastname\n ORDER BY MAX(l.time) ASC";
$params = array($timestart, $courseid);
return $DB->get_records_sql($sql, $params);
}
/**
* Builds and return the row of averages for the right part of the grader report.
* @param array $rows Whether to return only group averages or all averages.
* @param bool $grouponly Whether to return only group averages or all averages.
* @return array Array of rows for the right part of the report
*/
public function get_right_avg_row($rows=array(), $grouponly=false) {
global $CFG, $USER, $DB, $OUTPUT;
if (!$this->canviewhidden) {
// totals might be affected by hiding, if user can not see hidden grades the aggregations might be altered
// better not show them at all if user can not see all hidden grades
return $rows;
}
$showaverages = $this->get_pref('showaverages');
$showaveragesgroup = $this->currentgroup && $showaverages;
$averagesdisplaytype = $this->get_pref('averagesdisplaytype');
$averagesdecimalpoints = $this->get_pref('averagesdecimalpoints');
$meanselection = $this->get_pref('meanselection');
$shownumberofgrades = $this->get_pref('shownumberofgrades');
$avghtml = '';
$avgcssclass = 'avg';
if ($grouponly) {
$straverage = get_string('groupavg', 'grades');
$showaverages = $this->currentgroup && $this->get_pref('showaverages');
$groupsql = $this->groupsql;
$groupwheresql = $this->groupwheresql;
$groupwheresqlparams = $this->groupwheresql_params;
$avgcssclass = 'groupavg';
} else {
$straverage = get_string('overallaverage', 'grades');
$showaverages = $this->get_pref('showaverages');
$groupsql = "";
$groupwheresql = "";
$groupwheresqlparams = array();
}
if ($shownumberofgrades) {
$straverage .= ' (' . get_string('submissions', 'grades') . ') ';
}
$totalcount = $this->get_numusers($grouponly);
//limit to users with a gradeable role
list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
//limit to users with an active enrollment
list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
if ($showaverages) {
$params = array_merge(array('courseid'=>$this->courseid), $gradebookrolesparams, $enrolledparams, $groupwheresqlparams);
// find sums of all grade items in course
$SQL = "SELECT g.itemid, SUM(g.finalgrade) AS sum
FROM {grade_items} gi
JOIN {grade_grades} g
ON g.itemid = gi.id
JOIN {user} u
ON u.id = g.userid
JOIN ($enrolledsql) je
ON je.id = u.id
JOIN {role_assignments} ra
ON ra.userid = u.id
$groupsql
WHERE gi.courseid = :courseid
AND ra.roleid $gradebookrolessql
AND ra.contextid ".get_related_contexts_string($this->context)."
AND g.finalgrade IS NOT NULL
$groupwheresql
GROUP BY g.itemid";
$sumarray = array();
if ($sums = $DB->get_records_sql($SQL, $params)) {
foreach ($sums as $itemid => $csum) {
$sumarray[$itemid] = $csum->sum;
}
}
// MDL-10875 Empty grades must be evaluated as grademin, NOT always 0
// This query returns a count of ungraded grades (NULL finalgrade OR no matching record in grade_grades table)
$SQL = "SELECT gi.id, COUNT(u.id) AS count
FROM {grade_items} gi
CROSS JOIN {user} u
JOIN ($enrolledsql) je
ON je.id = u.id
JOIN {role_assignments} ra
ON ra.userid = u.id
LEFT OUTER JOIN {grade_grades} g
ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
$groupsql
WHERE gi.courseid = :courseid
AND ra.roleid $gradebookrolessql
AND ra.contextid ".get_related_contexts_string($this->context)."
AND g.id IS NULL
$groupwheresql
GROUP BY gi.id";
$ungradedcounts = $DB->get_records_sql($SQL, $params);
$avgrow = new html_table_row();
$avgrow->attributes['class'] = 'avg';
foreach ($this->gtree->items as $itemid=>$unused) {
$item =& $this->gtree->items[$itemid];
if ($item->needsupdate) {
$avgcell = new html_table_cell();
$avgcell->text = $OUTPUT->container(get_string('error'), 'gradingerror');
$avgrow->cells[] = $avgcell;
continue;
}
if (!isset($sumarray[$item->id])) {
$sumarray[$item->id] = 0;
}
if (empty($ungradedcounts[$itemid])) {
$ungradedcount = 0;
} else {
$ungradedcount = $ungradedcounts[$itemid]->count;
}
if ($meanselection == GRADE_REPORT_MEAN_GRADED) {
$meancount = $totalcount - $ungradedcount;
} else { // Bump up the sum by the number of ungraded items * grademin
$sumarray[$item->id] += $ungradedcount * $item->grademin;
$meancount = $totalcount;
}
$decimalpoints = $item->get_decimals();
// Determine which display type to use for this average
if ($USER->gradeediting[$this->courseid]) {
$displaytype = GRADE_DISPLAY_TYPE_REAL;
} else if ($averagesdisplaytype == GRADE_REPORT_PREFERENCE_INHERIT) { // no ==0 here, please resave the report and user preferences
$displaytype = $item->get_displaytype();
} else {
$displaytype = $averagesdisplaytype;
}
// Override grade_item setting if a display preference (not inherit) was set for the averages
if ($averagesdecimalpoints == GRADE_REPORT_PREFERENCE_INHERIT) {
$decimalpoints = $item->get_decimals();
} else {
$decimalpoints = $averagesdecimalpoints;
}
if (!isset($sumarray[$item->id]) || $meancount == 0) {
$avgcell = new html_table_cell();
$avgcell->text = '-';
$avgrow->cells[] = $avgcell;
} else {
$sum = $sumarray[$item->id];
$avgradeval = $sum/$meancount;
$gradehtml = grade_format_gradevalue($avgradeval, $item, true, $displaytype, $decimalpoints);
$numberofgrades = '';
if ($shownumberofgrades) {
$numberofgrades = " ($meancount)";
}
$avgcell = new html_table_cell();
$avgcell->text = $gradehtml.$numberofgrades;
$avgrow->cells[] = $avgcell;
}
}
$rows[] = $avgrow;
}
return $rows;
}
/**
* This function returns an array of grades that were included in the import,
* but wherer the user does not currenly have a graded role on the course. These gradse
* are still stored in the database, but will not be visible in the gradebook unless
* this user subsequently enrols on the course in a graded roles.
*
* The returned objects have fields user firstname, lastname and useridnumber, and gradeidnumber.
*
* @param integer $importcode import batch identifier
* @param integer $courseid the course we are importing to.
* @return mixed and array of user objects, or false if none.
*/
function get_unenrolled_users_in_import($importcode, $courseid)
{
global $CFG;
$relatedctxcondition = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $courseid));
$sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber, \n COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber\n FROM\n {$CFG->prefix}grade_import_values giv\n JOIN {$CFG->prefix}user u ON giv.userid = u.id\n LEFT JOIN {$CFG->prefix}grade_items gi ON gi.id = giv.itemid\n LEFT JOIN {$CFG->prefix}grade_import_newitem gin ON gin.id = giv.newgradeitem\n LEFT JOIN {$CFG->prefix}role_assignments ra ON (giv.userid = ra.userid AND\n ra.roleid IN ({$CFG->gradebookroles}) AND\n ra.contextid {$relatedctxcondition})\n WHERE giv.importcode = {$importcode}\n AND ra.id IS NULL\n ORDER BY gradeidnumber, u.lastname, u.firstname";
return get_records_sql($sql);
}
/**
* who has this capability in this context
* does not handling user level resolving!!!
* i.e 1 person has 2 roles 1 allow, 1 prevent, this will not work properly
* @param $context - object
* @param $capability - string capability
* @param $fields - fields to be pulled
* @param $sort - the sort order
* @param $limitfrom - number of records to skip (offset)
* @param $limitnum - number of records to fetch
* @param $groups - single group or array of groups - group(s) user is in
* @param $exceptions - list of users to exclude
*/
function internalmail_get_users_by_capability($context, $capability, $fields = '', $sort = 'u.firstname', $limitfrom = '', $limitnum = '', $groups = '', $exceptions = '', $doanything = true, $search = '', $firstinitial = '', $lastinitial = '')
{
global $CFG, $USER, $COURSE;
/// Sorting out groups
if ($groups !== '') {
$groupjoin = 'INNER JOIN ' . $CFG->prefix . 'groups_members gm ON gm.userid = ra.userid';
if (is_array($groups)) {
$groupsql = 'AND gm.groupid IN (' . implode(',', $groups) . ')';
} else {
if ($groups == 0) {
if (!has_capability('block/email_list:viewallgroups', $context) && $COURSE->groupmode == 1) {
$groupids = groups_get_groups_for_user($USER->id, $COURSE->id);
$groupsql = 'AND gm.groupid IN (' . implode(',', $groupids) . ')';
} else {
$groupsql = '';
}
} else {
$groupsql = 'AND gm.groupid = ' . $groups;
}
}
} else {
$groupjoin = '';
$groupsql = '';
}
/// Sorting out exceptions
$exceptionsql = $exceptions ? "AND u.id NOT IN ({$exceptions})" : '';
/// Set up default fields
if (empty($fields)) {
$fields = 'u.*, ul.timeaccess as lastaccess, ra.hidden';
}
/// Set up default sort
if (empty($sort)) {
$sortby = 'ul.timeaccess';
}
$sortby = $sort ? " ORDER BY {$sort} " : '';
/// If context is a course, then construct sql for ul
if ($context->contextlevel == CONTEXT_COURSE) {
$courseid = $context->instanceid;
$coursesql = "AND (ul.courseid = {$courseid} OR ul.courseid IS NULL)";
} else {
$coursesql = '';
}
$LIKE = sql_ilike();
$fullname = sql_fullname();
$search_sql = '';
if (!empty($search)) {
$search = trim($search);
$search_sql .= " AND ({$fullname} {$LIKE} '%{$search}%' OR email {$LIKE} '%{$search}%' OR username {$LIKE} '%{$search}%' OR idnumber {$LIKE} '%{$search}%') ";
}
if ($firstinitial) {
$search_sql .= ' AND firstname ' . $LIKE . ' \'' . $firstinitial . '%\'';
}
if ($lastinitial) {
$search_sql .= ' AND lastname ' . $LIKE . ' \'' . $lastinitial . '%\'';
}
/// Sorting out roles with this capability set
if ($possibleroles = get_roles_with_capability($capability, CAP_ALLOW, $context)) {
if (!$doanything) {
if (!($sitecontext = get_context_instance(CONTEXT_SYSTEM))) {
return false;
// Something is seriously wrong
}
$doanythingroles = get_roles_with_capability('moodle/site:doanything', CAP_ALLOW, $sitecontext);
}
$validroleids = array();
foreach ($possibleroles as $possiblerole) {
if (!$doanything) {
if (isset($doanythingroles[$possiblerole->id])) {
// We don't want these included
continue;
}
}
if ($caps = role_context_capabilities($possiblerole->id, $context, $capability)) {
// resolved list
if (isset($caps[$capability]) && $caps[$capability] > 0) {
// resolved capability > 0
$validroleids[] = $possiblerole->id;
}
}
}
if (empty($validroleids)) {
return false;
}
$roleids = '(' . implode(',', $validroleids) . ')';
} else {
return false;
// No need to continue, since no roles have this capability set
}
/// Construct the main SQL
$select = " SELECT {$fields}";
$from = " FROM {$CFG->prefix}user u\r\n INNER JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\r\n INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid\r\n LEFT OUTER JOIN {$CFG->prefix}user_lastaccess ul ON ul.userid = u.id\r\n {$groupjoin}";
$where = " WHERE ra.contextid " . get_related_contexts_string($context) . "\r\n AND u.deleted = 0\r\n AND ra.roleid in {$roleids}\r\n {$exceptionsql}\r\n {$coursesql}\r\n {$groupsql}\r\n {$search_sql}";
return get_records_sql($select . $from . $where . $sortby, $limitfrom, $limitnum);
}
/**
* Initialise the iterator
*
* @return boolean success
*/
public function init()
{
global $CFG, $DB;
$this->close();
grade_regrade_final_grades($this->course->id);
$course_item = grade_item::fetch_course_item($this->course->id);
if ($course_item->needsupdate) {
// can not calculate all final grades - sorry
return false;
}
$coursecontext = context_course::instance($this->course->id);
$relatedcontexts = get_related_contexts_string($coursecontext);
list($gradebookroles_sql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext, '', 0, $this->onlyactive);
$params = array_merge($params, $enrolledparams);
if ($this->groupid) {
$groupsql = "INNER JOIN {groups_members} gm ON gm.userid = u.id";
$groupwheresql = "AND gm.groupid = :groupid";
// $params contents: gradebookroles
$params['groupid'] = $this->groupid;
} else {
$groupsql = "";
$groupwheresql = "";
}
if (empty($this->sortfield1)) {
// we must do some sorting even if not specified
$ofields = ", u.id AS usrt";
$order = "usrt ASC";
} else {
$ofields = ", u.{$this->sortfield1} AS usrt1";
$order = "usrt1 {$this->sortorder1}";
if (!empty($this->sortfield2)) {
$ofields .= ", u.{$this->sortfield2} AS usrt2";
$order .= ", usrt2 {$this->sortorder2}";
}
if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
// user order MUST be the same in both queries,
// must include the only unique user->id if not already present
$ofields .= ", u.id AS usrt";
$order .= ", usrt ASC";
}
}
$userfields = 'u.*';
$customfieldssql = '';
if ($this->allowusercustomfields && !empty($CFG->grade_export_customprofilefields)) {
$customfieldscount = 0;
$customfieldsarray = grade_helper::get_user_profile_fields($this->course->id, $this->allowusercustomfields);
foreach ($customfieldsarray as $field) {
if (!empty($field->customid)) {
$customfieldssql .= "\n LEFT JOIN (SELECT * FROM {user_info_data}\n WHERE fieldid = :cf{$customfieldscount}) cf{$customfieldscount}\n ON u.id = cf{$customfieldscount}.userid";
$userfields .= ", cf{$customfieldscount}.data AS 'customfield_{$field->shortname}'";
$params['cf' . $customfieldscount] = $field->customid;
$customfieldscount++;
}
}
}
// $params contents: gradebookroles and groupid (for $groupwheresql)
$users_sql = "SELECT {$userfields} {$ofields}\n FROM {user} u\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$groupsql} {$customfieldssql}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookroles_sql}\n AND ra.contextid {$relatedcontexts}\n ) rainner ON rainner.userid = u.id\n WHERE u.deleted = 0\n {$groupwheresql}\n ORDER BY {$order}";
$this->users_rs = $DB->get_recordset_sql($users_sql, $params);
if (!empty($this->grade_items)) {
$itemids = array_keys($this->grade_items);
list($itemidsql, $grades_params) = $DB->get_in_or_equal($itemids, SQL_PARAMS_NAMED, 'items');
$params = array_merge($params, $grades_params);
// $params contents: gradebookroles, enrolledparams, groupid (for $groupwheresql) and itemids
$grades_sql = "SELECT g.* {$ofields}\n FROM {grade_grades} g\n JOIN {user} u ON g.userid = u.id\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$groupsql}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookroles_sql}\n AND ra.contextid {$relatedcontexts}\n ) rainner ON rainner.userid = u.id\n WHERE u.deleted = 0\n AND g.itemid {$itemidsql}\n {$groupwheresql}\n ORDER BY {$order}, g.itemid ASC";
$this->grades_rs = $DB->get_recordset_sql($grades_sql, $params);
} else {
$this->grades_rs = false;
}
return true;
}
/**
* This function is used to print roles column in user profile page.
* It is using the CFG->profileroles to limit the list to only interesting roles.
* (The permission tab has full details of user role assignments.)
*
* @param int $userid
* @param int $courseid
* @return string
*/
function get_user_roles_in_course($userid, $courseid)
{
global $CFG, $DB, $USER;
if (empty($CFG->profileroles)) {
return '';
}
if ($courseid == SITEID) {
$context = get_context_instance(CONTEXT_SYSTEM);
} else {
$context = get_context_instance(CONTEXT_COURSE, $courseid);
}
if (empty($CFG->profileroles)) {
return array();
}
$allowed = explode(',', $CFG->profileroles);
list($rallowed, $params) = $DB->get_in_or_equal($allowed, SQL_PARAMS_NAMED);
$contextlist = get_related_contexts_string($context);
$sql = "SELECT DISTINCT r.id, r.name, r.shortname, r.sortorder\n FROM {role_assignments} ra, {role} r\n WHERE r.id = ra.roleid\n AND ra.contextid {$contextlist}\n AND r.id {$rallowed}\n AND ra.userid = :userid\n ORDER BY r.sortorder ASC";
$params['userid'] = $userid;
$rolestring = '';
if ($roles = $DB->get_records_sql($sql, $params)) {
foreach ($roles as $userrole) {
$rolenames[$userrole->id] = $userrole->name;
}
$rolenames = role_fix_names($rolenames, $context);
// Substitute aliases
foreach ($rolenames as $roleid => $rolename) {
$rolenames[$roleid] = '<a href="' . $CFG->wwwroot . '/user/index.php?contextid=' . $context->id . '&roleid=' . $roleid . '">' . $rolename . '</a>';
}
$rolestring = implode(',', $rolenames);
}
return $rolestring;
}
$table->set_attribute('cellspacing', '0');
$table->set_attribute('id', 'participants');
$table->set_attribute('class', 'generaltable generalbox');
$table->set_control_variables(array(
TABLE_VAR_SORT => 'ssort',
TABLE_VAR_HIDE => 'shide',
TABLE_VAR_SHOW => 'sshow',
TABLE_VAR_IFIRST => 'sifirst',
TABLE_VAR_ILAST => 'silast',
TABLE_VAR_PAGE => 'spage'
));
$table->setup();
// we are looking for all users with this role assigned in this context or higher
$contextlist = get_related_contexts_string($context);
list($esql, $params) = get_enrolled_sql($context, NULL, $currentgroup, true);
$joins = array("FROM {user} u");
$wheres = array();
$extrasql = get_extra_user_fields_sql($context, 'u', '', array(
'id', 'username', 'firstname', 'lastname', 'email', 'city', 'country',
'picture', 'lang', 'timezone', 'maildisplay', 'imagealt', 'lastaccess'));
if ($isfrontpage) {
$select = "SELECT u.id, u.username, u.firstname, u.lastname,
u.email, u.city, u.country, u.picture,
u.lang, u.timezone, u.maildisplay, u.imagealt,
u.lastaccess$extrasql";
$joins[] = "JOIN ($esql) e ON e.id = u.id"; // everybody on the frontpage usually
/**
* This function returns an array of grades that were included in the import,
* but where the user does not currently have a graded role on the course. These grades
* are still stored in the database, but will not be visible in the gradebook unless
* this user subsequently enrols on the course in a graded roles.
*
* The returned objects have fields user firstname, lastname and useridnumber, and gradeidnumber.
*
* @param integer $importcode import batch identifier
* @param integer $courseid the course we are importing to.
* @return mixed and array of user objects, or false if none.
*/
function get_unenrolled_users_in_import($importcode, $courseid)
{
global $CFG, $DB;
$relatedctxcondition = get_related_contexts_string(context_course::instance($courseid));
//users with a gradeable role
list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
//enrolled users
$context = context_course::instance($courseid);
list($enrolledsql, $enrolledparams) = get_enrolled_sql($context);
list($sort, $sortparams) = users_order_by_sql('u');
$sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber,\n COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber\n FROM {grade_import_values} giv\n JOIN {user} u\n ON giv.userid = u.id\n LEFT JOIN {grade_items} gi\n ON gi.id = giv.itemid\n LEFT JOIN {grade_import_newitem} gin\n ON gin.id = giv.newgradeitem\n LEFT JOIN ({$enrolledsql}) je\n ON je.id = u.id\n LEFT JOIN {role_assignments} ra\n ON (giv.userid = ra.userid AND ra.roleid {$gradebookrolessql} AND ra.contextid {$relatedctxcondition})\n WHERE giv.importcode = :importcode\n AND (ra.id IS NULL OR je.id IS NULL)\n ORDER BY gradeidnumber, {$sort}";
$params = array_merge($gradebookrolesparams, $enrolledparams, $sortparams);
$params['importcode'] = $importcode;
return $DB->get_records_sql($sql, $params);
}
/**
* Gets the users for a course who are not in a specified group
* @param int $groupid The id of the group
* @param string searchtext similar to searchtext in role assign, search
* @return array An array of the userids of the non-group members, or false if
* an error occurred.
* This function was changed to get_users_by_capability style
* mostly because of the searchtext requirement
*/
function groups_get_users_not_in_group($courseid, $groupid, $searchtext = '')
{
global $CFG;
$context = get_context_instance(CONTEXT_COURSE, $courseid);
if ($searchtext !== '') {
// Search for a subset of remaining users
$LIKE = sql_ilike();
$FULLNAME = sql_fullname();
$wheresearch = " AND u.id IN (SELECT id FROM {$CFG->prefix}user WHERE {$FULLNAME} {$LIKE} '%{$searchtext}%' OR email {$LIKE} '%{$searchtext}%' )";
} else {
$wheresearch = '';
}
$capability = 'moodle/course:view';
$doanything = false;
// find all possible "student" roles
if ($possibleroles = get_roles_with_capability($capability, CAP_ALLOW, $context)) {
if (!$doanything) {
if (!($sitecontext = get_context_instance(CONTEXT_SYSTEM))) {
return false;
// Something is seriously wrong
}
$doanythingroles = get_roles_with_capability('moodle/site:doanything', CAP_ALLOW, $sitecontext);
}
$validroleids = array();
foreach ($possibleroles as $possiblerole) {
if (!$doanything) {
if (isset($doanythingroles[$possiblerole->id])) {
// We don't want these included
continue;
}
}
if ($caps = role_context_capabilities($possiblerole->id, $context, $capability)) {
// resolved list
if (isset($caps[$capability]) && $caps[$capability] > 0) {
// resolved capability > 0
$validroleids[] = $possiblerole->id;
}
}
}
if (empty($validroleids)) {
return false;
}
$roleids = '(' . implode(',', $validroleids) . ')';
} else {
return false;
// No need to continue, since no roles have this capability set
}
/// Construct the main SQL
$select = " SELECT u.id, u.firstname, u.lastname";
$from = " FROM {$CFG->prefix}user u\n INNER JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\n INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid";
$where = " WHERE ra.contextid " . get_related_contexts_string($context) . "\n AND u.deleted = 0\n AND ra.roleid in {$roleids}\n AND u.id NOT IN (SELECT userid\n FROM {$CFG->prefix}groups_members\n WHERE groupid = {$groupid})\n {$wheresearch}";
$groupby = " GROUP BY u.id, u.firstname, u.lastname ";
return get_records_sql($select . $from . $where . $groupby);
}
/**
* Searches logs to find all enrolments since a certain date
*
* used to print recent activity
*
* @uses $CFG
* @param int $courseid The course in question.
* @return object|false {@link $USER} records or false if error.
* @todo Finish documenting this function
*/
function get_recent_enrolments($courseid, $timestart)
{
global $CFG;
$context = get_context_instance(CONTEXT_COURSE, $courseid);
return get_records_sql("SELECT DISTINCT u.id, u.firstname, u.lastname, l.time\n FROM {$CFG->prefix}user u,\n {$CFG->prefix}role_assignments ra,\n {$CFG->prefix}log l\n WHERE l.time > '{$timestart}'\n AND l.course = '{$courseid}'\n AND l.module = 'course'\n AND l.action = 'enrol'\n AND l.info = u.id\n AND u.id = ra.userid\n AND ra.contextid " . get_related_contexts_string($context) . "\n ORDER BY l.time ASC");
}
/**
* Builds the grade item averages.
*
*/
function calculate_averages()
{
global $USER, $DB;
if ($this->showaverage) {
// this settings are actually grader report settings (not user report)
// however we're using them as having two separate but identical settings the
// user would have to keep in sync would be annoying
$averagesdisplaytype = $this->get_pref('averagesdisplaytype');
$averagesdecimalpoints = $this->get_pref('averagesdecimalpoints');
$meanselection = $this->get_pref('meanselection');
$shownumberofgrades = $this->get_pref('shownumberofgrades');
$avghtml = '';
$avgcssclass = 'avg';
$straverage = get_string('overallaverage', 'grades');
$groupsql = $this->groupsql;
$groupwheresql = $this->groupwheresql;
//$groupwheresqlparams = ;
if ($shownumberofgrades) {
$straverage .= ' (' . get_string('submissions', 'grades') . ') ';
}
$totalcount = $this->get_numusers(false);
//limit to users with a gradeable role ie students
list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
//limit to users with an active enrolment
list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
$params = array_merge($this->groupwheresql_params, $gradebookrolesparams, $enrolledparams);
$params['courseid'] = $this->courseid;
// find sums of all grade items in course
$sql = "SELECT gg.itemid, SUM(gg.finalgrade) AS sum\n FROM {grade_items} gi\n JOIN {grade_grades} gg ON gg.itemid = gi.id\n JOIN {user} u ON u.id = gg.userid\n JOIN ({$enrolledsql}) je ON je.id = gg.userid\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookrolessql}\n AND ra.contextid " . get_related_contexts_string($this->context) . "\n ) rainner ON rainner.userid = u.id\n {$groupsql}\n WHERE gi.courseid = :courseid\n AND u.deleted = 0\n AND gg.finalgrade IS NOT NULL\n AND gg.hidden = 0\n {$groupwheresql}\n GROUP BY gg.itemid";
$sum_array = array();
$sums = $DB->get_recordset_sql($sql, $params);
foreach ($sums as $itemid => $csum) {
$sum_array[$itemid] = $csum->sum;
}
$sums->close();
$columncount = 0;
// Empty grades must be evaluated as grademin, NOT always 0
// This query returns a count of ungraded grades (NULL finalgrade OR no matching record in grade_grades table)
// No join condition when joining grade_items and user to get a grade item row for every user
// Then left join with grade_grades and look for rows with null final grade (which includes grade items with no grade_grade)
$sql = "SELECT gi.id, COUNT(u.id) AS count\n FROM {grade_items} gi\n JOIN {user} u ON u.deleted = 0\n JOIN ({$enrolledsql}) je ON je.id = u.id\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookrolessql}\n AND ra.contextid " . get_related_contexts_string($this->context) . "\n ) rainner ON rainner.userid = u.id\n LEFT JOIN {grade_grades} gg\n ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0)\n {$groupsql}\n WHERE gi.courseid = :courseid\n AND gg.finalgrade IS NULL\n {$groupwheresql}\n GROUP BY gi.id";
$ungraded_counts = $DB->get_records_sql($sql, $params);
foreach ($this->gtree->items as $itemid => $unused) {
if (!empty($this->gtree->items[$itemid]->avg)) {
continue;
}
$item = $this->gtree->items[$itemid];
if ($item->needsupdate) {
$avghtml .= '<td class="cell c' . $columncount++ . '"><span class="gradingerror">' . get_string('error') . '</span></td>';
continue;
}
if (empty($sum_array[$item->id])) {
$sum_array[$item->id] = 0;
}
if (empty($ungraded_counts[$itemid])) {
$ungraded_count = 0;
} else {
$ungraded_count = $ungraded_counts[$itemid]->count;
}
//do they want the averages to include all grade items
if ($meanselection == GRADE_REPORT_MEAN_GRADED) {
$mean_count = $totalcount - $ungraded_count;
} else {
// Bump up the sum by the number of ungraded items * grademin
$sum_array[$item->id] += $ungraded_count * $item->grademin;
$mean_count = $totalcount;
}
$decimalpoints = $item->get_decimals();
// Determine which display type to use for this average
if (!empty($USER->gradeediting) && $USER->gradeediting[$this->courseid]) {
$displaytype = GRADE_DISPLAY_TYPE_REAL;
} else {
if ($averagesdisplaytype == GRADE_REPORT_PREFERENCE_INHERIT) {
// no ==0 here, please resave the report and user preferences
$displaytype = $item->get_displaytype();
} else {
$displaytype = $averagesdisplaytype;
}
}
// Override grade_item setting if a display preference (not inherit) was set for the averages
if ($averagesdecimalpoints == GRADE_REPORT_PREFERENCE_INHERIT) {
$decimalpoints = $item->get_decimals();
} else {
$decimalpoints = $averagesdecimalpoints;
}
if (empty($sum_array[$item->id]) || $mean_count == 0) {
$this->gtree->items[$itemid]->avg = '-';
} else {
$sum = $sum_array[$item->id];
$avgradeval = $sum / $mean_count;
$gradehtml = grade_format_gradevalue($avgradeval, $item, true, $displaytype, $decimalpoints);
$numberofgrades = '';
if ($shownumberofgrades) {
$numberofgrades = " ({$mean_count})";
}
$this->gtree->items[$itemid]->avg = $gradehtml . $numberofgrades;
}
}
}
}
/**
* Main filter function.
*/
function blog_fetch_entries($postid = '', $fetchlimit = 10, $fetchstart = '', $filtertype = '', $filterselect = '', $tagid = '', $tag = '', $sort = 'lastmodified DESC', $limit = true)
{
global $CFG, $USER;
/// the post table will be used for other things too
$typesql = " AND p.module = 'blog' ";
/// set the tag id for searching
if ($tagid) {
$tag = $tagid;
} else {
if ($tag) {
if ($tagrec = get_record_sql('SELECT * FROM ' . $CFG->prefix . 'tag WHERE name LIKE "' . addslashes($tag) . '"')) {
$tag = $tagrec->id;
} else {
$tag = -1;
//no records found
}
}
}
// If we have specified an ID
// Just return 1 entry
if ($postid) {
if ($post = get_record('post', 'id', $postid)) {
if (blog_user_can_view_user_post($post->userid, $post)) {
if ($user = get_record('user', 'id', $post->userid)) {
$post->email = $user->email;
$post->firstname = $user->firstname;
$post->lastname = $user->lastname;
}
$retarray[] = $post;
return $retarray;
} else {
return null;
}
} else {
// bad postid
return null;
}
}
if ($tag) {
$tagtablesql = $CFG->prefix . 'tag_instance ti, ';
$tagquerysql = ' AND ti.itemid = p.id AND ti.tagid = ' . $tag . ' AND ti.itemtype = \'post\' ';
} else {
$tagtablesql = '';
$tagquerysql = '';
}
if (isloggedin() && !has_capability('moodle/legacy:guest', get_context_instance(CONTEXT_SYSTEM), $USER->id, false)) {
$permissionsql = 'AND (p.publishstate = \'site\' OR p.publishstate = \'public\' OR p.userid = ' . $USER->id . ')';
} else {
$permissionsql = 'AND p.publishstate = \'public\'';
}
// fix for MDL-9165, use with readuserblogs capability in a user context can read that user's private blogs
// admins can see all blogs regardless of publish states, as described on the help page
if (has_capability('moodle/user:readuserblogs', get_context_instance(CONTEXT_SYSTEM))) {
$permissionsql = '';
} else {
if ($filtertype == 'user' && has_capability('moodle/user:readuserblogs', get_context_instance(CONTEXT_USER, $filterselect))) {
$permissionsql = '';
}
}
/****************************************
* depending on the type, there are 4 *
* different possible sqls *
****************************************/
$requiredfields = 'p.*, u.firstname,u.lastname,u.email';
if ($filtertype == 'course' && $filterselect == SITEID) {
// Really a site
$filtertype = 'site';
}
switch ($filtertype) {
case 'site':
$SQL = 'SELECT ' . $requiredfields . ' FROM ' . $CFG->prefix . 'post p, ' . $tagtablesql . $CFG->prefix . 'user u
WHERE p.userid = u.id ' . $tagquerysql . '
AND u.deleted = 0
' . $permissionsql . $typesql;
break;
case 'course':
// all users with a role assigned
$context = get_context_instance(CONTEXT_COURSE, $filterselect);
// MDL-10037, hidden users' blogs should not appear
if (has_capability('moodle/role:viewhiddenassigns', $context)) {
$hiddensql = '';
} else {
$hiddensql = ' AND ra.hidden = 0 ';
}
$SQL = 'SELECT ' . $requiredfields . ' FROM ' . $CFG->prefix . 'post p, ' . $tagtablesql . $CFG->prefix . 'role_assignments ra, ' . $CFG->prefix . 'user u
WHERE p.userid = ra.userid ' . $tagquerysql . '
AND ra.contextid ' . get_related_contexts_string($context) . '
AND u.id = p.userid
AND u.deleted = 0
' . $hiddensql . $permissionsql . $typesql;
break;
case 'group':
$SQL = 'SELECT ' . $requiredfields . ' FROM ' . $CFG->prefix . 'post p, ' . $tagtablesql . $CFG->prefix . 'groups_members gm, ' . $CFG->prefix . 'user u
WHERE p.userid = gm.userid AND u.id = p.userid ' . $tagquerysql . '
AND gm.groupid = ' . $filterselect . '
AND u.deleted = 0
' . $permissionsql . $typesql;
break;
case 'user':
// a hack to publish some blogs openly. Uses $CFG->openblogs = array(44, 322); in config.php
if (isset($CFG->openblogs) && in_array($filterselect, $CFG->openblogs)) {
$permissionsql = ' AND (p.publishstate = \'site\' OR p.publishstate = \'public\') ';
}
$SQL = 'SELECT ' . $requiredfields . ' FROM ' . $CFG->prefix . 'post p, ' . $tagtablesql . $CFG->prefix . 'user u
WHERE p.userid = u.id ' . $tagquerysql . '
AND u.id = ' . $filterselect . '
AND u.deleted = 0
' . $permissionsql . $typesql;
break;
}
$limitfrom = 0;
$limitnum = 0;
if ($fetchstart !== '' && $limit) {
$limitfrom = $fetchstart;
$limitnum = $fetchlimit;
}
$orderby = ' ORDER BY ' . $sort . ' ';
//global $db; $db->debug = true;
$records = get_records_sql($SQL . $orderby, $limitfrom, $limitnum);
//$db->debug = false;
if (empty($records)) {
return array();
}
return $records;
}
/**
* Lists users in a group based on their role on the course.
* Returns false if there's an error or there are no users in the group.
* Otherwise returns an array of role ID => role data, where role data includes:
* (role) $id, $shortname, $name
* $users: array of objects for each user which include the specified fields
* Users who do not have a role are stored in the returned array with key '-'
* and pseudo-role details (including a name, 'No role'). Users with multiple
* roles, same deal with key '*' and name 'Multiple roles'. You can find out
* which roles each has by looking in the $roles array of the user object.
*
* @param int $groupid
* @param int $courseid Course ID (should match the group's course)
* @param string $fields List of fields from user table prefixed with u, default 'u.*'
* @param string $sort SQL ORDER BY clause, default (when null passed) is what comes from users_order_by_sql.
* @param string $extrawheretest extra SQL conditions ANDed with the existing where clause.
* @param array $whereorsortparams any parameters required by $extrawheretest (named parameters).
* @return array Complex array as described above
*/
function groups_get_members_by_role($groupid, $courseid, $fields = 'u.*', $sort = null, $extrawheretest = '', $whereorsortparams = array())
{
global $CFG, $DB;
// Retrieve information about all users and their roles on the course or
// parent ('related') contexts
$context = context_course::instance($courseid);
if ($extrawheretest) {
$extrawheretest = ' AND ' . $extrawheretest;
}
if (is_null($sort)) {
list($sort, $sortparams) = users_order_by_sql('u');
$whereorsortparams = array_merge($whereorsortparams, $sortparams);
}
$sql = "SELECT r.id AS roleid, u.id AS userid, {$fields}\n FROM {groups_members} gm\n JOIN {user} u ON u.id = gm.userid\n LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid " . get_related_contexts_string($context) . ")\n LEFT JOIN {role} r ON r.id = ra.roleid\n WHERE gm.groupid=:mgroupid\n " . $extrawheretest . "\n ORDER BY r.sortorder, {$sort}";
$whereorsortparams['mgroupid'] = $groupid;
$rs = $DB->get_recordset_sql($sql, $whereorsortparams);
return groups_calculate_role_people($rs, $context);
}
$table->set_control_variables(array(TABLE_VAR_SORT => 'ssort', TABLE_VAR_HIDE => 'shide', TABLE_VAR_SHOW => 'sshow', TABLE_VAR_IFIRST => 'sifirst', TABLE_VAR_ILAST => 'silast', TABLE_VAR_PAGE => 'spage'));
$table->setup();
switch ($action) {
case 'view':
$actions = $viewnames;
break;
case 'post':
$actions = $postnames;
break;
default:
// some modules have stuff we want to hide, ie mail blocked etc so do actually need to limit here.
$actions = array_merge($viewnames, $postnames);
}
list($actionsql, $params) = $DB->get_in_or_equal($actions, SQL_PARAMS_NAMED, 'action');
$actionsql = "action {$actionsql}";
$relatedcontexts = get_related_contexts_string($context);
$sql = "SELECT ra.userid, u.firstname, u.lastname, u.idnumber, l.actioncount AS count\n FROM (SELECT * FROM {role_assignments} WHERE contextid {$relatedcontexts} AND roleid = :roleid ) ra\n JOIN {user} u ON u.id = ra.userid\n LEFT JOIN (\n SELECT userid, COUNT(action) AS actioncount FROM {log} WHERE cmid = :instanceid AND time > :timefrom AND {$actionsql} GROUP BY userid\n ) l ON (l.userid = ra.userid)";
$params['roleid'] = $roleid;
$params['instanceid'] = $instanceid;
$params['timefrom'] = $timefrom;
list($twhere, $tparams) = $table->get_sql_where();
if ($twhere) {
$sql .= ' WHERE ' . $twhere;
//initial bar
$params = array_merge($params, $tparams);
}
if ($table->get_sql_sort()) {
$sql .= ' ORDER BY ' . $table->get_sql_sort();
}
$countsql = "SELECT COUNT(DISTINCT(ra.userid))\n FROM {role_assignments} ra\n WHERE ra.contextid {$relatedcontexts} AND ra.roleid = :roleid";
$totalcount = $DB->count_records_sql($countsql, $params);
/**
* Fetches and returns a count of all the users that will be shown on this page.
* @param boolean $groups include groups limit
* @return int Count of users
*/
public function get_numusers($groups = true)
{
global $CFG, $DB;
$groupsql = "";
$groupwheresql = "";
//limit to users with a gradeable role
list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
//limit to users with an active enrollment
list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
$params = array_merge($gradebookrolesparams, $enrolledparams);
if ($groups) {
$groupsql = $this->groupsql;
$groupwheresql = $this->groupwheresql;
$params = array_merge($params, $this->groupwheresql_params);
}
$countsql = "SELECT COUNT(DISTINCT u.id)\n FROM {user} u\n JOIN ({$enrolledsql}) je\n ON je.id = u.id\n JOIN {role_assignments} ra\n ON u.id = ra.userid\n {$groupsql}\n WHERE ra.roleid {$gradebookrolessql}\n AND u.deleted = 0\n {$groupwheresql}\n AND ra.contextid " . get_related_contexts_string($this->context);
return $DB->count_records_sql($countsql, $params);
}
/**
* Searches logs to find all enrolments since a certain date
*
* used to print recent activity
*
* @global object
* @uses CONTEXT_COURSE
* @param int $courseid The course in question.
* @param int $timestart The date to check forward of
* @return object|false {@link $USER} records or false if error.
*/
function get_recent_enrolments($courseid, $timestart) {
global $DB;
$context = get_context_instance(CONTEXT_COURSE, $courseid);
$sql = "SELECT u.id, u.firstname, u.lastname, MAX(l.time)
FROM {user} u, {role_assignments} ra, {log} l
WHERE l.time > ?
AND l.course = ?
AND l.module = 'course'
AND l.action = 'enrol'
AND ".$DB->sql_cast_char2int('l.info')." = u.id
AND u.id = ra.userid
AND ra.contextid ".get_related_contexts_string($context)."
GROUP BY u.id, u.firstname, u.lastname
ORDER BY MAX(l.time) ASC";
$params = array($timestart, $courseid);
return $DB->get_records_sql($sql, $params);
}
/**
* Lists users in a group based on their role on the course.
* Returns false if there's an error or there are no users in the group.
* Otherwise returns an array of role ID => role data, where role data includes:
* (role) $id, $shortname, $name
* $users: array of objects for each user which include the specified fields
* Users who do not have a role are stored in the returned array with key '-'
* and pseudo-role details (including a name, 'No role'). Users with multiple
* roles, same deal with key '*' and name 'Multiple roles'. You can find out
* which roles each has by looking in the $roles array of the user object.
* @param int $groupid
* @param int $courseid Course ID (should match the group's course)
* @param string $fields List of fields from user table prefixed with u, default 'u.*'
* @param string $sort SQL ORDER BY clause, default 'u.lastname ASC'
* @param string $extrawheretest extra SQL conditions ANDed with the existing where clause.
* @return array Complex array as described above
*/
function groups_get_members_by_role($groupid, $courseid, $fields = 'u.*', $sort = 'u.lastname ASC', $extrawheretest = '')
{
global $CFG;
// Retrieve information about all users and their roles on the course or
// parent ('related') contexts
$context = get_context_instance(CONTEXT_COURSE, $courseid);
if ($extrawheretest) {
$extrawheretest = ' AND ' . $extrawheretest;
}
$rs = get_recordset_sql($crap = "SELECT r.id AS roleid,r.shortname AS roleshortname,r.name AS rolename,\n u.id AS userid,{$fields}\n FROM {$CFG->prefix}groups_members gm\n INNER JOIN {$CFG->prefix}user u ON u.id = gm.userid\n INNER JOIN {$CFG->prefix}role_assignments ra \n ON ra.userid = u.id \n INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid\n WHERE gm.groupid='{$groupid}'\n AND ra.contextid " . get_related_contexts_string($context) . $extrawheretest . "\n ORDER BY r.sortorder,{$sort}");
return groups_calculate_role_people($rs, $context);
}
/**
* Search through course users
*
* If $coursid specifies the site course then this function searches
* through all undeleted and confirmed users
*
* @global object
* @uses SITEID
* @uses SQL_PARAMS_NAMED
* @uses CONTEXT_COURSE
* @param int $courseid The course in question.
* @param int $groupid The group in question.
* @param string $searchtext The string to search for
* @param string $sort A field to sort by
* @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
* @return array
*/
function search_users($courseid, $groupid, $searchtext, $sort = '', array $exceptions = null)
{
global $DB;
$fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
if (!empty($exceptions)) {
list($exceptions, $params) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);
$except = "AND u.id {$exceptions}";
} else {
$except = "";
$params = array();
}
if (!empty($sort)) {
$order = "ORDER BY {$sort}";
} else {
$order = "";
}
$select = "u.deleted = 0 AND u.confirmed = 1 AND (" . $DB->sql_like($fullname, ':search1', false) . " OR " . $DB->sql_like('u.email', ':search2', false) . ")";
$params['search1'] = "%{$searchtext}%";
$params['search2'] = "%{$searchtext}%";
if (!$courseid or $courseid == SITEID) {
$sql = "SELECT u.id, u.firstname, u.lastname, u.email\n FROM {user} u\n WHERE {$select}\n {$except}\n {$order}";
return $DB->get_records_sql($sql, $params);
} else {
if ($groupid) {
$sql = "SELECT u.id, u.firstname, u.lastname, u.email\n FROM {user} u\n JOIN {groups_members} gm ON gm.userid = u.id\n WHERE {$select} AND gm.groupid = :groupid\n {$except}\n {$order}";
$params['groupid'] = $groupid;
return $DB->get_records_sql($sql, $params);
} else {
$context = get_context_instance(CONTEXT_COURSE, $courseid);
$contextlists = get_related_contexts_string($context);
$sql = "SELECT u.id, u.firstname, u.lastname, u.email\n FROM {user} u\n JOIN {role_assignments} ra ON ra.userid = u.id\n WHERE {$select} AND ra.contextid {$contextlists}\n {$except}\n {$order}";
return $DB->get_records_sql($sql, $params);
}
}
}
/**
* Gets the list of roles assigned to this context and up (parents)
* @param object $context
* @param view - set to true when roles are pulled for display only
* this is so that we can filter roles with no visible
* assignment, for example, you might want to "hide" all
* course creators when browsing the course participants
* list.
* @return array
*/
function get_roles_used_in_context($context, $view = false)
{
global $DB;
// filter for roles with all hidden assignments
// no need to return when only pulling roles for reviewing
// e.g. participants page.
$hiddensql = $view && !has_capability('moodle/role:viewhiddenassigns', $context) ? ' AND ra.hidden = 0 ' : '';
$contextlist = get_related_contexts_string($context);
$sql = "SELECT DISTINCT r.id, r.name, r.shortname, r.sortorder\n FROM {role_assignments} ra, {role} r\n WHERE r.id = ra.roleid\n AND ra.contextid {$contextlist}\n {$hiddensql}\n ORDER BY r.sortorder ASC";
return $DB->get_records_sql($sql);
}
continue;
}
if ($caps = role_context_capabilities($possiblerole->id, $context, 'moodle/course:view')) {
// resolved list
if (isset($caps['moodle/course:view']) && $caps['moodle/course:view'] > 0) {
// resolved capability > 0
$validroleids[] = $possiblerole->id;
}
}
}
if ($validroleids) {
$roleids = '(' . implode(',', $validroleids) . ')';
$select = " SELECT u.id, u.firstname, u.lastname, u.email";
$countselect = "SELECT COUNT(u.id)";
$from = " FROM {$CFG->prefix}user u\n INNER JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\n INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid";
$where = " WHERE ra.contextid " . get_related_contexts_string($context) . "\n AND u.deleted = 0\n AND ra.roleid in {$roleids}";
$excsql = " AND u.id NOT IN (\n SELECT u.id\n FROM {$CFG->prefix}role_assignments r,\n {$CFG->prefix}user u\n WHERE r.contextid = {$contextid}\n AND u.id = r.userid\n AND r.roleid = {$roleid}\n {$selectsql})";
$availableusers = get_recordset_sql($select . $from . $where . $selectsql . $excsql);
}
$usercount = $availableusers->_numOfRows;
}
} else {
/************************************************************************
* *
* context level is above or equal course context level *
* in this case we pull out all users matching search criteria (if any) *
* *
************************************************************************/
/// MDL-11111 do not include user already assigned this role in this context as available users
/// so that the number of available users is right and we save time looping later
$availableusers = get_recordset_sql('SELECT id, firstname, lastname, email
/**
* Builds and return the HTML row of column totals.
* @param bool $grouponly Whether to return only group averages or all averages.
* @return string HTML
*/
function get_avghtml($grouponly = false)
{
global $CFG, $USER;
if (!$this->canviewhidden) {
// totals might be affected by hiding, if user can not see hidden grades the aggregations might be altered
// better not show them at all if user can not see all hideen grades
return;
}
$averagesdisplaytype = $this->get_pref('averagesdisplaytype');
$averagesdecimalpoints = $this->get_pref('averagesdecimalpoints');
$meanselection = $this->get_pref('meanselection');
$shownumberofgrades = $this->get_pref('shownumberofgrades');
$avghtml = '';
$avgcssclass = 'avg';
if ($grouponly) {
$straverage = get_string('groupavg', 'grades');
$showaverages = $this->currentgroup && $this->get_pref('showaverages');
$groupsql = $this->groupsql;
$groupwheresql = $this->groupwheresql;
$avgcssclass = 'groupavg';
} else {
$straverage = get_string('overallaverage', 'grades');
$showaverages = $this->get_pref('showaverages');
$groupsql = "";
$groupwheresql = "";
}
if ($shownumberofgrades) {
$straverage .= ' (' . get_string('submissions', 'grades') . ') ';
}
$totalcount = $this->get_numusers($grouponly);
if ($showaverages) {
// find sums of all grade items in course
$SQL = "SELECT g.itemid, SUM(g.finalgrade) AS sum\n FROM {$CFG->prefix}grade_items gi\n JOIN {$CFG->prefix}grade_grades g ON g.itemid = gi.id\n JOIN {$CFG->prefix}user u ON u.id = g.userid\n JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\n {$groupsql}\n WHERE gi.courseid = {$this->courseid}\n AND ra.roleid in ({$this->gradebookroles})\n AND ra.contextid " . get_related_contexts_string($this->context) . "\n AND g.finalgrade IS NOT NULL\n {$groupwheresql}\n GROUP BY g.itemid";
$sum_array = array();
if ($sums = get_records_sql($SQL)) {
foreach ($sums as $itemid => $csum) {
$sum_array[$itemid] = $csum->sum;
}
}
$columncount = 0;
$avghtml = '<tr class="' . $avgcssclass . ' r' . $this->rowcount++ . '">';
// MDL-10875 Empty grades must be evaluated as grademin, NOT always 0
// This query returns a count of ungraded grades (NULL finalgrade OR no matching record in grade_grades table)
$SQL = "SELECT gi.id, COUNT(u.id) AS count\n FROM {$CFG->prefix}grade_items gi\n CROSS JOIN {$CFG->prefix}user u\n JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\n LEFT OUTER JOIN {$CFG->prefix}grade_grades g ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)\n {$groupsql}\n WHERE gi.courseid = {$this->courseid}\n AND ra.roleid in ({$this->gradebookroles})\n AND ra.contextid " . get_related_contexts_string($this->context) . "\n AND g.id IS NULL\n {$groupwheresql}\n GROUP BY gi.id";
$ungraded_counts = get_records_sql($SQL);
$fixedstudents = $this->is_fixed_students();
if (!$fixedstudents) {
$colspan = '';
if ($this->get_pref('showuseridnumber')) {
$colspan = 'colspan="2" ';
}
$avghtml .= '<th class="header c0 range" ' . $colspan . ' scope="row">' . $straverage . '</th>';
}
foreach ($this->gtree->items as $itemid => $unused) {
$item =& $this->gtree->items[$itemid];
if ($item->needsupdate) {
$avghtml .= '<td class="cell c' . $columncount++ . '"><span class="gradingerror">' . get_string('error') . '</span></td>';
continue;
}
if (!isset($sum_array[$item->id])) {
$sum_array[$item->id] = 0;
}
if (empty($ungraded_counts[$itemid])) {
$ungraded_count = 0;
} else {
$ungraded_count = $ungraded_counts[$itemid]->count;
}
if ($meanselection == GRADE_REPORT_MEAN_GRADED) {
$mean_count = $totalcount - $ungraded_count;
} else {
// Bump up the sum by the number of ungraded items * grademin
$sum_array[$item->id] += $ungraded_count * $item->grademin;
$mean_count = $totalcount;
}
$decimalpoints = $item->get_decimals();
// Determine which display type to use for this average
if ($USER->gradeediting[$this->courseid]) {
$displaytype = GRADE_DISPLAY_TYPE_REAL;
} else {
if ($averagesdisplaytype == GRADE_REPORT_PREFERENCE_INHERIT) {
// no ==0 here, please resave the report and user preferences
$displaytype = $item->get_displaytype();
} else {
$displaytype = $averagesdisplaytype;
}
}
// Override grade_item setting if a display preference (not inherit) was set for the averages
if ($averagesdecimalpoints == GRADE_REPORT_PREFERENCE_INHERIT) {
$decimalpoints = $item->get_decimals();
} else {
$decimalpoints = $averagesdecimalpoints;
}
if (!isset($sum_array[$item->id]) || $mean_count == 0) {
$avghtml .= '<td class="cell c' . $columncount++ . '">-</td>';
} else {
$sum = $sum_array[$item->id];
$avgradeval = $sum / $mean_count;
$gradehtml = grade_format_gradevalue($avgradeval, $item, true, $displaytype, $decimalpoints);
$numberofgrades = '';
if ($shownumberofgrades) {
$numberofgrades = " ({$mean_count})";
}
$avghtml .= '<td class="cell c' . $columncount++ . '">' . $gradehtml . $numberofgrades . '</td>';
}
}
$avghtml .= '</tr>';
}
return $avghtml;
}