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)
162 jp_milcent 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 ,
162 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)%';
162 jp_milcent 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));
75 jpm 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);
162 jp_milcent 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 '%;;%';
75 jpm 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)
162 jp_milcent 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,
162 jp_milcent 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,
97 jpm 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)
162 jp_milcent 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)))),
162 jp_milcent 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;