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
