SELECT DISTINCT
   observations.observationpk AS num_observation,
   observations.date AS Date_observation,
   --observations.date_create as Date_creation,
   users.nom AS Nom_enseignant,
   users.prenom AS Prenom_enseignant,
   --users.email as email,
   groupes.anneescol AS Annee_scol,
   groupes.name AS Nom_classe,
   groupes.niveau AS Niveau,
   observateurs.name AS Nom_groupe,
   observateurs.effectif AS Nombre_eleves,
   --dico_structures.no_uai as Numero_UAI,
   dico_structures.name AS Nom_etablissement,
   dico_structures.zipcode AS Code_postal_etablissement,
   dico_structures.city AS Ville_etablissement,
   dico_academies.name as Academie,
   zones.latitude AS Latitude_debut, 
   zones.longitude AS Longitude_debut,
   zones_description_sauvages.latitude_fin as Latitude_fin,
   zones_description_sauvages.longitude_fin as Longitude_fin,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_sauvages.cote_rue 
   AND t='zones_description_sauvages'
   AND champ='cote_rue') AS Cote_rue,
   zones.adresse AS Adresse,
   observations_abondances.nom_espece AS Espece,
   dico_species.speciepk,
   dico_structures.structurepk,
   (case cast( observations_abondances.environnement_sauvages->'0' as varchar) WHEN '{"label": "Chemin", "value": true}' THEN 'Oui' 
   																			   WHEN '{"label": "Chemin", "value": false}' THEN 'Non'																			   
   																			   ELSE 'Erreur' END) as Vue_dans_chemin,
   (case cast( observations_abondances.environnement_sauvages->'1' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Vue_dans_fissure,
   (case cast( observations_abondances.environnement_sauvages->'2' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Vue_dans_pelouse,
   (case cast( observations_abondances.environnement_sauvages->'3' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Vue_dans_pied_arbre,
   (case cast( observations_abondances.environnement_sauvages->'4' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Vue_dans_haie,
   (case cast( observations_abondances.environnement_sauvages->'5' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Vue_dans_platebande,
   (case cast( observations_abondances.environnement_sauvages->'6' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Vue_dans_mur,
   observations_abondances.photo_taxon as Photo_taxon,
   replace(replace(observations.notes,chr(10),' '),chr(13),' ') AS Notes

FROM observations
LEFT JOIN observateurs ON observations.observateurfk = observateurs.observateurpk
LEFT JOIN groupes ON groupes.groupepk = observateurs.groupefk
LEFT JOIN users ON users.userpk=groupes.userfk 
LEFT JOIN observations_abondances on observations_abondances.observationfk = observations.observationpk
left join zones_changes on observations.zonechangefk = zones_changes.zonechangepk
left join zones on zones.zonepk = zones_changes.zonefk
left join zones_description_sauvages on zones_description_sauvages.zonechangefk = zones_changes.zonechangepk
left join dico_structures on dico_structures.structurepk = groupes.structurefk 
left join dico_academies on dico_academies.academiepk = dico_structures.academiefk 
left join dico_species on dico_species.speciepk = observations_abondances.speciefk 

WHERE 
observations.protocolefk = 5
-- and groupes.anneescol = '2022'
and users.email not in ('vne5@yopmail.com')
--and dico_academies.name in ('LILLE', 'AMIENS')
--AND dico_etablissements.zipcode LIKE '93%'