Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 231 | Go to most recent revision | Blame | Compare with Previous | 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'];
        }
}
?>