949 lines
44 KiB
PHP
949 lines
44 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
session_set_cookie_params([
|
||
'httponly' => true,
|
||
'samesite' => 'Strict',
|
||
'secure' => !empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off',
|
||
'use_strict_mode' => true,
|
||
]);
|
||
session_start();
|
||
header('Cache-Control: no-store, no-cache, must-revalidate');
|
||
header('Pragma: no-cache');
|
||
header('X-Content-Type-Options: nosniff');
|
||
header('X-Frame-Options: DENY');
|
||
header('Referrer-Policy: no-referrer');
|
||
header('Permissions-Policy: camera=(), microphone=(), geolocation=()');
|
||
header("Content-Security-Policy: default-src 'self'; script-src 'self'; style-src 'self' 'unsafe-inline'; img-src 'self' data:; base-uri 'self'; form-action 'self'; frame-ancestors 'none'");
|
||
|
||
require_once __DIR__ . '/inc/db.inc.php';
|
||
require_once __DIR__ . '/inc/mail.inc.php';
|
||
require_once __DIR__ . '/inc/room_planner.php';
|
||
|
||
function postString(string $key): string
|
||
{
|
||
$value = $_POST[$key] ?? '';
|
||
|
||
return is_string($value) ? trim($value) : '';
|
||
}
|
||
|
||
function escape(string $value): string
|
||
{
|
||
return htmlspecialchars($value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
|
||
}
|
||
|
||
function accommodationLabel(?string $value): string
|
||
{
|
||
return match ($value) {
|
||
'single' => 'Einzelzimmer',
|
||
'double' => 'Doppelzimmer',
|
||
'multi' => 'Mehrbettzimmer',
|
||
null, '' => '–',
|
||
default => $value,
|
||
};
|
||
}
|
||
|
||
function dayLabel(mixed $value): string
|
||
{
|
||
return match ((string) $value) {
|
||
'1' => 'Fr.',
|
||
'2' => 'Sa.',
|
||
'3' => 'So.',
|
||
default => (string) $value,
|
||
};
|
||
}
|
||
|
||
function mealLabel(mixed $value): string
|
||
{
|
||
return match ((string) $value) {
|
||
'breakfast' => 'Frühstück',
|
||
'lunch' => 'Mittagessen',
|
||
'coffee' => 'Kaffee',
|
||
'drinks' => 'Getränkepauschale',
|
||
'dinner' => 'Abendessen',
|
||
default => (string) $value,
|
||
};
|
||
}
|
||
|
||
function earlyDepartureLabel(mixed $value): string
|
||
{
|
||
return match ((string) $value) {
|
||
'sat_breakfast' => 'Samstag nach dem Frühstück',
|
||
'sat_lunch' => 'Samstag nach dem Mittagessen',
|
||
'sat_coffee' => 'Samstag nach dem Kaffee',
|
||
'sat_dinner' => 'Samstag nach dem Abendessen',
|
||
'sun_breakfast' => 'Sonntag nach dem Frühstück',
|
||
default => (string) $value,
|
||
};
|
||
}
|
||
|
||
function mealSummary(array $days, array $mealsByDay): string
|
||
{
|
||
$parts = [];
|
||
foreach ($days as $day) {
|
||
$meals = array_map('mealLabel', $mealsByDay[(string) $day] ?? []);
|
||
$meals[] = 'Getränkepauschale';
|
||
$meals[] = 'Proberaumpauschale';
|
||
$parts[] = dayLabel($day) . ': ' . implode(', ', $meals);
|
||
}
|
||
|
||
return implode('; ', $parts);
|
||
}
|
||
|
||
function calculateBookingTotal(PDO $pdo, bool $under27, string $guestType, ?string $roomType, array $mealsByDay): float
|
||
{
|
||
$ageSuffix = $under27 ? 'under_27' : 'over_27';
|
||
$keys = [];
|
||
|
||
if ($guestType === 'regular_guest' && $roomType !== null) {
|
||
$keys[] = $roomType . '_' . $ageSuffix;
|
||
}
|
||
if ($guestType === 'day_guest') {
|
||
foreach ($mealsByDay as $meals) {
|
||
foreach (array_unique(array_merge($meals, ['drinks'])) as $meal) {
|
||
if (in_array($meal, ['breakfast', 'lunch', 'coffee', 'dinner'], true)) {
|
||
$keys[] = $meal . '_' . $ageSuffix;
|
||
} elseif ($meal === 'drinks') {
|
||
$keys[] = 'drinks';
|
||
}
|
||
}
|
||
$keys[] = 'rehearsal_room';
|
||
}
|
||
}
|
||
if ($keys === []) {
|
||
return 0.0;
|
||
}
|
||
|
||
$uniqueKeys = array_values(array_unique($keys));
|
||
$placeholders = implode(',', array_fill(0, count($uniqueKeys), '?'));
|
||
$statement = $pdo->prepare("SELECT setting_key, price FROM pricing_settings WHERE setting_key IN ($placeholders)");
|
||
$statement->execute($uniqueKeys);
|
||
$prices = [];
|
||
foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$prices[(string) $row['setting_key']] = (float) $row['price'];
|
||
}
|
||
|
||
return round(array_sum(array_map(
|
||
static fn (string $key): float => $prices[$key] ?? 0.0,
|
||
$keys
|
||
)), 2);
|
||
}
|
||
|
||
function fetchUnpaidRecipients(PDO $pdo): array
|
||
{
|
||
return $pdo->query(
|
||
"SELECT p.name, p.email, COALESCE(b.manual_total_price, b.total_price) AS effective_price
|
||
FROM bookings b
|
||
INNER JOIN participants p ON p.id = b.participant_id
|
||
WHERE b.is_paid = 0 AND p.email IS NOT NULL AND p.email <> ''
|
||
ORDER BY p.name"
|
||
)->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
function personalizeBulkEmail(string $template, array $recipient): string
|
||
{
|
||
return str_replace(
|
||
['{{preis}}', '{{name}}'],
|
||
[
|
||
number_format((float) $recipient['effective_price'], 2, ',', '.') . ' €',
|
||
(string) $recipient['name'],
|
||
],
|
||
$template
|
||
);
|
||
}
|
||
|
||
function redirectToAdmin(): never
|
||
{
|
||
header('Location: admin.php');
|
||
exit;
|
||
}
|
||
|
||
function csrfToken(): string
|
||
{
|
||
if (!isset($_SESSION['admin_csrf'])) {
|
||
$_SESSION['admin_csrf'] = bin2hex(random_bytes(32));
|
||
}
|
||
|
||
return $_SESSION['admin_csrf'];
|
||
}
|
||
|
||
function requireCsrf(): void
|
||
{
|
||
$submitted = postString('csrf_token');
|
||
$stored = $_SESSION['admin_csrf'] ?? '';
|
||
|
||
if ($stored === '' || !hash_equals($stored, $submitted)) {
|
||
http_response_code(403);
|
||
exit('Ungültige Anfrage.');
|
||
}
|
||
}
|
||
|
||
function createPdo(array $dbConfig): PDO
|
||
{
|
||
$dsn = sprintf(
|
||
'mysql:host=%s;port=%d;dbname=%s;charset=%s',
|
||
$dbConfig['host'],
|
||
$dbConfig['port'],
|
||
$dbConfig['database'],
|
||
$dbConfig['charset']
|
||
);
|
||
|
||
return new PDO($dsn, $dbConfig['username'], $dbConfig['password'], [
|
||
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
||
PDO::ATTR_EMULATE_PREPARES => false,
|
||
]);
|
||
}
|
||
|
||
$pdo = createPdo($dbConfig);
|
||
|
||
if (isset($_SESSION['admin_id'])) {
|
||
$lastActivity = (int) ($_SESSION['admin_last_activity'] ?? 0);
|
||
if ($lastActivity > 0 && $lastActivity < time() - 1800) {
|
||
$_SESSION = [];
|
||
session_regenerate_id(true);
|
||
} else {
|
||
$_SESSION['admin_last_activity'] = time();
|
||
}
|
||
}
|
||
|
||
if (isset($_SESSION['admin_id'])) {
|
||
$activeStatement = $pdo->prepare('SELECT id FROM admin_users WHERE id = :id AND is_active = 1 LIMIT 1');
|
||
$activeStatement->execute([':id' => (int) $_SESSION['admin_id']]);
|
||
if ($activeStatement->fetchColumn() === false) {
|
||
$_SESSION = [];
|
||
session_regenerate_id(true);
|
||
}
|
||
}
|
||
|
||
$message = $_SESSION['admin_message'] ?? '';
|
||
unset($_SESSION['admin_message']);
|
||
|
||
if (postString('action') === 'login') {
|
||
$username = postString('username');
|
||
$password = postString('password');
|
||
$lockedUntil = (int) ($_SESSION['admin_locked_until'] ?? 0);
|
||
|
||
if ($lockedUntil > time()) {
|
||
$message = 'Zu viele Fehlversuche. Bitte später erneut versuchen.';
|
||
} else {
|
||
$statement = $pdo->prepare(
|
||
'SELECT id, username, password_hash
|
||
FROM admin_users
|
||
WHERE username = :username AND is_active = 1
|
||
LIMIT 1'
|
||
);
|
||
$statement->execute([':username' => $username]);
|
||
$admin = $statement->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if ($admin !== false && password_verify($password, $admin['password_hash'])) {
|
||
if (password_needs_rehash($admin['password_hash'], PASSWORD_DEFAULT)) {
|
||
$rehash = $pdo->prepare('UPDATE admin_users SET password_hash = :password_hash WHERE id = :id');
|
||
$rehash->execute([
|
||
':password_hash' => password_hash($password, PASSWORD_DEFAULT),
|
||
':id' => (int) $admin['id'],
|
||
]);
|
||
}
|
||
unset($_SESSION['admin_login_failures'], $_SESSION['admin_locked_until']);
|
||
session_regenerate_id(true);
|
||
$_SESSION['admin_id'] = (int) $admin['id'];
|
||
$_SESSION['admin_username'] = $admin['username'];
|
||
$_SESSION['admin_last_activity'] = time();
|
||
$_SESSION['admin_csrf'] = bin2hex(random_bytes(32));
|
||
redirectToAdmin();
|
||
}
|
||
|
||
$failures = (int) ($_SESSION['admin_login_failures'] ?? 0) + 1;
|
||
$_SESSION['admin_login_failures'] = $failures;
|
||
if ($failures >= 5) {
|
||
$_SESSION['admin_locked_until'] = time() + 300;
|
||
}
|
||
usleep(500000);
|
||
$message = 'Benutzername oder Passwort ist ungültig.';
|
||
}
|
||
}
|
||
|
||
if (!isset($_SESSION['admin_id'])) {
|
||
?><!DOCTYPE html>
|
||
<html lang="de">
|
||
<head>
|
||
<meta charset="UTF-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
<title>Admin-Login</title>
|
||
<link rel="stylesheet" href="./css/admin.css?v=20260918-3">
|
||
</head>
|
||
<body class="admin-page">
|
||
<main class="login-card">
|
||
<p class="eyebrow">Chorwochenende</p>
|
||
<h1>Admin-Login</h1>
|
||
<?php if ($message !== ''): ?>
|
||
<p class="message error"><?= escape($message) ?></p>
|
||
<?php endif; ?>
|
||
<form method="post" autocomplete="off">
|
||
<input type="hidden" name="action" value="login">
|
||
<label for="username">Benutzername</label>
|
||
<input type="text" id="username" name="username" required autocomplete="username">
|
||
<label for="password">Passwort</label>
|
||
<input type="password" id="password" name="password" required autocomplete="current-password">
|
||
<button type="submit">Anmelden</button>
|
||
</form>
|
||
</main>
|
||
<script src="./scripts/security.js?v=20260918-3"></script>
|
||
</body>
|
||
</html><?php
|
||
exit;
|
||
}
|
||
|
||
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
|
||
requireCsrf();
|
||
$action = postString('action');
|
||
|
||
try {
|
||
if ($action === 'logout') {
|
||
$_SESSION = [];
|
||
setcookie(session_name(), '', [
|
||
'expires' => time() - 42000,
|
||
'path' => '/',
|
||
'secure' => !empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off',
|
||
'httponly' => true,
|
||
'samesite' => 'Strict',
|
||
]);
|
||
session_destroy();
|
||
redirectToAdmin();
|
||
}
|
||
|
||
if ($action === 'delete_booking') {
|
||
$bookingId = filter_var($_POST['booking_id'] ?? null, FILTER_VALIDATE_INT);
|
||
if ($bookingId === false || $bookingId === null) {
|
||
throw new InvalidArgumentException('Ungültige Registrierung.');
|
||
}
|
||
|
||
$pdo->beginTransaction();
|
||
$participantStatement = $pdo->prepare(
|
||
'SELECT participant_id FROM bookings WHERE id = :id FOR UPDATE'
|
||
);
|
||
$participantStatement->execute([':id' => $bookingId]);
|
||
$participantId = $participantStatement->fetchColumn();
|
||
|
||
if ($participantId === false) {
|
||
throw new InvalidArgumentException('Registrierung nicht gefunden.');
|
||
}
|
||
|
||
$deleteBookings = $pdo->prepare('DELETE FROM bookings WHERE participant_id = :participant_id');
|
||
$deleteBookings->execute([':participant_id' => $participantId]);
|
||
$deleteParticipant = $pdo->prepare('DELETE FROM participants WHERE id = :id');
|
||
$deleteParticipant->execute([':id' => $participantId]);
|
||
$pdo->commit();
|
||
$_SESSION['admin_message'] = 'Registrierung wurde gelöscht.';
|
||
}
|
||
|
||
if ($action === 'toggle_paid') {
|
||
$bookingId = filter_var($_POST['booking_id'] ?? null, FILTER_VALIDATE_INT);
|
||
$paid = filter_var($_POST['is_paid'] ?? null, FILTER_VALIDATE_INT);
|
||
if ($bookingId === false || $bookingId === null || !in_array($paid, [0, 1], true)) {
|
||
throw new InvalidArgumentException('Ungültiger Zahlungsstatus.');
|
||
}
|
||
|
||
$statement = $pdo->prepare('UPDATE bookings SET is_paid = :is_paid WHERE id = :id');
|
||
$statement->execute([':is_paid' => $paid, ':id' => $bookingId]);
|
||
$_SESSION['admin_message'] = $paid === 1
|
||
? 'Registrierung als bezahlt markiert.'
|
||
: 'Zahlungsmarkierung entfernt.';
|
||
}
|
||
|
||
if ($action === 'create_user') {
|
||
$username = postString('new_username');
|
||
$email = postString('new_email');
|
||
$password = postString('new_password');
|
||
|
||
if (!preg_match('/^[A-Za-z0-9_.-]{3,80}$/', $username)) {
|
||
throw new InvalidArgumentException('Der Benutzername enthält ungültige Zeichen.');
|
||
}
|
||
if (strlen($password) < 12) {
|
||
throw new InvalidArgumentException('Passwörter müssen mindestens 12 Zeichen lang sein.');
|
||
}
|
||
if (!filter_var($email, FILTER_VALIDATE_EMAIL) || mb_strlen($email) > 255) {
|
||
throw new InvalidArgumentException('Bitte eine gültige Admin-E-Mail-Adresse eingeben.');
|
||
}
|
||
|
||
$statement = $pdo->prepare(
|
||
'INSERT INTO admin_users (username, email, password_hash) VALUES (:username, :email, :password_hash)'
|
||
);
|
||
$statement->execute([
|
||
':username' => $username,
|
||
':email' => $email,
|
||
':password_hash' => password_hash($password, PASSWORD_DEFAULT),
|
||
]);
|
||
$_SESSION['admin_message'] = 'Admin-Benutzer wurde angelegt.';
|
||
}
|
||
|
||
if ($action === 'update_room_settings') {
|
||
$roomSettings = [
|
||
'single' => postString('room_count_single'),
|
||
'double' => postString('room_count_double'),
|
||
'multi' => postString('room_count_multi'),
|
||
];
|
||
$upsertRoomSetting = $pdo->prepare(
|
||
'INSERT INTO pricing_settings (setting_key, price) VALUES (:key, :price)
|
||
ON DUPLICATE KEY UPDATE price = VALUES(price)'
|
||
);
|
||
foreach ($roomSettings as $roomType => $rawCount) {
|
||
if ($rawCount === '' || !ctype_digit($rawCount) || (int) $rawCount > 9999) {
|
||
throw new InvalidArgumentException('Bitte gültige Zimmeranzahlen zwischen 0 und 9.999 eingeben.');
|
||
}
|
||
$upsertRoomSetting->execute([
|
||
':key' => 'room_count_' . $roomType,
|
||
':price' => $rawCount,
|
||
]);
|
||
}
|
||
$_SESSION['admin_message'] = 'Zimmeranzahlen wurden gespeichert.';
|
||
}
|
||
|
||
if ($action === 'prepare_unpaid_email') {
|
||
$template = postString('bulk_email_text');
|
||
if ($template === '' || mb_strlen($template) > 10000) {
|
||
throw new InvalidArgumentException('Bitte einen Nachrichtentext mit höchstens 10.000 Zeichen eingeben.');
|
||
}
|
||
$recipients = fetchUnpaidRecipients($pdo);
|
||
if ($recipients === []) {
|
||
throw new InvalidArgumentException('Es wurden keine unbezahlten Registrierungen mit E-Mail-Adresse gefunden.');
|
||
}
|
||
$_SESSION['bulk_email_draft'] = [
|
||
'text' => $template,
|
||
'created_at' => time(),
|
||
];
|
||
$_SESSION['admin_message'] = 'Vorschau vorbereitet. Bitte Empfängerzahl und Nachricht prüfen.';
|
||
}
|
||
|
||
if ($action === 'send_unpaid_email') {
|
||
$draft = $_SESSION['bulk_email_draft'] ?? null;
|
||
if (!is_array($draft) || !is_string($draft['text'] ?? null) || (int) ($draft['created_at'] ?? 0) < time() - 1800) {
|
||
unset($_SESSION['bulk_email_draft']);
|
||
throw new InvalidArgumentException('Die Vorschau ist abgelaufen. Bitte die Nachricht erneut vorbereiten.');
|
||
}
|
||
$recipients = fetchUnpaidRecipients($pdo);
|
||
$sent = 0;
|
||
$failed = 0;
|
||
foreach ($recipients as $recipient) {
|
||
try {
|
||
sendConfiguredTextEmail(
|
||
$mailConfig,
|
||
(string) $recipient['email'],
|
||
'One Voice - Chorwochenende - Deine Teilnahmegebühr',
|
||
personalizeBulkEmail($draft['text'], $recipient)
|
||
);
|
||
$sent++;
|
||
} catch (Throwable $exception) {
|
||
$failed++;
|
||
error_log('Massenmail konnte nicht an ' . (string) $recipient['email'] . ' versendet werden: ' . $exception->getMessage());
|
||
}
|
||
}
|
||
unset($_SESSION['bulk_email_draft']);
|
||
$_SESSION['admin_message'] = $failed === 0
|
||
? $sent . ' Zahlungserinnerung(en) wurden versendet.'
|
||
: $sent . ' Zahlungserinnerung(en) versendet, ' . $failed . ' Versand/Versände fehlgeschlagen.';
|
||
}
|
||
|
||
if ($action === 'update_booking_price') {
|
||
$bookingId = filter_var($_POST['booking_id'] ?? null, FILTER_VALIDATE_INT);
|
||
if ($bookingId === false || $bookingId === null) {
|
||
throw new InvalidArgumentException('Ungültige Registrierung.');
|
||
}
|
||
$correctPrice = isset($_POST['correct_price']) && $_POST['correct_price'] === '1';
|
||
$rawManualPrice = $correctPrice ? str_replace(',', '.', postString('manual_total_price')) : '';
|
||
$bookingStatement = $pdo->prepare(
|
||
'SELECT b.id, b.type, b.accommodation_type, b.meal_preferences, p.under_27
|
||
FROM bookings b
|
||
INNER JOIN participants p ON p.id = b.participant_id
|
||
WHERE b.id = :id'
|
||
);
|
||
$bookingStatement->execute([':id' => $bookingId]);
|
||
$bookingRow = $bookingStatement->fetch(PDO::FETCH_ASSOC);
|
||
if ($bookingRow === false) {
|
||
throw new InvalidArgumentException('Registrierung nicht gefunden.');
|
||
}
|
||
if ($rawManualPrice !== '' && (!is_numeric($rawManualPrice) || (float) $rawManualPrice < 0 || (float) $rawManualPrice > 999999.99)) {
|
||
throw new InvalidArgumentException('Bitte einen gültigen manuellen Gesamtpreis zwischen 0,00 und 999.999,99 eingeben.');
|
||
}
|
||
$manualPrice = $rawManualPrice === '' ? null : number_format((float) $rawManualPrice, 2, '.', '');
|
||
$mealData = $bookingRow['meal_preferences'] !== null
|
||
? json_decode($bookingRow['meal_preferences'], true)
|
||
: [];
|
||
$mealData = is_array($mealData) ? $mealData : [];
|
||
$mealsByDay = is_array($mealData['meals_by_day'] ?? null) ? $mealData['meals_by_day'] : [];
|
||
if ($mealsByDay === [] && is_array($mealData['meals'] ?? null)) {
|
||
foreach ($mealData['days'] ?? [] as $day) {
|
||
$mealsByDay[(string) $day] = $mealData['meals'];
|
||
}
|
||
}
|
||
foreach ($mealData['days'] ?? [] as $day) {
|
||
$mealsByDay[(string) $day] ??= [];
|
||
}
|
||
$automaticPrice = calculateBookingTotal(
|
||
$pdo,
|
||
(int) $bookingRow['under_27'] === 1,
|
||
$bookingRow['type'] === 'Day' ? 'day_guest' : 'regular_guest',
|
||
$bookingRow['accommodation_type'],
|
||
$mealsByDay
|
||
);
|
||
$statement = $pdo->prepare(
|
||
'UPDATE bookings SET manual_total_price = :manual_total_price, total_price = :total_price WHERE id = :id'
|
||
);
|
||
$statement->execute([
|
||
':manual_total_price' => $manualPrice,
|
||
':total_price' => $manualPrice ?? number_format($automaticPrice, 2, '.', ''),
|
||
':id' => $bookingId,
|
||
]);
|
||
$_SESSION['admin_message'] = $manualPrice === null
|
||
? 'Manueller Preis entfernt; automatische Berechnung wurde wiederhergestellt.'
|
||
: 'Preis wurde korrigiert.';
|
||
}
|
||
|
||
if ($action === 'update_prices') {
|
||
$priceKeys = [];
|
||
foreach (['single', 'double', 'multi', 'breakfast', 'lunch', 'coffee', 'dinner'] as $baseKey) {
|
||
$priceKeys[] = $baseKey . '_under_27';
|
||
$priceKeys[] = $baseKey . '_over_27';
|
||
}
|
||
$priceKeys[] = 'drinks';
|
||
$priceKeys[] = 'rehearsal_room';
|
||
$upsert = $pdo->prepare(
|
||
'INSERT INTO pricing_settings (setting_key, price) VALUES (:key, :price)
|
||
ON DUPLICATE KEY UPDATE price = VALUES(price)'
|
||
);
|
||
foreach ($priceKeys as $priceKey) {
|
||
$rawPrice = str_replace(',', '.', postString('price_' . $priceKey));
|
||
if ($rawPrice === '' || !is_numeric($rawPrice) || (float) $rawPrice < 0 || (float) $rawPrice > 999999.99) {
|
||
throw new InvalidArgumentException('Bitte gültige Preise zwischen 0,00 und 999.999,99 eingeben.');
|
||
}
|
||
$upsert->execute([':key' => $priceKey, ':price' => number_format((float) $rawPrice, 2, '.', '')]);
|
||
}
|
||
|
||
$bookingRows = $pdo->query(
|
||
'SELECT b.id, b.type, b.accommodation_type, b.meal_preferences, b.manual_total_price, p.under_27
|
||
FROM bookings b
|
||
INNER JOIN participants p ON p.id = b.participant_id'
|
||
)->fetchAll(PDO::FETCH_ASSOC);
|
||
$updateTotal = $pdo->prepare('UPDATE bookings SET total_price = :total_price WHERE id = :id');
|
||
foreach ($bookingRows as $bookingRow) {
|
||
if ($bookingRow['manual_total_price'] !== null) {
|
||
continue;
|
||
}
|
||
$mealData = $bookingRow['meal_preferences'] !== null
|
||
? json_decode($bookingRow['meal_preferences'], true)
|
||
: [];
|
||
$mealData = is_array($mealData) ? $mealData : [];
|
||
$guestType = $bookingRow['type'] === 'Day' ? 'day_guest' : 'regular_guest';
|
||
$mealsByDay = is_array($mealData['meals_by_day'] ?? null) ? $mealData['meals_by_day'] : [];
|
||
if ($mealsByDay === [] && is_array($mealData['meals'] ?? null)) {
|
||
foreach ($mealData['days'] ?? [] as $day) {
|
||
$mealsByDay[(string) $day] = $mealData['meals'];
|
||
}
|
||
}
|
||
foreach ($mealData['days'] ?? [] as $day) {
|
||
$mealsByDay[(string) $day] ??= [];
|
||
}
|
||
$totalPrice = calculateBookingTotal(
|
||
$pdo,
|
||
(int) $bookingRow['under_27'] === 1,
|
||
$guestType,
|
||
$bookingRow['accommodation_type'],
|
||
$mealsByDay
|
||
);
|
||
$updateTotal->execute([
|
||
':total_price' => number_format($totalPrice, 2, '.', ''),
|
||
':id' => (int) $bookingRow['id'],
|
||
]);
|
||
}
|
||
$_SESSION['admin_message'] = 'Preise wurden gespeichert.';
|
||
}
|
||
|
||
if ($action === 'change_password') {
|
||
$adminId = filter_var($_POST['admin_id'] ?? null, FILTER_VALIDATE_INT);
|
||
$password = postString('changed_password');
|
||
|
||
if ($adminId === false || $adminId === null || strlen($password) < 12) {
|
||
throw new InvalidArgumentException('Passwort muss mindestens 12 Zeichen lang sein.');
|
||
}
|
||
|
||
$statement = $pdo->prepare(
|
||
'UPDATE admin_users SET password_hash = :password_hash, updated_at = CURRENT_TIMESTAMP
|
||
WHERE id = :id AND is_active = 1'
|
||
);
|
||
$statement->execute([
|
||
':password_hash' => password_hash($password, PASSWORD_DEFAULT),
|
||
':id' => $adminId,
|
||
]);
|
||
$_SESSION['admin_message'] = 'Passwort wurde geändert.';
|
||
}
|
||
|
||
if ($action === 'delete_user') {
|
||
$adminId = filter_var($_POST['admin_id'] ?? null, FILTER_VALIDATE_INT);
|
||
if ($adminId === false || $adminId === null || $adminId === (int) $_SESSION['admin_id']) {
|
||
throw new InvalidArgumentException('Der eigene Benutzer kann nicht gelöscht werden.');
|
||
}
|
||
|
||
$activeCount = (int) $pdo->query('SELECT COUNT(*) FROM admin_users WHERE is_active = 1')->fetchColumn();
|
||
if ($activeCount <= 1) {
|
||
throw new InvalidArgumentException('Der letzte Admin-Benutzer kann nicht gelöscht werden.');
|
||
}
|
||
|
||
$statement = $pdo->prepare('DELETE FROM admin_users WHERE id = :id AND is_active = 1');
|
||
$statement->execute([':id' => $adminId]);
|
||
$_SESSION['admin_message'] = 'Admin-Benutzer wurde gelöscht.';
|
||
}
|
||
} catch (InvalidArgumentException $exception) {
|
||
if ($pdo->inTransaction()) {
|
||
$pdo->rollBack();
|
||
}
|
||
$_SESSION['admin_message'] = $exception->getMessage();
|
||
} catch (PDOException $exception) {
|
||
if ($pdo->inTransaction()) {
|
||
$pdo->rollBack();
|
||
}
|
||
error_log($exception->getMessage());
|
||
$_SESSION['admin_message'] = 'Die Änderung konnte nicht gespeichert werden.';
|
||
}
|
||
|
||
redirectToAdmin();
|
||
}
|
||
|
||
$bookings = $pdo->query(
|
||
'SELECT b.id AS booking_id, p.name, p.email, p.role, p.under_27, p.allergies,
|
||
b.type, b.accommodation_type, b.meal_preferences, b.is_paid, b.total_price,
|
||
b.manual_total_price, b.roommate_requests, b.early_departure, b.other_notes
|
||
FROM bookings b
|
||
INNER JOIN participants p ON p.id = b.participant_id
|
||
ORDER BY b.id DESC'
|
||
)->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$roomPlanningSettings = loadRoomPlanningSettings($pdo);
|
||
$roomPlanningParticipants = $pdo->query(
|
||
"SELECT p.id, p.name, p.email, b.accommodation_type AS room_type,
|
||
b.roommate_requests
|
||
FROM bookings b
|
||
INNER JOIN participants p ON p.id = b.participant_id
|
||
WHERE b.type = 'Stay'
|
||
AND b.accommodation_type IN ('single', 'double', 'multi')
|
||
ORDER BY p.name"
|
||
)->fetchAll(PDO::FETCH_ASSOC);
|
||
$roomPlan = buildRoomPlan($roomPlanningParticipants, $roomPlanningSettings);
|
||
|
||
$admins = $pdo->query(
|
||
'SELECT id, username, email, created_at, updated_at
|
||
FROM admin_users
|
||
WHERE is_active = 1
|
||
ORDER BY username'
|
||
)->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$pricingRows = $pdo->query('SELECT setting_key, price FROM pricing_settings')->fetchAll(PDO::FETCH_KEY_PAIR);
|
||
$priceGroups = [
|
||
'Zimmer' => ['single' => 'Einzelzimmer', 'double' => 'Doppelzimmer', 'multi' => 'Mehrbettzimmer'],
|
||
'Mahlzeiten' => ['breakfast' => 'Frühstück', 'lunch' => 'Mittagessen', 'coffee' => 'Kaffee', 'dinner' => 'Abendessen'],
|
||
];
|
||
$flatPriceItems = [
|
||
'drinks' => 'Getränkepauschale',
|
||
'rehearsal_room' => 'Proberaumpauschale',
|
||
];
|
||
|
||
$csrf = csrfToken();
|
||
$bulkEmailDraft = $_SESSION['bulk_email_draft'] ?? null;
|
||
$bulkEmailRecipients = is_array($bulkEmailDraft) && (int) ($bulkEmailDraft['created_at'] ?? 0) >= time() - 1800
|
||
? fetchUnpaidRecipients($pdo)
|
||
: [];
|
||
$bulkEmailPreview = $bulkEmailRecipients !== [] && is_array($bulkEmailDraft)
|
||
? personalizeBulkEmail((string) $bulkEmailDraft['text'], $bulkEmailRecipients[0])
|
||
: '';
|
||
?><!DOCTYPE html>
|
||
<html lang="de">
|
||
<head>
|
||
<meta charset="UTF-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
<title>Administration</title>
|
||
<link rel="stylesheet" href="./css/admin.css?v=20260918-3">
|
||
</head>
|
||
<body class="admin-page">
|
||
<header class="admin-header">
|
||
<div>
|
||
<p class="eyebrow">Chorwochenende</p>
|
||
<h1>Administration</h1>
|
||
</div>
|
||
<form method="post">
|
||
<input type="hidden" name="action" value="logout">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<button class="button-secondary" type="submit">Abmelden</button>
|
||
</form>
|
||
</header>
|
||
|
||
<main class="admin-content">
|
||
<?php if ($message !== ''): ?>
|
||
<p class="message"><?= escape($message) ?></p>
|
||
<?php endif; ?>
|
||
|
||
<section class="panel">
|
||
<div class="section-heading">
|
||
<div>
|
||
<p class="eyebrow">Anmeldungen</p>
|
||
<h2>Registrierungen</h2>
|
||
</div>
|
||
<div>
|
||
<span class="count"><?= count($bookings) ?></span>
|
||
<a class="button-secondary export-button" href="export.php">Excel-Export</a>
|
||
</div>
|
||
</div>
|
||
<div class="table-wrapper">
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th>Name</th>
|
||
<th>E-Mail</th>
|
||
<th>Altersgruppe</th>
|
||
<th>Bezahlt</th>
|
||
<th>Preis</th>
|
||
<th>Typ</th>
|
||
<th>Zimmer</th>
|
||
<th>Zimmerpartner-Wunsch</th>
|
||
<th>Frühzeitige Abreise</th>
|
||
<th>Mahlzeiten / Tage</th>
|
||
<th>Allergien</th>
|
||
<th>Sonstiges</th>
|
||
<th>Aktion</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<?php foreach ($bookings as $booking):
|
||
$mealData = $booking['meal_preferences'] !== null
|
||
? json_decode($booking['meal_preferences'], true)
|
||
: null;
|
||
$mealData = is_array($mealData) ? $mealData : [];
|
||
$days = is_array($mealData['days'] ?? null) ? $mealData['days'] : [];
|
||
$mealsByDay = is_array($mealData['meals_by_day'] ?? null) ? $mealData['meals_by_day'] : [];
|
||
if ($mealsByDay === [] && is_array($mealData['meals'] ?? null)) {
|
||
foreach ($days as $day) {
|
||
$mealsByDay[(string) $day] = $mealData['meals'];
|
||
}
|
||
}
|
||
$mealText = mealSummary($days, $mealsByDay);
|
||
?>
|
||
<tr>
|
||
<td><?= escape($booking['name']) ?></td>
|
||
<td><a href="mailto:<?= escape($booking['email']) ?>"><?= escape($booking['email']) ?></a></td>
|
||
<td><?= (int) $booking['under_27'] === 1 ? 'Unter 27' : 'Ab 27' ?></td>
|
||
<td>
|
||
<form method="post" class="paid-form">
|
||
<input type="hidden" name="action" value="toggle_paid">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<input type="hidden" name="booking_id" value="<?= (int) $booking['booking_id'] ?>">
|
||
<input type="hidden" name="is_paid" value="0">
|
||
<input type="checkbox" name="is_paid" value="1" data-submit-on-change="true" <?= (int) $booking['is_paid'] === 1 ? 'checked' : '' ?> aria-label="Als bezahlt markieren">
|
||
</form>
|
||
</td>
|
||
<td>
|
||
<div><?= number_format((float) $booking['total_price'], 2, ',', '.') ?> €</div>
|
||
<small><?= $booking['manual_total_price'] !== null ? 'korrigiert' : 'automatisch' ?></small>
|
||
<form method="post" class="manual-price-form">
|
||
<input type="hidden" name="action" value="update_booking_price">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<input type="hidden" name="booking_id" value="<?= (int) $booking['booking_id'] ?>">
|
||
<label class="checkbox-label" for="correct_price_<?= (int) $booking['booking_id'] ?>">
|
||
<input type="checkbox" id="correct_price_<?= (int) $booking['booking_id'] ?>" name="correct_price" value="1" data-toggle-target="manual_total_price_<?= (int) $booking['booking_id'] ?>" <?= $booking['manual_total_price'] !== null ? 'checked' : '' ?>>
|
||
Preis korrigieren
|
||
</label>
|
||
<div class="manual-price-input" data-toggle-content="manual_total_price_<?= (int) $booking['booking_id'] ?>" <?= $booking['manual_total_price'] === null ? 'hidden' : '' ?>>
|
||
<label class="sr-only" for="manual_total_price_<?= (int) $booking['booking_id'] ?>">Preis korrigieren</label>
|
||
<input type="number" id="manual_total_price_<?= (int) $booking['booking_id'] ?>" name="manual_total_price" min="0" max="999999.99" step="0.01" placeholder="Neuer Gesamtpreis" value="<?= $booking['manual_total_price'] !== null ? escape((string) $booking['manual_total_price']) : '' ?>" <?= $booking['manual_total_price'] === null ? 'disabled' : '' ?>>
|
||
<button class="button-secondary" type="submit">Speichern</button>
|
||
</div>
|
||
</form>
|
||
</td>
|
||
<td><?= escape($booking['role']) ?></td>
|
||
<td><?= escape(accommodationLabel($booking['accommodation_type'])) ?></td>
|
||
<td><?= escape((string) ($booking['roommate_requests'] ?? '–')) ?></td>
|
||
<td><?= escape($booking['early_departure'] !== null && $booking['early_departure'] !== '' ? earlyDepartureLabel($booking['early_departure']) : '–') ?></td>
|
||
<td><?= escape($mealText !== '' ? $mealText : '–') ?></td>
|
||
<td><?= escape((string) ($booking['allergies'] ?? '–')) ?></td>
|
||
<td><?= escape((string) ($booking['other_notes'] ?? '–')) ?></td>
|
||
<td>
|
||
<form method="post" data-confirm="Diese Registrierung wirklich löschen?">
|
||
<input type="hidden" name="action" value="delete_booking">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<input type="hidden" name="booking_id" value="<?= (int) $booking['booking_id'] ?>">
|
||
<button class="button-danger" type="submit">Löschen</button>
|
||
</form>
|
||
</td>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
<?php if ($bookings === []): ?>
|
||
<tr><td colspan="13">Noch keine Registrierungen vorhanden.</td></tr>
|
||
<?php endif; ?>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
</section>
|
||
|
||
<section class="panel">
|
||
<p class="eyebrow">Auswertung</p>
|
||
<h2>Zahlungserinnerung an Nichtzahler</h2>
|
||
<p>Angeschrieben werden alle angemeldeten Teilnehmer mit hinterlegter E-Mail-Adresse, deren Registrierung noch nicht als bezahlt markiert ist.</p>
|
||
<form method="post" class="stacked-form">
|
||
<input type="hidden" name="action" value="prepare_unpaid_email">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<label for="bulk_email_text">Nachrichtentext</label>
|
||
<textarea id="bulk_email_text" name="bulk_email_text" rows="8" maxlength="10000" required><?= is_array($bulkEmailDraft) ? escape((string) ($bulkEmailDraft['text'] ?? '')) : '' ?></textarea>
|
||
<small>Platzhalter: <code>{{name}}</code> für den Namen und <code>{{preis}}</code> für den jeweils offenen Preis.</small>
|
||
<button type="submit">Vorschau vorbereiten</button>
|
||
</form>
|
||
|
||
<?php if ($bulkEmailRecipients !== []): ?>
|
||
<div class="bulk-email-preview">
|
||
<h3>Versand bestätigen</h3>
|
||
<p><strong><?= count($bulkEmailRecipients) ?></strong> Empfänger gefunden. Beispiel für <?= escape((string) $bulkEmailRecipients[0]['name']) ?>:</p>
|
||
<pre><?= escape($bulkEmailPreview) ?></pre>
|
||
<form method="post" data-confirm="Die Zahlungserinnerung jetzt an alle <?= count($bulkEmailRecipients) ?> Empfänger senden?">
|
||
<input type="hidden" name="action" value="send_unpaid_email">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<button type="submit">Jetzt an alle Nichtzahler senden</button>
|
||
</form>
|
||
</div>
|
||
<?php endif; ?>
|
||
</section>
|
||
|
||
<section class="panel">
|
||
<p class="eyebrow">Auswertung</p>
|
||
<h2>Zimmerbelegung planen</h2>
|
||
<p>Berücksichtigt werden nur Übernachtungsgäste. Mehrbettzimmer werden mit maximal vier Schlafplätzen geplant.</p>
|
||
<form method="post" class="price-grid">
|
||
<input type="hidden" name="action" value="update_room_settings">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<div class="price-group">
|
||
<h3>Verfügbare Zimmer</h3>
|
||
<div class="price-item">
|
||
<label for="room_count_single">Einzelzimmer</label>
|
||
<input type="number" id="room_count_single" name="room_count_single" min="0" max="9999" step="1" value="<?= (int) $roomPlanningSettings['single'] ?>" required>
|
||
</div>
|
||
<div class="price-item">
|
||
<label for="room_count_double">Doppelzimmer</label>
|
||
<input type="number" id="room_count_double" name="room_count_double" min="0" max="9999" step="1" value="<?= (int) $roomPlanningSettings['double'] ?>" required>
|
||
</div>
|
||
<div class="price-item">
|
||
<label for="room_count_multi">Mehrbettzimmer (max. 4 Plätze)</label>
|
||
<input type="number" id="room_count_multi" name="room_count_multi" min="0" max="9999" step="1" value="<?= (int) $roomPlanningSettings['multi'] ?>" required>
|
||
</div>
|
||
</div>
|
||
<button type="submit">Zimmeranzahlen speichern</button>
|
||
</form>
|
||
|
||
<?php if ($roomPlan['warnings'] !== []): ?>
|
||
<div class="message error">
|
||
<strong>Planungshinweise</strong>
|
||
<ul>
|
||
<?php foreach ($roomPlan['warnings'] as $warning): ?>
|
||
<li><?= escape($warning) ?></li>
|
||
<?php endforeach; ?>
|
||
</ul>
|
||
</div>
|
||
<?php endif; ?>
|
||
|
||
<div class="table-wrapper">
|
||
<table>
|
||
<thead><tr><th>Zimmer</th><th>Belegung</th><th>Kapazität</th></tr></thead>
|
||
<tbody>
|
||
<?php foreach ($roomPlan['rooms'] as $room): ?>
|
||
<tr>
|
||
<td><?= escape(accommodationLabel($room['type'])) ?> <?= (int) $room['number'] ?></td>
|
||
<td><?= escape(implode(', ', array_column($room['members'], 'name'))) ?></td>
|
||
<td><?= count($room['members']) ?> / <?= (int) $room['capacity'] ?></td>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
<?php if ($roomPlan['rooms'] === []): ?>
|
||
<tr><td colspan="3">Noch keine Zimmerplanung möglich. Bitte Zimmeranzahlen und Übernachtungsgäste prüfen.</td></tr>
|
||
<?php endif; ?>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
</section>
|
||
|
||
<section class="panel">
|
||
<h2>Preise</h2>
|
||
<form method="post" class="price-grid">
|
||
<input type="hidden" name="action" value="update_prices">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<?php foreach ($priceGroups as $groupLabel => $priceItems): ?>
|
||
<div class="price-group">
|
||
<h3><?= escape($groupLabel) ?></h3>
|
||
<?php foreach ($priceItems as $priceKey => $priceLabel): ?>
|
||
<div class="price-item">
|
||
<strong><?= escape($priceLabel) ?></strong>
|
||
<label for="price_<?= $priceKey ?>_under_27">Unter 27</label>
|
||
<input type="number" id="price_<?= $priceKey ?>_under_27" name="price_<?= $priceKey ?>_under_27" min="0" max="999999.99" step="0.01" value="<?= escape((string) ($pricingRows[$priceKey . '_under_27'] ?? '0.00')) ?>" required>
|
||
<label for="price_<?= $priceKey ?>_over_27">Ab 27</label>
|
||
<input type="number" id="price_<?= $priceKey ?>_over_27" name="price_<?= $priceKey ?>_over_27" min="0" max="999999.99" step="0.01" value="<?= escape((string) ($pricingRows[$priceKey . '_over_27'] ?? '0.00')) ?>" required>
|
||
</div>
|
||
<?php endforeach; ?>
|
||
</div>
|
||
<?php endforeach; ?>
|
||
<div class="price-group">
|
||
<h3>Pauschalen</h3>
|
||
<?php foreach ($flatPriceItems as $priceKey => $priceLabel): ?>
|
||
<div class="price-item">
|
||
<strong><?= escape($priceLabel) ?></strong>
|
||
<label for="price_<?= $priceKey ?>">Preis</label>
|
||
<input type="number" id="price_<?= $priceKey ?>" name="price_<?= $priceKey ?>" min="0" max="999999.99" step="0.01" value="<?= escape((string) ($pricingRows[$priceKey] ?? '0.00')) ?>" required>
|
||
</div>
|
||
<?php endforeach; ?>
|
||
</div>
|
||
<button type="submit">Preise speichern</button>
|
||
</form>
|
||
</section>
|
||
|
||
<div class="admin-grid">
|
||
<section class="panel">
|
||
<p class="eyebrow">Zugänge</p>
|
||
<h2>Admin-Benutzer</h2>
|
||
<div class="user-list">
|
||
<?php foreach ($admins as $admin): ?>
|
||
<div class="user-row">
|
||
<div><strong><?= escape($admin['username']) ?></strong><br><small><?= escape($admin['email']) ?></small></div>
|
||
<div class="user-actions">
|
||
<form method="post">
|
||
<input type="hidden" name="action" value="change_password">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<input type="hidden" name="admin_id" value="<?= (int) $admin['id'] ?>">
|
||
<input type="password" name="changed_password" minlength="12" placeholder="Neues Passwort" required>
|
||
<button class="button-secondary" type="submit">Ändern</button>
|
||
</form>
|
||
<?php if ((int) $admin['id'] !== (int) $_SESSION['admin_id']): ?>
|
||
<form method="post">
|
||
<input type="hidden" name="action" value="delete_user">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<input type="hidden" name="admin_id" value="<?= (int) $admin['id'] ?>">
|
||
<button class="button-danger" type="submit">Löschen</button>
|
||
</form>
|
||
<?php endif; ?>
|
||
</div>
|
||
</div>
|
||
<?php endforeach; ?>
|
||
</div>
|
||
</section>
|
||
|
||
<section class="panel">
|
||
<p class="eyebrow">Zugänge</p>
|
||
<h2>Benutzer anlegen</h2>
|
||
<form method="post" class="stacked-form">
|
||
<input type="hidden" name="action" value="create_user">
|
||
<input type="hidden" name="csrf_token" value="<?= escape($csrf) ?>">
|
||
<label for="new_username">Benutzername</label>
|
||
<input type="text" id="new_username" name="new_username" pattern="[A-Za-z0-9_.-]{3,80}" required>
|
||
<label for="new_email">E-Mail-Adresse</label>
|
||
<input type="email" id="new_email" name="new_email" maxlength="255" required>
|
||
<label for="new_password">Passwort</label>
|
||
<input type="password" id="new_password" name="new_password" minlength="12" required>
|
||
<button type="submit">Benutzer anlegen</button>
|
||
</form>
|
||
</section>
|
||
</div>
|
||
</main>
|
||
<script src="./scripts/security.js?v=20260918-3"></script>
|
||
</body>
|
||
</html>
|