function ajouter_inscription($donneesExposant)
{
$bdd = connexionbdd();
/* On crée le nouveau propriétaire dans la bdd*/
$requete = $bdd->prepare('INSERT INTO exposants (firstname, lastname, email, city, cp, country, newsletter, club, marque, model, type, motorisation, immat, date_circu, infos, concours1, concours2, concours3, valid)
VALUES (:firstname, :lastname, :email, :city, :cp, :country, :newsletter, :club, :marque, :model, :type, :motorisation, :immat, :date_circu, :infos, :concours1, :concours2, :concours3, 0)');
try {
$requete->execute($donneesExposant);
} catch (Exception $e) {
throw $e;
}
/* On retourne l'id de l'exposant nouvellement créé dans la table*/
return $bdd->lastInsertId();
}
function common_routeur($conf, $item, $vocables)
{
$final_results = array();
$checktimestamp_log = time();
$item_checked = $item;
$pdo = connexionbdd($conf);
//second we take fails checker ip
$fails = array();
$sql = "SELECT DISTINCT checker_id, checker_ip FROM results WHERE result!=0 AND item=:item";
$statement = $pdo->prepare($sql);
$statement->bindValue(':item', $item);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
$fails[] = $checker;
}
$statement->closeCursor();
$nb_all_fail = $fails;
if (!!$fails) {
//third we take no fails checker ip
$no_fails = array();
$sql = "SELECT DISTINCT checker_id, checker_ip FROM results WHERE result=0 AND item=:item";
$statement = $pdo->prepare($sql);
$statement->bindValue(':item', $item);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
$no_fails[] = $checker;
}
$statement->closeCursor();
//fourth we take lost checker ip
$lost = array();
$sql = "SELECT distinct checker_id, checker_ip\n\t\tFROM results \n\t\tWHERE item = :item\n\t\t\t\tand check_timestamp < :dateMinus3000 \n\t\t\t\tand check_timestamp > :dateMinus6000\n\t\t\t\tand checker_ip NOT IN ( select checker_ip from results where item = :item\n\t\t\t\t\t\t\t\t\t\t\tand check_timestamp < :date \n\t\t\t\t\t\t\t\t\t\t\tand check_timestamp > :dateMinus3000 )";
$statement = $pdo->prepare($sql);
$statement->bindValue(':item', $item);
$statement->bindValue(':dateMinus3000', date('Y-m-d H:i:s', time() - 3000));
$statement->bindValue(':dateMinus6000', date('Y-m-d H:i:s', time() - 6000));
$statement->bindValue(':date', date('Y-m-d H:i:s', time()));
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
$lost[] = $checker;
}
$statement->closeCursor();
// Calculate routeur ip for failed checker
//we initate networks array
$network_for_failed_checker = array();
foreach ($fails as $checker) {
//we try to take route for network from db
$route = "";
$network = netmask($checker['checker_ip'], $conf['minimum_netmask_for_groups']);
$network = $network . "/" . $conf['minimum_netmask_for_groups'];
$sql = "select route from traceroute where check_net = :net";
$statement = $pdo->prepare($sql);
$statement->bindValue(':net', $network);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetch(PDO::FETCH_ASSOC);
$route = $result['route'];
//if we have the traceroute for ip checker
if (!!$result) {
//we first check if network has no no fail ip checkers
$i = 0;
while ($i < count($no_fails) and $network != netmask($no_fails[$i]['checker_ip'], $conf['minimum_netmask_for_groups'])) {
$i++;
}
if ($i == count($no_fails)) {
//if the networks exists in the array, we only add the ip checker to compare to the network key
if (!array_key_exists($network, $network_for_failed_checker)) {
$network_for_failed_checker[$network] = array();
$route = explode(';', $result['route']);
$ip_routeurs = array();
foreach ($route as $routeur) {
$ip_routeurs[] = explode(';', $routeur);
}
$network_for_failed_checker[$network]['route'] = $ip_routeurs;
$network_for_failed_checker[$network]['nb_fail'] = 1;
} else {
$network_for_failed_checker[$network]['nb_fail'] = $network_for_failed_checker[$network]['nb_fail'] + 1;
}
}
} else {
//one must add the road
$routes = shell_exec("traceroute -nI " . $checker['checker_ip']);
$routes = explode("\n", $routes);
array_splice($routes, 0, 1);
$final_route = array();
foreach ($routes as $route) {
$route_explode = explode(' ', $route);
if (count($route_explode) > 1) {
$final_route[] = $route_explode[3];
}
}
$net_with_mask = $network;
$sql = "INSERT INTO traceroute (check_net,route,timestamp) VALUES (:check,:route,:time)";
$statement = $pdo->prepare($sql);
$statement->bindValue(':check', $net_with_mask);
$statement->bindValue(':route', implode(';', $final_route));
$statement->bindValue(':time', date('Y-m-d H:i:s', time()));
if (!$statement->execute()) {
throw new PDOException();
}
}
}
$routeurs_failed = array();
if (count($network_for_failed_checker) > 1) {
$network_keys = array_keys($network_for_failed_checker);
foreach ($network_keys as $net) {
$network_1 = $network_for_failed_checker[$net];
$networks_left = $network_keys;
$networks_left = array_slice($networks_left, 0, array_search($net, $networks_left));
foreach ($networks_left as $net_compare) {
$network_2 = $network_for_failed_checker[$net_compare];
if ($network_1 != $network_2) {
$trouver = false;
$i = count($network_1['route']) - 1;
while ($i >= 0 and !$trouver) {
$j = count($network_2['route']) - 1;
while ($j >= 0 and $network_1['route'][$i] != $network_2['route'][$j]) {
$j--;
}
if ($j >= 0) {
$trouver = true;
if (!array_key_exists($network_1['route'][$i], $routeurs_failed)) {
$routeurs_failed[$network_1['route'][$i]] = array($network_1['nb_fail'], $network_2['nb_fail']);
} else {
echo "<p>" . $network_1['route'][$i] . "</p>";
$routers_failed[$network_1['route'][$i]][] = $network_1['nb_fail'];
$routers_failed[$network_1['route'][$i]][] = $network_2['nb_fail'];
}
}
$i--;
}
}
}
}
} else {
foreach ($network_for_failed_checker as $network_1) {
$routeur = $network_1['route'][count($network_1['route']) - 1][0];
$routeurs_failed = array($routeur => array($network_1['nb_fail']));
}
}
$routeurs_for_display = array();
foreach ($routeurs_failed as $routeur => $fails) {
$total_fails_for_routeur = 0;
foreach ($fails as $nb_fails) {
$total_fails_for_routeur = $total_fails_for_routeur + $nb_fails;
}
$routeurs_for_display[] = array('routeur' => $routeur, 'nb_fail' => $total_fails_for_routeur);
}
$nb_all = count($nb_all_fail);
if (count($routeurs_for_display) > 0 && $conf['active_logstalgia'] == 1) {
$final_results[] = array('factors' => $routeurs_for_display, 'type_of_checker' => 'failed');
foreach ($routeurs_for_display as $routeur) {
$item_log_explode = explode(":", $item_checked);
$size = $routeur['nb_fail'] * 100 / $nb_all * $conf['size_super_ball'];
$item_log = time() . "|" . $routeur['routeur'] . "|" . $item_log_explode[0] . "|" . "Fail" . "|" . $size . "|" . "0" . "|" . "FF0000" . "\n";
file_put_contents($conf['directory_tmp'] . "logstalgia.txt", $item_log, FILE_APPEND);
}
}
// Calculate routeurs ip for lost checker
//we initate networks array
$network_for_lost_checker = array();
foreach ($lost as $checker) {
//we try to take route for network from db
$route = "";
$network = netmask($checker['checker_ip'], $conf['minimum_netmask_for_groups']);
$sql = "SELECT route FROM traceroute WHERE check_net=:check and check_timestamp > :time";
$statement = $pdo->prepare($sql);
$statement->bindValue(':check', $network);
$statement->bindValue(':time', date('Y-m-d H:i:s', time()));
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetch(PDO::FETCH_ASSOC);
//if we have the traceroute for ip checker
if (!!$result) {
//we first check if network has no no fail ip checkers
$i = 0;
while ($i < count($no_fails) and $network != netmask($no_fails[$i]['checker_ip'], $conf['minimum_netmask_for_groups'])) {
$i++;
}
if ($i == count($no_fails)) {
$i = 0;
while ($i < count($fails) and $network != netmask($fails[$i]['checker_ip'], $conf['minimum_netmask_for_groups'])) {
$i++;
}
if ($i == count($fails)) {
//if the networks exists in the array, we only add the ip checker to compare to the network key
if (!array_key_exists($network, $network_for_lost_checker)) {
$network_for_lost_checker[$network] = array();
$route = explode(';', $result['route']);
$ip_routeurs = array();
foreach ($route as $routeur) {
$ip_routeurs = explode(';', $routeur);
}
$network_for_lost_checker[$network]['route'] = $ip_routeurs;
$network_for_lost_checker[$network]['nb_fail'] = 1;
} else {
$network_for_lost_checker[$network]['nb_fail'] = $network_for_lost_checker[$network]['nb_fail'] + 1;
}
}
}
}
}
$routers_lost = array();
if (count($network_for_lost_checker) > 1) {
$network_keys = array_keys($network_for_lost_checker);
foreach ($network_keys as $net) {
$network_1 = $network_for_lost_checker[$net];
$networks_left = $network_keys;
$networks_left = array_slice($networks_left, 0, array_search($net, $networks_left));
foreach ($networks_left as $net_compare) {
$network_2 = $network_for_lost_checker[$net_compare];
if ($network_1 != $network_2) {
$trouver = false;
$i = count($network_1['route']) - 1;
while ($i >= 0 and !$trouver) {
$j = count($network_2['route']) - 1;
while ($j >= 0 and $network_1['route'][$i] != $network_2['route'][$j]) {
$j--;
}
if ($j >= 0) {
$trouver = true;
if (!array_key_exists($network_1['route'][$i], $routeurs_failed)) {
$routeurs_failed[$network_1['route'][$i]] = array($network_1['nb_fail'], $network_2['nb_fail']);
} else {
echo "<p>" . $network_1['route'][$i] . "</p>";
$routers_failed[$network_1['route'][$i]][] = $network_1['nb_fail'];
$routers_failed[$network_1['route'][$i]][] = $network_2['nb_fail'];
}
}
$i--;
}
}
}
}
} else {
//problem, on doit choper le seul element pour prendre son routeur le plus à gauche
foreach ($network_for_lost_checker as $network_1) {
$routeur = $network_1['route'][count($network_1['route']) - 1][0];
$routeurs_lost = array($routeur => $network_1['nb_fail']);
}
}
$routeurs_for_lost_display = array();
foreach ($routers_lost as $routeur) {
$total_lost_for_routeur = 0;
foreach ($routeur as $nb_lost) {
$total_lost_for_routeur = $total_lost_for_routeur + $nb_lost;
}
$routeurs_for_lost_display[] = array('routeur' => $routeur, 'nb_lost' => $total_lost_for_routeur);
}
if (count($routeurs_for_lost_display) > 0 && $conf['active_logstalgia'] == 1) {
$final_results[] = array('factors' => $routeurs_for_lost_display, 'type_of_checker' => 'lost');
foreach ($routeurs_for_lost_display as $routeur) {
$item_log_explode = explode(":", $item_checked);
$size = $routeur['nb_lost'] * 100 / $nb_all * $conf['size_super_ball'];
$item_log = time() . "|" . $routeur['routeur'] . "|" . $item_log_explode[0] . "|" . "Fail" . "|" . $size . "|" . "0" . "|" . "FF0000" . "\n";
file_put_contents($conf['directory_tmp'] . "logstalgia.txt", $item_log, FILE_APPEND);
}
}
}
return $final_results;
}
<?php
include_once 'bdd.php';
$bdd = connexionbdd();
$stats = get_last();
$i = 0;
$data = [];
foreach ($stats as $key => $value) {
//$data[$value['date']] = $value['places'];
$data['date'][$i] = $value['date'];
$data['places'][$i] = $value['places'];
$data['velos'][$i] = $value['velos'];
$i++;
}
echo json_encode($stats);
<?php
include 'header.php';
//=== Connexion BDD
connexionbdd();
//=== On teste si le visiteur a soumis le formulaire
if (isset($_POST['submit']) && $_POST['submit'] == 'submit') {
//=== Système de Captcha
require_once 'includes/recaptchalib.php';
$privatekey = "ELLE DOIT RESTEE PRIVEE";
$resp = recaptcha_check_answer($privatekey, $_SERVER["REMOTE_ADDR"], $_POST["recaptcha_challenge_field"], $_POST["recaptcha_response_field"]);
//Si le Captcha est incorrect
if (!$resp->is_valid) {
$informations = "<p style='font-weight: bold; color: red; padding-left: 30px'>Erreur: Le Captcha est incorrect !</p>";
} else {
//=== Récupération valeur formulaire
$pseudo = mysql_real_escape_string($_POST['pseudo']);
$password = mysql_real_escape_string(md5($_POST['password']));
$mail = mysql_real_escape_string($_POST['mail']);
//=== Vérification pseudo déjà utilisé
$sql = 'SELECT count(*) FROM Membres WHERE pseudo="' . $pseudo . '"';
$req = mysql_query($sql) or die('Erreur SQL :/>' . $sql . '<br />' . mysql_error());
$data = mysql_fetch_array($req);
if ($data[0] == 0) {
//=== Vérification e-mail déjà utilisé
$sql1 = 'SELECT count(*) FROM Membres WHERE mail="' . $mail . '"';
$req1 = mysql_query($sql1) or die('Erreur SQL :/>' . $sql1 . '<br />' . mysql_error());
$data1 = mysql_fetch_array($req1);
if ($data1[0] == 0) {
//=== Inscription dans BDD
$sql2 = 'INSERT INTO Membres VALUES("", "' . $pseudo . '", "' . $password . '", "' . $mail . '", "0", CURDATE(), "1", "", "")';
function save_all_result()
{
$array_failed = array();
$array_failed_all = array();
$array_failed_match = array();
$array_failed_proxy = array();
$array_failed_latency = array();
$array_success = array();
$array_all = array();
$checktimestamp = date('Y/m/d H:i:s', time());
$array_failed = array();
//Storage database
$pdo = connexionbdd();
/*******
*
* Listing all the possible facts
*
*******/
//Table of people who missed an all item
$sql = "SELECT DISTINCT checker_id, item FROM results WHERE result=1 OR result=2 OR result=3 OR result=4";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
if ($checker['item'] != "") {
$array_failed_all[] = $checker['item'];
}
}
$array_count_failed_all = array_count_values($array_failed_all);
//Table of people who missed an item
$sql = "SELECT DISTINCT checker_id, item FROM results WHERE result=1";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
if ($checker['item'] != "") {
$array_failed[] = $checker['item'];
}
}
$array_count_failed = array_count_values($array_failed);
//Table of people who missed an item on the match
$sql = "SELECT DISTINCT checker_id, item FROM results WHERE result=2";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
if ($checker['item'] != "") {
$array_failed_match[] = $checker['item'];
}
}
$array_count_failed_match = array_count_values($array_failed_match);
//Table of people who missed an item on the proxy
$sql = "SELECT DISTINCT checker_id, item FROM results WHERE result=3";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
if ($checker['item'] != "") {
$array_failed_proxy[] = $checker['item'];
}
}
$array_count_failed_proxy = array_count_values($array_failed_proxy);
//Table of people who missed an item on the latency
$sql = "SELECT DISTINCT checker_id, item FROM results WHERE result=4";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
if ($checker['item'] != "") {
$array_failed_latency[] = $checker['item'];
}
}
$array_count_failed_latency = array_count_values($array_failed_latency);
/***/
/*******
*
* Listing items
*
*******/
$sql = "SELECT item from items";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $item) {
$array_item[] = $item;
}
$statement->closeCursor();
/***/
/*******
*
* Listing of people who tested an item
*
*******/
foreach ($array_item as $value) {
$sql = "SELECT count(DISTINCT checker_id)AS total,item FROM results WHERE item=:item";
$statement = $pdo->prepare($sql);
$statement->bindValue(':item', $value['item']);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $item) {
if ($item['item'] != NULL && $item['item'] != "") {
$array_all[] = $item;
}
}
}
$statement->closeCursor();
/***/
/*******
*
* Treatment and return
*
*******/
$result = array();
foreach ($array_all as $val) {
$glob_timeout = $glob_proxy = $glob_latency = $glob_match = 0;
if (array_key_exists($val['item'], $array_count_failed_all)) {
$glob = $array_count_failed_all[$val['item']] * 100 / $val['total'];
}
if (array_key_exists($val['item'], $array_count_failed)) {
$glob_timeout = $array_count_failed[$val['item']] * 100 / $val['total'];
}
if (array_key_exists($val['item'], $array_count_failed_proxy)) {
$glob_proxy = $array_count_failed_proxy[$val['item']] * 100 / $val['total'];
}
if (array_key_exists($val['item'], $array_count_failed_latency)) {
$glob_latency = $array_count_failed_latency[$val['item']] * 100 / $val['total'];
}
if (array_key_exists($val['item'], $array_count_failed_match)) {
$glob_match = $array_count_failed_match[$val['item']] * 100 / $val['total'];
}
if (array_key_exists($val['item'], $array_count_failed_all)) {
$result[] = array("md5" => md5($val['item']), "nb_f" => $array_count_failed_all[$val['item']], "nb_s" => $val['total'] - $array_count_failed_all[$val['item']], "item" => $val['item'], "glob" => $glob, "glob_timeout" => $glob_timeout, "glob_proxy" => $glob_proxy, "glob_latency" => $glob_latency, "glob_match" => $glob_match);
} else {
$result[] = array("md5" => md5($val['item']), "nb_f" => "0", "nb_s" => $val['total'], "item" => $val['item'], "glob" => "0", "glob_timeout" => "0", "glob_proxy" => "0", "glob_latency" => "0", "glob_match" => "0");
}
}
foreach ($result as $value) {
$sql2 = "INSERT INTO global_result (item,nb_f,nb_s,md5,glob,timestamp,glob_timeout,glob_proxy,glob_latency,glob_match) VALUES (:item,:nbf,:nbs,:md5,:glob,:timestamp,:globtimeout,:globproxy,:globlatency,:globmatch);";
$statement = $pdo->prepare($sql2);
$statement->bindValue(':item', $value["item"]);
$statement->bindValue(':nbf', $value["nb_f"]);
$statement->bindValue(':nbs', $value["nb_s"]);
$statement->bindValue(':md5', $value["md5"]);
$statement->bindValue(':glob', $value["glob"]);
$statement->bindValue(':timestamp', $checktimestamp);
$statement->bindValue(':globtimeout', $value["glob_timeout"]);
$statement->bindValue(':globproxy', $value["glob_proxy"]);
$statement->bindValue(':globlatency', $value["glob_latency"]);
$statement->bindValue(':globmatch', $value["glob_match"]);
if (!$statement->execute()) {
throw new PDOException();
}
$statement->closeCursor();
}
/***/
}
}
date_default_timezone_set('Europe/Berlin');
$md5 = trim(htmlspecialchars($_POST['md5']));
/*
* Function to connect to db
*/
function connexionbdd($conf)
{
try {
return new PDO('mysql:host=' . $conf['host_db'] . ';port=3306;dbname=' . $conf['bdd_db'], $conf['user_db'], $conf['password_db']);
} catch (Exception $e) {
echo 'Erreur : ' . $e->getMessage() . '<br />';
echo 'N° : ' . $e->getCode();
}
}
$pdo = connexionbdd($conf);
/*******
*
* Recovery of the item's name
*
*******/
$sql = "SELECT * FROM items WHERE hash=:md5";
$statement = $pdo->prepare($sql);
$statement->bindValue(':md5', $md5);
if (!$statement->execute()) {
throw new PDOException();
}
$result_item = $statement->fetchAll(PDO::FETCH_ASSOC);
$statement->closeCursor();
/***/
/*******
if ($argc < 2) {
echo "Missing two arguments: mysql root password and hostname or IP accessing this database \n";
exit(0);
} else {
if ($argc < 3) {
echo "Missing argument : mysql root password or hostname or IP accessing this database \n";
exit(0);
} else {
if ($argc > 3) {
echo "Too many arguments , only two are required. mysql root password or hostname or IP accessing this database \n";
exit(0);
}
}
}
$db_name = $conf['bdd_db'];
$pdo = connexionbdd($conf, $argv[1], $argv[2]);
echo "Starting install new db and tables\n";
$create_db = "CREATE DATABASE IF NOT EXISTS {$db_name}";
$statement = $pdo->prepare($create_db);
if (!$statement->execute()) {
echo "Error creating new database\n";
throw new PDOException();
} else {
echo "Database created\n";
}
$change_db = "USE {$db_name}";
$statement = $pdo->prepare($change_db);
if (!$statement->execute()) {
echo "Error using new database\n";
throw new PDOException();
} else {
function check_routes()
{
global $conf;
$pdo = connexionbdd();
//outskirts of recovering all the testers
$checkers = array();
$sql = "SELECT DISTINCT checker_id, checker_ip FROM results";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
$checkers[] = $checker;
}
$statement->closeCursor();
//var_dump($checkers);
//then retrieves all roads
$networks = array();
$sql = "SELECT check_net,timestamp FROM traceroute";
$statement = $pdo->prepare($sql);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $network) {
$networks[] = $network;
}
//var_dump($networks);
foreach ($checkers as $checker) {
//echo $checker['checker_ip']."\n";
$checker_network = netmask($checker['checker_ip'], $conf['minimum_netmask_for_groups']);
//echo $checker_network."\n";
$network_find = false;
$i = 0;
while ($i < count($networks) and !$network_find) {
if ($checker_network . "/" . $conf['minimum_netmask_for_groups'] == $networks[$i]['check_net']) {
$network_find = true;
} else {
$i++;
}
}
if (!$network_find) {
$routes = shell_exec("traceroute -nI " . $checker['checker_ip']);
$routes = explode("\n", $routes);
array_splice($routes, 0, 1);
$final_route = array();
foreach ($routes as $route) {
$route_explode = explode(' ', $route);
if (count($route_explode) > 1) {
$final_route[] = $route_explode[3] . ':' . $route_explode[5];
}
}
$net_with_mask = $checker_network . "/" . $conf['minimum_netmask_for_groups'];
//echo $net_with_mask;
$sql = "INSERT INTO traceroute (check_net,route,timestamp) VALUES (:check,:route,:time)";
$statement = $pdo->prepare($sql);
$statement->bindValue(':check', $net_with_mask);
$statement->bindValue(':route', implode(';', $final_route));
$statement->bindValue(':time', date('Y-m-d H:i:s', time()));
if (!$statement->execute()) {
throw new PDOException();
}
$networks[] = array('check_net' => $net_with_mask, 'route' => implode(';', $final_route), 'timestamp' => date('Y-m-d H:i:s', time()));
} else {
if ($networks[$i]['timestamp'] < time() - $conf['maximum_route_time_validaty'] * 3600000) {
$routes = shell_exec("traceroute -nI " . $checker['checker_ip']);
$routes = explode("\n", $routes);
array_splice($routes, 0, 1);
$final_route = array();
foreach ($routes as $route) {
$route_explode = explode(' ', $route);
if (count($route_explode) > 1) {
$final_route[] = $route_explode[3];
}
}
$sql = "update traceroute set route = :route, timestamp = :time where check_net = :check";
$statement = $pdo->prepare($sql);
$statement->bindValue(':check', $checker_network . "/" . $conf['minimum_netmask_for_groups']);
$statement->bindValue(':route', implode(';', $final_route));
$statement->bindValue(':time', date('Y-m-d H:i:s', time()));
if (!$statement->execute()) {
throw new PDOException();
}
}
}
}
}
* http://www.gnu.org/licenses/gpl-3.0.en.html
*/
$md5 = trim(htmlspecialchars($_GET['md']));
require "/etc/decentralized_monitoring/config.conf";
function connexionbdd()
{
global $conf;
try {
return new PDO('mysql:host=' . $conf['host_db'] . ';port=3306;dbname=' . $conf['bdd_db'], $conf['user_db'], $conf['password_db']);
} catch (Exception $e) {
echo 'Erreur : ' . $e->getMessage() . '<br />';
echo 'N° : ' . $e->getCode();
}
}
//Storage database
$pdo = connexionbdd();
//Table of people who missed an item
$item = "";
$sql = "SELECT item FROM items WHERE hash=:md5";
$statement = $pdo->prepare($sql);
$statement->bindValue(':md5', $md5);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
if (isset($result[0])) {
$item = $result[0]['item'];
}
$statement->closeCursor();
$from = 0;
$to = 0;
function common_routeur($conf, $item_md5)
{
//first we connect to database
$pdo = connexionbdd($conf);
$sql = "SELECT item from global_result WHERE md5=:md5";
$statement = $pdo->prepare($sql);
$statement->bindValue(':md5', $item_md5);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetch(PDO::FETCH_ASSOC);
$item = $result['item'];
$statement->closeCursor();
//second we take fails checker ip
$fails = array();
$sql = "SELECT DISTINCT checker_id, checker_ip FROM results WHERE result!=0 AND item=:item";
$statement = $pdo->prepare($sql);
$statement->bindValue(':item', $item);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
$fails[] = $checker;
}
$statement->closeCursor();
$nb_all_fail = $fails;
//third we take no fails checker ip
$no_fails = array();
$sql = "SELECT DISTINCT checker_id, checker_ip FROM results WHERE result=0 AND item=:item";
$statement = $pdo->prepare($sql);
$statement->bindValue(':item', $item);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $checker) {
$no_fails[] = $checker;
}
$statement->closeCursor();
$nb_all_no_fail = $no_fails;
// Calculate routeur ip for failed checker
//we initate networks array
$network_for_failed_checker = array();
foreach ($fails as $checker) {
//we try to take route for network from db
$route = "";
$network = netmask($checker['checker_ip'], $conf['minimum_netmask_for_groups']);
$network = $network . "/" . $conf['minimum_netmask_for_groups'];
$sql = "select route from traceroute where check_net = :network";
$statement = $pdo->prepare($sql);
$statement->bindValue(':network', $network);
if (!$statement->execute()) {
throw new PDOException();
}
$result = $statement->fetch(PDO::FETCH_ASSOC);
$route = $result['route'];
//if we have the traceroute for ip checker
if (!!$result) {
//we first check if network has no no fail ip checkers
$i = 0;
while ($i < count($no_fails) and $network != netmask($no_fails[$i]['checker_ip'], $conf['minimum_netmask_for_groups'])) {
$i++;
}
if ($i == count($no_fails)) {
//if the networks exists in the array, we only add the ip checker to compare to the network key
if (!array_key_exists($network, $network_for_failed_checker)) {
$network_for_failed_checker[$network] = array();
$route = explode(';', $result['route']);
$ip_routeurs = array();
foreach ($route as $routeur) {
$ip_routeurs[] = explode(';', $routeur);
}
$network_for_failed_checker[$network]['route'] = $ip_routeurs;
$network_for_failed_checker[$network]['nb_fail'] = 1;
} else {
$network_for_failed_checker[$network]['nb_fail'] = $network_for_failed_checker[$network]['nb_fail'] + 1;
}
}
} else {
//one must add the road
$routes = shell_exec("traceroute -nI " . $checker['checker_ip']);
$routes = explode("\n", $routes);
array_splice($routes, 0, 1);
$final_route = array();
foreach ($routes as $route) {
$route_explode = explode(' ', $route);
if (count($route_explode) > 1) {
$final_route[] = $route_explode[3];
}
}
$net_with_mask = $checker_network . "/" . $conf['minimum_netmask_for_groups'];
$sql = "INSERT INTO traceroute (check_net,route,timestamp) VALUES (:check,:route,:time)";
$statement = $pdo->prepare($sql);
$statement->bindValue(':check', $net_with_mask);
$statement->bindValue(':route', implode(';', $final_route));
$statement->bindValue(':time', date('Y-m-d H:i:s', time()));
if (!$statement->execute()) {
throw new PDOException();
}
}
}
$routeurs_failed = array();
if (count($network_for_failed_checker) > 1) {
$network_keys = array_keys($network_for_failed_checker);
foreach ($network_keys as $net) {
$network_1 = $network_for_failed_checker[$net];
$networks_left = $network_keys;
$networks_left = array_slice($networks_left, 0, array_search($net, $networks_left));
foreach ($networks_left as $net_compare) {
$network_2 = $network_for_failed_checker[$net_compare];
if ($network_1 != $network_2) {
$trouver = false;
$i = count($network_1['route']) - 1;
while ($i >= 0 and !$trouver) {
$j = count($network_2['route']) - 1;
while ($j >= 0 and $network_1['route'][$i] != $network_2['route'][$j]) {
$j--;
}
if ($j >= 0) {
$trouver = true;
if (!array_key_exists($network_1['route'][$i], $routeurs_failed)) {
$routeurs_failed[$network_1['route'][$i]] = array($network_1['nb_fail'], $network_2['nb_fail']);
} else {
echo "<p>" . $network_1['route'][$i] . "</p>";
$routers_failed[$network_1['route'][$i]][] = $network_1['nb_fail'];
$routers_failed[$network_1['route'][$i]][] = $network_2['nb_fail'];
}
}
$i--;
}
}
}
}
} else {
foreach ($network_for_failed_checker as $network_1) {
$routeur = $network_1['route'][count($network_1['route']) - 1][0];
$routeurs_failed = array($routeur => array($network_1['nb_fail']));
}
}
$routeurs_for_display = array();
foreach ($routeurs_failed as $routeur => $fails) {
$total_fails_for_routeur = 0;
foreach ($fails as $nb_fails) {
$total_fails_for_routeur = $total_fails_for_routeur + $nb_fails;
}
$routeurs_for_display[] = array('routeur' => $routeur, 'nb_fail' => $total_fails_for_routeur);
}
$nb_all = count($nb_all_fail) + count($nb_all_no_fail);
$return_final = $routeurs_for_display;
return $return_final;
}
