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