Subversion Repositories eFlore/Projets.eflore-projets

/*
À l'origine les observations nécessiteuses sont celles n'ayant pas de données génénées,
Soit: nom_ret, nom_ret_nn, nt ou famille à NULL|0|''

Eg:
        SELECT id_observation, nom_sel 
        FROM `tb_cel_test`.`cel_obs` 
        WHERE ( 
                nom_ret IS NULL or nom_ret = ''
                OR nom_ret_nn IS NULL or nom_ret_nn = 0
                OR nt IS NULL or nt = 0
                OR famille IS NULL or famille = ''
        )

Sauf que:
1) on exclue celles sans nom_sel (elles sont sans espoir):
nom_sel IS NOT NULL AND nom_sel != ''
2) on exclue celles qui on un nom_ret_nn à 0, car cela peut-être légal, cf maj-201307.sql à ce propos
# donc pas de `nom_ret_nn = 0` dans la requête
3) on exclue, dans un premier temps, celles dont le référentiel n'est pas défini
AND (nom_referentiel IS NULL)

D'où, les 3621 observations suivantes (2206 nom_sel distincts)
 SELECT id_observation, nom_sel 
 FROM `tb_cel_test`.`cel_obs` 
 WHERE (
        nom_sel IS NOT NULL AND nom_sel != ''
        AND (
            nom_ret IS NULL OR nom_ret = ''
            OR nom_ret_nn IS NULL
            OR nt IS NULL or nt = 0
            OR famille IS NULL or famille = ''
            )
        AND (nom_referentiel IS NOT NULL)
       )

Dans un premier temps nous travaillons avec le bdtfx, c'est à dire que
     AND (nom_referentiel IS NOT NULL)
devient
        AND (nom_referentiel like 'bdtfx%')
soit 3597/3621 observations:

Et effectuons une jointure sur bdtfx:
SELECT id_observation, nom_sel, b.num_nom, b.famille
   FROM `tb_cel_test`.`cel_obs` c INNER JOIN `tb_eflore`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel)
   WHERE (
        nom_sel IS NOT NULL AND nom_sel != ''
        AND (
            nom_ret IS NULL OR nom_ret = ''
            OR nom_ret_nn IS NULL
            OR nt IS NULL OR nt = 0
            OR c.famille IS NULL OR c.famille = ''
            )
        AND (nom_referentiel like 'bdtfx%')
       )

* Or nous observons que la famille est parfois légitimement NULL ! Ce n'est pas pertinent de l'utiliser
comme critère de caractérisation d'une observation buggée, contentons-nous donc de empty ('')

* Or nous observons que le numéro taxonomique est parfois légitimement 0 ! Ce n'est pas pertinent de l'utiliser
comme critère de caractérisation d'une observation buggée, contentons-nous donc de NULL



Soit 84 lignes, cependant, un nom_sel peut correspondre à plusieurs num_nom_retenu dans bdtfx ! (et oui, les suffixes latins et d'auteur).
Il s'agit donc de ne pas traiter ceux qui risquerait d'être mal-corrigé (sans les 100% de certitude).
Ainsi un ` GROUP BY id_observation HAVING count(id_observation) = 1 ` sera du meilleur effet.

Nous obtenons donc ainsi les 69 observations à mettre à jour:
SELECT id_observation, nom_sel, nom_ret, nom_ret_nn, nt, c.famille, b.num_nom, b.nom_sci, b.num_taxonomique, b.famille
   FROM `tb_cel_test`.`cel_obs` c INNER JOIN `tb_eflore`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel)
   WHERE (
        nom_sel IS NOT NULL AND nom_sel != ''
        AND (
            nom_ret IS NULL OR nom_ret = ''
            OR nom_ret_nn IS NULL
            OR nt IS NULL
            OR c.famille = ''
            )
        AND (nom_referentiel like 'bdtfx%')
       )
   GROUP BY id_observation HAVING count(id_observation) = 1


=== la mise à jour ===
Comme nous voulons utiliser UPDATE, nous devons remplacer le JOIN par des conditions du WHERE, mais le GROUP BY bloque de
toute manière, un SUB-SELECT (table temporaire) est donc nécessaire:

=== finale ===
*/

CREATE TEMPORARY TABLE T_bis ( INDEX(`id_observation`)) AS 
SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille
   FROM `tb_cel_test`.`cel_obs` c, `tb_eflore`.`bdtfx_v1_01` b
   WHERE (
        b.nom_sci = c.nom_sel
        AND nom_sel IS NOT NULL AND nom_sel != ''
        AND (
            nom_ret IS NULL OR nom_ret = ''
            OR nom_ret_nn IS NULL
            OR nt IS NULL OR nt = 0
            OR c.famille = ''
            )
        AND (nom_referentiel like 'bdtfx%')
       )
   GROUP BY id_observation HAVING count(id_observation) = 1

UPDATE `tb_cel_test`.`cel_obs` c, T_bis t SET
       c.nom_ret = t.nom_sci,
       c.nom_ret_nn = t.num_nom,
       c.nt = t.num_taxonomique,
       c.famille = t.famille
WHERE (c.id_observation = t.id_observation);
DROP TEMPORARY TABLE T_bis;