WebSVN – eFlore/Applications.coel – Diff – /trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql



	cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,
	cs_description, 
	cs_truk_url,
	cs_condition_acces,  
	cs_ce_meta) 
	SELECT ID_ORG, '2', '0', CONCAT("URN:tela-botanica.org:ihf:str",ID_ORG) AS GUID, IF(INDEX_HERB != '', CONCAT('IH##',INDEX_HERB), NULL) AS IDENTIFIANT_ALTERNATIF, 
		REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL, 
		IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION , 
		CONCAT(IF(HO_URL != 'http://' AND HO_URL != '', CONCAT('WEB##',HO_URL), ''),IF(HO_URL != 'http://' AND HO_URL != '' AND LOGO != '', ';;', ''), IF(LOGO != '', CONCAT('LOGO##',LOGO),'')) AS URLS, 
		CONVERT(ACCESS_RESTRICTION USING utf8),
		@idm := @idm+1 
	FROM tb_herbiers.HERBIERS_ORGANISATION 
	ORDER BY ID_ORG ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 

	ORDER BY ID_STAFF ASC;

 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', ';; +33'),cp_truk_telephone);
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '+336%', CONCAT('GSM##', cp_truk_telephone), CONCAT('FIX##', cp_truk_telephone));
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);

UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;; +336%', REPLACE(cp_truk_telephone, ';; +33', ';; GSM##+33'), REPLACE(cp_truk_telephone, ';; +33', ';; FIX##+33')) WHERE cp_truk_telephone LIKE '%;;%';
 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';

 
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_ont_un_staff vers coel_structure_a_personne
INSERT INTO tb_coel.coel_structure_a_personne
	(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
	SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE##', CONVERT(FONCTION USING utf8)), NULL) AS FONCTION, @idm := @idm+1
	FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF) 
	WHERE hous.ID_ORG != 0 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 

	SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8), 
		IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))), 
		IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
		CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n', CONVERT(DOC_STATE USING utf8)))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n', CONVERT(COLLECTEURS USING utf8))))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL, 
		NUM_SPECIMENS, 
		CAST(GROUP_CONCAT(DISTINCT CASE ID_PRES WHEN '1' THEN '2204' WHEN '2' THEN '2222' WHEN '3' THEN '2215' WHEN '4' THEN '2203' WHEN '5' THEN '2211' WHEN '6' THEN '2223' WHEN '7' THEN '2207' WHEN '8' THEN '2209' WHEN '9' THEN '2224' WHEN '10' THEN '2218' WHEN '12' THEN '2212' WHEN '13' THEN 'AUTRE##non précisé' ELSE ID_PRES END SEPARATOR ";;") AS CHAR)  AS PRESENTATION, 
		@idm := @idm+1
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID) 
	WHERE hc.ID != 4 
	GROUP BY hc.ID 
	ORDER BY hc.ID ASC;

	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
	ccb_truk_degradation_specimen, 
	ccb_truk_degradation_presentation,
	ccb_annotation_classement,
	ccb_ce_meta)
	SELECT ID, CONCAT('TOTAL##0|NULL|NULL|',NUM_SPECIMENS,'|', IF(PREC_SPECIMENS = '1', '0', IF(PREC_SPECIMENS = '2', '1', IF(PREC_SPECIMENS = '3', '?', 'NULL'))), '|', NUM_SPECIES, '|', IF(PREC_SPECIES = '1', '0', IF(PREC_SPECIES = '2', '1', IF(PREC_SPECIES = '3', '?', 'NULL')))), 
		DATE_DEBUT, IF(DATE_DEBUT_CARAC = '1', '2316', IF (DATE_DEBUT_CARAC = '2', '2317', IF (DATE_DEBUT_CARAC = '3', '2318', IF(DATE_DEBUT_CARAC = '4', '2319', IF (DATE_DEBUT_CARAC = '5', '2320', NULL))))), 
		DATE_FIN, IF( DATE_FIN_CARAC = '1', '2321', IF (DATE_FIN_CARAC = '2', '2322', IF(DATE_FIN_CARAC = '3', '2323', IF (DATE_FIN_CARAC = '4', '2325', IF(DATE_FIN_CARAC = '5', '2324', IF (DATE_FIN_CARAC = '6', '2326', NULL)))))), 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
		CONCAT('TOTAL##',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
		CONCAT('TOTAL##',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL), 
		@idm := @idm+1 
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;

Subversion Repositories eFlore/Applications.coel

(root)/trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql – Rev 158 → 162

Rev 158		Rev 162
Line 65...		Line 65...
65	`cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,`	65	`cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,`
66	`cs_description,`	66	`cs_description,`
67	`cs_truk_url,`	67	`cs_truk_url,`
68	`cs_condition_acces,`	68	`cs_condition_acces,`
69	`cs_ce_meta)`	69	`cs_ce_meta)`
70	`SELECT ID_ORG, '2', '0', CONCAT("URN:tela-botanica.org:ihf:str",ID_ORG) AS GUID, IF(INDEX_HERB != '', CONCAT('IH#',INDEX_HERB), NULL) AS IDENTIFIANT_ALTERNATIF,`	70	`SELECT ID_ORG, '2', '0', CONCAT("URN:tela-botanica.org:ihf:str",ID_ORG) AS GUID, IF(INDEX_HERB != '', CONCAT('IH##',INDEX_HERB), NULL) AS IDENTIFIANT_ALTERNATIF,`
71	`REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,`	71	`REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,`
72	`IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION ,`	72	`IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION ,`
73	`CONCAT(IF(HO_URL != 'http://' AND HO_URL != '', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND HO_URL != '' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS,`	73	`CONCAT(IF(HO_URL != 'http://' AND HO_URL != '', CONCAT('WEB##',HO_URL), ''),IF(HO_URL != 'http://' AND HO_URL != '' AND LOGO != '', ';;', ''), IF(LOGO != '', CONCAT('LOGO##',LOGO),'')) AS URLS,`
74	`CONVERT(ACCESS_RESTRICTION USING utf8),`	74	`CONVERT(ACCESS_RESTRICTION USING utf8),`
75	`@idm := @idm+1`	75	`@idm := @idm+1`
76	`FROM tb_herbiers.HERBIERS_ORGANISATION`	76	`FROM tb_herbiers.HERBIERS_ORGANISATION`
77	`ORDER BY ID_ORG ASC;`	77	`ORDER BY ID_ORG ASC;`
78	`INSERT INTO tb_coel.coel_meta_historique_ligne`	78	`INSERT INTO tb_coel.coel_meta_historique_ligne`
Line 211...		Line 211...
211	`ORDER BY ID_STAFF ASC;`	211	`ORDER BY ID_STAFF ASC;`
Line 212...		Line 212...
212		212
213	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';`	213	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';`
214	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';`	214	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';`
215	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';`	215	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';`
216	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', '; +33'),cp_truk_telephone);`	216	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', ';; +33'),cp_truk_telephone);`
217	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '+336%', CONCAT('GSM#', cp_truk_telephone), CONCAT('FIX#', cp_truk_telephone));`	217	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '+336%', CONCAT('GSM##', cp_truk_telephone), CONCAT('FIX##', cp_truk_telephone));`
218	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);`	218	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);`
Line 219...		Line 219...
219	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%; +336%', REPLACE(cp_truk_telephone, '; +33', '; GSM#+33'), REPLACE(cp_truk_telephone, '; +33', '; FIX#+33')) WHERE cp_truk_telephone LIKE '%;%';`	219	`UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;; +336%', REPLACE(cp_truk_telephone, ';; +33', ';; GSM##+33'), REPLACE(cp_truk_telephone, ';; +33', ';; FIX##+33')) WHERE cp_truk_telephone LIKE '%;;%';`
220		220
221	`UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';`	221	`UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';`
222	`UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';`	222	`UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';`
Line 227...		Line 227...
227		227
228	`#------------------------------------------------------------------------------------------------------------------------`	228	`#------------------------------------------------------------------------------------------------------------------------`
229	`# HERBIERS_ont_un_staff vers coel_structure_a_personne`	229	`# HERBIERS_ont_un_staff vers coel_structure_a_personne`
230	`INSERT INTO tb_coel.coel_structure_a_personne`	230	`INSERT INTO tb_coel.coel_structure_a_personne`
231	`(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)`	231	`(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)`
232	`SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE#', CONVERT(FONCTION USING utf8)), NULL) AS FONCTION, @idm := @idm+1`	232	`SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE##', CONVERT(FONCTION USING utf8)), NULL) AS FONCTION, @idm := @idm+1`
233	`FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)`	233	`FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)`
234	`WHERE hous.ID_ORG != 0`	234	`WHERE hous.ID_ORG != 0`
235	`ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;`	235	`ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;`
236	`INSERT INTO tb_coel.coel_meta_historique_ligne`	236	`INSERT INTO tb_coel.coel_meta_historique_ligne`
Line 261...		Line 261...
261	`SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8),`	261	`SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8),`
262	`IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))),`	262	`IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))),`
263	`IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,`	263	`IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,`
264	`CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n', CONVERT(DOC_STATE USING utf8)))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n', CONVERT(COLLECTEURS USING utf8))))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,`	264	`CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n', CONVERT(DOC_STATE USING utf8)))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n', CONVERT(COLLECTEURS USING utf8))))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,`
265	`NUM_SPECIMENS,`	265	`NUM_SPECIMENS,`
266	`CAST(GROUP_CONCAT(DISTINCT CASE ID_PRES WHEN '1' THEN '2204' WHEN '2' THEN '2222' WHEN '3' THEN '2215' WHEN '4' THEN '2203' WHEN '5' THEN '2211' WHEN '6' THEN '2223' WHEN '7' THEN '2207' WHEN '8' THEN '2209' WHEN '9' THEN '2224' WHEN '10' THEN '2218' WHEN '12' THEN '2212' WHEN '13' THEN 'AUTRE#non précisé' ELSE ID_PRES END SEPARATOR ";") AS CHAR) AS PRESENTATION,`	266	`CAST(GROUP_CONCAT(DISTINCT CASE ID_PRES WHEN '1' THEN '2204' WHEN '2' THEN '2222' WHEN '3' THEN '2215' WHEN '4' THEN '2203' WHEN '5' THEN '2211' WHEN '6' THEN '2223' WHEN '7' THEN '2207' WHEN '8' THEN '2209' WHEN '9' THEN '2224' WHEN '10' THEN '2218' WHEN '12' THEN '2212' WHEN '13' THEN 'AUTRE##non précisé' ELSE ID_PRES END SEPARATOR ";;") AS CHAR) AS PRESENTATION,`
267	`@idm := @idm+1`	267	`@idm := @idm+1`
268	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`	268	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`
269	`WHERE hc.ID != 4`	269	`WHERE hc.ID != 4`
270	`GROUP BY hc.ID`	270	`GROUP BY hc.ID`
271	`ORDER BY hc.ID ASC;`	271	`ORDER BY hc.ID ASC;`
Line 285...		Line 285...
285	`ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,`	285	`ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,`
286	`ccb_truk_degradation_specimen,`	286	`ccb_truk_degradation_specimen,`
287	`ccb_truk_degradation_presentation,`	287	`ccb_truk_degradation_presentation,`
288	`ccb_annotation_classement,`	288	`ccb_annotation_classement,`
289	`ccb_ce_meta)`	289	`ccb_ce_meta)`
290	`SELECT ID, CONCAT('TOTAL#0\|NULL\|NULL\|',NUM_SPECIMENS,'\|', IF(PREC_SPECIMENS = '1', '0', IF(PREC_SPECIMENS = '2', '1', IF(PREC_SPECIMENS = '3', '?', 'NULL'))), '\|', NUM_SPECIES, '\|', IF(PREC_SPECIES = '1', '0', IF(PREC_SPECIES = '2', '1', IF(PREC_SPECIES = '3', '?', 'NULL')))),`	290	`SELECT ID, CONCAT('TOTAL##0\|NULL\|NULL\|',NUM_SPECIMENS,'\|', IF(PREC_SPECIMENS = '1', '0', IF(PREC_SPECIMENS = '2', '1', IF(PREC_SPECIMENS = '3', '?', 'NULL'))), '\|', NUM_SPECIES, '\|', IF(PREC_SPECIES = '1', '0', IF(PREC_SPECIES = '2', '1', IF(PREC_SPECIES = '3', '?', 'NULL')))),`
291	`DATE_DEBUT, IF(DATE_DEBUT_CARAC = '1', '2316', IF (DATE_DEBUT_CARAC = '2', '2317', IF (DATE_DEBUT_CARAC = '3', '2318', IF(DATE_DEBUT_CARAC = '4', '2319', IF (DATE_DEBUT_CARAC = '5', '2320', NULL))))),`	291	`DATE_DEBUT, IF(DATE_DEBUT_CARAC = '1', '2316', IF (DATE_DEBUT_CARAC = '2', '2317', IF (DATE_DEBUT_CARAC = '3', '2318', IF(DATE_DEBUT_CARAC = '4', '2319', IF (DATE_DEBUT_CARAC = '5', '2320', NULL))))),`
292	`DATE_FIN, IF( DATE_FIN_CARAC = '1', '2321', IF (DATE_FIN_CARAC = '2', '2322', IF(DATE_FIN_CARAC = '3', '2323', IF (DATE_FIN_CARAC = '4', '2325', IF(DATE_FIN_CARAC = '5', '2324', IF (DATE_FIN_CARAC = '6', '2326', NULL)))))),`	292	`DATE_FIN, IF( DATE_FIN_CARAC = '1', '2321', IF (DATE_FIN_CARAC = '2', '2322', IF(DATE_FIN_CARAC = '3', '2323', IF (DATE_FIN_CARAC = '4', '2325', IF(DATE_FIN_CARAC = '5', '2324', IF (DATE_FIN_CARAC = '6', '2326', NULL)))))),`
293	`PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),`	293	`PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),`
294	`CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),`	294	`CONCAT('TOTAL##',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),`
295	`CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),`	295	`CONCAT('TOTAL##',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),`
296	`IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL),`	296	`IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL),`
297	`@idm := @idm+1`	297	`@idm := @idm+1`
298	`FROM tb_herbiers.HERBIERS_COLLECTION`	298	`FROM tb_herbiers.HERBIERS_COLLECTION`
299	`WHERE ID != 4`	299	`WHERE ID != 4`
300	`ORDER BY ID ASC;`	300	`ORDER BY ID ASC;`