/**
* 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;
}
