SELECT
   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,
   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.classe_espece as Type_espece,
   observations_abondances.abondance AS Nombre_individus,
   dico_species.speciepk,
   /*
   (case when observations_abondances.abondance is null and observations_abondances.presence_individu is true then 'Oui' 
   		 when observations_abondances.abondance is null and observations_abondances.presence_individu is false then 'Non'		 
   		 else 'NA' END) AS Presence_individus,*/
   -- Informations issues de 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.surface
   AND t='zones'
   AND champ='surface') AS Surface_zone,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_escargots.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_escargots.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_escargots.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_escargots.engrais 
   AND t='zones_description_escargots'
   AND champ='engrais') AS Utilisation_engrais,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_escargots.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_escargots.insecticides
   AND t='zones_description_escargots'
   AND champ='insecticides') AS Utilisation_insecticides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_escargots.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_escargots.herbicides
   AND t='zones_description_escargots'
   AND champ='herbicides') AS Utilisation_herbicides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_escargots.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_escargots.fongicides 
   AND t='zones_description_escargots'
   AND champ='fongicides') AS Utilisation_fongicides,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_escargots.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_escargots.antilimaces
   AND t='zones_description_escargots'
   AND champ='antilimaces') AS Utilisation_antilimaces,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_escargots.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_escargots.bouilliebordelaise
   AND t='zones_description_escargots'
   AND champ='bouilliebordelaise') AS Utilisation_boulliebordelaise,
   -- Description de la planche
   zones_placettes.placettepk AS Numero_planche,
   zones_placettes.largeur AS Largeur_planche,
   zones_placettes.longueur AS Longueur_planche,
   (zones_placettes.largeur*zones_placettes.longueur)/10000 AS Surface_planche,
   (case cast(zones_placettes.environnement -> '0' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_sous_un_arbre_un_buisson,
   (case cast(zones_placettes.environnement -> '1' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_en_terrain_degage,
   (case cast(zones_placettes.environnement -> '2' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_contre_un_mur,
   (case cast(zones_placettes.environnement -> '3' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_contre_la_terre_nue,
   (case cast(zones_placettes.environnement -> '4' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_sur_le_gazon,
   (case cast(zones_placettes.environnement -> '5' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_sur_des_feuilles_mortes,
   (case cast(zones_placettes.environnement -> '6' -> 'value' as bool) WHEN TRUE THEN 'Oui' ELSE 'Non' end) as Posee_sur_un_terrain_en_friche,
   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_placettes on zones_placettes.placettepk = observations_abondances.placettefk
left join zones_description_escargots on zones_description_escargots.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 = 2
--and groupes.anneescol = '2022'
--and dico_academies.name in ('LILLE', 'AMIENS')
and users.email not in('vne5@yopmail.com')
and dico_species.classe_espece not in ('autres-observations')
--AND dico_etablissements.zipcode LIKE '93%'
--and observations_abondances.abondance > 0 or observations_abondances.presence_individu is TRUE


order by 
observations.observationpk desc;
