import bleach import datetime import geojson import json from enum import Enum from flask import current_app from sqlalchemy.types import Float, Text from sqlalchemy import desc, cast, func, distinct from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound from sqlalchemy.orm.session import make_transient from geoalchemy2 import Geometry from backend import db from typing import List from backend.models.dtos.mapping_dto import TaskDTO, TaskHistoryDTO from backend.models.dtos.validator_dto import MappedTasksByUser, MappedTasks from backend.models.dtos.project_dto import ( ProjectComment, ProjectCommentsDTO, LockedTasksForUser, ) from backend.models.dtos.mapping_issues_dto import TaskMappingIssueDTO from backend.models.postgis.statuses import TaskStatus, MappingLevel from backend.models.postgis.user import User from backend.models.postgis.utils import ( InvalidData, InvalidGeoJson, ST_GeomFromGeoJSON, ST_SetSRID, timestamp, parse_duration, NotFound, ) from backend.models.postgis.task_annotation import TaskAnnotation class TaskAction(Enum): """ Describes the possible actions that can happen to to a task, that we'll record history for """ LOCKED_FOR_MAPPING = 1 LOCKED_FOR_VALIDATION = 2 STATE_CHANGE = 3 COMMENT = 4 AUTO_UNLOCKED_FOR_MAPPING = 5 AUTO_UNLOCKED_FOR_VALIDATION = 6 class TaskInvalidationHistory(db.Model): """ Describes the most recent history of task invalidation and subsequent validation """ __tablename__ = "task_invalidation_history" id = db.Column(db.Integer, primary_key=True) project_id = db.Column(db.Integer, db.ForeignKey("projects.id"), nullable=False) task_id = db.Column(db.Integer, nullable=False) is_closed = db.Column(db.Boolean, default=False) mapper_id = db.Column(db.BigInteger, db.ForeignKey("users.id", name="fk_mappers")) mapped_date = db.Column(db.DateTime) invalidator_id = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_invalidators") ) invalidated_date = db.Column(db.DateTime) invalidation_history_id = db.Column( db.Integer, db.ForeignKey("task_history.id", name="fk_invalidation_history") ) validator_id = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_validators") ) validated_date = db.Column(db.DateTime) updated_date = db.Column(db.DateTime, default=timestamp) __table_args__ = ( db.ForeignKeyConstraint( [task_id, project_id], ["tasks.id", "tasks.project_id"], name="fk_tasks" ), db.Index("idx_task_validation_history_composite", "task_id", "project_id"), db.Index( "idx_task_validation_mapper_status_composite", "invalidator_id", "is_closed" ), db.Index( "idx_task_validation_mapper_status_composite", "mapper_id", "is_closed" ), {}, ) def __init__(self, project_id, task_id): self.project_id = project_id self.task_id = task_id self.is_closed = False def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @staticmethod def get_open_for_task(project_id, task_id): return TaskInvalidationHistory.query.filter_by( task_id=task_id, project_id=project_id, is_closed=False ).one_or_none() @staticmethod def close_all_for_task(project_id, task_id): TaskInvalidationHistory.query.filter_by( task_id=task_id, project_id=project_id, is_closed=False ).update({"is_closed": True}) @staticmethod def record_invalidation(project_id, task_id, invalidator_id, history): # Invalidation always kicks off a new entry for a task, so close any existing ones. TaskInvalidationHistory.close_all_for_task(project_id, task_id) last_mapped = TaskHistory.get_last_mapped_action(project_id, task_id) if last_mapped is None: return entry = TaskInvalidationHistory(project_id, task_id) entry.invalidation_history_id = history.id entry.mapper_id = last_mapped.user_id entry.mapped_date = last_mapped.action_date entry.invalidator_id = invalidator_id entry.invalidated_date = history.action_date entry.updated_date = timestamp() db.session.add(entry) @staticmethod def record_validation(project_id, task_id, validator_id, history): entry = TaskInvalidationHistory.get_open_for_task(project_id, task_id) # If no open invalidation to update, then nothing to do if entry is None: return last_mapped = TaskHistory.get_last_mapped_action(project_id, task_id) entry.mapper_id = last_mapped.user_id entry.mapped_date = last_mapped.action_date entry.validator_id = validator_id entry.validated_date = history.action_date entry.is_closed = True entry.updated_date = timestamp() class TaskMappingIssue(db.Model): """Describes an issue (along with an occurrence count) with a task mapping that contributed to invalidation of the task""" __tablename__ = "task_mapping_issues" id = db.Column(db.Integer, primary_key=True) task_history_id = db.Column( db.Integer, db.ForeignKey("task_history.id"), nullable=False, index=True ) issue = db.Column(db.String, nullable=False) mapping_issue_category_id = db.Column( db.Integer, db.ForeignKey("mapping_issue_categories.id", name="fk_issue_category"), nullable=False, ) count = db.Column(db.Integer, nullable=False) def __init__(self, issue, count, mapping_issue_category_id, task_history_id=None): self.task_history_id = task_history_id self.issue = issue self.count = count self.mapping_issue_category_id = mapping_issue_category_id def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() def as_dto(self): issue_dto = TaskMappingIssueDTO() issue_dto.category_id = self.mapping_issue_category_id issue_dto.name = self.issue issue_dto.count = self.count return issue_dto def __repr__(self): return "{0}: {1}".format(self.issue, self.count) class TaskHistory(db.Model): """ Describes the history associated with a task """ __tablename__ = "task_history" id = db.Column(db.Integer, primary_key=True) project_id = db.Column(db.Integer, db.ForeignKey("projects.id"), index=True) task_id = db.Column(db.Integer, nullable=False) action = db.Column(db.String, nullable=False) action_text = db.Column(db.String) action_date = db.Column(db.DateTime, nullable=False, default=timestamp) user_id = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users"), index=True, nullable=False, ) invalidation_history = db.relationship( TaskInvalidationHistory, lazy="dynamic", cascade="all" ) actioned_by = db.relationship(User) task_mapping_issues = db.relationship(TaskMappingIssue, cascade="all") __table_args__ = ( db.ForeignKeyConstraint( [task_id, project_id], ["tasks.id", "tasks.project_id"], name="fk_tasks" ), db.Index("idx_task_history_composite", "task_id", "project_id"), db.Index("idx_task_history_project_id_user_id", "user_id", "project_id"), {}, ) def __init__(self, task_id, project_id, user_id): self.task_id = task_id self.project_id = project_id self.user_id = user_id def set_task_locked_action(self, task_action: TaskAction): if task_action not in [ TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION, ]: raise ValueError("Invalid Action") self.action = task_action.name def set_comment_action(self, comment): self.action = TaskAction.COMMENT.name clean_comment = bleach.clean( comment ) # Bleach input to ensure no nefarious script tags etc self.action_text = clean_comment def set_state_change_action(self, new_state): self.action = TaskAction.STATE_CHANGE.name self.action_text = new_state.name def set_auto_unlock_action(self, task_action: TaskAction): self.action = task_action.name def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @staticmethod def update_task_locked_with_duration( task_id: int, project_id: int, lock_action: TaskStatus, user_id: int ): """ Calculates the duration a task was locked for and sets it on the history record :param task_id: Task in scope :param project_id: Project ID in scope :param lock_action: The lock action, either Mapping or Validation :param user_id: Logged in user updating the task :return: """ try: last_locked = TaskHistory.query.filter_by( task_id=task_id, project_id=project_id, action=lock_action.name, action_text=None, user_id=user_id, ).one() except NoResultFound: # We suspect there's some kind or race condition that is occasionally deleting history records # prior to user unlocking task. Most likely stemming from auto-unlock feature. However, given that # we're trying to update a row that doesn't exist, it's better to return without doing anything # rather than showing the user an error that they can't fix return except MultipleResultsFound: # Again race conditions may mean we have multiple rows within the Task History. Here we attempt to # remove the oldest duplicate rows, and update the newest on the basis that this was the last action # the user was attempting to make. TaskHistory.remove_duplicate_task_history_rows( task_id, project_id, lock_action, user_id ) # Now duplicate is removed, we recursively call ourself to update the duration on the remaining row TaskHistory.update_task_locked_with_duration( task_id, project_id, lock_action, user_id ) return duration_task_locked = datetime.datetime.utcnow() - last_locked.action_date # Cast duration to isoformat for later transmission via api last_locked.action_text = ( (datetime.datetime.min + duration_task_locked).time().isoformat() ) db.session.commit() @staticmethod def remove_duplicate_task_history_rows( task_id: int, project_id: int, lock_action: TaskStatus, user_id: int ): """Method used in rare cases where we have duplicate task history records for a given action by a user This method will remove the oldest duplicate record, on the basis that the newest record was the last action the user was attempting to perform """ dupe = ( TaskHistory.query.filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action == lock_action.name, TaskHistory.user_id == user_id, ) .order_by(TaskHistory.id.asc()) .first() ) dupe.delete() @staticmethod def update_expired_and_locked_actions( project_id: int, task_id: int, expiry_date: datetime, action_text: str ): """ Sets auto unlock state to all not finished actions, that are older then the expiry date. Action is considered as a not finished, when it is in locked state and doesn't have action text :param project_id: Project ID in scope :param task_id: Task in scope :param expiry_date: Action created before this date is treated as expired :param action_text: Text which will be set for all changed actions :return: """ all_expired = TaskHistory.query.filter( TaskHistory.task_id == task_id, TaskHistory.project_id == project_id, TaskHistory.action_text.is_(None), TaskHistory.action.in_( [ TaskAction.LOCKED_FOR_VALIDATION.name, TaskAction.LOCKED_FOR_MAPPING.name, ] ), TaskHistory.action_date <= expiry_date, ).all() for task_history in all_expired: unlock_action = ( TaskAction.AUTO_UNLOCKED_FOR_MAPPING if task_history.action == "LOCKED_FOR_MAPPING" else TaskAction.AUTO_UNLOCKED_FOR_VALIDATION ) task_history.set_auto_unlock_action(unlock_action) task_history.action_text = action_text db.session.commit() @staticmethod def get_all_comments(project_id: int) -> ProjectCommentsDTO: """ Gets all comments for the supplied project_id""" comments = ( db.session.query( TaskHistory.task_id, TaskHistory.action_date, TaskHistory.action_text, User.username, ) .join(User) .filter( TaskHistory.project_id == project_id, TaskHistory.action == TaskAction.COMMENT.name, ) .all() ) comments_dto = ProjectCommentsDTO() for comment in comments: dto = ProjectComment() dto.comment = comment.action_text dto.comment_date = comment.action_date dto.user_name = comment.username dto.task_id = comment.task_id comments_dto.comments.append(dto) return comments_dto @staticmethod def get_last_status(project_id: int, task_id: int, for_undo: bool = False): """ Get the status the task was set to the last time the task had a STATUS_CHANGE""" result = ( db.session.query(TaskHistory.action_text) .filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action == TaskAction.STATE_CHANGE.name, ) .order_by(TaskHistory.action_date.desc()) .all() ) if not result: return TaskStatus.READY # No result so default to ready status if len(result) == 1 and for_undo: # We're looking for the previous status, however, there isn't any so we'll return Ready return TaskStatus.READY if for_undo and result[0][0] in [ TaskStatus.MAPPED.name, TaskStatus.BADIMAGERY.name, ]: # We need to return a READY when last status of the task is badimagery or mapped. return TaskStatus.READY if for_undo: # Return the second last status which was status the task was previously set to return TaskStatus[result[1][0]] else: return TaskStatus[result[0][0]] @staticmethod def get_last_action(project_id: int, task_id: int): """Gets the most recent task history record for the task""" return ( TaskHistory.query.filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id ) .order_by(TaskHistory.action_date.desc()) .first() ) @staticmethod def get_last_action_of_type( project_id: int, task_id: int, allowed_task_actions: list ): """Gets the most recent task history record having provided TaskAction""" return ( TaskHistory.query.filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action.in_(allowed_task_actions), ) .order_by(TaskHistory.action_date.desc()) .first() ) @staticmethod def get_last_locked_action(project_id: int, task_id: int): """Gets the most recent task history record with locked action for the task""" return TaskHistory.get_last_action_of_type( project_id, task_id, [TaskAction.LOCKED_FOR_MAPPING.name, TaskAction.LOCKED_FOR_VALIDATION.name], ) @staticmethod def get_last_locked_or_auto_unlocked_action(project_id: int, task_id: int): """Gets the most recent task history record with locked or auto unlocked action for the task""" return TaskHistory.get_last_action_of_type( project_id, task_id, [ TaskAction.LOCKED_FOR_MAPPING.name, TaskAction.LOCKED_FOR_VALIDATION.name, TaskAction.AUTO_UNLOCKED_FOR_MAPPING.name, TaskAction.AUTO_UNLOCKED_FOR_VALIDATION.name, ], ) def get_last_mapped_action(project_id: int, task_id: int): """Gets the most recent mapped action, if any, in the task history""" return ( db.session.query(TaskHistory) .filter( TaskHistory.project_id == project_id, TaskHistory.task_id == task_id, TaskHistory.action == TaskAction.STATE_CHANGE.name, TaskHistory.action_text.in_( [TaskStatus.BADIMAGERY.name, TaskStatus.MAPPED.name] ), ) .order_by(TaskHistory.action_date.desc()) .first() ) class Task(db.Model): """ Describes an individual mapping Task """ __tablename__ = "tasks" # Table has composite PK on (id and project_id) id = db.Column(db.Integer, primary_key=True) project_id = db.Column( db.Integer, db.ForeignKey("projects.id"), index=True, primary_key=True ) x = db.Column(db.Integer) y = db.Column(db.Integer) zoom = db.Column(db.Integer) extra_properties = db.Column(db.Unicode) # Tasks need to be split differently if created from an arbitrary grid or were clipped to the edge of the AOI is_square = db.Column(db.Boolean, default=True) geometry = db.Column(Geometry("MULTIPOLYGON", srid=4326)) task_status = db.Column(db.Integer, default=TaskStatus.READY.value) locked_by = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users_locked"), index=True ) mapped_by = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users_mapper"), index=True ) validated_by = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users_validator"), index=True ) # Mapped objects task_history = db.relationship( TaskHistory, cascade="all", order_by=desc(TaskHistory.action_date) ) task_annotations = db.relationship(TaskAnnotation, cascade="all") lock_holder = db.relationship(User, foreign_keys=[locked_by]) mapper = db.relationship(User, foreign_keys=[mapped_by]) def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() def update(self): """ Updates the DB with the current state of the Task """ db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @classmethod def from_geojson_feature(cls, task_id, task_feature): """ Constructs and validates a task from a GeoJson feature object :param task_id: Unique ID for the task :param task_feature: A geojson feature object :raises InvalidGeoJson, InvalidData """ if type(task_feature) is not geojson.Feature: raise InvalidGeoJson("Task: Invalid GeoJson should be a feature") task_geometry = task_feature.geometry if type(task_geometry) is not geojson.MultiPolygon: raise InvalidGeoJson("Task: Geometry must be a MultiPolygon") is_valid_geojson = geojson.is_valid(task_geometry) if is_valid_geojson["valid"] == "no": raise InvalidGeoJson( f"Task: Invalid MultiPolygon - {is_valid_geojson['message']}" ) task = cls() try: task.x = task_feature.properties["x"] task.y = task_feature.properties["y"] task.zoom = task_feature.properties["zoom"] task.is_square = task_feature.properties["isSquare"] except KeyError as e: raise InvalidData(f"Task: Expected property not found: {str(e)}") if "extra_properties" in task_feature.properties: task.extra_properties = json.dumps( task_feature.properties["extra_properties"] ) task.id = task_id task_geojson = geojson.dumps(task_geometry) task.geometry = ST_SetSRID(ST_GeomFromGeoJSON(task_geojson), 4326) return task @staticmethod def get(task_id: int, project_id: int): """ Gets specified task :param task_id: task ID in scope :param project_id: project ID in scope :return: Task if found otherwise None """ # LIKELY PROBLEM AREA return Task.query.filter_by(id=task_id, project_id=project_id).one_or_none() @staticmethod def get_tasks(project_id: int, task_ids: List[int]): """ Get all tasks that match supplied list """ return Task.query.filter( Task.project_id == project_id, Task.id.in_(task_ids) ).all() @staticmethod def get_all_tasks(project_id: int): """ Get all tasks for a given project """ return Task.query.filter(Task.project_id == project_id).all() @staticmethod def auto_unlock_delta(): return parse_duration(current_app.config["TASK_AUTOUNLOCK_AFTER"]) @staticmethod def auto_unlock_tasks(project_id: int): """Unlock all tasks locked for longer than the auto-unlock delta""" expiry_delta = Task.auto_unlock_delta() lock_duration = (datetime.datetime.min + expiry_delta).time().isoformat() expiry_date = datetime.datetime.utcnow() - expiry_delta old_tasks = ( db.session.query(Task.id) .filter(Task.id == TaskHistory.task_id) .filter(Task.project_id == TaskHistory.project_id) .filter(Task.task_status.in_([1, 3])) .filter( TaskHistory.action.in_(["LOCKED_FOR_VALIDATION", "LOCKED_FOR_MAPPING"]) ) .filter(TaskHistory.action_text.is_(None)) .filter(Task.project_id == project_id) .filter(TaskHistory.action_date <= str(expiry_date)) ) if old_tasks.count() == 0: # no tasks older than the delta found, return without further processing return for old_task in old_tasks: task = Task.get(old_task[0], project_id) task.auto_unlock_expired_tasks(expiry_date, lock_duration) def auto_unlock_expired_tasks(self, expiry_date, lock_duration): """Unlock all tasks locked before expiry date. Clears task lock if needed""" TaskHistory.update_expired_and_locked_actions( self.project_id, self.id, expiry_date, lock_duration ) last_action = TaskHistory.get_last_locked_or_auto_unlocked_action( self.project_id, self.id ) if last_action.action in [ "AUTO_UNLOCKED_FOR_MAPPING", "AUTO_UNLOCKED_FOR_VALIDATION", ]: self.clear_lock() def is_mappable(self): """ Determines if task in scope is in suitable state for mapping """ if TaskStatus(self.task_status) not in [ TaskStatus.READY, TaskStatus.INVALIDATED, ]: return False return True def set_task_history( self, action, user_id, comment=None, new_state=None, mapping_issues=None ): """ Sets the task history for the action that the user has just performed :param task: Task in scope :param user_id: ID of user performing the action :param action: Action the user has performed :param comment: Comment user has added :param new_state: New state of the task :param mapping_issues: Identified issues leading to invalidation """ history = TaskHistory(self.id, self.project_id, user_id) if action in [TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION]: history.set_task_locked_action(action) elif action == TaskAction.COMMENT: history.set_comment_action(comment) elif action == TaskAction.STATE_CHANGE: history.set_state_change_action(new_state) elif action in [ TaskAction.AUTO_UNLOCKED_FOR_MAPPING, TaskAction.AUTO_UNLOCKED_FOR_VALIDATION, ]: history.set_auto_unlock_action(action) if mapping_issues is not None: history.task_mapping_issues = mapping_issues self.task_history.append(history) return history def lock_task_for_mapping(self, user_id: int): self.set_task_history(TaskAction.LOCKED_FOR_MAPPING, user_id) self.task_status = TaskStatus.LOCKED_FOR_MAPPING.value self.locked_by = user_id self.update() def lock_task_for_validating(self, user_id: int): self.set_task_history(TaskAction.LOCKED_FOR_VALIDATION, user_id) self.task_status = TaskStatus.LOCKED_FOR_VALIDATION.value self.locked_by = user_id self.update() def reset_task(self, user_id: int): expiry_delta = Task.auto_unlock_delta() lock_duration = (datetime.datetime.min + expiry_delta).time().isoformat() if TaskStatus(self.task_status) in [ TaskStatus.LOCKED_FOR_MAPPING, TaskStatus.LOCKED_FOR_VALIDATION, ]: self.record_auto_unlock(lock_duration) self.set_task_history(TaskAction.STATE_CHANGE, user_id, None, TaskStatus.READY) self.mapped_by = None self.validated_by = None self.locked_by = None self.task_status = TaskStatus.READY.value self.update() def clear_task_lock(self): """ Unlocks task in scope in the database. Clears the lock as though it never happened. No history of the unlock is recorded. :return: """ # clear the lock action for the task in the task history last_action = TaskHistory.get_last_locked_action(self.project_id, self.id) last_action.delete() # Set locked_by to null and status to last status on task self.clear_lock() def record_auto_unlock(self, lock_duration): locked_user = self.locked_by last_action = TaskHistory.get_last_locked_action(self.project_id, self.id) next_action = ( TaskAction.AUTO_UNLOCKED_FOR_MAPPING if last_action.action == "LOCKED_FOR_MAPPING" else TaskAction.AUTO_UNLOCKED_FOR_VALIDATION ) self.clear_task_lock() # Add AUTO_UNLOCKED action in the task history auto_unlocked = self.set_task_history(action=next_action, user_id=locked_user) auto_unlocked.action_text = lock_duration self.update() def unlock_task( self, user_id, new_state=None, comment=None, undo=False, issues=None ): """ Unlock task and ensure duration task locked is saved in History """ if comment: self.set_task_history( action=TaskAction.COMMENT, comment=comment, user_id=user_id, mapping_issues=issues, ) history = self.set_task_history( action=TaskAction.STATE_CHANGE, new_state=new_state, user_id=user_id, mapping_issues=issues, ) if ( new_state in [TaskStatus.MAPPED, TaskStatus.BADIMAGERY] and TaskStatus(self.task_status) != TaskStatus.LOCKED_FOR_VALIDATION ): # Don't set mapped if state being set back to mapped after validation self.mapped_by = user_id elif new_state == TaskStatus.VALIDATED: TaskInvalidationHistory.record_validation( self.project_id, self.id, user_id, history ) self.validated_by = user_id elif new_state == TaskStatus.INVALIDATED: TaskInvalidationHistory.record_invalidation( self.project_id, self.id, user_id, history ) self.mapped_by = None self.validated_by = None if not undo: # Using a slightly evil side effect of Actions and Statuses having the same name here :) TaskHistory.update_task_locked_with_duration( self.id, self.project_id, TaskStatus(self.task_status), user_id ) self.task_status = new_state.value self.locked_by = None self.update() def reset_lock(self, user_id, comment=None): """Removes a current lock from a task, resets to last status and updates history with duration of lock""" if comment: self.set_task_history( action=TaskAction.COMMENT, comment=comment, user_id=user_id ) # Using a slightly evil side effect of Actions and Statuses having the same name here :) TaskHistory.update_task_locked_with_duration( self.id, self.project_id, TaskStatus(self.task_status), user_id ) self.clear_lock() def clear_lock(self): """ Resets to last status and removes current lock from a task """ self.task_status = TaskHistory.get_last_status(self.project_id, self.id).value self.locked_by = None self.update() @staticmethod def get_tasks_as_geojson_feature_collection( project_id, task_ids_str: str = None, order_by: str = None, order_by_type: str = "ASC", status: int = None, ): """ Creates a geoJson.FeatureCollection object for tasks related to the supplied project ID :param project_id: Owning project ID :order_by: sorting option: available values update_date and building_area_diff :status: task status id to filter by :return: geojson.FeatureCollection """ # subquery = ( # db.session.query(func.max(TaskHistory.action_date)) # .filter( # Task.id == TaskHistory.task_id, # Task.project_id == TaskHistory.project_id, # ) # .correlate(Task) # .group_by(Task.id) # .label("update_date") # ) query = db.session.query( Task.id, Task.x, Task.y, Task.zoom, Task.is_square, Task.task_status, Task.geometry.ST_AsGeoJSON().label("geojson"), Task.locked_by, # subquery, ) filters = [Task.project_id == project_id] if task_ids_str: task_ids = map(int, task_ids_str.split(",")) tasks = Task.get_tasks(project_id, task_ids) if not tasks or len(tasks) == 0: raise NotFound() else: tasks_filters = [task.id for task in tasks] filters = [Task.project_id == project_id, Task.id.in_(tasks_filters)] else: tasks = Task.get_all_tasks(project_id) if not tasks or len(tasks) == 0: raise NotFound() if status: filters.append(Task.task_status == status) if order_by == "effort_prediction": query = query.outerjoin(TaskAnnotation).filter(*filters) if order_by_type == "DESC": query = query.order_by( desc( cast( cast(TaskAnnotation.properties["building_area_diff"], Text), Float, ) ) ) else: query = query.order_by( cast( cast(TaskAnnotation.properties["building_area_diff"], Text), Float, ) ) # elif order_by == "last_updated": # if order_by_type == "DESC": # query = query.filter(*filters).order_by(desc("update_date")) # else: # query = query.filter(*filters).order_by("update_date") else: query = query.filter(*filters) project_tasks = query.all() tasks_features = [] for task in project_tasks: task_geometry = geojson.loads(task.geojson) task_properties = dict( taskId=task.id, taskX=task.x, taskY=task.y, taskZoom=task.zoom, taskIsSquare=task.is_square, taskStatus=TaskStatus(task.task_status).name, lockedBy=task.locked_by, ) feature = geojson.Feature( geometry=task_geometry, properties=task_properties ) tasks_features.append(feature) return geojson.FeatureCollection(tasks_features) @staticmethod def get_tasks_as_geojson_feature_collection_no_geom(project_id): """ Creates a geoJson.FeatureCollection object for all tasks related to the supplied project ID without geometry :param project_id: Owning project ID :return: geojson.FeatureCollection """ project_tasks = ( db.session.query( Task.id, Task.x, Task.y, Task.zoom, Task.is_square, Task.task_status ) .filter(Task.project_id == project_id) .all() ) tasks_features = [] for task in project_tasks: task_properties = dict( taskId=task.id, taskX=task.x, taskY=task.y, taskZoom=task.zoom, taskIsSquare=task.is_square, taskStatus=TaskStatus(task.task_status).name, ) feature = geojson.Feature(properties=task_properties) tasks_features.append(feature) return geojson.FeatureCollection(tasks_features) @staticmethod def get_mapped_tasks_by_user(project_id: int): """ Gets all mapped tasks for supplied project grouped by user""" results = ( db.session.query( User.username, User.mapping_level, func.count(distinct(Task.id)), func.json_agg(distinct(Task.id)), func.max(TaskHistory.action_date), User.date_registered, User.last_validation_date, ) .filter(Task.project_id == TaskHistory.project_id) .filter(Task.id == TaskHistory.task_id) .filter(Task.mapped_by == User.id) .filter(Task.project_id == project_id) .filter(Task.task_status == 2) .filter(TaskHistory.action_text == "MAPPED") .group_by( User.username, User.mapping_level, User.date_registered, User.last_validation_date, ) ) mapped_tasks_dto = MappedTasks() for row in results: user_mapped = MappedTasksByUser() user_mapped.username = row[0] user_mapped.mapping_level = MappingLevel(row[1]).name user_mapped.mapped_task_count = row[2] user_mapped.tasks_mapped = row[3] user_mapped.last_seen = row[4] user_mapped.date_registered = row[5] user_mapped.last_validation_date = row[6] mapped_tasks_dto.mapped_tasks.append(user_mapped) return mapped_tasks_dto @staticmethod def get_max_task_id_for_project(project_id: int): """Gets the nights task id currently in use on a project""" result = ( db.session.query(func.max(Task.id)) .filter(Task.project_id == project_id) .group_by(Task.project_id) ) if result.count() == 0: raise NotFound() for row in result: return row[0] def as_dto( self, task_history: List[TaskHistoryDTO] = [], last_updated: datetime.datetime = None, comments: int = None, ): """Just converts to a TaskDTO""" task_dto = TaskDTO() task_dto.task_id = self.id task_dto.project_id = self.project_id task_dto.task_status = TaskStatus(self.task_status).name task_dto.lock_holder = self.lock_holder.username if self.lock_holder else None task_dto.task_history = task_history task_dto.last_updated = last_updated if last_updated else None task_dto.auto_unlock_seconds = Task.auto_unlock_delta().total_seconds() task_dto.comments_number = comments if type(comments) == int else None return task_dto def as_dto_with_instructions(self, preferred_locale: str = "en") -> TaskDTO: """Get dto with any task instructions""" task_history = [] for action in self.task_history: history = TaskHistoryDTO() history.history_id = action.id history.action = action.action history.action_text = action.action_text history.action_date = action.action_date history.action_by = ( action.actioned_by.username if action.actioned_by else None ) history.picture_url = ( action.actioned_by.picture_url if action.actioned_by else None ) if action.task_mapping_issues: history.issues = [ issue.as_dto() for issue in action.task_mapping_issues ] task_history.append(history) last_updated = None if len(task_history) > 0: last_updated = task_history[0].action_date task_dto = self.as_dto(task_history, last_updated=last_updated) per_task_instructions = self.get_per_task_instructions(preferred_locale) # If we don't have instructions in preferred locale try again for default locale task_dto.per_task_instructions = ( per_task_instructions if per_task_instructions else self.get_per_task_instructions(self.projects.default_locale) ) annotations = self.get_per_task_annotations() task_dto.task_annotations = annotations if annotations else [] return task_dto def get_per_task_annotations(self): result = [ta.get_dto() for ta in self.task_annotations] return result def get_per_task_instructions(self, search_locale: str) -> str: """ Gets any per task instructions attached to the project """ project_info = self.projects.project_info.all() for info in project_info: if info.locale == search_locale: return self.format_per_task_instructions(info.per_task_instructions) def format_per_task_instructions(self, instructions) -> str: """ Format instructions by looking for X, Y, Z tokens and replacing them with the task values """ if not instructions: return "" # No instructions so return empty string properties = {} if self.x: properties["x"] = str(self.x) if self.y: properties["y"] = str(self.y) if self.zoom: properties["z"] = str(self.zoom) if self.extra_properties: properties.update(json.loads(self.extra_properties)) try: instructions = instructions.format(**properties) except KeyError: pass return instructions def copy_task_history(self) -> list: copies = [] for entry in self.task_history: db.session.expunge(entry) make_transient(entry) entry.id = None entry.task_id = None db.session.add(entry) copies.append(entry) return copies def get_locked_tasks_for_user(user_id: int): """ Gets tasks on project owned by specified user id""" tasks = Task.query.filter_by(locked_by=user_id) tasks_dto = LockedTasksForUser() for task in tasks: tasks_dto.locked_tasks.append(task.id) tasks_dto.project = task.project_id tasks_dto.task_status = TaskStatus(task.task_status).name return tasks_dto def get_locked_tasks_details_for_user(user_id: int): """ Gets tasks on project owned by specified user id""" tasks = Task.query.filter_by(locked_by=user_id) locked_tasks = [task for task in tasks] return locked_tasks