Merge branch 'main' into issue-3911issue-3911

1 files changed, 339 insertions, 0 deletions

diff --git a/lib/models/MvvOverlappingSelection.php b/lib/models/MvvOverlappingSelection.php
new file mode 100644
index 0000000..b3fcc15
--- /dev/null
+++ b/lib/models/MvvOverlappingSelection.php
@@ -0,0 +1,339 @@
+<?php
+/**
+ * MvvOverlappingSelection.php - model class for table mvv_ovl_selections
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License as
+ * published by the Free Software Foundation; either version 2 of
+ * the License, or (at your option) any later version.
+ *
+ * @author      Peter Thienel <thienel@data-quest.de>
+ * @copyright   2018 Stud.IP Core-Group
+ * @license     http://www.gnu.org/licenses/gpl-2.0.html GPL version 2
+ * @category    Stud.IP
+ * @since       4.4
+ *
+ * @property int $id database column
+ * @property string $selection_id database column
+ * @property string $semester_id database column
+ * @property string $base_version_id database column
+ * @property string $comp_version_id database column
+ * @property string $fachsems database column
+ * @property string $semtypes database column
+ * @property string $user_id database column
+ * @property int $show_excluded database column
+ * @property int $mkdate database column
+ * @property SimpleORMapCollection|MvvOverlappingConflict[] $conflicts has_many MvvOverlappingConflict
+ * @property SimpleORMapCollection|MvvOverlappingExclude[] $excludes has_many MvvOverlappingExclude
+ * @property Semester $semester belongs_to Semester
+ * @property StgteilVersion $base_version belongs_to StgteilVersion
+ * @property StgteilVersion $comp_version belongs_to StgteilVersion
+ * @property User $user belongs_to User
+ */
+
+class MvvOverlappingSelection extends SimpleORMap
+{
+    /**
+     * Configures the model.
+     *
+     * @param array  $config Configuration
+     */
+    protected static function configure($config = array())
+    {
+        $config['db_table'] = 'mvv_ovl_selections';
+        $config['belongs_to']['semester'] = [
+            'class_name'  => Semester::class,
+            'foreign_key' => 'semester_id'
+        ];
+        $config['belongs_to']['base_version'] = [
+            'class_name'  => StgteilVersion::class,
+            'foreign_key' => 'base_version_id'
+        ];
+        $config['belongs_to']['comp_version'] = [
+            'class_name'  => StgteilVersion::class,
+            'foreign_key' => 'comp_version_id'
+        ];
+        $config['has_many']['conflicts'] = [
+            'class_name'        => MvvOverlappingConflict::class,
+            'foreign_key'       => 'id',
+            'assoc_foreign_key' => 'selection_id',
+            'on_delete'         => 'delete',
+            'on_store'          => 'store'
+        ];
+        $config['belongs_to']['user'] =  [
+            'class_name'  => User::class,
+            'foreign_key' => 'user_id'
+        ];
+        $config['has_many']['excludes'] = [
+            'class_name'        => MvvOverlappingExclude::class,
+            'foreign_key'       => 'selection_id',
+            'assoc_foreign_key' => 'selection_id',
+            'on_delete'         => 'delete',
+            'on_store'          => 'store'
+        ];
+        parent::configure($config);
+    }
+
+    /**
+     * Creates a selection id and stores the selection.
+     *
+     * @throws UnexpectedValueException if there are forbidden NULL values
+     * @return number|boolean
+     */
+    public function store()
+    {
+        if ($this->isNew() && $this->selection_id == '') {
+            $this->selection_id = self::createSelectionId(
+                $this->base_version,
+                $this->comp_version,
+                $this->fachsems,
+                $this->semtypes
+            );
+        }
+        return parent::store();
+    }
+
+    /**
+     * Sets the given Fachsemester. Expects an array or a comma
+     * separated list of Fachsemester.
+     *
+     * @param array|string $semtypes
+     */
+    public function setFachsemester($fachsems)
+    {
+        if (is_array($fachsems)) {
+            sort($fachsems, SORT_NUMERIC);
+            $fachsems = implode(',', $fachsems);
+        }
+        $this->fachsems = $fachsems;
+    }
+
+    /**
+     * Sets the given course types (semtypes). Expects an array or a comma
+     * separated list of course types.
+     *
+     * @param array|string $semtypes
+     */
+    public function setCoursetypes($semtypes)
+    {
+        if (is_array($semtypes)) {
+            sort($semtypes, SORT_NUMERIC);
+            $semtypes = implode(',', $semtypes);
+        }
+        $this->semtypes = $semtypes;
+    }
+
+    /**
+     * Store this selection with its all conflicts.
+     *
+     * @throws UnexpectedValueException if there are forbidden NULL values
+     * @return number|boolean
+     */
+    public function storeConflicts()
+    {
+
+        $query = "
+            SELECT DISTINCT `cbase`.`metadate_id` AS `cbase_metadate_id`,
+                `cbase`.`seminar_id` AS `cbase_seminar_id`,
+                `sembase`.`abschnitt_id` AS `sembase_abschnitt_id`,
+                `sembase`.`modulteil_id` AS `sembase_modulteil_id`,
+                `ccomp`.`metadate_id` AS `ccomp_metadate_id`,
+                `ccomp`.`seminar_id` AS `ccomp_seminar_id`,
+                `semcomp`.`abschnitt_id` AS `semcomp_abschnitt_id`,
+                `semcomp`.`modulteil_id` AS `semcomp_modulteil_id`
+            FROM `seminar_cycle_dates` AS `cbase`
+                INNER JOIN (
+                    SELECT `mvv_lvgruppe_seminar`.`seminar_id`,
+                        `mvv_stgteilabschnitt_modul`.`abschnitt_id`,
+                        `mvv_modulteil`.`modulteil_id`
+                    FROM `mvv_stgteilabschnitt`
+                        INNER JOIN `mvv_stgteilabschnitt_modul` USING (`abschnitt_id`)
+                        INNER JOIN `mvv_modul` USING (`modul_id`)
+                        INNER JOIN `mvv_modulteil` USING (`modul_id`)
+                        INNER JOIN `mvv_lvgruppe_modulteil` USING (`modulteil_id`)
+                        INNER JOIN `mvv_lvgruppe_seminar` USING (`lvgruppe_id`)
+                        INNER JOIN `seminare` USING (`seminar_id`)
+                        INNER JOIN `mvv_modulteil_stgteilabschnitt`
+                            ON (`mvv_stgteilabschnitt_modul`.`abschnitt_id` =
+                                    `mvv_modulteil_stgteilabschnitt`.`abschnitt_id`
+                                AND `mvv_modulteil`.`modulteil_id` =
+                                    `mvv_modulteil_stgteilabschnitt`.`modulteil_id`)
+                        LEFT JOIN `semester_data` AS `start_sem`
+                            ON (`mvv_modul`.`start` = `start_sem`.`semester_id`)
+                        LEFT JOIN `semester_data` AS `end_sem`
+                            ON (`mvv_modul`.`end` = `end_sem`.`semester_id`)
+                        LEFT JOIN `semester_courses` ON (`seminare`.`Seminar_id` = `semester_courses`.`course_id`)
+                    WHERE `mvv_stgteilabschnitt`.`version_id` = :base_version
+                        AND `mvv_modulteil_stgteilabschnitt`.`fachsemester` IN (:fachsem)
+                        AND ((`start_sem`.`beginn` < :sem_end OR ISNULL(`start_sem`.`beginn`))
+                            AND (`end_sem`.`ende` > :sem_start OR ISNULL(`end_sem`.`ende`)))
+                        AND `seminare`.`status` IN (:typ)
+                        AND `seminare`.`start_time` <= :sem_end
+                        AND (`semester_courses`.`semester_id` IS NULL OR `semester_courses`.`semester_id` = :semester_id)
+                ) AS `sembase` ON (`sembase`.`seminar_id` = `cbase`.`seminar_id`)
+                INNER JOIN `seminar_cycle_dates` AS `ccomp`
+                    ON (`cbase`.`seminar_id` != `ccomp`.`seminar_id`
+                        AND `cbase`.`weekday` = `ccomp`.`weekday`
+                        AND `cbase`.`start_time` < `ccomp`.`end_time`
+                        AND `cbase`.`end_time` > `ccomp`.`start_time`
+                        AND `cbase`.`metadate_id` = (
+                            SELECT DISTINCT `metadate_id`
+                                FROM `termine`
+                                WHERE `termine`.`metadate_id` = `cbase`.`metadate_id`
+                                LIMIT 1)
+                        AND `ccomp`.`metadate_id` = (
+                            SELECT DISTINCT `metadate_id`
+                                FROM `termine`
+                                WHERE `termine`.`metadate_id` = `ccomp`.`metadate_id`
+                                LIMIT 1)
+                    )
+                INNER JOIN (
+                    SELECT `mvv_lvgruppe_seminar`.`seminar_id`,
+                        `mvv_stgteilabschnitt_modul`.`abschnitt_id`,
+                        `mvv_modulteil`.`modulteil_id`
+                    FROM `mvv_stgteilabschnitt`
+                        INNER JOIN `mvv_stgteilabschnitt_modul` USING (`abschnitt_id`)
+                        INNER JOIN `mvv_modul` USING (`modul_id`)
+                        INNER JOIN `mvv_modulteil` USING (`modul_id`)
+                        INNER JOIN `mvv_lvgruppe_modulteil` USING (`modulteil_id`)
+                        INNER JOIN `mvv_lvgruppe_seminar` USING (`lvgruppe_id`)
+                        INNER JOIN `seminare` USING (`seminar_id`)
+                        INNER JOIN `mvv_modulteil_stgteilabschnitt`
+                            ON (`mvv_stgteilabschnitt_modul`.`abschnitt_id` =
+                                    `mvv_modulteil_stgteilabschnitt`.`abschnitt_id`
+                                AND `mvv_modulteil`.`modulteil_id` =
+                                    `mvv_modulteil_stgteilabschnitt`.`modulteil_id`)
+                        LEFT JOIN `semester_data` AS `start_sem`
+                            ON (`mvv_modul`.`start` = `start_sem`.`semester_id`)
+                        LEFT JOIN `semester_data` AS `end_sem`
+                            ON (`mvv_modul`.`end` = `end_sem`.`semester_id`)
+                        LEFT JOIN `semester_courses` ON (`seminare`.`Seminar_id` = `semester_courses`.`course_id`)
+                    WHERE `mvv_stgteilabschnitt`.`version_id` = :comp_version
+                        AND `mvv_modulteil_stgteilabschnitt`.`fachsemester` IN (:fachsem)
+                        AND ((`start_sem`.`beginn` < :sem_end OR ISNULL(`start_sem`.`beginn`))
+                            AND (`end_sem`.`ende` > :sem_start OR ISNULL(`end_sem`.`ende`)))
+                        AND `seminare`.`status` IN (:typ)
+                        AND `seminare`.`start_time` <= :sem_end
+                        AND (`semester_courses`.`semester_id` IS NULL OR `semester_courses`.`semester_id` = :semester_id)
+                ) AS `semcomp` ON (`semcomp`.`seminar_id` = `ccomp`.`seminar_id`)
+                INNER JOIN `mvv_modulteil_stgteilabschnitt` AS `mms1`
+                    ON (`mms1`.`abschnitt_id` = `semcomp`.`abschnitt_id` AND `mms1`.`modulteil_id` = `semcomp`.`modulteil_id`)
+                    WHERE `mms1`.`fachsemester` IN (
+                        SELECT `fachsemester`
+                        FROM `mvv_modulteil_stgteilabschnitt` AS `mms2`
+                        WHERE `mms2`.`abschnitt_id` = `sembase`.`abschnitt_id`
+                            AND `mms2`.`modulteil_id` = `sembase`.`modulteil_id`)
+            ORDER BY `cbase_seminar_id`";
+
+        // if no filter is set use all types and fachsems
+        $fachsems = $this->fachsems ? $this->fachsems : implode(',', range(1, 6));
+        $semtypes = $this->semtypes ? $this->semtypes : implode(',', array_keys(SemType::getTypes()));
+
+        $db = DBManager::get();
+        $conflicts = $db->fetchAll($query, [
+            ':base_version' => $this->base_version_id,
+            ':comp_version' => $this->comp_version_id,
+            ':fachsem'      => explode(',', $fachsems),
+            ':typ'          => explode(',', $semtypes),
+            ':sem_start'    => $this->semester->beginn,
+            ':sem_end'      => $this->semester->ende,
+            ':semester_id'  => $this->semester->id
+        ]);
+
+        $conlicts = [];
+        foreach ($conflicts as $conflict) {
+            $ovl_conflict = new MvvOverlappingConflict();
+            $ovl_conflict->selection_id = $this->id;
+            $ovl_conflict->base_abschnitt_id = $conflict['sembase_abschnitt_id'];
+            $ovl_conflict->base_modulteil_id = $conflict['sembase_modulteil_id'];
+            $ovl_conflict->base_course_id    = $conflict['cbase_seminar_id'];
+            $ovl_conflict->base_metadate_id  = $conflict['cbase_metadate_id'];
+            $ovl_conflict->comp_abschnitt_id = $conflict['semcomp_abschnitt_id'];
+            $ovl_conflict->comp_modulteil_id = $conflict['semcomp_modulteil_id'];
+            $ovl_conflict->comp_course_id    = $conflict['ccomp_seminar_id'];
+            $ovl_conflict->comp_metadate_id  = $conflict['ccomp_metadate_id'];
+            $this->conflicts[] = $ovl_conflict;
+        }
+        return $this->store();
+    }
+
+    /**
+     * Returns all conflicts of all selections with the given selection id.
+     *
+     * @param string $selection_id The selection id.
+     * @param boolean $only_visible Returns only visible conflicts.
+     * @return SimpleORMapCollection All conflicts of appropriate selections.
+     */
+    public static function getConflictsBySelection($selection_id, $only_visible = false)
+    {
+        $excluded_courses = [];
+        $visible_sql = '';
+        if ($only_visible) {
+            $excluded_courses = SimpleORMapCollection::createFromArray(
+                    MvvOverlappingExclude::findBySelection_id($selection_id))->pluck('course_id');
+            if ($excluded_courses) {
+                $visible_sql = 'AND `mvv_ovl_conflicts`.`comp_course_id` NOT IN (:course_ids)';
+            }
+        }
+        return SimpleORMapCollection::createFromArray(
+            MvvOverlappingConflict::findBySql('LEFT JOIN `mvv_ovl_selections`
+                ON (`mvv_ovl_conflicts`.`selection_id` = `mvv_ovl_selections`.`id`)
+                WHERE `mvv_ovl_selections`.`selection_id` = :selection_id ' . $visible_sql, [
+                ':selection_id' => $selection_id,
+                ':course_ids'    => $excluded_courses
+            ])
+        );
+    }
+
+    /**
+     * Returns a md5 hash over all given parameters.
+     *
+     * @param string $base_version The id of the base version.
+     * @param string $comp_versions The id of the compared version.
+     * @param array|string $fachsems An array or a string with comma separated fachsem numbers.
+     * @param array|string $semtypes An array or a string with comma separated course types.
+     * @param string|null $user_id User id that created the selection (defaults to current user)
+     * @return string The md5 id.
+     */
+    public static function createSelectionId($base_version, $comp_versions, $fachsems, $semtypes, string $user_id = null)
+    {
+        if (is_array($fachsems)) {
+            sort($fachsems, SORT_NUMERIC);
+            $fachsems = implode(',', $fachsems);
+        }
+        if (is_array($semtypes)) {
+            sort($semtypes, SORT_NUMERIC);
+            $semtypes = implode(',', $semtypes);
+        }
+        if (is_array($comp_versions)) {
+            $comp_version_ids = [];
+            foreach ($comp_versions as $comp_version) {
+                $comp_version_ids[] = $comp_version->id;
+            }
+            sort($comp_version_ids);
+            $comp_versions = implode(',', $comp_version_ids);
+        } else {
+            $comp_versions = $comp_versions->id;
+        }
+        return md5(implode('_', [
+            $base_version->id,
+            $comp_versions,
+            trim($fachsems) ? $fachsems : 'x',
+            trim($semtypes) ? $semtypes : 'x',
+            $user_id ?? $GLOBALS['user']->id,
+        ]));
+    }
+
+    /**
+     * Returns all excluded (hidden) conflicts of this selection.
+     *
+     * @return SimpleORMapCollection The excluded (hidden) conflicts.
+     */
+    public function getExcludedConflicts()
+    {
+        return $this->conflicts->findBy(
+            'comp_course_id',
+            $this->excludes->pluck('course_id')
+        );
+    }
+}