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



# HERBIERS_ORGANISATION vers coel_structure
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION  ORDER BY ID_ORG;# Classement des organisations par ID croissant
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION  DROP NUM_COLLECTION,  DROP ADRESS_TEXT,  DROP TIME_ZONE ;# Suppression des champs inutiles
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
INSERT INTO tb_coel.coel_structure  
	(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_guid, cs_truk_identifiant_alternatif, 
	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, 
		CONVERT(INSTITUTION_NAME USING utf8), CONVERT(ADRESS_LINE USING uft8), 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://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' 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 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, 

	 cp_truk_courriel, cp_truk_url, 
	 cp_description, 
	 cp_ce_truk_role, 
	 cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,  
	 cp_ce_meta)
	SELECT U_ID, '3', CONVERT(CONCAT(U_SURNAME,' ',UPPER(U_NAME)) USING utf8) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), CONVERT(UPPER(U_NAME) USING utf8),
		   CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays USING utf8),
		   LOWER(U_MAIL), U_WEB, 
		   CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8), 
		   IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'), 
		   @idm := @idm+1
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)

	 cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,
	 cp_truk_courriel, cp_truk_url, 
	 cp_description, 
	 cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,  
	 cp_ce_meta)
	SELECT DISTINCT U_ID, '3', CONVERT(CONCAT(U_SURNAME,' ',UPPER(U_NAME)) USING utf8) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), CONVERT(UPPER(U_NAME) USING utf8),
		   CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays USING utf8),
		   LOWER(U_MAIL), U_WEB, 
		   CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8), 
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'), 
		   @idm := @idm+1
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID) 
		LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays) 

#------------------------------------------------------------------------------------------------------------------------
#HERBIERS_STAFF vers coel_personne
INSERT INTO tb_coel.coel_personne
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,
	cp_truk_telephone, cp_truk_fax, cp_ce_meta)
	SELECT 100000+ID_STAFF, '2', CONVERT(CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET USING utf8), CONVERT(UPPER(NOM) USING utf8), CONVERT(PRENOM USING utf8), CONVERT(ADRESSE1 USING utf8), CONVERT(ADRESSE2 USING utf8), CP, CONVERT(VILLE USING utf8), LOWER(MAIL), 
		   IF(TEL != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), IF(FAX != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), NULL), @idm := @idm+1
	FROM tb_herbiers.HERBIERS_STAFF
	ORDER BY ID_STAFF ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 

 
#------------------------------------------------------------------------------------------------------------------------
# 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 

	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,  
	 cc_description_specialiste, cc_truk_url, 
	 cc_specimen_type_nbre, 
	 cc_truk_preservation, 
    cc_ce_meta)
	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), CONVERT(STATUT USING utf8), IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
	   CONVERT(CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',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 

		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)
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 

#------------------------------------------------------------------------------------------------------------------------
#HERBIERS_INDIC vers coel_commentaires
INSERT INTO tb_coel.coel_commentaire
	(ccm_id_commentaire, ccm_ce_projet, ccm_titre, 
	ccm_texte, ccm_ponderation, ccm_ce_meta)
	SELECT ID_INDIC, '2', IF(REM_INDIC IS NOT NULL, IF(REM_INDIC != '', CONVERT(REM_INDIC USING utf8), CONVERT(CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...') USING utf8)),  CONVERT(CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...') USING utf8) AS TITRE,
		TXT_INDIC, 0, @idm := @idm+1 
	FROM tb_herbiers.HERBIERS_INDIC  
	ORDER BY ID_INDIC ASC;
INSERT INTO tb_coel.coel_meta_historique_ligne 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 

Subversion Repositories eFlore/Applications.coel

(root)/trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql @ 94 – Rev 92 → 93

Rev 92		Rev 93
Line 47...		Line 47...
47	`# HERBIERS_ORGANISATION vers coel_structure`	47	`# HERBIERS_ORGANISATION vers coel_structure`
48	`ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG;# Classement des organisations par ID croissant`	48	`ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG;# Classement des organisations par ID croissant`
49	`#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles`	49	`#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles`
50	`SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire`	50	`SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire`
51	`INSERT INTO tb_coel.coel_structure`	51	`INSERT INTO tb_coel.coel_structure`
52	`(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_truk_identifiant_alternatif, cs_guid,`	52	`(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_guid, cs_truk_identifiant_alternatif,`
53	`cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,`	53	`cs_nom, cs_adresse_01, cs_code_postal, cs_ville, cs_region, cs_pays, cs_telephone, cs_fax, cs_courriel,`
54	`cs_description,`	54	`cs_description,`
55	`cs_truk_url,`	55	`cs_truk_url,`
56	`cs_condition_acces,`	56	`cs_condition_acces,`
57	`cs_ce_meta)`	57	`cs_ce_meta)`
58	`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,`	58	`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,`
59	`INSTITUTION_NAME, ADRESS_LINE, ZIP, TOWN, REGION, IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,`	59	`CONVERT(INSTITUTION_NAME USING utf8), CONVERT(ADRESS_LINE USING uft8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL,`
60	`IF(SOURCE_DES_DONNEES != '', CONCAT("Source des données : ",SOURCE_DES_DONNEES), NULL) AS DESCRIPTION,`	60	`IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION ,`
61	`CONCAT(IF(HO_URL != 'http://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS,`	61	`CONCAT(IF(HO_URL != 'http://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS,`
62	`ACCESS_RESTRICTION,`	62	`CONVERT(ACCESS_RESTRICTION USING utf8),`
63	`@idm := @idm+1`	63	`@idm := @idm+1`
64	`FROM tb_herbiers.HERBIERS_ORGANISATION`	64	`FROM tb_herbiers.HERBIERS_ORGANISATION`
65	`ORDER BY ID_ORG ASC;`	65	`ORDER BY ID_ORG ASC;`
66	`INSERT INTO tb_coel.coel_meta_historique_ligne`	66	`INSERT INTO tb_coel.coel_meta_historique_ligne`
67	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,`	67	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,`
Line 112...		Line 112...
112	`cp_truk_courriel, cp_truk_url,`	112	`cp_truk_courriel, cp_truk_url,`
113	`cp_description,`	113	`cp_description,`
114	`cp_ce_truk_role,`	114	`cp_ce_truk_role,`
115	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`	115	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`
116	`cp_ce_meta)`	116	`cp_ce_meta)`
117	`SELECT U_ID, '3', CONCAT(U_SURNAME,' ',UPPER(U_NAME)) AS NOM_COMPLET, U_SURNAME, UPPER(U_NAME),`	117	`SELECT U_ID, '3', CONVERT(CONCAT(U_SURNAME,' ',UPPER(U_NAME)) USING utf8) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), CONVERT(UPPER(U_NAME) USING utf8),`
118	`U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays,`	118	`CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays USING utf8),`
119	`LOWER(U_MAIL), U_WEB,`	119	`LOWER(U_MAIL), U_WEB,`
120	`CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE),`	120	`CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8),`
121	`IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),`	121	`IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),`
122	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`	122	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`
123	`@idm := @idm+1`	123	`@idm := @idm+1`
124	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP`	124	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP`
125	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)`	125	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)`
Line 142...		Line 142...
142	`cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,`	142	`cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,`
143	`cp_truk_courriel, cp_truk_url,`	143	`cp_truk_courriel, cp_truk_url,`
144	`cp_description,`	144	`cp_description,`
145	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`	145	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`
146	`cp_ce_meta)`	146	`cp_ce_meta)`
147	`SELECT DISTINCT U_ID, '3', CONCAT(U_SURNAME,' ',UPPER(U_NAME)) AS NOM_COMPLET, U_SURNAME, UPPER(U_NAME),`	147	`SELECT DISTINCT U_ID, '3', CONVERT(CONCAT(U_SURNAME,' ',UPPER(U_NAME)) USING utf8) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), CONVERT(UPPER(U_NAME) USING utf8),`
148	`U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays,`	148	`CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays USING utf8),`
149	`LOWER(U_MAIL), U_WEB,`	149	`LOWER(U_MAIL), U_WEB,`
150	`CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE),`	150	`CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8),`
151	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`	151	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`
152	`@idm := @idm+1`	152	`@idm := @idm+1`
153	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`	153	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`
154	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`	154	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`
155	`LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)`	155	`LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)`
Line 184...		Line 184...
184	`#------------------------------------------------------------------------------------------------------------------------`	184	`#------------------------------------------------------------------------------------------------------------------------`
185	`#HERBIERS_STAFF vers coel_personne`	185	`#HERBIERS_STAFF vers coel_personne`
186	`INSERT INTO tb_coel.coel_personne`	186	`INSERT INTO tb_coel.coel_personne`
187	`(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,`	187	`(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_nom, cp_prenom, cp_adresse_01, cp_adresse_02, cp_code_postal, cp_ville, cp_truk_courriel,`
188	`cp_truk_telephone, cp_truk_fax, cp_ce_meta)`	188	`cp_truk_telephone, cp_truk_fax, cp_ce_meta)`
189	`SELECT 100000+ID_STAFF, '2', CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET, UPPER(NOM), PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL),`	189	`SELECT 100000+ID_STAFF, '2', CONVERT(CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET USING utf8), CONVERT(UPPER(NOM) USING utf8), CONVERT(PRENOM USING utf8), CONVERT(ADRESSE1 USING utf8), CONVERT(ADRESSE2 USING utf8), CP, CONVERT(VILLE USING utf8), LOWER(MAIL),`
190	`IF(TEL != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), IF(FAX != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), NULL), @idm := @idm+1`	190	`IF(TEL != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(TEL,'[33]',''))), NULL), IF(FAX != '', CONCAT('+33', TRIM(LEADING '0' FROM REPLACE(FAX,'[33]',''))), NULL), @idm := @idm+1`
191	`FROM tb_herbiers.HERBIERS_STAFF`	191	`FROM tb_herbiers.HERBIERS_STAFF`
192	`ORDER BY ID_STAFF ASC;`	192	`ORDER BY ID_STAFF ASC;`
193	`INSERT INTO tb_coel.coel_meta_historique_ligne`	193	`INSERT INTO tb_coel.coel_meta_historique_ligne`
194	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	194	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
Line 215...		Line 215...
215		215
216	`#------------------------------------------------------------------------------------------------------------------------`	216	`#------------------------------------------------------------------------------------------------------------------------`
217	`# HERBIERS_ont_un_staff vers coel_structure_a_personne`	217	`# HERBIERS_ont_un_staff vers coel_structure_a_personne`
218	`INSERT INTO tb_coel.coel_structure_a_personne`	218	`INSERT INTO tb_coel.coel_structure_a_personne`
219	`(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)`	219	`(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)`
220	`SELECT ID_ORG, 100000+hous.ID_STAFF, '2027', IF(LOWER(CONTACT) = 'oui', 1, 0) AS CONTACT, IF(FONCTION != '', CONCAT('AUTRE#', FONCTION), NULL) AS FONCTION, @idm := @idm+1`	220	`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`
221	`FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)`	221	`FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)`
222	`WHERE hous.ID_ORG != 0`	222	`WHERE hous.ID_ORG != 0`
223	`ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;`	223	`ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;`
224	`INSERT INTO tb_coel.coel_meta_historique_ligne`	224	`INSERT INTO tb_coel.coel_meta_historique_ligne`
Line 242...		Line 242...
242	`(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,`	242	`(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,`
243	`cc_description_specialiste, cc_truk_url,`	243	`cc_description_specialiste, cc_truk_url,`
244	`cc_specimen_type_nbre,`	244	`cc_specimen_type_nbre,`
245	`cc_truk_preservation,`	245	`cc_truk_preservation,`
246	`cc_ce_meta)`	246	`cc_ce_meta)`
247	`SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, NOM_COLLECTION, CONVERT(DESCRIPTION USING utf8), STATUT, IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,`	247	`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), CONVERT(STATUT USING utf8), IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,`
248	`CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',COLLECTEURS)))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,`	248	`CONVERT(CONCAT(IF(DOC_STATE = '', '', IF(DOC_STATE IS NULL, '', CONCAT('Etat documentation : \n',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',COLLECTEURS)))) USING utf8) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL,`
249	`NUM_SPECIMENS,`	249	`NUM_SPECIMENS,`
250	`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,`	250	`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,`
251	`@idm := @idm+1`	251	`@idm := @idm+1`
252	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`	252	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`
253	`WHERE hc.ID != 4`	253	`WHERE hc.ID != 4`
Line 264...		Line 264...
264	`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))))),`	264	`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))))),`
265	`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)))))),`	265	`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)))))),`
266	`PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),`	266	`PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),`
267	`CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),`	267	`CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),`
268	`CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),`	268	`CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),`
269	`IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)`	269	`IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL)`
270	`FROM tb_herbiers.HERBIERS_COLLECTION`	270	`FROM tb_herbiers.HERBIERS_COLLECTION`
271	`WHERE ID != 4`	271	`WHERE ID != 4`
272	`ORDER BY ID ASC;`	272	`ORDER BY ID ASC;`
273	`INSERT INTO tb_coel.coel_meta_historique_ligne`	273	`INSERT INTO tb_coel.coel_meta_historique_ligne`
274	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	274	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
Line 282...		Line 282...
282	`#------------------------------------------------------------------------------------------------------------------------`	282	`#------------------------------------------------------------------------------------------------------------------------`
283	`#HERBIERS_INDIC vers coel_commentaires`	283	`#HERBIERS_INDIC vers coel_commentaires`
284	`INSERT INTO tb_coel.coel_commentaire`	284	`INSERT INTO tb_coel.coel_commentaire`
285	`(ccm_id_commentaire, ccm_ce_projet, ccm_titre,`	285	`(ccm_id_commentaire, ccm_ce_projet, ccm_titre,`
286	`ccm_texte, ccm_ponderation, ccm_ce_meta)`	286	`ccm_texte, ccm_ponderation, ccm_ce_meta)`
287	`SELECT ID_INDIC, '2', IF(REM_INDIC IS NOT NULL, IF(REM_INDIC != '', REM_INDIC, CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...')), CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...')) AS TITRE,`	287	`SELECT ID_INDIC, '2', IF(REM_INDIC IS NOT NULL, IF(REM_INDIC != '', CONVERT(REM_INDIC USING utf8), CONVERT(CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...') USING utf8)), CONVERT(CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...') USING utf8) AS TITRE,`
288	`TXT_INDIC, 0, @idm := @idm+1`	288	`TXT_INDIC, 0, @idm := @idm+1`
289	`FROM tb_herbiers.HERBIERS_INDIC`	289	`FROM tb_herbiers.HERBIERS_INDIC`
290	`ORDER BY ID_INDIC ASC;`	290	`ORDER BY ID_INDIC ASC;`
291	`INSERT INTO tb_coel.coel_meta_historique_ligne`	291	`INSERT INTO tb_coel.coel_meta_historique_ligne`
292	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	292	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`