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