forked from moodle/moodle
-
Notifications
You must be signed in to change notification settings - Fork 0
/
datalib.php
1717 lines (1441 loc) · 56.8 KB
/
datalib.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<?php // $Id$
/**
* Library of functions for database manipulation.
*
* Other main libraries:
* - weblib.php - functions that produce web output
* - moodlelib.php - general-purpose Moodle functions
* @author Martin Dougiamas and many others
* @version $Id$
* @license http://www.gnu.org/copyleft/gpl.html GNU Public License
* @package moodlecore
*/
/**
* Escape all dangerous characters in a data record
*
* $dataobject is an object containing needed data
* Run over each field exectuting addslashes() function
* to escape SQL unfriendly characters (e.g. quotes)
* Handy when writing back data read from the database
*
* @param $dataobject Object containing the database record
* @return object Same object with neccessary characters escaped
*/
function addslashes_object( $dataobject ) {
$a = get_object_vars( $dataobject);
foreach ($a as $key=>$value) {
$a[$key] = addslashes( $value );
}
return (object)$a;
}
/// USER DATABASE ////////////////////////////////////////////////
/**
* Returns $user object of the main admin user
* primary admin = admin with lowest role_assignment id among admins
* @uses $CFG
* @return object(admin) An associative array representing the admin user.
*/
function get_admin () {
global $CFG;
if ( $admins = get_admins() ) {
foreach ($admins as $admin) {
return $admin; // ie the first one
}
} else {
return false;
}
}
/**
* Returns list of all admins
*
* @uses $CFG
* @return object
*/
function get_admins() {
global $CFG;
$context = get_context_instance(CONTEXT_SYSTEM, SITEID);
return get_users_by_capability($context, 'moodle/site:doanything', 'u.*, ra.id as adminid', 'ra.id ASC'); // only need first one
}
function get_courses_in_metacourse($metacourseid) {
global $CFG;
$sql = "SELECT c.id,c.shortname,c.fullname FROM {$CFG->prefix}course c, {$CFG->prefix}course_meta mc WHERE mc.parent_course = $metacourseid
AND mc.child_course = c.id ORDER BY c.shortname";
return get_records_sql($sql);
}
function get_courses_notin_metacourse($metacourseid,$count=false) {
global $CFG;
if ($count) {
$sql = "SELECT COUNT(c.id)";
} else {
$sql = "SELECT c.id,c.shortname,c.fullname";
}
$alreadycourses = get_courses_in_metacourse($metacourseid);
$sql .= " FROM {$CFG->prefix}course c WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).")
AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1 ".((empty($count)) ? " ORDER BY c.shortname" : "");
return get_records_sql($sql);
}
function count_courses_notin_metacourse($metacourseid) {
global $CFG;
$alreadycourses = get_courses_in_metacourse($metacourseid);
$sql = "SELECT COUNT(c.id) AS notin FROM {$CFG->prefix}course c
WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).")
AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1";
if (!$count = get_record_sql($sql)) {
return 0;
}
return $count->notin;
}
/**
* Search through course users
*
* If $coursid specifies the site course then this function searches
* through all undeleted and confirmed users
*
* @uses $CFG
* @uses SITEID
* @param int $courseid The course in question.
* @param int $groupid The group in question.
* @param string $searchtext ?
* @param string $sort ?
* @param string $exceptions ?
* @return object
*/
function search_users($courseid, $groupid, $searchtext, $sort='', $exceptions='') {
global $CFG;
$LIKE = sql_ilike();
$fullname = sql_fullname('u.firstname', 'u.lastname');
if (!empty($exceptions)) {
$except = ' AND u.id NOT IN ('. $exceptions .') ';
} else {
$except = '';
}
if (!empty($sort)) {
$order = ' ORDER BY '. $sort;
} else {
$order = '';
}
$select = 'u.deleted = \'0\' AND u.confirmed = \'1\'';
if (!$courseid or $courseid == SITEID) {
return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
FROM {$CFG->prefix}user u
WHERE $select
AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
$except $order");
} else {
if ($groupid) {
//TODO:check. Remove group DB dependencies.
return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
FROM {$CFG->prefix}user u,
{$CFG->prefix}groups_members gm
WHERE $select AND gm.groupid = '$groupid' AND gm.userid = u.id
AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
$except $order");
} else {
$context = get_context_instance(CONTEXT_COURSE, $courseid);
$contextlists = get_related_contexts_string($context);
$users = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
FROM {$CFG->prefix}user u,
{$CFG->prefix}role_assignments ra
WHERE $select AND ra.contextid $contextlists AND ra.userid = u.id
AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
$except $order");
}
return $users;
}
}
/**
* Returns a list of all site users
* Obsolete, just calls get_course_users(SITEID)
*
* @uses SITEID
* @deprecated Use {@link get_course_users()} instead.
* @param string $fields A comma separated list of fields to be returned from the chosen table.
* @return object|false {@link $USER} records or false if error.
*/
function get_site_users($sort='u.lastaccess DESC', $fields='*', $exceptions='') {
return get_course_users(SITEID, $sort, $exceptions, $fields);
}
/**
* Returns a subset of users
*
* @uses $CFG
* @param bool $get If false then only a count of the records is returned
* @param string $search A simple string to search for
* @param bool $confirmed A switch to allow/disallow unconfirmed users
* @param array(int) $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
* @param string $sort A SQL snippet for the sorting criteria to use
* @param string $firstinitial ?
* @param string $lastinitial ?
* @param string $page ?
* @param string $recordsperpage ?
* @param string $fields A comma separated list of fields to be returned from the chosen table.
* @return object|false|int {@link $USER} records unless get is false in which case the integer count of the records found is returned. False is returned if an error is encountered.
*/
function get_users($get=true, $search='', $confirmed=false, $exceptions='', $sort='firstname ASC',
$firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*') {
global $CFG;
if ($get && !$recordsperpage) {
debugging('Call to get_users with $get = true no $recordsperpage limit. ' .
'On large installations, this will probably cause an out of memory error. ' .
'Please think again and change your code so that it does not try to ' .
'load so much data into memory.', DEBUG_DEVELOPER);
}
$LIKE = sql_ilike();
$fullname = sql_fullname();
$select = 'username <> \'guest\' AND deleted = 0';
if (!empty($search)){
$search = trim($search);
$select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
}
if ($confirmed) {
$select .= ' AND confirmed = \'1\' ';
}
if ($exceptions) {
$select .= ' AND id NOT IN ('. $exceptions .') ';
}
if ($firstinitial) {
$select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\'';
}
if ($lastinitial) {
$select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\'';
}
if ($get) {
return get_records_select('user', $select, $sort, $fields, $page, $recordsperpage);
} else {
return count_records_select('user', $select);
}
}
/**
* shortdesc (optional)
*
* longdesc
*
* @uses $CFG
* @param string $sort ?
* @param string $dir ?
* @param int $categoryid ?
* @param int $categoryid ?
* @param string $search ?
* @param string $firstinitial ?
* @param string $lastinitial ?
* @returnobject {@link $USER} records
* @todo Finish documenting this function
*/
function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,
$search='', $firstinitial='', $lastinitial='', $remotewhere='') {
global $CFG;
$LIKE = sql_ilike();
$fullname = sql_fullname();
$select = "deleted <> '1'";
if (!empty($search)) {
$search = trim($search);
$select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%' OR username='$search') ";
}
if ($firstinitial) {
$select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\' ';
}
if ($lastinitial) {
$select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\' ';
}
$select .= $remotewhere;
if ($sort) {
$sort = ' ORDER BY '. $sort .' '. $dir;
}
/// warning: will return UNCONFIRMED USERS
return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed, mnethostid
FROM {$CFG->prefix}user
WHERE $select $sort", $page, $recordsperpage);
}
/**
* Full list of users that have confirmed their accounts.
*
* @uses $CFG
* @return object
*/
function get_users_confirmed() {
global $CFG;
return get_records_sql("SELECT *
FROM {$CFG->prefix}user
WHERE confirmed = 1
AND deleted = 0
AND username <> 'guest'");
}
/**
* Full list of users that have not yet confirmed their accounts.
*
* @uses $CFG
* @param string $cutofftime ?
* @return object {@link $USER} records
*/
function get_users_unconfirmed($cutofftime=2000000000) {
global $CFG;
return get_records_sql("SELECT *
FROM {$CFG->prefix}user
WHERE confirmed = 0
AND firstaccess > 0
AND firstaccess < $cutofftime");
}
/**
* All users that we have not seen for a really long time (ie dead accounts)
*
* @uses $CFG
* @param string $cutofftime ?
* @return object {@link $USER} records
*/
function get_users_longtimenosee($cutofftime) {
global $CFG;
return get_records_sql("SELECT userid as id, courseid
FROM {$CFG->prefix}user_lastaccess
WHERE courseid != ".SITEID."
AND timeaccess > 0
AND timeaccess < $cutofftime ");
}
/**
* Full list of bogus accounts that are probably not ever going to be used
*
* @uses $CFG
* @param string $cutofftime ?
* @return object {@link $USER} records
*/
function get_users_not_fully_set_up($cutofftime=2000000000) {
global $CFG;
return get_records_sql("SELECT *
FROM {$CFG->prefix}user
WHERE confirmed = 1
AND lastaccess > 0
AND lastaccess < $cutofftime
AND deleted = 0
AND (lastname = '' OR firstname = '' OR email = '')");
}
/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
/**
* Returns $course object of the top-level site.
*
* @return course A {@link $COURSE} object for the site
*/
function get_site() {
global $SITE;
if (!empty($SITE->id)) { // We already have a global to use, so return that
return $SITE;
}
if ($course = get_record('course', 'category', 0)) {
return $course;
} else {
return false;
}
}
/**
* Returns list of courses, for whole site, or category
*
* Returns list of courses, for whole site, or category
* Important: Using c.* for fields is extremely expensive because
* we are using distinct. You almost _NEVER_ need all the fields
* in such a large SELECT
*
* @param type description
*
*/
function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
global $USER, $CFG;
if ($categoryid != "all" && is_numeric($categoryid)) {
$categoryselect = "WHERE c.category = '$categoryid'";
} else {
$categoryselect = "";
}
if (empty($sort)) {
$sortstatement = "";
} else {
$sortstatement = "ORDER BY $sort";
}
$visiblecourses = array();
// pull out all course matching the cat
if ($courses = get_records_sql("SELECT $fields
FROM {$CFG->prefix}course c
$categoryselect
$sortstatement")) {
// loop throught them
foreach ($courses as $course) {
if (isset($course->visible) && $course->visible <= 0) {
// for hidden courses, require visibility check
if (has_capability('moodle/course:viewhiddencourses',
get_context_instance(CONTEXT_COURSE, $course->id))) {
$visiblecourses [] = $course;
}
} else {
$visiblecourses [] = $course;
}
}
}
return $visiblecourses;
/*
$teachertable = "";
$visiblecourses = "";
$sqland = "";
if (!empty($categoryselect)) {
$sqland = "AND ";
}
if (!empty($USER->id)) { // May need to check they are a teacher
if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
$visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
$teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course = c.id";
}
} else {
$visiblecourses = "$sqland c.visible > 0";
}
if ($categoryselect or $visiblecourses) {
$selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
} else {
$selectsql = "{$CFG->prefix}course c $teachertable";
}
$extrafield = str_replace('ASC','',$sort);
$extrafield = str_replace('DESC','',$extrafield);
$extrafield = trim($extrafield);
if (!empty($extrafield)) {
$extrafield = ','.$extrafield;
}
return get_records_sql("SELECT ".((!empty($teachertable)) ? " DISTINCT " : "")." $fields $extrafield FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : ""));
*/
}
/**
* Returns list of courses, for whole site, or category
*
* Similar to get_courses, but allows paging
* Important: Using c.* for fields is extremely expensive because
* we are using distinct. You almost _NEVER_ need all the fields
* in such a large SELECT
*
* @param type description
*
*/
function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
&$totalcount, $limitfrom="", $limitnum="") {
global $USER, $CFG;
$categoryselect = "";
if ($categoryid != "all" && is_numeric($categoryid)) {
$categoryselect = "WHERE c.category = '$categoryid'";
} else {
$categoryselect = "";
}
// pull out all course matching the cat
$visiblecourses = array();
if (!($courses = get_records_sql("SELECT $fields
FROM {$CFG->prefix}course c
$categoryselect
ORDER BY $sort"))) {
return $visiblecourses;
}
$totalcount = 0;
if (!$limitnum) {
$limitnum = count($courses);
}
if (!$limitfrom) {
$limitfrom = 0;
}
// iteration will have to be done inside loop to keep track of the limitfrom and limitnum
foreach ($courses as $course) {
if ($course->visible <= 0) {
// for hidden courses, require visibility check
if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
$totalcount++;
if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
$visiblecourses [] = $course;
}
}
} else {
$totalcount++;
if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
$visiblecourses [] = $course;
}
}
}
return $visiblecourses;
/**
$categoryselect = "";
if ($categoryid != "all" && is_numeric($categoryid)) {
$categoryselect = "c.category = '$categoryid'";
}
$teachertable = "";
$visiblecourses = "";
$sqland = "";
if (!empty($categoryselect)) {
$sqland = "AND ";
}
if (!empty($USER) and !empty($USER->id)) { // May need to check they are a teacher
if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
$visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
$teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course=c.id";
}
} else {
$visiblecourses = "$sqland c.visible > 0";
}
if ($limitfrom !== "") {
$limit = sql_paging_limit($limitfrom, $limitnum);
} else {
$limit = "";
}
$selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
$totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
return get_records_sql("SELECT $fields FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : "")." $limit");
*/
}
/**
* List of courses that a user has access to view. Note that for admins,
* this usually includes every course on the system.
*
* @uses $CFG
* @param int $userid The user of interest
* @param string $sort the sortorder in the course table
* @param string $fields the fields to return
* @param bool $doanything True if using the doanything flag
* @param int $limit Maximum number of records to return, or 0 for unlimited
* @return array {@link $COURSE} of course objects
*/
function get_my_courses($userid, $sort=NULL, $fields=NULL, $doanything=false,$limit=0) {
global $CFG, $USER;
// Default parameters
$d_sort = 'visible DESC,sortorder ASC';
$d_fields = 'id, category, sortorder, shortname, fullname, idnumber, newsitems, teacher, teachers, student, students, guest, startdate, visible, cost, enrol, summary, groupmode, groupmodeforce';
$usingdefaults = true;
if (is_null($sort) || $sort === $d_sort) {
$sort = $d_sort;
} else {
$usingdefaults = false;
}
if (is_null($fields) || $fields === $d_fields) {
$fields = $d_fields;
} else {
$usingdefaults = false;
}
$reallimit = 0; // this is only set if we are using a limit on the first call
// If using default params, we may have it cached...
if (!empty($USER->id) && ($USER->id == $userid) && $usingdefaults) {
if (!empty($USER->mycourses[$doanything])) {
if ($limit && $limit < count($USER->mycourses[$doanything])) {
//silence warnings in PHP 4.x - the last param was added in PHP 5.0.2
return @array_slice($USER->mycourses[$doanything], 0, $limit, true);
} else {
return $USER->mycourses[$doanything];
}
} else {
// now, this is the first call, i.e. no cache, and we are using defaults, with a limit supplied,
// we need to store the limit somewhere, retrieve all, cache properly and then slice the array
// to return the proper number of entries. This is so that we don't keep missing calls like limit 20,20,20
if ($limit) {
$reallimit = $limit;
$limit = 0;
}
}
}
$mycourses = array();
// Fix fields to refer to the course table c
$fields=preg_replace('/([a-z0-9*]+)/','c.$1',$fields);
// Attempt to filter the list of courses in order to reduce the number
// of queries in the next part.
// Check root permissions
$sitecontext = get_context_instance(CONTEXT_SYSTEM, SITEID);
// Guest's do not have any courses
if (has_capability('moodle/legacy:guest',$sitecontext,$userid,false)) {
return(array());
}
// we can optimise some things for true admins
$candoanything = false;
if ($doanything && has_capability('moodle/site:doanything',$sitecontext,$userid,true)) {
$candoanything = true;
}
if ($candoanything || has_capability('moodle/course:view',$sitecontext,$userid,$doanything)) {
// User can view all courses, although there might be exceptions
// which we will filter later.
$rs = get_recordset('course c', '', '', $sort, $fields);
} else {
// The only other context level above courses that applies to moodle/course:view
// is category. So we consider:
// 1. All courses in which the user is assigned a role
// 2. All courses in categories in which the user is assigned a role
// 2BIS. All courses in subcategories in which the user gets assignment because he is assigned in one of its ascendant categories
// 3. All courses which have overrides for moodle/course:view
// Remember that this is just a filter. We check each individual course later.
// However for a typical student on a large system this can reduce the
// number of courses considered from around 2,000 to around 2, with corresponding
// reduction in the number of queries needed.
$rs=get_recordset_sql("
SELECT $fields
FROM {$CFG->prefix}course c, (
SELECT
c.id
FROM
{$CFG->prefix}role_assignments ra
INNER JOIN {$CFG->prefix}context x ON x.id = ra.contextid
INNER JOIN {$CFG->prefix}course c ON x.instanceid = c.id
WHERE
ra.userid = $userid AND
x.contextlevel = 50
UNION
SELECT
c.id
FROM
{$CFG->prefix}role_assignments ra
INNER JOIN {$CFG->prefix}context x ON x.id = ra.contextid
INNER JOIN {$CFG->prefix}course_categories a ON a.path LIKE ".sql_concat("'%/'", 'x.instanceid', "'/%'")." OR x.instanceid = a.id
INNER JOIN {$CFG->prefix}course c ON c.category = a.id
WHERE
ra.userid = $userid AND
x.contextlevel = 40
UNION
SELECT
c.id
FROM
{$CFG->prefix}role_capabilities ca
INNER JOIN {$CFG->prefix}context x ON x.id = ca.contextid
INNER JOIN {$CFG->prefix}course c ON c.id = x.instanceid
WHERE
ca.capability = 'moodle/course:view' AND
ca.contextid != {$sitecontext->id} AND
x.contextlevel = 50
) cids
WHERE c.id = cids.id
ORDER BY $sort"
);
}
if ($rs && $rs->RecordCount() > 0) {
while ($course = rs_fetch_next_record($rs)) {
if ($course->id != SITEID) {
if ($candoanything) { // no need for further checks...
$mycourses[$course->id] = $course;
continue;
}
// users with moodle/course:view are considered course participants
// the course needs to be visible, or user must have moodle/course:viewhiddencourses
// capability set to view hidden courses
$context = get_context_instance(CONTEXT_COURSE, $course->id);
if ( has_capability('moodle/course:view', $context, $userid, $doanything) &&
!has_capability('moodle/legacy:guest', $context, $userid, false) &&
($course->visible || has_capability('moodle/course:viewhiddencourses', $context, $userid))) {
$mycourses[$course->id] = $course;
// Only return a limited number of courses if limit is set
if($limit>0) {
$limit--;
if($limit==0) {
break;
}
}
}
}
}
}
// Cache if using default params...
if (!empty($USER->id) && ($USER->id == $userid) && $usingdefaults && $limit == 0) {
$USER->mycourses[$doanything] = $mycourses;
}
if (!empty($mycourses) && $reallimit) {
return array_slice($mycourses, 0, $reallimit, true);
} else {
return $mycourses;
}
}
/**
* A list of courses that match a search
*
* @uses $CFG
* @param array $searchterms ?
* @param string $sort ?
* @param int $page ?
* @param int $recordsperpage ?
* @param int $totalcount Passed in by reference. ?
* @return object {@link $COURSE} records
*/
function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
global $CFG;
//to allow case-insensitive search for postgesql
if ($CFG->dbfamily == 'postgres') {
$LIKE = 'ILIKE';
$NOTLIKE = 'NOT ILIKE'; // case-insensitive
$REGEXP = '~*';
$NOTREGEXP = '!~*';
} else {
$LIKE = 'LIKE';
$NOTLIKE = 'NOT LIKE';
$REGEXP = 'REGEXP';
$NOTREGEXP = 'NOT REGEXP';
}
$fullnamesearch = '';
$summarysearch = '';
foreach ($searchterms as $searchterm) {
/// Under Oracle and MSSQL, trim the + and - operators and perform
/// simpler LIKE search
if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') {
$searchterm = trim($searchterm, '+-');
}
if ($fullnamesearch) {
$fullnamesearch .= ' AND ';
}
if ($summarysearch) {
$summarysearch .= ' AND ';
}
if (substr($searchterm,0,1) == '+') {
$searchterm = substr($searchterm,1);
$summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
$fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
} else if (substr($searchterm,0,1) == "-") {
$searchterm = substr($searchterm,1);
$summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
$fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
} else {
$summarysearch .= ' summary '. $LIKE .' \'%'. $searchterm .'%\' ';
$fullnamesearch .= ' fullname '. $LIKE .' \'%'. $searchterm .'%\' ';
}
}
$selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\'';
$totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
$courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort, $page, $recordsperpage);
if ($courses) { /// Remove unavailable courses from the list
foreach ($courses as $key => $course) {
if (!$course->visible) {
if (!has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
unset($courses[$key]);
$totalcount--;
}
}
}
}
return $courses;
}
/**
* Returns a sorted list of categories
*
* @param string $parent The parent category if any
* @param string $sort the sortorder
* @return array of categories
*/
function get_categories($parent='none', $sort='sortorder ASC') {
if ($parent === 'none') {
$categories = get_records('course_categories', '', '', $sort);
} else {
$categories = get_records('course_categories', 'parent', $parent, $sort);
}
if ($categories) { /// Remove unavailable categories from the list
foreach ($categories as $key => $category) {
if (!$category->visible) {
if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $category->id))) {
unset($categories[$key]);
}
}
}
}
return $categories;
}
/**
* Returns an array of category ids of all the subcategories for a given
* category.
* @param $catid - The id of the category whose subcategories we want to find.
* @return array of category ids.
*/
function get_all_subcategories($catid) {
$subcats = array();
if ($categories = get_records('course_categories', 'parent', $catid)) {
foreach ($categories as $cat) {
array_push($subcats, $cat->id);
$subcats = array_merge($subcats, get_all_subcategories($cat->id));
}
}
return $subcats;
}
/**
* This recursive function makes sure that the courseorder is consecutive
*
* @param type description
*
* $n is the starting point, offered only for compatilibity -- will be ignored!
* $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade
* safely from 1.4 to 1.5
*/
function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') {
global $CFG;
$count = 0;
$catgap = 1000; // "standard" category gap
$tolerance = 200; // how "close" categories can get
if ($categoryid > 0){
// update depth and path
$cat = get_record('course_categories', 'id', $categoryid);
if ($cat->parent == 0) {
$depth = 0;
$path = '';
} else if ($depth == 0 ) { // doesn't make sense; get from DB
// this is only called if the $depth parameter looks dodgy
$parent = get_record('course_categories', 'id', $cat->parent);
$path = $parent->path;
$depth = $parent->depth;
}
$path = $path . '/' . $categoryid;
$depth = $depth + 1;
set_field('course_categories', 'path', addslashes($path), 'id', $categoryid);
set_field('course_categories', 'depth', $depth, 'id', $categoryid);
}
// get some basic info about courses in the category
$info = get_record_sql('SELECT MIN(sortorder) AS min,
MAX(sortorder) AS max,
COUNT(sortorder) AS count
FROM ' . $CFG->prefix . 'course
WHERE category=' . $categoryid);
if (is_object($info)) { // no courses?
$max = $info->max;
$count = $info->count;
$min = $info->min;
unset($info);
}
if ($categoryid > 0 && $n==0) { // only passed category so don't shift it
$n = $min;
}
// $hasgap flag indicates whether there's a gap in the sequence
$hasgap = false;
if ($max-$min+1 != $count) {
$hasgap = true;
}
// $mustshift indicates whether the sequence must be shifted to
// meet its range
$mustshift = false;
if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) {
$mustshift = true;
}
// actually sort only if there are courses,
// and we meet one ofthe triggers:
// - safe flag
// - they are not in a continuos block
// - they are too close to the 'bottom'
if ($count && ( $safe || $hasgap || $mustshift ) ) {
// special, optimized case where all we need is to shift
if ( $mustshift && !$safe && !$hasgap) {
$shift = $n + $catgap - $min;
// UPDATE course SET sortorder=sortorder+$shift
execute_sql("UPDATE {$CFG->prefix}course
SET sortorder=sortorder+$shift
WHERE category=$categoryid", 0);
$n = $n + $catgap + $count;
} else { // do it slowly
$n = $n + $catgap;
// if the new sequence overlaps the current sequence, lack of transactions
// will stop us -- shift things aside for a moment...
if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) {
$shift = $max + $n + 1000;
execute_sql("UPDATE {$CFG->prefix}course
SET sortorder=sortorder+$shift
WHERE category=$categoryid", 0);
}
$courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
begin_sql();
foreach ($courses as $course) {
if ($course->sortorder != $n ) { // save db traffic
set_field('course', 'sortorder', $n, 'id', $course->id);
}
$n++;
}
commit_sql();
}
}
set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
// $n could need updating
$max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid");
if ($max > $n) {
$n = $max;
}
if ($categories = get_categories($categoryid)) {
foreach ($categories as $category) {