<?php

class TourController extends ControllerBase implements ControllerInterface
{
    public function allowUnauthenticated()
    {
	return [];
    }

    public function getNavigation()
    {
	if (empty($_SESSION['userid']))
	    return [];

	$list = [];
	$list[] = ['title' => 'Home',
		   'url' => Application::url()];
	$list[] = ['title' => 'Status',
		   'url' => Application::url('tour', 'index', $this->tour)];
	if ($this->tour && $this->tour->isAdmin() && $this->tour->isPlanned())
	    $list[] = ['title' => 'Neu einladen',
		       'url' => Application::url('tour', 'invite', $this->tour)];
	if ($this->tour && $this->tour->isAdmin() && $this->tour->isPlanned())
	    $list[] = ['title' => 'Termine',
		       'url' => Application::url('tour', 'dates', $this->tour)];
	if ($this->tour && $this->tour->isPlanned())
	    $list[] = ['title' => 'Planung',
		       'url' => Application::url('tour', 'plan', $this->tour)];
	if ($this->tour && $this->tour->isAdmin())
	    $list[] = ['title' => 'Matrix',
		       'url' => Application::url('tour', 'matrix', $this->tour)];
	$list[] = ['title' => 'Notizen',
		   'url' => Application::url('tour', 'notes', $this->tour)];
	$list[] = ['title' => 'Zwischenziele',
		   'url' => Application::url('tour', 'pov', $this->tour)];
	if ($this->tour && $this->tour->isAdmin()) {
	    $list[] = ['title' => 'Administration',
		       'url' => Application::url('tour', 'admin', $this->tour)];
	    $list[] = ['title' => 'Logbuch',
		       'url' => Application::url('tour', 'log', $this->tour)];
	}

	return $list;
    }

    public function indexAction($request, $response)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	$data = $this->tour->getBaseData();
	$data->information_html = Wiki::renderHTML($data->information);

	Application::get()->addJavascriptCode("load_dates();");

	$vars = array_merge(get_object_vars($data),
			    ['admin' => $this->tour->isAdmin(),
			     'notes' => $this->tour->getNotes(),
			     'members' => $this->tour->getMembers()]);

	$response->setData(Template::render('tour/index', $vars));
    }

    public function adminAction($request, $response)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	$form = new Form('tourplan');
	$form->add(new FormElement('text', ['name' => 'name',
					    'title' => 'Name',
					    'help' => 'z.B. Sauerland 2019',
					    'value' => $this->tour->get('name')]));
	$form->add(new FormElement('text', ['name' => 'urlkey',
					    'title' => 'URL-Key',
					    'help' => 'Keine Leer- oder Sonderzeichen, z.B. sl2019',
					    'value' => $this->tour->get('key')]));
	$form->add(new FormElement('number', ['name' => 'year',
					      'title' => 'Jahr',
					      'min' => date('Y'),
					      'max' => 2050,
					      'value' => $this->tour->get('year')]));
	$form->add(new FormElement('number', ['name' => 'duration',
					      'title' => 'Dauer in Tagen',
					      'min' => 1,
					      'value' => $this->tour->get('duration')]));

	$sql = "SELECT id AS value, name AS text FROM tour_status ORDER BY priority";
	$form->add(new FormElement('select', ['name' => 'tour_status_id',
					      'title' => 'Tour-Status',
					      'selected' => $this->tour->get('tour_status_id'),
					      'options' => $this->db->fetchObjectList($sql)]));
	$options = [];
	foreach ($this->tour->getDates() as $row)
	    $options[] = new Storage(['value' => $row->id, 'text' => $row->start_datum]);

	$form->add(new FormElement('select', ['name' => 'tour_date_id',
					      'title' => 'Datum',
					      'empty' => '-- bitte wählen --',
					      'selected' => $this->tour->get('tour_date_id'),
					      'options' => $options]));

	$form->add(new FormElement('textarea', ['name' => 'information',
						'title' => 'Tourinformationen',
						'help' => 'MoinMoin-typische Syntax erlaubt',
						'rows' => 5,
						'value' => $this->tour->get('information')]));

	$options = [];
	foreach ($this->tour->getMembers(false) as $row)
	    $options[] = new Storage(['value' => $row->id, 'text' => $row->name]);

	if (count($options)) {
	    $adminform = new Form('touradmin');
	    $adminform->add(new FormElement('select', ['name' => 'tour_member_id',
						       'title' => 'Weiterer Tourleiter',
						       'empty' => '-- Bitte wählen --',
						       'options' => $options]));
	    $adminformstr = $adminform->toString();
	} else {
	    $adminformstr = '';
	}

	$adminlist = $this->tour->getMembers(true);

	$response->setData(Template::render('tour/admin', ['form' => $form->toString(),
							   'adminform' => $adminformstr,
							   'adminlist' => $adminlist]));
    }

    public function ajaxAdmin($request, $response, $data)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	foreach (['name', 'urlkey', 'year', 'duration'] as $name)
	    if (!strlen($data[$name]))
		throw new Exception('Nicht alle Pflichtfelder ausgefüllt');

	if (!preg_match('/^[a-zA-Z0-9_\.-]+$/', $data['urlkey']))
	    throw new Exception('Sonderzeichen nicht im URL-Key erlaubt');

	$status_plan = new Tour_Status('plan', 'key');

	$ok = $this->db->update('tour', ['key' => $data['urlkey'],
					 'name' => $data['name'],
					 'year' => intval($data['year']),
					 'duration' => intval($data['duration']),
					 'tour_status_id' => $data['tour_status_id'],
					 'tour_date_id' => strlen($data['tour_date_id']) ? intval($data['tour_date_id']) : NULL,
					 'information' => strlen($data['information']) ? $data['information'] : NULL,
					 ], 'id='.$this->tour->id());

	if (!$ok)
	    throw new Exception('Tour-Daten konnten nicht aktualisiert werden');
    }

    public function ajaxTouradmin($request, $response, $data)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	$this->db->update('tour_member', ['admin' => true], 'id='.$data['tour_member_id']);

	$sql = sprintf("SELECT name FROM sys_user JOIN tour_member ON member_id = sys_user.id WHERE tour_member.id = %d",
		       $data['tour_member_id']);
	Tour_Log::add($this->tour->id(), sprintf("%s als Leiter hinzugefügt", $this->db->fetchValue($sql)));
    }

    public function ajaxDeladmin($request, $response, $data)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	$this->db->update('tour_member', ['admin' => false], 'id='.$data['id']);

	$sql = sprintf("SELECT name FROM sys_user JOIN tour_member ON member_id = sys_user.id WHERE tour_member.id = %d",
		       $data['id']);
	Tour_Log::add($this->tour->id(), sprintf("%s als Leiter gelöscht", $this->db->fetchValue($sql)));
    }

    protected function formatDates()
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$ok = new Tour_Date_Status('ok', 'key');
	$maybe = new Tour_Date_Status('maybe', 'key');
	$not = new Tour_Date_Status('nope', 'key');
	$sql = <<<EOS
	    SELECT
	      id,
	      (SELECT count(*) FROM tour_date_member WHERE tour_date_id = tour_date.id AND tour_date_status_id = {$ok->id()}) AS sum_ok,
	      (SELECT count(*) FROM tour_date_member WHERE tour_date_id = tour_date.id AND tour_date_status_id = {$maybe->id()}) AS sum_maybe,
	      (SELECT count(*) FROM tour_date_member WHERE tour_date_id = tour_date.id AND tour_date_status_id = {$not->id()}) AS sum_not,
	      start_date
	    FROM tour_date
	    WHERE tour_id = {$this->tour->id()}
	    ORDER BY start_date
EOS;

	$list = $this->db->fetchObjectList($sql);

	foreach ($list as &$row) {
	    $dt = new DateTime($row->start_date);
	    $row->year = $dt->format('Y');
	    $row->start_short = $dt->format('d.m.');
	    $dt->add(new DateInterval('P'.$this->tour->get('duration').'D'));
	    $row->end_short = $dt->format('d.m.');

	    if ($row->sum_ok >= 4)
		$row->maybe = true;
	    elseif ($row->sum_ok >= 3 && $row->sum_not == 0)
		$row->maybe = true;
	    else
		$row->maybe = false;
	}

	return Template::render('tour/datelist', ['tour_date_id' => $this->tour->get('tour_date_id'),
						  'list' => $list]);
    }

    public function ajaxDates($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$response->setData(['table' => $this->formatDates()]);
    }

    public function datesAction($request, $response)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	$plan = new Tour_Status('plan', 'key');

	$formstr = '';
	if ($this->tour->isAdmin() && $this->tour->get('tour_status_id') == $plan->id()) {
	    $form = new Form('newdate');
	    $form->setTitle('Neuer Termin');
	    $form->add(new FormElement('date', ['name' => 'start_date',
						'placeholder' => '2019-01-12',
						'value' => '']));
	    $formstr = '<hr>'.$form->toString();
	}

	Application::get()->addJavascriptCode("load_dates();");

	$response->setData(Template::render('tour/dates', ['admin' => $this->tour->isAdmin(),
							   'form' => $formstr]));
    }

    public function planAction($request, $response)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	$plan = new Tour_Status('plan', 'key');
	if ($this->tour->get('tour_status_id') != $plan->id())
	    return $response->setLocation(Application::url('tour', 'index', $this->tour));

	$list = $this->tour->getAvailability($_SESSION['userid']);

	$sql = sprintf("SELECT comment FROM tour_member WHERE tour_id = %d AND member_id = %d",
		       $this->tour->id(), $_SESSION['userid']);
	$comment = $this->db->fetchValue($sql);

	$form = new Form('tourmember');
	$form->setTitle('Allgemeiner Status');
	$form->add(new FormElement('text', ['name' => 'comment',
					    'help' => 'z.B. kein fahrbereites Motorrad',
					    'value' => $comment]));
	$formstr = '<hr>'.$form->toString();

	$response->setData(Template::render('tour/plan', ['list' => $list,
							  'form' => $formstr]));
    }

    public function ajaxTourmember($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	$ok = $this->db->update('tour_member', ['comment' => strlen($data['comment']) ? $data['comment'] : NULL],
				sprintf('tour_id = %d AND member_id = %d', $this->tour->id(), $_SESSION['userid']));

	if (strlen($data['comment']))
	    Tour_Log::add($this->tour->id(), sprintf("Neuer Status %s", $data['comment']));
	else
	    Tour_Log::add($this->tour->id(), "Status gelöscht");
    }

    public function inviteAction($request, $response)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	$user = new Sys_User();
	$userlist = $user->getUserList($this->tour->id());

	$response->setData(Template::render('tour/invite', ['list' => $userlist]));
    }

    public function ajaxTogglestatus($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	if (empty($data['user']))
	    $uid = $_SESSION['userid'];
	elseif ($this->tour->isAdmin())
	    $uid = $data['user'];
	else
	    $uid = $_SESSION['userid'];

	$this->tour->toggleDateStatus($data['id'], $uid);

	$sql =<<<EOS
	    SELECT
	      tour_date_status.key AS status_key,
	      tour_date_status.name AS status_text
	    FROM tour_date_member
	    LEFT JOIN tour_date_status ON tour_date_status_id = tour_date_status.id
	    LEFT JOIN tour_member ON tour_member_id = tour_member.id
	    WHERE tour_date_id = %d AND tour_member.member_id = %d
EOS;
	$sql = sprintf($sql, $data['id'], $uid);

	$response->setData($this->db->fetchAssoc($sql));
    }

    public function ajaxInvite($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	$this->tour->inviteMember($data['sys_user_id']);

	$user = new Sys_User($data['sys_user_id']);
	Tour_Log::add($this->tour->id(), sprintf("%s eingeladen", $user->get('name')));
    }

    public function ajaxNewdate($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	$start_date = false;

	if (preg_match('/^(\d+)-(\d+)-(\d+)$/', $data['start_date'], $match))
	    $start_date = $data['start_date'];
	elseif (preg_match('/^(\d+).(\d+).(\d+)$/', $data['start_date'], $match))
	    $start_date = sprintf('%d-%d-%d', $match[3], $match[2], $match[1]);
	else
	    throw new Exception('Kein Datum angegeben');

	$ok = $this->db->insertInto('tour_date', ['tour_id' => $this->tour->id(),
						  'start_date' => $start_date]);

	if (!$ok)
	    $response->setError('Fehler beim Speichern');
    }

    protected function formatPovList()
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	$sql = <<<EOS
	    SELECT
	      id,
	      destination
	    FROM tour_pov
	    WHERE tour_id = {$this->tour->id()}
	    ORDER BY seqnum, destination
EOS;

	$list = $this->db->fetchObjectList($sql);

	foreach ($list as &$row) {
	    $dt = new DateTime($row->start_date);
	    $row->year = $dt->format('Y');
	    $row->start_short = $dt->format('d.m.');
	    $dt->add(new DateInterval('P'.$this->tour->get('duration').'D'));
	    $row->end_short = $dt->format('d.m.');
	}

	return Template::render('tour/povlist', ['is_planned' => $this->tour->isPlanned(),
						 'list' => $list]);
    }

    public function ajaxPov($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$response->setData(['table' => $this->formatPovList()]);
    }

    public function povAction($request, $response)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	$plan = new Tour_Status('plan', 'key');
	if ($this->tour->get('tour_status_id') == $plan->id()) {
	    $form = new Form('newpov');
	    $form->setTitle('Neues Zwischenziel');
	    $form->add(new FormElement('text', ['name' => 'destination',
						'value' => '']));
	    $formstr = '<hr>'.$form->toString();
	}

	Application::get()->addJavascriptCode("load_pov();");

	$response->setData(Template::render('tour/pov', ['form' => $formstr]));
    }

    public function ajaxNewpov($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$plan = new Tour_Status('plan', 'key');
	if ($this->tour->get('tour_status_id') != $plan->id())
	    throw new Exception("Nicht mehr in der Planungsphase");

	$sql = sprintf("SELECT max(seqnum) FROM tour_pov WHERE tour_id = %d",
		       $this->tour->id());
	$max = intval($this->db->fetchValue($sql));

	$ok = $this->db->insertInto('tour_pov', ['tour_id' => $this->tour->id(),
						 'seqnum' => $max + 1,
						 'destination' => $data['destination']]);

	if (!$ok)
	    $response->setError('Fehler beim Speichern');

	Tour_Log::add($this->tour->id(), sprintf("Zwischenziel %s hinzugefügt", $data['destination']));
    }

    public function ajaxPovmove($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$sql = sprintf("SELECT seqnum FROM tour_pov WHERE tour_id = %d AND id = %d",
		       $this->tour->id(), $data['pov']);
	$seqnum = $this->db->fetchvalue($sql);

	if ($seqnum === false)
	    return $response->setError('Zwischenziel nicht gefunden');

	if ($data['direction'] == 'up') {
	    $sql = sprintf("SELECT id,seqnum FROM tour_pov WHERE tour_id = %d AND seqnum < %d ORDER BY seqnum DESC LIMIT 1",
			   $this->tour->id(), $seqnum);
	} elseif ($data['direction'] == 'down') {
	    $sql = sprintf("SELECT id,seqnum FROM tour_pov WHERE tour_id = %d AND seqnum > %d ORDER BY seqnum ASC LIMIT 1",
			   $this->tour->id(), $seqnum);
	} else {
	    return $response->setError("Unknown direction");
	}
	$other = $this->db->fetchObject($sql);

	$this->db->execute(sprintf("UPDATE tour_pov SET seqnum = %d WHERE id = %d", $seqnum, $other->id));
	$this->db->execute(sprintf("UPDATE tour_pov SET seqnum = %d WHERE id = %d", $other->seqnum, $data['pov']));

	return $ok;
    }

    public function ajaxPovdel($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$sql = sprintf("SELECT destination FROM tour_pov WHERE tour_id = %d AND id = %d",
		       $this->tour->id(), $data['pov']);
	$destination = $this->db->fetchvalue($sql);

	if ($destination === false)
	    return $response->setError('Zwischenziel nicht gefunden');

	$ok = $this->db->execute(sprintf("DELETE FROM tour_pov WHERE id = %d", $data['pov']));

	if (!$ok)
	    throw new Exception("Zwischenziel konne nicht gelöscht werden");

	Tour_Log::add($this->tour->id(), sprintf("Zwischenziel %s gelöscht", $destination));
    }

    public function matrixAction($request, $response)
    {
	if (empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	$info = $this->tour->getAvailabilityMatrix();

	$response->setData(Template::render('tour/matrix', ['is_planned' => $this->tour->isPlanned(),
							    'user' => $info['user'],
							    'list' => $info['list']]));
    }

    public function newmemberAction($request, $response)
    {
	if (!$this->app->isAdmin() && empty($this->tour))
	    return $response->setLocation($this->app->getBaseURL());

	$form = new Form('newbiker');
	$form->setTitle('Neuer Biker');
	$form->add(new FormElement('text', ['name' => 'name',
					    'title' => 'Name',
					    'value' => '']));
	$form->add(new FormElement('text', ['name' => 'email',
					    'title' => 'E-Mail',
					    'value' => '']));
	$form->add(new FormElement('text', ['name' => 'mobile',
					    'title' => 'Mobiltelefon',
					    'help' => 'Nur sichtbar für Tour-Mitglieder',
					    'placeholder' => '0150-1234567',
					    'value' => '']));

	$formstr = '<hr>'.$form->toString();

	if (!empty($this->tour))
	    Application::get()->addJavascriptCode("load_dates();");

	$response->setData($formstr);
    }

    public function ajaxNewbiker($request, $response, $data)
    {
	if (!strlen($data['name']))
	    throw new Exception("Name muß ausgfüllt werden");

	if (!strlen($data['email']))
	    throw new Exception("E-Mail muß ausgfüllt werden");

	$ok = $this->db->insertInto('sys_user', ['name' => $data['name'],
						 'email' => strtolower($data['email']),
						 'active' => true,
						 'passwd' => '',
						 'mobile' => strlen($data['mobile']) ? $data['mobile'] : NULL]);

	if ($ok && !empty($this->tour)) {
	    $id = $this->db->lastInsertId();

	    $ok = $this->db->insertInto('tour_member', ['tour_id' => $this->tour->id(),
							'member_id' => $id]);
	    if (!$ok)
		throw new Exception('Teilnehmer konnte nicht gesetzt werden');

	    $user = new Sys_User($id);
	    Tour_Log::add($this->tour->id(), sprintf("%s hinzugefügt", $user->get('name')));
	}

	return $ok;
    }

    public function newAction($request, $response)
    {
	if (!$this->app->isAdmin())
	    throw new Exception("Nur Administratoren können Touren anlegen");

	$user = new Sys_User();
	$userlist = $user->getUserList();

	$options = [];
	foreach ($userlist as $row)
	    $options[] = new Storage(['value' => $row->id, 'text' => $row->name]);

	$form = new Form('newtour');
	$form->setTitle('Neue Tour anlegen');
	$form->add(new FormElement('text', ['name' => 'name',
					    'title' => 'Name',
					    'help' => 'z.B. Sauerland 2019',
					    'value' => '']));
	$form->add(new FormElement('text', ['name' => 'urlkey',
					    'title' => 'URL-Key',
					    'help' => 'Keine Leer- oder Sonderzeichen, z.B. sl2019',
					    'value' => '']));
	$form->add(new FormElement('number', ['name' => 'year',
					      'title' => 'Jahr',
					      'min' => date('Y'),
					      'max' => 2050,
					      'value' => '']));
	$form->add(new FormElement('number', ['name' => 'duration',
					      'title' => 'Dauer',
					      'min' => 1,
					      'value' => '']));
	$form->add(new FormElement('select', ['name' => 'leader',
					      'title' => 'Tourleiter',
					      'empty' => '-- Bitte wählen --',
					      'options' => $options]));

	$response->setData(Template::render('page/formpage', ['form' => $form->toString()]));
    }

    public function ajaxNewtour($request, $response, $data)
    {
	if (!$this->app->isAdmin())
	    throw new Exception("Nur Administratoren können Touren anlegen");

	foreach (['name', 'urlkey', 'year', 'duration', 'leader'] as $name)
	    if (!strlen($data[$name]))
		throw new Exception('Alle Felder müssen ausgefüllt sein');

	if (!preg_match('/^[a-zA-Z0-9_\.-]+$/', $data['urlkey']))
	    throw new Exception('Sonderzeichen nicht im URL-Key erlaubt');

	$status_plan = new Tour_Status('plan', 'key');

	$ok = $this->db->insertInto('tour', ['key' => $data['urlkey'],
					     'name' => $data['name'],
					     'year' => intval($data['year']),
					     'duration' => intval($data['duration']),
					     'tour_status_id' => $status_plan->id(),
					     ]);

	if (!$ok)
	    throw new Exception('Neue Tour konnte nicht gespeichert werden');

	$tour_id = $this->db->lastInsertId();

	Tour_Log::add($tour_id, sprintf("Tour %s angelegt", $data['name']));

	$ok = $this->db->insertInto('tour_member', ['tour_id' => $tour_id,
						    'admin' => true,
						    'member_id' => $data['leader']]);
	if (!$ok)
	    throw new Exception('Tourleiter konnte nicht gesetzt werden');

	$user = new Sys_User($data['leader']);
	Tour_Log::add($tour_id, sprintf("%s als Leiter hinzugefügt", $user->get('name')));
    }

    public function notesAction($request, $response)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	$list = $this->tour->getNotes(true);

	$response->setData(Template::render('tour/notes', ['list' => $list]));
    }

    public function ajaxNotedel($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	$sql = sprintf("SELECT id FROM tour_note WHERE id = %d AND tour_id = %d AND sys_user_id = %d",
		       $data['id'], $this->tour->id(), $_SESSION['userid']);

	if (!$this->db->fetchValue($sql))
	    return $response->setError('Notiz nicht gefunden');

	$this->db->update('tour_note', ['deleted' => true], 'id='.intval($data['id']));
    }

    public function logAction($request, $response)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	if (!$this->tour->isAdmin())
	    throw new Exception("Keine Berechtigung");

	$sql = <<<EOS
	    SELECT
	      tour_log.id,
	      extract(day from logdate) || '.' || extract(month from logdate) || '.' || extract(year from logdate) AS date,
	      logdate,
	      logtext,
	      name
	    FROM tour_log
	    JOIN sys_user ON tour_log.sys_user_id = sys_user.id
	    WHERE tour_id = {$this->tour->id()}
	    ORDER BY logdate DESC, tour_log.sys_edit DESC
EOS;
	$list = $this->db->fetchObjectList($sql);

	$response->setData(Template::render('tour/log', ['list' => $list]));
    }

    public function notenewAction($request, $response)
    {
	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	$form = new Form('newnote');
	$form->setTitle('Neue Notiz');
	$form->setAction(Application::url('tour', 'preview', $this->tour));
	$form->setSecondButton('Preview');
	$form->setDescription(Template::render('wikisyntax', []));
	$form->add(new FormElement('textarea', ['name' => 'note',
						'title' => 'Text',
						'help' => 'MoinMoin-typische Syntax erlaubt',
						'rows' => 5,
						'value' => '']));

	$response->setData(Template::render('page/formpage', ['form' => $form->toString()]));
    }

    public function previewAction($request, $response)
    {
	if (!$request->isPost())
	    throw new Exception("Only POST requests allowed here");

	if (empty($this->tour))
	    throw new Exception("Keine Tour angegeben");

	$data = $request->getPost();

	$form = new Form('newnote');
	$form->setTitle('Neue Notiz');
	$form->setAction(Application::url('tour', 'preview', $this->tour));
	$form->setSecondButton('Preview');
	$form->setDescription(Template::render('wikisyntax', []));
	$form->add(new FormElement('textarea', ['name' => 'note',
						'title' => 'Text',
						'help' => 'MoinMoin-typische Syntax erlaubt',
						'rows' => 5,
						'value' => $data['note']]));

	$preview = Template::render('tour/notepreview', ['text' => Wiki::renderHTML($data['note'])]);
	$response->setData(Template::render('page/formpage', ['form' => $form->toString()
							      .$preview]));
    }

    public function ajaxNotenew($request, $response, $data)
    {
	if (empty($this->tour))
	    throw new Exception('Keine Tour ausgewählt');

	if (!strlen($data['note']))
	    throw new Exception('Kein Text angegeben');

	$data = ['tour_id' => $this->tour->id(),
		 'note' => trim($data['note'])];

	$this->db->insertInto('tour_note', $data);
    }

}