Subversion Repositories eFlore/Applications.coel

Rev

Rev 583 | Rev 589 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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