Merge from MOODLE_14_STABLE: moodle--eduforge--1.3.3--patch-207(partial) + moodle...
[moodle.git] / lib / datalib.php
CommitLineData
7cf1c7bd 1<?php
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
7d8c2ec3 16/// GLOBAL CONSTANTS /////////////////////////////////////////////////////////
7cf1c7bd 17
7d8c2ec3 18if ($SITE = get_site()) {
7cf1c7bd 19 /**
fbc21ae8 20 * If $SITE global from {@link get_site()} is set then SITEID to $SITE->id, otherwise set to 1.
7cf1c7bd 21 */
7d8c2ec3 22 define('SITEID', $SITE->id);
23} else {
7cf1c7bd 24 /**
25 * @ignore
26 */
7d8c2ec3 27 define('SITEID', 1);
28}
222ac91b 29
df28d6c5 30/// FUNCTIONS FOR DATABASE HANDLING ////////////////////////////////
222ac91b 31
18a97fd8 32/**
7cf1c7bd 33 * Execute a given sql command string
34 *
35 * Completely general function - it just runs some SQL and reports success.
36 *
fbc21ae8 37 * @uses $db
38 * @param string $command The sql string you wish to be executed.
39 * @param boolean $feedback Set this argument to true if the results generated should be printed. Default is true.
7cf1c7bd 40 * @return string
41 */
df28d6c5 42function execute_sql($command, $feedback=true) {
43/// Completely general function - it just runs some SQL and reports success.
44
45 global $db;
8f0cd6ef 46
d4419d55 47 $result = $db->Execute($command);
df28d6c5 48
49 if ($result) {
50 if ($feedback) {
fbc21ae8 51 echo '<p><font color="green"><strong>'. get_string('success') .'</strong></font></p>';
df28d6c5 52 }
53 return true;
54 } else {
55 if ($feedback) {
fbc21ae8 56 echo '<p><font color="red"><strong>'. get_string('error') .'</strong></font></p>';
df28d6c5 57 }
58 return false;
59 }
60}
1ed5abc8 61/**
62* on DBs that support it, switch to transaction mode and begin a transaction
63* you'll need to ensure you call commit_sql() or your changes *will* be lost
64* this is _very_ useful for massive updates
65*/
66function begin_sql() {
67/// Completely general function - it just runs some SQL and reports success.
68
69 global $CFG;
70 if ($CFG->dbtype === 'postgres7') {
71 return execute_sql('BEGIN', false);
72 }
73 return true;
74}
75/**
76* on DBs that support it, commit the transaction
77*/
78function commit_sql() {
79/// Completely general function - it just runs some SQL and reports success.
80
81 global $CFG;
82 if ($CFG->dbtype === 'postgres7') {
83 return execute_sql('COMMIT', false);
84 }
85 return true;
86}
df28d6c5 87
7cf1c7bd 88/**
89 * Run an arbitrary sequence of semicolon-delimited SQL commands
90 *
91 * Assumes that the input text (file or string) consists of
92 * a number of SQL statements ENDING WITH SEMICOLONS. The
93 * semicolons MUST be the last character in a line.
94 * Lines that are blank or that start with "#" are ignored.
95 * Only tested with mysql dump files (mysqldump -p -d moodle)
96 *
fbc21ae8 97 * @uses $CFG
7cf1c7bd 98 * @param string $sqlfile The path where a file with sql commands can be found on the server.
99 * @param string $sqlstring If no path is supplied then a string with semicolon delimited sql
100 * commands can be supplied in this argument.
fbc21ae8 101 * @return boolean Returns true if databse was modified successfully.
7cf1c7bd 102 */
d4419d55 103function modify_database($sqlfile='', $sqlstring='') {
df28d6c5 104
105 global $CFG;
106
8f0cd6ef 107 $success = true; // Let's be optimistic
2b051f1c 108
109 if (!empty($sqlfile)) {
110 if (!is_readable($sqlfile)) {
111 $success = false;
d4419d55 112 echo '<p>Tried to modify database, but "'. $sqlfile .'" doesn\'t exist!</p>';
2b051f1c 113 return $success;
114 } else {
115 $lines = file($sqlfile);
116 }
117 } else {
118 $lines[] = $sqlstring;
119 }
120
d4419d55 121 $command = '';
2b051f1c 122
123 foreach ($lines as $line) {
124 $line = rtrim($line);
125 $length = strlen($line);
126
d4419d55 127 if ($length and $line[0] <> '#') {
128 if (substr($line, $length-1, 1) == ';') {
2b051f1c 129 $line = substr($line, 0, $length-1); // strip ;
130 $command .= $line;
d4419d55 131 $command = str_replace('prefix_', $CFG->prefix, $command); // Table prefixes
2b051f1c 132 if (! execute_sql($command)) {
133 $success = false;
df28d6c5 134 }
d4419d55 135 $command = '';
2b051f1c 136 } else {
137 $command .= $line;
df28d6c5 138 }
139 }
df28d6c5 140 }
141
142 return $success;
2b051f1c 143
df28d6c5 144}
145
a3fb1c45 146/// FUNCTIONS TO MODIFY TABLES ////////////////////////////////////////////
147
18a97fd8 148/**
fbc21ae8 149 * Add a new field to a table, or modify an existing one (if oldfield is defined).
150 *
151 * @uses $CFG
152 * @uses $db
89dcb99d 153 * @param string $table ?
154 * @param string $oldfield ?
155 * @param string $field ?
156 * @param string $type ?
157 * @param string $size ?
158 * @param string $signed ?
159 * @param string $default ?
160 * @param string $null ?
fbc21ae8 161 * @todo Finish documenting this function
162 */
18a97fd8 163
d4419d55 164function table_column($table, $oldfield, $field, $type='integer', $size='10',
165 $signed='unsigned', $default='0', $null='not null', $after='') {
8a230a7d 166 global $CFG, $db;
a3fb1c45 167
168 switch (strtolower($CFG->dbtype)) {
169
d4419d55 170 case 'mysql':
171 case 'mysqlt':
a3fb1c45 172
173 switch (strtolower($type)) {
d4419d55 174 case 'text':
175 $type = 'TEXT';
176 $signed = '';
c2cb4545 177 break;
d4419d55 178 case 'integer':
179 $type = 'INTEGER('. $size .')';
a3fb1c45 180 break;
d4419d55 181 case 'varchar':
182 $type = 'VARCHAR('. $size .')';
183 $signed = '';
a3fb1c45 184 break;
185 }
186
187 if (!empty($oldfield)) {
d4419d55 188 $operation = 'CHANGE '. $oldfield .' '. $field;
a3fb1c45 189 } else {
d4419d55 190 $operation = 'ADD '. $field;
a3fb1c45 191 }
192
d4419d55 193 $default = 'DEFAULT \''. $default .'\'';
a3fb1c45 194
195 if (!empty($after)) {
d4419d55 196 $after = 'AFTER `'. $after .'`';
a3fb1c45 197 }
198
d4419d55 199 return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' '. $operation .' '. $type .' '. $signed .' '. $default .' '. $null .' '. $after);
a3fb1c45 200 break;
201
d4419d55 202 case 'postgres7': // From Petri Asikainen
8a230a7d 203 //Check db-version
204 $dbinfo = $db->ServerInfo();
29622339 205 $dbver = substr($dbinfo['version'],0,3);
8f0cd6ef 206
5a4d292b 207 //to prevent conflicts with reserved words
d4419d55 208 $realfield = '"'. $field .'"';
209 $field = '"'. $field .'_alter_column_tmp"';
210 $oldfield = '"'. $oldfield .'"';
5a4d292b 211
212 switch (strtolower($type)) {
d4419d55 213 case 'integer':
09d4c9a9 214 if ($size <= 4) {
d4419d55 215 $type = 'INT2';
8f0cd6ef 216 }
09d4c9a9 217 if ($size <= 10) {
d4419d55 218 $type = 'INT';
5a4d292b 219 }
09d4c9a9 220 if ($size > 10) {
d4419d55 221 $type = 'INT8';
5a4d292b 222 }
223 break;
d4419d55 224 case 'varchar':
225 $type = 'VARCHAR('. $size .')';
5a4d292b 226 break;
227 }
228
d4419d55 229 $default = '\''. $default .'\'';
5a4d292b 230
231 //After is not implemented in postgesql
232 //if (!empty($after)) {
233 // $after = "AFTER '$after'";
234 //}
235
d05024a7 236 //Use transactions
d4419d55 237 execute_sql('BEGIN');
d05024a7 238
239 //Allways use temporaly column
d4419d55 240 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
d05024a7 241 //Add default values
d4419d55 242 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default);
8f0cd6ef 243
5a4d292b 244
d4419d55 245 if ($dbver >= '7.3') {
8a230a7d 246 // modifying 'not null' is posible before 7.3
247 //update default values to table
d4419d55 248 if ($null == 'NOT NULL') {
249 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default .' WHERE '. $field .' IS NULL');
250 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
8a230a7d 251 } else {
d4419d55 252 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' DROP NOT NULL');
8a230a7d 253 }
5a4d292b 254 }
8a230a7d 255
d4419d55 256 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET DEFAULT '. $default);
8f0cd6ef 257
d4419d55 258 if ( $oldfield != '""' ) {
259 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .' = '. $oldfield);
260 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' DROP COLUMN '. $oldfield);
d05024a7 261 }
5a4d292b 262
7cf1c7bd 263 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $field .' TO '. $realfield);
8f0cd6ef 264
d4419d55 265 return execute_sql('COMMIT');
5a4d292b 266 break;
a3fb1c45 267
268 default:
269 switch (strtolower($type)) {
d4419d55 270 case 'integer':
271 $type = 'INTEGER';
a3fb1c45 272 break;
d4419d55 273 case 'varchar':
274 $type = 'VARCHAR';
92230499 275 break;
a3fb1c45 276 }
277
d4419d55 278 $default = 'DEFAULT \''. $default .'\'';
a3fb1c45 279
280 if (!empty($after)) {
d4419d55 281 $after = 'AFTER '. $after;
a3fb1c45 282 }
283
284 if (!empty($oldfield)) {
d4419d55 285 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $oldfield .' '. $field);
a3fb1c45 286 } else {
d4419d55 287 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
a3fb1c45 288 }
289
d4419d55 290 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
291 return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $default);
a3fb1c45 292 break;
293
294 }
295}
296
297
298
299/// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////
df28d6c5 300
18a97fd8 301/**
fbc21ae8 302 * Returns true or false depending on whether the specified record exists
303 *
304 * @uses $CFG
305 * @param string $table The database table to be checked for the record.
306 * @param string $field1 The first table field to be checked for a given value. Do not supply a field1 string to leave out a WHERE clause altogether.
307 * @param string $value1 The value to match if field1 is specified.
308 * @param string $field2 The second table field to be checked for a given value.
309 * @param string $value2 The value to match if field2 is specified.
310 * @param string $field3 The third table field to be checked for a given value.
311 * @param string $value3 The value to match if field3 is specified.
312 * @return boolean True is record exists
313 */
d4419d55 314function record_exists($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
df28d6c5 315
316 global $CFG;
317
5c63e0c4 318 if ($field1) {
d4419d55 319 $select = 'WHERE '. $field1 .' = \''. $value1 .'\'';
9fa49e22 320 if ($field2) {
d4419d55 321 $select .= ' AND '. $field2 .' = \''. $value2 .'\'';
9fa49e22 322 if ($field3) {
d4419d55 323 $select .= ' AND '. $field3 .' = \''. $value3 .'\'';
df28d6c5 324 }
325 }
5c63e0c4 326 } else {
d4419d55 327 $select = '';
df28d6c5 328 }
329
d4419d55 330 return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select .' LIMIT 1');
df28d6c5 331}
332
333
18a97fd8 334/**
fbc21ae8 335* Determine whether a specified record exists.
8f0cd6ef 336*
fbc21ae8 337* This function returns true if the SQL executed returns records.
18a97fd8 338*
fbc21ae8 339* @uses $CFG
340* @uses $db
341* @param string $sql The SQL statement to be executed.
342* @return boolean
18a97fd8 343*/
df28d6c5 344function record_exists_sql($sql) {
df28d6c5 345
06b1db82 346 global $CFG, $db;
df28d6c5 347
9c7fee6c 348 if (!$rs = $db->Execute($sql)) {
2eef791f 349 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 350 notify($db->ErrorMsg().'<br /><br />'.$sql);
9c7fee6c 351 }
352 return false;
353 }
df28d6c5 354
355 if ( $rs->RecordCount() ) {
356 return true;
357 } else {
358 return false;
359 }
360}
361
362
18a97fd8 363/**
fbc21ae8 364 * Get all specified records from the specified table and return the count of them
365 *
366 * @uses $CFG
367 * @param string $table The database table to be checked against.
368 * @param string $field1 The first table field to be checked for a given value. Do not supply a field1 string to leave out a WHERE clause altogether.
369 * @param string $value1 The value to match if field1 is specified.
370 * @param string $field2 The second table field to be checked for a given value.
371 * @param string $value2 The value to match if field2 is specified.
372 * @param string $field3 The third table field to be checked for a given value.
373 * @param string $value3 The value to match if field3 is specified.
374 * @return int The count of records returned from the specified criteria.
375 */
d4419d55 376function count_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
df28d6c5 377
378 global $CFG;
379
5c63e0c4 380 if ($field1) {
d4419d55 381 $select = 'WHERE '. $field1 .' = \''. $value1 .'\'';
9fa49e22 382 if ($field2) {
d4419d55 383 $select .= ' AND '. $field2 .' = \''. $value2 .'\'';
9fa49e22 384 if ($field3) {
d4419d55 385 $select .= ' AND '. $field3 .' = \''. $value3 .'\'';
df28d6c5 386 }
387 }
5c63e0c4 388 } else {
d4419d55 389 $select = '';
df28d6c5 390 }
391
d4419d55 392 return count_records_sql('SELECT COUNT(*) FROM '. $CFG->prefix . $table .' '. $select);
df28d6c5 393}
394
18a97fd8 395/**
fbc21ae8 396 * Get all the records and count them
397 *
398 * @uses $CFG
399 * @param string $table The database table to be checked against.
400 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
401 * @param string $countitem The count string to be used in the SQL call. Default is COUNT(*).
402 * @return int The count of records returned from the specified criteria.
d0d56205 403 */
d4419d55 404function count_records_select($table, $select='', $countitem='COUNT(*)') {
9fa49e22 405
406 global $CFG;
407
d26d7ed0 408 if ($select) {
d4419d55 409 $select = 'WHERE '.$select;
d26d7ed0 410 }
411
d4419d55 412 return count_records_sql('SELECT '. $countitem .' FROM '. $CFG->prefix . $table .' '. $select);
9fa49e22 413}
414
415
18a97fd8 416/**
fbc21ae8 417 * Get all the records returned from the specified SQL call and return the count of them
418 *
419 * @uses $CFG
420 * @uses $db
421 * @param string $sql The SQL string you wish to be executed.
422 * @return int The count of records returned from the specified SQL string.
423 */
df28d6c5 424function count_records_sql($sql) {
df28d6c5 425
06b1db82 426 global $CFG, $db;
df28d6c5 427
d4419d55 428 $rs = $db->Execute($sql);
9c7fee6c 429 if (!$rs) {
2eef791f 430 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 431 notify($db->ErrorMsg() .'<br /><br />'. $sql);
9c7fee6c 432 }
433 return 0;
434 }
df28d6c5 435
436 return $rs->fields[0];
437}
438
a3fb1c45 439
440
441
442/// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA ///////////////////////////////////
443
18a97fd8 444/**
fbc21ae8 445 * Get a single record as an object
446 *
447 * @uses $CFG
448 * @param string $table The name of the table to select from
449 * @param string $field1 The name of the field for the first criteria
450 * @param string $value1 The value of the field for the first criteria
451 * @param string $field2 The name of the field for the second criteria
452 * @param string $value2 The value of the field for the second criteria
453 * @param string $field3 The name of the field for the third criteria
454 * @param string $value3 The value of the field for the third criteria
455 * @return object(fieldset) A fieldset object containing the first record selected
456 * @todo Isn't the return from this function an associative array (array type) ?
457 */
d4419d55 458function get_record($table, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {
8f0cd6ef 459
7427070a 460 global $CFG ;
df28d6c5 461
d4419d55 462 $select = 'WHERE '. $field1 .' = \''. $value1 .'\'';
df28d6c5 463
9fa49e22 464 if ($field2) {
d4419d55 465 $select .= ' AND '. $field2 .' = \''. $value2 .'\'';
9fa49e22 466 if ($field3) {
d4419d55 467 $select .= ' AND '. $field3 .' = \''. $value3 .'\'';
df28d6c5 468 }
469 }
470
d4419d55 471 return get_record_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select);
df28d6c5 472}
473
18a97fd8 474/**
fbc21ae8 475 * Get a single record as an object using the specified SQL statement
476 *
477 * A LIMIT is normally added to only look for 1 record
478 *
479 * @uses $CFG
480 * @uses $db
481 * @param string $sql The SQL string you wish to be executed.
482 * @return boolean
483 * @todo include code example of SQL calls including using LIMIT for only 1 record
484 */
df28d6c5 485function get_record_sql($sql) {
df28d6c5 486
4d7a3735 487 global $db, $CFG;
df28d6c5 488
2eef791f 489 if (isset($CFG->debug) and $CFG->debug > 7) { // Debugging mode - don't use limit
d4419d55 490 $limit = '';
4d7a3735 491 } else {
d4419d55 492 $limit = ' LIMIT 1'; // Workaround - limit to one record
4d7a3735 493 }
494
d4419d55 495 if (!$rs = $db->Execute($sql . $limit)) {
2eef791f 496 if (isset($CFG->debug) and $CFG->debug > 7) { // Debugging mode - print checks
d4419d55 497 notify( $db->ErrorMsg() . '<br /><br />'. $sql . $limit );
7618a8eb 498 }
499 return false;
500 }
4d7a3735 501
7618a8eb 502 if (!$recordcount = $rs->RecordCount()) {
503 return false; // Found no records
4d7a3735 504 }
df28d6c5 505
7618a8eb 506 if ($recordcount == 1) { // Found one record
df28d6c5 507 return (object)$rs->fields;
4d7a3735 508
7618a8eb 509 } else { // Error: found more than one record
d4419d55 510 notify('Error: Turn off debugging to hide this error.');
511 notify($sql . $limit);
4d7a3735 512 if ($records = $rs->GetAssoc(true)) {
d4419d55 513 notify('Found more than one record in get_record_sql !');
4d7a3735 514 print_object($records);
4d7a3735 515 } else {
d4419d55 516 notify('Very strange error in get_record_sql !');
7618a8eb 517 print_object($rs);
4d7a3735 518 }
d4419d55 519 print_continue($CFG->wwwroot .'/admin/config.php');
df28d6c5 520 }
521}
522
18a97fd8 523/**
fbc21ae8 524 * Gets one record from a table, as an object
525 *
526 * @uses $CFG
527 * @param string $table The database table to be checked against.
528 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
529 * @param string $fields A comma separated list of fields to be returned from the chosen table.
530 * @return array An associative array with the results from the SQL call.
d0d56205 531 */
d4419d55 532function get_record_select($table, $select='', $fields='*') {
18496c59 533
534 global $CFG;
535
536 if ($select) {
d4419d55 537 $select = 'WHERE '. $select;
18496c59 538 }
539
d4419d55 540 return get_record_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select);
18496c59 541}
542
543
18a97fd8 544/**
fbc21ae8 545 * Get a number of records as an array of objects
546 *
547 * Can optionally be sorted eg "time ASC" or "time DESC"
548 * If "fields" is specified, only those fields are returned
549 * The "key" is the first column returned, eg usually "id"
550 * limitfrom and limitnum must both be specified or not at all
551 *
552 * @uses $CFG
553 * @param string $table The database table to be checked against.
554 * @param string $field ?
555 * @param string $value ?
556 * @param string $sort ?
557 * @param string $fields A comma separated list of fields to be returned from the chosen table.
558 * @param int $limitfrom ?
559 * @param int $limitnum ?
c6d15803 560 * @return array|false An associative array with the results from the SQL call or false if error.
fbc21ae8 561 * @todo Finish documenting this function
562 */
d4419d55 563function get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
df28d6c5 564
565 global $CFG;
566
9fa49e22 567 if ($field) {
d4419d55 568 $select = 'WHERE '. $field .' = \''. $value .'\'';
5c63e0c4 569 } else {
d4419d55 570 $select = '';
df28d6c5 571 }
5c63e0c4 572
d4419d55 573 if ($limitfrom !== '') {
0eeac484 574 switch ($CFG->dbtype) {
d4419d55 575 case 'mysql':
576 $limit = 'LIMIT '. $limitfrom .','. $limitnum;
0eeac484 577 break;
d4419d55 578 case 'postgres7':
579 $limit = 'LIMIT '. $limitnum .' OFFSET '. $limitfrom;
0eeac484 580 break;
8f0cd6ef 581 default:
d4419d55 582 $limit = 'LIMIT '. $limitnum .','. $limitfrom;
0eeac484 583 }
584 } else {
d4419d55 585 $limit = '';
0eeac484 586 }
587
df28d6c5 588 if ($sort) {
d4419d55 589 $sort = 'ORDER BY '. $sort;
df28d6c5 590 }
591
d4419d55 592 return get_records_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort .' '. $limit);
df28d6c5 593}
594
18a97fd8 595/**
fbc21ae8 596 * Get a number of records as an array of objects
597 *
598 * Can optionally be sorted eg "time ASC" or "time DESC"
599 * "select" is a fragment of SQL to define the selection criteria
600 * The "key" is the first column returned, eg usually "id"
601 * limitfrom and limitnum must both be specified or not at all
602 *
603 * @uses $CFG
604 * @param string $table The database table to be checked against.
605 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
606 * @param string $sort ?
607 * @param string $fields A comma separated list of fields to be returned from the chosen table.
608 * @param int $limitfrom ?
609 * @param int $limitnum ?
c6d15803 610 * @return array|false An associative array with the results from the SQL call or false if error.
fbc21ae8 611 * @todo Finish documenting this function
612 */
d4419d55 613function get_records_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
9fa49e22 614
615 global $CFG;
616
d26d7ed0 617 if ($select) {
d4419d55 618 $select = 'WHERE '. $select;
5c63e0c4 619 }
620
d4419d55 621 if ($limitfrom !== '') {
4f91b296 622 switch ($CFG->dbtype) {
d4419d55 623 case 'mysql':
624 $limit = 'LIMIT '. $limitfrom .','. $limitnum;
4f91b296 625 break;
d4419d55 626 case 'postgres7':
627 $limit = 'LIMIT '. $limitnum .' OFFSET '. $limitfrom;
4f91b296 628 break;
8f0cd6ef 629 default:
d4419d55 630 $limit = 'LIMIT '. $limitnum .','. $limitfrom;
4f91b296 631 }
632 } else {
d4419d55 633 $limit = '';
4f91b296 634 }
635
5c63e0c4 636 if ($sort) {
d4419d55 637 $sort = 'ORDER BY '. $sort;
d26d7ed0 638 }
639
d4419d55 640 return get_records_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort .' '. $limit);
9fa49e22 641}
642
df28d6c5 643
18a97fd8 644/**
fbc21ae8 645 * Get a number of records as an array of objects
646 *
647 * Differs from get_records() in that the values variable
648 * can be a comma-separated list of values eg "4,5,6,10"
649 * Can optionally be sorted eg "time ASC" or "time DESC"
650 * The "key" is the first column returned, eg usually "id"
651 *
652 * @uses $CFG
653 * @param string $table The database table to be checked against.
654 * @param string $field ?
655 * @param string $values ?
656 * @param string $sort ?
657 * @param string $fields A comma separated list of fields to be returned from the chosen table.
c6d15803 658 * @return array|false An associative array with the results from the SQL call or false if error.
fbc21ae8 659 * @todo Finish documenting this function
660 */
d4419d55 661function get_records_list($table, $field='', $values='', $sort='', $fields='*') {
df28d6c5 662
663 global $CFG;
664
9fa49e22 665 if ($field) {
d4419d55 666 $select = 'WHERE '. $field .' IN ( '. $values .')';
5c63e0c4 667 } else {
d4419d55 668 $select = '';
df28d6c5 669 }
5c63e0c4 670
df28d6c5 671 if ($sort) {
d4419d55 672 $sort = 'ORDER BY '. $sort;
df28d6c5 673 }
674
d4419d55 675 return get_records_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort);
df28d6c5 676}
677
678
9fa49e22 679
18a97fd8 680/**
fbc21ae8 681 * Get a number of records as an array of objects
682 *
683 * The "key" is the first column returned, eg usually "id"
684 * The sql statement is provided as a string.
685 *
686 * @uses $CFG
687 * @uses $db
688 * @param string $sql The SQL string you wish to be executed.
c6d15803 689 * @return array|false Returns an associative array with found records or false if no records or error occured.
fbc21ae8 690 */
df28d6c5 691function get_records_sql($sql) {
df28d6c5 692
fbc21ae8 693 global $CFG, $db;
df28d6c5 694
9c7fee6c 695 if (!$rs = $db->Execute($sql)) {
2eef791f 696 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 697 notify($db->ErrorMsg() .'<br /><br />'. $sql);
9c7fee6c 698 }
699 return false;
700 }
8f0cd6ef 701
df28d6c5 702 if ( $rs->RecordCount() > 0 ) {
703 if ($records = $rs->GetAssoc(true)) {
704 foreach ($records as $key => $record) {
705 $objects[$key] = (object) $record;
706 }
707 return $objects;
708 } else {
709 return false;
710 }
711 } else {
712 return false;
713 }
714}
715
18a97fd8 716/**
717* Get a number of records as an array of objects
8f0cd6ef 718*
18a97fd8 719* Can optionally be sorted eg "time ASC" or "time DESC"
720* If "fields" is specified, only those fields are returned
721* The "key" is the first column returned, eg usually "id"
8f0cd6ef 722*
fbc21ae8 723 * @uses $CFG
724 * @param string $table The database table to be checked against.
725 * @param string $field ?
726 * @param string $value ?
727 * @param string $sort ?
728 * @param string $fields A comma separated list of fields to be returned from the chosen table.
729 * @return array An associative array with the results from the SQL call.
7cf1c7bd 730* @todo Finish documenting this function
18a97fd8 731*/
d4419d55 732function get_records_menu($table, $field='', $value='', $sort='', $fields='*') {
9fa49e22 733
734 global $CFG;
735
736 if ($field) {
d4419d55 737 $select = 'WHERE '. $field .' = \''. $value .'\'';
5c63e0c4 738 } else {
d4419d55 739 $select = '';
9fa49e22 740 }
5c63e0c4 741
9fa49e22 742 if ($sort) {
d4419d55 743 $sort = 'ORDER BY '. $sort;
9fa49e22 744 }
745
d4419d55 746 return get_records_sql_menu('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort);
9fa49e22 747}
748
fbc21ae8 749
18a97fd8 750/**
fbc21ae8 751 * Get a number of records as an array of values
752 *
753 * Can optionally be sorted eg "time ASC" or "time DESC"
754 * "select" is a fragment of SQL to define the selection criteria
755 * Returns associative array of first two fields
756 *
757 * @uses $CFG
758 * @param string $table The database table to be checked against.
759 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
760 * @param string $sort ?
761 * @param string $fields A comma separated list of fields to be returned from the chosen table.
762 * @return array An associative array with the results from the SQL call.
763 * @todo Finish documenting this function
764 */
d4419d55 765function get_records_select_menu($table, $select='', $sort='', $fields='*') {
9fa49e22 766
767 global $CFG;
768
d26d7ed0 769 if ($select) {
d4419d55 770 $select = 'WHERE '. $select;
d26d7ed0 771 }
772
5c63e0c4 773 if ($sort) {
d4419d55 774 $sort = 'ORDER BY '. $sort;
5c63e0c4 775 }
776
d4419d55 777 return get_records_sql_menu('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort);
9fa49e22 778}
779
780
18a97fd8 781/**
fbc21ae8 782 * Retrieve an array of the first two columns returned from a SQL statment.
783 *
784 * Given a SQL statement, this function returns an associative
785 * array of the first two columns. This is most useful in
786 * combination with the {@link choose_from_menu()} function to create
787 * a form menu.
788 *
789 * @uses $CFG
790 * @uses $db
791 * @param string $sql The SQL string you wish to be executed.
792 * @return boolean
793 * @todo Finish documenting this function
794 */
df28d6c5 795function get_records_sql_menu($sql) {
df28d6c5 796
06b1db82 797 global $CFG, $db;
df28d6c5 798
9c7fee6c 799 if (!$rs = $db->Execute($sql)) {
2eef791f 800 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 801 notify($db->ErrorMsg() .'<br /><br />'. $sql);
9c7fee6c 802 }
803 return false;
804 }
df28d6c5 805
806 if ( $rs->RecordCount() > 0 ) {
807 while (!$rs->EOF) {
808 $menu[$rs->fields[0]] = $rs->fields[1];
809 $rs->MoveNext();
810 }
811 return $menu;
8f0cd6ef 812
df28d6c5 813 } else {
814 return false;
815 }
816}
817
18a97fd8 818/**
fbc21ae8 819 * Get a single field from a database record
820 *
821 * @uses $CFG
822 * @uses $db
823 * @param string $table The database table to be checked against.
824 * @param string $field1 The first table field to be checked for a given value. Do not supply a field1 string to leave out a WHERE clause altogether.
825 * @param string $value1 The value to match if field1 is specified.
826 * @param string $field2 The second table field to be checked for a given value.
827 * @param string $value2 The value to match if field2 is specified.
828 * @param string $field3 The third table field to be checked for a given value.
829 * @param string $value3 The value to match if field3 is specified.
c6d15803 830 * @return mixed|false Returns the value return from the SQL statment or false if an error occured.
fbc21ae8 831 * @todo Finish documenting this function
832 */
d4419d55 833function get_field($table, $return, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {
df28d6c5 834
835 global $db, $CFG;
836
d4419d55 837 $select = 'WHERE '. $field1 .' = \''. $value1 .'\'';
ec2a28a6 838
839 if ($field2) {
d4419d55 840 $select .= ' AND '. $field2 .' = \''. $value2 .'\'';
ec2a28a6 841 if ($field3) {
d4419d55 842 $select .= ' AND '. $field3 .' = \''. $value3 .'\'';
ec2a28a6 843 }
844 }
845
d4419d55 846 $rs = $db->Execute('SELECT '. $return .' FROM '. $CFG->prefix . $table .' '. $select);
9c7fee6c 847 if (!$rs) {
2eef791f 848 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 849 notify($db->ErrorMsg() .'<br /><br />SELECT '. $return .' FROM '. $CFG->prefix . $table .' '. $select);
9c7fee6c 850 }
851 return false;
852 }
df28d6c5 853
854 if ( $rs->RecordCount() == 1 ) {
d4419d55 855 return $rs->fields[$return];
df28d6c5 856 } else {
857 return false;
858 }
859}
860
b4bac9b6 861
862/**
fbc21ae8 863 * Get a single field from a database record
864 *
865 * @uses $CFG
866 * @uses $db
867 * @param string $sql The SQL string you wish to be executed.
c6d15803 868 * @return mixed|false Returns the value return from the SQL statment or false if an error occured.
fbc21ae8 869 * @todo Finish documenting this function
870 */
b4bac9b6 871function get_field_sql($sql) {
872
873 global $db, $CFG;
874
875 $rs = $db->Execute($sql);
876 if (!$rs) {
877 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 878 notify($db->ErrorMsg() .'<br /><br />'. $sql);
b4bac9b6 879 }
880 return false;
881 }
882
883 if ( $rs->RecordCount() == 1 ) {
884 return $rs->fields[0];
885 } else {
886 return false;
887 }
888}
889
18a97fd8 890/**
fbc21ae8 891 * Set a single field in a database record
892 *
893 * @uses $CFG
894 * @uses $db
895 * @param string $table The database table to be checked against.
896 * @param string $field1 The first table field to be checked for a given value. Do not supply a field1 string to leave out a WHERE clause altogether.
897 * @param string $value1 The value to match if field1 is specified.
898 * @param string $field2 The second table field to be checked for a given value.
899 * @param string $value2 The value to match if field2 is specified.
900 * @param string $field3 The third table field to be checked for a given value.
901 * @param string $value3 The value to match if field3 is specified.
902 * @return array An associative array with the results from the SQL call.
903 * @todo Verify return type
904 */
d4419d55 905function set_field($table, $newfield, $newvalue, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {
df28d6c5 906
907 global $db, $CFG;
908
d4419d55 909 $select = 'WHERE '. $field1 .' = \''. $value1 .'\'';
ec2a28a6 910
911 if ($field2) {
d4419d55 912 $select .= ' AND '. $field2 .' = \''. $value2 .'\'';
ec2a28a6 913 if ($field3) {
d4419d55 914 $select .= ' AND '. $field3 .' = \''. $value3 .'\'';
ec2a28a6 915 }
916 }
917
d4419d55 918 return $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $newfield .' = \''. $newvalue .'\' '. $select);
df28d6c5 919}
920
18a97fd8 921/**
fbc21ae8 922 * Delete one or more records from a table
923 *
924 * @uses $CFG
925 * @uses $db
926 * @param string $table The database table to be checked against.
927 * @param string $field1 The first table field to be checked for a given value. Do not supply a field1 string to leave out a WHERE clause altogether.
928 * @param string $value1 The value to match if field1 is specified.
929 * @param string $field2 The second table field to be checked for a given value.
930 * @param string $value2 The value to match if field2 is specified.
931 * @param string $field3 The third table field to be checked for a given value.
932 * @param string $value3 The value to match if field3 is specified.
933 * @return array An associative array with the results from the SQL call.
934 * @todo Verify return type
935 */
d4419d55 936function delete_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
df28d6c5 937
938 global $db, $CFG;
939
5c63e0c4 940 if ($field1) {
d4419d55 941 $select = 'WHERE '. $field1 .' = \''. $value1 .'\'';
9fa49e22 942 if ($field2) {
d4419d55 943 $select .= ' AND '. $field2 .' = \''. $value2 .'\'';
9fa49e22 944 if ($field3) {
d4419d55 945 $select .= ' AND '. $field3 .' = \''. $value3 .'\'';
df28d6c5 946 }
947 }
5c63e0c4 948 } else {
d4419d55 949 $select = '';
df28d6c5 950 }
951
d4419d55 952 return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
df28d6c5 953}
954
18a97fd8 955/**
fbc21ae8 956 * Delete one or more records from a table
957 *
958 * @uses $CFG
959 * @uses $db
960 * @param string $table The database table to be checked against.
961 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
962 * @return array An associative array with the results from the SQL call.
963 * @todo Verify return type.
964 */
d4419d55 965function delete_records_select($table, $select='') {
30f89d68 966
967 global $CFG, $db;
968
969 if ($select) {
d4419d55 970 $select = 'WHERE '.$select;
30f89d68 971 }
972
d4419d55 973 return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
30f89d68 974}
975
18a97fd8 976/**
fbc21ae8 977 * Insert a record into a table and return the "id" field if required
978 *
979 * If the return ID isn't required, then this just reports success as true/false.
980 * $dataobject is an object containing needed data
981 *
982 * @uses $db
983 * @uses $CFG
984 * @param string $table The database table to be checked against.
985 * @param array $dataobject An associative array with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
986 * @param boolean $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned.
987 * @param string $primarykey Defaults to 'id'. The primary key to use against the specified table when inserting the contents of $dataobject.
c6d15803 988 * @return int|false Returns the id for the new record created or false if an error occured.
fbc21ae8 989 * @todo Finish documenting this function. Dataobject is actually an associateive array, correct?
990 */
fcf9c450 991function insert_record($table, $dataobject, $returnid=true, $primarykey='id') {
8f0cd6ef 992
0892f7bd 993 global $db, $CFG;
994
d4469f2a 995/// Execute a dummy query to get an empty recordset
d4419d55 996 if (!$rs = $db->Execute('SELECT * FROM '. $CFG->prefix . $table .' WHERE '. $primarykey .' = \'-1\'')) {
d4469f2a 997 return false;
998 }
8f0cd6ef 999
d4469f2a 1000/// Get the correct SQL from adoDB
1001 if (!$insertSQL = $db->GetInsertSQL($rs, (array)$dataobject, true)) {
1002 return false;
1003 }
8f0cd6ef 1004
d4469f2a 1005/// Run the SQL statement
1006 if (!$rs = $db->Execute($insertSQL)) {
2eef791f 1007 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 1008 notify($db->ErrorMsg() .'<br /><br />'.$insertSQL);
9c7fee6c 1009 }
0892f7bd 1010 return false;
1011 }
8f0cd6ef 1012
d4469f2a 1013/// If a return ID is not needed then just return true now
8f0cd6ef 1014 if (!$returnid) {
0892f7bd 1015 return true;
1016 }
1017
d4469f2a 1018/// Find the return ID of the newly inserted record
0892f7bd 1019 switch ($CFG->dbtype) {
d4419d55 1020 case 'postgres7': // Just loves to be special
0892f7bd 1021 $oid = $db->Insert_ID();
d4419d55 1022 if ($rs = $db->Execute('SELECT '. $primarykey .' FROM '. $CFG->prefix . $table .' WHERE oid = '. $oid)) {
0892f7bd 1023 if ($rs->RecordCount() == 1) {
fcf9c450 1024 return (integer) $rs->fields[0];
0892f7bd 1025 }
1026 }
1027 return false;
1028
8f0cd6ef 1029 default:
d4469f2a 1030 return $db->Insert_ID(); // Should work on most databases, but not all!
0892f7bd 1031 }
1032}
1033
1034
18a97fd8 1035/**
fbc21ae8 1036 * Update a record in a table
1037 *
1038 * $dataobject is an object containing needed data
1039 * Relies on $dataobject having a variable "id" to
1040 * specify the record to update
1041 *
1042 * @uses $CFG
1043 * @uses $db
1044 * @param string $table The database table to be checked against.
1045 * @param array $dataobject An associative array with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1046 * @return boolean
1047 * @todo Finish documenting this function. Dataobject is actually an associateive array, correct?
1048 */
df28d6c5 1049function update_record($table, $dataobject) {
df28d6c5 1050
1051 global $db, $CFG;
1052
1053 if (! isset($dataobject->id) ) {
1054 return false;
1055 }
1056
1057 // Determine all the fields in the table
d4419d55 1058 if (!$columns = $db->MetaColumns($CFG->prefix . $table)) {
df28d6c5 1059 return false;
1060 }
1061 $data = (array)$dataobject;
1062
1063 // Pull out data matching these fields
1064 foreach ($columns as $column) {
d4419d55 1065 if ($column->name <> 'id' and isset($data[$column->name]) ) {
df28d6c5 1066 $ddd[$column->name] = $data[$column->name];
1067 }
1068 }
1069
1070 // Construct SQL queries
1071 $numddd = count($ddd);
1072 $count = 0;
d4419d55 1073 $update = '';
df28d6c5 1074
1075 foreach ($ddd as $key => $value) {
1076 $count++;
d4419d55 1077 $update .= $key .' = \''. $value .'\'';
df28d6c5 1078 if ($count < $numddd) {
d4419d55 1079 $update .= ', ';
df28d6c5 1080 }
1081 }
1082
d4419d55 1083 if ($rs = $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'')) {
df28d6c5 1084 return true;
1085 } else {
2eef791f 1086 if (isset($CFG->debug) and $CFG->debug > 7) {
d4419d55 1087 notify($db->ErrorMsg() .'<br /><br />UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'');
9c7fee6c 1088 }
df28d6c5 1089 return false;
1090 }
1091}
1092
1093
df28d6c5 1094
1095
1096/// USER DATABASE ////////////////////////////////////////////////
1097
18a97fd8 1098/**
fbc21ae8 1099 * Get a complete user record, which includes all the info
1100 *
1101 * in the user record, as well as membership information
1102 * Suitable for setting as $USER session cookie.
1103 *
1104 * @uses $CFG
1105 * @uses SITEID
1106 * @param string $field The first table field to be checked for a given value.
1107 * @param string $value The value to match for $field.
89dcb99d 1108 * @return user A {@link $USER} object.
fbc21ae8 1109 * @todo Finish documenting this function
1110 */
df28d6c5 1111function get_user_info_from_db($field, $value) {
df28d6c5 1112
ca60b03b 1113 global $CFG;
1114
18496c59 1115 if (!$field or !$value) {
df28d6c5 1116 return false;
df28d6c5 1117 }
1118
ca60b03b 1119/// Get all the basic user data
1120
d4419d55 1121 if (! $user = get_record_select('user', $field .' = \''. $value .'\' AND deleted <> \'1\'')) {
18496c59 1122 return false;
1123 }
df28d6c5 1124
ca60b03b 1125/// Add membership information
1126
d4419d55 1127 if ($admins = get_records('user_admins', 'userid', $user->id)) {
ca60b03b 1128 $user->admin = true;
1129 }
df28d6c5 1130
222ac91b 1131 $user->student[SITEID] = isstudent(SITEID, $user->id);
df28d6c5 1132
ca60b03b 1133/// Determine enrolments based on current enrolment module
df28d6c5 1134
d4419d55 1135 require_once($CFG->dirroot .'/enrol/'. $CFG->enrol .'/enrol.php');
ca60b03b 1136 $enrol = new enrolment_plugin();
1137 $enrol->get_student_courses($user);
1138 $enrol->get_teacher_courses($user);
df28d6c5 1139
ca60b03b 1140/// Get various settings and preferences
18496c59 1141
d4419d55 1142 if ($displays = get_records('course_display', 'userid', $user->id)) {
b86fc0e2 1143 foreach ($displays as $display) {
1144 $user->display[$display->course] = $display->display;
1145 }
1146 }
1147
ca60b03b 1148 if ($preferences = get_records('user_preferences', 'userid', $user->id)) {
1149 foreach ($preferences as $preference) {
1150 $user->preference[$preference->name] = $preference->value;
1151 }
1152 }
1153
d4419d55 1154 if ($groups = get_records('groups_members', 'userid', $user->id)) {
3facf28f 1155 foreach ($groups as $groupmember) {
d4419d55 1156 $courseid = get_field('groups', 'courseid', 'id', $groupmember->groupid);
3facf28f 1157 $user->groupmember[$courseid] = $groupmember->groupid;
f374fb10 1158 }
1159 }
1160
70812e39 1161
18496c59 1162 return $user;
df28d6c5 1163}
1164
df28d6c5 1165
18a97fd8 1166/**
fbc21ae8 1167 * Does this username and password specify a valid admin user?
1168 *
1169 * @uses $CFG
1170 * @param string $username The name of the user to be tested for admin rights
1171 * @param string $md5password The password supplied by the user in md5 encrypted format.
1172 * @return boolean
1173 */
df28d6c5 1174function adminlogin($username, $md5password) {
df28d6c5 1175
1176 global $CFG;
1177
8f0cd6ef 1178 return record_exists_sql("SELECT u.id
1179 FROM {$CFG->prefix}user u,
1180 {$CFG->prefix}user_admins a
1181 WHERE u.id = a.userid
1182 AND u.username = '$username'
df28d6c5 1183 AND u.password = '$md5password'");
1184}
1185
18a97fd8 1186/**
fbc21ae8 1187 * Get the guest user information from the database
1188 *
1189 * @return object(user) An associative array with the details of the guest user account.
1190 * @todo Is object(user) a correct return type? Or is array the proper return type with a note that the contents include all details for a user.
1191 */
9fa49e22 1192function get_guest() {
d4419d55 1193 return get_user_info_from_db('username', 'guest');
9fa49e22 1194}
1195
1196
18a97fd8 1197/**
fbc21ae8 1198 * Returns $user object of the main admin user
1199 *
1200 * @uses $CFG
1201 * @return object(admin) An associative array representing the admin user.
1202 * @todo Verify documentation of this function
1203 */
df28d6c5 1204function get_admin () {
df28d6c5 1205
1206 global $CFG;
1207
1208 if ( $admins = get_admins() ) {
1209 foreach ($admins as $admin) {
8f0cd6ef 1210 return $admin; // ie the first one
df28d6c5 1211 }
1212 } else {
1213 return false;
1214 }
1215}
1216
18a97fd8 1217/**
fbc21ae8 1218 * Returns list of all admins
1219 *
1220 * @uses $CFG
89dcb99d 1221 * @return array An array of {@link $USER} records.
fbc21ae8 1222 * @todo Finish documenting this function
1223 */
df28d6c5 1224function get_admins() {
df28d6c5 1225
1226 global $CFG;
1227
8f0cd6ef 1228 return get_records_sql("SELECT u.*, a.id as adminid
1229 FROM {$CFG->prefix}user u,
ebc3bd2b 1230 {$CFG->prefix}user_admins a
1231 WHERE a.userid = u.id
427fdf66 1232 ORDER BY a.id ASC");
df28d6c5 1233}
1234
18a97fd8 1235/**
fbc21ae8 1236 * Returns list of all creators
1237 *
1238 * @uses $CFG
89dcb99d 1239 * @return array An array of {@link $USER} objects.
fbc21ae8 1240 * @todo Finish documenting this function
1241 */
1924074c 1242function get_creators() {
1924074c 1243
1244 global $CFG;
1245
1246 return get_records_sql("SELECT u.*
1247 FROM {$CFG->prefix}user u,
1248 {$CFG->prefix}user_coursecreators a
1249 WHERE a.userid = u.id
1250 ORDER BY u.id ASC");
1251}
df28d6c5 1252
18a97fd8 1253/**
fbc21ae8 1254 * Returns $user object of the main teacher for a course
1255 *
1256 * @uses $CFG
1257 * @param int $courseid The course in question.
89dcb99d 1258 * @return user|false A {@link $USER} record of the main teacher for the specified course or false if error.
fbc21ae8 1259 * @todo Finish documenting this function
1260 */
df28d6c5 1261function get_teacher($courseid) {
df28d6c5 1262
1263 global $CFG;
1264
d4419d55 1265 if ( $teachers = get_course_teachers($courseid, 't.authority ASC')) {
df28d6c5 1266 foreach ($teachers as $teacher) {
1267 if ($teacher->authority) {
1268 return $teacher; // the highest authority teacher
1269 }
1270 }
1271 } else {
1272 return false;
1273 }
1274}
1275
18a97fd8 1276/**
fbc21ae8 1277 * Searches logs to find all enrolments since a certain date
1278 *
1279 * used to print recent activity
1280 *
1281 * @uses $CFG
1282 * @param int $courseid The course in question.
89dcb99d 1283 * @return array|false An array of {@link $USER} records or false if error.
fbc21ae8 1284 * @todo Finish documenting this function
1285 */
0a08581d 1286function get_recent_enrolments($courseid, $timestart) {
0a08581d 1287
1288 global $CFG;
1289
fbf5081c 1290 return get_records_sql("SELECT DISTINCT u.id, u.firstname, u.lastname, l.time
0a08581d 1291 FROM {$CFG->prefix}user u,
1292 {$CFG->prefix}user_students s,
1293 {$CFG->prefix}log l
8f0cd6ef 1294 WHERE l.time > '$timestart'
0a08581d 1295 AND l.course = '$courseid'
8f0cd6ef 1296 AND l.module = 'course'
0a08581d 1297 AND l.action = 'enrol'
1298 AND l.info = u.id
1299 AND u.id = s.userid
64cbb55c 1300 AND s.course = '$courseid'
0a08581d 1301 ORDER BY l.time ASC");
1302}
1303
18a97fd8 1304/**
fbc21ae8 1305 * Returns array of userinfo of all students in this course
1306 * or on this site if courseid is id of site
1307 *
1308 * @uses $CFG
1309 * @uses SITEID
1310 * @param int $courseid The course in question.
1311 * @param string $sort ?
1312 * @param string $dir ?
1313 * @param int $page ?
1314 * @param int $recordsperpage ?
1315 * @param string $firstinitial ?
1316 * @param string $lastinitial ?
1317 * @param ? $group ?
1318 * @param string $search ?
1319 * @param string $fields A comma separated list of fields to be returned from the chosen table.
1320 * @param string $exceptions ?
89dcb99d 1321 * @return array An array of {@link $USER} records.
fbc21ae8 1322 * @todo Finish documenting this function
1323 */
d4419d55 1324function get_course_students($courseid, $sort='s.timeaccess', $dir='', $page=0, $recordsperpage=99999,
1325 $firstinitial='', $lastinitial='', $group=NULL, $search='', $fields='', $exceptions='') {
df28d6c5 1326
1327 global $CFG;
8f0cd6ef 1328
1329 if ($courseid == SITEID and $CFG->allusersaresitestudents) {
2700d113 1330 // return users with confirmed, undeleted accounts who are not site teachers
1331 // the following is a mess because of different conventions in the different user functions
1332 $sort = str_replace('s.timeaccess', 'lastaccess', $sort); // site users can't be sorted by timeaccess
1333 $sort = str_replace('timeaccess', 'lastaccess', $sort); // site users can't be sorted by timeaccess
1334 $sort = str_replace('u.', '', $sort); // the get_user function doesn't use the u. prefix to fields
8f0cd6ef 1335 $fields = str_replace('u.', '', $fields);
2700d113 1336 if ($sort) {
d4419d55 1337 $sort = $sort .' '. $dir;
2700d113 1338 }
1339 // Now we have to make sure site teachers are excluded
1340 if ($teachers = get_records('user_teachers', 'course', SITEID)) {
1341 foreach ($teachers as $teacher) {
d4419d55 1342 $exceptions .= ','. $teacher->userid;
2700d113 1343 }
1344 $exceptions = ltrim($exceptions, ',');
1345 }
8f0cd6ef 1346 return get_users(true, $search, true, $exceptions, $sort, $firstinitial, $lastinitial,
2700d113 1347 $page, $recordsperpage, $fields ? $fields : '*');
1348 }
df28d6c5 1349
4969ad74 1350 switch ($CFG->dbtype) {
d4419d55 1351 case 'mysql':
1352 $fullname = ' CONCAT(firstname," ",lastname) ';
1353 $limit = 'LIMIT '. $page .','. $recordsperpage;
1354 $LIKE = 'LIKE';
4969ad74 1355 break;
d4419d55 1356 case 'postgres7':
73fc8c1a 1357 $fullname = " firstname||' '||lastname ";
d4419d55 1358 $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page);
1359 $LIKE = 'ILIKE';
4969ad74 1360 break;
8f0cd6ef 1361 default:
d4419d55 1362 $fullname = ' firstname||" "||lastname ';
1363 $limit = 'LIMIT '. $recordsperpage .','. $page;
1364 $LIKE = 'ILIKE';
a328425d 1365 }
1366
0720313b 1367 $groupmembers = '';
f950af3c 1368
1369 // make sure it works on the site course
d4419d55 1370 $select = 's.course = \''. $courseid .'\' AND ';
222ac91b 1371 if ($courseid == SITEID) {
f950af3c 1372 $select = '';
1373 }
1374
d4419d55 1375 $select .= 's.userid = u.id AND u.deleted = \'0\' ';
73fc8c1a 1376
1284a926 1377 if (!$fields) {
1378 $fields = 'u.id, u.confirmed, u.username, u.firstname, u.lastname, '.
1379 'u.maildisplay, u.mailformat, u.maildigest, u.email, u.city, '.
1380 'u.country, u.picture, u.idnumber, u.department, u.institution, '.
1381 'u.emailstop, u.lang, u.timezone, s.timeaccess as lastaccess';
1382 }
1383
73fc8c1a 1384 if ($search) {
d4419d55 1385 $search = ' AND ('. $fullname .' '. $LIKE .'\'%'. $search .'%\' OR email '. $LIKE .'\'%'. $search .'%\') ';
73fc8c1a 1386 }
a328425d 1387
1388 if ($firstinitial) {
d4419d55 1389 $select .= ' AND u.firstname '. $LIKE .'\''. $firstinitial .'%\' ';
a328425d 1390 }
1391
1392 if ($lastinitial) {
d4419d55 1393 $select .= ' AND u.lastname '. $LIKE .'\''. $lastinitial .'%\' ';
4969ad74 1394 }
1395
3d35e6b7 1396 if ($group === 0) { /// Need something here to get all students not in a group
1397 return array();
1398
1399 } else if ($group !== NULL) {
d4419d55 1400 $groupmembers = ', '. $CFG->prefix .'groups_members gm ';
1401 $select .= ' AND u.id = gm.userid AND gm.groupid = \''. $group .'\'';
0720313b 1402 }
8f0cd6ef 1403
900df8b6 1404 if (!empty($exceptions)) {
d4419d55 1405 $select .= ' AND u.id NOT IN ('. $exceptions .')';
900df8b6 1406 }
0720313b 1407
8e4c9ef7 1408 if ($sort) {
d4419d55 1409 $sort = ' ORDER BY '. $sort .' ';
8e4c9ef7 1410 }
1411
2700d113 1412 $students = get_records_sql("SELECT $fields
488acd1b 1413 FROM {$CFG->prefix}user u,
1414 {$CFG->prefix}user_students s
1415 $groupmembers
73fc8c1a 1416 WHERE $select $search $sort $dir $limit");
2700d113 1417
1418 if ($courseid != SITEID) {
1419 return $students;
1420 }
8f0cd6ef 1421
2700d113 1422 // We are here because we need the students for the site.
1423 // These also include teachers on real courses minus those on the site
1424 if ($teachers = get_records('user_teachers', 'course', SITEID)) {
1425 foreach ($teachers as $teacher) {
d4419d55 1426 $exceptions .= ','. $teacher->userid;
2700d113 1427 }
1428 $exceptions = ltrim($exceptions, ',');
d4419d55 1429 $select .= ' AND u.id NOT IN ('. $exceptions .')';
2700d113 1430 }
1431 if (!$teachers = get_records_sql("SELECT $fields
1432 FROM {$CFG->prefix}user u,
1433 {$CFG->prefix}user_teachers s
1434 $groupmembers
1435 WHERE $select $search $sort $dir $limit")) {
1436 return $students;
1437 }
1438 if (!$students) {
1439 return $teachers;
1440 }
1441 return $teachers + $students;
df28d6c5 1442}
1443
fbc21ae8 1444
a328425d 1445/**
fbc21ae8 1446 * Counts the students in a given course (or site), or a subset of them
1447 *
1448 * @param object $course The course in question as a course object.
1449 * @param string $search ?
1450 * @param string $firstinitial ?
1451 * @param string $lastinitial ?
1452 * @param ? $group ?
1453 * @param string $exceptions ?
1454 * @return int
1455 * @todo Finish documenting this function
1456 */
d4419d55 1457function count_course_students($course, $search='', $firstinitial='', $lastinitial='', $group=NULL, $exceptions='') {
a328425d 1458
2700d113 1459 if ($students = get_course_students($course->id, '', '', 0, 999999, $firstinitial, $lastinitial, $group, $search, '', $exceptions)) {
1460 return count($students);
a328425d 1461 }
2700d113 1462 return 0;
a328425d 1463}
1464
1465
18a97fd8 1466/**
fbc21ae8 1467 * Returns list of all teachers in this course
1468 *
1469 * If $courseid matches the site id then this function
1470 * returns a list of all teachers for the site.
1471 *
1472 * @uses $CFG
1473 * @param int $courseid The course in question.
1474 * @param string $sort ?
1475 * @param string $exceptions ?
89dcb99d 1476 * @return array An array of {@link $USER} records.
fbc21ae8 1477 * @todo Finish documenting this function
1478 */
d4419d55 1479function get_course_teachers($courseid, $sort='t.authority ASC', $exceptions='') {
df28d6c5 1480
1481 global $CFG;
1482
900df8b6 1483 if (!empty($exceptions)) {
f6f319f8 1484 $exceptions = ' AND u.id NOT IN ('. $exceptions .') ';
1485 }
1486
1487 if (!empty($sort)) {
1488 $sort = ' ORDER by '.$sort;
900df8b6 1489 }
1490
8d241374 1491 return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat, u.maildigest,
8f0cd6ef 1492 u.email, u.city, u.country, u.lastlogin, u.picture, u.lang, u.timezone,
86fb5ed5 1493 u.emailstop, t.authority,t.role,t.editall,t.timeaccess as lastaccess
8f0cd6ef 1494 FROM {$CFG->prefix}user u,
688d06f4 1495 {$CFG->prefix}user_teachers t
8f0cd6ef 1496 WHERE t.course = '$courseid' AND t.userid = u.id
f6f319f8 1497 AND u.deleted = '0' AND u.confirmed = '1' $exceptions $sort");
df28d6c5 1498}
1499
18a97fd8 1500/**
fbc21ae8 1501 * Returns all the users of a course: students and teachers
1502 *
1503 * @param int $courseid The course in question.
1504 * @param string $sort ?
1505 * @param string $exceptions ?
1506 * @param string $fields A comma separated list of fields to be returned from the chosen table.
89dcb99d 1507 * @return array An array of {@link $USER} records.
fbc21ae8 1508 * @todo Finish documenting this function
1509 */
d4419d55 1510function get_course_users($courseid, $sort='timeaccess DESC', $exceptions='', $fields='') {
353d0338 1511
900df8b6 1512 /// Using this method because the single SQL is too inefficient
1513 // Note that this has the effect that teachers and students are
1514 // sorted individually. Returns first all teachers, then all students
0720313b 1515
900df8b6 1516 if (!$teachers = get_course_teachers($courseid, $sort, $exceptions)) {
1517 $teachers = array();
1518 }
65ee9c16 1519 if (!$students = get_course_students($courseid, $sort, '', 0, 99999, '', '', NULL, '', $fields, $exceptions)) {
900df8b6 1520 $students = array();
1521 }
df28d6c5 1522
900df8b6 1523 return $teachers + $students;
0720313b 1524
900df8b6 1525}
0720313b 1526
900df8b6 1527/**
fbc21ae8 1528 * Search through course users
1529 *
1530 * If $coursid specifies the site course then this function searches
1531 * through all undeleted and confirmed users
1532 *
1533 * @uses $CFG
1534 * @uses SITEID
1535 * @param int $courseid The course in question.
1536 * @param int $groupid The group in question.
1537 * @param string $searchtext ?
1538 * @param string $sort ?
1539 * @param string $exceptions ?
89dcb99d 1540 * @return array An array of {@link $USER} records.
fbc21ae8 1541 * @todo Finish documenting this function
1542 */
900df8b6 1543function search_users($courseid, $groupid, $searchtext, $sort='', $exceptions='') {
1544 global $CFG;
0720313b 1545
900df8b6 1546 switch ($CFG->dbtype) {
d4419d55 1547 case 'mysql':
1548 $fullname = ' CONCAT(u.firstname," ",u.lastname) ';
1549 $LIKE = 'LIKE';
900df8b6 1550 break;
d4419d55 1551 case 'postgres7':
900df8b6 1552 $fullname = " u.firstname||' '||u.lastname ";
d4419d55 1553 $LIKE = 'ILIKE';
900df8b6 1554 break;
8f0cd6ef 1555 default:
d4419d55 1556 $fullname = ' u.firstname||" "||u.lastname ';
1557 $LIKE = 'ILIKE';
900df8b6 1558 }
8f0cd6ef 1559
900df8b6 1560 if (!empty($exceptions)) {
d4419d55 1561 $except = ' AND u.id NOT IN ('. $exceptions .') ';
900df8b6 1562 } else {
1563 $except = '';
1564 }
2700d113 1565
900df8b6 1566 if (!empty($sort)) {
d4419d55 1567 $order = ' ORDER BY '. $sort;
900df8b6 1568 } else {
1569 $order = '';
1570 }
8f0cd6ef 1571
d4419d55 1572 $select = 'u.deleted = \'0\' AND u.confirmed = \'1\'';
2700d113 1573
222ac91b 1574 if (!$courseid or $courseid == SITEID) {
2700d113 1575 return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
1576 FROM {$CFG->prefix}user u
1577 WHERE $select
900df8b6 1578 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
2700d113 1579 $except $order");
8f0cd6ef 1580 } else {
2700d113 1581
900df8b6 1582 if ($groupid) {
1583 return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
8f0cd6ef 1584 FROM {$CFG->prefix}user u,
900df8b6 1585 {$CFG->prefix}groups_members g
2700d113 1586 WHERE $select AND g.groupid = '$groupid' AND g.userid = u.id
900df8b6 1587 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
1588 $except $order");
1589 } else {
1590 if (!$teachers = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
8f0cd6ef 1591 FROM {$CFG->prefix}user u,
900df8b6 1592 {$CFG->prefix}user_teachers s
2700d113 1593 WHERE $select AND s.course = '$courseid' AND s.userid = u.id
900df8b6 1594 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
1595 $except $order")) {
1596 $teachers = array();
1597 }
1598 if (!$students = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
8f0cd6ef 1599 FROM {$CFG->prefix}user u,
900df8b6 1600 {$CFG->prefix}user_students s
2700d113 1601 WHERE $select AND s.course = '$courseid' AND s.userid = u.id
900df8b6 1602 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
1603 $except $order")) {
1604 $students = array();
1605 }
1606 return $teachers + $students;
1607 }
1608 }
df28d6c5 1609}
1610
2700d113 1611
18a97fd8 1612/**
fbc21ae8 1613 * Returns a list of all site users
1614 * Obsolete, just calls get_course_users(SITEID)
1615 *
1616 * @uses SITEID
c6d15803 1617 * @deprecated Use {@link get_course_users()} instead.
fbc21ae8 1618 * @param string $fields A comma separated list of fields to be returned from the chosen table.
89dcb99d 1619 * @return array|false An array of {@link $USER} records or false if error.
fbc21ae8 1620 * @todo Finish documenting this function. The return type need to be better defined.
1621 */
d4419d55 1622function get_site_users($sort='u.lastaccess DESC', $fields='*', $exceptions='') {
2d0b30a0 1623
65ee9c16 1624 return get_course_users(SITEID, $sort, $exceptions, $fields);
2d0b30a0 1625}
1626
9fa49e22 1627
18a97fd8 1628/**
fbc21ae8 1629 * Returns a subset of users
1630 *
1631 * @uses $CFG
1632 * @param boolean $get If false then only a count of the records is returned
1633 * @param string $search A simple string to search for
1634 * @param boolean $confirmed A switch to allow/disallow unconfirmed users
1635 * @param array(int) $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
1636 * @param string $sort A SQL snippet for the sorting criteria to use
1637 * @param string $firstinitial ?
1638 * @param string $lastinitial ?
1639 * @param string $page ?
1640 * @param string $recordsperpage ?
1641 * @param string $fields A comma separated list of fields to be returned from the chosen table.
89dcb99d 1642 * @return array|false|int An array of {@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 1643 * @todo Finish documenting this function. The return type needs to be better defined.
1644 */
d4419d55 1645function get_users($get=true, $search='', $confirmed=false, $exceptions='', $sort='firstname ASC',
1646 $firstinitial='', $lastinitial='', $page=0, $recordsperpage=99999, $fields='*') {
18a97fd8 1647
1648 global $CFG;
1649
1650 switch ($CFG->dbtype) {
d4419d55 1651 case 'mysql':
1652 $limit = 'LIMIT '. $page .','. $recordsperpage;
1653 $fullname = ' CONCAT(firstname," ",lastname) ';
1654 $LIKE = 'LIKE';
489b2919 1655 break;
d4419d55 1656 case 'postgres7':
1657 $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page);
3a954ad4 1658 $fullname = " firstname||' '||lastname ";
d4419d55 1659 $LIKE = 'ILIKE';
18a97fd8 1660 break;
8f0cd6ef 1661 default:
d4419d55 1662 $limit = 'LIMIT '. $recordsperpage .','. $page;
1663 $fullname = ' firstname||" "||lastname ';
1664 $LIKE = 'ILIKE';
18a97fd8 1665 }
e384fb7b 1666
d4419d55 1667 $select = 'username <> \'guest\' AND deleted = 0';
488acd1b 1668
e384fb7b 1669 if ($search) {
488acd1b 1670 $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
e384fb7b 1671 }
1672
5a741655 1673 if ($confirmed) {
d4419d55 1674 $select .= ' AND confirmed = \'1\' ';
5a741655 1675 }
1676
1677 if ($exceptions) {
d4419d55 1678 $select .= ' AND id NOT IN ('. $exceptions .') ';
5a741655 1679 }
1680
488acd1b 1681 if ($firstinitial) {
d4419d55 1682 $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\'';
8f0cd6ef 1683 }
488acd1b 1684 if ($lastinitial) {
d4419d55 1685 $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\'';
8f0cd6ef 1686 }
488acd1b 1687
5a741655 1688 if ($sort and $get) {
d4419d55 1689 $sort = ' ORDER BY '. $sort .' ';
5a741655 1690 } else {
d4419d55 1691 $sort = '';
5a741655 1692 }
1693
1694 if ($get) {
d4419d55 1695 return get_records_select('user', $select .' '. $sort .' '. $limit, '', $fields);
5a741655 1696 } else {
d4419d55 1697 return count_records_select('user', $select .' '. $sort .' '. $limit);
5a741655 1698 }
9fa49e22 1699}
1700
5a741655 1701
18a97fd8 1702/**
fbc21ae8 1703 * shortdesc (optional)
1704 *
1705 * longdesc
1706 *
1707 * @uses $CFG
1708 * @param string $sort ?
1709 * @param string $dir ?
1710 * @param int $categoryid ?
1711 * @param int $categoryid ?
1712 * @param string $search ?
1713 * @param string $firstinitial ?
1714 * @param string $lastinitial ?
89dcb99d 1715 * @return array An array of {@link $USER} records
fbc21ae8 1716 * @todo Finish documenting this function
1717 */
1718
d4419d55 1719function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=99999,
7cf1c7bd 1720 $search='', $firstinitial='', $lastinitial='') {
488acd1b 1721
9fa49e22 1722 global $CFG;
31fefa63 1723
c2a96d6b 1724 switch ($CFG->dbtype) {
d4419d55 1725 case 'mysql':
1726 $limit = 'LIMIT '. $page .','. $recordsperpage;
1727 $fullname = ' CONCAT(firstname," ",lastname) ';
1728 $LIKE = 'LIKE';
c2a96d6b 1729 break;
d4419d55 1730 case 'postgres7':
1731 $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page);
1732 $fullname = ' firstname||\' \'||lastname ';
1733 $LIKE = 'ILIKE';
c2a96d6b 1734 break;
8f0cd6ef 1735 default:
d4419d55 1736 $limit = 'LIMIT '. $recordsperpage .','. $page;
1737 $fullname = ' firstname||\' \'||lastname ';
1738 $LIKE = 'LIKE';
31fefa63 1739 }
c2a96d6b 1740
488acd1b 1741 $select = 'deleted <> 1';
1742
c750592a 1743 if ($search) {
488acd1b 1744 $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
1745 }
1746
1747 if ($firstinitial) {
d4419d55 1748 $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\' ';
488acd1b 1749 }
1750
1751 if ($lastinitial) {
d4419d55 1752 $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\' ';
c750592a 1753 }
1754
488acd1b 1755 if ($sort) {
d4419d55 1756 $sort = ' ORDER BY '. $sort .' '. $dir;
488acd1b 1757 }
1758
1759/// warning: will return UNCONFIRMED USERS
ea5d48ee 1760 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed
8f0cd6ef 1761 FROM {$CFG->prefix}user
488acd1b 1762 WHERE $select $sort $limit ");
9fa49e22 1763
1764}
1765
488acd1b 1766
18a97fd8 1767/**
fbc21ae8 1768 * shortdesc (optional)
1769 *
1770 * longdesc
1771 *
1772 * @uses $CFG
89dcb99d 1773 * @return array An array of {@link $USER} records
fbc21ae8 1774 * @todo Finish documenting this function
1775 */
9fa49e22 1776function get_users_confirmed() {
1777 global $CFG;
8f0cd6ef 1778 return get_records_sql("SELECT *
1779 FROM {$CFG->prefix}user
1780 WHERE confirmed = 1
9fa49e22 1781 AND deleted = 0
8f0cd6ef 1782 AND username <> 'guest'
9fa49e22 1783 AND username <> 'changeme'");
1784}
1785
1786
18a97fd8 1787/**
fbc21ae8 1788 * shortdesc (optional)
1789 *
1790 * longdesc
1791 *
1792 * @uses $CFG
1793 * @param string $cutofftime ?
89dcb99d 1794 * @return array An array of {@link $USER} records
fbc21ae8 1795 * @todo Finish documenting this function
1796 */
99988d1a 1797function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 1798 global $CFG;
8f0cd6ef 1799 return get_records_sql("SELECT *
1800 FROM {$CFG->prefix}user
9fa49e22 1801 WHERE confirmed = 0
8f0cd6ef 1802 AND firstaccess > 0
9fa49e22 1803 AND firstaccess < '$cutofftime'");
1804}
1805
1806
18a97fd8 1807/**
fbc21ae8 1808 * shortdesc (optional)
1809 *
1810 * longdesc
1811 *
1812 * @uses $CFG
1813 * @param string $cutofftime ?
89dcb99d 1814 * @return array An array of {@link $USER} records
fbc21ae8 1815 * @todo Finish documenting this function
1816 */
9fa49e22 1817function get_users_longtimenosee($cutofftime) {
1818 global $CFG;
937ae59c 1819 return get_records_sql("SELECT DISTINCT *
1820 FROM {$CFG->prefix}user_students
8f0cd6ef 1821 WHERE timeaccess > '0'
937ae59c 1822 AND timeaccess < '$cutofftime' ");
9fa49e22 1823}
1824
f374fb10 1825/**
fbc21ae8 1826 * Returns an array of group objects that the user is a member of
1827 * in the given course. If userid isn't specified, then return a
1828 * list of all groups in the course.
1829 *
1830 * @uses $CFG
89dcb99d 1831 * @param int $courseid The id of the course in question.
fbc21ae8 1832 * @param int $userid The id of the user in question as found in the 'user' table 'id' field.
1833 * @return array
1834 * @todo Finish documenting this function
1835 */
f374fb10 1836function get_groups($courseid, $userid=0) {
1837 global $CFG;
1838
1839 if ($userid) {
d4419d55 1840 $dbselect = ', '. $CFG->prefix .'groups_members m';
1841 $userselect = 'AND m.groupid = g.id AND m.userid = \''. $userid .'\'';
2d439c9d 1842 } else {
1843 $dbselect = '';
1844 $userselect = '';
f374fb10 1845 }
1846
1847 return get_records_sql("SELECT DISTINCT g.*
2d439c9d 1848 FROM {$CFG->prefix}groups g $dbselect
f374fb10 1849 WHERE g.courseid = '$courseid' $userselect ");
1850}
1851
1852
1853/**
fbc21ae8 1854 * Returns an array of user objects
1855 *
1856 * @uses $CFG
1857 * @param int $groupid The group in question.
1858 * @param string $sort ?
1859 * @param string $exceptions ?
1860 * @return array
1861 * @todo Finish documenting this function
1862 */
d4419d55 1863function get_group_users($groupid, $sort='u.lastaccess DESC', $exceptions='') {
f374fb10 1864 global $CFG;
900df8b6 1865 if (!empty($exceptions)) {
d4419d55 1866 $except = ' AND u.id NOT IN ('. $exceptions .') ';
900df8b6 1867 } else {
1868 $except = '';
1869 }
f374fb10 1870 return get_records_sql("SELECT DISTINCT u.*
1871 FROM {$CFG->prefix}user u,
8f0cd6ef 1872 {$CFG->prefix}groups_members m
f374fb10 1873 WHERE m.groupid = '$groupid'
900df8b6 1874 AND m.userid = u.id $except
2c4263c4 1875 ORDER BY $sort");
f374fb10 1876}
1877
1878/**
fbc21ae8 1879 * An efficient way of finding all the users who aren't in groups yet
1880 *
1881 * Currently unimplemented.
1882 * @uses $CFG
1883 * @param int $courseid The course in question.
1884 * @return array
1885 */
f374fb10 1886function get_users_not_in_group($courseid) {
1887 global $CFG;
1888
1889 return array(); /// XXX TO BE DONE
1890}
1891
60b025d1 1892/**
fbc21ae8 1893 * Returns an array of user objects
1894 *
1895 * @uses $CFG
1896 * @param int $groupid The group in question.
1897 * @param string $sort ?
1898 * @return array
1899 * @todo Finish documenting this function
1900 */
d4419d55 1901function get_group_students($groupid, $sort='u.lastaccess DESC') {
60b025d1 1902 global $CFG;
1903 return get_records_sql("SELECT DISTINCT u.*
1904 FROM {$CFG->prefix}user u,
1905 {$CFG->prefix}groups_members m,
1906 {$CFG->prefix}groups g,
1907 {$CFG->prefix}user_students s
1908 WHERE m.groupid = '$groupid'
8f0cd6ef 1909 AND m.userid = u.id
1910 AND m.groupid = g.id
60b025d1 1911 AND g.courseid = s.course
1912 AND s.userid = u.id
1913 ORDER BY $sort");
1914}
1915
f374fb10 1916/**
fbc21ae8 1917 * Returns the user's group in a particular course
1918 *
1919 * @uses $CFG
1920 * @param int $courseid The course in question.
1921 * @param int $userid The id of the user as found in the 'user' table.
1922 * @return array
1923 * @todo Finish documenting this function
1924 */
f374fb10 1925function user_group($courseid, $userid) {
1926 global $CFG;
1927
1928 return get_record_sql("SELECT g.*
0da33e07 1929 FROM {$CFG->prefix}groups g,
1930 {$CFG->prefix}groups_members m
f374fb10 1931 WHERE g.courseid = '$courseid'
1932 AND g.id = m.groupid
1933 AND m.userid = '$userid'");
1934}
1935
1936
9fa49e22 1937
02ebf404 1938
1939/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
1940
1941
18a97fd8 1942/**
fbc21ae8 1943 * Returns $course object of the top-level site.
1944 *
89dcb99d 1945 * @return course A {@link $COURSE} object for the site
1946 * @todo Finish documenting this function.
fbc21ae8 1947 */
02ebf404 1948function get_site () {
02ebf404 1949
d4419d55 1950 if ( $course = get_record('course', 'category', 0)) {
02ebf404 1951 return $course;
1952 } else {
1953 return false;
1954 }
1955}
1956
18a97fd8 1957/**
fbc21ae8 1958 * Returns list of courses, for whole site, or category
1959 *
1960 * @uses $CFG
1961 * @uses $USER
1962 * @param int $categoryid ?
1963 * @param string $sort ?
1964 * @param string $fields A comma separated list of fields to be returned from the chosen table.
89dcb99d 1965 * @return array An array of {@link $COURSE} records
fbc21ae8 1966 * @todo Finish documenting this function
1967 */
d4419d55 1968function get_courses($categoryid='all', $sort='c.sortorder ASC', $fields='c.*') {
c7fe5c6f 1969/// This function deliberately uses PHP to do the checking at the end,
1970/// because MySQL has been known to really bog down when trying to do a JOIN
1971/// on the 'course' and 'user_teachers' table at the same time.
02ebf404 1972
8ef9cb56 1973 global $USER, $CFG;
1974
c7fe5c6f 1975 $select = array();
1976
d4419d55 1977 if ($categoryid != 'all') {
c7fe5c6f 1978 $select[] = "c.category = '$categoryid'";
02ebf404 1979 }
1980
c7fe5c6f 1981 /// Admins can always see invisible courses
1982 /// Creators can always see invisible courses
1983 /// Teachers can only see their own invisible courses <- needs detailed checking
1984 /// Students can't see invisible courses at all
1985 /// Guests can't see invisible courses at all
1986
d4419d55 1987 $visiblecourses = '';
c7fe5c6f 1988 $showallinvisible = iscreator(); // includes admins
1989 $hideallinvisible = empty($USER->id) || (!isteacher());
1990
1991 if ($hideallinvisible) {
1992 $select[] = 'c.visible > 0';
8ef9cb56 1993 }
1994
c7fe5c6f 1995 if ($select) {
fbc21ae8 1996 $selectsql = $CFG->prefix .'course c WHERE '. implode(' AND ', $select);
14f32609 1997 } else {
fbc21ae8 1998 $selectsql = $CFG->prefix .'course c ';
14f32609 1999 }
2000
169fc66a 2001 $courses = get_records_sql('SELECT '. $fields .' FROM '. $selectsql .' ORDER BY '. $sort);
c7fe5c6f 2002
2003 if ($courses and (!$hideallinvisible) and (!$showallinvisible)) { // For ordinary users we need to check visibility
2004 foreach ($courses as $key => $course) {
2005 if ($course->visible == 0) { // Invisible course, let's check if we are a teacher
2006 if (!isteacher($course->id)) { // We shouldn't see this
2007 unset($courses[$key]);
2008 }
2009 }
2010 }
2011 }
8130b77b 2012
c7fe5c6f 2013 return $courses;
8130b77b 2014}
2015
18a97fd8 2016/**
fbc21ae8 2017 * Returns list of courses, for whole site, or category
2018 *
2019 * Similar to get_courses, but allows paging
2020 *
2021 * @uses $CFG
2022 * @uses $USER
2023 * @param int $categoryid ?
2024 * @param string $sort ?
2025 * @param string $fields A comma separated list of fields to be returned from the chosen table.
2026 * @param int $totalcount Passed by reference. ?
2027 * @param int $limitfrom ?
2028 * @param int $limitnum ?
89dcb99d 2029 * @return array An array of {@link $COURSE} records
fbc21ae8 2030 * @todo Finish documenting this function
2031 */
d4419d55 2032function get_courses_page($categoryid='all', $sort='c.sortorder ASC', $fields='c.*',
2033 &$totalcount, $limitfrom='', $limitnum='') {
8130b77b 2034
c7fe5c6f 2035/// This function deliberately uses PHP to do the checking at the end,
2036/// because MySQL has been known to really bog down when trying to do a JOIN
2037/// on the 'course' and 'user_teachers' table at the same time.
2038
8130b77b 2039 global $USER, $CFG;
2040
c7fe5c6f 2041 $select = array();
2042
d4419d55 2043 if ($categoryid != 'all') {
fbc21ae8 2044 $select[] = 'c.category = \''. $categoryid .'\'';
8130b77b 2045 }
2046
c7fe5c6f 2047 /// Admins can always see invisible courses
2048 /// Creators can always see invisible courses
2049 /// Teachers can only see their own invisible courses <- needs detailed checking
2050 /// Students can't see invisible courses at all
2051 /// Guests can't see invisible courses at all
2052
d4419d55 2053 $visiblecourses = '';
c7fe5c6f 2054 $showallinvisible = iscreator(); // includes admins
2055 $hideallinvisible = empty($USER->id) || (!isteacher());
2056
2057 if ($hideallinvisible) {
2058 $select[] = 'c.visible > 0';
2059 }
2060
2061 if ($select) {
fbc21ae8 2062 $selectsql = $CFG->prefix .'course c WHERE '.implode(' AND ', $select);
8130b77b 2063 } else {
fbc21ae8 2064 $selectsql = $CFG->prefix .'course c ';
8130b77b 2065 }
2066
169fc66a 2067 $courses = get_records_sql('SELECT '. $fields .' FROM '. $selectsql .' ORDER BY '. $sort);
c7fe5c6f 2068
2069 if ($courses and (!$hideallinvisible) and (!$showallinvisible)) { // For ordinary users we need to check visibility
2070 foreach ($courses as $key => $course) {
2071 if ($course->visible == 0) { // Invisible course, let's check if we are a teacher
2072 if (!isteacher($course->id)) { // We shouldn't see this
2073 unset($courses[$key]);
2074 }
2075 }
02ebf404 2076 }
2077 }
8ef9cb56 2078
c7fe5c6f 2079 $totalcount = count($courses);
8ef9cb56 2080
c7fe5c6f 2081 if ($courses and ($limitfrom or $limitnum)) {
2082 $courses = array_slice($courses, (int)$limitfrom, (int)$limitnum);
2083 }
8ef9cb56 2084
c7fe5c6f 2085 return $courses;
02ebf404 2086}
2087
2088
18a97fd8 2089/**
fbc21ae8 2090 * shortdesc (optional)
2091 *
2092 * longdesc
2093 *
2094 * @uses $CFG
2095 * @param int $userid ?
2096 * @param string $sort ?
89dcb99d 2097 * @return array An array of {@link $COURSE} records
fbc21ae8 2098 * @todo Finish documenting this function
2099 */
d4419d55 2100function get_my_courses($userid, $sort='visible DESC,sortorder ASC') {
2f3499b7 2101
02ebf404 2102 global $CFG;
2103
2f3499b7 2104 $course = array();
2105
d4419d55 2106 if ($students = get_records('user_students', 'userid', $userid, '', 'id, course')) {
2f3499b7 2107 foreach ($students as $student) {
2108 $course[$student->course] = $student->course;
2109 }
2110 }
d4419d55 2111 if ($teachers = get_records('user_teachers', 'userid', $userid, '', 'id, course')) {
2f3499b7 2112 foreach ($teachers as $teacher) {
2113 $course[$teacher->course] = $teacher->course;
2114 }
2115 }
2116 if (empty($course)) {
2117 return $course;
2118 }
2119
2120 $courseids = implode(',', $course);
2121
d4419d55 2122 return get_records_list('course', 'id', $courseids, $sort);
2f3499b7 2123
2124// The following is correct but VERY slow with large datasets
2125//
8f0cd6ef 2126// return get_records_sql("SELECT c.*
2127// FROM {$CFG->prefix}course c,
2128// {$CFG->prefix}user_students s,
2129// {$CFG->prefix}user_teachers t
2f3499b7 2130// WHERE (s.userid = '$userid' AND s.course = c.id)
2131// OR (t.userid = '$userid' AND t.course = c.id)
8f0cd6ef 2132// GROUP BY c.id
2f3499b7 2133// ORDER BY $sort");
02ebf404 2134}
2135
2136
18a97fd8 2137/**
fbc21ae8 2138 * Returns a list of courses that match a search
2139 *
2140 * @uses $CFG
2141 * @param array $searchterms ?
2142 * @param string $sort ?
2143 * @param int $page ?
2144 * @param int $recordsperpage ?
2145 * @param int $totalcount Passed in by reference. ?
89dcb99d 2146 * @return array An array of {@link $COURSE} records
fbc21ae8 2147 * @todo Finish documenting this function
2148 */
d4419d55 2149function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
02ebf404 2150
2151 global $CFG;
2152
2153 switch ($CFG->dbtype) {
d4419d55 2154 case 'mysql':
2155 $limit = 'LIMIT '. $page .','. $recordsperpage;
02ebf404 2156 break;
d4419d55 2157 case 'postgres7':
ea09b04b 2158 $limit = 'LIMIT '. $recordsperpage .' OFFSET '.$page;
02ebf404 2159 break;
8f0cd6ef 2160 default:
d4419d55 2161 $limit = 'LIMIT '. $recordsperpage .','. $page;
02ebf404 2162 }
2163
18a97fd8 2164 //to allow case-insensitive search for postgesql
d4419d55 2165 if ($CFG->dbtype == 'postgres7') {
2166 $LIKE = 'ILIKE';
2167 $NOTLIKE = 'NOT ILIKE'; // case-insensitive
2168 $REGEXP = '~*';
2169 $NOTREGEXP = '!~*';
02ebf404 2170 } else {
d4419d55 2171 $LIKE = 'LIKE';
2172 $NOTLIKE = 'NOT LIKE';
2173 $REGEXP = 'REGEXP';
2174 $NOTREGEXP = 'NOT REGEXP';
02ebf404 2175 }
2176
d4419d55 2177 $fullnamesearch = '';
2178 $summarysearch = '';
02ebf404 2179
02ebf404 2180 foreach ($searchterms as $searchterm) {
2181 if ($fullnamesearch) {
d4419d55 2182 $fullnamesearch .= ' AND ';
02ebf404 2183 }
02ebf404 2184 if ($summarysearch) {
d4419d55 2185 $summarysearch .= ' AND ';
02ebf404 2186 }
a8b56716 2187
d4419d55 2188 if (substr($searchterm,0,1) == '+') {
a8b56716 2189 $searchterm = substr($searchterm,1);
2190 $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
2191 $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
2192 } else if (substr($searchterm,0,1) == "-") {
2193 $searchterm = substr($searchterm,1);
2194 $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
2195 $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
2196 } else {
d4419d55 2197 $summarysearch .= ' summary '. $LIKE .'\'%'. $searchterm .'%\' ';
2198 $fullnamesearch .= ' fullname '. $LIKE .'\'%'. $searchterm .'%\' ';
a8b56716 2199 }
2200
02ebf404 2201 }
2202
d4419d55 2203 $selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\'';
a8b56716 2204
d4419d55 2205 $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
02ebf404 2206
d4419d55 2207 $courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort .' '. $limit);
02ebf404 2208
2209 if ($courses) { /// Remove unavailable courses from the list
2210 foreach ($courses as $key => $course) {
2211 if (!$course->visible) {
2212 if (!isteacher($course->id)) {
2213 unset($courses[$key]);
a8b56716 2214 $totalcount--;
02ebf404 2215 }
2216 }
2217 }
2218 }
2219
2220 return $courses;
2221}
2222
2223
18a97fd8 2224/**
fbc21ae8 2225 * Returns a sorted list of categories
2226 *
2227 * @param string $parent ?
2228 * @param string $sort ?
2229 * @return ?
2230 * @todo Finish documenting this function
2231 */
d4419d55 2232function get_categories($parent='none', $sort='sortorder ASC') {
02ebf404 2233
d4419d55 2234 if ($parent == 'none') {
2235 $categories = get_records('course_categories', '', '', $sort);
02ebf404 2236 } else {
d4419d55 2237 $categories = get_records('course_categories', 'parent', $parent, $sort);
02ebf404 2238 }
2239 if ($categories) { /// Remove unavailable categories from the list
3af6e1db 2240 $creator = iscreator();
02ebf404 2241 foreach ($categories as $key => $category) {
2242 if (!$category->visible) {
3af6e1db 2243 if (!$creator) {
02ebf404 2244 unset($categories[$key]);
2245 }
2246 }
2247 }
2248 }
2249 return $categories;
2250}
2251
2252
18a97fd8 2253/**
fbc21ae8 2254 * This recursive function makes sure that the courseorder is consecutive
2255 *
2256 * @param int $categoryid ?
2257 * @param int $n ?
2258 * @return ?
2259 * @todo Finish documenting this function
2260 */
6bc502cc 2261function fix_course_sortorder($categoryid=0, $n=0) {
8f0cd6ef 2262
02ebf404 2263 $count = 0;
6bc502cc 2264 if ($courses = get_courses($categoryid)) {
2265 foreach ($courses as $course) {
2266 set_field('course', 'sortorder', $n, 'id', $course->id);
2267 $n++;
2268 $count++;
02ebf404 2269 }
02ebf404 2270 }
d4419d55 2271 set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
8f0cd6ef 2272
6bc502cc 2273 if ($categories = get_categories($categoryid)) {
2274 foreach ($categories as $category) {
2275 $n = fix_course_sortorder($category->id, $n);
2276 }
2277 }
8f0cd6ef 2278
6bc502cc 2279 return $n;
02ebf404 2280}
2281
fbc21ae8 2282
18a97fd8 2283/**
fbc21ae8 2284 * This function creates a default separated/connected scale
2285 *
2286 * This function creates a default separated/connected scale
2287 * so there's something in the database. The locations of
2288 * strings and files is a bit odd, but this is because we
2289 * need to maintain backward compatibility with many different
2290 * existing language translations and older sites.
2291 *
2292 * @uses $CFG
2293 */
02ebf404 2294function make_default_scale() {
02ebf404 2295
2296 global $CFG;
2297
2298 $defaultscale = NULL;
2299 $defaultscale->courseid = 0;
2300 $defaultscale->userid = 0;
d4419d55 2301 $defaultscale->name = get_string('separateandconnected');
2302 $defaultscale->scale = get_string('postrating1', 'forum').','.
2303 get_string('postrating2', 'forum').','.
2304 get_string('postrating3', 'forum');
02ebf404 2305 $defaultscale->timemodified = time();
2306
8f0cd6ef 2307 /// Read in the big description from the file. Note this is not
02ebf404 2308 /// HTML (despite the file extension) but Moodle format text.
d4419d55 2309 $parentlang = get_string('parentlang');
2310 if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
2311 $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
2312 } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
2313 $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
2314 } else if (is_readable($CFG->dirroot .'/lang/en/help/forum/ratings.html')) {
2315 $file = file($CFG->dirroot .'/lang/en/help/forum/ratings.html');
02ebf404 2316 } else {
d4419d55 2317 $file = '';
02ebf404 2318 }
2319
d4419d55 2320 $defaultscale->description = addslashes(implode('', $file));
02ebf404 2321
d4419d55 2322 if ($defaultscale->id = insert_record('scale', $defaultscale)) {
2323 execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false);
02ebf404 2324 }
2325}
2326
fbc21ae8 2327
18a97fd8 2328/**
fbc21ae8 2329 * Returns a menu of all available scales from the site as well as the given course
2330 *
2331 * Returns a menu of all available scales from the site as well as the given course
2332 *
2333 * @uses $CFG
2334 * @param int $courseid The id of the course as found in the 'course' table.
2335 * @return array
2336 * @todo Finish documenting this function
2337 */
02ebf404 2338function get_scales_menu($courseid=0) {
02ebf404 2339
2340 global $CFG;
8f0cd6ef 2341
2342 $sql = "SELECT id, name FROM {$CFG->prefix}scale
2343 WHERE courseid = '0' or courseid = '$courseid'
02ebf404 2344 ORDER BY courseid ASC, name ASC";
2345
d4419d55 2346 if ($scales = get_records_sql_menu($sql)) {
02ebf404 2347 return $scales;
2348 }
2349
2350 make_default_scale();
2351
d4419d55 2352 return get_records_sql_menu($sql);
02ebf404 2353}
2354
df28d6c5 2355/// MODULE FUNCTIONS /////////////////////////////////////////////////
2356
18a97fd8 2357/**
fbc21ae8 2358 * Just gets a raw list of all modules in a course
2359 *
2360 * @uses $CFG
2361 * @param int $courseid The id of the course as found in the 'course' table.
2362 * @return array
2363 * @todo Finish documenting this function
2364 */
9fa49e22 2365function get_course_mods($courseid) {
9fa49e22 2366 global $CFG;
2367
7acaa63d 2368 return get_records_sql("SELECT cm.*, m.name as modname
8f0cd6ef 2369 FROM {$CFG->prefix}modules m,
7acaa63d 2370 {$CFG->prefix}course_modules cm
8f0cd6ef 2371 WHERE cm.course = '$courseid'
9fa49e22 2372 AND cm.deleted = '0'
2373 AND cm.module = m.id ");
2374}
2375
fbc21ae8 2376
18a97fd8 2377/**
fbc21ae8 2378 * Given an instance of a module, finds the coursemodule description
2379 *
2380 * @uses $CFG
2381 * @param string $modulename ?
2382 * @param string $instance ?
2383 * @param int $courseid The id of the course as found in the 'course' table.
2384 * @return array
2385 * @todo Finish documenting this function
2386 */
df28d6c5 2387function get_coursemodule_from_instance($modulename, $instance, $courseid) {
df28d6c5 2388
2389 global $CFG;
2390
2391 return get_record_sql("SELECT cm.*, m.name
8f0cd6ef 2392 FROM {$CFG->prefix}course_modules cm,
2393 {$CFG->prefix}modules md,
2394 {$CFG->prefix}$modulename m
2395 WHERE cm.course = '$courseid' AND
df28d6c5 2396 cm.deleted = '0' AND
8f0cd6ef 2397 cm.instance = m.id AND
2398 md.name = '$modulename' AND
df28d6c5 2399 md.id = cm.module AND
2400 m.id = '$instance'");
2401
2402}
2403
fbc21ae8 2404
18a97fd8 2405/**
fbc21ae8 2406 * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
2407 *
2408 * Returns an array of all the active instances of a particular
2409 * module in a given course, sorted in the order they are defined
2410 * in the course. Returns false on any errors.
2411 *
2412 * @uses $CFG
2413 * @param string $modulename The name of the module to get instances for
2414 * @param object(course) $course This depends on an accurate $course->modinfo
2415 * @todo Finish documenting this function. Is a course object to be documented as object(course) or array(course) since a coures object is really just an associative array, not a php object?
2416 */
cccb016a 2417function get_all_instances_in_course($modulename, $course) {
df28d6c5 2418
2419 global $CFG;
2420
cccb016a 2421 if (!$modinfo = unserialize($course->modinfo)) {
2422 return array();
1acfbce5 2423 }
2424
404afe6b 2425 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode
8f0cd6ef 2426 FROM {$CFG->prefix}course_modules cm,
2427 {$CFG->prefix}course_sections cw,
2428 {$CFG->prefix}modules md,
2429 {$CFG->prefix}$modulename m
2430 WHERE cm.course = '$course->id' AND
2431 cm.instance = m.id AND
df28d6c5 2432 cm.deleted = '0' AND
8f0cd6ef 2433 cm.section = cw.id AND
2434 md.name = '$modulename' AND
cccb016a 2435 md.id = cm.module")) {
2436 return array();
2437 }
2438
2439 // Hide non-visible instances from students
2440 if (isteacher($course->id)) {
2441 $invisible = -1;
2442 } else {
2443 $invisible = 0;
2444 }
2445
2446 foreach ($modinfo as $mod) {
2447 if ($mod->mod == $modulename and $mod->visible > $invisible) {
7f12f9cd 2448 $instance = $rawmods[$mod->cm];
2449 if (!empty($mod->extra)) {
2450 $instance->extra = $mod->extra;
2451 }
2452 $outputarray[] = $instance;
cccb016a 2453 }
2454 }
2455
2456 return $outputarray;
df28d6c5 2457
2458}
2459
9fa49e22 2460
18a97fd8 2461/**
fbc21ae8 2462 * Determine whether a module instance is visible within a course
2463 *
2464 * Given a valid module object with info about the id and course,
2465 * and the module's type (eg "forum") returns whether the object
2466 * is visible or not
2467 *
2468 * @uses $CFG
2469 * @param $moduletype ?
2470 * @param $module ?
2471 * @return boolean
2472 * @todo Finish documenting this function
2473 */
580f2fbc 2474function instance_is_visible($moduletype, $module) {
580f2fbc 2475
2476 global $CFG;
2477
86e6076b 2478 if ($records = get_records_sql("SELECT cm.instance, cm.visible
580f2fbc 2479 FROM {$CFG->prefix}course_modules cm,
580f2fbc 2480 {$CFG->prefix}modules m
2481 WHERE cm.course = '$module->course' AND
8f0cd6ef 2482 cm.module = m.id AND
2483 m.name = '$moduletype' AND
2484 cm.instance = '$module->id'")) {
580f2fbc 2485
2486 foreach ($records as $record) { // there should only be one - use the first one
2487 return $record->visible;
2488 }
2489 }
2490
2491 return true; // visible by default!
2492}
2493
a3fb1c45 2494
2495
2496
9fa49e22 2497/// LOG FUNCTIONS /////////////////////////////////////////////////////
2498
2499
18a97fd8 2500/**
fbc21ae8 2501 * Add an entry to the log table.
2502 *
2503 * Add an entry to the log table. These are "action" focussed rather
2504 * than web server hits, and provide a way to easily reconstruct what
2505 * any particular student has been doing.
2506 *
2507 * @uses $CFG
2508 * @uses $USER
2509 * @uses $db
2510 * @uses $REMOTE_ADDR
2511 * @uses SITEID
89dcb99d 2512 * @param int $courseid The course id
fbc21ae8 2513 * @param string $module The module name - e.g. forum, journal, resource, course, user etc
2514 * @param string $action View, edit, post (often but not always the same as the file.php)
2515 * @param string $url The file and parameters used to see the results of the action
2516 * @param string $info Additional description information
2517 * @param string $cm The course_module->id if there is one
2518 * @param string $user If log regards $user other than $USER
2519 */
d4419d55 2520function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
9fa49e22 2521
31fefa63 2522 global $db, $CFG, $USER, $REMOTE_ADDR;
9fa49e22 2523
3d94772d 2524 if ($user) {
2525 $userid = $user;
2526 } else {
2527 if (isset($USER->realuser)) { // Don't log
2528 return;
2529 }
d4419d55 2530 $userid = empty($USER->id) ? '0' : $USER->id;
9fa49e22 2531 }
2532
2533 $timenow = time();
2534 $info = addslashes($info);
8f0cd6ef 2535 $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
9fa49e22 2536
d4419d55 2537 $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info)
2538 VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
ebc3bd2b 2539
ce78926d 2540 if (!$result and ($CFG->debug > 7)) {
d4419d55 2541 echo '<p>Error: Could not insert a new entry to the Moodle log</p>'; // Don't throw an error
8f0cd6ef 2542 }
540995b8 2543 if (!$user and isset($USER->id)) {
222ac91b 2544 if ($courseid == SITEID) {
d4419d55 2545 $db->Execute('UPDATE '. $CFG->prefix .'user SET lastIP=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\'
2546 WHERE id = \''. $USER->id .'\' ');
540995b8 2547 } else if (isstudent($courseid)) {
d4419d55 2548 $db->Execute('UPDATE '. $CFG->prefix .'user_students SET timeaccess = \''. $timenow .'\' '.
2549 'WHERE course = \''. $courseid .'\' AND userid = \''. $userid .'\'');
540995b8 2550 } else if (isteacher($courseid, false, false)) {
d4419d55 2551 $db->Execute('UPDATE '. $CFG->prefix .'user_teachers SET timeaccess = \''. $timenow .'\' '.
2552 'WHERE course = \''. $courseid .'\' AND userid = \''. $userid .'\'');
3d94772d 2553 }
8f0cd6ef 2554 }
9fa49e22 2555}
2556
2557
18a97fd8 2558/**
fbc21ae8 2559 * Select all log records based on SQL criteria
2560 *
2561 * @uses $CFG
2562 * @param string $select SQL select criteria
2563 * @param string $order SQL order by clause to sort the records returned
2564 * @param string $limitfrom ?
2565 * @param int $limitnum ?
2566 * @param int $totalcount Passed in by reference.
2567 * @return array
2568 * @todo Finish documenting this function
2569 */
d4419d55 2570function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
9fa49e22 2571 global $CFG;
2572
d4419d55 2573 if ($limitfrom !== '') {
519d369f 2574 switch ($CFG->dbtype) {
d4419d55 2575 case 'mysql':
2576 $limit = 'LIMIT '. $limitfrom .','. $limitnum;
519d369f 2577 break;
d4419d55 2578 case 'postgres7':
2579 $limit = 'LIMIT '. $limitnum .' OFFSET '. $limitfrom;
519d369f 2580 break;
8f0cd6ef 2581 default:
d4419d55 2582 $limit = 'LIMIT '. $limitnum .','. $limitfrom;
519d369f 2583 }
2584 } else {
d4419d55 2585 $limit = '';
519d369f 2586 }
2587
2588 if ($order) {
d4419d55 2589 $order = 'ORDER BY '. $order;
519d369f 2590 }
2591
fbc21ae8 2592 $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : '');
d4419d55 2593 $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
519d369f 2594
d4419d55 2595 return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture
2596 FROM '. $selectsql .' '. $order .' '. $limit);
9fa49e22 2597}
2598
519d369f 2599
18a97fd8 2600/**
fbc21ae8 2601 * Select all log records for a given course and user
2602 *
2603 * @uses $CFG
2f87145b 2604 * @uses DAYSECS
fbc21ae8 2605 * @param int $userid The id of the user as found in the 'user' table.
2606 * @param int $courseid The id of the course as found in the 'course' table.
2607 * @param string $coursestart ?
2608 * @todo Finish documenting this function
2609 */
9fa49e22 2610function get_logs_usercourse($userid, $courseid, $coursestart) {
2611 global $CFG;
2612
da0c90c3 2613 if ($courseid) {
d4419d55 2614 $courseselect = ' AND course = \''. $courseid .'\' ';
2700d113 2615 } else {
2616 $courseselect = '';
da0c90c3 2617 }
2618
2f87145b 2619 return get_records_sql("SELECT floor((`time` - $coursestart)/". DAYSECS .") as day, count(*) as num
8f0cd6ef 2620 FROM {$CFG->prefix}log
2621 WHERE userid = '$userid'
da0c90c3 2622 AND `time` > '$coursestart' $courseselect
9fa49e22 2623 GROUP BY day ");
2624}
2625
18a97fd8 2626/**
fbc21ae8 2627 * Select all log records for a given course, user, and day
2628 *
2629 * @uses $CFG
2f87145b 2630 * @uses HOURSECS
fbc21ae8 2631 * @param int $userid The id of the user as found in the 'user' table.
2632 * @param int $courseid The id of the course as found in the 'course' table.
2633 * @param string $daystart ?
2634 * @return array
2635 * @todo Finish documenting this function
2636 */
9fa49e22 2637function get_logs_userday($userid, $courseid, $daystart) {
2638 global $CFG;
2639
7e4a6488 2640 if ($courseid) {
d4419d55 2641 $courseselect = ' AND course = \''. $courseid .'\' ';
2700d113 2642 } else {
2643 $courseselect = '';
7e4a6488 2644 }
2645
2f87145b 2646 return get_records_sql("SELECT floor((`time` - $daystart)/". HOURSECS .") as hour, count(*) as num
9fa49e22 2647 FROM {$CFG->prefix}log
8f0cd6ef 2648 WHERE userid = '$userid'
7e4a6488 2649 AND `time` > '$daystart' $courseselect
9fa49e22 2650 GROUP BY hour ");
2651}
2652
b4bac9b6 2653/**
2654 * Returns an object with counts of failed login attempts
2655 *
8f0cd6ef 2656 * Returns information about failed login attempts. If the current user is
2657 * an admin, then two numbers are returned: the number of attempts and the
b4bac9b6 2658 * number of accounts. For non-admins, only the attempts on the given user
2659 * are shown.
2660 *
fbc21ae8 2661 * @param string $mode Either 'admin', 'teacher' or 'everybody'
2662 * @param string $username The username we are searching for
2663 * @param string $lastlogin The date from which we are searching
2664 * @return int
b4bac9b6 2665 */
2666
2667function count_login_failures($mode, $username, $lastlogin) {
2668
d4419d55 2669 $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin;
b4bac9b6 2670
2671 if (isadmin()) { // Return information about all accounts
2672 if ($count->attempts = count_records_select('log', $select)) {
2673 $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)');
2674 return $count;
2675 }
2676 } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacher())) {
d4419d55 2677 if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) {
b4bac9b6 2678 return $count;
2679 }
2680 }
2681 return NULL;
2682}
2683
2684
a3fb1c45 2685/// GENERAL HELPFUL THINGS ///////////////////////////////////
2686
18a97fd8 2687/**
fbc21ae8 2688 * Dump a given object's information in a PRE block.
2689 *
2690 * Mostly just used for debugging.
2691 *
2692 * @param mixed $object The data to be printed
2693 * @todo add example usage and example output
2694 */
a3fb1c45 2695function print_object($object) {
a3fb1c45 2696
d4419d55 2697 echo '<pre>';
2b051f1c 2698 print_r($object);
d4419d55 2699 echo '</pre>';
a3fb1c45 2700}
2701
2702
9fa49e22 2703
9d5b689c 2704// vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
f6f319f8 2705?>