Subversion Repositories Applications.referentiel

Rev

Rev 380 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
380 mathias 1 
SELECT b.num_nom AS isfan_num_nom, b.num_nom_retenu AS isfan_num_nom_retenu, b.num_tax_sup AS isfan_num_tax_sup, b.rang AS isfan_rang, b.nom_sci AS isfan_nom_sci, b.auteur AS isfan_auteur, b.annee AS isfan_annee, b.`biblio_origine` AS isfan_biblio, b.nom_francais AS isfan_nom_francais, isfan_synonymes, b.`nom_supra_generique` AS isfan_nom_supra_generique, b.`genre` AS isfan_genre, b.`epithete_sp` AS isfan_epithete_sp, b.`type_epithete` AS isfan_type_epithete, b.`epithete_infra_sp` AS isfan_epithete_infra_sp, b.`cultivar` AS isfan_cultivar, b.`cultivar_groupe` AS isfan_cultivar_groupe, concat('https://beta.tela-botanica.org/test/isfan-nn-', b.`num_nom_retenu`) as isfan_url,
2 
CASE WHEN b.rang > 290 THEN
3 
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),'","',
4 
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),'","',
5 
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),'"]')
6 
WHEN b.rang = 220 THEN
7 
concat('["',substr(`genre`, 1, 1), '","', substr(`genre`, 1, 2), '","', substr(`genre`, 1, 3), '","', substr(`genre`, 1, 4), '"]')
8 
WHEN b.rang < 220 THEN
9 
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), '"]')
10 
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),'"]')
11 
END as isfan_shortcuts
12 
FROM `isfan` b, (SELECT num_nom_retenu, concat('[',group_concat('"',nom_sci,' ',auteur,'"'),']') AS isfan_synonymes FROM isfan WHERE num_nom != num_nom_retenu AND num_nom_retenu != 0 GROUP BY `num_nom_retenu`) as c
13 
WHERE b.num_nom_retenu = c.num_nom_retenu AND b.num_nom = b.num_nom_retenu AND b.nom_sci != '   '
14 
UNION
15 
SELECT b.num_nom AS isfan_num_nom, b.num_nom_retenu AS isfan_num_nom_retenu, b.num_tax_sup AS isfan_num_tax_sup, b.rang AS isfan_rang, b.nom_sci AS isfan_nom_sci, b.auteur AS isfan_auteur, b.annee AS isfan_annee, b.`biblio_origine` AS isfan_biblio, b.nom_francais AS isfan_nom_francais, '' as isfan_synonymes, b.`nom_supra_generique` AS isfan_nom_supra_generique, b.`genre` AS isfan_genre, b.`epithete_sp` AS isfan_epithete_sp, b.`type_epithete` AS isfan_type_epithete, b.`epithete_infra_sp` AS isfan_epithete_infra_sp, b.`cultivar` AS isfan_cultivar, b.`cultivar_groupe` AS isfan_cultivar_groupe, concat('https://beta.tela-botanica.org/eflore/isfan-nn-', b.`num_nom_retenu`) as isfan_url,
16 
CASE WHEN b.rang > 290 THEN
17 
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),'","',
18 
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),'","',
19 
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),'"]')
20 
WHEN b.rang = 220 THEN
21 
concat('["',substr(`genre`, 1, 1), '","', substr(`genre`, 1, 2), '","', substr(`genre`, 1, 3), '","', substr(`genre`, 1, 4), '"]')
22 
WHEN b.rang < 220 THEN
23 
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), '"]')
24 
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),'"]')
25 
END as isfan_shortcuts
26 
FROM `isfan` b WHERE b.num_nom_retenu not in (SELECT distinct num_nom_retenu FROM isfan 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") AND b.nom_sci != '   '