<?php
	function connexion()
	{
		$machine='serveur-etu.polytech-lille.fr' ;
		$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)
	{
		$db = connexion();
		$num = countMember() +1;
		$pwd_hache = password_hash($pwd, PASSWORD_DEFAULT);
		$query = "INSERT INTO membres VALUES('$num', '$nom', '$prenom', '$pwd_hache', '$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 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 = 'binaire';
		$extension_upload = strtolower(  substr(  strrchr($_FILES['fichier']['name'], '.')  ,1)  );
		$fichier = "upload/{$name}.{$extension_upload}";
		//$fichier = basename($_FILES[$index]['name']);
		return move_uploaded_file($_FILES['fichier']['tmp_name'],$fichier);
	}

	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, $name, $data, $date)
	{
		$db = connexion();
		$ip_address = (string)$ip_address;
		$numero = (int)$numero;
		$name = (string)$name;
		$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 add($nom, $type, $raspberry, $numero, $unite)
	{
		$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')";
			$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;
			}
		}
	}	
?>