def get_project_summary(self, preferred_locale) -> ProjectSummary: """ Create Project Summary model for postgis project object""" summary = ProjectSummary() summary.project_id = self.id summary.campaign_tag = self.campaign_tag summary.created = self.created summary.last_updated = self.last_updated summary.mapper_level = MappingLevel(self.mapper_level).name summary.organisation_tag = self.organisation_tag summary.status = ProjectStatus(self.status).name centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON()) summary.aoi_centroid = geojson.loads(centroid_geojson) summary.percent_mapped = int( (self.tasks_mapped / (self.total_tasks - self.tasks_bad_imagery)) * 100) summary.percent_validated = int( ((self.tasks_validated + self.tasks_bad_imagery) / self.total_tasks) * 100) project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale) summary.name = project_info.name summary.short_description = project_info.short_description return summary
def get_mapped_projects(user_id: int, preferred_locale: str) -> UserMappedProjectsDTO: """ Get all projects a user has mapped on """ sql = '''select p.id, p.status, p.default_locale, count(t.mapped_by), count(t.validated_by), st_asgeojson(p.centroid), st_asgeojson(p.geometry) from projects p, tasks t where p.id in (select unnest(projects_mapped) from users where id = {0}) and p.id = t.project_id and (t.mapped_by = {0} or t.mapped_by is null) and (t.validated_by = {0} or t.validated_by is null) GROUP BY p.id, p.status, p.centroid, p.geometry'''.format(user_id) results = db.engine.execute(sql) if results.rowcount == 0: raise NotFound() 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]) mapped_project.aoi = geojson.loads(row[6]) 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 as_dto_for_mapping(self, locale: str) -> Optional[ProjectDTO]: """ Creates a Project DTO suitable for transmitting to mapper users """ project, project_dto = self._get_project_and_base_dto(self.id) project_dto.tasks = Task.get_tasks_as_geojson_feature_collection(self.id) project_dto.project_info = ProjectInfo.get_dto_for_locale(self.id, locale, project.default_locale) return project_dto
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 polygon = to_shape(self.geometry) polygon_aea = transform( partial( pyproj.transform, pyproj.Proj(init='EPSG:4326'), pyproj.Proj( proj='aea', lat1=polygon.bounds[1], lat2=polygon.bounds[3])), polygon) area = polygon_aea.area/1000000 summary.area = area summary.campaign_tag = self.campaign_tag summary.changeset_comment = self.changeset_comment summary.created = self.created summary.last_updated = self.last_updated summary.due_date = self.due_date summary.mapper_level = MappingLevel(self.mapper_level).name summary.mapper_level_enforced = self.enforce_mapper_level summary.validator_level_enforced = self.enforce_validator_role summary.organisation_tag = self.organisation_tag summary.status = ProjectStatus(self.status).name summary.entities_to_map = self.entities_to_map 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) project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale) summary.name = project_info.name summary.short_description = project_info.short_description return summary
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 = {0}) AND t.validated_by = {0} 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 = {0}) AND t.mapped_by = {0} 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'''.format( user_id) results = db.engine.execute(sql) if results.rowcount == 0: raise NotFound() 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_project_summary(project, preferred_locale) -> ProjectSummary: """ Create Project Summary model for postgis project object""" pm_project = ProjectSummary() pm_project.project_id = project.id pm_project.campaign_tag = project.campaign_tag pm_project.created = project.created pm_project.last_updated = project.last_updated pm_project.aoi_centroid = geojson.loads(project.geojson) pm_project.percent_mapped = round((project.tasks_mapped / (project.total_tasks - project.tasks_bad_imagery)) * 100, 0) pm_project.percent_validated = round(((project.tasks_validated + project.tasks_bad_imagery) / project.total_tasks) * 100, 0) project_info = ProjectInfo.get_dto_for_locale(project.id, preferred_locale, project.default_locale) pm_project.name = project_info.name return pm_project
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(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 not dto.project_id 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 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 polygon = to_shape(self.geometry) polygon_aea = transform( partial( pyproj.transform, pyproj.Proj(init='EPSG:4326'), pyproj.Proj( proj='aea', lat1=polygon.bounds[1], lat2=polygon.bounds[3])), polygon) area = polygon_aea.area/1000000 summary.area = area summary.campaign_tag = self.campaign_tag summary.changeset_comment = self.changeset_comment summary.created = self.created summary.last_updated = self.last_updated summary.due_date = self.due_date summary.mapper_level = MappingLevel(self.mapper_level).name summary.mapper_level_enforced = self.enforce_mapper_level summary.validator_level_enforced = self.enforce_validator_role summary.organisation_tag = self.organisation_tag summary.status = ProjectStatus(self.status).name summary.total_mappers = db.session.query(User).filter(User.projects_mapped.any(self.id)).count() unique_mappers = TaskHistory.query.filter( TaskHistory.action == 'LOCKED_FOR_MAPPING', TaskHistory.project_id == self.id ).distinct(TaskHistory.user_id).count() unique_validators = TaskHistory.query.filter( TaskHistory.action == 'LOCKED_FOR_VALIDATION', TaskHistory.project_id == self.id ).distinct(TaskHistory.user_id).count() summary.total_tasks = self.total_tasks summary.total_comments = db.session.query(ProjectChat).filter(ProjectChat.project_id == self.id).count() summary.entities_to_map = self.entities_to_map centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON()) summary.aoi_centroid = geojson.loads(centroid_geojson) summary.percent_mapped = int(((self.tasks_mapped + self.tasks_bad_imagery) / self.total_tasks) * 100) summary.percent_validated = int((self.tasks_validated / self.total_tasks) * 100) summary.percent_bad_imagery = int((self.tasks_bad_imagery / self.total_tasks) * 100) project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale) summary.name = project_info.name summary.short_description = project_info.short_description summary.total_time_spent = 0 summary.total_mapping_time = 0 summary.total_validation_time = 0 summary.average_mapping_time = 0 summary.average_validation_time = 0 sql = '''SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history WHERE action='LOCKED_FOR_MAPPING'and project_id = {0};'''.format(self.id) total_mapping_time = db.engine.execute(sql) for row in total_mapping_time: total_mapping_time = row[0] if total_mapping_time: total_mapping_seconds = total_mapping_time.total_seconds() summary.total_mapping_time = total_mapping_seconds summary.total_time_spent += summary.total_mapping_time if unique_mappers: average_mapping_time = total_mapping_seconds/unique_mappers summary.average_mapping_time = average_mapping_time sql = '''SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history WHERE action='LOCKED_FOR_VALIDATION' and project_id = {0};'''.format(self.id) total_validation_time = db.engine.execute(sql) for row in total_validation_time: total_validation_time = row[0] if total_validation_time: total_validation_seconds = total_validation_time.total_seconds() summary.total_validation_time = total_validation_seconds summary.total_time_spent += summary.total_validation_time if unique_validators: average_validation_time = total_validation_seconds/unique_validators summary.average_validation_time = average_validation_time return summary