def get_mapped_tasks_by_user(project_id: int): """ Gets all mapped tasks for supplied project grouped by user""" # Raw SQL is easier to understand that SQL alchemy here :) sql = """select u.username, u.mapping_level, count(distinct(t.id)), json_agg(distinct(t.id)), max(th.action_date) last_seen, u.date_registered, u.last_validation_date from tasks t, task_history th, users u where t.project_id = th.project_id and t.id = th.task_id and t.mapped_by = u.id and t.project_id = {0} and t.task_status = 2 and th.action_text = 'MAPPED' group by u.username, u.mapping_level, u.date_registered, u.last_validation_date""".format(project_id) results = db.engine.execute(sql) if results.rowcount == 0: raise NotFound() 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
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_all_users(query: UserSearchQuery) -> UserSearchDTO: """ Search and filter all users """ # Base query that applies to all searches base = db.session.query(User.id, User.username, User.mapping_level, User.role) # Add filter to query as required if query.mapping_level: base = base.filter(User.mapping_level == MappingLevel[ query.mapping_level.upper()].value) if query.username: base = base.filter( User.username.ilike(query.username.lower() + '%')) if query.role: base = base.filter(User.role == UserRole[query.role.upper()].value) results = base.order_by(User.username).paginate(query.page, 20, True) dto = UserSearchDTO() for result in results.items: listed_user = ListedUser() listed_user.id = result.id listed_user.mapping_level = MappingLevel(result.mapping_level).name listed_user.username = result.username listed_user.role = UserRole(result.role).name dto.users.append(listed_user) dto.pagination = Pagination(results) return 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 search_projects( search_dto: ProjectSearchDTO) -> ProjectSearchResultsDTO: """ Searches all projects for matches to the criteria provided by the user """ all_results, paginated_results = ProjectSearchService._filter_projects( search_dto) if paginated_results.total == 0: raise NotFound() features = [] for project in all_results: # This loop creates a geojson feature collection so you can see all active projects on the map properties = { "projectId": project.id, "priority": ProjectPriority(project.priority).name } centroid = project.centroid feature = geojson.Feature(geometry=geojson.loads(project.centroid), properties=properties) features.append(feature) feature_collection = geojson.FeatureCollection(features) dto = ProjectSearchResultsDTO() dto.map_results = feature_collection for project in paginated_results.items: # This loop loads the paginated text results # TODO would be nice to get this for an array rather than individually would be more efficient project_info_dto = ProjectInfo.get_dto_for_locale( project.id, search_dto.preferred_locale, project.default_locale) list_dto = ListSearchResultDTO() list_dto.project_id = project.id list_dto.locale = project_info_dto.locale list_dto.name = project_info_dto.name list_dto.priority = ProjectPriority(project.priority).name list_dto.mapper_level = MappingLevel(project.mapper_level).name list_dto.short_description = project_info_dto.short_description list_dto.organisation_tag = project.organisation_tag list_dto.campaign_tag = project.campaign_tag list_dto.percent_mapped = Project.calculate_tasks_percent( 'mapped', project.total_tasks, project.tasks_mapped, project.tasks_validated, project.tasks_bad_imagery) list_dto.percent_validated = Project.calculate_tasks_percent( 'validated', project.total_tasks, project.tasks_mapped, project.tasks_validated, project.tasks_bad_imagery) list_dto.status = ProjectStatus(project.status).name list_dto.active_mappers = Project.get_active_mappers(project.id) dto.results.append(list_dto) dto.pagination = Pagination(paginated_results) return dto
def as_dto(self, logged_in_username: str) -> UserDTO: """ Create DTO object from user in scope """ user_dto = UserDTO() user_dto.id = self.id user_dto.username = self.username user_dto.role = UserRole(self.role).name user_dto.mapping_level = MappingLevel(self.mapping_level).name user_dto.is_expert = self.is_expert or False user_dto.date_registered = str(self.date_registered) try: user_dto.projects_mapped = len(self.projects_mapped) # Handle users that haven't touched a project yet. except: user_dto.projects_mapped = 0 user_dto.tasks_mapped = self.tasks_mapped user_dto.tasks_validated = self.tasks_validated user_dto.tasks_invalidated = self.tasks_invalidated user_dto.twitter_id = self.twitter_id user_dto.linkedin_id = self.linkedin_id user_dto.facebook_id = self.facebook_id user_dto.validation_message = self.validation_message user_dto.total_time_spent = 0 user_dto.time_spent_mapping = 0 user_dto.time_spent_validating = 0 sql = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history WHERE (action='LOCKED_FOR_VALIDATION' or action='AUTO_UNLOCKED_FOR_VALIDATION') and user_id = :user_id;""" total_validation_time = db.engine.execute(text(sql), user_id=self.id) for row in total_validation_time: total_validation_time = row[0] if total_validation_time: total_validation_seconds = total_validation_time.total_seconds( ) user_dto.time_spent_validating = total_validation_seconds user_dto.total_time_spent += user_dto.time_spent_validating sql = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history WHERE (action='LOCKED_FOR_MAPPING' or action='AUTO_UNLOCKED_FOR_MAPPING') and user_id = :user_id;""" total_mapping_time = db.engine.execute(text(sql), user_id=self.id) for row in total_mapping_time: total_mapping_time = row[0] if total_mapping_time: total_mapping_seconds = total_mapping_time.total_seconds() user_dto.time_spent_mapping = total_mapping_seconds user_dto.total_time_spent += user_dto.time_spent_mapping if self.username == logged_in_username: # Only return email address when logged in user is looking at their own profile user_dto.email_address = self.email_address user_dto.is_email_verified = self.is_email_verified return user_dto
def _get_project_and_base_dto(self): """ Populates a project DTO with properties common to all roles """ base_dto = ProjectDTO() base_dto.project_id = self.id base_dto.project_status = ProjectStatus(self.status).name base_dto.default_locale = self.default_locale base_dto.project_priority = ProjectPriority(self.priority).name base_dto.area_of_interest = self.get_aoi_geometry_as_geojson() base_dto.enforce_mapper_level = self.enforce_mapper_level base_dto.enforce_validator_role = self.enforce_validator_role base_dto.private = self.private base_dto.mapper_level = MappingLevel(self.mapper_level).name base_dto.entities_to_map = self.entities_to_map base_dto.changeset_comment = self.changeset_comment base_dto.due_date = self.due_date base_dto.imagery = self.imagery base_dto.josm_preset = self.josm_preset base_dto.campaign_tag = self.campaign_tag base_dto.organisation_tag = self.organisation_tag base_dto.license_id = self.license_id base_dto.created = self.created base_dto.last_updated = self.last_updated base_dto.id_custom_imagery = self.id_custom_imagery base_dto.id_custom_presets = self.id_custom_presets base_dto.id_min_editable_zoom = self.id_min_editable_zoom base_dto.author = User().get_by_id(self.author_id).username base_dto.active_mappers = Project.get_active_mappers(self.id) base_dto.task_creation_mode = TaskCreationMode( self.task_creation_mode).name if self.private: # If project is private it should have a list of allowed users allowed_usernames = [] for user in self.allowed_users: allowed_usernames.append(user.username) base_dto.allowed_usernames = allowed_usernames if self.mapping_types: mapping_types = [] for mapping_type in self.mapping_types: mapping_types.append(MappingTypes(mapping_type).name) base_dto.mapping_types = mapping_types if self.priority_areas: geojson_areas = [] for priority_area in self.priority_areas: geojson_areas.append(priority_area.get_as_geojson()) base_dto.priority_areas = geojson_areas return self, base_dto
def _get_project_and_base_dto(self, project_id): """ Populates a project DTO with properties common to all roles """ project = Project.get(project_id) if project is None: return None, None aoi = project.area_of_interest base_dto = ProjectDTO() base_dto.project_id = project_id base_dto.project_status = ProjectStatus(project.status).name base_dto.default_locale = project.default_locale base_dto.project_priority = ProjectPriority(project.priority).name base_dto.area_of_interest = aoi.get_aoi_geometry_as_geojson() base_dto.enforce_mapper_level = project.enforce_mapper_level base_dto.enforce_validator_role = project.enforce_validator_role base_dto.private = project.private base_dto.mapper_level = MappingLevel(project.mapper_level).name base_dto.entities_to_map = project.entities_to_map base_dto.changeset_comment = project.changeset_comment base_dto.due_date = project.due_date base_dto.imagery = project.imagery base_dto.josm_preset = project.josm_preset base_dto.campaign_tag = project.campaign_tag base_dto.organisation_tag = project.organisation_tag base_dto.license_id = project.license_id base_dto.last_updated = project.last_updated base_dto.author = User().get_by_id(project.author_id).username if project.private: # If project is private it should have a list of allowed users allowed_usernames = [] for user in project.allowed_users: allowed_usernames.append(user.username) base_dto.allowed_usernames = allowed_usernames if project.mapping_types: mapping_types = [] for mapping_type in project.mapping_types: mapping_types.append(MappingTypes(mapping_type).name) base_dto.mapping_types = mapping_types if project.priority_areas: geojson_areas = [] for priority_area in project.priority_areas: geojson_areas.append(priority_area.get_as_geojson()) base_dto.priority_areas = geojson_areas return project, base_dto
def as_dto(self, logged_in_username: str) -> UserDTO: """ Create DTO object from user in scope """ user_dto = UserDTO() user_dto.username = self.username user_dto.role = UserRole(self.role).name user_dto.mapping_level = MappingLevel(self.mapping_level).name user_dto.tasks_mapped = self.tasks_mapped user_dto.tasks_validated = self.tasks_validated user_dto.twitter_id = self.twitter_id user_dto.linkedin_id = self.linkedin_id user_dto.facebook_id = self.facebook_id if self.username == logged_in_username: # Only return email address when logged in user is looking at their own profile user_dto.email_address = self.email_address user_dto.is_email_verified = self.is_email_verified return user_dto
def search_projects( search_dto: ProjectSearchDTO) -> ProjectSearchResultsDTO: """ Searches all projects for matches to the criteria provided by the user """ filtered_projects = ProjectSearchService._filter_projects(search_dto) if filtered_projects.total == 0: raise NotFound() dto = ProjectSearchResultsDTO() for project in filtered_projects.items: # TODO would be nice to get this for an array rather than individually would be more efficient project_info_dto = ProjectInfo.get_dto_for_locale( project.id, search_dto.preferred_locale, project.default_locale) result_dto = ProjectSearchResultDTO() result_dto.project_id = project.id result_dto.locale = project_info_dto.locale result_dto.name = project_info_dto.name result_dto.priority = ProjectPriority(project.priority).name result_dto.mapper_level = MappingLevel(project.mapper_level).name result_dto.short_description = project_info_dto.short_description result_dto.aoi_centroid = geojson.loads(project.centroid) result_dto.organisation_tag = project.organisation_tag result_dto.campaign_tag = project.campaign_tag result_dto.percent_mapped = round( (project.tasks_mapped / (project.total_tasks - project.tasks_bad_imagery)) * 100, 0) result_dto.percent_validated = round( ((project.tasks_validated + project.tasks_bad_imagery) / project.total_tasks) * 100, 0) dto.results.append(result_dto) dto.pagination = Pagination(filtered_projects) return 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.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