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