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);