Subversion Repositories Sites.tela-botanica.org

DELIMITER $$
DROP FUNCTION IF EXISTS legumino.HTML_UnEncode$$
CREATE DEFINER=`legumino`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN

DECLARE TextString VARCHAR(255) ;
SET TextString = x ;

IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&', '&') ;
END IF ;

IF INSTR( x , '’' )
THEN SET TextString = REPLACE(TextString, '’', '''') ;
END IF ;

IF INSTR( x , '°' )
THEN SET TextString = REPLACE(TextString, '°', '°') ;
END IF ;

IF INSTR( x , 'à' )
THEN SET TextString = REPLACE(TextString, 'à', 'à');
END IF ;

IF INSTR( x , 'â' )
THEN SET TextString = REPLACE(TextString, 'â', 'â');
END IF ;

IF INSTR( x , 'ä' )
THEN SET TextString = REPLACE(TextString, 'ä', 'ä');
END IF ;

IF INSTR( x , 'è' )
THEN SET TextString = REPLACE(TextString, 'è', 'è');
END IF ;

IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é', 'é');
END IF ;

IF INSTR( x , 'ê' )
THEN SET TextString = REPLACE(TextString, 'ê', 'ê');
END IF ;

IF INSTR( x , 'í' )
THEN SET TextString = REPLACE(TextString, 'í', 'í');
END IF ;

IF INSTR( x , 'î' )
THEN SET TextString = REPLACE(TextString, 'î', 'î');
END IF ;

IF INSTR( x , 'ô' )
THEN SET TextString = REPLACE(TextString, 'ô', 'ô');
END IF ;

IF INSTR( x , 'ö' )
THEN SET TextString = REPLACE(TextString, 'ö', 'ö');
END IF ;

IF INSTR( x , 'ü' )
THEN SET TextString = REPLACE(TextString, 'ü', 'ü');
END IF ;

RETURN TextString ;

END$$

DELIMITER ;

DROP VIEW IF EXISTS legumino.legumino_tapir;
CREATE ALGORITHM=UNDEFINED DEFINER=`legumino`@`localhost` SQL SECURITY DEFINER VIEW `legumino_tapir` AS 
        SELECT 
                CONCAT('urn:lsid:tela-botanica.org:legumino:', c.id_contributions) AS guid,
                c.id_contributions AS observation_id,
                c.date AS observation_date, 
                HTML_UnEncode(CONCAT_WS(' ', 
                        l.Genre, 
                        l.Espece,  
                        IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))),  
                        IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL))),  
                        IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL)))))) AS nom_scientifique_complet,
                l.NumNomen AS nom_num_nomenclatural,
                l.NumTaxon AS nom_num_taxonomique,
                HTML_UnEncode(l.Famille) AS nom_famille,
                HTML_UnEncode(l.Genre) AS nom_genre,
                HTML_UnEncode(l.Espece) AS nom_espece,
                IF (TypeInfra3 != '', TypeInfra3, IF (TypeInfra2 != '', TypeInfra2, IF (TypeSousEspece != '', TypeSousEspece, NULL))) AS nom_type,
                HTML_UnEncode(IF (Infra3 != '', Infra3, IF (Infra2 != '', Infra2, IF (SousEspece != '', SousEspece, NULL)))) AS nom_infra_sp,
                HTML_UnEncode(IF (AuteurInfra3 != '', AuteurInfra3, IF (AuteurInfra2 != '', AuteurInfra2, IF (AuteurSousEspece != '', AuteurSousEspece, IF (Auteur != '', Auteur, NULL))))) AS nom_auteur,
                d.nom_departements AS lieu_departement_nom,
                CONCAT(CONVERT(cm.nom_communes USING UTF8), ' [INSEE:', SUBSTRING(cm.code_insee FROM 4), ']') AS lieu_commune_nom_complet,
                cm.nom_communes AS lieu_commune_nom,
                SUBSTRING(cm.code_insee FROM 4) AS lieu_commune_code_insee,
                cm.latitude AS lieu_commune_latitude,
                cm.longitude AS lieu_commune_longitude,
                HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs , CONCAT('<', ct.mail, '>'))) AS observateur_nom_complet,
                HTML_UnEncode(CONCAT_WS(' ', ct.prenom_contributeurs, ct.nom_contributeurs)) AS observateur_nom,
                ct.mail AS observateur_courriel,
                HTML_UnEncode(c.commentaire) AS observation_commentaire,
                CONCAT(_utf8'<?xml version="1.0" encoding="UTF-8"?>', 
                        '<info>',
                                '<nom_num_nomenclatural>',l.NumNomen,'</nom_num_nomenclatural>',
                                '<nom_num_taxonomique>',l.NumTaxon,'</nom_num_taxonomique>',
                        '</info>') AS observation_information_complement,
                c.date_saisie AS saisie_date_modification
        FROM contributions AS c 
                LEFT JOIN communes AS cm ON (c.code_insee = cm.code_insee)
                LEFT JOIN departements AS d ON (cm.num_departements = d.num_departements)
                LEFT JOIN legumino AS l ON (c.NumTaxon = l.NumTaxon) 
                LEFT JOIN contributeurs AS ct ON (c.id_contributeurs = ct.id_contributeurs);