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