Removed comment that no longer applies
[moodle.git] / lib / datalib.php
CommitLineData
6078ba30 1<?php // $Id$
7cf1c7bd 2
3/**
4 * Library of functions for database manipulation.
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
7cf1c7bd 10 * @version $Id$
89dcb99d 11 * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
7cf1c7bd 12 * @package moodlecore
13 */
14
df28d6c5 15
11a052a6 16/**
17 * Escape all dangerous characters in a data record
18 *
19 * $dataobject is an object containing needed data
20 * Run over each field exectuting addslashes() function
21 * to escape SQL unfriendly characters (e.g. quotes)
22 * Handy when writing back data read from the database
23 *
24 * @param $dataobject Object containing the database record
25 * @return object Same object with neccessary characters escaped
26 */
27function addslashes_object( $dataobject ) {
28 $a = get_object_vars( $dataobject);
29 foreach ($a as $key=>$value) {
30 $a[$key] = addslashes( $value );
31 }
32 return (object)$a;
33}
0892f7bd 34
df28d6c5 35/// USER DATABASE ////////////////////////////////////////////////
36
18a97fd8 37/**
fbc21ae8 38 * Returns $user object of the main admin user
20aeb4b8 39 * primary admin = admin with lowest role_assignment id among admins
fbc21ae8 40 * @uses $CFG
41 * @return object(admin) An associative array representing the admin user.
fbc21ae8 42 */
df28d6c5 43function get_admin () {
df28d6c5 44
45 global $CFG;
46
47 if ( $admins = get_admins() ) {
48 foreach ($admins as $admin) {
8f0cd6ef 49 return $admin; // ie the first one
df28d6c5 50 }
51 } else {
52 return false;
53 }
54}
55
18a97fd8 56/**
fbc21ae8 57 * Returns list of all admins
58 *
59 * @uses $CFG
7290c7fa 60 * @return object
fbc21ae8 61 */
df28d6c5 62function get_admins() {
df28d6c5 63
64 global $CFG;
20aeb4b8 65
66 $context = get_context_instance(CONTEXT_SYSTEM, SITEID);
df28d6c5 67
41f6ed56 68 return get_users_by_capability($context, 'moodle/site:doanything', 'u.*, ra.id as adminid', 'ra.id ASC'); // only need first one
20aeb4b8 69
df28d6c5 70}
71
72
b61efafb 73function get_courses_in_metacourse($metacourseid) {
74 global $CFG;
75
5f37b628 76 $sql = "SELECT c.id,c.shortname,c.fullname FROM {$CFG->prefix}course c, {$CFG->prefix}course_meta mc WHERE mc.parent_course = $metacourseid
5afa0de6 77 AND mc.child_course = c.id ORDER BY c.shortname";
b61efafb 78
79 return get_records_sql($sql);
80}
81
82function get_courses_notin_metacourse($metacourseid,$count=false) {
83
84 global $CFG;
85
b61efafb 86 if ($count) {
87 $sql = "SELECT COUNT(c.id)";
c44d5d42 88 } else {
b61efafb 89 $sql = "SELECT c.id,c.shortname,c.fullname";
90 }
178ccd11 91
ffed6bf3 92 $alreadycourses = get_courses_in_metacourse($metacourseid);
93
c44d5d42 94 $sql .= " FROM {$CFG->prefix}course c WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).")
5afa0de6 95 AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1 ".((empty($count)) ? " ORDER BY c.shortname" : "");
96
b61efafb 97 return get_records_sql($sql);
98}
99
493cde24 100function count_courses_notin_metacourse($metacourseid) {
101 global $CFG;
102
103 $alreadycourses = get_courses_in_metacourse($metacourseid);
104
69cd298a 105 $sql = "SELECT COUNT(c.id) AS notin FROM {$CFG->prefix}course c
493cde24 106 WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).")
107 AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1";
108
69cd298a 109 if (!$count = get_record_sql($sql)) {
493cde24 110 return 0;
111 }
112
69cd298a 113 return $count->notin;
493cde24 114}
115
900df8b6 116/**
fbc21ae8 117 * Search through course users
118 *
119 * If $coursid specifies the site course then this function searches
120 * through all undeleted and confirmed users
121 *
122 * @uses $CFG
123 * @uses SITEID
124 * @param int $courseid The course in question.
125 * @param int $groupid The group in question.
126 * @param string $searchtext ?
127 * @param string $sort ?
128 * @param string $exceptions ?
7290c7fa 129 * @return object
fbc21ae8 130 */
900df8b6 131function search_users($courseid, $groupid, $searchtext, $sort='', $exceptions='') {
132 global $CFG;
0720313b 133
29daf3a0 134 $LIKE = sql_ilike();
135 $fullname = sql_fullname('u.firstname', 'u.lastname');
8f0cd6ef 136
900df8b6 137 if (!empty($exceptions)) {
d4419d55 138 $except = ' AND u.id NOT IN ('. $exceptions .') ';
900df8b6 139 } else {
140 $except = '';
141 }
2700d113 142
900df8b6 143 if (!empty($sort)) {
d4419d55 144 $order = ' ORDER BY '. $sort;
900df8b6 145 } else {
146 $order = '';
147 }
8f0cd6ef 148
d4419d55 149 $select = 'u.deleted = \'0\' AND u.confirmed = \'1\'';
2700d113 150
222ac91b 151 if (!$courseid or $courseid == SITEID) {
2700d113 152 return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
153 FROM {$CFG->prefix}user u
154 WHERE $select
900df8b6 155 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
2700d113 156 $except $order");
8f0cd6ef 157 } else {
2700d113 158
900df8b6 159 if ($groupid) {
f3f7610c 160//TODO:check. Remove group DB dependencies.
900df8b6 161 return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
8f0cd6ef 162 FROM {$CFG->prefix}user u,
f3f7610c
ML
163 ".groups_members_from_sql()."
164 WHERE $select AND ".groups_members_where_sql($groupid, 'u.id')."
900df8b6 165 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
166 $except $order");
167 } else {
ea8158c1 168 $context = get_context_instance(CONTEXT_COURSE, $courseid);
169 $contextlists = get_related_contexts_string($context);
170 $users = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
8f0cd6ef 171 FROM {$CFG->prefix}user u,
ea8158c1 172 {$CFG->prefix}role_assignments ra
173 WHERE $select AND ra.contextid $contextlists AND ra.userid = u.id
900df8b6 174 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
ea8158c1 175 $except $order");
900df8b6 176 }
ea8158c1 177 return $users;
900df8b6 178 }
df28d6c5 179}
180
2700d113 181
18a97fd8 182/**
fbc21ae8 183 * Returns a list of all site users
184 * Obsolete, just calls get_course_users(SITEID)
185 *
186 * @uses SITEID
c6d15803 187 * @deprecated Use {@link get_course_users()} instead.
fbc21ae8 188 * @param string $fields A comma separated list of fields to be returned from the chosen table.
7290c7fa 189 * @return object|false {@link $USER} records or false if error.
fbc21ae8 190 */
d4419d55 191function get_site_users($sort='u.lastaccess DESC', $fields='*', $exceptions='') {
2d0b30a0 192
65ee9c16 193 return get_course_users(SITEID, $sort, $exceptions, $fields);
2d0b30a0 194}
195
9fa49e22 196
18a97fd8 197/**
fbc21ae8 198 * Returns a subset of users
199 *
200 * @uses $CFG
7290c7fa 201 * @param bool $get If false then only a count of the records is returned
fbc21ae8 202 * @param string $search A simple string to search for
7290c7fa 203 * @param bool $confirmed A switch to allow/disallow unconfirmed users
fbc21ae8 204 * @param array(int) $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
205 * @param string $sort A SQL snippet for the sorting criteria to use
206 * @param string $firstinitial ?
207 * @param string $lastinitial ?
208 * @param string $page ?
209 * @param string $recordsperpage ?
210 * @param string $fields A comma separated list of fields to be returned from the chosen table.
7290c7fa 211 * @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 212 */
d4419d55 213function get_users($get=true, $search='', $confirmed=false, $exceptions='', $sort='firstname ASC',
36075e09 214 $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*') {
18a97fd8 215
216 global $CFG;
36075e09 217
218 if ($get && !$recordsperpage) {
219 debugging('Call to get_users with $get = true no $recordsperpage limit. ' .
220 'On large installations, this will probably cause an out of memory error. ' .
221 'Please think again and change your code so that it does not try to ' .
03517306 222 'load so much data into memory.', DEBUG_DEVELOPER);
36075e09 223 }
18a97fd8 224
29daf3a0 225 $LIKE = sql_ilike();
226 $fullname = sql_fullname();
e384fb7b 227
e8e0bb2d 228 $select = 'username <> \'guest\' AND deleted = 0';
488acd1b 229
0044147e 230 if (!empty($search)){
231 $search = trim($search);
488acd1b 232 $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
e384fb7b 233 }
234
5a741655 235 if ($confirmed) {
d4419d55 236 $select .= ' AND confirmed = \'1\' ';
5a741655 237 }
238
239 if ($exceptions) {
d4419d55 240 $select .= ' AND id NOT IN ('. $exceptions .') ';
5a741655 241 }
242
488acd1b 243 if ($firstinitial) {
d4419d55 244 $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\'';
8f0cd6ef 245 }
488acd1b 246 if ($lastinitial) {
d4419d55 247 $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\'';
8f0cd6ef 248 }
488acd1b 249
5a741655 250 if ($get) {
36075e09 251 return get_records_select('user', $select, $sort, $fields, $page, $recordsperpage);
5a741655 252 } else {
36075e09 253 return count_records_select('user', $select);
5a741655 254 }
9fa49e22 255}
256
5a741655 257
18a97fd8 258/**
fbc21ae8 259 * shortdesc (optional)
260 *
261 * longdesc
262 *
263 * @uses $CFG
264 * @param string $sort ?
265 * @param string $dir ?
266 * @param int $categoryid ?
267 * @param int $categoryid ?
268 * @param string $search ?
269 * @param string $firstinitial ?
270 * @param string $lastinitial ?
7290c7fa 271 * @returnobject {@link $USER} records
fbc21ae8 272 * @todo Finish documenting this function
273 */
274
36075e09 275function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,
03d820c7 276 $search='', $firstinitial='', $lastinitial='', $remotewhere='') {
488acd1b 277
9fa49e22 278 global $CFG;
31fefa63 279
29daf3a0 280 $LIKE = sql_ilike();
281 $fullname = sql_fullname();
c2a96d6b 282
e8e0bb2d 283 $select = "deleted <> '1'";
488acd1b 284
0044147e 285 if (!empty($search)) {
286 $search = trim($search);
488acd1b 287 $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
288 }
289
290 if ($firstinitial) {
d4419d55 291 $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\' ';
488acd1b 292 }
293
294 if ($lastinitial) {
d4419d55 295 $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\' ';
c750592a 296 }
297
03d820c7 298 $select .= $remotewhere;
299
488acd1b 300 if ($sort) {
d4419d55 301 $sort = ' ORDER BY '. $sort .' '. $dir;
488acd1b 302 }
303
304/// warning: will return UNCONFIRMED USERS
03d820c7 305 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed, mnethostid
8f0cd6ef 306 FROM {$CFG->prefix}user
422770d8 307 WHERE $select $sort", $page, $recordsperpage);
9fa49e22 308
309}
310
488acd1b 311
18a97fd8 312/**
7290c7fa 313 * Full list of users that have confirmed their accounts.
fbc21ae8 314 *
315 * @uses $CFG
7290c7fa 316 * @return object
fbc21ae8 317 */
9fa49e22 318function get_users_confirmed() {
319 global $CFG;
8f0cd6ef 320 return get_records_sql("SELECT *
321 FROM {$CFG->prefix}user
322 WHERE confirmed = 1
9fa49e22 323 AND deleted = 0
e8e0bb2d 324 AND username <> 'guest'");
9fa49e22 325}
326
327
18a97fd8 328/**
7290c7fa 329 * Full list of users that have not yet confirmed their accounts.
fbc21ae8 330 *
331 * @uses $CFG
332 * @param string $cutofftime ?
7290c7fa 333 * @return object {@link $USER} records
fbc21ae8 334 */
99988d1a 335function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 336 global $CFG;
8f0cd6ef 337 return get_records_sql("SELECT *
338 FROM {$CFG->prefix}user
9fa49e22 339 WHERE confirmed = 0
8f0cd6ef 340 AND firstaccess > 0
cf36da64 341 AND firstaccess < $cutofftime");
9fa49e22 342}
343
613bbd7c 344/**
345 * All users that we have not seen for a really long time (ie dead accounts)
346 *
347 * @uses $CFG
348 * @param string $cutofftime ?
349 * @return object {@link $USER} records
613bbd7c 350 */
351function get_users_longtimenosee($cutofftime) {
352 global $CFG;
cc7c0592 353 return get_records_sql("SELECT userid as id, courseid
354 FROM {$CFG->prefix}user_lastaccess
cf36da64 355 WHERE courseid != ".SITEID."
356 AND timeaccess > 0
357 AND timeaccess < $cutofftime ");
613bbd7c 358}
9fa49e22 359
fa22fd5f 360/**
361 * Full list of bogus accounts that are probably not ever going to be used
362 *
363 * @uses $CFG
364 * @param string $cutofftime ?
365 * @return object {@link $USER} records
fa22fd5f 366 */
367
368function get_users_not_fully_set_up($cutofftime=2000000000) {
369 global $CFG;
370 return get_records_sql("SELECT *
371 FROM {$CFG->prefix}user
372 WHERE confirmed = 1
373 AND lastaccess > 0
cf36da64 374 AND lastaccess < $cutofftime
fa22fd5f 375 AND deleted = 0
376 AND (lastname = '' OR firstname = '' OR email = '')");
377}
378
379
f3f7610c
ML
380/** TODO: functions now in /group/lib/legacylib.php (3)
381get_groups
382get_group_users
383user_group
384
fbc21ae8 385 * Returns an array of group objects that the user is a member of
386 * in the given course. If userid isn't specified, then return a
387 * list of all groups in the course.
388 *
389 * @uses $CFG
89dcb99d 390 * @param int $courseid The id of the course in question.
fbc21ae8 391 * @param int $userid The id of the user in question as found in the 'user' table 'id' field.
7290c7fa 392 * @return object
f3f7610c 393 *
f374fb10 394function get_groups($courseid, $userid=0) {
395 global $CFG;
396
397 if ($userid) {
d4419d55 398 $dbselect = ', '. $CFG->prefix .'groups_members m';
399 $userselect = 'AND m.groupid = g.id AND m.userid = \''. $userid .'\'';
2d439c9d 400 } else {
401 $dbselect = '';
402 $userselect = '';
f374fb10 403 }
404
94ef00f3 405 return get_records_sql("SELECT g.*
2d439c9d 406 FROM {$CFG->prefix}groups g $dbselect
f374fb10 407 WHERE g.courseid = '$courseid' $userselect ");
408}
409
410
411/**
613bbd7c 412 * Returns an array of user objects that belong to a given group
fbc21ae8 413 *
414 * @uses $CFG
415 * @param int $groupid The group in question.
416 * @param string $sort ?
417 * @param string $exceptions ?
7290c7fa 418 * @return object
f3f7610c 419 *
49668367 420function get_group_users($groupid, $sort='u.lastaccess DESC', $exceptions='', $fields='u.*') {
f374fb10 421 global $CFG;
900df8b6 422 if (!empty($exceptions)) {
d4419d55 423 $except = ' AND u.id NOT IN ('. $exceptions .') ';
900df8b6 424 } else {
425 $except = '';
426 }
c1147b7e 427 // in postgres, you can't have things in sort that aren't in the select, so...
428 $extrafield = str_replace('ASC','',$sort);
d5efb299 429 $extrafield = str_replace('DESC','',$extrafield);
c1147b7e 430 $extrafield = trim($extrafield);
431 if (!empty($extrafield)) {
432 $extrafield = ','.$extrafield;
433 }
82749af7 434 return get_records_sql("SELECT $fields $extrafield
f374fb10 435 FROM {$CFG->prefix}user u,
8f0cd6ef 436 {$CFG->prefix}groups_members m
f374fb10 437 WHERE m.groupid = '$groupid'
900df8b6 438 AND m.userid = u.id $except
2c4263c4 439 ORDER BY $sort");
f374fb10 440}
441
f374fb10 442/**
fbc21ae8 443 * Returns the user's group in a particular course
444 *
445 * @uses $CFG
446 * @param int $courseid The course in question.
447 * @param int $userid The id of the user as found in the 'user' table.
fa22fd5f 448 * @param int $groupid The id of the group the user is in.
7290c7fa 449 * @return object
f3f7610c 450 *
f374fb10 451function user_group($courseid, $userid) {
452 global $CFG;
453
fa22fd5f 454 return get_records_sql("SELECT g.*
0da33e07 455 FROM {$CFG->prefix}groups g,
456 {$CFG->prefix}groups_members m
f374fb10 457 WHERE g.courseid = '$courseid'
458 AND g.id = m.groupid
fa22fd5f 459 AND m.userid = '$userid'
460 ORDER BY name ASC");
f374fb10 461}
f3f7610c 462*/
f374fb10 463
9fa49e22 464
02ebf404 465
466/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
467
468
18a97fd8 469/**
fbc21ae8 470 * Returns $course object of the top-level site.
471 *
89dcb99d 472 * @return course A {@link $COURSE} object for the site
fbc21ae8 473 */
c44d5d42 474function get_site() {
475
476 global $SITE;
477
478 if (!empty($SITE->id)) { // We already have a global to use, so return that
479 return $SITE;
480 }
02ebf404 481
c44d5d42 482 if ($course = get_record('course', 'category', 0)) {
02ebf404 483 return $course;
484 } else {
485 return false;
486 }
487}
488
18a97fd8 489/**
613bbd7c 490 * Returns list of courses, for whole site, or category
491 *
492 * Returns list of courses, for whole site, or category
493 * Important: Using c.* for fields is extremely expensive because
494 * we are using distinct. You almost _NEVER_ need all the fields
495 * in such a large SELECT
496 *
497 * @param type description
498 *
613bbd7c 499 */
6315b1c8 500function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
02ebf404 501
8ef9cb56 502 global $USER, $CFG;
6315b1c8 503
6315b1c8 504 if ($categoryid != "all" && is_numeric($categoryid)) {
71dea306 505 $categoryselect = "WHERE c.category = '$categoryid'";
506 } else {
507 $categoryselect = "";
09575480 508 }
509
510 if (empty($sort)) {
511 $sortstatement = "";
512 } else {
513 $sortstatement = "ORDER BY $sort";
514 }
515
516 $visiblecourses = array();
71dea306 517
518 // pull out all course matching the cat
09575480 519 if ($courses = get_records_sql("SELECT $fields
71dea306 520 FROM {$CFG->prefix}course c
521 $categoryselect
09575480 522 $sortstatement")) {
523
524 // loop throught them
525 foreach ($courses as $course) {
526
285f94f5 527 if (isset($course->visible) && $course->visible <= 0) {
09575480 528 // for hidden courses, require visibility check
285f94f5 529 if (has_capability('moodle/course:viewhiddencourses',
530 get_context_instance(CONTEXT_COURSE, $course->id))) {
09575480 531 $visiblecourses [] = $course;
532 }
533 } else {
71dea306 534 $visiblecourses [] = $course;
09575480 535 }
536 }
6315b1c8 537 }
71dea306 538 return $visiblecourses;
6315b1c8 539
71dea306 540/*
6315b1c8 541 $teachertable = "";
542 $visiblecourses = "";
543 $sqland = "";
544 if (!empty($categoryselect)) {
545 $sqland = "AND ";
546 }
547 if (!empty($USER->id)) { // May need to check they are a teacher
ae9e4c06 548 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
6315b1c8 549 $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
550 $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course = c.id";
551 }
552 } else {
553 $visiblecourses = "$sqland c.visible > 0";
8ef9cb56 554 }
555
6315b1c8 556 if ($categoryselect or $visiblecourses) {
557 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
14f32609 558 } else {
6315b1c8 559 $selectsql = "{$CFG->prefix}course c $teachertable";
14f32609 560 }
561
5b66416f 562 $extrafield = str_replace('ASC','',$sort);
563 $extrafield = str_replace('DESC','',$extrafield);
564 $extrafield = trim($extrafield);
565 if (!empty($extrafield)) {
566 $extrafield = ','.$extrafield;
567 }
568 return get_records_sql("SELECT ".((!empty($teachertable)) ? " DISTINCT " : "")." $fields $extrafield FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : ""));
71dea306 569 */
8130b77b 570}
571
8130b77b 572
6315b1c8 573/**
613bbd7c 574 * Returns list of courses, for whole site, or category
575 *
576 * Similar to get_courses, but allows paging
577 * Important: Using c.* for fields is extremely expensive because
578 * we are using distinct. You almost _NEVER_ need all the fields
579 * in such a large SELECT
580 *
581 * @param type description
582 *
613bbd7c 583 */
6315b1c8 584function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
585 &$totalcount, $limitfrom="", $limitnum="") {
c7fe5c6f 586
8130b77b 587 global $USER, $CFG;
71dea306 588
589 $categoryselect = "";
590 if ($categoryid != "all" && is_numeric($categoryid)) {
591 $categoryselect = "WHERE c.category = '$categoryid'";
592 } else {
593 $categoryselect = "";
594 }
595
596 // pull out all course matching the cat
12490fc2 597 $visiblecourses = array();
598 if (!($courses = get_records_sql("SELECT $fields
71dea306 599 FROM {$CFG->prefix}course c
600 $categoryselect
12490fc2 601 ORDER BY $sort"))) {
602 return $visiblecourses;
603 }
71dea306 604 $totalcount = 0;
605
606 if (!$limitnum) {
607 $limitnum = count($courses);
608 }
609
285f94f5 610 if (!$limitfrom) {
71dea306 611 $limitfrom = 0;
612 }
613
614 // iteration will have to be done inside loop to keep track of the limitfrom and limitnum
615 foreach ($courses as $course) {
616 if ($course->visible <= 0) {
617 // for hidden courses, require visibility check
618 if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
619 $totalcount++;
620 if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
621 $visiblecourses [] = $course;
622 }
623 }
624 } else {
625 $totalcount++;
626 if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
627 $visiblecourses [] = $course;
628 }
629 }
630 }
631
632 return $visiblecourses;
633
634/**
8130b77b 635
6315b1c8 636 $categoryselect = "";
b565bbdf 637 if ($categoryid != "all" && is_numeric($categoryid)) {
6315b1c8 638 $categoryselect = "c.category = '$categoryid'";
8130b77b 639 }
640
6315b1c8 641 $teachertable = "";
642 $visiblecourses = "";
643 $sqland = "";
644 if (!empty($categoryselect)) {
645 $sqland = "AND ";
c7fe5c6f 646 }
2d2da684 647 if (!empty($USER) and !empty($USER->id)) { // May need to check they are a teacher
ae9e4c06 648 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
6315b1c8 649 $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
650 $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course=c.id";
651 }
8130b77b 652 } else {
6315b1c8 653 $visiblecourses = "$sqland c.visible > 0";
8130b77b 654 }
655
6315b1c8 656 if ($limitfrom !== "") {
29daf3a0 657 $limit = sql_paging_limit($limitfrom, $limitnum);
6315b1c8 658 } else {
659 $limit = "";
02ebf404 660 }
8ef9cb56 661
6315b1c8 662 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
8ef9cb56 663
6315b1c8 664 $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
8ef9cb56 665
2338ad32 666 return get_records_sql("SELECT $fields FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : "")." $limit");
71dea306 667 */
02ebf404 668}
669
670
18a97fd8 671/**
f8e1c7af 672 * List of courses that a user has access to view. Note that for admins,
673 * this usually includes every course on the system.
fbc21ae8 674 *
675 * @uses $CFG
7290c7fa 676 * @param int $userid The user of interest
33f85740 677 * @param string $sort the sortorder in the course table
678 * @param string $fields the fields to return
f8e1c7af 679 * @param bool $doanything True if using the doanything flag
680 * @param int $limit Maximum number of records to return, or 0 for unlimited
33f85740 681 * @return array {@link $COURSE} of course objects
fbc21ae8 682 */
f8e1c7af 683function get_my_courses($userid, $sort='visible DESC,sortorder ASC', $fields='*', $doanything=false,$limit=0) {
2f3499b7 684
8e82745a 685 global $USER;
686
687 if (!empty($USER->id) && ($USER->id == $userid)) {
688 if (!empty($USER->mycourses)) {
689 return $USER->mycourses; // Just return the cached version
690 }
691 }
692
61b03dc7 693 $mycourses = array();
f8e1c7af 694
695 // Fix fields to refer to the course table c
696 $fields=preg_replace('/([a-z0-9*]+)/','c.$1',$fields);
2f3499b7 697
f8e1c7af 698 // Attempt to filter the list of courses in order to reduce the number
699 // of queries in the next part.
700
701 // Check root permissions
702 $sitecontext = get_context_instance(CONTEXT_SYSTEM, SITEID);
703 if(has_capability('moodle/course:view',$sitecontext,$userid,$doanything)) {
704 // User can view all courses, although there might be exceptions
705 // which we will filter later.
706 $rs = get_recordset('course c', '', '', $sort, $fields);
707 } else {
708 // The only other context level above courses that applies to moodle/course:view
709 // is category. So we consider:
710 // 1. All courses in which the user is assigned a role
711 // 2. All courses in categories in which the user is assigned a role
712 // 3. All courses which have overrides for moodle/course:view
713 // Remember that this is just a filter. We check each individual course later.
714 // However for a typical student on a large system this can reduce the
715 // number of courses considered from around 2,000 to around 2, with corresponding
716 // reduction in the number of queries needed.
717 global $CFG;
718 $rs=get_recordset_sql("
719SELECT
720 $fields
721FROM
722 {$CFG->prefix}role_assignments ra
723 INNER JOIN {$CFG->prefix}context x ON x.id=ra.contextid
724 INNER JOIN {$CFG->prefix}course c ON x.instanceid=c.id AND x.contextlevel=50
725WHERE
726 ra.userid=$userid
727UNION
728SELECT
729 $fields
730FROM
731 {$CFG->prefix}role_assignments ra
732 INNER JOIN {$CFG->prefix}context x ON x.id=ra.contextid
733 INNER JOIN {$CFG->prefix}course_categories a ON x.instanceid=a.id AND x.contextlevel=40
734 INNER JOIN {$CFG->prefix}course c ON c.category=a.id
735WHERE
736 ra.userid=$userid
737UNION
738SELECT
739 $fields
740FROM
741 {$CFG->prefix}role_capabilities ca
742 INNER JOIN {$CFG->prefix}context x ON x.id=ca.contextid AND x.contextlevel=50
743 INNER JOIN {$CFG->prefix}course c ON c.id=x.instanceid
744WHERE
745 ca.contextid <> {$sitecontext->id} AND ca.capability='moodle/course:view'
746ORDER BY $sort");
747 }
0dde27bb 748
749 if ($rs && $rs->RecordCount() > 0) {
b5eb7523 750 while ($course = rs_fetch_next_record($rs)) {
0dde27bb 751 if ($course->id != SITEID) {
752 // users with moodle/course:view are considered course participants
753 // the course needs to be visible, or user must have moodle/course:viewhiddencourses
754 // capability set to view hidden courses
755 $context = get_context_instance(CONTEXT_COURSE, $course->id);
03bb25e1 756 if ( has_capability('moodle/course:view', $context, $userid, $doanything) &&
81cc8046 757 !has_capability('moodle/legacy:guest', $context, $userid, false) &&
0dde27bb 758 ($course->visible || has_capability('moodle/course:viewhiddencourses', $context, $userid))) {
33f85740 759 $mycourses[$course->id] = $course;
f8e1c7af 760
761 // Only return a limited number of courses if limit is set
762 if($limit>0) {
763 $limit--;
764 if($limit==0) {
765 break;
766 }
767 }
fbcbd77c 768 }
769 }
2f3499b7 770 }
771 }
152a9060 772
1cbf4fcc 773 if (!empty($USER->id) && ($USER->id == $userid)) {
774 $USER->mycourses = $mycourses;
775 }
776
0dde27bb 777 return $mycourses;
02ebf404 778}
779
780
18a97fd8 781/**
7290c7fa 782 * A list of courses that match a search
fbc21ae8 783 *
784 * @uses $CFG
785 * @param array $searchterms ?
786 * @param string $sort ?
787 * @param int $page ?
788 * @param int $recordsperpage ?
789 * @param int $totalcount Passed in by reference. ?
7290c7fa 790 * @return object {@link $COURSE} records
fbc21ae8 791 */
d4419d55 792function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
02ebf404 793
794 global $CFG;
795
18a97fd8 796 //to allow case-insensitive search for postgesql
48505662 797 if ($CFG->dbfamily == 'postgres') {
d4419d55 798 $LIKE = 'ILIKE';
799 $NOTLIKE = 'NOT ILIKE'; // case-insensitive
800 $REGEXP = '~*';
801 $NOTREGEXP = '!~*';
02ebf404 802 } else {
d4419d55 803 $LIKE = 'LIKE';
804 $NOTLIKE = 'NOT LIKE';
805 $REGEXP = 'REGEXP';
806 $NOTREGEXP = 'NOT REGEXP';
02ebf404 807 }
808
d4419d55 809 $fullnamesearch = '';
810 $summarysearch = '';
02ebf404 811
02ebf404 812 foreach ($searchterms as $searchterm) {
6bb0f67f 813
814 /// Under Oracle and MSSQL, trim the + and - operators and perform
815 /// simpler LIKE search
48505662 816 if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') {
6bb0f67f 817 $searchterm = trim($searchterm, '+-');
818 }
819
02ebf404 820 if ($fullnamesearch) {
d4419d55 821 $fullnamesearch .= ' AND ';
02ebf404 822 }
02ebf404 823 if ($summarysearch) {
d4419d55 824 $summarysearch .= ' AND ';
02ebf404 825 }
a8b56716 826
d4419d55 827 if (substr($searchterm,0,1) == '+') {
a8b56716 828 $searchterm = substr($searchterm,1);
829 $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
830 $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
831 } else if (substr($searchterm,0,1) == "-") {
832 $searchterm = substr($searchterm,1);
833 $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
834 $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
835 } else {
d4419d55 836 $summarysearch .= ' summary '. $LIKE .'\'%'. $searchterm .'%\' ';
837 $fullnamesearch .= ' fullname '. $LIKE .'\'%'. $searchterm .'%\' ';
a8b56716 838 }
839
02ebf404 840 }
841
d4419d55 842 $selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\'';
a8b56716 843
d4419d55 844 $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
02ebf404 845
422770d8 846 $courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort, $page, $recordsperpage);
02ebf404 847
848 if ($courses) { /// Remove unavailable courses from the list
849 foreach ($courses as $key => $course) {
152a9060 850 if (!$course->visible) {
1c45e42e 851 if (!has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
02ebf404 852 unset($courses[$key]);
a8b56716 853 $totalcount--;
02ebf404 854 }
855 }
856 }
857 }
858
859 return $courses;
860}
861
862
18a97fd8 863/**
fbc21ae8 864 * Returns a sorted list of categories
865 *
613bbd7c 866 * @param string $parent The parent category if any
867 * @param string $sort the sortorder
868 * @return array of categories
fbc21ae8 869 */
d4419d55 870function get_categories($parent='none', $sort='sortorder ASC') {
02ebf404 871
814748c9 872 if ($parent === 'none') {
d4419d55 873 $categories = get_records('course_categories', '', '', $sort);
02ebf404 874 } else {
d4419d55 875 $categories = get_records('course_categories', 'parent', $parent, $sort);
02ebf404 876 }
877 if ($categories) { /// Remove unavailable categories from the list
ae9e4c06 878 $creator = has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID));
02ebf404 879 foreach ($categories as $key => $category) {
152a9060 880 if (!$category->visible) {
3af6e1db 881 if (!$creator) {
02ebf404 882 unset($categories[$key]);
883 }
884 }
885 }
886 }
887 return $categories;
888}
889
890
18a97fd8 891/**
ba87a4da 892* This recursive function makes sure that the courseorder is consecutive
893*
894* @param type description
895*
896* $n is the starting point, offered only for compatilibity -- will be ignored!
897* $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade
898* safely from 1.4 to 1.5
899*/
f41ef63e 900function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') {
901
ba87a4da 902 global $CFG;
8f0cd6ef 903
02ebf404 904 $count = 0;
ba87a4da 905
f41ef63e 906 $catgap = 1000; // "standard" category gap
907 $tolerance = 200; // how "close" categories can get
908
909 if ($categoryid > 0){
910 // update depth and path
911 $cat = get_record('course_categories', 'id', $categoryid);
912 if ($cat->parent == 0) {
913 $depth = 0;
914 $path = '';
915 } else if ($depth == 0 ) { // doesn't make sense; get from DB
916 // this is only called if the $depth parameter looks dodgy
917 $parent = get_record('course_categories', 'id', $cat->parent);
918 $path = $parent->path;
919 $depth = $parent->depth;
920 }
921 $path = $path . '/' . $categoryid;
922 $depth = $depth + 1;
ba87a4da 923
f41ef63e 924 set_field('course_categories', 'path', addslashes($path), 'id', $categoryid);
925 set_field('course_categories', 'depth', $depth, 'id', $categoryid);
926 }
39f65595 927
928 // get some basic info about courses in the category
ba87a4da 929 $info = get_record_sql('SELECT MIN(sortorder) AS min,
930 MAX(sortorder) AS max,
f41ef63e 931 COUNT(sortorder) AS count
ba87a4da 932 FROM ' . $CFG->prefix . 'course
933 WHERE category=' . $categoryid);
934 if (is_object($info)) { // no courses?
935 $max = $info->max;
936 $count = $info->count;
937 $min = $info->min;
938 unset($info);
939 }
940
814748c9 941 if ($categoryid > 0 && $n==0) { // only passed category so don't shift it
942 $n = $min;
943 }
944
39f65595 945 // $hasgap flag indicates whether there's a gap in the sequence
946 $hasgap = false;
947 if ($max-$min+1 != $count) {
948 $hasgap = true;
949 }
950
951 // $mustshift indicates whether the sequence must be shifted to
952 // meet its range
953 $mustshift = false;
954 if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) {
955 $mustshift = true;
956 }
957
ba87a4da 958 // actually sort only if there are courses,
959 // and we meet one ofthe triggers:
960 // - safe flag
961 // - they are not in a continuos block
962 // - they are too close to the 'bottom'
39f65595 963 if ($count && ( $safe || $hasgap || $mustshift ) ) {
964 // special, optimized case where all we need is to shift
965 if ( $mustshift && !$safe && !$hasgap) {
966 $shift = $n + $catgap - $min;
967 // UPDATE course SET sortorder=sortorder+$shift
968 execute_sql("UPDATE {$CFG->prefix}course
969 SET sortorder=sortorder+$shift
970 WHERE category=$categoryid", 0);
971 $n = $n + $catgap + $count;
972
973 } else { // do it slowly
974 $n = $n + $catgap;
975 // if the new sequence overlaps the current sequence, lack of transactions
976 // will stop us -- shift things aside for a moment...
48505662 977 if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) {
d6a49dab 978 $shift = $max + $n + 1000;
39f65595 979 execute_sql("UPDATE {$CFG->prefix}course
980 SET sortorder=sortorder+$shift
981 WHERE category=$categoryid", 0);
ba87a4da 982 }
983
39f65595 984 $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
985 begin_sql();
ba87a4da 986 foreach ($courses as $course) {
987 if ($course->sortorder != $n ) { // save db traffic
988 set_field('course', 'sortorder', $n, 'id', $course->id);
989 }
990 $n++;
991 }
992 commit_sql();
993 }
02ebf404 994 }
d4419d55 995 set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
8f0cd6ef 996
814748c9 997 // $n could need updating
998 $max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid");
999 if ($max > $n) {
1000 $n = $max;
1001 }
758b9a4d 1002
6bc502cc 1003 if ($categories = get_categories($categoryid)) {
1004 foreach ($categories as $category) {
f41ef63e 1005 $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path);
6bc502cc 1006 }
1007 }
8f0cd6ef 1008
39f65595 1009 return $n+1;
02ebf404 1010}
1011
db4b12eb 1012/**
1013 * List of remote courses that a user has access to via MNET.
1014 * Works only on the IDP
1015 *
1016 * @uses $CFG, $USER
1017 * @return array {@link $COURSE} of course objects
1018 */
1019function get_my_remotecourses($userid=0) {
1020 global $CFG, $USER;
1021
1022 if (empty($userid)) {
1023 $userid = $USER->id;
1024 }
1025
86dd62a7 1026 $sql = "SELECT c.remoteid, c.shortname, c.fullname,
1027 c.hostid, c.summary, c.cat_name,
1028 h.name AS hostname
db4b12eb 1029 FROM {$CFG->prefix}mnet_enrol_course c
1030 JOIN {$CFG->prefix}mnet_enrol_assignments a ON c.id=a.courseid
86dd62a7 1031 JOIN {$CFG->prefix}mnet_host h ON c.hostid=h.id
db4b12eb 1032 WHERE a.userid={$userid}";
1033
1034 return get_records_sql($sql);
1035}
1036
1037/**
1038 * List of remote hosts that a user has access to via MNET.
1039 * Works on the SP
1040 *
1041 * @uses $CFG, $USER
1042 * @return array of host objects
1043 */
1044function get_my_remotehosts() {
1045 global $CFG, $USER;
1046
1047 if ($USER->mnethostid == $CFG->mnet_localhost_id) {
1048 return false; // Return nothing on the IDP
1049 }
1050 if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) {
1051 return $USER->mnet_foreign_host_array;
1052 }
1053 return false;
1054}
fbc21ae8 1055
18a97fd8 1056/**
fbc21ae8 1057 * This function creates a default separated/connected scale
1058 *
1059 * This function creates a default separated/connected scale
1060 * so there's something in the database. The locations of
1061 * strings and files is a bit odd, but this is because we
1062 * need to maintain backward compatibility with many different
1063 * existing language translations and older sites.
1064 *
1065 * @uses $CFG
1066 */
02ebf404 1067function make_default_scale() {
02ebf404 1068
1069 global $CFG;
1070
1071 $defaultscale = NULL;
1072 $defaultscale->courseid = 0;
1073 $defaultscale->userid = 0;
d4419d55 1074 $defaultscale->name = get_string('separateandconnected');
1075 $defaultscale->scale = get_string('postrating1', 'forum').','.
1076 get_string('postrating2', 'forum').','.
1077 get_string('postrating3', 'forum');
02ebf404 1078 $defaultscale->timemodified = time();
1079
8f0cd6ef 1080 /// Read in the big description from the file. Note this is not
02ebf404 1081 /// HTML (despite the file extension) but Moodle format text.
d4419d55 1082 $parentlang = get_string('parentlang');
ee6e91d4 1083 if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1084 $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1085 } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
d4419d55 1086 $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
ee6e91d4 1087 } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1088 $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
d4419d55 1089 } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1090 $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
ee6e91d4 1091 } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) {
1092 $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html');
02ebf404 1093 } else {
d4419d55 1094 $file = '';
02ebf404 1095 }
1096
d4419d55 1097 $defaultscale->description = addslashes(implode('', $file));
02ebf404 1098
d4419d55 1099 if ($defaultscale->id = insert_record('scale', $defaultscale)) {
1100 execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false);
02ebf404 1101 }
1102}
1103
fbc21ae8 1104
18a97fd8 1105/**
fbc21ae8 1106 * Returns a menu of all available scales from the site as well as the given course
1107 *
1108 * @uses $CFG
1109 * @param int $courseid The id of the course as found in the 'course' table.
7290c7fa 1110 * @return object
fbc21ae8 1111 */
02ebf404 1112function get_scales_menu($courseid=0) {
02ebf404 1113
1114 global $CFG;
8f0cd6ef 1115
1116 $sql = "SELECT id, name FROM {$CFG->prefix}scale
1117 WHERE courseid = '0' or courseid = '$courseid'
02ebf404 1118 ORDER BY courseid ASC, name ASC";
1119
d4419d55 1120 if ($scales = get_records_sql_menu($sql)) {
02ebf404 1121 return $scales;
1122 }
1123
1124 make_default_scale();
1125
d4419d55 1126 return get_records_sql_menu($sql);
02ebf404 1127}
1128
5baa0ad6 1129
1130
1131/**
1132 * Given a set of timezone records, put them in the database, replacing what is there
1133 *
1134 * @uses $CFG
1135 * @param array $timezones An array of timezone records
1136 */
1137function update_timezone_records($timezones) {
1138/// Given a set of timezone records, put them in the database
1139
1140 global $CFG;
1141
1142/// Clear out all the old stuff
1143 execute_sql('TRUNCATE TABLE '.$CFG->prefix.'timezone', false);
1144
1145/// Insert all the new stuff
1146 foreach ($timezones as $timezone) {
1147 insert_record('timezone', $timezone);
1148 }
1149}
1150
1151
df28d6c5 1152/// MODULE FUNCTIONS /////////////////////////////////////////////////
1153
18a97fd8 1154/**
fbc21ae8 1155 * Just gets a raw list of all modules in a course
1156 *
1157 * @uses $CFG
1158 * @param int $courseid The id of the course as found in the 'course' table.
7290c7fa 1159 * @return object
fbc21ae8 1160 */
9fa49e22 1161function get_course_mods($courseid) {
9fa49e22 1162 global $CFG;
1163
3a11c548 1164 if (empty($courseid)) {
1165 return false; // avoid warnings
1166 }
1167
7acaa63d 1168 return get_records_sql("SELECT cm.*, m.name as modname
8f0cd6ef 1169 FROM {$CFG->prefix}modules m,
7acaa63d 1170 {$CFG->prefix}course_modules cm
8f0cd6ef 1171 WHERE cm.course = '$courseid'
9fa49e22 1172 AND cm.module = m.id ");
1173}
1174
fbc21ae8 1175
18a97fd8 1176/**
f9d5371b 1177 * Given an id of a course module, finds the coursemodule description
fbc21ae8 1178 *
f9d5371b 1179 * @param string $modulename name of module type, eg. resource, assignment,...
1180 * @param int $cmid course module id (id in course_modules table)
1181 * @param int $courseid optional course id for extra validation
1182 * @return object course module instance with instance and module name
1183 */
1184function get_coursemodule_from_id($modulename, $cmid, $courseid=0) {
1185
1186 global $CFG;
1187
1188 $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1189
1190 return get_record_sql("SELECT cm.*, m.name, md.name as modname
1191 FROM {$CFG->prefix}course_modules cm,
1192 {$CFG->prefix}modules md,
1193 {$CFG->prefix}$modulename m
1194 WHERE $courseselect
1195 cm.id = '$cmid' AND
1196 cm.instance = m.id AND
1197 md.name = '$modulename' AND
1198 md.id = cm.module");
1199}
1200
1201/**
1202 * Given an instance number of a module, finds the coursemodule description
1203 *
1204 * @param string $modulename name of module type, eg. resource, assignment,...
1205 * @param int $instance module instance number (id in resource, assignment etc. table)
1206 * @param int $courseid optional course id for extra validation
1207 * @return object course module instance with instance and module name
fbc21ae8 1208 */
b63c0ee5 1209function get_coursemodule_from_instance($modulename, $instance, $courseid=0) {
df28d6c5 1210
1211 global $CFG;
f9d5371b 1212
b63c0ee5 1213 $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
df28d6c5 1214
f9d5371b 1215 return get_record_sql("SELECT cm.*, m.name, md.name as modname
8f0cd6ef 1216 FROM {$CFG->prefix}course_modules cm,
1217 {$CFG->prefix}modules md,
1218 {$CFG->prefix}$modulename m
b63c0ee5 1219 WHERE $courseselect
8f0cd6ef 1220 cm.instance = m.id AND
1221 md.name = '$modulename' AND
df28d6c5 1222 md.id = cm.module AND
1223 m.id = '$instance'");
1224
1225}
1226
185cfb09 1227/**
1228 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1229 *
1230 * Returns an array of all the active instances of a particular
1231 * module in given courses, sorted in the order they are defined
1232 * in the course. Returns false on any errors.
1233 *
1234 * @uses $CFG
1235 * @param string $modulename The name of the module to get instances for
613bbd7c 1236 * @param array $courses This depends on an accurate $course->modinfo
1237 * @return array of instances
185cfb09 1238 */
00e12c73 1239function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
185cfb09 1240 global $CFG;
1241 if (empty($courses) || !is_array($courses) || count($courses) == 0) {
1242 return array();
1243 }
1244 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode, cm.course
1245 FROM {$CFG->prefix}course_modules cm,
1246 {$CFG->prefix}course_sections cw,
1247 {$CFG->prefix}modules md,
1248 {$CFG->prefix}$modulename m
1249 WHERE cm.course IN (".implode(',',array_keys($courses)).") AND
1250 cm.instance = m.id AND
1251 cm.section = cw.id AND
1252 md.name = '$modulename' AND
1253 md.id = cm.module")) {
1254 return array();
1255 }
1256
1257 $outputarray = array();
1258
1259 foreach ($courses as $course) {
00e12c73 1260 if ($includeinvisible) {
1261 $invisible = -1;
1262 } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1263 // Usually hide non-visible instances from students
185cfb09 1264 $invisible = -1;
1265 } else {
1266 $invisible = 0;
1267 }
fea43a7f 1268
1269 /// Casting $course->modinfo to string prevents one notice when the field is null
1270 if (!$modinfo = unserialize((string)$course->modinfo)) {
185cfb09 1271 continue;
1272 }
1273 foreach ($modinfo as $mod) {
1274 if ($mod->mod == $modulename and $mod->visible > $invisible) {
1275 $instance = $rawmods[$mod->cm];
1276 if (!empty($mod->extra)) {
1277 $instance->extra = $mod->extra;
1278 }
1279 $outputarray[] = $instance;
1280 }
1281 }
1282 }
1283
1284 return $outputarray;
1285
1286}
fbc21ae8 1287
18a97fd8 1288/**
fbc21ae8 1289 * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1290 *
1291 * Returns an array of all the active instances of a particular
1292 * module in a given course, sorted in the order they are defined
1293 * in the course. Returns false on any errors.
1294 *
1295 * @uses $CFG
1296 * @param string $modulename The name of the module to get instances for
1297 * @param object(course) $course This depends on an accurate $course->modinfo
fbc21ae8 1298 */
00e12c73 1299function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
df28d6c5 1300
1301 global $CFG;
1302
3cc8b355 1303 if (empty($course->modinfo)) {
1304 return array();
1305 }
1306
fea43a7f 1307 if (!$modinfo = unserialize((string)$course->modinfo)) {
cccb016a 1308 return array();
1acfbce5 1309 }
1310
404afe6b 1311 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode
8f0cd6ef 1312 FROM {$CFG->prefix}course_modules cm,
1313 {$CFG->prefix}course_sections cw,
1314 {$CFG->prefix}modules md,
1315 {$CFG->prefix}$modulename m
1316 WHERE cm.course = '$course->id' AND
1317 cm.instance = m.id AND
8f0cd6ef 1318 cm.section = cw.id AND
1319 md.name = '$modulename' AND
cccb016a 1320 md.id = cm.module")) {
1321 return array();
1322 }
1323
00e12c73 1324 if ($includeinvisible) {
1325 $invisible = -1;
1326 } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1327 // Usually hide non-visible instances from students
cccb016a 1328 $invisible = -1;
1329 } else {
1330 $invisible = 0;
1331 }
1332
78d4711e 1333 $outputarray = array();
1334
cccb016a 1335 foreach ($modinfo as $mod) {
1336 if ($mod->mod == $modulename and $mod->visible > $invisible) {
7f12f9cd 1337 $instance = $rawmods[$mod->cm];
1338 if (!empty($mod->extra)) {
1339 $instance->extra = $mod->extra;
1340 }
1341 $outputarray[] = $instance;
cccb016a 1342 }
1343 }
1344
1345 return $outputarray;
df28d6c5 1346
1347}
1348
9fa49e22 1349
18a97fd8 1350/**
fbc21ae8 1351 * Determine whether a module instance is visible within a course
1352 *
1353 * Given a valid module object with info about the id and course,
1354 * and the module's type (eg "forum") returns whether the object
1355 * is visible or not
1356 *
1357 * @uses $CFG
613bbd7c 1358 * @param $moduletype Name of the module eg 'forum'
1359 * @param $module Object which is the instance of the module
7290c7fa 1360 * @return bool
fbc21ae8 1361 */
580f2fbc 1362function instance_is_visible($moduletype, $module) {
580f2fbc 1363
1364 global $CFG;
1365
2b49ae96 1366 if (!empty($module->id)) {
1367 if ($records = get_records_sql("SELECT cm.instance, cm.visible
1368 FROM {$CFG->prefix}course_modules cm,
1369 {$CFG->prefix}modules m
1370 WHERE cm.course = '$module->course' AND
1371 cm.module = m.id AND
1372 m.name = '$moduletype' AND
1373 cm.instance = '$module->id'")) {
1374
1375 foreach ($records as $record) { // there should only be one - use the first one
1376 return $record->visible;
1377 }
580f2fbc 1378 }
1379 }
580f2fbc 1380 return true; // visible by default!
1381}
1382
a3fb1c45 1383
1384
1385
9fa49e22 1386/// LOG FUNCTIONS /////////////////////////////////////////////////////
1387
1388
18a97fd8 1389/**
fbc21ae8 1390 * Add an entry to the log table.
1391 *
1392 * Add an entry to the log table. These are "action" focussed rather
1393 * than web server hits, and provide a way to easily reconstruct what
1394 * any particular student has been doing.
1395 *
1396 * @uses $CFG
1397 * @uses $USER
1398 * @uses $db
1399 * @uses $REMOTE_ADDR
1400 * @uses SITEID
89dcb99d 1401 * @param int $courseid The course id
fbc21ae8 1402 * @param string $module The module name - e.g. forum, journal, resource, course, user etc
f7664880 1403 * @param string $action 'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
fbc21ae8 1404 * @param string $url The file and parameters used to see the results of the action
1405 * @param string $info Additional description information
1406 * @param string $cm The course_module->id if there is one
1407 * @param string $user If log regards $user other than $USER
1408 */
d4419d55 1409function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
e8395a09 1410 // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1411 // This is for a good reason: it is the most frequently used DB update function,
1412 // so it has been optimised for speed.
fcaff7ff 1413 global $db, $CFG, $USER;
9fa49e22 1414
7a5b1fc5 1415 if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default
f78b3c34 1416 $cm = 0;
1417 }
1418
3d94772d 1419 if ($user) {
1420 $userid = $user;
1421 } else {
cb80265b 1422 if (!empty($USER->realuser)) { // Don't log
3d94772d 1423 return;
1424 }
d4419d55 1425 $userid = empty($USER->id) ? '0' : $USER->id;
9fa49e22 1426 }
1427
fcaff7ff 1428 $REMOTE_ADDR = getremoteaddr();
1429
9fa49e22 1430 $timenow = time();
1431 $info = addslashes($info);
10a760b9 1432 if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1433 $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
1434 }
853df85e 1435
1436 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; $PERF->logwrites++;};
1437
8b497bbc 1438 if ($CFG->type = 'oci8po') {
1439 if (empty($info)) {
1440 $info = ' ';
1441 }
1442 }
1443
d4419d55 1444 $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info)
1445 VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
ebc3bd2b 1446
ea82d6b6 1447 if (!$result and debugging()) {
d4419d55 1448 echo '<p>Error: Could not insert a new entry to the Moodle log</p>'; // Don't throw an error
8f0cd6ef 1449 }
cb80265b 1450
1451/// Store lastaccess times for the current user
1452
1453 if (!empty($USER->id) && ($userid == $USER->id) ) {
1454 $db->Execute('UPDATE '. $CFG->prefix .'user
1455 SET lastip=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\'
1456 WHERE id = \''. $userid .'\' ');
1457 if ($courseid != SITEID && !empty($courseid)) {
853df85e 1458 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++;};
1c45e42e 1459
cb80265b 1460 if ($record = get_record('user_lastaccess', 'userid', $userid, 'courseid', $courseid)) {
1461 $record->timeaccess = $timenow;
1462 return update_record('user_lastaccess', $record);
1463 } else {
ae9e4c06 1464 $record = new object;
1465 $record->userid = $userid;
1466 $record->courseid = $courseid;
1467 $record->timeaccess = $timenow;
1468 return insert_record('user_lastaccess', $record);
114176a2 1469 }
3d94772d 1470 }
8f0cd6ef 1471 }
9fa49e22 1472}
1473
1474
18a97fd8 1475/**
fbc21ae8 1476 * Select all log records based on SQL criteria
1477 *
1478 * @uses $CFG
1479 * @param string $select SQL select criteria
1480 * @param string $order SQL order by clause to sort the records returned
1481 * @param string $limitfrom ?
1482 * @param int $limitnum ?
1483 * @param int $totalcount Passed in by reference.
7290c7fa 1484 * @return object
fbc21ae8 1485 * @todo Finish documenting this function
1486 */
d4419d55 1487function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
9fa49e22 1488 global $CFG;
1489
519d369f 1490 if ($order) {
d4419d55 1491 $order = 'ORDER BY '. $order;
519d369f 1492 }
1493
fbc21ae8 1494 $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : '');
a2ddd957 1495 $countsql = $CFG->prefix.'log l '.((strlen($select) > 0) ? ' WHERE '. $select : '');
1496
1497 $totalcount = count_records_sql("SELECT COUNT(*) FROM $countsql");
519d369f 1498
d4419d55 1499 return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture
93a89227 1500 FROM '. $selectsql .' '. $order, $limitfrom, $limitnum) ;
9fa49e22 1501}
1502
519d369f 1503
18a97fd8 1504/**
fbc21ae8 1505 * Select all log records for a given course and user
1506 *
1507 * @uses $CFG
2f87145b 1508 * @uses DAYSECS
fbc21ae8 1509 * @param int $userid The id of the user as found in the 'user' table.
1510 * @param int $courseid The id of the course as found in the 'course' table.
1511 * @param string $coursestart ?
1512 * @todo Finish documenting this function
1513 */
9fa49e22 1514function get_logs_usercourse($userid, $courseid, $coursestart) {
1515 global $CFG;
1516
da0c90c3 1517 if ($courseid) {
d4419d55 1518 $courseselect = ' AND course = \''. $courseid .'\' ';
2700d113 1519 } else {
1520 $courseselect = '';
da0c90c3 1521 }
1522
1604a0fc 1523 return get_records_sql("SELECT floor((time - $coursestart)/". DAYSECS .") as day, count(*) as num
8f0cd6ef 1524 FROM {$CFG->prefix}log
1525 WHERE userid = '$userid'
1604a0fc 1526 AND time > '$coursestart' $courseselect
9fa49e22 1527 GROUP BY day ");
1528}
1529
18a97fd8 1530/**
fbc21ae8 1531 * Select all log records for a given course, user, and day
1532 *
1533 * @uses $CFG
2f87145b 1534 * @uses HOURSECS
fbc21ae8 1535 * @param int $userid The id of the user as found in the 'user' table.
1536 * @param int $courseid The id of the course as found in the 'course' table.
1537 * @param string $daystart ?
7290c7fa 1538 * @return object
fbc21ae8 1539 * @todo Finish documenting this function
1540 */
9fa49e22 1541function get_logs_userday($userid, $courseid, $daystart) {
1542 global $CFG;
1543
7e4a6488 1544 if ($courseid) {
d4419d55 1545 $courseselect = ' AND course = \''. $courseid .'\' ';
2700d113 1546 } else {
1547 $courseselect = '';
7e4a6488 1548 }
1549
1604a0fc 1550 return get_records_sql("SELECT floor((time - $daystart)/". HOURSECS .") as hour, count(*) as num
9fa49e22 1551 FROM {$CFG->prefix}log
8f0cd6ef 1552 WHERE userid = '$userid'
1604a0fc 1553 AND time > '$daystart' $courseselect
9fa49e22 1554 GROUP BY hour ");
1555}
1556
b4bac9b6 1557/**
1558 * Returns an object with counts of failed login attempts
1559 *
8f0cd6ef 1560 * Returns information about failed login attempts. If the current user is
1561 * an admin, then two numbers are returned: the number of attempts and the
b4bac9b6 1562 * number of accounts. For non-admins, only the attempts on the given user
1563 * are shown.
1564 *
fbc21ae8 1565 * @param string $mode Either 'admin', 'teacher' or 'everybody'
1566 * @param string $username The username we are searching for
1567 * @param string $lastlogin The date from which we are searching
1568 * @return int
b4bac9b6 1569 */
b4bac9b6 1570function count_login_failures($mode, $username, $lastlogin) {
1571
d4419d55 1572 $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin;
b4bac9b6 1573
51792df0 1574 if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM, SITEID))) { // Return information about all accounts
b4bac9b6 1575 if ($count->attempts = count_records_select('log', $select)) {
1576 $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)');
1577 return $count;
1578 }
9407d456 1579 } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) {
d4419d55 1580 if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) {
b4bac9b6 1581 return $count;
1582 }
1583 }
1584 return NULL;
1585}
1586
1587
a3fb1c45 1588/// GENERAL HELPFUL THINGS ///////////////////////////////////
1589
18a97fd8 1590/**
fbc21ae8 1591 * Dump a given object's information in a PRE block.
1592 *
1593 * Mostly just used for debugging.
1594 *
1595 * @param mixed $object The data to be printed
fbc21ae8 1596 */
a3fb1c45 1597function print_object($object) {
1aa1044f 1598 echo '<pre>'.htmlspecialchars(print_r($object,true)).'</pre>';
a3fb1c45 1599}
1600
0986271b 1601function course_parent_visible($course = null) {
fa145ae1 1602 global $CFG;
1603
418b4e5a 1604 if (empty($course)) {
1605 return true;
1606 }
1607 if (!empty($CFG->allowvisiblecoursesinhiddencategories)) {
1608 return true;
1609 }
0986271b 1610 return category_parent_visible($course->category);
1611}
1612
1613function category_parent_visible($parent = 0) {
824f1c40 1614
1615 static $visible;
1616
0986271b 1617 if (!$parent) {
1618 return true;
1619 }
824f1c40 1620
1621 if (empty($visible)) {
1622 $visible = array(); // initialize
1623 }
1624
1625 if (array_key_exists($parent,$visible)) {
1626 return $visible[$parent];
1627 }
1628
0986271b 1629 $category = get_record('course_categories', 'id', $parent);
1630 $list = explode('/', preg_replace('/^\/(.*)$/', '$1', $category->path));
1631 $list[] = $parent;
1632 $parents = get_records_list('course_categories', 'id', implode(',', $list), 'depth DESC');
824f1c40 1633 $v = true;
1634 foreach ($parents as $p) {
1635 if (!$p->visible) {
1636 $v = false;
0986271b 1637 }
1638 }
824f1c40 1639 $visible[$parent] = $v; // now cache it
1640 return $v;
0986271b 1641}
1642
62d4e774 1643/**
1644 * This function is the official hook inside XMLDB stuff to delegate its debug to one
1645 * external function.
1646 *
1647 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
1648 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
1649 *
1650 * @param $message string contains the error message
1651 * @param $object object XMLDB object that fired the debug
1652 */
1653function xmldb_debug($message, $object) {
1654
1655 error_log($message);
1656}
1657
9d5b689c 1658// vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
03517306 1659?>