From 59ad17e3ff68384a719daa96bb9f5f3106fed061 Mon Sep 17 00:00:00 2001 From: Peter Thienel Date: Fri, 19 Sep 2025 14:25:30 +0200 Subject: Abschluss, -Kategorie, Fach, Studiengang --- app/controllers/fachabschluss/abschluesse.php | 121 ++++++++- app/controllers/studiengaenge/abschluesse.php | 38 +-- app/controllers/studiengaenge/kategorien.php | 42 ++-- app/controllers/studiengaenge/studiengaenge.php | 278 ++++++++++++++++++++- app/controllers/studiengaenge/versionen.php | 19 +- app/views/fachabschluss/abschluesse/abschluss.php | 37 ++- app/views/fachabschluss/abschluesse/index.php | 24 +- app/views/studiengaenge/abschluesse/index.php | 12 +- .../studiengaenge/studiengaenge/studiengaenge.php | 2 +- lib/models/Abschluss.php | 88 +++---- lib/models/AbschlussKategorie.php | 10 +- lib/models/Fach.php | 6 + lib/models/ModuleManagementModel.php | 6 +- lib/models/Studiengang.php | 10 +- 14 files changed, 552 insertions(+), 141 deletions(-) diff --git a/app/controllers/fachabschluss/abschluesse.php b/app/controllers/fachabschluss/abschluesse.php index 0ef58f2..1793a6a 100644 --- a/app/controllers/fachabschluss/abschluesse.php +++ b/app/controllers/fachabschluss/abschluesse.php @@ -26,7 +26,7 @@ class Fachabschluss_AbschluesseController extends MVVController $this->sortby = $this->sortby ?: 'name'; $this->order = $this->order ?: 'ASC'; //get data - $this->abschluesse = Abschluss::getAllEnriched( + $this->abschluesse = self::getAllEnriched( $this->sortby, $this->order, self::$items_per_page, @@ -36,7 +36,7 @@ class Fachabschluss_AbschluesseController extends MVVController if (count($this->abschluesse) === 0) { PageLayout::postInfo(_('Es wurden noch keine Abschlüsse angelegt.')); } - $this->count = Abschluss::getCount($filter); + $this->count = self::getCount($filter); $this->setSidebar(); @@ -68,7 +68,8 @@ class Fachabschluss_AbschluesseController extends MVVController $this->abschluss_kategorien = AbschlussKategorie::getAll(); if (count($this->abschluss_kategorien) === 0) { PageLayout::postError( - _('Es wurden noch keine Abschluss-Kategorien angelegt. Bevor Sie fortfahren, legen Sie bitte hier zunächst eine Abschluss-Kategorie an!') + _('Es wurden noch keine Abschluss-Kategorien angelegt. ' + . 'Bevor Sie fortfahren, legen Sie bitte hier zunächst eine Abschluss-Kategorie an!') ); $this->redirect('fachabschluss/kategorien/kategorie'); } @@ -85,15 +86,20 @@ class Fachabschluss_AbschluesseController extends MVVController } if (Request::submitted('store')) { CSRFProtection::verifyUnsafeRequest(); - $stored = false; + $store = true; $this->abschluss->name = Request::i18n('name')->trim(); $this->abschluss->name_kurz = Request::i18n('name_kurz')->trim(); $this->abschluss->beschreibung = Request::i18n('beschreibung')->trim(); - $this->abschluss->assignKategorie(Request::option('kategorie_id')); - try { + if (!$this->assignKategorie($this->abschluss, Request::option('kategorie_id'))) { + PageLayout::postError(_('Es muss eine Abschluss-Kategorie ausgewählt werden.')); + $store = false; + } + if (Abschluss::findByName($this->abschluss->name)) { + sprintf(_('Es existiert bereits ein Abschluss mit dem Namen "%s"!'), $this->name); + $store = false; + } + if ($store) { $stored = $this->abschluss->store(true); - } catch (InvalidValuesException $e) { - PageLayout::postError(htmlReady($e->getMessage())); } if ($stored !== false) { $this->sessSet('sortby', 'chdate'); @@ -106,7 +112,7 @@ class Fachabschluss_AbschluesseController extends MVVController } else { PageLayout::postInfo(_('Es wurden keine Änderungen vorgenommen.')); } - $this->redirect($this->action_url('index')); + $this->redirect($this->indexURL()); return; } } @@ -117,7 +123,7 @@ class Fachabschluss_AbschluesseController extends MVVController $action_widget = $sidebar->getWidget('actions'); $action_widget->addLink( _('Log-Einträge dieses Abschlusses'), - $this->url_for('shared/log_event/show/Abschluss/' . $this->abschluss->getId()), + $this->url_for('shared/log_event/show/Abschluss/' . $this->abschluss->id), Icon::create('log') )->asDialog(); } @@ -151,7 +157,7 @@ class Fachabschluss_AbschluesseController extends MVVController } } - $this->redirect($this->action_url('index')); + $this->redirect($this->indexURL()); } public function fach_action($fach_id = null) @@ -179,11 +185,100 @@ class Fachabschluss_AbschluesseController extends MVVController $widget = new ActionsWidget(); $widget->addLink( _('Neuen Abschluss anlegen'), - $this->action_url('abschluss'), - Icon::create('add') + $this->abschlussURL(), + Icon::create('add'), + ['data-dialog' => ''] ); $sidebar->addWidget($widget); } $this->sidebar_rendered = true; } + + /** + * Returns all or a specified (by row count and offset) number of + * Abschluesse sorted and filtered by given parameters and enriched with + * some additional fields. This function is mainly used in the list view. + * + * @param string $sortby Field name to order by. + * @param string $order ASC or DESC direction of order. + * @param int $row_count The max number of objects to return. + * @param int $offset The first object to return in a result set. + * @param array $filter Key-value pairs of filed names and values + * to filter the result set. + * @return SimpleCollection A SimpleCollection of Abschluss objects. + */ + private static function getAllEnriched( + $order_field = 'name', + $order = 'ASC', + $row_count = null, + $offset = null, + $filter = null + ): SimpleCollection + { + $order_by = Abschluss::createSortStatement($order_field, $order, 'chdate', + ['kategorie_name', 'count_faecher', 'count_studiengaenge']); + return Abschluss::getEnrichedByQuery(' + SELECT abschluss.*, mvv_abschl_kategorie.name AS `kategorie_name`, + COUNT(DISTINCT mvv_stgteil.fach_id) AS `count_faecher`, + COUNT(DISTINCT mvv_studiengang.studiengang_id) AS `count_studiengaenge` + FROM abschluss + LEFT JOIN mvv_abschl_zuord USING (abschluss_id) + LEFT JOIN mvv_abschl_kategorie USING (kategorie_id) + LEFT JOIN mvv_studiengang USING (abschluss_id) + LEFT JOIN mvv_stg_stgteil USING (studiengang_id) + LEFT JOIN mvv_stgteil USING (stgteil_id) + LEFT JOIN mvv_fach_inst USING (fach_id) + ' . Abschluss::getFilterSql($filter, true) . ' + GROUP BY abschluss_id + ORDER BY ' . $order_by, + [], $row_count, $offset); + } + + /** + * Returns the number of Abschlüsse optional filtered by $filter. + * + * @param array $filter Key-value pairs of filed names and values + * to filter the result set. + * @return int The number of Abschluesse + */ + public static function getCount($filter = null) + { + $query = ' + SELECT COUNT(DISTINCT(abschluss_id)) + FROM abschluss + LEFT JOIN mvv_abschl_zuord USING (abschluss_id) + LEFT JOIN mvv_abschl_kategorie USING (kategorie_id) + LEFT JOIN mvv_studiengang USING (abschluss_id) + LEFT JOIN mvv_stg_stgteil USING (studiengang_id) + LEFT JOIN mvv_stgteil USING (stgteil_id) + LEFT JOIN mvv_fach_inst USING (fach_id) + ' . Abschluss::getFilterSql($filter, true); + $db = DBManager::get()->prepare($query); + $db->execute(); + return $db->fetchColumn(0); + } + + /** + * Assigns an Abschluss-Kategorie to this Abschluss. + * + * @param string $kategorie_id The id of the Abschluss-Kategorie + * @param int Position of this Abschluss in the given Kategorie. + * @return object|null The assigned Kategorie. Null if assigned + * Abschluss-Kategorie is unknown + * TODO Position? + */ + private static function assignKategorie($abschluss, $kategorie_id, $position = null) + { + $kategorie = AbschlussKategorie::find($kategorie_id); + if ($kategorie) { + $category_assignment = new AbschlussZuord($abschluss->id); + $category_assignment->kategorie_id = $kategorie->id; + if (!is_null($position)) { + $category_assignment->position = $position; + } + $abschluss->category_assignment = $category_assignment; + } + return $kategorie; + } + } diff --git a/app/controllers/studiengaenge/abschluesse.php b/app/controllers/studiengaenge/abschluesse.php index 60bc091..ee0b48e 100644 --- a/app/controllers/studiengaenge/abschluesse.php +++ b/app/controllers/studiengaenge/abschluesse.php @@ -8,7 +8,7 @@ require_once __DIR__ . '/studiengaenge.php'; class Studiengaenge_AbschluesseController extends Studiengaenge_StudiengaengeController { - + /** * Liste der Studiengänge gruppiert nach Fachbereiche (Fachbereich ist * die verantwortliche Einrichtung des zugeordneten Faches, nicht die des @@ -17,21 +17,29 @@ class Studiengaenge_AbschluesseController extends Studiengaenge_StudiengaengeCon public function index_action($studiengang_id = null) { PageLayout::setTitle(_('Studiengänge gruppiert nach Abschlüssen')); - + // Nur Abschlüsse von Fachbereichen an denen der User eine Rolle hat - $perm_institutes = MvvPerm::getOwnInstitutes(); - + $own_institutes = MvvPerm::getOwnInstitutes(); + $this->initPageParams('abschluesse'); $this->sortby = $this->sortby ?: 'name'; $this->order = $this->order ?: 'ASC'; - $this->abschluesse = Abschluss::getAllEnriched( - $this->sortby, - $this->order, - null, - null, - ['mvv_fach_inst.institut_id' => $perm_institutes] + + $this->abschluesse = Abschluss::findBySQL( + 'JOIN `mvv_studiengang` USING (abschluss_id) + JOIN `mvv_stg_stgteil` USING (studiengang_id) + JOIN `mvv_stgteil` USING (stgteil_id) + JOIN `mvv_fach_inst` USING (fach_id) + WHERE `mvv_fach_inst`.`institut_id` IN (?) OR ? + ORDER BY ? ?', + [ + $own_institutes, + count($own_institutes) === 0, + $this->sortby, + $this->order + ] ); - + if ($studiengang_id) { $studiengang = Studiengang::find($studiengang_id); $this->details_action($studiengang->abschluss_id, $studiengang->id); @@ -39,10 +47,10 @@ class Studiengaenge_AbschluesseController extends Studiengaenge_StudiengaengeCon $this->setSidebar(); } - + /** * shows the studiengaenge of a fachbereich - * + * * @param string $fachbereich_id the id of the fachbereich */ public function details_action($abschluss_id, $studiengang_id = null, $stgteil_bez_id = null) @@ -67,12 +75,12 @@ class Studiengaenge_AbschluesseController extends Studiengaenge_StudiengaengeCon } else { $this->studiengaenge = Studiengang::findByAbschluss_id($this->abschluss_id); } - + if ($studiengang_id) { $this->studiengang_id = $studiengang_id; $this->set_studiengangteile($studiengang_id, $stgteil_bez_id); } - + if (Request::isXhr()) { if ($this->studiengang) { if ($this->studiengang->typ == 'einfach') { diff --git a/app/controllers/studiengaenge/kategorien.php b/app/controllers/studiengaenge/kategorien.php index 8f6a98d..fe01721 100644 --- a/app/controllers/studiengaenge/kategorien.php +++ b/app/controllers/studiengaenge/kategorien.php @@ -17,21 +17,25 @@ class Studiengaenge_KategorienController extends Studiengaenge_StudiengaengeCont public function index_action($studiengang_id = null) { PageLayout::setTitle(_('Studiengänge gruppiert nach Abschluss-Kategorien')); - + // Nur Abschlüsse von Fachbereichen an denen der User eine Rolle hat $perm_institutes = MvvPerm::getOwnInstitutes(); - + $this->initPageParams('kategorien'); $this->sortby = $this->sortby ?: 'name'; $this->order = $this->order ?: 'ASC'; - $abschluss_ids = Abschluss::getAllEnriched( - null, - null, - null, - null, - ['mvv_fach_inst.institut_id' => $perm_institutes] - )->pluck('abschluss_id'); - + $stmt = DBManager::get()->prepare( + 'SELECT `abschluss_id` + FROM `mvv_abschl_zuord` + JOIN `mvv_abschl_kategorie` USING (`kategorie_id`) + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + JOIN `mvv_fach_inst` USING (`fach_id`) + WHERE `mvv_fach_inst`.`institut_id` IN (?)' + ); + $stmt->execute([$perm_institutes]); + $abschluss_ids = $stmt->fetchAll(PDO::FETCH_UNIQUE); $this->kategorien = AbschlussKategorie::getAllEnriched( $this->sortby, $this->order, @@ -42,44 +46,44 @@ class Studiengaenge_KategorienController extends Studiengaenge_StudiengaengeCont 'mvv_studiengang.institut_id' => $perm_institutes ] ); - + if ($studiengang_id) { $studiengang = Studiengang::find($studiengang_id); $this->details_action($studiengang->abschluss->kategorie_id, $studiengang->id); } - + $this->setSidebar(); } - + /** * shows the studiengaenge of a fachbereich - * + * * @param string $fachbereich_id the id of the fachbereich */ public function details_action($kategorie_id, $studiengang_id = null, $stgteil_bez_id = null) { $perm_institutes = MvvPerm::getOwnInstitutes(); $this->kategorie_id = $kategorie_id; - + $filter = [ 'mvv_abschl_kategorie.kategorie_id' => $this->kategorie_id, 'mvv_studiengang.institut_id' => $perm_institutes ]; $this->studiengaenge = Studiengang::getAllEnriched('name', 'ASC', $filter); - + if ($studiengang_id) { $studiengang = Studiengang::find($studiengang_id); $this->studiengang_id = $studiengang->id; - + if (count($perm_institutes)) { if (!in_array($studiengang->institut_id, $perm_institutes)) { throw new Trails\Exception(403); } } - + $this->set_studiengangteile($studiengang_id, $stgteil_bez_id); } - + if (Request::isXhr()) { if ($this->studiengang) { if ($this->studiengang->typ == 'einfach') { diff --git a/app/controllers/studiengaenge/studiengaenge.php b/app/controllers/studiengaenge/studiengaenge.php index 809d1ab..c242542 100644 --- a/app/controllers/studiengaenge/studiengaenge.php +++ b/app/controllers/studiengaenge/studiengaenge.php @@ -952,10 +952,10 @@ class Studiengaenge_StudiengaengeController extends MVVController if ($semester_id !== 'all') { $semester = Semester::find($semester_id); $this->filter['start_sem.beginn'] = $semester->beginn; - $this->filter['end_sem.ende'] = $semester->beginn; + $this->filter['end_sem.ende'] = $semester->ende; } else { - $this->filter['start_sem.beginn'] = -1; - $this->filter['end_sem.ende'] = -1; + $this->filter['start_sem.beginn'] = 0; + $this->filter['end_sem.ende'] = PHP_INT_MAX; } // Status if (mb_strlen(Request::option('status_filter'))) { @@ -1041,10 +1041,10 @@ class Studiengaenge_StudiengaengeController extends MVVController private function sidebar_filter() { $template_factory = $this->get_template_factory(); - $studiengang_ids = Studiengang::findByFilter($this->filter); + $this->studiengang_ids = $this->findByFilter($this->filter); if (!empty($this->search_result['Studiengang'])) { - $studiengang_ids = array_intersect($studiengang_ids, $this->search_result['Studiengang']); + $this->studiengang_ids = array_intersect($studiengang_ids, $this->search_result['Studiengang']); } // Semesters @@ -1052,6 +1052,35 @@ class Studiengaenge_StudiengaengeController extends MVVController $semesters = $semesters->orderBy('beginn desc'); $selected_semester = $semesters->findOneBy('beginn', $this->filter['start_sem.beginn']); + + $sidebar = Sidebar::get(); + + $filter = new OptionsWidget(_('Filter')); + $filter->setId('studycourse-filter-widget'); + + /* + Now draw the configurable elements according + to the values inside the visibleElements array. + */ + /* + if (!empty($visibleElements['search'])) { + $this->setSearchWiget(); + } + */ + + $filter->addElement($this->getSemesterSelector()); + $filter->addElement($this->getStatusSelector()); + $filter->addElement($this->getSubjectSelector()); + $filter->addElement($this->getDegreeSelector()); + $filter->addElement($this->getCategorySelector()); + $filter->addElement($this->getInstituteSelector()); + /* + $filter->addElement($this->getDegreeSelector()); + $filter->addElement($this->getDepartmentSelector()); +*/ + $sidebar->addWidget($filter, 'filter'); + +/* $count_faecher = $this->countFaecher($studiengang_ids, $this->filter['mvv_stgteil.fach_id'] ?? ''); $filter_template = $template_factory->render('shared/filter', [ 'semester' => $semesters, @@ -1083,6 +1112,242 @@ class Studiengaenge_StudiengaengeController extends MVVController $widget->setTitle(_('Filter')); $widget->addElement(new WidgetElement($filter_template)); $sidebar->addWidget($widget, 'filter'); + */ + + + } + + private function getSemesterSelector() + { + $semesters = array_reverse(Semester::getAll()); + $selected_semester = Semester::findByTimestamp($this->filter['start_sem.beginn']); + $list = []; + $list[] = new SelectElement('', _('Alle')); + foreach ($semesters as $semester) { + $list[] = new SelectElement( + $semester->id, + $semester->name, + $semester->id === $selected_semester->id + ); + } + + return new SelectListElement( + _('Semester'), + 'semester_filter', + $list, + false, + ['data-formaction' => $this->set_filterURL()], + true); + } + + private function getStatusSelector() + { + if (is_array($this->studiengang_ids) && count($this->studiengang_ids)) { + $stmt = DBManager::get()->prepare(" + SELECT IFNULL(stat, '__undefined__') AS stat, + COUNT(studiengang_id) AS count_objects + FROM mvv_studiengang WHERE studiengang_id IN (?) + GROUP BY stat"); + $stmt->execute([$this->studiengang_ids]); + } else { + $stmt = DBManager::get()->prepare(" + SELECT IFNULL(stat, '__undefined__') AS stat, + COUNT(studiengang_id) AS count_objects + FROM mvv_studiengang + GROUP BY stat + "); + $stmt->execute(); + } + $list = []; + $list[] = new SelectElement('', _('Alle')); + foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $status) { + $list[] = new SelectElement( + $status['stat'], + sprintf('%1$s (%2$s)', + $GLOBALS['MVV_STUDIENGANG']['STATUS']['values'][$status['stat']]['name'] ?? _('Undefinierter Status'), + $status['count_objects'] + ), + $status['stat'] === ($this->filter['mvv_studiengang.stat'] ?? '') + ); + } + return new SelectListElement( + _('Status'), + 'status_filter', + $list, + false, + ['data-formaction' => $this->set_filterURL()], + true); + } + + private function getSubjectSelector() + { + $stmt = DBManager::get()->prepare(' + SELECT `fach`.`fach_id`, `fach`.`name`, COUNT(DISTINCT `studiengang_id`) AS `count_objects` + FROM `fach` + JOIN `mvv_stgteil` USING(`fach_id`) + JOIN `mvv_stg_stgteil` USING(`stgteil_id`) + WHERE `mvv_stg_stgteil`.`studiengang_id` IN (?) + GROUP BY `fach_id` + '); + $stmt->execute([$this->studiengang_ids]); + $list = []; + $list[] = new SelectElement('', _('Alle')); + foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $subject) { + $list[] = new SelectElement( + $subject['fach_id'], + sprintf('%1$s (%2$s)', $subject['name'], $subject['count_objects']), + $subject['fach_id'] === ($this->filter['mvv_stgteil.fach_id'] ?? '') + ); + } + return new SelectListElement( + _('Fach'), + 'fach_filter', + $list, + false, + ['data-formaction' => $this->set_filterURL()], + true); + } + + private function getDegreeSelector() + { + $stmt = DBManager::get()->prepare(" + SELECT `abschluss`.`abschluss_id`, `abschluss`.`name`, + COUNT(`studiengang_id`) AS `count_objects` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + WHERE `mvv_studiengang`.`studiengang_id` IN (?) + GROUP BY `abschluss_id` + ORDER BY `abschluss`.`name` + "); + $stmt->execute([$this->studiengang_ids]); + $list = []; + $list[] = new SelectElement('', _('Alle')); + foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $degree) { + $list[] = new SelectElement( + $degree['abschluss_id'], + sprintf('%1$s (%2$s)', $degree['name'], $degree['count_objects']), + $degree['abschluss_id'] === ($this->filter['abschluss.abschluss_id'] ?? '') + ); + } + return new SelectListElement( + _('Abschluss'), + 'abschluss_filter', + $list, + false, + ['data-formaction' => $this->set_filterURL()], + true); + } + + private function getCategorySelector() + { + $stmt = DBManager::get()->prepare(" + SELECT `mvv_abschl_kategorie`.`kategorie_id`, `mvv_abschl_kategorie`.`name`, + COUNT(studiengang_id) AS count_objects + FROM `mvv_abschl_kategorie` + JOIN `mvv_abschl_zuord` USING (`kategorie_id`) + JOIN `mvv_studiengang` USING (`abschluss_id`) + WHERE `mvv_studiengang`.`studiengang_id` IN (?) + GROUP BY kategorie_id + ORDER BY `mvv_abschl_kategorie`.`name` ASC + "); + $stmt->execute([$this->studiengang_ids]); + $list = []; + $list[] = new SelectElement('', _('Alle')); + foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $category) { + $list[] = new SelectElement( + $category['kategorie_id'], + sprintf('%1$s (%2$s)', $category['name'], $category['count_objects']), + $category['kategorie_id'] === ($this->filter['mvv_abschl_zuord.kategorie_id'] ?? '') + ); + } + return new SelectListElement( + _('Abschluss-Kategorie'), + 'kategorie_filter', + $list, + false, + ['data-formaction' => $this->set_filterURL()], + true); + } + + private function getInstituteSelector() + { + $stmt = DBManager::get()->prepare(" + SELECT DISTINCT `Institute`.`Name` AS `inst_name`, + `Institute`.`Institut_id` AS `institut_id`, + IF(`Institute`.`Institut_id` = `Institute`.`fakultaets_id`, 1, 0) + AS `is_faculty`, `faculties`.`Name` AS `faculty_name`, + COUNT(`studiengang_id`) AS `count_objects` + FROM `Institute` + JOIN `mvv_studiengang` ON `Institute`.`Institut_id` = `mvv_studiengang`.`institut_id` + JOIN `Institute` AS `faculties` ON `Institute`.`fakultaets_id` = `faculties`.`Institut_id` + WHERE `mvv_studiengang`.`studiengang_id` IN (?) + GROUP BY `Institut_id` + ORDER BY `inst_name` + "); + $stmt->execute([$this->studiengang_ids]); + $list = []; + $list[] = new SelectElement('', _('Alle')); + foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $institute) { + $list[] = new SelectElement( + $institute['institut_id'], + sprintf('%1$s (%2$s)', $institute['inst_name'], $institute['count_objects']), + $institute['institut_id'] === ($this->filter['mvv_studiengang.institut_id'] ?? '') + ); + } + return new SelectListElement( + _('Verantwortliche Einrichtung'), + 'institut_filter', + $list, + false, + ['data-formaction' => $this->set_filterURL()], + true); + } + + /** + * Returns an array with ids of all Studiengaenge found by the given filter. + * If no filter is set an empty array will be returned. + * + * @see ModuleManagementModel::getFilterSql() + * @param array $filter Key-value pairs of filed names and values + * to filter the result set. + * @return array An array of Studiengang ids. + */ + private function findByFilter($filter) + { + $stmt = DBManager::get()->prepare(' + SELECT DISTINCT `mvv_studiengang`.`studiengang_id` + FROM `mvv_studiengang` + LEFT JOIN `mvv_abschl_zuord` USING(`abschluss_id`) + LEFT JOIN `mvv_abschl_kategorie` USING(`kategorie_id`) + LEFT JOIN `mvv_stg_stgteil` USING(`studiengang_id`) + LEFT JOIN `mvv_stgteil` USING(`stgteil_id`) + LEFT JOIN `mvv_fach_inst` USING(`fach_id`) + LEFT JOIN `semester_data` AS `start_sem` ON (`mvv_studiengang`.`start` = `start_sem`.`semester_id`) + LEFT JOIN `semester_data` AS `end_sem` ON (`mvv_studiengang`.`end` = `end_sem`.`semester_id`) + WHERE (`mvv_studiengang`.`stat` = :status OR :status) + AND (`mvv_studiengang`.`institut_id` IN (:institute) OR :inst_filter) + AND (`mvv_abschl_zuord`.`abschluss_id` = :degree OR :degree) + AND (`mvv_abschl_kategorie`.`kategorie_id` = :category OR :category) + AND (`mvv_stgteil`.`fach_id` = :subject OR :subject) + AND (`mvv_fach_inst`.`institut_id` = :department OR :department) + AND (`start_sem`.`beginn` <= :start OR ISNULL(`start_sem`.`beginn`)) + AND (`end_sem`.`ende` >= :end OR ISNULL(`end_sem`.`ende`)) + + '); + $stmt->execute( + [ + ':status' => $this->filter['mvv_studiengang.stat'] ?? 1, + ':inst_filter' => empty($this->filter['mvv_studiengang.institut_id']), + ':institute' => $this->filter['mvv_studiengang.institut_id'] ?? '', + ':degree' => $this->filter['abschluss.abschluss_id'] ?? 1, + ':category' => $this->filter['mvv_abschl_zuord.kategorie_id'] ?? 1, + ':subject' => $this->filter['mvv_stgteil.fach_id'] ?? 1, + ':department' => $this->filter['mvv_fach_inst.institut_id'] ?? 1, + ':start' => $this->filter['start_sem.beginn'] ?? 0, + ':end' => $this->filter['end_sem.ende'] ?? PHP_INT_MAX, + ] + ); + return $stmt->fetchAll(PDO::FETCH_COLUMN, 0); } /** @@ -1137,7 +1402,9 @@ class Studiengaenge_StudiengaengeController extends MVVController * @param array $studiengang_ids The ids of the study courses. * @param string $fach_id The id of the selected subject. * @return array Number of study courses grouped by subjects. + * REMOVE */ + /* private function countFaecher(array $studiengang_ids, string $fach_id): array { if ($fach_id === '') { @@ -1159,6 +1426,7 @@ class Studiengaenge_StudiengaengeController extends MVVController GROUP BY `fach_id`"; return DBManager::get()->fetchPairs($query, $params); } + */ public function approve_action($studiengang_id) { diff --git a/app/controllers/studiengaenge/versionen.php b/app/controllers/studiengaenge/versionen.php index 41625bf..b101f3e 100644 --- a/app/controllers/studiengaenge/versionen.php +++ b/app/controllers/studiengaenge/versionen.php @@ -72,12 +72,21 @@ class Studiengaenge_VersionenController extends SharedVersionController protected function chooser_kategorien_fachbereich() { - $kategorien = AbschlussKategorie::findByFachbereich( - $this->chooser_filter['fachbereich'] + $stmt = DBManager::get()->prepare( + 'SELECT `mvv_abschl_kategorie`.* + FROM `mvv_fach_inst` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + JOIN `mvv_abschl_zuord` USING (`abschluss_id`) + JOIN `mvv_abschl_kategorie` USING (`kategorie_id`) + WHERE `mvv_fach_inst`.`institut_id` = ? + ORDER BY `mvv_abschl_kategorie`.`position`' ); - foreach ($kategorien as $kategorie) { - $this->lists['kategorien']['elements'][$kategorie->id] = [ - 'name' => $kategorie->name + $stmt->execute([$this->chooser_filter['fachbereich'] ?? '']); + foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $category) { + $this->lists['kategorien']['elements'][$category['kategorie_id']] = [ + 'name' => $category['name'] ]; } $this->lists['kategorien']['headline'] = _('Abschluss-Kategorie'); diff --git a/app/views/fachabschluss/abschluesse/abschluss.php b/app/views/fachabschluss/abschluesse/abschluss.php index 5c37ef3..38c7344 100644 --- a/app/views/fachabschluss/abschluesse/abschluss.php +++ b/app/views/fachabschluss/abschluesse/abschluss.php @@ -1,20 +1,43 @@ + -
+
@@ -24,12 +47,12 @@ + id === $abschluss->kategorie_id ? 'selected ' : '') ?>value="id ?>">name) ?> - kategorie_id)->getDisplayName()) ?> + category->getDisplayName()) ?>
@@ -42,6 +65,6 @@ _('Änderungen übernehmen')]) ?> - action_url('index'), ['title' => _('Zurück zur Übersicht')]) ?> + indexURL(), ['title' => _('Zurück zur Übersicht')]) ?>
diff --git a/app/views/fachabschluss/abschluesse/index.php b/app/views/fachabschluss/abschluesse/index.php index 7e8947f..1580c7d 100644 --- a/app/views/fachabschluss/abschluesse/index.php +++ b/app/views/fachabschluss/abschluesse/index.php @@ -1,3 +1,12 @@ +
@@ -14,25 +23,25 @@ - + - - + + diff --git a/app/views/studiengaenge/abschluesse/index.php b/app/views/studiengaenge/abschluesse/index.php index 51cd733..d2849db 100644 --- a/app/views/studiengaenge/abschluesse/index.php +++ b/app/views/studiengaenge/abschluesse/index.php @@ -20,17 +20,17 @@ continue; } ?> - + - + id) : ?> diff --git a/app/views/studiengaenge/studiengaenge/studiengaenge.php b/app/views/studiengaenge/studiengaenge/studiengaenge.php index 4ae0dc3..cd03ae7 100644 --- a/app/views/studiengaenge/studiengaenge/studiengaenge.php +++ b/app/views/studiengaenge/studiengaenge/studiengaenge.php @@ -68,7 +68,7 @@ ?> havePerm(MvvPerm::PERM_CREATE)) : ?> - count_faecher) : ?> + studiengangteile)) : ?> addButton( 'delete', _('Studiengang löschen'), diff --git a/lib/models/Abschluss.php b/lib/models/Abschluss.php index 104dbd9..4f26563 100644 --- a/lib/models/Abschluss.php +++ b/lib/models/Abschluss.php @@ -67,21 +67,9 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject 'order_by' => 'GROUP BY fach_id ORDER BY name' ]; - $config['additional_fields']['count_faecher']['get'] = function (Abschluss $abschluss) { - return $abschluss->count_faecher; - }; - $config['additional_fields']['kategorie_name']['get'] = function (Abschluss $abschluss) { - return $abschluss->kategorie_name; - }; $config['additional_fields']['kategorie_id']['get'] = function (Abschluss $abschluss) { return $abschluss->category_assignment ? $abschluss->category_assignment->kategorie_id : null; }; - $config['additional_fields']['count_studiengaenge']['get'] = function (Abschluss $abschluss) { - return $abschluss->count_studiengaenge; - }; - $config['additional_fields']['count_objects']['get'] = function (Abschluss $abschluss) { - return $abschluss->count_objects; - }; $config['additional_fields']['count_user']['get'] = 'countUser'; $config['i18n_fields']['name'] = true; @@ -91,36 +79,6 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject parent::configure($config); } - /** - * Number of assigned Faecher. - * @var type int - */ - private $count_faecher; - - /** - * Number of Studiengaenge this Abschluss is assigned to. - * @var type int - */ - private $count_studiengaenge; - - /** - * The name of the assigned Kategorie. - * @var type string - */ - private $kategorie_name; - - /** - * The id of the assigned Kategorie. - * @var type - */ - private $kategorie_id; - - /** - * Alias for $count_studiengaenge - * @var type - */ - private $count_objects; - public function __construct($id = null) { parent::__construct($id); @@ -139,7 +97,9 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject * @param array $filter Key-value pairs of filed names and values * to filter the result set. * @return object A SimpleORMapCollection of Abschluss objects. + * REMOVED */ + /* public static function getAllEnriched($sortby = 'name', $order = 'ASC', $row_count = null, $offset = null, $filter = null) { @@ -161,6 +121,7 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject ORDER BY ' . $sortby, [], $row_count, $offset); } + */ /** * Returns the number of Abschlüsse optional filtered by $filter. @@ -168,7 +129,9 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject * @param array $filter Key-value pairs of filed names and values * to filter the result set. * @return int The number of Abschluesse + * REMOVED */ + /* public static function getCount($filter = null) { $query = ' @@ -185,34 +148,39 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject $db->execute(); return $db->fetchColumn(0); } + */ /** * Returns all Abschluesse assigned to a given Fach. + * An Abschluss is assigned to a Fach if a Studiengangteil (that holds th Fach) + * is assigned to a Studiengang (as the home of the Abschluss). * * @param string $fach_id The id of the fach. * @return array An array of abschluss objects. + * REMOVED */ + /* public static function findByFach($fach_id) { - return parent::getEnrichedByQuery(' - SELECT ma.*, - COUNT(DISTINCT mss.studiengang_id) AS count_studiengaenge - FROM mvv_stgteil AS mst - INNER JOIN mvv_stg_stgteil AS mss USING (stgteil_id) - LEFT JOIN mvv_studiengang USING (studiengang_id) - LEFT JOIN abschluss AS ma USING (abschluss_id) - WHERE mst.fach_id = ? - GROUP BY ma.abschluss_id - ORDER BY name', + return self::findBySQL( + 'JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = ? + GROUP BY `abschluss`.`abschluss_id` + ORDER BY `abschluss`.`name`', [$fach_id] ); } + */ /** * Returns all Abschluesse assigned to Studiengaenge. * * @return array An array of Abschluesse. + * REMOVED */ + /* public static function findUsed() { return parent::getEnrichedByQuery(' @@ -223,6 +191,7 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject ORDER BY name '); } + */ /** * Returns all Abschluesse assigned to the given Studiengaenge. @@ -231,7 +200,9 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject * Studiengaenge. * @return array An array of Abschluesse with number of assigned * Studiengaenge. + * REMOVED */ + /* public static function findByStudiengaenge($studiengang_ids = []) { return parent::getEnrichedByQuery(' @@ -243,13 +214,16 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject GROUP BY ma.abschluss_id ORDER BY ma.name'); } + */ /** * Returns all Abschluesse assigned to the given Fachbereich. * * @param string $fachbereich_id The id of a Fachbereich. * @return array An array of Abschluesse. + * REMOVED */ + /* public static function findByFachbereich($fachbereich_id) { return parent::getEnrichedByQuery(' @@ -265,13 +239,16 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject [$fachbereich_id] ); } + */ /** * Returns all Abschluesse assigned to the given module. * * @param string $modul_id The id of a module. * @return array An array of Abschluesse. + * REMOVED */ + /* public static function findByModul($modul_id) { return parent::getEnrichedByQuery(' @@ -287,6 +264,7 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject [$modul_id] ); } + */ /** * @see ModuleManagementModel::getClassDisplayName @@ -303,7 +281,9 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject * @param int Position of this Abschluss in the given Kategorie. * @return object|null The assigned Kategorie. Null if assigned * Abschluss-Kategorie is unknown + * REMOVED */ + /* public function assignKategorie($kategorie_id, $position = null) { $kategorie = AbschlussKategorie::find($kategorie_id); @@ -318,6 +298,7 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject return $kategorie; } + */ /** * Returns all Faecher this Abschluss is assigned to. @@ -436,6 +417,8 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject } + // REMOVED + /* public function validate() { $ret = parent::validate(); @@ -478,6 +461,7 @@ class Abschluss extends ModuleManagementModelTreeItem implements PrivacyObject } return $ret; } + */ public function countUser() { diff --git a/lib/models/AbschlussKategorie.php b/lib/models/AbschlussKategorie.php index 36eb9aa..1be2f9c 100644 --- a/lib/models/AbschlussKategorie.php +++ b/lib/models/AbschlussKategorie.php @@ -140,7 +140,7 @@ class AbschlussKategorie extends ModuleManagementModelTreeItem * @param int $offset The first object to return in a result set. * @param array $filter Key-value pairs of filed names and values * to filter the result set. - * @return object A SimpleORMapCollection of Abschluss objects. + * @return SimpleOrMapCollection A collection of Abschluss objects. */ public static function getAllEnriched($sortby = 'position', $order = 'ASC', $row_count = null, $offset = null, $filter = null) @@ -173,7 +173,7 @@ class AbschlussKategorie extends ModuleManagementModelTreeItem /** * Finds all kategorien assigned to abschluesse used by studiengaenge. * - * @return array Array of objects or empty array + * @return SimpleOrMapCollection Collection of objects */ public static function findUsed() { @@ -192,7 +192,9 @@ class AbschlussKategorie extends ModuleManagementModelTreeItem * * @param string $fachbereich_id The id of the fachbereich * @return array Array of objects or empty array + * REMOVED */ + /* public static function findByFachbereich($fachbereich_id) { return parent::getEnrichedByQuery(' @@ -208,6 +210,7 @@ class AbschlussKategorie extends ModuleManagementModelTreeItem [$fachbereich_id] ); } + */ /** * Returns all Kategorien implicitly assigned (through @@ -215,7 +218,9 @@ class AbschlussKategorie extends ModuleManagementModelTreeItem * * @param array $studiengang_ids Array of Studiengang ids. * @return object SimpleORMapCollection of Kategorien. + * REMOVED */ + /* public static function findByStudiengaenge($studiengang_ids = []) { return parent::getEnrichedByQuery(' @@ -229,6 +234,7 @@ class AbschlussKategorie extends ModuleManagementModelTreeItem ORDER BY mak.name ASC '); } + */ /** * @see ModuleManagementModel::getClassDisplayName diff --git a/lib/models/Fach.php b/lib/models/Fach.php index 8f03cf4..802fcd4 100644 --- a/lib/models/Fach.php +++ b/lib/models/Fach.php @@ -430,7 +430,9 @@ class Fach extends ModuleManagementModelTreeItem implements PrivacyObject * @param string $kategorie_id The id of the Abschluss-Kategorie. * @param string $abschluss_id The id of the Abschluss. * @return array Found Fachbereiche as array. Empty array if none was found. + * REMOVED */ + /* public static function findUsedFachbereiche($kategorie_id = null, $abschluss_id = null) { @@ -479,6 +481,7 @@ class Fach extends ModuleManagementModelTreeItem implements PrivacyObject } return $fachbereiche; } + */ /** @@ -600,11 +603,14 @@ class Fach extends ModuleManagementModelTreeItem implements PrivacyObject * Returns all Abschluesse this Fach is implicitly assigned to. * * @return A collection of Faecher. + * REMOVED */ + /* public function getAbschluesse() { return Abschluss::findByFach($this->getId()); } + */ /** * @see MvvTreeItem::getTrailParentId() diff --git a/lib/models/ModuleManagementModel.php b/lib/models/ModuleManagementModel.php index ebb93e6..d7c2398 100644 --- a/lib/models/ModuleManagementModel.php +++ b/lib/models/ModuleManagementModel.php @@ -236,9 +236,7 @@ abstract class ModuleManagementModel extends SimpleORMap implements ModuleManage * * @throws InvalidValuesException */ - public function validate() { - - } + public function validate() {} /** * @see SimpleOrMap::delete() @@ -626,7 +624,7 @@ abstract class ModuleManagementModel extends SimpleORMap implements ModuleManage * @param array $additional_fields Calculated columns. * @return string The sort query part. */ - protected static function createSortStatement($sort, $order = 'ASC', + public static function createSortStatement($sort, $order = 'ASC', $standard_field = null, $additional_fields = []) { $order = mb_strtoupper(trim($order)) !== 'DESC' ? ' ASC' : ' DESC'; diff --git a/lib/models/Studiengang.php b/lib/models/Studiengang.php index 2906e35..5220922 100644 --- a/lib/models/Studiengang.php +++ b/lib/models/Studiengang.php @@ -156,14 +156,10 @@ class Studiengang extends ModuleManagementModelTreeItem $config['additional_fields']['count_dokumente']['get'] = function ($stg) { return $stg->count_dokumente; }; - $config['additional_fields']['count_faecher']['get'] = - function ($stg) { return $stg->count_faecher; }; $config['additional_fields']['count_module']['get'] = function ($stg) { return $stg->count_module; }; $config['additional_fields']['institut_name']['get'] = function ($stg) { return $stg->institut_name; }; - $config['additional_fields']['kategorie_name']['get'] = - function ($stg) { return $stg->kategorie_name; }; $config['additional_fields']['display_name']['get'] = function ($stg) { return $stg->getDisplayName(); }; @@ -531,7 +527,9 @@ class Studiengang extends ModuleManagementModelTreeItem * @param array $filter Key-value pairs of filed names and values * to filter the result set. * @return SimpleORMapCollection A collection of institutes. + * REMOVED */ + /* public static function getAllAssignedInstitutes($filter = null) { return Fachbereich::getEnrichedByQuery(' @@ -550,6 +548,7 @@ class Studiengang extends ModuleManagementModelTreeItem GROUP BY Institute.Institut_id ORDER BY name', []); } + */ /** * @see ModuleManagementModel::findBySearchTerm() @@ -663,7 +662,9 @@ class Studiengang extends ModuleManagementModelTreeItem * @param array $filter Key-value pairs of filed names and values * to filter the result set. * @return array An array of Studiengang ids. + * REMOVED */ + /* public static function findByFilter($filter) { $filter_sql = self::getFilterSql($filter, true); @@ -685,6 +686,7 @@ class Studiengang extends ModuleManagementModelTreeItem $stmt->execute(); return $stmt->fetchAll(PDO::FETCH_COLUMN, 0); } + */ /** * Returns an array with Modul ids from modules related to this Studiengang. -- cgit v1.0
- count_faecher) : ?> + faecher)) : ?> name) ?> name) ?> kategorie_name) ?>count_faecher ?>category->name) ?>faecher) ?> - + asSvg(); ?> - count_faecher) : ?> + faecher) === 0) : ?> asInput( [ 'formaction' => $controller->action_url('delete/' . $abschluss->id), @@ -60,11 +69,10 @@ $pagination->set_attribute('perPage', MVVController::$items_per_page); $pagination->set_attribute('num_postings', $count); $pagination->set_attribute('page', $page); - $page_link = explode('?', $controller->action_url('index'))[0] . '?page_abschluesse=%s'; + $page_link = strtok('?', $controller->indexURL()) . '?page_abschluesse=%s'; $pagination->set_attribute('pagelink', $page_link); echo $pagination->render(); ?> -
- name) && $abschluss->count_studiengaenge) : ?> - + name) && count($abschluss->studiengaenge)) : ?> + - count_studiengaenge) : ?> + studiengaenge)) : ?> + href="detailsLink($abschluss->id) ?>"> getDisplayName()) ?> @@ -38,7 +38,7 @@ count_studiengaenge ?>studiengaenge) ?>