WebSVN – eFlore/Applications.coel – Diff – /trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql


#------------------------------------------------------------------------------------------------------------------------
# SCRIPT de MIGRATION de HERBIERS vers COEL v1.0
#------------------------------------------------------------------------------------------------------------------------
# TODO : remplacer les valeurs vides par NULL -> utiliser le script remplacer_vide_par_null.php
 
#---------------------------------------------------------------------------------------------------------------------#
# Variables
SET @idm = 29170;# Identifiant pour les métadonnées
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire
SET @idm_max = 99999;# Identifiant pour les métadonnées maximum si déjà des données dans la base
#---------------------------------------------------------------------------------------------------------------------#
# Nettoyage des tables
# coel_projet
DELETE FROM tb_coel.coel_projet WHERE cpr_id_projet IN (2,3);
# coel_projet_version
DELETE FROM tb_coel.coel_projet_version WHERE cprv_id_version IN (1,2);
# coel_meta_historique_ligne
DELETE FROM tb_coel.coel_meta_historique_ligne WHERE cmhl_id_historique_ligne >= @idm AND cmhl_id_historique_ligne < @idm_max;
# coel_structure
DELETE FROM tb_coel.coel_structure WHERE cs_id_structure IN (SELECT ID_ORG FROM tb_herbiers.HERBIERS_ORGANISATION ORDER BY ID_ORG ASC);
# coel_structure_a_personne
DELETE FROM tb_coel.coel_structure_a_personne WHERE csap_id_role IN ('2026', '2027');
# coel_personne
DELETE FROM tb_coel.coel_personne WHERE cp_ce_projet IN ('2', '3');
# coel_personne_a_relation 
DELETE FROM tb_coel.coel_personne_a_relation WHERE cpar_id_projet = '2';
# coel_collection_botanique
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'));
# coel_collection_a_commentaire
DELETE FROM tb_coel.coel_collection_a_commentaire ;
# coel_collection
DELETE FROM tb_coel.coel_collection WHERE cc_ce_projet IN ('2');
# coel_commentaire
DELETE FROM tb_coel.coel_commentaire WHERE ccm_ce_projet IN ('2');
 
#---------------------------------------------------------------------------------------------------------------------#
# EFLORE_PROJET vers coel_projet
# Insertion du projet Inventaire des Herbiers de France - Version 1 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_mark_public, cpr_ce_meta) 
	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 
	FROM tb_herbiers.EFLORE_PROJET;
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) 
	VALUES	(@idm_tmp := @idm_tmp+1, '115', '2', NULL , NOW( ) , 'Ajout du projet IHF', '1', '1', '162.38.234.1');
# Insertion du projet Utilisateur de l''application COEL - Version 1 
INSERT INTO tb_coel.coel_projet (cpr_id_projet, cpr_nom, cpr_abreviation, cpr_description, cpr_url, cpr_mark_public, cpr_ce_meta) 
	VALUES (3, 'Utilisateurs', 'COEL-U', 'Les utilisateurs de l''application COEL.', NULL, 0, @idm := @idm+1);
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) 
	VALUES	(@idm_tmp := @idm_tmp+1, '115', '2', NULL , NOW( ) , 'Ajout du projet U', '1', '1', '162.38.234.1');
 
#---------------------------------------------------------------------------------------------------------------------#
# coel_projet_version
# Insertion de la version 1 en cours du projet Inventaire des Herbiers de France 
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) 
	VALUES (1, 2, 'IHF - version 1.00', 'IHF-v1.00', '2002-03-07 00:00:00', NULL, @idm := @idm+1);
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) 
	VALUES	(@idm_tmp := @idm_tmp+1, '117', '1', NULL , NOW( ) , 'Ajout de la version 1 du projet IHF', '1', '1', '162.38.234.1');
# Insertion de la version 1 du projet Utilisateur de l''application COEL
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) 
	VALUES (2, 3, 'COEL-U - version 1.00', 'COEL-U-v1.00', '2002-03-07 00:00:00', NULL, @idm := @idm+1);
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) 
	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');
 
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_ORGANISATION vers coel_structure
ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION  ORDER BY ID_ORG;# Classement des organisations par ID croissant
#ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION  DROP NUM_COLLECTION,  DROP ADRESS_TEXT,  DROP TIME_ZONE ;# Suppression des champs inutiles
 
 
INSERT INTO tb_coel.coel_structure  
	(cs_id_structure, cs_ce_projet, cs_ce_mere, cs_guid, cs_truk_identifiant_alternatif, 
	cs_nom, cs_adresse_01, cs_code_postal, cs_ville, 
	cs_ce_truk_region, 
	cs_ce_truk_pays, 
	cs_truk_telephone, 
	cs_courriel,
	cs_description, 
	cs_truk_url,
	cs_condition_acces,  
	cs_ce_meta) 
	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, 
		REPLACE(CONVERT(INSTITUTION_NAME USING utf8), '\r\n', ''), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8), 
		IF(REGION != '', CONCAT('AUTRE##',CONVERT(REGION USING utf8)), NULL), 
		IF(COUNTRY_CODE = 'fr', '2654', CONCAT('AUTRE##', CONVERT(COUNTRY_CODE USING utf8))) AS PAYS, 
		CONCAT(IF(TEL != '', CONCAT('(+33)', REPLACE(TEL,'[33]','')), NULL), IF(FAX != '', CONCAT(IF(TEL != '', ';;', ''), 'FAX##(+33)', REPLACE(FAX,'[33]','')), NULL)), 
		EMAIL, 
		IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION, 
		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, 
		CONVERT(ACCESS_RESTRICTION USING utf8),
		@idm := @idm+1 
	FROM tb_herbiers.HERBIERS_ORGANISATION 
	ORDER BY ID_ORG ASC;
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) 
	SELECT @idm_tmp := @idm_tmp+1, '120', ID_ORG, NULL, IF(DATE_DERNIERE_MODIF = '0000-00-00 00:00:00', NOW(), DATE_DERNIERE_MODIF),
		CONCAT('Importation de l''enregistrement #',ID_ORG,' de la table HERBIERS_ORGANISATION.'), 
		IF(CE_MODIFIER_PAR = '0', '1', CE_MODIFIER_PAR), '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_ORGANISATION 
	ORDER BY ID_ORG ASC;
 
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '-', '') WHERE cs_truk_telephone LIKE '%-%';
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '.', '') WHERE cs_truk_telephone LIKE '%.%';
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ' ', '') WHERE cs_truk_telephone LIKE '% %';
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%';
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%';
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ',', ';; (+33)') WHERE cs_truk_telephone LIKE '%,%';
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
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
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
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
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, '(+33)(0)', '(+33)0') WHERE cs_truk_telephone LIKE '%(+33)(0)%';
UPDATE tb_coel.coel_structure SET cs_truk_telephone = REPLACE(cs_truk_telephone, ';;0', ';; (+33)0') WHERE cs_truk_telephone LIKE '%;;0%';
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)__%';
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 '%;;%';
 
#------------------------------------------------------------------------------------------------------------------------
#EFLORE_DROIT_POSSEDER vers coel_personne_a_relation
INSERT INTO tb_coel.coel_personne_a_relation
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
	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
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER 
	ORDER BY EDP_ID_UTILISATEUR ASC;
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)
	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'
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER 
	ORDER BY EDP_ID_UTILISATEUR ASC;
 
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_COORDONNE vers coel_personne_a_relation
INSERT INTO tb_coel.coel_personne_a_relation
	(cpar_id_personne_01, cpar_id_personne_02, cpar_id_projet, cpar_id_role, cpar_ce_meta)
	SELECT HC_ID_COORDINATEUR, HC_ID_REDACTEUR, '2', '2025', @idm := @idm+1
	FROM tb_herbiers.HERBIERS_COORDONNE 
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
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)
	SELECT @idm_tmp := @idm_tmp+1, '114', CONCAT(HC_ID_COORDINATEUR, '-', HC_ID_REDACTEUR, '-2-2025'), NULL, 
		NOW(), CONCAT('Importation des relations de coordination de l''utilisateur #',HC_ID_COORDINATEUR,' de la table HERBIER_COORDONNE.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_COORDONNE
	ORDER BY HC_ID_COORDINATEUR, HC_ID_REDACTEUR ASC;
 
#------------------------------------------------------------------------------------------------------------------------	
# EFLORE_DROIT_POSSEDER et annuaire_tela vers coel_personne
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
INSERT INTO tb_coel.coel_personne
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom, 
	 cp_adresse_01, cp_adresse_02, cp_ce_truk_region, cp_code_postal, cp_ville, cp_ce_truk_pays,
	 cp_truk_courriel, cp_truk_url, 
	 cp_description, 
	 cp_ce_truk_role, 
	 cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,  
	 cp_ce_meta)
	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)),
		   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)),
		   LOWER(U_MAIL), U_WEB, 
		   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)), 
		   IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'), 
		   @idm := @idm+1
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)
		LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)  
	ORDER BY U_ID ASC;
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) 
	SELECT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à EFLORE_DROIT_POSSEDER.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)  
	ORDER BY U_ID ASC;
 
#------------------------------------------------------------------------------------------------------------------------	
# HERBIERS_ADMINISTRER et annuaire_tela vers coel_personne
# Pour l''identifiant on garde celui de l''annuaire Tela. Les autres projets commenceront à partir de 100 000
INSERT INTO tb_coel.coel_personne
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom, 
	 cp_adresse_01, cp_adresse_02, cp_ce_truk_region, cp_code_postal, cp_ville, cp_ce_truk_pays,
	 cp_truk_courriel, cp_truk_url, 
	 cp_description, 
	 cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,  
	 cp_ce_meta)
	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)),
		   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)),
		   LOWER(U_MAIL), U_WEB, 
		   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)), 
		   U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'), 
		   @idm := @idm+1
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID) 
		LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays) 
	WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
	ORDER BY U_ID ASC;
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) 
	SELECT DISTINCT @idm_tmp := @idm_tmp+1, '113', U_ID, NULL, U_DATE,
		CONCAT('Importation de l''utilisateur #', U_ID,' de la table annuaire_tela liée à HERBIERS_ADMINISTRER.'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA 
		LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)    
	WHERE U_ID NOT IN (SELECT DISTINCT EDP_ID_UTILISATEUR FROM tb_herbiers.EFLORE_DROIT_POSSEDER)
	ORDER BY U_ID ASC;
 
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_ADMINISTRER vers coel_structure_a_personne
INSERT INTO tb_coel.coel_structure_a_personne
	(csap_id_structure, csap_id_personne, csap_id_role, csap_ce_meta)
	SELECT HA_ID_ORG, HA_ID_ANNUAIRE, '2026', @idm := @idm+1
	FROM tb_herbiers.HERBIERS_ADMINISTRER 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
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)
	SELECT @idm_tmp := @idm_tmp+1, '121', CONCAT(HA_ID_ORG, '-', HA_ID_ANNUAIRE, '-2026'), NULL, 
		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'
	FROM tb_herbiers.HERBIERS_ADMINISTRER 
	ORDER BY HA_ID_ANNUAIRE, HA_ID_ORG ASC;
	
	
#------------------------------------------------------------------------------------------------------------------------
#HERBIERS_STAFF vers coel_personne
INSERT INTO tb_coel.coel_personne
	(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,
	cp_truk_telephone,
	cp_ce_meta)
	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), 
		   CONCAT(IF(TEL != '', CONCAT('(+33)', REPLACE(TEL,'[33]','')), NULL), IF(FAX != '', CONCAT(IF(TEL != '', ';;', ''), 'FAX##(+33)', REPLACE(FAX,'[33]','')), NULL)), 
		   @idm := @idm+1
	FROM tb_herbiers.HERBIERS_STAFF
	ORDER BY ID_STAFF ASC;
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)
	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()),
		CONCAT('Importation de l''utilisateur #', 100000+ID_STAFF,' de la table HERBIERS_STAFF'), '1', '1', '162.38.234.1'
	FROM tb_herbiers.HERBIERS_STAFF
	ORDER BY ID_STAFF ASC;
 
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ' ', '') WHERE cp_truk_telephone LIKE '% %';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '-', '') WHERE cp_truk_telephone LIKE '%-%';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '.', '') WHERE cp_truk_telephone LIKE '%.%';
# Gérer les séparateurs ; au cas par cas
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%';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ',', ';; (+33)') WHERE cp_truk_telephone LIKE '%,%';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)+33', '(+33)') WHERE cp_truk_telephone LIKE '%(+33)+33%';
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
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
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
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
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, '(+33)(0)', '(+33)0') WHERE cp_truk_telephone LIKE '%(+33)(0)%';
UPDATE tb_coel.coel_personne SET cp_truk_telephone = REPLACE(cp_truk_telephone, ';;0', ';; (+33)0') WHERE cp_truk_telephone LIKE '%;;0%';
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)__%';
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 '%;;%';
 
ALTER TABLE tb_coel.coel_personne  ORDER BY cp_id_personne;
 
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_ont_un_staff vers coel_structure_a_personne
# ATTENTION : Nous ignorons les liaisons qui n ont pas de correspondance dans la table HERBIER_STAFF
INSERT INTO tb_coel.coel_structure_a_personne
	(csap_id_structure, csap_id_personne, csap_id_role, csap_mark_contact, csap_ce_truk_fonction, csap_ce_meta)
	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
	FROM tb_herbiers.HERBIERS_STAFF AS hs LEFT JOIN tb_herbiers.HERBIERS_ont_un_staff AS hous ON (hous.ID_STAFF = hs.ID_STAFF) 
	WHERE hous.ID_ORG != 0 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;
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)
	SELECT @idm_tmp := @idm_tmp+1, '121', CONCAT(ID_ORG, '-', 100000+hous.ID_STAFF, '-2027'), NULL, 
		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'
	FROM tb_herbiers.HERBIERS_STAFF AS hs LEFT JOIN tb_herbiers.HERBIERS_ont_un_staff AS hous ON (hous.ID_STAFF = hs.ID_STAFF)
	WHERE hous.ID_ORG != 0 
	ORDER BY hous.ID_ORG, hous.ID_STAFF ASC;	
	
UPDATE tb_coel.coel_structure_a_personne 
SET csap_ce_truk_fonction = IF(csap_ce_truk_fonction IS NOT NULL, IF(csap_ce_truk_fonction LIKE '%irecteur%', '2028', 
	IF(csap_ce_truk_fonction LIKE '%onservateur%', '2029', IF(csap_ce_truk_fonction LIKE '%echnicien%', '2030', csap_ce_truk_fonction))), NULL) 
WHERE csap_id_role = '2027'	;
	
#------------------------------------------------------------------------------------------------------------------------
#HERBIER_COLLECTION vers coel_collection
INSERT INTO tb_coel.coel_collection
	(cc_id_collection, cc_ce_projet, cc_ce_structure, cc_guid, cc_truk_code, cc_nom, cc_description, 
	cc_ce_type_depot, 
	cc_ce_specimen_type,  
	cc_description_specialiste, cc_truk_url, 
	cc_specimen_type_nbre, 
	cc_truk_preservation, 
    cc_ce_meta)
	SELECT hc.ID, '2', PARENT_ID, CONCAT("URN:tela-botanica.org:ihf:col",hc.ID) AS GUID, COLLECTION_CODE, CONVERT(NOM_COLLECTION USING utf8), CONVERT(DESCRIPTION USING utf8), 
		IF(STATUT = 1, '2159', IF(STATUT = 2, '2160', IF(STATUT = 3, '2161', IF(STATUT = 4, '2162', NULL)))), 
		IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE,
		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, 
		NUM_SPECIMENS, 
		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, 
		@idm := @idm+1
	FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID) 
	WHERE hc.ID != 4 
	GROUP BY hc.ID 
	ORDER BY hc.ID ASC;
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) 
	SELECT @idm_tmp := @idm_tmp+1, '101', ID, NULL, 
		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') 	
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;
#------------------------------------------------------------------------------------------------------------------------
#HERBIER_COLLECTION vers coel_collection_botanique
INSERT INTO tb_coel.coel_collection_botanique 
	(ccb_id_collection, ccb_truk_unite_base, 
	ccb_recolte_date_debut, ccb_ce_recolte_date_debut_type, ccb_recolte_date_fin, ccb_ce_recolte_date_fin_type, 
	ccb_inventaire_digital_pourcent, ccb_ce_classement_etat,
	ccb_truk_degradation_specimen, 
	ccb_truk_degradation_presentation,
	ccb_annotation_classement,
	ccb_ce_meta)
	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')))), 
		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))))), 
		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)))))), 
		PERCENT_DATABASED, IF(ETAT_CLASSEMENT = 1, '2328', IF(ETAT_CLASSEMENT = 2, '2329', IF(ETAT_CLASSEMENT = 3, '2330', IF(ETAT_CLASSEMENT = 4, '2331', NULL)))),
		CONCAT('TOTAL##',IF(ETAT_SPECIMENS = 1, '2328', IF(ETAT_SPECIMENS = 2, '2329', IF(ETAT_SPECIMENS = 3, '2330', IF(ETAT_SPECIMENS = 4, '2331', NULL))))),
		CONCAT('TOTAL##',IF(ETAT_PRESENTATION = 1, '2328', IF(ETAT_PRESENTATION = 2, '2329', IF(ETAT_PRESENTATION = 3, '2330', IF(ETAT_PRESENTATION = 4, '2331', NULL))))),
		IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL), 
		@idm := @idm+1 
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;
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) 
	SELECT @idm_tmp := @idm_tmp+1, '106', ID, NULL, 
		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') 	
	FROM tb_herbiers.HERBIERS_COLLECTION
	WHERE ID != 4 
	ORDER BY ID ASC;	
		
#------------------------------------------------------------------------------------------------------------------------
#HERBIERS_INDIC vers coel_commentaires
INSERT INTO tb_coel.coel_commentaire
	(ccm_id_commentaire, ccm_ce_projet, ccm_titre, 
	ccm_texte, ccm_ponderation, ccm_ce_meta)
	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,
		CONVERT(TXT_INDIC USING utf8), 0, @idm := @idm+1 
	FROM tb_herbiers.HERBIERS_INDIC  
	ORDER BY ID_INDIC ASC;
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) 
	SELECT @idm_tmp := @idm_tmp+1, '107', hi.ID_INDIC, NULL, 
		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') 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_INDIC_HISTORIQUE AS hih ON (hi.ID_INDIC = hih.ID_INDIC) 
	GROUP BY hi.ID_INDIC 
	ORDER BY hi.ID_INDIC ASC;
	
#------------------------------------------------------------------------------------------------------------------------
# HERBIERS_A_UN_TYPE vers coel_collection_a_commentaire
INSERT INTO tb_coel.coel_collection_a_commentaire
	(ccac_id_collection, ccac_id_commentaire, 
	ccac_truk_type, 
	ccac_ce_meta)
	SELECT hi.ID_INDIC, ID, 
		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,
		@idm := @idm+1 
	FROM tb_herbiers.HERBIERS_INDIC AS hi LEFT JOIN tb_herbiers.HERBIERS_A_UN_TYPE AS haut ON (hi.ID_INDIC = haut.ID_INDIC)
	WHERE ID IN (SELECT ccm_id_commentaire FROM tb_coel.coel_commentaire)
	GROUP BY hi.ID_INDIC 
	ORDER BY hi.ID_INDIC ASC;
# ATTENTION : ajoute moins de valeurs à @
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) 
	SELECT @idm_tmp := @idm_tmp+1, '102', CONCAT(hi.ID_INDIC,'-',ID), NULL, 
		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') 	
	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) 
	WHERE ID IN (SELECT ccm_id_commentaire FROM tb_coel.coel_commentaire)
	GROUP BY hi.ID_INDIC 
	ORDER BY hi.ID_INDIC ASC;
 
# Mise à jour des id de personne inexistant dans l''annuaire Tela dans la table : coel_meta_historique_ligne
# Récupération manuelle...
INSERT INTO tb_coel.coel_personne
	(cp_id_personne, cp_ce_projet, cp_fmt_nom_complet, cp_prenom, cp_nom, cp_description, cp_ce_annuaire_tela, cp_ce_meta)
VALUES 	(19, 3, 'Inconnu #19', 'Inconnu', 'INCONNU', 'Individu non présent dans l''annuaire Tela Botanica.', 19, @idm := @idm+1),
		(920, 3, 'Inconnu #920', 'Inconnu', 'INCONNU', 'Individu non présent dans l''annuaire Tela Botanica.', 920, @idm := @idm+1);
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) 
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'),
		(@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');
Subversion Repositories eFlore/Applications.coel

(root)/trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql – Rev 600 → 601
