WebSVN – Sites.obs-saisons.fr – /trunk/applications/jrest/services/OdsSpipVersDrupalMigration.php

<?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);                             
                                }
                        }
                }
    }
}
?>
Subversion Repositories Sites.obs-saisons.fr

(root)/trunk/applications/jrest/services/OdsSpipVersDrupalMigration.php – Rev 207
