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_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