575 lines
20 KiB
PHP
575 lines
20 KiB
PHP
<?php
|
|
include "../config/config.php";
|
|
Init_session(basename($_SERVER['SCRIPT_NAME'])); // Fonction ouvrant la session et vérifiant que les accès sont autorisés.
|
|
|
|
|
|
|
|
|
|
//************************************************************************************************************************************
|
|
// Initialisation des variables globales
|
|
//************************************************************************************************************************************
|
|
$message_erreur = "";
|
|
$message_information = "";
|
|
$flag_redirection = false;
|
|
|
|
Ouverture_bdd(); // Connexion à MySql
|
|
|
|
|
|
//************************************************************************************************************************************
|
|
// Traitement des variables d'entrées
|
|
//************************************************************************************************************************************
|
|
|
|
// Variables de SESSION :
|
|
if (!isset($_SESSION['Administration']['page'])) $_SESSION['Administration']['page'] = 'Accueil';
|
|
if (!isset($_SESSION['Administration']['ancre'])) $_SESSION['Administration']['ancre'] = "#";
|
|
if (!isset($_SESSION['Administration']['id_shipmember'])) $_SESSION['Administration']['id_shipmember'] = 0;
|
|
|
|
|
|
// Variables GET :
|
|
if (isset($_GET['action']))
|
|
{
|
|
switch ($_GET['action'])
|
|
{
|
|
//===================================
|
|
case "navigation":
|
|
{
|
|
if (isset($_GET['destination']))
|
|
{
|
|
$_SESSION['Administration']['page'] = $_GET['destination'];
|
|
//$_SESSION['display_menu'] = false;
|
|
}
|
|
|
|
if (isset($_GET['ancre']))
|
|
{
|
|
$_SESSION['Administration']['ancre'] = $_GET['ancre'];
|
|
//$_SESSION['display_menu'] = false;
|
|
}
|
|
break;
|
|
}
|
|
|
|
//===================================
|
|
case "changement_langue":
|
|
{
|
|
if (isset($_GET['langue']))
|
|
{
|
|
$_SESSION['langue'] = $_GET['langue'];
|
|
chargement_langue();
|
|
}
|
|
break;
|
|
}
|
|
|
|
//===================================
|
|
case "details_shipmember":
|
|
{
|
|
if (isset($_GET['id_shipmember']))
|
|
{
|
|
$_SESSION['Administration']['id_shipmember'] = $_GET['id_shipmember'];
|
|
$_SESSION['Administration']['page'] = 'Details_shipmember';
|
|
}
|
|
|
|
break;
|
|
}
|
|
|
|
//===================================
|
|
case "changement_type":
|
|
{
|
|
if (isset($_GET['type']))
|
|
{
|
|
$_SESSION['type_utilisateur'] = $_GET['type'];
|
|
chargement_langue();
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (!$flag_redirection) header('Location:administration.php');
|
|
}
|
|
|
|
|
|
// Variables POST :
|
|
|
|
|
|
|
|
|
|
|
|
//************************************************************************************************************************************
|
|
// Traitement des données
|
|
//************************************************************************************************************************************
|
|
|
|
// Protection contre les accès directs :
|
|
if ((!$_SESSION['acces_valide']) OR ($_SESSION['shipmember']['Niveau_acces'] < NIVEAU_ACCES_MINI_ADMINISTRATEUR)) header('Location:shipmembers.php?action=navigation&destination=Login');
|
|
|
|
switch ($_SESSION['Administration']['page'])
|
|
{
|
|
//****************************************************************************************************************************
|
|
case "Accueil":
|
|
{
|
|
|
|
break;
|
|
}
|
|
|
|
|
|
//****************************************************************************************************************************
|
|
case "Shipmembers":
|
|
{
|
|
// Récupération des informations sur les ShipMembers :
|
|
$nb_shipmembers = 0;
|
|
|
|
$requete = 'SELECT * FROM TB_Shipmembers WHERE Validite=1 ORDER BY ID ASC';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(),
|
|
__FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$shipmembers[$nb_shipmembers ++] = $donnees;
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
break;
|
|
}
|
|
|
|
|
|
//****************************************************************************************************************************
|
|
case "Details_shipmember":
|
|
{
|
|
// Modifier les droits d'accès d'un utilisateur
|
|
if (isset($_POST['niveau_acces_shipmember']))
|
|
{
|
|
$na = $_POST['niveau_acces_shipmember'];
|
|
if (($na >=NIVEAU_ACCES_MINI) AND ($na <= NIVEAU_ACCES_MAXI))
|
|
{
|
|
$requete = 'UPDATE TB_Shipmembers SET Niveau_acces=? WHERE ID=?';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(
|
|
array('i', $na),
|
|
array('i', $_SESSION['Administration']['id_shipmember'])),
|
|
__FILE__, __LINE__);
|
|
}
|
|
}
|
|
|
|
// Récupération des informations sur le ShipMember :
|
|
$requete = 'SELECT *, (SELECT Texte FROM TB_Textes_traduction WHERE ID_TYPE = 1 AND ID_TEXTES=(SELECT ID_NOM FROM TB_Pays WHERE ID=TB_Shipmembers.ID_PAYS) AND ID_LANGUAGES=1) AS Libelle_pays FROM TB_Shipmembers WHERE ID='.$_SESSION['Administration']['id_shipmember'].' ';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(),
|
|
__FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$shipmember = $donnees;
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
$requete = '';
|
|
|
|
// Récupération des informations sur le ou les bateaux accessibles à ce ShipMember :
|
|
$nb_bateaux = 0;
|
|
$requete = "SELECT * FROM TB_Lien_bateau_shipmember WHERE ID_SHIPMEMBER=? AND Statut IN ('actif','suspendu')"; // Hors invitations et accès révoqués (synthèse §47)
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(
|
|
array('i', $_SESSION['Administration']['id_shipmember'])),
|
|
__FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$bateaux[$nb_bateaux ++]['Cle_primaire'] = $donnees['ID_Bateau'];
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
// Récupération des informations relatives à chacun des bateaux accessibles :
|
|
for ($i=0; $i<$nb_bateaux; $i ++)
|
|
{
|
|
$requete = 'SELECT * FROM TB_Bateaux WHERE Cle_primaire=? ';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(
|
|
array('i', $bateaux[$i]['Cle_primaire'])),
|
|
__FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$bateaux[$i] = $donnees;
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
}
|
|
|
|
Rafraichir_acces_session($_SESSION['shipmember']['Cle_primaire']); // Accès actifs (synthèse §47)
|
|
// $_SESSION['acces_valide'] = true;
|
|
|
|
break;
|
|
}
|
|
|
|
|
|
|
|
//****************************************************************************************************************************
|
|
case "Purge_bdd":
|
|
{
|
|
$nb_tables = 0;
|
|
$tables[$nb_tables ++]['Nom'] = "TB_BATEAUX_Mesures_circuits";
|
|
$tables[$nb_tables ++]['Nom'] = "TB_BATEAUX_Mesures_batteries";
|
|
$tables[$nb_tables ++]['Nom'] = "TB_BATEAUX_Mesures_reservoirs";
|
|
$tables[$nb_tables ++]['Nom'] = "TB_BATEAUX_Mesures_capteurs";
|
|
|
|
$max_lignes_traitees = 500000;
|
|
|
|
for ($x = 0; $x < $nb_tables; $x ++)
|
|
{
|
|
$nom_table = $tables[$x]['Nom'];
|
|
|
|
$start_time = microtime(true);
|
|
|
|
//------------------------------------------------------
|
|
// Recherche de la taille de la table avant traitement :
|
|
$requete = 'SELECT COUNT(*) AS Nb FROM '.$nom_table.' ';
|
|
$reponse = Requete_bdd($requete, __FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$taille_table_avant_traitement = $donnees['Nb'];
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
//---------------------------------------------------
|
|
// Recherche des bateaux concernés dans cette table :
|
|
$nb_bateaux = 0;
|
|
$requete = 'SELECT DISTINCT(ID_Bateau) FROM '.$nom_table.' WHERE 1';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(),
|
|
__FILE__, __LINE__);
|
|
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$bateaux[$nb_bateaux ++] = $donnees;
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
//--------------------------------------------------------------------------------------------------------
|
|
// Boucle sur tous les bateaux pour rechercher les éléments concernés par chaque bateau dans cette table :
|
|
for ($i = 0; $i < $nb_bateaux ; $i ++)
|
|
{
|
|
// Recherche des éléments (circuits, réservoirs, batteries, ...) concernés sur chaque bateau :
|
|
$bateaux[$i]['nb_elements'] = 0;
|
|
$requete = 'SELECT DISTINCT(ID) FROM '.$nom_table.' WHERE ID_Bateau=? ';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(
|
|
array('i', $bateaux[$i]['ID_Bateau'])),
|
|
__FILE__, __LINE__);
|
|
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$bateaux[$i][$bateaux[$i]['nb_elements'] ++] = $donnees['ID'];
|
|
// echo '<p>'.$bateaux[$i]['nb_elements'].' : ID = '.$donnees['ID'].'</p>';
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
}
|
|
|
|
//-------------------------------------------------------------------
|
|
// Boucle sur tous les bateaux pour traiter les entrées redondantes:
|
|
$nb_total_lignes_supprimees = 0;
|
|
for ($i = 0; $i < $nb_bateaux ; $i ++)
|
|
{
|
|
// Boucle sur tous les éléments :
|
|
for ($j = 0; $j < $bateaux[$i]['nb_elements']; $j ++)
|
|
{
|
|
$nb_redondants = 0;
|
|
$val_1 = -1;
|
|
$val_2 = 0;
|
|
$id_2 = 0;
|
|
$requete = 'SELECT Cle_primaire, Date, Valeur FROM '.$nom_table.' WHERE ID_Bateau=? AND ID=? ORDER BY Date ';
|
|
$reponse = Requete_preparee_bdd($requete,
|
|
array(
|
|
array('i', $bateaux[$i]['ID_Bateau']),
|
|
array('i', $bateaux[$i][$j])),
|
|
__FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
// Recherche des conditions de redondance :
|
|
if (($val_1 == $val_2) AND ($val_2 == $donnees['Valeur'])) // Si la valeur actuelle est égale à la précédente et à celle encore avant :
|
|
{
|
|
$redondants[$nb_redondants ++] = $id_2; // ... on mémorise l'entrée intermédiaire (N-1)
|
|
$nb_total_lignes_supprimees ++;
|
|
if ($nb_total_lignes_supprimees >= $max_lignes_traitees) break;
|
|
}
|
|
|
|
$val_1 = $val_2;
|
|
$val_2 = $donnees['Valeur'];
|
|
$id_2 = $donnees['Cle_primaire'];
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
|
|
// Suppression des enregistrements redondants :
|
|
for ($k = 0; $k < $nb_redondants; $k ++)
|
|
{
|
|
// Création d'une chaine avec les différentes clés à supprimer afin de réduire le nombre de requêtes DELETE (impact sur temps de traitement d'environ / 50 avec la valeur de 100) :
|
|
$max_l = 100;
|
|
$chaine_cles_primaires = '(Cle_primaire='.$redondants[$k ++].' ';
|
|
if ($k < ($nb_redondants - 1))
|
|
{
|
|
for ($l = 1; $l < $max_l; $l ++)
|
|
{
|
|
$chaine_cles_primaires .= 'OR Cle_primaire='.$redondants[$k].' ';
|
|
if (($k >= ($nb_redondants - 1)) OR ($l >= ($max_l - 1))) break;
|
|
$k ++;
|
|
}
|
|
}
|
|
$chaine_cles_primaires .= ')';
|
|
|
|
$requete = 'DELETE FROM '.$nom_table.' WHERE ID_Bateau='.$bateaux[$i]['ID_Bateau'].' AND '.$chaine_cles_primaires.' ';
|
|
// echo 'Requête = ['.$requete.']<br />';
|
|
Requete_bdd($requete, __FILE__, __LINE__); // Pas de requête préparée pour accélérer le traitement...
|
|
}
|
|
if ($nb_total_lignes_supprimees >= $max_lignes_traitees) break;
|
|
}
|
|
if ($nb_total_lignes_supprimees >= $max_lignes_traitees) break;
|
|
}
|
|
|
|
// Recherche de la taille de la table après traitement :
|
|
$requete = 'SELECT COUNT(*) AS Nb FROM '.$nom_table.' ';
|
|
$reponse = Requete_bdd($requete, __FILE__, __LINE__);
|
|
while ($donnees = Recherche_ligne_bdd($reponse))
|
|
{
|
|
$taille_table_apres_traitement = $donnees['Nb'];
|
|
}
|
|
Liberation_jeu_de_resultat_bdd($reponse);
|
|
|
|
// Calculate script execution time
|
|
$execution_time = number_format((microtime(true) - $start_time), 1, ".", " ");
|
|
|
|
|
|
|
|
$tables[$x]['Taille_avant_traitement'] = $taille_table_avant_traitement;
|
|
$tables[$x]['Nb_total_lignes_supprimees'] = $nb_total_lignes_supprimees;
|
|
$tables[$x]['Execution_time'] = $execution_time;
|
|
$tables[$x]['Taille_apres_traitement'] = $taille_table_apres_traitement;
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
break;
|
|
}
|
|
|
|
//****************************************************************************************************************************
|
|
case "Questionnaires":
|
|
{
|
|
|
|
|
|
break;
|
|
}
|
|
|
|
|
|
|
|
}
|
|
|
|
Fermeture_bdd(); // Déconnexion de MySQL
|
|
|
|
//************************************************************************************************************************************
|
|
// Affichage
|
|
//************************************************************************************************************************************
|
|
|
|
Entete_page_html();
|
|
|
|
//********************************************************************************************************************************
|
|
// Affichage principal :
|
|
echo '<div id="Administration">';
|
|
|
|
//=========================================================================================
|
|
// Entête :
|
|
Entete_page($message_erreur, $message_information);
|
|
|
|
switch ($_SESSION['Administration']['page'])
|
|
{
|
|
//************************************************************************************************************************
|
|
case "Accueil":
|
|
{
|
|
echo '<div id="accueil" class="page_principale">';
|
|
|
|
echo '<h1>Administration</h1>';
|
|
|
|
echo '<h2>Accueil</h2>';
|
|
echo '<p>Attention : toute la partie Administration n\'est disponible qu\'en français.</p>';
|
|
|
|
echo '<h2>ShipMembers et Bateaux</h2>';
|
|
echo '<div class="boutons_bas_de_page">';
|
|
echo '<a title="" href="administration.php?action=navigation&destination=Shipmembers">ShipMembers</a>';
|
|
echo '<a title="" href="bateaux.php?action=navigation&destination=Accueil">Gestion des bateaux</a>';
|
|
echo '</div>';
|
|
|
|
echo '<h2>Technique</h2>';
|
|
echo '<div class="boutons_bas_de_page">';
|
|
echo '<a title="" href="versions.php?action=navigation&destination=Accueil">Gestion des versions</a>';
|
|
echo '<a title="" href="cartes.php?action=navigation&destination=Accueil">Gestion des cartes</a>';
|
|
echo '<a title="" href="uploads.php?action=navigation&destination=Accueil" onclick="return confirm(\'Vous êtes sur vous voulez traiter les uploads ?\');">Traitement des uploads</a>';
|
|
//echo '<a title="" href="data_treatment.php" onclick="return confirm(\'Etes-vous sûr de vouloir lancer DATE_TREATMENT.PHP pour traiter les tables [old] ?\');">Traitement des tables OLD</a>';
|
|
echo '</div>';
|
|
|
|
echo '<div class="boutons_bas_de_page">';
|
|
echo '<a title="" href="shipmembers.php?action=navigation&destination=Accueil">Retour au menu ShipMember</a>';
|
|
echo '</div>';
|
|
|
|
echo '</div>';
|
|
|
|
break;
|
|
}
|
|
|
|
//************************************************************************************************************************
|
|
case "Shipmembers":
|
|
{
|
|
echo '<div id="shipmembers" class="page_principale">';
|
|
|
|
echo '<h1>Administration</h1>';
|
|
|
|
echo '<h2>ShipMembers</h2>';
|
|
|
|
echo '<table class="shipmember">
|
|
<thead>
|
|
<tr>
|
|
<th>Nom</th>
|
|
<th>Entreprise</th>
|
|
<th>Telephone</th>
|
|
<th>Email</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>';
|
|
for ($i = 0; $i < $nb_shipmembers; $i ++)
|
|
{
|
|
echo '<tr>
|
|
<td>
|
|
<a href="administration.php?action=details_shipmember&id_shipmember='.$shipmembers[$i]['ID'].'">
|
|
'.$shipmembers[$i]['Prenom'].' '.$shipmembers[$i]['Nom'].'
|
|
</a>
|
|
</td>
|
|
<td>'.$shipmembers[$i]['Entreprise'].'</td>
|
|
<td>'.$shipmembers[$i]['Telephone'].'</td>
|
|
<td>'.$shipmembers[$i]['Email'].'</td>
|
|
</tr>';
|
|
}
|
|
echo '</tbody>
|
|
</table>';
|
|
|
|
echo '<div class="boutons_bas_de_page">';
|
|
echo '<a title="" href="administration.php?action=navigation&destination=Accueil">Retour au menu Administration</a>';
|
|
echo '</div>';
|
|
|
|
echo '</div>';
|
|
|
|
break;
|
|
}
|
|
|
|
|
|
//************************************************************************************************************************
|
|
case "Details_shipmember":
|
|
{
|
|
echo '<div id="details_shipmember" class="page_principale">';
|
|
|
|
echo '<h1>Administration</h1>';
|
|
|
|
echo '<h2>ShipMember n° <span class="gras">'.$shipmember['ID'].'</span></h2>';
|
|
|
|
echo '<table class="shipmember">
|
|
<tbody>';
|
|
echo '<tr>
|
|
<td>Nom complet</td>
|
|
<td>'.$shipmember['Prenom'].' '.$shipmember['Nom'].'</td>
|
|
</tr>';
|
|
|
|
echo '<tr>
|
|
<td>Entreprise</td>
|
|
<td>'.$shipmember['Entreprise'].'</td>
|
|
</tr>';
|
|
|
|
echo '<tr>
|
|
<td>Adresse</td>
|
|
<td>'.$shipmember['Rue'].' - '.$shipmember['Code_postal'].' '.$shipmember['Ville'].' - '.$shipmember['Libelle_pays'].'</td>
|
|
</tr>';
|
|
|
|
echo '<tr>
|
|
<td>Téléphone</td>
|
|
<td>'.$shipmember['Telephone'].'</td>
|
|
</tr>';
|
|
|
|
echo '<tr>
|
|
<td>Email</td>
|
|
<td>'.$shipmember['Email'].'</td>
|
|
</tr>';
|
|
|
|
echo '<tr>
|
|
<td>Date création du compte</td>
|
|
<td>'.Formatage_date($shipmember['Date_creation_compte']).'</td>
|
|
</tr>';
|
|
|
|
echo '<tr>
|
|
<td>Niveau d\'accès</td>
|
|
<td> <form method="POST" action="administration.php">
|
|
<input name="niveau_acces_shipmember" type="number" max="99" min="0" value="'.$shipmember['Niveau_acces'].'" style="max-width:20%">
|
|
<button type="submit" style="font-size: 12px"> Valider </button>
|
|
</form>
|
|
</td>
|
|
</tr>';
|
|
|
|
|
|
echo '<tr>
|
|
<td>Bateaux accessibles</td>
|
|
<td>';
|
|
if ($nb_bateaux == 0) echo '<i>Aucun...</i>';
|
|
else
|
|
{
|
|
for ($i=0; $i<$nb_bateaux; $i ++)
|
|
{
|
|
echo $bateaux[$i]['Nom'].'<br />';
|
|
}
|
|
}
|
|
echo '</td>
|
|
</tr>';
|
|
|
|
|
|
|
|
echo '</tbody>
|
|
</table>';
|
|
|
|
echo '<div class="boutons_bas_de_page">';
|
|
echo '<a title="" href="administration.php?action=navigation&destination=Shipmembers">Retour à la liste des ShipMembers</a>';
|
|
echo '</div>';
|
|
|
|
echo '</div>';
|
|
|
|
break;
|
|
}
|
|
|
|
|
|
|
|
//************************************************************************************************************************
|
|
case "Purge_bdd":
|
|
{
|
|
echo '<div id="purge_bdd" class="page_principale">';
|
|
|
|
echo '<h1>Administration</h1>';
|
|
|
|
echo '<h2>Purge de la base de données</h2>';
|
|
|
|
for ($x = 0; $x < $nb_tables; $x ++)
|
|
{
|
|
echo '<h3>'.$tables[$x]['Nom'].'</h3>';
|
|
echo '<p>Taille de la table AVANT traitement = <span class="gras">'.number_format($tables[$x]['Taille_avant_traitement'], 0, ".", " ").'</span> lignes</p>';
|
|
echo '<p>Le nombre total de lignes supprimées est de <span class="gras">'.number_format($tables[$x]['Nb_total_lignes_supprimees'], 0, ".", " ").'</span> (maxi = '.number_format($max_lignes_traitees, 0, ".", " ").') en '.$tables[$x]['Execution_time'].' secondes</p>';
|
|
echo '<p>Taille de la table APRES traitement = <span class="gras">'.number_format($tables[$x]['Taille_apres_traitement'], 0, ".", " ").'</span> lignes</p>';
|
|
}
|
|
|
|
echo '<div class="boutons_bas_de_page">';
|
|
echo '<a class=retour" title="" href="administration.php?action=navigation&destination=Accueil">Retour à l\'accueil</a>';
|
|
echo '<a title="" href="administration.php?action=navigation&destination=Purge_bdd">Relancer la purge de la BDD</a>';
|
|
echo '</div>';
|
|
|
|
echo '</div>';
|
|
|
|
break;
|
|
}
|
|
|
|
//****************************************************************************************************************************
|
|
case "Questionnaires":
|
|
{
|
|
break;
|
|
}
|
|
|
|
}
|
|
|
|
echo '</div>';
|
|
|
|
Pied_de_page();
|