current code from svn, revision 62608

1 files changed, 443 insertions, 0 deletions

diff --git a/lib/classes/AdminCourseFilter.class.php b/lib/classes/AdminCourseFilter.class.php
new file mode 100644
index 0000000..ea0de9a
--- /dev/null
+++ b/lib/classes/AdminCourseFilter.class.php
@@ -0,0 +1,443 @@
+<?php
+
+/**
+ * Class AdminCourseFilter
+ *
+ * The main class to filter all courses for admins. It's a singleton class, so you
+ * better call it with AdminCourseFilter::get(). The whole class is created to
+ * provide a nice hook for plugins to add special filters into the admin-area of
+ * Stud.IP.
+ *
+ * To add a filter with a plugin, listen to the notification "AdminCourseFilterWillQuery"
+ * like this:
+ *
+ *     NotificationCenter::addObserver($this, "addMyFilter", "AdminCourseFilterWillQuery");
+ *
+ * Where $this is an object and "addMyFilter" a method. Such a method might look like this:
+ *
+ *     public function addLectureshipFilter($event, $filter)
+ *     {
+ *         if ($GLOBALS['user']->cfg->getValue("LECTURESHIP_FILTER")) {
+ *             $filter->settings['query']['joins']['lehrauftrag'] = array(
+ *                 'join' => "INNER JOIN",
+ *                 'on' => "seminare.Seminar_id = lehrauftrag.seminar_id"
+ *             );
+ *         }
+ *     }
+ *
+ * Within this method you alter the public $filter->settings array, because this array
+ * describes entirely the big query for the admin-search. In our example above
+ * we simple add an INNER JOIN to filter for the course having an entry in
+ * the lehrauftrag table.
+ *
+ * Description of this array is as follows:
+ *
+ * $filter->settings['query']            : The main sql query as a prepared statement.
+ * $filter->settings['query']['select']  : An assoc array. $filter->settings['query']['select']['Number_of_teachers'] = "COUNT(DISTINCT dozenten.user_id)"
+ *                                         will select the result of COUNT as the variable Number_of_teachers.
+ * $filter->settings['query']['joins']   : Example $filter->settings['query']['joins']['dozenten'] = array(
+ *                                          'join' => "INNER JOIN", //default value, else use "LEFT JOIN"
+ *                                          'table' => "seminar_user", //can me omitted if you don't want to use a table-alias
+ *                                          'on' => "dozenten.Seminar_id = seminare.Seminar_id AND dozenten.status = 'dozent'"
+ *                                          )
+ *                                         if 'table' differs from the index, the index will be the alias of the table.
+ *                                         So normally you don't need to name a table if you don't want it to be aliased.
+ * $filter->settings['query']['where']   : You might want to use the method $filter->where($sql, $parameter) instead.
+ * $filter->settings['query']['orderby'] : You might want to use $filter->orderBy($attribute, $flag = "ASC") instead.
+ * $filter->settings['parameter']        : An assoc array of parameter that will be passed to
+ *                                         the prepared statement.
+ *
+ */
+class AdminCourseFilter
+{
+    static protected $instance = null;
+    public $max_show_courses = 500;
+    public $settings = [];
+
+    /**
+     * returns an AdminCourseFilter singleton object
+     * @return AdminCourseFilter or derived-class object
+     */
+    static public function get($reset_settings = false)
+    {
+        if (!self::$instance) {
+            $class = get_called_class();
+            self::$instance = new $class($reset_settings);
+        }
+        return self::$instance;
+    }
+
+    /**
+     * Constructor of the singleton-object. The settings might come from the session
+     * if $reset_settings is false.
+     * @param bool $reset_settings : should the session settings of the singleton be reset?
+     */
+    public function __construct($reset_settings = false)
+    {
+        $this->initSettings();
+
+        if ($reset_settings) {
+            $this->resetSettings();
+        } else {
+            $this->restoreSettings();
+        }
+    }
+
+    /**
+     * store settings in session
+     */
+    public function storeSettings()
+    {
+        $_SESSION['AdminCourseFilter_settings'] = $this->settings;
+    }
+
+    /**
+     * restore settings from session
+     */
+    public function restoreSettings()
+    {
+        if ($_SESSION['AdminCourseFilter_settings']) {
+            $this->settings = $_SESSION['AdminCourseFilter_settings'];
+        }
+    }
+
+    /**
+     * reset settings
+     */
+    public function resetSettings()
+    {
+        $this->initSettings();
+        unset($_SESSION['AdminCourseFilter_settings']);
+    }
+
+    /**
+     * initialize settings
+     */
+    public function initSettings()
+    {
+        $this->settings = [];
+
+        $this->settings['query']['select'] = [
+            'Institut' => "Institute.Name",
+            'teilnehmer' => "(SELECT COUNT(seminar_id)
+                          FROM seminar_user
+                          WHERE seminar_id = seminare.Seminar_id AND status != 'dozent' AND status != 'tutor')",
+            'prelim' => "(SELECT COUNT(seminar_id)
+                          FROM admission_seminar_user
+                          WHERE seminar_id = seminare.Seminar_id AND status = 'accepted')",
+            'waiting' => "(SELECT COUNT(seminar_id)
+                          FROM admission_seminar_user
+                          WHERE seminar_id = seminare.Seminar_id AND status = 'awaiting')",
+            'requests' => "(SELECT COUNT(id)
+                          FROM resource_requests
+                          WHERE course_id = seminare.Seminar_id)",
+            'course_set' => "(SELECT set_id FROM seminar_courseset WHERE seminar_id = seminare.Seminar_id LIMIT 1)"
+        ];
+        $this->settings['query']['joins'] = [
+            'seminar_inst' => [
+                'join' => "INNER JOIN",
+                'on' => "seminare.Seminar_id = seminar_inst.seminar_id"
+            ],
+            'Institute' => [
+                'join' => "INNER JOIN",
+                'on' => "seminar_inst.institut_id = Institute.Institut_id"
+            ],
+            'sem_types' => [
+                'join' => "LEFT JOIN",
+                'on' => "sem_types.id = seminare.status"
+            ],
+            'sem_classes' => [
+                'join' => "LEFT JOIN",
+                'on' => "sem_classes.id = sem_types.class"
+            ]
+        ];
+        $this->settings['query']['where'] = [];
+        $this->settings['query']['orderby'] = Config::get()->IMPORTANT_SEMNUMBER ? "seminare.veranstaltungsnummer, seminare.name" : "seminare.name";
+    }
+
+    /**
+     * Adds a filter for all courses of the given semester.
+     * @param string $semester_id : ID of the given semester.
+     * @return $this
+     * @throws Exception if semester_id does not exist
+     */
+    public function filterBySemester($semester_id)
+    {
+        $semester = Semester::find($semester_id);
+        if (!$semester) {
+            throw new Exception("Das ausgewählte Semester scheint nicht zu existieren.");
+        }
+        $this->settings['query']['joins']['semester_courses'] = [
+            'join' => "LEFT JOIN",
+            'on' => "semester_courses.course_id = seminare.Seminar_id"
+        ];
+        $this->settings['query']['where']['semester'] = "(seminare.start_time <= :semester_beginn AND (semester_courses.semester_id IS NULL OR semester_courses.semester_id = :semester_id))";
+        $this->settings['parameter']['semester_beginn'] = $semester['beginn'];
+        $this->settings['parameter']['semester_id'] = $semester['id'];
+        return $this;
+    }
+
+    /**
+     * Adds a filter for a sem_type or many sem_types if the parameter is an array.
+     * @param array|integer $type : id or ids of sem_types
+     * @return $this
+     */
+    public function filterByType($type)
+    {
+        if (is_array($type)) {
+            $this->settings['query']['where']['status'] = "seminare.status IN (:types)";
+            $this->settings['parameter']['types'] = $type;
+        } else {
+            $this->settings['query']['where']['status'] = "seminare.status = :type";
+            $this->settings['parameter']['type'] = (int) $type;
+        }
+        return $this;
+    }
+
+    /**
+     * Adds a filter for an institut_id or many institut_ids if the parameter is an array.
+     * @param array|integer $institut_ids : id or ids of institutes
+     * @return $this
+     */
+    public function filterByInstitute($institut_ids)
+    {
+        if (Config::get()->ALLOW_ADMIN_RELATED_INST) {
+            $sem_inst = 'seminar_inst';
+        } else {
+            $sem_inst = 'seminare';
+        }
+
+        if (is_array($institut_ids)) {
+            $this->settings['query']['where']['institute'] = "$sem_inst.institut_id IN (:institut_ids)";
+            $this->settings['parameter']['institut_ids'] = $institut_ids;
+        } else {
+            $this->settings['query']['where']['status'] = "$sem_inst.institut_id = :institut_id";
+            $this->settings['parameter']['institut_id'] = (string) $institut_ids;
+        }
+        return $this;
+    }
+
+    /**
+     * Adds a filter for an stgteil_id or many stgteil_ids if the parameter is an array.
+     * @param array|integer $stgteil_ids : id or ids of stgteile
+     * @return $this
+     */
+    public function filterByStgTeil($stgteil_ids)
+    {
+        $this->settings['query']['joins']['mvv_lvgruppe_seminar'] = [
+            'join' => "LEFT JOIN",
+            'table' => "mvv_lvgruppe_seminar",
+            'on' => "mvv_lvgruppe_seminar.seminar_id = seminare.Seminar_id"
+        ];
+        $this->settings['query']['joins']['mvv_lvgruppe_modulteil'] = [
+            'join' => "LEFT JOIN",
+            'table' => "mvv_lvgruppe_modulteil",
+            'on' => "mvv_lvgruppe_modulteil.lvgruppe_id = mvv_lvgruppe_seminar.lvgruppe_id"
+        ];
+        $this->settings['query']['joins']['mvv_modulteil'] = [
+            'join' => "LEFT JOIN",
+            'table' => "mvv_modulteil",
+            'on' => "mvv_modulteil.modulteil_id = mvv_lvgruppe_modulteil.modulteil_id"
+        ];
+        $this->settings['query']['joins']['mvv_stgteilabschnitt_modul'] = [
+            'join' => "LEFT JOIN",
+            'table' => "mvv_stgteilabschnitt_modul",
+            'on' => "mvv_stgteilabschnitt_modul.modul_id = mvv_modulteil.modul_id"
+        ];
+        $this->settings['query']['joins']['mvv_stgteilabschnitt'] = [
+            'join' => "LEFT JOIN",
+            'table' => "mvv_stgteilabschnitt",
+            'on' => "mvv_stgteilabschnitt.abschnitt_id = mvv_stgteilabschnitt_modul.abschnitt_id"
+        ];
+        $this->settings['query']['joins']['mvv_stgteilversion'] = [
+            'join' => "LEFT JOIN",
+            'table' => "mvv_stgteilversion",
+            'on' => "mvv_stgteilversion.version_id = mvv_stgteilabschnitt.version_id"
+        ];
+
+        if (is_array($stgteil_ids)) {
+            $this->settings['query']['where']['mvv_stgteilversion'] = "mvv_stgteilversion.stgteil_id IN (:stgteil_ids)";
+            $this->settings['parameter']['stgteil_ids'] = $stgteil_ids;
+        } else {
+            $this->settings['query']['where']['mvv_stgteilversion'] = "mvv_stgteilversion.stgteil_id = :stgteil_id";
+            $this->settings['parameter']['stgteil_id'] = (string) $stgteil_ids;
+        }
+        return $this;
+    }
+
+    public function filterByDozent($user_ids)
+    {
+        $this->settings['query']['joins']['dozenten'] = [
+            'join' => "INNER JOIN",
+            'table' => "seminar_user",
+            'on' => "dozenten.Seminar_id = seminare.Seminar_id AND dozenten.status = 'dozent'"
+        ];
+        if (is_array($user_ids)) {
+            $this->settings['query']['where']['dozenten'] = "dozenten.user_id IN (:dozenten_ids)";
+            $this->settings['parameter']['dozenten_ids'] = $user_ids;
+        } else {
+            $this->settings['query']['where']['dozenten'] = "dozenten.user_id = :dozenten_id";
+            $this->settings['parameter']['dozenten_id'] = (string) $user_ids;
+        }
+        return $this;
+    }
+
+    /**
+     * Adds a filter for a textstring, that can be the coursenumber, the name of the course
+     * or the last name of one of the dozenten.
+     * @param string $text : the searchstring
+     * @return $this
+     */
+    public function filterBySearchstring($text)
+    {
+        $this->settings['query']['joins']['dozenten'] = [
+            'join' => "INNER JOIN",
+            'table' => "seminar_user",
+            'on' => "dozenten.Seminar_id = seminare.Seminar_id AND dozenten.status = 'dozent'"
+        ];
+        $this->settings['query']['joins']['dozentendata'] = [
+            'join' => "INNER JOIN",
+            'table' => "auth_user_md5",
+            'on' => "dozenten.user_id = dozentendata.user_id"
+        ];
+        $this->settings['query']['where']['search'] = "(CONCAT_WS(' ', seminare.VeranstaltungsNummer, seminare.name, seminare.Untertitel, dozentendata.Nachname) LIKE :search
+            OR CONCAT(dozentendata.Nachname, ', ', dozentendata.Vorname) LIKE :search
+            OR CONCAT_WS(' ', dozentendata.Vorname, dozentendata.Nachname) LIKE :search
+            OR dozentendata.Vorname LIKE :search
+            OR dozentendata.Nachname LIKE :search
+        )";
+        $this->settings['parameter']['search'] = "%".$text."%";
+
+        return $this;
+    }
+
+    /**
+     * @param string $attribute : column, name of the column, yb whcih we should order the results
+     * @param string $flag : "ASC" or "DESC for ascending order or descending order,
+     * @return $this
+     * @throws Exception if $flag does not exist
+     */
+    public function orderBy($attribute, $flag = 'ASC')
+    {
+        $flag = mb_strtoupper($flag);
+        if (!in_array($flag, words('ASC DESC'))) {
+            throw new Exception("Sortierreihenfolge undefiniert.");
+        }
+        if (in_array($attribute, words('VeranstaltungsNummer Name status teilnehmer waiting prelim requests completion start_time'))) {
+            $this->settings['query']['orderby'] = $attribute . ' ' . $flag;
+        }
+        return $this;
+    }
+
+    /**
+     * Adds a where filter.
+     * @param string $where : any where condition like "sem_classes.overview = 'CoreOverview'"
+     * @param array $parameter : an array of parameter that appear in the $where query.
+     * @param null|string $id : an id of the where-query. Use this to possibly
+     *                          avoid double where conditions or allow deleting the condition
+     *                          by plugins if necessary. Can be omitted.
+     * @return $this
+     */
+    public function where($where, $parameter = [], $id = null)
+    {
+        if (!$id) {
+            $id = md5($where);
+        }
+        $this->settings['query']['where'][$id] = $where;
+        $this->settings['parameter'] = array_merge((array) $this->settings['parameter'], $parameter);
+        return $this;
+    }
+
+    /**
+     * Returns the data of the resultset of the AdminCourseFilter.
+     * Also saves the settings in the session.
+     * Note that a notification AdminCourseFilterWillQuery will be posted, before the result is computed.
+     * Plugins may register at this event to fully alter this AdminCourseFilter-object and so the resultset.
+     * @return array : associative array with seminar_ids as keys and seminar-data-arrays as values.
+     */
+    public function getCourses($grouped = true)
+    {
+        NotificationCenter::postNotification("AdminCourseFilterWillQuery", $this);
+        if (empty($this->settings['query']['where'])) {
+            return [];
+        }
+        $statement = DBManager::get()->prepare($this->createQuery());
+        $statement->execute($this->settings['parameter']);
+        $_SESSION['AdminCourseFilter_settings'] = $this->settings;
+        return $statement->fetchAll($grouped ? (PDO::FETCH_GROUP | PDO::FETCH_ASSOC) : PDO::FETCH_ASSOC);
+    }
+
+    /**
+     * @return number of courses that this filter would return
+     */
+    public function countCourses()
+    {
+        NotificationCenter::postNotification("AdminCourseFilterWillQuery", $this);
+        if (empty($this->settings['query']['where'])) {
+            return 0;
+        }
+        return DBManager::get()->fetchColumn($this->createQuery(true), $this->settings['parameter']);
+    }
+
+    /**
+     * Returns the data of the resultset of the AdminCourseFilter.
+     *
+     * Note that a notification AdminCourseFilterWillQuery will be posted, before the result is computed.
+     * Plugins may register at this event to fully alter this AdminCourseFilter-object and so the resultset.
+     * @return array : associative array with seminar_ids as keys and seminar-data-arrays as values.
+     */
+    public function getCoursesForAdminWidget()
+    {
+        $count_courses = $this->countCourses();
+        if ($count_courses && $count_courses <= $this->max_show_courses) {
+            $settings = $this->settings;
+            $this->settings['query']['select'] = [];
+            $this->settings['query']['orderby'] = Config::get()->IMPORTANT_SEMNUMBER ? 'seminare.veranstaltungsnummer, seminare.name' : 'seminare.name';
+            $ret = $this->getCourses(false);
+            $this->settings = $settings;
+            return $ret;
+        }
+        return [];
+    }
+
+    /**
+     * Creates the sql-query from the $this->settings['query']
+     * @only_count : boolean
+     * @return string : the big query
+     */
+    public function createQuery($only_count = false)
+    {
+        if ($only_count) {
+            $select_query = "COUNT(DISTINCT seminare.Seminar_id) ";
+        } else {
+            $select_query = "seminare.* ";
+            foreach ((array) $this->settings['query']['select'] as $alias => $select) {
+                $select_query .= ", ".$select." AS ".$alias." ";
+            }
+        }
+
+        $join_query = "";
+        foreach ((array) $this->settings['query']['joins'] as $alias => $joininfo) {
+            $table = isset($joininfo['table']) ? $joininfo['table']." AS ".$alias : $alias;
+            $on = isset($joininfo['on']) ? " ON (".$joininfo['on'].")" : "";
+            $join_query .= " ".(isset($joininfo['join']) ? $joininfo['join'] : "INNER JOIN")." ".$table.$on." ";
+        }
+
+        $where_query = "";
+        if (count($this->settings['query']['where']) > 0) {
+            $where_query .= implode(" AND ", $this->settings['query']['where']);
+        }
+
+        $query = "
+            SELECT ".$select_query."
+            FROM seminare
+                ".$join_query."
+            ".($where_query ? "WHERE ".$where_query : "");
+        if (!$only_count) {
+            $query .= " GROUP BY seminare.Seminar_id ORDER BY ".$this->settings['query']['orderby'].($this->settings['query']['orderby'] !== "seminare.name" ? ", seminare.name" : "");
+        }
+
+        return $query;
+    }
+
+}