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