Resolve "Veranstaltungsverzeichnis/Einrichtungsverzeichnis findet keine unbegrenzt laufenden Veranstaltungen mehr"

Closes #4752

Merge request studip/studip!3544

3 files changed, 107 insertions, 152 deletions

diff --git a/lib/classes/StudipTreeNodeCourseTrait.php b/lib/classes/StudipTreeNodeCourseTrait.php
new file mode 100644
index 0000000..23ea992
--- /dev/null
+++ b/lib/classes/StudipTreeNodeCourseTrait.php
@@ -0,0 +1,62 @@
+<?php
+trait StudipTreeNodeCourseTrait
+{
+    protected function getCoursesCondition(
+        string $alias,
+        string $semester_id,
+        $sem_class,
+        string $searchterm = '',
+        array $courses = []
+    ): array {
+        $parameters = [];
+        $order_by = [];
+
+        $condition = " JOIN `seminare` s ON (s.`Seminar_id` = {$alias}.`seminar_id`)";
+
+        if ($semester_id !== 'all') {
+            $condition .= " LEFT JOIN `semester_courses` sc ON ({$alias}.`seminar_id` = sc.`course_id`)
+                  LEFT JOIN `semester_data` sd USING (`semester_id`)
+                  WHERE (sc.`semester_id` = :semester OR sc.`semester_id` IS NULL)";
+            $parameters[':semester'] = $semester_id;
+            $order_by[] = 'sd.`beginn`';
+        } else {
+            $condition .= " WHERE 1";
+        }
+
+        if (!$GLOBALS['perm']->have_perm(Config::get()->SEM_VISIBILITY_PERM)) {
+            $condition .= " AND s.`visible` = 1";
+        }
+
+        if ($sem_class) {
+            $condition .= "  AND s.`status` IN (:types)";
+            $parameters['types'] = array_map(
+                function ($type) {
+                    return $type['id'];
+                },
+                array_filter(
+                    SemType::getTypes(),
+                    function ($t) use ($sem_class) {
+                        return $t['class'] === $sem_class;
+                    }
+                )
+            );
+        }
+
+        if ($searchterm) {
+            $condition .= " AND s.`Name` LIKE :searchterm";
+            $parameters['searchterm'] = '%' . trim($searchterm) . '%';
+        }
+
+        if ($courses) {
+            $condition .= " AND {$alias}.`seminar_id` IN (:courses)";
+            $parameters['courses'] = $courses;
+        }
+
+        if (Config::get()->IMPORTANT_SEMNUMBER) {
+            $order_by[] = 's.`VeranstaltungsNummer`';
+        }
+        $order_by[] = 's.`Name`';
+
+        return [$condition, $parameters, $order_by];
+    }
+}
diff --git a/lib/models/RangeTreeNode.php b/lib/models/RangeTreeNode.php
index c1c884f..971ba3d 100644
--- a/lib/models/RangeTreeNode.php
+++ b/lib/models/RangeTreeNode.php
@@ -28,6 +28,7 @@
 class RangeTreeNode extends SimpleORMap implements StudipTreeNode
 {
     use StudipTreeNodeCachableTrait;
+    use StudipTreeNodeCourseTrait;
 
     protected static function configure($config = [])
     {
@@ -120,20 +121,18 @@ class RangeTreeNode extends SimpleORMap implements StudipTreeNode
      */
     public function countCourses($semester_id = '', $semclass = 0, $with_children = false): int
     {
-        $query = "SELECT COUNT(DISTINCT i.`seminar_id`) FROM `seminar_inst` i";
-
-        if ($semester_id !== 'all') {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = i.`seminar_id`)
-                  LEFT JOIN `semester_courses` sc ON (i.`seminar_id` = sc.`course_id`)
-                  WHERE sc.`semester_id` = :semester";
-            $parameters = [
-                'semester' => $semester_id
-            ];
-        } else {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = i.`seminar_id`)";
-            $parameters = [];
+        if (!$this->institute && !$with_children) {
+            return 0;
         }
 
+        [$condition, $parameters] = $this->getCoursesCondition(
+            'i',
+            $semester_id,
+            $semclass
+        );
+
+        $query = "SELECT COUNT(DISTINCT i.`seminar_id`) FROM `seminar_inst` i {$condition}";
+
         if ($with_children) {
             $query .= " AND i.`institut_id` IN (
                     SELECT DISTINCT `studip_object_id` FROM `range_tree` WHERE `item_id` IN (:ids)
@@ -144,24 +143,7 @@ class RangeTreeNode extends SimpleORMap implements StudipTreeNode
             $parameters['id'] = $this->studip_object_id;
         }
 
-        if (!$GLOBALS['perm']->have_perm(Config::get()->SEM_VISIBILITY_PERM)) {
-            $query .= " AND s.`visible` = 1";
-        }
-
-        if ($semclass !== 0) {
-            $query .= "  AND s.`status` IN (:types)";
-            $parameters['types'] = array_map(
-                function ($type) {
-                    return $type['id'];
-                },
-                array_filter(
-                    SemType::getTypes(),
-                    function ($t) use ($semclass) { return $t['class'] === $semclass; }
-                )
-            );
-        }
-
-        return !$this->institute && !$with_children ? 0 : DBManager::get()->fetchColumn($query, $parameters);
+        return DBManager::get()->fetchColumn($query, $parameters);
     }
 
     public function getCourses(
@@ -172,19 +154,15 @@ class RangeTreeNode extends SimpleORMap implements StudipTreeNode
         array $courses = []
     ): array
     {
-        $query = "SELECT DISTINCT s.* FROM `seminar_inst` i";
-
-        if ($semester_id !== 'all') {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = i.`seminar_id`)
-                  LEFT JOIN `semester_courses` sc ON (i.`seminar_id` = sc.`course_id`)
-                  WHERE sc.`semester_id` = :semester";
-            $parameters = [
-                'semester' => $semester_id
-            ];
-        } else {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = i.`seminar_id`)";
-            $parameters = [];
-        }
+        [$condition, $parameters, $order_by] = $this->getCoursesCondition(
+            'i',
+            $semester_id,
+            $semclass,
+            $searchterm,
+            $courses
+        );
+
+        $query = "SELECT DISTINCT s.* FROM `seminar_inst` AS i {$condition}";
 
         if ($with_children) {
             $query .= " AND i.`institut_id` IN (
@@ -196,38 +174,7 @@ class RangeTreeNode extends SimpleORMap implements StudipTreeNode
             $parameters['id'] = $this->studip_object_id;
         }
 
-        if (!$GLOBALS['perm']->have_perm(Config::get()->SEM_VISIBILITY_PERM)) {
-            $query .= " AND s.`visible` = 1";
-        }
-
-        if ($semclass !== 0) {
-            $query .= "  AND s.`status` IN (:types)";
-            $parameters['types'] = array_map(
-                function ($type) {
-                    return $type['id'];
-                },
-                array_filter(
-                    SemType::getTypes(),
-                    function ($t) use ($semclass) { return $t['class'] === $semclass; }
-                )
-            );
-        }
-
-        if ($searchterm) {
-            $query .= " AND s.`Name` LIKE :searchterm";
-            $parameters['searchterm'] = '%' . trim($searchterm) . '%';
-        }
-
-        if ($courses) {
-            $query .= " AND t.`seminar_id` IN (:courses)";
-            $parameters['courses'] = $courses;
-        }
-
-        if (Config::get()->IMPORTANT_SEMNUMBER) {
-            $query .= " ORDER BY s.`start_time`, s.`VeranstaltungsNummer`, s.`Name`";
-        } else {
-            $query .= " ORDER BY s.`start_time`, s.`Name`";
-        }
+        $query .= " ORDER BY " . implode(', ', $order_by);
 
         return DBManager::get()->fetchAll($query, $parameters, 'Course::buildExisting');
     }
diff --git a/lib/models/StudipStudyArea.class.php b/lib/models/StudipStudyArea.class.php
index a559cf5..4143bf6 100644
--- a/lib/models/StudipStudyArea.class.php
+++ b/lib/models/StudipStudyArea.class.php
@@ -31,6 +31,7 @@
 class StudipStudyArea extends SimpleORMap implements StudipTreeNode
 {
     use StudipTreeNodeCachableTrait;
+    use StudipTreeNodeCourseTrait;
 
     /**
      * This constant represents the key of the root area.
@@ -334,7 +335,7 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
      * Get an associative array of all study areas of a course. The array
      * contains StudipStudyArea instances
      *
-     * @param  id         the course's ID
+     * @param  string $id the course's ID
      *
      * @return SimpleCollection      a SimpleORMapCollection of that course's study areas
      */
@@ -483,22 +484,19 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
         $semester_id = 'all',
         $semclass = 0,
         $with_children = false
-    ) :int
-    {
-        $query = "SELECT COUNT(DISTINCT t.`seminar_id`) FROM `seminar_sem_tree` t";
-
-        if ($semester_id !== 'all') {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = t.`seminar_id`)
-                  LEFT JOIN `semester_courses` sc ON (t.`seminar_id` = sc.`course_id`)
-                  WHERE sc.`semester_id` = :semester";
-            $parameters = [
-                'semester' => $semester_id
-            ];
-        } else {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = t.`seminar_id`)";
-            $parameters = [];
+    ): int {
+        if ($this->id === 'root' && !$with_children) {
+            return 0;
         }
 
+        [$condition, $parameters] = $this->getCoursesCondition(
+            't',
+            $semester_id,
+            $semclass
+        );
+
+        $query = "SELECT COUNT(DISTINCT t.`seminar_id`) FROM `seminar_sem_tree` t {$condition}";
+
         if ($with_children) {
             $query .= " AND t.`sem_tree_id` IN (:ids)";
             $parameters['ids'] = array_merge([$this->id], $this->getDescendantIds());
@@ -507,24 +505,7 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
             $parameters['id'] = $this->id;
         }
 
-        if (!$GLOBALS['perm']->have_perm(Config::get()->SEM_VISIBILITY_PERM)) {
-            $query .= " AND s.`visible` = 1";
-        }
-
-        if ($semclass !== 0) {
-            $query .= "  AND s.`status` IN (:types)";
-            $parameters['types'] = array_map(
-                function ($type) {
-                    return $type['id'];
-                },
-                array_filter(
-                    SemType::getTypes(),
-                    function ($t) use ($semclass) { return $t['class'] === $semclass; }
-                )
-            );
-        }
-
-        return $this->id === 'root' && !$with_children ? 0 : DBManager::get()->fetchColumn($query, $parameters);
+        return DBManager::get()->fetchColumn($query, $parameters);
     }
 
     public function getCourses(
@@ -535,19 +516,15 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
         array $courses = []
     ): array
     {
-        $query = "SELECT DISTINCT s.* FROM `seminar_sem_tree` t";
+        [$condition, $parameters, $order_by] = $this->getCoursesCondition(
+            't',
+            $semester_id,
+            $semclass,
+            $searchterm,
+            $courses
+        );
 
-        if ($semester_id !== 'all') {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = t.`seminar_id`)
-                  LEFT JOIN `semester_courses` sc ON (t.`seminar_id` = sc.`course_id`)
-                  WHERE sc.`semester_id` = :semester";
-            $parameters = [
-                'semester' => $semester_id
-            ];
-        } else {
-            $query .= " JOIN `seminare` s ON (s.`Seminar_id` = t.`seminar_id`)";
-            $parameters = [];
-        }
+        $query = "SELECT DISTINCT s.* FROM `seminar_sem_tree` AS t {$condition}";
 
         if ($with_children) {
             $query .= " AND t.`sem_tree_id` IN (:ids)";
@@ -557,38 +534,7 @@ class StudipStudyArea extends SimpleORMap implements StudipTreeNode
             $parameters['id'] = $this->id;
         }
 
-        if (!$GLOBALS['perm']->have_perm(Config::get()->SEM_VISIBILITY_PERM)) {
-            $query .= " AND s.`visible` = 1";
-        }
-
-        if ($semclass !== 0) {
-            $query .= "  AND s.`status` IN (:types)";
-            $parameters['types'] = array_map(
-                function ($type) {
-                    return $type['id'];
-                },
-                array_filter(
-                    SemType::getTypes(),
-                    function ($t) use ($semclass) { return $t['class'] === $semclass; }
-                )
-            );
-        }
-
-        if ($searchterm) {
-            $query .= " AND s.`Name` LIKE :searchterm";
-            $parameters['searchterm'] = '%' . trim($searchterm) . '%';
-        }
-
-        if ($courses) {
-            $query .= " AND t.`seminar_id` IN (:courses)";
-            $parameters['courses'] = $courses;
-        }
-
-        if (Config::get()->IMPORTANT_SEMNUMBER) {
-            $query .= " ORDER BY s.`start_time`, s.`VeranstaltungsNummer`, s.`Name`";
-        } else {
-            $query .= " ORDER BY s.`start_time`, s.`Name`";
-        }
+        $query .= " ORDER BY " . implode(', ', $order_by);
 
         return DBManager::get()->fetchAll($query, $parameters, 'Course::buildExisting');
     }