BROOKO UK NETWORK
Where code meets creativity & adventure
File viewer
feature_modules.php
Type
php
Size
41.28 KB
Modified
15 May
feature_modules.php
41.28 KB
<?php
if (!function_exists('wp_feature_require_management')) {
function wp_feature_require_management(array $fallbackPermissions = []): void {
requireAuth();
if (isAdminRole() || hasPermission('management.access')) {
return;
}
if ($fallbackPermissions && hasAnyPermission($fallbackPermissions)) {
return;
}
http_response_code(403);
die('Access denied. Management privileges required.');
}
}
if (!function_exists('wp_feature_require_admin')) {
function wp_feature_require_admin(): void {
requireAuth();
if (!isAdminRole()) {
http_response_code(403);
die('Access denied. Admin required.');
}
}
}
if (!function_exists('wp_feature_flash_set')) {
function wp_feature_flash_set(string $type, string $message): void {
$_SESSION['feature_flash'] = ['type' => $type, 'message' => $message];
}
}
if (!function_exists('wp_feature_flash_get')) {
function wp_feature_flash_get(): ?array {
if (empty($_SESSION['feature_flash']) || !is_array($_SESSION['feature_flash'])) return null;
$flash = $_SESSION['feature_flash'];
unset($_SESSION['feature_flash']);
return $flash;
}
}
if (!function_exists('wp_feature_current_user_name')) {
function wp_feature_current_user_name(): string {
return (string)($_SESSION['user_display_name'] ?? ($_SESSION['user_role'] ?? 'User'));
}
}
if (!function_exists('wp_feature_current_user_id')) {
function wp_feature_current_user_id(): ?int {
return isset($_SESSION['user_id']) ? (int)$_SESSION['user_id'] : null;
}
}
if (!function_exists('wp_feature_current_user_identifiers')) {
function wp_feature_current_user_identifiers(): array {
$items = [];
if (!empty($_SESSION['user_id'])) {
$items[] = ['type' => 'id', 'value' => (int)$_SESSION['user_id']];
}
foreach ([
$_SESSION['user_display_name'] ?? null,
$_SESSION['username'] ?? null,
$_SESSION['user_email'] ?? null,
] as $value) {
$value = trim((string)$value);
if ($value !== '') {
$items[] = ['type' => 'text', 'value' => mb_strtolower($value)];
}
}
if (function_exists('getCurrentUser')) {
$user = getCurrentUser() ?: [];
foreach ([$user['display_name'] ?? null, $user['username'] ?? null, $user['email'] ?? null] as $value) {
$value = trim((string)$value);
if ($value !== '') {
$items[] = ['type' => 'text', 'value' => mb_strtolower($value)];
}
}
}
$seen = [];
$final = [];
foreach ($items as $row) {
$key = $row['type'] . ':' . (string)$row['value'];
if (isset($seen[$key])) continue;
$seen[$key] = true;
$final[] = $row;
}
return $final;
}
}
if (!function_exists('wp_feature_users')) {
function wp_feature_users(): array {
global $pdo;
try {
$stmt = $pdo->query("SELECT id, username, display_name, email, role FROM users WHERE is_active = 1 ORDER BY display_name ASC, username ASC");
return $stmt->fetchAll() ?: [];
} catch (Throwable $e) {
return [];
}
}
}
if (!function_exists('wp_feature_normalize_person_key')) {
function wp_feature_normalize_person_key(?string $value): string {
$value = mb_strtolower(trim((string)$value));
if ($value === '') return '';
$value = preg_replace('/[^a-z0-9]+/u', '', $value) ?? '';
return $value;
}
}
if (!function_exists('wp_feature_person_tokens')) {
function wp_feature_person_tokens(?string $value): array {
$value = mb_strtolower(trim((string)$value));
if ($value === '') return [];
$parts = preg_split('/[^a-z0-9]+/u', $value) ?: [];
$parts = array_values(array_filter(array_map('trim', $parts), static fn($v) => $v !== ''));
return array_values(array_unique($parts));
}
}
if (!function_exists('wp_feature_person_token_key')) {
function wp_feature_person_token_key(?string $value): string {
$tokens = wp_feature_person_tokens($value);
sort($tokens);
return implode('|', $tokens);
}
}
if (!function_exists('wp_feature_person_matches')) {
function wp_feature_person_matches(?string $candidate, array $aliases, array $aliasNorms, array $aliasTokenKeys): bool {
$candidate = trim((string)$candidate);
if ($candidate === '') return false;
$candidateLower = mb_strtolower($candidate);
if (in_array($candidateLower, $aliases, true)) return true;
$candidateNorm = wp_feature_normalize_person_key($candidate);
if ($candidateNorm !== '') {
foreach ($aliasNorms as $aliasNorm) {
if ($aliasNorm === '') continue;
if ($candidateNorm === $aliasNorm) return true;
if (strlen($candidateNorm) >= 6 && strlen($aliasNorm) >= 6) {
if (str_contains($candidateNorm, $aliasNorm) || str_contains($aliasNorm, $candidateNorm)) return true;
}
}
}
$tokenKey = wp_feature_person_token_key($candidate);
if ($tokenKey !== '' && in_array($tokenKey, $aliasTokenKeys, true)) return true;
$candidateTokens = wp_feature_person_tokens($candidate);
if (!$candidateTokens) return false;
foreach ($aliasTokenKeys as $aliasTokenKey) {
if ($aliasTokenKey === '') continue;
$aliasTokens = explode('|', $aliasTokenKey);
$common = array_values(array_intersect($candidateTokens, $aliasTokens));
if (count($common) >= 2) return true;
if (count($common) >= 1 && count($candidateTokens) === 1 && count($aliasTokens) === 1) return true;
}
return false;
}
}
if (!function_exists('wp_feature_find_user_id')) {
function wp_feature_find_user_id(string $needle): ?int {
$needle = trim($needle);
if ($needle === '') return null;
$needleLower = mb_strtolower($needle);
$needleNorm = wp_feature_normalize_person_key($needle);
$needleTokenKey = wp_feature_person_token_key($needle);
foreach (wp_feature_users() as $user) {
$aliases = [];
$aliasNorms = [];
$aliasTokenKeys = [];
foreach (['display_name','username','email'] as $k) {
$candidate = trim((string)($user[$k] ?? ''));
if ($candidate === '') continue;
$candidateLower = mb_strtolower($candidate);
$aliases[] = $candidateLower;
$aliasNorms[] = wp_feature_normalize_person_key($candidate);
$aliasTokenKeys[] = wp_feature_person_token_key($candidate);
if ($candidateLower === $needleLower) return (int)$user['id'];
if ($needleNorm !== '' && wp_feature_normalize_person_key($candidate) === $needleNorm) return (int)$user['id'];
if ($needleTokenKey !== '' && wp_feature_person_token_key($candidate) === $needleTokenKey) return (int)$user['id'];
}
if (wp_feature_person_matches($needle, array_values(array_unique($aliases)), array_values(array_unique($aliasNorms)), array_values(array_unique($aliasTokenKeys)))) {
return (int)$user['id'];
}
}
return null;
}
}
if (!function_exists('wp_feature_generate_code')) {
function wp_feature_generate_code(string $prefix, string $table, string $column = 'code'): string {
global $pdo;
$prefix = strtoupper(trim($prefix));
$max = 0;
try {
$stmt = $pdo->query("SELECT {$column} FROM {$table} WHERE {$column} LIKE " . $pdo->quote($prefix . '-%'));
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $val) {
if (preg_match('/^' . preg_quote($prefix, '/') . '-(\d+)/', (string)$val, $m)) {
$max = max($max, (int)$m[1]);
}
}
} catch (Throwable $e) {}
return sprintf('%s-%04d', $prefix, $max + 1);
}
}
if (!function_exists('wp_feature_csv_download')) {
function wp_feature_csv_download(string $filename, array $headers, array $rows): void {
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . preg_replace('/[^A-Za-z0-9._-]/', '_', $filename) . '"');
$out = fopen('php://output', 'w');
fputcsv($out, $headers);
foreach ($rows as $row) {
fputcsv($out, $row);
}
fclose($out);
exit;
}
}
if (!function_exists('wp_feature_slugify_filename')) {
function wp_feature_slugify_filename(string $name): string {
$name = preg_replace('/[^A-Za-z0-9._-]+/', '_', $name) ?: 'file';
$name = trim($name, '_');
return $name !== '' ? $name : 'file';
}
}
if (!function_exists('wp_feature_setting')) {
function wp_feature_setting(string $key, string $default = ''): string {
return getSystemInfo($key, $default);
}
}
if (!function_exists('wp_feature_save_settings')) {
function wp_feature_save_settings(array $settings): void {
global $pdo;
$stmt = $pdo->prepare("INSERT INTO system_info (`key`,`value`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)");
foreach ($settings as $key => $value) {
$stmt->execute([(string)$key, (string)$value]);
}
}
}
if (!function_exists('wp_onedrive_is_configured')) {
function wp_onedrive_is_configured(): bool {
return wp_feature_setting('onedrive_tenant_id') !== ''
&& wp_feature_setting('onedrive_client_id') !== ''
&& wp_feature_setting('onedrive_client_secret') !== ''
&& wp_feature_setting('onedrive_drive_id') !== '';
}
}
if (!function_exists('wp_onedrive_token')) {
function wp_onedrive_token(): array {
$tenantId = wp_feature_setting('onedrive_tenant_id');
$clientId = wp_feature_setting('onedrive_client_id');
$clientSecret = wp_feature_setting('onedrive_client_secret');
if ($tenantId === '' || $clientId === '' || $clientSecret === '') {
return ['success' => false, 'message' => 'OneDrive integration is not configured.'];
}
if (!function_exists('curl_init')) {
return ['success' => false, 'message' => 'cURL is not enabled on this server.'];
}
$url = 'https://login.microsoftonline.com/' . rawurlencode($tenantId) . '/oauth2/v2.0/token';
$fields = http_build_query([
'client_id' => $clientId,
'client_secret' => $clientSecret,
'scope' => 'https://graph.microsoft.com/.default',
'grant_type' => 'client_credentials',
]);
$ch = curl_init($url);
curl_setopt_array($ch, [
CURLOPT_RETURNTRANSFER => true,
CURLOPT_POST => true,
CURLOPT_POSTFIELDS => $fields,
CURLOPT_HTTPHEADER => ['Content-Type: application/x-www-form-urlencoded'],
CURLOPT_TIMEOUT => 30,
]);
$resp = curl_exec($ch);
$code = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
$err = curl_error($ch);
curl_close($ch);
if ($resp === false || $resp === '') {
return ['success' => false, 'message' => $err !== '' ? $err : 'No response from Microsoft token endpoint.'];
}
$data = json_decode($resp, true);
if ($code < 200 || $code >= 300 || !is_array($data) || empty($data['access_token'])) {
return ['success' => false, 'message' => $data['error_description'] ?? ('Token request failed with HTTP ' . $code)];
}
return ['success' => true, 'token' => $data['access_token']];
}
}
if (!function_exists('wp_onedrive_upload_file')) {
function wp_onedrive_upload_file(string $tmpPath, string $originalName, string $subfolder = 'incidents'): array {
$driveId = wp_feature_setting('onedrive_drive_id');
$baseFolder = trim(wp_feature_setting('onedrive_base_folder', 'WorkersPanel'), '/');
if (!file_exists($tmpPath)) {
return ['success' => false, 'message' => 'Uploaded file temp path not found.'];
}
$tokenResult = wp_onedrive_token();
if (!$tokenResult['success']) return $tokenResult;
$token = $tokenResult['token'];
$ext = pathinfo($originalName, PATHINFO_EXTENSION);
$safeName = wp_feature_slugify_filename(pathinfo($originalName, PATHINFO_FILENAME));
$finalName = date('Ymd_His') . '_' . $safeName . ($ext !== '' ? '.' . strtolower($ext) : '');
$parts = array_filter([$baseFolder, trim($subfolder, '/')], fn($v) => $v !== '');
$encoded = array_map(static fn($seg) => rawurlencode($seg), $parts);
$path = implode('/', $encoded);
$nameEnc = rawurlencode($finalName);
$uploadUrl = 'https://graph.microsoft.com/v1.0/drives/' . rawurlencode($driveId) . '/root:/' . ($path !== '' ? $path . '/' : '') . $nameEnc . ':/content';
if (!function_exists('curl_init')) {
return ['success' => false, 'message' => 'cURL is not enabled on this server.'];
}
$body = file_get_contents($tmpPath);
$mime = mime_content_type($tmpPath) ?: 'application/octet-stream';
$ch = curl_init($uploadUrl);
curl_setopt_array($ch, [
CURLOPT_RETURNTRANSFER => true,
CURLOPT_CUSTOMREQUEST => 'PUT',
CURLOPT_POSTFIELDS => $body,
CURLOPT_HTTPHEADER => [
'Authorization: Bearer ' . $token,
'Content-Type: ' . $mime,
],
CURLOPT_TIMEOUT => 60,
]);
$resp = curl_exec($ch);
$code = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
$err = curl_error($ch);
curl_close($ch);
if ($resp === false || $resp === '') {
return ['success' => false, 'message' => $err !== '' ? $err : 'No response from Microsoft Graph upload.'];
}
$data = json_decode($resp, true);
if ($code < 200 || $code >= 300 || !is_array($data)) {
$msg = $data['error']['message'] ?? ('Upload failed with HTTP ' . $code);
return ['success' => false, 'message' => $msg];
}
return [
'success' => true,
'item_id' => (string)($data['id'] ?? ''),
'name' => (string)($data['name'] ?? $finalName),
'web_url' => (string)($data['webUrl'] ?? ''),
];
}
}
if (!function_exists('wp_feature_ensure_time_clock_schema')) {
function wp_feature_ensure_time_clock_schema(): bool {
global $pdo;
if (!isset($pdo) || !($pdo instanceof PDO)) return false;
try {
$pdo->exec("CREATE TABLE IF NOT EXISTS `time_clock_entries` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL,
`username` varchar(120) NOT NULL,
`display_name` varchar(150) DEFAULT NULL,
`role_name` varchar(80) DEFAULT NULL,
`van_label` varchar(120) DEFAULT NULL,
`van_reg` varchar(120) DEFAULT NULL,
`partner_name` varchar(160) DEFAULT NULL,
`mileage_in` int(11) DEFAULT NULL,
`mileage_out` int(11) DEFAULT NULL,
`clock_in_at` datetime NOT NULL,
`clock_out_at` datetime DEFAULT NULL,
`status` varchar(32) NOT NULL DEFAULT 'clocked_in',
`ip_address` varchar(45) DEFAULT NULL,
`notes` text DEFAULT NULL,
`start_meta_json` longtext DEFAULT NULL,
`end_meta_json` longtext DEFAULT NULL,
`photo_count` int(11) NOT NULL DEFAULT 0,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_clock_user` (`user_id`),
KEY `idx_clock_in_at` (`clock_in_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
$extra = [
'van_label' => "ALTER TABLE `time_clock_entries` ADD COLUMN `van_label` varchar(120) DEFAULT NULL AFTER `role_name`",
'van_reg' => "ALTER TABLE `time_clock_entries` ADD COLUMN `van_reg` varchar(120) DEFAULT NULL AFTER `van_label`",
'partner_name' => "ALTER TABLE `time_clock_entries` ADD COLUMN `partner_name` varchar(160) DEFAULT NULL AFTER `van_reg`",
'mileage_in' => "ALTER TABLE `time_clock_entries` ADD COLUMN `mileage_in` int(11) DEFAULT NULL AFTER `partner_name`",
'mileage_out' => "ALTER TABLE `time_clock_entries` ADD COLUMN `mileage_out` int(11) DEFAULT NULL AFTER `mileage_in`",
'start_meta_json' => "ALTER TABLE `time_clock_entries` ADD COLUMN `start_meta_json` longtext NULL AFTER `notes`",
'end_meta_json' => "ALTER TABLE `time_clock_entries` ADD COLUMN `end_meta_json` longtext NULL AFTER `start_meta_json`",
'photo_count' => "ALTER TABLE `time_clock_entries` ADD COLUMN `photo_count` int(11) NOT NULL DEFAULT 0 AFTER `end_meta_json`",
];
foreach ($extra as $col => $sql) {
if (!wp_feature_column_exists('time_clock_entries', $col)) {
try {
$pdo->exec($sql);
} catch (Throwable $e) {}
}
}
$pdo->exec("CREATE TABLE IF NOT EXISTS `time_clock_photos` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`clock_entry_id` bigint(20) UNSIGNED NOT NULL,
`phase` varchar(20) NOT NULL DEFAULT 'clock_out',
`storage` varchar(32) NOT NULL DEFAULT 'onedrive',
`file_name` varchar(255) NOT NULL,
`onedrive_item_id` varchar(255) DEFAULT NULL,
`onedrive_web_url` text DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_clock_entry_phase` (`clock_entry_id`,`phase`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
return true;
} catch (Throwable $e) {
return false;
}
}
}
if (!function_exists('wp_feature_clock_role_mode')) {
function wp_feature_clock_role_mode(?string $role = null): string {
$role = wp_normalize_role_name($role ?: ($_SESSION['user_role'] ?? ''));
return match ($role) {
'driver', 'transport-manager-driver' => 'driver',
'porter', 'transport-manager-porter' => 'porter',
default => 'general',
};
}
}
if (!function_exists('wp_feature_clock_start_requirements')) {
function wp_feature_clock_start_requirements(?string $role = null): array {
$mode = wp_feature_clock_role_mode($role);
if ($mode === 'driver') {
return [
'title' => 'Driver start-of-shift details',
'permission' => 'clock.driver_start',
'photo_permission' => 'clock.photo_upload',
'require_van' => true,
'require_partner' => true,
'partner_label' => 'Porter / helper',
'require_van_reg' => true,
'require_mileage' => true,
'photo_min' => 5,
'items' => [
'vehicle_walkaround' => 'Vehicle walk-around completed before leaving',
'keys_docs_checked' => 'Keys, documents and fuel card checked',
],
];
}
if ($mode === 'porter') {
return [
'title' => 'Porter start-of-shift confirmation',
'permission' => 'clock.porter_start',
'photo_permission' => null,
'require_van' => true,
'require_partner' => true,
'partner_label' => 'Driver',
'require_van_reg' => false,
'require_mileage' => false,
'photo_min' => 0,
'items' => [
'assigned_van_confirmed' => 'Assigned van / job team confirmed',
'ppe_equipment_ready' => 'PPE and equipment ready for the shift',
],
];
}
return [
'title' => 'Start-of-shift note',
'permission' => null,
'photo_permission' => null,
'require_van' => false,
'require_partner' => false,
'partner_label' => 'Working with',
'require_van_reg' => false,
'require_mileage' => false,
'photo_min' => 0,
'items' => [],
];
}
}
if (!function_exists('wp_feature_clock_checkout_requirements')) {
function wp_feature_clock_checkout_requirements(?string $role = null): array {
$mode = wp_feature_clock_role_mode($role);
if ($mode === 'driver') {
return [
'title' => 'Driver end-of-shift checks',
'permission' => 'clock.driver_checkout',
'photo_permission' => 'clock.photo_upload',
'require_photo' => true,
'photo_min' => 6,
'require_van' => true,
'require_partner' => true,
'partner_label' => 'Porter / helper',
'require_mileage' => true,
'items' => [
'van_parked' => 'Van parked correctly',
'locked_disclock' => 'Van locked and disc lock fitted',
'inventory_checked' => 'Inventory checked and OK',
'fuel_card_returned' => 'Fuel card returned',
'fuel_added' => 'Fuel question completed',
],
];
}
if ($mode === 'porter') {
return [
'title' => 'Porter end-of-shift checks',
'permission' => 'clock.porter_checkout',
'photo_permission' => null,
'require_photo' => false,
'photo_min' => 0,
'require_van' => true,
'require_partner' => true,
'partner_label' => 'Driver',
'require_mileage' => false,
'items' => [
'job_area_cleared' => 'Job area cleared and left safe',
'equipment_returned' => 'Equipment returned and accounted for',
'issues_reported' => 'Any damage / issues have been reported',
],
];
}
return [
'title' => 'Shift notes',
'permission' => null,
'photo_permission' => null,
'require_photo' => false,
'photo_min' => 0,
'require_van' => false,
'require_partner' => false,
'partner_label' => 'Working with',
'require_mileage' => false,
'items' => [],
];
}
}
if (!function_exists('wp_feature_clock_upload_files')) {
function wp_feature_clock_upload_files(int $entryId, array $files, string $phase = 'clock_out'): array {
global $pdo;
$saved = 0;
$errors = [];
if ($entryId <= 0 || !isset($pdo) || !($pdo instanceof PDO)) {
return ['saved' => 0, 'errors' => ['Database unavailable for upload save.']];
}
if (!wp_feature_ensure_time_clock_schema()) {
return ['saved' => 0, 'errors' => ['Time clock storage is not available.']];
}
$names = $files['name'] ?? [];
if (!is_array($names)) return ['saved' => 0, 'errors' => []];
$tmpNames = $files['tmp_name'] ?? [];
$uploadErrors = $files['error'] ?? [];
$stmt = $pdo->prepare("INSERT INTO time_clock_photos (clock_entry_id, phase, storage, file_name, onedrive_item_id, onedrive_web_url, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW())");
foreach ($names as $i => $name) {
$name = trim((string)$name);
$tmp = (string)($tmpNames[$i] ?? '');
$errCode = (int)($uploadErrors[$i] ?? UPLOAD_ERR_NO_FILE);
if ($name === '' || $errCode === UPLOAD_ERR_NO_FILE) continue;
if ($errCode !== UPLOAD_ERR_OK || $tmp === '' || !is_uploaded_file($tmp)) {
$errors[] = 'One of the shift photos failed to upload.';
continue;
}
$upload = wp_onedrive_upload_file($tmp, $name, 'shift-clock/' . $phase);
if (!empty($upload['success'])) {
$stmt->execute([$entryId, $phase, 'onedrive', (string)($upload['name'] ?? $name), (string)($upload['item_id'] ?? ''), (string)($upload['web_url'] ?? '')]);
$saved += 1;
continue;
}
$errors[] = (string)($upload['message'] ?? 'A shift photo could not be uploaded to OneDrive.');
}
if ($saved > 0) {
try {
$up = $pdo->prepare('UPDATE time_clock_entries SET photo_count = COALESCE(photo_count, 0) + ? WHERE id = ?');
$up->execute([$saved, $entryId]);
} catch (Throwable $e) {}
}
return ['saved' => $saved, 'errors' => array_values(array_unique(array_filter($errors)))];
}
}
if (!function_exists('wp_feature_format_money')) {
function wp_feature_format_money(float $value): string {
return '£' . number_format($value, 2);
}
}
if (!function_exists('wp_feature_table_exists')) {
function wp_feature_table_exists(string $table): bool {
global $pdo;
try {
$stmt = $pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1");
$stmt->execute([$table]);
return (bool)$stmt->fetchColumn();
} catch (Throwable $e) {
return false;
}
}
}
if (!function_exists('wp_feature_column_exists')) {
function wp_feature_column_exists(string $table, string $column): bool {
global $pdo;
try {
$stmt = $pdo->prepare("SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ? LIMIT 1");
$stmt->execute([$table, $column]);
return (bool)$stmt->fetchColumn();
} catch (Throwable $e) {
return false;
}
}
}
if (!function_exists('wp_feature_ensure_rota_schema')) {
function wp_feature_ensure_rota_schema(): bool {
global $pdo;
if (!isset($pdo) || !($pdo instanceof PDO)) {
return false;
}
$createFlat = static function() use ($pdo): void {
$pdo->exec("CREATE TABLE IF NOT EXISTS `rota_entries` (
"
. " `id` int(11) NOT NULL AUTO_INCREMENT,
"
. " `code` varchar(30) NOT NULL,
"
. " `shift_date` date NOT NULL,
"
. " `staff_user_id` int(11) DEFAULT NULL,
"
. " `staff_name` varchar(120) NOT NULL,
"
. " `route_name` varchar(120) DEFAULT NULL,
"
. " `van_label` varchar(80) DEFAULT NULL,
"
. " `role_label` varchar(80) DEFAULT NULL,
"
. " `notes` text DEFAULT NULL,
"
. " `created_by` int(11) DEFAULT NULL,
"
. " `created_by_name` varchar(120) DEFAULT NULL,
"
. " `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
"
. " `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
"
. " PRIMARY KEY (`id`),
"
. " UNIQUE KEY `uniq_rota_code` (`code`),
"
. " KEY `idx_rota_date` (`shift_date`),
"
. " KEY `idx_rota_staff_user_id` (`staff_user_id`)
"
. ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
};
try {
if (!wp_feature_table_exists('rota_entries')) {
$createFlat();
return true;
}
$stmt = $pdo->prepare("SELECT column_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'rota_entries'");
$stmt->execute();
$cols = array_map('strtolower', $stmt->fetchAll(PDO::FETCH_COLUMN) ?: []);
$cols = array_values(array_unique($cols));
$flatRequired = ['code','shift_date','staff_user_id','staff_name','route_name','van_label','role_label','notes','created_by','created_by_name','created_at','updated_at'];
$legacySignals = ['week_start','entry_date','lane_id','value'];
$isLegacyShape = !in_array('shift_date', $cols, true);
foreach ($legacySignals as $signal) {
if (in_array($signal, $cols, true)) {
$isLegacyShape = true;
break;
}
}
if ($isLegacyShape) {
$backupTable = 'rota_entries_oldschema_backup_' . date('YmdHis');
$pdo->exec('RENAME TABLE `rota_entries` TO `' . $backupTable . '`');
$createFlat();
return true;
}
$missing = [];
$required = [
'code' => "ALTER TABLE `rota_entries` ADD COLUMN `code` varchar(30) NULL AFTER `id`",
'shift_date' => "ALTER TABLE `rota_entries` ADD COLUMN `shift_date` date NULL AFTER `code`",
'staff_user_id' => "ALTER TABLE `rota_entries` ADD COLUMN `staff_user_id` int(11) NULL AFTER `shift_date`",
'staff_name' => "ALTER TABLE `rota_entries` ADD COLUMN `staff_name` varchar(120) NULL AFTER `staff_user_id`",
'route_name' => "ALTER TABLE `rota_entries` ADD COLUMN `route_name` varchar(120) NULL AFTER `staff_name`",
'van_label' => "ALTER TABLE `rota_entries` ADD COLUMN `van_label` varchar(80) NULL AFTER `route_name`",
'role_label' => "ALTER TABLE `rota_entries` ADD COLUMN `role_label` varchar(80) NULL AFTER `van_label`",
'notes' => "ALTER TABLE `rota_entries` ADD COLUMN `notes` text NULL AFTER `role_label`",
'created_by' => "ALTER TABLE `rota_entries` ADD COLUMN `created_by` int(11) NULL AFTER `notes`",
'created_by_name' => "ALTER TABLE `rota_entries` ADD COLUMN `created_by_name` varchar(120) NULL AFTER `created_by`",
'created_at' => "ALTER TABLE `rota_entries` ADD COLUMN `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_by_name`",
'updated_at' => "ALTER TABLE `rota_entries` ADD COLUMN `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`",
];
foreach ($required as $col => $sql) {
if (!in_array($col, $cols, true)) {
$missing[$col] = $sql;
}
}
foreach ($missing as $sql) {
$pdo->exec($sql);
}
try {
$pdo->exec("UPDATE `rota_entries` SET `code` = CONCAT('ROT-', LPAD(`id`, 4, '0')) WHERE `code` IS NULL OR `code` = ''");
} catch (Throwable $e) {}
try {
$pdo->exec("UPDATE `rota_entries` SET `staff_name` = 'Unassigned' WHERE `staff_name` IS NULL OR TRIM(`staff_name`) = ''");
} catch (Throwable $e) {}
try {
$pdo->exec("ALTER TABLE `rota_entries` MODIFY `code` varchar(30) NOT NULL");
} catch (Throwable $e) {}
try {
$pdo->exec("ALTER TABLE `rota_entries` MODIFY `shift_date` date NOT NULL");
} catch (Throwable $e) {}
try {
$pdo->exec("ALTER TABLE `rota_entries` MODIFY `staff_name` varchar(120) NOT NULL");
} catch (Throwable $e) {}
try {
$pdo->exec("CREATE UNIQUE INDEX `uniq_rota_code` ON `rota_entries` (`code`)");
} catch (Throwable $e) {}
try {
$pdo->exec("CREATE INDEX `idx_rota_date` ON `rota_entries` (`shift_date`)");
} catch (Throwable $e) {}
try {
$pdo->exec("CREATE INDEX `idx_rota_staff_user_id` ON `rota_entries` (`staff_user_id`)");
} catch (Throwable $e) {}
return true;
} catch (Throwable $e) {
return false;
}
}
}
if (!function_exists('wp_feature_fetch_rota_for_current_user')) {
function wp_feature_fetch_rota_for_current_user(?string $fromDate = null, int $limit = 20): array {
global $pdo;
if (!isset($pdo) || !($pdo instanceof PDO)) {
return [];
}
if (!wp_feature_ensure_rota_schema() || !wp_feature_table_exists('rota_entries')) {
return [];
}
$fromDate = $fromDate ?: date('Y-m-d');
$ids = wp_feature_current_user_identifiers();
if (!$ids) {
return [];
}
$userIds = [];
$aliases = [];
$aliasNorms = [];
$aliasTokenKeys = [];
foreach ($ids as $row) {
if (($row['type'] ?? '') === 'id') {
$userIds[] = (int)$row['value'];
continue;
}
$value = trim((string)($row['value'] ?? ''));
if ($value === '') continue;
$aliases[] = mb_strtolower($value);
$norm = wp_feature_normalize_person_key($value);
if ($norm !== '') {
$aliasNorms[] = $norm;
}
$tokenKey = wp_feature_person_token_key($value);
if ($tokenKey !== '') {
$aliasTokenKeys[] = $tokenKey;
}
}
$userIds = array_values(array_unique(array_filter($userIds)));
$aliases = array_values(array_unique(array_filter($aliases)));
$aliasNorms = array_values(array_unique(array_filter($aliasNorms)));
$aliasTokenKeys = array_values(array_unique(array_filter($aliasTokenKeys)));
try {
$stmt = $pdo->prepare("SELECT * FROM rota_entries WHERE shift_date >= ? ORDER BY shift_date ASC, id ASC LIMIT 500");
$stmt->execute([$fromDate]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
return [];
}
$matches = [];
foreach ($rows as $row) {
$rowUserId = isset($row['staff_user_id']) && $row['staff_user_id'] !== null ? (int)$row['staff_user_id'] : 0;
$rowNameRaw = (string)($row['staff_name'] ?? '');
$isMatch = false;
if ($rowUserId > 0 && in_array($rowUserId, $userIds, true)) {
$isMatch = true;
}
if (!$isMatch && wp_feature_person_matches($rowNameRaw, $aliases, $aliasNorms, $aliasTokenKeys)) {
$isMatch = true;
if ($rowUserId <= 0) {
$resolvedUserId = wp_feature_find_user_id($rowNameRaw);
if ($resolvedUserId) {
try {
$up = $pdo->prepare('UPDATE rota_entries SET staff_user_id = ? WHERE id = ? AND (staff_user_id IS NULL OR staff_user_id = 0)');
$up->execute([$resolvedUserId, (int)$row['id']]);
$row['staff_user_id'] = $resolvedUserId;
} catch (Throwable $e) {}
}
}
}
if ($isMatch) {
$matches[] = $row;
if (count($matches) >= max(1, (int)$limit)) {
break;
}
}
}
return $matches;
}
}
if (!function_exists('wp_feature_fetch_contacts')) {
function wp_feature_fetch_contacts(int $limit = 100): array {
global $pdo;
if (!isset($pdo) || !($pdo instanceof PDO) || !wp_feature_table_exists('contacts')) {
return [];
}
try {
$stmt = $pdo->query('SELECT * FROM contacts ORDER BY name ASC LIMIT ' . max(1, (int)$limit));
return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
return [];
}
}
}
if (!function_exists('wp_feature_fetch_user_alerts')) {
function wp_feature_fetch_user_alerts(int $limit = 8): array {
global $pdo;
if (!isset($pdo) || !($pdo instanceof PDO) || !wp_feature_table_exists('alerts')) {
return ['unread' => 0, 'items' => []];
}
$userRole = strtolower((string)($_SESSION['user_role'] ?? ''));
$userName = strtolower(wp_feature_current_user_name());
$userId = wp_feature_current_user_id();
$reads = [];
try {
if (wp_feature_table_exists('alert_reads')) {
$stmt = $pdo->prepare('SELECT alert_id FROM alert_reads WHERE user_id = ? OR username = ?');
$stmt->execute([$userId, wp_feature_current_user_name()]);
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $rid) {
$reads[(int)$rid] = true;
}
}
} catch (Throwable $e) {}
$items = [];
$unread = 0;
try {
$rows = $pdo->query('SELECT * FROM alerts ORDER BY sent_at DESC LIMIT 100')->fetchAll(PDO::FETCH_ASSOC) ?: [];
foreach ($rows as $a) {
$expires = $a['expires_at'] ?? null;
if ($expires && strtotime((string)$expires) < time()) continue;
$targetType = strtolower((string)($a['target_type'] ?? 'all'));
$targetValue = strtolower((string)($a['target_value'] ?? ''));
$forMe = $targetType === 'all'
|| ($targetType === 'role' && $targetValue !== '' && $targetValue === $userRole)
|| ($targetType === 'user' && $targetValue !== '' && $targetValue === $userName);
if (!$forMe) continue;
$a['is_read'] = !empty($reads[(int)$a['id']]);
if (!$a['is_read']) $unread++;
$items[] = $a;
if (count($items) >= $limit) break;
}
} catch (Throwable $e) {}
return ['unread' => $unread, 'items' => $items];
}
}
if (!function_exists('wp_feature_mark_alert_read')) {
function wp_feature_mark_alert_read(int $alertId): void {
global $pdo;
if (!isset($pdo) || !($pdo instanceof PDO) || $alertId <= 0 || !wp_feature_table_exists('alert_reads')) {
return;
}
try {
$stmt = $pdo->prepare('INSERT IGNORE INTO alert_reads (alert_id, user_id, username, read_at) VALUES (?, ?, ?, NOW())');
$stmt->execute([$alertId, wp_feature_current_user_id(), wp_feature_current_user_name()]);
} catch (Throwable $e) {}
}
}
if (!function_exists('wp_feature_dashboard_metrics')) {
function wp_feature_dashboard_metrics(): array {
global $pdo;
$metrics = [
'rota_today' => 0,
'holiday_pending' => 0,
'holiday_mine' => 0,
'incidents_open' => 0,
'contacts_total' => 0,
'alerts_active' => 0,
'alerts_unread' => 0,
];
$ids = wp_feature_current_user_identifiers();
$idValues = [];
$textValues = [];
$textNorms = [];
foreach ($ids as $row) {
if ($row['type'] === 'id') {
$idValues[] = (int)$row['value'];
} else {
$textValues[] = (string)$row['value'];
$norm = wp_feature_normalize_person_key((string)$row['value']);
if ($norm !== '') $textNorms[] = $norm;
}
}
$textNorms = array_values(array_unique($textNorms));
try {
if (wp_feature_ensure_rota_schema() && wp_feature_table_exists('rota_entries')) {
$where = [];
$params = [];
foreach ($idValues as $v) { $where[] = 'staff_user_id = ?'; $params[] = $v; }
foreach ($textValues as $v) { $where[] = 'LOWER(TRIM(staff_name)) = ?'; $params[] = $v; }
foreach ($textNorms as $v) { $where[] = 'LOWER(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(staff_name), " ", ""), "-", ""), ".", ""), ",", "")) = ?'; $params[] = $v; }
if ($where) {
$stmt = $pdo->prepare('SELECT COUNT(*) FROM rota_entries WHERE shift_date = CURDATE() AND (' . implode(' OR ', $where) . ')');
$stmt->execute($params);
$metrics['rota_today'] = (int)$stmt->fetchColumn();
}
}
} catch (Throwable $e) {}
try {
if (wp_feature_table_exists('holiday_requests')) {
$metrics['holiday_pending'] = (int)$pdo->query("SELECT COUNT(*) FROM holiday_requests WHERE status = 'Pending'")->fetchColumn();
$where = [];
$params = [];
foreach ($idValues as $v) { $where[] = 'user_id = ?'; $params[] = $v; }
foreach ($textValues as $v) { $where[] = 'LOWER(TRIM(username)) = ?'; $params[] = $v; }
if ($where) {
$stmt = $pdo->prepare("SELECT COUNT(*) FROM holiday_requests WHERE (" . implode(' OR ', $where) . ") AND status IN ('Pending','Approved')");
$stmt->execute($params);
$metrics['holiday_mine'] = (int)$stmt->fetchColumn();
}
}
} catch (Throwable $e) {}
try {
if (wp_feature_table_exists('incidents')) {
$metrics['incidents_open'] = (int)$pdo->query("SELECT COUNT(*) FROM incidents WHERE status IN ('Open','Investigating')")->fetchColumn();
}
} catch (Throwable $e) {}
try {
if (wp_feature_table_exists('contacts')) {
$metrics['contacts_total'] = (int)$pdo->query('SELECT COUNT(*) FROM contacts')->fetchColumn();
}
} catch (Throwable $e) {}
try {
if (wp_feature_table_exists('alerts')) {
$alerts = wp_feature_fetch_user_alerts(50);
$metrics['alerts_active'] = count($alerts['items']);
$metrics['alerts_unread'] = (int)$alerts['unread'];
}
} catch (Throwable $e) {}
return $metrics;
}
}