Subversion Repositories eFlore/Applications.coel

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
54 jpm 1 
#------------------------------------------------------------------------------------------------------------------------
2 
# SCRIPT de MIGRATION de HERBIERS vers COEL v1.0
3 
#------------------------------------------------------------------------------------------------------------------------
4 
# TODO : remplacer les valeurs vides par NULL -> utiliser le script remplacer_vide_par_null.php
5 
 
586 jp_milcent 6 
#---------------------------------------------------------------------------------------------------------------------#
7 
# Variables
601 jp_milcent 8 
SET @idm = 29170;# Identifiant pour les métadonnées
9 
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
56 jpm 10 
SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base
54 jpm 11 
#---------------------------------------------------------------------------------------------------------------------#
56 jpm 12 
# Nettoyage des tables
589 jp_milcent 13 
# coel_projet
75 jpm 14 
DELETE FROM tb_coel.coel_projet WHERE cpr_id_projet IN (2,3);
589 jp_milcent 15 
# coel_projet_version
16 
DELETE FROM tb_coel.coel_projet_version WHERE cprv_id_version IN (1,2);
17 
# coel_meta_historique_ligne
56 jpm 18 
DELETE FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_id_historique_ligne >= @idm AND cmhl_id_historique_ligne < @idm_max;
600 jp_milcent 19 
# coel_structure
56 jpm 20 
DELETE FROM tb_coel.coel_structure WHERE cs_id_structure IN (SELECT ID_ORG FROM tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG ASC);
78 jpm 21 
# coel_structure_a_personne
22 
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role IN ('2026', '2027');
23 
# coel_personne
24 
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet IN ('2', '3');
75 jpm 25 
# coel_personne_a_relation
26 
DELETE FROM tb_coel.coel_personne_a_relation WHERE cpar_id_projet = '2';
78 jpm 27 
# coel_collection_botanique
28 
DELETE FROM tb_coel.coel_collection_botanique WHERE ccb_id_collection IN (SELECT cc_id_collection FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2'));
29 
# coel_collection_a_commentaire
589 jp_milcent 30 
DELETE FROM tb_coel.coel_collection_a_commentaire ;
78 jpm 31 
# coel_collection
32 
DELETE FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2');
33 
# coel_commentaire
34 
DELETE FROM tb_coel.coel_commentaire WHERE ccm_ce_projet IN ('2');
56 jpm 35 
 
36 
#---------------------------------------------------------------------------------------------------------------------#
37 
# EFLORE_PROJET vers coel_projet
38 
# Insertion du projet Inventaire des Herbiers de France - Version 1
167 jp_milcent 39 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_mark_public, cpr_ce_meta)
601 jp_milcent 40 
	SELECT 2, CONVERT(EPR_INTITULE_PROJET USING utf8), CONVERT(EPR_ABREVIATION_PROJET USING utf8), CONVERT(EPR_DESCRIPTION_PROJET USING utf8), EPR_LIEN_WEB, 1, @idm := @idm+1
56 jpm 41 
	FROM tb_herbiers.EFLORE_PROJET;
73 jpm 42 
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
601 jp_milcent 43 
	VALUES	(@idm_tmp := @idm_tmp+1, '115', '2', NULL , NOW( ) , 'Ajout du projet IHF', '1', '1', '162.38.234.1');
73 jpm 44 
# Insertion du projet Utilisateur de l''application COEL - Version 1
167 jp_milcent 45 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_mark_public, cpr_ce_meta)
601 jp_milcent 46 
	VALUES (3, 'Utilisateurs', 'COEL-U', 'Les utilisateurs de l''application COEL.', NULL, 0, @idm := @idm+1);
73 jpm 47 
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
601 jp_milcent 48 
	VALUES	(@idm_tmp := @idm_tmp+1, '115', '2', NULL , NOW( ) , 'Ajout du projet U', '1', '1', '162.38.234.1');
54 jpm 49 
 
95 jpm 50 
#---------------------------------------------------------------------------------------------------------------------#
51 
# coel_projet_version
52 
# Insertion de la version 1 en cours du projet Inventaire des Herbiers de France
53 
INSERT INTO tb_coel.coel_projet_version (cprv_id_version, cprv_ce_projet, cprv_nom, cprv_code, cprv_date_debut, cprv_date_fin, cprv_ce_meta)
601 jp_milcent 54 
	VALUES (1, 2, 'IHF - version 1.00', 'IHF-v1.00', '2002-03-07 00:00:00', NULL, @idm := @idm+1);
95 jpm 55 
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
601 jp_milcent 56 
	VALUES	(@idm_tmp := @idm_tmp+1, '117', '1', NULL , NOW( ) , 'Ajout de la version 1 du projet IHF', '1', '1', '162.38.234.1');
95 jpm 57 
# Insertion de la version 1 du projet Utilisateur de l''application COEL
58 
INSERT INTO tb_coel.coel_projet_version (cprv_id_version, cprv_ce_projet, cprv_nom, cprv_code, cprv_date_debut, cprv_date_fin, cprv_ce_meta)
601 jp_milcent 59 
	VALUES (2, 3, 'COEL-U - version 1.00', 'COEL-U-v1.00', '2002-03-07 00:00:00', NULL, @idm := @idm+1);
95 jpm 60 
INSERT INTO tb_coel.coel_meta_historique_ligne (cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
601 jp_milcent 61 
	VALUES	(@idm_tmp := @idm_tmp+1, '117', '2', NULL , NOW( ) , 'Ajout de la version 1 du projet COEL-U', '1', '1', '162.38.234.1');
95 jpm 62 
 
54 jpm 63 
#------------------------------------------------------------------------------------------------------------------------
64 
# HERBIERS_ORGANISATION vers coel_structure
65 
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION  ORDER BY ID_ORG;# Classement des organisations par ID croissant
56 jpm 66 
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION  DROP NUM_COLLECTION,  DROP ADRESS_TEXT,  DROP TIME_ZONE ;# Suppression des champs inutiles
601 jp_milcent 67 
 
54 jpm 68 
INSERT INTO tb_coel.coel_structure
93 jpm 69 
	(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_guid, cs_truk_identifiant_alternatif,
583 jp_milcent 70 
	cs_nom, cs_adresse_01, cs_code_postal, cs_ville,
71 
	cs_ce_truk_region,
72 
	cs_ce_truk_pays,
73 
	cs_truk_telephone,
74 
	cs_courriel,
56 jpm 75 
	cs_description,
76 
	cs_truk_url,
77 
	cs_condition_acces,
78 
	cs_ce_meta)
864 jpm 79 
	SELECT ID_ORG, '2', '0', CONCAT("urn:lsid:tela-botanica.org:ihf:str",ID_ORG) AS GUID, IF(INDEX_HERB != '', CONCAT('IH##',INDEX_HERB), NULL) AS IDENTIFIANT_ALTERNATIF,
583 jp_milcent 80 
		REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8),
81 
		IF(REGION != '', CONCAT('AUTRE##',CONVERT(REGION USING utf8)), NULL),
82 
		IF(COUNTRY_CODE = 'fr', '2654', CONCAT('AUTRE##', CONVERT(COUNTRY_CODE USING utf8))) AS PAYS,
83 
		CONCAT(IF(TEL != '', CONCAT('(+33)', REPLACE(TEL,'[33]','')), NULL), IF(FAX != '', CONCAT(IF(TEL != '', ';;', ''), 'FAX##(+33)', REPLACE(FAX,'[33]','')), NULL)),
84 
		EMAIL,
85 
		IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION,
86 
		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,
93 jpm 87 
		CONVERT(ACCESS_RESTRICTION USING utf8),
56 jpm 88 
		@idm := @idm+1
89 
	FROM tb_herbiers.HERBIERS_ORGANISATION
90 
	ORDER BY ID_ORG ASC;
91 
INSERT INTO tb_coel.coel_meta_historique_ligne
92 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
93 
	cmhl_notes,
94 
	cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
601 jp_milcent 95 
	SELECT @idm_tmp := @idm_tmp+1, '120', ID_ORG, NULL, IF(DATE_DERNIERE_MODIF = '0000-00-00 00:00:00', NOW(), DATE_DERNIERE_MODIF),
56 jpm 96 
		CONCAT('Importation de l''enregistrement #',ID_ORG,' de la table HERBIERS_ORGANISATION.'),
78 jpm 97 
		IF(CE_MODIFIER_PAR = '0', '1', CE_MODIFIER_PAR), '1', '162.38.234.1'
56 jpm 98 
	FROM tb_herbiers.HERBIERS_ORGANISATION
68 jpm 99 
	ORDER BY ID_ORG ASC;
78 jpm 100 
 
583 jp_milcent 101 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '-', '') WHERE cs_truk_telephone LIKE '%-%';
102 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '.', '') WHERE cs_truk_telephone LIKE '%.%';
103 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ' ', '') WHERE cs_truk_telephone LIKE '% %';
104 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '2;04', '2;;FIX##(+33)04') WHERE cs_truk_telephone LIKE '%2;04%';
105 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '1;04', '1;;FIX##(+33)04') WHERE cs_truk_telephone LIKE '%1;04%';
106 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ',', ';; (+33)') WHERE cs_truk_telephone LIKE '%,%';
107 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)689', '(+689)') WHERE cs_truk_telephone LIKE '%(+33)689%';# Polynésie
108 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(687)', '(+687)') WHERE cs_truk_telephone LIKE '%(+33)(687)%';# Nouvelle-Calédonie
109 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)594', '(+594)0594') WHERE cs_truk_telephone LIKE '%(+33)594%';# Guyanne
110 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(590)(0)590', '(+590)0590') WHERE cs_truk_telephone LIKE '%(+33)(590)(0)590%';# Guadeloupe
111 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(0)', '(+33)0') WHERE cs_truk_telephone LIKE '%(+33)(0)%';
112 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ';;0', ';; (+33)0') WHERE cs_truk_telephone LIKE '%;;0%';
113 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = IF( cs_truk_telephone LIKE '(+33)06%', CONCAT('GSM##', cs_truk_telephone), CONCAT('FIX##', cs_truk_telephone)) WHERE cs_truk_telephone LIKE '(+33)__%';
114 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = IF(cs_truk_telephone LIKE '%;; (+33)06%', REPLACE(cs_truk_telephone, ';; (+33)', ';;GSM##(+33)'), REPLACE(cs_truk_telephone, ';; (+33)', ';;FIX##(+33)')) WHERE cs_truk_telephone LIKE '%;;%';
115 
 
68 jpm 116 
#------------------------------------------------------------------------------------------------------------------------
117 
#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation
118 
INSERT INTO tb_coel.coel_personne_a_relation
119 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
78 jpm 120 
	SELECT EDP_ID_UTILISATEUR, EDP_ID_UTILISATEUR, '2', IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', 2023, IF(EDP_ID_DROIT = '3', '2024', '0'))), @idm := @idm+1
68 jpm 121 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER
122 
	ORDER BY EDP_ID_UTILISATEUR ASC;
123 
INSERT INTO tb_coel.coel_meta_historique_ligne
124 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
125 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
92 jpm 126 
	SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(EDP_ID_UTILISATEUR, '-', EDP_ID_UTILISATEUR, '-2-', IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0)))), NULL, NOW(),
127 
	CONCAT('Importation des droits l''utilisateur #',EDP_ID_UTILISATEUR,' de la table EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'
68 jpm 128 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER
129 
	ORDER BY EDP_ID_UTILISATEUR ASC;
130 
 
131 
#------------------------------------------------------------------------------------------------------------------------
75 jpm 132 
# HERBIERS_COORDONNE vers coel_personne_a_relation
68 jpm 133 
INSERT INTO tb_coel.coel_personne_a_relation
134 
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
78 jpm 135 
	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, '2', '2025', @idm := @idm+1
75 jpm 136 
	FROM tb_herbiers.HERBIERS_COORDONNE
68 jpm 137 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
138 
INSERT INTO tb_coel.coel_meta_historique_ligne
139 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
140 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
78 jpm 141 
	SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL,
68 jpm 142 
		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'
75 jpm 143 
	FROM tb_herbiers.HERBIERS_COORDONNE
144 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
68 jpm 145 
 
146 
#------------------------------------------------------------------------------------------------------------------------
83 jpm 147 
# EFLORE_DROIT_POSSEDER et annuaire_tela vers coel_personne
73 jpm 148 
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
68 jpm 149 
INSERT INTO tb_coel.coel_personne
150 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
583 jp_milcent 151 
	 cp_adresse_01, cp_adresse_02, cp_ce_truk_region, cp_code_postal, cp_ville, cp_ce_truk_pays,
68 jpm 152 
	 cp_truk_courriel, cp_truk_url,
153 
	 cp_description,
154 
	 cp_ce_truk_role,
73 jpm 155 
	 cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
68 jpm 156 
	 cp_ce_meta)
94 jpm 157 
	SELECT U_ID, '3', CONCAT(CONVERT(U_SURNAME USING utf8),' ',UPPER(CONVERT(U_NAME USING utf8))) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), UPPER(CONVERT(U_NAME USING utf8)),
583 jp_milcent 158 
		   CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONCAT('AUTRE##', CONVERT(U_STATE USING utf8)), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONCAT('AUTRE##', CONVERT(CP.CP_Intitule_pays USING utf8)),
73 jpm 159 
		   LOWER(U_MAIL), U_WEB,
94 jpm 160 
		   CONCAT('Spécialités systématiques : ', CONVERT(U_SPE USING utf8), '\nSpécialités géographiques : ', CONVERT(U_GEO USING utf8), '\nFonction : ', CONVERT(U_FONCTION USING utf8), '\nStructure : ', CONVERT(U_TITLE USING utf8)),
78 jpm 161 
		   IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),
73 jpm 162 
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
68 jpm 163 
		   @idm := @idm+1
83 jpm 164 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
165 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)
166 
		LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)
68 jpm 167 
	ORDER BY U_ID ASC;
168 
INSERT INTO tb_coel.coel_meta_historique_ligne
169 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
170 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
78 jpm 171 
	SELECT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
92 jpm 172 
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'
83 jpm 173 
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
174 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)
68 jpm 175 
	ORDER BY U_ID ASC;
176 
 
83 jpm 177 
#------------------------------------------------------------------------------------------------------------------------
178 
# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne
179 
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
92 jpm 180 
INSERT INTO tb_coel.coel_personne
83 jpm 181 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
583 jp_milcent 182 
	 cp_adresse_01, cp_adresse_02, cp_ce_truk_region, cp_code_postal, cp_ville, cp_ce_truk_pays,
83 jpm 183 
	 cp_truk_courriel, cp_truk_url,
184 
	 cp_description,
185 
	 cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,
186 
	 cp_ce_meta)
94 jpm 187 
	SELECT DISTINCT U_ID, '3', CONCAT(CONVERT(U_SURNAME USING utf8),' ',UPPER(CONVERT(U_NAME USING utf8))) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), UPPER(CONVERT(U_NAME USING utf8)),
583 jp_milcent 188 
		   CONVERT(U_ADDR1 USING utf8), CONVERT(U_ADDR2 USING utf8), CONCAT('AUTRE##', CONVERT(U_STATE USING utf8)), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONCAT('AUTRE##', CONVERT(CP.CP_Intitule_pays USING utf8)),
83 jpm 189 
		   LOWER(U_MAIL), U_WEB,
94 jpm 190 
		   CONCAT('Spécialités systématiques : ', CONVERT(U_SPE USING utf8), '\nSpécialités géographiques : ', CONVERT(U_GEO USING utf8), '\nFonction : ', CONVERT(U_FONCTION USING utf8), '\nStructure : ', CONVERT(U_TITLE USING utf8)),
83 jpm 191 
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),
192 
		   @idm := @idm+1
193 
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA
194 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)
195 
		LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)
92 jpm 196 
	WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
83 jpm 197 
	ORDER BY U_ID ASC;
198 
INSERT INTO tb_coel.coel_meta_historique_ligne
199 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
200 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
201 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
92 jpm 202 
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à HERBIERS_ADMINISTRER.'), '1', '1', '162.38.234.1'
203 
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA
204 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)
205 
	WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
83 jpm 206 
	ORDER BY U_ID ASC;
207 
 
68 jpm 208 
#------------------------------------------------------------------------------------------------------------------------
75 jpm 209 
# HERBIERS_ADMINISTRER vers coel_structure_a_personne
68 jpm 210 
INSERT INTO tb_coel.coel_structure_a_personne
211 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
212 
	SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
75 jpm 213 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
68 jpm 214 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
215 
INSERT INTO tb_coel.coel_meta_historique_ligne
216 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
217 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
601 jp_milcent 218 
	SELECT @idm_tmp := @idm_tmp+1, '121', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL,
83 jpm 219 
		NOW(), CONCAT('Importation de l''administrateur #',HA_ID_ANNUAIRE,' de la structure #', HA_ID_ORG,' de la table HERBIER_ADMINISTRER.'), '1', '1', '162.38.234.1'
75 jpm 220 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
68 jpm 221 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
73 jpm 222 
 
223 
 
224 
#------------------------------------------------------------------------------------------------------------------------
225 
#HERBIERS_STAFF vers coel_personne
226 
INSERT INTO tb_coel.coel_personne
227 
	(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,
583 jp_milcent 228 
	cp_truk_telephone,
229 
	cp_ce_meta)
94 jpm 230 
	SELECT 100000+ID_STAFF, '2', CONCAT(CONVERT(PRENOM USING utf8),' ', UPPER(CONVERT(NOM USING utf8))) AS NOM_COMPLET, UPPER(CONVERT(NOM USING utf8)), CONVERT(PRENOM USING utf8), CONVERT(ADRESSE1 USING utf8), CONVERT(ADRESSE2 USING utf8), CP, CONVERT(VILLE USING utf8), LOWER(MAIL),
583 jp_milcent 231 
		   CONCAT(IF(TEL != '', CONCAT('(+33)', REPLACE(TEL,'[33]','')), NULL), IF(FAX != '', CONCAT(IF(TEL != '', ';;', ''), 'FAX##(+33)', REPLACE(FAX,'[33]','')), NULL)),
232 
		   @idm := @idm+1
73 jpm 233 
	FROM tb_herbiers.HERBIERS_STAFF
234 
	ORDER BY ID_STAFF ASC;
75 jpm 235 
INSERT INTO tb_coel.coel_meta_historique_ligne
73 jpm 236 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
237 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
81 jpm 238 
	SELECT @idm_tmp := @idm_tmp+1, '113', 100000+ID_STAFF, NULL, IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()),
92 jpm 239 
		CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
73 jpm 240 
	FROM tb_herbiers.HERBIERS_STAFF
241 
	ORDER BY ID_STAFF ASC;
68 jpm 242 
 
583 jp_milcent 243 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
244 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '-', '') WHERE cp_truk_telephone LIKE '%-%';
75 jpm 245 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
583 jp_milcent 246 
# Gérer les séparateurs ; au cas par cas
247 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '1;04', '1;;FIX##(+33)04') WHERE cp_truk_telephone LIKE '%1;04%';
248 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ',', ';; (+33)') WHERE cp_truk_telephone LIKE '%,%';
249 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)+33', '(+33)') WHERE cp_truk_telephone LIKE '%(+33)+33%';
250 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)689', '(+689)') WHERE cp_truk_telephone LIKE '%(+33)689%';# Polynésie
251 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(687)', '(+687)') WHERE cp_truk_telephone LIKE '%(+33)(687)%';# Nouvelle-Calédonie
252 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)594', '(+594)0594') WHERE cp_truk_telephone LIKE '%(+33)594%';# Guyanne
253 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(590)(0)590', '(+590)0590') WHERE cp_truk_telephone LIKE '%(+33)(590)(0)590%';# Guadeloupe
254 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(0)', '(+33)0') WHERE cp_truk_telephone LIKE '%(+33)(0)%';
255 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ';;0', ';; (+33)0') WHERE cp_truk_telephone LIKE '%;;0%';
256 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF( cp_truk_telephone LIKE '(+33)06%', CONCAT('GSM##', cp_truk_telephone), CONCAT('FIX##', cp_truk_telephone)) WHERE cp_truk_telephone LIKE '(+33)__%';
257 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;; (+33)06%', REPLACE(cp_truk_telephone, ';; (+33)', ';;GSM##(+33)'), REPLACE(cp_truk_telephone, ';; (+33)', ';;FIX##(+33)')) WHERE cp_truk_telephone LIKE '%;;%';
75 jpm 258 
 
83 jpm 259 
ALTER TABLE tb_coel.coel_personne  ORDER BY cp_id_personne;
75 jpm 260 
 
68 jpm 261 
#------------------------------------------------------------------------------------------------------------------------
73 jpm 262 
# HERBIERS_ont_un_staff vers coel_structure_a_personne
589 jp_milcent 263 
# ATTENTION : Nous ignorons les liaisons qui n ont pas de correspondance dans la table HERBIER_STAFF
73 jpm 264 
INSERT INTO tb_coel.coel_structure_a_personne
265 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
162 jp_milcent 266 
	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
589 jp_milcent 267 
	FROM tb_herbiers.HERBIERS_STAFF AS hs LEFT JOIN tb_herbiers.HERBIERS_ont_un_staff AS hous ON (hous.ID_STAFF = hs.ID_STAFF)
73 jpm 268 
	WHERE hous.ID_ORG != 0
269 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
270 
INSERT INTO tb_coel.coel_meta_historique_ligne
271 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
272 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
601 jp_milcent 273 
	SELECT @idm_tmp := @idm_tmp+1, '121', CONCAT(ID_ORG, '-', 100000+hous.ID_STAFF, '-2027'), NULL,
589 jp_milcent 274 
		NOW(), CONCAT('Importation de la personne #',100000+hous.ID_STAFF,' de la structure #', ID_ORG,' de la table HERBIERS_ont_un_staff.'), '1', '1', '162.38.234.1'
275 
	FROM tb_herbiers.HERBIERS_STAFF AS hs LEFT JOIN tb_herbiers.HERBIERS_ont_un_staff AS hous ON (hous.ID_STAFF = hs.ID_STAFF)
276 
	WHERE hous.ID_ORG != 0
277 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
73 jpm 278 
 
75 jpm 279 
UPDATE tb_coel.coel_structure_a_personne
280 
SET csap_ce_truk_fonction = IF(csap_ce_truk_fonction IS NOT NULL, IF(csap_ce_truk_fonction LIKE '%irecteur%', '2028',
281 
	IF(csap_ce_truk_fonction LIKE '%onservateur%', '2029', IF(csap_ce_truk_fonction LIKE '%echnicien%', '2030', csap_ce_truk_fonction))), NULL)
282 
WHERE csap_id_role = '2027'	;
283 
 
73 jpm 284 
#------------------------------------------------------------------------------------------------------------------------
68 jpm 285 
#HERBIER_COLLECTION vers coel_collection
75 jpm 286 
INSERT INTO tb_coel.coel_collection
97 jpm 287 
	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description,
139 jpm 288 
	cc_ce_type_depot,
97 jpm 289 
	cc_description_specialiste, cc_truk_url,
866 jpm 290 
	cc_ce_type,
873 jpm 291 
	cc_ce_specimen_type,
97 jpm 292 
	cc_truk_preservation,
866 jpm 293 
	cc_ce_meta)
864 jpm 294 
	SELECT hc.ID, '2', PARENT_ID, CONCAT("urn:lsid:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8),
97 jpm 295 
		IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))),
296 
		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,
866 jpm 297 
		'2157',
873 jpm 298 
		IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
162 jp_milcent 299 
		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,
97 jpm 300 
		@idm := @idm+1
78 jpm 301 
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
92 jpm 302 
	WHERE hc.ID != 4
78 jpm 303 
	GROUP BY hc.ID
304 
	ORDER BY hc.ID ASC;
139 jpm 305 
INSERT INTO tb_coel.coel_meta_historique_ligne
306 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
307 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
308 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
309 
		IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()) , CONCAT('Ajout de la collection #',ID,' de la table HERBIER_COLLECTION.'), IF(CE_MODIFIER_PAR != '0', CE_MODIFIER_PAR, '1'), '1', IF(CE_MODIFIER_PAR != '0', NULL, '162.38.234.1')
310 
	FROM tb_herbiers.HERBIERS_COLLECTION
311 
	WHERE ID != 4
312 
	ORDER BY ID ASC;
313 
#------------------------------------------------------------------------------------------------------------------------
314 
#HERBIER_COLLECTION vers coel_collection_botanique
75 jpm 315 
INSERT INTO tb_coel.coel_collection_botanique
316 
	(ccb_id_collection, ccb_truk_unite_base,
317 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
318 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
319 
	ccb_truk_degradation_specimen,
320 
	ccb_truk_degradation_presentation,
139 jpm 321 
	ccb_annotation_classement,
322 
	ccb_ce_meta)
962 jpm 323 
	SELECT ID, CONCAT('2270##0||||||',NUM_SPECIMENS,'||', IF(PREC_SPECIMENS = '1', 'E', IF(PREC_SPECIMENS = '2', 'A', IF(PREC_SPECIMENS = '3', 'A', 'NULL'))), '||', NUM_SPECIES, '||', IF(PREC_SPECIES = '1', 'E', IF(PREC_SPECIES = '2', 'A', IF(PREC_SPECIES = '3', 'A', 'NULL')))),
78 jpm 324 
		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))))),
325 
		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)))))),
326 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
873 jpm 327 
		CONCAT('Diverses##',IF(ETAT_SPECIMENS = 1, '30748', IF(ETAT_SPECIMENS = 2, '30749', IF(ETAT_SPECIMENS = 3, '30751', IF(ETAT_SPECIMENS = 4, '30752', NULL))))),
328 
		CONCAT('Diverses##',IF(ETAT_PRESENTATION = 1, '30748', IF(ETAT_PRESENTATION = 2, '30749', IF(ETAT_PRESENTATION = 3, '30751', IF(ETAT_PRESENTATION = 4, '30752', NULL))))),
139 jpm 329 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL),
330 
		@idm := @idm+1
92 jpm 331 
	FROM tb_herbiers.HERBIERS_COLLECTION
332 
	WHERE ID != 4
75 jpm 333 
	ORDER BY ID ASC;
334 
INSERT INTO tb_coel.coel_meta_historique_ligne
335 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
336 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
139 jpm 337 
	SELECT @idm_tmp := @idm_tmp+1, '106', ID, NULL,
78 jpm 338 
		IF(DATE_DERNIERE_MODIF != '0000-00-00 00:00:00', DATE_DERNIERE_MODIF, NOW()) , CONCAT('Ajout de la collection #',ID,' de la table HERBIER_COLLECTION.'), IF(CE_MODIFIER_PAR != '0', CE_MODIFIER_PAR, '1'), '1', IF(CE_MODIFIER_PAR != '0', NULL, '162.38.234.1')
339 
	FROM tb_herbiers.HERBIERS_COLLECTION
92 jpm 340 
	WHERE ID != 4
78 jpm 341 
	ORDER BY ID ASC;
342 
 
75 jpm 343 
#------------------------------------------------------------------------------------------------------------------------
78 jpm 344 
#HERBIERS_INDIC vers coel_commentaires
345 
INSERT INTO tb_coel.coel_commentaire
346 
	(ccm_id_commentaire, ccm_ce_projet, ccm_titre,
347 
	ccm_texte, ccm_ponderation, ccm_ce_meta)
94 jpm 348 
	SELECT ID_INDIC, '2', IF(REM_INDIC IS NOT NULL, IF(REM_INDIC != '', CONVERT(REM_INDIC USING utf8), CONCAT(SUBSTRING(REPLACE(REPLACE(CONVERT(TXT_INDIC USING utf8),'<i>',''),'</i>',''), 1, 50),'...')),  CONCAT(SUBSTRING(REPLACE(REPLACE(CONVERT(TXT_INDIC USING utf8),'<i>',''),'</i>',''), 1, 50),'...')) AS TITRE,
349 
		CONVERT(TXT_INDIC USING utf8), 0, @idm := @idm+1
78 jpm 350 
	FROM tb_herbiers.HERBIERS_INDIC
351 
	ORDER BY ID_INDIC ASC;
352 
INSERT INTO tb_coel.coel_meta_historique_ligne
353 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
354 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
355 
	SELECT @idm_tmp := @idm_tmp+1, '107', hi.ID_INDIC, NULL,
589 jp_milcent 356 
		FROM_UNIXTIME(MAX(MAJ_INDIC)) , CONCAT('Ajout du commentaire #',hi.ID_INDIC,' de la table HERBIER_INDIC.'), IF(ID_INDICATEUR != '0', ID_INDICATEUR, '1'), '1', IF(ID_INDICATEUR != '0', NULL, '162.38.234.1')
78 jpm 357 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)
589 jp_milcent 358 
	GROUP BY hi.ID_INDIC
78 jpm 359 
	ORDER BY hi.ID_INDIC ASC;
68 jpm 360 
 
78 jpm 361 
#------------------------------------------------------------------------------------------------------------------------
79 jpm 362 
# HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
78 jpm 363 
INSERT INTO tb_coel.coel_collection_a_commentaire
364 
	(ccac_id_collection, ccac_id_commentaire,
365 
	ccac_truk_type,
366 
	ccac_ce_meta)
367 
	SELECT hi.ID_INDIC, ID,
984 jpm 368 
		CAST(GROUP_CONCAT(DISTINCT CASE ID_TYPE WHEN '1' THEN '2368' WHEN '2' THEN '2369' WHEN '3' THEN '2370' WHEN '4' THEN '2371' WHEN '5' THEN '2372' WHEN '6' THEN '2373' ELSE NULL END SEPARATOR ";;") AS CHAR)  AS TYPE,
78 jpm 369 
		@idm := @idm+1
87 jpm 370 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC)
371 
	WHERE ID IN (SELECT ccm_id_commentaire FROM tb_coel.coel_commentaire)
78 jpm 372 
	GROUP BY hi.ID_INDIC
373 
	ORDER BY hi.ID_INDIC ASC;
589 jp_milcent 374 
# ATTENTION : ajoute moins de valeurs à @
78 jpm 375 
INSERT INTO tb_coel.coel_meta_historique_ligne
376 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
377 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
589 jp_milcent 378 
	SELECT @idm_tmp := @idm_tmp+1, '102', CONCAT(hi.ID_INDIC,'-',ID), NULL,
379 
		FROM_UNIXTIME(MAX(MAJ_INDIC)) , CONCAT('Ajout des types du commentaire #',hi.ID_INDIC,' de la table HERBIERS_A_UN_TYPE.'), IF(ID_INDICATEUR != '0', ID_INDICATEUR, '1'), '1', IF(ID_INDICATEUR != '0', NULL, '162.38.234.1')
380 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC) LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)
87 jpm 381 
	WHERE ID IN (SELECT ccm_id_commentaire FROM tb_coel.coel_commentaire)
78 jpm 382 
	GROUP BY hi.ID_INDIC
383 
	ORDER BY hi.ID_INDIC ASC;
384 
 
589 jp_milcent 385 
# Mise à jour des id de personne inexistant dans l''annuaire Tela dans la table : coel_meta_historique_ligne
386 
# Récupération manuelle...
387 
INSERT INTO tb_coel.coel_personne
388 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom, cp_description, cp_ce_annuaire_tela, cp_ce_meta)
389 
VALUES 	(19, 3, 'Inconnu #19', 'Inconnu', 'INCONNU', 'Individu non présent dans l''annuaire Tela Botanica.', 19, @idm := @idm+1),
390 
		(920, 3, 'Inconnu #920', 'Inconnu', 'INCONNU', 'Individu non présent dans l''annuaire Tela Botanica.', 920, @idm := @idm+1);
391 
INSERT INTO tb_coel.coel_meta_historique_ligne
83 jpm 392 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
393 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
589 jp_milcent 394 
VALUES 	(@idm_tmp := @idm_tmp+1, '113', 19, NULL, NOW(), 'Création de l''utilisateur inconnu #19 non présent la table annuaire_tela.', '1', '1', '162.38.234.1'),
395 
		(@idm_tmp := @idm_tmp+1, '113', 920, NULL, NOW(), 'Création de l''utilisateur inconnu #920 non présent la table annuaire_tela.', '1', '1', '162.38.234.1');