Subversion Repositories eFlore/Applications.cel

Rev

Rev 3302 | Rev 3306 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

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