| 1,22 → 1,40 |
|---|
| DROP VIEW IF EXISTS cel.cel_tapir; |
| CREATE ALGORITHM=UNDEFINED DEFINER=`telabotap`@`localhost` SQL SECURITY DEFINER |
| VIEW `cel_tapir` AS |
| SELECT |
| concat(_utf8'urn:lsid:tela-botanica.org:cel:',i.id) AS guid, |
| i.date_modification AS date_modification, |
| i.id AS id, |
| i.nom_sel AS nom_sel, |
| i.num_nom_sel AS num_nom_sel, |
| i.famille AS famille, |
| l.code AS code, |
| l.name AS name, |
| i.date_observation AS date_observation, |
| i.identifiant AS identifiant, |
| concat(l.sector,' ',l.x_utm,'m E ',l.y_utm,'m N') AS coordonnees_bruts, |
| l.sector AS sector, |
| l.x_utm AS x_utm, |
| l.y_utm AS y_utm, |
| i.date_transmission AS date_transmission, |
| concat(_utf8'<?xml version="1.0" encoding="UTF-8"?>','<info>','<ordre>',i.ordre,'</ordre>','<num_nom_sel>',i.num_nom_sel,'</num_nom_sel>','<nom_ret>',i.nom_ret,'</nom_ret>','<num_nom_ret>',i.num_nom_ret,'</num_nom_ret>','<num_taxon>',i.num_taxon,'</num_taxon>','<lieudit>',i.lieudit,'</lieudit>','<station>',i.station,'</station>','<milieu>',i.milieu,'</milieu>','<commentaire>',i.commentaire,'</commentaire>','<transmission>',i.transmission,'</transmission>','<date_creation>',i.date_creation,'</date_creation>','<maj_name>',l.maj_name,'</maj_name>','<sector>',l.sector,'</sector>','<update_date>',l.update_date,'</update_date>','</info>') AS related_information, |
| i.num_taxon AS num_taxon |
| CONCAT(_utf8'urn:lsid:tela-botanica.org:cel:',i.id) AS guid, |
| i.id AS observation_id, |
| DATE_FORMAT(i.date_observation, '%Y-%m-%d') AS observation_date, |
| i.nom_sel AS nom_scientifique_complet, |
| i.num_nom_sel AS nom_num_nomenclatural, |
| i.num_taxon AS nom_num_taxonomique, |
| i.famille AS nom_famille, |
| CONCAT(_utf8'', l.name, ' [INSEE:', l.insee_code, ']') AS lieu_commune_nom_complet, |
| l.name AS lieu_commune_nom, |
| l.insee_code AS lieu_commune_code_insee, |
| concat(l.sector,' ',l.x_utm,'m E ',l.y_utm,'m N') AS lieu_commune_utm_coordonnees_bruts, |
| l.sector AS lieu_commune_utm_zone, |
| l.x_utm AS lieu_commune_utm_x, |
| l.y_utm AS lieu_commune_utm_y, |
| i.lieudit AS lieu_localite, |
| i.identifiant AS observateur_courriel, |
| IF(i.commentaire = 'null', NULL, i.commentaire) AS observation_commentaire, |
| CONCAT(_utf8'<?xml version="1.0" encoding="UTF-8"?>', |
| '<info>', |
| '<nom_num_nomenclatural>',i.num_nom_sel,'</nom_num_nomenclatural>', |
| '<nom_num_taxonomique>',i.num_taxon,'</nom_num_taxonomique>', |
| '<saisie_date_transmission>',i.transmission,'</saisie_date_transmission>', |
| '<saisie_date_creation>',i.date_creation,'</saisie_date_creation>', |
| '<lieu_commune_utm_secteur>',l.sector,'</lieu_commune_utm_secteur>', |
| '<ordre>',i.ordre,'</ordre>', |
| '<nom_ret>',i.nom_ret,'</nom_ret>', |
| '<num_nom_ret>',i.num_nom_ret,'</num_nom_ret>', |
| '<station>',i.station,'</station>', |
| '<milieu>',i.milieu,'</milieu>', |
| '<maj_name>',l.maj_name,'</maj_name>', |
| '<update_date>',l.update_date,'</update_date>', |
| '</info>') AS observation_information_complement, |
| i.date_modification AS saisie_date_modification, |
| i.date_creation AS saisie_date_creation |
| FROM (cel_inventory AS i LEFT JOIN locations AS l ON (((i.location = l.name) AND (i.id_location = l.code)))) |
| WHERE (i.transmission = 1); |