Subversion Repositories eFlore/Projets.eflore-projets

<?php

class OntologieDAO {

	private $bdd = null;

	private $versions = null;

	private $requeteNbreTermesTotal = null;

	private $masquesStrictes = array();

	private $paramsMasque = array();

	public function __construct(Ressources $ressources, Parametres $parametres, Bdd $bdd, Versions $versions) {

		$this->ressources = $ressources;

		$this->parametres = $parametres;

		$this->bdd = $bdd;

		$this->versions = $versions;

		$this->masquesStrictes = array('code');

		$this->paramsMasque = array(

					'' => 'nom',

					'code' => 'id_terme',

					'nom' => $this->getChampPourLangue('nom'),

					'description' => $this->getChampPourLangue('description'));

	}

	public function getDetails() {

		$table = $this->getTableTerme();

		$tableType = $this->getTableType();

		$detailsId = $this->ressources->getDetailsId();

		$detailsId = $this->bdd->proteger($detailsId);

		$requete =

				'SELECT o.*, type '.

				"FROM $table AS o ".

				"	LEFT JOIN $tableType ON (ce_type = id_type) ".

				"WHERE id_terme = $detailsId ";

		$resultats = $this->bdd->recuperer($requete);

		$terme = new OntologieDO($resultats);

		return $terme;

	}

	public function rechercher() {

		$clause = $this->getClauseSelectSpeciale();

		$table = $this->getTableTerme();

		$tableType = $this->getTableType();

		$tablePublication = $this->getTablePublication();

		$tableAuteur = $this->getTableAuteur();

		$tableImage = $this->getTableImage();

		$conditions = $this->getConditions();

		$where = $this->getWhere($conditions);

		$ordre = $this->getOrdre();

		$navigation = $this->getNavigation();

		$requete = "SELECT $clause o.*, t.type, p.*,  a.*,  i.* ".

			"FROM $table AS o ".

			"	LEFT JOIN $tableType AS t ON (ce_type = id_type) ".

			"	LEFT JOIN $tablePublication AS p ON (ce_publication = id_publication) ".

			"	LEFT JOIN $tableAuteur AS a ON (ce_auteur = id_auteur) ".

			"	LEFT JOIN $tableImage AS i ON (ce_image = id_image) ".

			$where.' '.$conditions.' '.$ordre.' '.

			"LIMIT $navigation ";

		//die($requete);

		$this->requeteNbreNomsTotal = $this->transformerRequetePourNbreTermesTotal($requete);

		$resultats = $this->bdd->recupererTous($requete);

		return $resultats;

	}

	private function getClauseSelectSpeciale() {

		$clause = (Config::get('bdd_protocole') == 'mysql') ? 'SQL_CALC_FOUND_ROWS' : '';

		return $clause;

	}

	private function getTableTerme() {

		return sprintf($this->getNomTableTpl(), 'terme');

	}

	private function getTableType() {

		return sprintf($this->getNomTableTpl(), 'type');

	}

	private function getTablePublication() {

		return sprintf($this->getNomTableTpl(), 'publication');

	}

	private function getTableAuteur() {

		return sprintf($this->getNomTableTpl(), 'auteur');

	}

	private function getTableImage() {

		return sprintf($this->getNomTableTpl(), 'image');

	}

	private function getTableHierarchie() {

		return sprintf($this->getNomTableTpl(), 'hierarchie');

	}

	private function getNomTableTpl() {

		$versions = $this->versions->getVersions();

		$derniereVersion = end($versions);

		$projetNom = strtolower($this->ressources->getProjetNom());

		return $projetNom.'_ontologies_%s_v'.$derniereVersion;

	}

	private function getConditions() {

		$operateurParDefaut = $this->getOperateurCondition();

		$conditionsSql = array();

		foreach ($this->paramsMasque as $typeMasque => $champ) {

			$operateur = in_array($typeMasque, $this->masquesStrictes) ? '=' : $operateurParDefaut;

			if ($valeurMasque = $this->parametres->getMasquePourBdd($typeMasque)) {

				if ($operateur == 'SOUNDEX') {

					$tpl = '(SOUNDEX(%s) = SOUNDEX(%s)) OR (SOUNDEX(REVERSE(%s)) = SOUNDEX(REVERSE(%s))) ';

					$conditionsSql[] = sprintf($tpl, $champ, $valeurMasque, $champ, $valeurMasque);

				} else {

					$conditionsSql[] = "$champ $operateur $valeurMasque";

				}

			}

		}

		return implode(' AND ', $conditionsSql);

	}

	private function getOperateurCondition() {

		$operateur = '';

		$recherche = $this->parametres->get('recherche');

		if ($recherche == 'stricte') {

			$operateur = '=';

		} else if ($recherche == 'etendue') {

			$operateur = 'LIKE';

		} else if ($recherche == 'floue') {

			$operateur = 'SOUNDEX';

		}

		return $operateur;

	}

	private function getWhere($conditions = '') {

		$where = '';

		if ($conditions != '') {

			$where = 'WHERE ';

		}

		return $where;

	}

	private function getOrdre() {

		$champNom = $this->getChampPourLangue('nom');

		$ordre = "ORDER BY $champNom ASC ";

		return $ordre;

	}

	private function getChampPourLangue($champ) {

		$lg = $this->parametres->get('retour.langue');

		if ($lg == 'en') {

			$champ .= '_en';

		}

		return $champ;

	}

	private function getNavigation() {

		$debut = (int) $this->parametres->get('navigation.depart');

		$nbre = $this->parametres->get('navigation.limite');

		$navigation = "$debut,$nbre";

		return $navigation;

	}

	private function transformerRequetePourNbreTermesTotal($requete) {

		$requete = preg_replace('/SELECT .* FROM/', 'SELECT COUNT(*) AS nbre FROM', $requete);

		$requete = preg_replace('/LIMIT [0-9]+,[0-9]+/', '', $requete);

		return $requete;

	}

	public function trierResultatsFloue($termes) {

		foreach ($this->paramsMasque as $typeMasque => $champ) {

			if ($termeDemande = $this->parametres->getMasquePourBdd($typeMasque)) {

				$termeDemandeSimple = strtolower(Chaine::supprimerAccents($termeDemande));

				foreach ($termes as $id => $terme) {

					$termeFlouSimple = strtolower(Chaine::supprimerAccents($terme[$this->getChampPourLangue('nom')]));

					// Prime pour la ressemblance globale :

					$leven = levenshtein($termeFlouSimple, $termeDemandeSimple);

					// On affine

					$similar = similar_text($termeDemandeSimple, $termeFlouSimple) * 3;

					// Prime Soundex

					$soundex = (soundex($termeDemandeSimple) == soundex($termeFlouSimple)) ? 1000 : 0;

					// Calcul du score

					$score = 500 - $leven + $similar + $soundex;

					$termes[$id]['score'] = $score;

					$termes[$id]['score_calcul'] = "$termeDemandeSimple / $termeFlouSimple : 500 - $leven + $similar + $soundex = $score";

				}

				$termes = Tableau::trierMD($termes, array('score' => SORT_DESC));

			}

		}

		//echo 'ici<pre>'.print_r($termes, true).'</pre>';die();

		return $termes;

	}

	public function recupererNombreTermesTotal() {

		if (Config::get('bdd_protocole') == 'mysql') {

			$requete = 'SELECT FOUND_ROWS() AS nbre';

		} else {

			$requete = $this->requeteNbreNomsTotal;

		}

		$nombre = $this->bdd->recuperer($requete);

		return (int) $nombre['nbre'];

	}

}

?>