<?php
require_once 'Framework/Modele.php';

class Medecin extends Modele {

    public function getmedeconspossibles($codePrestataire, $nomsearch, $noOrdreMedecin) 
	{
		$nomsearch 		= contruireParamLike($nomsearch);
		$noOrdreMedecin = contruireParamLike($noOrdreMedecin);

		$sql = 'call sp_p_medecins_possibles(?, ?, ?)';
		
        $medecins = $this->executerRequete($sql, array($codePrestataire, $nomsearch, $noOrdreMedecin));
		
        return $medecins;
    }

    public function getmedecinsvide() 
	{		
		$sql 		= 'call sp_p_medecins_vide()';		
        $medecins 	= $this->executerRequete($sql);
        return $medecins;
    }
	
    public function getMedecins($codeMedecin=null,$nom=null,$prenoms=null) {
		$codeMedecin = contruireParamLike($codeMedecin);
		$nom = contruireParamLike($nom);
		$prenoms = contruireParamLike($prenoms);
		
		$sql = 'SELECT A.*, B.libelle as specialite FROM p_medecin A left join specialite B on (B.codeSpecialite=A.codeSpecialite) 
		WHERE (A.codeMedecin LIKE ? AND A.nom LIKE ? AND A.prenoms LIKE ?) 
		order by A.nom, A.prenoms';
		
        $medecins = $this->executerRequete($sql, array($codeMedecin,$nom,$prenoms));
        return $medecins;
    }
	
    public function getMedecin($id) {
		$sql = 'SELECT A.*, B.libelle as specialite FROM p_medecin A 
		left join specialite B on (B.codeSpecialite=A.codeSpecialite) 
		WHERE (A.id=?)';

        $medecin = $this->executerRequete($sql, array($id));
		return $medecin->fetch(PDO::FETCH_ASSOC);
    }
    public function ajouter($codeMedecin, $nom, $prenoms, $telephone, $email, $noOrdreMedecin, $codeSpecialite) {
		$sql = 'INSERT INTO p_medecin (codeMedecin, nom, prenoms, telephone, email, noOrdreMedecin, codeSpecialite) 
		VALUES (?, ?, ?, ?, ?, ?, ?)';
        $this->executerRequete($sql, array($codeMedecin, $nom, $prenoms, $telephone, $email, $noOrdreMedecin, $codeSpecialite));
    }	
	
    public function modifier($id, $codeMedecin, $nom, $prenoms, $telephone, $email, $noOrdreMedecin, $codeSpecialite) {
		$sql = 'UPDATE p_medecin SET codeMedecin=? , nom=?, prenoms=?, telephone=?, email=?, noOrdreMedecin=? , codeSpecialite=? where (id=?)';
        $this->executerRequete($sql, array($codeMedecin, $nom, $prenoms, $telephone, $email, $noOrdreMedecin, $codeSpecialite, $id));
    }	

    public function supprimer($id) {
		$sql = 'DELETE FROM p_medecin WHERE (id=?)';
        $this->executerRequete($sql, array($id));
    }		
	
    public function existeligne($codeMedecin) {
        $sql = 'select id FROM p_medecin WHERE (codeMedecin=?)';
        $resultat = $this->executerRequete($sql, array($codeMedecin));
		return ($resultat->rowCount() > 0);
    }
	
    public function getListe($codePrestataire) 
	{
		$sql = 'SELECT codeUtilisateur as `code`, concat(B.nom," ",B.prenom)	as libelle
		FROM tacutilisateur A
		JOIN metabase B on (B.nomutilisateur=A.codeUtilisateur)
		WHERE (codeSociete=?) order by B.nom, B.prenom';
		
		$liste = $this->executerRequete($sql, array($_SESSION['codeSociete']));
        return $liste->fetchAll(PDO::FETCH_ASSOC);
    }	
	

    public function getmedecinparametrage($codeMetier, $codeSpecialite, $nom, $prenoms, $noOrdreMedecin)
	{
		$codeMetier 		= contruireParamLike($codeMetier);
		$codeSpecialite 	= contruireParamLike($codeSpecialite);
		$nom 				= contruireParamLike($nom);
		$prenoms 			= contruireParamLike($prenoms);
		$noOrdreMedecin 	= contruireParamLike($noOrdreMedecin);

		$sql = 'call sp_liste_medecins_parametrage(?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($codeMetier, $codeSpecialite, $nom, $prenoms, $noOrdreMedecin));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }


    public function creermedecin($nom, $prenoms, $telephone, $email, $codeSpecialite, $noOrdreMedecin, $sexe, $codeMetier, $adresse)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_creer_medecin(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($nom, $prenoms, $telephone, $email, $codeSpecialite, $noOrdreMedecin, $sexe, $codeMetier, $adresse, $user));
		
		$ligne = $resultat->fetch(PDO::FETCH_ASSOC);
        return $ligne['idMedecin'];
    }

    public function getunmedecin($idMedecin)
	{
		$sql = 'call sp_afficher_un_medecin(?)';
		
		$resultat = $this->executerRequete($sql, array($idMedecin));
		
		return $resultat->fetch(PDO::FETCH_ASSOC);
    }

    public function existelignenoordremedecin($noOrdreMedecin) {
        $sql = 'select noOrdreMedecin FROM p_medecin WHERE (noOrdreMedecin=?) AND noOrdreMedecin !="";';
        $resultat = $this->executerRequete($sql, array($noOrdreMedecin));
		return ($resultat->rowCount() > 0);
    }

    public function enregistrermodifmedecin($idMedecin, $nom, $prenoms, $telephone, $email, $codeSpecialite, $noOrdreMedecin, $sexe, $codeMetier, $adresse)
	{
		$user = $_SESSION['login'];

		$sql = 'call sp_modifier_medecin(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		
		$resultat = $this->executerRequete($sql, array($idMedecin, $nom, $prenoms, $telephone, $email, $codeSpecialite, $noOrdreMedecin, $sexe, $codeMetier, $adresse, $user));
    }

    public function getmedecinparametrageexport($codeMetier, $codeSpecialite, $nom, $prenoms, $noOrdreMedecin)
	{
		
		$codeMetier 		= contruireParamLike($codeMetier);
		$codeSpecialite 	= contruireParamLike($codeSpecialite);
		$nom 				= contruireParamLike($nom);
		$prenoms 			= contruireParamLike($prenoms);
		$noOrdreMedecin 	= contruireParamLike($noOrdreMedecin);

		if (est_anglophone())
		{
			$sql = 'call sp_liste_medecins_export_eng(?, ?, ?, ?, ?)';
		}
		else
		{
			$sql = 'call sp_liste_medecins_export( ?, ?, ?, ?, ?)';
		}
		
		$resultat = $this->executerRequete($sql, array($codeMetier, $codeSpecialite, $nom, $prenoms, $noOrdreMedecin));
		
		return $resultat->fetchAll(PDO::FETCH_ASSOC);
    }
	
	public function getCorporation($codeMetier)
	{
		if (est_anglophone())
		{
			$sql = 'SELECT fn_libelle_corporation_eng(?) AS corporation;';
		}
		else
		{
			$sql = 'SELECT fn_libelle_corporation(?) AS corporation;';
		}
		
		
        $resultat = $this->executerRequete($sql, array($codeMetier))->fetch(PDO::FETCH_ASSOC);
		
		return $resultat['corporation'];
        
	}
	
}