255 lines
11 KiB
PHP
255 lines
11 KiB
PHP
<?php
|
|
/**
|
|
* admin/duplicates.php
|
|
*
|
|
* Detection et gestion des doublons / collisions eNB-Cell ID.
|
|
* Suit le meme modele de securite que data_quality.php :
|
|
* - authentification admin obligatoire (admin_auth.php),
|
|
* - jeton CSRF sur toute action de fusion/suppression,
|
|
* - aucune suppression automatique : chaque action necessite une
|
|
* confirmation explicite de l'utilisateur (case a cocher + bouton),
|
|
* - requetes preparees uniquement, jamais de concatenation SQL.
|
|
*
|
|
* CORRIGE (20/09/2026, suite a erreur 500 en usage reel) : cette page
|
|
* utilisait des noms de colonnes inexistants dans le schema reel
|
|
* (c.enb, c.technology, s.name), ecrits avant que le schema definitif
|
|
* (install_full_schema.sql) ne soit connu. Alignee maintenant sur les
|
|
* vraies colonnes :
|
|
* - cells.tech (pas "technology")
|
|
* - cells.sub_id2 (eNB/RNC, pas de colonne "enb")
|
|
* - sites n'a pas de colonne "name" : utilise address_desc en
|
|
* priorite, puis location_desc, puis "Site #<id>" en repli.
|
|
*
|
|
* Corrige suite a l'audit du 20/09/2026 : le detail technique des
|
|
* exceptions n'est plus affiche a l'ecran ; il est journalise via
|
|
* error_log().
|
|
*
|
|
* Note : le controle par role (require_admin_role) est volontairement
|
|
* absent de cette version, la separation des privileges etant reportee
|
|
* a une migration dediee.
|
|
*/
|
|
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();
|
|
$feedback = null;
|
|
$feedbackType = 'success';
|
|
|
|
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
|
|
require_csrf_token();
|
|
|
|
$action = $_POST['action'] ?? '';
|
|
$confirmed = isset($_POST['confirm']) && $_POST['confirm'] === '1';
|
|
|
|
if (!$confirmed) {
|
|
$feedback = 'Action refusee : la confirmation explicite est obligatoire.';
|
|
$feedbackType = 'error';
|
|
} elseif ($action === 'delete_cell') {
|
|
$cellId = filter_input(INPUT_POST, 'cell_id', FILTER_VALIDATE_INT);
|
|
if ($cellId === false || $cellId === null) {
|
|
$feedback = 'Identifiant de cellule invalide.';
|
|
$feedbackType = 'error';
|
|
} else {
|
|
try {
|
|
$stmt = $db->prepare('DELETE FROM cells WHERE id = ? LIMIT 1');
|
|
$stmt->execute([$cellId]);
|
|
$feedback = $stmt->rowCount() > 0
|
|
? sprintf('Cellule #%d supprimee.', $cellId)
|
|
: 'Aucune cellule correspondante trouvee (deja supprimee ?).';
|
|
} catch (Throwable $e) {
|
|
error_log('[duplicates] delete_cell: ' . $e->getMessage());
|
|
$feedback = 'Une erreur est survenue lors de la suppression.';
|
|
$feedbackType = 'error';
|
|
}
|
|
}
|
|
} elseif ($action === 'merge_sites') {
|
|
$keepId = filter_input(INPUT_POST, 'keep_site_id', FILTER_VALIDATE_INT);
|
|
$dropId = filter_input(INPUT_POST, 'drop_site_id', FILTER_VALIDATE_INT);
|
|
|
|
if (!$keepId || !$dropId || $keepId === $dropId) {
|
|
$feedback = 'Selection de sites invalide pour la fusion.';
|
|
$feedbackType = 'error';
|
|
} else {
|
|
try {
|
|
$db->beginTransaction();
|
|
$stmt = $db->prepare('UPDATE cells SET site_id = ? WHERE site_id = ?');
|
|
$stmt->execute([$keepId, $dropId]);
|
|
$moved = $stmt->rowCount();
|
|
|
|
$stmt = $db->prepare('DELETE FROM sites WHERE id = ? LIMIT 1');
|
|
$stmt->execute([$dropId]);
|
|
|
|
$db->commit();
|
|
$feedback = sprintf(
|
|
'Fusion effectuee : %d cellule(s) rattachee(s) au site #%d, site #%d supprime.',
|
|
$moved,
|
|
$keepId,
|
|
$dropId
|
|
);
|
|
} catch (Throwable $e) {
|
|
$db->rollBack();
|
|
error_log('[duplicates] merge_sites: ' . $e->getMessage());
|
|
$feedback = 'Une erreur est survenue lors de la fusion. Aucune donnee n\'a ete modifiee.';
|
|
$feedbackType = 'error';
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Collisions eNB/Cell ID : deux cellules distinctes qui partagent le
|
|
* meme couple (sub_id2, cell_id) sur la meme technologie sont des
|
|
* candidates a la fusion ou a la correction manuelle. area_code est
|
|
* volontairement exclu de ce regroupement (contrairement a uniq_cell)
|
|
* pour repérer aussi les cas ou le TAC differe par erreur de saisie
|
|
* entre deux lignes du meme eNB.
|
|
*/
|
|
$cellDuplicates = $db->query(
|
|
"SELECT c.sub_id2, c.cell_id, c.tech, COUNT(*) AS occurrences,
|
|
GROUP_CONCAT(c.id ORDER BY c.id SEPARATOR ',') AS cell_ids,
|
|
GROUP_CONCAT(
|
|
DISTINCT COALESCE(s.address_desc, s.location_desc, CONCAT('Site #', s.id))
|
|
ORDER BY s.id SEPARATOR ' | '
|
|
) AS site_names
|
|
FROM cells c
|
|
LEFT JOIN sites s ON s.id = c.site_id
|
|
WHERE c.sub_id2 IS NOT NULL AND c.cell_id IS NOT NULL
|
|
GROUP BY c.sub_id2, c.cell_id, c.tech
|
|
HAVING occurrences > 1
|
|
ORDER BY occurrences DESC
|
|
LIMIT 200"
|
|
)->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$siteDuplicates = $db->query(
|
|
"SELECT ROUND(s.lat, 5) AS lat_r, ROUND(s.lon, 5) AS lon_r,
|
|
COUNT(*) AS occurrences,
|
|
GROUP_CONCAT(s.id ORDER BY s.id SEPARATOR ',') AS site_ids,
|
|
GROUP_CONCAT(
|
|
COALESCE(s.address_desc, s.location_desc, CONCAT('Site #', s.id))
|
|
ORDER BY s.id SEPARATOR ' | '
|
|
) AS names
|
|
FROM sites s
|
|
WHERE s.lat IS NOT NULL AND s.lon IS NOT NULL
|
|
AND NOT (s.lat = 0 AND s.lon = 0)
|
|
GROUP BY lat_r, lon_r
|
|
HAVING occurrences > 1
|
|
ORDER BY occurrences DESC
|
|
LIMIT 200"
|
|
)->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$user = current_admin_username();
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="fr">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<title>Doublons & collisions — NetMonster Admin</title>
|
|
<link rel="stylesheet" href="assets/theme.css">
|
|
</head>
|
|
<body>
|
|
<?php render_admin_nav('duplicates.php', $user); ?>
|
|
|
|
<main class="page-wrap">
|
|
<h1>Doublons & collisions eNB / Cell ID</h1>
|
|
<p class="page-subtitle">Cette page detecte les collisions potentielles sans jamais modifier la base automatiquement. Toute fusion ou suppression exige une confirmation explicite.</p>
|
|
|
|
<?php if ($feedback !== null): ?>
|
|
<p class="<?= $feedbackType === 'error' ? 'msg-err' : 'msg-ok' ?>">
|
|
<?= htmlspecialchars($feedback, ENT_QUOTES, 'UTF-8') ?>
|
|
</p>
|
|
<?php endif; ?>
|
|
|
|
<section class="card">
|
|
<h2>Collisions eNB / Cell ID (<?= count($cellDuplicates) ?>)</h2>
|
|
<?php if (empty($cellDuplicates)): ?>
|
|
<p>Aucune collision detectee.</p>
|
|
<?php else: ?>
|
|
<table class="data-table">
|
|
<tr>
|
|
<th>Techno</th>
|
|
<th>eNB/RNC</th>
|
|
<th>Cell ID</th>
|
|
<th>Occurrences</th>
|
|
<th>Sites concernes</th>
|
|
<th>Action</th>
|
|
</tr>
|
|
<?php foreach ($cellDuplicates as $row): ?>
|
|
<tr>
|
|
<td><?= htmlspecialchars((string)$row['tech'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= htmlspecialchars((string)$row['sub_id2'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= htmlspecialchars((string)$row['cell_id'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= (int)$row['occurrences'] ?></td>
|
|
<td><?= htmlspecialchars((string)$row['site_names'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td>
|
|
<form method="post" onsubmit="return confirm('Confirmer la suppression de cette cellule en doublon ?');">
|
|
<?= csrf_field() ?>
|
|
<input type="hidden" name="action" value="delete_cell">
|
|
<input type="hidden" name="confirm" value="1">
|
|
<?php $ids = explode(',', (string)$row['cell_ids']); ?>
|
|
<select name="cell_id">
|
|
<?php foreach ($ids as $id): ?>
|
|
<option value="<?= (int)$id ?>">Cellule #<?= (int)$id ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
<button type="submit" class="reject-small">Supprimer</button>
|
|
</form>
|
|
</td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</table>
|
|
<?php endif; ?>
|
|
</section>
|
|
|
|
<section class="card">
|
|
<h2>Sites potentiellement dupliques (<?= count($siteDuplicates) ?>)</h2>
|
|
<?php if (empty($siteDuplicates)): ?>
|
|
<p>Aucun site en collision de coordonnees.</p>
|
|
<?php else: ?>
|
|
<table class="data-table">
|
|
<tr>
|
|
<th>Latitude</th>
|
|
<th>Longitude</th>
|
|
<th>Occurrences</th>
|
|
<th>Sites</th>
|
|
<th>Fusionner</th>
|
|
</tr>
|
|
<?php foreach ($siteDuplicates as $row): ?>
|
|
<?php $siteIds = explode(',', (string)$row['site_ids']); ?>
|
|
<tr>
|
|
<td><?= htmlspecialchars((string)$row['lat_r'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= htmlspecialchars((string)$row['lon_r'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td><?= (int)$row['occurrences'] ?></td>
|
|
<td><?= htmlspecialchars((string)$row['names'], ENT_QUOTES, 'UTF-8') ?></td>
|
|
<td>
|
|
<form method="post" onsubmit="return confirm('Confirmer la fusion de ces deux sites ? Cette action est irreversible.');">
|
|
<?= csrf_field() ?>
|
|
<input type="hidden" name="action" value="merge_sites">
|
|
<input type="hidden" name="confirm" value="1">
|
|
Garder
|
|
<select name="keep_site_id">
|
|
<?php foreach ($siteIds as $id): ?>
|
|
<option value="<?= (int)$id ?>">#<?= (int)$id ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
Supprimer
|
|
<select name="drop_site_id">
|
|
<?php foreach ($siteIds as $id): ?>
|
|
<option value="<?= (int)$id ?>">#<?= (int)$id ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
<button type="submit" class="reject-small">Fusionner</button>
|
|
</form>
|
|
</td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</table>
|
|
<?php endif; ?>
|
|
</section>
|
|
</main>
|
|
</body>
|
|
</html>
|