def get_latest_activity(project_id: int, page: int) -> ProjectActivityDTO:
""" Gets all the activity on a project """
if not ProjectService.exists(project_id):
raise NotFound
results = (db.session.query(
TaskHistory.id,
TaskHistory.task_id,
TaskHistory.action,
TaskHistory.action_date,
TaskHistory.action_text,
User.username,
).join(User).filter(TaskHistory.project_id == project_id,
TaskHistory.action != "COMMENT").order_by(
TaskHistory.action_date.desc()).paginate(
page, 10, True))
activity_dto = ProjectActivityDTO()
for item in results.items:
history = TaskHistoryDTO()
history.task_id = item.id
history.task_id = item.task_id
history.action = item.action
history.action_text = item.action_text
history.action_date = item.action_date
history.action_by = item.username
activity_dto.activity.append(history)
activity_dto.pagination = Pagination(results)
return activity_dto
def get_all_messages(
user_id: int,
locale: str,
page: int,
page_size=10,
sort_by=None,
sort_direction=None,
message_type=None,
from_username=None,
project=None,
task_id=None,
):
""" Get all messages for user """
sort_column = Message.__table__.columns.get(sort_by)
if sort_column is None:
sort_column = Message.date
sort_column = (
sort_column.asc() if sort_direction.lower() == "asc" else sort_column.desc()
)
query = Message.query
if project is not None:
query = query.filter(Message.project_id == project)
if task_id is not None:
query = query.filter(Message.task_id == task_id)
if message_type:
message_type_filters = map(int, message_type.split(","))
query = query.filter(Message.message_type.in_(message_type_filters))
if from_username is not None:
query = query.join(Message.from_user).filter(
User.username.ilike(from_username + "%")
)
results = (
query.filter(Message.to_user_id == user_id)
.order_by(sort_column)
.paginate(page, page_size, True)
)
# if results.total == 0:
# raise NotFound()
messages_dto = MessagesDTO()
for item in results.items:
message_dto = None
if isinstance(item, tuple):
message_dto = item[0].as_dto()
message_dto.project_title = item[1].name
else:
message_dto = item.as_dto()
if item.project_id is not None:
message_dto.project_title = item.project.get_project_title(locale)
messages_dto.user_messages.append(message_dto)
messages_dto.pagination = Pagination(results)
return messages_dto
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(text(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 dto.project_id not 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_tasks_dto(
user_id: int,
start_date: datetime.datetime = None,
end_date: datetime.datetime = None,
task_status: str = None,
project_status: str = None,
project_id: int = None,
page=1,
page_size=10,
sort_by: str = None,
) -> UserTaskDTOs:
base_query = (TaskHistory.query.with_entities(
TaskHistory.project_id,
TaskHistory.task_id,
func.max(TaskHistory.action_date),
).filter(TaskHistory.user_id == user_id).group_by(
TaskHistory.task_id, TaskHistory.project_id))
if task_status:
base_query = base_query.filter(
TaskHistory.action_text == TaskStatus[
task_status.upper()].name)
if start_date:
base_query = base_query.filter(
TaskHistory.action_date >= start_date)
if end_date:
base_query = base_query.filter(TaskHistory.action_date <= end_date)
if sort_by == "action_date":
base_query = base_query.order_by(func.max(TaskHistory.action_date))
elif sort_by == "-action_date":
base_query = base_query.order_by(
desc(func.max(TaskHistory.action_date)))
user_task_dtos = UserTaskDTOs()
task_id_list = base_query.subquery()
tasks = Task.query.join(
task_id_list,
and_(
Task.id == task_id_list.c.task_id,
Task.project_id == task_id_list.c.project_id,
),
)
tasks = tasks.add_column("max_1")
if project_status:
tasks = tasks.filter(
Task.project_id == Project.id,
Project.status == ProjectStatus[project_status.upper()].value,
)
if project_id:
tasks = tasks.filter_by(project_id=project_id)
results = tasks.paginate(page, page_size, True)
task_list = []
for task, action_date in results.items:
task_list.append(task.as_dto(last_updated=action_date))
user_task_dtos.user_tasks = task_list
user_task_dtos.pagination = Pagination(results)
return user_task_dtos
def get_tasks_dto(
user_id: int,
start_date: datetime.datetime = None,
end_date: datetime.datetime = None,
task_status: str = None,
project_status: str = None,
project_id: int = None,
page=1,
page_size=10,
sort_by: str = None,
) -> UserTaskDTOs:
base_query = (TaskHistory.query.with_entities(
TaskHistory.project_id.label("project_id"),
TaskHistory.task_id.label("task_id"),
func.max(TaskHistory.action_date).label("max"),
).filter(TaskHistory.user_id == user_id).group_by(
TaskHistory.task_id, TaskHistory.project_id))
if task_status:
base_query = base_query.filter(
TaskHistory.action_text == TaskStatus[
task_status.upper()].name)
if start_date:
base_query = base_query.filter(
TaskHistory.action_date >= start_date)
if end_date:
base_query = base_query.filter(TaskHistory.action_date <= end_date)
if sort_by == "action_date":
base_query = base_query.order_by(func.max(TaskHistory.action_date))
elif sort_by == "-action_date":
base_query = base_query.order_by(
desc(func.max(TaskHistory.action_date)))
user_task_dtos = UserTaskDTOs()
task_id_list = base_query.subquery()
comments_query = (TaskHistory.query.with_entities(
TaskHistory.project_id,
TaskHistory.task_id,
func.count(TaskHistory.action).label("count"),
).filter(TaskHistory.action == "COMMENT").group_by(
TaskHistory.task_id, TaskHistory.project_id)).subquery()
sq = (db.session.query(
func.coalesce(comments_query.c.count, 0).label("comments"),
task_id_list).select_from(task_id_list).outerjoin(
comments_query,
(comments_query.c.task_id == task_id_list.c.task_id)
& (comments_query.c.project_id == task_id_list.c.project_id),
).subquery())
tasks = Task.query.join(
sq,
and_(
Task.id == sq.c.task_id,
Task.project_id == sq.c.project_id,
),
)
tasks = tasks.add_columns("max", "comments")
if project_status:
tasks = tasks.filter(
Task.project_id == Project.id,
Project.status == ProjectStatus[project_status.upper()].value,
)
if project_id:
tasks = tasks.filter_by(project_id=project_id)
results = tasks.paginate(page, page_size, True)
task_list = []
for task, action_date, comments in results.items:
task_list.append(
task.as_dto(last_updated=action_date, comments=comments))
user_task_dtos.user_tasks = task_list
user_task_dtos.pagination = Pagination(results)
return user_task_dtos