public function find_users($search) { global $DB; list($wherecondition, $params) = $this->search_sql($search, ''); $fields = 'SELECT ' . $this->required_fields_sql(''); $countfields = 'SELECT COUNT(1)'; $sql = " FROM {user}\n WHERE {$wherecondition}\n AND id NOT IN (\n SELECT r.userid\n FROM {role_assignments} r\n WHERE r.contextid = :contextid\n AND r.roleid = :roleid)"; list($sort, $sortparams) = users_order_by_sql('', $search, $this->accesscontext); $order = ' ORDER BY ' . $sort; $params['contextid'] = $this->context->id; $params['roleid'] = $this->roleid; if (!$this->is_validating()) { $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params); if ($potentialmemberscount > $this->maxusersperpage) { return $this->too_many_results($search, $potentialmemberscount); } } $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams)); if (empty($availableusers)) { return array(); } if ($search) { $groupname = get_string('potusersmatching', 'core_role', $search); } else { $groupname = get_string('potusers', 'core_role'); } return array($groupname => $availableusers); }
/** * Find allowed or not allowed users of a service (depend of $this->displayallowedusers) * @global object $DB * @param <type> $search * @return array */ public function find_users($search) { global $DB; //by default wherecondition retrieves all users except the deleted, not //confirmed and guest list($wherecondition, $params) = $this->search_sql($search, 'u'); $params['serviceid'] = $this->serviceid; $fields = 'SELECT ' . $this->required_fields_sql('u'); $countfields = 'SELECT COUNT(1)'; if ($this->displayallowedusers) { ///the following SQL retrieve all users that are allowed to the serviceid $sql = " FROM {user} u, {external_services_users} esu\n WHERE {$wherecondition}\n AND u.deleted = 0\n AND esu.userid = u.id\n AND esu.externalserviceid = :serviceid"; } else { ///the following SQL retrieve all users that are not allowed to the serviceid $sql = " FROM {user} u WHERE {$wherecondition} AND u.deleted = 0\n AND NOT EXISTS (SELECT esu.userid FROM {external_services_users} esu\n WHERE esu.externalserviceid = :serviceid\n AND esu.userid = u.id)"; } list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $order = ' ORDER BY ' . $sort; if (!$this->is_validating()) { $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params); if ($potentialmemberscount > service_user_selector::MAX_USERS_PER_PAGE) { return $this->too_many_results($search, $potentialmemberscount); } } $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams)); if (empty($availableusers)) { return array(); } if ($search) { $groupname = $this->displayallowedusers ? get_string('serviceusersmatching', 'webservice', $search) : get_string('potusersmatching', 'webservice', $search); } else { $groupname = $this->displayallowedusers ? get_string('serviceusers', 'webservice') : get_string('potusers', 'webservice'); } return array($groupname => $availableusers); }
public function find_users($search) { global $DB; list($wherecondition, $params) = $this->search_sql($search, 'u'); list($ctxcondition, $ctxparams) = $DB->get_in_or_equal($this->context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'ctx'); $params = array_merge($params, $ctxparams); $params['roleid'] = $this->roleid; list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $params = array_merge($params, $sortparams); $sql = "SELECT ra.id AS raid," . $this->required_fields_sql('u') . ",ra.contextid,ra.component\n FROM {role_assignments} ra\n JOIN {user} u ON u.id = ra.userid\n JOIN {context} ctx ON ra.contextid = ctx.id\n WHERE {$wherecondition}\n AND ctx.id {$ctxcondition}\n AND ra.roleid = :roleid\n ORDER BY ctx.depth DESC, ra.component, {$sort}"; $contextusers = $DB->get_records_sql($sql, $params); // No users at all. if (empty($contextusers)) { return array(); } // We have users. Out put them in groups by context depth. // To help the loop below, tack a dummy user on the end of the results // array, to trigger output of the last group. $dummyuser = new stdClass(); $dummyuser->contextid = 0; $dummyuser->id = 0; $dummyuser->component = ''; $contextusers[] = $dummyuser; $results = array(); // The results array we are building up. $doneusers = array(); // Ensures we only list each user at most once. $currentcontextid = $this->context->id; $currentgroup = array(); foreach ($contextusers as $user) { if (isset($doneusers[$user->id])) { continue; } $doneusers[$user->id] = 1; if ($user->contextid != $currentcontextid) { // We have got to the end of the previous group. Add it to the results array. if ($currentcontextid == $this->context->id) { $groupname = $this->this_con_group_name($search, count($currentgroup)); } else { $groupname = $this->parent_con_group_name($search, $currentcontextid); } $results[$groupname] = $currentgroup; // Get ready for the next group. $currentcontextid = $user->contextid; $currentgroup = array(); } // Add this user to the group we are building up. unset($user->contextid); if ($currentcontextid != $this->context->id) { $user->disabled = true; } if ($user->component !== '') { // Bad luck, you can tweak only manual role assignments. $user->disabled = true; } unset($user->component); $currentgroup[$user->id] = $user; } return $results; }
public function find_users($search) { global $CFG, $DB; list($wherecondition, $params) = $this->search_sql($search, ''); $fields = 'SELECT ' . $this->required_fields_sql(''); $countfields = 'SELECT COUNT(1)'; $sql = " FROM {user}\n WHERE {$wherecondition} AND mnethostid = :localmnet"; // It could be dangerous to make remote users admins and also this could lead to other problems. $params['localmnet'] = $CFG->mnet_localhost_id; list($sort, $sortparams) = users_order_by_sql('', $search, $this->accesscontext); $order = ' ORDER BY ' . $sort; // Check to see if there are too many to show sensibly. if (!$this->is_validating()) { $potentialcount = $DB->count_records_sql($countfields . $sql, $params); if ($potentialcount > $this->maxusersperpage) { return $this->too_many_results($search, $potentialcount); } } $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams)); if (empty($availableusers)) { return array(); } if ($search) { $groupname = get_string('potusersmatching', 'core_role', $search); } else { $groupname = get_string('potusers', 'core_role'); } return array($groupname => $availableusers); }
/** * Finds all subscribed users * * @param string $search * @return array */ public function find_users($search) { global $DB; list($wherecondition, $params) = $this->search_sql($search, 'u'); $params['forumid'] = $this->forumid; // only active enrolled or everybody on the frontpage list($esql, $eparams) = get_enrolled_sql($this->context, '', $this->currentgroup, true); $fields = $this->required_fields_sql('u'); list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $params = array_merge($params, $eparams, $sortparams); $subscribers = $DB->get_records_sql("SELECT {$fields}\n FROM {user} u\n JOIN ({$esql}) je ON je.id = u.id\n JOIN {forum_subscriptions} s ON s.userid = u.id\n WHERE {$wherecondition} AND s.forum = :forumid\n ORDER BY {$sort}", $params); return array(get_string("existingsubscribers", 'forum') => $subscribers); }
/** * Finds all subscribed users * * @param string $search * @return array */ public function find_users($search) { global $DB; list($wherecondition, $params) = $this->search_sql($search, 'u'); $params['twfid'] = $this->twfid; // only active enrolled or everybody on the frontpage list($esql, $eparams) = get_enrolled_sql($this->context, '', $this->currentgroup, true); $fields = $this->required_fields_sql('u'); list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $params = array_merge($params, $eparams, $sortparams); $subscribers = $DB->get_records_sql("SELECT {$fields}\n FROM {user} u\n JOIN ({$esql}) je ON je.id = u.id\n JOIN {twf_subscriptions} s ON s.userid = u.id\n WHERE {$wherecondition} AND s.twf = :twfid\n ORDER BY {$sort}", $params); $cm = get_coursemodule_from_instance('twf', $this->twfid); $modinfo = get_fast_modinfo($cm->course); $info = new \core_availability\info_module($modinfo->get_cm($cm->id)); $subscribers = $info->filter_user_list($subscribers); return array(get_string("existingsubscribers", 'twf') => $subscribers); }
public function test_users_order_by_sql_search_with_extra_fields_and_prefix() { global $CFG, $DB; $CFG->showuseridentity = 'email,idnumber'; $this->setAdminUser(); $this->resetAfterTest(true); list($sort, $params) = users_order_by_sql('u', 'search', context_system::instance()); $this->assert_same_sql('CASE WHEN ' . $DB->sql_fullname('u.firstname', 'u.lastname') . ' = :usersortexact1 OR LOWER(u.firstname) = LOWER(:usersortexact2) OR LOWER(u.lastname) = LOWER(:usersortexact3) OR LOWER(u.email) = LOWER(:usersortexact4) OR LOWER(u.idnumber) = LOWER(:usersortexact5) THEN 0 ELSE 1 END, u.lastname, u.firstname, u.id', $sort); $this->assertEquals(array('usersortexact1' => 'search', 'usersortexact2' => 'search', 'usersortexact3' => 'search', 'usersortexact4' => 'search', 'usersortexact5' => 'search'), $params); }
public function find_users($search) { global $DB, $CFG; list($wherecondition, $params) = $this->search_sql($search, ''); $fields = 'SELECT ' . $this->required_fields_sql(''); if ($wherecondition) { $wherecondition = "{$wherecondition} AND id IN ({$CFG->siteadmins})"; } else { $wherecondition = "id IN ({$CFG->siteadmins})"; } $sql = " FROM {user}\n WHERE {$wherecondition}"; list($sort, $sortparams) = users_order_by_sql('', $search, $this->accesscontext); $params = array_merge($params, $sortparams); $order = ' ORDER BY ' . $sort; $availableusers = $DB->get_records_sql($fields . $sql . $order, $params); if (empty($availableusers)) { return array(); } $mainadmin = array(); $mainadminuser = get_admin(); if ($mainadminuser && isset($availableusers[$mainadminuser->id])) { $mainadmin = array($mainadminuser->id => $availableusers[$mainadminuser->id]); unset($availableusers[$mainadminuser->id]); } $result = array(); if ($mainadmin) { $result[get_string('mainadmin', 'core_role')] = $mainadmin; } if ($availableusers) { if ($search) { $groupname = get_string('extusersmatching', 'core_role', $search); } else { $groupname = get_string('extusers', 'core_role'); } $result[$groupname] = $availableusers; } return $result; }
public function find_users($search) { global $DB; list($enrolsql, $eparams) = get_enrolled_sql($this->context); // Now we have to go to the database. list($wherecondition, $params) = $this->search_sql($search, 'u'); $params = array_merge($params, $eparams); if ($wherecondition) { $wherecondition = ' AND ' . $wherecondition; } $fields = 'SELECT ' . $this->required_fields_sql('u'); $countfields = 'SELECT COUNT(u.id)'; $sql = " FROM ({$enrolsql}) enrolled_users_view\n JOIN {user} u ON u.id = enrolled_users_view.id\n LEFT JOIN {role_assignments} ra ON (ra.userid = enrolled_users_view.id AND\n ra.roleid = :roleid AND ra.contextid = :contextid)\n WHERE ra.id IS NULL\n {$wherecondition}"; $params['contextid'] = $this->context->id; $params['roleid'] = $this->roleid; list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $order = ' ORDER BY ' . $sort; // Check to see if there are too many to show sensibly. if (!$this->is_validating()) { $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params); if ($potentialmemberscount > $this->maxusersperpage) { return $this->too_many_results($search, $potentialmemberscount); } } // If not, show them. $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams)); if (empty($availableusers)) { return array(); } if ($search) { $groupname = get_string('potusersmatching', 'core_role', $search); } else { $groupname = get_string('potusers', 'core_role'); } return array($groupname => $availableusers); }
/** * Set the marking allocation for multiple users * * @return void */ protected function process_set_batch_marking_allocation() { global $CFG, $DB; // Include batch marking allocation form. require_once $CFG->dirroot . '/mod/assign/batchsetallocatedmarkerform.php'; $formparams = array('userscount' => 0, 'usershtml' => ''); list($sort, $params) = users_order_by_sql(); $markers = get_users_by_capability($this->get_context(), 'mod/assign:grade', '', $sort); $markerlist = array(); foreach ($markers as $marker) { $markerlist[$marker->id] = fullname($marker); } $formparams['markers'] = $markerlist; $mform = new mod_assign_batch_set_allocatedmarker_form(null, $formparams); if ($mform->is_cancelled()) { return true; } if ($formdata = $mform->get_data()) { $useridlist = explode(',', $formdata->selectedusers); $marker = $DB->get_record('user', array('id' => $formdata->allocatedmarker), '*', MUST_EXIST); foreach ($useridlist as $userid) { $flags = $this->get_user_flags($userid, true); if ($flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_READYFORREVIEW || $flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_INREVIEW || $flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_READYFORRELEASE || $flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_RELEASED) { continue; // Allocated marker can only be changed in certain workflow states. } $flags->allocatedmarker = $marker->id; if ($this->update_user_flags($flags)) { $user = $DB->get_record('user', array('id' => $userid), '*', MUST_EXIST); \mod_assign\event\marker_updated::create_from_marker($this, $user, $marker)->trigger(); } } } }
foreach ($pages as $key => $page) { if ($page->qtype != LESSON_PAGE_ESSAY) { unset($pages[$key]); } } if (count($pages) > 0) { // Get only the attempts that are in response to essay questions list($usql, $parameters) = $DB->get_in_or_equal(array_keys($pages), SQL_PARAMS_NAMED); // If group selected, only get group members attempts. list($esql, $params) = get_enrolled_sql($context, '', $currentgroup, true); $parameters = array_merge($params, $parameters); $sql = "SELECT a.*\n FROM {lesson_attempts} a\n JOIN ({$esql}) ue ON a.userid = ue.id\n WHERE pageid {$usql}"; if ($essayattempts = $DB->get_records_sql($sql, $parameters)) { $ufields = user_picture::fields('u'); // Get all the users who have taken this lesson. list($sort, $sortparams) = users_order_by_sql('u'); $params['lessonid'] = $lesson->id; $sql = "SELECT DISTINCT {$ufields}\n FROM {user} u\n JOIN {lesson_attempts} a ON u.id = a.userid\n JOIN ({$esql}) ue ON ue.id = a.userid\n WHERE a.lessonid = :lessonid\n ORDER BY {$sort}"; if (!($users = $DB->get_records_sql($sql, $params))) { $mode = 'none'; // not displaying anything if (!empty($currentgroup)) { $groupname = groups_get_group_name($currentgroup); $lesson->add_message(get_string('noonehasansweredgroup', 'lesson', $groupname)); } else { $lesson->add_message(get_string('noonehasanswered', 'lesson')); } } } else { $mode = 'none'; // not displaying anything
/** * Prepares data object with all workshop grades to be rendered * * @param int $userid the user we are preparing the report for * @param int $groupid if non-zero, prepare the report for the given group only * @param int $page the current page (for the pagination) * @param int $perpage participants per page (for the pagination) * @param string $sortby lastname|firstname|submissiontitle|submissiongrade|gradinggrade * @param string $sorthow ASC|DESC * @return stdclass data for the renderer */ public function prepare_grading_report_data($userid, $groupid, $page, $perpage, $sortby, $sorthow) { global $DB; $canviewall = has_capability('mod/workshop:viewallassessments', $this->context, $userid); $isparticipant = $this->is_participant($userid); if (!$canviewall and !$isparticipant) { // who the hell is this? return array(); } if (!in_array($sortby, array('lastname', 'firstname', 'submissiontitle', 'submissiongrade', 'gradinggrade'))) { $sortby = 'lastname'; } if (!($sorthow === 'ASC' or $sorthow === 'DESC')) { $sorthow = 'ASC'; } // get the list of user ids to be displayed if ($canviewall) { $participants = $this->get_participants(false, $groupid); } else { // this is an ordinary workshop participant (aka student) - display the report just for him/her $participants = array($userid => (object) array('id' => $userid)); } // we will need to know the number of all records later for the pagination purposes $numofparticipants = count($participants); if ($numofparticipants > 0) { // load all fields which can be used for sorting and paginate the records list($participantids, $params) = $DB->get_in_or_equal(array_keys($participants), SQL_PARAMS_NAMED); $params['workshopid1'] = $this->id; $params['workshopid2'] = $this->id; $sqlsort = array(); $sqlsortfields = array($sortby => $sorthow) + array('lastname' => 'ASC', 'firstname' => 'ASC', 'u.id' => 'ASC'); foreach ($sqlsortfields as $sqlsortfieldname => $sqlsortfieldhow) { $sqlsort[] = $sqlsortfieldname . ' ' . $sqlsortfieldhow; } $sqlsort = implode(',', $sqlsort); $picturefields = user_picture::fields('u', array(), 'userid'); $sql = "SELECT {$picturefields}, s.title AS submissiontitle, s.grade AS submissiongrade, ag.gradinggrade\n FROM {user} u\n LEFT JOIN {workshop_submissions} s ON (s.authorid = u.id AND s.workshopid = :workshopid1 AND s.example = 0)\n LEFT JOIN {workshop_aggregations} ag ON (ag.userid = u.id AND ag.workshopid = :workshopid2)\n WHERE u.id {$participantids}\n ORDER BY {$sqlsort}"; $participants = $DB->get_records_sql($sql, $params, $page * $perpage, $perpage); } else { $participants = array(); } // this will hold the information needed to display user names and pictures $userinfo = array(); // get the user details for all participants to display $additionalnames = get_all_user_name_fields(); foreach ($participants as $participant) { if (!isset($userinfo[$participant->userid])) { $userinfo[$participant->userid] = new stdclass(); $userinfo[$participant->userid]->id = $participant->userid; $userinfo[$participant->userid]->picture = $participant->picture; $userinfo[$participant->userid]->imagealt = $participant->imagealt; $userinfo[$participant->userid]->email = $participant->email; foreach ($additionalnames as $addname) { $userinfo[$participant->userid]->{$addname} = $participant->{$addname}; } } } // load the submissions details $submissions = $this->get_submissions(array_keys($participants)); // get the user details for all moderators (teachers) that have overridden a submission grade foreach ($submissions as $submission) { if (!isset($userinfo[$submission->gradeoverby])) { $userinfo[$submission->gradeoverby] = new stdclass(); $userinfo[$submission->gradeoverby]->id = $submission->gradeoverby; $userinfo[$submission->gradeoverby]->picture = $submission->overpicture; $userinfo[$submission->gradeoverby]->imagealt = $submission->overimagealt; $userinfo[$submission->gradeoverby]->email = $submission->overemail; foreach ($additionalnames as $addname) { $temp = 'over' . $addname; $userinfo[$submission->gradeoverby]->{$addname} = $submission->{$temp}; } } } // get the user details for all reviewers of the displayed participants $reviewers = array(); if ($submissions) { list($submissionids, $params) = $DB->get_in_or_equal(array_keys($submissions), SQL_PARAMS_NAMED); list($sort, $sortparams) = users_order_by_sql('r'); $picturefields = user_picture::fields('r', array(), 'reviewerid'); $sql = "SELECT a.id AS assessmentid, a.submissionid, a.grade, a.gradinggrade, a.gradinggradeover, a.weight,\n {$picturefields}, s.id AS submissionid, s.authorid\n FROM {workshop_assessments} a\n JOIN {user} r ON (a.reviewerid = r.id)\n JOIN {workshop_submissions} s ON (a.submissionid = s.id AND s.example = 0)\n WHERE a.submissionid {$submissionids}\n ORDER BY a.weight DESC, {$sort}"; $reviewers = $DB->get_records_sql($sql, array_merge($params, $sortparams)); foreach ($reviewers as $reviewer) { if (!isset($userinfo[$reviewer->reviewerid])) { $userinfo[$reviewer->reviewerid] = new stdclass(); $userinfo[$reviewer->reviewerid]->id = $reviewer->reviewerid; $userinfo[$reviewer->reviewerid]->picture = $reviewer->picture; $userinfo[$reviewer->reviewerid]->imagealt = $reviewer->imagealt; $userinfo[$reviewer->reviewerid]->email = $reviewer->email; foreach ($additionalnames as $addname) { $userinfo[$reviewer->reviewerid]->{$addname} = $reviewer->{$addname}; } } } } // get the user details for all reviewees of the displayed participants $reviewees = array(); if ($participants) { list($participantids, $params) = $DB->get_in_or_equal(array_keys($participants), SQL_PARAMS_NAMED); list($sort, $sortparams) = users_order_by_sql('e'); $params['workshopid'] = $this->id; $picturefields = user_picture::fields('e', array(), 'authorid'); $sql = "SELECT a.id AS assessmentid, a.submissionid, a.grade, a.gradinggrade, a.gradinggradeover, a.reviewerid, a.weight,\n s.id AS submissionid, {$picturefields}\n FROM {user} u\n JOIN {workshop_assessments} a ON (a.reviewerid = u.id)\n JOIN {workshop_submissions} s ON (a.submissionid = s.id AND s.example = 0)\n JOIN {user} e ON (s.authorid = e.id)\n WHERE u.id {$participantids} AND s.workshopid = :workshopid\n ORDER BY a.weight DESC, {$sort}"; $reviewees = $DB->get_records_sql($sql, array_merge($params, $sortparams)); foreach ($reviewees as $reviewee) { if (!isset($userinfo[$reviewee->authorid])) { $userinfo[$reviewee->authorid] = new stdclass(); $userinfo[$reviewee->authorid]->id = $reviewee->authorid; $userinfo[$reviewee->authorid]->picture = $reviewee->picture; $userinfo[$reviewee->authorid]->imagealt = $reviewee->imagealt; $userinfo[$reviewee->authorid]->email = $reviewee->email; foreach ($additionalnames as $addname) { $userinfo[$reviewee->authorid]->{$addname} = $reviewee->{$addname}; } } } } // finally populate the object to be rendered $grades = $participants; foreach ($participants as $participant) { // set up default (null) values $grades[$participant->userid]->submissionid = null; $grades[$participant->userid]->submissiontitle = null; $grades[$participant->userid]->submissiongrade = null; $grades[$participant->userid]->submissiongradeover = null; $grades[$participant->userid]->submissiongradeoverby = null; $grades[$participant->userid]->submissionpublished = null; $grades[$participant->userid]->reviewedby = array(); $grades[$participant->userid]->reviewerof = array(); } unset($participants); unset($participant); foreach ($submissions as $submission) { $grades[$submission->authorid]->submissionid = $submission->id; $grades[$submission->authorid]->submissiontitle = $submission->title; $grades[$submission->authorid]->submissiongrade = $this->real_grade($submission->grade); $grades[$submission->authorid]->submissiongradeover = $this->real_grade($submission->gradeover); $grades[$submission->authorid]->submissiongradeoverby = $submission->gradeoverby; $grades[$submission->authorid]->submissionpublished = $submission->published; } unset($submissions); unset($submission); foreach ($reviewers as $reviewer) { $info = new stdclass(); $info->userid = $reviewer->reviewerid; $info->assessmentid = $reviewer->assessmentid; $info->submissionid = $reviewer->submissionid; $info->grade = $this->real_grade($reviewer->grade); $info->gradinggrade = $this->real_grading_grade($reviewer->gradinggrade); $info->gradinggradeover = $this->real_grading_grade($reviewer->gradinggradeover); $info->weight = $reviewer->weight; $grades[$reviewer->authorid]->reviewedby[$reviewer->reviewerid] = $info; } unset($reviewers); unset($reviewer); foreach ($reviewees as $reviewee) { $info = new stdclass(); $info->userid = $reviewee->authorid; $info->assessmentid = $reviewee->assessmentid; $info->submissionid = $reviewee->submissionid; $info->grade = $this->real_grade($reviewee->grade); $info->gradinggrade = $this->real_grading_grade($reviewee->gradinggrade); $info->gradinggradeover = $this->real_grading_grade($reviewee->gradinggradeover); $info->weight = $reviewee->weight; $grades[$reviewee->reviewerid]->reviewerof[$reviewee->authorid] = $info; } unset($reviewees); unset($reviewee); foreach ($grades as $grade) { $grade->gradinggrade = $this->real_grading_grade($grade->gradinggrade); } $data = new stdclass(); $data->grades = $grades; $data->userinfo = $userinfo; $data->totalcount = $numofparticipants; $data->maxgrade = $this->real_grade(100); $data->maxgradinggrade = $this->real_grading_grade(100); return $data; }
/** * Given list of DB records from table course populates each record with list of users with course contact roles * * This function fills the courses with raw information as {@link get_role_users()} would do. * See also {@link course_in_list::get_course_contacts()} for more readable return * * $courses[$i]->managers = array( * $roleassignmentid => $roleuser, * ... * ); * * where $roleuser is an stdClass with the following properties: * * $roleuser->raid - role assignment id * $roleuser->id - user id * $roleuser->username * $roleuser->firstname * $roleuser->lastname * $roleuser->rolecoursealias * $roleuser->rolename * $roleuser->sortorder - role sortorder * $roleuser->roleid * $roleuser->roleshortname * * @todo MDL-38596 minimize number of queries to preload contacts for the list of courses * * @param array $courses */ public static function preload_course_contacts(&$courses) { global $CFG, $DB; if (empty($courses) || empty($CFG->coursecontact)) { return; } $managerroles = explode(',', $CFG->coursecontact); $cache = cache::make('core', 'coursecontacts'); $cacheddata = $cache->get_many(array_merge(array('basic'), array_keys($courses))); // Check if cache was set for the current course contacts and it is not yet expired. if (empty($cacheddata['basic']) || $cacheddata['basic']['roles'] !== $CFG->coursecontact || $cacheddata['basic']['lastreset'] < time() - self::CACHE_COURSE_CONTACTS_TTL) { // Reset cache. $cache->purge(); $cache->set('basic', array('roles' => $CFG->coursecontact, 'lastreset' => time())); $cacheddata = $cache->get_many(array_merge(array('basic'), array_keys($courses))); } $courseids = array(); foreach (array_keys($courses) as $id) { if ($cacheddata[$id] !== false) { $courses[$id]->managers = $cacheddata[$id]; } else { $courseids[] = $id; } } // Array $courseids now stores list of ids of courses for which we still need to retrieve contacts. if (empty($courseids)) { return; } // First build the array of all context ids of the courses and their categories. $allcontexts = array(); foreach ($courseids as $id) { $context = context_course::instance($id); $courses[$id]->managers = array(); foreach (preg_split('|/|', $context->path, 0, PREG_SPLIT_NO_EMPTY) as $ctxid) { if (!isset($allcontexts[$ctxid])) { $allcontexts[$ctxid] = array(); } $allcontexts[$ctxid][] = $id; } } // Fetch list of all users with course contact roles in any of the courses contexts or parent contexts. list($sql1, $params1) = $DB->get_in_or_equal(array_keys($allcontexts), SQL_PARAMS_NAMED, 'ctxid'); list($sql2, $params2) = $DB->get_in_or_equal($managerroles, SQL_PARAMS_NAMED, 'rid'); list($sort, $sortparams) = users_order_by_sql('u'); $notdeleted = array('notdeleted' => 0); $allnames = get_all_user_name_fields(true, 'u'); $sql = "SELECT ra.contextid, ra.id AS raid,\n r.id AS roleid, r.name AS rolename, r.shortname AS roleshortname,\n rn.name AS rolecoursealias, u.id, u.username, {$allnames}\n FROM {role_assignments} ra\n JOIN {user} u ON ra.userid = u.id\n JOIN {role} r ON ra.roleid = r.id\n LEFT JOIN {role_names} rn ON (rn.contextid = ra.contextid AND rn.roleid = r.id)\n WHERE ra.contextid " . $sql1 . " AND ra.roleid " . $sql2 . " AND u.deleted = :notdeleted\n ORDER BY r.sortorder, {$sort}"; $rs = $DB->get_recordset_sql($sql, $params1 + $params2 + $notdeleted + $sortparams); $checkenrolments = array(); foreach ($rs as $ra) { foreach ($allcontexts[$ra->contextid] as $id) { $courses[$id]->managers[$ra->raid] = $ra; if (!isset($checkenrolments[$id])) { $checkenrolments[$id] = array(); } $checkenrolments[$id][] = $ra->id; } } $rs->close(); // Remove from course contacts users who are not enrolled in the course. $enrolleduserids = self::ensure_users_enrolled($checkenrolments); foreach ($checkenrolments as $id => $userids) { if (empty($enrolleduserids[$id])) { $courses[$id]->managers = array(); } else { if ($notenrolled = array_diff($userids, $enrolleduserids[$id])) { foreach ($courses[$id]->managers as $raid => $ra) { if (in_array($ra->id, $notenrolled)) { unset($courses[$id]->managers[$raid]); } } } } } // Set the cache. $values = array(); foreach ($courseids as $id) { $values[$id] = $courses[$id]->managers; } $cache->set_many($values); }
/** * Get the list of potential subscribers to a forum. * * @param context_module $context the forum context. * @param integer $groupid the id of a group, or 0 for all groups. * @param string $fields the list of fields to return for each user. As for get_users_by_capability. * @param string $sort sort order. As for get_users_by_capability. * @return array list of users. */ public static function get_potential_subscribers($context, $groupid, $fields, $sort = '') { global $DB; // Only active enrolled users or everybody on the frontpage. list($esql, $params) = get_enrolled_sql($context, 'mod/forum:allowforcesubscribe', $groupid, true); if (!$sort) { list($sort, $sortparams) = users_order_by_sql('u'); $params = array_merge($params, $sortparams); } $sql = "SELECT {$fields}\n FROM {user} u\n JOIN ({$esql}) je ON je.id = u.id\n ORDER BY {$sort}"; 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); }
function referentiel_get_accompagnements_teacher($referentiel_instance_id, $course_id, $ref_teacher) { // retourne la liste des id des accompagnes // global $DB; if (!empty($referentiel_instance_id) && !empty($course_id) && !empty($ref_teacher)) { $params = array("refid" => "{$referentiel_instance_id}", "courseid" => "{$course_id}", "teacherid" => "{$ref_teacher}"); list($sort, $sortparams) = users_order_by_sql('u'); $params += $sortparams; $sql = "SELECT ra.userid FROM {referentiel_accompagnement} AS ra, {user} AS u\n WHERE ra.ref_instance=:refid\n \tAND ra.courseid=:courseid AND u.id=ra.userid AND ra.teacherid=:teacherid\n ORDER BY {$sort} "; return $DB->get_records_sql($sql, $params); } return NULL; }
/** * Load all of the uses who have the capability into choice array * * @return bool Always returns true */ function load_choices() { if (is_array($this->choices)) { return true; } list($sort, $sortparams) = users_order_by_sql('u'); if (!empty($sortparams)) { throw new coding_exception('users_order_by_sql returned some query parameters. ' . 'This is unexpected, and a problem because there is no way to pass these ' . 'parameters to get_users_by_capability. See MDL-34657.'); } $userfields = 'u.id, u.username, ' . get_all_user_name_fields(true, 'u'); $users = get_users_by_capability(context_system::instance(), $this->capability, $userfields, $sort); $this->choices = array('$@NONE@$' => get_string('nobody'), '$@ALL@$' => get_string('everyonewhocan', 'admin', get_capability_string($this->capability))); if ($this->includeadmins) { $admins = get_admins(); foreach ($admins as $user) { $this->choices[$user->id] = fullname($user); } } if (is_array($users)) { foreach ($users as $user) { $this->choices[$user->id] = fullname($user); } } return true; }
/** * Find our users who could be enrolled into the remote course * * Our users must have 'moodle/site:mnetlogintoremote' capability assigned. * Remote users, guests, deleted and not confirmed users are not returned. * * @param string $search * @return array */ public function find_users($search) { global $CFG, $DB; $systemcontext = context_system::instance(); $userids = get_users_by_capability($systemcontext, 'moodle/site:mnetlogintoremote', 'u.id'); if (empty($userids)) { return array(); } list($usql, $uparams) = $DB->get_in_or_equal(array_keys($userids), SQL_PARAMS_NAMED, 'uid'); list($wherecondition, $params) = $this->search_sql($search, 'u'); $params = array_merge($params, $uparams); $params['hostid'] = $this->hostid; $params['remotecourseid'] = $this->remotecourseid; $params['mnetlocalhostid'] = $CFG->mnet_localhost_id; $fields = "SELECT " . $this->required_fields_sql("u"); $countfields = "SELECT COUNT(1)"; $sql = " FROM {user} u\n WHERE {$wherecondition}\n AND u.mnethostid = :mnetlocalhostid\n AND u.id {$usql}\n AND u.id NOT IN (SELECT e.userid\n FROM {mnetservice_enrol_enrolments} e\n WHERE (e.hostid = :hostid AND e.remotecourseid = :remotecourseid))"; list($sort, $sortparams) = users_order_by_sql('u'); $order = " ORDER BY {$sort}"; if (!$this->is_validating()) { $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params); if ($potentialmemberscount > 100) { return $this->too_many_results($search, $potentialmemberscount); } } $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams)); if (empty($availableusers)) { return array(); } if ($search) { $groupname = get_string('enrolcandidatesmatching', 'enrol', $search); } else { $groupname = get_string('enrolcandidates', 'enrol'); } return array($groupname => $availableusers); }
/** * Gets all the users assigned this role in this context or higher * * @param int $roleid (can also be an array of ints!) * @param context $context * @param bool $parent if true, get list of users assigned in higher context too * @param string $fields fields from user (u.) , role assignment (ra) or role (r.) * @param string $sort sort from user (u.) , role assignment (ra.) or role (r.). * null => use default sort from users_order_by_sql. * @param bool $gethidden_ignored use enrolments instead * @param string $group defaults to '' * @param mixed $limitfrom defaults to '' * @param mixed $limitnum defaults to '' * @param string $extrawheretest defaults to '' * @param array $whereorsortparams any paramter values used by $sort or $extrawheretest. * @return array */ function get_role_users($roleid, context $context, $parent = false, $fields = '', $sort = null, $gethidden_ignored = null, $group = '', $limitfrom = '', $limitnum = '', $extrawheretest = '', $whereorsortparams = array()) { global $DB; if (empty($fields)) { $fields = 'u.id, u.confirmed, u.username, u.firstname, u.lastname, ' . 'u.maildisplay, u.mailformat, u.maildigest, u.email, u.emailstop, u.city, ' . 'u.country, u.picture, u.idnumber, u.department, u.institution, ' . 'u.lang, u.timezone, u.lastaccess, u.mnethostid, r.name AS rolename, r.sortorder, ' . 'r.shortname AS roleshortname, rn.name AS rolecoursealias'; } $parentcontexts = ''; if ($parent) { $parentcontexts = substr($context->path, 1); // kill leading slash $parentcontexts = str_replace('/', ',', $parentcontexts); if ($parentcontexts !== '') { $parentcontexts = ' OR ra.contextid IN (' . $parentcontexts . ' )'; } } if ($roleid) { list($rids, $params) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'r'); $roleselect = "AND ra.roleid {$rids}"; } else { $params = array(); $roleselect = ''; } if ($coursecontext = $context->get_course_context(false)) { $params['coursecontext'] = $coursecontext->id; } else { $params['coursecontext'] = 0; } if ($group) { $groupjoin = "JOIN {groups_members} gm ON gm.userid = u.id"; $groupselect = " AND gm.groupid = :groupid "; $params['groupid'] = $group; } else { $groupjoin = ''; $groupselect = ''; } $params['contextid'] = $context->id; if ($extrawheretest) { $extrawheretest = ' AND ' . $extrawheretest; } if ($whereorsortparams) { $params = array_merge($params, $whereparams); } if (!$sort) { list($sort, $sortparams) = users_order_by_sql('u'); $params = array_merge($params, $sortparams); } $sql = "SELECT DISTINCT {$fields}, ra.roleid\n FROM {role_assignments} ra\n JOIN {user} u ON u.id = ra.userid\n JOIN {role} r ON ra.roleid = r.id\n LEFT JOIN {role_names} rn ON (rn.contextid = :coursecontext AND rn.roleid = r.id)\n {$groupjoin}\n WHERE (ra.contextid = :contextid {$parentcontexts})\n {$roleselect}\n {$groupselect}\n {$extrawheretest}\n ORDER BY {$sort}"; // join now so that we can just use fullname() later return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); }
/** * Get course participants details * * @param int $courseid course id * @param array $options options { * 'name' => option name * 'value' => option value * } * @return array An array of users */ public static function get_enrolled_users($courseid, $options = array()) { global $CFG, $USER, $DB; require_once $CFG->dirroot . "/user/lib.php"; $params = self::validate_parameters(self::get_enrolled_users_parameters(), array('courseid' => $courseid, 'options' => $options)); $withcapability = ''; $groupid = 0; $onlyactive = false; $userfields = array(); $limitfrom = 0; $limitnumber = 0; $sortby = 'us.id'; $sortparams = array(); $sortdirection = 'ASC'; foreach ($options as $option) { switch ($option['name']) { case 'withcapability': $withcapability = $option['value']; break; case 'groupid': $groupid = (int) $option['value']; break; case 'onlyactive': $onlyactive = !empty($option['value']); break; case 'userfields': $thefields = explode(',', $option['value']); foreach ($thefields as $f) { $userfields[] = clean_param($f, PARAM_ALPHANUMEXT); } break; case 'limitfrom': $limitfrom = clean_param($option['value'], PARAM_INT); break; case 'limitnumber': $limitnumber = clean_param($option['value'], PARAM_INT); break; case 'sortby': $sortallowedvalues = array('id', 'firstname', 'lastname', 'siteorder'); if (!in_array($option['value'], $sortallowedvalues)) { throw new invalid_parameter_exception('Invalid value for sortby parameter (value: ' . $option['value'] . '),' . 'allowed values are: ' . implode(',', $sortallowedvalues)); } if ($option['value'] == 'siteorder') { list($sortby, $sortparams) = users_order_by_sql('us'); } else { $sortby = 'us.' . $option['value']; } break; case 'sortdirection': $sortdirection = strtoupper($option['value']); $directionallowedvalues = array('ASC', 'DESC'); if (!in_array($sortdirection, $directionallowedvalues)) { throw new invalid_parameter_exception('Invalid value for sortdirection parameter (value: ' . $sortdirection . '),' . 'allowed values are: ' . implode(',', $directionallowedvalues)); } break; } } $course = $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST); $coursecontext = context_course::instance($courseid, IGNORE_MISSING); if ($courseid == SITEID) { $context = context_system::instance(); } else { $context = $coursecontext; } try { self::validate_context($context); } catch (Exception $e) { $exceptionparam = new stdClass(); $exceptionparam->message = $e->getMessage(); $exceptionparam->courseid = $params['courseid']; throw new moodle_exception('errorcoursecontextnotvalid', 'webservice', '', $exceptionparam); } if ($courseid == SITEID) { require_capability('moodle/site:viewparticipants', $context); } else { require_capability('moodle/course:viewparticipants', $context); } // to overwrite this parameter, you need role:review capability if ($withcapability) { require_capability('moodle/role:review', $coursecontext); } // need accessallgroups capability if you want to overwrite this option if (!empty($groupid) && !groups_is_member($groupid)) { require_capability('moodle/site:accessallgroups', $coursecontext); } // to overwrite this option, you need course:enrolereview permission if ($onlyactive) { require_capability('moodle/course:enrolreview', $coursecontext); } list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext, $withcapability, $groupid, $onlyactive); $ctxselect = ', ' . context_helper::get_preload_record_columns_sql('ctx'); $ctxjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)"; $enrolledparams['contextlevel'] = CONTEXT_USER; $groupjoin = ''; if (empty($groupid) && groups_get_course_groupmode($course) == SEPARATEGROUPS && !has_capability('moodle/site:accessallgroups', $coursecontext)) { // Filter by groups the user can view. $usergroups = groups_get_user_groups($course->id); if (!empty($usergroups['0'])) { list($groupsql, $groupparams) = $DB->get_in_or_equal($usergroups['0'], SQL_PARAMS_NAMED); $groupjoin = "JOIN {groups_members} gm ON (u.id = gm.userid AND gm.groupid {$groupsql})"; $enrolledparams = array_merge($enrolledparams, $groupparams); } else { // User doesn't belong to any group, so he can't see any user. Return an empty array. return array(); } } $sql = "SELECT us.*\n FROM {user} us\n JOIN (\n SELECT DISTINCT u.id {$ctxselect}\n FROM {user} u {$ctxjoin} {$groupjoin}\n WHERE u.id IN ({$enrolledsql})\n ) q ON q.id = us.id\n ORDER BY {$sortby} {$sortdirection}"; $enrolledparams = array_merge($enrolledparams, $sortparams); $enrolledusers = $DB->get_recordset_sql($sql, $enrolledparams, $limitfrom, $limitnumber); $users = array(); foreach ($enrolledusers as $user) { context_helper::preload_from_record($user); if ($userdetails = user_get_user_details($user, $course, $userfields)) { $users[] = $userdetails; } } $enrolledusers->close(); return $users; }
public function find_users($search) { global $DB; list($wherecondition, $params) = $this->search_sql($search, 'u'); $fields = 'SELECT ' . $this->required_fields_sql('u'); $countfields = 'SELECT COUNT(1)'; $coursecontext = $this->accesscontext->get_course_context(false); if ($coursecontext and $coursecontext != SITEID) { $sql1 = " FROM {user} u\n JOIN {user_enrolments} ue ON (ue.userid = u.id)\n JOIN {enrol} e ON (e.id = ue.enrolid AND e.courseid = :courseid1)\n WHERE {$wherecondition}"; $params['courseid1'] = $coursecontext->instanceid; if ($this->onlyenrolled) { $sql2 = null; } else { $sql2 = " FROM {user} u\n LEFT JOIN ({user_enrolments} ue\n JOIN {enrol} e ON (e.id = ue.enrolid AND e.courseid = :courseid2)) ON (ue.userid = u.id)\n WHERE {$wherecondition}\n AND ue.id IS NULL"; $params['courseid2'] = $coursecontext->instanceid; } } else { if ($this->onlyenrolled) { // Bad luck, current user may not view only enrolled users. return array(); } $sql1 = null; $sql2 = " FROM {user} u\n WHERE {$wherecondition}"; } $params['contextid'] = $this->accesscontext->id; list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $order = ' ORDER BY ' . $sort; $result = array(); if ($search) { $groupname1 = get_string('enrolledusersmatching', 'enrol', $search); $groupname2 = get_string('potusersmatching', 'core_role', $search); } else { $groupname1 = get_string('enrolledusers', 'enrol'); $groupname2 = get_string('potusers', 'core_role'); } if ($sql1) { $enrolleduserscount = $DB->count_records_sql($countfields . $sql1, $params); if (!$this->is_validating() and $enrolleduserscount > $this->maxusersperpage) { $result[$groupname1] = array(); $toomany = $this->too_many_results($search, $enrolleduserscount); $result[implode(' - ', array_keys($toomany))] = array(); } else { $enrolledusers = $DB->get_records_sql($fields . $sql1 . $order, array_merge($params, $sortparams)); if ($enrolledusers) { $result[$groupname1] = $enrolledusers; } } if ($sql2) { $result[''] = array(); } } if ($sql2) { $otheruserscount = $DB->count_records_sql($countfields . $sql2, $params); if (!$this->is_validating() and $otheruserscount > $this->maxusersperpage) { $result[$groupname2] = array(); $toomany = $this->too_many_results($search, $otheruserscount); $result[implode(' - ', array_keys($toomany))] = array(); } else { $otherusers = $DB->get_records_sql($fields . $sql2 . $order, array_merge($params, $sortparams)); if ($otherusers) { $result[$groupname2] = $otherusers; } } } return $result; }
public function get_class_users(context $context, $withcapability = '', $groupid = 0, $userfields = 'u.*', $orderby = null, $limitfrom = 0, $limitnum = 0, $onlyactive = false) { global $DB, $CFG; $params = array($this->id); $sql = "select $userfields ,uc.* from {local_attendance} as la JOIN {local_clclasses} as cl ON cl.id=la.classid JOIN {local_user_clclasses} as uc ON uc.classid=cl.id JOIN {user} as u ON u.id=uc.userid WHERE u.deleted = 0 and uc.studentapproval=1 and uc.registrarapproval=1 and la.id=? "; if ($orderby) { $sql = "$sql ORDER BY $orderby"; } else { list($sort, $sortparams) = users_order_by_sql('u'); $sql = "$sql ORDER BY $sort"; $params = array_merge($params, $sortparams); } // print_object($params); $result = $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); }
/** * 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; $coursecontext = context_course::instance($courseid); // We want to query both the current context and parent contexts. list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($coursecontext->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx'); // 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 {$relatedctxsql})\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, $relatedctxparams); $params['importcode'] = $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(); } // We want to query both the current context and parent contexts. list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx'); // 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,\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 {$relatedctxsql} AND ra.roleid {$roleids})\n LEFT JOIN {role} r ON r.id = ra.roleid\n LEFT JOIN {groups_members} gm ON (gm.userid = u.id AND gm.groupid = :groupid)\n WHERE u.deleted = 0\n AND gm.id IS NULL\n AND {$searchcondition}"; list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $orderby = ' ORDER BY ' . $sort; $params = array_merge($searchparams, $roleparams, $enrolparams, $relatedctxparams); $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 > $this->maxusersperpage) { 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); }
/** * Gets all the users assigned this role in this context or higher * * @param int $roleid (can also be an array of ints!) * @param context $context * @param bool $parent if true, get list of users assigned in higher context too * @param string $fields fields from user (u.) , role assignment (ra) or role (r.) * @param string $sort sort from user (u.) , role assignment (ra.) or role (r.). * null => use default sort from users_order_by_sql. * @param bool $all true means all, false means limit to enrolled users * @param string $group defaults to '' * @param mixed $limitfrom defaults to '' * @param mixed $limitnum defaults to '' * @param string $extrawheretest defaults to '' * @param array $whereorsortparams any paramter values used by $sort or $extrawheretest. * @return array */ function get_role_users($roleid, context $context, $parent = false, $fields = '', $sort = null, $all = true, $group = '', $limitfrom = '', $limitnum = '', $extrawheretest = '', $whereorsortparams = array()) { global $DB; if (empty($fields)) { $allnames = get_all_user_name_fields(true, 'u'); $fields = 'u.id, u.confirmed, u.username, '. $allnames . ', ' . 'u.maildisplay, u.mailformat, u.maildigest, u.email, u.emailstop, u.city, '. 'u.country, u.picture, u.idnumber, u.department, u.institution, '. 'u.lang, u.timezone, u.lastaccess, u.mnethostid, r.name AS rolename, r.sortorder, '. 'r.shortname AS roleshortname, rn.name AS rolecoursealias'; } $parentcontexts = ''; if ($parent) { $parentcontexts = substr($context->path, 1); // kill leading slash $parentcontexts = str_replace('/', ',', $parentcontexts); if ($parentcontexts !== '') { $parentcontexts = ' OR ra.contextid IN ('.$parentcontexts.' )'; } } if ($roleid) { list($rids, $params) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'r'); $roleselect = "AND ra.roleid $rids"; } else { $params = array(); $roleselect = ''; } if ($coursecontext = $context->get_course_context(false)) { $params['coursecontext'] = $coursecontext->id; } else { $params['coursecontext'] = 0; } if ($group) { $groupjoin = "JOIN {groups_members} gm ON gm.userid = u.id"; $groupselect = " AND gm.groupid = :groupid "; $params['groupid'] = $group; } else { $groupjoin = ''; $groupselect = ''; } $params['contextid'] = $context->id; if ($extrawheretest) { $extrawheretest = ' AND ' . $extrawheretest; } if ($whereorsortparams) { $params = array_merge($params, $whereorsortparams); } if (!$sort) { list($sort, $sortparams) = users_order_by_sql('u'); $params = array_merge($params, $sortparams); } if ($all === null) { // Previously null was used to indicate that parameter was not used. $all = true; } if (!$all and $coursecontext) { // Do not use get_enrolled_sql() here for performance reasons. $ejoin = "JOIN {user_enrolments} ue ON ue.userid = u.id JOIN {enrol} e ON (e.id = ue.enrolid AND e.courseid = :ecourseid)"; $params['ecourseid'] = $coursecontext->instanceid; } else { $ejoin = ""; } $sql = "SELECT DISTINCT $fields, ra.roleid FROM {role_assignments} ra JOIN {user} u ON u.id = ra.userid JOIN {role} r ON ra.roleid = r.id $ejoin LEFT JOIN {role_names} rn ON (rn.contextid = :coursecontext AND rn.roleid = r.id) $groupjoin WHERE (ra.contextid = :contextid $parentcontexts) $roleselect $groupselect $extrawheretest ORDER BY $sort"; // join now so that we can just use fullname() later return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); }
/** * Returns and array of users + enrolment details. * * Given an array of user id's this function returns and array of user enrolments for those users * as well as enough user information to display the users name and picture for each enrolment. * * @global moodle_database $DB * @param array $userids * @return array */ public function get_users_enrolments(array $userids) { global $DB; $instances = $this->get_enrolment_instances(); $plugins = $this->get_enrolment_plugins(false); if (!empty($this->instancefilter)) { $instancesql = ' = :instanceid'; $instanceparams = array('instanceid' => $this->instancefilter); } else { list($instancesql, $instanceparams) = $DB->get_in_or_equal(array_keys($instances), SQL_PARAMS_NAMED, 'instanceid0000'); } $userfields = user_picture::fields('u'); list($idsql, $idparams) = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED, 'userid0000'); list($sort, $sortparams) = users_order_by_sql('u'); $sql = "SELECT ue.id AS ueid, ue.status, ue.enrolid, ue.userid, ue.timestart, ue.timeend, ue.modifierid, ue.timecreated, ue.timemodified, {$userfields}\n FROM {user_enrolments} ue\n LEFT JOIN {user} u ON u.id = ue.userid\n WHERE ue.enrolid {$instancesql} AND\n u.id {$idsql}\n ORDER BY {$sort}"; $rs = $DB->get_recordset_sql($sql, $idparams + $instanceparams + $sortparams); $users = array(); foreach ($rs as $ue) { $user = user_picture::unalias($ue); $ue->id = $ue->ueid; unset($ue->ueid); if (!array_key_exists($user->id, $users)) { $user->enrolments = array(); $users[$user->id] = $user; } $ue->enrolmentinstance = $instances[$ue->enrolid]; $ue->enrolmentplugin = $plugins[$ue->enrolmentinstance->enrol]; $users[$user->id]->enrolments[$ue->id] = $ue; } $rs->close(); return $users; }
/** * list current marker * * @param stdClass $row - The row of data * @return id the user->id of the marker. */ public function col_allocatedmarker(stdClass $row) { static $markers = null; static $markerlist = array(); if ($markers === null) { list($sort, $params) = users_order_by_sql(); $markers = get_users_by_capability($this->assignment->get_context(), 'mod/assign:grade', '', $sort); $markerlist[0] = get_string('choosemarker', 'assign'); foreach ($markers as $marker) { $markerlist[$marker->id] = fullname($marker); } } if (empty($markerlist)) { // TODO: add some form of notification here that no markers are available. return ''; } if ($this->is_downloading()) { if (isset($markers[$row->allocatedmarker])) { return fullname($markers[$row->allocatedmarker]); } else { return ''; } } if ($this->quickgrading && has_capability('mod/assign:manageallocations', $this->assignment->get_context()) && (empty($row->workflowstate) || $row->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_INMARKING || $row->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_NOTMARKED)) { $name = 'quickgrade_' . $row->id . '_allocatedmarker'; return html_writer::select($markerlist, $name, $row->allocatedmarker, false); } else { if (!empty($row->allocatedmarker)) { $output = ''; if ($this->quickgrading) { // Add hidden field for quickgrading page. $name = 'quickgrade_' . $row->id . '_allocatedmarker'; $output .= html_writer::empty_tag('input', array('type' => 'hidden', 'name' => $name, 'value' => $row->allocatedmarker)); } $output .= $markerlist[$row->allocatedmarker]; return $output; } } }
/** * Candidate users * @param string $search * @return array */ public function find_users($search) { global $DB; // By default wherecondition retrieves all users except the deleted, not confirmed and guest. list($wherecondition, $params) = $this->search_sql($search, 'u'); $params['cohortid'] = $this->cohortid; $fields = 'SELECT ' . $this->required_fields_sql('u'); $countfields = 'SELECT COUNT(1)'; $sql = " FROM {user} u\n JOIN {cohort_members} cm ON (cm.userid = u.id AND cm.cohortid = :cohortid)\n WHERE {$wherecondition}"; list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $order = ' ORDER BY ' . $sort; if (!$this->is_validating()) { $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params); if ($potentialmemberscount > 100) { return $this->too_many_results($search, $potentialmemberscount); } } $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams)); if (empty($availableusers)) { return array(); } if ($search) { $groupname = get_string('currentusersmatching', 'cohort', $search); } else { $groupname = get_string('currentusers', 'cohort'); } return array($groupname => $availableusers); }
/** * Send welcome email to specified user. * * @param stdClass $instance * @param stdClass $user user record * @return void */ protected function email_welcome_message($instance, $user) { global $CFG, $DB; $course = $DB->get_record('course', array('id' => $instance->courseid), '*', MUST_EXIST); $context = context_course::instance($course->id); $a = new stdClass(); $a->coursename = format_string($course->fullname, true, array('context' => $context)); $a->profileurl = "{$CFG->wwwroot}/user/view.php?id={$user->id}&course={$course->id}"; if (trim($instance->customtext1) !== '') { $message = $instance->customtext1; $key = array('{$a->coursename}', '{$a->profileurl}', '{$a->fullname}', '{$a->email}'); $value = array($a->coursename, $a->profileurl, fullname($user), $user->email); $message = str_replace($key, $value, $message); if (strpos($message, '<') === false) { // Plain text only. $messagetext = $message; $messagehtml = text_to_html($messagetext, null, false, true); } else { // This is most probably the tag/newline soup known as FORMAT_MOODLE. $messagehtml = format_text($message, FORMAT_MOODLE, array('context' => $context, 'para' => false, 'newlines' => true, 'filter' => true)); $messagetext = html_to_text($messagehtml); } } else { $messagetext = get_string('welcometocoursetext', 'enrol_self', $a); $messagehtml = text_to_html($messagetext, null, false, true); } $subject = get_string('welcometocourse', 'enrol_self', format_string($course->fullname, true, array('context' => $context))); $rusers = array(); if (!empty($CFG->coursecontact)) { $croles = explode(',', $CFG->coursecontact); list($sort, $sortparams) = users_order_by_sql('u'); // We only use the first user. $i = 0; do { $rusers = get_role_users($croles[$i], $context, true, '', 'r.sortorder ASC, ' . $sort, null, '', '', '', '', $sortparams); $i++; } while (empty($rusers) && !empty($croles[$i])); } if ($rusers) { $contact = reset($rusers); } else { $contact = core_user::get_support_user(); } // Directly emailing welcome message rather than using messaging. email_to_user($user, $contact, $subject, $messagetext, $messagehtml); }
/** * Lists all roles that have the ability to backup user data, as well as users * @param bool $detailed * @return object result */ function report_security_check_riskbackup($detailed = false) { global $CFG, $DB; $result = new stdClass(); $result->issue = 'report_security_check_riskbackup'; $result->name = get_string('check_riskbackup_name', 'report_security'); $result->info = null; $result->details = null; $result->status = null; $result->link = null; $syscontext = context_system::instance(); $params = array('capability' => 'moodle/backup:userinfo', 'permission' => CAP_ALLOW, 'contextid' => $syscontext->id); $sql = "SELECT DISTINCT r.id, r.name, r.shortname, r.sortorder, r.archetype\n FROM {role} r\n JOIN {role_capabilities} rc ON rc.roleid = r.id\n WHERE rc.capability = :capability\n AND rc.contextid = :contextid\n AND rc.permission = :permission"; $systemroles = $DB->get_records_sql($sql, $params); $params = array('capability' => 'moodle/backup:userinfo', 'permission' => CAP_ALLOW, 'contextid' => $syscontext->id); $sql = "SELECT DISTINCT r.id, r.name, r.shortname, r.sortorder, r.archetype, rc.contextid\n FROM {role} r\n JOIN {role_capabilities} rc ON rc.roleid = r.id\n WHERE rc.capability = :capability\n AND rc.contextid <> :contextid\n AND rc.permission = :permission"; $overriddenroles = $DB->get_records_sql($sql, $params); // list of users that are able to backup personal info // note: "sc" is context where is role assigned, // "c" is context where is role overridden or system context if in role definition $params = array('capability' => 'moodle/backup:userinfo', 'permission' => CAP_ALLOW, 'context1' => CONTEXT_COURSE, 'context2' => CONTEXT_COURSE); $sqluserinfo = "\n FROM (SELECT rcx.*\n FROM {role_capabilities} rcx\n WHERE rcx.permission = :permission AND rcx.capability = :capability) rc,\n {context} c,\n {context} sc,\n {role_assignments} ra,\n {user} u\n WHERE c.id = rc.contextid\n AND (sc.path = c.path OR sc.path LIKE " . $DB->sql_concat('c.path', "'/%'") . " OR c.path LIKE " . $DB->sql_concat('sc.path', "'/%'") . ")\n AND u.id = ra.userid AND u.deleted = 0\n AND ra.contextid = sc.id AND ra.roleid = rc.roleid\n AND sc.contextlevel <= :context1 AND c.contextlevel <= :context2"; $usercount = $DB->count_records_sql("SELECT COUNT('x') FROM (SELECT DISTINCT u.id {$sqluserinfo}) userinfo", $params); $systemrolecount = empty($systemroles) ? 0 : count($systemroles); $overriddenrolecount = empty($overriddenroles) ? 0 : count($overriddenroles); $result->status = REPORT_SECURITY_WARNING; // there is always at least one admin $a = (object) array('rolecount' => $systemrolecount, 'overridecount' => $overriddenrolecount, 'usercount' => $usercount); $result->info = get_string('check_riskbackup_warning', 'report_security', $a); if ($detailed) { $result->details = ''; // Will be added to later // Make a list of roles if ($systemroles) { $links = array(); foreach ($systemroles as $role) { $role->name = role_get_name($role); $role->url = "{$CFG->wwwroot}/{$CFG->admin}/roles/manage.php?action=edit&roleid={$role->id}"; $links[] = '<li>' . get_string('check_riskbackup_editrole', 'report_security', $role) . '</li>'; } $links = '<ul>' . implode($links) . '</ul>'; $result->details .= get_string('check_riskbackup_details_systemroles', 'report_security', $links); } // Make a list of overrides to roles $rolelinks2 = array(); if ($overriddenroles) { $links = array(); foreach ($overriddenroles as $role) { $role->name = $role->localname; $context = context::instance_by_id($role->contextid); $role->name = role_get_name($role, $context, ROLENAME_BOTH); $role->contextname = $context->get_context_name(); $role->url = "{$CFG->wwwroot}/{$CFG->admin}/roles/override.php?contextid={$role->contextid}&roleid={$role->id}"; $links[] = '<li>' . get_string('check_riskbackup_editoverride', 'report_security', $role) . '</li>'; } $links = '<ul>' . implode('', $links) . '</ul>'; $result->details .= get_string('check_riskbackup_details_overriddenroles', 'report_security', $links); } // Get a list of affected users as well $users = array(); list($sort, $sortparams) = users_order_by_sql('u'); $rs = $DB->get_recordset_sql("SELECT DISTINCT u.id, u.firstname, u.lastname, u.picture, u.imagealt, u.email, ra.contextid, ra.roleid\n {$sqluserinfo} ORDER BY {$sort}", array_merge($params, $sortparams)); foreach ($rs as $user) { $context = context::instance_by_id($user->contextid); $url = "{$CFG->wwwroot}/{$CFG->admin}/roles/assign.php?contextid={$user->contextid}&roleid={$user->roleid}"; $a = (object) array('fullname' => fullname($user), 'url' => $url, 'email' => $user->email, 'contextname' => $context->get_context_name()); $users[] = '<li>' . get_string('check_riskbackup_unassign', 'report_security', $a) . '</li>'; } if (!empty($users)) { $users = '<ul>' . implode('', $users) . '</ul>'; $result->details .= get_string('check_riskbackup_details_users', 'report_security', $users); } } return $result; }