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