Subversion Repositories eFlore/Projets.eflore-projets

<?php

class SourceDonnees {
        
        private $bdd = null;
        private $limitesCarte = '';
        private $taxon = array();
        
        private $nomRang = '';
        private $taxons = array();
        private $genres = array();
        private $utilisateurs = null;
        
        
        public function __construct($limitesCarte, $taxon) {
                $this->limitesCarte = $limitesCarte;
                foreach ($this->limitesCarte as $bord => $valeur) {
                        $this->limitesCarte[$bord] = str_replace(",", ".", round($valeur, 6));
                }
                $this->bdd = new Bdd();
                $this->taxon = $taxon ?? [];
                $this->nomRang = $this->obtenirNomRang();
                if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {
                        $this->taxons = $this->recupererSynonymesEtSousEspeces();
                } elseif ($this->nomRang == 'famille') {
                        $this->genres = $this->recupererGenres();
                }
                $this->utilisateurs = new Utilisateurs();
        }
        
        private function obtenirNomRang() {
                $nomsRangs = array('famille', 'genre', 'espece', 'sous_espece');
                $rangs = explode(',', Config::get('rangs'));
                for ($index = 0; $index < count($nomsRangs) && $rangs[$index] != $this->taxon['rang']; $index ++);
                $position = $index == count($nomsRangs) ? count($nomsRangs)-1 : $index;
                return $nomsRangs[$position];
        }
        
        protected function recupererSynonymesEtSousEspeces() {
                $requete =
                "SELECT num_nom, nom_sci, nom_complet, num_taxonomique FROM ".Config::get('bdd_table_referentiel').
                " WHERE hierarchie LIKE '%-{$this->taxon['num_nom']}-%' ".
                "OR num_nom_retenu = {$this->taxon['num_nom_retenu']}";
                return $this->bdd->recupererTous($requete);
        }
        
        protected function recupererGenres() {
                $this->bdd->requeter("USE ".Config::get('bdd_nom'));
                $requete =
                "SELECT num_nom, nom_sci, num_taxonomique FROM ".Config::get('bdd_table_referentiel').
                " WHERE rang=220 AND num_tax_sup={$this->taxon['num_nom']}";
                return $this->bdd->recupererTous($requete);
        }
        
        public function recupererStationsFloradata() {
                $this->bdd->requeter("USE ".Config::get('bdd_nom_floradata'));
                $requete =
                "SELECT DISTINCTROW zone_geo AS commune, Date(date_observation) AS date, Floor(wgs84_latitude*10)/10 AS lat, ".
                "Floor(wgs84_longitude*10)/10 AS lng, courriel_utilisateur AS auteur ".
                "FROM cel_export LEFT JOIN cel_zones_geo cz ON ce_zone_geo=id_zone_geo ".
                "WHERE ".$this->construireWhereTaxonFloradata()." AND nom_referentiel = '".Config::get('referentielsDispo')."' AND ".
                "wgs84_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
                "AND wgs84_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
                "AND date_observation<>'0000-00-00 00-00-00' ORDER BY lat DESC, lng ASC, commune, date";
                $stations = $this->bdd->recupererTous($requete);
                
                $this->extraireIdentitesAuteurs($stations);
                foreach($stations as &$station) {
                        $station['auteur'] = $this->utilisateurs->getIntitule($station['auteur']);
                }       
                return $stations;
        }
        
        private function extraireIdentitesAuteurs($stations) {
                $courriels = array();
                foreach ($stations as &$station) {
                        $courriels[] = $station['auteur'];
                }
                $this->utilisateurs->setCourriels($courriels);
                $this->utilisateurs->chargerIdentites();
        }
        
        private function construireWhereTaxonFloradata() {
                $criteres = array();
                $nomRang = $this->obtenirNomRang($this->taxon);
                if ($this->nomRang == 'famille') {
                        $criteres[] = "famille=".$this->bdd->proteger($this->taxon['nom_sci']);
                } elseif ($this->nomRang == 'genre') {
                        $criteres[] = "nom_sel LIKE ".$this->bdd->proteger($this->taxon['nom_sci'].'%');
                } else {
                        $taxons = array($this->taxon['num_nom']);
                        foreach ($this->taxons as $sousTaxon) {
                                $taxons[] = $sousTaxon['num_nom'];
                        }
                        $criteres[] = "nom_sel_nn IN (".implode(',', array_unique($taxons))     .")";
                }
                return "(".implode(' OR ',array_unique($criteres)).")";
        }
        
        
        
        public function recupererStationsMoissonnage($source) {
                $this->bdd->requeter("USE ".Config::get('bdd_nom'));
                $requete =
                "SELECT DISTINCTROW lieu_commune_code_insee, observation_date AS date, observateur_nom_complet AS auteur ".
                "FROM {$source}_tapir WHERE ".$this->construireWhereTaxonMoissonnage($source)." ".
                "AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
                "AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
                "AND Length(lieu_commune_code_insee)=5 ORDER BY lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
                $stations = $this->bdd->recupererTous($requete);                
                $this->rechercherInfosCommune($stations);
                return $stations;
        }
        
        public function recupererStationsVigieFlore() {
                $this->bdd->requeter("USE ".Config::get('bdd_nom'));
                $requete =
                "SELECT DISTINCTROW lieu_commune_code_insee AS commune, Floor(lieu_station_longitude*10)/10 as lng, Floor(lieu_station_latitude*10)/10 as lat, observation_date AS date, observateur_nom_complet AS auteur ".
                "FROM vigie_flore_tapir WHERE ".$this->construireWhereTaxonMoissonnage('vigie_flore')." ".
                "AND lieu_station_longitude BETWEEN ".$this->limitesCarte['ouest']." AND ".$this->limitesCarte['est']." ".
                "AND lieu_station_latitude BETWEEN ".$this->limitesCarte['sud']." AND ".$this->limitesCarte['nord']." ".
                " ORDER BY lat DESC, lng ASC, lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
                $stations = $this->bdd->recupererTous($requete);
                return $stations;
        }
        
        public function recupererStationsPhotoflora() {
                $this->bdd->requeter("USE ".Config::get('bdd_nom'));
                $requete =
                "SELECT DISTINCTROW lieu_commune_code_insee, observation_date AS date, observateur_nom_complet AS auteur ".
                "FROM photoflora_tapir WHERE ".$this->construireWhereTaxonMoissonnage('photoflora')." ".
                "AND Length(lieu_commune_code_insee)=5 ORDER BY lieu_commune_code_insee, date"." -- " . __FILE__ . ":" . __LINE__." ". @$_SERVER['REQUEST_URI'];
                $stations = $this->bdd->recupererTous($requete);
                $this->rechercherInfosCommune($stations);
                return $stations;
        }
        
        
        private function construireWhereTaxonMoissonnage($source) {
                $nomRang = $this->obtenirNomRang();
                $criteres = array();
                $criteres[] = (in_array($source, array("baznat", "ifn", "vigie_flore", "photoflora")) ) ? 
                                                "num_nom = ".$this->taxon['num_nom'] : 
                                                "nom_scientifique_complet LIKE ".$this->bdd->proteger($this->taxon['nom_sci']) ;
                if ($this->nomRang == 'espece' || $this->nomRang == 'sous_espece') {
                        foreach ($this->taxons as $sousTaxon) {
                                if ($source == "sophy") {
                                        if (isset($sousTaxon['num_nom_retenu']) && $sousTaxon['num_nom'] == $sousTaxon['num_nom_retenu']) {
                                                $criteres[] =
                                                "nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ;
                                        }
                                } else {
                                        $criteres[] = (in_array($source, array("baznat", "ifn", "vigie_flore", "photoflora"))) ?
                                                                                "num_nom = ".$sousTaxon['num_nom'] : 
                                                                                "nom_scientifique_complet LIKE ".$this->bdd->proteger($sousTaxon['nom_sci']) ;
                                }
                        }
                } elseif ($this->nomRang == 'famille') {
                        foreach ($this->genres as $genre) {
                                $criteres[] = "nom_scientifique_complet LIKE ".$this->bdd->proteger($genre['nom_sci']."%");
                        }
                }
                return "(".implode(' OR ',array_unique($criteres)).")";
                
        }
        
        private function rechercherInfosCommune(& $stations) {
                $codesInsee = array();
                foreach ($stations as $station) {
                        $codeInsee = $station['lieu_commune_code_insee'];
                        if (substr($codeInsee, 0, 2) == '20') {
                                $codeInsee  = '2A'.substr($codeInsee, 2);
                                $codeInsee2 = '2B'.substr($codeInsee, 2);
                        }
                        if (!in_array($codeInsee, $codesInsee)) {
                                if (substr($codeInsee, 0, 2) == '20') {
                                        $codesInsee[] = "'$codeInsee2'";
                                }
                                $codesInsee[] = "'$codeInsee'";
                        }
                }
                $nomTableCommunes = Config::get('bdd_table_communes');
                $communes = array();
                if(count($codesInsee) > 0) {
                        $requete =
                        "SELECT insee, nom AS commune, Floor(latitude_degre*10)/10 AS lat, Floor(longitude_degre*10)/10 AS lng ".
                        "FROM $nomTableCommunes WHERE insee IN (".implode(',', array_unique($codesInsee)).") ORDER BY insee";
                        $communes = $this->bdd->recupererTous($requete);
                }
                $indexStation = 0;
                foreach ($communes as $commune) {
                        $codeInsee = $commune['insee'];
                        if (substr($codeInsee, 0, 2) == '2A' || substr($codeInsee, 0, 2) == '2B') {
                                $codeInsee = '20'.substr($codeInsee, 2);
                        }
                        while ($stations[$indexStation]['lieu_commune_code_insee'] < $codeInsee) {
                                $indexStation ++;
                        }
                        if ($stations[$indexStation]['lieu_commune_code_insee'] == $codeInsee) {
                                $stations[$indexStation]['lat'] = $commune['lat'];
                                $stations[$indexStation]['lng'] = $commune['lng'];
                                $stations[$indexStation]['commune'] = $commune['commune'];
                        }
                }
                
                $lat = array();
                $lng = array();
                foreach ($stations as $index => $station) {
                        if (!isset($station['lat'])) {
                                $station['lat'] = -100;
                                $station['lng'] = -100;
                        }
                        $lat[$index] = $station['lat'];
                        $lng[$index] = $station['lng'];
                }
                array_multisort($lat, SORT_DESC, $lng, SORT_ASC, $stations);
        }
        
}

?>