def send_favorite_project_activities(user_id: int): current_app.logger.debug("Sending Favorite Project Activities") favorited_projects = UserService.get_projects_favorited(user_id) contributed_projects = UserService.get_projects_mapped(user_id) if contributed_projects is None: contributed_projects = [] for favorited_project in favorited_projects.favorited_projects: contributed_projects.append(favorited_project.project_id) recently_updated_projects = ( Project.query.with_entities( Project.id, func.DATE(Project.last_updated).label("last_updated") ) .filter(Project.id.in_(contributed_projects)) .filter( func.DATE(Project.last_updated) > datetime.date.today() - datetime.timedelta(days=300) ) ) user = UserService.get_user_dto_by_id(user_id) if user.projects_notifications is False: return messages = [] for project in recently_updated_projects: activity_message = [] query_last_active_users = """ select distinct(user_id) from (select user_id from task_history where project_id = :project_id order by action_date desc limit 15 ) t """ last_active_users = db.engine.execute( text(query_last_active_users), project_id=project.id ) for recent_user_id in last_active_users: recent_user_details = UserService.get_user_by_id(recent_user_id) user_profile_link = MessageService.get_user_profile_link( recent_user_details.username ) activity_message.append(user_profile_link) activity_message = str(activity_message)[1:-1] project_link = MessageService.get_project_link(project.id) message = Message() message.message_type = MessageType.PROJECT_ACTIVITY_NOTIFICATION.value message.project_id = project.id message.to_user_id = user.id message.subject = ( "Recent activities from your contributed/favorited Projects" ) message.message = ( f"{activity_message} contributed to Project {project_link} recently" ) messages.append(dict(message=message, user=user)) MessageService._push_messages(messages)
def _filter_projects(search_dto: ProjectSearchDTO, user): """ Filters all projects based on criteria provided by user""" query = ProjectSearchService.create_search_query(user) query = query.join(ProjectInfo).filter( ProjectInfo.locale.in_([search_dto.preferred_locale, "en"])) project_status_array = [] if search_dto.project_statuses: project_status_array = [ ProjectStatus[project_status].value for project_status in search_dto.project_statuses ] query = query.filter(Project.status.in_(project_status_array)) else: if not search_dto.created_by: project_status_array = [ProjectStatus.PUBLISHED.value] query = query.filter(Project.status.in_(project_status_array)) if search_dto.interests: query = query.join( project_interests, project_interests.c.project_id == Project.id).filter( project_interests.c.interest_id.in_(search_dto.interests)) if search_dto.created_by: query = query.filter(Project.author_id == search_dto.created_by) if search_dto.mapped_by: projects_mapped = UserService.get_projects_mapped( search_dto.mapped_by) query = query.filter(Project.id.in_(projects_mapped)) if search_dto.favorited_by: projects_favorited = user.favorites query = query.filter( Project.id.in_([project.id for project in projects_favorited])) if search_dto.mapper_level and search_dto.mapper_level.upper( ) != "ALL": query = query.filter(Project.mapper_level == MappingLevel[ search_dto.mapper_level].value) if search_dto.organisation_name: query = query.filter( Organisation.name == search_dto.organisation_name) if search_dto.organisation_id: query = query.filter(Organisation.id == search_dto.organisation_id) if search_dto.team_id: query = query.join(ProjectTeams, ProjectTeams.project_id == Project.id).filter( ProjectTeams.team_id == search_dto.team_id) if search_dto.campaign: query = query.join(Campaign, Project.campaign).group_by(Campaign.name) query = query.filter(Campaign.name == search_dto.campaign) if search_dto.mapping_types: # Construct array of mapping types for query mapping_type_array = [] mapping_type_array = [ MappingTypes[mapping_type].value for mapping_type in search_dto.mapping_types ] query = query.filter( Project.mapping_types.contains(mapping_type_array)) if search_dto.text_search: # We construct an OR search, so any projects that contain or more of the search terms should be returned or_search = " | ".join( [x for x in search_dto.text_search.split(" ") if x != ""]) opts = [ ProjectInfo.text_searchable.match( or_search, postgresql_regconfig="english"), ProjectInfo.name.ilike(f"%{or_search}%"), ] try: opts.append(Project.id == int(search_dto.text_search)) except ValueError: pass query = query.filter(or_(*opts)) if search_dto.country: # Unnest country column array. sq = Project.query.with_entities( Project.id, func.unnest(Project.country).label("country")).subquery() query = query.filter( sq.c.country.ilike("%{}%".format( search_dto.country))).filter(Project.id == sq.c.id) order_by = search_dto.order_by if search_dto.order_by_type == "DESC": order_by = desc(search_dto.order_by) query = query.order_by(order_by).distinct(search_dto.order_by, Project.id) if search_dto.managed_by and user.role != UserRole.ADMIN.value: # Get all the projects associated with the user and team. orgs_projects_ids = [[p.id for p in u.projects] for u in user.organisations] orgs_projects_ids = [ item for sublist in orgs_projects_ids for item in sublist ] team_project_ids = [[ p.project_id for p in u.team.projects if p.role == TeamRoles.PROJECT_MANAGER.value ] for u in user.teams] team_project_ids = [ item for sublist in team_project_ids for item in sublist ] orgs_projects_ids.extend(team_project_ids) ids = tuple(set(orgs_projects_ids)) query = query.filter(Project.id.in_(ids)) all_results = [] if not search_dto.omit_map_results: query_result = query query_result.column_descriptions.clear() query_result.add_column(Project.id) query_result.add_column( Project.centroid.ST_AsGeoJSON().label("centroid")) query_result.add_column(Project.priority) all_results = query_result.all() paginated_results = query.paginate(search_dto.page, 14, True) return all_results, paginated_results
def _filter_projects(search_dto: ProjectSearchDTO, user): """ Filters all projects based on criteria provided by user""" query = ProjectSearchService.create_search_query(user) query = query.join(ProjectInfo).filter( ProjectInfo.locale.in_([search_dto.preferred_locale, "en"]) ) project_status_array = [] if search_dto.project_statuses: for project_status in search_dto.project_statuses: project_status_array.append(ProjectStatus[project_status].value) query = query.filter(Project.status.in_(project_status_array)) else: if not search_dto.created_by: project_status_array = [ProjectStatus.PUBLISHED.value] query = query.filter(Project.status.in_(project_status_array)) if search_dto.interests: query = query.join( project_interests, project_interests.c.project_id == Project.id ).filter(project_interests.c.interest_id.in_(search_dto.interests)) if search_dto.created_by: query = query.filter(Project.author_id == search_dto.created_by) if search_dto.mapped_by: projects_mapped = UserService.get_projects_mapped(search_dto.mapped_by) query = query.filter(Project.id.in_(projects_mapped)) if search_dto.favorited_by: user = UserService.get_user_by_id(search_dto.favorited_by) projects_favorited = user.favorites query = query.filter( Project.id.in_([project.id for project in projects_favorited]) ) if search_dto.mapper_level and search_dto.mapper_level.upper() != "ALL": query = query.filter( Project.mapper_level == MappingLevel[search_dto.mapper_level].value ) if search_dto.organisation_name: query = query.filter(Organisation.name == search_dto.organisation_name) if search_dto.organisation_id: query = query.filter(Organisation.id == search_dto.organisation_id) if search_dto.team_id: query = query.join( ProjectTeams, ProjectTeams.project_id == Project.id ).filter(ProjectTeams.team_id == search_dto.team_id) if search_dto.campaign: query = query.join(Campaign, Project.campaign).group_by( Project.id, Campaign.name ) query = query.filter(Campaign.name == search_dto.campaign) if search_dto.mapping_types: # Construct array of mapping types for query mapping_type_array = [] for mapping_type in search_dto.mapping_types: mapping_type_array.append(MappingTypes[mapping_type].value) query = query.filter(Project.mapping_types.contains(mapping_type_array)) if search_dto.text_search: # We construct an OR search, so any projects that contain or more of the search terms should be returned or_search = " | ".join( [x for x in search_dto.text_search.split(" ") if x != ""] ) opts = [ ProjectInfo.text_searchable.match( or_search, postgresql_regconfig="english" ), ProjectInfo.name.like(f"%{or_search}%"), ] try: opts.append(Project.id == int(search_dto.text_search)) except ValueError: pass query = query.filter(or_(*opts)) if search_dto.country: # Unnest country column array. sq = Project.query.with_entities( Project.id, func.unnest(Project.country).label("country") ).subquery() query = query.filter( sq.c.country.ilike("%{}%".format(search_dto.country)) ).filter(Project.id == sq.c.id) order_by = search_dto.order_by if search_dto.order_by_type == "DESC": order_by = desc(search_dto.order_by) query = query.order_by(order_by).group_by(Project.id) if search_dto.managed_by and user.role != UserRole.ADMIN.value: team_projects = query.join(ProjectTeams).filter( ProjectTeams.role == TeamRoles.PROJECT_MANAGER.value, ProjectTeams.project_id == Project.id, ) user_orgs_list = OrganisationService.get_organisations_managed_by_user( search_dto.managed_by ) orgs_managed = [org.id for org in user_orgs_list] org_projects = query.filter(Project.organisation_id.in_(orgs_managed)) query = org_projects.union(team_projects) all_results = query.all() paginated_results = query.paginate(search_dto.page, 14, True) return all_results, paginated_results