454 lines
18 KiB
PHP
454 lines
18 KiB
PHP
<?php
|
|
/**
|
|
* admin/search_site.php
|
|
*
|
|
* Recherche d'une antenne par eNB/RNC, Cell ID, bande, frequence ou adresse.
|
|
* Affiche les cellules regroupees par site et les azimuts sur une carte Leaflet.
|
|
*
|
|
* Corrige suite a l'audit du 20/09/2026 : les popups Leaflet (marqueurs
|
|
* de site et secteurs d'azimut) construisaient du HTML par
|
|
* concatenation de template literals a partir de donnees issues de la
|
|
* base (operateur, pays, adresse, bande, frequence...). Un contenu
|
|
* malveillant stocke via import .ntm ou saisie manuelle pouvait donc
|
|
* s'executer comme JavaScript dans le navigateur de l'administrateur.
|
|
* Les popups utilisent maintenant des elements DOM avec textContent.
|
|
*/
|
|
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();
|
|
|
|
$query = trim((string)($_GET['q'] ?? ''));
|
|
$countryFilter = trim((string)($_GET['country'] ?? ''));
|
|
$operatorFilter = trim((string)($_GET['operator'] ?? ''));
|
|
$techFilter = trim((string)($_GET['tech'] ?? ''));
|
|
$selectedSiteId = (int)($_GET['site_id'] ?? 0);
|
|
|
|
$countries = $db->query('SELECT id, iso2, name FROM countries ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);
|
|
$operators = $db->query('SELECT id, mcc, mnc, brand FROM operators ORDER BY brand')->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$sites = [];
|
|
$selectedSite = null;
|
|
$selectedCells = [];
|
|
|
|
if ($query !== '' || $countryFilter !== '' || $operatorFilter !== '' || $techFilter !== '' || $selectedSiteId > 0) {
|
|
$sql = '
|
|
SELECT
|
|
s.id AS site_id,
|
|
s.lat,
|
|
s.lon,
|
|
s.location_desc,
|
|
s.address_desc,
|
|
s.country_id,
|
|
s.operator_id,
|
|
o.brand AS operator_brand,
|
|
o.mcc AS operator_mcc,
|
|
o.mnc AS operator_mnc,
|
|
c.name AS country_name,
|
|
cl.id AS cell_pk,
|
|
cl.mcc,
|
|
cl.mnc,
|
|
cl.tech,
|
|
cl.cell_id,
|
|
cl.area_code,
|
|
cl.sub_id,
|
|
cl.sub_id2,
|
|
cl.band,
|
|
cl.frequency,
|
|
cl.azimuth,
|
|
cl.bandwidth,
|
|
cl.arfcn
|
|
FROM sites s
|
|
JOIN operators o ON o.id = s.operator_id
|
|
JOIN countries c ON c.id = s.country_id
|
|
JOIN cells cl ON cl.site_id = s.id
|
|
WHERE 1=1
|
|
';
|
|
$params = [];
|
|
|
|
if ($query !== '') {
|
|
$sql .= '
|
|
AND (
|
|
cl.sub_id2 LIKE ?
|
|
OR cl.cell_id LIKE ?
|
|
OR cl.sub_id LIKE ?
|
|
OR cl.band LIKE ?
|
|
OR cl.frequency LIKE ?
|
|
OR s.location_desc LIKE ?
|
|
OR s.address_desc LIKE ?
|
|
)
|
|
';
|
|
$like = '%' . $query . '%';
|
|
array_push($params, $like, $like, $like, $like, $like, $like, $like);
|
|
}
|
|
|
|
if ($countryFilter !== '') {
|
|
$sql .= ' AND c.iso2 = ?';
|
|
$params[] = $countryFilter;
|
|
}
|
|
|
|
if ($operatorFilter !== '') {
|
|
$sql .= ' AND o.id = ?';
|
|
$params[] = (int)$operatorFilter;
|
|
}
|
|
|
|
if ($techFilter !== '') {
|
|
$sql .= ' AND cl.tech = ?';
|
|
$params[] = $techFilter;
|
|
}
|
|
|
|
if ($selectedSiteId > 0) {
|
|
$sql .= ' AND s.id = ?';
|
|
$params[] = $selectedSiteId;
|
|
}
|
|
|
|
$sql .= ' ORDER BY s.id, cl.sub_id2, cl.tech, cl.band, cl.frequency, cl.id LIMIT 1000';
|
|
|
|
$stmt = $db->prepare($sql);
|
|
$stmt->execute($params);
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
foreach ($rows as $row) {
|
|
$siteId = (int)$row['site_id'];
|
|
|
|
if (!isset($sites[$siteId])) {
|
|
$sites[$siteId] = [
|
|
'site_id' => $siteId,
|
|
'lat' => (float)$row['lat'],
|
|
'lon' => (float)$row['lon'],
|
|
'location_desc' => (string)($row['location_desc'] ?? ''),
|
|
'address_desc' => (string)($row['address_desc'] ?? ''),
|
|
'operator_brand' => (string)$row['operator_brand'],
|
|
'country_name' => (string)$row['country_name'],
|
|
'cell_count' => 0,
|
|
'cells' => [],
|
|
];
|
|
}
|
|
|
|
$cell = [
|
|
'cell_pk' => (int)$row['cell_pk'],
|
|
'mcc' => (string)($row['mcc'] ?? ''),
|
|
'mnc' => (string)($row['mnc'] ?? ''),
|
|
'tech' => (string)($row['tech'] ?? ''),
|
|
'cell_id' => (string)($row['cell_id'] ?? ''),
|
|
'area_code' => (string)($row['area_code'] ?? ''),
|
|
'sub_id' => (string)($row['sub_id'] ?? ''),
|
|
'sub_id2' => (string)($row['sub_id2'] ?? ''),
|
|
'band' => (string)($row['band'] ?? ''),
|
|
'frequency' => (string)($row['frequency'] ?? ''),
|
|
'azimuth' => $row['azimuth'] !== null && $row['azimuth'] !== '' ? (float)$row['azimuth'] : null,
|
|
'bandwidth' => (string)($row['bandwidth'] ?? ''),
|
|
'arfcn' => (string)($row['arfcn'] ?? ''),
|
|
];
|
|
|
|
$sites[$siteId]['cells'][] = $cell;
|
|
$sites[$siteId]['cell_count']++;
|
|
}
|
|
|
|
$sites = array_values($sites);
|
|
|
|
if ($selectedSiteId > 0 && isset($sites[0]) && (int)$sites[0]['site_id'] === $selectedSiteId) {
|
|
$selectedSite = $sites[0];
|
|
$selectedCells = $selectedSite['cells'];
|
|
} elseif (count($sites) === 1) {
|
|
$selectedSite = $sites[0];
|
|
$selectedCells = $selectedSite['cells'];
|
|
}
|
|
}
|
|
|
|
function h(mixed $value): string
|
|
{
|
|
return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
|
|
}
|
|
|
|
function techColor(string $tech): string
|
|
{
|
|
return match (strtoupper($tech)) {
|
|
'2G' => '#eab308',
|
|
'3G' => '#2563eb',
|
|
'4G' => '#16a34a',
|
|
'5G' => '#9333ea',
|
|
'CDMA' => '#ea580c',
|
|
default => '#64748b',
|
|
};
|
|
}
|
|
|
|
$mapSites = $selectedSite ? [$selectedSite] : $sites;
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="fr">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1">
|
|
<title>NetMonster Admin - Recherche antenne</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>
|
|
<link rel="stylesheet" href="assets/theme.css">
|
|
<style>
|
|
.search-layout { display:grid; grid-template-columns:minmax(300px, 38%) minmax(420px, 62%); gap:1rem; align-items:start; }
|
|
.search-card { margin-bottom:1rem; }
|
|
.search-form { display:grid; grid-template-columns:2fr 1fr 1fr 1fr auto; gap:.55rem; align-items:end; }
|
|
.search-form label { display:block; font-size:.78rem; margin-bottom:.25rem; color:var(--muted,#64748b); }
|
|
.search-form input, .search-form select { width:100%; box-sizing:border-box; }
|
|
.results-list { display:flex; flex-direction:column; gap:.6rem; max-height:650px; overflow:auto; }
|
|
.site-result { border:1px solid #dce2ea; border-radius:8px; padding:.75rem; background:#fff; cursor:pointer; transition:.15s; }
|
|
.site-result:hover, .site-result.selected { border-color:var(--primary); box-shadow:0 0 0 2px rgba(37,99,235,.12); }
|
|
.site-result h3 { margin:0 0 .3rem; font-size:1rem; }
|
|
.site-result p { margin:.2rem 0; font-size:.82rem; color:#526174; }
|
|
.site-result .meta { display:flex; gap:.4rem; flex-wrap:wrap; margin-top:.45rem; }
|
|
.badge { display:inline-block; padding:.16rem .42rem; border-radius:999px; color:#fff; font-size:.72rem; font-weight:600; }
|
|
.badge-neutral { background:#64748b; }
|
|
#searchMap { height:650px; min-height:420px; border-radius:var(--radius-sm); background:#e8eaf0; }
|
|
.map-card { position:sticky; top:1rem; }
|
|
.legend { display:flex; gap:.8rem; flex-wrap:wrap; margin-top:.65rem; font-size:.76rem; }
|
|
.legend span { display:inline-flex; align-items:center; gap:.25rem; }
|
|
.legend i { width:10px; height:10px; border-radius:50%; display:inline-block; }
|
|
.cells-card { margin-top:1rem; overflow:auto; }
|
|
.cells-card table { min-width:950px; }
|
|
.azimuth-cell { white-space:nowrap; font-weight:600; }
|
|
.empty-state { padding:1.2rem; text-align:center; color:#64748b; }
|
|
.site-actions { display:flex; gap:.5rem; margin-top:.65rem; flex-wrap:wrap; }
|
|
.popup-title { font-weight: 700; }
|
|
.popup-line { margin: 0; }
|
|
@media (max-width:1000px) { .search-layout { grid-template-columns:1fr; } .map-card { position:static; } .search-form { grid-template-columns:1fr 1fr; } #searchMap { height:480px; } }
|
|
@media (max-width:600px) { .search-form { grid-template-columns:1fr; } }
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<?php render_admin_nav('search_site.php', $user); ?>
|
|
|
|
<div class="page-wrap">
|
|
<h1>Recherche antenne / eNB</h1>
|
|
<p class="page-subtitle">Recherchez un eNB, une cellule, une bande, une frequence ou une adresse. Les secteurs avec azimut sont dessines sur la carte.</p>
|
|
|
|
<div class="card search-card">
|
|
<form class="search-form" method="get">
|
|
<div>
|
|
<label for="q">Recherche</label>
|
|
<input id="q" type="search" name="q" value="<?= h($query) ?>" placeholder="eNB, Cell ID, bande, frequence, adresse..." autofocus>
|
|
</div>
|
|
<div>
|
|
<label for="country">Pays</label>
|
|
<select id="country" name="country">
|
|
<option value="">Tous les pays</option>
|
|
<?php foreach ($countries as $country): ?>
|
|
<option value="<?= h($country['iso2']) ?>" <?= $countryFilter === $country['iso2'] ? 'selected' : '' ?>><?= h($country['name']) ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div>
|
|
<label for="operator">Operateur</label>
|
|
<select id="operator" name="operator">
|
|
<option value="">Tous les operateurs</option>
|
|
<?php foreach ($operators as $operator): ?>
|
|
<option value="<?= (int)$operator['id'] ?>" <?= $operatorFilter === (string)$operator['id'] ? 'selected' : '' ?>><?= h($operator['brand']) ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div>
|
|
<label for="tech">Technologie</label>
|
|
<select id="tech" name="tech">
|
|
<option value="">Toutes</option>
|
|
<?php foreach (['2G','3G','4G','5G','CDMA'] as $tech): ?>
|
|
<option value="<?= $tech ?>" <?= $techFilter === $tech ? 'selected' : '' ?>><?= $tech ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<button type="submit" class="primary">Rechercher</button>
|
|
</form>
|
|
</div>
|
|
|
|
<div class="search-layout">
|
|
<div>
|
|
<div class="card">
|
|
<h2>Antennes trouvees (<?= count($sites) ?>)</h2>
|
|
<?php if (!$query && !$countryFilter && !$operatorFilter && !$techFilter): ?>
|
|
<div class="empty-state">Saisissez un eNB, un Cell ID, une bande ou une adresse.</div>
|
|
<?php elseif (empty($sites)): ?>
|
|
<div class="empty-state">Aucune antenne trouvee.</div>
|
|
<?php else: ?>
|
|
<div class="results-list">
|
|
<?php foreach ($sites as $site): ?>
|
|
<?php $isSelected = $selectedSite && (int)$selectedSite['site_id'] === (int)$site['site_id']; ?>
|
|
<a class="site-result <?= $isSelected ? 'selected' : '' ?>" href="?<?= h(http_build_query(['q' => $query, 'country' => $countryFilter, 'operator' => $operatorFilter, 'tech' => $techFilter, 'site_id' => $site['site_id']])) ?>">
|
|
<h3><?= h($site['operator_brand']) ?> — <?= h($site['country_name']) ?></h3>
|
|
<p><strong>eNB/RNC :</strong> <?= h($site['cells'][0]['sub_id2'] ?: 'non renseigne') ?></p>
|
|
<p><?= h($site['address_desc'] ?: $site['location_desc'] ?: 'Adresse non renseignee') ?></p>
|
|
<p><strong><?= (int)$site['cell_count'] ?></strong> cellule(s) — <?= h((string)$site['lat']) ?>, <?= h((string)$site['lon']) ?></p>
|
|
<div class="meta">
|
|
<?php foreach (array_values(array_unique(array_filter(array_map(static fn(array $cell): string => $cell['tech'], $site['cells'])))) as $tech): ?>
|
|
<span class="badge" style="background:<?= h(techColor($tech)) ?>"><?= h($tech) ?></span>
|
|
<?php endforeach; ?>
|
|
<?php $azimuthCount = count(array_filter($site['cells'], static fn(array $cell): bool => $cell['azimuth'] !== null)); ?>
|
|
<span class="badge badge-neutral"><?= $azimuthCount ?> azimut(s)</span>
|
|
</div>
|
|
</a>
|
|
<?php endforeach; ?>
|
|
</div>
|
|
<?php endif; ?>
|
|
</div>
|
|
|
|
<?php if ($selectedSite): ?>
|
|
<div class="card cells-card">
|
|
<h2>Cellules de l'antenne</h2>
|
|
<p class="page-subtitle"><?= h($selectedSite['operator_brand']) ?> — <?= h($selectedSite['address_desc'] ?: $selectedSite['location_desc']) ?></p>
|
|
<div class="site-actions"><a class="primary" href="edit_site.php?site_id=<?= (int)$selectedSite['site_id'] ?>">Modifier cette antenne</a></div>
|
|
<table class="data-table">
|
|
<tr><th>Techno</th><th>Cell ID</th><th>eNB/RNC</th><th>Bande</th><th>Frequence</th><th>Largeur</th><th>Azimut</th><th>ARFCN</th></tr>
|
|
<?php foreach ($selectedCells as $cell): ?>
|
|
<tr>
|
|
<td><span class="badge" style="background:<?= h(techColor($cell['tech'])) ?>"><?= h($cell['tech']) ?></span></td>
|
|
<td><?= h($cell['cell_id']) ?></td>
|
|
<td><?= h($cell['sub_id2']) ?></td>
|
|
<td><?= h($cell['band']) ?></td>
|
|
<td><?= h($cell['frequency']) ?></td>
|
|
<td><?= h($cell['bandwidth']) ?></td>
|
|
<td class="azimuth-cell"><?= $cell['azimuth'] === null ? '—' : h(rtrim(rtrim(number_format((float)$cell['azimuth'], 2, '.', ''), '0'), '.')) . '°' ?></td>
|
|
<td><?= h($cell['arfcn']) ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</table>
|
|
</div>
|
|
<?php endif; ?>
|
|
</div>
|
|
|
|
<div class="card map-card">
|
|
<h2>Carte des azimuts</h2>
|
|
<div id="searchMap"></div>
|
|
<div class="legend">
|
|
<?php foreach (['2G','3G','4G','5G','CDMA'] as $tech): ?><span><i style="background:<?= h(techColor($tech)) ?>"></i><?= $tech ?></span><?php endforeach; ?>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
const mapSites = <?= json_encode($mapSites, JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_NUMERIC_CHECK) ?>;
|
|
const techColors = { '2G': '#eab308', '3G': '#2563eb', '4G': '#16a34a', '5G': '#9333ea', 'CDMA': '#ea580c' };
|
|
|
|
function destinationPoint(lat, lon, bearing, distanceMeters) {
|
|
const radius = 6371000;
|
|
const angularDistance = distanceMeters / radius;
|
|
const bearingRad = bearing * Math.PI / 180;
|
|
const latRad = lat * Math.PI / 180;
|
|
const lonRad = lon * Math.PI / 180;
|
|
const destLat = Math.asin(Math.sin(latRad) * Math.cos(angularDistance) + Math.cos(latRad) * Math.sin(angularDistance) * Math.cos(bearingRad));
|
|
const destLon = lonRad + Math.atan2(Math.sin(bearingRad) * Math.sin(angularDistance) * Math.cos(latRad), Math.cos(angularDistance) - Math.sin(latRad) * Math.sin(destLat));
|
|
return [destLat * 180 / Math.PI, destLon * 180 / Math.PI];
|
|
}
|
|
|
|
function sectorPoints(lat, lon, azimuth, radius, opening) {
|
|
const points = [[lat, lon]];
|
|
const start = azimuth - opening / 2;
|
|
const end = azimuth + opening / 2;
|
|
for (let angle = start; angle <= end; angle += 4) {
|
|
points.push(destinationPoint(lat, lon, angle, radius));
|
|
}
|
|
points.push(destinationPoint(lat, lon, end, radius));
|
|
points.push([lat, lon]);
|
|
return points;
|
|
}
|
|
|
|
/**
|
|
* Construit la popup d'un site via des elements DOM (textContent),
|
|
* jamais via innerHTML/template literal, pour empecher toute
|
|
* injection HTML/JS a partir de donnees stockees en base.
|
|
*/
|
|
function buildSitePopup(site) {
|
|
const container = document.createElement('div');
|
|
|
|
const title = document.createElement('div');
|
|
title.className = 'popup-title';
|
|
title.textContent = `${site.operator_brand ?? ''} — ${site.country_name ?? ''}`;
|
|
container.appendChild(title);
|
|
|
|
const addr = document.createElement('div');
|
|
addr.className = 'popup-line';
|
|
addr.textContent = site.address_desc || site.location_desc || 'Adresse non renseignee';
|
|
container.appendChild(addr);
|
|
|
|
const count = document.createElement('div');
|
|
count.className = 'popup-line';
|
|
count.textContent = `${site.cell_count} cellule(s)`;
|
|
container.appendChild(count);
|
|
|
|
return container;
|
|
}
|
|
|
|
/**
|
|
* Construit la popup d'un secteur (polygone d'azimut) via des
|
|
* elements DOM (textContent), meme logique que buildSitePopup.
|
|
*/
|
|
function buildSectorPopup(cell) {
|
|
const container = document.createElement('div');
|
|
|
|
const title = document.createElement('div');
|
|
title.className = 'popup-title';
|
|
title.textContent = `${cell.tech || ''} ${cell.band || ''}`.trim();
|
|
container.appendChild(title);
|
|
|
|
const lines = [
|
|
`Cell ID : ${cell.cell_id || ''}`,
|
|
`eNB/RNC : ${cell.sub_id2 || ''}`,
|
|
`Frequence : ${cell.frequency || ''}`,
|
|
`Largeur : ${cell.bandwidth || ''}`,
|
|
`Azimut : ${cell.azimuth}°`,
|
|
];
|
|
lines.forEach(text => {
|
|
const line = document.createElement('div');
|
|
line.className = 'popup-line';
|
|
line.textContent = text;
|
|
container.appendChild(line);
|
|
});
|
|
|
|
return container;
|
|
}
|
|
|
|
const mapElement = document.getElementById('searchMap');
|
|
const defaultCenter = [49.8153, 6.1296];
|
|
let mapCenter = defaultCenter;
|
|
let mapZoom = 8;
|
|
|
|
if (mapSites.length > 0) {
|
|
mapCenter = [Number(mapSites[0].lat), Number(mapSites[0].lon)];
|
|
mapZoom = mapSites.length === 1 ? 16 : 10;
|
|
}
|
|
|
|
const searchMap = L.map(mapElement).setView(mapCenter, mapZoom);
|
|
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(searchMap);
|
|
|
|
const bounds = [];
|
|
|
|
mapSites.forEach(site => {
|
|
const lat = Number(site.lat);
|
|
const lon = Number(site.lon);
|
|
if (!Number.isFinite(lat) || !Number.isFinite(lon)) return;
|
|
|
|
const marker = L.marker([lat, lon]).addTo(searchMap);
|
|
marker.bindPopup(buildSitePopup(site));
|
|
bounds.push([lat, lon]);
|
|
|
|
(site.cells || []).forEach(cell => {
|
|
if (cell.azimuth === null || cell.azimuth === '' || !Number.isFinite(Number(cell.azimuth))) return;
|
|
const azimuth = Number(cell.azimuth);
|
|
const sectorColor = techColors[String(cell.tech).toUpperCase()] || '#64748b';
|
|
const polygon = L.polygon(sectorPoints(lat, lon, azimuth, 250, 65), { color: sectorColor, fillColor: sectorColor, fillOpacity: 0.28, weight: 2 }).addTo(searchMap);
|
|
polygon.bindPopup(buildSectorPopup(cell));
|
|
bounds.push(...sectorPoints(lat, lon, azimuth, 250, 65));
|
|
});
|
|
});
|
|
|
|
if (bounds.length > 1) searchMap.fitBounds(bounds, { padding: [25, 25] });
|
|
setTimeout(() => searchMap.invalidateSize(), 100);
|
|
</script>
|
|
</body>
|
|
</html>
|