SELECT 
   observations.observationpk AS num_observation,
   observations.date AS date_observation,
   observations_details_spipoll.horaire_debut_session as Heure_debut,
   observations_details_spipoll.horaire_fin_session as Heure_fin,
   users.nom AS Nom_enseignant,
   users.prenom AS Prenom_enseignant,
   groupes.anneescol AS Annee_scolaire,
   groupes.name AS Nom_classe,
   groupes.niveau AS Niveau,
   observateurs.name AS Nom_groupe,
   groupes.effectifs 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, 
   zones.longitude AS Longitude,
   zones.adresse AS Adresse,
   observations_abondances.nom_espece AS Espece,
   dico_species.speciepk,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_abondances.abondance 
   AND t='observations_details_spipoll'
   AND champ='nb_insecte_simultane') AS Nombre_individus,
   observations_abondances.photo_taxon as Photo_insecte,
   
   -- Informations li?es ? l'observation
   observations_details_spipoll.fleur as Espece_fleur,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_spipoll.type_plante 
   AND t='observations_details_spipoll'
   AND champ='type_plante') AS Type_plante,
   CASE WHEN observations_details_spipoll.ombre = FALSE THEN 'Non'
        ELSE 'Oui' END AS "Fleur_ombre",
   observations_details_spipoll.photo_fleur_env_json as Photo_environnement,   
   observations_details_spipoll.photo_plante_entiere_json as Photo_plante_entiere,
   observations_details_spipoll.photo_fleur_gp_json as Photo_fleur_gros_plan,
   observations_details_spipoll.photo_feuille_gp_json as Photo_feuille_gros_plan,
   round(cast (observations_details_spipoll.score_plante*100 as numeric), 1) as Score_determination_Plantnet,
   --observations_details_spipoll.session as session,

   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_spipoll.couverture_nuageuse 
   AND t='observations_details_spipoll'
   AND champ='couverture_nuageuse') AS Couverture_nuageuse,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_spipoll.temperature 
   AND t='observations_details_spipoll'
   AND champ='temperature') AS Temperature,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_spipoll.vent 
   AND t='observations_details_spipoll'
   AND champ='vent') AS Vent,
   
   --Informations li?es ? la description de la zone d'observation
  (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones.environnement
   AND t='zones'
   AND champ='environnement') AS Environnement,
   --(SELECT label_order
   --FROM dico_labels
   --WHERE dico_labels.valeur=zones.type
   --AND t='zones'
   --AND champ='type') AS Type_zone,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones.surface
   AND t='zones'
   AND champ='surface') AS Surface_zone,
   zones_changes.type_habitat as type_habitat,
   zones_description_spipoll.distance_fleur_ruche as Distance_ruche,
   CASE WHEN zones_description_spipoll.presence_grande_culture = 0 THEN 'Non'
        WHEN zones_description_spipoll.presence_grande_culture = 1 THEN 'Oui'
        WHEN zones_description_spipoll.presence_grande_culture = 2 THEN 'NSP'
        ELSE '' END AS "Presence_grande_culture",
   replace(replace(observations_details_spipoll.commentaire,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 observations_details_spipoll on observations_details_spipoll.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_spipoll on zones_description_spipoll.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 = 3
-- 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%'

order by
observations.observationpk;