| 56,7 → 56,7 |
|---|
| 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, |
| CONVERT(INSTITUTION_NAME USING utf8), CONVERT(ADRESS_LINE USING uft8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL, |
| CONVERT(INSTITUTION_NAME USING utf8), CONVERT(ADRESS_LINE USING utf8), ZIP, CONVERT(TOWN USING utf8), CONVERT(REGION USING utf8), IF(COUNTRY_CODE = 'fr', 'France', 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://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS, |
| CONVERT(ACCESS_RESTRICTION USING utf8), |
| 114,10 → 114,10 |
|---|
| cp_ce_truk_role, |
| cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre, |
| cp_ce_meta) |
| SELECT U_ID, '3', CONVERT(CONCAT(U_SURNAME,' ',UPPER(U_NAME)) USING utf8) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), CONVERT(UPPER(U_NAME) USING utf8), |
| 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), |
| LOWER(U_MAIL), U_WEB, |
| CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8), |
| 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 |
| 144,10 → 144,10 |
|---|
| cp_description, |
| cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre, |
| cp_ce_meta) |
| SELECT DISTINCT U_ID, '3', CONVERT(CONCAT(U_SURNAME,' ',UPPER(U_NAME)) USING utf8) AS NOM_COMPLET, CONVERT(U_SURNAME USING utf8), CONVERT(UPPER(U_NAME) USING utf8), |
| 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), |
| LOWER(U_MAIL), U_WEB, |
| CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8), |
| 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 |
| 186,7 → 186,7 |
|---|
| 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) |
| SELECT 100000+ID_STAFF, '2', CONVERT(CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET USING utf8), CONVERT(UPPER(NOM) USING utf8), CONVERT(PRENOM USING utf8), CONVERT(ADRESSE1 USING utf8), CONVERT(ADRESSE2 USING utf8), CP, CONVERT(VILLE USING utf8), LOWER(MAIL), |
| 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 |
| FROM tb_herbiers.HERBIERS_STAFF |
| ORDER BY ID_STAFF ASC; |
| 245,7 → 245,7 |
|---|
| 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), CONVERT(STATUT USING utf8), IF(INCLUDED_TYPE = '0', '2239', IF (INCLUDED_TYPE = '1', '2242', IF (INCLUDED_TYPE = '2', '2241', NULL))) AS INCLUDED_TYPE, |
| CONVERT(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)))) USING utf8) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL, |
| 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 |
| 284,8 → 284,8 |
|---|
| 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), CONVERT(CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...') USING utf8)), CONVERT(CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...') USING utf8) AS TITRE, |
| TXT_INDIC, 0, @idm := @idm+1 |
| 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 |