| New file |
|---|
| 0,0 → 1,175 |
|---|
| <?php |
| class Dao extends Bdd { |
| |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requête d'intégration sur table sophy_publication |
| public function integrerBiblio($biblio){ |
| $biblio = array_map(array($this, 'proteger'), $biblio); |
| $requete = 'INSERT INTO sophy_publication VALUES ('.implode(', ', $biblio).');'; |
| $resultat = $this->requeter($requete); |
| if ($resultat === false) { |
| $info = $biblio['id_publi']." n'est pas intégrée.\n"; |
| } else { |
| $info = $biblio['id_publi']." est intégrée.\n"; |
| } |
| return $info; |
| } |
| |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requête d'intégration sur table sophy_tableau |
| public function integrerTableau($titre) { |
| $titre = array_map(array($this, 'proteger'), $titre); |
| $requete_integre_tableau = 'INSERT INTO sophy_tableau VALUES ('.implode(', ', $titre).');'; |
| $reponse_requete_int_tab = $this->requeter($requete_integre_tableau); |
| if ($reponse_requete_int_tab === false) { |
| $info = "tableau ".$titre['numPubli']."-".$titre['numTableau']." non intégrée."; |
| } else { |
| $info = ''; |
| } |
| return $info; |
| } |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requêtes sur table sophy_station |
| // Requête d'intégration qui retourne l'id de la dernière station insérée (rechercheIdStation) |
| public function integrerStation($station) { |
| $station = array_map(array($this, 'proteger'), $station); |
| $reponse['id_station'] = null; |
| $requete_integre_station = 'INSERT INTO sophy_station (`ss_num_source`, `ss_poste_meteo`, `ss_localisation`, |
| `ss_latitude`, `ss_pays`, `ss_longitude`,`ss_code_departement`, `ss_altitude`, `ss_code_insee_commune`, |
| `ss_ce_precision_geographique`, `ss_systeme_projection`, `ss_latitude_dms`, `ss_longitude_dms`, `ss_latitude_wgs`, |
| `ss_longitude_wgs`, `ss_utmNorthing`, `ss_utmEasting`, `ss_utmZone`) VALUES |
| ('.implode(', ', $station).');'; |
| |
| $reponse_requete_int_stat = $this->requeter($requete_integre_station); |
| if ($reponse_requete_int_stat === false) { |
| $reponse['info'] = "station ".$station['numSource']." non intégrée. $requete_integre_station"; |
| } else { |
| $reponse['id_station'] = $this->rechercheIdStation(); |
| $reponse['info'] = ''; |
| } |
| return $reponse; |
| } |
| |
| // Retourne le dernier identifiant de la table station |
| public function rechercheIdStation() { |
| $requete_select_id = "SELECT MAX(ss_id_station) as idMax FROM sophy_station;"; |
| $resultat_requete_id = $this->recuperer($requete_select_id); |
| return $resultat_requete_id['idMax']; |
| } |
| |
| public function rechercherCoordonneesWgs() { |
| $requete = "SELECT `ss_longitude_wgs` as longitude, `ss_latitude_wgs` as latitude |
| FROM `sophy_station` |
| GROUP BY `longitude` , `latitude`"; |
| /* Les stations restantes avec coordonnées sans code insee |
| SELECT COUNT( * ) AS `Lignes` , `ss_longitude_wgs` , `ss_latitude_wgs` |
| FROM `sophy_station` |
| WHERE `ss_latitude_wgs` != '' |
| AND `ss_code_insee_calculee` =0 |
| GROUP BY `ss_longitude_wgs` , `ss_latitude_wgs` |
| ORDER BY `Lignes` DESC |
| */ |
| $resultat = $this->recupererTous($requete); |
| return $resultat; |
| } |
| |
| public function creerColonneCodeInseeCalculee() { |
| $create = "ALTER TABLE `sophy_station` ADD `ss_code_insee_calculee` VARCHAR( 5 ) NOT NULL , |
| ADD INDEX ( `ss_code_insee_calculee` )"; |
| $this->requeter($create); |
| } |
| |
| public function ajouterCodeInseeCalculee($latitude, $longitude, $code_insee) { |
| $insert = "UPDATE `sophy_station` SET `ss_code_insee_calculee` = '$code_insee' ". |
| "WHERE ss_latitude_wgs = '$latitude' AND ss_longitude_wgs = '$longitude'"; |
| $this->requeter($insert); |
| } |
| |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requête d'intégration sur table sophy_releve |
| public function integrerReleve($titre, $numReleve, $idStation) { |
| $requete_integre_releve = "INSERT INTO sophy_releve VALUES (".$titre['numPubli'].", ". |
| $titre['numTableau'].", ".$numReleve.", ".$idStation.");"; |
| $reponse_requete_int_tab = $this->requeter($requete_integre_releve); |
| if ($reponse_requete_int_tab === false) { |
| $info = "releve ".$titre['numPubli']."-".$titre['numTableau']."-".$numReleve." non intégrée."; |
| echo $requete_integre_releve."\n"; |
| } else { |
| $info = ''; |
| } |
| return $info; |
| } |
| |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requête d'intégration sur table sophy_observation |
| public function integrerObservation($observations) { |
| $requete = "INSERT INTO `sophy_observation` (`so_id_publi`, `so_id_tableau`, `so_id_releve`, `so_num_ligne`,". |
| " `so_id_taxon`, `so_id_strate`, `so_ce_abondance`) VALUES "; |
| foreach ($observations as $plante) { |
| if (isset($plante)) { |
| foreach ($plante as $observation) { |
| $observation = array_map(array($this, 'proteger'), $observation); |
| $requete .= " (".implode(', ', $observation)." ),"; |
| } |
| } |
| } |
| $requete = substr($requete,0,-1).";"; |
| $resultat = $this->requeter($requete); |
| if ($resultat === false) { |
| echo $requete."\n"; |
| $info = " n'est pas intégrée.\n"; |
| } else { |
| $info = ''; |
| } |
| return $info; |
| } |
| |
| |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requête pour calculer les statistiques sur toutes les tables |
| public function getNombreLigne($tables) { |
| $requete = null; |
| foreach ($tables as $nomTable => $colonnes) { |
| $requete = "SELECT COUNT(*) as nombreTotal"; |
| foreach ($colonnes as $recherche => $nom) { |
| $requete .= ", count(distinct {$recherche}) as {$nom}"; |
| } |
| $requete .= " FROM {$nomTable}; "; |
| $resultat[$nomTable] = $this->recupererTous($requete); |
| } |
| return $resultat; |
| } |
| |
| // +-------------------------------------------------------------------------------------------------------------------+ |
| // Requête de création et d'insertion sur table sophy_tapir |
| public function creerTapir() { |
| $info = 'Créé'; |
| $requete = "DROP TABLE IF EXISTS `sophy_tapir`; |
| CREATE table `sophy_tapir` AS |
| SELECT CONCAT(_utf8'urn:lsid:tela-botanica.org:sophy:',o.so_id_publi,'.', |
| o.so_id_tableau,'.',o.so_id_releve,'.',o.so_num_ligne,'.', |
| o.so_id_taxon,'.',o.so_id_strate) AS `guid`, |
| CONCAT(o.so_id_publi,'.',o.so_id_tableau,'.',o.so_id_releve,'.', |
| o.so_num_ligne,'.',o.so_id_taxon,'.',o.so_id_strate) AS `observation_id`, |
| p.sp_date AS observation_date, |
| t.st_nom AS nom_scientifique_complet, |
| CONCAT(s.ss_code_departement,s.ss_code_insee_commune) AS lieu_commune_code_insee, |
| s.ss_localisation AS `lieu_station_nom`, |
| s.ss_latitude_wgs AS `lieu_station_latitude`, |
| s.ss_longitude_wgs AS `lieu_station_longitude`, |
| s.ss_utmEasting AS `lieu_station_utm_est`, |
| s.ss_utmNorthing AS `lieu_station_utm_nord`, |
| s.ss_utmZone AS `lieu_station_utm_zone`, |
| p.sp_auteur AS observateur_nom_complet |
| FROM sophy_observation o LEFT JOIN sophy_taxon t ON (o.so_id_taxon = t.st_id_taxon) |
| LEFT JOIN sophy_releve r ON (r.sr_id_publi = o.so_id_publi AND r.sr_id_tableau = o.so_id_tableau AND r.sr_id_releve = o.so_id_releve ) |
| LEFT JOIN sophy_station s ON (r.sr_id_station = s.ss_id_station) |
| LEFT JOIN sophy_publication p ON (r.sr_id_publi = p.sp_id_publi);"; |
| $reponse = $this->requeter($requete); |
| if ($reponse === false) { |
| $info = "Erreur"; |
| } |
| return $info; |
| } |
| } |
| ?> |