Subversion Repositories Applications.referentiel

Rev

Rev 380 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 380 Rev 382
Line 1... Line 1...
1
SELECT b.num_nom AS apd_num_nom, b.num_nom_retenu AS apd_num_nom_retenu, b.num_tax_sup AS apd_num_tax_sup, b.rang AS apd_rang, b.nom_sci AS apd_nom_sci, b.auteur AS apd_auteur, b.annee AS apd_annee, apd_synonymes, b.`nom_supra_generique` AS apd_nom_supra_generique, b.`genre` AS apd_genre, b.`epithete_sp` AS apd_epithete_sp, b.`type_epithete` AS apd_type_epithete, b.`epithete_infra_sp` AS apd_epithete_infra_sp, b.`cultivar` AS apd_cultivar, b.`cultivar_groupe` AS apd_cultivar_groupe, concat('https://beta.tela-botanica.org/test/apd-nn-', b.`num_nom_retenu`) as apd_url, 
1
SELECT b.num_nom AS apd_num_nom, b.num_nom_retenu AS apd_num_nom_retenu, b.num_tax_sup AS apd_num_tax_sup, b.rang AS apd_rang, b.nom_sci AS apd_nom_sci, b.auteur AS apd_auteur, b.annee AS apd_annee, b.`biblio_origine` AS apd_biblio, apd_synonymes, b.`nom_supra_generique` AS apd_nom_supra_generique, b.`genre` AS apd_genre, b.`epithete_sp` AS apd_epithete_sp, b.`type_epithete` AS apd_type_epithete, b.`epithete_infra_sp` AS apd_epithete_infra_sp, b.`cultivar` AS apd_cultivar, b.`cultivar_groupe` AS apd_cultivar_groupe, concat('https://beta.tela-botanica.org/test/apd-nn-', b.`num_nom_retenu`) as apd_url, 
2
CASE WHEN b.rang > 290 THEN
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),'","',
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),'","',
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),'"]') 
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
6
WHEN b.rang = 220 THEN
Line 10... Line 10...
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),'"]')
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 apd_shortcuts
11
END as apd_shortcuts
12
FROM `apd` b, (SELECT num_nom_retenu, concat('[',group_concat('"',nom_sci,' ',auteur,'"'),']') AS apd_synonymes FROM apd WHERE num_nom != num_nom_retenu AND num_nom_retenu != 0 GROUP BY `num_nom_retenu`) as c
12
FROM `apd` b, (SELECT num_nom_retenu, concat('[',group_concat('"',nom_sci,' ',auteur,'"'),']') AS apd_synonymes FROM apd 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
13
WHERE (b.num_nom_retenu = c.num_nom_retenu) AND b.num_nom = b.num_nom_retenu
14
UNION
14
UNION
15
SELECT b.num_nom AS apd_num_nom, b.num_nom_retenu AS apd_num_nom_retenu, b.num_tax_sup AS apd_num_tax_sup, b.rang AS apd_rang, b.nom_sci AS apd_nom_sci, b.auteur AS apd_auteur, b.annee AS apd_annee, '' as apd_synonymes, b.`nom_supra_generique` AS apd_nom_supra_generique, b.`genre` AS apd_genre, b.`epithete_sp` AS apd_epithete_sp, b.`type_epithete` AS apd_type_epithete, b.`epithete_infra_sp` AS apd_epithete_infra_sp, b.`cultivar` AS apd_cultivar, b.`cultivar_groupe` AS apd_cultivar_groupe, concat('https://beta.tela-botanica.org/test/apd-nn-', b.`num_nom_retenu`) as apd_url, 
15
SELECT b.num_nom AS apd_num_nom, b.num_nom_retenu AS apd_num_nom_retenu, b.num_tax_sup AS apd_num_tax_sup, b.rang AS apd_rang, b.nom_sci AS apd_nom_sci, b.auteur AS apd_auteur, b.annee AS apd_annee, b.`biblio_origine` AS apd_biblio, '' as apd_synonymes, b.`nom_supra_generique` AS apd_nom_supra_generique, b.`genre` AS apd_genre, b.`epithete_sp` AS apd_epithete_sp, b.`type_epithete` AS apd_type_epithete, b.`epithete_infra_sp` AS apd_epithete_infra_sp, b.`cultivar` AS apd_cultivar, b.`cultivar_groupe` AS apd_cultivar_groupe, concat('https://beta.tela-botanica.org/test/apd-nn-', b.`num_nom_retenu`) as apd_url, 
16
CASE WHEN b.rang > 290 THEN
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),'","',
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),'","',
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),'"]') 
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
20
WHEN b.rang = 220 THEN