Subversion Repositories Sites.tela-botanica.org

Rev

Rev 623 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 623 Rev 627
1 
DELIMITER $$
 1 
DELIMITER $$
2 
DROP FUNCTION IF EXISTS legumino.HTML_UnEncode$$
 2 
DROP FUNCTION IF EXISTS pterido2.HTML_UnEncode$$
3 
CREATE DEFINER=`legumino`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
 3 
CREATE DEFINER=`pterido2`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
4 
BEGIN
 4 
BEGIN
5 
 
 5 
 
6 
DECLARE TextString VARCHAR(255) ;
 6 
DECLARE TextString VARCHAR(255) ;
7 
SET TextString = x ;
 7 
SET TextString = x ;
- 
 
 8 
 
- 
 
 9 
IF INSTR( x , '"' )
- 
 
 10 
THEN SET TextString = REPLACE(TextString, '"', '"') ;
- 
 
 11 
END IF ;
8 
 
 12 
 
9 
IF INSTR( x , '&' )
 13 
IF INSTR( x , '&' )
10 
THEN SET TextString = REPLACE(TextString, '&', '&') ;
 14 
THEN SET TextString = REPLACE(TextString, '&', '&') ;
11 
END IF ;
 15 
END IF ;
- 
 
 16 
 
- 
 
 17 
IF INSTR( x , '<' )
- 
 
 18 
THEN SET TextString = REPLACE(TextString, '&#60;', '<') ;
- 
 
 19 
END IF ;
- 
 
 20 
 
- 
 
 21 
IF INSTR( x , '&#62;' )
- 
 
 22 
THEN SET TextString = REPLACE(TextString, '&#62;', '>') ;
- 
 
 23 
END IF ;
- 
 
 24 
 
- 
 
 25 
IF INSTR( x , '&#145;' )
- 
 
 26 
THEN SET TextString = REPLACE(TextString, '&#145;', '‘') ;
- 
 
 27 
END IF ;
12 
 
 28 
 
13 
IF INSTR( x , '&#146;' )
 29 
IF INSTR( x , '&#146;' )
14 
THEN SET TextString = REPLACE(TextString, '&#146;', '''') ;
 30 
THEN SET TextString = REPLACE(TextString, '&#146;', '''') ;
15 
END IF ;
 31 
END IF ;
- 
 
 32 
 
- 
 
 33 
IF INSTR( x , '&#147;' )
- 
 
 34 
THEN SET TextString = REPLACE(TextString, '&#147;', '“') ;
- 
 
 35 
END IF ;
- 
 
 36 
 
- 
 
 37 
IF INSTR( x , '&#148;' )
- 
 
 38 
THEN SET TextString = REPLACE(TextString, '&#148;', '”') ;
- 
 
 39 
END IF ;
- 
 
 40 
 
- 
 
 41 
IF INSTR( x , '&#156;' )
- 
 
 42 
THEN SET TextString = REPLACE(TextString, '&#156;', 'œ') ;
- 
 
 43 
END IF ;
- 
 
 44 
 
- 
 
 45 
IF INSTR( x , '&#171;' )
- 
 
 46 
THEN SET TextString = REPLACE(TextString, '&#171;', '«') ;
- 
 
 47 
END IF ;
16 
 
 48 
 
17 
IF INSTR( x , '&#176;' )
 49 
IF INSTR( x , '&#176;' )
18 
THEN SET TextString = REPLACE(TextString, '&#176;', '°') ;
 50 
THEN SET TextString = REPLACE(TextString, '&#176;', '°') ;
19 
END IF ;
 51 
END IF ;
- 
 
 52 
 
- 
 
 53 
IF INSTR( x , '&#177;' )
- 
 
 54 
THEN SET TextString = REPLACE(TextString, '&#177;', '±') ;
- 
 
 55 
END IF ;
- 
 
 56 
 
- 
 
 57 
IF INSTR( x , '&#178;' )
- 
 
 58 
THEN SET TextString = REPLACE(TextString, '&#178;', '²') ;
- 
 
 59 
END IF ;
- 
 
 60 
 
- 
 
 61 
IF INSTR( x , '&#185;' )
- 
 
 62 
THEN SET TextString = REPLACE(TextString, '&#185;', '¹') ;
- 
 
 63 
END IF ;
- 
 
 64 
 
- 
 
 65 
IF INSTR( x , '&#187;' )
- 
 
 66 
THEN SET TextString = REPLACE(TextString, '&#187;', '»') ;
- 
 
 67 
END IF ;
- 
 
 68 
 
- 
 
 69 
IF INSTR( x , '&#201;' )
- 
 
 70 
THEN SET TextString = REPLACE(TextString, '&#201;', 'É');
- 
 
 71 
END IF ;
- 
 
 72 
 
- 
 
 73 
IF INSTR( x , '&#206;' )
- 
 
 74 
THEN SET TextString = REPLACE(TextString, '&#206;', 'Î');
- 
 
 75 
END IF ;
- 
 
 76 
 
- 
 
 77 
IF INSTR( x , '&#225;' )
- 
 
 78 
THEN SET TextString = REPLACE(TextString, '&#225;', 'á');
- 
 
 79 
END IF ;
20 
 
 80 
 
21 
IF INSTR( x , '&#224;' )
 81 
IF INSTR( x , '&#224;' )
22 
THEN SET TextString = REPLACE(TextString, '&#224;', 'à');
 82 
THEN SET TextString = REPLACE(TextString, '&#224;', 'à');
23 
END IF ;
 83 
END IF ;
24 
 
 84 
 
25 
IF INSTR( x , '&#226;' )
 85 
IF INSTR( x , '&#226;' )
26 
THEN SET TextString = REPLACE(TextString, '&#226;', 'â');
 86 
THEN SET TextString = REPLACE(TextString, '&#226;', 'â');
27 
END IF ;
 87 
END IF ;
28 
 
 88 
 
29 
IF INSTR( x , '&#228;' )
 89 
IF INSTR( x , '&#228;' )
30 
THEN SET TextString = REPLACE(TextString, '&#228;', 'ä');
 90 
THEN SET TextString = REPLACE(TextString, '&#228;', 'ä');
31 
END IF ;
 91 
END IF ;
- 
 
 92 
 
- 
 
 93 
IF INSTR( x , '&#231;' )
- 
 
 94 
THEN SET TextString = REPLACE(TextString, '&#231;', 'ç');
- 
 
 95 
END IF ;
32 
 
 96 
 
33 
IF INSTR( x , '&#232;' )
 97 
IF INSTR( x , '&#232;' )
34 
THEN SET TextString = REPLACE(TextString, '&#232;', 'è');
 98 
THEN SET TextString = REPLACE(TextString, '&#232;', 'è');
35 
END IF ;
 99 
END IF ;
36 
 
 100 
 
37 
IF INSTR( x , '&#233;' )
 101 
IF INSTR( x , '&#233;' )
38 
THEN SET TextString = REPLACE(TextString, '&#233;', 'é');
 102 
THEN SET TextString = REPLACE(TextString, '&#233;', 'é');
39 
END IF ;
 103 
END IF ;
40 
 
 104 
 
41 
IF INSTR( x , '&#234;' )
 105 
IF INSTR( x , '&#234;' )
42 
THEN SET TextString = REPLACE(TextString, '&#234;', 'ê');
 106 
THEN SET TextString = REPLACE(TextString, '&#234;', 'ê');
43 
END IF ;
 107 
END IF ;
- 
 
 108 
 
- 
 
 109 
IF INSTR( x , '&#235;' )
- 
 
 110 
THEN SET TextString = REPLACE(TextString, '&#235;', 'ë');
- 
 
 111 
END IF ;
44 
 
 112 
 
45 
IF INSTR( x , '&#237;' )
 113 
IF INSTR( x , '&#237;' )
46 
THEN SET TextString = REPLACE(TextString, '&#237;', 'í');
 114 
THEN SET TextString = REPLACE(TextString, '&#237;', 'í');
47 
END IF ;
 115 
END IF ;
48 
 
 116 
 
49 
IF INSTR( x , '&#238;' )
 117 
IF INSTR( x , '&#238;' )
50 
THEN SET TextString = REPLACE(TextString, '&#238;', 'î');
 118 
THEN SET TextString = REPLACE(TextString, '&#238;', 'î');
51 
END IF ;
 119 
END IF ;
- 
 
 120 
 
- 
 
 121 
IF INSTR( x , '&#239;' )
- 
 
 122 
THEN SET TextString = REPLACE(TextString, '&#239;', 'ï');
- 
 
 123 
END IF ;
- 
 
 124 
 
- 
 
 125 
IF INSTR( x , '&#242;' )
- 
 
 126 
THEN SET TextString = REPLACE(TextString, '&#242;', 'ò');
- 
 
 127 
END IF ;
52 
 
 128 
 
53 
IF INSTR( x , '&#244;' )
 129 
IF INSTR( x , '&#244;' )
54 
THEN SET TextString = REPLACE(TextString, '&#244;', 'ô');
 130 
THEN SET TextString = REPLACE(TextString, '&#244;', 'ô');
55 
END IF ;
 131 
END IF ;
56 
 
 132 
 
57 
IF INSTR( x , '&#246;' )
 133 
IF INSTR( x , '&#246;' )
58 
THEN SET TextString = REPLACE(TextString, '&#246;', 'ö');
 134 
THEN SET TextString = REPLACE(TextString, '&#246;', 'ö');
59 
END IF ;
 135 
END IF ;
- 
 
 136 
 
- 
 
 137 
IF INSTR( x , '&#249;' )
- 
 
 138 
THEN SET TextString = REPLACE(TextString, '&#249;', 'ù');
- 
 
 139 
END IF ;
- 
 
 140 
 
- 
 
 141 
IF INSTR( x , '&#251;' )
- 
 
 142 
THEN SET TextString = REPLACE(TextString, '&#251;', 'û');
- 
 
 143 
END IF ;
60 
 
 144 
 
61 
IF INSTR( x , '&#252;' )
 145 
IF INSTR( x , '&#252;' )
62 
THEN SET TextString = REPLACE(TextString, '&#252;', 'ü');
 146 
THEN SET TextString = REPLACE(TextString, '&#252;', 'ü');
63 
END IF ;
 147 
END IF ;
- 
 
 148 
 
- 
 
 149 
IF INSTR( x , '&#255;' )
- 
 
 150 
THEN SET TextString = REPLACE(TextString, '&#255;', 'ÿ');
- 
 
 151 
END IF ;
64 
 
 152 
 
65 
RETURN TextString ;
 153 
RETURN TextString ;
66 
 
 154 
 
67 
END$$
 155 
END$$
68 
 
 156 
 
69 
DELIMITER ;
 157 
DELIMITER ;
70 
 
 158 
 
71 
DROP VIEW IF EXISTS legumino.legumino_tapir;
 159 
DROP VIEW IF EXISTS legumino.legumino_tapir;
72 
CREATE ALGORITHM=UNDEFINED DEFINER=`legumino`@`localhost` SQL SECURITY DEFINER VIEW `legumino_tapir` AS
160 
CREATE ALGORITHM=UNDEFINED DEFINER=`legumino`@`localhost` SQL SECURITY DEFINER VIEW `legumino_tapir` AS
73 
	SELECT
161 
	SELECT
74 
		CONCAT('urn:lsid:tela-botanica.org:legumino:', c.id_contributions) AS guid,
 162 
		CONCAT('urn:lsid:tela-botanica.org:legumino:', c.id_contributions) AS guid,
75 
		c.id_contributions AS observation_id,
 163 
		c.id_contributions AS observation_id,
76 
		c.date AS observation_date,
164 
		c.date AS observation_date,
77 
		HTML_UnEncode(CONCAT_WS(' ',
165 
		HTML_UnEncode(CONCAT_WS(' ',
78 
			l.Genre,
166 
			l.Genre,
79 
			l.Espece,
167 
			l.Espece,
80 
			IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))),
168 
			IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))),
81 
			IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL))),
169 
			IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL))),
82 
			IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL)))))) AS nom_scientifique_complet,
 170 
			IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL)))))) AS nom_scientifique_complet,
83 
		l.NumNomen AS nom_num_nomenclatural,
 171 
		l.NumNomen AS nom_num_nomenclatural,
84 
		l.NumTaxon AS nom_num_taxonomique,
 172 
		l.NumTaxon AS nom_num_taxonomique,
85 
		HTML_UnEncode(l.Famille) AS nom_famille,
 173 
		HTML_UnEncode(l.Famille) AS nom_famille,
86 
		HTML_UnEncode(l.Genre) AS nom_genre,
 174 
		HTML_UnEncode(l.Genre) AS nom_genre,
87 
		HTML_UnEncode(l.Espece) AS nom_espece,
 175 
		HTML_UnEncode(l.Espece) AS nom_espece,
88 
		IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))) AS nom_type,
 176 
		IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))) AS nom_type,
89 
		HTML_UnEncode(IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL)))) AS nom_infra_sp,
 177 
		HTML_UnEncode(IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL)))) AS nom_infra_sp,
90 
		HTML_UnEncode(IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL))))) AS nom_auteur,
 178 
		HTML_UnEncode(IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL))))) AS nom_auteur,
91 
		d.nom_departements AS lieu_departement_nom,
 179 
		d.nom_departements AS lieu_departement_nom,
92 
		CONCAT(CONVERT(cm.nom_communes USING UTF8), ' [INSEE:', SUBSTRING(cm.code_insee FROM 4), ']') AS lieu_commune_nom_complet,
 180 
		CONCAT(CONVERT(cm.nom_communes USING UTF8), ' [INSEE:', SUBSTRING(cm.code_insee FROM 4), ']') AS lieu_commune_nom_complet,
93 
		cm.nom_communes AS lieu_commune_nom,
 181 
		cm.nom_communes AS lieu_commune_nom,
94 
		SUBSTRING(cm.code_insee FROM 4) AS lieu_commune_code_insee,
 182 
		SUBSTRING(cm.code_insee FROM 4) AS lieu_commune_code_insee,
95 
		cm.latitude AS lieu_commune_latitude,
 183 
		cm.latitude AS lieu_commune_latitude,
96 
		cm.longitude AS lieu_commune_longitude,
 184 
		cm.longitude AS lieu_commune_longitude,
97 
		HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs , CONCAT('<', ct.mail, '>'))) AS observateur_nom_complet,
 185 
		HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs , CONCAT('<', ct.mail, '>'))) AS observateur_nom_complet,
98 
		HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs)) AS observateur_nom,
 186 
		HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs)) AS observateur_nom,
99 
		ct.mail AS observateur_courriel,
 187 
		ct.mail AS observateur_courriel,
100 
		HTML_UnEncode(c.commentaire) AS observation_commentaire,
 188 
		HTML_UnEncode(c.commentaire) AS observation_commentaire,
101 
		CONCAT(_utf8'<?xml version="1.0" encoding="UTF-8"?>',
189 
		CONCAT(_utf8'<?xml version="1.0" encoding="UTF-8"?>',
102 
			'<info>',
 190 
			'<info>',
103 
				'<nom_num_nomenclatural>',l.NumNomen,'</nom_num_nomenclatural>',
 191 
				'<nom_num_nomenclatural>',l.NumNomen,'</nom_num_nomenclatural>',
104 
				'<nom_num_taxonomique>',l.NumTaxon,'</nom_num_taxonomique>',
 192 
				'<nom_num_taxonomique>',l.NumTaxon,'</nom_num_taxonomique>',
105 
			'</info>') AS observation_information_complement,
 193 
			'</info>') AS observation_information_complement,
106 
		c.date_saisie AS saisie_date_modification
 194 
		DATE_FORMAT(c.date_saisie, '%Y-%m-%d 00:00:00') AS saisie_date_modification
107 
	FROM contributions AS c
195 
	FROM contributions AS c
108 
		LEFT JOIN communes AS cm ON (c.code_insee = cm.code_insee)
 196 
		LEFT JOIN communes AS cm ON (c.code_insee = cm.code_insee)
109 
		LEFT JOIN departements AS d ON (cm.num_departements = d.num_departements)
 197 
		LEFT JOIN departements AS d ON (cm.num_departements = d.num_departements)
110 
		LEFT JOIN legumino AS l ON (c.NumTaxon = l.NumTaxon)
198 
		LEFT JOIN legumino AS l ON (c.NumTaxon = l.NumTaxon)
111 
		LEFT JOIN contributeurs AS ct ON (c.id_contributeurs = ct.id_contributeurs);
 199 
		LEFT JOIN contributeurs AS ct ON (c.id_contributeurs = ct.id_contributeurs);
112 
 
 200