site/accesBase.php

<?php
	function connexion()
	{
		$machine = 'localhost' ;
		$user='grouille' ;
		$pwd='postgres' ;
		$db='projetCapteurs' ;
		$link = pg_connect("host=$machine user=$user password=$pwd dbname=$db") or die('Erreur de Connection !<br />'.pg_last_error()) ;
		return $link ;
	}

	function deconnexion($db)
	{
		pg_close($db);
	}
	
	function verifPwd($id, $pwd)
	{
		$db = connexion();
		$query = "SELECT pwd FROM membres WHERE identifiant = '$id'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$pass = pg_fetch_assoc($result);
		deconnexion($db);
		return(password_verify($pwd, $pass['pwd']));
	}
	
	function notAMember($id)
	{
		$db = connexion();
		$query = "SELECT * FROM membres WHERE identifiant = '$id'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		if (pg_affected_rows($result)!=0){
			return False;
		}
		else{
			return True;
		}
	}
	
	function countMember()
	{
		$db = connexion();
		$query = "SELECT * FROM membres";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		return pg_affected_rows($result);
	}

	function members()
	{
		$db = connexion();
		$query = "SELECT * FROM membres";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_all($result);
		return $tab;
	}
	
	function inscription($id, $pwd, $nom, $prenom, $groupe)
	{
		$db = connexion();
		$num = countMember() +1;
		$pwd_hache = password_hash($pwd, PASSWORD_DEFAULT);
		$query = "INSERT INTO membres VALUES('$num', '$nom', '$prenom', '$pwd_hache', '$id', '$groupe')";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		if (pg_affected_rows($result)!=0){
			return True;
		}
		else{
			return False;
		}
	}

	function suppression($id)
	{
		$db = connexion();
		$query = "DELETE FROM membres WHERE identifiant = '$id'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		if (pg_affected_rows($result)!=0){
			return True;
		}
		else{
			return False;
		}
	}

	function getGroupe($id)
	{
		$db = connexion();
		$query = "SELECT groupe FROM membres where identifiant = '$id'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		deconnexion($db);
		return $tab['groupe'];
	}

	function groupeId($id)
	{
		$db = connexion();
		$query = "SELECT groupe FROM groupes where id = '$id'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		deconnexion($db);
		return $tab['groupe'];
	}

	function getGroupes()
	{
		$db = connexion();
		$query = "SELECT * FROM groupes";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_all($result);
		deconnexion($db);
		return $tab;
	}
	
	function getMembreByIdPwd($id, $pwd)
	{
		$db = connexion();
		$query = "SELECT nom, prenom, pwd FROM membres WHERE identifiant = '$id'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		if(password_verify($pwd, $tab['pwd']))
			return $tab;
	}
	
	function getSensors()
	{
		$db = connexion();
		$query = "SELECT * FROM capteurs";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_all($result);
		return $tab;
	}

	function load($tmp_name, $name)
	{
		$dossier = "upload/";
		$fichier = "main.c";
		$taille_max = 100000;
		$taille = filesize($tmp_name);
		$extensions = Array('.c');
		$extension = strrchr($name, '.');
		if(in_array($extension, $extensions) && taille <= taille_max)
		{
			return move_uploaded_file($tmp_name, $dossier.$fichier);
		}
		return False;
	}

	function update_bdd($ip_address, $numero, $data, $date)
	{
		$name = getSensorName($ip_address, $numero);
		$db = connexion();
		$ip_address = (string)$ip_address;
		$numero = (int)$numero;
		$data =  (float)$data;
		$query = "UPDATE capteurs SET value=$data WHERE raspberry='$ip_address' and numero=$numero and nom='$name'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$query = "INSERT INTO history VALUES('$name', '$data', '$date')";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		deconnexion($db);
	}

	function date_min()
	{
		$db = connexion();
		$query = "SELECT MIN(date) FROM history";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$min = pg_fetch_assoc($result);
		return $min;
	}

	function date_max()
        {
                $db = connexion();
                $query = "SELECT MAX(date) FROM history";
                $result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
                $max = pg_fetch_assoc($result);
                return $max;
        }

	function getHistory($name, $type, $raspberry, $start, $end, $nombre)
	{
		$db = connexion();
		$query = "SELECT capteurs.nom as name, capteurs.raspberry as raspberry, capteurs.type as type, history.value as value, history.date as date, capteurs.unite as unite ";
		$query = $query."FROM capteurs, history ";
		$query = $query."WHERE capteurs.nom = history.sensor ";
		if($name != "") $query = $query."AND history.sensor = '$name' ";
		if($type != "")  $query = $query."AND capteurs.type = '$type' ";
		if($raspberry != "")  $query = $query."AND capteurs.raspberry = '$raspberry' ";
		$query = $query."AND (history.date BETWEEN '$start' AND '$end') ORDER BY date DESC LIMIT $nombre";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
                $tab = pg_fetch_all($result);
		deconnexion($db);
		return $tab;
	}

	function get($parametre)
	{
		$db = connexion();
		$query = "SELECT DISTINCT $parametre from capteurs ORDER BY $parametre";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_all($result);
		deconnexion($db);
		return $tab;
	}

	function getSensorName($raspberry, $numero)
	{
		$db = connexion();
		$query = "SELECT nom FROM capteurs where raspberry = '$raspberry' AND numero = $numero";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		$tab =  pg_fetch_assoc($result);
		deconnexion($db);
		return $tab['nom'];
	}

	function getSensorVersion($raspberry, $numero)
	{
		$db = connexion();
		$query = "SELECT version FROM capteurs where raspberry = '$raspberry' AND numero = $numero";
		$result = pg_query($db, $query) or die("La requète a echoué : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		deconnexion($db);
		return (int)$tab['version'];
	}

	function getVersion($nom)
	{
		$db = connexion();
		$query = "SELECT version FROM capteurs where nom = '$nom'";
		$result = pg_query($db, $query) or die("La requète a echoué : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		deconnexion($db);
		return (int)$tab['version'];
	}

	function getRaspberrys()
	{
		$db = connexion();
		$query = "SELECT * FROM raspberry";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		$tab = pg_fetch_all($result);
		deconnexion($db);
		return $tab;
	}

	function getSetup($raspberry)
	{
		$db = connexion();
		$query = "SELECT setup FROM raspberry WHERE ip = '$raspberry'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                $tab = pg_fetch_assoc($result);
                deconnexion($db);
                return $tab['setup'];
	}

	function setup($raspberry)
	{
		$db = connexion();
		$query = "UPDATE raspberry SET setup = 0 WHERE ip = '$raspberry'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                deconnexion($db);
	}

	function addRpi($ip)
	{
		$raspberry = "172.26.145.".$ip;
		$db = connexion();
		$query = "SELECT * FROM raspberry WHERE ip = '$raspberry'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		if (pg_affected_rows($result)!=0)
		{
			deconnexion($db);
			return False;
		}
		else{
			$query = "INSERT INTO raspberry VALUES('$raspberry', 1)";
			$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
			deconnexion($db);
			return pg_affected_rows($result)==1;
		}
	}

	function removeRpi($ip)
	{
		$db = connexion();
		$query = "SELECT nom FROM capteurs WHERE raspberry = '$ip'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		$capteurs = pg_fetch_all($result);
		foreach($capteurs as $capteur)
		{
			remove($capteur['nom']);
		}
		$db = connexion();
		$query = "DELETE FROM raspberry WHERE ip = '$ip'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		deconnexion($db);
		if (pg_affected_rows($result)!=0){
		   return True;
		}
		else{
			return False;
		}
	}

	function add($nom, $type, $raspberry, $unite, $version)
	{
		$db = connexion();
		$query = "SELECT MAX(numero) as numero FROM capteurs WHERE raspberry = '$raspberry'";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		$numero = $tab['numero'] +1;
		if($numero > 4) return False;
		$query = "SELECT * FROM capteurs WHERE nom = '$nom' OR (raspberry = '$raspberry' AND numero = '$numero')";
		$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
		if (pg_affected_rows($result)!=0){
			deconnexion($db);
			return False;
		}
		else
		{
			$query = "INSERT INTO capteurs VALUES($numero, '$nom', '$raspberry', '$type', 0, '$unite', $version)";
			$result = pg_query($db, $query) or die("La requête a echoué : ".pg_last_error());
			deconnexion($db);
			if (pg_affected_rows($result)!=0){
			   return True;
			}
			else{
				return False;
			}
		}
	}

	function remove($nom)
	{
		$db = connexion();
		$query = "DELETE FROM history WHERE sensor = '$nom'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		$query = "DELETE FROM capteurs WHERE nom = '$nom'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		deconnexion($db);
		if (pg_affected_rows($result)!=0){
		   return True;
		}
		else{
			return False;
		}
	}

	function makeArray($tab, $column)
	{
		foreach($tab as $element)
		{
			$array[] = $element[$column];
		}
		return $array;
	}

	function getLegend($nom)
	{
		$db = connexion();
		$query = "SELECT type, unite FROM capteurs WHERE nom = '$nom'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
		$tab = pg_fetch_assoc($result);
		deconnexion($db);
		return $tab;
	}

	function historyExist($sensor)
	{
		$db = connexion();
		$query = "SELECT * FROM history WHERE sensor = '$sensor'";
                $result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                deconnexion($db);
		if(pg_affected_rows($result)>1)
		{
			return True;
		}
		else
		{
			return False;
		}
	}

	function getNumero($nom)
	{
		$db = connexion();
		$query = "SELECT numero FROM capteurs WHERE nom = '$nom'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                $tab = pg_fetch_assoc($result);
                deconnexion($db);
                return $tab['numero'];
	}

	function getRaspberry($nom)
	{
		$db = connexion();
		$query = "SELECT raspberry FROM capteurs WHERE nom = '$nom'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                $tab = pg_fetch_assoc($result);
                deconnexion($db);
                return $tab['raspberry'];
	}

	function getSensorByRaspberry($ip)
	{
		$db = connexion();
		$query = "SELECT * FROM capteurs WHERE raspberry = '$ip'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                $tab = pg_fetch_all($result);
                deconnexion($db);
                return $tab;
	}

	function getTime($type)
	{
		$db = connexion();
		$query = "SELECT temps FROM types WHERE nom = '$type'";
		$result = pg_query($db, $query) or die("La requ  te a echou   : ".pg_last_error());
                $tab = pg_fetch_assoc($result);
                deconnexion($db);
                return $tab['temps'];
	}
?>