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; |
} |
} |
?> |