From b015aa61b1fb3d5abe647748f223820e76141e99 Mon Sep 17 00:00:00 2001 From: Peter Thienel Date: Wed, 3 Jan 2024 10:19:29 +0000 Subject: =?UTF-8?q?Resolve=20"MVV:=20Suche=20(Filter)=20nach=20Abschl?= =?UTF-8?q?=C3=BCssen=20und=20F=C3=A4chern=20im=20Backend"?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Closes #3387 Merge request studip/studip!2306 --- app/controllers/lvgruppen/lvgruppen.php | 117 +++++++++- app/controllers/materialien/files.php | 271 ++++++++++++++++++++++- app/controllers/module/module.php | 144 ++++++++++++- app/controllers/shared/contacts.php | 273 +++++++++++++++++++++++- app/controllers/studiengaenge/studiengaenge.php | 40 +++- app/views/shared/filter.php | 59 ++++- lib/models/MvvFile.php | 13 +- 7 files changed, 887 insertions(+), 30 deletions(-) diff --git a/app/controllers/lvgruppen/lvgruppen.php b/app/controllers/lvgruppen/lvgruppen.php index 194f171..5bac0fd 100644 --- a/app/controllers/lvgruppen/lvgruppen.php +++ b/app/controllers/lvgruppen/lvgruppen.php @@ -41,6 +41,25 @@ class Lvgruppen_LvgruppenController extends MVVController $search_result = $this->getSearchResult('Lvgruppe'); + $lvg_ids = []; + if (count($search_result) > 0) { + $lvg_ids = $search_result; + } else { + if (!empty($_SESSION['mvv_filter_lvg_fach_id'])) { + $lvg_ids = $this->findLvgIdsByFach($_SESSION['mvv_filter_lvg_fach_id']); + } + if (!empty($_SESSION['mvv_filter_lvg_abschluss_id'])) { + if (count($lvg_ids) > 0) { + $lvg_ids = array_intersect( + $lvg_ids, + $this->findLvgIdsByAbschluss($_SESSION['mvv_filter_lvg_abschluss_id']) + ); + } else { + $lvg_ids = $this->findLvgIdsByAbschluss($_SESSION['mvv_filter_lvg_abschluss_id']); + } + } + } + // Nur LvGruppen an Modulen von verantwortlichen Einrichtungen an denen der User // eine Rolle hat $own_institutes = MvvPerm::getOwnInstitutes(); @@ -342,7 +361,7 @@ class Lvgruppen_LvgruppenController extends MVVController $sidebar = Sidebar::get(); $widget = new SelectWidget( - _('Verwendung in Semester:'), + _('Verwendet in Semester'), $this->action_url('set_filter', ['fachbereich_filter' => $selected_fachbereich]), 'semester_filter' ); @@ -368,7 +387,7 @@ class Lvgruppen_LvgruppenController extends MVVController $perm_institutes = MvvPerm::getOwnInstitutes(); if ($perm_institutes !== false) { $widget = new SelectWidget( - _('Verwendet von Fachbereich:'), + _('Verwendet von Fachbereich'), $this->action_url('set_filter', ['semester_filter' => $this->semester_filter]), 'fachbereich_filter' ); @@ -400,6 +419,52 @@ class Lvgruppen_LvgruppenController extends MVVController $sidebar->addWidget($widget, 'fachbereich_filter'); } + + $selected_fach = $_SESSION['mvv_filter_lvg_fach_id'] ?? ''; + $widget = new SelectWidget( + _('Verwendet von Fach'), + $this->action_url('set_filter', ['fach_filter' => $selected_fach]), + 'fach_filter' + ); + $widget->class = 'nested-select'; + $widget->addElement( + new SelectElement('select-none', _('Alle'), $selected_fach === '') + ); + $faecher = Fach::findBySQL(' 1 ORDER BY `name`'); + foreach ($faecher as $fach) { + $widget->addElement( + new SelectElement( + $fach->id, + $fach->name, + $selected_fach === $fach->id + ), + 'select-' . $fach->name + ); + } + $sidebar->addWidget($widget, 'fach_filter'); + + $selected_abschluss = $_SESSION['mvv_filter_lvg_abschluss_id'] ?? ''; + $widget = new SelectWidget( + _('Verwendet von Abschluss'), + $this->action_url('set_filter', ['abschluss_filter' => $selected_abschluss]), + 'abschluss_filter' + ); + $widget->class = 'nested-select'; + $widget->addElement( + new SelectElement('select-none', _('Alle'), $selected_abschlussh === '') + ); + $abschluesse = Abschluss::findBySQL(' 1 ORDER BY `name`'); + foreach ($abschluesse as $abschluss) { + $widget->addElement( + new SelectElement( + $abschluss->id, + $abschluss->name, + $selected_abschluss === $abschluss->id + ), + 'select-' . $abschluss->name + ); + } + $sidebar->addWidget($widget, 'abschluss_filter'); } /** @@ -415,6 +480,12 @@ class Lvgruppen_LvgruppenController extends MVVController $this->semester_filter = mb_strlen(Request::get('semester_filter')) ? Request::option('semester_filter') : null; + // Fach + $_SESSION['mvv_filter_lvg_fach_id'] = Request::option('fach_filter', ''); + + // Abschluss + $_SESSION['mvv_filter_lvg_abschluss_id'] = Request::option('abschluss_filter', ''); + // store filter $this->reset_page(); $this->sessSet('filter', $this->filter); @@ -424,6 +495,9 @@ class Lvgruppen_LvgruppenController extends MVVController public function reset_filter_action() { + $_SESSION['mvv_filter_lvg_fach_id'] = ''; + $_SESSION['mvv_filter_lvg_abschluss_id'] = ''; + $this->filter = []; $this->sessRemove('filter'); $this->semester_filter = null; @@ -467,4 +541,43 @@ class Lvgruppen_LvgruppenController extends MVVController } ); } + + /** + * Returns the ids of the lvgruppen related to the given subject id. + * + * @param string $fach_id The id of the selected subject. + * @return array The ids of the modules related to the subject. + */ + private function findLvgIdsByFach(string $fach_id): array + { + $query = "SELECT `mvv_lvgruppe_modulteil`.`lvgruppe_id` + FROM `mvv_lvgruppe_modulteil` + JOIN `mvv_modulteil` USING (`modulteil_id`) + JOIN `mvv_stgteilabschnitt_modul` USING (`modul_id`) + JOIN `mvv_stgteilabschnitt` USING (`abschnitt_id`) + JOIN `mvv_stgteilversion` USING (`version_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = ?"; + return DBManager::get()->fetchFirst($query, [$fach_id]); + } + + /** + * Returns the ids of the lvgruppen related to the given degree id. + * + * @param string $abschluss_id The id of the selected degree. + * @return array The ids of the lvgruppen related to the degree. + */ + private function findLvgIdsByAbschluss(string $abschluss_id): array + { + $query = "SELECT `mvv_lvgruppe_modulteil`.`lvgruppe_id` + FROM `mvv_lvgruppe_modulteil` + JOIN `mvv_modulteil` USING (`modulteil_id`) + JOIN `mvv_stgteilabschnitt_modul` USING (`modul_id`) + JOIN `mvv_stgteilabschnitt` USING (`abschnitt_id`) + JOIN `mvv_stgteilversion` USING (`version_id`) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + WHERE `mvv_studiengang`.`abschluss_id` = ?"; + return DBManager::get()->fetchFirst($query, [$abschluss_id]); + } } diff --git a/app/controllers/materialien/files.php b/app/controllers/materialien/files.php index 65c9b44..804668e 100644 --- a/app/controllers/materialien/files.php +++ b/app/controllers/materialien/files.php @@ -38,11 +38,31 @@ class Materialien_FilesController extends MVVController $this->initSearchParams(); $search_result = $this->getSearchResult('MvvFile'); - if ($search_result) { + + $file_ids = []; + if (count($search_result) > 0) { + $file_ids = $search_result; + } else { + if (!empty($_SESSION['mvv_filter_files_fach_id'])) { + $file_ids = $this->findFileIdsByFach($_SESSION['mvv_filter_contacts_fach_id']); + } + if (!empty($_SESSION['mvv_filter_files_abschluss_id'])) { + if (count($file_ids) > 0) { + $file_ids = array_intersect( + $file_ids, + $this->findFileIdsByAbschluss($_SESSION['mvv_filter_contacts_abschluss_id']) + ); + } else { + $file_ids = $this->findFileIdsByAbschluss($_SESSION['mvv_filter_contacts_abschluss_id']); + } + } + } + + if ($file_ids) { $ranges = []; $refs = []; $this->filter = array_merge( - ['mvv_files.mvvfile_id' => $search_result], + ['mvv_files.mvvfile_id' => $file_ids], (array) $this->filter ); } @@ -589,6 +609,13 @@ class Materialien_FilesController extends MVVController } else { $this->sessRemove('filter'); } + + // Fach + $_SESSION['mvv_filter_files_fach_id'] = Request::option('fach_filter', ''); + + // Abschluss + $_SESSION['mvv_filter_files_abschluss_id'] = Request::option('abschluss_filter', ''); + // store filter $this->reset_page(); $this->sessSet('filter', $this->filter); @@ -597,6 +624,9 @@ class Materialien_FilesController extends MVVController public function reset_filter_action() { + $_SESSION['mvv_filter_files_fach_id'] = ''; + $_SESSION['mvv_filter_files_abschluss_id'] = ''; + $this->filter = []; $this->reset_search(); $this->sessRemove('filter'); @@ -652,7 +682,9 @@ class Materialien_FilesController extends MVVController $this->filter ); unset($institute_filter['searchnames']); - + $file_ids = MvvFile::getIdsFiltered($this->filter, true, false); + $count_faecher = $this->countFaecher($file_ids, $_SESSION['mvv_filter_files_fach_id'] ?? ''); + $count_abschluesse = $this->countAbschluesse($file_ids, $_SESSION['mvv_filter_files_abschluss_id'] ?? ''); $semesters = new SimpleCollection(array_reverse(Semester::getAll())); $filter_template = $template_factory->render('shared/filter', [ 'name_search' => true, @@ -666,6 +698,16 @@ class Materialien_FilesController extends MVVController 'selected_institut' => $this->filter['mvv_studiengang.institut_id'] ?? '', 'zuordnungen' => !empty($this->search_result['MvvFile']) ? MvvFile::getAllRelations($this->search_result['MvvFile']) : [], 'selected_zuordnung' => $this->filter['mvv_files_ranges.range_type'] ?? '', + 'faecher' => SimpleORMapCollection::createFromArray( + Fach::findMany(array_keys($count_faecher)) + )->orderBy('name'), + 'count_faecher' => $count_faecher, + 'selected_fach' => $_SESSION['mvv_filter_files_fach_id'] ?? '', + 'abschluesse' => SimpleORMapCollection::createFromArray( + Abschluss::findMany(array_keys($count_abschluesse)) + )->orderBy('name'), + 'count_abschluesse' => $count_abschluesse, + 'selected_abschluss' => $_SESSION['mvv_filter_files_abschluss_id'] ?? '', 'action' => $this->action_url('set_filter'), 'action_reset' => $this->action_url('reset_filter')] ); @@ -895,4 +937,227 @@ class Materialien_FilesController extends MVVController $this->redirect($this->url_for('materialien/files/add_dokument', 'index', $range_type, implode(',', Request::getArray('range_id')))); } } + + /** + * Returns the number of files grouped by subjects for given file ids. + * + * @param array $module_ids The ids of the files. + * @param string $fach_id The id of the selected subject. + * @return array Number of files grouped by subjects. + */ + private function countFaecher(array $file_ids, string $fach_id): array + { + if ($fach_id === '') { + $params = [':file_ids' => $file_ids]; + $where = "`mvv_files_ranges`.`mvvfile_id` IN (:file_ids)"; + } else { + $params = [ + ':fach_id' => $fach_id, + ':file_ids' => $file_ids + ]; + $where = "`fach`.`fach_id` = :fach_id + AND `mvv_files_ranges`.`mvvfile_id` IN (:file_ids)"; + } + $query = "SELECT `counting`.`fach_id`, COUNT(DISTINCT `counting`.`mvvfile_id`) + FROM ( + SELECT `fach_id`, `mvvfile_id` + FROM `fach` + 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_files_ranges` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_abschl_zuord`.`kategorie_id` + AND `mvv_files_ranges`.`range_type` = 'AbschlussKategorie' + ) + WHERE {$where} + + UNION ALL + + SELECT `fach_id`, `mvvfile_id` + FROM `fach` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + JOIN `mvv_files_ranges` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_studiengang`.`studiengang_id` + AND `mvv_files_ranges`.`range_type` = 'Studiengang' + ) + WHERE {$where} + + UNION ALL + + SELECT `fach_id`, `mvvfile_id` + FROM `fach` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_stgteilversion` USING(`stgteil_id`) + JOIN `mvv_files_ranges` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_stgteilversion`.`version_id` + AND `mvv_files_ranges`.`range_type` = 'StgteilVersion' + ) + WHERE {$where} + ) AS `counting` + GROUP BY `counting`.`fach_id`"; + return DBManager::get()->fetchPairs($query, $params); + } + + /** + * Returns the ids of the files related to the given subject id. + * + * @param string $fach_id The id of the selected subject. + * @return array The ids of the files related to the subject. + */ + private function findFileIdsByFach(string $fach_id): array + { + $query = "SELECT `mvv_files_ranges`.`mvvfile_id` + FROM `mvv_files_ranges` + JOIN `mvv_stgteilversion` + ON ( + `mvv_stgteilversion`.`version_id` = `mvv_files_ranges`.`range_id` + AND `mvv_files_ranges`.`range_type` = 'StgteilVersion' + ) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = :fach_id + + UNION + + SELECT `mvv_files_ranges`.`mvvfile_id` + FROM `mvv_files_ranges` + JOIN `mvv_studiengang` + ON ( + `mvv_studiengang`.`studiengang_id` = `mvv_files_ranges`.`range_id` + AND `mvv_files_ranges`.`range_type` = 'Studiengang' + ) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = :fach_id + + UNION + + SELECT `mvv_files_ranges`.`mvvfile_id` + FROM `mvv_files_ranges` + JOIN `mvv_abschl_zuord` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_abschl_zuord`.`kategorie_id` + AND `mvv_files_ranges`.`range_type` = 'AbschlussKategorie' + ) + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = :fach_id"; + + return DBManager::get()->fetchFirst($query, [':fach_id' => $fach_id]); + } + + /** + * Returns the number of files grouped by degrees for given file ids. + * + * @param array $module_ids The ids of the files. + * @param string $abschluss_id The id of the selected degree. + * @return array Number of files grouped by degrees. + */ + private function countAbschluesse(array $file_ids, string $abschluss_id): array + { + if ($abschluss_id === '') { + $params = [':file_ids' => $file_ids]; + $where = "`mvv_files_ranges`.`mvvfile_id` IN (:file_ids)"; + } else { + $params = [ + ':abschluss_id' => $abschluss_id, + ':file_ids' => $file_ids + ]; + $where = "`abschluss`.`abschluss_id` = :abschluss_id + AND `mvv_files_ranges`.`mvvfile_id` IN (:file_ids)"; + } + $query = "SELECT `counting`.`abschluss_id`, COUNT(DISTINCT `counting`.`mvvfile_id`) + FROM ( + SELECT `abschluss_id`, `mvvfile_id` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteilversion` USING (`stgteil_id`) + JOIN `mvv_files_ranges` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_stgteilversion`.`version_id` + AND `mvv_files_ranges`.`range_type` = 'StgteilVersion' + ) + WHERE {$where} + + UNION ALL + + SELECT `abschluss_id`, `mvvfile_id` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_files_ranges` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_studiengang`.`studiengang_id` + AND `mvv_files_ranges`.`range_type` = 'Studiengang' + ) + WHERE {$where} + + UNION ALL + + SELECT `abschluss_id`, `mvvfile_id` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_abschl_zuord` USING (`abschluss_id`) + JOIN `mvv_files_ranges` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_abschl_zuord`.`kategorie_id` + AND `mvv_files_ranges`.`range_type` = 'AbschlussKategorie' + ) + WHERE {$where} + ) AS `counting` + GROUP BY `counting`.`abschluss_id`"; + + return DBManager::get()->fetchPairs($query, $params); + } + + /** + * Returns the ids of the files related to the given degree id. + * + * @param string $abschluss_id The id of the selected degree. + * @return array The ids of the files related to the degree. + */ + private function findFileIdsByAbschluss(string $abschluss_id): array + { + $query = "SELECT `mvv_files_ranges`.`mvvfile_id` + FROM `mvv_files_ranges` + JOIN `mvv_abschl_zuord` + ON ( + `mvv_abschl_zuord`.`kategorie_id` = `mvv_files_ranges`.`range_id` + AND `mvv_files_ranges`.`range_type` = 'AbschlussKategorie' + ) + JOIN `mvv_studiengang` USING(`abschluss_id`) + WHERE `mvv_studiengang`.`abschluss_id` = :abschluss_id + + UNION + + SELECT `mvv_files_ranges`.`mvvfile_id` + FROM `mvv_files_ranges` + JOIN `mvv_studiengang` + ON ( + `mvv_studiengang`.`studiengang_id` = `mvv_files_ranges`.`range_id` + AND `mvv_files_ranges`.`range_type` = 'Studiengang' + ) + WHERE `mvv_studiengang`.`abschluss_id` = :abschluss_id + + UNION + + SELECT `mvv_files_ranges`.`mvvfile_id` + FROM `mvv_files_ranges` + JOIN `mvv_stgteilversion` + ON ( + `mvv_files_ranges`.`range_id` = `mvv_stgteilversion`.`version_id` + AND `mvv_files_ranges`.`range_type` = 'StgteilVersion' + ) + JOIN `mvv_stg_stgteil` USING(`stgteil_id`) + JOIN `mvv_studiengang` USING(`studiengang_id`) + WHERE `mvv_studiengang`.`abschluss_id` = :abschluss_id"; + + return DBManager::get()->fetchFirst($query, [':abschluss_id' => $abschluss_id]); + } } diff --git a/app/controllers/module/module.php b/app/controllers/module/module.php index f8c9917..852e9d9 100644 --- a/app/controllers/module/module.php +++ b/app/controllers/module/module.php @@ -50,9 +50,29 @@ class Module_ModuleController extends MVVController if (empty($this->filter['mvv_modul_inst.institut_id'])) { unset($this->filter['mvv_modul_inst.institut_id']); } + + $module_ids = []; + if (count($search_result) > 0) { + $module_ids = $search_result; + } else { + if ($_SESSION['mvv_filter_module_fach_id']) { + $module_ids = $this->findModuleIdsByFach($_SESSION['mvv_filter_module_fach_id']); + } + if (!empty($_SESSION['mvv_filter_module_abschluss_id'])) { + if (count($module_ids) > 0) { + $module_ids = array_intersect( + $module_ids, + $this->findModuleIdsByAbschluss($_SESSION['mvv_filter_module_abschluss_id']) + ); + } else { + $module_ids = $this->findModuleIdsByAbschluss($_SESSION['mvv_filter_module_abschluss_id']); + } + } + } + $this->filter = array_merge( [ - 'mvv_modul.modul_id' => $search_result, + 'mvv_modul.modul_id' => $module_ids, 'mvv_modul_inst.gruppe' => 'hauptverantwortlich', 'mvv_modul_inst.institut_id' => MvvPerm::getOwnInstitutes() ], @@ -1286,6 +1306,12 @@ class Module_ModuleController extends MVVController $this->filter['mvv_modul_inst.institut_id'] = MvvPerm::getOwnInstitutes(); } + // Fach + $_SESSION['mvv_filter_module_fach_id'] = Request::option('fach_filter', ''); + + // Abschluss + $_SESSION['mvv_filter_module_abschluss_id'] = Request::option('abschluss_filter', ''); + // store filter $this->reset_page(); $this->sessSet('filter', $this->filter); @@ -1296,6 +1322,9 @@ class Module_ModuleController extends MVVController { $this->reset_page(); + $_SESSION['mvv_filter_module_fach_id'] = ''; + $_SESSION['mvv_filter_module_abschluss_id'] = ''; + $this->sessSet('filter', []); $this->redirect($this->indexURL()); } @@ -1380,6 +1409,21 @@ class Module_ModuleController extends MVVController $selected_semester = $semesters->findOneBy('beginn', $this->filter['start_sem.beginn']); } + // Fach + $count_faecher = $this->countFaecher($modul_ids, $_SESSION['mvv_filter_module_fach_id'] ?? ''); + $template->set_attribute('faecher', SimpleORMapCollection::createFromArray( + Fach::findMany(array_keys($count_faecher)) + )->orderBy('name')); + $template->set_attribute('count_faecher', $count_faecher); + $template->set_attribute('selected_fach', $_SESSION['mvv_filter_module_fach_id'] ?? ''); + + // Abschluss + $count_abschluesse = $this->countAbschluesse($modul_ids, $_SESSION['mvv_filter_module_abschluss_id'] ?? ''); + $template->set_attribute('abschluesse', SimpleORMapCollection::createFromArray( + Abschluss::findMany(array_keys($count_abschluesse)) + )->orderBy('name')); + $template->set_attribute('count_abschluesse', $count_abschluesse); + $template->set_attribute('selected_abschluss', $_SESSION['mvv_filter_module_abschluss_id'] ?? ''); $template->set_attribute('semester', $semesters); $template->set_attribute('selected_semester', $selected_semester->id ?? ''); @@ -1432,4 +1476,102 @@ class Module_ModuleController extends MVVController ); $sidebar->addWidget($widget, 'search'); } + + /** + * Returns the number of modules grouped by subjects for given module ids. + * + * @param array $module_ids The ids of the modules. + * @param string $fach_id The id of the selected subject. + * @return array Number of modules grouped by subjects. + */ + private function countFaecher(array $module_ids, string $fach_id): array + { + if ($fach_id === '') { + $params = [':module_ids' => $module_ids]; + $where = "`mvv_stgteilabschnitt_modul`.`modul_id` IN (:module_ids)"; + } else { + $params = [ + ':fach_id' => $fach_id, + ':module_ids' => $module_ids + ]; + $where = "`fach`.`fach_id` = :fach_id + AND `mvv_stgteilabschnitt_modul`.`modul_id` IN (:module_ids)"; + } + $query = "SELECT `fach`.`fach_id`, COUNT(DISTINCT `modul_id`) AS `count_faecher` + FROM `fach` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_stgteilversion` USING (`stgteil_id`) + JOIN `mvv_stgteilabschnitt` USING (`version_id`) + JOIN `mvv_stgteilabschnitt_modul` USING (`abschnitt_id`) + WHERE {$where} + GROUP BY `fach_id`"; + return DBManager::get()->fetchPairs($query, $params); + } + + /** + * Returns the ids of the modules related to the given subject id. + * + * @param string $fach_id The id of the selected subject. + * @return array The ids of the modules related to the subject. + */ + private function findModuleIdsByFach(string $fach_id): array + { + $query = "SELECT `mvv_stgteilabschnitt_modul`.`modul_id` + FROM `mvv_stgteilabschnitt_modul` + JOIN `mvv_stgteilabschnitt` USING (`abschnitt_id`) + JOIN `mvv_stgteilversion` USING (`version_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = ?"; + return DBManager::get()->fetchFirst($query, [$fach_id]); + } + + /** + * Returns the number of modules grouped by degrees for given module ids. + * + * @param array $module_ids The ids of the modules. + * @param string $abschluss_id The id of the selected degree. + * @return array Number of modules grouped by degrees. + */ + private function countAbschluesse(array $module_ids, $abschluss_id): array + { + if ($abschluss_id === '') { + $params = [':module_ids' => $module_ids]; + $where = "`mvv_stgteilabschnitt_modul`.`modul_id` IN (:module_ids)"; + } else { + $params = [ + ':abschluss_ids' => $abschluss_id, + ':module_ids' => $module_ids + ]; + $where = "`abschluss`.`abschluss_id` = :abschluss_ids + AND `mvv_stgteilabschnitt_modul`.`modul_id` IN (:module_ids)"; + } + $query = "SELECT `abschluss`.`abschluss_id`, COUNT(DISTINCT `modul_id`) AS `count_abschluesse` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteilversion` USING (`stgteil_id`) + JOIN `mvv_stgteilabschnitt` USING (`version_id`) + JOIN `mvv_stgteilabschnitt_modul` USING (`abschnitt_id`) + WHERE {$where} + GROUP BY `abschluss_id`"; + return DBManager::get()->fetchPairs($query, $params); + } + + /** + * Returns the ids of the modules related to the given degree id. + * + * @param string $abschluss_id The id of the selected degree. + * @return array The ids of the modules related to the degree. + */ + private function findModuleIdsByAbschluss(string $abschluss_id): array + { + $query = "SELECT `mvv_stgteilabschnitt_modul`.`modul_id` + FROM `mvv_stgteilabschnitt_modul` + JOIN `mvv_stgteilabschnitt` USING (`abschnitt_id`) + JOIN `mvv_stgteilversion` USING (`version_id`) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + WHERE `mvv_studiengang`.`abschluss_id` = ?"; + return DBManager::get()->fetchFirst($query, [$abschluss_id]); + } } diff --git a/app/controllers/shared/contacts.php b/app/controllers/shared/contacts.php index a3da1a7..98670f4 100644 --- a/app/controllers/shared/contacts.php +++ b/app/controllers/shared/contacts.php @@ -64,8 +64,28 @@ class Shared_ContactsController extends MVVController if (empty($this->filter['mvv_modul_inst.institut_id'])) { unset($this->filter['mvv_modul_inst.institut_id']); } - if ($search_result) { - $this->filter['mvv_contacts.contact_id'] = $search_result; + + $contact_ids = []; + if (count($search_result) > 0) { + $contact_ids = $search_result; + } else { + if (!empty($_SESSION['mvv_filter_contacts_fach_id'])) { + $contact_ids = $this->findContactIdsByFach($_SESSION['mvv_filter_contacts_fach_id']); + } + if (!empty($_SESSION['mvv_filter_contacts_abschluss_id'])) { + if (count($contact_ids) > 0) { + $contact_ids = array_intersect( + $contact_ids, + $this->findContactIdsByAbschluss($_SESSION['mvv_filter_contacts_abschluss_id']) + ); + } else { + $contact_ids = $this->findContactIdsByAbschluss($_SESSION['mvv_filter_contacts_abschluss_id']); + } + } + } + + if (count($contact_ids) > 0) { + $this->filter['mvv_contacts.contact_id'] = $contact_ids; } $own_institutes = MvvPerm::getOwnInstitutes(); @@ -241,6 +261,12 @@ class Shared_ContactsController extends MVVController $this->filter['mvv_contacts_ranges.range_type'] = Request::option('zuordnung_filter'); } + // Fach + $_SESSION['mvv_filter_contacts_fach_id'] = Request::option('fach_filter', ''); + + // Abschluss + $_SESSION['mvv_filter_contacts_abschluss_id'] = Request::option('abschluss_filter', ''); + // store filter $this->reset_page(); $this->sessSet('filter', $this->filter); @@ -249,6 +275,9 @@ class Shared_ContactsController extends MVVController public function reset_filter_action() { + $_SESSION['mvv_filter_contacts_fach_id'] = ''; + $_SESSION['mvv_filter_contacts_abschluss_id'] = ''; + $this->filter = []; $this->reset_search(); $this->sessRemove('filter'); @@ -346,6 +375,9 @@ class Shared_ContactsController extends MVVController $this->filter ); + $contact_ids = MvvContact::getIdsFiltered($this->filter); + $count_faecher = $this->countFaecher($contact_ids, $_SESSION['mvv_filter_contacts_fach_id'] ?? ''); + $count_abschluesse = $this->countAbschluesse($contact_ids, $_SESSION['mvv_filter_contacts_abschluss_id'] ?? ''); $semesters = new SimpleCollection(array_reverse(Semester::getAll())); $filter_template = $template_factory->render('shared/filter', [ 'semester' => $semesters, @@ -365,6 +397,16 @@ class Shared_ContactsController extends MVVController 'status' => $this->findStatusByIds(), 'selected_status' => $this->filter['mvv_contacts.contact_status'] ?? '', 'status_array' => ['intern' => ['name' => _('Intern')], 'extern' => ['name' =>_('Extern')]], + 'faecher' => SimpleORMapCollection::createFromArray( + Fach::findMany(array_keys($count_faecher)) + )->orderBy('name'), + 'count_faecher' => $count_faecher, + 'selected_fach' => $_SESSION['mvv_filter_contacts_fach_id'] ?? '', + 'abschluesse' => SimpleORMapCollection::createFromArray( + Abschluss::findMany(array_keys($count_abschluesse)) + )->orderBy('name'), + 'count_abschluesse' => $count_abschluesse, + 'selected_abschluss' => $_SESSION['mvv_filter_contacts_abschluss_id'] ?? '', 'action' => $this->action_url('set_filter'), 'action_reset' => $this->action_url('reset_filter') ]); @@ -991,4 +1033,231 @@ class Shared_ContactsController extends MVVController } return $result; } + + /** + * Returns the number of contacts grouped by subjects for given contact ids. + * + * @param array $contact_ids The ids of the contacts. + * @param string $fach_id The id of the selected subject. + * @return array Number of contacts grouped by subjects. + */ + private function countFaecher(array $contact_ids, string $fach_id): array + { + if ($fach_id === '') { + $params = [':contact_ids' => $contact_ids]; + $where = "`mvv_contacts_ranges`.`contact_id` IN (:contact_ids)"; + } else { + $params = [ + ':fach_id' => $fach_id, + ':contact_ids' => $contact_ids + ]; + $where = "`fach`.`fach_id` = :fach_id + AND `mvv_contacts_ranges`.`contact_id` IN (:contact_ids)"; + } + $query = "SELECT `counting`.`fach_id`, COUNT(DISTINCT `counting`.`contact_id`) + FROM ( + SELECT `fach_id`, `contact_id` + FROM `fach` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_contacts_ranges` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_stgteil`.`stgteil_id` + AND `mvv_contacts_ranges`.`range_type` = 'StudiengangTeil' + ) + WHERE {$where} + + UNION ALL + + SELECT `fach_id`, `contact_id` + FROM `fach` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + JOIN `mvv_contacts_ranges` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_studiengang`.`studiengang_id` + AND `mvv_contacts_ranges`.`range_type` = 'Studiengang' + ) + WHERE {$where} + + UNION ALL + + SELECT `fach_id`, `contact_id` + FROM `fach` + JOIN `mvv_stgteil` USING (`fach_id`) + JOIN `mvv_stgteilversion` USING (`stgteil_id`) + JOIN `mvv_stgteilabschnitt` USING (`version_id`) + JOIN `mvv_stgteilabschnitt_modul` USING (`abschnitt_id`) + JOIN `mvv_contacts_ranges` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_stgteilabschnitt_modul`.`modul_id` + AND `mvv_contacts_ranges`.`range_type` = 'Modul' + ) + WHERE {$where} + ) AS `counting` + GROUP BY `counting`.`fach_id`"; + return DBManager::get()->fetchPairs($query, $params); + } + + /** + * Returns the ids of the contacts related to the given subject id. + * + * @param string $fach_id The id of the selected subject. + * @return array The ids of the contacts related to the subject. + */ + private function findContactIdsByFach(string $fach_id): array + { + $query = "SELECT `mvv_contacts_ranges`.`contact_id` + FROM `mvv_contacts_ranges` + JOIN `mvv_stgteilabschnitt_modul` + ON ( + `mvv_stgteilabschnitt_modul`.`modul_id` = `mvv_contacts_ranges`.`range_id` + AND `mvv_contacts_ranges`.`range_type` = 'Modul' + ) + JOIN `mvv_stgteilabschnitt` USING (`abschnitt_id`) + JOIN `mvv_stgteilversion` USING (`version_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = :fach_id + + UNION + + SELECT `mvv_contacts_ranges`.`contact_id` + FROM `mvv_contacts_ranges` + JOIN `mvv_studiengang` + ON ( + `mvv_studiengang`.`studiengang_id` = `mvv_contacts_ranges`.`range_id` + AND `mvv_contacts_ranges`.`range_type` = 'Studiengang' + ) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + WHERE `mvv_stgteil`.`fach_id` = :fach_id + + UNION + + SELECT `mvv_contacts_ranges`.`contact_id` + FROM `mvv_contacts_ranges` + JOIN `mvv_stgteil` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_stgteil`.`stgteil_id` + AND `mvv_contacts_ranges`.`range_type` = 'StudiengangTeil' + ) + WHERE `mvv_stgteil`.`fach_id` = :fach_id"; + + return DBManager::get()->fetchFirst($query, [':fach_id' => $fach_id]); + } + + /** + * Returns the number of contacts grouped by degrees for given contact ids. + * + * @param array $contact_ids The ids of the contacts. + * @param string $abschluss_id The id of the selected degree. + * @return array Number of contacts grouped by degrees. + */ + private function countAbschluesse(array $contact_ids, string $abschluss_id): array + { + if ($abschluss_id === '') { + $params = [':contact_ids' => $contact_ids]; + $where = "`mvv_contacts_ranges`.`contact_id` IN (:contact_ids)"; + } else { + $params = [ + ':abschluss_id' => $abschluss_id, + ':contact_ids' => $contact_ids + ]; + $where = "`abschluss`.`abschluss_id` = :abschluss_id + AND `mvv_contacts_ranges`.`contact_id` IN (:contact_ids)"; + } + $query = "SELECT `counting`.`abschluss_id`, COUNT(DISTINCT `counting`.`contact_id`) + FROM ( + SELECT `abschluss_id`, `contact_id` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteil` USING (`stgteil_id`) + JOIN `mvv_contacts_ranges` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_stgteil`.`stgteil_id` + AND `mvv_contacts_ranges`.`range_type` = 'StudiengangTeil' + ) + WHERE {$where} + + UNION ALL + + SELECT `abschluss_id`, `contact_id` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_contacts_ranges` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_studiengang`.`studiengang_id` + AND `mvv_contacts_ranges`.`range_type` = 'Studiengang' + ) + WHERE {$where} + + UNION ALL + + SELECT `abschluss_id`, `contact_id` + FROM `abschluss` + JOIN `mvv_studiengang` USING (`abschluss_id`) + JOIN `mvv_stg_stgteil` USING (`studiengang_id`) + JOIN `mvv_stgteilversion` USING (`stgteil_id`) + JOIN `mvv_stgteilabschnitt` USING (`version_id`) + JOIN `mvv_stgteilabschnitt_modul` USING (`abschnitt_id`) + JOIN `mvv_contacts_ranges` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_stgteilabschnitt_modul`.`modul_id` + AND `mvv_contacts_ranges`.`range_type` = 'Modul' + ) + WHERE {$where} + ) AS `counting` + GROUP BY `counting`.`abschluss_id`"; + + return DBManager::get()->fetchPairs($query, $params); + } + + /** + * Returns the ids of the contacts related to the given degree id. + * + * @param string $abschluss_id The id of the selected degree. + * @return array The ids of the contacts related to the degree. + */ + private function findContactIdsByAbschluss(string $abschluss_id): array + { + $query = "SELECT `mvv_contacts_ranges`.`contact_id` + FROM `mvv_contacts_ranges` + JOIN `mvv_stgteilabschnitt_modul` + ON ( + `mvv_stgteilabschnitt_modul`.`modul_id` = `mvv_contacts_ranges`.`range_id` + AND `mvv_contacts_ranges`.`range_type` = 'Modul' + ) + JOIN `mvv_stgteilabschnitt` USING (`abschnitt_id`) + JOIN `mvv_stgteilversion` USING (`version_id`) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + WHERE `mvv_studiengang`.`abschluss_id` = :abschluss_id + + UNION + + SELECT `mvv_contacts_ranges`.`contact_id` + FROM `mvv_contacts_ranges` + JOIN `mvv_studiengang` + ON ( + `mvv_studiengang`.`studiengang_id` = `mvv_contacts_ranges`.`range_id` + AND `mvv_contacts_ranges`.`range_type` = 'Studiengang' + ) + WHERE `mvv_studiengang`.`abschluss_id` = :abschluss_id + + UNION + + SELECT `mvv_contacts_ranges`.`contact_id` + FROM `mvv_contacts_ranges` + JOIN `mvv_stgteil` + ON ( + `mvv_contacts_ranges`.`range_id` = `mvv_stgteil`.`stgteil_id` + AND `mvv_contacts_ranges`.`range_type` = 'StudiengangTeil' + ) + JOIN `mvv_stg_stgteil` USING (`stgteil_id`) + JOIN `mvv_studiengang` USING (`studiengang_id`) + WHERE `mvv_studiengang`.`abschluss_id` = :abschluss_id"; + + return DBManager::get()->fetchFirst($query, [':abschluss_id' => $abschluss_id]); + } } diff --git a/app/controllers/studiengaenge/studiengaenge.php b/app/controllers/studiengaenge/studiengaenge.php index e0dec62..d053f48 100644 --- a/app/controllers/studiengaenge/studiengaenge.php +++ b/app/controllers/studiengaenge/studiengaenge.php @@ -975,8 +975,9 @@ class Studiengaenge_StudiengaengeController extends MVVController if (mb_strlen(Request::get('kategorie_filter'))) { $this->filter['mvv_abschl_zuord.kategorie_id'] = Request::option('kategorie_filter'); } - if (mb_strlen(Request::get('kategorie_filter'))) { - $this->filter['mvv_abschl_zuord.kategorie_id'] = Request::option('kategorie_filter'); + // Fach + if (mb_strlen(Request::get('fach_filter'))) { + $this->filter['mvv_stgteil.fach_id'] = Request::option('fach_filter'); } // Verantwortliche Einrichtung if (mb_strlen(Request::get('institut_filter'))) { @@ -1057,6 +1058,7 @@ class Studiengaenge_StudiengaengeController extends MVVController $semesters = $semesters->orderBy('beginn desc'); $selected_semester = $semesters->findOneBy('beginn', $this->filter['start_sem.beginn']); + $count_faecher = $this->countFaecher($studiengang_ids, $this->filter['mvv_stgteil.fach_id'] ?? ''); $filter_template = $template_factory->render('shared/filter', [ 'semester' => $semesters, 'selected_semester' => $selected_semester ? $selected_semester->id : '', @@ -1074,6 +1076,11 @@ class Studiengaenge_StudiengaengeController extends MVVController 'selected_institut' => $this->filter['mvv_studiengang.institut_id'], 'fachbereiche' => Fach::getAllAssignedInstitutes($studiengang_ids), 'selected_fachbereich' => $this->filter['mvv_fach_inst.institut_id'] ?? '', + 'faecher' => SimpleORMapCollection::createFromArray( + Fach::findMany(array_keys($count_faecher)) + )->orderBy('name'), + 'selected_fach' => $this->filter['mvv_stgteil.fach_id'] ?? '', + 'count_faecher' => $count_faecher, 'action' => $this->action_url('set_filter'), 'action_reset' => $this->action_url('reset_filter') ]); @@ -1130,6 +1137,35 @@ class Studiengaenge_StudiengaengeController extends MVVController } + /** + * Returns the number of study courses grouped by subjects for given study cours ids. + * + * @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. + */ + private function countFaecher(array $studiengang_ids, string $fach_id): array + { + if ($fach_id === '') { + $params = [':studiengang_ids' => $studiengang_ids]; + $where = "`mvv_stg_stgteil`.`studiengang_id` IN (:studiengang_ids)"; + } else { + $params = [ + ':fach_id' => $fach_id, + ':studiengang_ids' => $studiengang_ids + ]; + $where = "`fach`.`fach_id` = :fach_id + AND `mvv_stg_stgteil`.`studiengang_id` IN (:studiengang_ids)"; + } + $query = "SELECT `fach`.`fach_id`, COUNT(DISTINCT `studiengang_id`) AS `count_faecher` + FROM `fach` + JOIN `mvv_stgteil` USING(`fach_id`) + JOIN `mvv_stg_stgteil` USING(`stgteil_id`) + WHERE {$where} + GROUP BY `fach_id`"; + return DBManager::get()->fetchPairs($query, $params); + } + public function approve_action($studiengang_id) { $this->studiengang_id = $studiengang_id; diff --git a/app/views/shared/filter.php b/app/views/shared/filter.php index 6674ef3..2b1e027 100644 --- a/app/views/shared/filter.php +++ b/app/views/shared/filter.php @@ -1,4 +1,16 @@ - +
_('Filter zurücksetzen')])->asImg(); ?> @@ -9,14 +21,14 @@