3 // This file is part of Moodle - http://moodle.org/
5 // Moodle is free software: you can redistribute it and/or modify
6 // it under the terms of the GNU General Public License as published by
7 // the Free Software Foundation, either version 3 of the License, or
8 // (at your option) any later version.
10 // Moodle is distributed in the hope that it will be useful,
11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 // GNU General Public License for more details.
15 // You should have received a copy of the GNU General Public License
16 // along with Moodle. If not, see <http://www.gnu.org/licenses/>.
19 * Library of functions for database manipulation.
21 * Other main libraries:
22 * - weblib.php - functions that produce web output
23 * - moodlelib.php - general-purpose Moodle functions
27 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com}
28 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
31 defined('MOODLE_INTERNAL') ||
die();
34 * The maximum courses in a category
35 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer!
37 define('MAX_COURSES_IN_CATEGORY', 10000);
40 * The maximum number of course categories
41 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer!
43 define('MAX_COURSE_CATEGORIES', 10000);
46 * Number of seconds to wait before updating lastaccess information in DB.
48 define('LASTACCESS_UPDATE_SECS', 60);
51 * Returns $user object of the main admin user
53 * @static stdClass $mainadmin
54 * @return stdClass {@link $USER} record from DB, false if not found
56 function get_admin() {
59 static $mainadmin = null;
61 if (isset($mainadmin)) {
62 return clone($mainadmin);
65 if (empty($CFG->siteadmins
)) { // Should not happen on an ordinary site
69 foreach (explode(',', $CFG->siteadmins
) as $id) {
70 if ($user = $DB->get_record('user', array('id'=>$id, 'deleted'=>0))) {
77 return clone($mainadmin);
79 // this should not happen
85 * Returns list of all admins, using 1 DB query
89 function get_admins() {
92 if (empty($CFG->siteadmins
)) { // Should not happen on an ordinary site
98 WHERE u.deleted = 0 AND u.id IN ($CFG->siteadmins)";
100 return $DB->get_records_sql($sql);
104 * Search through course users
106 * If $coursid specifies the site course then this function searches
107 * through all undeleted and confirmed users
111 * @uses SQL_PARAMS_NAMED
112 * @uses CONTEXT_COURSE
113 * @param int $courseid The course in question.
114 * @param int $groupid The group in question.
115 * @param string $searchtext The string to search for
116 * @param string $sort A field to sort by
117 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
120 function search_users($courseid, $groupid, $searchtext, $sort='', array $exceptions=null) {
123 $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
125 if (!empty($exceptions)) {
126 list($exceptions, $params) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED
, 'ex', false);
127 $except = "AND u.id $exceptions";
134 $order = "ORDER BY $sort";
139 $select = "u.deleted = 0 AND u.confirmed = 1 AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('u.email', ':search2', false).")";
140 $params['search1'] = "%$searchtext%";
141 $params['search2'] = "%$searchtext%";
143 if (!$courseid or $courseid == SITEID
) {
144 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
149 return $DB->get_records_sql($sql, $params);
153 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
155 JOIN {groups_members} gm ON gm.userid = u.id
156 WHERE $select AND gm.groupid = :groupid
159 $params['groupid'] = $groupid;
160 return $DB->get_records_sql($sql, $params);
163 $context = get_context_instance(CONTEXT_COURSE
, $courseid);
164 $contextlists = get_related_contexts_string($context);
166 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
168 JOIN {role_assignments} ra ON ra.userid = u.id
169 WHERE $select AND ra.contextid $contextlists
172 return $DB->get_records_sql($sql, $params);
178 * Returns a subset of users
181 * @uses DEBUG_DEVELOPER
182 * @uses SQL_PARAMS_NAMED
183 * @param bool $get If false then only a count of the records is returned
184 * @param string $search A simple string to search for
185 * @param bool $confirmed A switch to allow/disallow unconfirmed users
186 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
187 * @param string $sort A SQL snippet for the sorting criteria to use
188 * @param string $firstinitial Users whose first name starts with $firstinitial
189 * @param string $lastinitial Users whose last name starts with $lastinitial
190 * @param string $page The page or records to return
191 * @param string $recordsperpage The number of records to return per page
192 * @param string $fields A comma separated list of fields to be returned from the chosen table.
193 * @return array|int|bool {@link $USER} records unless get is false in which case the integer count of the records found is returned.
194 * False is returned if an error is encountered.
196 function get_users($get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC',
197 $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null) {
200 if ($get && !$recordsperpage) {
201 debugging('Call to get_users with $get = true no $recordsperpage limit. ' .
202 'On large installations, this will probably cause an out of memory error. ' .
203 'Please think again and change your code so that it does not try to ' .
204 'load so much data into memory.', DEBUG_DEVELOPER
);
207 $fullname = $DB->sql_fullname();
209 $select = " id <> :guestid AND deleted = 0";
210 $params = array('guestid'=>$CFG->siteguest
);
212 if (!empty($search)){
213 $search = trim($search);
214 $select .= " AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('email', ':search2', false)." OR username = :search3)";
215 $params['search1'] = "%$search%";
216 $params['search2'] = "%$search%";
217 $params['search3'] = "$search";
221 $select .= " AND confirmed = 1";
225 list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED
, 'ex', false);
226 $params = $params +
$eparams;
227 $except = " AND id $exceptions";
231 $select .= " AND ".$DB->sql_like('firstname', ':fni', false, false);
232 $params['fni'] = "$firstinitial%";
235 $select .= " AND ".$DB->sql_like('lastname', ':lni', false, false);
236 $params['lni'] = "$lastinitial%";
240 $select .= " AND $extraselect";
241 $params = $params +
(array)$extraparams;
245 return $DB->get_records_select('user', $select, $params, $sort, $fields, $page, $recordsperpage);
247 return $DB->count_records_select('user', $select, $params);
253 * @todo Finish documenting this function
255 * @param string $sort An SQL field to sort by
256 * @param string $dir The sort direction ASC|DESC
257 * @param int $page The page or records to return
258 * @param int $recordsperpage The number of records to return per page
259 * @param string $search A simple string to search for
260 * @param string $firstinitial Users whose first name starts with $firstinitial
261 * @param string $lastinitial Users whose last name starts with $lastinitial
262 * @param string $extraselect An additional SQL select statement to append to the query
263 * @param array $extraparams Additional parameters to use for the above $extraselect
264 * @param object $extracontext If specified, will include user 'extra fields'
265 * as appropriate for current user and given context
266 * @return array Array of {@link $USER} records
268 function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,
269 $search='', $firstinitial='', $lastinitial='', $extraselect='',
270 array $extraparams=null, $extracontext = null) {
273 $fullname = $DB->sql_fullname();
275 $select = "deleted <> 1";
278 if (!empty($search)) {
279 $search = trim($search);
280 $select .= " AND (". $DB->sql_like($fullname, ':search1', false, false).
281 " OR ". $DB->sql_like('email', ':search2', false, false).
282 " OR username = :search3)";
283 $params['search1'] = "%$search%";
284 $params['search2'] = "%$search%";
285 $params['search3'] = "$search";
289 $select .= " AND ". $DB->sql_like('firstname', ':fni', false, false);
290 $params['fni'] = "$firstinitial%";
293 $select .= " AND ". $DB->sql_like('lastname', ':lni', false, false);
294 $params['lni'] = "$lastinitial%";
298 $select .= " AND $extraselect";
299 $params = $params +
(array)$extraparams;
303 $sort = " ORDER BY $sort $dir";
306 // If a context is specified, get extra user fields that the current user
307 // is supposed to see.
310 $extrafields = get_extra_user_fields_sql($extracontext, '', '',
311 array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country',
312 'lastaccess', 'confirmed', 'mnethostid'));
315 // warning: will return UNCONFIRMED USERS
316 return $DB->get_records_sql("SELECT id, username, email, firstname, lastname, city, country,
317 lastaccess, confirmed, mnethostid, suspended $extrafields
320 $sort", $params, $page, $recordsperpage);
326 * Full list of users that have confirmed their accounts.
329 * @return array of unconfirmed users
331 function get_users_confirmed() {
333 return $DB->get_records_sql("SELECT *
335 WHERE confirmed = 1 AND deleted = 0 AND id <> ?", array($CFG->siteguest
));
339 /// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
343 * Returns $course object of the top-level site.
345 * @return object A {@link $COURSE} object for the site, exception if not found
347 function get_site() {
350 if (!empty($SITE->id
)) { // We already have a global to use, so return that
354 if ($course = $DB->get_record('course', array('category'=>0))) {
357 // course table exists, but the site is not there,
358 // unfortunately there is no automatic way to recover
359 throw new moodle_exception('nosite', 'error');
364 * Returns list of courses, for whole site, or category
366 * Returns list of courses, for whole site, or category
367 * Important: Using c.* for fields is extremely expensive because
368 * we are using distinct. You almost _NEVER_ need all the fields
369 * in such a large SELECT
374 * @uses CONTEXT_COURSE
375 * @param string|int $categoryid Either a category id or 'all' for everything
376 * @param string $sort A field and direction to sort by
377 * @param string $fields The additional fields to return
378 * @return array Array of courses
380 function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
382 global $USER, $CFG, $DB;
386 if ($categoryid !== "all" && is_numeric($categoryid)) {
387 $categoryselect = "WHERE c.category = :catid";
388 $params['catid'] = $categoryid;
390 $categoryselect = "";
396 $sortstatement = "ORDER BY $sort";
399 $visiblecourses = array();
401 list($ccselect, $ccjoin) = context_instance_preload_sql('c.id', CONTEXT_COURSE
, 'ctx');
403 $sql = "SELECT $fields $ccselect
409 // pull out all course matching the cat
410 if ($courses = $DB->get_records_sql($sql, $params)) {
412 // loop throught them
413 foreach ($courses as $course) {
414 context_instance_preload($course);
415 if (isset($course->visible
) && $course->visible
<= 0) {
416 // for hidden courses, require visibility check
417 if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE
, $course->id
))) {
418 $visiblecourses [$course->id
] = $course;
421 $visiblecourses [$course->id
] = $course;
425 return $visiblecourses;
430 * Returns list of courses, for whole site, or category
432 * Similar to get_courses, but allows paging
433 * Important: Using c.* for fields is extremely expensive because
434 * we are using distinct. You almost _NEVER_ need all the fields
435 * in such a large SELECT
440 * @uses CONTEXT_COURSE
441 * @param string|int $categoryid Either a category id or 'all' for everything
442 * @param string $sort A field and direction to sort by
443 * @param string $fields The additional fields to return
444 * @param int $totalcount Reference for the number of courses
445 * @param string $limitfrom The course to start from
446 * @param string $limitnum The number of courses to limit to
447 * @return array Array of courses
449 function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
450 &$totalcount, $limitfrom="", $limitnum="") {
451 global $USER, $CFG, $DB;
455 $categoryselect = "";
456 if ($categoryid != "all" && is_numeric($categoryid)) {
457 $categoryselect = "WHERE c.category = :catid";
458 $params['catid'] = $categoryid;
460 $categoryselect = "";
463 list($ccselect, $ccjoin) = context_instance_preload_sql('c.id', CONTEXT_COURSE
, 'ctx');
469 $visiblecourses = array();
471 $sql = "SELECT $fields $ccselect
477 // pull out all course matching the cat
478 $rs = $DB->get_recordset_sql($sql, $params);
479 // iteration will have to be done inside loop to keep track of the limitfrom and limitnum
480 foreach($rs as $course) {
481 context_instance_preload($course);
482 if ($course->visible
<= 0) {
483 // for hidden courses, require visibility check
484 if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE
, $course->id
))) {
486 if ($totalcount > $limitfrom && (!$limitnum or count($visiblecourses) < $limitnum)) {
487 $visiblecourses [$course->id
] = $course;
492 if ($totalcount > $limitfrom && (!$limitnum or count($visiblecourses) < $limitnum)) {
493 $visiblecourses [$course->id
] = $course;
498 return $visiblecourses;
502 * Retrieve course records with the course managers and other related records
503 * that we need for print_course(). This allows print_courses() to do its job
504 * in a constant number of DB queries, regardless of the number of courses,
505 * role assignments, etc.
507 * The returned array is indexed on c.id, and each course will have
508 * - $course->managers - array containing RA objects that include a $user obj
509 * with the minimal fields needed for fullname()
514 * @uses CONTEXT_COURSE
515 * @uses CONTEXT_SYSTEM
516 * @uses CONTEXT_COURSECAT
518 * @param int|string $categoryid Either the categoryid for the courses or 'all'
519 * @param string $sort A SQL sort field and direction
520 * @param array $fields An array of additional fields to fetch
523 function get_courses_wmanagers($categoryid=0, $sort="c.sortorder ASC", $fields=array()) {
527 * - Grab the courses JOINed w/context
529 * - Grab the interesting course-manager RAs
530 * JOINed with a base user obj and add them to each course
532 * So as to do all the work in 2 DB queries. The RA+user JOIN
533 * ends up being pretty expensive if it happens over _all_
534 * courses on a large site. (Are we surprised!?)
536 * So this should _never_ get called with 'all' on a large site.
539 global $USER, $CFG, $DB;
542 $allcats = false; // bool flag
543 if ($categoryid === 'all') {
544 $categoryclause = '';
546 } elseif (is_numeric($categoryid)) {
547 $categoryclause = "c.category = :catid";
548 $params['catid'] = $categoryid;
550 debugging("Could not recognise categoryid = $categoryid");
551 $categoryclause = '';
554 $basefields = array('id', 'category', 'sortorder',
555 'shortname', 'fullname', 'idnumber',
556 'startdate', 'visible',
557 'newsitems', 'groupmode', 'groupmodeforce');
559 if (!is_null($fields) && is_string($fields)) {
560 if (empty($fields)) {
561 $fields = $basefields;
563 // turn the fields from a string to an array that
564 // get_user_courses_bycap() will like...
565 $fields = explode(',',$fields);
566 $fields = array_map('trim', $fields);
567 $fields = array_unique(array_merge($basefields, $fields));
569 } elseif (is_array($fields)) {
570 $fields = array_merge($basefields,$fields);
572 $coursefields = 'c.' .join(',c.', $fields);
577 $sortstatement = "ORDER BY $sort";
580 $where = 'WHERE c.id != ' . SITEID
;
581 if ($categoryclause !== ''){
582 $where = "$where AND $categoryclause";
585 // pull out all courses matching the cat
586 list($ccselect, $ccjoin) = context_instance_preload_sql('c.id', CONTEXT_COURSE
, 'ctx');
587 $sql = "SELECT $coursefields $ccselect
595 if ($courses = $DB->get_records_sql($sql, $params)) {
596 // loop on courses materialising
597 // the context, and prepping data to fetch the
598 // managers efficiently later...
599 foreach ($courses as $k => $course) {
600 context_instance_preload($course);
601 $coursecontext = get_context_instance(CONTEXT_COURSE
, $course->id
);
602 $courses[$k] = $course;
603 $courses[$k]->managers
= array();
604 if ($allcats === false) {
605 // single cat, so take just the first one...
606 if ($catpath === NULL) {
607 $catpath = preg_replace(':/\d+$:', '', $coursecontext->path
);
610 // chop off the contextid of the course itself
611 // like dirname() does...
612 $catpaths[] = preg_replace(':/\d+$:', '', $coursecontext->path
);
616 return array(); // no courses!
619 $CFG->coursecontact
= trim($CFG->coursecontact
);
620 if (empty($CFG->coursecontact
)) {
624 $managerroles = explode(',', $CFG->coursecontact
);
626 if (count($managerroles)) {
627 if ($allcats === true) {
628 $catpaths = array_unique($catpaths);
630 foreach ($catpaths as $cpath) {
631 $ctxids = array_merge($ctxids, explode('/',substr($cpath,1)));
633 $ctxids = array_unique($ctxids);
634 $catctxids = implode( ',' , $ctxids);
638 // take the ctx path from the first course
639 // as all categories will be the same...
640 $catpath = substr($catpath,1);
641 $catpath = preg_replace(':/\d+$:','',$catpath);
642 $catctxids = str_replace('/',',',$catpath);
644 if ($categoryclause !== '') {
645 $categoryclause = "AND $categoryclause";
648 * Note: Here we use a LEFT OUTER JOIN that can
649 * "optionally" match to avoid passing a ton of context
650 * ids in an IN() clause. Perhaps a subselect is faster.
652 * In any case, this SQL is not-so-nice over large sets of
653 * courses with no $categoryclause.
656 $sql = "SELECT ctx.path, ctx.instanceid, ctx.contextlevel,
657 r.id AS roleid, r.name as rolename,
658 u.id AS userid, u.firstname, u.lastname
659 FROM {role_assignments} ra
660 JOIN {context} ctx ON ra.contextid = ctx.id
661 JOIN {user} u ON ra.userid = u.id
662 JOIN {role} r ON ra.roleid = r.id
663 LEFT OUTER JOIN {course} c
664 ON (ctx.instanceid=c.id AND ctx.contextlevel=".CONTEXT_COURSE
.")
665 WHERE ( c.id IS NOT NULL";
666 // under certain conditions, $catctxids is NULL
667 if($catctxids == NULL){
670 $sql .= " OR ra.contextid IN ($catctxids) )";
673 $sql .= "AND ra.roleid IN ({$CFG->coursecontact})
675 ORDER BY r.sortorder ASC, ctx.contextlevel ASC, ra.sortorder ASC";
676 $rs = $DB->get_recordset_sql($sql, $params);
678 // This loop is fairly stupid as it stands - might get better
679 // results doing an initial pass clustering RAs by path.
680 foreach($rs as $ra) {
681 $user = new stdClass
;
682 $user->id
= $ra->userid
; unset($ra->userid
);
683 $user->firstname
= $ra->firstname
; unset($ra->firstname
);
684 $user->lastname
= $ra->lastname
; unset($ra->lastname
);
686 if ($ra->contextlevel
== CONTEXT_SYSTEM
) {
687 foreach ($courses as $k => $course) {
688 $courses[$k]->managers
[] = $ra;
690 } else if ($ra->contextlevel
== CONTEXT_COURSECAT
) {
691 if ($allcats === false) {
693 foreach ($courses as $k => $course) {
694 $courses[$k]->managers
[] = $ra;
697 foreach ($courses as $k => $course) {
698 $coursecontext = get_context_instance(CONTEXT_COURSE
, $course->id
);
699 // Note that strpos() returns 0 as "matched at pos 0"
700 if (strpos($coursecontext->path
, $ra->path
.'/') === 0) {
701 // Only add it to subpaths
702 $courses[$k]->managers
[] = $ra;
706 } else { // course-level
707 if (!array_key_exists($ra->instanceid
, $courses)) {
708 //this course is not in a list, probably a frontpage course
711 $courses[$ra->instanceid
]->managers
[] = $ra;
721 * A list of courses that match a search
725 * @param array $searchterms An array of search criteria
726 * @param string $sort A field and direction to sort by
727 * @param int $page The page number to get
728 * @param int $recordsperpage The number of records per page
729 * @param int $totalcount Passed in by reference.
730 * @return object {@link $COURSE} records
732 function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
735 if ($DB->sql_regex_supported()) {
736 $REGEXP = $DB->sql_regex(true);
737 $NOTREGEXP = $DB->sql_regex(false);
740 $searchcond = array();
744 // Thanks Oracle for your non-ansi concat and type limits in coalesce. MDL-29912
745 if ($DB->get_dbfamily() == 'oracle') {
746 $concat = $DB->sql_concat('c.summary', "' '", 'c.fullname', "' '", 'c.idnumber', "' '", 'c.shortname');
748 $concat = $DB->sql_concat("COALESCE(c.summary, '". $DB->sql_empty() ."')", "' '", 'c.fullname', "' '", 'c.idnumber', "' '", 'c.shortname');
751 foreach ($searchterms as $searchterm) {
754 $NOT = false; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle
755 /// will use it to simulate the "-" operator with LIKE clause
757 /// Under Oracle and MSSQL, trim the + and - operators and perform
758 /// simpler LIKE (or NOT LIKE) queries
759 if (!$DB->sql_regex_supported()) {
760 if (substr($searchterm, 0, 1) == '-') {
763 $searchterm = trim($searchterm, '+-');
766 // TODO: +- may not work for non latin languages
768 if (substr($searchterm,0,1) == '+') {
769 $searchterm = trim($searchterm, '+-');
770 $searchterm = preg_quote($searchterm, '|');
771 $searchcond[] = "$concat $REGEXP :ss$i";
772 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
774 } else if (substr($searchterm,0,1) == "-") {
775 $searchterm = trim($searchterm, '+-');
776 $searchterm = preg_quote($searchterm, '|');
777 $searchcond[] = "$concat $NOTREGEXP :ss$i";
778 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
781 $searchcond[] = $DB->sql_like($concat,":ss$i", false, true, $NOT);
782 $params['ss'.$i] = "%$searchterm%";
786 if (empty($searchcond)) {
791 $searchcond = implode(" AND ", $searchcond);
794 $c = 0; // counts how many visible courses we've seen
797 $limitfrom = $page * $recordsperpage;
798 $limitto = $limitfrom +
$recordsperpage;
800 list($ccselect, $ccjoin) = context_instance_preload_sql('c.id', CONTEXT_COURSE
, 'ctx');
801 $sql = "SELECT c.* $ccselect
804 WHERE $searchcond AND c.id <> ".SITEID
."
807 $rs = $DB->get_recordset_sql($sql, $params);
808 foreach($rs as $course) {
809 context_instance_preload($course);
810 $coursecontext = get_context_instance(CONTEXT_COURSE
, $course->id
);
811 if ($course->visible ||
has_capability('moodle/course:viewhiddencourses', $coursecontext)) {
812 // Don't exit this loop till the end
813 // we need to count all the visible courses
814 // to update $totalcount
815 if ($c >= $limitfrom && $c < $limitto) {
816 $courses[$course->id
] = $course;
823 // our caller expects 2 bits of data - our return
824 // array, and an updated $totalcount
831 * Returns a sorted list of categories. Each category object has a context
832 * property that is a context object.
834 * When asking for $parent='none' it will return all the categories, regardless
835 * of depth. Wheen asking for a specific parent, the default is to return
836 * a "shallow" resultset. Pass false to $shallow and it will return all
837 * the child categories as well.
840 * @uses CONTEXT_COURSECAT
841 * @param string $parent The parent category if any
842 * @param string $sort the sortorder
843 * @param bool $shallow - set to false to get the children too
844 * @return array of categories
846 function get_categories($parent='none', $sort=NULL, $shallow=true) {
849 if ($sort === NULL) {
850 $sort = 'ORDER BY cc.sortorder ASC';
851 } elseif ($sort ==='') {
854 $sort = "ORDER BY $sort";
857 list($ccselect, $ccjoin) = context_instance_preload_sql('cc.id', CONTEXT_COURSECAT
, 'ctx');
859 if ($parent === 'none') {
860 $sql = "SELECT cc.* $ccselect
861 FROM {course_categories} cc
866 } elseif ($shallow) {
867 $sql = "SELECT cc.* $ccselect
868 FROM {course_categories} cc
872 $params = array($parent);
875 $sql = "SELECT cc.* $ccselect
876 FROM {course_categories} cc
878 JOIN {course_categories} ccp
879 ON ((cc.parent = ccp.id) OR (cc.path LIKE ".$DB->sql_concat('ccp.path',"'/%'")."))
882 $params = array($parent);
884 $categories = array();
886 $rs = $DB->get_recordset_sql($sql, $params);
887 foreach($rs as $cat) {
888 context_instance_preload($cat);
889 $catcontext = get_context_instance(CONTEXT_COURSECAT
, $cat->id
);
890 if ($cat->visible ||
has_capability('moodle/category:viewhiddencategories', $catcontext)) {
891 $categories[$cat->id
] = $cat;
900 * Returns an array of category ids of all the subcategories for a given
904 * @param int $catid - The id of the category whose subcategories we want to find.
905 * @return array of category ids.
907 function get_all_subcategories($catid) {
912 if ($categories = $DB->get_records('course_categories', array('parent'=>$catid))) {
913 foreach ($categories as $cat) {
914 array_push($subcats, $cat->id
);
915 $subcats = array_merge($subcats, get_all_subcategories($cat->id
));
922 * Return specified category, default if given does not exist
925 * @uses MAX_COURSES_IN_CATEGORY
926 * @uses CONTEXT_COURSECAT
928 * @param int $catid course category id
929 * @return object caregory
931 function get_course_category($catid=0) {
936 if (!empty($catid)) {
937 $category = $DB->get_record('course_categories', array('id'=>$catid));
941 // the first category is considered default for now
942 if ($category = $DB->get_records('course_categories', null, 'sortorder', '*', 0, 1)) {
943 $category = reset($category);
946 $cat = new stdClass();
947 $cat->name
= get_string('miscellaneous');
949 $cat->sortorder
= MAX_COURSES_IN_CATEGORY
;
950 $cat->timemodified
= time();
951 $catid = $DB->insert_record('course_categories', $cat);
952 // make sure category context exists
953 get_context_instance(CONTEXT_COURSECAT
, $catid);
954 mark_context_dirty('/'.SYSCONTEXTID
);
955 fix_course_sortorder(); // Required to build course_categories.depth and .path.
956 $category = $DB->get_record('course_categories', array('id'=>$catid));
964 * Fixes course category and course sortorder, also verifies category and course parents and paths.
965 * (circular references are not fixed)
969 * @uses MAX_COURSES_IN_CATEGORY
970 * @uses MAX_COURSE_CATEGORIES
972 * @uses CONTEXT_COURSE
975 function fix_course_sortorder() {
978 //WARNING: this is PHP5 only code!
980 if ($unsorted = $DB->get_records('course_categories', array('sortorder'=>0))) {
981 //move all categories that are not sorted yet to the end
982 $DB->set_field('course_categories', 'sortorder', MAX_COURSES_IN_CATEGORY
*MAX_COURSE_CATEGORIES
, array('sortorder'=>0));
985 $allcats = $DB->get_records('course_categories', null, 'sortorder, id', 'id, sortorder, parent, depth, path');
987 $brokencats = array();
988 foreach ($allcats as $cat) {
989 $sortorder = (int)$cat->sortorder
;
991 while(isset($topcats[$sortorder])) {
994 $topcats[$sortorder] = $cat;
997 if (!isset($allcats[$cat->parent
])) {
998 $brokencats[] = $cat;
1001 if (!isset($allcats[$cat->parent
]->children
)) {
1002 $allcats[$cat->parent
]->children
= array();
1004 while(isset($allcats[$cat->parent
]->children
[$sortorder])) {
1007 $allcats[$cat->parent
]->children
[$sortorder] = $cat;
1011 // add broken cats to category tree
1013 $defaultcat = reset($topcats);
1014 foreach ($brokencats as $cat) {
1019 // now walk recursively the tree and fix any problems found
1021 $fixcontexts = array();
1022 _fix_course_cats($topcats, $sortorder, 0, 0, '', $fixcontexts);
1024 // detect if there are "multiple" frontpage courses and fix them if needed
1025 $frontcourses = $DB->get_records('course', array('category'=>0), 'id');
1026 if (count($frontcourses) > 1) {
1027 if (isset($frontcourses[SITEID
])) {
1028 $frontcourse = $frontcourses[SITEID
];
1029 unset($frontcourses[SITEID
]);
1031 $frontcourse = array_shift($frontcourses);
1033 $defaultcat = reset($topcats);
1034 foreach ($frontcourses as $course) {
1035 $DB->set_field('course', 'category', $defaultcat->id
, array('id'=>$course->id
));
1036 $context = get_context_instance(CONTEXT_COURSE
, $course->id
);
1037 $fixcontexts[$context->id
] = $context;
1039 unset($frontcourses);
1041 $frontcourse = reset($frontcourses);
1044 // now fix the paths and depths in context table if needed
1046 foreach ($fixcontexts as $fixcontext) {
1047 $fixcontext->reset_paths(false);
1049 context_helper
::build_all_paths(false);
1050 unset($fixcontexts);
1056 unset($fixcontexts);
1058 // fix frontpage course sortorder
1059 if ($frontcourse->sortorder
!= 1) {
1060 $DB->set_field('course', 'sortorder', 1, array('id'=>$frontcourse->id
));
1063 // now fix the course counts in category records if needed
1064 $sql = "SELECT cc.id, cc.coursecount, COUNT(c.id) AS newcount
1065 FROM {course_categories} cc
1066 LEFT JOIN {course} c ON c.category = cc.id
1067 GROUP BY cc.id, cc.coursecount
1068 HAVING cc.coursecount <> COUNT(c.id)";
1070 if ($updatecounts = $DB->get_records_sql($sql)) {
1071 // categories with more courses than MAX_COURSES_IN_CATEGORY
1072 $categories = array();
1073 foreach ($updatecounts as $cat) {
1074 $cat->coursecount
= $cat->newcount
;
1075 if ($cat->coursecount
>= MAX_COURSES_IN_CATEGORY
) {
1076 $categories[] = $cat->id
;
1078 unset($cat->newcount
);
1079 $DB->update_record_raw('course_categories', $cat, true);
1081 if (!empty($categories)) {
1082 $str = implode(', ', $categories);
1083 debugging("The number of courses (category id: $str) has reached MAX_COURSES_IN_CATEGORY (" . MAX_COURSES_IN_CATEGORY
. "), it will cause a sorting performance issue, please increase the value of MAX_COURSES_IN_CATEGORY in lib/datalib.php file. See tracker issue: MDL-25669", DEBUG_DEVELOPER
);
1087 // now make sure that sortorders in course table are withing the category sortorder ranges
1088 $sql = "SELECT DISTINCT cc.id, cc.sortorder
1089 FROM {course_categories} cc
1090 JOIN {course} c ON c.category = cc.id
1091 WHERE c.sortorder < cc.sortorder OR c.sortorder > cc.sortorder + ".MAX_COURSES_IN_CATEGORY
;
1093 if ($fixcategories = $DB->get_records_sql($sql)) {
1094 //fix the course sortorder ranges
1095 foreach ($fixcategories as $cat) {
1096 $sql = "UPDATE {course}
1097 SET sortorder = ".$DB->sql_modulo('sortorder', MAX_COURSES_IN_CATEGORY
)." + ?
1098 WHERE category = ?";
1099 $DB->execute($sql, array($cat->sortorder
, $cat->id
));
1102 unset($fixcategories);
1104 // categories having courses with sortorder duplicates or having gaps in sortorder
1105 $sql = "SELECT DISTINCT c1.category AS id , cc.sortorder
1107 JOIN {course} c2 ON c1.sortorder = c2.sortorder
1108 JOIN {course_categories} cc ON (c1.category = cc.id)
1109 WHERE c1.id <> c2.id";
1110 $fixcategories = $DB->get_records_sql($sql);
1112 $sql = "SELECT cc.id, cc.sortorder, cc.coursecount, MAX(c.sortorder) AS maxsort, MIN(c.sortorder) AS minsort
1113 FROM {course_categories} cc
1114 JOIN {course} c ON c.category = cc.id
1115 GROUP BY cc.id, cc.sortorder, cc.coursecount
1116 HAVING (MAX(c.sortorder) <> cc.sortorder + cc.coursecount) OR (MIN(c.sortorder) <> cc.sortorder + 1)";
1117 $gapcategories = $DB->get_records_sql($sql);
1119 foreach ($gapcategories as $cat) {
1120 if (isset($fixcategories[$cat->id
])) {
1121 // duplicates detected already
1123 } else if ($cat->minsort
== $cat->sortorder
and $cat->maxsort
== $cat->sortorder +
$cat->coursecount
- 1) {
1124 // easy - new course inserted with sortorder 0, the rest is ok
1125 $sql = "UPDATE {course}
1126 SET sortorder = sortorder + 1
1127 WHERE category = ?";
1128 $DB->execute($sql, array($cat->id
));
1131 // it needs full resorting
1132 $fixcategories[$cat->id
] = $cat;
1135 unset($gapcategories);
1137 // fix course sortorders in problematic categories only
1138 foreach ($fixcategories as $cat) {
1140 $courses = $DB->get_records('course', array('category'=>$cat->id
), 'sortorder ASC, id DESC', 'id, sortorder');
1141 foreach ($courses as $course) {
1142 if ($course->sortorder
!= $cat->sortorder +
$i) {
1143 $course->sortorder
= $cat->sortorder +
$i;
1144 $DB->update_record_raw('course', $course, true);
1152 * Internal recursive category verification function, do not use directly!
1154 * @todo Document the arguments of this function better
1157 * @uses MAX_COURSES_IN_CATEGORY
1158 * @uses CONTEXT_COURSECAT
1159 * @param array $children
1160 * @param int $sortorder
1161 * @param string $parent
1163 * @param string $path
1164 * @param array $fixcontexts
1167 function _fix_course_cats($children, &$sortorder, $parent, $depth, $path, &$fixcontexts) {
1172 foreach ($children as $cat) {
1173 $sortorder = $sortorder + MAX_COURSES_IN_CATEGORY
;
1175 if ($parent != $cat->parent
or $depth != $cat->depth
or $path.'/'.$cat->id
!= $cat->path
) {
1176 $cat->parent
= $parent;
1177 $cat->depth
= $depth;
1178 $cat->path
= $path.'/'.$cat->id
;
1181 // make sure context caches are rebuild and dirty contexts marked
1182 $context = get_context_instance(CONTEXT_COURSECAT
, $cat->id
);
1183 $fixcontexts[$context->id
] = $context;
1185 if ($cat->sortorder
!= $sortorder) {
1186 $cat->sortorder
= $sortorder;
1190 $DB->update_record('course_categories', $cat, true);
1192 if (isset($cat->children
)) {
1193 _fix_course_cats($cat->children
, $sortorder, $cat->id
, $cat->depth
, $cat->path
, $fixcontexts);
1199 * List of remote courses that a user has access to via MNET.
1200 * Works only on the IDP
1204 * @param int @userid The user id to get remote courses for
1205 * @return array Array of {@link $COURSE} of course objects
1207 function get_my_remotecourses($userid=0) {
1210 if (empty($userid)) {
1211 $userid = $USER->id
;
1214 // we can not use SELECT DISTINCT + text field (summary) because of MS SQL and Oracle, subselect used therefore
1215 $sql = "SELECT c.id, c.remoteid, c.shortname, c.fullname,
1216 c.hostid, c.summary, c.summaryformat, c.categoryname AS cat_name,
1218 FROM {mnetservice_enrol_courses} c
1219 JOIN (SELECT DISTINCT hostid, remotecourseid
1220 FROM {mnetservice_enrol_enrolments}
1222 ) e ON (e.hostid = c.hostid AND e.remotecourseid = c.remoteid)
1223 JOIN {mnet_host} h ON h.id = c.hostid";
1225 return $DB->get_records_sql($sql, array($userid));
1229 * List of remote hosts that a user has access to via MNET.
1234 * @return array|bool Array of host objects or false
1236 function get_my_remotehosts() {
1239 if ($USER->mnethostid
== $CFG->mnet_localhost_id
) {
1240 return false; // Return nothing on the IDP
1242 if (!empty($USER->mnet_foreign_host_array
) && is_array($USER->mnet_foreign_host_array
)) {
1243 return $USER->mnet_foreign_host_array
;
1249 * This function creates a default separated/connected scale
1251 * This function creates a default separated/connected scale
1252 * so there's something in the database. The locations of
1253 * strings and files is a bit odd, but this is because we
1254 * need to maintain backward compatibility with many different
1255 * existing language translations and older sites.
1260 function make_default_scale() {
1263 $defaultscale = NULL;
1264 $defaultscale->courseid
= 0;
1265 $defaultscale->userid
= 0;
1266 $defaultscale->name
= get_string('separateandconnected');
1267 $defaultscale->description
= get_string('separateandconnectedinfo');
1268 $defaultscale->scale
= get_string('postrating1', 'forum').','.
1269 get_string('postrating2', 'forum').','.
1270 get_string('postrating3', 'forum');
1271 $defaultscale->timemodified
= time();
1273 $defaultscale->id
= $DB->insert_record('scale', $defaultscale);
1274 $DB->execute("UPDATE {forum} SET scale = ?", array($defaultscale->id
));
1279 * Returns a menu of all available scales from the site as well as the given course
1282 * @param int $courseid The id of the course as found in the 'course' table.
1285 function get_scales_menu($courseid=0) {
1288 $sql = "SELECT id, name
1290 WHERE courseid = 0 or courseid = ?
1291 ORDER BY courseid ASC, name ASC";
1292 $params = array($courseid);
1294 if ($scales = $DB->get_records_sql_menu($sql, $params)) {
1298 make_default_scale();
1300 return $DB->get_records_sql_menu($sql, $params);
1306 * Given a set of timezone records, put them in the database, replacing what is there
1309 * @param array $timezones An array of timezone records
1312 function update_timezone_records($timezones) {
1315 /// Clear out all the old stuff
1316 $DB->delete_records('timezone');
1318 /// Insert all the new stuff
1319 foreach ($timezones as $timezone) {
1320 if (is_array($timezone)) {
1321 $timezone = (object)$timezone;
1323 $DB->insert_record('timezone', $timezone);
1328 /// MODULE FUNCTIONS /////////////////////////////////////////////////
1331 * Just gets a raw list of all modules in a course
1334 * @param int $courseid The id of the course as found in the 'course' table.
1337 function get_course_mods($courseid) {
1340 if (empty($courseid)) {
1341 return false; // avoid warnings
1344 return $DB->get_records_sql("SELECT cm.*, m.name as modname
1345 FROM {modules} m, {course_modules} cm
1346 WHERE cm.course = ? AND cm.module = m.id AND m.visible = 1",
1347 array($courseid)); // no disabled mods
1352 * Given an id of a course module, finds the coursemodule description
1355 * @param string $modulename name of module type, eg. resource, assignment,... (optional, slower and less safe if not specified)
1356 * @param int $cmid course module id (id in course_modules table)
1357 * @param int $courseid optional course id for extra validation
1358 * @param bool $sectionnum include relative section number (0,1,2 ...)
1359 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1360 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1361 * MUST_EXIST means throw exception if no record or multiple records found
1364 function get_coursemodule_from_id($modulename, $cmid, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING
) {
1367 $params = array('cmid'=>$cmid);
1370 if (!$modulename = $DB->get_field_sql("SELECT md.name
1372 JOIN {course_modules} cm ON cm.module = md.id
1373 WHERE cm.id = :cmid", $params, $strictness)) {
1378 $params['modulename'] = $modulename;
1385 $courseselect = "AND cm.course = :courseid";
1386 $params['courseid'] = $courseid;
1390 $sectionfield = ", cw.section AS sectionnum";
1391 $sectionjoin = "LEFT JOIN {course_sections} cw ON cw.id = cm.section";
1394 $sql = "SELECT cm.*, m.name, md.name AS modname $sectionfield
1395 FROM {course_modules} cm
1396 JOIN {modules} md ON md.id = cm.module
1397 JOIN {".$modulename."} m ON m.id = cm.instance
1399 WHERE cm.id = :cmid AND md.name = :modulename
1402 return $DB->get_record_sql($sql, $params, $strictness);
1406 * Given an instance number of a module, finds the coursemodule description
1409 * @param string $modulename name of module type, eg. resource, assignment,...
1410 * @param int $instance module instance number (id in resource, assignment etc. table)
1411 * @param int $courseid optional course id for extra validation
1412 * @param bool $sectionnum include relative section number (0,1,2 ...)
1413 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1414 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1415 * MUST_EXIST means throw exception if no record or multiple records found
1418 function get_coursemodule_from_instance($modulename, $instance, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING
) {
1421 $params = array('instance'=>$instance, 'modulename'=>$modulename);
1428 $courseselect = "AND cm.course = :courseid";
1429 $params['courseid'] = $courseid;
1433 $sectionfield = ", cw.section AS sectionnum";
1434 $sectionjoin = "LEFT JOIN {course_sections} cw ON cw.id = cm.section";
1437 $sql = "SELECT cm.*, m.name, md.name AS modname $sectionfield
1438 FROM {course_modules} cm
1439 JOIN {modules} md ON md.id = cm.module
1440 JOIN {".$modulename."} m ON m.id = cm.instance
1442 WHERE m.id = :instance AND md.name = :modulename
1445 return $DB->get_record_sql($sql, $params, $strictness);
1449 * Returns all course modules of given activity in course
1451 * @param string $modulename The module name (forum, quiz, etc.)
1452 * @param int $courseid The course id to get modules for
1453 * @param string $extrafields extra fields starting with m.
1454 * @return array Array of results
1456 function get_coursemodules_in_course($modulename, $courseid, $extrafields='') {
1459 if (!empty($extrafields)) {
1460 $extrafields = ", $extrafields";
1463 $params['courseid'] = $courseid;
1464 $params['modulename'] = $modulename;
1467 return $DB->get_records_sql("SELECT cm.*, m.name, md.name as modname $extrafields
1468 FROM {course_modules} cm, {modules} md, {".$modulename."} m
1469 WHERE cm.course = :courseid AND
1470 cm.instance = m.id AND
1471 md.name = :modulename AND
1472 md.id = cm.module", $params);
1476 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1478 * Returns an array of all the active instances of a particular
1479 * module in given courses, sorted in the order they are defined
1480 * in the course. Returns an empty array on any errors.
1482 * The returned objects includle the columns cw.section, cm.visible,
1483 * cm.groupmode and cm.groupingid, cm.groupmembersonly, and are indexed by cm.id.
1487 * @param string $modulename The name of the module to get instances for
1488 * @param array $courses an array of course objects.
1489 * @param int $userid
1490 * @param int $includeinvisible
1491 * @return array of module instance objects, including some extra fields from the course_modules
1492 * and course_sections tables, or an empty array if an error occurred.
1494 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
1497 $outputarray = array();
1499 if (empty($courses) ||
!is_array($courses) ||
count($courses) == 0) {
1500 return $outputarray;
1503 list($coursessql, $params) = $DB->get_in_or_equal(array_keys($courses), SQL_PARAMS_NAMED
, 'c0');
1504 $params['modulename'] = $modulename;
1506 if (!$rawmods = $DB->get_records_sql("SELECT cm.id AS coursemodule, m.*, cw.section, cm.visible AS visible,
1507 cm.groupmode, cm.groupingid, cm.groupmembersonly
1508 FROM {course_modules} cm, {course_sections} cw, {modules} md,
1510 WHERE cm.course $coursessql AND
1511 cm.instance = m.id AND
1512 cm.section = cw.id AND
1513 md.name = :modulename AND
1514 md.id = cm.module", $params)) {
1515 return $outputarray;
1518 foreach ($courses as $course) {
1519 $modinfo = get_fast_modinfo($course, $userid);
1521 if (empty($modinfo->instances
[$modulename])) {
1525 foreach ($modinfo->instances
[$modulename] as $cm) {
1526 if (!$includeinvisible and !$cm->uservisible
) {
1529 if (!isset($rawmods[$cm->id
])) {
1532 $instance = $rawmods[$cm->id
];
1533 if (!empty($cm->extra
)) {
1534 $instance->extra
= $cm->extra
;
1536 $outputarray[] = $instance;
1540 return $outputarray;
1544 * Returns an array of all the active instances of a particular module in a given course,
1545 * sorted in the order they are defined.
1547 * Returns an array of all the active instances of a particular
1548 * module in a given course, sorted in the order they are defined
1549 * in the course. Returns an empty array on any errors.
1551 * The returned objects includle the columns cw.section, cm.visible,
1552 * cm.groupmode and cm.groupingid, cm.groupmembersonly, and are indexed by cm.id.
1554 * Simply calls {@link all_instances_in_courses()} with a single provided course
1556 * @param string $modulename The name of the module to get instances for
1557 * @param object $course The course obect.
1558 * @return array of module instance objects, including some extra fields from the course_modules
1559 * and course_sections tables, or an empty array if an error occurred.
1560 * @param int $userid
1561 * @param int $includeinvisible
1563 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
1564 return get_all_instances_in_courses($modulename, array($course->id
=> $course), $userid, $includeinvisible);
1569 * Determine whether a module instance is visible within a course
1571 * Given a valid module object with info about the id and course,
1572 * and the module's type (eg "forum") returns whether the object
1573 * is visible or not, groupmembersonly visibility not tested
1577 * @param $moduletype Name of the module eg 'forum'
1578 * @param $module Object which is the instance of the module
1579 * @return bool Success
1581 function instance_is_visible($moduletype, $module) {
1584 if (!empty($module->id
)) {
1585 $params = array('courseid'=>$module->course
, 'moduletype'=>$moduletype, 'moduleid'=>$module->id
);
1586 if ($records = $DB->get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.groupmembersonly, cm.course
1587 FROM {course_modules} cm, {modules} m
1588 WHERE cm.course = :courseid AND
1589 cm.module = m.id AND
1590 m.name = :moduletype AND
1591 cm.instance = :moduleid", $params)) {
1593 foreach ($records as $record) { // there should only be one - use the first one
1594 return $record->visible
;
1598 return true; // visible by default!
1602 * Determine whether a course module is visible within a course,
1603 * this is different from instance_is_visible() - faster and visibility for user
1607 * @uses DEBUG_DEVELOPER
1608 * @uses CONTEXT_MODULE
1609 * @uses CONDITION_MISSING_EXTRATABLE
1610 * @param object $cm object
1611 * @param int $userid empty means current user
1612 * @return bool Success
1614 function coursemodule_visible_for_user($cm, $userid=0) {
1617 if (empty($cm->id
)) {
1618 debugging("Incorrect course module parameter!", DEBUG_DEVELOPER
);
1621 if (empty($userid)) {
1622 $userid = $USER->id
;
1624 if (!$cm->visible
and !has_capability('moodle/course:viewhiddenactivities', get_context_instance(CONTEXT_MODULE
, $cm->id
), $userid)) {
1627 if ($CFG->enableavailability
) {
1628 require_once($CFG->libdir
.'/conditionlib.php');
1629 $ci=new condition_info($cm,CONDITION_MISSING_EXTRATABLE
);
1630 if(!$ci->is_available($cm->availableinfo
,false,$userid) and
1631 !has_capability('moodle/course:viewhiddenactivities',
1632 get_context_instance(CONTEXT_MODULE
, $cm->id
), $userid)) {
1636 return groups_course_module_visible($cm, $userid);
1642 /// LOG FUNCTIONS /////////////////////////////////////////////////////
1646 * Add an entry to the log table.
1648 * Add an entry to the log table. These are "action" focussed rather
1649 * than web server hits, and provide a way to easily reconstruct what
1650 * any particular student has been doing.
1656 * @uses DEBUG_DEVELOPER
1658 * @param int $courseid The course id
1659 * @param string $module The module name - e.g. forum, journal, resource, course, user etc
1660 * @param string $action 'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
1661 * @param string $url The file and parameters used to see the results of the action
1662 * @param string $info Additional description information
1663 * @param string $cm The course_module->id if there is one
1664 * @param string $user If log regards $user other than $USER
1667 function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
1668 // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1669 // This is for a good reason: it is the most frequently used DB update function,
1670 // so it has been optimised for speed.
1671 global $DB, $CFG, $USER;
1673 if ($cm === '' ||
is_null($cm)) { // postgres won't translate empty string to its default
1680 if (session_is_loggedinas()) { // Don't log
1683 $userid = empty($USER->id
) ?
'0' : $USER->id
;
1686 if (isset($CFG->logguests
) and !$CFG->logguests
) {
1687 if (!$userid or isguestuser($userid)) {
1692 $REMOTE_ADDR = getremoteaddr();
1696 if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1697 $url = html_entity_decode($url);
1702 // Restrict length of log lines to the space actually available in the
1703 // database so that it doesn't cause a DB error. Log a warning so that
1704 // developers can avoid doing things which are likely to cause this on a
1706 $tl = textlib_get_instance();
1707 if(!empty($info) && $tl->strlen($info)>255) {
1708 $info = $tl->substr($info,0,252).'...';
1709 debugging('Warning: logged very long info',DEBUG_DEVELOPER
);
1712 // If the 100 field size is changed, also need to alter print_log in course/lib.php
1713 if(!empty($url) && $tl->strlen($url)>100) {
1714 $url=$tl->substr($url,0,97).'...';
1715 debugging('Warning: logged very long URL',DEBUG_DEVELOPER
);
1718 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->logwrites++
;};
1720 $log = array('time'=>$timenow, 'userid'=>$userid, 'course'=>$courseid, 'ip'=>$REMOTE_ADDR, 'module'=>$module,
1721 'cmid'=>$cm, 'action'=>$action, 'url'=>$url, 'info'=>$info);
1724 $DB->insert_record_raw('log', $log, false);
1725 } catch (dml_exception
$e) {
1726 debugging('Error: Could not insert a new entry to the Moodle log', DEBUG_ALL
);
1727 // MDL-11893, alert $CFG->supportemail if insert into log failed
1728 if ($CFG->supportemail
and empty($CFG->noemailever
)) {
1729 // email_to_user is not usable because email_to_user tries to write to the logs table,
1730 // and this will get caught in an infinite loop, if disk is full
1732 $subject = 'Insert into log failed at your moodle site '.$site->fullname
;
1733 $message = "Insert into log table failed at ". date('l dS \of F Y h:i:s A') .".\n It is possible that your disk is full.\n\n";
1734 $message .= "The failed query parameters are:\n\n" . var_export($log, true);
1736 $lasttime = get_config('admin', 'lastloginserterrormail');
1737 if(empty($lasttime) ||
time() - $lasttime > 60*60*24) { // limit to 1 email per day
1738 //using email directly rather than messaging as they may not be able to log in to access a message
1739 mail($CFG->supportemail
, $subject, $message);
1740 set_config('lastloginserterrormail', time(), 'admin');
1747 * Store user last access times - called when use enters a course or site
1752 * @uses LASTACCESS_UPDATE_SECS
1754 * @param int $courseid, empty means site
1757 function user_accesstime_log($courseid=0) {
1758 global $USER, $CFG, $DB;
1760 if (!isloggedin() or session_is_loggedinas()) {
1761 // no access tracking
1765 if (empty($courseid)) {
1771 /// Store site lastaccess time for the current user
1772 if ($timenow - $USER->lastaccess
> LASTACCESS_UPDATE_SECS
) {
1773 /// Update $USER->lastaccess for next checks
1774 $USER->lastaccess
= $timenow;
1776 $last = new stdClass();
1777 $last->id
= $USER->id
;
1778 $last->lastip
= getremoteaddr();
1779 $last->lastaccess
= $timenow;
1781 $DB->update_record_raw('user', $last);
1784 if ($courseid == SITEID
) {
1785 /// no user_lastaccess for frontpage
1789 /// Store course lastaccess times for the current user
1790 if (empty($USER->currentcourseaccess
[$courseid]) or ($timenow - $USER->currentcourseaccess
[$courseid] > LASTACCESS_UPDATE_SECS
)) {
1792 $lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', array('userid'=>$USER->id
, 'courseid'=>$courseid));
1794 if ($lastaccess === false) {
1795 // Update course lastaccess for next checks
1796 $USER->currentcourseaccess
[$courseid] = $timenow;
1798 $last = new stdClass();
1799 $last->userid
= $USER->id
;
1800 $last->courseid
= $courseid;
1801 $last->timeaccess
= $timenow;
1802 $DB->insert_record_raw('user_lastaccess', $last, false);
1804 } else if ($timenow - $lastaccess < LASTACCESS_UPDATE_SECS
) {
1805 // no need to update now, it was updated recently in concurrent login ;-)
1808 // Update course lastaccess for next checks
1809 $USER->currentcourseaccess
[$courseid] = $timenow;
1811 $DB->set_field('user_lastaccess', 'timeaccess', $timenow, array('userid'=>$USER->id
, 'courseid'=>$courseid));
1817 * Select all log records based on SQL criteria
1819 * @todo Finish documenting this function
1822 * @param string $select SQL select criteria
1823 * @param array $params named sql type params
1824 * @param string $order SQL order by clause to sort the records returned
1825 * @param string $limitfrom ?
1826 * @param int $limitnum ?
1827 * @param int $totalcount Passed in by reference.
1830 function get_logs($select, array $params=null, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
1834 $order = "ORDER BY $order";
1841 $select = "WHERE $select";
1844 $sql = "SELECT COUNT(*)
1848 $totalcount = $DB->count_records_sql($sql, $params);
1850 $sql = "SELECT l.*, u.firstname, u.lastname, u.picture
1852 LEFT JOIN {user} u ON l.userid = u.id
1856 return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum) ;
1861 * Select all log records for a given course and user
1863 * @todo Finish documenting this function
1867 * @param int $userid The id of the user as found in the 'user' table.
1868 * @param int $courseid The id of the course as found in the 'course' table.
1869 * @param string $coursestart ?
1871 function get_logs_usercourse($userid, $courseid, $coursestart) {
1878 $courseselect = "AND course = :courseid";
1879 $params['courseid'] = $courseid;
1881 $params['userid'] = $userid;
1882 $
$coursestart = (int)$coursestart; // note: unfortunately pg complains if you use name parameter or column alias in GROUP BY
1884 return $DB->get_records_sql("SELECT FLOOR((time - $coursestart)/". DAYSECS
.") AS day, COUNT(*) AS num
1886 WHERE userid = :userid
1887 AND time > $coursestart $courseselect
1888 GROUP BY FLOOR((time - $coursestart)/". DAYSECS
.")", $params);
1892 * Select all log records for a given course, user, and day
1896 * @param int $userid The id of the user as found in the 'user' table.
1897 * @param int $courseid The id of the course as found in the 'course' table.
1898 * @param string $daystart ?
1901 function get_logs_userday($userid, $courseid, $daystart) {
1904 $params = array('userid'=>$userid);
1908 $courseselect = "AND course = :courseid";
1909 $params['courseid'] = $courseid;
1911 $daystart = (int)$daystart; // note: unfortunately pg complains if you use name parameter or column alias in GROUP BY
1913 return $DB->get_records_sql("SELECT FLOOR((time - $daystart)/". HOURSECS
.") AS hour, COUNT(*) AS num
1915 WHERE userid = :userid
1916 AND time > $daystart $courseselect
1917 GROUP BY FLOOR((time - $daystart)/". HOURSECS
.") ", $params);
1921 * Returns an object with counts of failed login attempts
1923 * Returns information about failed login attempts. If the current user is
1924 * an admin, then two numbers are returned: the number of attempts and the
1925 * number of accounts. For non-admins, only the attempts on the given user
1929 * @uses CONTEXT_SYSTEM
1930 * @param string $mode Either 'admin' or 'everybody'
1931 * @param string $username The username we are searching for
1932 * @param string $lastlogin The date from which we are searching
1935 function count_login_failures($mode, $username, $lastlogin) {
1938 $params = array('mode'=>$mode, 'username'=>$username, 'lastlogin'=>$lastlogin);
1939 $select = "module='login' AND action='error' AND time > :lastlogin";
1941 $count = new stdClass();
1943 if (is_siteadmin()) {
1944 if ($count->attempts
= $DB->count_records_select('log', $select, $params)) {
1945 $count->accounts
= $DB->count_records_select('log', $select, $params, 'COUNT(DISTINCT info)');
1948 } else if ($mode == 'everybody') {
1949 if ($count->attempts
= $DB->count_records_select('log', "$select AND info = :username", $params)) {
1957 /// GENERAL HELPFUL THINGS ///////////////////////////////////
1960 * Dumps a given object's information for debugging purposes
1962 * When used in a CLI script, the object's information is written to the standard
1963 * error output stream. When used in a web script, the object is dumped to a
1964 * pre-formatted block with the "notifytiny" CSS class.
1966 * @param mixed $object The data to be printed
1967 * @return void output is echo'd
1969 function print_object($object) {
1971 // we may need a lot of memory here
1972 raise_memory_limit(MEMORY_EXTRA
);
1975 fwrite(STDERR
, print_r($object, true));
1976 fwrite(STDERR
, PHP_EOL
);
1978 echo html_writer
::tag('pre', s(print_r($object, true)), array('class' => 'notifytiny'));
1983 * This function is the official hook inside XMLDB stuff to delegate its debug to one
1984 * external function.
1986 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
1987 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
1989 * @uses DEBUG_DEVELOPER
1990 * @param string $message string contains the error message
1991 * @param object $object object XMLDB object that fired the debug
1993 function xmldb_debug($message, $object) {
1995 debugging($message, DEBUG_DEVELOPER
);
2000 * @uses CONTEXT_COURSECAT
2001 * @return boolean Whether the user can create courses in any category in the system.
2003 function user_can_create_courses() {
2005 $catsrs = $DB->get_recordset('course_categories');
2006 foreach ($catsrs as $cat) {
2007 if (has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT
, $cat->id
))) {