def get_user_contributions(project_id: int) -> ProjectContributionsDTO:
""" Get all user contributions on a project"""
contrib_query = '''select m.mapped_by, m.username, m.mapped, v.validated_by, v.username, v.validated
from (select t.mapped_by, u.username, count(t.mapped_by) mapped
from tasks t,
users u
where t.mapped_by = u.id
and t.project_id = {0}
and t.mapped_by is not null
group by t.mapped_by, u.username) m FULL OUTER JOIN
(select t.validated_by, u.username, count(t.validated_by) validated
from tasks t,
users u
where t.validated_by = u.id
and t.project_id = {0}
and t.validated_by is not null
group by t.validated_by, u.username) v
ON m.mapped_by = v.validated_by
'''.format(project_id)
results = db.engine.execute(contrib_query)
if results.rowcount == 0:
raise NotFound()
contrib_dto = ProjectContributionsDTO()
for row in results:
user_contrib = UserContribution()
user_contrib.username = row[1] if row[1] else row[4]
user_contrib.mapped = row[2] if row[2] else 0
user_contrib.validated = row[5] if row[5] else 0
contrib_dto.user_contributions.append(user_contrib)
return contrib_dto
def get_user_contributions(project_id: int) -> ProjectContributionsDTO:
""" Get all user contributions on a project"""
contrib_query = '''select m.mapped_by, m.username, m.mapped, v.validated_by, v.username, v.validated
from (select t.mapped_by, u.username, count(t.mapped_by) mapped
from tasks t,
users u
where t.mapped_by = u.id
and t.project_id = {0}
and t.mapped_by is not null
group by t.mapped_by, u.username) m FULL OUTER JOIN
(select t.validated_by, u.username, count(t.validated_by) validated
from tasks t,
users u
where t.validated_by = u.id
and t.project_id = {0}
and t.validated_by is not null
group by t.validated_by, u.username) v
ON m.mapped_by = v.validated_by
'''.format(project_id)
results = db.engine.execute(contrib_query)
if results.rowcount == 0:
raise NotFound()
contrib_dto = ProjectContributionsDTO()
for row in results:
if row[0]:
user_contrib = UserContribution()
user_contrib.username = row[1] if row[1] else row[4]
user_contrib.mapped = row[2] if row[2] else 0
user_contrib.validated = row[5] if row[5] else 0
user_contrib.total_time_spent = 0
user_contrib.time_spent_mapping = 0
user_contrib.time_spent_validating = 0
sql = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE action='LOCKED_FOR_MAPPING'
and user_id = {0} and project_id = {1};""".format(row[0], project_id)
total_mapping_time = db.engine.execute(sql)
for time in total_mapping_time:
total_mapping_time = time[0]
if total_mapping_time:
user_contrib.time_spent_mapping = total_mapping_time.total_seconds()
user_contrib.total_time_spent += user_contrib.time_spent_mapping
sql = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE action='LOCKED_FOR_VALIDATION'
and user_id = {0} and project_id = {1};""".format(row[0], project_id)
total_validation_time = db.engine.execute(sql)
for time in total_validation_time:
total_validation_time = time[0]
if total_validation_time:
user_contrib.time_spent_validating = total_validation_time.total_seconds()
user_contrib.total_time_spent += user_contrib.time_spent_validating
contrib_dto.user_contributions.append(user_contrib)
return contrib_dto