def get_organisation_stats(organisation_id: int) -> Organisation:
projects = db.session.query(
Project.id,
Project.status).filter(Project.organisation_id == organisation_id)
published_projects = projects.filter(
Project.status == ProjectStatus.PUBLISHED.value)
active_tasks = db.session.query(
Task.id, Task.project_id, Task.task_status).filter(
Task.project_id.in_([i.id for i in published_projects.all()]))
# populate projects stats
projects_dto = OrganizationProjectsStatsDTO()
projects_dto.draft = projects.filter(
Project.status == ProjectStatus.DRAFT.value).count()
projects_dto.published = published_projects.count()
projects_dto.archived = projects.filter(
Project.status == ProjectStatus.ARCHIVED.value).count()
# populate tasks stats
tasks_dto = OrganizationTasksStatsDTO()
tasks_dto.ready = active_tasks.filter(
Task.task_status == TaskStatus.READY.value).count()
tasks_dto.locked_for_mapping = active_tasks.filter(
Task.task_status == TaskStatus.LOCKED_FOR_MAPPING.value).count()
tasks_dto.mapped = active_tasks.filter(
Task.task_status == TaskStatus.MAPPED.value).count()
tasks_dto.locked_for_validation = active_tasks.filter(
Task.task_status ==
TaskStatus.LOCKED_FOR_VALIDATION.value).count()
tasks_dto.validated = active_tasks.filter(
Task.task_status == TaskStatus.VALIDATED.value).count()
tasks_dto.invalidated = active_tasks.filter(
Task.task_status == TaskStatus.INVALIDATED.value).count()
tasks_dto.badimagery = active_tasks.filter(
Task.task_status == TaskStatus.BADIMAGERY.value).count()
# populate and return main dto
stats_dto = OrganizationStatsDTO()
stats_dto.projects = projects_dto
stats_dto.active_tasks = tasks_dto
return stats_dto
def get_homepage_stats(abbrev=True) -> HomePageStatsDTO:
""" Get overall TM stats to give community a feel for progress that's being made """
dto = HomePageStatsDTO()
dto.total_projects = Project.query.count()
dto.mappers_online = (
Task.query.filter(Task.locked_by is not None)
.distinct(Task.locked_by)
.count()
)
dto.total_mappers = User.query.count()
dto.tasks_mapped = Task.query.filter(
Task.task_status.in_((TaskStatus.MAPPED.value, TaskStatus.VALIDATED.value))
).count()
if not abbrev:
dto.total_validators = (
Task.query.filter(Task.task_status == TaskStatus.VALIDATED.value)
.distinct(Task.validated_by)
.count()
)
dto.tasks_validated = Task.query.filter(
Task.task_status == TaskStatus.VALIDATED.value
).count()
total_area_sql = """select coalesce(sum(ST_Area(geometry,true)/1000000),0) as sum
from public.projects as area"""
total_area_result = db.engine.execute(total_area_sql)
dto.total_area = total_area_result.fetchone()["sum"]
tasks_mapped_sql = """select coalesce(sum(ST_Area(geometry, true)/1000000), 0) as sum from public.tasks
where task_status = :task_status"""
tasks_mapped_result = db.engine.execute(
text(tasks_mapped_sql), task_status=TaskStatus.MAPPED.value
)
dto.total_mapped_area = tasks_mapped_result.fetchone()["sum"]
tasks_validated_sql = """select coalesce(sum(ST_Area(geometry, true)/1000000), 0) as sum from public.tasks
where task_status = :task_status"""
tasks_validated_result = db.engine.execute(
text(tasks_validated_sql), task_status=TaskStatus.VALIDATED.value
)
dto.total_validated_area = tasks_validated_result.fetchone()["sum"]
unique_campaigns_sql = "select count(name) as sum from campaigns"
unique_campaigns = db.engine.execute(unique_campaigns_sql).fetchone()["sum"]
linked_campaigns_sql = "select campaigns.name, count(campaign_projects.campaign_id) from campaigns INNER JOIN campaign_projects\
ON campaigns.id=campaign_projects.campaign_id group by campaigns.id"
linked_campaigns_count = db.engine.execute(linked_campaigns_sql).fetchall()
no_campaign_count_sql = "select count(*) as project_count from projects where id not in \
(select distinct project_id from campaign_projects order by project_id)"
no_campaign_count = db.engine.execute(no_campaign_count_sql).fetchone()[
"project_count"
]
for tup in linked_campaigns_count:
campaign_stats = CampaignStatsDTO(tup)
dto.campaigns.append(campaign_stats)
if no_campaign_count:
no_campaign_proj = CampaignStatsDTO(("Unassociated", no_campaign_count))
dto.campaigns.append(no_campaign_proj)
dto.total_campaigns = unique_campaigns
unique_orgs_sql = "select count(name) as sum from organisations"
unique_orgs = db.engine.execute(unique_orgs_sql).fetchone()["sum"]
linked_orgs_sql = "select organisations.name, count(projects.organisation_id) from projects INNER JOIN organisations\
ON organisations.id=projects.organisation_id group by organisations.id"
linked_orgs_count = db.engine.execute(linked_orgs_sql).fetchall()
no_org_project_count = 0
no_org_project_count_sql = "select count(*) as project_count from organisations where id not in \
(select distinct organisation_id from projects order by organisation_id)"
no_org_project_count = db.engine.execute(
no_org_project_count_sql
).fetchone()["project_count"]
for tup in linked_orgs_count:
org_stats = OrganizationStatsDTO(tup)
dto.organizations.append(org_stats)
if no_org_project_count:
no_org_proj = OrganizationStatsDTO(
("Unassociated", no_org_project_count)
)
dto.organisations.append(no_org_proj)
dto.total_organisations = unique_orgs
else:
# Clear null attributes for abbreviated call
clear_attrs = [
"total_validators",
"tasks_validated",
"total_area",
"total_mapped_area",
"total_validated_area",
"campaigns",
"total_campaigns",
"organisations",
"total_organisations",
]
for attr in clear_attrs:
delattr(dto, attr)
return dto
def get_homepage_stats(abbrev=True) -> HomePageStatsDTO:
""" Get overall TM stats to give community a feel for progress that's being made """
dto = HomePageStatsDTO()
dto.total_projects = Project.query.with_entities(
func.count(Project.id)
).scalar()
dto.mappers_online = (
Task.query.with_entities(func.count(Task.locked_by.distinct()))
.filter(Task.locked_by.isnot(None))
.scalar()
)
dto.total_mappers = User.query.with_entities(func.count(User.id)).scalar()
dto.tasks_mapped = (
Task.query.with_entities(func.count())
.filter(
Task.task_status.in_(
(TaskStatus.MAPPED.value, TaskStatus.VALIDATED.value)
)
)
.scalar()
)
if not abbrev:
dto.total_validators = (
Task.query.filter(Task.task_status == TaskStatus.VALIDATED.value)
.distinct(Task.validated_by)
.count()
)
dto.tasks_validated = Task.query.filter(
Task.task_status == TaskStatus.VALIDATED.value
).count()
dto.total_area = Project.query.with_entities(
func.coalesce(func.sum(func.ST_Area(Project.geometry, True) / 1000000))
).scalar()
dto.total_mapped_area = (
Task.query.with_entities(
func.coalesce(func.sum(func.ST_Area(Task.geometry, True) / 1000000))
)
.filter(Task.task_status == TaskStatus.MAPPED.value)
.scalar()
)
dto.total_validated_area = (
Task.query.with_entities(
func.coalesce(func.sum(func.ST_Area(Task.geometry, True) / 1000000))
)
.filter(Task.task_status == TaskStatus.VALIDATED.value)
.scalar()
)
unique_campaigns = Campaign.query.with_entities(
func.count(Campaign.id)
).scalar()
linked_campaigns_count = (
Campaign.query.join(
campaign_projects, Campaign.id == campaign_projects.c.campaign_id
)
.with_entities(
Campaign.name, func.count(campaign_projects.c.campaign_id)
)
.group_by(Campaign.id)
.all()
)
subquery = (
db.session.query(campaign_projects.c.project_id.distinct())
.order_by(campaign_projects.c.project_id)
.subquery()
)
no_campaign_count = (
Project.query.with_entities(func.count())
.filter(~Project.id.in_(subquery))
.scalar()
)
dto.campaigns = [CampaignStatsDTO(row) for row in linked_campaigns_count]
if no_campaign_count:
dto.campaigns.append(
CampaignStatsDTO(("Unassociated", no_campaign_count))
)
dto.total_campaigns = unique_campaigns
unique_orgs = Organisation.query.with_entities(
func.count(Organisation.id)
).scalar()
linked_orgs_count = (
db.session.query(Organisation.name, func.count(Project.organisation_id))
.join(Project.organisation)
.group_by(Organisation.id)
.all()
)
subquery = (
db.session.query(Project.organisation_id.distinct())
.order_by(Project.organisation_id)
.subquery()
)
no_org_project_count = (
Organisation.query.with_entities(func.count())
.filter(~Organisation.id.in_(subquery))
.scalar()
)
dto.organisations = [OrganizationStatsDTO(row) for row in linked_orgs_count]
if no_org_project_count:
no_org_proj = OrganizationStatsDTO(
("Unassociated", no_org_project_count)
)
dto.organisations.append(no_org_proj)
dto.total_organisations = unique_orgs
else:
# Clear null attributes for abbreviated call
clear_attrs = [
"total_validators",
"tasks_validated",
"total_area",
"total_mapped_area",
"total_validated_area",
"campaigns",
"total_campaigns",
"organisations",
"total_organisations",
]
for attr in clear_attrs:
delattr(dto, attr)
return dto