function display($quiz, $cm, $course)
{
/// This function just displays the report
global $CFG, $SESSION, $db, $QTYPES;
$strnoquiz = get_string('noquiz', 'quiz');
$strnoattempts = get_string('noattempts', 'quiz');
/// Only print headers if not asked to download data
$download = optional_param('download', NULL);
if (!$download) {
$this->print_header_and_tabs($cm, $course, $quiz, $reportmode = "analysis");
}
/// Construct the table for this particular report
if (!$quiz->questions) {
print_heading($strnoattempts);
return true;
}
/// Check to see if groups are being used in this quiz
if ($groupmode = groupmode($course, $cm)) {
// Groups are being used
if (!$download) {
$currentgroup = setup_and_print_groups($course, $groupmode, "report.php?id={$cm->id}&mode=analysis");
} else {
$currentgroup = get_and_set_current_group($course, $groupmode);
}
} else {
$currentgroup = get_and_set_current_group($course, $groupmode);
}
// set Table and Analysis stats options
if (!isset($SESSION->quiz_analysis_table)) {
$SESSION->quiz_analysis_table = array('attemptselection' => 0, 'lowmarklimit' => 0, 'pagesize' => 10);
}
foreach ($SESSION->quiz_analysis_table as $option => $value) {
$urlparam = optional_param($option, NULL);
if ($urlparam === NULL) {
${$option} = $value;
} else {
${$option} = $SESSION->quiz_analysis_table[$option] = $urlparam;
}
}
$scorelimit = $quiz->sumgrades * $lowmarklimit / 100;
// ULPGC ecastro DEBUG this is here to allow for different SQL to select attempts
switch ($attemptselection) {
case QUIZ_ALLATTEMPTS:
$limit = '';
$group = '';
break;
case QUIZ_HIGHESTATTEMPT:
$limit = ', max(qa.sumgrades) ';
$group = ' GROUP BY qa.userid ';
break;
case QUIZ_FIRSTATTEMPT:
$limit = ', min(qa.timemodified) ';
$group = ' GROUP BY qa.userid ';
break;
case QUIZ_LASTATTEMPT:
$limit = ', max(qa.timemodified) ';
$group = ' GROUP BY qa.userid ';
break;
}
if ($attemptselection != QUIZ_ALLATTEMPTS) {
$sql = 'SELECT qa.userid ' . $limit . 'FROM ' . $CFG->prefix . 'user u LEFT JOIN ' . $CFG->prefix . 'quiz_attempts qa ON u.id = qa.userid ' . 'WHERE qa.quiz = ' . $quiz->id . ' AND qa.preview = 0 ' . $group;
$usermax = get_records_sql_menu($sql);
}
$groupmembers = '';
$groupwhere = '';
//Add this to the SQL to show only group users
if ($currentgroup) {
$groupmembers = ', ' . groups_members_from_sql();
$groupwhere = ' AND ' . groups_members_where_sql($currentgroup, 'u.id');
}
$sql = 'SELECT qa.* FROM ' . $CFG->prefix . 'quiz_attempts qa, ' . $CFG->prefix . 'user u ' . $groupmembers . 'WHERE u.id = qa.userid AND qa.quiz = ' . $quiz->id . ' AND qa.preview = 0 AND ( qa.sumgrades >= ' . $scorelimit . ' ) ' . $groupwhere;
// ^^^^^^ es posible seleccionar aqu TODOS los quizzes, como quiere Jussi,
// pero habra que llevar la cuenta ed cada quiz para restaura las preguntas (quizquestions, states)
/// Fetch the attempts
$attempts = get_records_sql($sql);
if (empty($attempts)) {
print_heading(get_string('nothingtodisplay'));
$this->print_options_form($quiz, $cm, $attemptselection, $lowmarklimit, $pagesize);
return true;
}
/// Here we rewiew all attempts and record data to construct the table
$questions = array();
$statstable = array();
$questionarray = array();
foreach ($attempts as $attempt) {
$questionarray[] = quiz_questions_in_quiz($attempt->layout);
}
$questionlist = quiz_questions_in_quiz(implode(",", $questionarray));
$questionarray = array_unique(explode(",", $questionlist));
$questionlist = implode(",", $questionarray);
unset($questionarray);
foreach ($attempts as $attempt) {
switch ($attemptselection) {
case QUIZ_ALLATTEMPTS:
$userscore = 0;
// can be anything, not used
break;
case QUIZ_HIGHESTATTEMPT:
$userscore = $attempt->sumgrades;
break;
case QUIZ_FIRSTATTEMPT:
$userscore = $attempt->timemodified;
break;
case QUIZ_LASTATTEMPT:
$userscore = $attempt->timemodified;
break;
}
if ($attemptselection == QUIZ_ALLATTEMPTS || $userscore == $usermax[$attempt->userid]) {
$sql = "SELECT q.*, i.grade AS maxgrade, i.id AS instance" . " FROM {$CFG->prefix}question q," . " {$CFG->prefix}quiz_question_instances i" . " WHERE i.quiz = '{$quiz->id}' AND q.id = i.question" . " AND q.id IN ({$questionlist})";
if (!($quizquestions = get_records_sql($sql))) {
error('No questions found');
}
// Load the question type specific information
if (!get_question_options($quizquestions)) {
error('Could not load question options');
}
// Restore the question sessions to their most recent states
// creating new sessions where required
if (!($states = get_question_states($quizquestions, $quiz, $attempt))) {
error('Could not restore question sessions');
}
$numbers = explode(',', $questionlist);
$statsrow = array();
foreach ($numbers as $i) {
if (!isset($quizquestions[$i]) or !isset($states[$i])) {
continue;
}
$qtype = $quizquestions[$i]->qtype == 'random' ? $states[$i]->options->question->qtype : $quizquestions[$i]->qtype;
$q = get_question_responses($quizquestions[$i], $states[$i]);
if (empty($q)) {
continue;
}
$qid = $q->id;
if (!isset($questions[$qid])) {
$questions[$qid]['id'] = $qid;
$questions[$qid]['qname'] = $quizquestions[$i]->name;
foreach ($q->responses as $answer => $r) {
$r->count = 0;
$questions[$qid]['responses'][$answer] = $r->answer;
$questions[$qid]['rcounts'][$answer] = 0;
$questions[$qid]['credits'][$answer] = $r->credit;
$statsrow[$qid] = 0;
}
}
$responses = get_question_actual_response($quizquestions[$i], $states[$i]);
foreach ($responses as $resp) {
if ($resp) {
if ($key = array_search($resp, $questions[$qid]['responses'])) {
$questions[$qid]['rcounts'][$key]++;
} else {
$test = new stdClass();
$test->responses = $QTYPES[$quizquestions[$i]->qtype]->get_correct_responses($quizquestions[$i], $states[$i]);
if ($key = $QTYPES[$quizquestions[$i]->qtype]->check_response($quizquestions[$i], $states[$i], $test)) {
$questions[$qid]['rcounts'][$key]++;
} else {
$questions[$qid]['responses'][] = $resp;
$questions[$qid]['rcounts'][] = 1;
$questions[$qid]['credits'][] = 0;
}
}
}
}
$statsrow[$qid] = get_question_fraction_grade($quizquestions[$i], $states[$i]);
}
$attemptscores[$attempt->id] = $attempt->sumgrades;
$statstable[$attempt->id] = $statsrow;
}
}
// Statistics Data table built
unset($attempts);
unset($quizquestions);
unset($states);
// now calculate statistics and set the values in the $questions array
$top = max($attemptscores);
$bottom = min($attemptscores);
$gap = ($top - $bottom) / 3;
$top -= $gap;
$bottom += $gap;
foreach ($questions as $qid => $q) {
$questions[$qid] = $this->report_question_stats($q, $attemptscores, $statstable, $top, $bottom);
}
unset($attemptscores);
unset($statstable);
/// Now check if asked download of data
if ($download = optional_param('download', NULL)) {
$filename = clean_filename("{$course->shortname} " . format_string($quiz->name, true));
switch ($download) {
case "Excel":
$this->Export_Excel($questions, $filename);
break;
case "ODS":
$this->Export_ODS($questions, $filename);
break;
case "CSV":
$this->Export_CSV($questions, $filename);
break;
}
}
/// Construct the table for this particular report
$tablecolumns = array('id', 'qname', 'responses', 'credits', 'rcounts', 'rpercent', 'facility', 'qsd', 'disc_index', 'disc_coeff');
$tableheaders = array(get_string('qidtitle', 'quiz_analysis'), get_string('qtexttitle', 'quiz_analysis'), get_string('responsestitle', 'quiz_analysis'), get_string('rfractiontitle', 'quiz_analysis'), get_string('rcounttitle', 'quiz_analysis'), get_string('rpercenttitle', 'quiz_analysis'), get_string('facilitytitle', 'quiz_analysis'), get_string('stddevtitle', 'quiz_analysis'), get_string('dicsindextitle', 'quiz_analysis'), get_string('disccoefftitle', 'quiz_analysis'));
$table = new flexible_table('mod-quiz-report-itemanalysis');
$table->define_columns($tablecolumns);
$table->define_headers($tableheaders);
$table->define_baseurl($CFG->wwwroot . '/mod/quiz/report.php?q=' . $quiz->id . '&mode=analysis');
$table->sortable(true);
$table->no_sorting('rpercent');
$table->collapsible(true);
$table->initialbars(false);
$table->column_class('id', 'numcol');
$table->column_class('credits', 'numcol');
$table->column_class('rcounts', 'numcol');
$table->column_class('rpercent', 'numcol');
$table->column_class('facility', 'numcol');
$table->column_class('qsd', 'numcol');
$table->column_class('disc_index', 'numcol');
$table->column_class('disc_coeff', 'numcol');
$table->column_suppress('id');
$table->column_suppress('qname');
$table->column_suppress('facility');
$table->column_suppress('qsd');
$table->column_suppress('disc_index');
$table->column_suppress('disc_coeff');
$table->set_attribute('cellspacing', '0');
$table->set_attribute('id', 'itemanalysis');
$table->set_attribute('class', 'generaltable generalbox');
// Start working -- this is necessary as soon as the niceties are over
$table->setup();
$tablesort = $table->get_sql_sort();
$sorts = explode(",", trim($tablesort));
if ($tablesort and is_array($sorts)) {
$sortindex = array();
$sortorder = array();
foreach ($sorts as $sort) {
$data = explode(" ", trim($sort));
$sortindex[] = trim($data[0]);
$s = trim($data[1]);
if ($s == "ASC") {
$sortorder[] = SORT_ASC;
} else {
$sortorder[] = SORT_DESC;
}
}
if (count($sortindex) > 0) {
$sortindex[] = "id";
$sortorder[] = SORT_ASC;
foreach ($questions as $qid => $row) {
$index1[$qid] = $row[$sortindex[0]];
$index2[$qid] = $row[$sortindex[1]];
}
array_multisort($index1, $sortorder[0], $index2, $sortorder[1], $questions);
}
}
$format_options = new stdClass();
$format_options->para = false;
$format_options->noclean = true;
$format_options->newlines = false;
// Now it is time to page the data, simply slice the keys in the array
if (!isset($pagesize) || (int) $pagesize < 1) {
$pagesize = 10;
}
$table->pagesize($pagesize, count($questions));
$start = $table->get_page_start();
$pagequestions = array_slice(array_keys($questions), $start, $pagesize);
foreach ($pagequestions as $qnum) {
$q = $questions[$qnum];
$qid = $q['id'];
$question = get_record('question', 'id', $qid);
if (has_capability('moodle/question:manage', get_context_instance(CONTEXT_COURSE, $course->id))) {
$qnumber = " (" . link_to_popup_window('/question/question.php?id=' . $qid, '&cmid=' . $cm->id . 'editquestion', $qid, 450, 550, get_string('edit'), 'none', true) . ") ";
} else {
$qnumber = $qid;
}
$qname = '<div class="qname">' . format_text($question->name . " : ", $question->questiontextformat, $format_options, $quiz->course) . '</div>';
$qicon = print_question_icon($question, true);
$qreview = quiz_question_preview_button($quiz, $question);
$qtext = format_text($question->questiontext, $question->questiontextformat, $format_options, $quiz->course);
$qquestion = $qname . "\n" . $qtext . "\n";
$responses = array();
foreach ($q['responses'] as $aid => $resp) {
$response = new stdClass();
if ($q['credits'][$aid] <= 0) {
$qclass = 'uncorrect';
} elseif ($q['credits'][$aid] == 1) {
$qclass = 'correct';
} else {
$qclass = 'partialcorrect';
}
$response->credit = '<span class="' . $qclass . '">(' . format_float($q['credits'][$aid], 2) . ') </span>';
$response->text = '<span class="' . $qclass . '">' . format_text($resp, FORMAT_MOODLE, $format_options, $quiz->course) . ' </span>';
$count = $q['rcounts'][$aid] . '/' . $q['count'];
$response->rcount = $count;
$response->rpercent = '(' . format_float($q['rcounts'][$aid] / $q['count'] * 100, 0) . '%)';
$responses[] = $response;
}
$facility = format_float($q['facility'] * 100, 0) . "%";
$qsd = format_float($q['qsd'], 3);
$di = format_float($q['disc_index'], 2);
$dc = format_float($q['disc_coeff'], 2);
$response = array_shift($responses);
$table->add_data(array($qnumber . "\n<br />" . $qicon . "\n " . $qreview, $qquestion, $response->text, $response->credit, $response->rcount, $response->rpercent, $facility, $qsd, $di, $dc));
foreach ($responses as $response) {
$table->add_data(array('', '', $response->text, $response->credit, $response->rcount, $response->rpercent, '', '', '', ''));
}
}
print_heading_with_help(get_string("analysistitle", "quiz_analysis"), "itemanalysis", "quiz");
echo '<div id="tablecontainer">';
$table->print_html();
echo '</div>';
$this->print_options_form($quiz, $cm, $attemptselection, $lowmarklimit, $pagesize);
return true;
}
/**
* Returns a join testing user.id against member's user ID.
* Relies on 'user' table being included as 'user u'.
* Used in Quiz module reports.
* @param group ID, optional to include a test for this in the SQL.
* @return SQL string.
*/
function groups_members_join_sql($groupid = false)
{
$sql = ' JOIN ' . groups_members_from_sql() . ' ON u.id = gm.userid ';
if ($groupid) {
$sql = "AND gm.groupid = '{$groupid}' ";
}
return $sql;
}
/**
* Returns a join testing user.id against member's user ID.
* Relies on 'user' table being included as 'user u'.
* Used in Quiz module reports.
* @param group ID, optional to include a test for this in the SQL.
* @return SQL string.
*/
function groups_members_join_sql($groupid = false)
{
$sql = ' JOIN ' . groups_members_from_sql() . ' ON u.id = gm.userid ';
if ($groupid) {
$sql = "AND gm.groupid = '{$groupid}' ";
}
return $sql;
//return ' INNER JOIN '.$CFG->prefix.'role_assignments ra ON u.id=ra.userid'.
// ' INNER JOIN '.$CFG->prefix.'context c ON ra.contextid=c.id AND c.contextlevel='.CONTEXT_GROUP.' AND c.instanceid='.$groupid;
}
function get_content()
{
global $USER, $CFG, $COURSE;
if ($this->content !== NULL) {
return $this->content;
}
$this->content = new stdClass();
$this->content->text = '';
$this->content->footer = '';
if (empty($this->instance)) {
return $this->content;
}
$timetoshowusers = 300;
//Seconds default
if (isset($CFG->block_online_users_timetosee)) {
$timetoshowusers = $CFG->block_online_users_timetosee * 60;
}
$timefrom = 100 * floor((time() - $timetoshowusers) / 100);
// Round to nearest 100 seconds for better query cache
// Get context so we can check capabilities.
$context = get_context_instance(CONTEXT_COURSE, $COURSE->id);
//Calculate if we are in separate groups
$isseparategroups = $COURSE->groupmode == SEPARATEGROUPS && $COURSE->groupmodeforce && !has_capability('moodle/site:accessallgroups', $context);
//Get the user current group
$currentgroup = $isseparategroups ? get_and_set_current_group($COURSE, groupmode($COURSE)) : NULL;
$groupmembers = "";
$groupselect = "";
//Add this to the SQL to show only group users
if ($currentgroup !== NULL) {
$groupmembers = ', ' . groups_members_from_sql();
//TODO: ", {$CFG->prefix}groups_members gm ";
$groupselect = ' AND ' . groups_members_where_sql($currentgroup, 'u.id');
//" AND u.id = gm.userid AND gm.groupid = '$currentgroup'";
}
if ($COURSE->id == SITEID) {
// Site-level
$select = "SELECT u.id, u.username, u.firstname, u.lastname, u.picture, max(u.lastaccess) as lastaccess ";
$from = "FROM {$CFG->prefix}user u \n {$groupmembers} ";
$where = "WHERE u.lastaccess > {$timefrom}\n {$groupselect} ";
$order = "ORDER BY lastaccess DESC ";
} else {
// Course-level
$courseselect = "AND ul.courseid = '" . $COURSE->id . "'";
$select = "SELECT u.id, u.username, u.firstname, u.lastname, u.picture, max(ul.timeaccess) as lastaccess ";
$from = "FROM {$CFG->prefix}user_lastaccess ul,\n {$CFG->prefix}user u\n {$groupmembers} ";
$where = "WHERE ul.timeaccess > {$timefrom}\n AND u.id = ul.userid\n AND ul.courseid = {$COURSE->id}\n {$groupselect} ";
$order = "ORDER BY lastaccess DESC ";
}
$groupby = "GROUP BY u.id, u.username, u.firstname, u.lastname, u.picture ";
$SQL = $select . $from . $where . $groupby . $order;
$users = array();
$pcontext = get_related_contexts_string($context);
if ($pusers = get_records_sql($SQL, 0, 50)) {
// We'll just take the most recent 50 maximum
foreach ($pusers as $puser) {
// if current user can't view hidden role assignment in this context and
// user has a hidden role assigned at this context or any parent contexts,
// ignore this user
$SQL = "SELECT id FROM {$CFG->prefix}role_assignments\n WHERE userid = {$puser->id}\n AND contextid {$pcontext}\n AND hidden = 1";
if (!has_capability('moodle/role:viewhiddenassigns', $context) && record_exists_sql($SQL)) {
// can't see this user as the current user has no capability
// and this user has a hidden assignment at this context or higher
continue;
}
$puser->fullname = fullname($puser);
$users[$puser->id] = $puser;
}
}
//Calculate minutes
$minutes = floor($timetoshowusers / 60);
$this->content->text = "<div class=\"info\">(" . get_string("periodnminutes", "block_online_users", $minutes) . ")</div>";
//Now, we have in users, the list of users to show
//Because they are online
if (!empty($users)) {
//Accessibility: Don't want 'Alt' text for the user picture; DO want it for the envelope/message link (existing lang string).
//Accessibility: Converted <div> to <ul>, inherit existing classes & styles.
$this->content->text .= "<ul class='list'>\n";
foreach ($users as $user) {
$this->content->text .= '<li class="listentry">';
$timeago = format_time(time() - $user->lastaccess);
//bruno to calculate correctly on frontpage
if ($user->username == 'guest') {
$this->content->text .= '<div class="user">' . print_user_picture($user->id, $COURSE->id, $user->picture, 16, true, false, '', false);
$this->content->text .= get_string('guestuser') . '</div>';
} else {
$this->content->text .= '<div class="user"><a href="' . $CFG->wwwroot . '/user/view.php?id=' . $user->id . '&course=' . $COURSE->id . '" title="' . $timeago . '">';
$this->content->text .= print_user_picture($user->id, $COURSE->id, $user->picture, 16, true, false, '', false);
$this->content->text .= $user->fullname . '</a></div>';
}
if (!empty($USER->id) and $USER->id != $user->id and !empty($CFG->messaging) and !isguest() and $user->username != 'guest') {
// Only when logged in and messaging active etc
$this->content->text .= '<div class="message"><a title="' . get_string('messageselectadd') . '" href="' . $CFG->wwwroot . '/message/discussion.php?id=' . $user->id . '" onclick="this.target=\'message_' . $user->id . '\';return openpopup(\'/message/discussion.php?id=' . $user->id . '\', \'message_' . $user->id . '\', \'menubar=0,location=0,scrollbars,status,resizable,width=400,height=500\', 0);">' . '<img class="iconsmall" src="' . $CFG->pixpath . '/t/message.gif" alt="' . get_string('messageselectadd') . '" /></a></div>';
}
$this->content->text .= "</li>\n";
}
$this->content->text .= '</ul><div class="clearer"><!-- --></div>';
} else {
$this->content->text .= "<div class=\"info\">" . get_string("none") . "</div>";
}
return $this->content;
}
/**
* Returns list of user objects that are subscribed to this forum
*/
function forum_subscribed_users($course, $forum, $groupid = 0, $cache = false)
{
global $CFG;
static $resultscache = array();
if ($cache && isset($resultscache[$forum->id][$groupid])) {
return $resultscache[$forum->id][$groupid];
}
if ($groupid) {
$grouptables = ', ' . groups_members_from_sql();
$groupselect = 'AND' . groups_members_where_sql($groupid, 'u.id');
} else {
$grouptables = '';
$groupselect = '';
}
if (forum_is_forcesubscribed($forum)) {
$results = get_course_users($course->id);
// Otherwise get everyone in the course
} else {
$results = get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat, u.maildigest, u.emailstop,\n u.email, u.city, u.country, u.lastaccess, u.lastlogin, u.picture, u.timezone, u.theme, u.lang, u.trackforums\n FROM {$CFG->prefix}user u,\n {$CFG->prefix}forum_subscriptions s {$grouptables}\n WHERE s.forum = '{$forum->id}'\n AND s.userid = u.id\n AND u.deleted <> 1 {$groupselect}\n ORDER BY u.email ASC");
}
// Guest user should never be subscribed to a forum.
if ($guest = guest_user()) {
unset($results[$guest->id]);
}
if ($cache) {
$resultscache[$forum->id][$groupid] = $results;
}
return $results;
}
function survey_get_responses($surveyid, $groupid)
{
global $CFG;
if ($groupid) {
$groupsdb = ', ' . groups_members_from_sql();
$groupsql = 'AND' . groups_members_where_sql($groupid, 'u.id');
} else {
$groupsdb = "";
$groupsql = "";
}
return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.picture, MAX(a.time) as time\n FROM {$CFG->prefix}survey_answers a,\n {$CFG->prefix}user u {$groupsdb}\n WHERE a.survey = {$surveyid}\n AND a.userid = u.id {$groupsql}\n GROUP BY u.id, u.firstname, u.lastname, u.picture\n ORDER BY time ASC");
}
/**
* Main filter function.
*/
function 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 . 'tags WHERE text LIKE "' . $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 . 'blog_tag_instance bt, ';
$tagquerysql = ' AND bt.entryid = p.id AND bt.tagid = ' . $tag . ' ';
} else {
$tagtablesql = '';
$tagquerysql = '';
}
if (isloggedin() && !has_capability('moodle/legacy:guest', get_context_instance(CONTEXT_SYSTEM, SITEID), $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, SITEID))) {
$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 . groups_members_from_sql() . ', ' . $CFG->prefix . 'user u
WHERE ' . groups_members_where_sql($filterselect, 'p.userid') . '
AND u.id = p.userid
AND u.deleted = 0
' . $permissionsql . $typesql;
/*'SELECT '.$requiredfields.' FROM '.$CFG->prefix.'post p, '.$tagtablesql
.$CFG->prefix.'groups_members m, '.$CFG->prefix.'user u
WHERE p.userid = m.userid '.$tagquerysql.'
AND u.id = p.userid
AND m.groupid = '.$filterselect.'
AND u.deleted = 0
AND '.$permissionsql.$typesql;
'.$permissionsql;
*/
break;
case 'user':
$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;
}
function journal_count_entries($journal, $groupid = 0)
{
/// Counts all the journal entries (optionally in a given group)
global $CFG, $db;
if ($groupid) {
/// How many in a particular group?
return count_records_sql("SELECT COUNT(*) \n FROM {$CFG->prefix}journal_entries j,\n " . groups_members_from_sql() . "\n WHERE j.journal = {$journal->id} \n AND " . groups_members_where_sql($groupid, 'j.userid'));
} else {
/// Count all the entries from the whole course
$journals = get_records_sql("SELECT u.*\n FROM {$CFG->prefix}journal_entries j,\n {$CFG->prefix}user u \n WHERE j.userid = u.id\n AND j.journal = {$journal->id}\n ORDER BY j.modified DESC");
if (empty($journals)) {
return 0;
}
// remove unenrolled participants
foreach ($journals as $key => $user) {
if (!isteacher($journal->course, $user->id) and !isstudent($journal->course, $user->id)) {
unset($journals[$key]);
}
}
return count($journals);
}
}
/**
* Counts all real assignment submissions by ENROLLED students (not empty ones)
*
* There are also assignment type methods count_real_submissions() wich in the default
* implementation simply call this function.
* @param $groupid int optional If nonzero then count is restricted to this group
* @return int The number of submissions
*/
function assignment_count_real_submissions($assignment, $groupid = 0)
{
global $CFG;
if ($groupid) {
/// How many in a particular group?
return count_records_sql("SELECT COUNT(DISTINCT gm.userid, gm.groupid)\n FROM {$CFG->prefix}assignment_submissions a,\n " . groups_members_from_sql() . "\n WHERE a.assignment = {$assignment->id} \n AND a.timemodified > 0\n AND " . groups_members_where_sql($groupid, 'a.userid'));
} else {
$cm = get_coursemodule_from_instance('assignment', $assignment->id);
$context = get_context_instance(CONTEXT_MODULE, $cm->id);
// this is all the users with this capability set, in this context or higher
if ($users = get_users_by_capability($context, 'mod/assignment:submit', '', '', '', '', 0, '', false)) {
foreach ($users as $user) {
$array[] = $user->id;
}
$userlists = '(' . implode(',', $array) . ')';
return count_records_sql("SELECT COUNT(*)\n FROM {$CFG->prefix}assignment_submissions\n WHERE assignment = '{$assignment->id}' \n AND timemodified > 0\n AND userid IN {$userlists} ");
} else {
return 0;
// no users enroled in course
}
}
}
/**
* Search through course users
*
* If $coursid specifies the site course then this function searches
* through all undeleted and confirmed users
*
* @uses $CFG
* @uses SITEID
* @param int $courseid The course in question.
* @param int $groupid The group in question.
* @param string $searchtext ?
* @param string $sort ?
* @param string $exceptions ?
* @return object
*/
function search_users($courseid, $groupid, $searchtext, $sort = '', $exceptions = '')
{
global $CFG;
$LIKE = sql_ilike();
$fullname = sql_fullname('u.firstname', 'u.lastname');
if (!empty($exceptions)) {
$except = ' AND u.id NOT IN (' . $exceptions . ') ';
} else {
$except = '';
}
if (!empty($sort)) {
$order = ' ORDER BY ' . $sort;
} else {
$order = '';
}
$select = 'u.deleted = \'0\' AND u.confirmed = \'1\'';
if (!$courseid or $courseid == SITEID) {
return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email\n FROM {$CFG->prefix}user u\n WHERE {$select}\n AND ({$fullname} {$LIKE} '%{$searchtext}%' OR u.email {$LIKE} '%{$searchtext}%')\n {$except} {$order}");
} else {
if ($groupid) {
//TODO:check. Remove group DB dependencies.
return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email\n FROM {$CFG->prefix}user u,\n " . groups_members_from_sql() . "\n WHERE {$select} AND " . groups_members_where_sql($groupid, 'u.id') . "\n AND ({$fullname} {$LIKE} '%{$searchtext}%' OR u.email {$LIKE} '%{$searchtext}%')\n {$except} {$order}");
} else {
$context = get_context_instance(CONTEXT_COURSE, $courseid);
$contextlists = get_related_contexts_string($context);
$users = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email\n FROM {$CFG->prefix}user u,\n {$CFG->prefix}role_assignments ra\n WHERE {$select} AND ra.contextid {$contextlists} AND ra.userid = u.id\n AND ({$fullname} {$LIKE} '%{$searchtext}%' OR u.email {$LIKE} '%{$searchtext}%')\n {$except} {$order}");
}
return $users;
}
}
