BROOKO UK NETWORK
Where code meets creativity & adventure
File viewer
rota.php
Type
php
Size
20.78 KB
Modified
15 May
rota.php
20.78 KB
<?php
require_once __DIR__ . '/../bootstrap.php';
require_once __DIR__ . '/../lib/feature_modules.php';
requireAuth();
if (!(isAdminRole() || hasPermission('rota.view') || hasPermission('rota.edit'))) {
http_response_code(403);
die('Access denied.');
}
$pageTitle = 'Rota';
$users = wp_feature_users();
$vans = [];
try { $vans = $pdo->query("SELECT id, plate_full, plate_short, make, model FROM vans WHERE is_active = 1 ORDER BY plate_full ASC")->fetchAll(PDO::FETCH_ASSOC) ?: []; } catch (Throwable $e) {}
$rotaSchemaOk = wp_feature_ensure_rota_schema();
$canManageAll = isAdminRole() || hasPermission('management.access') || hasPermission('rota.edit');
function wp_rota_import_date(mixed $value): string {
$value = trim((string)$value);
if ($value === '') return '';
if (is_numeric($value)) {
$days = (float)$value;
if ($days > 20000 && $days < 80000) {
return gmdate('Y-m-d', (int)(($days - 25569) * 86400));
}
}
$formats = ['Y-m-d','d/m/Y','d-m-Y','m/d/Y','d.m.Y'];
foreach ($formats as $fmt) {
$dt = DateTime::createFromFormat($fmt, $value);
if ($dt instanceof DateTime) return $dt->format('Y-m-d');
}
$ts = strtotime($value);
return $ts ? date('Y-m-d', $ts) : '';
}
function wp_rota_import_header_key(string $name): string {
$key = strtolower(trim($name));
$key = preg_replace('/[^a-z0-9]+/', '_', $key) ?: '';
return trim($key, '_');
}
function wp_rota_import_rows_from_csv(string $path): array {
$fh = fopen($path, 'r');
if (!$fh) return [];
$rows = [];
while (($row = fgetcsv($fh)) !== false) $rows[] = $row;
fclose($fh);
return $rows;
}
function wp_rota_import_rows_from_xlsx(string $path): array {
if (!class_exists('ZipArchive')) return [];
$zip = new ZipArchive();
if ($zip->open($path) !== true) return [];
$shared = [];
$stringsXml = $zip->getFromName('xl/sharedStrings.xml');
if ($stringsXml !== false) {
$xml = @simplexml_load_string($stringsXml);
if ($xml) {
foreach ($xml->si as $si) {
$text = '';
if (isset($si->t)) $text = (string)$si->t;
elseif (isset($si->r)) foreach ($si->r as $run) $text .= (string)$run->t;
$shared[] = $text;
}
}
}
$sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
$zip->close();
if ($sheetXml === false) return [];
$xml = @simplexml_load_string($sheetXml);
if (!$xml) return [];
$rows = [];
foreach ($xml->sheetData->row as $rowNode) {
$row = [];
foreach ($rowNode->c as $cell) {
$ref = (string)$cell['r'];
$letters = preg_replace('/[^A-Z]/', '', strtoupper($ref));
$col = 0;
for ($i=0; $i<strlen($letters); $i++) $col = $col * 26 + (ord($letters[$i]) - 64);
$idx = max(0, $col - 1);
$type = (string)$cell['t'];
$value = isset($cell->v) ? (string)$cell->v : '';
if ($type === 's') $value = $shared[(int)$value] ?? '';
if ($type === 'inlineStr' && isset($cell->is->t)) $value = (string)$cell->is->t;
$row[$idx] = $value;
}
if ($row) {
ksort($row);
$rows[] = array_values($row + array_fill(0, max(array_keys($row)) + 1, ''));
}
}
return $rows;
}
function wp_rota_import_rows(string $path, string $name): array {
$ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
if ($ext === 'xlsx') return wp_rota_import_rows_from_xlsx($path);
return wp_rota_import_rows_from_csv($path);
}
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
if (!$rotaSchemaOk) {
wp_feature_csv_download('rota_export_' . date('Ymd_His') . '.csv', ['Message'], [['Rota export is not available because the rota table could not be repaired.']]);
exit;
}
$rows = [];
try {
$stmt = $pdo->query("SELECT code, shift_date, staff_name, route_name, van_label, role_label, notes, created_at FROM rota_entries ORDER BY shift_date ASC, staff_name ASC");
foreach ($stmt->fetchAll() as $r) {
$rows[] = [$r['code'], $r['shift_date'], $r['staff_name'], $r['route_name'], $r['van_label'], $r['role_label'], $r['notes'], $r['created_at']];
}
} catch (Throwable $e) {
$rows[] = ['Rota export failed to load rows.'];
wp_feature_csv_download('rota_export_' . date('Ymd_His') . '.csv', ['Message'], $rows);
exit;
}
wp_feature_csv_download('rota_export_' . date('Ymd_His') . '.csv', ['Code','Date','Staff','Route','Van','Role','Notes','Created At'], $rows);
exit;
}
if ($_SERVER['REQUEST_METHOD'] === 'POST' && (isAdminRole() || hasPermission('rota.edit'))) {
$action = $_POST['action'] ?? '';
if ($action === 'import_rota') {
if (!$rotaSchemaOk) {
wp_feature_flash_set('error', 'The rota table is not available for import.');
app_redirect('management/rota');
}
if (empty($_FILES['rota_file']['tmp_name']) || !is_uploaded_file($_FILES['rota_file']['tmp_name'])) {
wp_feature_flash_set('error', 'Upload a CSV or XLSX rota file first.');
app_redirect('management/rota');
}
$rows = wp_rota_import_rows($_FILES['rota_file']['tmp_name'], (string)($_FILES['rota_file']['name'] ?? 'rota.csv'));
if (!$rows || count($rows) < 2) {
wp_feature_flash_set('error', 'No rota rows were found. Use columns like Date, Staff, Route, Van, Role and Notes.');
app_redirect('management/rota');
}
$headers = array_map('wp_rota_import_header_key', array_shift($rows));
$findCol = static function(array $names) use ($headers): ?int {
foreach ($names as $name) {
$idx = array_search($name, $headers, true);
if ($idx !== false) return (int)$idx;
}
return null;
};
$dateCol = $findCol(['date','shift_date','shift','day']);
$staffCol = $findCol(['staff','staff_name','name','employee','worker','driver','porter']);
$routeCol = $findCol(['route','route_name','job','run']);
$vanCol = $findCol(['van','vehicle','vehicle_reg','registration','reg']);
$roleCol = $findCol(['role','role_label','position']);
$notesCol = $findCol(['notes','note','comments','comment']);
if ($dateCol === null || $staffCol === null) {
wp_feature_flash_set('error', 'Import needs at least Date and Staff columns.');
app_redirect('management/rota');
}
$ins = $pdo->prepare("INSERT INTO rota_entries (code, shift_date, staff_user_id, staff_name, route_name, van_label, role_label, notes, created_by, created_by_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$imported = 0;
$skipped = 0;
foreach ($rows as $row) {
$get = static fn(?int $idx): string => ($idx !== null && isset($row[$idx])) ? trim((string)$row[$idx]) : '';
$shiftDate = wp_rota_import_date($get($dateCol));
$staffName = $get($staffCol);
if ($shiftDate === '' || $staffName === '') { $skipped++; continue; }
$staffUserId = wp_feature_find_user_id($staffName) ?: 0;
if ($staffUserId) {
foreach ($users as $u) {
if ((int)$u['id'] === (int)$staffUserId) { $staffName = trim((string)($u['display_name'] ?: $u['username'] ?: $staffName)); break; }
}
}
$code = wp_feature_generate_code('ROT', 'rota_entries');
$ins->execute([$code, $shiftDate, $staffUserId, $staffName, $get($routeCol), $get($vanCol), $get($roleCol), $get($notesCol), wp_feature_current_user_id(), wp_feature_current_user_name()]);
$imported++;
}
logActivity('rota_imported', 'rota', null, "Imported $imported rota rows ($skipped skipped)");
wp_feature_flash_set('success', "Imported $imported rota row" . ($imported === 1 ? '' : 's') . ($skipped ? " ($skipped skipped)." : '.'));
app_redirect('management/rota');
}
if ($action === 'save_rota') {
$id = (int)($_POST['id'] ?? 0);
$shiftDate = trim((string)($_POST['shift_date'] ?? ''));
$staffUserId = (int)($_POST['staff_user_id'] ?? 0);
$staffName = trim((string)($_POST['staff_name'] ?? ''));
if ($staffUserId > 0) {
foreach ($users as $u) {
if ((int)$u['id'] === $staffUserId) {
$staffName = trim((string)($u['display_name'] ?: $u['username'] ?: $staffName));
break;
}
}
} else {
$staffUserId = wp_feature_find_user_id($staffName) ?: 0;
}
$routeName = trim((string)($_POST['route_name'] ?? ''));
$vanChoice = trim((string)($_POST['van_choice'] ?? ''));
$vanLabel = trim((string)($_POST['van_label'] ?? ''));
if ($vanChoice !== '' && $vanChoice !== '__other__') {
$vanLabel = $vanChoice;
}
$roleLabel = trim((string)($_POST['role_label'] ?? ''));
$notes = trim((string)($_POST['notes'] ?? ''));
if ($shiftDate === '' || $staffName === '') {
wp_feature_flash_set('error', 'Date and staff are required.');
app_redirect('management/rota');
}
if ($id > 0) {
$stmt = $pdo->prepare("UPDATE rota_entries SET shift_date=?, staff_user_id=?, staff_name=?, route_name=?, van_label=?, role_label=?, notes=?, updated_at=NOW() WHERE id=?");
$stmt->execute([$shiftDate, $staffUserId, $staffName, $routeName, $vanLabel, $roleLabel, $notes, $id]);
logActivity('rota_updated', 'rota_entry', $id, 'Updated rota entry ' . $staffName . ' on ' . $shiftDate);
wp_feature_flash_set('success', 'Rota entry updated.');
} else {
$code = wp_feature_generate_code('ROT', 'rota_entries');
$stmt = $pdo->prepare("INSERT INTO rota_entries (code, shift_date, staff_user_id, staff_name, route_name, van_label, role_label, notes, created_by, created_by_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->execute([$code, $shiftDate, $staffUserId, $staffName, $routeName, $vanLabel, $roleLabel, $notes, wp_feature_current_user_id(), wp_feature_current_user_name()]);
logActivity('rota_created', 'rota_entry', (int)$pdo->lastInsertId(), 'Created rota entry ' . $staffName . ' on ' . $shiftDate);
wp_feature_flash_set('success', 'Rota entry added.');
}
app_redirect('management/rota');
}
if ($action === 'delete_rota') {
$id = (int)($_POST['id'] ?? 0);
if ($id > 0) {
$stmt = $pdo->prepare("DELETE FROM rota_entries WHERE id = ?");
$stmt->execute([$id]);
logActivity('rota_deleted', 'rota_entry', $id, 'Deleted rota entry #' . $id);
wp_feature_flash_set('success', 'Rota entry removed.');
}
app_redirect('management/rota');
}
}
if (!$rotaSchemaOk) {
wp_feature_flash_set('error', 'The rota table could not be repaired automatically.');
}
$edit = null;
if (isset($_GET['edit']) && ctype_digit((string)$_GET['edit']) && $canManageAll) {
$stmt = $pdo->prepare("SELECT * FROM rota_entries WHERE id = ?");
$stmt->execute([(int)$_GET['edit']]);
$edit = $stmt->fetch() ?: null;
}
$mine = isset($_GET['mine']) || !$canManageAll;
$filterDate = trim((string)($_GET['date'] ?? '')) ?: date('Y-m-d');
$filterStaff = trim((string)($_GET['staff'] ?? ''));
if ($mine) {
$entries = wp_feature_fetch_rota_for_current_user($filterDate, 250);
if ($filterStaff !== '') {
$needle = mb_strtolower(trim($filterStaff));
$entries = array_values(array_filter($entries, static function(array $row) use ($needle): bool {
return $needle === '' || str_contains(mb_strtolower((string)($row['staff_name'] ?? '')), $needle);
}));
}
} else {
$where = [];
$params = [];
if ($filterDate !== '') { $where[] = 'shift_date >= ?'; $params[] = $filterDate; }
if ($filterStaff !== '' && $canManageAll) { $where[] = 'staff_name LIKE ?'; $params[] = '%' . $filterStaff . '%'; }
$sql = 'SELECT * FROM rota_entries' . ($where ? ' WHERE ' . implode(' AND ', $where) : '') . ' ORDER BY shift_date ASC, staff_name ASC LIMIT 250';
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$entries = $stmt->fetchAll() ?: [];
}
$flash = wp_feature_flash_get();
include __DIR__ . '/../partials/header.php';
?>
<div class="content-header">
<div>
<h1 class="content-title">🗓️ Rota</h1>
<p class="content-subtitle"><?= e($mine ? 'Your attached shifts and assignments.' : 'Manage rota entries and assignments.') ?></p>
</div>
<div class="d-flex gap-md" style="flex-wrap:wrap;">
<a class="btn btn-secondary" href="<?= e(app_url('management/rota?mine=1')) ?>">My Rota</a>
<?php if ($canManageAll): ?><a class="btn btn-secondary" href="<?= e(app_url('management/rota')) ?>">All Rota</a><?php endif; ?>
<a class="btn btn-secondary" href="<?= e(app_url('management/rota?export=csv')) ?>">⬇️ Export CSV</a>
</div>
</div>
<?php if ($flash): ?><div class="alert alert-<?= e($flash['type'] === 'error' ? 'error' : 'success') ?>" style="margin-bottom:16px;"><?= e($flash['message']) ?></div><?php endif; ?>
<?php if ($canManageAll): ?>
<div class="grid grid-2 mb-lg">
<div class="card">
<div class="card-header"><h3 class="card-title"><?= $edit ? 'Edit Entry' : 'Add Entry' ?></h3></div>
<form method="post" class="grid" style="gap:12px;">
<input type="hidden" name="action" value="save_rota">
<input type="hidden" name="id" value="<?= (int)($edit['id'] ?? 0) ?>">
<div><label class="field-label">Date</label><input class="input" type="date" name="shift_date" value="<?= e($edit['shift_date'] ?? date('Y-m-d')) ?>" required></div>
<div>
<label class="field-label">Staff account</label>
<select class="input" name="staff_user_id" id="rotaStaffSelect">
<option value="0">Other / manual input</option>
<?php foreach ($users as $u): ?>
<?php $selectedUser = (int)($edit['staff_user_id'] ?? 0) === (int)$u['id']; ?>
<option value="<?= (int)$u['id'] ?>" data-name="<?= e((string)($u['display_name'] ?: $u['username'])) ?>" <?= $selectedUser ? 'selected' : '' ?>><?= e((string)($u['display_name'] ?: $u['username'])) ?><?= !empty($u['email']) ? ' · ' . e($u['email']) : '' ?></option>
<?php endforeach; ?>
</select>
</div>
<div id="rotaStaffManualWrap">
<label class="field-label">Staff name</label>
<input class="input" type="text" name="staff_name" id="rotaStaffName" value="<?= e($edit['staff_name'] ?? '') ?>" required placeholder="Manual staff name">
<div class="text-muted" style="margin-top:6px;font-size:.88rem;">Use Other only if the person is not in the database.</div>
</div>
<div><label class="field-label">Route</label><input class="input" type="text" name="route_name" value="<?= e($edit['route_name'] ?? '') ?>"></div>
<div>
<label class="field-label">Vehicle</label>
<?php $editVanLabel = (string)($edit['van_label'] ?? ''); ?>
<select class="input" name="van_choice" id="rotaVanSelect">
<option value="">No vehicle</option>
<?php foreach ($vans as $v): ?>
<?php $vLabel = trim((string)($v['plate_short'] ?? '') . ' ' . (string)($v['plate_full'] ?? '')); ?>
<option value="<?= e($vLabel) ?>" <?= $editVanLabel === $vLabel ? 'selected' : '' ?>><?= e($vLabel) ?><?= trim(($v['make'] ?? '') . ' ' . ($v['model'] ?? '')) ? ' · ' . e(trim(($v['make'] ?? '') . ' ' . ($v['model'] ?? ''))) : '' ?></option>
<?php endforeach; ?>
<option value="__other__" <?= ($editVanLabel !== '' && !in_array($editVanLabel, array_map(static fn($v) => trim((string)($v['plate_short'] ?? '') . ' ' . (string)($v['plate_full'] ?? '')), $vans), true)) ? 'selected' : '' ?>>Other / manual input</option>
</select>
</div>
<div id="rotaVanManualWrap">
<label class="field-label">Manual vehicle</label>
<input class="input" type="text" name="van_label" id="rotaVanLabel" value="<?= e($editVanLabel) ?>" placeholder="Vehicle/reg if not in database">
</div>
<div><label class="field-label">Role</label><input class="input" type="text" name="role_label" value="<?= e($edit['role_label'] ?? '') ?>" placeholder="Driver / Porter / Office"></div>
<div><label class="field-label">Notes</label><textarea class="input" name="notes" rows="3"><?= e($edit['notes'] ?? '') ?></textarea></div>
<div class="d-flex gap-md" style="flex-wrap:wrap;"><button class="btn btn-primary" type="submit"><?= $edit ? 'Save Changes' : 'Add Entry' ?></button><?php if ($edit): ?><a class="btn btn-secondary" href="<?= e(app_url('management/rota')) ?>">Cancel</a><?php endif; ?></div>
</form>
</div>
<div class="card">
<div class="card-header"><h3 class="card-title">Upload Rota Sheet</h3></div>
<form method="post" enctype="multipart/form-data" class="grid" style="gap:12px;">
<input type="hidden" name="action" value="import_rota">
<div><label class="field-label">CSV or XLSX file</label><input class="input" type="file" name="rota_file" accept=".csv,.xlsx,text/csv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" required></div>
<p class="text-muted" style="margin:0;font-size:.9rem;">Required columns: Date and Staff. Optional columns: Route, Van, Role, Notes. Staff and vehicle names are matched to the database where possible.</p>
<div><button class="btn btn-primary" type="submit">Upload and Import</button></div>
</form>
</div>
<div class="card">
<div class="card-header"><h3 class="card-title">Filters</h3></div>
<form method="get" class="grid" style="gap:12px;">
<div><label class="field-label">From date</label><input class="input" type="date" name="date" value="<?= e($filterDate) ?>"></div>
<div><label class="field-label">Staff</label><input class="input" type="text" name="staff" value="<?= e($filterStaff) ?>" placeholder="Search staff name"></div>
<div class="d-flex gap-md" style="flex-wrap:wrap;"><button class="btn btn-secondary" type="submit">Apply</button><a class="btn btn-secondary" href="<?= e(app_url('management/rota')) ?>">Reset</a></div>
</form>
</div>
</div>
<?php endif; ?>
<div class="card">
<div class="card-header"><h3 class="card-title"><?= e($mine ? 'My shifts' : 'Entries') ?></h3></div>
<div class="table-wrap">
<table class="table" style="min-width:900px;">
<thead><tr><th>Date</th><th>Staff</th><th>Route</th><th>Van</th><th>Role</th><th>Notes</th><?php if ($canManageAll): ?><th>Actions</th><?php endif; ?></tr></thead>
<tbody>
<?php if (!$entries): ?><tr><td colspan="<?= $canManageAll ? 7 : 6 ?>" class="text-muted">No rota entries found.</td></tr>
<?php else: foreach ($entries as $row): ?>
<tr>
<td><?= e($row['shift_date']) ?></td>
<td><?= e($row['staff_name']) ?></td>
<td><?= e($row['route_name']) ?></td>
<td><?= e($row['van_label']) ?></td>
<td><?= e($row['role_label']) ?></td>
<td><?= e($row['notes']) ?></td>
<?php if ($canManageAll): ?><td><div class="d-flex gap-sm" style="flex-wrap:wrap;"><a class="btn btn-secondary btn-xs" href="<?= e(app_url('management/rota?edit=' . (int)$row['id'])) ?>">Edit</a><form method="post" onsubmit="return confirm('Delete this rota entry?');" style="display:inline;"><input type="hidden" name="action" value="delete_rota"><input type="hidden" name="id" value="<?= (int)$row['id'] ?>"><button class="btn btn-danger btn-xs" type="submit">Delete</button></form></div></td><?php endif; ?>
</tr>
<?php endforeach; endif; ?>
</tbody>
</table>
</div>
</div>
<script>
(function(){
const staffSelect = document.getElementById('rotaStaffSelect');
const staffName = document.getElementById('rotaStaffName');
const staffWrap = document.getElementById('rotaStaffManualWrap');
const vanSelect = document.getElementById('rotaVanSelect');
const vanWrap = document.getElementById('rotaVanManualWrap');
const vanInput = document.getElementById('rotaVanLabel');
function syncStaff(){
if(!staffSelect || !staffName || !staffWrap) return;
const opt = staffSelect.options[staffSelect.selectedIndex];
const isOther = !staffSelect.value || staffSelect.value === '0';
staffWrap.style.display = isOther ? '' : 'none';
if(!isOther && opt?.dataset?.name) staffName.value = opt.dataset.name;
}
function syncVan(){
if(!vanSelect || !vanWrap || !vanInput) return;
const isOther = vanSelect.value === '__other__';
vanWrap.style.display = isOther ? '' : 'none';
if(!isOther) vanInput.value = vanSelect.value || '';
}
staffSelect?.addEventListener('change', syncStaff);
vanSelect?.addEventListener('change', syncVan);
syncStaff(); syncVan();
})();
</script>
<?php include __DIR__ . '/../partials/footer.php'; ?>