Subversion Repositories eFlore/Applications.coel

Rev

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