def get_mapped_projects(user_id: int, preferred_locale: str) -> UserMappedProjectsDTO: """ Get all projects a user has mapped on """ from backend.models.postgis.task import Task from backend.models.postgis.project import Project query = db.session.query(func.unnest( User.projects_mapped)).filter_by(id=user_id) query_validated = (db.session.query( Task.project_id.label("project_id"), func.count(Task.validated_by).label("validated"), ).filter(Task.project_id.in_(query)).filter_by( validated_by=user_id).group_by(Task.project_id, Task.validated_by).subquery()) query_mapped = (db.session.query( Task.project_id.label("project_id"), func.count(Task.mapped_by).label("mapped"), ).filter(Task.project_id.in_(query)).filter_by( mapped_by=user_id).group_by(Task.project_id, Task.mapped_by).subquery()) query_union = (db.session.query( func.coalesce(query_validated.c.project_id, query_mapped.c.project_id).label("project_id"), func.coalesce(query_validated.c.validated, 0).label("validated"), func.coalesce(query_mapped.c.mapped, 0).label("mapped"), ).join( query_mapped, query_validated.c.project_id == query_mapped.c.project_id, full=True, ).subquery()) results = (db.session.query( Project.id, Project.status, Project.default_locale, query_union.c.mapped, query_union.c.validated, functions.ST_AsGeoJSON(Project.centroid), ).filter(Project.id == query_union.c.project_id).order_by( desc(Project.id)).all()) mapped_projects_dto = UserMappedProjectsDTO() for row in results: mapped_project = MappedProject() mapped_project.project_id = row[0] mapped_project.status = ProjectStatus(row[1]).name mapped_project.tasks_mapped = row[3] mapped_project.tasks_validated = row[4] mapped_project.centroid = geojson.loads(row[5]) project_info = ProjectInfo.get_dto_for_locale( row[0], preferred_locale, row[2]) mapped_project.name = project_info.name mapped_projects_dto.mapped_projects.append(mapped_project) return mapped_projects_dto
def get_mapped_projects(user_id: int, preferred_locale: str) -> UserMappedProjectsDTO: """ Get all projects a user has mapped on """ # This query looks scary, but we're really just creating an outer join between the query that gets the # counts of all mapped tasks and the query that gets counts of all validated tasks. This is necessary to # handle cases where users have only validated tasks on a project, or only mapped on a project. sql = """SELECT p.id, p.status, p.default_locale, c.mapped, c.validated, st_asgeojson(p.centroid) FROM projects p, (SELECT coalesce(v.project_id, m.project_id) project_id, coalesce(v.validated, 0) validated, coalesce(m.mapped, 0) mapped FROM (SELECT t.project_id, count (t.validated_by) validated FROM tasks t WHERE t.project_id IN (SELECT unnest(projects_mapped) FROM users WHERE id = :user_id) AND t.validated_by = :user_id GROUP BY t.project_id, t.validated_by) v FULL OUTER JOIN (SELECT t.project_id, count(t.mapped_by) mapped FROM tasks t WHERE t.project_id IN (SELECT unnest(projects_mapped) FROM users WHERE id = :user_id) AND t.mapped_by = :user_id GROUP BY t.project_id, t.mapped_by) m ON v.project_id = m.project_id) c WHERE p.id = c.project_id ORDER BY p.id DESC""" results = db.engine.execute(text(sql), user_id=user_id) mapped_projects_dto = UserMappedProjectsDTO() for row in results: mapped_project = MappedProject() mapped_project.project_id = row[0] mapped_project.status = ProjectStatus(row[1]).name mapped_project.tasks_mapped = row[3] mapped_project.tasks_validated = row[4] mapped_project.centroid = geojson.loads(row[5]) project_info = ProjectInfo.get_dto_for_locale( row[0], preferred_locale, row[2]) mapped_project.name = project_info.name mapped_projects_dto.mapped_projects.append(mapped_project) return mapped_projects_dto
def get_user_invalidated_tasks( as_validator, username: str, preferred_locale: str, closed=None, project_id=None, page=1, page_size=10, sort_by="updated_date", sort_direction="desc", ) -> InvalidatedTasks: """ Get invalidated tasks either mapped or invalidated by the user """ user = UserService.get_user_by_username(username) query = ( TaskInvalidationHistory.query.filter_by(invalidator_id=user.id) if as_validator else TaskInvalidationHistory.query.filter_by(mapper_id=user.id) ) if closed is not None: query = query.filter_by(is_closed=closed) if project_id is not None: query = query.filter_by(project_id=project_id) results = query.order_by(text(sort_by + " " + sort_direction)).paginate( page, page_size, True ) project_names = {} invalidated_tasks_dto = InvalidatedTasks() for entry in results.items: dto = InvalidatedTask() dto.task_id = entry.task_id dto.project_id = entry.project_id dto.history_id = entry.invalidation_history_id dto.closed = entry.is_closed dto.updated_date = entry.updated_date if dto.project_id not in project_names: project_names[dto.project_id] = ProjectInfo.get_dto_for_locale( dto.project_id, preferred_locale ).name dto.project_name = project_names[dto.project_id] invalidated_tasks_dto.invalidated_tasks.append(dto) invalidated_tasks_dto.pagination = Pagination(results) return invalidated_tasks_dto
def as_dto_for_mapping( self, authenticated_user_id: int = None, locale: str = "en", abbrev: bool = True ) -> Optional[ProjectDTO]: """ Creates a Project DTO suitable for transmitting to mapper users """ # Check for project visibility settings is_allowed_user = True if self.status == ProjectStatus.DRAFT.value: if not self.check_draft_project_visibility(authenticated_user_id): is_allowed_user = False if self.private: is_allowed_user = False if authenticated_user_id: user = User.get_by_id(authenticated_user_id) if ( UserRole(user.role) == UserRole.ADMIN or authenticated_user_id == self.author_id ): is_allowed_user = True for user in self.allowed_users: if user.id == authenticated_user_id: is_allowed_user = True break if is_allowed_user: project, project_dto = self._get_project_and_base_dto() if abbrev is False: project_dto.tasks = Task.get_tasks_as_geojson_feature_collection( self.id, None ) else: project_dto.tasks = Task.get_tasks_as_geojson_feature_collection_no_geom( self.id ) project_dto.project_info = ProjectInfo.get_dto_for_locale( self.id, locale, project.default_locale ) if project.organisation_id: project_dto.organisation = project.organisation.id project_dto.organisation_name = project.organisation.name project_dto.organisation_logo = project.organisation.logo project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales( self.id ) return project_dto
def as_dto_for_mapping(self, authenticated_user_id: int = None, locale: str = "en", abbrev: bool = True) -> Optional[ProjectDTO]: """ Creates a Project DTO suitable for transmitting to mapper users """ project, project_dto = self._get_project_and_base_dto() if abbrev is False: project_dto.tasks = Task.get_tasks_as_geojson_feature_collection( self.id, None) else: project_dto.tasks = Task.get_tasks_as_geojson_feature_collection_no_geom( self.id) project_dto.project_info = ProjectInfo.get_dto_for_locale( self.id, locale, project.default_locale) if project.organisation_id: project_dto.organisation = project.organisation.id project_dto.organisation_name = project.organisation.name project_dto.organisation_logo = project.organisation.logo project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales( self.id) return project_dto
def get_project_title(self, preferred_locale): project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale) return project_info.name
def get_project_summary(self, preferred_locale) -> ProjectSummary: """ Create Project Summary model for postgis project object""" summary = ProjectSummary() summary.project_id = self.id priority = self.priority if priority == 0: summary.priority = "URGENT" elif priority == 1: summary.priority = "HIGH" elif priority == 2: summary.priority = "MEDIUM" else: summary.priority = "LOW" summary.author = User.get_by_id(self.author_id).username summary.default_locale = self.default_locale summary.country_tag = self.country summary.changeset_comment = self.changeset_comment summary.due_date = self.due_date summary.created = self.created summary.last_updated = self.last_updated summary.osmcha_filter_id = self.osmcha_filter_id summary.mapper_level = MappingLevel(self.mapper_level).name summary.mapping_permission = MappingPermission( self.mapping_permission).name summary.validation_permission = ValidationPermission( self.validation_permission).name summary.random_task_selection_enforced = self.enforce_random_task_selection summary.private = self.private summary.license_id = self.license_id summary.status = ProjectStatus(self.status).name summary.id_presets = self.id_presets summary.imagery = self.imagery if self.organisation_id: summary.organisation = self.organisation_id summary.organisation_name = self.organisation.name summary.organisation_logo = self.organisation.logo if self.campaign: summary.campaigns = [i.as_dto() for i in self.campaign] # Cast MappingType values to related string array mapping_types_array = [] if self.mapping_types: for mapping_type in self.mapping_types: mapping_types_array.append(MappingTypes(mapping_type).name) summary.mapping_types = mapping_types_array if self.mapping_editors: mapping_editors = [] for mapping_editor in self.mapping_editors: mapping_editors.append(Editors(mapping_editor).name) summary.mapping_editors = mapping_editors if self.validation_editors: validation_editors = [] for validation_editor in self.validation_editors: validation_editors.append(Editors(validation_editor).name) summary.validation_editors = validation_editors if self.custom_editor: summary.custom_editor = self.custom_editor.as_dto() # If project is private, fetch list of allowed users if self.private: allowed_users = [] for user in self.allowed_users: allowed_users.append(user.username) summary.allowed_users = allowed_users centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON()) summary.aoi_centroid = geojson.loads(centroid_geojson) summary.percent_mapped = Project.calculate_tasks_percent( "mapped", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) summary.percent_validated = Project.calculate_tasks_percent( "validated", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) summary.percent_bad_imagery = Project.calculate_tasks_percent( "bad_imagery", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) summary.project_teams = [ ProjectTeamDTO( dict( team_id=t.team.id, team_name=t.team.name, role=TeamRoles(t.role).name, )) for t in self.teams ] project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale) summary.project_info = project_info return summary