MDL-15263 Fixed numeric entities remaining after html_to_text
[moodle.git] / lib / datalib.php
CommitLineData
6078ba30 1<?php // $Id$
341b5ed2 2
7cf1c7bd 3/**
4 * Library of functions for database manipulation.
5930cded 5 *
7cf1c7bd 6 * Other main libraries:
7 * - weblib.php - functions that produce web output
8 * - moodlelib.php - general-purpose Moodle functions
6159ce65 9 * @author Martin Dougiamas and many others
89dcb99d 10 * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
7cf1c7bd 11 * @package moodlecore
12 */
13
3dce78e1 14/**
15 * Sets up global $DB moodle_database instance
16 * @return void
17 */
18function setup_DB() {
19 global $CFG, $DB;
20
21 if (isset($DB)) {
22 return;
23 }
24
25 if (!isset($CFG->dbuser)) {
26 $CFG->dbuser = '';
27 }
28
29 if (!isset($CFG->dbpass)) {
30 $CFG->dbpass = '';
31 }
32
33 if (!isset($CFG->dbname)) {
34 $CFG->dbname = '';
35 }
36
37 if (!isset($CFG->dbpersist)) {
38 $CFG->dbpersist = false;
39 }
40
41 if (!isset($CFG->dblibrary)) {
42 $CFG->dblibrary = 'adodb';
43 }
44
45 if (!isset($CFG->dboptions)) {
46 $CFG->dboptions = array();
47 }
48
49 if ($CFG->dblibrary == 'adodb') {
50 $classname = $CFG->dbtype.'_adodb_moodle_database';
51 require_once($CFG->libdir.'/dml/'.$classname.'.php');
52 $DB = new $classname();
53
54 } else {
55 error('Not implemented db library yet: '.$CFG->dblibrary);
56 }
57
58 $CFG->dbfamily = $DB->get_dbfamily(); // TODO: BC only for now
59
60 $driverstatus = $DB->driver_installed();
61
62 if ($driverstatus !== true) {
63 print_error('dbdriverproblem', 'error', '', $driverstatus);
64 }
65
66 if (debugging('', DEBUG_ALL)) {
67 // catch errors
68 ob_start();
69 } else {
70 $prevdebug = error_reporting(0);
71 }
72 if (!$DB->connect($CFG->dbhost, $CFG->dbuser, $CFG->dbpass, $CFG->dbname, $CFG->dbpersist, $CFG->prefix, $CFG->dboptions)) {
73 if (debugging('', DEBUG_ALL)) {
74 if ($dberr = ob_get_contents()) {
75 $dberr = '<p><em>'.$dberr.'</em></p>';
76 }
77 ob_end_clean();
78 } else {
79 $dberr = '';
80 }
81 if (empty($CFG->noemailever) and !empty($CFG->emailconnectionerrorsto)) {
82 @mail($CFG->emailconnectionerrorsto,
83 'WARNING: Database connection error: '.$CFG->wwwroot,
84 'Connection error: '.$CFG->wwwroot);
85 }
86 print_error('dbconnectionfailed', 'error', '', $dberr);
87 }
88 if (debugging('', DEBUG_ALL)) {
89 ob_end_clean();
90 } else {
91 error_reporting($prevdebug);
92 }
93
94 return true;
95}
96
341b5ed2 97 /// Some constants
98 define('LASTACCESS_UPDATE_SECS', 60); /// Number of seconds to wait before
99 /// updating lastaccess information in DB.
df28d6c5 100
df28d6c5 101/// USER DATABASE ////////////////////////////////////////////////
102
18a97fd8 103/**
fbc21ae8 104 * Returns $user object of the main admin user
20aeb4b8 105 * primary admin = admin with lowest role_assignment id among admins
fbc21ae8 106 * @uses $CFG
107 * @return object(admin) An associative array representing the admin user.
fbc21ae8 108 */
df28d6c5 109function get_admin () {
df28d6c5 110
111 global $CFG;
2965f8fd 112 static $myadmin;
113
114 if (isset($myadmin)) {
115 return $myadmin;
116 }
df28d6c5 117
118 if ( $admins = get_admins() ) {
119 foreach ($admins as $admin) {
2965f8fd 120 $myadmin = $admin;
8f0cd6ef 121 return $admin; // ie the first one
df28d6c5 122 }
123 } else {
124 return false;
125 }
126}
127
18a97fd8 128/**
c26ecb1a 129 * Returns list of all admins, using 1 DB query. It depends on DB schema v1.7
130 * but does not depend on the v1.9 datastructures (context.path, etc).
fbc21ae8 131 *
132 * @uses $CFG
7290c7fa 133 * @return object
fbc21ae8 134 */
df28d6c5 135function get_admins() {
624a690b 136 global $DB;
5930cded 137
c26ecb1a 138 $sql = "SELECT ra.userid, SUM(rc.permission) AS permission, MIN(ra.id) AS adminid
624a690b 139 FROM {role_capabilities} rc
140 JOIN {context} ctx ON ctx.id=rc.contextid
141 JOIN {role_assignments} ra ON ra.roleid=rc.roleid AND ra.contextid=ctx.id
d251907c 142 WHERE ctx.contextlevel=10 AND rc.capability IN (?, ?, ?)
624a690b 143 GROUP BY ra.userid
c26ecb1a 144 HAVING SUM(rc.permission) > 0";
624a690b 145 $params = array('moodle/site:config', 'moodle/legacy:admin', 'moodle/site:doanything');
c26ecb1a 146
147 $sql = "SELECT u.*, ra.adminid
624a690b 148 FROM {user} u
149 JOIN ($sql) ra
150 ON u.id=ra.userid
151 ORDER BY ra.adminid ASC";
5930cded 152
624a690b 153 return $DB->get_records_sql($sql, $params);
df28d6c5 154}
155
156
b61efafb 157function get_courses_in_metacourse($metacourseid) {
624a690b 158 global $DB;
b61efafb 159
624a690b 160 $sql = "SELECT c.id, c.shortname, c.fullname
161 FROM {course} c, {course_meta} mc
162 WHERE mc.parent_course = ? AND mc.child_course = c.id
163 ORDER BY c.shortname";
164 $params = array($metacourseid);
b61efafb 165
624a690b 166 return $DB->get_records_sql($sql, $params);
b61efafb 167}
168
624a690b 169function get_courses_notin_metacourse($metacourseid) {
170 global $DB;
b61efafb 171
624a690b 172 if ($alreadycourses = get_courses_in_metacourse($metacourseid)) {
173 $alreadycourses = implode(',',array_keys($alreadycourses));
174 $alreadycourses = "AND c.id NOT IN ($alreadycourses)";
c44d5d42 175 } else {
624a690b 176 $alreadycourses = "";
b61efafb 177 }
178ccd11 178
624a690b 179 $sql = "SELECT c.id,c.shortname,c.fullname
180 FROM {course} c
181 WHERE c.id != ? and c.id != ".SITEID." and c.metacourse != 1
182 $alreadycourses
183 ORDER BY c.shortname";
184 $params = array($metacourseid);
5930cded 185
624a690b 186 return $DB->get_records_sql($sql, $params);
b61efafb 187}
188
493cde24 189function count_courses_notin_metacourse($metacourseid) {
624a690b 190 global $DB;
493cde24 191
624a690b 192 if ($alreadycourses = get_courses_in_metacourse($metacourseid)) {
193 $alreadycourses = implode(',',array_keys($alreadycourses));
194 $alreadycourses = "AND c.id NOT IN ($alreadycourses)";
195 } else {
196 $alreadycourses = "";
493cde24 197 }
198
d251907c 199 $sql = "SELECT COUNT(c.id)
624a690b 200 FROM {course} c
201 WHERE c.id != ? and c.id != ".SITEID." and c.metacourse != 1
202 $alreadycourses";
203 $params = array($metacourseid);
204
205 return $DB->count_records_sql($sql, $params);
493cde24 206}
207
900df8b6 208/**
fbc21ae8 209 * Search through course users
210 *
5930cded 211 * If $coursid specifies the site course then this function searches
fbc21ae8 212 * through all undeleted and confirmed users
213 *
fbc21ae8 214 * @param int $courseid The course in question.
215 * @param int $groupid The group in question.
216 * @param string $searchtext ?
217 * @param string $sort ?
624a690b 218 * @param array $exceptions ?
7290c7fa 219 * @return object
fbc21ae8 220 */
624a690b 221function search_users($courseid, $groupid, $searchtext, $sort='', array $exceptions=null) {
222 global $DB;
0720313b 223
245ac557 224 $LIKE = $DB->sql_ilike();
225 $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
8f0cd6ef 226
900df8b6 227 if (!empty($exceptions)) {
624a690b 228 list($exceptions, $params) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex0000', false);
229 $except = "AND u.id $exceptions";
900df8b6 230 } else {
624a690b 231 $except = "";
232 $params = array();
900df8b6 233 }
2700d113 234
900df8b6 235 if (!empty($sort)) {
624a690b 236 $order = "ORDER BY $sort";
900df8b6 237 } else {
624a690b 238 $order = "";
900df8b6 239 }
8f0cd6ef 240
624a690b 241 $select = "u.deleted = 0 AND u.confirmed = 1 AND ($fullname $LIKE :search1 OR u.email $LIKE :search2)";
242 $params['search1'] = "%$searchtext%";
243 $params['search2'] = "%$searchtext%";
2700d113 244
222ac91b 245 if (!$courseid or $courseid == SITEID) {
624a690b 246 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
247 FROM {user} u
248 WHERE $select
249 $except
250 $order";
251 return $DB->get_records_sql($sql, $params);
2700d113 252
624a690b 253 } else {
900df8b6 254 if ($groupid) {
624a690b 255 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
256 FROM {user} u
257 JOIN {groups_members} gm ON gm.userid = u.id
258 WHERE $select AND gm.groupid = :groupid
259 $except
260 $order";
261 $params['groupid'] = $groupid;
262 return $DB->get_records_sql($sql, $params);
263
900df8b6 264 } else {
ea8158c1 265 $context = get_context_instance(CONTEXT_COURSE, $courseid);
266 $contextlists = get_related_contexts_string($context);
624a690b 267
268 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
269 FROM {user} u
270 JOIN {role_assignments} ra ON ra.userid = u.id
271 WHERE $select AND ra.contextid $contextlists
272 $except
273 $order";
274 return $DB->get_records_sql($sql, $params);
900df8b6 275 }
276 }
df28d6c5 277}
278
18a97fd8 279/**
fbc21ae8 280 * Returns a subset of users
281 *
282 * @uses $CFG
7290c7fa 283 * @param bool $get If false then only a count of the records is returned
fbc21ae8 284 * @param string $search A simple string to search for
7290c7fa 285 * @param bool $confirmed A switch to allow/disallow unconfirmed users
fbc21ae8 286 * @param array(int) $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
287 * @param string $sort A SQL snippet for the sorting criteria to use
288 * @param string $firstinitial ?
289 * @param string $lastinitial ?
290 * @param string $page ?
291 * @param string $recordsperpage ?
292 * @param string $fields A comma separated list of fields to be returned from the chosen table.
7290c7fa 293 * @return object|false|int {@link $USER} records unless get is false in which case the integer count of the records found is returned. False is returned if an error is encountered.
fbc21ae8 294 */
624a690b 295function get_users($get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC',
296 $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null) {
297 global $DB;
5930cded 298
36075e09 299 if ($get && !$recordsperpage) {
300 debugging('Call to get_users with $get = true no $recordsperpage limit. ' .
301 'On large installations, this will probably cause an out of memory error. ' .
302 'Please think again and change your code so that it does not try to ' .
03517306 303 'load so much data into memory.', DEBUG_DEVELOPER);
36075e09 304 }
18a97fd8 305
245ac557 306 $LIKE = $DB->sql_ilike();
307 $fullname = $DB->sql_fullname();
e384fb7b 308
624a690b 309 $select = " username <> :guest AND deleted = 0";
310 $params = array('guest'=>'guest');
488acd1b 311
0044147e 312 if (!empty($search)){
313 $search = trim($search);
624a690b 314 $select .= " AND ($fullname $LIKE :search1 OR email $LIKE :search2 OR username = :search3)";
315 $params['search1'] = "%$search%";
316 $params['search2'] = "%$search%";
317 $params['search3'] = "$search";
e384fb7b 318 }
319
5a741655 320 if ($confirmed) {
624a690b 321 $select .= " AND confirmed = 1";
5a741655 322 }
323
324 if ($exceptions) {
624a690b 325 list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex0000', false);
326 $params = $params + $eparams;
327 $except = " AND id $exceptions";
5a741655 328 }
329
488acd1b 330 if ($firstinitial) {
624a690b 331 $select .= " AND firstname $LIKE :fni";
332 $params['fni'] = "$firstinitial%";
8f0cd6ef 333 }
488acd1b 334 if ($lastinitial) {
624a690b 335 $select .= " AND lastname $LIKE :lni";
336 $params['lni'] = "$lastinitial%";
8f0cd6ef 337 }
488acd1b 338
cd1edf9e 339 if ($extraselect) {
624a690b 340 $select .= " AND $extraselect";
341 $params = $params + (array)$extraparams;
cd1edf9e 342 }
343
5a741655 344 if ($get) {
624a690b 345 return $DB->get_records_select('user', $select, $params, $sort, $fields, $page, $recordsperpage);
5a741655 346 } else {
624a690b 347 return $DB->count_records_select('user', $select, $params);
5a741655 348 }
9fa49e22 349}
350
5a741655 351
18a97fd8 352/**
fbc21ae8 353 * shortdesc (optional)
354 *
355 * longdesc
356 *
fbc21ae8 357 * @param string $sort ?
358 * @param string $dir ?
359 * @param int $categoryid ?
360 * @param int $categoryid ?
361 * @param string $search ?
362 * @param string $firstinitial ?
363 * @param string $lastinitial ?
7290c7fa 364 * @returnobject {@link $USER} records
fbc21ae8 365 * @todo Finish documenting this function
366 */
367
36075e09 368function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,
624a690b 369 $search='', $firstinitial='', $lastinitial='', $extraselect='', array $extraparams=null) {
370 global $DB;
31fefa63 371
245ac557 372 $LIKE = $DB->sql_ilike();
373 $fullname = $DB->sql_fullname();
c2a96d6b 374
624a690b 375 $select = "deleted <> 1";
376 $params = array();
488acd1b 377
0044147e 378 if (!empty($search)) {
379 $search = trim($search);
624a690b 380 $select .= " AND ($fullname $LIKE :search1 OR email $LIKE :search2 OR username = :search3)";
381 $params['search1'] = "%$search%";
382 $params['search2'] = "%$search%";
383 $params['search3'] = "$search";
488acd1b 384 }
385
386 if ($firstinitial) {
624a690b 387 $select .= " AND firstname $LIKE :fni";
388 $params['fni'] = "$firstinitial%";
488acd1b 389 }
488acd1b 390 if ($lastinitial) {
624a690b 391 $select .= " AND lastname $LIKE :lni";
392 $params['lni'] = "$lastinitial%";
c750592a 393 }
394
cd1edf9e 395 if ($extraselect) {
624a690b 396 $select .= " AND $extraselect";
397 $params = $params + (array)$extraparams;
cd1edf9e 398 }
03d820c7 399
488acd1b 400 if ($sort) {
624a690b 401 $sort = " ORDER BY $sort $dir";
488acd1b 402 }
403
404/// warning: will return UNCONFIRMED USERS
624a690b 405 return $DB->get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed, mnethostid
406 FROM {user}
407 WHERE $select
408 $sort", $params, $page, $recordsperpage);
9fa49e22 409
410}
411
488acd1b 412
18a97fd8 413/**
7290c7fa 414 * Full list of users that have confirmed their accounts.
fbc21ae8 415 *
624a690b 416 * @return array of unconfirmed users
fbc21ae8 417 */
9fa49e22 418function get_users_confirmed() {
624a690b 419 global $DB;
420 return $DB->get_records_sql("SELECT *
421 FROM {user}
422 WHERE confirmed = 1 AND deleted = 0 AND username <> ?", array('guest'));
9fa49e22 423}
424
425
02ebf404 426/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
427
428
18a97fd8 429/**
fbc21ae8 430 * Returns $course object of the top-level site.
431 *
89dcb99d 432 * @return course A {@link $COURSE} object for the site
fbc21ae8 433 */
c44d5d42 434function get_site() {
624a690b 435 global $SITE, $DB;
c44d5d42 436
437 if (!empty($SITE->id)) { // We already have a global to use, so return that
438 return $SITE;
439 }
02ebf404 440
624a690b 441 if ($course = $DB->get_record('course', array('category'=>0))) {
02ebf404 442 return $course;
443 } else {
444 return false;
445 }
446}
447
18a97fd8 448/**
613bbd7c 449 * Returns list of courses, for whole site, or category
450 *
451 * Returns list of courses, for whole site, or category
bfbfdb53 452 * Important: Using c.* for fields is extremely expensive because
613bbd7c 453 * we are using distinct. You almost _NEVER_ need all the fields
454 * in such a large SELECT
455 *
456 * @param type description
3b8a284c 457 * @return array of courses
613bbd7c 458 */
6315b1c8 459function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
02ebf404 460
3b8a284c 461 global $USER, $CFG, $DB;
5930cded 462
3b8a284c 463 $params = array();
464
465 if ($categoryid !== "all" && is_numeric($categoryid)) {
466 $categoryselect = "WHERE c.category = :catid";
467 $params['catid'] = $categoryid;
71dea306 468 } else {
5930cded 469 $categoryselect = "";
09575480 470 }
471
472 if (empty($sort)) {
473 $sortstatement = "";
474 } else {
475 $sortstatement = "ORDER BY $sort";
476 }
477
478 $visiblecourses = array();
5930cded 479
3b8a284c 480 $sql = "SELECT $fields,
481 ctx.id AS ctxid, ctx.path AS ctxpath,
482 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
483 FROM {course} c
484 JOIN {context} ctx
485 ON (c.id = ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
486 $categoryselect
487 $sortstatement";
488
71dea306 489 // pull out all course matching the cat
3b8a284c 490 if ($courses = $DB->get_records_sql($sql, $params)) {
09575480 491
492 // loop throught them
493 foreach ($courses as $course) {
656418b1 494 $course = make_context_subobj($course);
285f94f5 495 if (isset($course->visible) && $course->visible <= 0) {
09575480 496 // for hidden courses, require visibility check
656418b1 497 if (has_capability('moodle/course:viewhiddencourses', $course->context)) {
3b8a284c 498 $visiblecourses [$course->id] = $course;
09575480 499 }
500 } else {
3b8a284c 501 $visiblecourses [$course->id] = $course;
5930cded 502 }
09575480 503 }
6315b1c8 504 }
71dea306 505 return $visiblecourses;
8130b77b 506}
507
8130b77b 508
6315b1c8 509/**
613bbd7c 510 * Returns list of courses, for whole site, or category
511 *
512 * Similar to get_courses, but allows paging
5930cded 513 * Important: Using c.* for fields is extremely expensive because
613bbd7c 514 * we are using distinct. You almost _NEVER_ need all the fields
515 * in such a large SELECT
516 *
517 * @param type description
3b8a284c 518 * @return array of courses
613bbd7c 519 */
6315b1c8 520function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
521 &$totalcount, $limitfrom="", $limitnum="") {
3b8a284c 522 global $USER, $CFG, $DB;
c7fe5c6f 523
3b8a284c 524 $params = array();
5930cded 525
71dea306 526 $categoryselect = "";
527 if ($categoryid != "all" && is_numeric($categoryid)) {
3b8a284c 528 $categoryselect = "WHERE c.category = :catid";
529 $params['catid'] = $categoryid;
71dea306 530 } else {
5930cded 531 $categoryselect = "";
532 }
533
3b8a284c 534 $sql = "SELECT $fields,
535 ctx.id AS ctxid, ctx.path AS ctxpath,
536 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
537 FROM {course} c
538 JOIN {context} ctx
539 ON (c.id = ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
540 $categoryselect
541 ORDER BY $sort";
542
71dea306 543 // pull out all course matching the cat
3b8a284c 544 if (!$rs = $DB->get_recordset_sql($sql, $params)) {
545 return array();
12490fc2 546 }
71dea306 547 $totalcount = 0;
5930cded 548
285f94f5 549 if (!$limitfrom) {
5930cded 550 $limitfrom = 0;
71dea306 551 }
5930cded 552
71dea306 553 // iteration will have to be done inside loop to keep track of the limitfrom and limitnum
3b8a284c 554 $visiblecourses = array();
555 foreach($rs as $course) {
03cedd62 556 $course = make_context_subobj($course);
557 if ($course->visible <= 0) {
558 // for hidden courses, require visibility check
559 if (has_capability('moodle/course:viewhiddencourses', $course->context)) {
71dea306 560 $totalcount++;
03cedd62 561 if ($totalcount > $limitfrom && (!$limitnum or count($visiblecourses) < $limitnum)) {
3b8a284c 562 $visiblecourses [$course->id] = $course;
71dea306 563 }
564 }
03cedd62 565 } else {
566 $totalcount++;
567 if ($totalcount > $limitfrom && (!$limitnum or count($visiblecourses) < $limitnum)) {
3b8a284c 568 $visiblecourses [$course->id] = $course;
03cedd62 569 }
5930cded 570 }
71dea306 571 }
3b8a284c 572 $rs->close();
71dea306 573 return $visiblecourses;
02ebf404 574}
575
624a690b 576/**
70f15878 577 * Retrieve course records with the course managers and other related records
578 * that we need for print_course(). This allows print_courses() to do its job
579 * in a constant number of DB queries, regardless of the number of courses,
580 * role assignments, etc.
bfbfdb53 581 *
70f15878 582 * The returned array is indexed on c.id, and each course will have
583 * - $course->context - a context obj
584 * - $course->managers - array containing RA objects that include a $user obj
585 * with the minimal fields needed for fullname()
586 *
587 */
588function get_courses_wmanagers($categoryid=0, $sort="c.sortorder ASC", $fields=array()) {
589 /*
bfbfdb53 590 * The plan is to
70f15878 591 *
592 * - Grab the courses JOINed w/context
593 *
594 * - Grab the interesting course-manager RAs
595 * JOINed with a base user obj and add them to each course
596 *
597 * So as to do all the work in 2 DB queries. The RA+user JOIN
598 * ends up being pretty expensive if it happens over _all_
599 * courses on a large site. (Are we surprised!?)
600 *
601 * So this should _never_ get called with 'all' on a large site.
602 *
603 */
3b8a284c 604 global $USER, $CFG, $DB;
70f15878 605
3b8a284c 606 $params = array();
70f15878 607 $allcats = false; // bool flag
608 if ($categoryid === 'all') {
609 $categoryclause = '';
610 $allcats = true;
611 } elseif (is_numeric($categoryid)) {
3b8a284c 612 $categoryclause = "c.category = :catid";
613 $params['catid'] = $categoryid;
70f15878 614 } else {
615 debugging("Could not recognise categoryid = $categoryid");
616 $categoryclause = '';
617 }
618
619 $basefields = array('id', 'category', 'sortorder',
620 'shortname', 'fullname', 'idnumber',
621 'teacher', 'teachers', 'student', 'students',
622 'guest', 'startdate', 'visible',
623 'newsitems', 'cost', 'enrol',
624 'groupmode', 'groupmodeforce');
625
626 if (!is_null($fields) && is_string($fields)) {
627 if (empty($fields)) {
628 $fields = $basefields;
629 } else {
bfbfdb53 630 // turn the fields from a string to an array that
70f15878 631 // get_user_courses_bycap() will like...
632 $fields = explode(',',$fields);
633 $fields = array_map('trim', $fields);
634 $fields = array_unique(array_merge($basefields, $fields));
635 }
636 } elseif (is_array($fields)) {
637 $fields = array_merge($basefields,$fields);
638 }
639 $coursefields = 'c.' .join(',c.', $fields);
640
641 if (empty($sort)) {
642 $sortstatement = "";
643 } else {
644 $sortstatement = "ORDER BY $sort";
645 }
646
e89f157b 647 $where = 'WHERE c.id != ' . SITEID;
70f15878 648 if ($categoryclause !== ''){
e89f157b 649 $where = "$where AND $categoryclause";
70f15878 650 }
651
652 // pull out all courses matching the cat
653 $sql = "SELECT $coursefields,
45ea1afb 654 ctx.id AS ctxid, ctx.path AS ctxpath,
655 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
3b8a284c 656 FROM {course} c
657 JOIN {context} ctx
658 ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
659 $where
660 $sortstatement";
70f15878 661
662 $catpaths = array();
663 $catpath = NULL;
3b8a284c 664 if ($courses = $DB->get_records_sql($sql, $params)) {
70f15878 665 // loop on courses materialising
bfbfdb53 666 // the context, and prepping data to fetch the
70f15878 667 // managers efficiently later...
668 foreach ($courses as $k => $course) {
669 $courses[$k] = make_context_subobj($courses[$k]);
670 $courses[$k]->managers = array();
671 if ($allcats === false) {
672 // single cat, so take just the first one...
673 if ($catpath === NULL) {
674 $catpath = preg_replace(':/\d+$:', '',$courses[$k]->context->path);
675 }
676 } else {
677 // chop off the contextid of the course itself
678 // like dirname() does...
679 $catpaths[] = preg_replace(':/\d+$:', '',$courses[$k]->context->path);
680 }
681 }
682 } else {
683 return array(); // no courses!
684 }
685
b1cff118 686 $CFG->coursemanager = trim($CFG->coursemanager);
687 if (empty($CFG->coursemanager)) {
688 return $courses;
689 }
690
70f15878 691 $managerroles = split(',', $CFG->coursemanager);
692 $catctxids = '';
693 if (count($managerroles)) {
694 if ($allcats === true) {
695 $catpaths = array_unique($catpaths);
696 $ctxids = array();
697 foreach ($catpaths as $cpath) {
698 $ctxids = array_merge($ctxids, explode('/',substr($cpath,1)));
699 }
700 $ctxids = array_unique($ctxids);
701 $catctxids = implode( ',' , $ctxids);
c7a71127 702 unset($catpaths);
703 unset($cpath);
70f15878 704 } else {
705 // take the ctx path from the first course
706 // as all categories will be the same...
707 $catpath = substr($catpath,1);
708 $catpath = preg_replace(':/\d+$:','',$catpath);
709 $catctxids = str_replace('/',',',$catpath);
710 }
711 if ($categoryclause !== '') {
712 $categoryclause = "AND $categoryclause";
713 }
714 /*
bfbfdb53 715 * Note: Here we use a LEFT OUTER JOIN that can
70f15878 716 * "optionally" match to avoid passing a ton of context
717 * ids in an IN() clause. Perhaps a subselect is faster.
718 *
719 * In any case, this SQL is not-so-nice over large sets of
720 * courses with no $categoryclause.
721 *
722 */
723 $sql = "SELECT ctx.path, ctx.instanceid, ctx.contextlevel,
bfbfdb53 724 ra.hidden,
70f15878 725 r.id AS roleid, r.name as rolename,
726 u.id AS userid, u.firstname, u.lastname
3b8a284c 727 FROM {role_assignments} ra
728 JOIN {context} ctx ON ra.contextid = ctx.id
729 JOIN {user} u ON ra.userid = u.id
730 JOIN {role} r ON ra.roleid = r.id
731 LEFT OUTER JOIN {course} c
732 ON (ctx.instanceid=c.id AND ctx.contextlevel=".CONTEXT_COURSE.")
c7a71127 733 WHERE ( c.id IS NOT NULL";
734 // under certain conditions, $catctxids is NULL
735 if($catctxids == NULL){
736 $sql .= ") ";
737 }else{
738 $sql .= " OR ra.contextid IN ($catctxids) )";
739 }
740
741 $sql .= "AND ra.roleid IN ({$CFG->coursemanager})
70f15878 742 $categoryclause
743 ORDER BY r.sortorder ASC, ctx.contextlevel ASC, ra.sortorder ASC";
3b8a284c 744 $rs = $DB->get_recordset_sql($sql, $params);
bfbfdb53 745
70f15878 746 // This loop is fairly stupid as it stands - might get better
747 // results doing an initial pass clustering RAs by path.
3b8a284c 748 foreach($rs as $ra) {
03cedd62 749 $user = new StdClass;
750 $user->id = $ra->userid; unset($ra->userid);
751 $user->firstname = $ra->firstname; unset($ra->firstname);
752 $user->lastname = $ra->lastname; unset($ra->lastname);
753 $ra->user = $user;
754 if ($ra->contextlevel == CONTEXT_SYSTEM) {
755 foreach ($courses as $k => $course) {
756 $courses[$k]->managers[] = $ra;
757 }
758 } elseif ($ra->contextlevel == CONTEXT_COURSECAT) {
759 if ($allcats === false) {
760 // It always applies
70f15878 761 foreach ($courses as $k => $course) {
762 $courses[$k]->managers[] = $ra;
763 }
03cedd62 764 } else {
765 foreach ($courses as $k => $course) {
766 // Note that strpos() returns 0 as "matched at pos 0"
767 if (strpos($course->context->path, $ra->path.'/')===0) {
768 // Only add it to subpaths
70f15878 769 $courses[$k]->managers[] = $ra;
770 }
70f15878 771 }
70f15878 772 }
03cedd62 773 } else { // course-level
774 if(!array_key_exists($ra->instanceid, $courses)) {
775 //this course is not in a list, probably a frontpage course
776 continue;
777 }
778 $courses[$ra->instanceid]->managers[] = $ra;
70f15878 779 }
780 }
3b8a284c 781 $rs->close();
70f15878 782 }
783
784 return $courses;
785}
02ebf404 786
18a97fd8 787/**
bfbfdb53 788 * Convenience function - lists courses that a user has access to view.
fbc21ae8 789 *
82c62d1b 790 * For admins and others with access to "every" course in the system, we should
791 * try to get courses with explicit RAs.
792 *
793 * NOTE: this function is heavily geared towards the perspective of the user
bfbfdb53 794 * passed in $userid. So it will hide courses that the user cannot see
82c62d1b 795 * (for any reason) even if called from cron or from another $USER's
796 * perspective.
bfbfdb53 797 *
82c62d1b 798 * If you really want to know what courses are assigned to the user,
bfbfdb53 799 * without any hiding or scheming, call the lower-level
82c62d1b 800 * get_user_courses_bycap().
801 *
802 *
803 * Notes inherited from get_user_courses_bycap():
e1d5e5c1 804 *
805 * - $fields is an array of fieldnames to ADD
806 * so name the fields you really need, which will
807 * be added and uniq'd
808 *
809 * - the course records have $c->context which is a fully
810 * valid context object. Saves you a query per course!
811 *
352f6f74 812 * @uses $CFG,$USER
7290c7fa 813 * @param int $userid The user of interest
33f85740 814 * @param string $sort the sortorder in the course table
e1d5e5c1 815 * @param array $fields - names of _additional_ fields to return (also accepts a string)
f8e1c7af 816 * @param bool $doanything True if using the doanything flag
817 * @param int $limit Maximum number of records to return, or 0 for unlimited
33f85740 818 * @return array {@link $COURSE} of course objects
fbc21ae8 819 */
e1d5e5c1 820function get_my_courses($userid, $sort='visible DESC,sortorder ASC', $fields=NULL, $doanything=false,$limit=0) {
3b8a284c 821 global $CFG, $USER, $DB;
5930cded 822
4dbca99e 823 // Guest's do not have any courses
12d06877 824 $sitecontext = get_context_instance(CONTEXT_SYSTEM);
3b8a284c 825 if (has_capability('moodle/legacy:guest', $sitecontext, $userid, false)) {
4dbca99e 826 return(array());
827 }
601edb90 828
352f6f74 829 $basefields = array('id', 'category', 'sortorder',
830 'shortname', 'fullname', 'idnumber',
831 'teacher', 'teachers', 'student', 'students',
832 'guest', 'startdate', 'visible',
833 'newsitems', 'cost', 'enrol',
834 'groupmode', 'groupmodeforce');
835
e1d5e5c1 836 if (!is_null($fields) && is_string($fields)) {
837 if (empty($fields)) {
352f6f74 838 $fields = $basefields;
e1d5e5c1 839 } else {
bfbfdb53 840 // turn the fields from a string to an array that
573674bf 841 // get_user_courses_bycap() will like...
352f6f74 842 $fields = explode(',',$fields);
843 $fields = array_map('trim', $fields);
844 $fields = array_unique(array_merge($basefields, $fields));
845 }
bbfed0ec 846 } elseif (is_array($fields)) {
bfbfdb53 847 $fields = array_unique(array_merge($basefields, $fields));
352f6f74 848 } else {
849 $fields = $basefields;
850 }
851
b9e9491a 852 $orderby = '';
853 $sort = trim($sort);
854 if (!empty($sort)) {
70070493 855 $rawsorts = explode(',', $sort);
856 $sorts = array();
857 foreach ($rawsorts as $rawsort) {
858 $rawsort = trim($rawsort);
c7e6b7e4 859 if (strpos($rawsort, 'c.') === 0) {
70070493 860 $rawsort = substr($rawsort, 2);
861 }
862 $sorts[] = trim($rawsort);
863 }
864 $sort = 'c.'.implode(',c.', $sorts);
b9e9491a 865 $orderby = "ORDER BY $sort";
866 }
867
352f6f74 868 //
869 // Logged-in user - Check cached courses
870 //
871 // NOTE! it's a _string_ because
872 // - it's all we'll ever use
873 // - it serialises much more compact than an array
82c62d1b 874 // this a big concern here - cost of serialise
875 // and unserialise gets huge as the session grows
352f6f74 876 //
877 // If the courses are too many - it won't be set
878 // for large numbers of courses, caching in the session
879 // has marginal benefits (costs too much, not
880 // worthwhile...) and we may hit SQL parser limits
881 // because we use IN()
882 //
ae1555ae 883 if ($userid === $USER->id) {
bfbfdb53 884 if (isset($USER->loginascontext)
fe3141e0 885 && $USER->loginascontext->contextlevel == CONTEXT_COURSE) {
ae1555ae 886 // list _only_ this course
887 // anything else is asking for trouble...
888 $courseids = $USER->loginascontext->instanceid;
bfbfdb53 889 } elseif (isset($USER->mycourses)
ae1555ae 890 && is_string($USER->mycourses)) {
891 if ($USER->mycourses === '') {
892 // empty str means: user has no courses
893 // ... so do the easy thing...
894 return array();
895 } else {
896 $courseids = $USER->mycourses;
897 }
898 }
899 if (isset($courseids)) {
bfbfdb53 900 // The data massaging here MUST be kept in sync with
352f6f74 901 // get_user_courses_bycap() so we return
902 // the same...
903 // (but here we don't need to check has_cap)
904 $coursefields = 'c.' .join(',c.', $fields);
905 $sql = "SELECT $coursefields,
45ea1afb 906 ctx.id AS ctxid, ctx.path AS ctxpath,
907 ctx.depth as ctxdepth, ctx.contextlevel AS ctxlevel,
82c62d1b 908 cc.path AS categorypath
3b8a284c 909 FROM {course} c
910 JOIN {course_categories} cc ON c.category=cc.id
911 JOIN {context} ctx
912 ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
913 WHERE c.id IN ($courseids)
914 $orderby";
915 $rs = $DB->get_recordset_sql($sql);
352f6f74 916 $courses = array();
917 $cc = 0; // keep count
3b8a284c 918 foreach ($rs as $c) {
03cedd62 919 // build the context obj
920 $c = make_context_subobj($c);
c1b7a5e5 921
03cedd62 922 $courses[$c->id] = $c;
923 if ($limit > 0 && $cc++ > $limit) {
924 break;
352f6f74 925 }
926 }
3b8a284c 927 $rs->close();
352f6f74 928 return $courses;
2f3499b7 929 }
930 }
152a9060 931
352f6f74 932 // Non-cached - get accessinfo
e1d5e5c1 933 if ($userid === $USER->id && isset($USER->access)) {
aeb3916b 934 $accessinfo = $USER->access;
bdf3bbd1 935 } else {
e1d5e5c1 936 $accessinfo = get_user_access_sitewide($userid);
aeb3916b 937 }
352f6f74 938
bfbfdb53 939
573674bf 940 $courses = get_user_courses_bycap($userid, 'moodle/course:view', $accessinfo,
941 $doanything, $sort, $fields,
942 $limit);
352f6f74 943
82c62d1b 944 $cats = NULL;
945 // If we have to walk category visibility
946 // to eval course visibility, get the categories
947 if (empty($CFG->allowvisiblecoursesinhiddencategories)) {
948 $sql = "SELECT cc.id, cc.path, cc.visible,
45ea1afb 949 ctx.id AS ctxid, ctx.path AS ctxpath,
950 ctx.depth as ctxdepth, ctx.contextlevel AS ctxlevel
3b8a284c 951 FROM {course_categories} cc
952 JOIN {context} ctx ON (cc.id = ctx.instanceid)
953 WHERE ctx.contextlevel = ".CONTEXT_COURSECAT."
954 ORDER BY cc.id";
955 $rs = $DB->get_recordset_sql($sql);
bfbfdb53 956
957 // Using a temporary array instead of $cats here, to avoid a "true" result when isnull($cats) further down
958 $categories = array();
3b8a284c 959 foreach($rs as $course_cat) {
03cedd62 960 // build the context obj
961 $course_cat = make_context_subobj($course_cat);
962 $categories[$course_cat->id] = $course_cat;
82c62d1b 963 }
3b8a284c 964 $rs->close();
bfbfdb53 965
966 if (!empty($categories)) {
967 $cats = $categories;
968 }
969
970 unset($course_cat);
82c62d1b 971 }
352f6f74 972 //
973 // Strangely, get_my_courses() is expected to return the
aeb3916b 974 // array keyed on id, which messes up the sorting
352f6f74 975 // So do that, and also cache the ids in the session if appropriate
976 //
aeb3916b 977 $kcourses = array();
bfbfdb53 978 $courses_count = count($courses);
352f6f74 979 $cacheids = NULL;
82c62d1b 980 $vcatpaths = array();
bfbfdb53 981 if ($userid === $USER->id && $courses_count < 500) {
352f6f74 982 $cacheids = array();
983 }
bfbfdb53 984 for ($n=0; $n<$courses_count; $n++) {
82c62d1b 985
986 //
b00cb46b 987 // Check whether $USER (not $userid) can _actually_ see them
82c62d1b 988 // Easy if $CFG->allowvisiblecoursesinhiddencategories
989 // is set, and we don't have to care about categories.
990 // Lots of work otherwise... (all in mem though!)
991 //
bfbfdb53 992 $cansee = false;
82c62d1b 993 if (is_null($cats)) { // easy rules!
994 if ($courses[$n]->visible == true) {
995 $cansee = true;
996 } elseif (has_capability('moodle/course:viewhiddencourses',
b00cb46b 997 $courses[$n]->context, $USER->id)) {
82c62d1b 998 $cansee = true;
999 }
1000 } else {
1001 //
1002 // Is the cat visible?
1003 // we have to assume it _is_ visible
1004 // so we can shortcut when we find a hidden one
1005 //
1006 $viscat = true;
1007 $cpath = $courses[$n]->categorypath;
1008 if (isset($vcatpaths[$cpath])) {
1009 $viscat = $vcatpaths[$cpath];
1010 } else {
1011 $cpath = substr($cpath,1); // kill leading slash
1012 $cpath = explode('/',$cpath);
1013 $ccct = count($cpath);
1014 for ($m=0;$m<$ccct;$m++) {
1015 $ccid = $cpath[$m];
1016 if ($cats[$ccid]->visible==false) {
1017 $viscat = false;
1018 break;
1019 }
1020 }
1021 $vcatpaths[$courses[$n]->categorypath] = $viscat;
1022 }
1023
1024 //
b00cb46b 1025 // Perhaps it's actually visible to $USER
82c62d1b 1026 // check moodle/category:visibility
bfbfdb53 1027 //
82c62d1b 1028 // The name isn't obvious, but the description says
1029 // "See hidden categories" so the user shall see...
bfbfdb53 1030 // But also check if the allowvisiblecoursesinhiddencategories setting is true, and check for course visibility
82c62d1b 1031 if ($viscat === false) {
bfbfdb53 1032 $catctx = $cats[$courses[$n]->category]->context;
1033 if (has_capability('moodle/category:visibility', $catctx, $USER->id)) {
82c62d1b 1034 $vcatpaths[$courses[$n]->categorypath] = true;
1035 $viscat = true;
bfbfdb53 1036 } elseif ($CFG->allowvisiblecoursesinhiddencategories && $courses[$n]->visible == true) {
1037 $viscat = true;
82c62d1b 1038 }
1039 }
1040
1041 //
1042 // Decision matrix
1043 //
1044 if ($viscat === true) {
1045 if ($courses[$n]->visible == true) {
1046 $cansee = true;
1047 } elseif (has_capability('moodle/course:viewhiddencourses',
b00cb46b 1048 $courses[$n]->context, $USER->id)) {
82c62d1b 1049 $cansee = true;
1050 }
1051 }
1052 }
1053 if ($cansee === true) {
1054 $kcourses[$courses[$n]->id] = $courses[$n];
1055 if (is_array($cacheids)) {
1056 $cacheids[] = $courses[$n]->id;
1057 }
352f6f74 1058 }
1059 }
1060 if (is_array($cacheids)) {
1061 // Only happens
1062 // - for the logged in user
1063 // - below the threshold (500)
1064 // empty string is _valid_
1065 $USER->mycourses = join(',',$cacheids);
1066 } elseif ($userid === $USER->id && isset($USER->mycourses)) {
1067 // cheap sanity check
1068 unset($USER->mycourses);
aeb3916b 1069 }
352f6f74 1070
aeb3916b 1071 return $kcourses;
02ebf404 1072}
1073
18a97fd8 1074/**
7290c7fa 1075 * A list of courses that match a search
fbc21ae8 1076 *
1077 * @uses $CFG
1078 * @param array $searchterms ?
1079 * @param string $sort ?
1080 * @param int $page ?
1081 * @param int $recordsperpage ?
1082 * @param int $totalcount Passed in by reference. ?
7290c7fa 1083 * @return object {@link $COURSE} records
fbc21ae8 1084 */
d4419d55 1085function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
3b8a284c 1086 global $CFG, $DB;
02ebf404 1087
06c1a1da 1088 if ($DB->sql_regex_supported()) {
1089 $REGEXP = $DB->sql_regex(true);
1090 $NOTREGEXP = $DB->sql_regex(false);
02ebf404 1091 }
3b8a284c 1092 $LIKE = $DB->sql_ilike(); // case-insensitive
02ebf404 1093
06c1a1da 1094 $searchcond = array();
1095 $params = array();
1096 $i = 0;
02ebf404 1097
06c1a1da 1098 $concat = $DB->sql_concat('c.summary', "' '", 'c.fullname');
3b8a284c 1099
02ebf404 1100 foreach ($searchterms as $searchterm) {
06c1a1da 1101 $i++;
6bb0f67f 1102
0f62a5b5 1103 $NOT = ''; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle
1104 /// will use it to simulate the "-" operator with LIKE clause
1105
6bb0f67f 1106 /// Under Oracle and MSSQL, trim the + and - operators and perform
0f62a5b5 1107 /// simpler LIKE (or NOT LIKE) queries
06c1a1da 1108 if (!$DB->sql_regex_supported()) {
0f62a5b5 1109 if (substr($searchterm, 0, 1) == '-') {
1110 $NOT = ' NOT ';
1111 }
6bb0f67f 1112 $searchterm = trim($searchterm, '+-');
1113 }
1114
06c1a1da 1115 // TODO: +- may not work for non latin languages
3b8a284c 1116
d4419d55 1117 if (substr($searchterm,0,1) == '+') {
06c1a1da 1118 $searchterm = trim($searchterm, '+-');
1119 $searchterm = preg_quote($searchterm, '|');
1120 $searchcond[] = "$concat $REGEXP :ss$i";
1121 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
1122
a8b56716 1123 } else if (substr($searchterm,0,1) == "-") {
06c1a1da 1124 $searchterm = trim($searchterm, '+-');
1125 $searchterm = preg_quote($searchterm, '|');
1126 $searchcond[] = "$concat $NOTREGEXP :ss$i";
1127 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
1128
a8b56716 1129 } else {
06c1a1da 1130 $searchcond[] = "$concat $NOT $LIKE :ss$i";
1131 $params['ss'.$i] = "%$searchterm%";
a8b56716 1132 }
02ebf404 1133 }
1134
06c1a1da 1135 if (empty($searchcond)) {
1136 $totalcount = 0;
1137 return array();
1138 }
1139
1140 $searchcond = implode(" AND ", $searchcond);
1141
2c64f65c 1142 $sql = "SELECT c.*,
45ea1afb 1143 ctx.id AS ctxid, ctx.path AS ctxpath,
1144 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
3b8a284c 1145 FROM {course} c
1146 JOIN {context} ctx
1147 ON (c.id = ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
06c1a1da 1148 WHERE $searchcond AND c.id <> ".SITEID."
1149 ORDER BY $sort";
2c64f65c 1150 $courses = array();
3b8a284c 1151 $c = 0; // counts how many visible courses we've seen
02ebf404 1152
3b8a284c 1153 if ($rs = $DB->get_recordset_sql($sql, $params)) {
2c64f65c 1154 // Tiki pagination
1155 $limitfrom = $page * $recordsperpage;
1156 $limitto = $limitfrom + $recordsperpage;
2c64f65c 1157
3b8a284c 1158 foreach($rs as $course) {
2c64f65c 1159 $course = make_context_subobj($course);
1160 if ($course->visible || has_capability('moodle/course:viewhiddencourses', $course->context)) {
1161 // Don't exit this loop till the end
1162 // we need to count all the visible courses
1163 // to update $totalcount
1164 if ($c >= $limitfrom && $c < $limitto) {
3b8a284c 1165 $courses[$course->id] = $course;
02ebf404 1166 }
2c64f65c 1167 $c++;
02ebf404 1168 }
1169 }
3b8a284c 1170 $rs->close();
02ebf404 1171 }
1172
2c64f65c 1173 // our caller expects 2 bits of data - our return
1174 // array, and an updated $totalcount
1175 $totalcount = $c;
02ebf404 1176 return $courses;
1177}
1178
1179
18a97fd8 1180/**
40fb8aa6 1181 * Returns a sorted list of categories. Each category object has a context
1182 * property that is a context object.
bfbfdb53 1183 *
40fb8aa6 1184 * When asking for $parent='none' it will return all the categories, regardless
1185 * of depth. Wheen asking for a specific parent, the default is to return
1186 * a "shallow" resultset. Pass false to $shallow and it will return all
bfbfdb53 1187 * the child categories as well.
1188 *
fbc21ae8 1189 *
613bbd7c 1190 * @param string $parent The parent category if any
1191 * @param string $sort the sortorder
40fb8aa6 1192 * @param bool $shallow - set to false to get the children too
613bbd7c 1193 * @return array of categories
fbc21ae8 1194 */
40fb8aa6 1195function get_categories($parent='none', $sort=NULL, $shallow=true) {
3b8a284c 1196 global $DB;
40fb8aa6 1197
1198 if ($sort === NULL) {
1199 $sort = 'ORDER BY cc.sortorder ASC';
1200 } elseif ($sort ==='') {
1201 // leave it as empty
1202 } else {
1203 $sort = "ORDER BY $sort";
1204 }
02ebf404 1205
814748c9 1206 if ($parent === 'none') {
40fb8aa6 1207 $sql = "SELECT cc.*,
3b8a284c 1208 ctx.id AS ctxid, ctx.path AS ctxpath,
1209 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
1210 FROM {course_categories} cc
1211 JOIN {context} ctx
1212 ON cc.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSECAT."
40fb8aa6 1213 $sort";
3b8a284c 1214 $params = array();
1215
40fb8aa6 1216 } elseif ($shallow) {
40fb8aa6 1217 $sql = "SELECT cc.*,
45ea1afb 1218 ctx.id AS ctxid, ctx.path AS ctxpath,
1219 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
3b8a284c 1220 FROM {course_categories} cc
1221 JOIN {context} ctx
1222 ON cc.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSECAT."
1223 WHERE cc.parent=?
40fb8aa6 1224 $sort";
3b8a284c 1225 $params = array($parent);
1226
02ebf404 1227 } else {
40fb8aa6 1228 $sql = "SELECT cc.*,
45ea1afb 1229 ctx.id AS ctxid, ctx.path AS ctxpath,
1230 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel
3b8a284c 1231 FROM {course_categories} cc
1232 JOIN {context} ctx
1233 ON cc.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSECAT."
1234 JOIN {course_categories} ccp
1235 ON (cc.path LIKE ".$DB->sql_concat('ccp.path',"'%'").")
1236 WHERE ccp.id=?
40fb8aa6 1237 $sort";
3b8a284c 1238 $params = array($parent);
02ebf404 1239 }
40fb8aa6 1240 $categories = array();
1241
3b8a284c 1242 if( $rs = $DB->get_recordset_sql($sql, $params) ){
1243 foreach($rs as $cat) {
40fb8aa6 1244 $cat = make_context_subobj($cat);
3b8a284c 1245 if ($cat->visible || has_capability('moodle/course:create', $cat->context)) {
40fb8aa6 1246 $categories[$cat->id] = $cat;
02ebf404 1247 }
1248 }
3b8a284c 1249 $rs->close();
02ebf404 1250 }
1251 return $categories;
1252}
1253
1254
2327b9df 1255/**
1256 * Returns an array of category ids of all the subcategories for a given
1257 * category.
1258 * @param $catid - The id of the category whose subcategories we want to find.
1259 * @return array of category ids.
1260 */
1261function get_all_subcategories($catid) {
3b8a284c 1262 global $DB;
2327b9df 1263
1264 $subcats = array();
1265
3b8a284c 1266 if ($categories = $DB->get_records('course_categories', array('parent'=>$catid))) {
2327b9df 1267 foreach ($categories as $cat) {
1268 array_push($subcats, $cat->id);
1269 $subcats = array_merge($subcats, get_all_subcategories($cat->id));
1270 }
1271 }
1272 return $subcats;
1273}
1274
1275
18a97fd8 1276/**
ba87a4da 1277* This recursive function makes sure that the courseorder is consecutive
1278*
1279* @param type description
1280*
1281* $n is the starting point, offered only for compatilibity -- will be ignored!
1282* $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade
1283* safely from 1.4 to 1.5
1284*/
f41ef63e 1285function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') {
c3df0901 1286 global $CFG, $DB;
8f0cd6ef 1287
02ebf404 1288 $count = 0;
5930cded 1289
f41ef63e 1290 $catgap = 1000; // "standard" category gap
1291 $tolerance = 200; // how "close" categories can get
5930cded 1292
f41ef63e 1293 if ($categoryid > 0){
1294 // update depth and path
c3df0901 1295 $cat = $DB->get_record('course_categories', array('id'=>$categoryid));
f41ef63e 1296 if ($cat->parent == 0) {
1297 $depth = 0;
1298 $path = '';
1299 } else if ($depth == 0 ) { // doesn't make sense; get from DB
1300 // this is only called if the $depth parameter looks dodgy
c3df0901 1301 $parent = $DB->get_record('course_categories', array('id'=>$cat->parent));
f41ef63e 1302 $path = $parent->path;
1303 $depth = $parent->depth;
1304 }
1305 $path = $path . '/' . $categoryid;
1306 $depth = $depth + 1;
ba87a4da 1307
c5d13b68 1308 if ($cat->path !== $path) {
c3df0901 1309 $DB->set_field('course_categories', 'path', $path, array('id'=>$categoryid));
c5d13b68 1310 }
1311 if ($cat->depth != $depth) {
c3df0901 1312 $DB->set_field('course_categories', 'depth', $depth, array('id'=>$categoryid));
c5d13b68 1313 }
f41ef63e 1314 }
39f65595 1315
1316 // get some basic info about courses in the category
c3df0901 1317 $info = $DB->get_record_sql("SELECT MIN(sortorder) AS min,
1318 MAX(sortorder) AS max,
1319 COUNT(sortorder) AS count
1320 FROM {course}
1321 WHERE category=?", array($categoryid));
1322 if ($info) { // no courses?
ba87a4da 1323 $max = $info->max;
1324 $count = $info->count;
1325 $min = $info->min;
1326 unset($info);
1327 }
1328
814748c9 1329 if ($categoryid > 0 && $n==0) { // only passed category so don't shift it
1330 $n = $min;
1331 }
1332
39f65595 1333 // $hasgap flag indicates whether there's a gap in the sequence
5930cded 1334 $hasgap = false;
39f65595 1335 if ($max-$min+1 != $count) {
1336 $hasgap = true;
1337 }
5930cded 1338
39f65595 1339 // $mustshift indicates whether the sequence must be shifted to
1340 // meet its range
1341 $mustshift = false;
1342 if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) {
1343 $mustshift = true;
1344 }
1345
ba87a4da 1346 // actually sort only if there are courses,
1347 // and we meet one ofthe triggers:
1348 // - safe flag
1349 // - they are not in a continuos block
1350 // - they are too close to the 'bottom'
39f65595 1351 if ($count && ( $safe || $hasgap || $mustshift ) ) {
1352 // special, optimized case where all we need is to shift
1353 if ( $mustshift && !$safe && !$hasgap) {
1354 $shift = $n + $catgap - $min;
f8ea6077 1355 if ($shift < $count) {
1356 $shift = $count + $catgap;
1357 }
39f65595 1358 // UPDATE course SET sortorder=sortorder+$shift
c3df0901 1359 $DB->execute("UPDATE {course}
1360 SET sortorder=sortorder+?
1361 WHERE category=?", array($shift, $categoryid));
5930cded 1362 $n = $n + $catgap + $count;
1363
39f65595 1364 } else { // do it slowly
5930cded 1365 $n = $n + $catgap;
39f65595 1366 // if the new sequence overlaps the current sequence, lack of transactions
1367 // will stop us -- shift things aside for a moment...
48505662 1368 if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) {
d6a49dab 1369 $shift = $max + $n + 1000;
655b09ca 1370 $DB->execute("UPDATE {course}
1371 SET sortorder=sortorder+?
1372 WHERE category=?", array($shift, $categoryid));
ba87a4da 1373 }
1374
39f65595 1375 $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
c3df0901 1376 $DB->begin_sql();
f8ea6077 1377 $tx = true; // transaction sanity
5930cded 1378 foreach ($courses as $course) {
f8ea6077 1379 if ($tx && $course->sortorder != $n ) { // save db traffic
c3df0901 1380 $tx = $tx && $DB->set_field('course', 'sortorder', $n, array('id'=>$course->id));
ba87a4da 1381 }
1382 $n++;
1383 }
f8ea6077 1384 if ($tx) {
c3df0901 1385 $DB->commit_sql();
f8ea6077 1386 } else {
c3df0901 1387 $DB->rollback_sql();
f8ea6077 1388 if (!$safe) {
1389 // if we failed when called with !safe, try
1390 // to recover calling self with safe=true
1391 return fix_course_sortorder($categoryid, $n, true, $depth, $path);
1392 }
1393 }
5930cded 1394 }
02ebf404 1395 }
c3df0901 1396 $DB->set_field('course_categories', 'coursecount', $count, array('id'=>$categoryid));
8f0cd6ef 1397
5930cded 1398 // $n could need updating
c3df0901 1399 $max = $DB->get_field_sql("SELECT MAX(sortorder) FROM {course} WHERE category=?", array($categoryid));
814748c9 1400 if ($max > $n) {
1401 $n = $max;
1402 }
758b9a4d 1403
6bc502cc 1404 if ($categories = get_categories($categoryid)) {
1405 foreach ($categories as $category) {
f41ef63e 1406 $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path);
6bc502cc 1407 }
1408 }
8f0cd6ef 1409
39f65595 1410 return $n+1;
02ebf404 1411}
1412
d8634192 1413/**
1414 * Ensure all courses have a valid course category
1415 * useful if a category has been removed manually
1416 **/
1417function fix_coursecategory_orphans() {
c3df0901 1418 global $DB;
d8634192 1419
1420 // Note: the handling of sortorder here is arguably
1421 // open to race conditions. Hard to fix here, unlikely
1422 // to hit anyone in production.
1423
1424 $sql = "SELECT c.id, c.category, c.shortname
c3df0901 1425 FROM {course} c
1426 LEFT OUTER JOIN {course_categories} cc ON c.category=cc.id
1427 WHERE cc.id IS NULL AND c.id <> " . SITEID;
d8634192 1428
c3df0901 1429 if (!$rs = $DB->get_recordset_sql($sql)) {
1430 return;
1431 }
d8634192 1432
c3df0901 1433 if ($rs->valid()) { // we have some orphans
d8634192 1434
1435 // the "default" category is the lowest numbered...
c3df0901 1436 $default = $DB->get_field_sql("SELECT MIN(id)
1437 FROM {course_categories}");
1438 $sortorder = $DB->get_field_sql("SELECT MAX(sortorder)
1439 FROM {course}
1440 WHERE category=?", array($default));
1441
1442
1443 $DB->begin_sql();
1444 foreach ($rs as $course) {
1445 if (!$DB->set_field('course', 'category', $default, array('id'=>$course->id))
1446 or !$DB->set_field('course', 'sortorder', ++$sortorder, array('id'=>$course->id))) {
1447 $DB->rollback_sql();
1448 return;
1449 }
d8634192 1450 }
c3df0901 1451 $DB->commit_sql();
d8634192 1452 }
c3df0901 1453 $rs->close();
d8634192 1454}
1455
db4b12eb 1456/**
1457 * List of remote courses that a user has access to via MNET.
1458 * Works only on the IDP
1459 *
1460 * @uses $CFG, $USER
1461 * @return array {@link $COURSE} of course objects
1462 */
1463function get_my_remotecourses($userid=0) {
c3df0901 1464 global $DB, $USER;
db4b12eb 1465
1466 if (empty($userid)) {
1467 $userid = $USER->id;
1468 }
1469
5930cded 1470 $sql = "SELECT c.remoteid, c.shortname, c.fullname,
86dd62a7 1471 c.hostid, c.summary, c.cat_name,
1472 h.name AS hostname
c3df0901 1473 FROM {mnet_enrol_course} c
1474 JOIN {mnet_enrol_assignments} a ON c.id=a.courseid
1475 JOIN {mnet_host} h ON c.hostid=h.id
1476 WHERE a.userid=?";
db4b12eb 1477
c3df0901 1478 return $DB->get_records_sql($sql, array($userid));
db4b12eb 1479}
1480
1481/**
1482 * List of remote hosts that a user has access to via MNET.
1483 * Works on the SP
1484 *
1485 * @uses $CFG, $USER
1486 * @return array of host objects
1487 */
1488function get_my_remotehosts() {
1489 global $CFG, $USER;
1490
1491 if ($USER->mnethostid == $CFG->mnet_localhost_id) {
1492 return false; // Return nothing on the IDP
1493 }
1494 if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) {
1495 return $USER->mnet_foreign_host_array;
1496 }
1497 return false;
1498}
fbc21ae8 1499
18a97fd8 1500/**
fbc21ae8 1501 * This function creates a default separated/connected scale
1502 *
1503 * This function creates a default separated/connected scale
1504 * so there's something in the database. The locations of
1505 * strings and files is a bit odd, but this is because we
1506 * need to maintain backward compatibility with many different
1507 * existing language translations and older sites.
fbc21ae8 1508 */
02ebf404 1509function make_default_scale() {
c3df0901 1510 global $CFG, $DB;
02ebf404 1511
1512 $defaultscale = NULL;
1513 $defaultscale->courseid = 0;
1514 $defaultscale->userid = 0;
d4419d55 1515 $defaultscale->name = get_string('separateandconnected');
1516 $defaultscale->scale = get_string('postrating1', 'forum').','.
1517 get_string('postrating2', 'forum').','.
1518 get_string('postrating3', 'forum');
02ebf404 1519 $defaultscale->timemodified = time();
1520
8f0cd6ef 1521 /// Read in the big description from the file. Note this is not
02ebf404 1522 /// HTML (despite the file extension) but Moodle format text.
f191a887 1523 $parentlang = get_string('parentlanguage');
1524 if ($parentlang[0] == '[') {
1525 $parentlang = '';
1526 }
ee6e91d4 1527 if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1528 $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1529 } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
d4419d55 1530 $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
ee6e91d4 1531 } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1532 $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
d4419d55 1533 } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1534 $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
ee6e91d4 1535 } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) {
1536 $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html');
02ebf404 1537 } else {
d4419d55 1538 $file = '';
02ebf404 1539 }
1540
c3df0901 1541 $defaultscale->description = implode('', $file);
02ebf404 1542
c3df0901 1543 if ($defaultscale->id = $DB->insert_record('scale', $defaultscale)) {
1544 $DB->execute("UPDATE {forum} SET scale = ?", array($defaultscale->id));
02ebf404 1545 }
1546}
1547
fbc21ae8 1548
18a97fd8 1549/**
fbc21ae8 1550 * Returns a menu of all available scales from the site as well as the given course
1551 *
1552 * @uses $CFG
1553 * @param int $courseid The id of the course as found in the 'course' table.
7290c7fa 1554 * @return object
fbc21ae8 1555 */
02ebf404 1556function get_scales_menu($courseid=0) {
c3df0901 1557 global $DB;
02ebf404 1558
c3df0901 1559 $sql = "SELECT id, name
1560 FROM {scale}
1561 WHERE courseid = 0 or courseid = ?
02ebf404 1562 ORDER BY courseid ASC, name ASC";
c3df0901 1563 $params = array($courseid);
02ebf404 1564
c3df0901 1565 if ($scales = $DB->get_records_sql_menu($sql, $params)) {
02ebf404 1566 return $scales;
1567 }
1568
1569 make_default_scale();
1570
c3df0901 1571 return $DB->get_records_sql_menu($sql, $params);
02ebf404 1572}
1573
5baa0ad6 1574
1575
1576/**
1577 * Given a set of timezone records, put them in the database, replacing what is there
1578 *
1579 * @uses $CFG
1580 * @param array $timezones An array of timezone records
1581 */
1582function update_timezone_records($timezones) {
c3df0901 1583 global $DB;
5baa0ad6 1584
1585/// Clear out all the old stuff
b820eb8c 1586 $DB->delete_records('timezone');
5baa0ad6 1587
1588/// Insert all the new stuff
1589 foreach ($timezones as $timezone) {
a599aeeb 1590 if (is_array($timezone)) {
1591 $timezone = (object)$timezone;
1592 }
c3df0901 1593 $DB->insert_record('timezone', $timezone);
5baa0ad6 1594 }
1595}
1596
1597
df28d6c5 1598/// MODULE FUNCTIONS /////////////////////////////////////////////////
1599
18a97fd8 1600/**
fbc21ae8 1601 * Just gets a raw list of all modules in a course
1602 *
fbc21ae8 1603 * @param int $courseid The id of the course as found in the 'course' table.
7290c7fa 1604 * @return object
fbc21ae8 1605 */
9fa49e22 1606function get_course_mods($courseid) {
c3df0901 1607 global $DB;
9fa49e22 1608
3a11c548 1609 if (empty($courseid)) {
1610 return false; // avoid warnings
1611 }
1612
c3df0901 1613 return $DB->get_records_sql("SELECT cm.*, m.name as modname
1614 FROM {modules} m, {course_modules} cm
1615 WHERE cm.course = ? AND cm.module = m.id AND m.visible = 1",
1616 array($courseid)); // no disabled mods
9fa49e22 1617}
1618
fbc21ae8 1619
18a97fd8 1620/**
f9d5371b 1621 * Given an id of a course module, finds the coursemodule description
fbc21ae8 1622 *
f9d5371b 1623 * @param string $modulename name of module type, eg. resource, assignment,...
1624 * @param int $cmid course module id (id in course_modules table)
1625 * @param int $courseid optional course id for extra validation
1626 * @return object course module instance with instance and module name
1627 */
1628function get_coursemodule_from_id($modulename, $cmid, $courseid=0) {
c3df0901 1629 global $DB;
f9d5371b 1630
c3df0901 1631 $params = array();
d251907c 1632 $courseselect = "";
f9d5371b 1633
c3df0901 1634 if ($courseid) {
1635 $courseselect = "cm.course = :courseid AND ";
1636 $params['courseid'] = $courseid;
d251907c 1637 }
c3df0901 1638 $params['cmid'] = $cmid;
1639 $params['modulename'] = $modulename;
1640
1641 return $DB->get_record_sql("SELECT cm.*, m.name, md.name as modname
1642 FROM {course_modules} cm, {modules} md, {".$modulename."} m
1643 WHERE $courseselect
1644 cm.id = :cmid AND
1645 cm.instance = m.id AND
1646 md.name = :modulename AND
1647 md.id = cm.module", $params);
f9d5371b 1648}
1649
1650/**
1651 * Given an instance number of a module, finds the coursemodule description
1652 *
1653 * @param string $modulename name of module type, eg. resource, assignment,...
1654 * @param int $instance module instance number (id in resource, assignment etc. table)
1655 * @param int $courseid optional course id for extra validation
1656 * @return object course module instance with instance and module name
fbc21ae8 1657 */
b63c0ee5 1658function get_coursemodule_from_instance($modulename, $instance, $courseid=0) {
c3df0901 1659 global $DB;
df28d6c5 1660
c3df0901 1661 $params = array();
d251907c 1662 $courseselect = "";
df28d6c5 1663
c3df0901 1664 if ($courseid) {
1665 $courseselect = "cm.course = :courseid AND ";
1666 $params['courseid'] = $courseid;
d251907c 1667 }
c3df0901 1668 $params['instance'] = $instance;
1669 $params['modulename'] = $modulename;
1670
1671 return $DB->get_record_sql("SELECT cm.*, m.name, md.name as modname
1672 FROM {course_modules} cm, {modules} md, {".$modulename."} m
1673 WHERE $courseselect
1674 cm.instance = m.id AND
8a445484 1675 md.name = :modulename AND
c3df0901 1676 md.id = cm.module AND
8a445484 1677 m.id = :instance", $params);
df28d6c5 1678
1679}
1680
dd97c328 1681/**
1682 * Returns all course modules of given activity in course
1683 * @param string $modulename (forum, quiz, etc.)
1684 * @param int $courseid
1685 * @param string $extrafields extra fields starting with m.
1686 * @return array of cm objects, false if not found or error
1687 */
1688function get_coursemodules_in_course($modulename, $courseid, $extrafields='') {
c3df0901 1689 global $DB;
dd97c328 1690
1691 if (!empty($extrafields)) {
1692 $extrafields = ", $extrafields";
1693 }
c3df0901 1694 $params = array();
1695 $params['courseid'] = $courseid;
1696 $params['modulename'] = $modulename;
1697
1698
1699 return $DB->get_records_sql("SELECT cm.*, m.name, md.name as modname $extrafields
1700 FROM {course_modules} cm, {modules} md, {".$modulename."} m
1701 WHERE cm.course = :courseid AND
1702 cm.instance = m.id AND
1703 md.name = :modulename AND
e0985504 1704 md.id = cm.module", $params);
dd97c328 1705}
ac0b1a19 1706
185cfb09 1707/**
1708 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1709 *
1710 * Returns an array of all the active instances of a particular
1711 * module in given courses, sorted in the order they are defined
ac0b1a19 1712 * in the course. Returns an empty array on any errors.
185cfb09 1713 *
ac0b1a19 1714 * The returned objects includle the columns cw.section, cm.visible,
1715 * cm.groupmode and cm.groupingid, cm.groupmembersonly, and are indexed by cm.id.
1716 *
1717 * @param string $modulename The name of the module to get instances for
1718 * @param array $courses an array of course objects.
1719 * @return array of module instance objects, including some extra fields from the course_modules
1720 * and course_sections tables, or an empty array if an error occurred.
185cfb09 1721 */
00e12c73 1722function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
c3df0901 1723 global $CFG, $DB;
ac0b1a19 1724
1725 $outputarray = array();
1726
185cfb09 1727 if (empty($courses) || !is_array($courses) || count($courses) == 0) {
ac0b1a19 1728 return $outputarray;
185cfb09 1729 }
ac0b1a19 1730
c3df0901 1731 list($coursessql, $params) = $DB->get_in_or_equal(array_keys($courses), SQL_PARAMS_NAMED, 'c0');
1732 $params['modulename'] = $modulename;
1733
1734 if (!$rawmods = $DB->get_records_sql("SELECT cm.id AS coursemodule, m.*, cw.section, cm.visible AS visible,
1735 cm.groupmode, cm.groupingid, cm.groupmembersonly
1736 FROM {course_modules} cm, {course_sections} cw, {modules} md,
1737 {".$modulename."} m
1738 WHERE cm.course $coursessql AND
1739 cm.instance = m.id AND
1740 cm.section = cw.id AND
1741 md.name = :modulename AND
1742 md.id = cm.module", $params)) {
ac0b1a19 1743 return $outputarray;
185cfb09 1744 }
1745
ac0b1a19 1746 require_once($CFG->dirroot.'/course/lib.php');
185cfb09 1747
1748 foreach ($courses as $course) {
ac0b1a19 1749 $modinfo = get_fast_modinfo($course, $userid);
fea43a7f 1750
ac0b1a19 1751 if (empty($modinfo->instances[$modulename])) {
185cfb09 1752 continue;
1753 }
ac0b1a19 1754
1755 foreach ($modinfo->instances[$modulename] as $cm) {
1756 if (!$includeinvisible and !$cm->uservisible) {
1757 continue;
1758 }
1759 if (!isset($rawmods[$cm->id])) {
1760 continue;
185cfb09 1761 }
ac0b1a19 1762 $instance = $rawmods[$cm->id];
1763 if (!empty($cm->extra)) {
1764 $instance->extra = urlencode($cm->extra); // bc compatibility
1765 }
1766 $outputarray[] = $instance;
185cfb09 1767 }
1768 }
1769
1770 return $outputarray;
185cfb09 1771}
fbc21ae8 1772
18a97fd8 1773/**
3d96cba7 1774 * Returns an array of all the active instances of a particular module in a given course,
1775 * sorted in the order they are defined.
fbc21ae8 1776 *
1777 * Returns an array of all the active instances of a particular
1778 * module in a given course, sorted in the order they are defined
3d96cba7 1779 * in the course. Returns an empty array on any errors.
1780 *
1781 * The returned objects includle the columns cw.section, cm.visible,
ac0b1a19 1782 * cm.groupmode and cm.groupingid, cm.groupmembersonly, and are indexed by cm.id.
fbc21ae8 1783 *
3d96cba7 1784 * @param string $modulename The name of the module to get instances for
ac0b1a19 1785 * @param object $course The course obect.
3d96cba7 1786 * @return array of module instance objects, including some extra fields from the course_modules
1787 * and course_sections tables, or an empty array if an error occurred.
fbc21ae8 1788 */
00e12c73 1789function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
ac0b1a19 1790 return get_all_instances_in_courses($modulename, array($course->id => $course), $userid, $includeinvisible);
df28d6c5 1791}
1792
9fa49e22 1793
18a97fd8 1794/**
fbc21ae8 1795 * Determine whether a module instance is visible within a course
1796 *
1797 * Given a valid module object with info about the id and course,
1798 * and the module's type (eg "forum") returns whether the object
dd97c328 1799 * is visible or not, groupmembersonly visibility not tested
fbc21ae8 1800 *
613bbd7c 1801 * @param $moduletype Name of the module eg 'forum'
1802 * @param $module Object which is the instance of the module
7290c7fa 1803 * @return bool
fbc21ae8 1804 */
580f2fbc 1805function instance_is_visible($moduletype, $module) {
c3df0901 1806 global $DB;
580f2fbc 1807
2b49ae96 1808 if (!empty($module->id)) {
d251907c 1809 $params = array('courseid'=>$module->course, 'moduletype'=>$moduletype, 'moduleid'=>$module->id);
c3df0901 1810 if ($records = $DB->get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.groupmembersonly, cm.course
1811 FROM {course_modules} cm, {modules} m
1812 WHERE cm.course = :courseid AND
1813 cm.module = m.id AND
1814 m.name = :moduletype AND
f93ea222 1815 cm.instance = :moduleid", $params)) {
5930cded 1816
2b49ae96 1817 foreach ($records as $record) { // there should only be one - use the first one
dd97c328 1818 return $record->visible;
2b49ae96 1819 }
580f2fbc 1820 }
1821 }
580f2fbc 1822 return true; // visible by default!
1823}
1824
dd97c328 1825/**
1826 * Determine whether a course module is visible within a course,
1827 * this is different from instance_is_visible() - faster and visibility for user
1828 *
1829 * @param object $cm object
1830 * @param int $userid empty means current user
1831 * @return bool
1832 */
1833function coursemodule_visible_for_user($cm, $userid=0) {
1834 global $USER;
1835
1836 if (empty($cm->id)) {
1837 debugging("Incorrect course module parameter!", DEBUG_DEVELOPER);
1838 return false;
1839 }
1840 if (empty($userid)) {
1841 $userid = $USER->id;
1842 }
1843 if (!$cm->visible and !has_capability('moodle/course:viewhiddenactivities', get_context_instance(CONTEXT_MODULE, $cm->id), $userid)) {
1844 return false;
1845 }
1846 return groups_course_module_visible($cm, $userid);
1847}
1848
a3fb1c45 1849
1850
1851
9fa49e22 1852/// LOG FUNCTIONS /////////////////////////////////////////////////////
1853
1854
18a97fd8 1855/**
fbc21ae8 1856 * Add an entry to the log table.
1857 *
1858 * Add an entry to the log table. These are "action" focussed rather
1859 * than web server hits, and provide a way to easily reconstruct what
1860 * any particular student has been doing.
1861 *
1862 * @uses $CFG
1863 * @uses $USER
fbc21ae8 1864 * @uses $REMOTE_ADDR
1865 * @uses SITEID
89dcb99d 1866 * @param int $courseid The course id
fbc21ae8 1867 * @param string $module The module name - e.g. forum, journal, resource, course, user etc
f7664880 1868 * @param string $action 'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
fbc21ae8 1869 * @param string $url The file and parameters used to see the results of the action
1870 * @param string $info Additional description information
1871 * @param string $cm The course_module->id if there is one
1872 * @param string $user If log regards $user other than $USER
1873 */
d4419d55 1874function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
e8395a09 1875 // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1876 // This is for a good reason: it is the most frequently used DB update function,
1877 // so it has been optimised for speed.
f33e1ed4 1878 global $DB, $CFG, $USER;
9fa49e22 1879
7a5b1fc5 1880 if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default
f78b3c34 1881 $cm = 0;
1882 }
1883
3d94772d 1884 if ($user) {
1885 $userid = $user;
1886 } else {
cb80265b 1887 if (!empty($USER->realuser)) { // Don't log
3d94772d 1888 return;
1889 }
d4419d55 1890 $userid = empty($USER->id) ? '0' : $USER->id;
9fa49e22 1891 }
1892
fcaff7ff 1893 $REMOTE_ADDR = getremoteaddr();
1894
9fa49e22 1895 $timenow = time();
ac1ba33e 1896 $info = $info;
10a760b9 1897 if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1898 $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
1899 }
853df85e 1900
6c5a2108 1901 // Restrict length of log lines to the space actually available in the
1902 // database so that it doesn't cause a DB error. Log a warning so that
1903 // developers can avoid doing things which are likely to cause this on a
1904 // routine basis.
ac1ba33e 1905 $tl = textlib_get_instance();
6c5a2108 1906 if(!empty($info) && $tl->strlen($info)>255) {
ac1ba33e 1907 $info = $tl->substr($info,0,252).'...';
6c5a2108 1908 debugging('Warning: logged very long info',DEBUG_DEVELOPER);
1909 }
ac1ba33e 1910
6c5a2108 1911 // If the 100 field size is changed, also need to alter print_log in course/lib.php
1912 if(!empty($url) && $tl->strlen($url)>100) {
1913 $url=$tl->substr($url,0,97).'...';
1914 debugging('Warning: logged very long URL',DEBUG_DEVELOPER);
1915 }
8a445484 1916
f33e1ed4 1917 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->logwrites++;};
853df85e 1918
8b497bbc 1919 if ($CFG->type = 'oci8po') {
f33e1ed4 1920 if ($info == '') {
8b497bbc 1921 $info = ' ';
1922 }
1923 }
f33e1ed4 1924 $log = array('time'=>$timenow, 'userid'=>$userid, 'course'=>$courseid, 'ip'=>$REMOTE_ADDR, 'module'=>$module,
1925 'cmid'=>$cm, 'action'=>$action, 'url'=>$url, 'info'=>$info);
1926 $result = $DB->insert_record_raw('log', $log, false);
ebc3bd2b 1927
9f064546 1928 // MDL-11893, alert $CFG->supportemail if insert into log failed
f33e1ed4 1929 if (!$result and $CFG->supportemail and empty($CFG->noemailever)) {
1930 // email_to_user is not usable because email_to_user tries to write to the logs table,
1931 // and this will get caught in an infinite loop, if disk is full
9f064546 1932 $site = get_site();
1933 $subject = 'Insert into log failed at your moodle site '.$site->fullname;
0fdf06cd 1934 $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";
f33e1ed4 1935 $message .= "The failed query parameters are:\n\n" . var_export($log, true);
0fdf06cd 1936
f33e1ed4 1937 $lasttime = get_config('admin', 'lastloginserterrormail');
1938 if(empty($lasttime) || time() - $lasttime > 60*60*24) { // limit to 1 email per day
1939 mail($CFG->supportemail, $subject, $message);
1940 set_config('lastloginserterrormail', time(), 'admin');
58538527 1941 }
9f064546 1942 }
1943
252720c4 1944 if (!$result) {
1945 debugging('Error: Could not insert a new entry to the Moodle log', DEBUG_ALL);
8f0cd6ef 1946 }
cb80265b 1947
341b5ed2 1948}
1949
1950/**
1951 * Store user last access times - called when use enters a course or site
1952 *
341b5ed2 1953 * @param int $courseid, empty means site
1954 * @return void
1955 */
1956function user_accesstime_log($courseid=0) {
f33e1ed4 1957 global $USER, $CFG, $DB;
341b5ed2 1958
1959 if (!isloggedin() or !empty($USER->realuser)) {
1960 // no access tracking
1961 return;
1962 }
1963
1964 if (empty($courseid)) {
1965 $courseid = SITEID;
1966 }
1967
1968 $timenow = time();
1969
1970/// Store site lastaccess time for the current user
1971 if ($timenow - $USER->lastaccess > LASTACCESS_UPDATE_SECS) {
1972 /// Update $USER->lastaccess for next checks
1973 $USER->lastaccess = $timenow;
341b5ed2 1974
f33e1ed4 1975 $last = new object();
1976 $last->id = $USER->id;
1977 $last->lastip = getremoteaddr();
1978 $last->lastaccess = $timenow;
1979
1980 if (!$DB->update_record_raw('user', $last)) {
1981 debugging('Error: Could not update global user lastaccess information', DEBUG_ALL); // Don't throw an error
341b5ed2 1982 }
1983 }
1984
1985 if ($courseid == SITEID) {
1986 /// no user_lastaccess for frontpage
1987 return;
1988 }
cb8aaedf 1989
341b5ed2 1990/// Store course lastaccess times for the current user
1991 if (empty($USER->currentcourseaccess[$courseid]) or ($timenow - $USER->currentcourseaccess[$courseid] > LASTACCESS_UPDATE_SECS)) {
341b5ed2 1992
f33e1ed4 1993 $lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', array('userid'=>$USER->id, 'courseid'=>$courseid));
341b5ed2 1994
f33e1ed4 1995 if ($lastaccess === false) {
1996 // Update course lastaccess for next checks
1997 $USER->currentcourseaccess[$courseid] = $timenow;
1998
1999 $last = new object();
2000 $last->userid = $USER->id;
2001 $last->courseid = $courseid;
2002 $last->timeaccess = $timenow;
2003 if (!$DB->insert_record_raw('user_lastaccess', $last, false)) {
2004 debugging('Error: Could not insert course user lastaccess information', DEBUG_ALL); // Don't throw an error
edb15b8f 2005 }
d251907c 2006
f33e1ed4 2007 } else if ($timenow - $lastaccess < LASTACCESS_UPDATE_SECS) {
2008 // no need to update now, it was updated recently in concurrent login ;-)
341b5ed2 2009
f33e1ed4 2010 } else {
2011 // Update course lastaccess for next checks
2012 $USER->currentcourseaccess[$courseid] = $timenow;
2013
2014 if (!$DB->set_field('user_lastaccess', 'timeaccess', $timenow, array('userid'=>$USER->id, 'courseid'=>$courseid))) {
2015 debugging('Error: Could not update course user lastacess information'); // Don't throw an error
114176a2 2016 }
3d94772d 2017 }
8f0cd6ef 2018 }
9fa49e22 2019}
2020
18a97fd8 2021/**
fbc21ae8 2022 * Select all log records based on SQL criteria
2023 *
fbc21ae8 2024 * @param string $select SQL select criteria
c3df0901 2025 * @param array $params named sql type params
fbc21ae8 2026 * @param string $order SQL order by clause to sort the records returned
2027 * @param string $limitfrom ?
2028 * @param int $limitnum ?
2029 * @param int $totalcount Passed in by reference.
7290c7fa 2030 * @return object
fbc21ae8 2031 * @todo Finish documenting this function
2032 */
c3df0901 2033function get_logs($select, array $params=null, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
2034 global $DB;
9fa49e22 2035
519d369f 2036 if ($order) {
c3df0901 2037 $order = "ORDER BY $order";
2038 }
2039
2040 $selectsql = "";
2041 $countsql = "";
2042
2043 if ($select) {
2044 $select = "WHERE $select";
519d369f 2045 }
2046
c3df0901 2047 $sql = "SELECT COUNT(*)
2048 FROM {log} l
2049 $select";
2050
2051 $totalcount = $DB->count_records_sql($sql, $params);
a2ddd957 2052
c3df0901 2053 $sql = "SELECT l.*, u.firstname, u.lastname, u.picture
d251907c 2054 FROM {log} l
c3df0901 2055 LEFT JOIN {user} u ON l.userid = u.id
d251907c 2056 $select
c3df0901 2057 $order";
519d369f 2058
c3df0901 2059 return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum) ;
9fa49e22 2060}
2061
519d369f 2062
18a97fd8 2063/**
fbc21ae8 2064 * Select all log records for a given course and user
2065 *
2066 * @uses $CFG
2f87145b 2067 * @uses DAYSECS
fbc21ae8 2068 * @param int $userid The id of the user as found in the 'user' table.
2069 * @param int $courseid The id of the course as found in the 'course' table.
2070 * @param string $coursestart ?
2071 * @todo Finish documenting this function
2072 */
9fa49e22 2073function get_logs_usercourse($userid, $courseid, $coursestart) {
c3df0901 2074 global $DB;
9fa49e22 2075
c3df0901 2076 $params = array();
2077
2078 $courseselect = '';
da0c90c3 2079 if ($courseid) {
c3df0901 2080 $courseselect = "AND course = :courseid";
d251907c 2081 $params['courseid'] = $courseid;
da0c90c3 2082 }
c3df0901 2083 $params['userid'] = $userid;
d251907c 2084 $params['coursestart'] = $coursestart;
da0c90c3 2085
c3df0901 2086 return $DB->get_records_sql("SELECT FLOOR((time - :coursestart)/". DAYSECS .") AS day, COUNT(*) AS num
2087 FROM {log}
2088 WHERE userid = :userid
2089 AND time > :coursestart $courseselect
2090 GROUP BY FLOOR((time - :coursestart)/". DAYSECS .")", $params);
9fa49e22 2091}
2092
18a97fd8 2093/**
fbc21ae8 2094 * Select all log records for a given course, user, and day
2095 *
2096 * @uses $CFG
2f87145b 2097 * @uses HOURSECS
fbc21ae8 2098 * @param int $userid The id of the user as found in the 'user' table.
2099 * @param int $courseid The id of the course as found in the 'course' table.
2100 * @param string $daystart ?
7290c7fa 2101 * @return object
fbc21ae8 2102 * @todo Finish documenting this function
2103 */
9fa49e22 2104function get_logs_userday($userid, $courseid, $daystart) {
c3df0901 2105 global $DB;
2106
2107 $params = array();
9fa49e22 2108
c3df0901 2109 $courseselect = '';
7e4a6488 2110 if ($courseid) {
c3df0901 2111 $courseselect = "AND course = :courseid";
d251907c 2112 $params['courseid'] = $courseid;
7e4a6488 2113 }
c3df0901 2114 $params['userid'] = $userid;
d251907c 2115 $params['daystart'] = $daystart;
7e4a6488 2116
c3df0901 2117 return $DB->get_records_sql("SELECT FLOOR((time - :daystart)/". HOURSECS .") AS hour, COUNT(*) AS num
2118 FROM {log}
2119 WHERE userid = :userid
2120 AND time > :daystart $courseselect
2121 GROUP BY FLOOR((time - :daystart)/". HOURSECS .") ");
9fa49e22 2122}
2123
b4bac9b6 2124/**
2125 * Returns an object with counts of failed login attempts
2126 *
8f0cd6ef 2127 * Returns information about failed login attempts. If the current user is
2128 * an admin, then two numbers are returned: the number of attempts and the
b4bac9b6 2129 * number of accounts. For non-admins, only the attempts on the given user
2130 * are shown.
2131 *
fbc21ae8 2132 * @param string $mode Either 'admin', 'teacher' or 'everybody'
2133 * @param string $username The username we are searching for
2134 * @param string $lastlogin The date from which we are searching
2135 * @return int
b4bac9b6 2136 */
b4bac9b6 2137function count_login_failures($mode, $username, $lastlogin) {
c3df0901 2138 global $DB;
b4bac9b6 2139
c3df0901 2140 $params = array('mode'=>$mode, 'username'=>$username, 'lastlogin'=>$lastlogin);
2141 $select = "module='login' AND action='error' AND time > :lastlogin";
2142
2143 $count = new object();
b4bac9b6 2144
12d06877 2145 if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM))) { // Return information about all accounts
c3df0901 2146 if ($count->attempts = $DB->count_records_select('log', $select, $params)) {
2147 $count->accounts = $DB->count_records_select('log', $select, $params, 'COUNT(DISTINCT info)');
b4bac9b6 2148 return $count;
2149 }
9407d456 2150 } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) {
c3df0901 2151 if ($count->attempts = $DB->count_records_select('log', "$select AND info = :username", $params)) {
b4bac9b6 2152 return $count;
2153 }
2154 }
2155 return NULL;
2156}
2157
2158
a3fb1c45 2159/// GENERAL HELPFUL THINGS ///////////////////////////////////
2160
18a97fd8 2161/**
fbc21ae8 2162 * Dump a given object's information in a PRE block.
2163 *
2164 * Mostly just used for debugging.
2165 *
2166 * @param mixed $object The data to be printed
fbc21ae8 2167 */
a3fb1c45 2168function print_object($object) {
1aa7b31d 2169 echo '<pre class="notifytiny">' . htmlspecialchars(print_r($object,true)) . '</pre>';
a3fb1c45 2170}
2171
624a690b 2172/**
3511647c 2173 * Check whether a course is visible through its parents
bfbfdb53 2174 * path.
3511647c 2175 *
2176 * Notes:
2177 *
2178 * - All we need from the course is ->category. _However_
2179 * if the course object has a categorypath property,
2180 * we'll save a dbquery
2181 *
2182 * - If we return false, you'll still need to check if
2183 * the user can has the 'moodle/category:visibility'
2184 * capability...
2185 *
bfbfdb53 2186 * - Will generate 2 DB calls.
3511647c 2187 *
2188 * - It does have a small local cache, however...
2189 *
2190 * - Do NOT call this over many courses as it'll generate
2191 * DB traffic. Instead, see what get_my_courses() does.
2192 *
2193 * @param mixed $object A course object
2194 * @return bool
2195 */
0986271b 2196function course_parent_visible($course = null) {
c3df0901 2197 global $CFG, $DB;
3511647c 2198 //return true;
2199 static $mycache;
fa145ae1 2200
3511647c 2201 if (!is_object($course)) {
418b4e5a 2202 return true;
2203 }
2204 if (!empty($CFG->allowvisiblecoursesinhiddencategories)) {
2205 return true;
2206 }
0986271b 2207
3511647c 2208 if (!isset($mycache)) {
2209 $mycache = array();
2210 } else {
2211 // cast to force assoc array
bfbfdb53 2212 $k = (string)$course->category;
3511647c 2213 if (isset($mycache[$k])) {
2214 return $mycache[$k];
2215 }
0986271b 2216 }
5930cded 2217
3511647c 2218 if (isset($course->categorypath)) {
2219 $path = $course->categorypath;
2220 } else {
c3df0901 2221 $path = $DB->get_field('course_categories', 'path', array('id'=>$course->category));
824f1c40 2222 }
3511647c 2223 $catids = substr($path,1); // strip leading slash
2224 $catids = str_replace('/',',',$catids);
824f1c40 2225
3511647c 2226 $sql = "SELECT MIN(visible)
c3df0901 2227 FROM {course_categories}
2228 WHERE id IN ($catids)";
2229 $vis = $DB->get_field_sql($sql);
5930cded 2230
3511647c 2231 // cast to force assoc array
2232 $k = (string)$course->category;
2233 $mycache[$k] = $vis;
2234
2235 return $vis;
0986271b 2236}
2237
62d4e774 2238/**
5930cded 2239 * This function is the official hook inside XMLDB stuff to delegate its debug to one
62d4e774 2240 * external function.
2241 *
2242 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
2243 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
2244 *
2245 * @param $message string contains the error message
2246 * @param $object object XMLDB object that fired the debug
2247 */
2248function xmldb_debug($message, $object) {
2249
92b564f4 2250 debugging($message, DEBUG_DEVELOPER);
62d4e774 2251}
2252
49860445 2253/**
2254 * true or false function to see if user can create any courses at all
2255 * @return bool
2256 */
2257function user_can_create_courses() {
2258 global $USER;
2259 // if user has course creation capability at any site or course cat, then return true;
5930cded 2260
12d06877 2261 if (has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM))) {
5930cded 2262 return true;
49860445 2263 } else {
5930cded 2264 return (bool) count(get_creatable_categories());
49860445 2265 }
5930cded 2266
49860445 2267}
2268
2269/**
624a690b 2270 * Get the list of categories the current user can create courses in
49860445 2271 * @return array
2272 */
2273function get_creatable_categories() {
c3df0901 2274 global $DB;
5930cded 2275
49860445 2276 $creatablecats = array();
c3df0901 2277 if ($cats = $DB->get_records('course_categories')) {
49860445 2278 foreach ($cats as $cat) {
2279 if (has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $cat->id))) {
2280 $creatablecats[$cat->id] = $cat->name;
2281 }
2282 }
2283 }
2284 return $creatablecats;
2285}
2286
03517306 2287?>