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; }