/** * Gets all bookings filtered by given criteria. * * @param array $filter filter criteria * * @return array */ public function getFilteredBookings(array $filter) { $query = 'SELECT b.id, b.user_id, b.subject, b.bookingcomment,' . ' r.id AS room_id, b.date_from, b.date_to, b.seq_id FROM ' . dbc::BOOKINGS_TABLE . ' b ' . ' JOIN ' . dbc::ROOMS_TABLE . ' r ON b.room_id = r.id ' . ' WHERE (date_from >= ' . $this->ilDB->quote(date('Y-m-d H:i:s'), 'timestamp') . ' OR date_to >= ' . $this->ilDB->quote(date('Y-m-d H:i:s'), 'timestamp') . ')' . ' AND b.pool_id = ' . $this->ilDB->quote($this->pool_id, 'integer'); if ($filter['user_id'] || $filter['user_id']) { $user_id = $this->ilRoomSharingDatabase->getUserIdByUsername($filter['user_id']); if ($user_id == NULL) { $user_id = $filter['user_id']; } $query .= ' AND b.user_id = ' . $this->ilDB->quote($user_id, 'integer') . ' '; } if ($filter['room_name'] || $filter['room_name']) { $query .= ' AND r.name LIKE ' . $this->ilDB->quote('%' . $filter['room_name'] . '%', 'text') . ' '; } if ($filter['subject'] || $filter['subject']) { $query .= ' AND b.subject LIKE ' . $this->ilDB->quote('%' . $filter['subject'] . '%', 'text') . ' '; } if ($filter['comment'] || $filter['comment']) { $query .= ' AND b.bookingcomment LIKE ' . $this->ilDB->quote('%' . $filter['comment'] . '%', 'text') . ' '; } if ($filter['attributes']) { foreach ($filter['attributes'] as $attribute => $value) { $query .= ' AND EXISTS (SELECT * FROM ' . dbc::BOOKING_TO_ATTRIBUTE_TABLE . ' ba ' . ' LEFT JOIN ' . dbc::BOOKING_ATTRIBUTES_TABLE . ' a ON a.id = ba.attr_id ' . ' WHERE booking_id = b.id AND name = ' . $this->ilDB->quote($attribute, 'text') . ' AND value LIKE ' . $this->ilDB->quote('%' . $value . '%', 'text') . ' ) '; } } $set = $this->ilDB->query($query); $bookings = array(); while ($row = $this->ilDB->fetchAssoc($set)) { $bookings[] = $row; } return $bookings; }