Subversion Repositories eFlore/Applications.cel

Rev

Rev 3311 | Rev 3441 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 3311 Rev 3440
Line 19... Line 19...
19 
 */
 19 
 */
Line 20... Line 20...
20 
 
 20 
 
Line 21... Line 21...
21 
class Migrat2019 extends Script {
 21 
class Migrat2019 extends Script {
22 
 
 22 
 
Line 23... Line 23...
23 
	private $mode;
 23 
	private $mode;
24 
	private $base = "tb_new_cel";
 24 
	private $bd_new_cel = "tb_new_cel";
25
25
- 
 
 26 
	public function __construct($script_nom, $parametres_cli) {
- 
 
 27 
	    parent::__construct($script_nom, $parametres_cli);
26 
	public function __construct($script_nom, $parametres_cli) {
 28 
	    $this->bdd = new Bdd();
Line 27... Line 29...
27 
	    parent::__construct($script_nom, $parametres_cli);
 29 
	    $this->bd_new_cel = Config::get('nettoyage.cel_new');
28 
	    $this->bdd = new Bdd();
 30 
	    $this->bd_cel = Config::get('nettoyage.cel_nettoye');
29 
	}
 31 
	}
Line 56... Line 58...
56 
				echo 'Méthode inconnue, les méthodes possibles sont obs et images'."\n";
 58 
				echo 'Méthode inconnue, les méthodes possibles sont obs et images'."\n";
57 
		}
 59 
		}
58 
	}
 60 
	}
Line 59... Line 61...
59 
 
 61 
 
60 
	private function migrerObservations() {
 62 
	private function migrerObservations() {
61 
	    $requete = "ALTER TABLE ".$this->base.".`occurrence` ADD project varchar(50);";
 63 
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`occurrence` ADD project varchar(50);";
Line 62... Line 64...
62 
	    $this->bdd->requeter($requete);
 64 
	    $this->bdd->requeter($requete);
63
65
64 
		$requete = "INSERT INTO ".$this->base.".`occurrence`
 66 
		$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence`
65 
            (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)
 67 
            (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)
- 
 
 68 
            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,
- 
 
 69 
				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,
- 
 
 70 
				CONCAT('{""type"":""Point"",""coordinates"":[', longitude, ',', latitude,']}'),  altitude, geodatum, zone_geo, ce_zone_geo, lieudit,  milieu, locality_consistency, station, published_location, pays,  nom_referentiel
66 
            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
71 
            FROM ".$this->bd_cel."`cel_obs`";
67 
            FROM tb_nettoye_cel.`cel_obs`";
 72 
 
Line 68... Line 73...
68 
		$this->bdd->requeter($requete);
 73 
		$this->bdd->requeter($requete);
69 
	}
 74 
	}
70
75
71 
	private function migrerObsEtendusChampsUtilisateur() {
 76 
	private function migrerObsEtendusChampsUtilisateur() {
72 
	    $requete = "ALTER TABLE ".$this->base.".`user_custom_field` ADD `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";
 77 
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`user_custom_field` ADD `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";
73 
	    $this->bdd->requeter($requete);
 78 
	    $this->bdd->requeter($requete);
74 
	    $requete = "INSERT INTO ".$this->base.".`user_custom_field`
 79 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field`
75 
	        (name, data_type, default_value, field_id)
 80 
	        (name, data_type, default_value, field_id)
76 
	        SELECT label, '', '', cle
 81 
	        SELECT label, type, valeur, cle
77 
	        FROM tb_nettoye_cel.`cel_catalogue_champs_etendus`
82 
	        FROM ".$this->bd_cel."`cel_catalogue_champs_etendus`
Line 78... Line 83...
78 
	        WHERE `cle` not in (SELECT `champ` FROM `cel_catalogue_champs_etendus_liaison`)";
 83 
	        WHERE `cle` not in (SELECT `champ` FROM `cel_catalogue_champs_etendus_liaison`)";
79 
	    $this->bdd->requeter($requete);
 84 
	    $this->bdd->requeter($requete);
80 
	}
 85 
	}
81
86
82 
	private function migrerObsEtendusUtilisateur() {
 87 
	private function migrerObsEtendusUtilisateur() {
83 
	    $requete = "INSERT INTO ".$this->base.".`user_custom_field_occurrence`
 88 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence`
84 
	        (`occurrence_id`, `user_custom_field_id`, `value`)
 89 
	        (`occurrence_id`, `user_custom_field_id`, `value`)
85 
	        SELECT `id_observation`, id, `valeur`
90 
	        SELECT `id_observation`, id, `valeur`
Line 86... Line 91...
86 
	        FROM tb_nettoye_cel.`cel_obs_etendues`
 91 
	        FROM ".$this->bd_cel."`cel_obs_etendues`
87 
	        LEFT JOIN ".$this->base.".`user_custom_field` ON field_id = cle";
 92 
	        LEFT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle";
88 
	    $this->bdd->requeter($requete);
 93 
	    $this->bdd->requeter($requete);
89 
	}
 94 
	}
90
95
91
96
- 
 
 97 
	private function migrerObsEtendusProjet() {
92 
	private function migrerObsEtendusProjet() {
 98 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence`
93 
	    $requete = "INSERT INTO ".$this->base.".`user_custom_field_occurrence`
 99 
	        (`occurrence_id`, `extended_field_id`, `value`)
Line 94... Line 100...
94 
	        (`occurrence_id`, `user_custom_field_id`, `value`)
 100 
	        SELECT `id_observation`, id, `valeur`
95 
	        SELECT `id_observation`, id, `valeur`
 101 
	        FROM ".$this->bd_cel."`cel_obs_etendues`
96 
	        FROM tb_nettoye_cel.`cel_obs_etendues`
 102 
	        RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle
97 
	        LEFT JOIN ".$this->base.".`extended_field` ON field_id = cle";
 103 
			WHERE id_observation in (select id FROM `occurrence`)";
98 
	    $this->bdd->requeter($requete);
 104 
	    $this->bdd->requeter($requete);
99 
	}
 105 
	}
100
106
Line 101... Line 107...
101 
	private function migrerMotsClesObs() {
 107 
	private function migrerMotsClesObs() {
102 
	    $requete = "INSERT INTO ".$this->base.".user_occurrence_tag
 108 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag
103 
	        (id, user_id, name, path)
 109 
	        (id, user_id, name, path)
104 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
 110 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
105 
	        FROM tb_nettoye_cel.`cel_arbre_mots_cles_obs`";
 111 
	        FROM ".$this->bd_cel."`cel_arbre_mots_cles_obs`";
106 
	    $this->bdd->requeter($requete);
 112 
	    $this->bdd->requeter($requete);
Line 107... Line 113...
107 
	}
 113 
	}
108
114
109 
	private function migrerMotsClesObsLiaison() {
 115 
	private function migrerMotsClesObsLiaison() {
110 
	    $requete = "INSERT INTO ".$this->base.".occurrence_user_occurrence_tag
 116 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".occurrence_user_occurrence_tag
111 
	        (occurrence_id, user_occurrence_tag_id)
 117 
	        (occurrence_id, user_occurrence_tag_id)
112 
	        SELECT `id_element_lie`, `id_mot_cle` FROM tb_nettoye_cel.`cel_mots_cles_obs_liaison`";
 118 
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel."`cel_mots_cles_obs_liaison`";
113 
	    $this->bdd->requeter($requete);
 119 
	    $this->bdd->requeter($requete);
114 
	}
 120 
	}
115
121
116 
	private function migrerImages() {
 122 
	private function migrerImages() {
117 
	    $requete = "INSERT INTO ".$this->base.".photo
 123 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo
118 
	        (id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type,
 124 
	        (id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type,
Line 119... Line 125...
119 
	        date_updated, date_created, date_linked_to_occurrence, content_url, url)
 125 
	        date_updated, date_created, date_linked_to_occurrence, content_url, url)
120 
	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
126 
	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
121 
	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
127 
	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
122 
	        date_creation, date_liaison, '', concat('http://api.tela-botanica.org/img:000', '', `id_image`,
 128 
	        date_creation, date_liaison, '', concat('http://api.tela-botanica.org/img:000', '', `id_image`,
123 
	        'O.jpg')
 129 
	        'O.jpg')
124 
	        FROM tb_nettoye_cel.cel_images
 130 
	        FROM ".$this->bd_cel."cel_images
125 
	        WHERE ";
 131 
	        WHERE ";
Line 126... Line 132...
126 
	    $this->bdd->requeter($requete);
 132 
	    $this->bdd->requeter($requete);
127 
	}
 133 
	}
128 
 
 134 
 
129 
	private function migrerMotsClesImages() {
 135 
	private function migrerMotsClesImages() {
130 
	    $requete = "INSERT INTO ".$this->base.".photo_tag
 136 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag
131 
	        (id, user_id, name, path)
 137 
	        (id, user_id, name, path)
Line 132... Line 138...
132 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
 138 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
133 
	        FROM tb_nettoye_cel.`cel_arbre_mots_cles_images`";
 139 
	        FROM ".$this->bd_cel."`cel_arbre_mots_cles_images`";