Subversion Repositories eFlore/Applications.cel

Rev

Rev 3443 | Rev 3448 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
3301 delphine 1 
<?php
2 
// declare(encoding='UTF-8');
3 
/**
4 
 * Migration des données CEL vers la base 2019 après avoir lancer le script nettoyage
5 
 *
6 
 * Utilisation :
7 
 *  - migrer les mots-clés obs : <code>/opt/lamp/bin/php cli.php migration_mots_cles -a obs</code>
8 
 *  - migrer les mots-clés images : <code>/opt/lamp/bin/php cli.php migration_mots_cles -a images</code>
9 
 *
10 
 * @category   CEL
11 
 * @package    Scripts
12 
 * @subpackage Migration : Mots-Clés
13 
 * @author     Mathias CHOUET <mathias@tela-botanica.org>
14 
 * @author     Jean-Pascal MILCENT <jpm@tela-botanica.org>
15 
 * @author     Aurelien PERONNET <aurelien@tela-botanica.org>
16 
 * @license    GPL v3 <http://www.gnu.org/licenses/gpl.txt>
17 
 * @license    CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
18 
 * @copyright  1999-2014 Tela Botanica <accueil@tela-botanica.org>
19 
 */
20 
 
3302 delphine 21 
class Migrat2019 extends Script {
3301 delphine 22 
 
23 
	private $mode;
3440 delphine 24 
	private $bd_new_cel = "tb_new_cel";
3301 delphine 25 
 
26 
	public function __construct($script_nom, $parametres_cli) {
27 
	    parent::__construct($script_nom, $parametres_cli);
28 
	    $this->bdd = new Bdd();
3440 delphine 29 
	    $this->bd_new_cel = Config::get('nettoyage.cel_new');
30 
	    $this->bd_cel = Config::get('nettoyage.cel_nettoye');
3301 delphine 31 
	}
32 
 
33 
	public function executer() {
34 
		$cmd = $this->getParametre('a');
35 
		$this->mode_verbeux = $this->getParametre('v');
36 
 
37 
		switch($cmd) {
3443 delphine 38 
		    case 'tout' :
39 
		        $this->migrerObservations();
40 
		        $this->migrerObsEtendusChampsUtilisateur();
41 
		        $this->migrerObsEtendusUtilisateur();
42 
		        $this->migrerObsEtendusProjet();
43 
		        $this->migrerMotsClesObs();
44 
		        $this->migrerMotsClesObsLiaison();
45 
		        $this->migrerImages();
46 
		        $this->migrerMotsClesImages();
47 
		        $this->migrerMotsClesImagesLiaison();
48 
		        $this->ajouterIdProjet();
49 
		        break;
3301 delphine 50 
			case 'obs':
51 
				$this->migrerObservations();
52 
				break;
53 
			case 'obs_etendus':
3306 delphine 54 
			    $this->migrerObsEtendusChampsUtilisateur();
55 
			    $this->migrerObsEtendusUtilisateur();
56 
			    $this->migrerObsEtendusProjet();
3301 delphine 57 
				break;
58 
			case 'obs_mots_cles':
59 
			    $this->migrerMotsClesObs();
60 
			    $this->migrerMotsClesObsLiaison();
61 
			    break;
62 
			case 'images':
63 
			    $this->migrerImages();
64 
			    break;
65 
			case 'images_tag':
66 
			    $this->migrerMotsClesImages();
67 
			    $this->migrerMotsClesImagesLiaison();
68 
			    break;
3443 delphine 69 
			case 'projet_id':
70 
			    $this->ajouterIdProjet();
71 
			    break;
3301 delphine 72 
			default:
73 
				echo 'Méthode inconnue, les méthodes possibles sont obs et images'."\n";
74 
		}
75 
	}
76 
 
77 
	private function migrerObservations() {
3440 delphine 78 
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`occurrence` ADD project varchar(50);";
3305 delphine 79 
	    $this->bdd->requeter($requete);
80 
 
3440 delphine 81 
		$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence`
3305 delphine 82 
            (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)
3440 delphine 83 
            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,
84 
				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,
3441 delphine 85 
				CONCAT('{\"type\":\"Point\",\"coordinates\":[', longitude, ',', latitude,']}'),  altitude, geodatum, zone_geo, ce_zone_geo, lieudit,  milieu, locality_consistency, station, published_location, pays,  nom_referentiel
3442 delphine 86 
            FROM ".$this->bd_cel.".`cel_obs`";
3440 delphine 87 
 
3301 delphine 88 
		$this->bdd->requeter($requete);
89 
	}
90 
 
91 
	private function migrerObsEtendusChampsUtilisateur() {
3440 delphine 92 
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`user_custom_field` ADD `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";
3301 delphine 93 
	    $this->bdd->requeter($requete);
3440 delphine 94 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field`
3301 delphine 95 
	        (name, data_type, default_value, field_id)
3443 delphine 96 
	        SELECT label, 'text', '', cle
3442 delphine 97 
	        FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus`
3444 delphine 98 
	        WHERE `cle` not in (SELECT `champ` FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus_liaison`)";
3301 delphine 99 
	    $this->bdd->requeter($requete);
100 
	}
101 
 
102 
	private function migrerObsEtendusUtilisateur() {
3440 delphine 103 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence`
3301 delphine 104 
	        (`occurrence_id`, `user_custom_field_id`, `value`)
105 
	        SELECT `id_observation`, id, `valeur`
3442 delphine 106 
	        FROM ".$this->bd_cel.".`cel_obs_etendues`
3443 delphine 107 
	        RIGHT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle
3444 delphine 108 
			WHERE id_observation in (select id FROM ".$this->bd_new_cel.".`occurrence`)";
3301 delphine 109 
	    $this->bdd->requeter($requete);
110 
	}
111 
 
112 
 
113 
	private function migrerObsEtendusProjet() {
3440 delphine 114 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence`
115 
	        (`occurrence_id`, `extended_field_id`, `value`)
3301 delphine 116 
	        SELECT `id_observation`, id, `valeur`
3442 delphine 117 
	        FROM ".$this->bd_cel.".`cel_obs_etendues`
3440 delphine 118 
	        RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle
3443 delphine 119 
			WHERE id_observation in (select id FROM ".$this->bd_new_cel.".`occurrence`)";
3301 delphine 120 
	    $this->bdd->requeter($requete);
121 
	}
122 
 
123 
	private function migrerMotsClesObs() {
3440 delphine 124 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag
3301 delphine 125 
	        (id, user_id, name, path)
126 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
3442 delphine 127 
	        FROM ".$this->bd_cel.".`cel_arbre_mots_cles_obs`";
3301 delphine 128 
	    $this->bdd->requeter($requete);
129 
	}
130 
 
131 
	private function migrerMotsClesObsLiaison() {
3440 delphine 132 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".occurrence_user_occurrence_tag
3301 delphine 133 
	        (occurrence_id, user_occurrence_tag_id)
3442 delphine 134 
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_obs_liaison`";
3301 delphine 135 
	    $this->bdd->requeter($requete);
136 
	}
137 
 
138 
	private function migrerImages() {
3440 delphine 139 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo
3311 delphine 140 
	        (id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type,
3301 delphine 141 
	        date_updated, date_created, date_linked_to_occurrence, content_url, url)
142 
	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
143 
	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
144 
	        date_creation, date_liaison, '', concat('http://api.tela-botanica.org/img:000', '', `id_image`,
145 
	        'O.jpg')
3443 delphine 146 
	        FROM ".$this->bd_cel.".cel_images";
3301 delphine 147 
	    $this->bdd->requeter($requete);
148 
	}
149 
 
150 
	private function migrerMotsClesImages() {
3440 delphine 151 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag
3301 delphine 152 
	        (id, user_id, name, path)
153 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
3442 delphine 154 
	        FROM ".$this->bd_cel.".`cel_arbre_mots_cles_images`";
3301 delphine 155 
	    $this->bdd->requeter($requete);
156 
	}
157 
 
158 
	private function migrerMotsClesImagesLiaison() {
3440 delphine 159 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag_photo
3301 delphine 160 
	        (photo_id, photo_tag_id)
3442 delphine 161 
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_images_liaison`";
3301 delphine 162 
	    $this->bdd->requeter($requete);
163 
	}
3443 delphine 164 
 
165 
	private function ajouterIdProjet() {
166 
	    $requete = "UPDATE ".$this->bd_new_cel.".`project_settings` SET `project_id` = `id`";
167 
	    $this->bdd->requeter($requete);
3444 delphine 168 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`tb_project`(`id`, `label`, `is_private`) SELECT `id`, `project`, '0' FROM ".$this->bd_new_cel.".`project_settings` ";
3443 delphine 169 
	    $this->bdd->requeter($requete);
3444 delphine 170 
	    $requete = "UPDATE ".$this->bd_new_cel.".`extended_field` f right join ".$this->bd_new_cel.".`project_settings` s on
3443 delphine 171 
	        s.`project` = f.`project` SET f.`project_id` = s.`project_id`";
172 
	    $this->bdd->requeter($requete);
3444 delphine 173 
	    $requete = "UPDATE ".$this->bd_new_cel.".`occurrence` o right join ".$this->bd_new_cel.".`project_settings` s on
3443 delphine 174 
	        s.`project` = o.`project` SET o.`project_id` = s.`project_id`";
175 
	    $this->bdd->requeter($requete);
176 
	}
3301 delphine 177 
 
178 
 
179 
}