PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); function h($s): string { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); } // ---- Council config (name + source URL) ---- $councilConfig = []; $cfgFile = __DIR__ . '/config/councils.json'; if (is_readable($cfgFile)) { $decoded = json_decode(file_get_contents($cfgFile), true); if (is_array($decoded)) $councilConfig = $decoded; } function council_name_from_key(string $k): string { global $councilConfig; if (!empty($councilConfig[$k]['name'])) return $councilConfig[$k]['name']; $base = preg_replace('/^da_/', '', $k); $base = str_replace('_', ' ', $base); $map = [ 'flinders_council' => 'Flinders', 'southernmidlands' => 'Southern Midlands', ]; if (isset($map[$base])) return $map[$base]; return ucwords($base); } function council_source_url(string $k): string { global $councilConfig; return $councilConfig[$k]['source_url'] ?? ''; } function tableExists(PDO $pdo, string $table): bool { $st = $pdo->prepare("SHOW TABLES LIKE ?"); $st->execute([$table]); return (bool)$st->fetch(PDO::FETCH_NUM); } function tableHasColumn(PDO $pdo, string $table, string $col): bool { $q = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?"); $q->execute([$col]); return (bool)$q->fetch(); } function columnMeta(PDO $pdo, string $table, string $col): ?array { $q = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?"); $q->execute([$col]); $r = $q->fetch(); return $r ?: null; } function firstExistingCol(PDO $pdo, string $table, array $candidates): ?string { foreach ($candidates as $c) { if (tableHasColumn($pdo, $table, $c)) return $c; } return null; } function fmt_dt(?string $dt): string { if (!$dt) return ''; try { $d = new DateTime($dt); return $d->format('Y-m-d H:i'); } catch (Throwable $e) { return (string)$dt; } } function rel_age(?string $dt): string { if (!$dt) return ''; try { $d = new DateTime($dt); $now = new DateTime('now'); $diff = $now->getTimestamp() - $d->getTimestamp(); if ($diff < 0) return 'in future'; $mins = (int)floor($diff / 60); if ($mins < 60) return $mins . ' min'; $hrs = (int)floor($mins / 60); if ($hrs < 48) return $hrs . ' hr'; $days = (int)floor($hrs / 24); return $days . ' d'; } catch (Throwable $e) { return ''; } } // ---- Discover da_* tables ---- $allTables = []; $st = $pdo->query("SHOW TABLES"); while ($row = $st->fetch(PDO::FETCH_NUM)) { $t = $row[0]; if (strpos($t, 'da_') === 0) $allTables[] = $t; } // Exclude tables you don't want $exclude = ['geo_cache', 'da_plandata', 'da_plan_data', 'da_dorset_stages']; $allTables = array_values(array_filter($allTables, fn($t) => !in_array($t, $exclude, true))); if (!$allTables) { http_response_code(200); echo "

No da_* tables found

"; exit; } sort($allTables); // Optional sort controls $sort = (string)($_GET['sort'] ?? 'name'); // name|last|count|open $dir = strtolower((string)($_GET['dir'] ?? 'asc')) === 'desc' ? 'desc' : 'asc'; // Optional external scrape log table support // If you have a runs table already, this will attempt to use it. // Supported names: scrape_runs, scrape_log, runs $runsTable = null; foreach (['scrape_runs','scrape_log','runs'] as $cand) { if (tableExists($pdo, $cand)) { $runsTable = $cand; break; } } $runsCouncilCol = $runsTable ? firstExistingCol($pdo, $runsTable, ['council_key','council','table_name','source']) : null; $runsStatusCol = $runsTable ? firstExistingCol($pdo, $runsTable, ['status','state','result','ok']) : null; $runsTimeCol = $runsTable ? firstExistingCol($pdo, $runsTable, ['finished_at','ended_at','completed_at','run_finished_at','created_at','started_at','run_at','timestamp']) : null; $summary = []; foreach ($allTables as $t) { // counts $total = (int)$pdo->query("SELECT COUNT(*) AS c FROM `{$t}`")->fetch()['c']; $open = null; if (tableHasColumn($pdo, $t, 'on_notice_to')) { $open = (int)$pdo->query("SELECT COUNT(*) AS c FROM `{$t}` WHERE (`on_notice_to` IS NULL OR `on_notice_to` >= CURDATE())")->fetch()['c']; } // last successful scrape $lastScrape = null; // 1) Try a runs/log table (if present) if ($runsTable && $runsCouncilCol && $runsTimeCol) { $where = "WHERE `{$runsCouncilCol}` = ?"; $params = [$t]; if ($runsStatusCol) { $statusMeta = columnMeta($pdo, $runsTable, $runsStatusCol); $type = strtolower((string)($statusMeta['Type'] ?? '')); if (strpos($type, 'tinyint') !== false || strpos($type, 'int') !== false || $runsStatusCol === 'ok') { $where .= " AND `{$runsStatusCol}` = 1"; } else { $where .= " AND `{$runsStatusCol}` IN ('ok','OK','success','successful','done','completed')"; } } $sql = "SELECT `{$runsTimeCol}` AS ts FROM `{$runsTable}` {$where} ORDER BY `{$runsTimeCol}` DESC LIMIT 1"; $q = $pdo->prepare($sql); $q->execute($params); $r = $q->fetch(); if ($r && $r['ts']) $lastScrape = (string)$r['ts']; } // 2) Fallback to per-table timestamp columns if (!$lastScrape) { $tsCol = firstExistingCol($pdo, $t, [ 'dev_timestamp', 'scraped_at', 'scrape_ts', 'last_scraped_at', 'fetched_at', 'updated_at', 'created_at', 'inserted_at', ]); if ($tsCol) { $meta = columnMeta($pdo, $t, $tsCol); $type = strtolower((string)($meta['Type'] ?? '')); if (strpos($type, 'int') !== false) { $r = $pdo->query("SELECT FROM_UNIXTIME(MAX(`{$tsCol}`)) AS ts FROM `{$t}`")->fetch(); if ($r && $r['ts']) $lastScrape = (string)$r['ts']; } else { $r = $pdo->query("SELECT MAX(`{$tsCol}`) AS ts FROM `{$t}`")->fetch(); if ($r && $r['ts']) $lastScrape = (string)$r['ts']; } } } // 3) Final fallback: latest date_received if (!$lastScrape && tableHasColumn($pdo, $t, 'date_received')) { $r = $pdo->query("SELECT MAX(`date_received`) AS ts FROM `{$t}`")->fetch(); if ($r && $r['ts']) $lastScrape = (string)$r['ts']; } // classification stats (only if column exists) $classified = null; $withPdf = null; $typeBreakdown = []; if (tableHasColumn($pdo, $t, 'application_type')) { $row = $pdo->query("SELECT COUNT(local_document_url) AS with_pdf, COUNT(application_type) AS classified FROM `{$t}`")->fetch(); $withPdf = (int)$row['with_pdf']; $classified = (int)$row['classified']; $bt = $pdo->query("SELECT application_type, COUNT(*) AS n FROM `{$t}` WHERE application_type IS NOT NULL GROUP BY application_type ORDER BY n DESC"); while ($br = $bt->fetch()) { $typeBreakdown[(string)$br['application_type']] = (int)$br['n']; } } $summary[] = [ 'council_key' => $t, 'council' => council_name_from_key($t), 'total' => $total, 'open' => $open, 'last_scrape' => $lastScrape, 'classified' => $classified, 'with_pdf' => $withPdf, 'type_breakdown' => $typeBreakdown, ]; } // Sorting in PHP (keeps it simple and avoids dynamic SQL) $cmp = function(array $a, array $b) use ($sort, $dir): int { $mul = $dir === 'desc' ? -1 : 1; if ($sort === 'count') { return $mul * (($a['total'] <=> $b['total']) ?: strcasecmp($a['council'], $b['council'])); } if ($sort === 'open') { $ao = $a['open'] ?? -1; $bo = $b['open'] ?? -1; return $mul * (($ao <=> $bo) ?: strcasecmp($a['council'], $b['council'])); } if ($sort === 'last') { $at = $a['last_scrape'] ? strtotime($a['last_scrape']) : 0; $bt = $b['last_scrape'] ? strtotime($b['last_scrape']) : 0; return $mul * (($at <=> $bt) ?: strcasecmp($a['council'], $b['council'])); } if ($sort === 'classified') { $ac = ($a['with_pdf'] > 0) ? ($a['classified'] / $a['with_pdf']) : -1; $bc = ($b['with_pdf'] > 0) ? ($b['classified'] / $b['with_pdf']) : -1; return $mul * (($ac <=> $bc) ?: strcasecmp($a['council'], $b['council'])); } return $mul * strcasecmp($a['council'], $b['council']); }; usort($summary, $cmp); ?> Council scrape status

Council scrape status

Open applications

Council	Table	Total	Open	Last successful scrape	Classification
↗			n/a' : number_format((int)$r['open']) ?>	( ago) unknown	— no PDFs / $n): ?>	View

Notes: “Last successful scrape” is taken from a scrape log table if present (scrape_runs, scrape_log, runs). Otherwise it uses the best available per-row timestamp column, otherwise date_received.