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