62,15 → 62,23 |
SET @idm_tmp = @idm;# Identifiant pour les métadonnées temporaire |
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_region, cs_pays, cs_telephone, cs_fax, cs_courriel, |
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), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'FR', COUNTRY_CODE) AS PAYS, TEL, FAX, 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, |
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 |
85,6 → 93,21 |
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 |
120,7 → 143,7 |
# 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_region, cp_code_postal, cp_ville, cp_pays, |
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, |
127,7 → 150,7 |
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), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays 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))), |
151,13 → 174,13 |
# 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_region, cp_code_postal, cp_ville, cp_pays, |
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), CONVERT(U_STATE USING utf8), U_ZIP_CODE, CONVERT(U_CITY USING utf8), CONVERT(CP.CP_Intitule_pays 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>'), |
197,9 → 220,11 |
#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_truk_fax, cp_ce_meta) |
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), |
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 |
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 |
210,19 → 235,22 |
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 '%.%'; |
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, '+33(0)', '') WHERE cp_truk_telephone LIKE '%+33(0)%'; |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%;%', REPLACE(cp_truk_telephone, ';0', ';; +33'),cp_truk_telephone); |
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)); |
UPDATE tb_coel.coel_personne SET cp_truk_telephone = IF(cp_truk_telephone LIKE '%+330%', REPLACE(cp_truk_telephone, '+330', '+33'),cp_truk_telephone); |
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 '%;;%'; |
# 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 '%;;%'; |
|
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '.', '') WHERE cp_truk_fax LIKE '%.%'; |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, '+33 (0)', '') WHERE cp_truk_fax LIKE '%+33 (0)%'; |
UPDATE tb_coel.coel_personne SET cp_truk_fax = REPLACE(cp_truk_fax, ' ', '') WHERE cp_truk_fax LIKE '% %'; |
UPDATE tb_coel.coel_personne SET cp_truk_fax = IF(cp_truk_fax LIKE '%+330%', REPLACE(cp_truk_fax, '+330', '+33'),cp_truk_fax); |
|
ALTER TABLE tb_coel.coel_personne ORDER BY cp_id_personne; |
|
#------------------------------------------------------------------------------------------------------------------------ |