| Line -... |
Line 1... |
| - |
|
1 |
DROP VIEW IF EXISTS cel.cel_tapir;
|
| 1 |
CREATE ALGORITHM=UNDEFINED DEFINER=`telabotap`@`localhost` SQL SECURITY DEFINER
|
2 |
CREATE ALGORITHM=UNDEFINED DEFINER=`telabotap`@`localhost` SQL SECURITY DEFINER
|
| 2 |
VIEW `cel_tapir` AS
|
3 |
VIEW `cel_tapir` AS
|
| 3 |
SELECT
|
4 |
SELECT
|
| 4 |
concat(_utf8'urn:lsid:tela-botanica.org:cel:',i.id) AS guid,
|
5 |
CONCAT(_utf8'urn:lsid:tela-botanica.org:cel:',i.id) AS guid,
|
| 5 |
i.date_modification AS date_modification,
|
6 |
i.id AS observation_id,
|
| 6 |
i.id AS id,
|
7 |
DATE_FORMAT(i.date_observation, '%Y-%m-%d') AS observation_date,
|
| 7 |
i.nom_sel AS nom_sel,
|
8 |
i.nom_sel AS nom_scientifique_complet,
|
| 8 |
i.num_nom_sel AS num_nom_sel,
|
9 |
i.num_nom_sel AS nom_num_nomenclatural,
|
| 9 |
i.famille AS famille,
|
10 |
i.num_taxon AS nom_num_taxonomique,
|
| 10 |
l.code AS code,
|
11 |
i.famille AS nom_famille,
|
| 11 |
l.name AS name,
|
12 |
CONCAT(_utf8'', l.name, ' [INSEE:', l.insee_code, ']') AS lieu_commune_nom_complet,
|
| 12 |
i.date_observation AS date_observation,
|
13 |
l.name AS lieu_commune_nom,
|
| 13 |
i.identifiant AS identifiant,
|
14 |
l.insee_code AS lieu_commune_code_insee,
|
| 14 |
concat(l.sector,' ',l.x_utm,'m E ',l.y_utm,'m N') AS coordonnees_bruts,
|
15 |
concat(l.sector,' ',l.x_utm,'m E ',l.y_utm,'m N') AS lieu_commune_utm_coordonnees_bruts,
|
| 15 |
l.sector AS sector,
|
16 |
l.sector AS lieu_commune_utm_zone,
|
| 16 |
l.x_utm AS x_utm,
|
17 |
l.x_utm AS lieu_commune_utm_x,
|
| 17 |
l.y_utm AS y_utm,
|
18 |
l.y_utm AS lieu_commune_utm_y,
|
| - |
|
19 |
i.lieudit AS lieu_localite,
|
| - |
|
20 |
i.identifiant AS observateur_courriel,
|
| - |
|
21 |
IF(i.commentaire = 'null', NULL, i.commentaire) AS observation_commentaire,
|
| - |
|
22 |
CONCAT(_utf8'<?xml version="1.0" encoding="UTF-8"?>',
|
| - |
|
23 |
'<info>',
|
| - |
|
24 |
'<nom_num_nomenclatural>',i.num_nom_sel,'</nom_num_nomenclatural>',
|
| - |
|
25 |
'<nom_num_taxonomique>',i.num_taxon,'</nom_num_taxonomique>',
|
| 18 |
i.date_transmission AS date_transmission,
|
26 |
'<saisie_date_transmission>',i.transmission,'</saisie_date_transmission>',
|
| - |
|
27 |
'<saisie_date_creation>',i.date_creation,'</saisie_date_creation>',
|
| 19 |
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,
|
28 |
'<lieu_commune_utm_secteur>',l.sector,'</lieu_commune_utm_secteur>',
|
| - |
|
29 |
'<ordre>',i.ordre,'</ordre>',
|
| - |
|
30 |
'<nom_ret>',i.nom_ret,'</nom_ret>',
|
| - |
|
31 |
'<num_nom_ret>',i.num_nom_ret,'</num_nom_ret>',
|
| - |
|
32 |
'<station>',i.station,'</station>',
|
| - |
|
33 |
'<milieu>',i.milieu,'</milieu>',
|
| - |
|
34 |
'<maj_name>',l.maj_name,'</maj_name>',
|
| - |
|
35 |
'<update_date>',l.update_date,'</update_date>',
|
| - |
|
36 |
'</info>') AS observation_information_complement,
|
| - |
|
37 |
i.date_modification AS saisie_date_modification,
|
| 20 |
i.num_taxon AS num_taxon
|
38 |
i.date_creation AS saisie_date_creation
|
| 21 |
FROM (cel_inventory AS i LEFT JOIN locations AS l ON (((i.location = l.name) AND (i.id_location = l.code))))
|
39 |
FROM (cel_inventory AS i LEFT JOIN locations AS l ON (((i.location = l.name) AND (i.id_location = l.code))))
|
| 22 |
WHERE (i.transmission = 1);
|
40 |
WHERE (i.transmission = 1);
|