diff options
Diffstat (limited to 'lib/resources/RoomManager.php')
| -rw-r--r-- | lib/resources/RoomManager.php | 994 |
1 files changed, 994 insertions, 0 deletions
diff --git a/lib/resources/RoomManager.php b/lib/resources/RoomManager.php new file mode 100644 index 0000000..0122a48 --- /dev/null +++ b/lib/resources/RoomManager.php @@ -0,0 +1,994 @@ +<?php + +/** + * RoomManager.php + * + * 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 Moritz Strohm <strohm@data-quest.de> + * @copyright 2017 + * @license http://www.gnu.org/licenses/gpl-2.0.html GPL version 2 + * @category Stud.IP + */ + +/** + * The RoomManager class contains methods that simplify the handling of rooms + * which are one type of resources. + */ +class RoomManager +{ + public static function findRoomsByRequest(ResourceRequest $request, $excluded_room_ids = []) + { + $all_relevant_properties = $request->getPropertyData(); + $all_relevant_properties['room_category_id'] = $request->category_id; + return self::findRooms( + '', + '', + '', + $all_relevant_properties, + $request->getTimeIntervals(true), + 'name ASC, mkdate ASC', + true, + $excluded_room_ids, + false + ); + } + + + /** + * Helper method that creates the identical SQL query for the + * countUserRooms and findUserRooms methods. + */ + protected static function getUserRoomsSqlData( + User $user, + $level = 'user', + $permanent_only = false, + $time = null, + $sql_conditions = '', + $sql_condition_parameters = [], + $sql_order_by = 'ORDER BY resources.sort_position DESC, resources.name ASC' + ) + { + $used_time = time(); + if ($time instanceof DateTime) { + $used_time = $time->getTimestamp(); + } elseif ($time) { + $used_time = $time; + } + + $user_is_admin = ( + ResourceManager::userHasGlobalPermission( + $user, + 'admin' + ) || + $GLOBALS['perm']->have_perm('root') + ); + + $query = ''; + $data = []; + if ($user_is_admin) { + $query = "resources.category_id IN (:room_categories) + "; + if ($sql_conditions) { + $query .= 'AND ' . $sql_conditions; + if (count($sql_condition_parameters)) { + $data = $sql_condition_parameters; + } + } + } else { + $perms = ResourceManager::getHigherPermissionLevels($level); + array_push($perms, $level); + + $query = "resources.category_id IN (:room_categories) + AND + resources.id IN ( + SELECT resource_id FROM resource_permissions + WHERE user_id = :user_id + AND perms IN ( :perms ) "; + $data = [ + 'user_id' => $user->id, + 'perms' => $perms + ]; + if (!$permanent_only) { + $query .= " UNION + SELECT resource_id FROM resource_temporary_permissions + WHERE user_id = :user_id + AND perms IN ( :perms ) + AND (begin <= :time) + AND (end >= :time) "; + $data['time'] = $used_time; + } + $query .= ") "; + + if ($sql_conditions) { + $query .= 'AND ' . $sql_conditions; + if (count($sql_condition_parameters)) { + $data = array_merge($data, $sql_condition_parameters); + } + } + } + $data['room_categories'] = SimpleCollection::createFromArray(ResourceCategory::findAll()) + ->findBy('class_name', self::getAllRoomClassNames()) + ->pluck('id'); + $query .= " GROUP BY resources.id " . $sql_order_by; + + return [ + 'query' => $query, + 'data' => $data + ]; + } + + + /** + * Checks whether the specified user has at least one permanent or + * temporary permission on at least one room. Root users and global resource + * admins always have permanent permissions. + * + * @param User $user The user whose permissions shall be checkec. + * + * @param string $level The minimum permission level the user must have + * on a room so that it will be accepted for the check. + * Defaults to the "user" permission level. + * + * @param bool $permanent_only Whether to count only rooms with permanent + * permissions (true) or rooms with permanent and temporary + * permissions (false). Defaults to false. + * + * @param string $sql_conditions An optional SQL condition that will be + * placed in the WHERE block of the SQL query to filter + * the result set. + * + * @param array $sql_condition_parameters An optional associative array + * for the sql condition parameter containing variable parameters + * that shall be used in the SQL query. + * + * @param DateTime|int|null $time The timestamp for the check on + * temporary permissions. If this parameter is not set + * the current timestamp will be used. + * + * @returns bool True, when the user has permissions for at least one room, + * false otherwise. + */ + public static function userHasRooms( + User $user, + $level = 'user', + $permanent_only = false, + $time = null, + $sql_conditions = '', + $sql_condition_parameters = [] + ) + { + if ($GLOBALS['perm']->have_perm('root', $user->id)) { + return true; + } + $sql = self::getUserRoomsSqlData($user, $level, $permanent_only, $time, $sql_conditions, $sql_condition_parameters, 'ORDER BY NULL'); + + $db = DBManager::get(); + $exists_query = 'SELECT 1 FROM resources WHERE ' . $sql['query'] . ' LIMIT 1'; + $stmt = $db->prepare($exists_query); + $stmt->execute($sql['data']); + return $stmt->fetchColumn(); + } + + + /** + * Counts all rooms for which the specified user has permanent or + * temporary permissions. Root users and global resource admins + * get the amount of rooms in the Stud.IP system as result. + * + * @param User $user The user whose rooms shall be retrieved. + * + * @param string $level The minimum permission level the user must have + * on a room so that it will be included in the result set. + * Defaults to the "user" permission level. + * + * @param bool $permanent_only Whether to count only rooms with permanent + * permissions (true) or rooms with permanent and temporary + * permissions (false). Defaults to false. + * + * @param DateTime|int|null $time The timestamp for the check on + * temporary permissions. If this parameter is not set + * the current timestamp will be used. + * + * @param string $sql_conditions An optional SQL condition that will be + * placed in the WHERE block of the SQL query to filter + * the result set. + * + * @param array $sql_condition_parameters An optional associative array + * for the sql condition parameter containing variable parameters + * that shall be used in the SQL query. + */ + public static function countUserRooms( + User $user, + $level = 'user', + $permanent_only = false, + $time = null, + $sql_conditions = '', + $sql_condition_parameters = [] + ) + { + if (ResourceManager::userHasGlobalPermission($user, $level)) { + //Count all rooms. + $room_class_names = self::getAllRoomClassNames(); + return Room::countBySql( + "INNER JOIN resource_categories + ON resources.category_id = resource_categories.id + WHERE + resource_categories.class_name IN ( :room_class_names )", + [ + 'room_class_names' => $room_class_names + ] + ); + } + $sql = self::getUserRoomsSqlData($user, $level, $permanent_only, $time, $sql_conditions, $sql_condition_parameters); + return Room::countBySql($sql['query'], $sql['data']); + } + + + /** + * Retrieves all rooms for which the specified user has permanent or + * temporary permissions. Root users and global resource admins + * get a list of all rooms stored in the Stud.IP system. + * + * @param User $user The user whose rooms shall be retrieved. + * + * @param bool $permanent_only Whether to retrieve only rooms with permanent + * permissions (true) or rooms with permanent and temporary + * permissions (false). Defaults to false. + * + * @param string $level The minimum permission level the user must have + * on a room so that it will be included in the result set. + * + * @param string $sql_conditions An optional SQL condition that will be + * placed in the WHERE block of the SQL query to filter + * the result set. + * + * @param array $sql_condition_parameters An optional associative array + * for the sql condition parameter containing variable parameters + * that shall be used in the SQL query. + * + * @param DateTime|int|null $time The timestamp for the check on + * temporary permissions. If this parameter is not set + * the current timestamp will be used. + * @return Room[] + */ + public static function getUserRooms( + User $user, + $level = 'user', + $permanent_only = false, + $time = null, + $sql_conditions = '', + $sql_condition_parameters = [] + ) + { + if (ResourceManager::userHasGlobalPermission($user, $level)) { + //Return all rooms: + $room_class_names = self::getAllRoomClassNames(); + return Room::findBySql( + "INNER JOIN resource_categories + ON resources.category_id = resource_categories.id + WHERE + resource_categories.class_name IN ( :room_class_names ) + GROUP BY resources.id + ORDER BY resources.sort_position DESC, resources.name ASC", + [ + 'room_class_names' => $room_class_names + ] + ); + } + $sql = self::getUserRoomsSqlData($user, $level, $permanent_only, $time, $sql_conditions, $sql_condition_parameters); + return Room::findBySql($sql['query'], $sql['data']); + } + + + /** + * @param bool $notify_teachers True, if the teachers of the course where + * the room request belongs to shall be notified, false otherwise. + * This parameter is only useful for room requests made for courses + * and their dates. + */ + public static function createRoomBookingsByRequest( + Room $room, + RoomRequest $room_request, + User $user, + $notify_teachers = true + ) + { + $time_intervals = $room_request->getTimeIntervals(); + + $bookings = []; + + //Create a ResourceBooking for each time interval: + foreach ($time_intervals as $interval) { + $booking = new ResourceBooking(); + $booking->resource_id = $room->id; + $booking->begin = $interval['begin']; + $booking->end = $interval['end']; + $booking->range_id = $user->id; + $booking->preparation_time = $room_request->preparation_time; + if ($booking->store()) { + $bookings[] = $booking; + } + } + + //Notify the creator of the room request: + if ($room_request->user) { + //Send the message in the room request creator's preferred language: + setTempLanguage($room_request->user->id); + Message::send( + '____%system%____', + [$room_request->user->username], + sprintf( + _('Ihre Raumanfrage für den Raum %1$s wurde von %2$s aufgelöst'), + $room->name, + $user->getFullName() + ), + sprintf( + _('Ihre Raumanfrage für den Raum %1$s wurde von %2$s aufgelöst.'), + $room->name, + $user->getFullName() + ) + ); + restoreLanguage(); + } + + if ($notify_teachers) { + //Notify the teachers of the course, but check first if the room + //request belongs to a course: + + if ($room_request->course) { + $lecturers = CourseMember::findByCourseAndStatus( + $room_request->course_id, + 'dozent' + ); + + foreach ($lecturers as $lecturer) { + //Send the message in the lecturer's preferred language: + setTempLanguage($lecturer->user->id); + $message_body = sprintf( + _('Für die Veranstaltung %1$s wurden Raumbuchungen für die folgenden Termine erstellt:'), + $room_request->course->name + ) . "\n\n"; + + foreach ($time_intervals as $time_interval) { + $message_body .= '- ' + . sprintf( + _('Am %1$s von %2$s Uhr bis %3$s Uhr'), + date('d.m.Y', $time_interval['begin']), + date('H:i', $time_interval['begin']), + date('H:i', $time_interval['end']) + ) + . "\n"; + } + + Message::send( + '____%system%____', + $lecturer->user->username, + sprintf( + _('Auflösung einer Raumanfrage in Veranstaltung %s'), + $room_request->course->name + ), + $message_body + ); + restoreLanguage(); + } + } + } + + $room_request->closed = 2; + $room_request->store(); + + return $bookings; + } + + + public static function getBookingIntervalsForRoom( + Room $room, + DateTime $begin, + DateTime $end, + $booking_types = [ + ResourceBooking::TYPE_NORMAL, + ResourceBooking::TYPE_RESERVATION, + ResourceBooking::TYPE_LOCK, + ResourceBooking::TYPE_PLANNED, + ], + $building_booking_types = [ + ResourceBooking::TYPE_NORMAL, + ResourceBooking::TYPE_RESERVATION, + ResourceBooking::TYPE_LOCK, + ResourceBooking::TYPE_PLANNED, + ], + $exclude_canceled_intervals = true + ) + { + $intervals = []; + + if (!$booking_types && !$building_booking_types) { + //No types specified => nothing to do. + return $intervals; + } + + if ($begin >= $end) { + //Invalid time range specified => nothing to do either. + return $intervals; + } + + //Build the SQL query and the SQL parameters: + $sql = "INNER JOIN resource_bookings + ON resource_booking_intervals.booking_id = resource_bookings.id + WHERE resource_booking_intervals.begin < :end AND resource_booking_intervals.end > :begin "; + $sql_array = [ + 'begin' => $begin->getTimestamp(), + 'end' => $end->getTimestamp(), + ]; + + if ($exclude_canceled_intervals) { + $sql .= "AND resource_booking_intervals.takes_place = '1' "; + } + + if ($booking_types) { + $sql .= "AND ( + resource_booking_intervals.resource_id = :room_id + AND + resource_bookings.booking_type IN ( :booking_types ) + ) "; + $sql_array['room_id'] = $room->id; + $sql_array['booking_types'] = $booking_types; + } + if ($building_booking_types) { + $sql .= "OR ( + resource_booking_intervals.resource_id = :building_id + AND + resource_bookings.booking_type IN ( :building_booking_types ) + ) "; + $sql_array['building_id'] = $room->building->id; + $sql_array['building_booking_types'] = $building_booking_types; + } + + $sql .= "ORDER BY begin ASC, end ASC"; + + $intervals = ResourceBookingInterval::findBySql($sql, $sql_array); + return $intervals; + } + + + /** + * Finds rooms by name, time range and which (at your option) + * meet the requirements specified by other properties. + * If a property isn't specified it won't be searched for. + * + * @param string $room_name The name of the room. + * @param string $location_id The ID of the location where the room shall + * lie in. + * @param string $building_id + * @param string[] $properties An array of properties + * and their desired states. + * This array has the following structure: + * $properties['property_name'] = 'property_state'; + * The array keys specify the property names, the entries of the array + * specify the desired state. + * Only the seats property may be a single value or an array with + * two entries. In the first case the room must have + * at least $seats seats. In the latter case the room must have between + * $seats[0] and $seats[1] seats. + * There are special pseudo properties that are handled by this array: + * - room_category_id: The id of a resource category + * that has 'Room' as class name. + * + * @param array $time_ranges Optional time ranges where the rooms have + * to be available. The format of the array is the following: + * [ + * [ + * 'begin' => (begin timestamp or DateTime object), + * 'end' => (end timestamp or DateTime object) + * ] + * ] + * @param string $order_by An optional SQL snippet specifying the order of + * the results. Defaults to null (no sorting). + * @param bool $only_requestable_rooms Whether the search shall be limited + * to requestable rooms only (true) or not (false). + * @param array $excluded_room_ids + * + * @param bool $only_fully_available Whether only rooms shall be added to + * the result set that are fully available in the requested time + * ranges (true) or whether rooms shall be added that are only + * partially available in those time ranges (false). + * Defaults to true. + * + * @return array + */ + public static function findRooms( + $room_name = '', + $location_id = null, + $building_id = null, + $properties = [], + $time_ranges = [], + $order_by = null, + $only_requestable_rooms = true, + $excluded_room_ids = [], + $only_fully_available = true + ) + { + $sql = "INNER JOIN resource_categories rc + ON resources.category_id = rc.id + WHERE rc.class_name IN ( :room_class_names ) "; + + $sql_array = [ + 'room_class_names' => self::getAllRoomClassNames() + ]; + + if ($only_requestable_rooms) { + $sql .= "AND resources.requestable > '0' "; + } + + if ($room_name) { + $sql .= "AND resources.name LIKE CONCAT('%', :room_name, '%') "; + $sql_array['room_name'] = $room_name; + } + + if (!empty($properties['room_category_id'])) { + $sql .= "AND rc.id = :room_category_id "; + $sql_array['room_category_id'] = $properties['room_category_id']; + } + if ($excluded_room_ids && is_array($excluded_room_ids)) { + $sql .= " AND resources.id NOT IN ( :room_ids ) "; + $sql_array['room_ids'] = $excluded_room_ids; + } + + $sql .= " GROUP BY resources.name "; + + if ($order_by) { + $sql .= "ORDER BY " . $order_by . ' '; + } + + $rooms = Room::findBySql($sql, $sql_array); + + //We must filter the rooms after we retrieved them since + //room bookings can have repetitions that we have to check. + + $filtered_rooms = []; + if ($location_id || $building_id) { + foreach ($rooms as $room) { + //First check if the room lies in the building + //before checking its availability: + + $building = $room->findParentByClassName('Building'); + + if (!$building) { + //An invalid room: we don't want that either! + continue; + } + + if ($building_id) { + if (is_array($building_id)) { + if (in_array($building->id, $building_id)) { + //We have found a room inside the building we are + //looking for. + $filtered_rooms[] = $room; + continue; + } + } + + if ($building->id == $building_id) { + //We have found a room inside the building we are + //looking for. + $filtered_rooms[] = $room; + continue; + } + } + + //If code execution reaches this point, we haven't found + //a room yet, because no building has been specified. + //We have to look for locations instead, if one is given. + if ($location_id) { + $location = $building->findParentByClassName('Location'); + + if ($location_id && ($location->id == $location_id)) { + //The room lies in the location we're searching for. + $filtered_rooms[] = $room; + } + } + } + } else { + //There is no selection for a specific building or location. + $filtered_rooms = $rooms; + } + + $result = []; + if (!empty($time_ranges)) { + + //We must check if the room is available: + foreach ($filtered_rooms as $room) { + $room_is_available = $only_fully_available; + foreach ($time_ranges as $time_range) { + if (!$time_range['begin'] || !$time_range['end']) { + //Invalid format. + continue; + } + if ($time_range['begin'] instanceof DateTime) { + $begin = $time_range['begin']; + } else { + $begin = new DateTime(); + $begin->setTimestamp($time_range['begin']); + } + if ($time_range['end'] instanceof DateTime) { + $end = $time_range['end']; + } else { + $end = new DateTime(); + $end->setTimestamp($time_range['end']); + } + if ($room->isAvailable($begin, $end)) { + if (!$only_fully_available) { + $room_is_available = true; + break; + } + } else { + if ($only_fully_available) { + $room_is_available = false; + break; + } + } + } + if ($room_is_available) { + $result[] = $room; + } + } + } else { + //No time frame has been specified. + //All found rooms can be transferred to the result set. + $result = $filtered_rooms; + } + + //Filter out the special property room_category_id: + unset($properties['room_category_id']); + + //Now we filter each room for the properties, if any: + if (is_array($properties) && count($properties)) { + $old_result = $result; + $result = []; + $properties = array_filter($properties, function ($property) { + return !empty($property); + }); + $required_property_c = count($properties); + foreach ($old_result as $room) { + $room_property_match = 0; + foreach ($properties as $name => $state) { + $room_prop_state = $room->getProperty($name); + if ($room_prop_state === null) { + //No such property. + continue; + } + if (is_array($state)) { + //A range is specified. We must check if the + //value of the property lies in the range. + //Furthermore we must check if only minimum or maximum are + //set or if both are set. Depending on that condition, + //the conditions are different. + if ($state[0] && $state[1]) { + //Minimum and maximum are specified: + if ($room_prop_state >= $state[0] && $room_prop_state <= $state[1]) { + $room_property_match++; + } + } elseif ($state[0]) { + //Only a minimum is given: + if ($room_prop_state >= $state[0]) { + $room_property_match++; + } + } elseif ($state[1]) { + //Only a maximum is given: + if ($room_prop_state <= $state[1]) { + $room_property_match++; + } + } + } elseif ($state) { + if ($room_prop_state == $state) { + $room_property_match++; + } + } + } + if ($room_property_match === $required_property_c) { + $result[] = $room; + } + } + } + + return $result; + } + + /** + * Searches for room bookings where the room is overbooked + * in the specified time range. + * A room is overbooked if a resource booking exists for that room + * which is associated with a course and the amount of course participants + * is higher than the amount of seats in the room. + * + * @param DateTime $begin The begin of the time range. + * @param DateTime $end The end of the time range. + * + * @returns SimpleORMapCollection A collection of overbooked room bookings + * as ResourceBooking objects. + */ + public static function findOverbookedRoomBookings(DateTime $begin, DateTime $end) + { + //First we must check if $begin and $end specify a valid time range: + if ($end <= $begin) { + throw new InvalidArgumentException( + _('Der Startzeitpunkt darf nicht hinter dem Endzeitpunkt liegen!') + ); + } + + //Now we query all room resources together with courses and + //resource bookings: + + $found_bookings = ResourceBooking::findBySql( + "INNER JOIN resources + ON resource_bookings.resource_id = resources.id + INNER JOIN resource_categories + ON resources.category_id = resource_categories.id + INNER JOIN resource_properties + ON resources.id = resource_properties.resource_id + INNER JOIN resource_property_definitions rpd + ON resource_properties.property_id = rpd.property_id + WHERE TRUE" + ); + + return $found_bookings; + } + + + /** + * Retrieves all properties that are bound to room resource categories + * or room resources directly. + * + * @param bool $only_searchable Whether only searchable properties + * shall be returned (true) or all properties shall be returned (false). + * Defaults to false. + * + * @param string[] $excluded_properties An array containing the names + * of the properties that shall be excluded from the result set. + * + * @returns ResourcePropertyDefinition[] An array of + * ResourcePropertyDefinition objects. + */ + public static function getAllRoomPropertyDefinitions( + $only_searchable = false, + $excluded_properties = [] + ) + { + if ($excluded_properties) { + if (!is_array($excluded_properties)) { + $excluded_properties = [$excluded_properties]; + } + } + + $sql = "INNER JOIN resource_category_properties rcp + USING (property_id) + INNER JOIN resource_categories rc + ON rcp.category_id = rc.id + WHERE + rc.class_name IN ( :room_class_names ) "; + $sql_array = [ + 'room_class_names' => self::getAllRoomClassNames() + ]; + + if ($only_searchable) { + $sql .= "AND resource_property_definitions.searchable = '1' "; + } + + if ($excluded_properties) { + $sql .= "AND resource_property_definitions.name NOT IN ( + :excluded_properties + ) "; + $sql_array['excluded_properties'] = $excluded_properties; + } + + $sql .= "GROUP BY + resource_property_definitions.property_id + ORDER BY + resource_property_definitions.name ASC, + resource_property_definitions.mkdate ASC;"; + + return ResourcePropertyDefinition::findBySql( + $sql, $sql_array + ); + } + + + /** + * Returns the names of all hierarchy elements from the location to the + * specified room. + * + * @param Room $room The room to start with. + * + * @param bool $all_items Whether to include all resource item names + * while traversing the tree upwards (true) or to only include the + * names of the room, the building and the location (false). + * + * @returns string[] An array with the names of the hierarchy elements, + * starting with the location name. + */ + public static function getHierarchyNames(Room $room, $all_items = false) + { + $names = [$room->name]; + + if ($all_items) { + return ResourceManager::getHierarchyNames($room); + } else { + $building = $room->building; + if ($building instanceof Building) { + $names[] = $building->name; + + $location = $building->location; + if ($location instanceof Location) { + $names[] = $location->name; + } + } + } + return array_reverse($names); + } + + + /** + * Groups rooms by their location and building. + * + * @param Room[] $rooms An array of rooms that shall be grouped. + * + * @returns Array[][][] A three dimensional array with the whole + * hierarchy for the specified rooms. + * The array structure is as follows: + * [ + * [ + * 'location' => Location object + * 'buildings' => [ + * 'building' => Building object + * 'rooms' => Array of room objects. + * ] + * ] + * ] + */ + public static function groupRooms($rooms = []) + { + $grouped_rooms = []; + foreach ($rooms as $room) { + if (!$room instanceof Room) { + continue; + } + $building = $room->building; + if (!($building instanceof Building)) { + //Invalid room + continue; + } + $location = $building->location; + if (!($location instanceof Location)) { + //Invalid room + continue; + } + if (empty($grouped_rooms[$location->id])) { + $grouped_rooms[$location->id] = [ + 'location' => $location, + 'buildings' => [] + ]; + } + if (empty($grouped_rooms[$location->id]['buildings'][$building->id])) { + $grouped_rooms[$location->id]['buildings'][$building->id] = [ + 'building' => $building, + 'rooms' => [] + ]; + } + $grouped_rooms[$location->id]['buildings'][$building->id]['rooms'][] = $room; + } + + return $grouped_rooms; + } + + + /** + * @returns string[] A list containing class names of all classes that are + * derived from the Room class and the Room class itself. + */ + public static function getAllRoomClassNames() + { + $resource_class_names = ResourceManager::getAllResourceClassNames(); + $room_class_names = []; + foreach($resource_class_names as $class_name) { + if (is_a($class_name, 'Room', true)) { + $room_class_names[] = $class_name; + } + } + return $room_class_names; + } + + + /** + * This method creates the SQL data for the methods countRequestableRooms + * and findRequestableRooms. + * + * @param array time_ranges[][] The time ranges in which a requestable room + * must be available to be included in the result set. This is a + * two-dimensional array where the second dimension is an associative + * array with two indexes: "begin" and "end". These can either be + * timestamps or DateTime objects representing the begin and end + * of the time range, respectively. + * + * @returns array An associative array with two indexes: + * - sql: The SQL query as string. + * - sql_params: An associative array with all parameters for the query. + */ + protected static function getRequestableRoomsSqlData(array $time_ranges = []) + { + $sql = "INNER JOIN `resource_categories` rc + ON `resources`.`category_id` = rc.id "; + if ($time_ranges) { + $sql .= "INNER JOIN `resource_booking_intervals` rbi + ON `resources`.`id` = rbi.`resource_id` "; + } + $sql .= "WHERE `rc`.`class_name` IN ( :room_class_names ) + AND `resources`.`requestable` > '0' "; + + $sql_params = [ + 'room_class_names' => self::getAllRoomClassNames() + ]; + + if (!$time_ranges) { + $sql .= 'ORDER BY `resources`.`name` ASC'; + return [ + 'sql' => $sql, + 'sql_params' => $sql_params + ]; + } + + $sql .= "AND ("; + + $i = 1; + foreach ($time_ranges as $time_range) { + if (!is_array($time_range)) { + continue; + } + if ($time_range['begin'] >= $time_range['end']) { + continue; + } + + $begin = $time_range['begin']; + if ($time_range['begin'] instanceof DateTime) { + $begin = $time_range['begin']->getTimestamp(); + } + $end = $time_range['end']; + if ($time_range['end'] instanceof DateTime) { + $end = $time_range['end']->getTimestamp(); + } + + if ($i > 1) { + $sql .= 'AND '; + } + // FIXME this checks looks bogus to me + $sql .= "(rbi.begin >= :end$i OR rbi.end <= :begin$i)"; + $sql_params["begin$i"] = $begin; + $sql_params["end$i"] = $end; + + $i++; + } + $sql .= ') GROUP BY `resources`.`id` ORDER BY `resources`.`name` ASC'; + + return [ + 'sql' => $sql, + 'sql_params' => $sql_params + ]; + } + + + public static function countRequestableRooms(array $time_ranges = []) + { + $sql_data = self::getRequestableRoomsSqlData($time_ranges); + return Room::countBySql($sql_data['sql'], $sql_data['sql_params']); + } + + + public static function findRequestableRooms(array $time_ranges = []) + { + $sql_data = self::getRequestableRoomsSqlData($time_ranges); + return Room::findBySql($sql_data['sql'], $sql_data['sql_params']); + } +} |