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