in backend/models/postgis/project.py [0:0]
def get_project_stats(self) -> ProjectStatsDTO:
""" Create Project Stats model for postgis project object"""
project_stats = ProjectStatsDTO()
project_stats.project_id = self.id
project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
project_area_result = db.engine.execute(text(project_area_sql), id=self.id)
project_stats.area = project_area_result.fetchone()["area"]
project_stats.total_mappers = (
db.session.query(User).filter(User.projects_mapped.any(self.id)).count()
)
project_stats.total_tasks = self.total_tasks
project_stats.total_comments = (
db.session.query(ProjectChat)
.filter(ProjectChat.project_id == self.id)
.count()
)
project_stats.percent_mapped = Project.calculate_tasks_percent(
"mapped",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_validated = Project.calculate_tasks_percent(
"validated",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
"bad_imagery",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
project_stats.aoi_centroid = geojson.loads(centroid_geojson)
project_stats.total_time_spent = 0
project_stats.total_mapping_time = 0
project_stats.total_validation_time = 0
project_stats.average_mapping_time = 0
project_stats.average_validation_time = 0
total_mapping_time, total_mapping_tasks = (
db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
),
func.count(TaskHistory.action),
)
.filter(
or_(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
)
)
.filter(TaskHistory.project_id == self.id)
.one()
)
if total_mapping_tasks > 0:
total_mapping_time = total_mapping_time.total_seconds()
project_stats.total_mapping_time = total_mapping_time
project_stats.average_mapping_time = (
total_mapping_time / total_mapping_tasks
)
project_stats.total_time_spent += total_mapping_time
total_validation_time, total_validation_tasks = (
db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
),
func.count(TaskHistory.action),
)
.filter(
or_(
TaskHistory.action == "LOCKED_FOR_VALIDATION",
TaskHistory.action == "AUTO_UNLOCKED_FOR_VALIDATION",
)
)
.filter(TaskHistory.project_id == self.id)
.one()
)
if total_validation_tasks > 0:
total_validation_time = total_validation_time.total_seconds()
project_stats.total_validation_time = total_validation_time
project_stats.average_validation_time = (
total_validation_time / total_validation_tasks
)
project_stats.total_time_spent += total_validation_time
actions = []
if project_stats.average_mapping_time <= 0:
actions.append(TaskStatus.LOCKED_FOR_MAPPING.name)
if project_stats.average_validation_time <= 0:
actions.append(TaskStatus.LOCKED_FOR_VALIDATION.name)
zoom_levels = []
# Check that averages are non-zero.
if len(actions) != 0:
zoom_levels = (
Task.query.with_entities(Task.zoom.distinct())
.filter(Task.project_id == self.id)
.all()
)
zoom_levels = [z[0] for z in zoom_levels]
# Validate project has arbitrary tasks.
is_square = True
if None in zoom_levels:
is_square = False
sq = (
TaskHistory.query.with_entities(
Task.zoom,
TaskHistory.action,
(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
).label("ts"),
)
.filter(Task.is_square == is_square)
.filter(TaskHistory.project_id == Task.project_id)
.filter(TaskHistory.task_id == Task.id)
.filter(TaskHistory.action.in_(actions))
)
if is_square is True:
sq = sq.filter(Task.zoom.in_(zoom_levels))
sq = sq.subquery()
nz = (
db.session.query(sq.c.zoom, sq.c.action, sq.c.ts)
.filter(sq.c.ts > datetime.time(0))
.limit(10000)
.subquery()
)
if project_stats.average_mapping_time <= 0:
mapped_avg = (
db.session.query(nz.c.zoom, (func.avg(nz.c.ts)).label("avg"))
.filter(nz.c.action == TaskStatus.LOCKED_FOR_MAPPING.name)
.group_by(nz.c.zoom)
.all()
)
mapping_time = sum([t.avg.total_seconds() for t in mapped_avg]) / len(
mapped_avg
)
project_stats.average_mapping_time = mapping_time
if project_stats.average_validation_time <= 0:
val_avg = (
db.session.query(nz.c.zoom, (func.avg(nz.c.ts)).label("avg"))
.filter(nz.c.action == TaskStatus.LOCKED_FOR_VALIDATION.name)
.group_by(nz.c.zoom)
.all()
)
validation_time = sum([t.avg.total_seconds() for t in val_avg]) / len(
val_avg
)
project_stats.average_validation_time = validation_time
time_to_finish_mapping = (
self.total_tasks
- (self.tasks_mapped + self.tasks_bad_imagery + self.tasks_validated)
) * project_stats.average_mapping_time
project_stats.time_to_finish_mapping = time_to_finish_mapping
project_stats.time_to_finish_validating = (
self.total_tasks - (self.tasks_validated + self.tasks_bad_imagery)
) * project_stats.average_validation_time
return project_stats