Subversion Repositories Sites.tela-botanica.org

Compare Revisions

Ignore whitespace Rev 622 → Rev 623

/trunk/tapirlink/bdd/legumino_vue.sql
1,24 → 1,112
DELIMITER $$
DROP FUNCTION IF EXISTS legumino.HTML_UnEncode$$
CREATE DEFINER=`legumino`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN
 
DECLARE TextString VARCHAR(255) ;
SET TextString = x ;
 
IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&', '&') ;
END IF ;
 
IF INSTR( x , '’' )
THEN SET TextString = REPLACE(TextString, '’', '''') ;
END IF ;
 
IF INSTR( x , '°' )
THEN SET TextString = REPLACE(TextString, '°', '°') ;
END IF ;
 
IF INSTR( x , 'à' )
THEN SET TextString = REPLACE(TextString, 'à', 'à');
END IF ;
 
IF INSTR( x , 'â' )
THEN SET TextString = REPLACE(TextString, 'â', 'â');
END IF ;
 
IF INSTR( x , 'ä' )
THEN SET TextString = REPLACE(TextString, 'ä', 'ä');
END IF ;
 
IF INSTR( x , 'è' )
THEN SET TextString = REPLACE(TextString, 'è', 'è');
END IF ;
 
IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é', 'é');
END IF ;
 
IF INSTR( x , 'ê' )
THEN SET TextString = REPLACE(TextString, 'ê', 'ê');
END IF ;
 
IF INSTR( x , 'í' )
THEN SET TextString = REPLACE(TextString, 'í', 'í');
END IF ;
 
IF INSTR( x , 'î' )
THEN SET TextString = REPLACE(TextString, 'î', 'î');
END IF ;
 
IF INSTR( x , 'ô' )
THEN SET TextString = REPLACE(TextString, 'ô', 'ô');
END IF ;
 
IF INSTR( x , 'ö' )
THEN SET TextString = REPLACE(TextString, 'ö', 'ö');
END IF ;
 
IF INSTR( x , 'ü' )
THEN SET TextString = REPLACE(TextString, 'ü', 'ü');
END IF ;
 
RETURN TextString ;
 
END$$
 
DELIMITER ;
 
DROP VIEW IF EXISTS legumino.legumino_tapir;
CREATE ALGORITHM=UNDEFINED DEFINER=`legumino`@`localhost` SQL SECURITY DEFINER VIEW `legumino_tapir` AS
SELECT
concat(_utf8'urn:lsid:tela-botanica.org:legumino:', c.id_contributions) AS guid,
c.date AS date_observation,
c.commentaire AS commentaire,
c.date_saisie AS date_modification,
SUBSTRING(cm.code_insee FROM 4) AS commune_code_insee,
cm.nom_communes AS commune_nom,
cm.latitude AS commune_latitude,
cm.longitude AS commune_longitude,
l.NumNomen AS num_nomenclatural,
l.NumTaxon AS num_taxonomique,
l.Famille AS nom_famille,
l.Genre AS nom_genre,
l.Espece AS nom_espece,
l.Auteur AS nom_auteur,
`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`
FROM (contributions AS c
LEFT JOIN communes AS cm ON (c.code_insee = cm.code_insee)
LEFT JOIN legumino AS l (c.NumTaxon = l.NumTaxon)
LEFT JOIN contributeurs AS ct (c.id_contributeurs = ct.mail)
WHERE (`i`.`transmission` = 1);
CONCAT('urn:lsid:tela-botanica.org:legumino:', c.id_contributions) AS guid,
c.id_contributions AS observation_id,
c.date AS observation_date,
HTML_UnEncode(CONCAT_WS(' ',
l.Genre,
l.Espece,
IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))),
IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL))),
IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL)))))) AS nom_scientifique_complet,
l.NumNomen AS nom_num_nomenclatural,
l.NumTaxon AS nom_num_taxonomique,
HTML_UnEncode(l.Famille) AS nom_famille,
HTML_UnEncode(l.Genre) AS nom_genre,
HTML_UnEncode(l.Espece) AS nom_espece,
IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))) AS nom_type,
HTML_UnEncode(IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL)))) AS nom_infra_sp,
HTML_UnEncode(IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL))))) AS nom_auteur,
d.nom_departements AS lieu_departement_nom,
CONCAT(CONVERT(cm.nom_communes USING UTF8), ' [INSEE:', SUBSTRING(cm.code_insee FROM 4), ']') AS lieu_commune_nom_complet,
cm.nom_communes AS lieu_commune_nom,
SUBSTRING(cm.code_insee FROM 4) AS lieu_commune_code_insee,
cm.latitude AS lieu_commune_latitude,
cm.longitude AS lieu_commune_longitude,
HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs , CONCAT('<', ct.mail, '>'))) AS observateur_nom_complet,
HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs)) AS observateur_nom,
ct.mail AS observateur_courriel,
HTML_UnEncode(c.commentaire) AS observation_commentaire,
CONCAT(_utf8'<?xml version="1.0" encoding="UTF-8"?>',
'<info>',
'<nom_num_nomenclatural>',l.NumNomen,'</nom_num_nomenclatural>',
'<nom_num_taxonomique>',l.NumTaxon,'</nom_num_taxonomique>',
'</info>') AS observation_information_complement,
c.date_saisie AS saisie_date_modification
FROM contributions AS c
LEFT JOIN communes AS cm ON (c.code_insee = cm.code_insee)
LEFT JOIN departements AS d ON (cm.num_departements = d.num_departements)
LEFT JOIN legumino AS l ON (c.NumTaxon = l.NumTaxon)
LEFT JOIN contributeurs AS ct ON (c.id_contributeurs = ct.id_contributeurs);
 
/trunk/tapirlink/bdd/cel_vue.sql
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);