Subversion Repositories eFlore/Applications.coel

Rev

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