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,
   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,
   CONCAT (LEFT (zones_placettes.name, 10), RIGHT (zones_placettes.name, 7)) AS Num_quadrat,
   observations_abondances.nom_espece AS espece,
   CASE  WHEN observations_abondances.abondance>0 THEN observations_abondances.abondance else 0 end as nombre_individus,
   observations_abondances.photo_taxon as Photo_taxon,
   observations_details_vdt.photo_zone_environnement as Photo_environnement,
   dico_species.speciepk,
  
-- 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,
   
   CASE WHEN zones_description_vdt.proximite_dechets_organiques = FALSE THEN 'Non'
    	WHEN zones_description_vdt.proximite_dechets_organiques = true THEN 'Oui'
       	ELSE NULL END AS Proximite_dechets_organiques, 
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.type_dechets 
   AND t='zones_description_vdt'
   AND champ='type_dechets') AS Type_dechets_organique,
   zones_description_vdt.distance_zone_echantillonage as Distance_des_dechets,
 
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.usage_zone 
   AND t='zones_description_vdt'
   AND champ='usage_zone') AS Usage_zone,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.type_culture 
   AND t='zones_description_vdt'
   AND champ='type_culture') AS Informations_type_culture,
   zones_description_vdt.usage_zone_autre as Informations_autre_type_usage,
   -- Les rubriques suivantes sont compl?t?es si les choix Grandes cultures, Maraichage dont jardin particulier, autre ont ?t? fait
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.travail_sol_avant 
   AND t='zones_description_vdt'
   AND champ='travail_sol_avant') AS Travail_sol_avant_mise_en_culture,
   zones_description_vdt.frequence_interventions as Nombre_interventions_par_an,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.date_derniere_intervention 
   AND t='observations_details_vdt'
   AND champ='date_derniere_intervention') AS Date_derniere_intervention,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.mode_production 
   AND t='zones_description_vdt'
   AND champ='mode_production') AS Mode_production,
   zones_description_vdt.mode_production_autre as Informations_mode_production_autre,
   
   CASE WHEN zones_description_vdt.fauche_tonte = FALSE THEN 'Non'
    	WHEN zones_description_vdt.fauche_tonte = true THEN 'Oui'
       	ELSE NULL END AS Fauche_tonte, 
   zones_description_vdt.frequence_fauche as Nombre_fauche_par_an,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.date_derniere_fauche 
   AND t='zones_description_vdt'
   AND champ='date_derniere_fauche') AS Date_derniere_fauche,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.residus 
   AND t='zones_description_vdt'
   AND champ='residus') AS Residus,
   
   CASE WHEN zones_description_vdt.paturage = false THEN 'Non'
    	WHEN zones_description_vdt.paturage = true THEN 'Oui'
       	ELSE NULL END AS Presence_paturage,
   zones_description_vdt.type_animaux as Informations_types_animaux,
   
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=zones_description_vdt.utilisation_engrais 
   AND t='zones_description_vdt'
   AND champ='utilisation_engrais') AS Utilisation_engrais,
   zones_description_vdt.frequence_engrais as Nombre_utilisation_engrais_par_an,

-- Donn?es suppl?mentaires collect?es lors de l'observation 

   
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.pluie
   AND t='observations_details_vdt'
   AND champ='pluie') AS Pluie,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.vent
   AND t='observations_details_vdt'
   AND champ='vent') AS Vent,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.ensoleillement 
   AND t='observations_details_vdt'
   AND champ='ensoleillement') AS Ensoleillement,
   observations_details_vdt.temperature as Temperature,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.humidite_sol 
   AND t='observations_details_vdt'
   AND champ='humidite_sol') AS Humidite_sol,
   observations_details_vdt.date_gelee as Date_derniere_gelee,
   observations_details_vdt.date_pluie as Date_derniere_pluie,
  
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.durete_sol 
   AND t='observations_details_vdt'
   AND champ='durete_sol') AS Difficulte_enfoncer_crayon,
   (SELECT label_order
   FROM dico_labels
   WHERE dico_labels.valeur=observations_details_vdt.taupinieres 
   AND t='observations_details_vdt'
   AND champ='taupinieres') AS Taupinieres,
   observations_details_vdt.distance_changement_parcelle as Distance_changement_parcelle,
   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_vdt on observations_details_vdt.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_placettes on zones_placettes.zonechangefk = zones_changes.zonechangepk
left join zones_description_vdt on zones_description_vdt.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 = 4
and zones_placettes.placettepk = observations_abondances.placettefk
-- and groupes.anneescol = '2022'
--and dico_academies.name in ('LILLE', 'AMIENS')
--and users.nom = 'Richard'
and users.email not in('vne5@yopmail.com')
--AND dico_etablissements.zipcode LIKE '93%'

order by
observations.observationpk desc;
