Subversion Repositories eFlore/Applications.coel

Rev

Rev 85 | Rev 92 | 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;