Subversion Repositories eFlore/Applications.cel

Rev

Rev 3512 | 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();
3457 delphine 40
		        $this->migrerProjet();
3443 delphine 41
		        $this->migrerObsEtendusChampsUtilisateur();
42
		        $this->migrerObsEtendusUtilisateur();
43
		        $this->migrerObsEtendusProjet();
44
		        $this->migrerMotsClesObs();
45
		        $this->migrerMotsClesObsLiaison();
46
		        $this->migrerImages();
47
		        $this->migrerMotsClesImages();
48
		        $this->migrerMotsClesImagesLiaison();
49
		        $this->ajouterIdProjet();
50
		        break;
3301 delphine 51
			case 'obs':
52
				$this->migrerObservations();
53
				break;
3457 delphine 54
			case 'projet':
55
			    $this->migrerProjet();
56
			    break;
3301 delphine 57
			case 'obs_etendus':
3306 delphine 58
			    $this->migrerObsEtendusChampsUtilisateur();
59
			    $this->migrerObsEtendusUtilisateur();
60
			    $this->migrerObsEtendusProjet();
3301 delphine 61
				break;
62
			case 'obs_mots_cles':
63
			    $this->migrerMotsClesObs();
64
			    $this->migrerMotsClesObsLiaison();
65
			    break;
66
			case 'images':
67
			    $this->migrerImages();
68
			    break;
69
			case 'images_tag':
70
			    $this->migrerMotsClesImages();
71
			    $this->migrerMotsClesImagesLiaison();
72
			    break;
3443 delphine 73
			case 'projet_id':
74
			    $this->ajouterIdProjet();
75
			    break;
3301 delphine 76
			default:
77
				echo 'Méthode inconnue, les méthodes possibles sont obs et images'."\n";
78
		}
79
	}
80
 
81
	private function migrerObservations() {
3457 delphine 82
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`occurrence` ADD IF NOT EXISTS project varchar(50);";
3305 delphine 83
	    $this->bdd->requeter($requete);
84
 
3440 delphine 85
		$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence`
3305 delphine 86
            (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 87
            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,
88
				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 89
				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 90
            FROM ".$this->bd_cel.".`cel_obs`";
3440 delphine 91
 
3301 delphine 92
		$this->bdd->requeter($requete);
93
	}
94
 
95
	private function migrerObsEtendusChampsUtilisateur() {
3457 delphine 96
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`user_custom_field` ADD IF NOT EXISTS `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";
3301 delphine 97
	    $this->bdd->requeter($requete);
3440 delphine 98
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field`
3301 delphine 99
	        (name, data_type, default_value, field_id)
3448 delphine 100
	        SELECT label, 'Texte', '', cle
3442 delphine 101
	        FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus`
3444 delphine 102
	        WHERE `cle` not in (SELECT `champ` FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus_liaison`)";
3301 delphine 103
	    $this->bdd->requeter($requete);
104
	}
105
 
106
	private function migrerObsEtendusUtilisateur() {
3440 delphine 107
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence`
3301 delphine 108
	        (`occurrence_id`, `user_custom_field_id`, `value`)
109
	        SELECT `id_observation`, id, `valeur`
3442 delphine 110
	        FROM ".$this->bd_cel.".`cel_obs_etendues`
3443 delphine 111
	        RIGHT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle
3444 delphine 112
			WHERE id_observation in (select id FROM ".$this->bd_new_cel.".`occurrence`)";
3301 delphine 113
	    $this->bdd->requeter($requete);
114
	}
115
 
116
 
117
	private function migrerObsEtendusProjet() {
3440 delphine 118
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence`
119
	        (`occurrence_id`, `extended_field_id`, `value`)
3301 delphine 120
	        SELECT `id_observation`, id, `valeur`
3442 delphine 121
	        FROM ".$this->bd_cel.".`cel_obs_etendues`
3440 delphine 122
	        RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle
3443 delphine 123
			WHERE id_observation in (select id FROM ".$this->bd_new_cel.".`occurrence`)";
3301 delphine 124
	    $this->bdd->requeter($requete);
125
	}
126
 
3457 delphine 127
	private function migrerProjet() {
3464 delphine 128
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`project_settings` (`id`, `project_id`, `project`, `language`, `title`, `logo`, `description`, `type`, `is_type`, `css_style`, `image_font`, `date_created`, `date_updated`, `taxo_restriction_type`, `taxo_restriction_value`, `location_type`, `location`, `published_location`, `environment`, `project_tag_name`, `info`)
3457 delphine 129
	        SELECT * FROM ".$this->bd_cel.".`project_settings`";
130
	    $this->bdd->requeter($requete);
131
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field` (`id`, `project_id`, `field_id`, `project`, `data_type`, `is_visible`, `is_mandatory`, `min_value`, `max_value`, `regexp`, `unit`)
132
	        SELECT * FROM ".$this->bd_cel.".`extended_field`";
133
	    $this->bdd->requeter($requete);
134
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extendedfield_translation`(`id`, `extended_field_id`, `project`, `label`, `description`, `default_value`, `error_message`, `language_iso_code`, `help`)
135
	        SELECT * FROM ".$this->bd_cel.".`extendedfield_translation`";
136
	    $this->bdd->requeter($requete);
137
	}
138
 
3301 delphine 139
	private function migrerMotsClesObs() {
3440 delphine 140
	    $requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag
3301 delphine 141
	        (id, user_id, name, path)
142
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
3442 delphine 143
	        FROM ".$this->bd_cel.".`cel_arbre_mots_cles_obs`";
3301 delphine 144
	    $this->bdd->requeter($requete);
145
	}
146
 
147
	private function migrerMotsClesObsLiaison() {
3440 delphine 148
	    $requete = "INSERT INTO ".$this->bd_new_cel.".occurrence_user_occurrence_tag
3301 delphine 149
	        (occurrence_id, user_occurrence_tag_id)
3442 delphine 150
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_obs_liaison`";
3301 delphine 151
	    $this->bdd->requeter($requete);
152
	}
153
 
154
	private function migrerImages() {
3440 delphine 155
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo
3311 delphine 156
	        (id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type,
3512 delphine 157
	        date_updated, date_created, date_linked_to_occurrence, content_url, size, url)
3301 delphine 158
	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
159
	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
3512 delphine 160
	        date_creation, date_liaison, '', '6444444',concat('https://api.tela-botanica.org/img:',
3466 delphine 161
	        lpad(`id_image`, 9, '0'), 'O')
3443 delphine 162
	        FROM ".$this->bd_cel.".cel_images";
3301 delphine 163
	    $this->bdd->requeter($requete);
3466 delphine 164
	    $requete_jpg = 'UPDATE `photo` SET `mime_type` = "image/jpeg", `content_url`= concat('.Config::get('nettoyage.chemin_photo').
3512 delphine 165
	        ',substr(lpad(id, 9, "0"),1,3),"/",substr(lpad(id, 9, "0"),4,3),"/O/",substr(lpad(id, 9, "0"),1,3),"_",substr(lpad(id, 9, "0"),4,3),"_",substr(lpad(id, 9, "0"),7,3),"_O",substr(`original_name` , char_length(`original_name`) - locate('.', reverse(`original_name`)) +1, locate('.', reverse(`original_name`))))
3466 delphine 166
	        WHERE `original_name` like "%.jp%" and content_url = ""';
167
	    $this->bdd->requeter($requete_jpg);
168
	    $requete_png = 'UPDATE `photo` SET `mime_type` = "image/png", `content_url`= concat('.Config::get('nettoyage.chemin_photo').
3512 delphine 169
	        ',substr(lpad(id, 9, "0"),1,3),"/",substr(lpad(id, 9, "0"),4,3),"/O/",substr(lpad(id, 9, "0"),1,3),"_",substr(lpad(id, 9, "0"),4,3),"_",substr(lpad(id, 9, "0"),7,3),"_O",substr(`original_name` , char_length(`original_name`) - locate('.', reverse(`original_name`)) +1, locate('.', reverse(`original_name`))))
3466 delphine 170
	        WHERE `original_name` like "%.png" and content_url = ""';
171
	    $this->bdd->requeter($requete_png);
3512 delphine 172
	    'UPDATE `photo` SET `mime_type` = "image/jpeg", `content_url`= concat('.Config::get('nettoyage.chemin_photo').
173
	        ',substr(lpad(id, 9, "0"),1,3),"/",substr(lpad(id, 9, "0"),4,3),"/O/",substr(lpad(id, 9, "0"),1,3),"_",substr(lpad(id, 9, "0"),4,3),"_",substr(lpad(id, 9, "0"),7,3),"_O.jpg")
174
	        WHERE content_url = "" and (`original_name` != "" or `original_name` is null)';
3301 delphine 175
	}
176
 
177
	private function migrerMotsClesImages() {
3440 delphine 178
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag
3301 delphine 179
	        (id, user_id, name, path)
180
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
3442 delphine 181
	        FROM ".$this->bd_cel.".`cel_arbre_mots_cles_images`";
3301 delphine 182
	    $this->bdd->requeter($requete);
183
	}
184
 
185
	private function migrerMotsClesImagesLiaison() {
3440 delphine 186
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag_photo
3301 delphine 187
	        (photo_id, photo_tag_id)
3442 delphine 188
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_images_liaison`";
3301 delphine 189
	    $this->bdd->requeter($requete);
190
	}
3443 delphine 191
 
192
	private function ajouterIdProjet() {
193
	    $requete = "UPDATE ".$this->bd_new_cel.".`project_settings` SET `project_id` = `id`";
194
	    $this->bdd->requeter($requete);
3444 delphine 195
	    $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 196
	    $this->bdd->requeter($requete);
3444 delphine 197
	    $requete = "UPDATE ".$this->bd_new_cel.".`extended_field` f right join ".$this->bd_new_cel.".`project_settings` s on
3443 delphine 198
	        s.`project` = f.`project` SET f.`project_id` = s.`project_id`";
199
	    $this->bdd->requeter($requete);
3444 delphine 200
	    $requete = "UPDATE ".$this->bd_new_cel.".`occurrence` o right join ".$this->bd_new_cel.".`project_settings` s on
3443 delphine 201
	        s.`project` = o.`project` SET o.`project_id` = s.`project_id`";
202
	    $this->bdd->requeter($requete);
203
	}
3301 delphine 204
 
205
 
206
}