def get_project_user_stats(self, user_id: int) -> ProjectUserStatsDTO:
"""Compute project specific stats for a given user"""
stats_dto = ProjectUserStatsDTO()
stats_dto.time_spent_mapping = 0
stats_dto.time_spent_validating = 0
stats_dto.total_time_spent = 0
query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_MAPPING' or action='AUTO_UNLOCKED_FOR_MAPPING')
and user_id = :user_id and project_id = :project_id;"""
total_mapping_time = db.engine.execute(text(query),
user_id=user_id,
project_id=self.id)
for time in total_mapping_time:
total_mapping_time = time[0]
if total_mapping_time:
stats_dto.time_spent_mapping = total_mapping_time.total_seconds(
)
stats_dto.total_time_spent += stats_dto.time_spent_mapping
query = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_VALIDATION' or action='AUTO_UNLOCKED_FOR_VALIDATION')
and user_id = :user_id and project_id = :project_id;"""
total_validation_time = db.engine.execute(text(query),
user_id=user_id,
project_id=self.id)
for time in total_validation_time:
total_validation_time = time[0]
if total_validation_time:
stats_dto.time_spent_validating = total_validation_time.total_seconds(
)
stats_dto.total_time_spent += stats_dto.time_spent_validating
return stats_dto
def get_project_user_stats(self, user_id: int) -> ProjectUserStatsDTO:
"""Compute project specific stats for a given user"""
stats_dto = ProjectUserStatsDTO()
stats_dto.time_spent_mapping = 0
stats_dto.time_spent_validating = 0
stats_dto.total_time_spent = 0
total_mapping_time = (
db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
)
)
.filter(
or_(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
)
)
.filter(TaskHistory.user_id == user_id)
.filter(TaskHistory.project_id == self.id)
)
for time in total_mapping_time:
total_mapping_time = time[0]
if total_mapping_time:
stats_dto.time_spent_mapping = total_mapping_time.total_seconds()
stats_dto.total_time_spent += stats_dto.time_spent_mapping
query = (
TaskHistory.query.with_entities(
func.date_trunc("minute", TaskHistory.action_date).label("trn"),
func.max(TaskHistory.action_text).label("tm"),
)
.filter(TaskHistory.user_id == user_id)
.filter(TaskHistory.project_id == self.id)
.filter(TaskHistory.action == "LOCKED_FOR_VALIDATION")
.group_by("trn")
.subquery()
)
total_validation_time = db.session.query(
func.sum(cast(func.to_timestamp(query.c.tm, "HH24:MI:SS"), Time))
).all()
for time in total_validation_time:
total_validation_time = time[0]
if total_validation_time:
stats_dto.time_spent_validating = total_validation_time.total_seconds()
stats_dto.total_time_spent += stats_dto.time_spent_validating
return stats_dto