DevInformatiques : CelDeclencheurNewBD

Creation table export

--
-- Structure de la table `cel_export`
--

CREATE TABLE IF NOT EXISTS `cel_export` (
  `guid` varchar(51) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `id_observation` bigint(20) NOT NULL DEFAULT '0',
  `ce_utilisateur` varchar(55) NOT NULL,
  `pseudo_utilisateur` varchar(255) DEFAULT NULL,
  `courriel_utilisateur` varchar(255) DEFAULT NULL,
  `nom_sel` varchar(601) DEFAULT NULL,
  `nom_sel_nn` decimal(9,0) DEFAULT NULL COMMENT 'Numéro du nom sélectionné.',
  `nom_ret` varchar(601) DEFAULT NULL,
  `nom_ret_nn` decimal(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
  `famille` varchar(255) DEFAULT NULL,
  `nom_referentiel` varchar(255) DEFAULT NULL,
  `pays` varchar(150) DEFAULT NULL COMMENT 'Code de pays suivant le standard ISO 3166-2',
  `ce_zone_geo` varchar(50) DEFAULT NULL,
  `dept` varchar(2) DEFAULT NULL,
  `zone_geo` varchar(255) DEFAULT NULL,
  `lieudit` varchar(255) DEFAULT NULL,
  `station` varchar(255) DEFAULT NULL,
  `milieu` varchar(255) DEFAULT NULL,
  `latitude` decimal(8,5) DEFAULT NULL,
  `longitude` decimal(8,5) DEFAULT NULL,
  `altitude` int(5) DEFAULT NULL,
  `geodatum` varchar(25) DEFAULT NULL COMMENT 'Référentiel géographique utilisé.\nPar exmple : WGS84',
  geometry text DEFAULT NULL,
  localisation_precision varchar(255) DEFAULT NULL,
  localisation_floutage varchar(255) DEFAULT NULL,
  localisation_coherence tinyint(1) DEFAULT NULL,  
`date_observation` datetime DEFAULT NULL,
  `programme` varchar(75) DEFAULT NULL,
  `mots_cles_texte` longtext COMMENT 'Champ calculé contenant la liste des mots clés utilisateurs séparé par des virgules.',
  `commentaire` text,
  `date_creation` datetime DEFAULT NULL,
  `date_modification` datetime DEFAULT NULL,
  `date_transmission` datetime DEFAULT NULL,
  `abondance` varchar(50) DEFAULT NULL,
  `certitude` varchar(255) DEFAULT NULL,
  `phenologie` varchar(255) DEFAULT NULL,
   spontaneite tinyint(1) DEFAULT NULL,
  observateur varchar(255) DEFAULT NULL,
  observateur_structure varchar(255) DEFAULT NULL,
  type_donnees varchar(30) DEFAULT NULL,
  biblio varchar(255) DEFAULT NULL,
  source varchar(25) DEFAULT NULL,
  herbier tinyint(1) DEFAULT NULL,
  determinateur varchar(255) DEFAULT NULL,
  `url_identiplante` varchar(65) CHARACTER SET utf8mb4 DEFAULT NULL,
  `validation_identiplante` int(1) DEFAULT '0' COMMENT 'La proposition "validée" une fois que l''auteur a  validé et que le nom_sel de cel_obs a été modifié à partir du nom_sel de del_commentaire.',
  `date_validation` datetime DEFAULT NULL,
 score_identiplante int(11) DEFAULT NULL,
  `images` text CHARACTER DEFAULT NULL, grade INT(1) DEFAULT NULL
) 
--
-- Index pour les tables exportées
--

--
-- Index pour la table `cel_export`
--

ALTER TABLE `cel_export`
 ADD PRIMARY KEY (`id_observation`), ADD KEY `courriel_utilisateur` (`courriel_utilisateur`), ADD KEY `nom_sel_nn` (`nom_sel_nn`), ADD KEY `nom_ret_nn` (`nom_ret_nn`), ADD KEY `pays` (`pays`), ADD KEY `dept` (`dept`), ADD KEY `programme` (`programme`), ADD KEY `validation_identiplante` (`validation_identiplante`);


--
-- Structure de la table `cel_export_total`
--

CREATE TABLE IF NOT EXISTS `cel_export_total` (
  `guid` varchar(51) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `donnees_standard` tinyint(1) NOT NULL,
  `transmission` tinyint(1) NOT NULL,
  `id_observation` bigint(20) NOT NULL DEFAULT '0',
  `ce_utilisateur` varchar(55) NOT NULL,
  `pseudo_utilisateur` varchar(255) DEFAULT NULL,
  `courriel_utilisateur` varchar(255) DEFAULT NULL,
  `nom_sel` varchar(601) DEFAULT NULL,
  `nom_sel_nn` decimal(9,0) DEFAULT NULL COMMENT 'Numéro du nom sélectionné.',
  `nom_ret` varchar(601) DEFAULT NULL,
  `nom_ret_nn` decimal(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
  `famille` varchar(255) DEFAULT NULL,
  `nom_referentiel` varchar(255) DEFAULT NULL,
  `pays` varchar(150) DEFAULT NULL COMMENT 'Code de pays suivant le standard ISO 3166-2',
  `ce_zone_geo` varchar(50) DEFAULT NULL,
  `dept` varchar(2) DEFAULT NULL, 
  `zone_geo` varchar(255) DEFAULT NULL,
  `lieudit` varchar(255) DEFAULT NULL,
  `station` varchar(255) DEFAULT NULL,
  `milieu` varchar(255) DEFAULT NULL,
  `latitude` decimal(8,5) DEFAULT NULL,
  `longitude` decimal(8,5) DEFAULT NULL,
  `altitude` int(5) DEFAULT NULL,
  `geodatum` varchar(25) DEFAULT NULL COMMENT 'Référentiel géographique utilisé.\nPar exmple : WGS84',
  geometry text DEFAULT NULL,
 `lat_prive` decimal(8,5) DEFAULT NULL,
  `long_prive` decimal(8,5) DEFAULT NULL,
  localisation_precision varchar(255) DEFAULT NULL,
  localisation_floutage varchar(255) DEFAULT NULL,
  localisation_coherence tinyint(1) DEFAULT NULL, 
 `date_observation` datetime DEFAULT NULL,
  `programme` varchar(75) DEFAULT NULL,
  `mots_cles_texte` longtext COMMENT 'Champ calculé contenant la liste des mots clés utilisateurs séparé par des virgules.',
  `commentaire` text,
  `date_creation` datetime DEFAULT NULL,
  `date_modification` datetime DEFAULT NULL,
  `date_transmission` datetime DEFAULT NULL,
  `abondance` varchar(50) DEFAULT NULL,
  `certitude` varchar(255) DEFAULT NULL,
  `phenologie` varchar(255) DEFAULT NULL,
   spontaneite tinyint(1) DEFAULT NULL,
  observateur varchar(255) DEFAULT NULL,
  observateur_structure varchar(255) DEFAULT NULL,
  type_donnees varchar(30) DEFAULT NULL,
  biblio varchar(255) DEFAULT NULL,
  source varchar(25) DEFAULT NULL,
  herbier tinyint(1) DEFAULT NULL,
  determinateur varchar(255) DEFAULT NULL,
  `url_identiplante` varchar(65) CHARACTER SET utf8mb4 DEFAULT NULL,
 `validation_identiplante` int(1) DEFAULT '0' COMMENT 'La proposition "validée" une fois que l''auteur a  validé et que le nom_sel de cel_obs a été modifié à partir du nom_sel de del_commentaire.',
  `date_validation` datetime DEFAULT NULL,
  score_identiplante int(11) DEFAULT NULL,
  `images` text CHARACTER SET utf8mb4
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Index pour les tables exportées
--

--
-- Index pour la table `cel_export_total`
--

ALTER TABLE `cel_export_total`
 ADD PRIMARY KEY (`id_observation`), ADD KEY `courriel_utilisateur` (`courriel_utilisateur`), ADD KEY `nom_sel_nn` (`nom_sel_nn`), ADD KEY `pays` (`pays`), ADD KEY `dept` (`dept`), ADD KEY `programme` (`programme`), ADD KEY `validation_identiplante` (`validation_identiplante`);

Images

create table cel_images_export as select `i`.`id` AS `id_image`,`i`.`occurrence_id` AS `ce_observation`,`i`.`date_shot` AS `date_prise_de_vue`,'' AS `mots_cles_texte`, `i`.`original_name` AS `nom_original`,`i`.`date_created` AS `date_creation`,`i`.`date_updated` AS `date_modification`,`i`.`date_linked_to_occurrence` AS `date_liaison` from (`tb_new_cel`.`photo` `i` left join `tb_new_cel`.`cel_export_total` `o` on(`o`.`id_observation` = `i`.`occurrence_id`)) where `o`.`transmission` = '1'

Migration données export

données standards = données avec courriel ?

données standard

INSERT INTO cel_export (guid, id_observation, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel,  
 ce_zone_geo, zone_geo, pays, dept,
lieudit, station, milieu, longitude, latitude,altitude, geodatum, geometry, localisation_precision, localisation_floutage, localisation_coherence,
date_observation, programme, commentaire, date_creation, date_modification, date_transmission, abondance, certitude, phenologie,
spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
  herbier, determinateur, score_identiplante) 
select 
concat('urn:lsid:tela-botanica.org:cel:', id), id, user_id, user_pseudo, user_email, user_sci_name, 
user_sci_name_id, accepted_sci_name, accepted_sci_name_id, 
family, UPPER(taxo_repo),  locality_insee_code, locality, osm_country, substr(`locality_insee_code`,1,2), sublocality, station, environment, 
ST_X(ST_GeomFromGeoJSON(geometry)), ST_Y(ST_GeomFromGeoJSON(geometry)), elevation, geodatum, geometry, location_accuracy, published_location,locality_consistency,
date_observed, project, annotation, date_created, date_updated,  	date_published, coef, certainty, phenology,
is_wild, observer, observer_institution, occurrence_type, bibliography_source, input_source,
 sample_herbarium, identification_author, NULL from occurrence where (is_public = "1" AND is_visible_in_cel="1" AND `date_observed` IS NOT NULL AND 
(user_sci_name != "" OR user_sci_name IS NOT NULL) AND `user_id` REGEXP "^[0-9]+$|^.*@.*$" AND 
(certainty = "certain" OR certainty is NULL) AND ((`geometry` != "" OR `geometry` IS NOT NULL) OR 
(`locality` != "" OR `locality` is not NULL)));

photos

UPDATE `cel_export` RIGHT JOIN (SELECT `occurrence_id`, group_concat(`url` SEPARATOR ', ') as photos FROM `photo` WHERE `occurrence_id` IS NOT NULL group by `occurrence_id`) a on `id_observation` = occurrence_id SET `url_identiplante`= concat("https://www.tela-botanica.org/appli:identiplante?#obs~", occurrence_id) , `images`= photos

floutage

UPDATE `cel_export` SET `latitude` = NULL, `longitude`= NULL, `geometry`= NULL WHERE `localisation_floutage` = "localité";
UPDATE `cel_export` SET `latitude` = floor(ST_Y(ST_GeomFromGeoJSON(geometry))*10)/10, `longitude`= floor(ST_X(ST_GeomFromGeoJSON(geometry))*10)/10, `geometry`= NULL WHERE `localisation_floutage` = "10x10km";

mots-clés

UPDATE `cel_export` right join (SELECT occurrence_id, group_concat(name) grp from (SELECT occurrence_id, user_occurrence_tag_id, name FROM `user_occurrence_tag` t join occurrence_user_occurrence_tag o on t.`id` = user_occurrence_tag_id) mcobs group by occurrence_id) mc on `id_observation` = occurrence_id SET `mots_cles_texte` = mc.grp

si ne fonctionne pas, supprimer la table mc après

create table mc as SELECT occurrence_id, user_occurrence_tag_id, name FROM `user_occurrence_tag` t join occurrence_user_occurrence_tag o on t.`id` = user_occurrence_tag_id;
UPDATE `cel_export` right join mc group by occurrence_id) mc on `id_observation` = occurrence_id SET `mots_cles_texte` = mc.grp

données cel total

INSERT INTO cel_export_total (guid, id_observation, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel,  transmission,
 ce_zone_geo, zone_geo, pays, dept,
lieudit, station, milieu, longitude, latitude, altitude, geodatum, geometry, long_prive, lat_prive, localisation_precision, localisation_floutage, localisation_coherence,
date_observation, programme, commentaire, date_creation, date_modification, date_transmission, abondance, certitude, phenologie,
spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
  herbier, determinateur, score_identiplante) 
select 
concat('urn:lsid:tela-botanica.org:cel:', id), id, user_id, user_pseudo, user_email, user_sci_name, 
user_sci_name_id, accepted_sci_name, accepted_sci_name_id, 
family, UPPER(taxo_repo), is_public,  locality_insee_code, locality, osm_country, substr(`locality_insee_code`,1,2), sublocality, station, environment, 
ST_X(ST_GeomFromGeoJSON(geometry)), ST_Y(ST_GeomFromGeoJSON(geometry)), elevation, geodatum, geometry, ST_X(ST_GeomFromGeoJSON(geometry)), ST_Y(ST_GeomFromGeoJSON(geometry)),  location_accuracy, published_location,locality_consistency,
date_observed, project, annotation, date_created, date_updated,  	date_published , coef, certainty, phenology,
is_wild, observer, observer_institution, occurrence_type, bibliography_source, input_source,
 sample_herbarium, identification_author, NULL from occurrence where is_visible_in_cel="1" ;

modif total info standard

UPDATE `cel_export_total` SET `donnees_standard` = 1 WHERE (transmission = "1" AND `date_observation` IS NOT NULL AND 
(`nom_sel` != "" OR `nom_sel` IS NOT NULL) AND `courriel_utilisateur` REGEXP "^.*@.*$" AND 
(`certitude` = "certain" OR `certitude` is NULL) AND ((`geometry` != "" OR `geometry` IS NOT NULL) OR 
(`zone_geo` != "" OR `zone_geo` is not NULL)));

photos

UPDATE `cel_export_total` RIGHT JOIN (SELECT `occurrence_id`, group_concat(`url` SEPARATOR ', ') as photos FROM `photo` WHERE `occurrence_id` IS NOT NULL group by `occurrence_id`) a on `id_observation` = occurrence_id SET `url_identiplante`= concat("https://www.tela-botanica.org/appli:identiplante?#obs~", occurrence_id) , `images`= photos

floutage

UPDATE `cel_export_total` SET `latitude` = NULL, `longitude`= NULL, `geometry`= NULL WHERE `localisation_floutage` = "localité";
UPDATE `cel_export_total` SET `latitude` = floor(ST_Y(ST_GeomFromGeoJSON(geometry))*10)/10, `longitude`= floor(ST_X(ST_GeomFromGeoJSON(geometry))*10)/10, `geometry`= NULL WHERE `localisation_floutage` = "10x10km";

mots-clés

UPDATE `cel_export_total` right join (SELECT occurrence_id, group_concat(name) grp from (SELECT occurrence_id, user_occurrence_tag_id, name FROM `user_occurrence_tag` t join occurrence_user_occurrence_tag o on t.`id` = user_occurrence_tag_id) mcobs group by occurrence_id) mc on `id_observation` = occurrence_id SET `mots_cles_texte` = mc.grp

Images

INSERT INTO `cel_images_export`(`id_image`, `ce_observation`, `date_prise_de_vue`, `mots_cles_texte`, `nom_original`, `date_creation`, `date_modification`, `date_liaison`) select i.id AS id_image, i.occurrence_id AS ce_observation, i.date_shot AS date_prise_de_vue, tag.mots_cles_texte AS mots_cles_texte,
	i.original_name AS nom_original,i.date_created AS date_creation,
	i.date_updated AS date_modification,i.date_linked_to_occurrence AS date_liaison
from tb_new_cel.photo i 
	left join tb_new_cel.cel_export_total o on(o.id_observation = i.occurrence_id) 
	left join (select tb_new_cel.photo_tag_photo.photo_id AS photo_id,
	 group_concat(tb_new_cel.photo_tag.name separator ',') AS mots_cles_texte 
	 from (tb_new_cel.photo_tag_photo join tb_new_cel.photo_tag on(tb_new_cel.photo_tag_photo.photo_tag_id = tb_new_cel.photo_tag.id)) group by tb_new_cel.photo_tag_photo.photo_id) tag on(tag.photo_id = i.id)
	 where o.transmission = '1'

mots-clés

UPDATE `cel_images_export` right join (SELECT photo_id, group_concat(name) grp from (SELECT photo_id, photo_tag_id, name FROM `photo_tag` t join photo_tag_photo o on t.`id` = photo_tag_id) mcobs group by photo_id) mc on `photo_id` = id_image SET `mots_cles_texte` = mc.grp

Identiplante

UPDATE `occurrence` 
	left join (SELECT ce_observation, id_commentaire, nom_sel_nn, `proposition_retenue`, score 
	FROM tb_del_test.`del_commentaire` c 
	left join (SELECT `ce_proposition`, sum(case when `valeur` = 0 and 
`ce_utilisateur` REGEXP '^-?[0-9]+$' then -3 when `valeur` = 0 then -1 when `valeur` = 1 and `ce_utilisateur` REGEXP '^-?[0-9]+$' then 3 when `valeur` = 1 then 1 END) as score 
FROM tb_del_test.`del_commentaire_vote` group by `ce_proposition`) sc 

on sc.ce_proposition = id_commentaire) ip 
on id = ce_observation AND user_sci_name_id= `nom_sel_nn` SET `identiplante_score`= score,`is_identiplante_validated`= case when proposition_retenue is null then 0 else proposition_retenue end

Declencheur

CEL

obs_aft_ins

IF (NEW.is_visible_in_cel="1") THEN

	INSERT INTO tb_del_test.del_observation_modif_date (id_observation, modif_date)
		VALUES (NEW.id, NEW.date_created);

	INSERT INTO `change_log`(`entity_id`, `action_type`, `entity_name`) SELECT NEW.id, "create", "occurrence";
	CASE NEW.published_location WHEN "10x10km" THEN	
		SET @lat = floor(ST_Y(ST_GeomFromGeoJSON(NEW.geometry))*10)/10;
		SET @long = floor(ST_X(ST_GeomFromGeoJSON(NEW.geometry))*10)/10;
		SET @geom = NULL;
	WHEN "localité" THEN	
		SET @lat = NULL;
		SET @long = NULL;
		SET @geom = NULL;		
	ELSE
		SET @lat = ST_Y(ST_GeomFromGeoJSON(NEW.geometry));
		SET @long = ST_X(ST_GeomFromGeoJSON(NEW.geometry));
		SET @geom = NEW.geometry;
	END CASE;


	IF (NEW.is_public = "1" AND NEW.`date_observed` IS NOT NULL AND 
				(NEW.user_sci_name != "" OR NEW.user_sci_name NOT LIKE "?") AND NEW.`user_email` REGEXP "^.*@.*$" AND 
				(NEW.certainty = "certain" OR NEW.certainty is NULL) AND ((NEW.`geometry` != "" OR NEW.`geometry` IS NOT NULL) OR 
				(NEW.`locality` != "" OR NEW.`locality` is not NULL))) THEN
		SET @standard = 1;
		INSERT INTO cel_export (guid, id_observation, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel,
			ce_zone_geo, zone_geo, pays, dept, 
			lieudit, station, milieu, longitude, latitude, altitude, geodatum, geometry, localisation_precision, localisation_floutage, localisation_coherence,
			date_observation, programme, commentaire, date_creation, date_modification, abondance, certitude, phenologie,
			spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
			herbier, determinateur, score_identiplante, date_transmission) 
		VALUES 
			(concat('urn:lsid:tela-botanica.org:cel:', NEW.id), NEW.id, NEW.user_id, NEW.user_pseudo, NEW.user_email, NEW.user_sci_name, 
			NEW.user_sci_name_id, NEW.accepted_sci_name, NEW.accepted_sci_name_id, 
			NEW.family, NEW.taxo_repo, NEW.locality_insee_code, NEW.locality, NEW.osm_country, substr(NEW.`locality_insee_code`,9,2), NEW.sublocality, NEW.station, NEW.environment, 
			@long, @lat, NEW.elevation, NEW.geodatum, @geom, NEW.location_accuracy,     NEW.published_location, 
			NEW.locality_consistency,
			NEW.date_observed, NEW.project, NEW.annotation, NEW.date_created, NEW.date_updated, NEW.coef, NEW.certainty, NEW.phenology,
			NEW.is_wild, NEW.observer, NEW.observer_institution, NEW.occurrence_type, NEW.bibliography_source, NEW.input_source,
			NEW.sample_herbarium, NEW.identification_author, NULL, NEW.date_published);
	ELSE
		SET @standard = 0;
	END IF;
	 
	INSERT INTO cel_export_total (guid, id_observation, donnees_standard, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel, transmission,
		 ce_zone_geo, zone_geo, pays, dept, 
		lieudit, station, milieu, longitude, latitude,altitude, geodatum, long_prive, lat_prive, geometry, localisation_precision, localisation_floutage, localisation_coherence,
		date_observation, programme, commentaire, date_creation, date_modification, abondance, certitude, phenologie,
		spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
		herbier, determinateur, score_identiplante, date_transmission) 
	VALUES 
		(concat('urn:lsid:tela-botanica.org:cel:', NEW.id), NEW.id, @standard, NEW.user_id, NEW.user_pseudo, NEW.user_email, NEW.user_sci_name, 
		NEW.user_sci_name_id, NEW.accepted_sci_name, NEW.accepted_sci_name_id, 
		NEW.family, NEW.taxo_repo, NEW.is_public, NEW.locality_insee_code, NEW.locality, NEW.osm_country, substr(NEW.`locality_insee_code`,9,2), NEW.sublocality, NEW.station, NEW.environment, 
		@long, @lat, NEW.elevation, NEW.geodatum, ST_X(ST_GeomFromGeoJSON(NEW.geometry)), ST_Y(ST_GeomFromGeoJSON(NEW.geometry)), @geom, NEW.location_accuracy, NEW.published_location, 
		NEW.locality_consistency,
		NEW.date_observed, NEW.project, NEW.annotation, NEW.date_created, NEW.date_updated, NEW.coef, NEW.certainty, NEW.phenology,
		NEW.is_wild, NEW.observer, NEW.observer_institution, NEW.occurrence_type, NEW.bibliography_source, NEW.input_source,
		NEW.sample_herbarium, NEW.identification_author, NULL, NEW.date_published);
END IF;

obs_aft_upd

IF NEW.is_visible_in_cel="1" THEN
	UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.date_updated
		WHERE id_observation = NEW.id;
    IF NEW.is_public != OLD.is_public THEN
     SET @photo = (SELECT photo.id from photo where occurrence_id = NEW.id);
     IF NEW.is_public = 1 and @photo != false THEN
       INSERT INTO cel_images_export (`id_image`, `ce_observation`, `date_prise_de_vue`, `nom_original`, `date_creation`, `date_modification`, `date_liaison`) SELECT `id`, `occurrence_id`, `date_shot`, `original_name`, `date_created`, `date_updated`, `date_linked_to_occurrence` FROM photo WHERE occurrence_id = NEW.id;
     ELSEIF NEW.is_public = 0 and @photo != false THEN
        DELETE FROM `cel_images_export` WHERE ce_observation = NEW.id;
     END IF;
    END IF;

    INSERT INTO `change_log`(`entity_id`, `action_type`, `entity_name`) SELECT NEW.id, "update", "occurrence";
    CASE NEW.published_location WHEN "10x10km" THEN	
		SET @lat = floor(ST_Y(ST_GeomFromGeoJSON(NEW.geometry))*10)/10;
		SET @long = floor(ST_X(ST_GeomFromGeoJSON(NEW.geometry))*10)/10;
		SET @geom = NULL;
	WHEN "localité" THEN	
		SET @lat = NULL;
		SET @long = NULL;
		SET @geom = NULL;		
	ELSE
		SET @lat = ST_Y(ST_GeomFromGeoJSON(NEW.geometry));
		SET @long = ST_X(ST_GeomFromGeoJSON(NEW.geometry));
		SET @geom = NEW.geometry;
	END CASE;

	IF (NEW.`date_observed` IS NOT NULL AND 
				(NEW.user_sci_name != "" OR NEW.user_sci_name NOT LIKE "?") AND NEW.`user_id` REGEXP "^[0-9]+$|^.*@.*$" AND 
				(NEW.certainty = "certain" OR NEW.certainty is NULL) AND ((NEW.`geometry` != "" OR NEW.`geometry` IS NOT NULL) OR 
				(NEW.`locality` != "" OR NEW.`locality` is not NULL))) THEN
		SET @standard = 1;
		INSERT INTO cel_export (guid, id_observation, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel, 
			ce_zone_geo, zone_geo, pays, dept, 
			lieudit, station, milieu, longitude, latitude,altitude, geodatum, geometry, localisation_precision, localisation_floutage, localisation_coherence,
			date_observation, programme, commentaire, date_creation, date_modification, abondance, certitude, phenologie,
			spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
			 herbier, determinateur, score_identiplante, validation_identiplante, date_transmission) 
			VALUES 
			(concat('urn:lsid:tela-botanica.org:cel:', NEW.id), NEW.id, NEW.user_id, NEW.user_pseudo, NEW.user_email, NEW.user_sci_name, 
			NEW.user_sci_name_id, NEW.accepted_sci_name, NEW.accepted_sci_name_id, 
			NEW.family, NEW.taxo_repo, NEW.locality_insee_code, NEW.locality, NEW.osm_country, substr(NEW.`locality_insee_code`,9,2), NEW.sublocality, NEW.station, NEW.environment, 
			@long, @lat, NEW.elevation, NEW.geodatum, @geom, NEW.location_accuracy,  NEW.published_location, 
			NEW.locality_consistency,
			NEW.date_observed, NEW.project, NEW.annotation, NEW.date_created, NEW.date_updated, NEW.coef, NEW.certainty, NEW.phenology,
			NEW.is_wild, NEW.observer, NEW.observer_institution, NEW.occurrence_type, NEW.bibliography_source, NEW.input_source,
			NEW.sample_herbarium, NEW.identification_author, NEW.identiplante_score, NEW.is_identiplante_validated, NEW.date_published) 
            
            ON DUPLICATE KEY UPDATE
			   `ce_utilisateur` = NEW.`user_id`, `pseudo_utilisateur` = NEW.user_pseudo, `courriel_utilisateur` = NEW.`user_email`, 
			   `nom_sel` = NEW.`user_sci_name`, `nom_sel_nn` = NEW.`user_sci_name_id`, `nom_ret` = NEW.`accepted_sci_name`, `nom_ret_nn` = 	NEW.`accepted_sci_name_id`, 
			   `famille`= NEW.`family`, `nom_referentiel` = NEW.`taxo_repo`, `ce_zone_geo` = NEW.`locality_insee_code`, 
				`zone_geo` = NEW.`locality`, pays = NEW.osm_country, dept = substr(NEW.`locality_insee_code`,9,2),`lieudit` = NEW.`sublocality`, `station` = NEW.`station`, 
				`milieu` = NEW.`environment`, `latitude` = @lat, `longitude` = @long, geometry = @geom, geodatum = NEW.geodatum, localisation_precision = NEW.location_accuracy, 
                localisation_floutage =  NEW.published_location, localisation_coherence = NEW.locality_consistency,
				`altitude` = NEW.`elevation`,  commentaire = NEW.annotation,
				`date_observation` = NEW.`date_observed`, programme = NEW.project, date_modification = NEW.date_updated, abondance = NEW.coef, certitude = NEW.certainty, phenologie = NEW.phenology,
				spontaneite = NEW.is_wild, observateur = NEW.observer, observateur_structure = NEW.observer_institution, type_donnees = NEW.occurrence_type, biblio = NEW.bibliography_source, 
                source = NEW.input_source, herbier = NEW.sample_herbarium, determinateur = NEW.identification_author, score_identiplante = NEW.identiplante_score, validation_identiplante = NEW.is_identiplante_validated, date_transmission = NEW.date_published;
	ELSE
		SET @standard = 0;
	END IF;
	
	INSERT INTO cel_export_total (guid, id_observation, donnees_standard, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel,  transmission,
		ce_zone_geo, zone_geo, pays, dept, 
		lieudit, station, milieu, longitude, latitude,altitude, geodatum, geometry, long_prive, lat_prive, localisation_precision, localisation_floutage, localisation_coherence,
		date_observation, programme, commentaire, date_creation, date_modification, abondance, certitude, phenologie,
		spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
		 herbier, determinateur, score_identiplante, validation_identiplante, date_transmission) 
		VALUES 
		(concat('urn:lsid:tela-botanica.org:cel:', NEW.id), NEW.id, @standard, NEW.user_id, NEW.user_pseudo, NEW.user_email, NEW.user_sci_name, 
		NEW.user_sci_name_id, NEW.accepted_sci_name, NEW.accepted_sci_name_id, 
		NEW.family, NEW.taxo_repo, NEW.is_public, NEW.locality_insee_code, NEW.locality, NEW.osm_country, substr(NEW.`locality_insee_code`,9,2), NEW.sublocality, NEW.station, NEW.environment, 
		@long, @lat, NEW.elevation, NEW.geodatum, @geom, ST_X(ST_GeomFromGeoJSON(NEW.geometry)), ST_Y(ST_GeomFromGeoJSON(NEW.geometry)), NEW.location_accuracy, NEW.published_location, 
		NEW.locality_consistency,
		NEW.date_observed, NEW.project, NEW.annotation, NEW.date_created, NEW.date_updated, NEW.coef, NEW.certainty, NEW.phenology,
		NEW.is_wild, NEW.observer, NEW.observer_institution, NEW.occurrence_type, NEW.bibliography_source, NEW.input_source,
		NEW.sample_herbarium, NEW.identification_author, NEW.identiplante_score, NEW.is_identiplante_validated, NEW.date_published) 
        ON DUPLICATE KEY UPDATE
		   `ce_utilisateur` = NEW.`user_id`, `pseudo_utilisateur` = NEW.user_pseudo, `courriel_utilisateur` = NEW.`user_email`, 
		   `nom_sel` = NEW.`user_sci_name`, `nom_sel_nn` = NEW.`user_sci_name_id`, `nom_ret` = NEW.`accepted_sci_name`, `nom_ret_nn` = NEW.`accepted_sci_name_id`, 
		   `famille`= NEW.`family`, `nom_referentiel` = NEW.`taxo_repo`, `ce_zone_geo` = NEW.`locality_insee_code`, 
			`zone_geo` = NEW.`locality`, pays = NEW.osm_country, dept = substr(NEW.`locality_insee_code`,9,2), `lieudit` = NEW.`sublocality`, `station` = NEW.`station`, 
			`milieu` = NEW.`environment`, `latitude` = @lat, `longitude` = @long, geometry = @geom, long_prive = ST_X(ST_GeomFromGeoJSON(NEW.geometry)), lat_prive =    ST_Y(ST_GeomFromGeoJSON(NEW.geometry)),  commentaire = NEW.annotation,
			`altitude` = NEW.`elevation`, `geodatum` = NEW.`geodatum`, transmission = NEW.is_public, donnees_standard = @standard,
			`date_observation` = NEW.`date_observed`, programme = NEW.project, date_modification = NEW.date_updated, abondance = NEW.coef, certitude = NEW.certainty, phenologie = NEW.phenology,
				spontaneite = NEW.is_wild, observateur = NEW.observer, observateur_structure = NEW.observer_institution, type_donnees = NEW.occurrence_type, biblio = NEW.bibliography_source, 
                source = NEW.input_source, herbier = NEW.sample_herbarium, determinateur = NEW.identification_author, score_identiplante = NEW.identiplante_score, validation_identiplante = NEW.is_identiplante_validated, date_transmission = NEW.date_published;	
             
  
END IF

obs_aft_del

BEGIN
DELETE FROM `cel_export` WHERE `id_observation` = OLD.`id`;
DELETE FROM `cel_export_total` WHERE `id_observation` =OLD.`id`;
DELETE FROM `extended_field_occurrence` WHERE `occurrence_id` = OLD.`id`;
DELETE FROM `user_custom_field_occurrence` WHERE `occurrence_id`= OLD.`id`;
INSERT INTO `change_log`(`entity_id`, `action_type`, `entity_name`) SELECT OLD.`id`, "delete", "occurrence";
END;

les commentaires ne sont pas supprimés de del quand les obs sont supprimées

img_aft_ins

IF NEW.occurrence_id IN (select id_observation FROM cel_export) THEN
	INSERT INTO `change_log`(`entity_id`, `action_type`, `entity_name`) SELECT NEW.`id`, "create", "photo";
	UPDATE cel_export SET images = IF (images IS NOT NULL, CONCAT(images, NEW.url), CONCAT(NEW.url,',')), 
	url_identiplante = CONCAT("www.tela-botanica.org/appli:identiplante#obs~", NEW.occurrence_id) WHERE NEW.occurrence_id =       id_observation;

	UPDATE cel_export_total SET images = IF (images IS NOT NULL, CONCAT(images, NEW.url), CONCAT(NEW.url,',')), 
	url_identiplante = CONCAT("www.tela-botanica.org/appli:identiplante#obs~", NEW.occurrence_id) WHERE NEW.occurrence_id = id_observation;

ELSEIF NEW.occurrence_id IN (select id_observation FROM cel_export_total) THEN
	INSERT INTO `change_log`(`entity_id`, `action_type`, `entity_name`) SELECT NEW.`id`, "create", "photo";
	UPDATE cel_export_total SET images = IF (images IS NOT NULL, CONCAT(images, NEW.url), CONCAT(NEW.url,',')), 
	url_identiplante = CONCAT("www.tela-botanica.org/appli:identiplante#obs~", NEW.occurrence_id) WHERE NEW.occurrence_id = id_observation;

END IF

img_aft_upd

IF NEW.occurrence_id IN (select id_observation FROM cel_export_total) OR OLD.occurrence_id IN (select id_observation FROM cel_export_total) THEN
      
   UPDATE cel_export 
	SET images = IF (images IS NOT NULL, CONCAT(images, NEW.url, ','), CONCAT(NEW.url, ',')), url_identiplante = CONCAT("www.tela-botanica.org/appli:identiplante#obs~", NEW.occurrence_id)
				WHERE id_observation= NEW.occurrence_id;
   UPDATE cel_export_total 
	SET images = IF (images IS NOT NULL, CONCAT(images, NEW.url, ','), CONCAT(NEW.url, ',')), url_identiplante = CONCAT("www.tela-botanica.org/appli:identiplante#obs~", NEW.occurrence_id)
			WHERE id_observation = NEW.occurrence_id;

   IF  OLD.occurrence_id IN (select id_observation FROM cel_export_total) THEN
         SET @img = (SELECT if(REPLACE(images, CONCAT(OLD.url, ','), '') = '', NULL, REPLACE(images, CONCAT(OLD.url, ','), '')) FROM cel_export_total WHERE id_observation = OLD.occurrence_id LIMIT 1);
         SET  @identiplante =  (SELECT IF(@img = NULL, NULL, url_identiplante) FROM cel_export_total WHERE id_observation = OLD.occurrence_id LIMIT 1);
         UPDATE cel_export SET images = @img, url_identiplante = @identiplante WHERE id_observation = OLD.occurrence_id;
         UPDATE cel_export_total SET images = @img, url_identiplante = @identiplante WHERE id_observation = OLD.occurrence_id;
    END IF;
    IF ((SELECT transmission FROM cel_export_total  WHERE  id_observation = NEW.occurrence_id) = 1) THEN
         INSERT INTO cel_images_export (`id_image`, `ce_observation`, `date_prise_de_vue`, `nom_original`, `date_creation`, `date_modification`, `date_liaison`) VALUES (NEW.`id`, NEW.`occurrence_id`,                  NEW.`date_shot`, NEW.`original_name`, NEW.`date_created`, NEW.`date_updated`, NEW.`date_linked_to_occurrence`) ON DUPLICATE KEY UPDATE `id_image` = NEW.`id`, `ce_observation` =    NEW.`occurrence_id`, `date_prise_de_vue` = NEW.`date_shot`, `nom_original` = NEW.`original_name`, `date_creation` = NEW.`date_created`, `date_modification` = NEW.`date_updated`, `date_liaison` = NEW.`date_linked_to_occurrence`;
    END IF;
END IF

img_aft_del

IF OLD.occurrence_id IN (select id_observation FROM cel_export_total) THEN
	INSERT INTO `change_log`(`entity_id`, `action_type`, `entity_name`) SELECT OLD.`id`, "delete", "photo";
	
	 SET @img = (SELECT if(REPLACE(images, CONCAT(OLD.url, ','), '') = '', NULL, REPLACE(images, CONCAT(OLD.url, ','), '')) FROM cel_export_total WHERE id_observation = OLD.occurrence_id LIMIT 1);
    SET  @identiplante =  (SELECT IF(@img = NULL, NULL, url_identiplante) FROM cel_export_total WHERE id_observation = OLD.occurrence_id LIMIT 1);
    
	UPDATE cel_export SET images = @img, url_identiplante = @identiplante WHERE id_observation = OLD.occurrence_id;
	UPDATE cel_export_total SET images =  @img, url_identiplante = @identiplante WHERE  id_observation = OLD.occurrence_id;
    DELETE FROM `cel_images_export` WHERE id_image = OLD.id;
END IF

DEL

commentaire_aft_upd

if (NEW.`proposition_retenue` = 1) THEN

UPDATE tb_cel_test.`cel_export` SET `validation_identiplante` = 1, `date_validation` = NEW.`date_validation` WHERE `id_observation` = NEW.`ce_observation`;

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = NEW.ce_observation;

ELSE

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = NEW.ce_observation;

END IF

img_tag_aft_ins

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = (SELECT ce_observation FROM tb_cel_test.cel_images WHERE id_image = NEW.ce_image)

img_tag_aft_upd

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = (SELECT ce_observation FROM tb_cel_test.cel_images WHERE id_image = NEW.ce_image)

img_vote_aft_ins

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = (SELECT ce_observation FROM tb_cel_test.cel_images WHERE id_image = NEW.ce_image)

img_vote_aft_upd

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = (SELECT ce_observation FROM tb_cel_test.cel_images WHERE id_image = NEW.ce_image)

Vues DEL

Observations

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `del_observation` AS 
select `o`.`id_observation` AS `id_observation`, 
o.`ce_utilisateur` AS `ce_utilisateur`,`o`.`pseudo_utilisateur` AS `prenom_utilisateur`,
"" AS `nom_utilisateur`,`o`.`courriel_utilisateur` AS `courriel_utilisateur`,
`o`.`nom_sel` AS `nom_sel`,`o`.`nom_sel_nn` AS `nom_sel_nn`,`o`.`nom_ret` AS `nom_ret`,
`o`.`nom_ret_nn` AS `nom_ret_nn`,`o`.`nom_ret_nn` AS `nt`,`o`.`famille` AS `famille`,`o`.`ce_zone_geo` AS `ce_zone_geo`,
`o`.`zone_geo` AS `zone_geo`,`o`.`lieudit` AS `lieudit`,`o`.`station` AS `station`,`o`.`milieu` AS `milieu`,
`o`.`nom_referentiel` AS `nom_referentiel`,`o`.`date_observation` AS `date_observation`,
`o`.`mots_cles_texte` AS `mots_cles_texte`,`o`.`commentaire` AS `commentaire`,
`o`.`date_creation` AS `date_creation`,`o`.`date_modification` AS `date_modification`,
`o`.`date_transmission` AS `date_transmission`,`o`.`certitude` AS `certitude`,`o`.`pays` AS `pays` 
from `tb_new_cel`.`cel_export_total` `o` 
where images is not null and 
`o`.`transmission` = '1';

Images

select i.id AS id_image, i.occurrence_id AS ce_observation,i.user_id AS ce_utilisateur,
	'' AS prenom_utilisateur,i.user_pseudo AS nom_utilisateur,i.user_email AS courriel_utilisateur,
	NULL AS hauteur,NULL AS largeur,i.date_shot AS date_prise_de_vue, tag.mots_cles_texte AS mots_cles_texte,
	NULL AS commentaire,i.original_name AS nom_original,i.date_created AS date_creation,
	i.date_updated AS date_modification,i.date_linked_to_occurrence AS date_liaison,
	o.date_transmission AS date_transmission 
from tb_new_cel.photo i 
	left join tb_new_cel.cel_export_total o on(o.id_observation = i.occurrence_id) 
	left join (select tb_new_cel.photo_tag_photo.photo_id AS photo_id,
	 group_concat(tb_new_cel.photo_tag.name separator ',') AS mots_cles_texte 
	 from (tb_new_cel.photo_tag_photo join tb_new_cel.photo_tag on(tb_new_cel.photo_tag_photo.photo_tag_id = tb_new_cel.photo_tag.id)) group by tb_new_cel.photo_tag_photo.photo_id) tag on(tag.photo_id = i.id)
	 where o.transmission = '1'

vote commentaire aft ins

if (NEW.ce_proposition IS NOT NULL) THEN
UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = (SELECT ce_observation FROM tb_del_test.del_commentaire WHERE id_commentaire = NEW.ce_proposition);

UPDATE tb_new_cel.`occurrence` join 
	(SELECT ce_observation, id_commentaire, nom_sel_nn, `proposition_retenue` FROM tb_del_test.`del_commentaire` WHERE id_commentaire = NEW.ce_proposition) c 	 	
	on id = ce_observation AND user_sci_name_id= `nom_sel_nn` 
	SET `identiplante_score`= case when NEW.`valeur` = 0 and NEW.`ce_utilisateur` REGEXP '^-?[0-9]+$' then ifnull(identiplante_score, 0) -3 when NEW.`valeur` = 0 then ifnull(identiplante_score, 0) -1 when NEW.`valeur` = 1 and NEW.`ce_utilisateur` REGEXP '^-?[0-9]+$' 
		then ifnull(identiplante_score, 0) + 3 when NEW.`valeur` = 1 then ifnull(identiplante_score, 0) + 1 END,`is_identiplante_validated`= case when proposition_retenue is null then 0 else proposition_retenue end;
END IF

vote commentaire aft upd

if (NEW.ce_proposition IS NOT NULL) THEN
UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = (SELECT ce_observation FROM tb_del_test.del_commentaire WHERE id_commentaire = NEW.ce_proposition);

UPDATE tb_new_cel.`occurrence` join 
	(SELECT ce_observation, id_commentaire, nom_sel_nn, `proposition_retenue` FROM tb_del_test.`del_commentaire` WHERE id_commentaire = NEW.ce_proposition) c 	 	
	on id = ce_observation AND user_sci_name_id= `nom_sel_nn` 
	SET `identiplante_score`= case when NEW.`valeur` = 0 and NEW.`ce_utilisateur` REGEXP '^-?[0-9]+$' then ifnull(identiplante_score, 0) -3 when NEW.`valeur` = 0 then ifnull(identiplante_score, 0) -1 when NEW.`valeur` = 1 and NEW.`ce_utilisateur` REGEXP '^-?[0-9]+$' 
		then ifnull(identiplante_score, 0) + 3 when NEW.`valeur` = 1 then ifnull(identiplante_score, 0) + 1 END,`is_identiplante_validated`= case when proposition_retenue is null then 0 else proposition_retenue end;
END IF

commentaire aft ins

if (SELECT 1 FROM tb_new_cel.occurrence WHERE id = NEW.`ce_observation` AND  	user_sci_name_id = NEW.`nom_sel_nn`) = 1 THEN

UPDATE tb_new_cel.`occurrence` SET `is_identiplante_validated`= case when NEW.proposition_retenue is null then 0 else NEW.proposition_retenue end WHERE `id` = NEW.`ce_observation`;

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = NEW.ce_observation;

ELSE
UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = NEW.ce_observation;

END IF

commentaire aft upd

if (SELECT 1 FROM tb_new_cel.occurrence WHERE id = NEW.`ce_observation` AND  	user_sci_name_id = NEW.`nom_sel_nn`) = 1 THEN

UPDATE tb_new_cel.`occurrence` SET `is_identiplante_validated`= case when NEW.proposition_retenue is null then 0 else NEW.proposition_retenue end WHERE `id` = NEW.`ce_observation`;

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = NEW.ce_observation;

ELSE

UPDATE tb_del_test.del_observation_modif_date SET modif_date = NEW.`date`
WHERE id_observation = NEW.ce_observation;

END IF

Déjà membre

Nouveau membre

Creation table export

Images

Migration données export

données standards = données avec courriel ?

données standard

photos

floutage

mots-clés

données cel total

modif total info standard

photos

floutage

mots-clés

Images

mots-clés

Identiplante

Declencheur

CEL

obs_aft_ins

obs_aft_upd

obs_aft_del

img_aft_ins

img_aft_upd

img_aft_del

DEL

commentaire_aft_upd

img_tag_aft_ins

img_tag_aft_upd

img_vote_aft_ins

img_vote_aft_upd

Vues DEL

Observations

Images

vote commentaire aft ins

vote commentaire aft upd

commentaire aft ins

commentaire aft upd