Subversion Repositories eFlore/Applications.coel

Rev

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