Subversion Repositories eFlore/Applications.coel

Rev

Rev 94 | Rev 97 | 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 
 
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
95 jpm 34 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_autorisation, cpr_ce_meta)
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
95 jpm 40 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_autorisation, cpr_ce_meta)
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,
94 jpm 71 
		CONVERT(INSTITUTION_NAME USING utf8), 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 ,
56 jpm 73 
		CONCAT(IF(HO_URL != 'http://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' 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
254 
	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, cc_truk_type_depot, cc_ce_specimen_type,
78 jpm 255 
	 cc_description_specialiste, cc_truk_url,
75 jpm 256 
	 cc_specimen_type_nbre,
78 jpm 257 
	 cc_truk_preservation,
75 jpm 258 
    cc_ce_meta)
93 jpm 259 
	SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8), CONVERT(STATUT USING utf8), IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
94 jpm 260 
	   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,
75 jpm 261 
	   NUM_SPECIMENS,
78 jpm 262 
	   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,
75 jpm 263 
	   @idm := @idm+1
78 jpm 264 
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
92 jpm 265 
	WHERE hc.ID != 4
78 jpm 266 
	GROUP BY hc.ID
267 
	ORDER BY hc.ID ASC;
75 jpm 268 
INSERT INTO tb_coel.coel_collection_botanique
269 
	(ccb_id_collection, ccb_truk_unite_base,
270 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
271 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
272 
	ccb_truk_degradation_specimen,
273 
	ccb_truk_degradation_presentation,
274 
	ccb_annotation_classement)
275 
	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 276 
		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))))),
277 
		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)))))),
278 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
279 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
280 
		CONCAT('TOTAL#',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
93 jpm 281 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL)
92 jpm 282 
	FROM tb_herbiers.HERBIERS_COLLECTION
283 
	WHERE ID != 4
75 jpm 284 
	ORDER BY ID ASC;
285 
INSERT INTO tb_coel.coel_meta_historique_ligne
286 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
287 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
288 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
78 jpm 289 
		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')
290 
	FROM tb_herbiers.HERBIERS_COLLECTION
92 jpm 291 
	WHERE ID != 4
78 jpm 292 
	ORDER BY ID ASC;
293 
 
75 jpm 294 
#------------------------------------------------------------------------------------------------------------------------
78 jpm 295 
#HERBIERS_INDIC vers coel_commentaires
296 
INSERT INTO tb_coel.coel_commentaire
297 
	(ccm_id_commentaire, ccm_ce_projet, ccm_titre,
298 
	ccm_texte, ccm_ponderation, ccm_ce_meta)
94 jpm 299 
	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,
300 
		CONVERT(TXT_INDIC USING utf8), 0, @idm := @idm+1
78 jpm 301 
	FROM tb_herbiers.HERBIERS_INDIC
302 
	ORDER BY ID_INDIC ASC;
303 
INSERT INTO tb_coel.coel_meta_historique_ligne
304 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
305 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
306 
	SELECT @idm_tmp := @idm_tmp+1, '107', hi.ID_INDIC, NULL,
307 
		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')
308 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)
309 
	ORDER BY hi.ID_INDIC ASC;
68 jpm 310 
 
78 jpm 311 
#------------------------------------------------------------------------------------------------------------------------
79 jpm 312 
# HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
78 jpm 313 
INSERT INTO tb_coel.coel_collection_a_commentaire
314 
	(ccac_id_collection, ccac_id_commentaire,
315 
	ccac_truk_type,
316 
	ccac_ce_meta)
317 
	SELECT hi.ID_INDIC, ID,
318 
		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,
319 
		@idm := @idm+1
87 jpm 320 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC)
321 
	WHERE ID IN (SELECT ccm_id_commentaire FROM tb_coel.coel_commentaire)
78 jpm 322 
	GROUP BY hi.ID_INDIC
323 
	ORDER BY hi.ID_INDIC ASC;
324 
INSERT INTO tb_coel.coel_meta_historique_ligne
325 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
326 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
327 
	SELECT @idm_tmp := @idm_tmp+1, '102', hi.ID_INDIC, NULL,
328 
		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 329 
	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)
330 
	WHERE ID IN (SELECT ccm_id_commentaire FROM tb_coel.coel_commentaire)
78 jpm 331 
	GROUP BY hi.ID_INDIC
332 
	ORDER BY hi.ID_INDIC ASC;
333 
 
79 jpm 334 
#------------------------------------------------------------------------------------------------------------------------
83 jpm 335 
# Mise à jour des id de personne inexistant dans l''annuaire Tela
336 
# dans la table : coel_meta_historique_ligne
337 
INSERT IGNORE INTO tb_coel.coel_personne
338 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
339 
	 cp_description, cp_ce_annuaire_tela,
340 
	 cp_ce_meta)
341 
	SELECT DISTINCT cmhl_ce_modifier_par, '3', CONCAT('Inconnu #', cmhl_ce_modifier_par) AS NOM_COMPLET, 'Inconnu', 'INCONNU',
342 
		   'Individu non présent dans l''annuaire Tela Botanica.', cmhl_ce_modifier_par,
343 
		   @idm := @idm+1
344 
	FROM tb_coel.coel_meta_historique_ligne
345 
	WHERE cmhl_ce_modifier_par NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
346 
	ORDER BY cmhl_ce_modifier_par ASC;
347 
INSERT IGNORE INTO tb_coel.coel_meta_historique_ligne
348 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
349 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
350 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', cmhl_ce_modifier_par, NULL, NOW(),
351 
		CONCAT('Création de l''utilisateur inconnu #', cmhl_ce_modifier_par,' non présent la table annuaire_tela.'), '1', '1', '162.38.234.1'
352 
	FROM tb_coel.coel_meta_historique_ligne
353 
	WHERE cmhl_ce_modifier_par NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
354 
	ORDER BY cmhl_ce_modifier_par ASC;
355 
# dans la table : coel_structure_a_personne
356 
INSERT IGNORE INTO tb_coel.coel_personne
357 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
358 
	 cp_description, cp_ce_annuaire_tela,
359 
	 cp_ce_meta)
360 
	SELECT DISTINCT csap_id_personne, '3', CONCAT('Inconnu #', csap_id_personne) AS NOM_COMPLET, 'Inconnu', 'INCONNU',
361 
		   'Individu non présent dans l''annuaire Tela Botanica.', csap_id_personne,
362 
		   @idm := @idm+1
363 
	FROM tb_coel.coel_structure_a_personne
364 
	WHERE csap_id_personne NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
365 
	ORDER BY csap_id_personne ASC;
366 
INSERT IGNORE INTO tb_coel.coel_meta_historique_ligne
367 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
368 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
369 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', csap_id_personne, NULL, NOW(),
370 
		CONCAT('Création de l''utilisateur inconnu #', csap_id_personne,' non présent la table annuaire_tela.'), '1', '1', '162.38.234.1'
371 
	FROM tb_coel.coel_structure_a_personne
372 
	WHERE csap_id_personne NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
373 
	ORDER BY csap_id_personne ASC;
374 
# dans la table : coel_personne_a_relation
375 
INSERT IGNORE INTO tb_coel.coel_personne
376 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
377 
	 cp_description, cp_ce_annuaire_tela,
378 
	 cp_ce_meta)
379 
	SELECT DISTINCT cpar_id_personne_01, '3', CONCAT('Inconnu #', cpar_id_personne_01) AS NOM_COMPLET, 'Inconnu', 'INCONNU',
380 
		   'Individu non présent dans l''annuaire Tela Botanica.', cpar_id_personne_01,
381 
		   @idm := @idm+1
382 
	FROM tb_coel.coel_personne_a_relation
383 
	WHERE cpar_id_personne_01 NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
384 
	ORDER BY cpar_id_personne_01 ASC;
385 
INSERT IGNORE INTO tb_coel.coel_meta_historique_ligne
386 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
387 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
388 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', cpar_id_personne_01, NULL, NOW(),
389 
		CONCAT('Création de l''utilisateur inconnu #', cpar_id_personne_01,' non présent la table annuaire_tela.'), '1', '1', '162.38.234.1'
390 
	FROM tb_coel.coel_personne_a_relation
391 
	WHERE cpar_id_personne_01 NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
392 
	ORDER BY cpar_id_personne_01 ASC;
393 
INSERT IGNORE INTO tb_coel.coel_personne
394 
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom,
395 
	 cp_description, cp_ce_annuaire_tela,
396 
	 cp_ce_meta)
397 
	SELECT DISTINCT cpar_id_personne_02, '3', CONCAT('Inconnu #', cpar_id_personne_02) AS NOM_COMPLET, 'Inconnu', 'INCONNU',
398 
		   'Individu non présent dans l''annuaire Tela Botanica.', cpar_id_personne_02,
399 
		   @idm := @idm+1
400 
	FROM tb_coel.coel_personne_a_relation
401 
	WHERE cpar_id_personne_02 NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
402 
	ORDER BY cpar_id_personne_02 ASC;
403 
INSERT IGNORE INTO tb_coel.coel_meta_historique_ligne
404 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, cmhl_date_modification,
405 
	cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
406 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', cpar_id_personne_02, NULL, NOW(),
407 
		CONCAT('Création de l''utilisateur inconnu #', cpar_id_personne_02,' non présent la table annuaire_tela.'), '1', '1', '162.38.234.1'
408 
	FROM tb_coel.coel_personne_a_relation
409 
	WHERE cpar_id_personne_02 NOT IN (SELECT U_ID FROM tb_v4.annuaire_tela)
410 
	ORDER BY cpar_id_personne_02 ASC;