def get_group_leaderboard(group_id: int,
interval: str = None,
week_start: WeekStart = 'monday') -> ResultProxy:
"""
Get exercise statistics from users in a specific group. These statistics are used to build a leaderboard in the
application.
:param group_id: The unique id for the group.
:param interval: A string representing a time interval (week, month, or year).
:param week_start: Day of the week that is used to signify the start of the week.
:return: Records with a users exercise statistics over an interval.
"""
date = dates.get_start_date_interval(interval=interval,
week_start=week_start)
if date is None:
return db.session.execute(
"""
SELECT
groupmembers.username,
MAX(logs.first) AS first,
MAX(logs.last) AS last,
COALESCE(SUM(miles), 0) AS miles,
COALESCE(SUM(CASE WHEN type = 'run' THEN miles END), 0) AS miles_run,
COALESCE(SUM(CASE WHEN type = 'bike' THEN miles END), 0) AS miles_biked,
COALESCE(SUM(CASE WHEN type = 'swim' THEN miles END), 0) AS miles_swam,
COALESCE(SUM(CASE WHEN type = 'other' THEN miles END), 0) AS miles_other
FROM logs
INNER JOIN groupmembers ON logs.username = groupmembers.username
WHERE group_id = :group_id
AND status = 'accepted'
AND logs.deleted IS FALSE
AND groupmembers.deleted IS FALSE
GROUP BY groupmembers.username
ORDER BY miles DESC
""", {'group_id': group_id})
else:
return db.session.execute(
"""
SELECT
groupmembers.username,
MAX(logs.first) AS first,
MAX(logs.last) AS last,
COALESCE(SUM(miles), 0) AS miles,
COALESCE(SUM(CASE WHEN type = 'run' THEN miles END), 0) AS miles_run,
COALESCE(SUM(CASE WHEN type = 'bike' THEN miles END), 0) AS miles_biked,
COALESCE(SUM(CASE WHEN type = 'swim' THEN miles END), 0) AS miles_swam,
COALESCE(SUM(CASE WHEN type = 'other' THEN miles END), 0) AS miles_other
FROM logs
INNER JOIN groupmembers ON logs.username = groupmembers.username
WHERE group_id = :group_id
AND date >= :date
AND status = 'accepted'
AND logs.deleted IS FALSE
AND groupmembers.deleted IS FALSE
GROUP BY groupmembers.username
ORDER BY miles DESC
""", {
'group_id': group_id,
'date': date
})
def get_user_miles_interval(username: str, interval: str = None, week_start: str = 'monday') -> Column:
"""
Get the total number of miles exercised by a user in a certain time interval. The options include
the past year, month, or week.
:param username: Unique identifier for a user
:param interval: Time interval for logs to count towards the mileage total
:param week_start: An option for which day is used as the start of the week.
Both 'monday' and 'sunday' are valid options.
:return: The total number of miles exercised
"""
date = dates.get_start_date_interval(interval=interval, week_start=week_start)
if date is None:
result: ResultProxy = db.session.execute(
'''
SELECT SUM(miles) AS total
FROM logs
WHERE username=:username
AND deleted IS FALSE
''',
{'username': username}
)
else:
result: ResultProxy = db.session.execute(
'''
SELECT SUM(miles) AS total
FROM logs
WHERE username=:username
AND date >= :date
AND deleted IS FALSE
''',
{'username': username, 'date': date}
)
return result.first()
def get_user_avg_feel_interval(username: str, interval: str = None, week_start: str = 'monday') -> Column:
"""
Retrieve the average feel statistic for a user during a certain interval in time
:param username: Unique identifier for a user
:param interval: Time interval for logs to count towards the average feel
:param week_start: An option for which day is used as the start of the week.
Both 'monday' and 'sunday' are valid options.
:return: The average feel during the interval
"""
date = dates.get_start_date_interval(interval=interval, week_start=week_start)
if date is None:
result: ResultProxy = db.session.execute(
'''
SELECT AVG(feel) AS average
FROM logs
WHERE username=:username
AND deleted IS FALSE
''',
{'username': username}
)
else:
result: ResultProxy = db.session.execute(
'''
SELECT AVG(feel) AS average
FROM logs
WHERE username=:username
AND date >= :date
AND deleted IS FALSE
''',
{'username': username, 'date': date}
)
return result.first()
def get_group_miles_interval_by_type(group_name: str, exercise_type: str,
interval: str = None, week_start: str = 'monday') -> Column:
"""
Get the total number of miles exercised by all the group members in a certain time interval and of a specific
exercise type. The interval options include the past year, month, and week.
The exercise type options include run, bike, swim, and other.
:param group_name: A name which uniquely identifies a group.
:param interval: Time interval for logs to count towards the mileage total.
:param exercise_type: Type of exercise to filter the logs by.
:param week_start: An option for which day is used as the start of the week.
Both 'monday' and 'sunday' are valid options.
:return: The total number of miles for the given exercise type
"""
date = dates.get_start_date_interval(interval=interval, week_start=week_start)
if date is None:
result: ResultProxy = db.session.execute(
'''
SELECT SUM(miles) AS total
FROM logs
INNER JOIN groupmembers ON logs.username = groupmembers.username
WHERE group_name=:group_name
AND type=:exercise_type
AND status='accepted'
AND logs.deleted IS FALSE
AND groupmembers.deleted IS FALSE
''',
{'group_name': group_name, 'exercise_type': exercise_type}
)
return result.first()
else:
result: ResultProxy = db.session.execute(
'''
SELECT SUM(miles) AS total
FROM logs
INNER JOIN groupmembers ON logs.username = groupmembers.username
WHERE group_name=:group_name
AND type=:exercise_type
AND date >= :date
AND status='accepted'
AND logs.deleted IS FALSE
AND groupmembers.deleted IS FALSE
''',
{'group_name': group_name, 'exercise_type': exercise_type, 'date': date}
)
return result.first()
def get_group_avg_feel_interval(group_name: str, interval: str = None, week_start: str = 'monday') -> Column:
"""
Retrieve the average feel statistic for all group members during a certain interval in time.
:param group_name: A name which uniquely identifies a group.
:param interval: Time interval for logs to count towards the average feel
:param week_start: An option for which day is used as the start of the week.
Both 'monday' and 'sunday' are valid options.
:return: The average feel during the interval
"""
date = dates.get_start_date_interval(interval=interval, week_start=week_start)
if date is None:
result: ResultProxy = db.session.execute(
'''
SELECT AVG(feel) AS average
FROM logs
INNER JOIN groupmembers ON logs.username = groupmembers.username
WHERE group_name=:group_name
AND status='accepted'
AND logs.deleted IS FALSE
AND groupmembers.deleted IS FALSE
''',
{'group_name': group_name}
)
return result.first()
else:
result: ResultProxy = db.session.execute(
'''
SELECT AVG(feel) AS average
FROM logs
INNER JOIN groupmembers ON logs.username = groupmembers.username
WHERE group_name=:group_name
AND date >= :date
AND status='accepted'
AND logs.deleted IS FALSE
AND groupmembers.deleted IS FALSE
''',
{'group_name': group_name, 'date': date}
)
return result.first()
def get_user_miles_interval_by_type(username: str, exercise_type: str,
interval: str = None, week_start: str = 'monday') -> Column:
"""
Get the total number of miles exercised by a user in a certain time interval and specific exercise type.
The interval options include the past year, month, and week.
The exercise type options include run, bike, swim, and other.
:param username: Unique identifier for a user
:param interval: Time interval for logs to count towards the mileage total
:param exercise_type: Type of exercise to filter the logs by
:param week_start: An option for which day is used as the start of the week.
Both 'monday' and 'sunday' are valid options.
:return: The total number of miles for the given exercise type
"""
date = dates.get_start_date_interval(interval=interval, week_start=week_start)
if date is None:
result: ResultProxy = db.session.execute(
'''
SELECT SUM(miles) AS total
FROM logs
WHERE username=:username
AND type=:exercise_type
AND deleted IS FALSE
''',
{'username': username, 'exercise_type': exercise_type}
)
else:
result: ResultProxy = db.session.execute(
'''
SELECT SUM(miles) AS total
FROM logs
WHERE username=:username
AND type=:exercise_type
AND date >= :date
AND deleted IS FALSE
''',
{'username': username, 'date': date, 'exercise_type': exercise_type}
)
return result.first()