Subversion Repositories Sites.obs-saisons.fr

Compare Revisions

Ignore whitespace Rev 206 → Rev 207

/trunk/applications/jrest/services/OdsSpipVersDrupalMigration.php
New file
0,0 → 1,504
<?php
 
class OdsSpipVersDrupalMigration extends OdsTriple {
 
const PREFIXE = 'get';
const BDD_DRUPAL = 'ods_redesign';
const BDD_ANCIEN_ODS = 'ods';
const BDD_NOUVEAU_ODS = 'ods_saisie';
/**
* Méthode appelée avec une requête de type GET.
*
*/
function getElement($param = array()) {
$type = $param[0];
if ($type == '*' || is_numeric($type)) {
$info = $this->getElementParDefaut($param);
} else {
$methode = self::PREFIXE.$type;
echo $methode;
if (method_exists($this, $methode)) {
array_shift($param);
$info = $this->$methode($param);
} else {
$this->messages[] = "Le type d'information demandé '$type' n'est pas disponible.";
}
}
// Envoi sur la sortie standard
$this->envoyer($info);
}
/**
* Méthode appelée pour ajouter un élément.
*/
public function createElement($params) {
print_r($params);
$this->envoyer();
}
private function getElementParDefaut($param) {
}
private function getReparationCommunes() {
$requete_stations_sans_communes = 'SELECT * FROM ods_saisie.ods_stations WHERE os_ce_commune = "NULL"';
$stations_sans_communes = $this->executerRequete($requete_stations_sans_communes);
$z = 0;
$nb_com = count($stations_sans_communes);
$ct = 0 ;
foreach($stations_sans_communes as $station_sans_commune) {
echo $ct.' ';
$nom_station = $station_sans_commune['os_nom'];
$id_station = $station_sans_commune['os_id_station'];
$nom_station_joker = str_replace('-','_',$nom_station);
$nom_station_joker = str_replace(' ','_',$nom_station);
$requete_recherche_nom = 'SELECT oc_code_insee FROM ods_saisie.ods_communes WHERE oc_nom LIKE "'.$nom_station_joker.'"';
 
$recherche_nom = $this->executerRequete($requete_recherche_nom);
if(!empty($recherche_nom)) {
$requete_maj_nom = 'UPDATE ods_stations set os_ce_commune = '.$recherche_nom[0]['oc_code_insee'].' WHERE os_id_station = '.$id_station;
echo $requete_maj_nom.'<br />';
$modif_nom = $this->executerRequeteSimple($requete_maj_nom);
$z++;
} else {
echo "rien trouvé pour la station ".$nom_station.'<br />';
}
$ct++;
}
echo $z.' stations réparées sur '.$nb_com;
 
$requete_altitude_communes = 'SELECT * FROM ods_communes_temp';
$res_alt_communes = $this->executerRequete($requete_altitude_communes);
}
private function getMigrationParticipants() {
$requete_participants_spip = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.PARTICIPANT WHERE PARTICIPANT_ID > 4';
$participants_spip = $this->executerRequete($requete_participants_spip);
$requete_insertion_participants_drupal = 'INSERT INTO '.self::BDD_DRUPAL.'.drupal_users '.
'(uid, name, pass, mail, created, access, login, status, timezone, language, init) '.
'VALUES ';
foreach($participants_spip as $participant_spip) {
$requete_insertion_participants_drupal .= '('.
$this->proteger($participant_spip['PARTICIPANT_ID']).', '.
$this->proteger($participant_spip['PARTICIPANT_PSEUDO']).', '.
$this->proteger(md5($participant_spip['PARTICIPANT_MOTDEPASSE'])).', '.
$this->proteger($participant_spip['PARTICIPANT_EMAIL']).', '.
$this->proteger(strtotime($participant_spip['PARTICIPANT_DATE_INSCRIPTION'])).', '.
$this->proteger(strtotime($participant_spip['PARTICIPANT_DATE_INSCRIPTION'])).', '.
$this->proteger(strtotime($participant_spip['PARTICIPANT_DATE_INSCRIPTION'])).', '.
$this->proteger('1').','.
$this->proteger('7200').', '.
$this->proteger('fr').', '.
$this->proteger($participant_spip['PARTICIPANT_EMAIL']).' '.
'),';
}
 
$requete_insertion_participants_drupal = rtrim($requete_insertion_participants_drupal,',');
 
$this->executerRequeteSimple($requete_insertion_participants_drupal);
}
private function getMigrationStations($liste_evenements) {
$this->supprimerDoublonStation();
$requete_communes_anciennes_stations = 'SELECT STATION_ID, COMMUNE.COMMUNE_ID, COMMUNE.COMMUNE_NOM, COMMUNE.COMMUNE_CODEPOSTAL '.
'FROM '.self::BDD_ANCIEN_ODS.'.SEQUENCE '.
'LEFT JOIN '.self::BDD_ANCIEN_ODS.'.COMMUNE '.
' ON '.self::BDD_ANCIEN_ODS.'.COMMUNE.COMMUNE_ID = '.self::BDD_ANCIEN_ODS.'.SEQUENCE.COMMUNE_ID '.
'GROUP BY STATION_ID';
$res_communes_anciennes_stations = $this->executerRequete($requete_communes_anciennes_stations);
$communes_anciennes_stations = array();
foreach($res_communes_anciennes_stations as $com_stat) {
$id_station = $com_stat['STATION_ID'];
$communes_anciennes_stations[$id_station]['nom_commune'] = $com_stat['COMMUNE_NOM'];
$communes_anciennes_stations[$id_station]['id_commune'] = $com_stat['COMMUNE_ID'];
$communes_anciennes_stations[$id_station]['code_postal_commune'] = $com_stat['COMMUNE_CODEPOSTAL'];
}
$requete_selection_station_ancien = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.STATION WHERE PARTICIPANT_ID > 5';
$stations_anciennes = $this->executerRequete($requete_selection_station_ancien);
$requete_insertion_station_nouveau = 'INSERT INTO '.self::BDD_NOUVEAU_ODS.'.ods_stations '.
'(os_id_station, os_ce_participant, os_nom, os_ce_commune, os_latitude, os_longitude, os_altitude, os_ce_environnement, os_commentaire) '.
'VALUES ';
foreach($stations_anciennes as $station_ancienne) {
$nouvel_id_environnement = $this->getNouvelIdEnvironnement($station_ancienne['STATION_ENVIRONNEMENT_ID']);
$id_commune = $communes_anciennes_stations[$station_ancienne['STATION_ID']]['id_commune'];
$nom_commune = $communes_anciennes_stations[$station_ancienne['STATION_ID']]['nom_commune'];
$cp_commune = $communes_anciennes_stations[$station_ancienne['STATION_ID']]['code_postal_commune'];
$code_insee_commune = $this->getCodeInseePourNomEtCP($nom_commune,$cp_commune);
// Correction des quelques noms de stations vides
if(trim($station_ancienne['STATION_NOM']) == '') {
$station_ancienne['STATION_NOM'] = 'station inconnue';
}
$requete_insertion_station_nouveau .= '('.
$this->proteger($station_ancienne['STATION_ID']).', '.
$this->proteger($station_ancienne['PARTICIPANT_ID']).', '.
$this->proteger($station_ancienne['STATION_NOM']).', '.
$this->proteger($code_insee_commune).', '.
$this->proteger($station_ancienne['STATION_LATITUDE']).', '.
$this->proteger($station_ancienne['STATION_LONGITUDE']).', '.
$this->proteger($station_ancienne['STATION_ALTITUDE']).', '.
$this->proteger($nouvel_id_environnement).', '.
'""'.
'),';
 
}
$requete_insertion_station_nouveau = rtrim($requete_insertion_station_nouveau,',');
$this->executerRequeteSimple($requete_insertion_station_nouveau);
}
private function getMigrationIndividus() {
$anciennes_espece = $this->getAnciennesEspeceGroupeesParNomSci();
$nouvelles_especes = $this->getEspeceGroupeesParNomSci();
$requete_selection_sequence = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.SEQUENCE ';
$sequences = $this->executerRequete($requete_selection_sequence);
$requete_insertion_sequence_nouveau = 'INSERT INTO '.self::BDD_NOUVEAU_ODS.'.ods_individus '.
'(oi_ce_espece, oi_ce_station, oi_nom) '.
'VALUES ';
$compteur_nom = 1;
$obs_a_migrer_par_individu = array();
foreach($sequences as $sequence) {
$nom_sci_espece = $anciennes_espece[$sequence['ESPECE_ID']];
$id_nouvelle_espece = $nouvelles_especes[$nom_sci_espece];
$requete_insertion_sequence_nouveau .= '('.
$this->proteger($id_nouvelle_espece).', '.
$this->proteger($sequence['STATION_ID']).', '.
$this->proteger('individu_'.$compteur_nom).' '.
'),';
$obs_a_migrer_par_individu['individu_'.$compteur_nom]['sequence'] = $sequence['SEQUENCE_ID'];
$compteur_nom++;
}
$requete_insertion_sequence_nouveau = rtrim($requete_insertion_sequence_nouveau,',');
$this->executerRequeteSimple($requete_insertion_sequence_nouveau);
}
private function getMigrationObservations() {
$anciennes_espece = $this->getAnciennesEspeceGroupeesParNomSci();
$nouvelles_especes = $this->getEspeceGroupeesParNomSci();
$requete_selection_mesure = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.MESURE ORDER BY MESURE_DATE';
$mesures = $this->executerRequete($requete_selection_mesure);
$requete_selection_sequence = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.SEQUENCE';
$sequences = $this->executerRequete($requete_selection_sequence);
$requete_selection_station = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.STATION';
$stations = $this->executerRequete($requete_selection_station);
$sequences_en_bordel = array();
$mesures_a_debordeliser = array();
$stations_a_classer = array();
foreach($stations as $station) {
$stations_a_classer[$station['STATION_ID']] = $station['PARTICIPANT_ID'];
}
foreach($sequences as $sequence) {
$sequences_en_bordel[$sequence['SEQUENCE_ID']]['station'] = $sequence['STATION_ID'];
$sequences_en_bordel[$sequence['SEQUENCE_ID']]['espece'] = $sequence['ESPECE_ID'];
}
foreach($mesures as $mesure) {
$station_de_mesure = $sequences_en_bordel[$mesure['SEQUENCE_ID']]['station'];
$espece_de_mesure = $sequences_en_bordel[$mesure['SEQUENCE_ID']]['espece'];
$individu_de_mesure = $mesure['MESURE_INDIVIDU'];
$evenement = $mesure['EVENEMENT_ID'];
$date_evenement = $mesure['MESURE_DATE'];
$id_participant = $stations_a_classer[$sequences_en_bordel[$mesure['SEQUENCE_ID']]['station']];
$mesures_a_debordeliser[$id_participant][$station_de_mesure][$espece_de_mesure][$individu_de_mesure][$evenement][] = $date_evenement;
}
$requete_insertion_individus_nouveau = 'INSERT INTO '.self::BDD_NOUVEAU_ODS.'.ods_individus '.
'(oi_id_individu, oi_ce_espece, oi_ce_station, oi_nom) '.
'VALUES ';
$requete_insertion_observations_nouveau = 'INSERT INTO '.self::BDD_NOUVEAU_ODS.'.ods_observations '.
'(oo_ce_participant, oo_ordre, oo_ce_individu, oo_ce_evenement,oo_date,oo_commentaire,oo_date_saisie,oo_date_modification) '.
'VALUES ';
$compteur_id_temp = 1;
$tableau_ancien_id_individu_nom_individu = array();
foreach($mesures_a_debordeliser as $id_participant => $stations_participant) {
$compteur_ordre_obs = 1;
foreach($stations_participant as $station => $especes) {
$id_station_individu = $station;
foreach($especes as $id_espece => $individus_espece) {
$nom_sci_espece = $anciennes_espece[$id_espece];
$id_nouvelle_espece = $nouvelles_especes[$nom_sci_espece];
foreach($individus_espece as $num_individu => $evenements) {
$nom_individu = 'individu_'.$num_individu;
$id_individu_temp = $compteur_id_temp;
foreach($evenements as $id_evenement => $evenement_individu) {
$nouvel_id_evenement = $this->getNouvelIdEvenement($id_evenement);
foreach($evenement_individu as $date_evenement) {
$requete_insertion_observations_nouveau .= '('.$this->proteger($id_participant).', '.
$this->proteger($compteur_ordre_obs).', '.
$this->proteger($compteur_id_temp).', '.
$this->proteger($nouvel_id_evenement).', '.
$this->proteger($date_evenement).', '.
"'',".
$this->proteger($date_evenement).', '.
$this->proteger($date_evenement).
'),';
$compteur_ordre_obs++;
}
}
$requete_insertion_individus_nouveau .= '('.
$this->proteger($compteur_id).', '.
$this->proteger($id_nouvelle_espece).', '.
$this->proteger($id_station_individu).', '.
$this->proteger($nom_individu).
'),';
$compteur_id_temp++;
}
}
}
}
$requete_insertion_individus_nouveau = rtrim($requete_insertion_individus_nouveau,',');
$this->executerRequeteSimple($requete_insertion_individus_nouveau);
$requete_insertion_observations_nouveau = rtrim($requete_insertion_observations_nouveau,',');
$this->executerRequeteSimple($requete_insertion_observations_nouveau);
}
private function getMigrationCommunes() {
$requete_communes = 'SELECT * FROM locations';
$communes = $this->executerRequete($requete_communes);
$requete_altitude_communes = 'SELECT * FROM ods_communes_temp';
$res_alt_communes = $this->executerRequete($requete_altitude_communes);
$altitudes_communes = array();
foreach($res_alt_communes as $commune_alt) {
$altitudes_communes[$commune_alt['oc_code_insee']] = $commune_alt['oc_altitude'];
}
$requete_insertion_communes_ods = 'INSERT INTO ods_communes '.
'(oc_code_insee, oc_nom, oc_secteur, oc_x_utm, oc_y_utm, oc_latitude, oc_longitude, oc_altitude) '.
'VALUES ';
foreach($communes as $commune) {
$lat_long = $this->convertirUtmVersLatLong($commune['x_utm'], $commune['y_utm'], $commune['sector']);
$altitude = $altitudes_communes[$commune['insee_code']];
$requete_insertion_communes_ods .= '('.
$this->proteger($commune['insee_code']).','.
$this->proteger($commune['name']).','.
$this->proteger($commune['sector']).','.
$this->proteger($commune['x_utm']).','.
$this->proteger($commune['y_utm']).','.
$this->proteger($lat_long['lat']).','.
$this->proteger($lat_long['long']).','.
$this->proteger($altitude).
'),';
}
$requete_insertion_communes_ods = rtrim($requete_insertion_communes_ods,',');
$this->executerRequeteSimple($requete_insertion_communes_ods);
}
private function convertirUtmVersLatLong($x, $y, $sector) {
$lat_long = array();
$convertisseur = new gPoint();
$convertisseur->setUTM($x, $y, $sector);
$convertisseur->convertTMtoLL();
$lat_long['lat'] = $convertisseur->Lat();
$lat_long['long'] = $convertisseur->Long();
return $lat_long;
}
private function getNouvelIdEnvironnement($ancien_id_environnement) {
$ids_env = array('1' => '7','2' => '8','3' => '9','4' => '10','6' => '11');
return $ids_env[$ancien_id_environnement];
}
private function getNouvelIdEvenement($ancien_id_evenement) {
$ids_evts = array('1' => '13',
'2' => '14',
'3' => '15',
'4' => '16',
'5' => '17',
'6' => '18',
'7' => '19',
'8' => '20');
return $ids_evts[$ancien_id_evenement];
}
private function getEspeceGroupeesParNomSci() {
$requete_espece = 'SELECT * FROM '.self::BDD_NOUVEAU_ODS.'.ods_especes';
$especes = $this->executerRequeteSimple($requete_espece);
$especes_ordonnees = array();
foreach($especes as $espece) {
$indice = strtolower(str_replace(' ','_', $espece['oe_nom_scientifique']));
$especes_ordonnees[$indice] = $espece['oe_id_espece'];
}
return $especes_ordonnees;
}
private function getAnciennesEspeceGroupeesParNomSci() {
$requete_espece = 'SELECT * FROM '.self::BDD_ANCIEN_ODS.'.ESPECE';
$especes = $this->executerRequete($requete_espece);
$especes_ordonnees = array();
foreach($especes as $espece) {
$nom_sci = strtolower(str_replace(' ','_', $espece['ESPECE_NOM_SCIENTIFIQUE']));
$especes_ordonnees[$espece['ESPECE_ID']] = $nom_sci;
}
return $especes_ordonnees;
}
private function getCodeInseePourNomEtCP($nom_commune, $cp) {
if(trim($nom_commune) == '') {
return 'NULL';
}
$limite = 2;
if(strlen($cp) == 4) {
$limite = 1;
}
$dpt = substr($cp,0,$limite);
$requete_code_insee = 'SELECT oc_code_insee '.
'FROM '.self::BDD_NOUVEAU_ODS.'.ods_communes '.
'WHERE oc_nom = '.$this->proteger($nom_commune).' '.
'AND oc_code_insee LIKE "'.$dpt.'___" '.
'LIMIT 1';
$infos_code_insee_commune = $this->executerRequete($requete_code_insee);
if(empty($infos_code_insee_commune)) {
// a migrer manuellement
return '_migrer_manu_'.$nom_commune;
}
return $infos_code_insee_commune[0]['oc_code_insee'];
}
private function supprimerDoublonStation() {
$requete = 'SELECT STATION_ID, STATION_NOM, PARTICIPANT_ID FROM '.self::BDD_ANCIEN_ODS.'.STATION ORDER BY PARTICIPANT_ID, STATION_NOM, STATION_ID ';
$res = $this->executerRequete($requete);
$stations = array();
foreach($res as $station) {
$stations[$station['PARTICIPANT_ID']][$station['STATION_NOM']][] = $station['STATION_ID'];
}
foreach($stations as $participant => $stations) {
foreach($stations as $station_nom => $doublons) {
if(count($doublons) > 1) {
$id_garde = $doublons[0];
$ids_a_supprimer = implode(',',array_slice($doublons, 1));
$requete_fusion_stations = 'DELETE FROM '.self::BDD_ANCIEN_ODS.'.STATION WHERE STATION_ID IN ('.$ids_a_supprimer.') AND PARTICIPANT_ID = '.$participant;
$this->executerRequeteSimple($requete_fusion_stations);
$requete_fusion_stations_obs = 'UPDATE '.self::BDD_ANCIEN_ODS.'.SEQUENCE
SET STATION_ID = '.$id_garde.' '.
'WHERE STATION_ID IN ('.$ids_a_supprimer.') '.
'AND PARTICIPANT_ID = '.$participant;
$this->executerRequeteSimple($requete_fusion_stations_obs);
}
}
}
}
}
?>