Subversion Repositories Sites.tela-botanica.org

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
622 jpm 1 
CREATE ALGORITHM=UNDEFINED DEFINER=`telabotap`@`localhost` SQL SECURITY DEFINER
2 
	VIEW `cel_tapir` AS
3 
	SELECT
4 
		concat(_utf8'urn:lsid:tela-botanica.org:cel:',i.id) AS guid,
5 
		i.date_modification AS date_modification,
6 
		i.id AS id,
7 
		i.nom_sel AS nom_sel,
8 
		i.num_nom_sel AS num_nom_sel,
9 
		i.famille AS famille,
10 
		l.code AS code,
11 
		l.name AS name,
12 
		i.date_observation AS date_observation,
13 
		i.identifiant AS identifiant,
14 
		concat(l.sector,' ',l.x_utm,'m E ',l.y_utm,'m N') AS coordonnees_bruts,
15 
		l.sector AS sector,
16 
		l.x_utm AS x_utm,
17 
		l.y_utm AS y_utm,
18 
		i.date_transmission AS date_transmission,
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,
20 
		i.num_taxon AS num_taxon
21 
	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);