function up()
{
$db = DbManager::get();
$db->exec("ALTER TABLE `datafields` CHANGE `type` `type` ENUM('bool','textline','textarea','selectbox','date','time','email','phone','radio','combo','link','selectboxmultiple') NOT NULL DEFAULT 'textline'");
$db->exec("ALTER TABLE `datafields` ADD `is_userfilter` TINYINT UNSIGNED NOT NULL DEFAULT '0' AFTER `is_required`");
SimpleORMap::expireTableScheme();
}
/**
* @see lib/classes/Config::fetchData()
*/
protected function fetchData($data = null)
{
if ($data !== null) {
$this->data = $data;
} else {
$this->data = array();
foreach (Config::get()->getFields('user') as $field) {
$this->data[$field] = Config::get()->{$field};
$metadata[$field] = Config::get()->getMetadata($field);
}
$db = DbManager::get();
$rs = $db->query("SELECT field, value FROM user_config WHERE user_id = " . $db->quote($this->user_id));
while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
switch ($metadata[$row['field']]['type']) {
case 'integer':
$value = (int) $row['value'];
break;
case 'boolean':
$value = (bool) $row['value'];
break;
case 'array':
$value = studip_utf8decode((array) json_decode($row['value'], true));
break;
default:
$value = $row['value'];
}
$this->data[$row['field']] = $value;
}
}
}
public static function findByUser($user_id)
{
$db = DbManager::get();
$st = $db->prepare("SELECT user_studiengang.*, abschluss.name as degree_name,\n studiengaenge.name as studycourse_name\n FROM user_studiengang\n LEFT JOIN abschluss USING (abschluss_id)\n LEFT JOIN studiengaenge USING (studiengang_id)\n WHERE user_id = ? ORDER BY studycourse_name");
$st->execute(array($user_id));
$ret = array();
while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
$ret[] = self::buildExisting($row);
}
return $ret;
}
function up()
{
$db = DbManager::get();
$db->exec("ALTER TABLE `message` DROP `chat_id`, DROP `readed`");
$db->exec("ALTER TABLE `message_user` DROP `confirmed_read`");
try {
$db->exec("ALTER TABLE `message_user` DROP COLUMN `dont_delete`");
} catch (Exception $e) {
}
$db->exec("ALTER TABLE `user_info` DROP `guestbook`");
$db->exec("DROP TABLE object_rate");
$db->exec("DROP TABLE object_user");
$db->exec("DROP TABLE px_topics");
$db->exec("DROP TABLE rss_feeds");
$db->exec("DELETE FROM user_visibility_settings WHERE plugin IS NOT NULL");
$db->exec("DELETE FROM user_visibility_settings WHERE identifier = 'plugins'");
SimpleORMap::expireTableScheme();
}
public function getRecipients()
{
if ($this->relations['receivers'] === null) {
$sql = "SELECT user_id,vorname,nachname,username,title_front,title_rear,perms,motto FROM\n message_user\n INNER JOIN auth_user_md5 aum USING(user_id)\n LEFT JOIN user_info ui USING(user_id)\n WHERE message_id=? AND snd_rec='rec'\n ORDER BY Nachname";
$params = array($this->id);
} else {
$sql = "SELECT user_id,vorname,nachname,username,title_front,title_rear,perms,motto FROM\n auth_user_md5 aum\n LEFT JOIN user_info ui USING(user_id)\n WHERE aum.user_id IN(?)\n ORDER BY Nachname";
$params = array($this->receivers->pluck('user_id'));
}
$db = DbManager::get();
return new SimpleCollection($db->fetchAll($sql, $params, function ($data) {
$user_id = $data['user_id'];
unset($data['user_id']);
$user = User::build($data);
$ret = $user->toArray('username vorname nachname');
$ret['fullname'] = $user->getFullname();
$ret['user_id'] = $user_id;
return $ret;
}));
}
/**
* Display the list of room requests
*/
public function index_action()
{
$this->url_params = array();
if (Request::get('origin') !== null) {
$this->url_params['origin'] = Request::get('origin');
}
$room_requests = RoomRequest::findBySQL(sprintf('seminar_id = %s ORDER BY seminar_id, metadate_id, termin_id', DbManager::get()->quote($this->course_id)));
$this->room_requests = $room_requests;
$this->request_id = Request::option('request_id');
$actions = new ActionsWidget();
$actions->addLink(_('Neue Raumanfrage erstellen'), $this->url_for('course/room_requests/new/' . $this->course_id), Icon::create('add', 'clickable'));
Sidebar::get()->addWidget($actions);
if ($GLOBALS['perm']->have_perm("admin")) {
$list = new SelectorWidget();
$list->setUrl("?#admin_top_links");
$list->setSelectParameterName("cid");
foreach (AdminCourseFilter::get()->getCoursesForAdminWidget() as $seminar) {
$list->addElement(new SelectElement($seminar['Seminar_id'], $seminar['Name']), 'select-' . $seminar['Seminar_id']);
}
$list->setSelection($this->course_id);
Sidebar::get()->addWidget($list);
}
}
public static function findByUser($user_id)
{
$db = DbManager::get();
return $db->fetchAll("SELECT user_inst.*, Institute.Name as institute_name\n FROM user_inst\n LEFT JOIN Institute USING (institut_id)\n WHERE user_id = ? ORDER BY priority,Institute.Name", array($user_id), __CLASS__ . '::buildExisting');
}
$sem_browse_obj->sem_number[0] = $sem_number;
}
switch ($level) {
case "sbb":
$sem_browse_obj->sem_browse_data['start_item_id'] = $id;
$sem_browse_obj->get_sem_range($id, false);
$sem_browse_obj->show_result = true;
$sem_browse_obj->sem_browse_data['sset'] = false;
$the_tree = $sem_browse_obj->sem_tree->tree;
$bereich_typ = _("Studienbereich");
$head_text = _("Übersicht aller Veranstaltungen eines Studienbereichs");
$intro_text = sprintf(_("Alle Veranstaltungen, die dem Studienbereich: <br><b>%s</b><br> zugeordnet wurden."), htmlReady($the_tree->getShortPath($id)));
$excel_text = strip_tags(DecodeHtml($intro_text));
break;
case "s":
$db = DbManager::get();
$bereich_typ = _("Einrichtung");
$head_text = _("Übersicht aller Veranstaltungen einer Einrichtung");
$intro_text = sprintf(_("Alle Veranstaltungen der Einrichtung: <b>%s</b>"), htmlReady(Institute::find($id)->name));
$excel_text = strip_tags(DecodeHtml($intro_text));
$parameters = array($id);
if ($show_semester) {
$query = "SELECT seminar_inst.seminar_id\n FROM seminar_inst\n LEFT JOIN seminare AS s ON (seminar_inst.seminar_id = s.Seminar_id)\n INNER JOIN semester_data sd\n ON ((s.start_time <= sd.beginn AND sd.beginn <= (s.start_time + s.duration_time )\n OR (s.start_time <= sd.beginn AND s.duration_time = -1))\n AND semester_id = ?)\n WHERE seminar_inst.Institut_id = ?";
array_unshift($parameters, $show_semester);
} else {
$query = "SELECT seminar_inst.seminar_id\n FROM seminar_inst\n LEFT JOIN seminare AS s ON (seminar_inst.seminar_id = s.Seminar_id)\n WHERE seminar_inst.Institut_id = ?";
}
if (!$GLOBALS['perm']->have_perm(get_config('SEM_VISIBILITY_PERM'))) {
$query .= " AND s.visible = 1";
}
$statement = DBManager::get()->prepare($query);
public function setAlgorithmRun($state)
{
$this->hasAlgorithmRun = (bool) $state;
$db = DbManager::get();
return $db->execute("UPDATE coursesets SET algorithm_run = ? WHERE set_id = ?", array($this->hasAlgorithmRun, $this->getId()));
}
public static function findByUser($user_id)
{
$db = DbManager::get();
return $db->fetchAll("SELECT admission_seminar_user.*, seminare.Name as course_name\n FROM admission_seminar_user\n LEFT JOIN seminare USING (seminar_id)\n WHERE user_id = ? ORDER BY seminare.Name", array($user_id), __CLASS__ . '::buildExisting');
}
public static function existsByCycle($metadate_id, $is_open = false)
{
$db = DbManager::get();
$id = self::existsForSQL(($is_open ? "closed = 0 AND " : "") . "metadate_id = " . $db->quote($metadate_id));
return $id;
}
/**
* get all statusgruppen for one user and one range
*
* @access public
* @param string $course_id
* @param string $user_id
* @return array ( statusgruppe_id => name)
*/
function GetGroupsByCourseAndUser($course_id, $user_id)
{
$st = DbManager::get()->prepare("SELECT statusgruppe_id, a.name
FROM statusgruppen a
INNER JOIN statusgruppe_user b USING (statusgruppe_id)
WHERE user_id = ? AND a.range_id = ?
ORDER BY a.position");
$st->execute(array($user_id, $course_id));
return $st->fetchGrouped(PDO::FETCH_COLUMN);
}
/**
* @return bool|string
*/
function auth_doregister()
{
global $_language_path;
$this->error_msg = "";
// check for direct link to register2.php
if (!$_SESSION['_language'] || $_SESSION['_language'] == "") {
$_SESSION['_language'] = get_accepted_languages();
}
$_language_path = init_i18n($_SESSION['_language']);
$this->auth["uname"] = Request::username('username');
// This provides access for "crcregister.ihtml"
$validator = new email_validation_class();
// Klasse zum Ueberpruefen der Eingaben
$validator->timeout = 10;
// Wie lange warten wir auf eine Antwort des Mailservers?
if (!Seminar_Session::check_ticket(Request::option('login_ticket'))) {
return false;
}
$username = trim(Request::get('username'));
$Vorname = trim(Request::get('Vorname'));
$Nachname = trim(Request::get('Nachname'));
// accept only registered domains if set
$cfg = Config::GetInstance();
$email_restriction = $cfg->getValue('EMAIL_DOMAIN_RESTRICTION');
if ($email_restriction) {
$Email = trim(Request::get('Email')) . '@' . trim(Request::get('emaildomain'));
} else {
$Email = trim(Request::get('Email'));
}
if (!$validator->ValidateUsername($username)) {
$this->error_msg = $this->error_msg . _("Der gewählte Benutzername ist zu kurz!") . "<br>";
return false;
}
// username syntaktisch falsch oder zu kurz
// auf doppelte Vergabe wird weiter unten getestet.
if (!$validator->ValidatePassword(Request::quoted('password'))) {
$this->error_msg = $this->error_msg . _("Das Passwort ist zu kurz!") . "<br>";
return false;
}
if (!$validator->ValidateName($Vorname)) {
$this->error_msg = $this->error_msg . _("Der Vorname fehlt oder ist unsinnig!") . "<br>";
return false;
}
// Vorname nicht korrekt oder fehlend
if (!$validator->ValidateName($Nachname)) {
$this->error_msg = $this->error_msg . _("Der Nachname fehlt oder ist unsinnig!") . "<br>";
return false;
// Nachname nicht korrekt oder fehlend
}
if (!$validator->ValidateEmailAddress($Email)) {
$this->error_msg = $this->error_msg . _("Die E-Mail-Adresse fehlt oder ist falsch geschrieben!") . "<br>";
return false;
}
// E-Mail syntaktisch nicht korrekt oder fehlend
$REMOTE_ADDR = $_SERVER["REMOTE_ADDR"];
$Zeit = date("H:i:s, d.m.Y", time());
if (!$validator->ValidateEmailHost($Email)) {
// Mailserver nicht erreichbar, ablehnen
$this->error_msg = $this->error_msg . _("Der Mailserver ist nicht erreichbar, bitte überprüfen Sie, ob Sie E-Mails mit der angegebenen Adresse verschicken und empfangen können!") . "<br>";
return false;
} else {
// Server ereichbar
if (!$validator->ValidateEmailBox($Email)) {
// aber user unbekannt. Mail an abuse!
StudipMail::sendAbuseMessage("Register", "Emailbox unbekannt\n\nUser: {$username}\nEmail: {$Email}\n\nIP: {$REMOTE_ADDR}\nZeit: {$Zeit}\n");
$this->error_msg = $this->error_msg . _("Die angegebene E-Mail-Adresse ist nicht erreichbar, bitte überprüfen Sie Ihre Angaben!") . "<br>";
return false;
} else {
// Alles paletti, jetzt kommen die Checks gegen die Datenbank...
}
}
$check_uname = StudipAuthAbstract::CheckUsername($username);
if ($check_uname['found']) {
// error_log("username schon vorhanden", 0);
$this->error_msg = $this->error_msg . _("Der gewählte Benutzername ist bereits vorhanden!") . "<br>";
return false;
// username schon vorhanden
}
if (count(User::findBySQL("Email LIKE " . DbManager::get()->quote($Email)))) {
$this->error_msg = $this->error_msg . _("Die angegebene E-Mail-Adresse wird bereits von einem anderen Benutzer verwendet. Sie müssen eine andere E-Mail-Adresse angeben!") . "<br>";
return false;
// Email schon vorhanden
}
// alle Checks ok, Benutzer registrieren...
$hasher = UserManagement::getPwdHasher();
$new_user = new User();
$new_user->username = $username;
$new_user->perms = 'user';
$new_user->password = $hasher->HashPassword(Request::get('password'));
$new_user->vorname = $Vorname;
$new_user->nachname = $Nachname;
$new_user->email = $Email;
$new_user->geschlecht = Request::int('geschlecht');
$new_user->title_front = trim(Request::get('title_front', Request::get('title_front_chooser')));
$new_user->title_rear = trim(Request::get('title_rear', Request::get('title_rear_chooser')));
$new_user->auth_plugin = 'standard';
$new_user->store();
if ($new_user->user_id) {
self::sendValidationMail($new_user);
$this->auth["perm"] = $new_user->perms;
return $new_user->user_id;
}
}
/**
* delete config entry from database
* @param string name of entry
* @throws InvalidArgumentException
* @return integer number of deleted rows
*/
function delete($field)
{
if (!$field) {
throw new InvalidArgumentException("config fieldname is mandatory");
}
$deleted = ConfigEntry::deleteBySql("field=" . DbManager::get()->quote($field));
if ($deleted) {
$this->fetchData();
}
return $deleted;
}
public function execute($last_result, $parameters = array())
{
$verbose = $parameters['verbose'];
$sets = DbManager::get()->fetchFirst("SELECT DISTINCT cr.set_id FROM courseset_rule cr INNER JOIN coursesets USING(set_id)\n WHERE type = 'ParticipantRestrictedAdmission' AND algorithm_run = 0");
if (count($sets)) {
if ($verbose) {
echo date('r') . ' - Starting seat distribution ' . chr(10);
$old_logger = Log::get()->getHandler();
$old_log_level = Log::get()->getLogLevel();
@mkdir($GLOBALS['TMP_PATH'] . '/seat_distribution_logs');
$logfile = $GLOBALS['TMP_PATH'] . '/seat_distribution_logs/' . date('Y-m-d-H-i') . '_seat_distribution.log';
if (is_dir($GLOBALS['TMP_PATH'] . '/seat_distribution_logs')) {
Log::get()->setHandler($logfile);
Log::get()->setLogLevel(Log::DEBUG);
echo 'logging to ' . $logfile . chr(10);
} else {
echo 'could not create directory ' . $GLOBALS['TMP_PATH'] . '/seat_distribution_logs' . chr(10);
}
}
foreach ($sets as $set_id) {
$courseset = new CourseSet($set_id);
if ($courseset->isSeatDistributionEnabled() && !$courseset->hasAlgorithmRun() && $courseset->getSeatDistributionTime() < time()) {
if ($verbose) {
echo ++$i . ' ' . $courseset->getId() . ' : ' . $courseset->getName() . chr(10);
$applicants = AdmissionPriority::getPriorities($set_id);
$courses = SimpleCollection::createFromArray(Course::findMany($courseset->getCourses()))->toGroupedArray('seminar_id', words('name veranstaltungsnummer'));
$captions = array(_("Nachname"), _("Vorname"), _("Nutzername"), _('Nutzer-ID'), _('Veranstaltung-ID'), _("Veranstaltung"), _("Nummer"), _("Priorität"));
$data = array();
$users = User::findEachMany(function ($user) use($courses, $applicants, &$data) {
$app_courses = $applicants[$user->id];
asort($app_courses);
foreach ($app_courses as $course_id => $prio) {
$row = array();
$row[] = $user->nachname;
$row[] = $user->vorname;
$row[] = $user->username;
$row[] = $user->id;
$row[] = $course_id;
$row[] = $courses[$course_id]['name'];
$row[] = $courses[$course_id]['veranstaltungsnummer'];
$row[] = $prio;
$data[] = $row;
}
}, array_keys($applicants), 'ORDER BY Nachname');
$applicants_file = $GLOBALS['TMP_PATH'] . '/seat_distribution_logs/applicants_' . $set_id . '.csv';
if (array_to_csv($data, $applicants_file, $captions)) {
echo 'applicants written to ' . $applicants_file . chr(10);
}
}
$courseset->distributeSeats();
}
}
if ($verbose) {
Log::get()->setHandler($old_logger);
Log::get()->setLogLevel($old_log_level);
}
} else {
if ($verbose) {
echo date('r') . ' - Nothing to do' . chr(10);
}
}
}
/**
* fetch number of rows for a table
* for innodb this is not exact, but much faster than count(*)
*
* @param string $table name of database table
* @return int number of rows
*/
function count_table_rows($table)
{
$stat = DbManager::get()->fetchOne("SHOW TABLE STATUS LIKE ?", array($table));
return (int) $stat['Rows'];
}
public function delete_tag_action()
{
CSRFProtection::verifyUnsafeRequest();
DbManager::get()->execute("DELETE FROM message_tags WHERE user_id=? AND tag LIKE ?", array($GLOBALS['user']->id, Request::get('tag')));
PageLayout::postMessage(MessageBox::success(_('Schlagwort gelöscht!')));
$this->redirect($this->url_for('messages/overview'));
}
public static function getMaxPriority($seminar_id)
{
return DbManager::get()->fetchColumn("SELECT MAX(priority) FROM themen WHERE seminar_id=?", array($seminar_id));
}
/**
* How many users have gotten a seat in distribution?
*
* @return Number of users who where lucky enough to be course members now.
*/
public function countParticipatingUsers($course_ids, $user_ids)
{
$distributed_users = array();
$sum = function ($r) use(&$distributed_users) {
$distributed_users[$r['user_id']] += $r['c'];
};
$db = DbManager::get();
$db->fetchAll("SELECT user_id, COUNT(*) as c FROM seminar_user\n WHERE seminar_id IN(?) AND user_id IN(?) GROUP BY user_id", array($course_ids, $user_ids), $sum);
$db->fetchAll("SELECT user_id, COUNT(*) as c FROM admission_seminar_user\n WHERE seminar_id IN(?) AND user_id IN(?) GROUP BY user_id", array($course_ids, $user_ids), $sum);
return $distributed_users;
}
