| 49,7 → 49,7 |
|---|
| #ALTER TABLE tb_herbiers.HERBIERS_ORGANISATION DROP NUM_COLLECTION, DROP ADRESS_TEXT, DROP TIME_ZONE ;# Suppression des champs inutiles |
| 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_truk_identifiant_alternatif, cs_guid, |
| (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_description, |
| cs_truk_url, |
| 56,10 → 56,10 |
|---|
| 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, |
| INSTITUTION_NAME, ADRESS_LINE, ZIP, TOWN, REGION, IF(COUNTRY_CODE = 'fr', 'France', COUNTRY_CODE) AS PAYS, TEL, FAX, EMAIL, |
| IF(SOURCE_DES_DONNEES != '', CONCAT("Source des données : ",SOURCE_DES_DONNEES), NULL) AS DESCRIPTION, |
| 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, |
| 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, |
| ACCESS_RESTRICTION, |
| CONVERT(ACCESS_RESTRICTION USING utf8), |
| @idm := @idm+1 |
| FROM tb_herbiers.HERBIERS_ORGANISATION |
| ORDER BY ID_ORG ASC; |
| 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', CONCAT(U_SURNAME,' ',UPPER(U_NAME)) AS NOM_COMPLET, U_SURNAME, UPPER(U_NAME), |
| U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays, |
| 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), |
| 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, |
| CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE), |
| CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', 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', CONCAT(U_SURNAME,' ',UPPER(U_NAME)) AS NOM_COMPLET, U_SURNAME, UPPER(U_NAME), |
| U_ADDR1, U_ADDR2, U_STATE, U_ZIP_CODE, U_CITY, CP.CP_Intitule_pays, |
| 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), |
| 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, |
| CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE), |
| CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', 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', CONCAT(PRENOM,' ', UPPER(NOM)) AS NOM_COMPLET, UPPER(NOM), PRENOM, ADRESSE1, ADRESSE2, CP, VILLE, LOWER(MAIL), |
| 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), |
| 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; |
| 217,7 → 217,7 |
|---|
| # HERBIERS_ont_un_staff vers coel_structure_a_personne |
| 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#', FONCTION), NULL) AS FONCTION, @idm := @idm+1 |
| 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_ont_un_staff AS hous LEFT JOIN tb_herbiers.HERBIERS_STAFF AS hs ON (hous.ID_STAFF = hs.ID_STAFF) |
| WHERE hous.ID_ORG != 0 |
| ORDER BY hous.ID_ORG, hous.ID_STAFF ASC; |
| 244,8 → 244,8 |
|---|
| 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, NOM_COLLECTION, CONVERT(DESCRIPTION USING utf8), STATUT, 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',DOC_STATE))),IF(COLLECTEURS = '', '', IF(COLLECTEURS IS NULL, '', CONCAT('\nCollecteurs : \n',COLLECTEURS)))) AS DESCRIPTION_SPECIALISTE, IF(URL = '', NULL, URL) AS URL, |
| 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, |
| 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 |
| 266,7 → 266,7 |
|---|
| 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 != '', MODE_CLASSEMENT, NULL), NULL) |
| IF(MODE_CLASSEMENT IS NOT NULL, IF(MODE_CLASSEMENT != '', CONVERT(MODE_CLASSEMENT USING utf8), NULL), NULL) |
| FROM tb_herbiers.HERBIERS_COLLECTION |
| WHERE ID != 4 |
| ORDER BY ID ASC; |
| 284,7 → 284,7 |
|---|
| 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 != '', REM_INDIC, CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...')), CONCAT(SUBSTRING(REPLACE(REPLACE(TXT_INDIC,'<i>',''),'</i>',''), 1, 50),'...')) AS TITRE, |
| 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 |
| FROM tb_herbiers.HERBIERS_INDIC |
| ORDER BY ID_INDIC ASC; |