SELECT
   users.userpk as userpk,
   observations.observationpk AS Num_observation,
   dico_structures.structurepk as structurepk,
   dico_structures.type AS type_etablissement,
   observations.date AS Date_observation,
   dico_protocoles.nom_courant as protocole,
   (CASE observations_abondances.nom_espece WHEN 'Anécique tête noire (juvénile)' THEN 'Anécique tête noire'
					   					   WHEN 'Anécique tête rouge (juvénile)' THEN 'Anécique tête rouge'
					   					   WHEN 'Endogé (juvénile)' THEN 'Endogé'
					   					   WHEN 'Epigés (juvénile)' THEN 'Epigé'
					   					   ELSE observations_abondances.nom_espece END) as Espece,
   (CASE WHEN observations.protocolefk=5 and observations_abondances.nom_espece isnull THEN 0
   		 WHEN observations.protocolefk=5 and observations_abondances.nom_espece notnull THEN 1
   		 when observations_abondances.presence_individu=true then 1
   		 when observations_abondances.abondance isnull then 0
   		 else observations_abondances.abondance end) AS Nombre_individus,
   (case when observations_abondances.abondance>0 then 1
   		 when observations_abondances.presence_individu=true then 1
   		 WHEN observations.protocolefk=5 and observations_abondances.nom_espece isnull THEN 0
   		 WHEN observations.protocolefk=5 and observations_abondances.nom_espece notnull THEN 1
   		 else 0 END) as calcul_diversite

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 dico_structures on dico_structures.structurepk = groupes.structurefk
left join dico_protocoles on dico_protocoles.protocolepk = observations.protocolefk


WHERE
users.email not in('vne5@yopmail.com')
--and observations.protocolefk = '14'

order by observations.observationpk
