Subversion Repositories eFlore/Applications.cel

<?php

// declare(encoding='UTF-8');

/**

 * Migration des données CEL vers la base 2019 après avoir lancer le script nettoyage

 *

 * Utilisation :

 *  - migrer les mots-clés obs : <code>/opt/lamp/bin/php cli.php migration_mots_cles -a obs</code>

 *  - migrer les mots-clés images : <code>/opt/lamp/bin/php cli.php migration_mots_cles -a images</code>

 *

 * @category   CEL

 * @package    Scripts

 * @subpackage Migration : Mots-Clés

 * @author     Mathias CHOUET <mathias@tela-botanica.org>

 * @author     Jean-Pascal MILCENT <jpm@tela-botanica.org>

 * @author     Aurelien PERONNET <aurelien@tela-botanica.org>

 * @license    GPL v3 <http://www.gnu.org/licenses/gpl.txt>

 * @license    CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>

 * @copyright  1999-2014 Tela Botanica <accueil@tela-botanica.org>

 */

class Migrat2019 extends Script {

	private $mode;

	private $base = "tb_new_cel";

	public function __construct($script_nom, $parametres_cli) {

	    parent::__construct($script_nom, $parametres_cli);

	    $this->bdd = new Bdd();

	}

	public function executer() {

		$cmd = $this->getParametre('a');

		$this->mode_verbeux = $this->getParametre('v');

		switch($cmd) {

			case 'obs':

				$this->migrerObservations();

				break;

			case 'obs_etendus':

				$this->migrerObsEtendus();

				break;

			case 'obs_mots_cles':

			    $this->migrerMotsClesObs();

			    $this->migrerMotsClesObsLiaison();

			    break;

			case 'images':

			    $this->migrerImages();

			    break;

			case 'images_tag':

			    $this->migrerMotsClesImages();

			    $this->migrerMotsClesImagesLiaison();

			    break;

			default:

				echo 'Méthode inconnue, les méthodes possibles sont obs et images'."\n";

		}

	}

	private function migrerObservations() {

	    $requete = "ALTER TABLE ".$this->base.".`occurrence` ADD project varchar(50);";

	    $this->bdd->requeter($requete);

		$requete = "INSERT INTO ".$this->base.".`occurrence`

            (id, project, user_id, user_email, user_pseudo, date_observed, date_created, date_updated, date_published, user_sci_name, user_sci_name_id, accepted_sci_name, accepted_sci_name_id, family, certainty, annotation, coef, phenology, input_source, is_public, is_visible_in_cel, geometry, elevation, geodatum, locality, locality_insee_code, sublocality, environment, locality_consistency, station, published_location, osm_country, taxo_repo)

            SELECT id_observation,  CASE WHEN `mots_cles_texte` like '%sauvages%' then 'sauvages' WHEN `mots_cles_texte` like '%missions-flore%' then 'missions-flore'  WHEN `mots_cles_texte` like '%arbres-tetards%' then 'arbres-tetards' WHEN `mots_cles_texte` like '%arbres-remarquables%' then 'arbres-remarquables' WHEN `mots_cles_texte` like '%bellesdemarue%' then 'bellesdemarue' WHEN `mots_cles_texte` like '%biodiversite34%' then 'biodiversite34'  WHEN `mots_cles_texte` like '%messicoles%' then 'messicoles' WHEN `mots_cles_texte` like '%florileges%' then 'florileges' END,  ce_utilisateur, courriel_utilisateur, concat (prenom_utilisateur, ' ', nom_utilisateur),  date_observation, date_creation, date_modification, date_transmission,  nom_sel, nom_sel_nn, nom_ret, nom_ret_nn,  famille, certitude, commentaire, abondance,  phenologie, input_source, transmission, 1,  PointFromText(CONCAT('POINT(',longitude, ' ', latitude,')')),  altitude, geodatum, zone_geo, ce_zone_geo, lieudit,  milieu, locality_consistency, station, published_location, pays,  nom_referentiel

            FROM tb_nettoye_cel.`cel_obs`";

		$this->bdd->requeter($requete);

	}

	private function migrerObsEtendusChampsUtilisateur() {

	    $requete = "ALTER TABLE `user_custom_field` ADD `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";

	    $this->bdd->requeter($requete);

	    $requete = "INSERT INTO ".$this->base.".`user_custom_field`

	        (name, data_type, default_value, field_id)

	        SELECT label, type, valeur, cle

	        FROM tb_nettoye_cel.`cel_catalogue_champs_etendus`

	        WHERE `cle` not in (SELECT `champ` FROM `cel_catalogue_champs_etendus_liaison`)";

	    $this->bdd->requeter($requete);

	}

	private function migrerObsEtendusUtilisateur() {

	    $requete = "INSERT INTO ".$this->base.".`user_custom_field_occurrence`

	        (`occurrence_id`, `user_custom_field_id`, `value`)

	        SELECT `id_observation`, id, `valeur`

	        FROM tb_nettoye_cel.`cel_obs_etendues`

	        LEFT JOIN ".$this->base.".`user_custom_field` ON field_id = cle";

	    $this->bdd->requeter($requete);

	}

	private function migrerObsEtendusProjet() {

	    $requete = "INSERT INTO ".$this->base.".`user_custom_field_occurrence`

	        (`occurrence_id`, `user_custom_field_id`, `value`)

	        SELECT `id_observation`, id, `valeur`

	        FROM tb_nettoye_cel.`cel_obs_etendues`

	        LEFT JOIN ".$this->base.".`extended_field` ON field_id = cle";

	    $this->bdd->requeter($requete);

	}

	private function migrerMotsClesObs() {

	    $requete = "INSERT INTO ".$this->base.".user_occurence_tag

	        (id, user_id, name, path)

	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin

	        FROM tb_nettoye_cel.`cel_arbre_mots_cles_obs`";

	    $this->bdd->requeter($requete);

	}

	private function migrerMotsClesObsLiaison() {

	    $requete = "INSERT INTO ".$this->base.".occurrence_user_occurrence_tag

	        (occurrence_id, user_occurrence_tag_id)

	        SELECT `id_element_lie`, `id_mot_cle` FROM tb_nettoye_cel.`cel_mots_cles_obs_liaison`";

	    $this->bdd->requeter($requete);

	}

	private function migrerImages() {

	    $requete = "INSERT INTO ".$this->base.".photo

	        (id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type

	        date_updated, date_created, date_linked_to_occurrence, content_url, url)

	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,

	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,

	        date_creation, date_liaison, '', concat('http://api.tela-botanica.org/img:000', '', `id_image`,

	        'O.jpg')

	        FROM tb_nettoye_cel.cel_images

	        WHERE ";

	    $this->bdd->requeter($requete);

	}

	private function migrerMotsClesImages() {

	    $requete = "INSERT INTO ".$this->base.".photo_tag

	        (id, user_id, name, path)

	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin

	        FROM tb_nettoye_cel.`cel_arbre_mots_cles_images`";

	    $this->bdd->requeter($requete);

	}

	private function migrerMotsClesImagesLiaison() {

	    $requete = "INSERT INTO ".$this->base.".photo_tag_photo

	        (photo_id, photo_tag_id)

	        SELECT `id_element_lie`, `id_mot_cle` FROM tb_nettoye_cel.`cel_mots_cles_images_liaison`";

	    $this->bdd->requeter($requete);

	}

}