Subversion Repositories eFlore/Applications.cel

Rev

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

Rev 3441 Rev 3442
Line 66... Line 66...
66 
		$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence`
 66 
		$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence`
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)
 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,
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,
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
70 
				CONCAT('{\"type\":\"Point\",\"coordinates\":[', 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`";
 71 
            FROM ".$this->bd_cel.".`cel_obs`";
Line 72... Line 72...
72 
 
 72 
 
73 
		$this->bdd->requeter($requete);
 73 
		$this->bdd->requeter($requete);
Line 74... Line 74...
74 
	}
 74 
	}
75
75
76 
	private function migrerObsEtendusChampsUtilisateur() {
 76 
	private function migrerObsEtendusChampsUtilisateur() {
77 
	    $requete = "ALTER TABLE ".$this->bd_new_cel.".`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`;";
78 
	    $this->bdd->requeter($requete);
 78 
	    $this->bdd->requeter($requete);
79 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field`
 79 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field`
80 
	        (name, data_type, default_value, field_id)
 80 
	        (name, data_type, default_value, field_id)
81 
	        SELECT label, type, valeur, cle
 81 
	        SELECT label, type, valeur, cle
82 
	        FROM ".$this->bd_cel."`cel_catalogue_champs_etendus`
82 
	        FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus`
83 
	        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`)";
Line 84... Line 84...
84 
	    $this->bdd->requeter($requete);
 84 
	    $this->bdd->requeter($requete);
85 
	}
 85 
	}
86
86
87 
	private function migrerObsEtendusUtilisateur() {
 87 
	private function migrerObsEtendusUtilisateur() {
88 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence`
 88 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence`
89 
	        (`occurrence_id`, `user_custom_field_id`, `value`)
 89 
	        (`occurrence_id`, `user_custom_field_id`, `value`)
90 
	        SELECT `id_observation`, id, `valeur`
90 
	        SELECT `id_observation`, id, `valeur`
91 
	        FROM ".$this->bd_cel."`cel_obs_etendues`
 91 
	        FROM ".$this->bd_cel.".`cel_obs_etendues`
Line 92... Line 92...
92 
	        LEFT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle";
 92 
	        LEFT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle";
93 
	    $this->bdd->requeter($requete);
 93 
	    $this->bdd->requeter($requete);
94 
	}
 94 
	}
95
95
96
96
97 
	private function migrerObsEtendusProjet() {
 97 
	private function migrerObsEtendusProjet() {
98 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence`
 98 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence`
99 
	        (`occurrence_id`, `extended_field_id`, `value`)
 99 
	        (`occurrence_id`, `extended_field_id`, `value`)
100 
	        SELECT `id_observation`, id, `valeur`
 100 
	        SELECT `id_observation`, id, `valeur`
Line 101... Line 101...
101 
	        FROM ".$this->bd_cel."`cel_obs_etendues`
 101 
	        FROM ".$this->bd_cel.".`cel_obs_etendues`
102 
	        RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle
102 
	        RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle
103 
			WHERE id_observation in (select id FROM `occurrence`)";
 103 
			WHERE id_observation in (select id FROM `occurrence`)";
104 
	    $this->bdd->requeter($requete);
 104 
	    $this->bdd->requeter($requete);
105 
	}
 105 
	}
106
106
107 
	private function migrerMotsClesObs() {
 107 
	private function migrerMotsClesObs() {
Line 108... Line 108...
108 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag
 108 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag
109 
	        (id, user_id, name, path)
 109 
	        (id, user_id, name, path)
110 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
 110 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
111 
	        FROM ".$this->bd_cel."`cel_arbre_mots_cles_obs`";
 111 
	        FROM ".$this->bd_cel.".`cel_arbre_mots_cles_obs`";
112 
	    $this->bdd->requeter($requete);
 112 
	    $this->bdd->requeter($requete);
113 
	}
 113 
	}
Line 114... Line 114...
114
114
115 
	private function migrerMotsClesObsLiaison() {
 115 
	private function migrerMotsClesObsLiaison() {
Line 125... Line 125...
125 
	        date_updated, date_created, date_linked_to_occurrence, content_url, url)
 125 
	        date_updated, date_created, date_linked_to_occurrence, content_url, url)
126 
	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
126 
	        SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
127 
	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
127 
	        courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
128 
	        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`,
129 
	        'O.jpg')
 129 
	        'O.jpg')
130 
	        FROM ".$this->bd_cel."cel_images
 130 
	        FROM ".$this->bd_cel.".cel_images
131 
	        WHERE ";
 131 
	        WHERE ";
132 
	    $this->bdd->requeter($requete);
 132 
	    $this->bdd->requeter($requete);
133 
	}
 133 
	}
Line 134... Line 134...
134 
 
 134 
 
135 
	private function migrerMotsClesImages() {
 135 
	private function migrerMotsClesImages() {
136 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag
 136 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag
137 
	        (id, user_id, name, path)
 137 
	        (id, user_id, name, path)
138 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
 138 
	        SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
139 
	        FROM ".$this->bd_cel."`cel_arbre_mots_cles_images`";
 139 
	        FROM ".$this->bd_cel.".`cel_arbre_mots_cles_images`";
140 
	    $this->bdd->requeter($requete);
 140 
	    $this->bdd->requeter($requete);
Line 141... Line 141...
141 
	}
 141 
	}
142
142
143 
	private function migrerMotsClesImagesLiaison() {
 143 
	private function migrerMotsClesImagesLiaison() {
144 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag_photo
 144 
	    $requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag_photo
145 
	        (photo_id, photo_tag_id)
 145 
	        (photo_id, photo_tag_id)
146 
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel."`cel_mots_cles_images_liaison`";
 146 
	        SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_images_liaison`";
Line 147... Line 147...
147 
	    $this->bdd->requeter($requete);
 147 
	    $this->bdd->requeter($requete);
148 
	}
 148 
	}