def create_result_dto(project, preferred_locale, total_contributors): project_info_dto = ProjectInfo.get_dto_for_locale( project.id, 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.last_updated = project.last_updated list_dto.due_date = project.due_date 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) list_dto.total_contributors = total_contributors list_dto.country = project.country list_dto.organisation_name = project.organisation_name list_dto.organisation_logo = project.organisation_logo list_dto.campaigns = Project.get_project_campaigns(project.id) return list_dto
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 = :project_id and t.task_status = 2 and th.action_text = 'MAPPED' group by u.username, u.mapping_level, u.date_registered, u.last_validation_date""" results = db.engine.execute(text(sql), project_id=project_id) 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_user_contributions(project_id: int) -> ProjectContributionsDTO: """ Get all user contributions on a project""" mapped_stmt = (Task.query.with_entities( Task.mapped_by, func.count(Task.mapped_by).label("count"), func.array_agg(Task.id).label("task_ids"), ).filter(Task.project_id == project_id).group_by( Task.mapped_by).subquery()) validated_stmt = (Task.query.with_entities( Task.validated_by, func.count(Task.validated_by).label("count"), func.array_agg(Task.id).label("task_ids"), ).filter(Task.project_id == project_id).group_by( Task.validated_by).subquery()) results = (db.session.query( User.id, User.username, User.name, User.mapping_level, User.picture_url, coalesce(mapped_stmt.c.count, 0).label("mapped"), coalesce(validated_stmt.c.count, 0).label("validated"), (coalesce(mapped_stmt.c.count, 0) + coalesce(validated_stmt.c.count, 0)).label("total"), (mapped_stmt.c.task_ids + validated_stmt.c.task_ids).label("task_ids"), ).outerjoin( validated_stmt, mapped_stmt.c.mapped_by == validated_stmt.c.validated_by).join( User, User.id == mapped_stmt.c.mapped_by).order_by( desc("total")).all()) contrib_dto = ProjectContributionsDTO() user_contributions = [ UserContribution( dict( username=r.username, name=r.name, mapping_level=MappingLevel(r.mapping_level).name, picture_url=r.picture_url, mapped=r.mapped, validated=r.validated, total=r.total, task_ids=r.task_ids, )) for r in results ] contrib_dto.user_contributions = user_contributions return contrib_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.projects_mapped = (len(self.projects_mapped) if self.projects_mapped else None) user_dto.is_expert = self.is_expert or False user_dto.date_registered = self.date_registered user_dto.twitter_id = self.twitter_id user_dto.linkedin_id = self.linkedin_id user_dto.facebook_id = self.facebook_id user_dto.skype_id = self.skype_id user_dto.slack_id = self.slack_id user_dto.irc_id = self.irc_id user_dto.city = self.city user_dto.country = self.country user_dto.name = self.name user_dto.picture_url = self.picture_url user_dto.osm_profile = self.osm_profile_url user_dto.missing_maps_profile = self.missing_maps_profile_url user_dto.default_editor = self.default_editor user_dto.mentions_notifications = self.mentions_notifications user_dto.projects_notifications = self.projects_notifications user_dto.comments_notifications = self.comments_notifications user_dto.tasks_notifications = self.tasks_notifications user_dto.teams_notifications = self.teams_notifications if self.username == logged_in_username: # Only return email address and gender information 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 gender = None if self.gender is not None: gender = UserGender(self.gender).name user_dto.gender = gender user_dto.self_description_gender = self.self_description_gender return user_dto
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, User.picture_url) # Add filter to query as required if query.mapping_level: mapping_levels = query.mapping_level.split(",") mapping_level_array = [] for mapping_level in mapping_levels: mapping_level_array.append(MappingLevel[mapping_level].value) base = base.filter(User.mapping_level.in_(mapping_level_array)) if query.username: base = base.filter( User.username.ilike(query.username.lower() + "%")) if query.role: roles = query.role.split(",") role_array = [] for role in roles: role_array.append(UserRole[role].value) base = base.filter(User.role.in_(role_array)) 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.picture_url = result.picture_url listed_user.role = UserRole(result.role).name dto.users.append(listed_user) dto.pagination = Pagination(results) return dto
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
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.aoi_bbox = shape(base_dto.area_of_interest).bounds base_dto.mapping_permission = MappingPermission( self.mapping_permission).name base_dto.validation_permission = ValidationPermission( self.validation_permission).name base_dto.enforce_random_task_selection = self.enforce_random_task_selection base_dto.private = self.private base_dto.mapper_level = MappingLevel(self.mapper_level).name base_dto.changeset_comment = self.changeset_comment base_dto.osmcha_filter_id = self.osmcha_filter_id base_dto.due_date = self.due_date base_dto.imagery = self.imagery base_dto.josm_preset = self.josm_preset base_dto.id_presets = self.id_presets base_dto.country_tag = self.country base_dto.organisation_id = self.organisation_id base_dto.license_id = self.license_id base_dto.created = self.created base_dto.last_updated = self.last_updated 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 base_dto.percent_mapped = Project.calculate_tasks_percent( "mapped", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) base_dto.percent_validated = Project.calculate_tasks_percent( "validated", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) base_dto.percent_bad_imagery = Project.calculate_tasks_percent( "bad_imagery", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) base_dto.project_teams = [ ProjectTeamDTO( dict( team_id=t.team.id, team_name=t.team.name, role=TeamRoles(t.role).name, )) for t in self.teams ] if self.custom_editor: base_dto.custom_editor = self.custom_editor.as_dto() 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.campaign: base_dto.campaigns = [i.as_dto() for i in self.campaign] if self.mapping_editors: mapping_editors = [] for mapping_editor in self.mapping_editors: mapping_editors.append(Editors(mapping_editor).name) base_dto.mapping_editors = mapping_editors if self.validation_editors: validation_editors = [] for validation_editor in self.validation_editors: validation_editors.append(Editors(validation_editor).name) base_dto.validation_editors = validation_editors 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 base_dto.interests = [ InterestDTO(dict(id=i.id, name=i.name)) for i in self.interests ] return self, base_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 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