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



	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, 
		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),
		@idm := @idm+1 
	FROM tb_herbiers.HERBIERS_ORGANISATION 

	 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), 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 : ', 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)

	 cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_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),
		   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) 

#------------------------------------------------------------------------------------------------------------------------
#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)
	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;
INSERT INTO tb_coel.coel_meta_historique_ligne
	(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement, 

	 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), CONVERT(STATUT USING utf8), 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 

#------------------------------------------------------------------------------------------------------------------------
#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) 

Rev 93	Rev 94
Line 54...	Line 54...
54	`cs_description,`	54	`cs_description,`
55	`cs_truk_url,`	55	`cs_truk_url,`
56	`cs_condition_acces,`	56	`cs_condition_acces,`
57	`cs_ce_meta)`	57	`cs_ce_meta)`
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,`	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,`
59	`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,`	59	`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,`
60	`IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), NULL) AS DESCRIPTION ,`	60	`IF(SOURCE_DES_DONNEES != '', CONVERT(CONCAT("Source des données : ",SOURCE_DES_DONNEES) USING utf8), 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,`	61	`CONCAT(IF(HO_URL != 'http://', CONCAT('WEB#',HO_URL), ''),IF(HO_URL != 'http://' AND LOGO != '', ';', ''), IF(LOGO != '', CONCAT('LOGO#',LOGO),'')) AS URLS,`
62	`CONVERT(ACCESS_RESTRICTION USING utf8),`	62	`CONVERT(ACCESS_RESTRICTION USING utf8),`
63	`@idm := @idm+1`	63	`@idm := @idm+1`
64	`FROM tb_herbiers.HERBIERS_ORGANISATION`	64	`FROM tb_herbiers.HERBIERS_ORGANISATION`
Line 112...	Line 112...
112	`cp_truk_courriel, cp_truk_url,`	112	`cp_truk_courriel, cp_truk_url,`
113	`cp_description,`	113	`cp_description,`
114	`cp_ce_truk_role,`	114	`cp_ce_truk_role,`
115	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`	115	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`
116	`cp_ce_meta)`	116	`cp_ce_meta)`
117	`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),`	117	`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)),`
118	`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),`	118	`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),`
119	`LOWER(U_MAIL), U_WEB,`	119	`LOWER(U_MAIL), U_WEB,`
120	`CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8),`	120	`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)),`
121	`IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),`	121	`IF(EDP_ID_DROIT = '1', '2022', IF(EDP_ID_DROIT = '2', '2023', IF(EDP_ID_DROIT = '3', '2024', 0))),`
122	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`	122	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`
123	`@idm := @idm+1`	123	`@idm := @idm+1`
124	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP`	124	`FROM tb_herbiers.EFLORE_DROIT_POSSEDER AS EDP`
125	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)`	125	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (EDP.EDP_ID_UTILISATEUR = AT.U_ID)`
Line 142...	Line 142...
142	`cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,`	142	`cp_adresse_01, cp_adresse_02, cp_region, cp_code_postal, cp_ville, cp_pays,`
143	`cp_truk_courriel, cp_truk_url,`	143	`cp_truk_courriel, cp_truk_url,`
144	`cp_description,`	144	`cp_description,`
145	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`	145	`cp_mot_de_passe, cp_login, cp_ce_annuaire_tela, cp_parametre,`
146	`cp_ce_meta)`	146	`cp_ce_meta)`
147	`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),`	147	`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)),`
148	`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),`	148	`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),`
149	`LOWER(U_MAIL), U_WEB,`	149	`LOWER(U_MAIL), U_WEB,`
150	`CONVERT(CONCAT('Spécialités systématiques : ', U_SPE, '\nSpécialités géographiques : ', U_GEO, '\nFonction : ', U_FONCTION, '\nStructure : ', U_TITLE) USING utf8),`	150	`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)),`
151	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`	151	`U_PASSWD, U_MAIL, U_ID, CONCAT('<?xml version="1.0" encoding="UTF-8"><parametres><niveau_bota>', U_NIV,'</niveau_bota></parametres>'),`
152	`@idm := @idm+1`	152	`@idm := @idm+1`
153	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`	153	`FROM tb_herbiers.HERBIERS_ADMINISTRER AS HA`
154	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`	154	`LEFT JOIN tb_v4.annuaire_tela AS AT ON (HA.HA_ID_ANNUAIRE = AT.U_ID)`
155	`LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)`	155	`LEFT JOIN tb_v4.carto_PAYS AS CP ON (AT.U_COUNTRY = CP.CP_ID_Pays)`
Line 184...	Line 184...
184	`#------------------------------------------------------------------------------------------------------------------------`	184	`#------------------------------------------------------------------------------------------------------------------------`
185	`#HERBIERS_STAFF vers coel_personne`	185	`#HERBIERS_STAFF vers coel_personne`
186	`INSERT INTO tb_coel.coel_personne`	186	`INSERT INTO tb_coel.coel_personne`
187	`(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,`	187	`(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,`
188	`cp_truk_telephone, cp_truk_fax, cp_ce_meta)`	188	`cp_truk_telephone, cp_truk_fax, cp_ce_meta)`
189	`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),`	189	`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),`
190	`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`	190	`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`
191	`FROM tb_herbiers.HERBIERS_STAFF`	191	`FROM tb_herbiers.HERBIERS_STAFF`
192	`ORDER BY ID_STAFF ASC;`	192	`ORDER BY ID_STAFF ASC;`
193	`INSERT INTO tb_coel.coel_meta_historique_ligne`	193	`INSERT INTO tb_coel.coel_meta_historique_ligne`
194	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	194	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
Line 243...	Line 243...
243	`cc_description_specialiste, cc_truk_url,`	243	`cc_description_specialiste, cc_truk_url,`
244	`cc_specimen_type_nbre,`	244	`cc_specimen_type_nbre,`
245	`cc_truk_preservation,`	245	`cc_truk_preservation,`
246	`cc_ce_meta)`	246	`cc_ce_meta)`
247	`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,`	247	`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,`
248	`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,`	248	`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,`
249	`NUM_SPECIMENS,`	249	`NUM_SPECIMENS,`
250	`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,`	250	`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,`
251	`@idm := @idm+1`	251	`@idm := @idm+1`
252	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`	252	`FROM tb_herbiers.HERBIERS_COLLECTION AS hc LEFT JOIN tb_herbiers.HERBIERS_ont_pres AS hop ON (hc.ID = hop.ID)`
253	`WHERE hc.ID != 4`	253	`WHERE hc.ID != 4`
Line 282...	Line 282...
282	`#------------------------------------------------------------------------------------------------------------------------`	282	`#------------------------------------------------------------------------------------------------------------------------`
283	`#HERBIERS_INDIC vers coel_commentaires`	283	`#HERBIERS_INDIC vers coel_commentaires`
284	`INSERT INTO tb_coel.coel_commentaire`	284	`INSERT INTO tb_coel.coel_commentaire`
285	`(ccm_id_commentaire, ccm_ce_projet, ccm_titre,`	285	`(ccm_id_commentaire, ccm_ce_projet, ccm_titre,`
286	`ccm_texte, ccm_ponderation, ccm_ce_meta)`	286	`ccm_texte, ccm_ponderation, ccm_ce_meta)`
287	`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,`	287	`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,`
288	`TXT_INDIC, 0, @idm := @idm+1`	288	`CONVERT(TXT_INDIC USING utf8), 0, @idm := @idm+1`
289	`FROM tb_herbiers.HERBIERS_INDIC`	289	`FROM tb_herbiers.HERBIERS_INDIC`
290	`ORDER BY ID_INDIC ASC;`	290	`ORDER BY ID_INDIC ASC;`
291	`INSERT INTO tb_coel.coel_meta_historique_ligne`	291	`INSERT INTO tb_coel.coel_meta_historique_ligne`
292	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`	292	`(cmhl_id_historique_ligne, cmhl_ce_table, cmhl_cle_ligne, cmhl_enregistrement,`
293	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)`	293	`cmhl_date_modification, cmhl_notes, cmhl_ce_modifier_par, cmhl_ce_etat, cmhl_ip)`

Subversion Repositories eFlore/Applications.coel

(root)/trunk/doc/bdd/migration_bdd_herbiers_vers_coel.sql – Rev 93 → 94