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