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