Subversion Repositories Sites.tela-botanica.org

Compare Revisions

Ignore whitespace Rev 625 → Rev 626

/trunk/tapirlink/bdd/pterido_vue.sql
New file
0,0 → 1,200
DELIMITER $$
DROP FUNCTION IF EXISTS pterido2.HTML_UnEncode$$
CREATE DEFINER=`pterido2`@`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, '&#60;', '<') ;
END IF ;
 
IF INSTR( x , '&#62;' )
THEN SET TextString = REPLACE(TextString, '&#62;', '>') ;
END IF ;
 
IF INSTR( x , '&#145;' )
THEN SET TextString = REPLACE(TextString, '&#145;', '‘') ;
END IF ;
 
IF INSTR( x , '&#146;' )
THEN SET TextString = REPLACE(TextString, '&#146;', '''') ;
END IF ;
 
IF INSTR( x , '&#147;' )
THEN SET TextString = REPLACE(TextString, '&#147;', '“') ;
END IF ;
 
IF INSTR( x , '&#148;' )
THEN SET TextString = REPLACE(TextString, '&#148;', '”') ;
END IF ;
 
IF INSTR( x , '&#156;' )
THEN SET TextString = REPLACE(TextString, '&#156;', 'œ') ;
END IF ;
 
IF INSTR( x , '&#171;' )
THEN SET TextString = REPLACE(TextString, '&#171;', '«') ;
END IF ;
 
IF INSTR( x , '&#176;' )
THEN SET TextString = REPLACE(TextString, '&#176;', '°') ;
END IF ;
 
IF INSTR( x , '&#177;' )
THEN SET TextString = REPLACE(TextString, '&#177;', '±') ;
END IF ;
 
IF INSTR( x , '&#178;' )
THEN SET TextString = REPLACE(TextString, '&#178;', '²') ;
END IF ;
 
IF INSTR( x , '&#185;' )
THEN SET TextString = REPLACE(TextString, '&#185;', '¹') ;
END IF ;
 
IF INSTR( x , '&#187;' )
THEN SET TextString = REPLACE(TextString, '&#187;', '»') ;
END IF ;
 
IF INSTR( x , '&#201;' )
THEN SET TextString = REPLACE(TextString, '&#201;', 'É');
END IF ;
 
IF INSTR( x , '&#206;' )
THEN SET TextString = REPLACE(TextString, '&#206;', 'Î');
END IF ;
 
IF INSTR( x , '&#225;' )
THEN SET TextString = REPLACE(TextString, '&#225;', 'á');
END IF ;
 
IF INSTR( x , '&#224;' )
THEN SET TextString = REPLACE(TextString, '&#224;', 'à');
END IF ;
 
IF INSTR( x , '&#226;' )
THEN SET TextString = REPLACE(TextString, '&#226;', 'â');
END IF ;
 
IF INSTR( x , '&#228;' )
THEN SET TextString = REPLACE(TextString, '&#228;', 'ä');
END IF ;
 
IF INSTR( x , '&#231;' )
THEN SET TextString = REPLACE(TextString, '&#231;', 'ç');
END IF ;
 
IF INSTR( x , '&#232;' )
THEN SET TextString = REPLACE(TextString, '&#232;', 'è');
END IF ;
 
IF INSTR( x , '&#233;' )
THEN SET TextString = REPLACE(TextString, '&#233;', 'é');
END IF ;
 
IF INSTR( x , '&#234;' )
THEN SET TextString = REPLACE(TextString, '&#234;', 'ê');
END IF ;
 
IF INSTR( x , '&#235;' )
THEN SET TextString = REPLACE(TextString, '&#235;', 'ë');
END IF ;
 
IF INSTR( x , '&#237;' )
THEN SET TextString = REPLACE(TextString, '&#237;', 'í');
END IF ;
 
IF INSTR( x , '&#238;' )
THEN SET TextString = REPLACE(TextString, '&#238;', 'î');
END IF ;
 
IF INSTR( x , '&#239;' )
THEN SET TextString = REPLACE(TextString, '&#239;', 'ï');
END IF ;
 
IF INSTR( x , '&#242;' )
THEN SET TextString = REPLACE(TextString, '&#242;', 'ò');
END IF ;
 
IF INSTR( x , '&#244;' )
THEN SET TextString = REPLACE(TextString, '&#244;', 'ô');
END IF ;
 
IF INSTR( x , '&#246;' )
THEN SET TextString = REPLACE(TextString, '&#246;', 'ö');
END IF ;
 
IF INSTR( x , '&#249;' )
THEN SET TextString = REPLACE(TextString, '&#249;', 'ù');
END IF ;
 
IF INSTR( x , '&#251;' )
THEN SET TextString = REPLACE(TextString, '&#251;', 'û');
END IF ;
 
IF INSTR( x , '&#252;' )
THEN SET TextString = REPLACE(TextString, '&#252;', 'ü');
END IF ;
 
IF INSTR( x , '&#255;' )
THEN SET TextString = REPLACE(TextString, '&#255;', 'ÿ');
END IF ;
 
RETURN TextString ;
 
END$$
 
DELIMITER ;
 
DROP VIEW IF EXISTS pterido2.pterido_tapir;
CREATE ALGORITHM=UNDEFINED DEFINER=`pterido2`@`localhost` SQL SECURITY DEFINER VIEW `pterido_tapir` AS
SELECT
CONCAT('urn:lsid:tela-botanica.org:pterido:', 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,
DATE_FORMAT(c.date_saisie, '%Y-%m-%d 00:00:00') 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 pterido AS l ON (c.NumTaxon = l.NumTaxon)
LEFT JOIN contributeurs AS ct ON (c.id_contributeurs = ct.id_contributeurs);