Subversion Repositories Sites.tela-botanica.org

Rev

Rev 623 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 623 Rev 627
Line 1... Line 1...
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
Line 5... Line 5...
5 
 
 5 
 
6 
DECLARE TextString VARCHAR(255) ;
 6 
DECLARE TextString VARCHAR(255) ;
Line -... Line 7...
- 
 
 7 
SET TextString = x ;
- 
 
 8 
 
- 
 
 9 
IF INSTR( x , '"' )
- 
 
 10 
THEN SET TextString = REPLACE(TextString, '"', '"') ;
7 
SET TextString = x ;
 11 
END IF ;
8 
 
 12 
 
9 
IF INSTR( x , '&' )
 13 
IF INSTR( x , '&' )
Line -... Line 14...
- 
 
 14 
THEN SET TextString = REPLACE(TextString, '&', '&') ;
- 
 
 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;' )
10 
THEN SET TextString = REPLACE(TextString, '&#38;', '&') ;
 26 
THEN SET TextString = REPLACE(TextString, '&#145;', '‘') ;
11 
END IF ;
 27 
END IF ;
12 
 
 28 
 
Line -... Line 29...
- 
 
 29 
IF INSTR( x , '&#146;' )
- 
 
 30 
THEN SET TextString = REPLACE(TextString, '&#146;', '''') ;
- 
 
 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 
 
13 
IF INSTR( x , '&#146;' )
 45 
IF INSTR( x , '&#171;' )
14 
THEN SET TextString = REPLACE(TextString, '&#146;', '''') ;
 46 
THEN SET TextString = REPLACE(TextString, '&#171;', '«') ;
15 
END IF ;
 47 
END IF ;
Line -... Line 48...
- 
 
 48 
 
- 
 
 49 
IF INSTR( x , '&#176;' )
- 
 
 50 
THEN SET TextString = REPLACE(TextString, '&#176;', '°') ;
- 
 
 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 ;
16 
 
 76 
 
17 
IF INSTR( x , '&#176;' )
 77 
IF INSTR( x , '&#225;' )
18 
THEN SET TextString = REPLACE(TextString, '&#176;', '°') ;
 78 
THEN SET TextString = REPLACE(TextString, '&#225;', 'á');
Line 19... Line 79...
19 
END IF ;
 79 
END IF ;
Line 28... Line 88...
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;', 'ä');
Line -... Line 91...
- 
 
 91 
END IF ;
- 
 
 92 
 
- 
 
 93 
IF INSTR( x , '&#231;' )
- 
 
 94 
THEN SET TextString = REPLACE(TextString, '&#231;', 'ç');
31 
END IF ;
 95 
END IF ;
32 
 
 96 
 
33 
IF INSTR( x , '&#232;' )
 97 
IF INSTR( x , '&#232;' )
Line 34... Line 98...
34 
THEN SET TextString = REPLACE(TextString, '&#232;', 'è');
 98 
THEN SET TextString = REPLACE(TextString, '&#232;', 'è');
Line 40... Line 104...
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;', 'ê');
Line -... Line 107...
- 
 
 107 
END IF ;
- 
 
 108 
 
- 
 
 109 
IF INSTR( x , '&#235;' )
- 
 
 110 
THEN SET TextString = REPLACE(TextString, '&#235;', 'ë');
43 
END IF ;
 111 
END IF ;
44 
 
 112 
 
45 
IF INSTR( x , '&#237;' )
 113 
IF INSTR( x , '&#237;' )
Line 46... Line 114...
46 
THEN SET TextString = REPLACE(TextString, '&#237;', 'í');
 114 
THEN SET TextString = REPLACE(TextString, '&#237;', 'í');
47 
END IF ;
 115 
END IF ;
48 
 
 116 
 
Line -... Line 117...
- 
 
 117 
IF INSTR( x , '&#238;' )
- 
 
 118 
THEN SET TextString = REPLACE(TextString, '&#238;', 'î');
- 
 
 119 
END IF ;
- 
 
 120 
 
- 
 
 121 
IF INSTR( x , '&#239;' )
- 
 
 122 
THEN SET TextString = REPLACE(TextString, '&#239;', 'ï');
- 
 
 123 
END IF ;
- 
 
 124 
 
49 
IF INSTR( x , '&#238;' )
 125 
IF INSTR( x , '&#242;' )
50 
THEN SET TextString = REPLACE(TextString, '&#238;', 'î');
 126 
THEN SET TextString = REPLACE(TextString, '&#242;', 'ò');
51 
END IF ;
 127 
END IF ;
Line 52... Line 128...
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;', 'ô');
Line -... Line 131...
- 
 
 131 
END IF ;
- 
 
 132 
 
- 
 
 133 
IF INSTR( x , '&#246;' )
- 
 
 134 
THEN SET TextString = REPLACE(TextString, '&#246;', 'ö');
- 
 
 135 
END IF ;
- 
 
 136 
 
- 
 
 137 
IF INSTR( x , '&#249;' )
- 
 
 138 
THEN SET TextString = REPLACE(TextString, '&#249;', 'ù');
55 
END IF ;
 139 
END IF ;
56 
 
 140 
 
57 
IF INSTR( x , '&#246;' )
 141 
IF INSTR( x , '&#251;' )
Line -... Line 142...
- 
 
 142 
THEN SET TextString = REPLACE(TextString, '&#251;', 'û');
- 
 
 143 
END IF ;
- 
 
 144 
 
- 
 
 145 
IF INSTR( x , '&#252;' )
58 
THEN SET TextString = REPLACE(TextString, '&#246;', 'ö');
 146 
THEN SET TextString = REPLACE(TextString, '&#252;', 'ü');
Line 59... Line 147...
59 
END IF ;
 147 
END IF ;
Line 60... Line 148...
60 
 
 148 
 
Line 101... Line 189...
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);