Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 3439 → Rev 3440

/trunk/scripts/modules/migrat2019/Migrat2019.php
21,11 → 21,13
class Migrat2019 extends Script {
 
private $mode;
private $base = "tb_new_cel";
private $bd_new_cel = "tb_new_cel";
public function __construct($script_nom, $parametres_cli) {
parent::__construct($script_nom, $parametres_cli);
$this->bdd = new Bdd();
$this->bd_new_cel = Config::get('nettoyage.cel_new');
$this->bd_cel = Config::get('nettoyage.cel_nettoye');
}
 
public function executer() {
58,63 → 60,67
}
 
private function migrerObservations() {
$requete = "ALTER TABLE ".$this->base.".`occurrence` ADD project varchar(50);";
$requete = "ALTER TABLE ".$this->bd_new_cel.".`occurrence` ADD project varchar(50);";
$this->bdd->requeter($requete);
$requete = "INSERT INTO ".$this->base.".`occurrence`
$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence`
(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)
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
FROM tb_nettoye_cel.`cel_obs`";
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,
CONCAT('{""type"":""Point"",""coordinates"":[', longitude, ',', latitude,']}'), altitude, geodatum, zone_geo, ce_zone_geo, lieudit, milieu, locality_consistency, station, published_location, pays, nom_referentiel
FROM ".$this->bd_cel."`cel_obs`";
 
$this->bdd->requeter($requete);
}
private function migrerObsEtendusChampsUtilisateur() {
$requete = "ALTER TABLE ".$this->base.".`user_custom_field` ADD `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";
$requete = "ALTER TABLE ".$this->bd_new_cel.".`user_custom_field` ADD `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;";
$this->bdd->requeter($requete);
$requete = "INSERT INTO ".$this->base.".`user_custom_field`
$requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field`
(name, data_type, default_value, field_id)
SELECT label, '', '', cle
FROM tb_nettoye_cel.`cel_catalogue_champs_etendus`
SELECT label, type, valeur, cle
FROM ".$this->bd_cel."`cel_catalogue_champs_etendus`
WHERE `cle` not in (SELECT `champ` FROM `cel_catalogue_champs_etendus_liaison`)";
$this->bdd->requeter($requete);
}
private function migrerObsEtendusUtilisateur() {
$requete = "INSERT INTO ".$this->base.".`user_custom_field_occurrence`
$requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence`
(`occurrence_id`, `user_custom_field_id`, `value`)
SELECT `id_observation`, id, `valeur`
FROM tb_nettoye_cel.`cel_obs_etendues`
LEFT JOIN ".$this->base.".`user_custom_field` ON field_id = cle";
FROM ".$this->bd_cel."`cel_obs_etendues`
LEFT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle";
$this->bdd->requeter($requete);
}
private function migrerObsEtendusProjet() {
$requete = "INSERT INTO ".$this->base.".`user_custom_field_occurrence`
(`occurrence_id`, `user_custom_field_id`, `value`)
$requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence`
(`occurrence_id`, `extended_field_id`, `value`)
SELECT `id_observation`, id, `valeur`
FROM tb_nettoye_cel.`cel_obs_etendues`
LEFT JOIN ".$this->base.".`extended_field` ON field_id = cle";
FROM ".$this->bd_cel."`cel_obs_etendues`
RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle
WHERE id_observation in (select id FROM `occurrence`)";
$this->bdd->requeter($requete);
}
private function migrerMotsClesObs() {
$requete = "INSERT INTO ".$this->base.".user_occurrence_tag
$requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag
(id, user_id, name, path)
SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
FROM tb_nettoye_cel.`cel_arbre_mots_cles_obs`";
FROM ".$this->bd_cel."`cel_arbre_mots_cles_obs`";
$this->bdd->requeter($requete);
}
private function migrerMotsClesObsLiaison() {
$requete = "INSERT INTO ".$this->base.".occurrence_user_occurrence_tag
$requete = "INSERT INTO ".$this->bd_new_cel.".occurrence_user_occurrence_tag
(occurrence_id, user_occurrence_tag_id)
SELECT `id_element_lie`, `id_mot_cle` FROM tb_nettoye_cel.`cel_mots_cles_obs_liaison`";
SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel."`cel_mots_cles_obs_liaison`";
$this->bdd->requeter($requete);
}
private function migrerImages() {
$requete = "INSERT INTO ".$this->base.".photo
$requete = "INSERT INTO ".$this->bd_new_cel.".photo
(id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type,
date_updated, date_created, date_linked_to_occurrence, content_url, url)
SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur,
121,23 → 127,23
courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification,
date_creation, date_liaison, '', concat('http://api.tela-botanica.org/img:000', '', `id_image`,
'O.jpg')
FROM tb_nettoye_cel.cel_images
FROM ".$this->bd_cel."cel_images
WHERE ";
$this->bdd->requeter($requete);
}
 
private function migrerMotsClesImages() {
$requete = "INSERT INTO ".$this->base.".photo_tag
$requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag
(id, user_id, name, path)
SELECT id_mot_cle, id_utilisateur, mot_cle, chemin
FROM tb_nettoye_cel.`cel_arbre_mots_cles_images`";
FROM ".$this->bd_cel."`cel_arbre_mots_cles_images`";
$this->bdd->requeter($requete);
}
private function migrerMotsClesImagesLiaison() {
$requete = "INSERT INTO ".$this->base.".photo_tag_photo
$requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag_photo
(photo_id, photo_tag_id)
SELECT `id_element_lie`, `id_mot_cle` FROM tb_nettoye_cel.`cel_mots_cles_images_liaison`";
SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel."`cel_mots_cles_images_liaison`";
$this->bdd->requeter($requete);
}