path: root/lib/models/SeminarCycleDate.php

<?php


//Do not remove! This is still needed for the toString method!
require_once 'lib/dates.inc.php';


/**
 * SeminarCycleDate.php
 * model class for table seminar_cycle_dates
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License as
 * published by the Free Software Foundation; either version 2 of
 * the License, or (at your option) any later version.
 *
 * @author      André Noack <noack@data-quest.de>
 * @license     http://www.gnu.org/licenses/gpl-2.0.html GPL version 2
 * @category    Stud.IP
 * @since       2.0
 *
 * @property string $id alias column for metadate_id
 * @property string $metadate_id database column
 * @property string $seminar_id database column
 * @property string $start_time database column
 * @property string $end_time database column
 * @property int $weekday database column
 * @property string $description database column
 * @property float $sws database column
 * @property int $cycle database column
 * @property int $week_offset database column
 * @property int|null $end_offset database column
 * @property int $sorter database column
 * @property int $mkdate database column
 * @property int $chdate database column
 * @property SimpleORMapCollection<RoomRequest> $room_requests has_many RoomRequest
 * @property SimpleORMapCollection<CourseDate> $dates has_many CourseDate
 * @property SimpleORMapCollection<CourseExDate> $exdates has_many CourseExDate
 * @property Course $course belongs_to Course
 * @property mixed $start_hour additional field
 * @property mixed $start_minute additional field
 * @property mixed $end_hour additional field
 * @property mixed $end_minute additional field
 * @property-read mixed $is_visible additional field
 */
class SeminarCycleDate extends SimpleORMap
{
    /**
     * The booking status of the regular date cannot be determined
     */
    const BOOKING_STATUS_UNDEFINED        = 0;

    /**
     * None of the single dates of the regular date is booked.
     */
    const BOOKING_STATUS_NOT_BOOKED       = 1;

    /**
     * Only a part (at least one) of the single dates of the regular dates is booked.
     */
    const BOOKING_STATUS_PARTIALLY_BOOKED = 2;

    /**
     * All single dates of the regular date are booked.
     */
    const BOOKING_STATUS_ALL_BOOKED       = 3;


    /**
     * Configures this model.
     *
     * @param Array $config Configuration array
     */
    protected static function configure($config = [])
    {
        $config['db_table'] = 'seminar_cycle_dates';
        $config['belongs_to']['course'] = array(
            'class_name' => Course::class,
            'foreign_key' => 'seminar_id',
        );
        $config['has_many']['room_requests'] = [
            'class_name' => RoomRequest::class,
            'on_store'   => 'store',
            'on_delete'  => 'delete',
            'assoc_func' => 'findByMetadate_id'
        ];
        $config['has_many']['dates'] = [
            'class_name' => CourseDate::class,
            'on_delete'  => 'delete',
            'on_store'   => 'store',
            'order_by'   => 'ORDER BY date'
        ];

        $config['has_many']['exdates'] = [
            'class_name' => CourseExDate::class,
            'on_delete'  => 'delete',
            'on_store'   => 'store',
            'order_by'   => 'ORDER BY date'
        ];

        $config['additional_fields']['start_hour'] = ['get' => 'getTimeFraction', 'set' => 'setTimeFraction'];
        $config['additional_fields']['start_minute'] = ['get' => 'getTimeFraction', 'set' => 'setTimeFraction'];
        $config['additional_fields']['end_hour'] = ['get' => 'getTimeFraction', 'set' => 'setTimeFraction'];
        $config['additional_fields']['end_minute'] = ['get' => 'getTimeFraction', 'set' => 'setTimeFraction'];
        $config['additional_fields']['is_visible'] = ['get' => 'getIsVisible'];
        parent::configure($config);
    }

    /**
     * returns array of instances of SeminarCycleDates of the given seminar_id
     *
     * @param string seminar_id: selected seminar to search for SeminarCycleDates
     * @return array of instances of SeminarCycleDates of the given seminar_id or
     *               an empty array
     */
    public static function findBySeminar($seminar_id)
    {
        return self::findBySeminar_id($seminar_id, "ORDER BY sorter ASC, weekday ASC, start_time ASC");
    }

    /**
     * return instance of SeminarCycleDates of given termin_id
     *
     * @param string termin_id: selected seminar to search for SeminarCycleDates
     * @return array
     */
    public static function findByTermin($termin_id)
    {
        return self::findOneBySql("metadate_id=(SELECT metadate_id FROM termine WHERE termin_id = ? "
                                  . "UNION SELECT metadate_id FROM ex_termine WHERE termin_id = ? )", [$termin_id, $termin_id]);
    }
    /**
     * Returns the time fraction for a given field.
     *
     * @param String $field Time fraction field
     * @return int the time fraction
     */
    protected function getTimeFraction($field)
    {
        if (in_array($field, ['start_hour', 'start_minute'])) {
            list($start_hour, $start_minute) = explode(':', $this->start_time);
            return (int)$$field;
        }
        if (in_array($field, ['end_hour', 'end_minute'])) {
            list($end_hour, $end_minute) = explode(':', $this->end_time);
            return (int)$$field;
        }

        throw new InvalidArgumentException("Invalid field {$field}");
    }

    /**
     * Sets the time fraction for a given field.
     *
     * @param String $field Time fraction field
     * @param mixed  $value Time fraction value as string or int
     * @return String containing the time fraction
     */
    protected function setTimeFraction($field, $value)
    {
        if ($field == 'start_hour') {
            $this->start_time = sprintf('%02u:%02u:00', $value, $this->start_minute);
            return $this->start_hour;
        }
        if ($field == 'start_minute') {
            $this->start_time = sprintf('%02u:%02u:00', $this->start_hour, $value);
            return $this->start_minute;
        }
        if ($field == 'end_hour') {
            $this->end_time = sprintf('%02u:%02u:00', $value, $this->end_minute);
            return $this->end_hour;
        }
        if ($field == 'end_minute') {
            $this->end_time = sprintf('%02u:%02u:00', $this->end_hour, $value);
            return $this->end_minute;
        }

        throw new InvalidArgumentException("Invalid field {$field}");
    }

    /**
     * Check if there is a least one not cancelled date for this cycle data
     *
     * @return bool   true, if there is at least one not cancelled date
     */
    public function getIsVisible()
    {
        return sizeof($this->dates) ? true : false;
    }

    /**
     * SWS needs special setter to always store a decimal
     *
     * @param number $value
     */
    protected function setSws($value)
    {
        if (is_string($value)) {
            $value = (float) str_replace(',', '.', $value);
        }
        $this->content['sws'] = round($value, 1);
    }

    /**
     * Generates a string for a regular date. Depending on the selected format, more or less information
     * are present in the generated string:
     * - short:      Only the weekday, beginning and end
     * - long:       Weekday, beginning, end and the repetition interval
     * - long-start: Same as long but with the start date.
     * - full:       Same as long, but also with the start and end week of the regular date in the semester
     *               and the description of the regular date, if provided.
     *
     * @param string $format The format string: "short", "long" or "full". Defaults to "short".
     *
     * @returns string The formatted string.
     */
    public function toString(string $format = 'short') : string
    {
        if (!in_array($format, ['short', 'long', 'long-start', 'full'])) {
            //Invalid format:
            return '';
        }

        $parameters = [
            'beginning' => sprintf('%02d:%02d', $this->start_hour, $this->start_minute),
            'end'       => sprintf('%02d:%02d', $this->end_hour, $this->end_minute),
        ];

        if ($format === 'short') {
            $parameters['weekday_short'] = getWeekday($this->weekday);
            return studip_interpolate(
                _('%{weekday_short}. %{beginning} - %{end}'),
                $parameters
            );
        } else {
            $parameters['weekday']  = getWeekday($this->weekday, false);
            $cycles = [_('Wöchentlich'), _('Zweiwöchentlich'), _('Dreiwöchentlich')];
            $parameters['interval'] = $cycles[(int)$this->cycle];
            if ($format === 'long') {
                return studip_interpolate(
                    _('%{weekday}, %{beginning} - %{end}, %{interval}'),
                    $parameters
                );
            } elseif ($format === 'long-start') {
                $text = _('%{weekday}, %{beginning} - %{end}, %{interval}');
                $room = $this->getMostBookedRoom();

                if ($room) {
                    $parameters['room_name'] = sprintf(
                        '<a href="%1$s" data-dialog="size=auto">%2$s</a>',
                        $room->getActionLink(),
                        htmlReady($room->name)
                    );
                } else {
                    //Use the freetext room name:
                    $room = $this->getMostUsedFreetextRoomName();
                    if ($room) {
                        $parameters['room_name'] = $room;
                    }
                }
                $first_date = $this->getFirstDate();
                if ($first_date) {
                    $parameters['start_date'] = date('d.m.y', $first_date->date);
                }
                if ($room && $first_date) {
                    $text = _('%{weekday}, %{beginning} - %{end}, %{interval} (ab dem %{start_date} im Raum %{room_name})');
                } elseif ($room) {
                    $text = _('%{weekday}, %{beginning} - %{end}, %{interval} (im Raum %{room_name})');
                } elseif ($first_date) {
                    $text = _('%{weekday}, %{beginning} - %{end}, %{interval} (ab dem %{start_date})');
                }
                return studip_interpolate($text, $parameters);
            } elseif ($format === 'full') {
                $parameters['start_week'] = $this->week_offset + 1;
                if ($this->description) {
                    $parameters['description'] = $this->description;
                }
                if ($this->end_offset) {
                    $parameters['end_week'] = $this->end_offset;
                }
                if ($this->description) {
                    if ($this->end_offset) {
                        return studip_interpolate(
                            _('%{weekday}, %{beginning} - %{end}, %{interval}, von der %{start_week}. bis zur %{end_week}. Semesterwoche (%{description})'),
                            $parameters
                        );
                    } else {
                        return studip_interpolate(
                            _('%{weekday}, %{beginning} - %{end}, %{interval}, ab der %{start_week}. Semesterwoche (%{description})'),
                            $parameters
                        );
                    }
                } else {
                    if ($this->end_offset) {
                        return studip_interpolate(
                            _('%{weekday}, %{beginning} - %{end}, %{interval}, von der %{start_week}. bis zur %{end_week}. Semesterwoche'),
                            $parameters
                        );
                    } else {
                        return studip_interpolate(
                            _('%{weekday}, %{beginning} - %{end}, %{interval}, ab der %{start_week}. Semesterwoche'),
                            $parameters
                        );
                    }
                }
            }
        }
        return '';
    }

    /**
     * Retrieves the first date of the regular date.
     *
     * @return CourseDate|null The first date or null if no such date exists.
     */
    public function getFirstDate() : ?CourseDate
    {
        return $this->dates->first();
    }

    /**
     * returns an sorted array with all dates and exdates for the cycledate entry
     * @return array of instances of dates or exdates
     */
    public function getAllDates()
    {
        $dates = [];
        foreach ($this->exdates as $date) {
            $dates[] = $date;
        }
        foreach ($this->dates as $date) {
            $dates[] = $date;
        }

        usort($dates, function ($a, $b) {
            return $a->date - $b->date;
        });

        return $dates;
    }

    /**
     * Retrieves the most booked room that is booked for this regular date.
     *
     * @return Room[] Either the most booked rooms for this regular date or an empty array
     *     in case no such room exists.
     */
    public function getMostBookedRooms(int $start_time = 0, int $end_time = 0) : array
    {
        $sql = "SELECT `resource_id`, COUNT(`resource_id`) AS resource_c
                FROM `termine`
                JOIN `resource_bookings`
                  ON (`termin_id` = `resource_bookings`.`range_id`) ";
        if ($start_time && $end_time && $start_time < $end_time) {
            $sql .= "JOIN `resource_booking_intervals`
                       ON `resource_booking_intervals`.`booking_id` = `resource_bookings`.`id` ";
        }
        $sql .= "WHERE ";
        $sql_params = ['regular_date_id' => $this->id];
        if ($start_time && $end_time && $start_time < $end_time) {
            $sql .= "`resource_booking_intervals`.`end` > :start AND `resource_booking_intervals`.`start` < :end AND ";
            $sql_params['start'] = $start_time;
            $sql_params['end']   = $end_time;
        }
        $sql .= "`termine`.`metadate_id` = :regular_date_id
             AND `resource_id` <> ''
             GROUP BY `resource_id`
             ORDER BY resource_c DESC";
        return DBManager::get()->fetchFirst(
            $sql,
            $sql_params,
            fn($room_id) => Room::find($room_id)
        );
    }

    /**
     * Retrieves the most booked room that is booked for this regular date.
     *
     * @return Room|null Either the most booked room for this regular date or null
     *     in case no such room exists.
     */
    public function getMostBookedRoom() : ?Room
    {
        $rooms = $this->getMostBookedRooms();
        return array_shift($rooms);
    }

    /**
     * @param int $start_time
     *
     * @param int $end_time
     *
     * @return string[] A list of free text rooms ordered by the most used one.
     *     In case no such rooms exist, an empty array is returned.
     */
    public function getMostUsedFreetextRoomNames(int $start_time = 0, int $end_time = 0) : array
    {
        $sql = "SELECT `raum`, COUNT(`raum`) AS room_name_c
                FROM `termine`
                WHERE ";
        $sql_params = ['regular_date_id' => $this->id];
        if ($start_time && $end_time && $start_time < $end_time) {
            $sql .= "`termine`.`date` BETWEEN :start AND :end AND ";
            $sql_params['start'] = $start_time;
            $sql_params['end']   = $end_time;
        }
        $sql .= "`termine`.`metadate_id` = :regular_date_id
             AND `termine`.`termin_id` NOT IN (SELECT `range_id` FROM `resource_bookings`)
             GROUP BY `raum`
             ORDER BY room_name_c DESC";
        return DBManager::get()->fetchFirst($sql, $sql_params);
    }

    /**
     * Retrieves the most booked free text room name that is used for this regular date.
     *
     * @return string|null Either the most used room name for this regular date or null
     *     in case no such room exists.
     */
    public function getMostUsedFreetextRoomName() : ?string
    {
        $rooms = $this->getMostUsedFreetextRoomNames();
        return array_shift($rooms);
    }

    /**
     * Deletes the cycle.
     *
     * @return int number of affected rows
     */
    public function delete()
    {
        $cycle_info = $this->toString();
        $seminar_id = $this->seminar_id;
        $metadate_id = $this->metadate_id;
        $result = parent::delete();

        if ($result) {
            $stmt = DBManager::get()->prepare('DELETE FROM schedule_courses WHERE metadate_id = :metadate_id');
            $stmt->execute(['metadate_id' => $metadate_id]);

            StudipLog::log('SEM_DELETE_CYCLE', $seminar_id, null, $cycle_info);
        }

        return $result;
    }

    /**
     * Set date-type for all dates
     * @param $type
     * @return int
     */
    public function setSingleDateType($type)
    {
        $result = 0;
        if (count($this->dates)) {
            foreach($this->dates as $date) {
                $date->date_typ = $type;
                $result += $date->store();
            }
        }
        return $result;
    }

    /**
     * Stores this cycle.
     * @return int number of changed rows
     */
    public function store()
    {
        //create new entry in seminare_cycle_date
        if ($this->isNew()) {
            $result = parent::store();
            if ($result) {
                $course = Course::find($this->seminar_id);
                //create start timestamp
                $new_dates = $this->createTerminSlots($this->calculateTimestamp(
                    $course->start_semester->vorles_beginn,
                    $this->week_offset*7
                ));

                if (!empty($new_dates)) {
                    foreach ($new_dates as $semester_dates) {
                        foreach ($semester_dates['dates'] as $date) {
                            $result += $date->store();
                        }
                    }
                } else {
                    $this->delete();
                    return 0;
                }
                $this->resetRelation("dates");
                StudipLog::log('SEM_ADD_CYCLE', $this->seminar_id, NULL, $this->toString());
                return $result;
            }
            return 0;
        }

        //change existing cycledate, changes also corresponding single dates
        $old_cycle = SeminarCycleDate::find($this->metadate_id);
        if (!parent::store()) {
            return false;
        }
        $update_count = 0;
        if (
            $this->start_time != $old_cycle->start_time
            || $this->end_time != $old_cycle->end_time
            || $old_cycle->weekday != $this->weekday
        ) {
            $update_count = $this->updateExistingDates($old_cycle);
        }

        if ($old_cycle->week_offset != $this->week_offset
            || $old_cycle->end_offset != $this->end_offset
            || $old_cycle->cycle != $this->cycle )
        {
            $update_count = $this->generateNewDates();
        }

        StudipLog::log('SEM_CHANGE_CYCLE', $this->seminar_id, NULL,
                $old_cycle->toString() .' -> ' . $this->toString());

        return $update_count;
    }

    private function updateExistingDates($old_cycle)
    {
        $update_count = 0;
        foreach ($this->getAllDates() as $date) {
            // ignore dates in the past
            if ($date->date < time()) {
                continue;
            }

            $tos = $date->date;
            $toe = $date->end_time;
            $day = $this->weekday - $old_cycle->weekday;

            $date->date = mktime(date('G', strtotime($this->start_time)), date('i', strtotime($this->start_time)), 0, date('m', $tos), date('d', $tos), date('Y', $tos)) + $day * 24 * 60 * 60;
            $date->end_time = mktime(date('G', strtotime($this->end_time)), date('i', strtotime($this->end_time)), 0, date('m', $toe), date('d', $toe), date('Y', $toe)) + $day * 24 * 60 * 60;

            if ($date instanceof CourseDate && !empty($date->room_bookings)) {
                //Check if the time range of the date has decreased and did not exceed the
                //boundaries of the existing room booking. In that case, the room bookings are shortened.
                foreach ($date->room_bookings as $room_booking) {
                    if ($date->date < $tos || $date->end_time > $toe) {
                        //The room booking must be deleted.
                        $room_booking->delete();
                    } else {
                        //The room booking must be shortened.
                        $room_booking->begin = $date->date;
                        $room_booking->end = $date->end_time;
                        $room_booking->store();
                    }
                }
            }

            if ($old_cycle->weekday != $this->weekday) {
                $all_holiday = SemesterHoliday::getAll(); // fetch all Holidays
                $holiday_date = false;
                foreach ($all_holiday as $val2) {
                    if (($val2["beginn"] <= $date->date) && ($date->date <= $val2["ende"])) {
                        $holiday_date = true;
                        break;
                    }
                }

                //check for calculatable holidays
                if ($date instanceof CourseDate || $date instanceof CourseExDate) {
                    $holy_type = SemesterHoliday::isHoliday($date->date, false);
                    if ($holy_type && $holy_type['col'] == 3) {
                        $holiday_date = true;
                    }
                }

                if ($holiday_date && $date instanceof CourseDate) {
                    $date->cancelDate();
                } else if (!$holiday_date && $date instanceof CourseExDate) {
                    $date->unCancelDate();
                } else if ($date->isDirty()) {
                    $date->store();
                    $update_count++;
                }
            } else if ($date->isDirty()) {
                $date->store();
                $update_count++;
            }
        }
        $this->resetRelation('dates');
        $this->resetRelation('exdates');
        return $update_count;
    }

    /**
     * Generate any currently missing single dates for this cycle.
     */
    public function generateNewDates()
    {
        $course = Course::find($this->seminar_id);
        $topics = [];
        //collect topics for existing future dates (CourseDate)
        foreach ($this->getAllDates() as $date) {
            if ($date->end_time >= time()) {
                $topics_tmp = CourseTopic::findByTermin_id($date->termin_id);
                if (!empty($topics_tmp)) {
                    $topics[] = $topics_tmp;
                }
            }

            if (is_null($this->end_offset)) {
                // check if seminar is endless
                if ($course->isOpenEnded()) {
                    $last_sem = Semester::findOneBySQL('1 ORDER BY beginn DESC');
                    $end_time_offset = $last_sem->vorles_ende;
                } else {
                    $end_time_offset = $course->end_semester->vorles_ende;
                }
            } else {
                $end_time_offset = $this->calculateTimestamp($course->start_semester->vorles_beginn,
                    ($this->end_offset + 1) * 7
                );
            }

            if ($date->date < $this->calculateTimestamp($course->start_semester->vorles_beginn, $this->week_offset * 7) ||
                $date->date > $end_time_offset
            ) {
                $date->delete();
            }
        }
        //restore for updated singledate entries
        $this->resetRelation('dates');
        $this->resetRelation('exdates');

        //create start timestamp
        $new_dates = $this->createTerminSlots($this->calculateTimestamp(
            $course->start_semester->vorles_beginn,
            $this->week_offset*7
        ));

        $update_count = 0;

        foreach ($new_dates as $semester_dates) {
            //update or create singeldate entries
            foreach ($semester_dates['dates'] as $date) {
                if ($date instanceof CourseDate && $date->date >= time() && count($topics) > 0) {
                    $date->topics = array_shift($topics);
                }
                if ($date->store()) {
                    $update_count++;
                }
            }

            //delete unnecessary singeldate entries
            foreach ($semester_dates['dates_to_delete'] as $date) {
                $date->delete();
            }
        }
        return $update_count;
    }

    /**
     * generate single date objects for one cycle and all semester, existing dates are merged in
     *
     * @param startAfterTimeStamp => int timestamp to override semester start
     * @return array array of arrays, for each semester id  an array of two arrays of SingleDate objects: 'dates' => all new and surviving dates, 'dates_to_delete' => obsolete dates
     */
    private function createTerminSlots($startAfterTimeStamp = 0)
    {
        $course = Course::find($this->seminar_id);
        $ret = [];

        if ($startAfterTimeStamp == 0 || $startAfterTimeStamp < $course->start_semester->vorles_beginn) {
            $startAfterTimeStamp = $course->start_semester->vorles_beginn;
        }

        // check if cycle has a fix end (end_offset == null -> endless)
        if (is_null($this->end_offset)) {
            // check if seminar is endless
            if ($course->isOpenEnded()) {
                $last_sem = Semester::findOneBySQL('1 ORDER BY beginn DESC');
                $sem_end = $last_sem->vorles_ende;
            } else {
                $sem_end = $course->end_semester->vorles_ende;
            }
        } else {
            $sem_end = $this->calculateTimestamp($course->start_semester->vorles_beginn, ($this->end_offset + 1) * 7);
        }

        $semester = Semester::findBySQL('beginn <= :ende AND ende >= :start',
                ['start' => $startAfterTimeStamp, 'ende' => $sem_end]);

        foreach ($semester as $val) {
                $ret[$val['semester_id']] = $this->createSemesterTerminSlots($val['vorles_beginn'], $val['vorles_ende'], $startAfterTimeStamp);
        }
        return $ret;
    }


    /**
     * generate single date objects for one cycle and one semester, existing dates are merged in
     *
     * @param string cycle id
     * @param int    timestamp of semester start
     * @param int    timestamp of semester end
     * @param int    alternative timestamp to start from
     * @return array returns an array of two arrays of SingleDate objects: 'dates' => all new and surviving dates, 'dates_to_delete' => obsolete dates
     */
    private function createSemesterTerminSlots($sem_begin, $sem_end, $startAfterTimeStamp)
    {

        $dates = [];
        $dates_to_delete = [];

        // The currently existing singledates for the by metadate_id denoted  regular time-entry
        //$existingSingleDates =& $this->cycles[$metadate_id]->getSingleDates();
        $all_dates = $this->getAllDates();
        $existingSingleDates =& $all_dates;

        $start_woche = $this->week_offset;
        $turnus_offset = 0;

        // This variable is used to check if a given singledate shall be created in a bi-weekly seminar.
        if ($start_woche == -1) {
            $start_woche = 0;
        }
        $week = 0;

        // get the first presence date after sem_begin
        $day_of_week = date('l', strtotime('Sunday + ' . $this->weekday . ' days'));
        $stamp = strtotime('this week ' . $day_of_week, max($sem_begin, $startAfterTimeStamp));

        $start = explode(':', $this->start_time);

        $start_time = mktime(
            (int)$start[0],                                     // Hour
            (int)$start[1],                                     // Minute
            0,                                                  // Second
            date("n", $stamp),                                  // Month
            date("j", $stamp),                                  // Day
            date("Y", $stamp));                                 // Year

        $end = explode(':', $this->end_time);
        $end_time = mktime(
            (int)$end[0],                                       // Hour
            (int)$end[1],                                       // Minute
            0,                                                  // Second
            date("n", $stamp),                                  // Month
            date("j", $stamp),                                  // Day
            date("Y", $stamp));                                 // Year

        $course = Course::find($this->seminar_id);

        // check if cycle has a fix end (end_offset == null -> endless)
        if (is_null($this->end_offset)) {
            // check if seminar is endless
            if ($course->isOpenEnded()) {
                $last_sem = Semester::findOneBySQL('1 ORDER BY beginn DESC');
                $end_time_offset = $last_sem->vorles_ende;
            } else {
                $end_time_offset = $course->end_semester->vorles_ende;
            }
        } else {
            $end_time_offset = $this->calculateTimestamp($course->start_semester->vorles_beginn, ($this->end_offset + 1) * 7);
        }

        // loop through all possible singledates for this regular time-entry
        do {

            // if dateExists is true, the singledate will not be created. Default is of course to create the singledate
            $dateExists = false;

            // do not create singledates if they are earlier than the semester start
            if ($end_time < $sem_begin) {
                $dateExists = true;
                $turnus_offset = 1;
            }

            /*
             * We only create dates, which do not already exist, so we do not overwrite existing dates.
             *
             * Additionally, we delete singledates which are not needed any more (bi-weekly, changed start-week, etc.)
             */
            $date_values['range_id'] = $this->seminar_id;
            $date_values['autor_id'] = $GLOBALS['user']->id;
            $date_values['metadate_id'] = $this->metadate_id;
            foreach ($existingSingleDates as $key => $val) {
                // take only the singledate into account, that maps the current timepoint
                // only compare the week and year, because dates in the past may differ on time or day
                if ($start_time > $startAfterTimeStamp && date('W Y', $val->date) == date('W Y', $start_time)) {
                    $dateExists = true;
                    if (isset($existingSingleDates[$key])) {
                        $dates[] = $val;
                    }
                }
            }

            if (!$dateExists) {
                $termin = new CourseDate();

                $all_holiday = SemesterHoliday::getAll(); // fetch all Holidays
                foreach ($all_holiday as $val2) {
                    if (($val2["beginn"] <= $start_time) && ($start_time <= $val2["ende"])) {
                        $termin = new CourseExDate();
                        break;
                    }
                }

                //check for calculatable holidays
                if ($termin instanceof CourseDate) {
                    $holy_type = SemesterHoliday::isHoliday($start_time, false);
                    if (!is_bool($holy_type) && $holy_type['col'] == 3) {
                        $termin = new CourseExDate();
                    }
                }
                $date_values['date'] = $start_time;
                $date_values['end_time'] = $end_time;
                $date_values['date_type'] = 1;
                $termin->setData($date_values);

                $dates[] = $termin;
            }

            //inc the week, create timestamps for the next singledate
            $start_time = strtotime('+ 1 week', $start_time);
            $end_time = strtotime('+ 1 week', $end_time);
            $week++;

        } while ($end_time < $sem_end && $end_time < $end_time_offset);

        //calulate trurnus
        if ($this->cycle != 0) {
            return $this->calculateTurnusDates($dates, $turnus_offset);
        }
        return ['dates' => $dates, 'dates_to_delete' => $dates_to_delete];
    }


    /**
     * Calculate turnus for singledate entries
     *
     * @param array $dates
     * @param int $turnus_offset correction for turnus calculation if first date is not within semester
     * @return array
     */
    public function calculateTurnusDates($dates, $turnus_offset)
    {
        $week_count = 0 + $turnus_offset;
        $dates_to_store = [];
        $dates_to_delete = [];
        foreach ($dates as $date) {

            if ($this->cycle == 1 && $week_count % 2 != 0 && $week_count > 0) {
                if (!$date->isNew()) {
                    $dates_to_delete[] = $date;
                }
            } else if ($this->cycle == 2 && $week_count % 3 != 0 && $week_count > 0) {
                if (!$date->isNew()) {
                    $dates_to_delete[] = $date;
                }
            } else {
                $dates_to_store[] = $date;
            }
            $week_count++;
        }
        return ['dates' => $dates_to_store, 'dates_to_delete' => $dates_to_delete];
    }

    /**
     * removes all singleDates which are NOT between $start and $end
     *
     * @param int    timestamp for start
     * @param int    timestamp for end
     * @param string seminar_id
     */
    public static function removeOutRangedSingleDates($start, $end, $seminar_id)
    {
        $query = "SELECT termin_id
                  FROM termine
                  WHERE range_id = ? AND (`date` NOT BETWEEN ? AND ?)
                    AND NOT (metadate_id IS NULL OR metadate_id = '')";
        $statement = DBManager::get()->prepare($query);
        $statement->execute([$seminar_id, $start, $end]);
        $ids = $statement->fetchAll(PDO::FETCH_COLUMN);

        foreach ($ids as $id) {
            $termin = new CourseDate($id);
            $termin->delete();
            unset($termin);
        }

        if (count($ids) > 0) {
            // remove all assigns for the dates in question
            $query = "SELECT id FROM resource_bookings WHERE range_id IN (?)";
            $statement = DBManager::get()->prepare($query);
            $statement->execute([$ids]);

            $booking_ids = $statement->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE, 0);

            ResourceBooking::deleteBySql(
                'id IN ( :booking_ids )',
                [
                    'booking_ids' => $booking_ids
                ]
            );
        }

        $query = "DELETE FROM ex_termine
                  WHERE range_id = ? AND (`date` NOT BETWEEN ? AND ?)
                    AND NOT (metadate_id IS NULL OR metadate_id = '')";
        $statement = DBManager::get()->prepare($query);
        $statement->execute([$seminar_id, $start, $end]);
    }

    /**
     * returns a new timestamp for an given start-timestamp and
     * an amount of days calculated with DateTime-Class
     *
     * @param  int  starttime as timestamp
     * @param  int  amount of days
     * @return int  new timestamp
     */
    private static function calculateTimestamp($base, $days = 0)
    {
        $date = new DateTime();
        $date->setTimestamp($base);
        $date->modify(sprintf('this week monday +%s days', $days));
        return $date->getTimestamp();
    }


    /**
     * This is a helper method for getRoomRequestsForDates and
     * countRoomRequestsForDates which helps at building the
     * SQL query and parameters.
     *
     * @returns array An associative array with two indexes:
     *     - sql: The SQL query
     *     - sql_params: The parameters for the SQL query as associative array.
     */
    protected function buildOpenRequestsForDatesQuery(
        $include_metadate = false,
        $order = ''
    )
    {
        $sql = "closed < '1' AND ";
        $sql_params = [];

        if ($include_metadate) {
            $sql = 'metadate_id = :metadate_id OR ';
        }

        $sql .= 'termin_id IN (
            SELECT termin_id FROM termine
            WHERE metadate_id = :metadate_id
        ) OR id IN (
            SELECT request_id
            FROM resource_request_appointments rra
            INNER JOIN termine
            ON rra.appointment_id = termine.termin_id
            WHERE termine.metadate_id = :metadate_id
        ) ';
        $sql_params['metadate_id'] = $this->id;

        if ($order) {
            $sql .= 'ORDER BY ' . $order;
        }
        return [
            'sql' => $sql,
            'sql_params' => $sql_params
        ];
    }


    /**
     * Returns the open room requests that are associated with this metadate.
     * If the optional parameter $include_metadate is set to false,
     * the requests associated with this metadate are not included
     * in the result.
     *
     * @param bool $include_metadate Whether to include requests associated
     *     with this metadate (true) or not (false). The default is false.
     *
     * @returns ResourceRequest[] The requests for this metadate.
     */
    public function getOpenRequestsForDates(
        $include_metadate = false,
        $order = 'mkdate DESC'
    )
    {
        $data = $this->buildOpenRequestsForDatesQuery($include_metadate, $order);
        return ResourceRequest::findBySql($data['sql'], $data['sql_params']);
    }


    /**
     * Returns the amount of open room requests that are associated
     * with this metadate.
     * The amount of requests is determined by counting requests that are
     * directly associated to this metadate and the requests that are
     * associated with the dates of this metadate.
     * If the optional parameter $include_metadate is set to false,
     * the requests associated with this metadate are not included
     * in the result.
     *
     * @param bool $include_metadate Whether to include requests associated
     *     with this metadate (true) or not (false). The default is false.
     *
     * @returns int The amount of requests for this metadate.
     */
    public function countOpenRequestsForDates($include_metadate = false)
    {
        $data = $this->buildOpenRequestsForDatesQuery($include_metadate);
        return ResourceRequest::countBySql($data['sql'], $data['sql_params']);
    }

    /**
     * Determines the booking status for the regular date and returns it as an integer that
     * corresponds to defined class constants:
     *
     * - If the booking status cannot be determined, BOOKING_STATUS_UNDEFINED is returned.
     * - If none of the single dates is booked, BOOKING_STATUS_NOT_BOOKED is returned.
     * - If only a part of the single dates is booked, BOOKING_STATUS_PARTIALLY_BOOKED is returned.
     * - If all single dates are booked, BOOKING_STATUS_ALL_BOOKED is returned.
     *
     * @returns int The booking status as integer.
     */
    public function getBookingStatus() : int
    {
        if (!Config::get()->RESOURCES_ENABLE || !Config::get()->RESOURCES_ENABLE_BOOKINGSTATUS_COLORING) {
            return self::BOOKING_STATUS_UNDEFINED;
        }

        if (count($this->dates) === 0) {
            //If there are no dates, the booking status cannot be determined.
            return self::BOOKING_STATUS_UNDEFINED;
        }

        //Count the course dates by their booking status:
        $booked_c = 0;

        foreach ($this->dates as $course_date) {
            if (count($course_date->room_bookings)) {
                $booked_c++;
            }
        }

        //Check which status is the dominant one (highest ratio):
        if ($booked_c === 0) {
            return self::BOOKING_STATUS_NOT_BOOKED;
        } elseif (count($this->dates) === $booked_c) {
            return self::BOOKING_STATUS_ALL_BOOKED;
        } elseif ($booked_c > 0) {
            return self::BOOKING_STATUS_PARTIALLY_BOOKED;
        }
        return self::BOOKING_STATUS_UNDEFINED;
    }

    /**
     * Generates the correct icon for the booking status of the regular date.
     *
     * @return Icon An icon representing the booking status of the regular date.
     */
    public function getIconForBookingStatus() : Icon
    {
        return match ($this->getBookingStatus()) {
            self::BOOKING_STATUS_ALL_BOOKED =>
                Icon::create('span-full', Icon::ROLE_STATUS_GREEN),
            self::BOOKING_STATUS_PARTIALLY_BOOKED =>
                Icon::create('span-2quarter', Icon::ROLE_STATUS_YELLOW),
            self::BOOKING_STATUS_NOT_BOOKED =>
                Icon::create('span-empty', Icon::ROLE_STATUS_RED),
            default =>
                Icon::create('exclaim-circle', Icon::ROLE_INACTIVE),
        };
    }

    /**
     * Generates a human-readable HTML text for the booking status of the regular date.
     *
     * @return string A HTML text for the booking status of the regular date.
     */
    public function getMessageForBookingStatus() : string
    {
        $booking_status = $this->getBookingStatus();
        if ($booking_status === self::BOOKING_STATUS_ALL_BOOKED) {
            return _('Alle Termine haben Raumbuchungen.');
        } elseif ($booking_status === self::BOOKING_STATUS_NOT_BOOKED) {
            return _('Alle Termine haben keine Raumbuchungen.');
        } elseif ($booking_status === self::BOOKING_STATUS_PARTIALLY_BOOKED) {
            //List the dates that have no room booking:
            $unbooked_dates = [];
            foreach ($this->dates as $course_date) {
                if (empty($course_date->room_bookings)) {
                    $unbooked_dates[] = $course_date;
                }
            }
            uasort($unbooked_dates, function(CourseDate $a, CourseDate $b) {
                return $a->date - $b->date
                    ?: $a->end_time - $b->end_time;
            });

            $unbooked_dates_text = [
                _('Die folgenden Termine haben keine Raumbuchungen:')
            ];
            foreach ($unbooked_dates as $date) {
                $unbooked_dates_text[] = htmlReady($date->getFullName());
            }
            return implode('<br>', $unbooked_dates_text);
        }
        return _('Es sind keine Informationen zu Buchungen verfügbar.');
    }
}