select distinct
  observations.observationpk AS Num_observation,
   observations.date AS Date_observation,
   users.nom AS Nom_enseignant,
   users.prenom AS Prenom_enseignant,
   groupes.anneescol AS Annee_scolaire,
   groupes.name AS Nom_classe,
   groupes.niveau AS Niveau,
   groupes.groupepk,
   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,
   dico_species.nom_espece as espece,
   dico_species.speciepk,
   observations_abondances.abondance AS Nombre_individus,
   observations_details_oiseaux.heure_debut AS Heure_debut,
   observations_details_oiseaux.heure_fin AS Heure_fin,
   (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.surface
   AND t='zones'
   AND champ='surface') AS Surface_zone,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.distance_bois 
   AND t='zones_description_escargots'
   AND champ='distance_bois') AS Distance_bois,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.distance_prairie 
   AND t='zones_description_escargots'
   AND champ='distance_prairie') AS Distance_prairie,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.distance_champ_cultive 
   AND t='zones_description_escargots'
   AND champ='distance_champ_cultive') AS Distance_champ,
   (case cast(zones_changes.composition->'1' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_parterre_arbuste,
   (case cast(zones_changes.composition->'2' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_haie,
   (case cast(zones_changes.composition->'3' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_verger,
   (case cast(zones_changes.composition->'4' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_friche,
   (case cast(zones_changes.composition->'5' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_potager,
   (case cast(zones_changes.composition->'6' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_bassin,
   (case cast(zones_changes.composition->'7' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_pelouse_tondue,
   (case cast(zones_changes.composition->'8' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_espaces_paves,
   (case cast(zones_changes.composition->'15' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_orties,
   (case cast(zones_changes.composition->'16' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_ronce,
   (case cast(zones_changes.composition->'17' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_lierre,  
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.engrais 
   AND t='zones_description_escargots'
   AND champ='engrais') AS Utilisation_engrais,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.engrais_type 
   AND t='zones_description_escargots'
   AND champ='engrais_type') AS Type_engrais,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.insecticides
   AND t='zones_description_escargots'
   AND champ='insecticides') AS Utilisation_insecticides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.insecticides_type 
   AND t='zones_description_escargots'
   AND champ='insecticides_type') AS Type_insecticides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.herbicides
   AND t='zones_description_escargots'
   AND champ='herbicides') AS Utilisation_herbicides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.herbicides_type 
   AND t='zones_description_escargots'
   AND champ='herbicides_type') AS Type_herbicides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.fongicides 
   AND t='zones_description_escargots'
   AND champ='fongicides') AS Utilisation_fongicides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.fongicides_type 
   AND t='zones_description_escargots'
   AND champ='fongicides_type') AS Type_fongicides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.antilimaces
   AND t='zones_description_escargots'
   AND champ='antilimaces') AS Utilisation_antilimaces,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.antilimaces_type 
   AND t='zones_description_escargots'
   AND champ='antilimaces_type') AS Type_antilimaces,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_oiseaux.bouilliebordelaise
   AND t='zones_description_escargots'
   AND champ='bouilliebordelaise') AS Utilisation_boulliebordelaise,
   zones_description_oiseaux.mangeoires_distributeurs as Nombre_mangeoires_verticales,
   zones_description_oiseaux.mangeoires_plateaux as Nombre_mangeoires_plateaux,
   zones_description_oiseaux.mangeoires_autres as Nombre_mangeoires_autres,
   zones_description_oiseaux.nichoirs_mesanges as Nombre_nichoirs_mesanges,
   zones_description_oiseaux.nichoirs_semi_ouverts as Nombre_nichoirs_semi_ouverts,
   zones_description_oiseaux.nichoirs_autres as Nombre_nichoirs_autres,
   observations_details_oiseaux.presence_chat as Presence_chat,
   observations_details_oiseaux.presence_chien as Presence_chien,
   observations_details_oiseaux.presence_type as Presence_type,
   (case cast(observations_details_oiseaux.type_nourriture->'0' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_graines_tournesol,
   (case cast(observations_details_oiseaux.type_nourriture->'1' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_boules_graisse,
   (case cast(observations_details_oiseaux.type_nourriture->'2' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_cacahouetes,
   (case cast(observations_details_oiseaux.type_nourriture->'3' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' END) as Presence_autre_nourriture,
   observations_abondances.photo_taxon as Photo_taxon,
   replace(replace(observations.notes,chr(10),' '),chr(13),' ') AS Notes


FROM observations
LEFT JOIN observations_abondances on observations_abondances.observationfk = observations.observationpk
left join observations_details_oiseaux on observations_details_oiseaux.observationfk = observations.observationpk
LEFT JOIN observateurs ON observateurs.observateurpk = observations.observateurfk
LEFT JOIN groupes ON groupes.groupepk = observateurs.groupefk
LEFT JOIN users ON users.userpk = groupes.userfk
left join zones_changes on zones_changes.zonechangepk = observations.zonechangefk
left join zones on zones.zonepk = zones_changes.zonefk
left join zones_description_oiseaux on zones_description_oiseaux.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 = 1
--and groupes.anneescol = '2022'
--and dico_academies.name in ('LILLE', 'AMIENS')
and users.email not in('vne5@yopmail.com')
--AND dico_etablissements.zipcode LIKE '93%'
--and observations_abondances.abondance > 0

order by
num_observation desc 
