<?php

class User{
	
	private $db_table = "users_student";
	
	public function isLoginExist($username, $password, $bdd){						
		try{
			$sql  = 'SELECT * FROM users_student WHERE username = :username  Limit 1';
			//$sql  = 'SELECT matricule, email FROM users_student WHERE username = :username  AND password = :password  Limit 1';
			$statement = $bdd->prepare($sql);
			$statement->bindParam(':username', 		$username, 		PDO::PARAM_STR);
			$statement->execute();
			return $statement;
		}catch(PDOException  $e ){
			echo $e->getMessage( );
		}
	}

	public function isLoginRExist($username, $password, $bdd){	
		$type_user1 = 1;	
		$type_user2 = 2;	
		try{
			$sql  = 'SELECT matricule FROM users WHERE username = :username  AND password = :password AND (type_user = :type_user1 OR type_user = :type_user2) Limit 1';
			$statement = $bdd->prepare($sql);
			$statement->bindParam(':username', 		$username, 		PDO::PARAM_STR);
			$statement->bindParam(':password', 		$password,		PDO::PARAM_STR);
			$statement->bindParam(':type_user1', 	$type_user1,	PDO::PARAM_STR);
			$statement->bindParam(':type_user2', 	$type_user2,	PDO::PARAM_STR);
			$statement->execute();
			return $statement;
		}catch(PDOException  $e ){
			echo $e->getMessage( );
		}
	}

	public function isEmailExist($email, $bdd){		
		try{
			$sql  = 'SELECT * FROM users_student WHERE email = :email Limit 1';
			$statement = $bdd->prepare($sql);
			$statement->bindParam(':email', 		$email, 		PDO::PARAM_STR);
			$statement->execute();
			return $statement;
		}catch(PDOException  $e ){
			echo $e->getMessage( );
		}
	}
	
	public function isCodeExist($code, $login, $bdd){		
		try{
			$sql  = 'SELECT * FROM verification WHERE code = :code  AND login = :login Limit 1';
			$statement = $bdd->prepare($sql);
			$statement->bindParam(':code', 		$code, 		PDO::PARAM_STR);
			$statement->bindParam(':login', 	$login,		PDO::PARAM_STR);
			$statement->execute();
			return $statement;
		}catch(PDOException  $e ){
			echo $e->getMessage( );
		}
	}
	
	
	public function createNewRegisterUser($username, $password, $email, $matricule, $phone, $date_nais, $phoneDad, $bdd1, $bdd){
		$query = "SELECT e.nom, e.prenom, e.date_nais, i.ajourGest FROM  tabetudiant AS e 
		INNER JOIN tabinscription AS i ON i.matricule = e.matricule WHERE e.matricule = :matri 
		AND e.date_nais = :date AND i.annee_acad = (SELECT MAX(`annee_acad`) AS `annee_acad` 
		FROM `tabinscription` WHERE matricule = :matricule)";

		$statement = $bdd->prepare($query);
		$statement->bindParam(':matri', $matricule, PDO::PARAM_STR);
		$statement->bindParam(':date', $date_nais, PDO::PARAM_STR);
		$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
		$statement->execute();
		$n = $statement->rowCount();	

		$tab=$statement->fetch(PDO::FETCH_ASSOC);
		$ajourGest  = $tab['ajourGest'];
				
		if($n==0){
			$json['success'] = -1;
		}else if($ajourGest=='NO'){
			$json['success'] = -2;
		}else{ 
			date_default_timezone_set('Africa/Porto-Novo');
			$date = date('Y-m-d H:i:s', time());

			try{
				$sql  = 'INSERT INTO `users_student` (username, password, email, matricule, phone, created_at, updated_at, phoneDad)';
				$sql .= 'VALUES (:username, :password, :email, :matricule, :phone, :created_at, :updated_at, :phoneDad)';
				$statement = $bdd1->prepare($sql);
				$statement->bindParam(':username', 		$username, 	PDO::PARAM_STR);
				$statement->bindParam(':password', 		$password,	PDO::PARAM_STR);
				$statement->bindParam(':email',  		$email, 	PDO::PARAM_STR);
				$statement->bindParam(':matricule',  	$matricule, PDO::PARAM_STR);
				$statement->bindParam(':phone',  		$phoneDad, 	PDO::PARAM_STR);
				$statement->bindParam(':created_at',  	$date, 		PDO::PARAM_STR);
				$statement->bindParam(':updated_at',  	$date, 		PDO::PARAM_STR);
				$statement->bindParam(':phoneDad',  	$phoneDad, 	PDO::PARAM_STR);
				$result = $statement->execute();	
				if($result){
					$json['success'] = 1;									
				}else{
					$json['success'] = 0;
				}				
			}catch(PDOException  $e ){
				echo $e->getMessage( );
				die();
			}
		}
		return $json;
	}
	
	public function createNewRegisterUserR($username, $password, $email, $matricule, $phone, $date_nais, $phoneDad, $bdd1, $bdd){
		$query = "select * from geststudent_r where (matricule = :matricule and date_nais = :date_nais)";
		$statement = $bdd->prepare($query);
		$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
		$statement->bindParam(':date_nais', $date_nais, PDO::PARAM_STR);
		$statement->execute();
		$nb = $statement->rowCount();
				
		$estAjour = 'YES';
		$query = "select * from geststudent_r where (matricule = :matricule and date_nais = :date_nais and estAjour = :estAjour)";
		$statement = $bdd->prepare($query);
		$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
		$statement->bindParam(':date_nais', $date_nais, PDO::PARAM_STR);
		$statement->bindParam(':estAjour',  $estAjour, PDO::PARAM_STR);
		$statement->execute();
		$nAjour = $statement->rowCount();	
				
		if($nb==0){
			$json['success'] = -1;
		}else if($nAjour==0){
			$json['success'] = -2;
		}else{ 
			date_default_timezone_set('Africa/Porto-Novo');
			$date = date('Y-m-d H:i:s', time());
			try{
				$sql  = 'INSERT INTO `users` (username, password, email, matricule, phone, created_at, updated_at, phoneDad)';
				$sql .= 'VALUES (:username, :password, :email, :matricule, :phone, :created_at, :updated_at, :phoneDad)';
				$statement = $bdd1->prepare($sql);
				$statement->bindParam(':username', 		$username, 	PDO::PARAM_STR);
				$statement->bindParam(':password', 		$password,	PDO::PARAM_STR);
				$statement->bindParam(':email',  		$email, 	PDO::PARAM_STR);
				$statement->bindParam(':matricule',  	$matricule, PDO::PARAM_STR);
				$statement->bindParam(':phone',  		$phoneDad, 	PDO::PARAM_STR);
				$statement->bindParam(':created_at',  	$date, 		PDO::PARAM_STR);
				$statement->bindParam(':updated_at',  	$date, 		PDO::PARAM_STR);
				$statement->bindParam(':phoneDad',  	$phoneDad, 	PDO::PARAM_STR);
				$result = $statement->execute();	
				if($result){
					$json['success'] = 1;									
				}else{
					$json['success'] = 0;
				}				
			}catch(PDOException  $e ){
				echo $e->getMessage( );
				die();
			}
		}
		return $json;
	}
	

	public function updatePassword($username, $password, $bdd){
		date_default_timezone_set('Africa/Porto-Novo'); 
		$updated_at = date('Y-m-d H:i:s');
		try{
			$sql  = 'update users_student set password = :password, updated_at = :updated_at where username = :username';
			$statement = $bdd->prepare($sql);
			$statement->bindParam(':password', 		$password,	PDO::PARAM_STR);
			$statement->bindParam(':updated_at',  	$updated_at, 	PDO::PARAM_STR);
			$statement->bindParam(':username', 		$username, 	PDO::PARAM_STR);
			$result = $statement->execute();	

			/*$query = "select phone, phoneDad, idEtablissement  from users_student where username = :username";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':username', $username, PDO::PARAM_STR);
			$statement->execute();
			$tab=$statement->fetch(PDO::FETCH_ASSOC);
			$phone  = $tab['phone'];
			$phoneDad  = $tab['phoneDad'];
			$idEtablissement  = $tab['idEtablissement'];

        	$universite= $bdd -> prepare('SELECT * FROM etablissement WHERE idEtablissement=:id');
        	$universite-> execute(array('id'=>$idEtablissement)) or die(print_r($universite->errorInfo));
        	$universite=$universite->fetch();
        	$idUniv = $universite['idUniversite'];*/
        
            if($idUniv == 1) {
				/*$arr = array('succ' => 0, 'phone' => $phone, 'phoneDad' => $phoneDad);
				$json['success'][] = $arr;
				return $json;
                exit();*/
            }
            
			if($result){
				$arr = array('succ' => 1, 'phone' => $phone, 'phoneDad' => $phoneDad);
				$json['success'][] = $arr;
				$query = "delete from verification where login = :log";
    			$statement = $bdd->prepare($query);
    			$statement->bindParam(':log', $username, PDO::PARAM_STR);
    			$statement->execute();
			}else{
				$arr = array('succ' => 0, 'phone' => $phone, 'phoneDad' => $phoneDad);
				$json['success'][] = $arr;
				//$json['success'] = 0;
			}
			return $json;			
		}catch(PDOException  $e ){
			echo $e->getMessage( );
			die();
		}
	}
	
	public function loginUsers($username, $password, $bdd1, $bdd){			
		$json = array();
		$canUserLogin = $this->isLoginExist($username, $password, $bdd1);
		$tab=$canUserLogin->fetch(PDO::FETCH_ASSOC);
		$matricule = $tab['matricule'];	
		$email	   = $tab['email'];	
		$nb = $canUserLogin->rowCount();
		
		$req = 'SELECT `idEtablissement` FROM `departement` INNER JOIN filieres ON 
		filieres.idDepartement = departement.idDepartement LIMIT 1';
		$statement = $bdd->prepare($req);
		$statement->execute();
		$row=$statement->fetch(PDO::FETCH_ASSOC);
		
		$aff = 'YES';
		$sql0 = "SELECT * FROM `resultats` where affiche= :aff and idEtablissement = :idEtablissement 
		AND annee_acad = (SELECT MAX(`annee_acad`) AS `annee_acad` FROM `tabinscription` WHERE matricule = :matri)";
		$statement0 = $bdd->prepare($sql0);
		$statement0->bindParam(':aff', $aff, PDO::PARAM_STR);
		$statement0->bindParam(':idEtablissement', 	$row["idEtablissement"], PDO::PARAM_STR);
		$statement0->bindParam(':matri', $matricule, PDO::PARAM_STR);
		$statement0->execute();
		$nb = $statement0->rowCount();
		$idEtablissement = $row["idEtablissement"];

    	$universite= $bdd -> prepare('SELECT * FROM etablissement WHERE 	idEtablissement=:id');
    	$universite-> execute(array('id'=>$idEtablissement)) or die(print_r($universite->errorInfo));
    	$universite=$universite->fetch();
    	$idUniv = $universite['idUniversite'];
    
        if($idUniv == 1) {
		  	/*$res = 0;
			$arr = array('succ' => $res);
			$json['success'][] = $arr;
			return $json;
            exit();*/
        }
		
		if($tab['password'] == crypt($password,$tab['password']) && $nb == 1) {		
			$query = "SELECT annee, niveauFormation, idFiliere FROM  tabinscription WHERE matricule = :matricule AND annee_acad = (SELECT MAX(`annee_acad`) AS `annee_acad` FROM `tabinscription` WHERE matricule = :matri)";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
			$statement->bindParam(':matri', $matricule, PDO::PARAM_STR);
			$statement->execute();
			$tab0=$statement->fetch(PDO::FETCH_ASSOC);
			$annee = $tab0['annee'];	
			$niveau = $tab0['niveauFormation'];	 
			$idFiliere = $tab0['idFiliere'];	
			
			$query = "select nom, prenom  from tabetudiant where matricule = :matricule Limit 1";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
			$statement->execute();
			$tab1=$statement->fetch(PDO::FETCH_ASSOC);
			$nom		 	= $tab1['nom'] ;
			$prenom 		= $tab1['prenom'] ;
					
			$query = "select sigleFilieres  from filieres where idFiliere = :id Limit 1";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':id', $idFiliere, PDO::PARAM_STR);
			$statement->execute();
			$tab12=$statement->fetch(PDO::FETCH_ASSOC);
			$sigleFilieres = $tab12['sigleFilieres'] ;
				
			$arr = array('succ' => 1);
			$arr2 = array('nomprenom' => "$nom $prenom");
			$json['success'][] = $tab;
			$json['success'][] = $arr;
			$json['success'][] = $arr2;
			$tab0 = array('annee' => "$annee");
			$json['success'][] = $tab0;
			$tab00 = array('niveau' => "$niveau");
			$json['success'][] = $tab00;
			$tab01 = array('idFiliere' => "$idFiliere");
			$json['success'][] = $tab01;
			$tab02 = array('sigleFilieres' => "$sigleFilieres");
			$json['success'][] = $tab02;
			$tab03 = array('idEtablissement' => "$idEtablissement");
			$json['success'][] = $tab03;
		}else if($tab['password'] == crypt($password,$tab['password']) && $nb == 0) {
			$res = -1;
			$arr = array('succ' => $res);
			$json['success'][] = $arr;
			$query = "SELECT annee, niveauFormation, idFiliere FROM  tabinscription WHERE matricule = :matricule AND annee_acad = (SELECT MAX(`annee_acad`) AS `annee_acad` FROM `tabinscription` WHERE matricule = :matri)";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
			$statement->bindParam(':matri', $matricule, PDO::PARAM_STR);
			$statement->execute();
			$tab0=$statement->fetch(PDO::FETCH_ASSOC);
			$annee = $tab0['annee'];	
			$niveau = $tab0['niveauFormation'];	 
			$idFiliere = $tab0['idFiliere'];	
			
			$query = "select nom, prenom  from tabetudiant where matricule = :matricule Limit 1";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
			$statement->execute();
			$tab1=$statement->fetch(PDO::FETCH_ASSOC);
			$nom		 	= $tab1['nom'] ;
			$prenom 		= $tab1['prenom'] ;
					
			$query = "select sigleFilieres  from filieres where idFiliere = :id Limit 1";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':id', $idFiliere, PDO::PARAM_STR);
			$statement->execute();
			$tab12=$statement->fetch(PDO::FETCH_ASSOC);
			$sigleFilieres = $tab12['sigleFilieres'] ;
			$arr2 = array('nomprenom' => "$nom $prenom");
			$json['success'][] = $tab;
			$json['success'][] = $arr;
			$json['success'][] = $arr2;
			$tab0 = array('annee' => "$annee");
			$json['success'][] = $tab0;
			$tab00 = array('niveau' => "$niveau");
			$json['success'][] = $tab00;
			$tab01 = array('idFiliere' => "$idFiliere");
			$json['success'][] = $tab01;
			$tab02 = array('sigleFilieres' => "$sigleFilieres");
			$json['success'][] = $tab02;
			$tab03 = array('idEtablissement' => "$idEtablissement");
			$json['success'][] = $tab03;
		}else{
		  	$res = 0;
			$arr = array('succ' => $res);
			$json['success'][] = $arr;
		}
		return $json;
	}

	public function loginUsersR($username, $password, $db1, $bdd){			
		$json = array();
		$canUserLogin = $this->isLoginRExist($username, $password, $bdd1);
		$tab=$canUserLogin->fetch(PDO::FETCH_ASSOC);
		$nb = $canUserLogin->rowCount();	
		$matricule = $tab['matricule'];	
				
		$query = "select * from geststudent_r where matricule = :matricule Limit 1";
		$statement = $bdd->prepare($query);
		$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
		$statement->execute();
		$tab1=$statement->fetch(PDO::FETCH_ASSOC);
		$nb = $statement->rowCount();

		$nom		 	= $tab1['nom'] ;
		$prenom 		= $tab1['prenom'] ;
		$idFiliere 		= $tab1['idFiliere'];
		
		if($nb == 1){
			$arr = array('succ' => 1);
			$arr2 = array('nomprenom' => "$nom $prenom");
			$json['success'][] = $tab;
			$json['success'][] = $arr;
			$json['success'][] = $arr2;
			$tab01 = array('idFiliere' => "$idFiliere");
			$json['success'][] = $tab01;
		}else{
			$arr = array('succ' => 0);
			$json['success'][] = $arr;
		}
		return $json;
	}


	public function emailUsers($email, $bdd){
			
		$json = array();
		$canUserEmail = $this->isEmailExist($email, $bdd);
		$tab=$canUserEmail->fetch(PDO::FETCH_ASSOC);
		$nb = $canUserEmail->rowCount();	
		$username           = $tab['username'];
		
		$idEtablissement    = $tab['idEtablissement'];

    	$universite= $bdd -> prepare('SELECT * FROM etablissement WHERE 	idEtablissement=:id');
    	$universite-> execute(array('id'=>$idEtablissement)) or die(print_r($universite->errorInfo));
    	$universite=$universite->fetch();
    	$idUniv = $universite['idUniversite'];
    
        if($idUniv == 1) {
			//$json['success'] = 0;
            //exit();
        }
        
		if($nb == 1){
			$json['success'] = 1;
		}else{
			$json['success'] = 0;
		}
		return $json;
	}
	
	public function codeUsers($code, $login, $bdd){
			
		$json = array();
		$codeUser = $this->isCodeExist($code, $login, $bdd);
		$nb = $codeUser->rowCount();
 		if($nb == 1){
			$json['success'] = 1;
		}else{
			$json['success'] = 0;
		}
		return $json;
	}

	public function selectID($matricule){						
		$query = "select idFiliere from tabetudiant where matricule = '$matricule' AND annee_acad = (SELECT MAX(annee_acad )FROM tabetudiant WHERE
		 matricule = '$matricule')";
		$result = mysql_query($query);
        return $result;
	}
	
	
	public function updateTokenInfo($tokenId, $matricule){		
		$selectID = $this->selectID($matricule);
		$tab = mysql_fetch_array($selectID, MYSQL_ASSOC);
		$idFiliere = $tab['idFiliere'];		
		$query = "update firebase set idFiliere = '$idFiliere', matricule = '$matricule' where id = '$tokenId'";
		$update = mysql_query($query);			
		if($update){
			$json['success'] = 1;
		}else{
			$json['success'] = 0;
		}
		return $json;
	}
	
	public function updateTokenInfoNew($token, $matricule, $idFiliere, $idEtablissement, $annee, $niveau, $bdd){
		$matr = 0;
		$idFi = 0;
		$query = "select * from firebase where token = :token AND matricule = :matri AND idFiliere = :id";
		$statement = $bdd->prepare($query);
		$statement->bindParam(':token', $token, PDO::PARAM_STR);
		$statement->bindParam(':matri', $matr, PDO::PARAM_STR);
		$statement->bindParam(':id', $idFi, PDO::PARAM_STR);
		$statement->execute();
		$n = $statement->rowCount();

		if($n > 0) {
			$query = "delete from firebase where matricule = :matri";
			$statement = $bdd->prepare($query);
			$statement->bindParam(':matri', $matricule, PDO::PARAM_STR);
			$statement->execute();
		}		

		$query = "update firebase set idFiliere = :id, idEtablissement = :idE, matricule = :matri, annee = :annee, niveau = :niveau where token = :token";
		$statement = $bdd->prepare($query);
		$statement->bindParam(':id', $idFiliere, PDO::PARAM_STR);
		$statement->bindParam(':idE', $idEtablissement, PDO::PARAM_STR);
		$statement->bindParam(':matri', $matricule, PDO::PARAM_STR);
		$statement->bindParam(':annee', $annee, PDO::PARAM_STR);
		$statement->bindParam(':niveau', $niveau, PDO::PARAM_STR);
		$statement->bindParam(':token', $token, PDO::PARAM_STR);
		$result = $statement->execute();

		if($result){
			$json['success'] = 1;
		}else{
			$json['success'] = 0;
		}
		return $json;
	}	
	

}


?>
