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