Another minor change to profile string
[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
6bff0453 228 $select = 'username <> \'guest\' AND username <> \'changeme\' 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
6bff0453 283 $select = "deleted <> '1' AND username <> 'changeme'";
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
8f0cd6ef 324 AND username <> 'guest'
9fa49e22 325 AND username <> 'changeme'");
326}
327
328
18a97fd8 329/**
7290c7fa 330 * Full list of users that have not yet confirmed their accounts.
fbc21ae8 331 *
332 * @uses $CFG
333 * @param string $cutofftime ?
7290c7fa 334 * @return object {@link $USER} records
fbc21ae8 335 */
99988d1a 336function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 337 global $CFG;
8f0cd6ef 338 return get_records_sql("SELECT *
339 FROM {$CFG->prefix}user
9fa49e22 340 WHERE confirmed = 0
8f0cd6ef 341 AND firstaccess > 0
cf36da64 342 AND firstaccess < $cutofftime");
9fa49e22 343}
344
613bbd7c 345/**
346 * All users that we have not seen for a really long time (ie dead accounts)
347 *
348 * @uses $CFG
349 * @param string $cutofftime ?
350 * @return object {@link $USER} records
613bbd7c 351 */
352function get_users_longtimenosee($cutofftime) {
353 global $CFG;
cc7c0592 354 return get_records_sql("SELECT userid as id, courseid
355 FROM {$CFG->prefix}user_lastaccess
cf36da64 356 WHERE courseid != ".SITEID."
357 AND timeaccess > 0
358 AND timeaccess < $cutofftime ");
613bbd7c 359}
9fa49e22 360
fa22fd5f 361/**
362 * Full list of bogus accounts that are probably not ever going to be used
363 *
364 * @uses $CFG
365 * @param string $cutofftime ?
366 * @return object {@link $USER} records
fa22fd5f 367 */
368
369function get_users_not_fully_set_up($cutofftime=2000000000) {
370 global $CFG;
371 return get_records_sql("SELECT *
372 FROM {$CFG->prefix}user
373 WHERE confirmed = 1
374 AND lastaccess > 0
cf36da64 375 AND lastaccess < $cutofftime
fa22fd5f 376 AND deleted = 0
377 AND (lastname = '' OR firstname = '' OR email = '')");
378}
379
380
f3f7610c
ML
381/** TODO: functions now in /group/lib/legacylib.php (3)
382get_groups
383get_group_users
384user_group
385
fbc21ae8 386 * Returns an array of group objects that the user is a member of
387 * in the given course. If userid isn't specified, then return a
388 * list of all groups in the course.
389 *
390 * @uses $CFG
89dcb99d 391 * @param int $courseid The id of the course in question.
fbc21ae8 392 * @param int $userid The id of the user in question as found in the 'user' table 'id' field.
7290c7fa 393 * @return object
f3f7610c 394 *
f374fb10 395function get_groups($courseid, $userid=0) {
396 global $CFG;
397
398 if ($userid) {
d4419d55 399 $dbselect = ', '. $CFG->prefix .'groups_members m';
400 $userselect = 'AND m.groupid = g.id AND m.userid = \''. $userid .'\'';
2d439c9d 401 } else {
402 $dbselect = '';
403 $userselect = '';
f374fb10 404 }
405
94ef00f3 406 return get_records_sql("SELECT g.*
2d439c9d 407 FROM {$CFG->prefix}groups g $dbselect
f374fb10 408 WHERE g.courseid = '$courseid' $userselect ");
409}
410
411
412/**
613bbd7c 413 * Returns an array of user objects that belong to a given group
fbc21ae8 414 *
415 * @uses $CFG
416 * @param int $groupid The group in question.
417 * @param string $sort ?
418 * @param string $exceptions ?
7290c7fa 419 * @return object
f3f7610c 420 *
49668367 421function get_group_users($groupid, $sort='u.lastaccess DESC', $exceptions='', $fields='u.*') {
f374fb10 422 global $CFG;
900df8b6 423 if (!empty($exceptions)) {
d4419d55 424 $except = ' AND u.id NOT IN ('. $exceptions .') ';
900df8b6 425 } else {
426 $except = '';
427 }
c1147b7e 428 // in postgres, you can't have things in sort that aren't in the select, so...
429 $extrafield = str_replace('ASC','',$sort);
d5efb299 430 $extrafield = str_replace('DESC','',$extrafield);
c1147b7e 431 $extrafield = trim($extrafield);
432 if (!empty($extrafield)) {
433 $extrafield = ','.$extrafield;
434 }
82749af7 435 return get_records_sql("SELECT $fields $extrafield
f374fb10 436 FROM {$CFG->prefix}user u,
8f0cd6ef 437 {$CFG->prefix}groups_members m
f374fb10 438 WHERE m.groupid = '$groupid'
900df8b6 439 AND m.userid = u.id $except
2c4263c4 440 ORDER BY $sort");
f374fb10 441}
442
f374fb10 443/**
fbc21ae8 444 * Returns the user's group in a particular course
445 *
446 * @uses $CFG
447 * @param int $courseid The course in question.
448 * @param int $userid The id of the user as found in the 'user' table.
fa22fd5f 449 * @param int $groupid The id of the group the user is in.
7290c7fa 450 * @return object
f3f7610c 451 *
f374fb10 452function user_group($courseid, $userid) {
453 global $CFG;
454
fa22fd5f 455 return get_records_sql("SELECT g.*
0da33e07 456 FROM {$CFG->prefix}groups g,
457 {$CFG->prefix}groups_members m
f374fb10 458 WHERE g.courseid = '$courseid'
459 AND g.id = m.groupid
fa22fd5f 460 AND m.userid = '$userid'
461 ORDER BY name ASC");
f374fb10 462}
f3f7610c 463*/
f374fb10 464
9fa49e22 465
02ebf404 466
467/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
468
469
18a97fd8 470/**
fbc21ae8 471 * Returns $course object of the top-level site.
472 *
89dcb99d 473 * @return course A {@link $COURSE} object for the site
fbc21ae8 474 */
c44d5d42 475function get_site() {
476
477 global $SITE;
478
479 if (!empty($SITE->id)) { // We already have a global to use, so return that
480 return $SITE;
481 }
02ebf404 482
c44d5d42 483 if ($course = get_record('course', 'category', 0)) {
02ebf404 484 return $course;
485 } else {
486 return false;
487 }
488}
489
18a97fd8 490/**
613bbd7c 491 * Returns list of courses, for whole site, or category
492 *
493 * Returns list of courses, for whole site, or category
494 * Important: Using c.* for fields is extremely expensive because
495 * we are using distinct. You almost _NEVER_ need all the fields
496 * in such a large SELECT
497 *
498 * @param type description
499 *
613bbd7c 500 */
6315b1c8 501function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
02ebf404 502
8ef9cb56 503 global $USER, $CFG;
6315b1c8 504
6315b1c8 505 if ($categoryid != "all" && is_numeric($categoryid)) {
71dea306 506 $categoryselect = "WHERE c.category = '$categoryid'";
507 } else {
508 $categoryselect = "";
09575480 509 }
510
511 if (empty($sort)) {
512 $sortstatement = "";
513 } else {
514 $sortstatement = "ORDER BY $sort";
515 }
516
517 $visiblecourses = array();
71dea306 518
519 // pull out all course matching the cat
09575480 520 if ($courses = get_records_sql("SELECT $fields
71dea306 521 FROM {$CFG->prefix}course c
522 $categoryselect
09575480 523 $sortstatement")) {
524
525 // loop throught them
526 foreach ($courses as $course) {
527
285f94f5 528 if (isset($course->visible) && $course->visible <= 0) {
09575480 529 // for hidden courses, require visibility check
285f94f5 530 if (has_capability('moodle/course:viewhiddencourses',
531 get_context_instance(CONTEXT_COURSE, $course->id))) {
09575480 532 $visiblecourses [] = $course;
533 }
534 } else {
71dea306 535 $visiblecourses [] = $course;
09575480 536 }
537 }
6315b1c8 538 }
71dea306 539 return $visiblecourses;
6315b1c8 540
71dea306 541/*
6315b1c8 542 $teachertable = "";
543 $visiblecourses = "";
544 $sqland = "";
545 if (!empty($categoryselect)) {
546 $sqland = "AND ";
547 }
548 if (!empty($USER->id)) { // May need to check they are a teacher
ae9e4c06 549 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
6315b1c8 550 $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
551 $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course = c.id";
552 }
553 } else {
554 $visiblecourses = "$sqland c.visible > 0";
8ef9cb56 555 }
556
6315b1c8 557 if ($categoryselect or $visiblecourses) {
558 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
14f32609 559 } else {
6315b1c8 560 $selectsql = "{$CFG->prefix}course c $teachertable";
14f32609 561 }
562
5b66416f 563 $extrafield = str_replace('ASC','',$sort);
564 $extrafield = str_replace('DESC','',$extrafield);
565 $extrafield = trim($extrafield);
566 if (!empty($extrafield)) {
567 $extrafield = ','.$extrafield;
568 }
569 return get_records_sql("SELECT ".((!empty($teachertable)) ? " DISTINCT " : "")." $fields $extrafield FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : ""));
71dea306 570 */
8130b77b 571}
572
8130b77b 573
6315b1c8 574/**
613bbd7c 575 * Returns list of courses, for whole site, or category
576 *
577 * Similar to get_courses, but allows paging
578 * Important: Using c.* for fields is extremely expensive because
579 * we are using distinct. You almost _NEVER_ need all the fields
580 * in such a large SELECT
581 *
582 * @param type description
583 *
613bbd7c 584 */
6315b1c8 585function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
586 &$totalcount, $limitfrom="", $limitnum="") {
c7fe5c6f 587
8130b77b 588 global $USER, $CFG;
71dea306 589
590 $categoryselect = "";
591 if ($categoryid != "all" && is_numeric($categoryid)) {
592 $categoryselect = "WHERE c.category = '$categoryid'";
593 } else {
594 $categoryselect = "";
595 }
596
597 // pull out all course matching the cat
12490fc2 598 $visiblecourses = array();
599 if (!($courses = get_records_sql("SELECT $fields
71dea306 600 FROM {$CFG->prefix}course c
601 $categoryselect
12490fc2 602 ORDER BY $sort"))) {
603 return $visiblecourses;
604 }
71dea306 605 $totalcount = 0;
606
607 if (!$limitnum) {
608 $limitnum = count($courses);
609 }
610
285f94f5 611 if (!$limitfrom) {
71dea306 612 $limitfrom = 0;
613 }
614
615 // iteration will have to be done inside loop to keep track of the limitfrom and limitnum
616 foreach ($courses as $course) {
617 if ($course->visible <= 0) {
618 // for hidden courses, require visibility check
619 if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
620 $totalcount++;
621 if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
622 $visiblecourses [] = $course;
623 }
624 }
625 } else {
626 $totalcount++;
627 if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
628 $visiblecourses [] = $course;
629 }
630 }
631 }
632
633 return $visiblecourses;
634
635/**
8130b77b 636
6315b1c8 637 $categoryselect = "";
b565bbdf 638 if ($categoryid != "all" && is_numeric($categoryid)) {
6315b1c8 639 $categoryselect = "c.category = '$categoryid'";
8130b77b 640 }
641
6315b1c8 642 $teachertable = "";
643 $visiblecourses = "";
644 $sqland = "";
645 if (!empty($categoryselect)) {
646 $sqland = "AND ";
c7fe5c6f 647 }
2d2da684 648 if (!empty($USER) and !empty($USER->id)) { // May need to check they are a teacher
ae9e4c06 649 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
6315b1c8 650 $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
651 $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course=c.id";
652 }
8130b77b 653 } else {
6315b1c8 654 $visiblecourses = "$sqland c.visible > 0";
8130b77b 655 }
656
6315b1c8 657 if ($limitfrom !== "") {
29daf3a0 658 $limit = sql_paging_limit($limitfrom, $limitnum);
6315b1c8 659 } else {
660 $limit = "";
02ebf404 661 }
8ef9cb56 662
6315b1c8 663 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
8ef9cb56 664
6315b1c8 665 $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
8ef9cb56 666
2338ad32 667 return get_records_sql("SELECT $fields FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : "")." $limit");
71dea306 668 */
02ebf404 669}
670
671
18a97fd8 672/**
7290c7fa 673 * List of courses that a user is a member of.
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
679 * @return array {@link $COURSE} of course objects
fbc21ae8 680 */
03bb25e1 681function get_my_courses($userid, $sort='visible DESC,sortorder ASC', $fields='*', $doanything=false) {
2f3499b7 682
61b03dc7 683 $mycourses = array();
2f3499b7 684
33f85740 685 $rs = get_recordset('course', '', '', $sort, $fields);
0dde27bb 686
687 if ($rs && $rs->RecordCount() > 0) {
688 while (!$rs->EOF) {
689 $course = (object)$rs->fields;
690
691 if ($course->id != SITEID) {
692 // users with moodle/course:view are considered course participants
693 // the course needs to be visible, or user must have moodle/course:viewhiddencourses
694 // capability set to view hidden courses
695 $context = get_context_instance(CONTEXT_COURSE, $course->id);
03bb25e1 696 if ( has_capability('moodle/course:view', $context, $userid, $doanything) &&
81cc8046 697 !has_capability('moodle/legacy:guest', $context, $userid, false) &&
0dde27bb 698 ($course->visible || has_capability('moodle/course:viewhiddencourses', $context, $userid))) {
33f85740 699 $mycourses[$course->id] = $course;
fbcbd77c 700 }
701 }
152a9060 702
0dde27bb 703 $rs->MoveNext();
2f3499b7 704 }
705 }
152a9060 706
0dde27bb 707 return $mycourses;
02ebf404 708}
709
710
18a97fd8 711/**
7290c7fa 712 * A list of courses that match a search
fbc21ae8 713 *
714 * @uses $CFG
715 * @param array $searchterms ?
716 * @param string $sort ?
717 * @param int $page ?
718 * @param int $recordsperpage ?
719 * @param int $totalcount Passed in by reference. ?
7290c7fa 720 * @return object {@link $COURSE} records
fbc21ae8 721 */
d4419d55 722function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
02ebf404 723
724 global $CFG;
725
18a97fd8 726 //to allow case-insensitive search for postgesql
d4419d55 727 if ($CFG->dbtype == 'postgres7') {
728 $LIKE = 'ILIKE';
729 $NOTLIKE = 'NOT ILIKE'; // case-insensitive
730 $REGEXP = '~*';
731 $NOTREGEXP = '!~*';
02ebf404 732 } else {
d4419d55 733 $LIKE = 'LIKE';
734 $NOTLIKE = 'NOT LIKE';
735 $REGEXP = 'REGEXP';
736 $NOTREGEXP = 'NOT REGEXP';
02ebf404 737 }
738
d4419d55 739 $fullnamesearch = '';
740 $summarysearch = '';
02ebf404 741
02ebf404 742 foreach ($searchterms as $searchterm) {
6bb0f67f 743
744 /// Under Oracle and MSSQL, trim the + and - operators and perform
745 /// simpler LIKE search
746 if ($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'mssql_n' || $CFG->dbtype == 'odbc_mssql') {
747 $searchterm = trim($searchterm, '+-');
748 }
749
02ebf404 750 if ($fullnamesearch) {
d4419d55 751 $fullnamesearch .= ' AND ';
02ebf404 752 }
02ebf404 753 if ($summarysearch) {
d4419d55 754 $summarysearch .= ' AND ';
02ebf404 755 }
a8b56716 756
d4419d55 757 if (substr($searchterm,0,1) == '+') {
a8b56716 758 $searchterm = substr($searchterm,1);
759 $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
760 $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
761 } else if (substr($searchterm,0,1) == "-") {
762 $searchterm = substr($searchterm,1);
763 $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
764 $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
765 } else {
d4419d55 766 $summarysearch .= ' summary '. $LIKE .'\'%'. $searchterm .'%\' ';
767 $fullnamesearch .= ' fullname '. $LIKE .'\'%'. $searchterm .'%\' ';
a8b56716 768 }
769
02ebf404 770 }
771
d4419d55 772 $selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\'';
a8b56716 773
d4419d55 774 $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
02ebf404 775
422770d8 776 $courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort, $page, $recordsperpage);
02ebf404 777
778 if ($courses) { /// Remove unavailable courses from the list
779 foreach ($courses as $key => $course) {
152a9060 780 if (!$course->visible) {
1c45e42e 781 if (!has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
02ebf404 782 unset($courses[$key]);
a8b56716 783 $totalcount--;
02ebf404 784 }
785 }
786 }
787 }
788
789 return $courses;
790}
791
792
18a97fd8 793/**
fbc21ae8 794 * Returns a sorted list of categories
795 *
613bbd7c 796 * @param string $parent The parent category if any
797 * @param string $sort the sortorder
798 * @return array of categories
fbc21ae8 799 */
d4419d55 800function get_categories($parent='none', $sort='sortorder ASC') {
02ebf404 801
814748c9 802 if ($parent === 'none') {
d4419d55 803 $categories = get_records('course_categories', '', '', $sort);
02ebf404 804 } else {
d4419d55 805 $categories = get_records('course_categories', 'parent', $parent, $sort);
02ebf404 806 }
807 if ($categories) { /// Remove unavailable categories from the list
ae9e4c06 808 $creator = has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID));
02ebf404 809 foreach ($categories as $key => $category) {
152a9060 810 if (!$category->visible) {
3af6e1db 811 if (!$creator) {
02ebf404 812 unset($categories[$key]);
813 }
814 }
815 }
816 }
817 return $categories;
818}
819
820
18a97fd8 821/**
ba87a4da 822* This recursive function makes sure that the courseorder is consecutive
823*
824* @param type description
825*
826* $n is the starting point, offered only for compatilibity -- will be ignored!
827* $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade
828* safely from 1.4 to 1.5
829*/
f41ef63e 830function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') {
831
ba87a4da 832 global $CFG;
8f0cd6ef 833
02ebf404 834 $count = 0;
ba87a4da 835
f41ef63e 836 $catgap = 1000; // "standard" category gap
837 $tolerance = 200; // how "close" categories can get
838
839 if ($categoryid > 0){
840 // update depth and path
841 $cat = get_record('course_categories', 'id', $categoryid);
842 if ($cat->parent == 0) {
843 $depth = 0;
844 $path = '';
845 } else if ($depth == 0 ) { // doesn't make sense; get from DB
846 // this is only called if the $depth parameter looks dodgy
847 $parent = get_record('course_categories', 'id', $cat->parent);
848 $path = $parent->path;
849 $depth = $parent->depth;
850 }
851 $path = $path . '/' . $categoryid;
852 $depth = $depth + 1;
ba87a4da 853
f41ef63e 854 set_field('course_categories', 'path', addslashes($path), 'id', $categoryid);
855 set_field('course_categories', 'depth', $depth, 'id', $categoryid);
856 }
39f65595 857
858 // get some basic info about courses in the category
ba87a4da 859 $info = get_record_sql('SELECT MIN(sortorder) AS min,
860 MAX(sortorder) AS max,
f41ef63e 861 COUNT(sortorder) AS count
ba87a4da 862 FROM ' . $CFG->prefix . 'course
863 WHERE category=' . $categoryid);
864 if (is_object($info)) { // no courses?
865 $max = $info->max;
866 $count = $info->count;
867 $min = $info->min;
868 unset($info);
869 }
870
814748c9 871 if ($categoryid > 0 && $n==0) { // only passed category so don't shift it
872 $n = $min;
873 }
874
39f65595 875 // $hasgap flag indicates whether there's a gap in the sequence
876 $hasgap = false;
877 if ($max-$min+1 != $count) {
878 $hasgap = true;
879 }
880
881 // $mustshift indicates whether the sequence must be shifted to
882 // meet its range
883 $mustshift = false;
884 if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) {
885 $mustshift = true;
886 }
887
ba87a4da 888 // actually sort only if there are courses,
889 // and we meet one ofthe triggers:
890 // - safe flag
891 // - they are not in a continuos block
892 // - they are too close to the 'bottom'
39f65595 893 if ($count && ( $safe || $hasgap || $mustshift ) ) {
894 // special, optimized case where all we need is to shift
895 if ( $mustshift && !$safe && !$hasgap) {
896 $shift = $n + $catgap - $min;
897 // UPDATE course SET sortorder=sortorder+$shift
898 execute_sql("UPDATE {$CFG->prefix}course
899 SET sortorder=sortorder+$shift
900 WHERE category=$categoryid", 0);
901 $n = $n + $catgap + $count;
902
903 } else { // do it slowly
904 $n = $n + $catgap;
905 // if the new sequence overlaps the current sequence, lack of transactions
906 // will stop us -- shift things aside for a moment...
94afadb3 907 if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbtype==='mysql')) {
d6a49dab 908 $shift = $max + $n + 1000;
39f65595 909 execute_sql("UPDATE {$CFG->prefix}course
910 SET sortorder=sortorder+$shift
911 WHERE category=$categoryid", 0);
ba87a4da 912 }
913
39f65595 914 $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
915 begin_sql();
ba87a4da 916 foreach ($courses as $course) {
917 if ($course->sortorder != $n ) { // save db traffic
918 set_field('course', 'sortorder', $n, 'id', $course->id);
919 }
920 $n++;
921 }
922 commit_sql();
923 }
02ebf404 924 }
d4419d55 925 set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
8f0cd6ef 926
814748c9 927 // $n could need updating
928 $max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid");
929 if ($max > $n) {
930 $n = $max;
931 }
758b9a4d 932
6bc502cc 933 if ($categories = get_categories($categoryid)) {
934 foreach ($categories as $category) {
f41ef63e 935 $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path);
6bc502cc 936 }
937 }
8f0cd6ef 938
39f65595 939 return $n+1;
02ebf404 940}
941
fbc21ae8 942
18a97fd8 943/**
fbc21ae8 944 * This function creates a default separated/connected scale
945 *
946 * This function creates a default separated/connected scale
947 * so there's something in the database. The locations of
948 * strings and files is a bit odd, but this is because we
949 * need to maintain backward compatibility with many different
950 * existing language translations and older sites.
951 *
952 * @uses $CFG
953 */
02ebf404 954function make_default_scale() {
02ebf404 955
956 global $CFG;
957
958 $defaultscale = NULL;
959 $defaultscale->courseid = 0;
960 $defaultscale->userid = 0;
d4419d55 961 $defaultscale->name = get_string('separateandconnected');
962 $defaultscale->scale = get_string('postrating1', 'forum').','.
963 get_string('postrating2', 'forum').','.
964 get_string('postrating3', 'forum');
02ebf404 965 $defaultscale->timemodified = time();
966
8f0cd6ef 967 /// Read in the big description from the file. Note this is not
02ebf404 968 /// HTML (despite the file extension) but Moodle format text.
d4419d55 969 $parentlang = get_string('parentlang');
ee6e91d4 970 if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
971 $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
972 } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
d4419d55 973 $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
ee6e91d4 974 } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
975 $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
d4419d55 976 } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
977 $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
ee6e91d4 978 } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) {
979 $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html');
02ebf404 980 } else {
d4419d55 981 $file = '';
02ebf404 982 }
983
d4419d55 984 $defaultscale->description = addslashes(implode('', $file));
02ebf404 985
d4419d55 986 if ($defaultscale->id = insert_record('scale', $defaultscale)) {
987 execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false);
02ebf404 988 }
989}
990
fbc21ae8 991
18a97fd8 992/**
fbc21ae8 993 * Returns a menu of all available scales from the site as well as the given course
994 *
995 * @uses $CFG
996 * @param int $courseid The id of the course as found in the 'course' table.
7290c7fa 997 * @return object
fbc21ae8 998 */
02ebf404 999function get_scales_menu($courseid=0) {
02ebf404 1000
1001 global $CFG;
8f0cd6ef 1002
1003 $sql = "SELECT id, name FROM {$CFG->prefix}scale
1004 WHERE courseid = '0' or courseid = '$courseid'
02ebf404 1005 ORDER BY courseid ASC, name ASC";
1006
d4419d55 1007 if ($scales = get_records_sql_menu($sql)) {
02ebf404 1008 return $scales;
1009 }
1010
1011 make_default_scale();
1012
d4419d55 1013 return get_records_sql_menu($sql);
02ebf404 1014}
1015
5baa0ad6 1016
1017
1018/**
1019 * Given a set of timezone records, put them in the database, replacing what is there
1020 *
1021 * @uses $CFG
1022 * @param array $timezones An array of timezone records
1023 */
1024function update_timezone_records($timezones) {
1025/// Given a set of timezone records, put them in the database
1026
1027 global $CFG;
1028
1029/// Clear out all the old stuff
1030 execute_sql('TRUNCATE TABLE '.$CFG->prefix.'timezone', false);
1031
1032/// Insert all the new stuff
1033 foreach ($timezones as $timezone) {
1034 insert_record('timezone', $timezone);
1035 }
1036}
1037
1038
df28d6c5 1039/// MODULE FUNCTIONS /////////////////////////////////////////////////
1040
18a97fd8 1041/**
fbc21ae8 1042 * Just gets a raw list of all modules in a course
1043 *
1044 * @uses $CFG
1045 * @param int $courseid The id of the course as found in the 'course' table.
7290c7fa 1046 * @return object
fbc21ae8 1047 */
9fa49e22 1048function get_course_mods($courseid) {
9fa49e22 1049 global $CFG;
1050
3a11c548 1051 if (empty($courseid)) {
1052 return false; // avoid warnings
1053 }
1054
7acaa63d 1055 return get_records_sql("SELECT cm.*, m.name as modname
8f0cd6ef 1056 FROM {$CFG->prefix}modules m,
7acaa63d 1057 {$CFG->prefix}course_modules cm
8f0cd6ef 1058 WHERE cm.course = '$courseid'
9fa49e22 1059 AND cm.module = m.id ");
1060}
1061
fbc21ae8 1062
18a97fd8 1063/**
f9d5371b 1064 * Given an id of a course module, finds the coursemodule description
fbc21ae8 1065 *
f9d5371b 1066 * @param string $modulename name of module type, eg. resource, assignment,...
1067 * @param int $cmid course module id (id in course_modules table)
1068 * @param int $courseid optional course id for extra validation
1069 * @return object course module instance with instance and module name
1070 */
1071function get_coursemodule_from_id($modulename, $cmid, $courseid=0) {
1072
1073 global $CFG;
1074
1075 $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1076
1077 return get_record_sql("SELECT cm.*, m.name, md.name as modname
1078 FROM {$CFG->prefix}course_modules cm,
1079 {$CFG->prefix}modules md,
1080 {$CFG->prefix}$modulename m
1081 WHERE $courseselect
1082 cm.id = '$cmid' AND
1083 cm.instance = m.id AND
1084 md.name = '$modulename' AND
1085 md.id = cm.module");
1086}
1087
1088/**
1089 * Given an instance number of a module, finds the coursemodule description
1090 *
1091 * @param string $modulename name of module type, eg. resource, assignment,...
1092 * @param int $instance module instance number (id in resource, assignment etc. table)
1093 * @param int $courseid optional course id for extra validation
1094 * @return object course module instance with instance and module name
fbc21ae8 1095 */
b63c0ee5 1096function get_coursemodule_from_instance($modulename, $instance, $courseid=0) {
df28d6c5 1097
1098 global $CFG;
f9d5371b 1099
b63c0ee5 1100 $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
df28d6c5 1101
f9d5371b 1102 return get_record_sql("SELECT cm.*, m.name, md.name as modname
8f0cd6ef 1103 FROM {$CFG->prefix}course_modules cm,
1104 {$CFG->prefix}modules md,
1105 {$CFG->prefix}$modulename m
b63c0ee5 1106 WHERE $courseselect
8f0cd6ef 1107 cm.instance = m.id AND
1108 md.name = '$modulename' AND
df28d6c5 1109 md.id = cm.module AND
1110 m.id = '$instance'");
1111
1112}
1113
185cfb09 1114/**
1115 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1116 *
1117 * Returns an array of all the active instances of a particular
1118 * module in given courses, sorted in the order they are defined
1119 * in the course. Returns false on any errors.
1120 *
1121 * @uses $CFG
1122 * @param string $modulename The name of the module to get instances for
613bbd7c 1123 * @param array $courses This depends on an accurate $course->modinfo
1124 * @return array of instances
185cfb09 1125 */
00e12c73 1126function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
185cfb09 1127 global $CFG;
1128 if (empty($courses) || !is_array($courses) || count($courses) == 0) {
1129 return array();
1130 }
1131 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode, cm.course
1132 FROM {$CFG->prefix}course_modules cm,
1133 {$CFG->prefix}course_sections cw,
1134 {$CFG->prefix}modules md,
1135 {$CFG->prefix}$modulename m
1136 WHERE cm.course IN (".implode(',',array_keys($courses)).") AND
1137 cm.instance = m.id AND
1138 cm.section = cw.id AND
1139 md.name = '$modulename' AND
1140 md.id = cm.module")) {
1141 return array();
1142 }
1143
1144 $outputarray = array();
1145
1146 foreach ($courses as $course) {
00e12c73 1147 if ($includeinvisible) {
1148 $invisible = -1;
1149 } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1150 // Usually hide non-visible instances from students
185cfb09 1151 $invisible = -1;
1152 } else {
1153 $invisible = 0;
1154 }
fea43a7f 1155
1156 /// Casting $course->modinfo to string prevents one notice when the field is null
1157 if (!$modinfo = unserialize((string)$course->modinfo)) {
185cfb09 1158 continue;
1159 }
1160 foreach ($modinfo as $mod) {
1161 if ($mod->mod == $modulename and $mod->visible > $invisible) {
1162 $instance = $rawmods[$mod->cm];
1163 if (!empty($mod->extra)) {
1164 $instance->extra = $mod->extra;
1165 }
1166 $outputarray[] = $instance;
1167 }
1168 }
1169 }
1170
1171 return $outputarray;
1172
1173}
fbc21ae8 1174
18a97fd8 1175/**
fbc21ae8 1176 * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1177 *
1178 * Returns an array of all the active instances of a particular
1179 * module in a given course, sorted in the order they are defined
1180 * in the course. Returns false on any errors.
1181 *
1182 * @uses $CFG
1183 * @param string $modulename The name of the module to get instances for
1184 * @param object(course) $course This depends on an accurate $course->modinfo
fbc21ae8 1185 */
00e12c73 1186function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
df28d6c5 1187
1188 global $CFG;
1189
3cc8b355 1190 if (empty($course->modinfo)) {
1191 return array();
1192 }
1193
fea43a7f 1194 if (!$modinfo = unserialize((string)$course->modinfo)) {
cccb016a 1195 return array();
1acfbce5 1196 }
1197
404afe6b 1198 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode
8f0cd6ef 1199 FROM {$CFG->prefix}course_modules cm,
1200 {$CFG->prefix}course_sections cw,
1201 {$CFG->prefix}modules md,
1202 {$CFG->prefix}$modulename m
1203 WHERE cm.course = '$course->id' AND
1204 cm.instance = m.id AND
8f0cd6ef 1205 cm.section = cw.id AND
1206 md.name = '$modulename' AND
cccb016a 1207 md.id = cm.module")) {
1208 return array();
1209 }
1210
00e12c73 1211 if ($includeinvisible) {
1212 $invisible = -1;
1213 } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1214 // Usually hide non-visible instances from students
cccb016a 1215 $invisible = -1;
1216 } else {
1217 $invisible = 0;
1218 }
1219
78d4711e 1220 $outputarray = array();
1221
cccb016a 1222 foreach ($modinfo as $mod) {
1223 if ($mod->mod == $modulename and $mod->visible > $invisible) {
7f12f9cd 1224 $instance = $rawmods[$mod->cm];
1225 if (!empty($mod->extra)) {
1226 $instance->extra = $mod->extra;
1227 }
1228 $outputarray[] = $instance;
cccb016a 1229 }
1230 }
1231
1232 return $outputarray;
df28d6c5 1233
1234}
1235
9fa49e22 1236
18a97fd8 1237/**
fbc21ae8 1238 * Determine whether a module instance is visible within a course
1239 *
1240 * Given a valid module object with info about the id and course,
1241 * and the module's type (eg "forum") returns whether the object
1242 * is visible or not
1243 *
1244 * @uses $CFG
613bbd7c 1245 * @param $moduletype Name of the module eg 'forum'
1246 * @param $module Object which is the instance of the module
7290c7fa 1247 * @return bool
fbc21ae8 1248 */
580f2fbc 1249function instance_is_visible($moduletype, $module) {
580f2fbc 1250
1251 global $CFG;
1252
2b49ae96 1253 if (!empty($module->id)) {
1254 if ($records = get_records_sql("SELECT cm.instance, cm.visible
1255 FROM {$CFG->prefix}course_modules cm,
1256 {$CFG->prefix}modules m
1257 WHERE cm.course = '$module->course' AND
1258 cm.module = m.id AND
1259 m.name = '$moduletype' AND
1260 cm.instance = '$module->id'")) {
1261
1262 foreach ($records as $record) { // there should only be one - use the first one
1263 return $record->visible;
1264 }
580f2fbc 1265 }
1266 }
580f2fbc 1267 return true; // visible by default!
1268}
1269
a3fb1c45 1270
1271
1272
9fa49e22 1273/// LOG FUNCTIONS /////////////////////////////////////////////////////
1274
1275
18a97fd8 1276/**
fbc21ae8 1277 * Add an entry to the log table.
1278 *
1279 * Add an entry to the log table. These are "action" focussed rather
1280 * than web server hits, and provide a way to easily reconstruct what
1281 * any particular student has been doing.
1282 *
1283 * @uses $CFG
1284 * @uses $USER
1285 * @uses $db
1286 * @uses $REMOTE_ADDR
1287 * @uses SITEID
89dcb99d 1288 * @param int $courseid The course id
fbc21ae8 1289 * @param string $module The module name - e.g. forum, journal, resource, course, user etc
f7664880 1290 * @param string $action 'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
fbc21ae8 1291 * @param string $url The file and parameters used to see the results of the action
1292 * @param string $info Additional description information
1293 * @param string $cm The course_module->id if there is one
1294 * @param string $user If log regards $user other than $USER
1295 */
d4419d55 1296function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
e8395a09 1297 // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1298 // This is for a good reason: it is the most frequently used DB update function,
1299 // so it has been optimised for speed.
fcaff7ff 1300 global $db, $CFG, $USER;
9fa49e22 1301
7a5b1fc5 1302 if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default
f78b3c34 1303 $cm = 0;
1304 }
1305
3d94772d 1306 if ($user) {
1307 $userid = $user;
1308 } else {
cb80265b 1309 if (!empty($USER->realuser)) { // Don't log
3d94772d 1310 return;
1311 }
d4419d55 1312 $userid = empty($USER->id) ? '0' : $USER->id;
9fa49e22 1313 }
1314
fcaff7ff 1315 $REMOTE_ADDR = getremoteaddr();
1316
9fa49e22 1317 $timenow = time();
1318 $info = addslashes($info);
10a760b9 1319 if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1320 $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
1321 }
853df85e 1322
1323 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; $PERF->logwrites++;};
1324
8b497bbc 1325 if ($CFG->type = 'oci8po') {
1326 if (empty($info)) {
1327 $info = ' ';
1328 }
1329 }
1330
d4419d55 1331 $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info)
1332 VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
ebc3bd2b 1333
ea82d6b6 1334 if (!$result and debugging()) {
d4419d55 1335 echo '<p>Error: Could not insert a new entry to the Moodle log</p>'; // Don't throw an error
8f0cd6ef 1336 }
cb80265b 1337
1338/// Store lastaccess times for the current user
1339
1340 if (!empty($USER->id) && ($userid == $USER->id) ) {
1341 $db->Execute('UPDATE '. $CFG->prefix .'user
1342 SET lastip=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\'
1343 WHERE id = \''. $userid .'\' ');
1344 if ($courseid != SITEID && !empty($courseid)) {
853df85e 1345 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++;};
1c45e42e 1346
cb80265b 1347 if ($record = get_record('user_lastaccess', 'userid', $userid, 'courseid', $courseid)) {
1348 $record->timeaccess = $timenow;
1349 return update_record('user_lastaccess', $record);
1350 } else {
ae9e4c06 1351 $record = new object;
1352 $record->userid = $userid;
1353 $record->courseid = $courseid;
1354 $record->timeaccess = $timenow;
1355 return insert_record('user_lastaccess', $record);
114176a2 1356 }
3d94772d 1357 }
8f0cd6ef 1358 }
9fa49e22 1359}
1360
1361
18a97fd8 1362/**
fbc21ae8 1363 * Select all log records based on SQL criteria
1364 *
1365 * @uses $CFG
1366 * @param string $select SQL select criteria
1367 * @param string $order SQL order by clause to sort the records returned
1368 * @param string $limitfrom ?
1369 * @param int $limitnum ?
1370 * @param int $totalcount Passed in by reference.
7290c7fa 1371 * @return object
fbc21ae8 1372 * @todo Finish documenting this function
1373 */
d4419d55 1374function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
9fa49e22 1375 global $CFG;
1376
519d369f 1377 if ($order) {
d4419d55 1378 $order = 'ORDER BY '. $order;
519d369f 1379 }
1380
fbc21ae8 1381 $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : '');
a2ddd957 1382 $countsql = $CFG->prefix.'log l '.((strlen($select) > 0) ? ' WHERE '. $select : '');
1383
1384 $totalcount = count_records_sql("SELECT COUNT(*) FROM $countsql");
519d369f 1385
d4419d55 1386 return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture
93a89227 1387 FROM '. $selectsql .' '. $order, $limitfrom, $limitnum) ;
9fa49e22 1388}
1389
519d369f 1390
18a97fd8 1391/**
fbc21ae8 1392 * Select all log records for a given course and user
1393 *
1394 * @uses $CFG
2f87145b 1395 * @uses DAYSECS
fbc21ae8 1396 * @param int $userid The id of the user as found in the 'user' table.
1397 * @param int $courseid The id of the course as found in the 'course' table.
1398 * @param string $coursestart ?
1399 * @todo Finish documenting this function
1400 */
9fa49e22 1401function get_logs_usercourse($userid, $courseid, $coursestart) {
1402 global $CFG;
1403
da0c90c3 1404 if ($courseid) {
d4419d55 1405 $courseselect = ' AND course = \''. $courseid .'\' ';
2700d113 1406 } else {
1407 $courseselect = '';
da0c90c3 1408 }
1409
1604a0fc 1410 return get_records_sql("SELECT floor((time - $coursestart)/". DAYSECS .") as day, count(*) as num
8f0cd6ef 1411 FROM {$CFG->prefix}log
1412 WHERE userid = '$userid'
1604a0fc 1413 AND time > '$coursestart' $courseselect
9fa49e22 1414 GROUP BY day ");
1415}
1416
18a97fd8 1417/**
fbc21ae8 1418 * Select all log records for a given course, user, and day
1419 *
1420 * @uses $CFG
2f87145b 1421 * @uses HOURSECS
fbc21ae8 1422 * @param int $userid The id of the user as found in the 'user' table.
1423 * @param int $courseid The id of the course as found in the 'course' table.
1424 * @param string $daystart ?
7290c7fa 1425 * @return object
fbc21ae8 1426 * @todo Finish documenting this function
1427 */
9fa49e22 1428function get_logs_userday($userid, $courseid, $daystart) {
1429 global $CFG;
1430
7e4a6488 1431 if ($courseid) {
d4419d55 1432 $courseselect = ' AND course = \''. $courseid .'\' ';
2700d113 1433 } else {
1434 $courseselect = '';
7e4a6488 1435 }
1436
1604a0fc 1437 return get_records_sql("SELECT floor((time - $daystart)/". HOURSECS .") as hour, count(*) as num
9fa49e22 1438 FROM {$CFG->prefix}log
8f0cd6ef 1439 WHERE userid = '$userid'
1604a0fc 1440 AND time > '$daystart' $courseselect
9fa49e22 1441 GROUP BY hour ");
1442}
1443
b4bac9b6 1444/**
1445 * Returns an object with counts of failed login attempts
1446 *
8f0cd6ef 1447 * Returns information about failed login attempts. If the current user is
1448 * an admin, then two numbers are returned: the number of attempts and the
b4bac9b6 1449 * number of accounts. For non-admins, only the attempts on the given user
1450 * are shown.
1451 *
fbc21ae8 1452 * @param string $mode Either 'admin', 'teacher' or 'everybody'
1453 * @param string $username The username we are searching for
1454 * @param string $lastlogin The date from which we are searching
1455 * @return int
b4bac9b6 1456 */
b4bac9b6 1457function count_login_failures($mode, $username, $lastlogin) {
1458
d4419d55 1459 $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin;
b4bac9b6 1460
51792df0 1461 if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM, SITEID))) { // Return information about all accounts
b4bac9b6 1462 if ($count->attempts = count_records_select('log', $select)) {
1463 $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)');
1464 return $count;
1465 }
9407d456 1466 } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) {
d4419d55 1467 if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) {
b4bac9b6 1468 return $count;
1469 }
1470 }
1471 return NULL;
1472}
1473
1474
a3fb1c45 1475/// GENERAL HELPFUL THINGS ///////////////////////////////////
1476
18a97fd8 1477/**
fbc21ae8 1478 * Dump a given object's information in a PRE block.
1479 *
1480 * Mostly just used for debugging.
1481 *
1482 * @param mixed $object The data to be printed
fbc21ae8 1483 */
a3fb1c45 1484function print_object($object) {
1aa1044f 1485 echo '<pre>'.htmlspecialchars(print_r($object,true)).'</pre>';
a3fb1c45 1486}
1487
11840632 1488
3ec22e35 1489/**
1490 * Checks for pg or mysql > 4
1491 */
1492
1493function check_db_compat() {
1494 global $CFG,$db;
1495
1496 if ($CFG->dbtype == 'postgres7') {
1497 return true;
1498 }
1499
1500 if (!$rs = $db->Execute("SELECT version();")) {
1501 return false;
1502 }
1503
1504 if (intval($rs->fields[0]) <= 3) {
1505 return false;
1506 }
1507
1508 return true;
1509}
1510
0986271b 1511function course_parent_visible($course = null) {
fa145ae1 1512 global $CFG;
1513
418b4e5a 1514 if (empty($course)) {
1515 return true;
1516 }
1517 if (!empty($CFG->allowvisiblecoursesinhiddencategories)) {
1518 return true;
1519 }
0986271b 1520 return category_parent_visible($course->category);
1521}
1522
1523function category_parent_visible($parent = 0) {
824f1c40 1524
1525 static $visible;
1526
0986271b 1527 if (!$parent) {
1528 return true;
1529 }
824f1c40 1530
1531 if (empty($visible)) {
1532 $visible = array(); // initialize
1533 }
1534
1535 if (array_key_exists($parent,$visible)) {
1536 return $visible[$parent];
1537 }
1538
0986271b 1539 $category = get_record('course_categories', 'id', $parent);
1540 $list = explode('/', preg_replace('/^\/(.*)$/', '$1', $category->path));
1541 $list[] = $parent;
1542 $parents = get_records_list('course_categories', 'id', implode(',', $list), 'depth DESC');
824f1c40 1543 $v = true;
1544 foreach ($parents as $p) {
1545 if (!$p->visible) {
1546 $v = false;
0986271b 1547 }
1548 }
824f1c40 1549 $visible[$parent] = $v; // now cache it
1550 return $v;
0986271b 1551}
1552
62d4e774 1553/**
1554 * This function is the official hook inside XMLDB stuff to delegate its debug to one
1555 * external function.
1556 *
1557 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
1558 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
1559 *
1560 * @param $message string contains the error message
1561 * @param $object object XMLDB object that fired the debug
1562 */
1563function xmldb_debug($message, $object) {
1564
1565 error_log($message);
1566}
1567
9d5b689c 1568// vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
03517306 1569?>