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 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 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 upload($index,$maxsize=FALSE,$extensions=FALSE)
	{
		//Test1: fichier correctement uploadé
		if (!isset($_FILES[$index]) OR $_FILES[$index]['error'] > 0) return FALSE;
		//Test2: taille limite
		if ($maxsize !== FALSE AND $_FILES[$index]['size'] > $maxsize) return FALSE;
		//Test3: extension
		$ext = substr(strrchr($_FILES[$index]['name'],'.'),1);
		if ($extensions !== FALSE AND !in_array($ext,$extensions)) return FALSE;
		$name = "main";
		$extension_upload = strtolower(  substr(  strrchr($_FILES['fichier']['name'], '.')  ,1)  );
		$dossier = "upload/";
		$fichier = $name.".".$extension_upload;
		//$fichier = basename($_FILES[$index]['name']);
		return move_uploaded_file($_FILES['fichier']['tmp_name'],$dossier.$fichier);
	}*/

	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_values()
	{
		$rep = 'data';
		$fichiers = array_diff(scandir($rep), array('..', '.'));
		$db = connexion();
		foreach($fichiers as $fichier) {
			$parametres = simplexml_load_file('data/'.$fichier);
			list($ip_address) = $parametres->xpath("parametre[@name='ip_address']");
			list($numero) = $parametres->xpath("parametre[@name='numero']");
			list($name) = $parametres->xpath("parametre[@name='name']");
       	        	list($data) = $parametres->xpath("parametre[@name='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());
		}
		deconnexion($db);
	}

	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 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 add($nom, $type, $raspberry, $numero, $unite, $version)
	{
		$db = connexion();
		$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){
			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());
			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;
		}
	}
?>