Subversion Repositories Applications.referentiel

SELECT b.num_nom AS bdtxa_num_nom, b.num_nom_retenu AS bdtxa_num_nom_retenu, b.num_tax_sup AS bdtxa_num_tax_sup, b.rang AS bdtxa_rang, b.nom_sci AS bdtxa_nom_sci, b.auteur AS bdtxa_auteur, b.annee AS bdtxa_annee, b.`biblio_origine` AS bdtxa_biblio, b.nom_francais AS bdtxa_nom_francais, bdtxa_synonymes, b.`nom_supra_generique` AS bdtxa_nom_supra_generique, b.`genre` AS bdtxa_genre, b.`epithete_sp` AS bdtxa_epithete_sp, b.`type_epithete` AS bdtxa_type_epithete, b.`epithete_infra_sp` AS bdtxa_epithete_infra_sp, b.`cultivar` AS bdtxa_cultivar, b.`cultivar_groupe` AS bdtxa_cultivar_groupe, concat('https://beta.tela-botanica.org/test/bdtxa-nn-', b.`num_nom_retenu`) as bdtxa_url, 
CASE WHEN b.rang > 290 THEN
concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 1),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1),'","',
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 2),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2),'","',
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 3),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3),'"]') 
WHEN b.rang = 220 THEN
concat('["',substr(`genre`, 1, 1), '","', substr(`genre`, 1, 2), '","', substr(`genre`, 1, 3), '","', substr(`genre`, 1, 4), '"]')
WHEN b.rang < 220 THEN
concat('["',substr(`nom_supra_generique`, 1, 1), '","', substr(`nom_supra_generique`, 1, 2), '","', substr(`nom_supra_generique`, 1, 3), '","', substr(`nom_supra_generique`, 1, 4), '"]')
ELSE concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),'"]')
END as bdtxa_shortcuts
FROM `bdtxa` b, (SELECT num_nom_retenu, concat('[',group_concat('"',nom_sci,' ',auteur,'"'),']') AS bdtxa_synonymes FROM bdtxa WHERE num_nom != num_nom_retenu AND num_nom_retenu != 0 GROUP BY `num_nom_retenu`) as c
WHERE b.num_nom_retenu = c.num_nom_retenu AND b.num_nom = b.num_nom_retenu
UNION
SELECT b.num_nom AS bdtxa_num_nom, b.num_nom_retenu AS bdtxa_num_nom_retenu, b.num_tax_sup AS bdtxa_num_tax_sup, b.rang AS bdtxa_rang, b.nom_sci AS bdtxa_nom_sci, b.auteur AS bdtxa_auteur, b.annee AS bdtxa_annee, b.`biblio_origine` AS bdtxa_biblio, b.nom_francais AS bdtxa_nom_francais, '' as bdtxa_synonymes, b.`nom_supra_generique` AS bdtxa_nom_supra_generique, b.`genre` AS bdtxa_genre, b.`epithete_sp` AS bdtxa_epithete_sp, b.`type_epithete` AS bdtxa_type_epithete, b.`epithete_infra_sp` AS bdtxa_epithete_infra_sp, b.`cultivar` AS bdtxa_cultivar, b.`cultivar_groupe` AS bdtxa_cultivar_groupe, concat('https://beta.tela-botanica.org/test/bdtxa-nn-', b.`num_nom_retenu`) as bdtxa_url, 
CASE WHEN b.rang > 290 THEN
concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 1),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1),'","',
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 2),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2),'","',
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 3),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3),'"]') 
WHEN b.rang = 220 THEN
concat('["',substr(`genre`, 1, 1), '","', substr(`genre`, 1, 2), '","', substr(`genre`, 1, 3), '","', substr(`genre`, 1, 4), '"]')
WHEN b.rang < 220 THEN
concat('["',substr(`nom_supra_generique`, 1, 1), '","', substr(`nom_supra_generique`, 1, 2), '","', substr(`nom_supra_generique`, 1, 3), '","', substr(`nom_supra_generique`, 1, 4), '"]')
ELSE concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),'"]')
END as bdtxa_shortcuts
FROM `bdtxa` b WHERE b.num_nom_retenu not in (SELECT distinct num_nom_retenu FROM bdtxa WHERE num_nom != num_nom_retenu and b.num_nom_retenu != 0) AND (b.num_nom = b.num_nom_retenu OR b.num_nom_retenu = "0")