/** * Returns the extra dates for one seminar * @param $sem_id the id of the seminar * @return the HTML for the schedule table for the extra dates */ function dumpExtraDatesSchedule($sem_id) { $presence_type_clause = getPresenceTypeClause(); $query = "SELECT termine.*, themen.title AS th_title, themen.description AS th_desc FROM termine LEFT JOIN themen_termine USING (termin_id) LEFT JOIN themen USING (issue_id) WHERE range_id = ? AND date_typ NOT IN {$presence_type_clause} ORDER BY date"; $statement = DBManager::get()->prepare($query); $statement->execute(array($sem_id)); $data = $statement->fetchAll(PDO::FETCH_ASSOC); return dumpScheduleTable($data, _('zusätzliche Termine')); }
function isSchedule($sem_id, $presence_dates_only = TRUE, $clearcache = FALSE) { $query = "SELECT COUNT(*)\n FROM termine\n WHERE range_id = ? AND metadate_id != '' AND metadate_id IS NOT NULL"; if ($presence_dates_only) { $query .= " AND date_typ IN " . getPresenceTypeClause(); } $statement = DBManager::get()->prepare($query); $statement->execute(array($sem_id)); return $statement->fetchColumn(); }
public function checkOpen($also_change = FALSE) { $db = DBManager::get(); $existing_assign = false; //a request for a date is easy... if ($this->termin_id) { $query = sprintf("SELECT assign_id FROM resources_assign WHERE assign_user_id = %s ", $db->quote($this->termin_id)); $existing_assign = $db->query($query)->fetchColumn(); //metadate request } elseif ($this->metadate_id) { $query = sprintf("SELECT count(termin_id)=count(assign_id) FROM termine LEFT JOIN resources_assign ON(termin_id=assign_user_id)\n WHERE metadate_id=%s", $db->quote($this->seminar_id)); //seminar request } else { $query = sprintf("SELECT count(termin_id)=count(assign_id) FROM termine LEFT JOIN resources_assign ON(termin_id=assign_user_id)\n WHERE range_id='%s' AND date_typ IN" . getPresenceTypeClause(), $this->seminar_id); } if ($query) { $existing_assign = $db->query($query)->fetchColumn(); } if ($existing_assign && $also_change) { $this->setClosed(1); $this->store(); } return (bool) $existing_assign; }
function getMyRoomRequests($user_id = '', $semester_id = null, $only_not_closed = true, $single_request = null, $sem_type = null, $faculty = null, $tagged = null) { global $user, $perm, $RELATIVE_PATH_RESOURCES; $db = DBManager::get(); if (!$user_id) { $user_id = $user->id; } $parameters = array(); if ($only_not_closed) { $criteria = ' closed = 0 '; } else { $criteria = ' 1 '; } if ($single_request) { $criteria .= " AND rr.request_id = :request_id"; $parameters[':request_id'] = $single_request; } elseif ($semester_id) { $semester = Semester::find($semester_id); $sem_criteria = ' AND t.date BETWEEN ' . (int) $semester['beginn'] . ' AND ' . (int) $semester['ende']; if ($sem_type) { $criteria .= " AND EXISTS (SELECT * FROM seminare WHERE seminare.Seminar_id=rr.seminar_id AND seminare.status=" . (int) $sem_type . ") "; } if ($faculty) { $criteria .= " AND EXISTS (SELECT * FROM seminare INNER JOIN Institute USING(Institut_id) WHERE seminare.Seminar_id=rr.seminar_id AND fakultaets_id=" . DBManager::get()->quote($faculty) . ") "; } if ($tagged) { $criteria .= " AND NOT EXISTS (SELECT * FROM resources_requests_user_status WHERE resources_requests_user_status.request_id=rr.request_id AND resources_requests_user_status.user_id=" . DBManager::get()->quote($user_id) . ") "; } } $query0 = "SELECT request_id, closed, rr.resource_id\n FROM resources_requests AS rr\n WHERE %s "; $queries = array(); $queries[] = "SELECT request_id\n FROM resources_requests AS rr\n INNER JOIN termine t\n ON (t.termin_id = rr.termin_id AND t.date > UNIX_TIMESTAMP() {$sem_criteria})\n WHERE rr.termin_id <> '' AND %s"; $presence_type_clause = getPresenceTypeClause(); $queries[] = "SELECT DISTINCT request_id\n FROM resources_requests AS rr\n INNER JOIN termine AS t\n ON (rr.seminar_id = t.range_id AND\n t.date_typ IN {$presence_type_clause} AND\n t.date > UNIX_TIMESTAMP() {$sem_criteria})\n WHERE rr.termin_id = '' AND rr.metadate_id = '' AND %s "; $queries[] = "SELECT DISTINCT request_id\n FROM resources_requests AS rr\n INNER JOIN termine AS t ON (t.metadate_id = rr.metadate_id AND t.date > UNIX_TIMESTAMP() {$sem_criteria})\n WHERE rr.metadate_id <> '' AND %s "; $requests = array(); if (getGlobalPerms($user_id) == 'admin') { $query = sprintf($query0, $criteria); $statement = DBManager::get()->prepare($query); $statement->execute($parameters); while ($row = $statement->fetch(PDO::FETCH_ASSOC)) { $requests[$row['request_id']] = array('my_sem' => true, 'my_res' => strlen($row['resource_id']) > 0, 'closed' => $row['closed'], 'resource_id' => $row['resource_id']); } foreach ($queries as $q) { $query = sprintf($q, $criteria); $statement = DBManager::get()->prepare($query); $statement->execute($parameters); while ($request_id = $statement->fetchColumn()) { $requests[$request_id]['have_times'] = 1; } } } else { //load all my resources $resList = new ResourcesUserRoomsList($user_id, FALSE, FALSE); $my_res = $resList->getRooms(); if (count($my_res) > 0) { foreach (array_keys($my_res) as $res_id) { $object_perms = ResourceObjectPerms::Factory($res_id, $user_id); if (!$object_perms->havePerm('tutor')) { unset($my_res[$res_id]); } } } if (count($my_res) > 0) { $res_criteria = $criteria . " AND rr.resource_id IN (:resource_ids)"; $params = $parameters; $params[':resource_ids'] = array_keys($my_res); $query = sprintf($query0, $res_criteria); $statement = DBManager::get()->prepare($query); $statement->execute($params); while ($row = $statement->fetch(PDO::FETCH_ASSOC)) { $requests[$row['request_id']] = array('my_res' => true, 'closed' => $row['closed'], 'resource_id' => $row['resource_id']); } foreach ($queries as $q) { $query = sprintf($q, $res_criteria); $statement = DBManager::get()->prepare($query); $statement->execute($params); while ($request_id = $statement->fetchColumn()) { $requests[$request_id]['have_times'] = 1; } } //load all my seminars $my_sems = search_administrable_seminars(); if (count($my_sems) > 0) { $sem_criteria = $criteria . " AND rr.seminar_id IN (:seminar_ids)"; $params = $parameters; $params[':seminar_ids'] = array_keys($my_sems); $query = sprintf($query0, $sem_criteria); $statement = DBManager::get()->prepare($query); $statement->execute($params); while ($row = $statement->fetch(PDO::FETCH_ASSOC)) { $request_id = $row['request_id']; $requests[$request_id]['my_sem'] = true; $requests[$request_id]['closed'] = $row['closed']; $requests[$request_id]['resource_id'] = $row['resource_id']; } foreach ($queries as $q) { $query = sprintf($q, $sem_criteria); $statement = DBManager::get()->prepare($query); $statement->execute($params); while ($request_id = $statement->fetchColumn()) { $requests[$request_id]['have_times'] = 1; } } } } } return $requests; }