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