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