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