272 lines
13 KiB
PHP
272 lines
13 KiB
PHP
<?php
|
|
/**
|
|
* admin/index.php
|
|
*
|
|
* Corrige suite a l'audit du 20/09/2026 : les popups Leaflet
|
|
* n'injectent plus de HTML construit par concatenation de chaines a
|
|
* partir de donnees issues de la base (operateur, pays, description,
|
|
* technos). Ces valeurs peuvent provenir d'imports .ntm ou de saisies
|
|
* manuelles ; sans cette correction, un contenu malveillant stocke en
|
|
* base (ex: location_desc contenant une balise <img onerror=...>)
|
|
* s'executait comme JavaScript dans le navigateur de l'administrateur
|
|
* connecte, permettant de voler le jeton CSRF present dans le DOM et
|
|
* d'agir a sa place.
|
|
*
|
|
* Les popups sont maintenant construites via des elements DOM avec
|
|
* textContent, qui n'interprete jamais son contenu comme HTML/JS.
|
|
*/
|
|
declare(strict_types=1);
|
|
|
|
require_once __DIR__ . '/admin_auth.php';
|
|
require_once __DIR__ . '/../lib/db.php';
|
|
require_once __DIR__ . '/_nav.php';
|
|
|
|
require_admin_auth();
|
|
$db = pdo();
|
|
$user = current_admin_username();
|
|
|
|
$countryFilter = $_GET['country'] ?? '';
|
|
$operatorFilter = $_GET['operator'] ?? '';
|
|
|
|
$totalSites = (int)$db->query('SELECT COUNT(*) FROM sites')->fetchColumn();
|
|
$totalCells = (int)$db->query('SELECT COUNT(*) FROM cells')->fetchColumn();
|
|
$totalPending = (int)$db->query('SELECT COUNT(*) FROM staging_cells WHERE status = "pending"')->fetchColumn();
|
|
$totalCountries = (int)$db->query('SELECT COUNT(*) FROM countries')->fetchColumn();
|
|
|
|
$byOperator = $db->query('
|
|
SELECT o.brand, o.mcc, o.mnc, c.name AS country_name, COUNT(DISTINCT s.id) AS site_count, COUNT(cl.id) AS cell_count
|
|
FROM operators o
|
|
LEFT JOIN countries c ON c.id = o.country_id
|
|
LEFT JOIN sites s ON s.operator_id = o.id
|
|
LEFT JOIN cells cl ON cl.site_id = s.id
|
|
GROUP BY o.id
|
|
ORDER BY c.name, o.brand
|
|
')->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$byTech = $db->query('
|
|
SELECT tech, COUNT(*) AS cnt FROM cells GROUP BY tech ORDER BY FIELD(tech, "2G","3G","4G","5G","CDMA")
|
|
')->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$recentActivity = $db->query('
|
|
SELECT s.id, s.lat, s.lon, s.location_desc, s.created_at, s.created_by, o.brand AS operator_brand, c.name AS country_name
|
|
FROM sites s
|
|
JOIN operators o ON o.id = s.operator_id
|
|
JOIN countries c ON c.id = s.country_id
|
|
ORDER BY s.created_at DESC
|
|
LIMIT 8
|
|
')->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$sql = '
|
|
SELECT s.id AS site_id, s.lat, s.lon, s.location_desc,
|
|
o.brand AS operator_brand, c.name AS country_name, c.iso2,
|
|
GROUP_CONCAT(DISTINCT cl.tech ORDER BY cl.tech SEPARATOR ", ") AS techs,
|
|
COUNT(cl.id) AS cell_count
|
|
FROM sites s
|
|
JOIN operators o ON o.id = s.operator_id
|
|
JOIN countries c ON c.id = s.country_id
|
|
LEFT JOIN cells cl ON cl.site_id = s.id
|
|
WHERE 1=1
|
|
';
|
|
$params = [];
|
|
if ($countryFilter !== '') { $sql .= ' AND c.iso2 = ?'; $params[] = $countryFilter; }
|
|
if ($operatorFilter !== '') { $sql .= ' AND o.id = ?'; $params[] = $operatorFilter; }
|
|
$sql .= ' GROUP BY s.id ORDER BY s.created_at DESC';
|
|
|
|
$stmt = $db->prepare($sql);
|
|
$stmt->execute($params);
|
|
$sites = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$operators = $db->query('SELECT id, mcc, mnc, brand FROM operators ORDER BY brand')->fetchAll(PDO::FETCH_ASSOC);
|
|
$countries = $db->query('SELECT iso2, name, default_lat, default_lon, default_zoom FROM countries ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$activeCountry = null;
|
|
if ($countryFilter !== '') {
|
|
foreach ($countries as $c) { if ($c['iso2'] === $countryFilter) { $activeCountry = $c; break; } }
|
|
}
|
|
if (!$activeCountry && !empty($countries)) {
|
|
$activeCountry = $countries[0];
|
|
}
|
|
if (!$activeCountry) {
|
|
$activeCountry = ['default_lat' => 20, 'default_lon' => 0, 'default_zoom' => 2];
|
|
}
|
|
|
|
$operatorColors = ['POST' => '#0057A0', 'Tango' => '#E30613', 'Orange' => '#FF7900', 'LoL' => '#2E7D32'];
|
|
$mapSites = array_map(fn($s) => [
|
|
'lat' => (float)$s['lat'], 'lon' => (float)$s['lon'], 'operator' => $s['operator_brand'],
|
|
'techs' => $s['techs'], 'cell_count' => (int)$s['cell_count'], 'desc' => $s['location_desc'], 'country' => $s['country_name'],
|
|
], $sites);
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="fr">
|
|
<head>
|
|
<meta charset="UTF-8"><title>NetMonster Admin - Accueil</title>
|
|
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css" crossorigin="" />
|
|
<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js" crossorigin=""></script>
|
|
<script src="https://unpkg.com/leaflet.markercluster@1.5.3/dist/leaflet.markercluster.js"></script>
|
|
<link rel="stylesheet" href="https://unpkg.com/leaflet.markercluster@1.5.3/dist/MarkerCluster.css" />
|
|
<link rel="stylesheet" href="https://unpkg.com/leaflet.markercluster@1.5.3/dist/MarkerCluster.Default.css" />
|
|
<link rel="stylesheet" href="assets/theme.css">
|
|
<style>
|
|
.kpi-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 1rem; margin-bottom: 1.4rem; }
|
|
.kpi-card { background: var(--surface); border: 1px solid var(--border); border-radius: var(--radius); padding: 1.1rem 1.3rem; box-shadow: var(--shadow); }
|
|
.kpi-card .kpi-value { font-size: 1.8rem; font-weight: 700; color: var(--text); }
|
|
.kpi-card .kpi-label { font-size: .82rem; color: var(--text-muted); margin-top: .2rem; }
|
|
.two-col { display: grid; grid-template-columns: 2fr 1fr; gap: 1.4rem; align-items: start; }
|
|
.legend-row { display: flex; gap: 1rem; flex-wrap: wrap; margin-bottom: .8rem; font-size: .82rem; color: var(--text-muted); }
|
|
.legend-row .dot-legend { width: 10px; height: 10px; border-radius: 50%; display: inline-block; margin-right: .3rem; }
|
|
.activity-item { padding: .6rem 0; border-bottom: 1px solid var(--border); font-size: .85rem; }
|
|
.activity-item:last-child { border-bottom: none; }
|
|
.activity-item .meta { color: var(--text-muted); font-size: .76rem; }
|
|
.popup-line { margin: 0; }
|
|
.popup-title { font-weight: 700; }
|
|
@media (max-width: 900px) { .kpi-grid { grid-template-columns: repeat(2, 1fr); } .two-col { grid-template-columns: 1fr; } }
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<?php render_admin_nav('index.php', $user); ?>
|
|
|
|
<div class="page-wrap">
|
|
<h1>Recapitulatif des antennes</h1>
|
|
<p class="page-subtitle">Vue d'ensemble de la base validee, tous pays et operateurs confondus.</p>
|
|
|
|
<div class="kpi-grid">
|
|
<div class="kpi-card"><div class="kpi-value"><?= $totalSites ?></div><div class="kpi-label">Sites valides</div></div>
|
|
<div class="kpi-card"><div class="kpi-value"><?= $totalCells ?></div><div class="kpi-label">Cellules validees</div></div>
|
|
<div class="kpi-card"><div class="kpi-value"><?= $totalPending ?></div><div class="kpi-label">En attente de validation</div></div>
|
|
<div class="kpi-card"><div class="kpi-value"><?= $totalCountries ?></div><div class="kpi-label">Pays configures</div></div>
|
|
</div>
|
|
|
|
<div class="two-col">
|
|
<div>
|
|
<div class="card">
|
|
<h2>Carte des antennes validees</h2>
|
|
<form class="filters" method="get">
|
|
<select name="country" onchange="this.form.submit()">
|
|
<option value="">Tous les pays</option>
|
|
<?php foreach ($countries as $c): ?>
|
|
<option value="<?= htmlspecialchars($c['iso2'], ENT_QUOTES, 'UTF-8') ?>" <?= $countryFilter === $c['iso2'] ? 'selected' : '' ?>><?= htmlspecialchars($c['name'], ENT_QUOTES, 'UTF-8') ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
<select name="operator" onchange="this.form.submit()">
|
|
<option value="">Tous les operateurs</option>
|
|
<?php foreach ($operators as $o): ?>
|
|
<option value="<?= (int)$o['id'] ?>" <?= (string)$operatorFilter === (string)$o['id'] ? 'selected' : '' ?>><?= htmlspecialchars($o['brand'], ENT_QUOTES, 'UTF-8') ?> (<?= htmlspecialchars($o['mcc'].'-'.$o['mnc'], ENT_QUOTES, 'UTF-8') ?>)</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</form>
|
|
<div class="legend-row">
|
|
<?php foreach ($operatorColors as $brand => $color): ?>
|
|
<span><span class="dot-legend" style="background:<?= htmlspecialchars($color, ENT_QUOTES, 'UTF-8') ?>"></span><?= htmlspecialchars($brand, ENT_QUOTES, 'UTF-8') ?></span>
|
|
<?php endforeach; ?>
|
|
<span><span class="dot-legend" style="background:#666"></span>Autre</span>
|
|
</div>
|
|
<div id="map"></div>
|
|
<p class="page-subtitle" style="margin:0;"><?= count($sites) ?> site(s) affiche(s) sur la carte.</p>
|
|
</div>
|
|
</div>
|
|
|
|
<div>
|
|
<div class="card">
|
|
<h2>Par operateur</h2>
|
|
<table class="data-table">
|
|
<tr><th>Operateur</th><th>Pays</th><th>Sites</th><th>Cellules</th></tr>
|
|
<?php foreach ($byOperator as $o): ?>
|
|
<tr>
|
|
<td><?= htmlspecialchars($o['brand'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= htmlspecialchars($o['country_name'] ?? '?', ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= (int)$o['site_count'] ?></td>
|
|
<td><?= (int)$o['cell_count'] ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</table>
|
|
</div>
|
|
|
|
<div class="card">
|
|
<h2>Par technologie</h2>
|
|
<table class="data-table">
|
|
<tr><th>Techno</th><th>Cellules</th></tr>
|
|
<?php foreach ($byTech as $t): ?>
|
|
<tr><td><?= htmlspecialchars($t['tech'], ENT_QUOTES, 'UTF-8') ?></td><td><?= (int)$t['cnt'] ?></td></tr>
|
|
<?php endforeach; ?>
|
|
<?php if (empty($byTech)): ?><tr><td colspan="2" class="no-cadastre">Aucune cellule validee pour le moment.</td></tr><?php endif; ?>
|
|
</table>
|
|
</div>
|
|
|
|
<div class="card">
|
|
<h2>Activite recente</h2>
|
|
<?php if (empty($recentActivity)): ?>
|
|
<p class="no-cadastre">Aucun site valide pour le moment.</p>
|
|
<?php endif; ?>
|
|
<?php foreach ($recentActivity as $a): ?>
|
|
<div class="activity-item">
|
|
<strong><?= htmlspecialchars($a['operator_brand'], ENT_QUOTES, 'UTF-8') ?></strong> · <?= htmlspecialchars($a['country_name'], ENT_QUOTES, 'UTF-8') ?><br>
|
|
<?= htmlspecialchars($a['location_desc'] ?: ($a['lat'] . ', ' . $a['lon']), ENT_QUOTES, 'UTF-8') ?>
|
|
<div class="meta">Ajoute par <?= htmlspecialchars($a['created_by'] ?? '?', ENT_QUOTES, 'UTF-8') ?> le <?= htmlspecialchars(date('d/m/Y H:i', strtotime($a['created_at'])), ENT_QUOTES, 'UTF-8') ?></div>
|
|
</div>
|
|
<?php endforeach; ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
const map = L.map('map').setView([<?= (float)$activeCountry['default_lat'] ?>, <?= (float)$activeCountry['default_lon'] ?>], <?= (int)$activeCountry['default_zoom'] ?>);
|
|
|
|
L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', {
|
|
maxZoom: 19,
|
|
attribution: '© <a href="https://www.openstreetmap.org/copyright" target="_blank">OpenStreetMap</a> contributors'
|
|
}).addTo(map);
|
|
|
|
const operatorColors = <?= json_encode($operatorColors, JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT) ?>;
|
|
const sites = <?= json_encode($mapSites, JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT) ?>;
|
|
const clusterGroup = L.markerClusterGroup();
|
|
|
|
/**
|
|
* Construit le contenu d'une popup via des elements DOM avec
|
|
* textContent (jamais innerHTML/template literal), afin qu'un
|
|
* contenu malveillant stocke en base (nom d'operateur, description,
|
|
* technos) ne puisse jamais s'executer comme HTML/JavaScript.
|
|
*/
|
|
function buildSitePopup(site) {
|
|
const container = document.createElement('div');
|
|
|
|
const title = document.createElement('div');
|
|
title.className = 'popup-title';
|
|
title.textContent = `${site.operator ?? ''} - ${site.country ?? ''}`;
|
|
container.appendChild(title);
|
|
|
|
const technos = document.createElement('div');
|
|
technos.className = 'popup-line';
|
|
technos.textContent = `Technos : ${site.techs ?? '-'}`;
|
|
container.appendChild(technos);
|
|
|
|
const cellCount = document.createElement('div');
|
|
cellCount.className = 'popup-line';
|
|
cellCount.textContent = `${site.cell_count} cellule(s)`;
|
|
container.appendChild(cellCount);
|
|
|
|
if (site.desc) {
|
|
const desc = document.createElement('div');
|
|
desc.className = 'popup-line';
|
|
desc.textContent = site.desc;
|
|
container.appendChild(desc);
|
|
}
|
|
|
|
return container;
|
|
}
|
|
|
|
sites.forEach(s => {
|
|
const color = operatorColors[s.operator] || '#666';
|
|
const marker = L.circleMarker([s.lat, s.lon], { radius: 8, color: color, fillColor: color, fillOpacity: 0.8, weight: 2 })
|
|
.bindPopup(buildSitePopup(s));
|
|
clusterGroup.addLayer(marker);
|
|
});
|
|
|
|
map.addLayer(clusterGroup);
|
|
|
|
if (sites.length > 0) {
|
|
map.fitBounds(L.latLngBounds(sites.map(s => [s.lat, s.lon])), { padding: [30, 30] });
|
|
}
|
|
</script>
|
|
</body>
|
|
</html>
|