def genericStatMedias(connection, tab):
tabStat = list()
for i in range(len(tab)):
rang, nomTaxon = list(tab[i].items())[0]
sql = """
SELECT t.nb_obs, t.cd_ref, t.lb_nom, t.nom_vern, t.group2_inpn,
m.url, m.chemin, m.auteur, m.id_media
FROM atlas.vm_taxons t
JOIN atlas.vm_medias m ON m.cd_ref = t.cd_ref AND m.id_type = 1
WHERE t.{} IN :nomTaxon
ORDER BY RANDOM()
LIMIT 10
""".format(rang)
req = connection.execute(text(sql), nomTaxon=tuple(nomTaxon))
tabStat.insert(i, list())
for r in req:
shorterName = None
if r.nom_vern != None:
shorterName = r.nom_vern.split(",")
shorterName = shorterName[0]
temp = {
"cd_ref": r.cd_ref,
"lb_nom": r.lb_nom,
"nom_vern": shorterName,
"path": utils.findPath(r),
"author": r.auteur,
"group2_inpn": utils.deleteAccent(r.group2_inpn),
"nb_obs": r.nb_obs,
"id_media": r.id_media,
}
tabStat[i].append(temp)
if len(tabStat[0]) == 0:
return None
else:
return tabStat
def lastObservations(connection, mylimit, idPhoto):
sql = """
SELECT obs.*,
COALESCE(split_part(tax.nom_vern, ',', 1) || ' | ', '')
|| tax.lb_nom as taxon,
tax.group2_inpn,
medias.url, medias.chemin, medias.id_media
FROM atlas.vm_observations obs
JOIN atlas.vm_taxons tax
ON tax.cd_ref = obs.cd_ref
LEFT JOIN atlas.vm_medias medias
ON medias.cd_ref = obs.cd_ref AND medias.id_type = :thisidphoto
WHERE obs.dateobs >= (CURRENT_TIMESTAMP - INTERVAL :thislimit)
ORDER BY obs.dateobs DESC """
observations = connection.execute(text(sql),
thislimit=mylimit,
thisidphoto=idPhoto)
obsList = list()
for o in observations:
temp = dict(o)
temp.pop("the_geom_point", None)
temp["geojson_point"] = json.loads(o.geojson_point or "{}")
temp["dateobs"] = str(o.dateobs)
temp["group2_inpn"] = utils.deleteAccent(o.group2_inpn)
temp["pathImg"] = utils.findPath(o)
obsList.append(temp)
return obsList
def lastObservationsMailles(connection, mylimit, idPhoto):
sql = """
SELECT obs.*,
tax.lb_nom, tax.nom_vern, tax.group2_inpn,
o.dateobs, o.altitude_retenue,
medias.url, medias.chemin, medias.id_media
FROM atlas.vm_observations_mailles obs
JOIN atlas.vm_taxons tax ON tax.cd_ref = obs.cd_ref
JOIN atlas.vm_observations o ON o.id_observation=obs.id_observation
LEFT JOIN atlas.vm_medias medias
ON medias.cd_ref = obs.cd_ref AND medias.id_type = :thisID
WHERE o.dateobs >= (CURRENT_TIMESTAMP - INTERVAL :thislimit)
ORDER BY o.dateobs DESC
"""
observations = connection.execute(text(sql),
thislimit=mylimit,
thisID=idPhoto)
obsList = list()
for o in observations:
if o.nom_vern:
inter = o.nom_vern.split(",")
taxon = inter[0] + " | " + o.lb_nom
else:
taxon = o.lb_nom
temp = {
"id_observation": o.id_observation,
"id_maille": o.id_maille,
"cd_ref": o.cd_ref,
"dateobs": str(o.dateobs),
"altitude_retenue": o.altitude_retenue,
"taxon": taxon,
"geojson_maille": json.loads(o.geojson_maille),
"group2_inpn": deleteAccent(o.group2_inpn),
"pathImg": findPath(o),
"id_media": o.id_media,
}
obsList.append(temp)
return obsList