Subversion Repositories eFlore/Applications.coel

Rev

Rev 1314 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1313 jpm 1
SET @id_table_cmlv = '112';
2
SET @id_admin = '1';
3
SET @ip_admin = '162.38.234.1';
4
SET @id_meta = '';
5
SET @enrg = '';
6
#+--------------------------------------------------------------------------------------------------------------------+#
7
# AJOUT
8
SET @id_etat_modif = '1';
9
 
10
# Ajout d''une valeur à la liste 58
11
SET @notes = 'Initialisation de la valeur «4» pour la liste «58».';
12
 
13
SET @id_valeur = '30762';
14
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
15
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
16
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
17
INSERT INTO tb_coel.coel_meta_liste_valeur (cmlv_id_valeur, cmlv_ce_parent, cmlv_ce_projet, cmlv_nom, cmlv_abreviation, cmlv_description, cmlv_ce_meta)
18
	VALUES ( @id_valeur, '1061', '1', 'Dactylographié', '4', NULL, @id_meta );
19
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
20
	'<row>\n',
21
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
22
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
23
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
24
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
25
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
26
	'<cmlv_description>', IF(cmlv_description IS NOT NULL, cmlv_description, ''), '</cmlv_description>\n',
27
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
28
	'</row>\n',
29
	'</resultset>') AS xmldoc
30
	FROM tb_coel.coel_meta_liste_valeur
31
	WHERE cmlv_id_valeur = @id_valeur;
32
UPDATE tb_coel.coel_meta_historique_ligne SET
33
	cmhl_enregistrement = @enrg
34
	WHERE cmhl_id_historique_ligne = @id_meta;
35
 
36
#+--------------------------------------------------------------------------------------------------------------------+#
37
# MODIFICATION
38
SET @id_etat_modif = '2';
39
 
40
# Mise à jour des abréviations de la liste 27 id 1030
41
SET @notes = 'Remplacement de l''abréviation numérique par un code.';
42
 
43
SET @id_valeur = '2134';
44
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
45
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
46
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
47
UPDATE tb_coel.coel_meta_liste_valeur SET
48
	cmlv_abreviation = 'CT',
49
	cmlv_ce_meta = @id_meta
50
	WHERE cmlv_id_valeur = @id_valeur;
51
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
52
	'<row>\n',
53
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
54
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
55
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
56
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
57
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
58
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
59
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
60
	'</row>\n',
61
	'</resultset>') AS xmldoc
62
	FROM tb_coel.coel_meta_liste_valeur
63
	WHERE cmlv_id_valeur = @id_valeur;
64
UPDATE tb_coel.coel_meta_historique_ligne SET
65
	cmhl_enregistrement = @enrg
66
	WHERE cmhl_id_historique_ligne = @id_meta;
67
 
68
SET @id_valeur = '2135';
69
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
70
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
71
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
72
UPDATE tb_coel.coel_meta_liste_valeur SET
73
	cmlv_abreviation = 'AU',
74
	cmlv_ce_meta = @id_meta
75
	WHERE cmlv_id_valeur = @id_valeur;
76
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
77
	'<row>\n',
78
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
79
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
80
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
81
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
82
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
83
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
84
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
85
	'</row>\n',
86
	'</resultset>') AS xmldoc
87
	FROM tb_coel.coel_meta_liste_valeur
88
	WHERE cmlv_id_valeur = @id_valeur;
89
UPDATE tb_coel.coel_meta_historique_ligne SET
90
	cmhl_enregistrement = @enrg
91
	WHERE cmhl_id_historique_ligne = @id_meta;
92
 
93
SET @id_valeur = '2136';
94
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
95
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
96
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
97
UPDATE tb_coel.coel_meta_liste_valeur SET
98
	cmlv_abreviation = 'CO',
99
	cmlv_ce_meta = @id_meta
100
	WHERE cmlv_id_valeur = @id_valeur;
101
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
102
	'<row>\n',
103
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
104
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
105
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
106
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
107
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
108
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
109
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
110
	'</row>\n',
111
	'</resultset>') AS xmldoc
112
	FROM tb_coel.coel_meta_liste_valeur
113
	WHERE cmlv_id_valeur = @id_valeur;
114
UPDATE tb_coel.coel_meta_historique_ligne SET
115
	cmhl_enregistrement = @enrg
116
	WHERE cmhl_id_historique_ligne = @id_meta;
117
 
118
SET @id_valeur = '2137';
119
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
120
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
121
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
122
UPDATE tb_coel.coel_meta_liste_valeur SET
123
	cmlv_abreviation = 'DE',
124
	cmlv_ce_meta = @id_meta
125
	WHERE cmlv_id_valeur = @id_valeur;
126
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
127
	'<row>\n',
128
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
129
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
130
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
131
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
132
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
133
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
134
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
135
	'</row>\n',
136
	'</resultset>') AS xmldoc
137
	FROM tb_coel.coel_meta_liste_valeur
138
	WHERE cmlv_id_valeur = @id_valeur;
139
UPDATE tb_coel.coel_meta_historique_ligne SET
140
	cmhl_enregistrement = @enrg
141
	WHERE cmhl_id_historique_ligne = @id_meta;
142
 
143
SET @id_valeur = '2138';
144
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
145
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
146
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
147
UPDATE tb_coel.coel_meta_liste_valeur SET
148
	cmlv_abreviation = 'HE',
149
	cmlv_ce_meta = @id_meta
150
	WHERE cmlv_id_valeur = @id_valeur;
151
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
152
	'<row>\n',
153
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
154
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
155
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
156
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
157
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
158
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
159
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
160
	'</row>\n',
161
	'</resultset>') AS xmldoc
162
	FROM tb_coel.coel_meta_liste_valeur
163
	WHERE cmlv_id_valeur = @id_valeur;
164
UPDATE tb_coel.coel_meta_historique_ligne SET
165
	cmhl_enregistrement = @enrg
166
	WHERE cmhl_id_historique_ligne = @id_meta;
167
 
168
 
169
#Correction bogue  FS#535 - [Collection] - Fautes de frappes
170
SET @notes = 'Correction syntaxe du nom.';
171
 
172
SET @id_valeur = '2295';
173
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
174
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
175
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
176
UPDATE tb_coel.coel_meta_liste_valeur SET
177
	cmlv_nom = 'Papier adhésif type scotch',
178
	cmlv_ce_meta = @id_meta
179
	WHERE cmlv_id_valeur = @id_valeur;
180
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
181
	'<row>\n',
182
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
183
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
184
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
185
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
186
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
187
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
188
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
189
	'</row>\n',
190
	'</resultset>') AS xmldoc
191
	FROM tb_coel.coel_meta_liste_valeur
192
	WHERE cmlv_id_valeur = @id_valeur;
193
UPDATE tb_coel.coel_meta_historique_ligne SET
194
	cmhl_enregistrement = @enrg
195
	WHERE cmhl_id_historique_ligne = @id_meta;
196
 
197
SET @id_valeur = '2247';
198
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
199
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
200
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
201
UPDATE tb_coel.coel_meta_liste_valeur SET
202
	cmlv_nom = 'Gymnospermes (Conifère)',
203
	cmlv_ce_meta = @id_meta
204
	WHERE cmlv_id_valeur = @id_valeur;
205
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
206
	'<row>\n',
207
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
208
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
209
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
210
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
211
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
212
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
213
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
214
	'</row>\n',
215
	'</resultset>') AS xmldoc
216
	FROM tb_coel.coel_meta_liste_valeur
217
	WHERE cmlv_id_valeur = @id_valeur;
218
UPDATE tb_coel.coel_meta_historique_ligne SET
219
	cmhl_enregistrement = @enrg
220
	WHERE cmhl_id_historique_ligne = @id_meta;
221
 
222
SET @id_valeur = '2248';
223
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
224
	VALUES	(@id_table_cmlv, @id_valeur, NULL , NOW( ) , @notes, @id_admin, @id_etat_modif, @ip_admin);
225
SELECT @id_meta := cmhl_id_historique_ligne, cmhl_date_modification FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_ce_table = CONVERT(@id_table_cmlv USING utf8) AND cmhl_cle_ligne = CONVERT(@id_valeur USING utf8) ORDER BY cmhl_date_modification DESC LIMIT 1;
226
UPDATE tb_coel.coel_meta_liste_valeur SET
227
	cmlv_nom = 'Angiospermes (plantes à fleur)',
228
	cmlv_ce_meta = @id_meta
229
	WHERE cmlv_id_valeur = @id_valeur;
230
SELECT @enrg := CONCAT('<?xml version="1.0" encoding="UTF-8" ?>\n<resultset>\n',
231
	'<row>\n',
232
	'<cmlv_id_valeur>', cmlv_id_valeur, '</cmlv_id_valeur>\n',
233
	'<cmlv_ce_parent>', cmlv_ce_parent, '</cmlv_ce_parent>\n',
234
	'<cmlv_ce_projet>', cmlv_ce_projet, '</cmlv_ce_projet>\n',
235
	'<cmlv_nom>', cmlv_nom, '</cmlv_nom>\n',
236
	'<cmlv_abreviation>', cmlv_abreviation, '</cmlv_abreviation>\n',
237
	'<cmlv_description>', cmlv_description, '</cmlv_description>\n',
238
	'<cmlv_ce_meta>', cmlv_ce_meta, '</cmlv_ce_meta>\n',
239
	'</row>\n',
240
	'</resultset>') AS xmldoc
241
	FROM tb_coel.coel_meta_liste_valeur
242
	WHERE cmlv_id_valeur = @id_valeur;
243
UPDATE tb_coel.coel_meta_historique_ligne SET
244
	cmhl_enregistrement = @enrg
245
	WHERE cmhl_id_historique_ligne = @id_meta;
246
 
247
# Correction bogue FS#645 - [BD] Nettoyage de CollectionACommentaire
248
DELETE FROM tb_coel.coel_collection_a_commentaire
249
	WHERE ccac_id_collection NOT IN (SELECT cc_id_collection FROM tb_coel.coel_collection);