Rev 231 | Blame | Last modification | View Log | RSS feed
<?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'];
}
}
?>