/**
* Exports lecture-data.
*
* This function gets the data of the lectures at an institute and writes it into $data_object.
* It calls output_data afterwards.
*
* @access public
* @param string $inst_id Stud.IP-inst_id for export
* @param string $ex_sem_id allows to choose if only a specific lecture is to be exported
*/
function export_sem($inst_id, $ex_sem_id = 'all')
{
global $range_id, $xml_file, $o_mode, $xml_names_lecture, $xml_groupnames_lecture, $object_counter, $SEM_TYPE, $SEM_CLASS, $filter, $ex_sem, $ex_sem_class, $ex_person_details, $persons;
$ex_only_homeinst = Request::int('ex_only_homeinst', 0);
// Prepare user count statement
$query = "SELECT COUNT(user_id)\n FROM seminar_user\n WHERE seminar_id = ? AND status = 'autor'";
$count_statement = DBManager::get()->prepare($query);
// Prepare inner statement
$query = "SELECT seminar_user.position,\n auth_user_md5.user_id, auth_user_md5.username, auth_user_md5.Vorname, auth_user_md5.Nachname,\n user_info.title_front, user_info.title_rear\n FROM seminar_user\n LEFT JOIN user_info USING (user_id)\n LEFT JOIN auth_user_md5 USING (user_id)\n WHERE seminar_user.status = 'dozent' AND seminar_user.Seminar_id = ?\n ORDER BY seminar_user.position";
$inner_statement = DBManager::get()->prepare($query);
// Prepare (build) and execute outmost query
switch ($filter) {
case "seminar":
$order = " seminare.Name";
break;
case "status":
$order = "seminare.status, seminare.Name";
$group = "FIRSTGROUP";
$group_tab_zelle = "status";
$do_group = true;
break;
default:
$order = "seminare.status, seminare.Name";
$group = "FIRSTGROUP";
$group_tab_zelle = "status";
$do_group = true;
}
$parameters = array();
if (isset($ex_sem) && ($semester = Semester::find($ex_sem))) {
$addquery = " AND seminare.start_time <= :begin AND (:begin <= (seminare.start_time + seminare.duration_time) OR seminare.duration_time = -1) ";
$parameters[':begin'] = $semester->beginn;
}
if ($ex_sem_id != 'all') {
if ($ex_sem_id == 'root') {
$addquery .= " AND EXISTS (SELECT * FROM seminar_sem_tree WHERE seminar_sem_tree.seminar_id = seminare.Seminar_id) ";
} else {
if (!is_array($ex_sem_id)) {
$ex_sem_id = array($ex_sem_id);
}
$ex_sem_id = array_flip($ex_sem_id);
}
}
if (!$GLOBALS['perm']->have_perm('root') && !$GLOBALS['perm']->have_studip_perm('admin', $inst_id)) {
$addquery .= " AND visible = 1 ";
}
if (count($ex_sem_class) > 0) {
$allowed_sem_types = array();
foreach (array_keys($ex_sem_class) as $semclassid) {
$allowed_sem_types += array_keys(SeminarCategories::get($semclassid)->getTypes());
}
$addquery .= " AND seminare.status IN (:status) ";
$parameters[':status'] = $allowed_sem_types;
} else {
$addquery .= " AND seminare.status NOT IN (:status) ";
$parameters[':status'] = studygroup_sem_types() ?: '';
}
if ($ex_only_homeinst) {
$query = "SELECT seminare.*,Seminar_id as seminar_id, Institute.Name AS heimateinrichtung\n FROM seminare\n LEFT JOIN Institute USING (Institut_id)\n WHERE Institut_id = :institute_id {$addquery}\n ORDER BY " . $order;
$parameters[':institute_id'] = $inst_id;
} else {
$query = "SELECT seminare.*,Seminar_id as seminar_id, Institute.Name AS heimateinrichtung\n FROM seminar_inst\n LEFT JOIN seminare USING (Seminar_id)\n LEFT JOIN Institute ON seminare.Institut_id = Institute.Institut_id\n WHERE seminar_inst.Institut_id = :institute_id {$addquery}\n ORDER BY " . $order;
$parameters[':institute_id'] = $inst_id;
}
$statement = DBManager::get()->prepare($query);
$statement->execute($parameters);
$data = $statement->fetchAll(PDO::FETCH_ASSOC);
$data_object .= xml_open_tag($xml_groupnames_lecture['group']);
foreach ($data as $row) {
if (is_array($ex_sem_id) && !isset($ex_sem_id[$row['seminar_id']])) {
continue;
}
$group_string = '';
if ($do_group && $group != $row[$group_tab_zelle]) {
if ($group != 'FIRSTGROUP') {
$group_string .= xml_close_tag($xml_groupnames_lecture['subgroup1']);
}
if ($group_tab_zelle == 'status') {
$group_string .= xml_open_tag($xml_groupnames_lecture['subgroup1'], $SEM_TYPE[$row[$group_tab_zelle]]['name']);
} else {
$group_string .= xml_open_tag($xml_groupnames_lecture['subgroup1'], $row[$group_tab_zelle]);
}
$group = $row[$group_tab_zelle];
if ($do_subgroup && $subgroup == $row[$subgroup_tab_zelle]) {
$subgroup = "NEXTGROUP";
}
}
if ($do_subgroup && $subgroup != $row[$subgroup_tab_zelle]) {
if ($subgroup != 'FIRSTGROUP') {
$group_string = xml_close_tag($xml_groupnames_lecture['subgroup2']) . $group_string;
}
$group_string .= xml_open_tag($xml_groupnames_lecture['subgroup2'], $row[$subgroup_tab_zelle]);
$subgroup = $row[$subgroup_tab_zelle];
}
$data_object .= $group_string;
$object_counter += 1;
$data_object .= xml_open_tag($xml_groupnames_lecture['object'], $row['seminar_id']);
$sem_obj = new Seminar($row['seminar_id']);
while (list($key, $val) = each($xml_names_lecture)) {
if ($val == '') {
$val = $key;
}
if ($key == 'status') {
$data_object .= xml_tag($val, $SEM_TYPE[$row[$key]]['name']);
} elseif ($key == 'ort') {
$data_object .= xml_tag($val, $sem_obj->getDatesTemplate('dates/seminar_export_location'));
} elseif ($key == 'bereich' && $SEM_CLASS[$SEM_TYPE[$row['status']]['class']]['bereiche']) {
$data_object .= xml_open_tag($xml_groupnames_lecture['childgroup3']);
$pathes = get_sem_tree_path($row['seminar_id']);
if (is_array($pathes)) {
foreach ($pathes as $path_name) {
$data_object .= xml_tag($val, $path_name);
}
} else {
$data_object .= xml_tag($val, 'n.a.');
}
$data_object .= xml_close_tag($xml_groupnames_lecture['childgroup3']);
} elseif ($key == 'admission_turnout') {
$data_object .= xml_open_tag($val, $row['admission_type'] ? _('max.') : _('erw.'));
$data_object .= $row[$key];
$data_object .= xml_close_tag($val);
} elseif ($key == 'teilnehmer_anzahl_aktuell') {
$count_statement->execute(array($row['seminar_id']));
$count = $count_statement->fetchColumn();
$count_statement->closeCursor();
$data_object .= xml_tag($val, $count);
} elseif ($key == 'metadata_dates') {
$data_object .= xml_open_tag($xml_groupnames_lecture['childgroup1']);
$vorb = vorbesprechung($row['seminar_id'], 'export');
if ($vorb != false) {
$data_object .= xml_tag($val[0], $vorb);
}
if (($first_date = SeminarDB::getFirstDate($row['seminar_id'])) && count($first_date)) {
$really_first_date = new SingleDate($first_date[0]);
$data_object .= xml_tag($val[1], $really_first_date->getDatesExport());
}
$data_object .= xml_tag($val[2], $sem_obj->getDatesExport());
$data_object .= xml_close_tag($xml_groupnames_lecture["childgroup1"]);
} elseif ($key == 'Institut_id') {
$data_object .= xml_tag($val, $row['heimateinrichtung'], array('key' => $row[$key]));
} elseif ($row[$key] != '') {
$data_object .= xml_tag($val, $row[$key]);
}
}
$data_object .= "<" . $xml_groupnames_lecture['childgroup2'] . ">\n";
$inner_statement->execute(array($row['seminar_id']));
while ($inner = $inner_statement->fetch(PDO::FETCH_ASSOC)) {
if ($ex_person_details) {
$persons[$inner['user_id']] = true;
}
$content_string = $inner['Vorname'] . ' ' . $inner['Nachname'];
if ($inner['title_front'] != '') {
$content_string = $inner['title_front'] . ' ' . $content_string;
}
if ($inner['title_rear'] != '') {
$content_string .= ', ' . $inner['title_rear'];
}
$data_object .= xml_tag($xml_groupnames_lecture['childobject2'], $content_string, array('key' => $inner['username']));
}
$data_object .= xml_close_tag($xml_groupnames_lecture['childgroup2']);
// freie Datenfelder ausgeben
$data_object .= export_datafields($row['seminar_id'], $xml_groupnames_lecture['childgroup4'], $xml_groupnames_lecture['childobject4'], 'sem', $row['status']);
$data_object .= xml_close_tag($xml_groupnames_lecture['object']);
reset($xml_names_lecture);
output_data($data_object, $o_mode);
$data_object = '';
}
if ($do_subgroup && $subgroup != 'FIRSTGROUP') {
$data_object .= xml_close_tag($xml_groupnames_lecture['subgroup2']);
}
if ($do_group && $group != 'FIRSTGROUP') {
$data_object .= xml_close_tag($xml_groupnames_lecture['subgroup1']);
}
$data_object .= xml_close_tag($xml_groupnames_lecture['group']);
output_data($data_object, $o_mode);
}
/**
* Export action
*/
public function export_csv_action()
{
$filter_config = $this->getFilterConfig();
unset($filter_config['contents']);
if (empty($filter_config)) {
return;
}
$sortby = $GLOBALS['user']->cfg->getValue('MEINE_SEMINARE_SORT');
$config_my_course_type_filter = $GLOBALS['user']->cfg->getValue('MY_COURSES_TYPE_FILTER');
$courses = $this->getCourses(array('sortby' => $sortby, 'sortFlag' => 'asc', 'typeFilter' => $config_my_course_type_filter, 'view_filter' => $filter_config));
$view_filters = $this->getViewFilters();
$data = array();
foreach ($courses as $course_id => $course) {
$sem = new Seminar(Course::buildExisting($course));
$row = array();
if (in_array('number', $filter_config)) {
$row['number'] = $course['VeranstaltungsNummer'];
}
if (in_array('name', $filter_config)) {
$row['name'] = $course['Name'];
}
if (in_array('type', $filter_config)) {
$row['type'] = sprintf('%s: %s', $sem->getSemClass()['name'], $sem->getSemType()['name']);
}
if (in_array('room_time', $filter_config)) {
$_room = $sem->getDatesExport(array('semester_id' => $this->semester->id, 'show_room' => true));
$row['room_time'] = $_room ?: _('nicht angegeben');
}
if (in_array('teachers', $filter_config)) {
$row['teachers'] = implode(', ', array_map(function ($d) {
return $d['fullname'];
}, $course['dozenten']));
}
if (in_array('members', $filter_config)) {
$row['members'] = $course['teilnehmer'];
}
if (in_array('waiting', $filter_config)) {
$row['waiting'] = $course['waiting'];
}
if (in_array('preliminary', $filter_config)) {
$row['preliminary'] = $course['prelim'];
}
$data[$course_id] = $row;
}
$captions = array();
foreach ($filter_config as $index) {
$captions[$index] = $view_filters[$index];
}
$tmpname = md5(uniqid('Veranstaltungsexport'));
if (array_to_csv($data, $GLOBALS['TMP_PATH'] . '/' . $tmpname, $captions)) {
$this->redirect(GetDownloadLink($tmpname, 'Veranstaltungen_Export.csv', 4, 'force'));
return;
}
}
public function getTypeExplained()
{
$ret = '';
if ($this->termin_id) {
$ret = _("Einzeltermin der Veranstaltung");
if (get_object_type($this->termin_id, array('date'))) {
$termin = new SingleDate($this->termin_id);
$ret .= chr(10) . '(' . $termin->toString() . ')';
}
} elseif ($this->metadate_id) {
$ret = _("alle Termine einer regelmäßigen Zeit");
if ($cycle = SeminarCycleDate::find($this->metadate_id)) {
$ret .= chr(10) . ' (' . $cycle->toString('full') . ')';
}
} elseif ($this->seminar_id) {
$ret = _("alle regelmäßigen und unregelmäßigen Termine der Veranstaltung");
if (get_object_type($this->seminar_id, array('sem'))) {
$course = new Seminar($this->seminar_id);
$ret .= chr(10) . ' (' . $course->getDatesExport(array('short' => true, 'shrink' => true)) . ')';
}
} else {
$ret = _("Kein Typ zugewiesen");
}
return $ret;
}
function print_result()
{
ob_start();
global $_fullname_sql, $SEM_TYPE, $SEM_CLASS;
if (is_array($this->sem_browse_data['search_result']) && count($this->sem_browse_data['search_result'])) {
if (!is_object($this->sem_tree)) {
$this->sem_tree = new StudipSemTreeViewSimple($this->sem_browse_data["start_item_id"], $this->sem_number, is_array($this->sem_browse_data['sem_status']) ? $this->sem_browse_data['sem_status'] : false, !(is_object($GLOBALS['perm']) && $GLOBALS['perm']->have_perm(get_config('SEM_VISIBILITY_PERM'))));
}
$the_tree = $this->sem_tree->tree;
SkipLinks::addIndex(_("Suchergebnis"), 'sem_search_result', 90);
list($group_by_data, $sem_data) = $this->get_result();
echo "\n<table class='default' id=\"sem_search_result\" border=\"0\" align=\"center\" cellspacing=0 cellpadding=2 width = \"99%\">\n";
echo "<caption>" . sprintf(_(" %s Veranstaltungen gefunden %s, Gruppierung: %s"), count($sem_data), $this->sem_browse_data['sset'] ? _("(Suchergebnis)") : "", $this->group_by_fields[$this->sem_browse_data['group_by']]['name']) . "</caption>";
foreach ($group_by_data as $group_field => $sem_ids) {
echo "\n<tr><th colspan='5'>";
switch ($this->sem_browse_data["group_by"]) {
case 0:
echo htmlReady($this->search_obj->sem_dates[$group_field]['name']);
break;
case 1:
if ($the_tree->tree_data[$group_field]) {
echo htmlReady($the_tree->getShortPath($group_field));
if (is_object($this->sem_tree)) {
echo $this->sem_tree->getInfoIcon($group_field);
}
} else {
echo _("keine Studienbereiche eingetragen");
}
break;
case 3:
echo htmlReady($SEM_TYPE[$group_field]["name"] . " (" . $SEM_CLASS[$SEM_TYPE[$group_field]["class"]]["name"] . ")");
break;
default:
echo htmlReady($group_field);
break;
}
echo "</th></tr><tr>";
ob_end_flush();
ob_start();
if (is_array($sem_ids['Seminar_id'])) {
if ($this->sem_browse_data["default_sem"] != 'all') {
$current_semester_id = SemesterData::GetSemesterIdByIndex($this->sem_browse_data["default_sem"]);
}
while (list($seminar_id, ) = each($sem_ids['Seminar_id'])) {
// create instance of seminar-object
$seminar_obj = new Seminar($seminar_id);
// is this sem a studygroup?
$studygroup_mode = SeminarCategories::GetByTypeId($seminar_obj->getStatus())->studygroup_mode;
$sem_name = $SEM_TYPE[key($sem_data[$seminar_id]["status"])]["name"] . ": " . key($sem_data[$seminar_id]["Name"]);
$seminar_number = key($sem_data[$seminar_id]['VeranstaltungsNummer']);
if ($studygroup_mode) {
$sem_name .= ' (' . _("Studiengruppe");
if ($seminar_obj->admission_prelim) {
$sem_name .= ', ' . _("Zutritt auf Anfrage");
}
$sem_name .= ')';
echo '<td width="1%">';
echo StudygroupAvatar::getAvatar($seminar_id)->getImageTag(Avatar::SMALL, array('title' => htmlReady($seminar_obj->getName())));
echo '</td>';
} else {
$sem_number_start = key($sem_data[$seminar_id]["sem_number"]);
$sem_number_end = key($sem_data[$seminar_id]["sem_number_end"]);
if ($sem_number_start != $sem_number_end) {
$sem_name .= " (" . $this->search_obj->sem_dates[$sem_number_start]['name'] . " - ";
$sem_name .= ($sem_number_end == -1 ? _("unbegrenzt") : $this->search_obj->sem_dates[$sem_number_end]['name']) . ")";
} elseif ($this->sem_browse_data["group_by"]) {
$sem_name .= " (" . $this->search_obj->sem_dates[$sem_number_start]['name'] . ")";
}
echo '<td width="1%">';
echo CourseAvatar::getAvatar($seminar_id)->getImageTag(Avatar::SMALL, array('title' => htmlReady($seminar_obj->getName())));
echo '</td>';
}
$send_from_search = URLHelper::getUrl(basename($_SERVER['PHP_SELF']), array('keep_result_set' => 1, 'cid' => null));
$send_from_search_link = UrlHelper::getLink($this->target_url, array($this->target_id => $seminar_id, 'cid' => null, 'send_from_search' => 1, 'send_from_search_page' => $send_from_search));
echo '<td width="66%" colspan="2">';
echo '<a href="' . $send_from_search_link . '">';
if (Config::get()->IMPORTANT_SEMNUMBER && $seminar_number) {
echo htmlReady($seminar_number) . " ";
}
echo htmlReady($sem_name) . '</a><br>';
//create Turnus field
if ($studygroup_mode) {
echo "<div style=\"font-size:smaller\">" . htmlReady(substr($seminar_obj->description, 0, 100)) . "</div>";
} else {
$temp_turnus_string = $seminar_obj->getDatesExport(array('short' => true, 'shrink' => true, 'semester_id' => $current_semester_id));
//Shorten, if string too long (add link for details.php)
if (strlen($temp_turnus_string) > 70) {
$temp_turnus_string = htmlReady(substr($temp_turnus_string, 0, strpos(substr($temp_turnus_string, 70, strlen($temp_turnus_string)), ",") + 71));
$temp_turnus_string .= " ... <a href=\"{$send_from_search_link}\">(" . _("mehr") . ")</a>";
} else {
$temp_turnus_string = htmlReady($temp_turnus_string);
}
if (!Config::get()->IMPORTANT_SEMNUMBER) {
echo "<div style=\"margin-left:5px;font-size:smaller\">" . htmlReady($seminar_number) . "</div>";
}
echo "<div style=\"margin-left:5px;font-size:smaller\">" . $temp_turnus_string . "</div>";
}
echo '</td>';
echo "<td align=\"right\">(";
$doz_name = array();
$c = 0;
reset($sem_data[$seminar_id]['fullname']);
foreach ($sem_data[$seminar_id]['username'] as $anzahl1) {
if ($c == 0) {
list($d_name, $anzahl2) = each($sem_data[$seminar_id]['fullname']);
$c = $anzahl2 / $anzahl1;
$doz_name = array_merge($doz_name, array_fill(0, $c, $d_name));
}
--$c;
}
$doz_uname = array_keys($sem_data[$seminar_id]['username']);
$doz_position = array_keys($sem_data[$seminar_id]['position']);
if (count($doz_name)) {
if (count($doz_position) != count($doz_uname)) {
$doz_position = range(1, count($doz_uname));
}
array_multisort($doz_position, $doz_name, $doz_uname);
$i = 0;
foreach ($doz_name as $index => $value) {
if ($value) {
// hide dozenten with empty username
if ($i == 4) {
echo "... <a href=\"{$send_from_search_link}\">(" . _("mehr") . ")</a>";
break;
}
echo "<a href=\"" . UrlHelper::getLink('dispatch.php/profile', array('username' => $doz_uname[$index])) . "\">" . htmlReady($value) . "</a>";
if ($i != count($doz_name) - 1) {
echo ", ";
}
}
++$i;
}
echo ")</td></tr>";
}
}
}
}
echo "</table>";
} elseif ($this->search_obj->search_button_clicked && !$this->search_obj->new_search_button_clicked) {
if ($this->search_obj->found_rows === false) {
$details = array(_("Der Suchbegriff fehlt oder ist zu kurz"));
}
if (!isset($details)) {
echo MessageBox::info(_("Ihre Suche ergab keine Treffer"), $details);
} else {
echo MessageBox::error(_("Ihre Suche ergab keine Treffer"), $details);
}
$this->sem_browse_data["sset"] = 0;
}
ob_end_flush();
}
