Subversion Repositories eFlore/Applications.coel

Rev

Rev 75 | Rev 79 | 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 
#------------------------------------------------------------------------------------------------------------------------
106 
#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)
73 jpm 116 
	SELECT U_ID, '3', CONCAT(U_SURNAME,'',U_NAME) AS NOM_COMPLET, U_SURNAME, 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
78 jpm 123 
	FROM tb_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP, tb_v4.carto_PAYS AS CP
68 jpm 124 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
125 
		AND CP.CP_ID_Pays = AT.U_COUNTRY
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'
78 jpm 132 
	FROM tb_v4.annuaire_tela AS AT, tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP
68 jpm 133 
	WHERE AT.U_ID = EDP.EDP_ID_UTILISATEUR
134 
	ORDER BY U_ID ASC;
135 
 
136 
#------------------------------------------------------------------------------------------------------------------------
75 jpm 137 
# HERBIERS_ADMINISTRER vers coel_structure_a_personne
68 jpm 138 
INSERT INTO tb_coel.coel_structure_a_personne
139 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
140 
	SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
75 jpm 141 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
68 jpm 142 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
143 
INSERT INTO tb_coel.coel_meta_historique_ligne
144 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
145 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
78 jpm 146 
	SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL,
68 jpm 147 
		NOW(), CONCAT('Importation des 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 148 
	FROM tb_herbiers.HERBIERS_ADMINISTRER
68 jpm 149 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
73 jpm 150 
 
151 
 
152 
#------------------------------------------------------------------------------------------------------------------------
153 
#HERBIERS_STAFF vers coel_personne
154 
INSERT INTO tb_coel.coel_personne
155 
	(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,
156 
	cp_truk_telephone, cp_truk_fax, cp_ce_meta)
157 
	SELECT 100000+ID_STAFF, '2', CONCAT(PRENOM,'', NOM) AS NOM_COMPLET, NOM, PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL),
75 jpm 158 
		   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 159 
	FROM tb_herbiers.HERBIERS_STAFF
160 
	ORDER BY ID_STAFF ASC;
75 jpm 161 
INSERT INTO tb_coel.coel_meta_historique_ligne
73 jpm 162 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
163 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
78 jpm 164 
	SELECT @idm_tmp := @idm_tmp+1, '113', ID_STAFF, NULL, DATE_DERNIERE_MODIF,
165 
		CONCAT('Importation de l''utilisateur #', ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
73 jpm 166 
	FROM tb_herbiers.HERBIERS_STAFF
167 
	ORDER BY ID_STAFF ASC;
68 jpm 168 
 
75 jpm 169 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
170 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
171 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%';
172 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', '; +33'),cp_truk_telephone);
173 
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));
174 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone);
175 
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 '%;%';
176 
 
177 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%';
178 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%';
179 
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, ' ', '') WHERE cp_truk_fax LIKE '% %';
180 
UPDATE tb_coel.coel_personne SET cp_truk_fax = IF(cp_truk_fax LIKE '%+330%', REPLACE(cp_truk_fax, '+330', '+33'),cp_truk_fax);
181 
 
182 
 
68 jpm 183 
#------------------------------------------------------------------------------------------------------------------------
73 jpm 184 
# HERBIERS_ont_un_staff vers coel_structure_a_personne
185 
INSERT INTO tb_coel.coel_structure_a_personne
186 
	(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
75 jpm 187 
	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 188 
	FROM tb_herbiers.HERBIERS_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF)
189 
	WHERE hous.ID_ORG != 0
190 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
191 
INSERT INTO tb_coel.coel_meta_historique_ligne
192 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
193 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat ,cmhl_ip)
78 jpm 194 
	SELECT @idm_tmp := @idm_tmp+1, '120', CONCAT(ID_ORG, '-', ID_STAFF, '-2027'), NULL,
195 
		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 196 
	FROM tb_herbiers.HERBIERS_ont_un_staff
75 jpm 197 
	WHERE ID_ORG != 0
73 jpm 198 
	ORDER BY ID_ORG, ID_STAFF ASC;
199 
 
75 jpm 200 
UPDATE tb_coel.coel_structure_a_personne
201 
SET csap_ce_truk_fonction = IF(csap_ce_truk_fonction IS NOT NULL, IF(csap_ce_truk_fonction LIKE '%irecteur%', '2028',
202 
	IF(csap_ce_truk_fonction LIKE '%onservateur%', '2029', IF(csap_ce_truk_fonction LIKE '%echnicien%', '2030', csap_ce_truk_fonction))), NULL)
203 
WHERE csap_id_role = '2027'	;
204 
 
73 jpm 205 
 
206 
#------------------------------------------------------------------------------------------------------------------------
68 jpm 207 
#HERBIER_COLLECTION vers coel_collection
75 jpm 208 
INSERT INTO tb_coel.coel_collection
209 
	(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 210 
	 cc_description_specialiste, cc_truk_url,
75 jpm 211 
	 cc_specimen_type_nbre,
78 jpm 212 
	 cc_truk_preservation,
75 jpm 213 
    cc_ce_meta)
78 jpm 214 
	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,
215 
	   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 216 
	   NUM_SPECIMENS,
78 jpm 217 
	   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 218 
	   @idm := @idm+1
78 jpm 219 
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)
220 
	GROUP BY hc.ID
221 
	ORDER BY hc.ID ASC;
75 jpm 222 
INSERT INTO tb_coel.coel_collection_botanique
223 
	(ccb_id_collection, ccb_truk_unite_base,
224 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type,
225 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
226 
	ccb_truk_degradation_specimen,
227 
	ccb_truk_degradation_presentation,
228 
	ccb_annotation_classement)
229 
	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 230 
		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))))),
231 
		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)))))),
232 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
233 
		CONCAT('TOTAL#',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
234 
		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 235 
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', MODE_CLASSEMENT, NULL), NULL)
78 jpm 236 
	FROM tb_herbiers.HERBIERS_COLLECTION
75 jpm 237 
	ORDER BY ID ASC;
238 
INSERT INTO tb_coel.coel_meta_historique_ligne
239 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
240 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
241 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL,
78 jpm 242 
		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')
243 
	FROM tb_herbiers.HERBIERS_COLLECTION
244 
	ORDER BY ID ASC;
245 
 
75 jpm 246 
#------------------------------------------------------------------------------------------------------------------------
78 jpm 247 
#HERBIERS_INDIC vers coel_commentaires
248 
INSERT INTO tb_coel.coel_commentaire
249 
	(ccm_id_commentaire, ccm_ce_projet, ccm_titre,
250 
	ccm_texte, ccm_ponderation, ccm_ce_meta)
251 
	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,
252 
		TXT_INDIC, 0, @idm := @idm+1
253 
	FROM tb_herbiers.HERBIERS_INDIC
254 
	ORDER BY ID_INDIC ASC;
255 
INSERT INTO tb_coel.coel_meta_historique_ligne
256 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
257 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
258 
	SELECT @idm_tmp := @idm_tmp+1, '107', hi.ID_INDIC, NULL,
259 
		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')
260 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC)
261 
	ORDER BY hi.ID_INDIC ASC;
68 jpm 262 
 
78 jpm 263 
#------------------------------------------------------------------------------------------------------------------------
264 
#HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
265 
INSERT INTO tb_coel.coel_collection_a_commentaire
266 
	(ccac_id_collection, ccac_id_commentaire,
267 
	ccac_truk_type,
268 
	ccac_ce_meta)
269 
	SELECT hi.ID_INDIC, ID,
270 
		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,
271 
		@idm := @idm+1
272 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC)
273 
	GROUP BY hi.ID_INDIC
274 
	ORDER BY hi.ID_INDIC ASC;
275 
INSERT INTO tb_coel.coel_meta_historique_ligne
276 
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,
277 
	cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)
278 
	SELECT @idm_tmp := @idm_tmp+1, '102', hi.ID_INDIC, NULL,
279 
		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')
280 
	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)
281 
	GROUP BY hi.ID_INDIC
282 
	ORDER BY hi.ID_INDIC ASC;
283 
 
68 jpm 284 
 
285 
 
286 
 
287 
 
288 
 
289 
 
290 
 
291 
 
292 
 
293 
 
294 
 
295 
 
296 
 
297