Completed
Push — master ( 1285bd...aa4c59 )
by Julito
12:22
created

countAvailableCoursesToShowInCatalog()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 28
Code Lines 16

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 2
eloc 16
nc 2
nop 1
dl 0
loc 28
rs 9.7333
c 0
b 0
f 0
1
<?php
2
/* For licensing terms, see /license.txt */
3
4
use Chamilo\CoreBundle\Entity\ExtraField;
5
use Doctrine\ORM\Query\Expr\Join;
6
7
/**
8
 * @todo change class name
9
 * Class CoursesAndSessionsCatalog
10
 */
11
class CoursesAndSessionsCatalog
12
{
13
    public const PAGE_LENGTH = 12;
14
15
    /**
16
     * Check the configuration for the courses and sessions catalog.
17
     *
18
     * @global array $_configuration Configuration
19
     *
20
     * @param int $value The value to check
21
     *
22
     * @return bool Whether the configuration is $value
23
     */
24
    public static function is($value = CATALOG_COURSES)
25
    {
26
        $showCoursesSessions = (int) api_get_setting('catalog_show_courses_sessions');
27
        if ($showCoursesSessions == $value) {
28
            return true;
29
        }
30
31
        return false;
32
    }
33
34
    /**
35
     * Check whether to display the sessions list.
36
     *
37
     * @global array $_configuration Configuration
38
     *
39
     * @return bool whether to display
40
     */
41
    public static function showSessions()
42
    {
43
        $catalogShow = (int) api_get_setting('catalog_show_courses_sessions');
44
45
        if ($catalogShow == CATALOG_SESSIONS || $catalogShow == CATALOG_COURSES_SESSIONS) {
46
            return true;
47
        }
48
49
        return false;
50
    }
51
52
    /**
53
     * Check whether to display the courses list.
54
     *
55
     * @global array $_configuration Configuration
56
     *
57
     * @return bool whether to display
58
     */
59
    public static function showCourses()
60
    {
61
        $catalogShow = (int) api_get_setting('catalog_show_courses_sessions');
62
63
        if ($catalogShow == CATALOG_COURSES || $catalogShow == CATALOG_COURSES_SESSIONS) {
64
            return true;
65
        }
66
67
        return false;
68
    }
69
70
    /**
71
     * @return array
72
     */
73
    public static function getCoursesToAvoid()
74
    {
75
        $TABLE_COURSE_FIELD = Database::get_main_table(TABLE_EXTRA_FIELD);
76
        $TABLE_COURSE_FIELD_VALUE = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
77
78
        // Check special courses
79
        $courseListToAvoid = CourseManager::get_special_course_list();
80
81
        // Checks "hide_from_catalog" extra field
82
        $extraFieldType = ExtraField::COURSE_FIELD_TYPE;
83
84
        $sql = "SELECT item_id FROM $TABLE_COURSE_FIELD_VALUE tcfv
85
                INNER JOIN $TABLE_COURSE_FIELD tcf
86
                ON tcfv.field_id =  tcf.id
87
                WHERE
88
                    tcf.extra_field_type = $extraFieldType AND
89
                    tcf.variable = 'hide_from_catalog' AND
90
                    tcfv.value = 1
91
                ";
92
93
        $result = Database::query($sql);
94
        if (Database::num_rows($result) > 0) {
95
            while ($row = Database::fetch_array($result)) {
96
                $courseListToAvoid[] = $row['item_id'];
97
            }
98
        }
99
100
        return $courseListToAvoid;
101
    }
102
103
    /**
104
     * @return string
105
     */
106
    public static function getAvoidCourseCondition()
107
    {
108
        $courseListToAvoid = self::getCoursesToAvoid();
109
        $condition = '';
110
        if (!empty($courseListToAvoid)) {
111
            $courses = [];
112
            foreach ($courseListToAvoid as $courseId) {
113
                $courses[] = '"'.$courseId.'"';
114
            }
115
            $condition = ' AND course.id NOT IN ('.implode(',', $courses).')';
116
        }
117
118
        return $condition;
119
    }
120
121
    /**
122
     * Get available le courses count.
123
     *
124
     * @param int $accessUrlId (optional)
125
     *
126
     * @return int Number of courses
127
     */
128
    public static function countAvailableCoursesToShowInCatalog($accessUrlId = 1)
129
    {
130
        $tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
131
        $tableCourseRelAccessUrl = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
132
        $courseToAvoidCondition = self::getAvoidCourseCondition();
133
        $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
134
135
        $accessUrlId = (int) $accessUrlId;
136
        if (empty($accessUrlId)) {
137
            $accessUrlId = 1;
138
        }
139
140
        $sql = "SELECT count(course.id)
141
                FROM $tableCourse course
142
                INNER JOIN $tableCourseRelAccessUrl u
143
                ON (course.id = u.c_id)
144
                WHERE
145
                    u.access_url_id = $accessUrlId AND
146
                    course.visibility != 0 AND
147
                    course.visibility != 4
148
                    $courseToAvoidCondition
149
                    $visibilityCondition
150
                ";
151
152
        $res = Database::query($sql);
153
        $row = Database::fetch_row($res);
154
155
        return $row[0];
156
    }
157
158
    public static function getCourseCategoriesTree()
159
    {
160
        $urlId = 1;
161
        if (api_is_multiple_url_enabled()) {
162
            $urlId = api_get_current_access_url_id();
163
        }
164
165
        $countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
166
        $categories = [];
167
        $list = [];
168
169
        $categories['ALL'] = [
170
            'id' => 0,
171
            'name' => get_lang('Display all'),
172
            'code' => 'ALL',
173
            'parent_id' => null,
174
            'tree_pos' => 0,
175
            'number_courses' => $countCourses,
176
            'level' => 0,
177
        ];
178
179
        $allCategories = CourseCategory::getAllCategories();
180
181
        foreach ($allCategories as $category) {
182
            if (empty($category['parent_id'])) {
183
                $list[$category['code']] = $category;
184
                $list[$category['code']]['level'] = 0;
185
                list($subList, $childrenCount) = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
186
                foreach ($subList as $item) {
187
                    $list[$item['code']] = $item;
188
                }
189
                // Real course count
190
                $countCourses = CourseCategory::countCoursesInCategory($category['code']);
191
                $list[$category['code']]['number_courses'] = $childrenCount + $countCourses;
192
            }
193
        }
194
195
        // count courses that are in no category
196
        $countCourses = CourseCategory::countCoursesInCategory();
197
        $categories['NONE'] = [
198
            'id' => 0,
199
            'name' => get_lang('Without category'),
200
            'code' => 'NONE',
201
            'parent_id' => null,
202
            'tree_pos' => 0,
203
            'children_count' => 0,
204
            'auth_course_child' => true,
205
            'auth_cat_child' => true,
206
            'number_courses' => $countCourses,
207
            'level' => 0,
208
        ];
209
210
        $result = array_merge($list, $categories);
211
212
        return $result;
213
    }
214
215
    /**
216
     * @return array
217
     */
218
    public static function getCourseCategories()
219
    {
220
        $urlId = 1;
221
        if (api_is_multiple_url_enabled()) {
222
            $urlId = api_get_current_access_url_id();
223
        }
224
225
        $countCourses = self::countAvailableCoursesToShowInCatalog($urlId);
226
227
        $categories = [];
228
        $categories[0][0] = [
229
            'id' => 0,
230
            'name' => get_lang('Display all'),
231
            'code' => 'ALL',
232
            'parent_id' => null,
233
            'tree_pos' => 0,
234
            'count_courses' => $countCourses,
235
        ];
236
237
        $categoriesFromDatabase = CourseCategory::getCategories();
238
239
        foreach ($categoriesFromDatabase as $row) {
240
            $countCourses = CourseCategory::countCoursesInCategory($row['code']);
241
            $row['count_courses'] = $countCourses;
242
            if (empty($row['parent_id'])) {
243
                $categories[0][$row['tree_pos']] = $row;
244
            } else {
245
                $categories[$row['parent_id']][$row['tree_pos']] = $row;
246
            }
247
        }
248
249
        // count courses that are in no category
250
        $countCourses = CourseCategory::countCoursesInCategory();
251
        $categories[0][count($categories[0]) + 1] = [
252
            'id' => 0,
253
            'name' => get_lang('none'),
254
            'code' => 'NONE',
255
            'parent_id' => null,
256
            'tree_pos' => $row['tree_pos'] + 1,
257
            'children_count' => 0,
258
            'auth_course_child' => true,
259
            'auth_cat_child' => true,
260
            'count_courses' => $countCourses,
261
        ];
262
263
        return $categories;
264
    }
265
266
    /**
267
     * Return LIMIT to filter SQL query.
268
     *
269
     * @param array $limit
270
     *
271
     * @return string
272
     */
273
    public static function getLimitFilterFromArray($limit)
274
    {
275
        $limitFilter = '';
276
        if (!empty($limit) && is_array($limit)) {
277
            $limitStart = isset($limit['start']) ? (int) $limit['start'] : 0;
278
            $limitLength = isset($limit['length']) ? (int) $limit['length'] : 12;
279
            $limitFilter = 'LIMIT '.$limitStart.', '.$limitLength;
280
        }
281
282
        return $limitFilter;
283
    }
284
285
    /**
286
     * @param string $category_code
287
     * @param int    $random_value
288
     * @param array  $limit         will be used if $random_value is not set.
289
     *                              This array should contains 'start' and 'length' keys
290
     *
291
     * @return array
292
     */
293
    public static function getCoursesInCategory($category_code, $random_value = null, $limit = [])
294
    {
295
        $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
296
        $avoidCoursesCondition = self::getAvoidCourseCondition();
297
        $visibilityCondition = CourseManager::getCourseVisibilitySQLCondition('course', true);
298
299
        if (!empty($random_value)) {
300
            $random_value = (int) $random_value;
301
302
            $sql = "SELECT COUNT(*) FROM $tbl_course";
303
            $result = Database::query($sql);
304
            list($num_records) = Database::fetch_row($result);
305
306
            if (api_is_multiple_url_enabled()) {
307
                $urlId = api_get_current_access_url_id();
308
                $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
309
310
                $urlCondition = ' access_url_id = '.$urlId.' ';
311
                $allowBaseCategories = api_get_configuration_value('allow_base_course_category');
312
                if ($allowBaseCategories) {
313
                    $urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1)  ';
314
                }
315
316
                $sql = "SELECT COUNT(*) FROM $tbl_course course
317
                        INNER JOIN $tbl_url_rel_course as url_rel_course
318
                        ON (url_rel_course.c_id = course.id)
319
                        WHERE access_url_id = '.$urlId.' ";
320
                $result = Database::query($sql);
321
                list($num_records) = Database::fetch_row($result);
322
323
                $sql = "SELECT course.id, course.id as real_id
324
                        FROM $tbl_course course
325
                        INNER JOIN $tbl_url_rel_course as url_rel_course
326
                        ON (url_rel_course.c_id = course.id)
327
                        WHERE
328
                            $urlCondition AND
329
                            RAND()*$num_records< $random_value
330
                            $avoidCoursesCondition
331
                            $visibilityCondition
332
                        ORDER BY RAND()
333
                        LIMIT 0, $random_value";
334
            } else {
335
                $sql = "SELECT id, id as real_id FROM $tbl_course course
336
                        WHERE
337
                            RAND()*$num_records< $random_value
338
                            $avoidCoursesCondition
339
                            $visibilityCondition
340
                        ORDER BY RAND()
341
                        LIMIT 0, $random_value";
342
            }
343
344
            $result = Database::query($sql);
345
            $id_in = null;
346
            while (list($id) = Database::fetch_row($result)) {
347
                if ($id_in) {
348
                    $id_in .= ",$id";
349
                } else {
350
                    $id_in = "$id";
351
                }
352
            }
353
            if ($id_in === null) {
354
                return [];
355
            }
356
            $sql = "SELECT *, id as real_id FROM $tbl_course WHERE id IN($id_in)";
357
        } else {
358
            $limitFilter = self::getLimitFilterFromArray($limit);
359
            $category_code = Database::escape_string($category_code);
360
            $listCode = self::childrenCategories($category_code);
361
            $conditionCode = ' ';
362
            $joinCategory = '';
363
364
            if (empty($listCode)) {
365
                if ($category_code === 'NONE') {
366
                    $conditionCode .= " category_id IS NULL";
367
                } else {
368
                    $conditionCode .= " course_category.code = '$category_code' ";
369
                    $joinCategory = " LEFT JOIN $tblCourseCategory course_category ON course.category_id = course_category.id ";
370
                }
371
            } else {
372
                foreach ($listCode as $code) {
373
                    $conditionCode .= " course_category.code = '$category_code' OR ";
374
                    $joinCategory = " LEFT JOIN $tblCourseCategory course_category ON course.category_id = course_category.id ";
375
                }
376
                $conditionCode .= " course_category.code = '$category_code' ";
377
            }
378
379
            if (empty($category_code) || $category_code == 'ALL') {
380
                $sql = "SELECT *, id as real_id
381
                        FROM $tbl_course course
382
                        WHERE
383
                          1=1
384
                          $avoidCoursesCondition
385
                          $visibilityCondition
386
                    ORDER BY title $limitFilter ";
387
            } else {
388
                $sql = "SELECT course.*, id as real_id FROM $tbl_course course
389
                        $joinCategory
390
                        WHERE
391
                            $conditionCode
392
                            $avoidCoursesCondition
393
                            $visibilityCondition
394
                        ORDER BY title $limitFilter ";
395
            }
396
397
            // Showing only the courses of the current Chamilo access_url_id
398
            if (api_is_multiple_url_enabled()) {
399
                $urlId = api_get_current_access_url_id();
400
                $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
401
402
                $urlCondition = ' access_url_id = '.$urlId.' ';
403
                if ($category_code != 'ALL') {
404
                    $sql = "SELECT *, course.id real_id, course_category.code AS category_code FROM $tbl_course as course
405
                            $joinCategory
406
                            INNER JOIN $tbl_url_rel_course as url_rel_course
407
                            ON (url_rel_course.c_id = course.id)
408
                            WHERE
409
                                $urlCondition AND
410
                                $conditionCode
411
                                $avoidCoursesCondition
412
                                $visibilityCondition
413
                            ORDER BY title $limitFilter";
414
                } else {
415
                    $sql = "SELECT *, course.id real_id FROM $tbl_course as course
416
                            INNER JOIN $tbl_url_rel_course as url_rel_course
417
                            ON (url_rel_course.c_id = course.id)
418
                            WHERE
419
                                $urlCondition
420
                                $avoidCoursesCondition
421
                                $visibilityCondition
422
                            ORDER BY title $limitFilter";
423
                }
424
            }
425
        }
426
427
        $result = Database::query($sql);
428
        $courses = [];
429
        while ($row = Database::fetch_array($result)) {
430
            $row['registration_code'] = !empty($row['registration_code']);
431
            $count_users = CourseManager::get_users_count_in_course($row['code']);
432
            $count_connections_last_month = Tracking::get_course_connections_count(
433
                $row['id'],
434
                0,
435
                api_get_utc_datetime(time() - (30 * 86400))
436
            );
437
438
            if ($row['tutor_name'] == '0') {
439
                $row['tutor_name'] = get_lang('No administrator');
440
            }
441
            $point_info = CourseManager::get_course_ranking($row['id'], 0);
442
            $courseInfo = api_get_course_info_by_id($row['id']);
443
444
            $course = [
445
                'real_id' => $row['real_id'],
446
                'point_info' => $point_info,
447
                'code' => $row['code'],
448
                'directory' => $row['directory'],
449
                'visual_code' => $row['visual_code'],
450
                'title' => $row['title'],
451
                'tutor' => $row['tutor_name'],
452
                'subscribe' => $row['subscribe'],
453
                'unsubscribe' => $row['unsubscribe'],
454
                'registration_code' => $row['registration_code'],
455
                'creation_date' => $row['creation_date'],
456
                'visibility' => $row['visibility'],
457
                'category' => $row['category_id'],
458
                'count_users' => $count_users,
459
                'count_connections' => $count_connections_last_month,
460
            ];
461
462
            $courseInfo = array_merge($courseInfo, $course);
463
464
            $courses[] = $courseInfo;
465
        }
466
467
        return $courses;
468
    }
469
470
    /**
471
     * Search the courses database for a course that matches the search term.
472
     * The search is done on the code, title and tutor field of the course table.
473
     *
474
     * @param string $search_term The string that the user submitted, what we are looking for
475
     * @param array  $limit
476
     * @param bool   $justVisible search only on visible courses in the catalogue
477
     *
478
     * @return array an array containing a list of all the courses matching the the search term
479
     */
480
    public static function search_courses($search_term, $limit, $justVisible = false)
481
    {
482
        $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
483
        $limitFilter = self::getLimitFilterFromArray($limit);
484
        $avoidCoursesCondition = self::getAvoidCourseCondition();
485
        $visibilityCondition = $justVisible ? CourseManager::getCourseVisibilitySQLCondition('course', true) : '';
486
        $search_term_safe = Database::escape_string($search_term);
487
        $sql = "SELECT * FROM $courseTable course
488
                WHERE (
489
                        course.code LIKE '%".$search_term_safe."%' OR
490
                        course.title LIKE '%".$search_term_safe."%' OR
491
                        course.tutor_name LIKE '%".$search_term_safe."%'
492
                    )
493
                    $avoidCoursesCondition
494
                    $visibilityCondition
495
                ORDER BY title, visual_code ASC
496
                $limitFilter
497
                ";
498
499
        if (api_is_multiple_url_enabled()) {
500
            $urlId = api_get_current_access_url_id();
501
            if ($urlId != -1) {
502
                $tbl_url_rel_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
503
504
                $urlCondition = ' access_url_id = '.$urlId.' AND';
505
                $allowBaseCategories = api_get_configuration_value('allow_base_course_category');
506
                if ($allowBaseCategories) {
507
                    $urlCondition = ' (access_url_id = '.$urlId.' OR access_url_id = 1) AND ';
508
                }
509
510
                $sql = "SELECT course.*
511
                        FROM $courseTable as course
512
                        INNER JOIN $tbl_url_rel_course as url_rel_course
513
                        ON (url_rel_course.c_id = course.id)
514
                        WHERE
515
                            access_url_id = $urlId AND
516
                            (
517
                                code LIKE '%".$search_term_safe."%' OR
518
                                title LIKE '%".$search_term_safe."%' OR
519
                                tutor_name LIKE '%".$search_term_safe."%'
520
                            )
521
                            $avoidCoursesCondition
522
                            $visibilityCondition
523
                        ORDER BY title, visual_code ASC
524
                        $limitFilter
525
                       ";
526
            }
527
        }
528
        $result_find = Database::query($sql);
529
        $courses = [];
530
        while ($row = Database::fetch_array($result_find)) {
531
            $row['registration_code'] = !empty($row['registration_code']);
532
            $countUsers = CourseManager::get_user_list_from_course_code(
533
                $row['code'],
534
                0,
535
                null,
536
                null,
537
                null,
538
                true
539
            );
540
            $connectionsLastMonth = Tracking::get_course_connections_count(
541
                $row['id'],
542
                0,
543
                api_get_utc_datetime(time() - (30 * 86400))
544
            );
545
546
            $point_info = CourseManager::get_course_ranking($row['id'], 0);
547
548
            $courses[] = [
549
                'real_id' => $row['id'],
550
                'point_info' => $point_info,
551
                'code' => $row['code'],
552
                'directory' => $row['directory'],
553
                'visual_code' => $row['visual_code'],
554
                'title' => $row['title'],
555
                'tutor' => $row['tutor_name'],
556
                'subscribe' => $row['subscribe'],
557
                'unsubscribe' => $row['unsubscribe'],
558
                'registration_code' => $row['registration_code'],
559
                'creation_date' => $row['creation_date'],
560
                'visibility' => $row['visibility'],
561
                'count_users' => $countUsers,
562
                'count_connections' => $connectionsLastMonth,
563
            ];
564
        }
565
566
        return $courses;
567
    }
568
569
    /**
570
     * List the sessions.
571
     *
572
     * @param string $date
573
     * @param array  $limit
574
     * @param bool   $returnQueryBuilder
575
     * @param bool   $getCount
576
     *
577
     * @return array|\Doctrine\ORM\Query The session list
578
     */
579
    public static function browseSessions($date = null, $limit = [], $returnQueryBuilder = false, $getCount = false)
580
    {
581
        $urlId = api_get_current_access_url_id();
582
583
        $select = 's';
584
        if ($getCount) {
585
            $select = 'count(s) ';
586
        }
587
588
        $dql = "SELECT $select
589
                FROM ChamiloCoreBundle:Session s
590
                WHERE EXISTS
591
                    (
592
                        SELECT url.sessionId FROM ChamiloCoreBundle:AccessUrlRelSession url
593
                        WHERE url.sessionId = s.id AND url.accessUrlId = $urlId
594
                    ) AND
595
                    s.nbrCourses > 0
596
                ";
597
        if (!is_null($date)) {
598
            $date = Database::escape_string($date);
599
            $dql .= "
600
                AND (
601
                    (s.accessEndDate IS NULL)
602
                    OR
603
                    (
604
                    s.accessStartDate IS NOT NULL AND
605
                    s.accessEndDate IS NOT NULL AND
606
                    s.accessStartDate <= '$date' AND s.accessEndDate >= '$date')
607
                    OR
608
                    (
609
                        s.accessStartDate IS NULL AND
610
                        s.accessEndDate IS NOT NULL AND
611
                        s.accessEndDate >= '$date'
612
                    )
613
                )
614
            ";
615
        }
616
617
        $qb = Database::getManager()->createQuery($dql);
618
619
        if (!empty($limit)) {
620
            $qb
621
                ->setFirstResult($limit['start'])
622
                ->setMaxResults($limit['length'])
623
            ;
624
        }
625
626
        if ($returnQueryBuilder) {
627
            return $qb;
628
        }
629
630
        if ($getCount) {
631
            return $qb->getSingleScalarResult();
632
        }
633
634
        return $qb->getResult();
635
    }
636
637
    /**
638
     * Search sessions by the tags in their courses.
639
     *
640
     * @param string $termTag Term for search in tags
641
     * @param array  $limit   Limit info
642
     *
643
     * @return array The sessions
644
     */
645
    public static function browseSessionsByTags($termTag, array $limit)
646
    {
647
        $em = Database::getManager();
648
        $qb = $em->createQueryBuilder();
649
650
        $urlId = api_get_current_access_url_id();
651
652
        $sessions = $qb->select('s')
653
            ->distinct()
654
            ->from('ChamiloCoreBundle:Session', 's')
655
            ->innerJoin(
656
                'ChamiloCoreBundle:SessionRelCourse',
657
                'src',
658
                Join::WITH,
659
                's.id = src.session'
660
            )
661
            ->innerJoin(
662
                'ChamiloCoreBundle:AccessUrlRelSession',
663
                'url',
664
                Join::WITH,
665
                'url.sessionId = s.id'
666
            )
667
            ->innerJoin(
668
                'ChamiloCoreBundle:ExtraFieldRelTag',
669
                'frt',
670
                Join::WITH,
671
                'src.course = frt.itemId'
672
            )
673
            ->innerJoin(
674
                'ChamiloCoreBundle:Tag',
675
                't',
676
                Join::WITH,
677
                'frt.tagId = t.id'
678
            )
679
            ->innerJoin(
680
                'ChamiloCoreBundle:ExtraField',
681
                'f',
682
                Join::WITH,
683
                'frt.fieldId = f.id'
684
            )
685
            ->where(
686
                $qb->expr()->like('t.tag', ':tag')
687
            )
688
            ->andWhere(
689
                $qb->expr()->eq('f.extraFieldType', ExtraField::COURSE_FIELD_TYPE)
690
            )->andWhere(
691
                $qb->expr()->eq('url.accessUrlId', $urlId)
692
            /*)->andWhere(
693
                's.name LIKE :name'
694
            )*/
695
            )
696
            ->setFirstResult($limit['start'])
697
            ->setMaxResults($limit['length'])
698
            ->setParameter('tag', "$termTag%")
699
            //->setParameter('name', "%$termTag%")
700
            ->getQuery()
701
            ->getResult();
702
703
        $sessionsToBrowse = [];
704
        foreach ($sessions as $session) {
705
            if ($session->getNbrCourses() === 0) {
706
                continue;
707
            }
708
            $sessionsToBrowse[] = $session;
709
        }
710
711
        return $sessionsToBrowse;
712
    }
713
714
    /**
715
     * Search sessions by the title
716
     *
717
     * @param string $keyword
718
     * @param array  $limit   Limit info
719
     *
720
     * @return array The sessions
721
     */
722
    public static function getSessionsByName($keyword, array $limit)
723
    {
724
        $em = Database::getManager();
725
        $qb = $em->createQueryBuilder();
726
727
        $urlId = api_get_current_access_url_id();
728
729
        $sessions = $qb->select('s')
730
            ->distinct()
731
            ->from('ChamiloCoreBundle:Session', 's')
732
            ->innerJoin(
733
                'ChamiloCoreBundle:SessionRelCourse',
734
                'src',
735
                Join::WITH,
736
                's.id = src.session'
737
            )
738
            ->innerJoin(
739
                'ChamiloCoreBundle:AccessUrlRelSession',
740
                'url',
741
                Join::WITH,
742
                'url.sessionId = s.id'
743
            )
744
            ->andWhere(
745
                $qb->expr()->eq('url.accessUrlId', $urlId)
746
            )->andWhere(
747
                's.name LIKE :keyword'
748
            )
749
            ->setFirstResult($limit['start'])
750
            ->setMaxResults($limit['length'])
751
            ->setParameter('keyword', "%$keyword%")
752
            ->getQuery()
753
            ->getResult();
754
755
        $sessionsToBrowse = [];
756
        foreach ($sessions as $session) {
757
            if ($session->getNbrCourses() === 0) {
758
                continue;
759
            }
760
            $sessionsToBrowse[] = $session;
761
        }
762
763
        return $sessionsToBrowse;
764
    }
765
766
    /**
767
     * Build a recursive tree of course categories.
768
     *
769
     * @param array $categories
770
     * @param int   $parentId
771
     * @param int   $level
772
     *
773
     * @return array
774
     */
775
    public static function buildCourseCategoryTree($categories, $parentId = 0, $level = 0)
776
    {
777
        $list = [];
778
        $count = 0;
779
        $level++;
780
        foreach ($categories as $category) {
781
            if (empty($category['parent_id'])) {
782
                continue;
783
            }
784
            if ($category['parent_id'] == $parentId) {
785
                $list[$category['code']] = $category;
786
                $count += $category['number_courses'];
787
                $list[$category['code']]['level'] = $level;
788
                list($subList, $childrenCount) = self::buildCourseCategoryTree(
789
                    $categories,
790
                    $category['code'],
791
                    $level
792
                );
793
                $list[$category['code']]['number_courses'] += $childrenCount;
794
                foreach ($subList as $item) {
795
                    $list[$item['code']] = $item;
796
                }
797
                $count += $childrenCount;
798
            }
799
        }
800
801
        return [$list, $count];
802
    }
803
804
    /**
805
     * List Code Search Category.
806
     *
807
     * @param string $code
808
     *
809
     * @return array
810
     */
811
    public static function childrenCategories($code)
812
    {
813
        $allCategories = CourseCategory::getAllCategories();
814
        $list = [];
815
        $row = [];
816
817
        if ($code != 'ALL' and $code != 'NONE') {
818
            foreach ($allCategories as $category) {
819
                if ($category['code'] === $code) {
820
                    $list = self::buildCourseCategoryTree($allCategories, $category['code'], 0);
821
                }
822
            }
823
            foreach ($list[0] as $item) {
824
                $row[] = $item['code'];
825
            }
826
        }
827
828
        return $row;
829
    }
830
}
831