def remove_team(
organization_id: int,
team_id: int,
*,
auth=Depends(authorization("organizations:delete_team")),
db: Session = Depends(get_db),
current_user: User = Depends(get_current_user),
):
"""
delete one team and its members by id
"""
organization_in_db = organization.get(db, id=team_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Team not found")
try:
members_in_db = crud.organization.get_members(db, id=team_id)
for member in members_in_db:
current_roles = enforcer.get_roles_for_user_in_domain(
str(member["id"]), str(team_id))
for current_role in current_roles:
enforcer.delete_roles_for_user_in_domain(
str(member["id"]), current_role, str(team_id))
except Exception as e:
logging.error(e)
return False
organization.remove(db, id=team_id)
return True
def delete_teams(
organization_id: int,
*,
auth=Depends(authorization("organizations:delete_team")),
db: Session = Depends(get_db),
current_user: User = Depends(get_current_user),
team_ids_in: List[int],
):
"""
bulk delete teams and their members
"""
teams_out = []
for team_id in team_ids_in:
organization_in_db = organization.get(db, id=team_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Team not found")
try:
members_in_db = crud.organization.get_members(db, id=team_id)
for member in members_in_db:
current_roles = enforcer.get_roles_for_user_in_domain(
str(member["id"]), str(team_id))
for current_role in current_roles:
enforcer.delete_roles_for_user_in_domain(
str(member["id"]), current_role, str(team_id))
except Exception as e:
logging.error(e)
organization.remove(db, id=team_id)
teams_out.append(dict(organization_in_db.as_dict()))
return teams_out
def get_tree_tiles(
z: int,
x: float,
y: float,
*,
organization_id: int,
db: Session = Depends(get_db)
) -> Dict:
"""
Generate Trees MVT for a given organization
"""
organization_in_db = organization.get(db, id=organization_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Organization not found")
sql = f"""
WITH mvtgeom AS (
SELECT
ST_AsMVTGeom(ST_SetSRID(geom, 4326), ST_Transform(ST_TileEnvelope({z},{x},{y}), 4326)) AS geom,
id,
properties
FROM public.tree AS t
WHERE ST_Intersects(ST_SetSRID(geom, 4326), ST_Transform(ST_TileEnvelope({z},{x},{y}), 4326)) AND t.organization_id = {organization_in_db.id}
)
SELECT ST_AsMVT(mvtgeom.*) AS mvt
FROM mvtgeom;
"""
result_proxy = db.execute(sql)
return result_proxy.first()
async def upload_working_area(
file: UploadFile = File(...),
*,
organization_id: int,
auth=Depends(authorization("organizations:upload_working_area")),
db: Session = Depends(get_db),
current_user: User = Depends(get_current_user),
):
"""
Upload a geo file
"""
try:
filename_parts = os.path.splitext(file.filename)
extension = filename_parts[1][1:]
if extension not in ["geojson"]:
raise HTTPException(status_code=415,
detail="File format unsupported")
unique_name = uuid.uuid4()
unique_filename = f"{unique_name}.{extension}"
copy_filename = f"{settings.UPLOADED_FILES_FOLDER}/{unique_filename}"
copy_file = await file.read()
with open(copy_filename, "wb") as f:
f.write(copy_file) # type: ignore
c = fiona.open(copy_filename)
record = next(iter(c))
if not record["geometry"]["type"]:
raise HTTPException(status_code=415, detail="File unsupported")
organization_in_db = organization.get(db, id=organization_id)
if not organization_in_db:
raise HTTPException(status_code=404,
detail="Organization not found")
shape_working_area = shape(record["geometry"])
working_area = shape_working_area.wkt
if record["geometry"]["type"] == "Polygon":
working_area = MultiPolygon([shape_working_area]).wkt
return organization.update(
db,
db_obj=organization_in_db,
obj_in={
"working_area": working_area
},
).to_schema()
finally:
os.remove(copy_filename)
await file.close()
def get_metrics_by_year(
*,
organization_id: int,
year: int,
auth=Depends(authorization("organizations:get_metrics")),
db: Session = Depends(get_db),
) -> Any:
"""
get dashboard metrics from one organization
"""
organization_in_db = organization.get(db, id=organization_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Organization not found")
trees_count = organization_in_db.total_trees
# Asked in specs but no corresponding intervention_type
planted_trees_count = crud.tree.get_planted(
db, organization_id=organization_id, year=year)
logged_trees_count = len(
crud.intervention.get_done_by_type_and_year(
db,
organization_id=organization_id,
intervention_type="felling",
year=year))
scheduled_cost = 0
scheduled_interventions = crud.intervention.get_scheduled_by_year(
db, organization_id=organization_id, year=year)
for i in scheduled_interventions:
scheduled_cost += i.estimated_cost
done_cost = 0
done_interventions = crud.intervention.get_done_by_year(
db, organization_id=organization_id, year=year)
for i in done_interventions:
done_cost += i.estimated_cost
metrics = OrganizationMetrics(
total_tree_count=trees_count,
logged_trees_count=logged_trees_count,
planted_trees_count=planted_trees_count,
done_interventions_cost=done_cost,
scheduled_interventions_cost=scheduled_cost,
)
return metrics
def get_geojson(
organization_id: int,
db: Session = Depends(get_db),
) -> Dict:
"""
generate geojson from organization
"""
organization_in_db = organization.get(db, id=organization_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Organization not found")
sql = f"SELECT * FROM public.tree WHERE organization_id = {organization_in_db.id}"
df = gpd.read_postgis(sql, db.bind)
data = df.to_json()
response = json.loads(data)
return response
def update_organization(
organization_id: int,
*,
auth=Depends(authorization("organizations:update")),
db: Session = Depends(get_db),
current_user: User = Depends(get_current_user),
organization_in: OrganizationUpdate,
) -> Optional[OrganizationModel]:
org = organization.get(db, id=organization_id)
if not org:
raise HTTPException(status_code=404, detail="Organization not found")
organization_in_db = organization.update(
db,
db_obj=org,
obj_in=jsonable_encoder(organization_in, exclude_unset=True),
)
return organization_in_db.to_schema()
def get_working_area(
*,
organization_id: int,
auth=Depends(authorization("organizations:get_working_area")),
db: Session = Depends(get_db),
) -> Any:
"""
get working_area geojson from one organization
"""
organization_in_db = organization.get(db, id=organization_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Organization not found")
if organization_in_db.working_area is None:
raise HTTPException(status_code=404,
detail="Organization working area is empty")
shape = to_shape(organization_in_db.working_area)
return {"type": "Feature", "geometry": mapping(shape), "properties": {}}
def archive_team(
organization_id: int,
team_id: int,
*,
auth=Depends(authorization("organizations:delete_team")),
db: Session = Depends(get_db),
current_user: User = Depends(get_current_user),
):
"""
Archive one team by id
"""
organization_in_db = organization.get(db, id=team_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Team not found")
organization_in_db.archived = True
organization_in_db.archived_at = datetime.now()
db.add(organization_in_db)
db.commit()
db.refresh(organization_in_db)
return organization_in_db
def archive_teams(
organization_id: int,
*,
auth=Depends(authorization("organizations:delete_team")),
db: Session = Depends(get_db),
current_user: User = Depends(get_current_user),
team_ids_in: List[int],
):
"""
Bulk archive teams
"""
teams_out = []
for team_id in team_ids_in:
organization_in_db = organization.get(db, id=team_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Team not found")
organization_in_db.archived = True
organization_in_db.archived_at = datetime.now()
db.add(organization_in_db)
db.commit()
db.refresh(organization_in_db)
teams_out.append(dict(organization_in_db.as_dict()))
return teams_out
def get_geojson(
organization_id: int,
mode: Optional[str] = "geopandas",
current_user: User = Depends(get_optional_current_active_user),
db: Session = Depends(get_db)
) -> Optional[FeatureCollection]:
"""
Get Organization GeoJSON
"""
organization_in_db = organization.get(db, id=organization_id)
if not organization_in_db:
raise HTTPException(status_code=404, detail="Organization not found")
response = None
if mode == 'geopandas':
sql = f"""SELECT t.id as id, t.properties, geom,
COALESCE(json_agg(json_build_object('id', i.id,
'date', i.date,
'intervention_end_date', i.intervention_end_date,
'intervention_start_date', i.intervention_start_date,
'intervenant', i.intervenant,
'required_documents', i.required_documents,
'required_material', i.required_material,
'estimated_cost', i.estimated_cost,
'intervention_type', i."intervention_type",
'properties', i."properties"
)) FILTER (WHERE i.id IS NOT NULL), '[]'
) as interventions
FROM public.tree t LEFT JOIN intervention i
ON i.tree_id = t.id
WHERE t.organization_id = {organization_in_db.id}
GROUP BY t.id, i.id
"""
df = gpd.read_postgis(sql, db.bind)
data = df.to_json()
response = json.loads(data)
else:
sql = f"""SELECT jsonb_build_object('type','FeatureCollection', 'features', jsonb_agg(features.feature))
FROM (
SELECT jsonb_build_object(
'type', 'Feature',
'id', id,
'geometry', ST_AsGeoJSON(geom)::jsonb,
'properties', to_jsonb(selected_trees) - 'geom'
) AS feature
FROM (
SELECT t.id as id, t.properties, geom,
COALESCE(json_agg(json_build_object('id', i.id,
'date', i.date,
'intervention_end_date', i.intervention_end_date,
'intervention_start_date', i.intervention_start_date,
'intervenant', i.intervenant,
'required_documents', i.required_documents,
'required_material', i.required_material,
'estimated_cost', i.estimated_cost,
'intervention_type', i."intervention_type",
'properties', i."properties"
)) FILTER (WHERE i.id IS NOT NULL), '[]'
) as interventions
FROM public.tree t LEFT JOIN intervention i
ON i.tree_id = t.id
WHERE t.organization_id = {organization_in_db.id}
GROUP BY t.id, i.id
)
selected_trees)
features;
"""
res = db.execute(sql)
row = res.first()
response = dict(row.jsonb_build_object)
return response