AdminCourseFilter.class.php

<?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'] = "(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;
    }

}