SELECT DISTINCT
       spipoll_participation.id as numero_observation,
       users.username as pseudo,
       DATE_FORMAT(spipoll_participation.date, '%d/%m/%Y') AS date_observation,
       DATE_FORMAT(spipoll_participation.date, '%m') AS mois,
       DATE_FORMAT(spipoll_participation.date, '%Y') AS annee,
       DATE_FORMAT(spipoll_participation.heureDebut, '%H:%i') AS heure_debut,
       DATE_FORMAT(spipoll_participation.heureFin, '%H:%i') AS heure_fin,
       spipoll_participation.protocoleLong,  
       spipoll_insecte.Ordre,
       spipoll_participation.postalCode as code_postal,
       spipoll_participation.deptCode as departement,
       st_y(geopoint) as latitude,
       st_x(geopoint) as longitude,
       spipoll_insecte.fr_name as espece,
       (SELECT value
        FROM thesaurus t 
        WHERE t.id=spipoll_observation.nbTaxonId) AS nombre_individus,
       spipoll_observation.nbValidation AS nombre_validation,
       spipoll_observation.taxonVuSurFleur AS insecte_vu_sur_fleur,
       (CASE spipoll_participation.shotOnPhone WHEN 1 THEN 'Oui' ELSE 'Non' END) as smartphone,
       spipoll_plante.fr_name AS plante,
       spipoll_plante.sc_name AS Nom_scientifique, 
       (CASE spipoll_participation.fleurOmbre WHEN 1 THEN 'Oui' ELSE 'Non' END) as fleur_ombragee,
       (SELECT value
        FROM thesaurus t 
        WHERE t.id=spipoll_participation.caractereFleurId) AS type_plante,
       (SELECT value
        FROM thesaurus t 
        WHERE t.id=spipoll_participation.nebulositeId) AS nebulosite,
       (SELECT value
        FROM thesaurus t 
        WHERE t.id=spipoll_participation.temperatureId) AS temperature,
       (SELECT value
        FROM thesaurus t 
        WHERE t.id=spipoll_participation.ventId) AS vent,  
       spipoll_participation.distanceRuche as distance_ruche,
       (SELECT value
        FROM thesaurus t 
        WHERE t.id=spipoll_participation.grandeCultureId) as presence_grande_culture,
       spipoll_participation_habitat.habitatId as habitat,
		IF (m.url is not null,
			CONCAT('https://spgp-api.65mo.fr',
                    m.url),
			CONCAT('https://spgp-api.65mo.fr/api/containers/',
                    m.container,
                    '/download/',
                    m.filename)) as photo_taxon,
   		IF (m.url is not null,
			CONCAT('https://spgp-api.65mo.fr',
                    m2.url),
			CONCAT('https://spgp-api.65mo.fr/api/containers/',
                    m2.container,
                    '/download/',
                    m2.filename)) as photo_fleur,
       cast(replace(replace(replace(spipoll_participation.commentaire,'\r',' '),'\n',' '),'\t',' ') as char character set utf8) as commentaire
       FROM
       spipoll_participation
       INNER JOIN users ON spipoll_participation.userId = users.id
       INNER JOIN spipoll_plante ON spipoll_participation.planteId = spipoll_plante.id 
       INNER JOIN spipoll_observation ON spipoll_observation.participationId = spipoll_participation.id 
       INNER JOIN spipoll_insecte ON spipoll_observation.taxonId = spipoll_insecte.id
       INNER JOIN spipoll_participation_habitat ON spipoll_participation_habitat.participationId=spipoll_participation.Id
       LEFT JOIN medias m ON spipoll_observation.imgTaxon1Id = m.id
       LEFT JOIN medias m2 ON spipoll_participation.imgFleurId = m2.id
       where spipoll_participation.protocoleLong = 0 or spipoll_participation.protocoleLong is NULL
