def indiv_worst_day_points():
ridersq = text("""
select count(distinct(athlete_id)) as riders from rides group by date(start_date)
""")
riders = [
x['riders'] for x in meta.scoped_session().execute(ridersq).fetchall()
]
median_riders = 0 if len(riders) == 0 else median(riders)
q = text(f"""
select A.id as athlete_id, A.team_id, A.display_name as athlete_name, T.name as team_name,
sum(s.distance) as total_distance, sum(s.points) as total_score, sum(s.adj_points) as total_adjusted,
count(s.points) as days_ridden from
(select DS.athlete_id, DS.distance, DS.points, DS.ride_date, DDS.num_riders, (DS.points*POW(1.025,({median_riders}-DDS.num_riders))) adj_points from daily_scores DS,
(select ride_date, count(distinct(athlete_id)) as num_riders from daily_scores group by ride_date order by ride_date) DDS where DS.ride_date=DDS.ride_date) s
join lbd_athletes A on A.id = s.athlete_id
join teams T on T.id = A.team_id
group by A.id, A.display_name
order by total_adjusted desc;
""")
data = [(x['athlete_name'], x['team_name'], x['total_distance'],
x['total_score'], x['total_adjusted'], x['days_ridden'])
for x in meta.scoped_session().execute(q).fetchall()]
return render_template('alt_scoring/indiv_worst_day_points.html',
data=data,
competition_title=config.COMPETITION_TITLE,
median=median_riders)
def ride_refetch_photos():
ride_id = request.form['id']
ride = meta.scoped_session().query(Ride).filter(Ride.id == ride_id).filter(Ride.athlete_id == session.get('athlete_id')).one()
ride.photos_fetched = False
logging.info("Marking photos to be refetched for ride {}".format(ride))
meta.scoped_session().commit()
return jsonify(success=True) # I don't really have anything useful to spit back.
def write_ride_streams(streams, ride):
"""
Store GPS track for activity as LINESTRING in db.
:param streams: The Strava :class:`stravalib.orm.Activity` object.
:type streams: list[stravalib.orm.Stream]
:param ride: The db model object for ride.
:type ride: :class:`bafs.orm.Ride`
"""
try:
streams_dict = {s.type: s for s in streams}
""" :type: dict[str,stravalib.orm.Stream] """
lonlat_points = [(lon,lat) for (lat,lon) in streams_dict['latlng'].data]
if not lonlat_points:
raise ValueError("No data points in latlng streams.")
except (KeyError, ValueError) as x:
log.info("No GPS track for activity {} (skipping): {}".format(ride, x), exc_info=log.isEnabledFor(logging.DEBUG))
ride.track_fetched = None
else:
# Start by removing any existing segments for the ride.
meta.engine.execute(RideTrack.__table__.delete().where(RideTrack.ride_id == ride.id))
gps_track = WKTSpatialElement(wktutils.linestring_wkt(lonlat_points))
ride_track = RideTrack()
ride_track.gps_track = gps_track
ride_track.ride_id = ride.id
ride_track.elevation_stream = streams_dict['altitude'].data
ride_track.time_stream = streams_dict['time'].data
meta.scoped_session().add(ride_track)
ride.track_fetched = True
def team_leaderboard_classic():
# Get teams sorted by points
q = team_leaderboard_query()
team_rows = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
q = text("""
select A.id as athlete_id, A.team_id, A.display_name as athlete_name,
(sum(WS.team_distance) + sum(WS.days)*10) as total_score,
sum(WS.team_distance) as total_distance,
sum(WS.days) as days_ridden
from weekly_stats WS
join athletes A on A.id = WS.athlete_id
group by A.id, A.display_name
order by total_score desc
;
""")
team_members = {}
for indiv_row in meta.scoped_session().execute(q).fetchall(): # @UndefinedVariable
team_members.setdefault(indiv_row['team_id'], []).append(indiv_row)
for team_id in team_members:
team_members[team_id] = reversed(sorted(team_members[team_id], key=lambda m: m['total_score']))
return render_template('leaderboard/team_text.html',
team_rows=team_rows,
team_members=team_members,
competition_title=config.COMPETITION_TITLE)
def people_show_person(user_id):
our_user = meta.scoped_session().query(Athlete).filter_by(
id=user_id).first()
if our_user.profile_photo and not str.startswith(our_user.profile_photo,
'http'):
our_user.profile_photo = 'https://www.strava.com/' + our_user.profile_photo
if not our_user:
abort(404)
our_team = meta.scoped_session().query(Team).filter_by(
id=our_user.team_id).first()
today = get_today()
week_start = today.date() - timedelta(days=(today.weekday()) % 7)
week_end = week_start + timedelta(days=6)
weekly_dist = 0
weekly_rides = 0
total_rides = 0
total_dist = 0
for r in our_user.rides:
total_rides += 1
total_dist += r.distance
ride_date = r.start_date.replace(tzinfo=timezone(r.timezone)).date()
if week_start <= ride_date <= week_end:
weekly_dist += r.distance
weekly_rides += 1
return render_template('people/show.html',
data={
"user": our_user,
"team": our_team,
"weekrides": weekly_rides,
"weektotal": weekly_dist,
"totaldist": total_dist,
"totalrides": total_rides
},
competition_title=config.COMPETITION_TITLE)
def team_leaderboard_classic():
# Get teams sorted by points
q = team_leaderboard_query()
# @UndefinedVariable
team_rows = meta.scoped_session().execute(q).fetchall()
q = text("""
select
A.id as athlete_id,
A.team_id,
A.display_name as athlete_name,
sum(DS.points) as total_score,
sum(DS.distance) as total_distance,
count(DS.points) as days_ridden
from
daily_scores DS join athletes A on A.id = DS.athlete_id
group by A.id, A.display_name
order by total_score desc
;
""")
team_members = {}
# @UndefinedVariable
for indiv_row in meta.scoped_session().execute(q).fetchall():
team_members.setdefault(indiv_row['team_id'], []).append(indiv_row)
for team_id in team_members:
team_members[team_id] = reversed(
sorted(team_members[team_id], key=lambda m: m['total_score']))
return render_template('leaderboard/team_text.html',
team_rows=team_rows,
team_members=team_members,
competition_title=config.COMPETITION_TITLE)
def people_show_person(user_id):
our_user = meta.scoped_session().query(Athlete).filter_by(id=user_id).first()
if not our_user:
abort(404)
our_team = meta.scoped_session().query(Team).filter_by(id=our_user.team_id).first()
today = get_today()
week_start = today.date() - timedelta(days=(today.weekday()) % 7)
week_end = week_start + timedelta(days=6)
weekly_dist = 0
weekly_rides = 0
total_rides = 0
total_dist = 0
for r in our_user.rides:
total_rides += 1
total_dist += r.distance
ride_date = r.start_date.replace(tzinfo=timezone(r.timezone)).date()
if week_start <= ride_date <= week_end:
weekly_dist += r.distance
weekly_rides += 1
return render_template('people/show.html', data={
"user": our_user,
"team": our_team,
"weekrides": weekly_rides,
"weektotal": weekly_dist,
"totaldist": total_dist,
"totalrides": total_rides},
competition_title=config.COMPETITION_TITLE)
def team_leaderboard():
"""
Loads the leaderboard data broken down by team.
"""
q = text("""
select T.id as team_id, T.name as team_name, sum(DS.points) as total_score,
sum(DS.distance) as total_distance
from daily_scores DS
join teams T on T.id = DS.team_id
where not T.leaderboard_exclude
group by T.id, T.name
order by total_score desc
;
""")
team_rows = meta.scoped_session().execute(
q).fetchall() # @UndefinedVariable
q = text("""
select A.id as athlete_id, A.team_id, A.display_name as athlete_name,
sum(DS.points) as total_score, sum(DS.distance) as total_distance,
count(DS.points) as days_ridden
from daily_scores DS
join lbd_athletes A on A.id = DS.athlete_id
group by A.id, A.display_name
order by total_score desc
;
""")
team_members = {}
for indiv_row in meta.scoped_session().execute(
q).fetchall(): # @UndefinedVariable
team_members.setdefault(indiv_row['team_id'], []).append(indiv_row)
for team_id in team_members:
team_members[team_id] = reversed(
sorted(team_members[team_id], key=lambda m: m['total_score']))
rows = []
for i, res in enumerate(team_rows):
place = i + 1
members = [{
'athlete_id': member['athlete_id'],
'athlete_name': member['athlete_name'],
'total_score': member['total_score'],
'total_distance': member['total_distance'],
'days_ridden': member['days_ridden']
} for member in team_members.get(res['team_id'], [])]
rows.append({
'team_name': res['team_name'],
'total_score': res['total_score'],
'total_distance': res['total_distance'],
'team_id': res['team_id'],
'rank': place,
'team_members': members
})
return jsonify(dict(leaderboard=rows))
def stats_general():
q = text("""select count(*) as num_contestants from lbd_athletes""")
indiv_count_res = meta.scoped_session().execute(q).fetchone() # @UndefinedVariable
contestant_count = indiv_count_res['num_contestants']
q = text("""
select count(*) as num_rides, coalesce(sum(R.moving_time),0) as moving_time,
coalesce(sum(R.distance),0) as distance
from rides R
;
""")
all_res = meta.scoped_session().execute(q).fetchone() # @UndefinedVariable
total_miles = int(all_res['distance'])
total_hours = uh.timedelta_to_seconds(timedelta(seconds=int(all_res['moving_time']))) / 3600
total_rides = all_res['num_rides']
q = text("""
select count(*) as num_rides, coalesce(sum(R.moving_time),0) as moving_time
from rides R
join ride_weather W on W.ride_id = R.id
where W.ride_temp_avg < 32
;
""")
sub32_res = meta.scoped_session().execute(q).fetchone() # @UndefinedVariable
sub_freezing_hours = uh.timedelta_to_seconds(timedelta(seconds=int(sub32_res['moving_time']))) / 3600
q = text("""
select count(*) as num_rides, coalesce(sum(R.moving_time),0) as moving_time
from rides R
join ride_weather W on W.ride_id = R.id
where W.ride_rain = 1
;
""")
rain_res = meta.scoped_session().execute(q).fetchone() # @UndefinedVariable
rain_hours = uh.timedelta_to_seconds(timedelta(seconds=int(rain_res['moving_time']))) / 3600
q = text("""
select count(*) as num_rides, coalesce(sum(R.moving_time),0) as moving_time
from rides R
join ride_weather W on W.ride_id = R.id
where W.ride_snow = 1
;
""")
snow_res = meta.scoped_session().execute(q).fetchone() # @UndefinedVariable
snow_hours = uh.timedelta_to_seconds(timedelta(seconds=int(snow_res['moving_time']))) / 3600
return jsonify(
team_count=len(config.COMPETITION_TEAMS),
contestant_count=contestant_count,
total_rides=total_rides,
total_hours=total_hours,
total_miles=total_miles,
rain_hours=rain_hours,
snow_hours=snow_hours,
sub_freezing_hours=sub_freezing_hours)
def disambiguate_athlete_display_names():
q = meta.scoped_session().query(orm.Athlete)
q = q.filter(orm.Athlete.access_token != None)
athletes = q.all()
# Ok, here is the plan; bin these things together based on firstname and last initial.
# Then iterate over each bin and if there are multiple entries, find the least number
# of letters to make them all different. (we should be able to use set intersection
# to check for differences within the bins?)
def firstlast(name):
name_parts = a.name.split(' ')
fname = name_parts[0]
if len(name_parts) < 2:
lname = None
else:
lname = name_parts[-1]
return (fname, lname)
athletes_bin = {}
for a in athletes:
(fname, lname) = firstlast(a.name)
if lname is None:
# We only care about people with first and last names for this exercise
# key = fname
continue
else:
key = '{0} {1}'.format(fname, lname[0])
athletes_bin.setdefault(key, []).append(a)
for (name_key, athletes) in athletes_bin.items():
shortest_lname = min([firstlast(a.name)[1] for a in athletes], key=len)
required_length = None
for i in range(len(shortest_lname)):
# Calculate fname + lname-of-x-chars for each athlete.
# If unique, then use this number and update the model objects
candidate_short_lasts = [firstlast(a.name)[1][:i + 1] for a in athletes]
if len(set(candidate_short_lasts)) == len(candidate_short_lasts):
required_length = i + 1
break
if required_length is not None:
for a in athletes:
fname, lname = firstlast(a.name)
log.debug("Converting '{fname} {lname}' -> '{fname} {minlname}".format(fname=fname,
lname=lname,
minlname=lname[
:required_length]))
a.display_name = '{0} {1}'.format(fname, lname[:required_length])
else:
log.debug("Unable to find a minimum lastname; using full lastname.")
# Just use full names
for a in athletes:
fname, lname = firstlast(a.name)
a.display_name = '{0} {1}'.format(fname, lname[:required_length])
# Update the database with new values
meta.scoped_session().commit()
def ride_refetch_photos():
ride_id = request.form['id']
ride = meta.scoped_session().query(Ride).filter(Ride.id == ride_id).filter(
Ride.athlete_id == session.get('athlete_id')).one()
ride.photos_fetched = False
logging.info("Marking photos to be refetched for ride {}".format(ride))
meta.scoped_session().commit()
return jsonify(
success=True) # I don't really have anything useful to spit back.
def team_leaderboard():
"""
Loads the leaderboard data broken down by team.
"""
q = text("""
select T.id as team_id, T.name as team_name, sum(DS.points) as total_score,
sum(DS.distance) as total_distance
from daily_scores DS
join teams T on T.id = DS.team_id
where not T.leaderboard_exclude
group by T.id, T.name
order by total_score desc
;
""")
team_rows = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
q = text("""
select A.id as athlete_id, A.team_id, A.display_name as athlete_name,
sum(DS.points) as total_score, sum(DS.distance) as total_distance,
count(DS.points) as days_ridden
from daily_scores DS
join lbd_athletes A on A.id = DS.athlete_id
group by A.id, A.display_name
order by total_score desc
;
""")
team_members = {}
for indiv_row in meta.scoped_session().execute(q).fetchall(): # @UndefinedVariable
team_members.setdefault(indiv_row['team_id'], []).append(indiv_row)
for team_id in team_members:
team_members[team_id] = reversed(sorted(team_members[team_id], key=lambda m: m['total_score']))
rows = []
for i, res in enumerate(team_rows):
place = i + 1
members = [{'athlete_id': member['athlete_id'],
'athlete_name': member['athlete_name'],
'total_score': member['total_score'],
'total_distance': member['total_distance'],
'days_ridden': member['days_ridden']}
for member in team_members.get(res['team_id'], [])]
rows.append({
'team_name': res['team_name'],
'total_score': res['total_score'],
'total_distance': res['total_distance'],
'team_id': res['team_id'],
'rank': place,
'team_members': members
})
return jsonify(dict(leaderboard=rows))
def update_athlete_auth(strava_athlete, token_dict):
"""
Update auth tokens for specified athlete
:return: The updated athlete model object, or None if no athlete found.
:rtype: :class:`bafs.orm.Athlete`
"""
athlete = meta.scoped_session().query(Athlete).get(strava_athlete.id)
if athlete is not None:
athlete.access_token = token_dict['access_token']
athlete.refresh_token = token_dict['refresh_token']
athlete.expires_at = token_dict['expires_at']
meta.scoped_session().add(athlete)
meta.scoped_session().commit()
return athlete
def update_athlete_auth(strava_athlete, token_dict):
"""
Update auth tokens for specified athlete
:return: The updated athlete model object, or None if no athlete found.
:rtype: :class:`bafs.orm.Athlete`
"""
athlete = meta.scoped_session().query(Athlete).get(strava_athlete.id)
if athlete is not None:
athlete.access_token = token_dict['access_token']
athlete.refresh_token = token_dict['refresh_token']
athlete.expires_at = token_dict['expires_at']
meta.scoped_session().add(athlete)
meta.scoped_session().commit()
return athlete
def update_ride_complete(self, strava_activity: Activity, ride: Ride):
"""
Updates all ride data from a fully-populated Strava `Activity`.
:param strava_activity: The Activity that has been populated from detailed fetch.
:param ride: The database ride object to update.
"""
session = meta.scoped_session()
# We do this just to take advantage of the use-cache/only-cache feature for reprocessing activities.
self.update_ride_basic(strava_activity=strava_activity, ride=ride)
session.flush()
try:
self.logger.info("Writing out efforts for {!r}".format(ride))
self.write_ride_efforts(strava_activity, ride)
session.flush()
except:
self.logger.error(
"Error writing efforts for activity {0}, athlete {1}".format(
ride.id, ride.athlete),
exc_info=self.logger.isEnabledFor(logging.DEBUG))
raise
try:
self.logger.info("Writing out primary photo for {!r}".format(ride))
if strava_activity.total_photo_count > 0:
self.write_ride_photo_primary(strava_activity, ride)
else:
self.logger.debug("No photos for {!r}".format(ride))
except:
self.logger.error(
"Error writing primary photo for activity {}, athlete {}".
format(ride.id, ride.athlete),
exc_info=self.logger.isEnabledFor(logging.DEBUG))
raise
ride.detail_fetched = True
def points_per_mile():
"""
Note: set num_days to the minimum number of ride days to be eligible for the prize.
This was 33 in 2017, 36 in 2018, and 40 in 2019.
(@hozn noted: I didn't pay enough attention to determine if this is something we can calculate.)
"""
num_days = 40
query = text("""
select
A.id,
A.display_name as athlete_name,
sum(B.distance) as dist,
sum(B.points) as pnts,
count(B.athlete_id) as ridedays
from lbd_athletes A join daily_scores B on A.id = B.athlete_id group by athlete_id;
""")
ppm = [(x['athlete_name'], x['pnts'], x['dist'], (x['pnts'] / x['dist']),
x['ridedays'])
for x in meta.scoped_session().execute(query).fetchall()]
ppm.sort(key=lambda tup: tup[3], reverse=True)
return render_template('pointless/points_per_mile.html',
data={
"riders": ppm,
"days": num_days
})
def kidsathlon():
q = text("""
select
A.id as athlete_id,
A.display_name as athlete_name,
sum(case when (upper(R.name) like '%#KIDICAL%' and upper(R.name) like '%#WITHKID%') then R.distance else 0 end) as miles_both,
sum(case when (upper(R.name) like '%#KIDICAL%' and upper(R.name) not like '%#WITHKID%')then R.distance else 0 end) as kidical,
sum(case when (upper(R.name) like '%#WITHKID%' and upper(R.name) not like '%#KIDICAL%') then R.distance else 0 end) as withkid
from lbd_athletes A
join rides R on R.athlete_id = A.id
where (upper(R.name) like '%#KIDICAL%' or upper(R.name) like '%#WITHKID%')
group by A.id, A.display_name
""")
data = []
for x in meta.scoped_session().execute(q).fetchall():
miles_both = float(x['miles_both'])
kidical = miles_both + float(x['kidical'])
withkid = miles_both + float(x['withkid'])
if kidical > 0 and withkid > 0:
kidsathlon = kidical + withkid - miles_both
else:
kidsathlon = float(0)
data.append(
(x['athlete_id'], x['athlete_name'], kidical, withkid, kidsathlon))
return render_template(
'pointless/kidsathlon.html',
data={'tdata': sorted(data, key=lambda v: v[4], reverse=True)})
def teardown_request(exception):
session = meta.scoped_session()
if exception:
session.rollback()
else:
session.commit()
meta.scoped_session.remove()
def index():
page = int(request.args.get('page', 1))
if page < 1:
page = 1
page_size = 60
offset = page_size * (page - 1)
limit = page_size
log.debug("Page = {0}, offset={1}, limit={2}".format(page, offset, limit))
total_q = meta.scoped_session().query(RidePhoto).join(Ride).order_by(
Ride.start_date.desc())
num_photos = total_q.count()
page_q = total_q.limit(limit).offset(offset)
if num_photos < offset:
page = 1
total_pages = int(math.ceil((1.0 * num_photos) / page_size))
if page > total_pages:
page = total_pages
return render_template('photos.html',
photos=page_q,
page=page,
total_pages=total_pages,
competition_title=config.COMPETITION_TITLE)
def team_elev_gain():
q = text("""
select T.id, T.name as team_name, sum(R.elevation_gain) as cumul_elev_gain
from rides R
join lbd_athletes A on A.id = R.athlete_id
join teams T on T.id = A.team_id
group by T.id, team_name
order by cumul_elev_gain desc
;
""")
team_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Score', 'type': 'number'},
# {"id":"","label":"","pattern":"","type":"number","p":{"role":"interval"}},
]
rows = []
for i, res in enumerate(team_q):
place = i + 1
cells = [{'v': res['team_name'], 'f': '{0} [{1}]'.format(res['team_name'], place)},
{'v': res['cumul_elev_gain'], 'f': str(int(res['cumul_elev_gain']))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_leaderboard_data():
"""
Loads the leaderboard data broken down by team.
"""
q = text("""
select A.id as athlete_id, A.display_name as athlete_name, sum(DS.points) as total_score
from daily_scores DS
join lbd_athletes A on A.id = DS.athlete_id
group by A.id, A.display_name
order by total_score desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Score', 'type': 'number'},
# {"id":"","label":"","pattern":"","type":"number","p":{"role":"interval"}},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['total_score'], 'f': str(int(res['total_score']))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def teardown_request(exception):
session = meta.scoped_session()
if exception:
session.rollback()
else:
session.commit()
meta.scoped_session.remove()
def ridedays():
q = text("""
SELECT
a.id,
a.display_name,
count(b.ride_date) as rides,
sum(b.distance) as miles,
max(b.ride_date) as lastride
FROM
lbd_athletes a,
daily_scores b where a.id = b.athlete_id
group by b.athlete_id
order by
rides desc,
miles desc,
display_name
;
""")
loc_time = get_today()
loc_total_days = loc_time.timetuple().tm_yday
ride_days = [(x['id'], x['display_name'], x['rides'], x['miles'],
x['lastride'] >= loc_time.date())
for x in meta.scoped_session().execute(q).fetchall()]
return render_template('people/ridedays.html',
ride_days=ride_days,
num_days=loc_total_days)
def people_list_users():
users_list = meta.scoped_session().query(Athlete).filter(
Athlete.team.has(leaderboard_exclude=0)).order_by(
Athlete.name) # @UndefinedVariable
today = get_today()
week_start = today.date() - timedelta(days=(today.weekday()) % 7)
week_end = week_start + timedelta(days=6)
users = []
for u in users_list:
weekly_dist = 0
weekly_rides = 0
total_rides = 0
total_dist = 0
for r in u.rides:
total_rides += 1
total_dist += r.distance
ride_date = r.start_date.replace(
tzinfo=timezone(r.timezone)).date()
if week_start <= ride_date <= week_end:
weekly_dist += r.distance
weekly_rides += 1
users.append({
"name": u.display_name,
"id": u.id,
"weekrides": weekly_rides,
"weektotal": weekly_dist,
"totaldist": total_dist,
"totalrides": total_rides
})
return render_template('people/list.html',
users=users,
weekstart=week_start,
weekend=week_end,
competition_title=config.COMPETITION_TITLE)
def team_weekly_points():
"""
"""
q = text("""
select WS.team_id, WS.team_name, WS.week_num, (sum(WS.team_distance) + sum(WS.days)*10) as total_score
from weekly_stats WS group by WS.team_id, WS.team_name, WS.week_num order by WS.team_id, WS.week_num
;
""")
cols = [{'id': 'week', 'label': 'Week No.', 'type': 'string'}]
rows = []
res = meta.scoped_session().execute(q).fetchall()
d = defaultdict(list)
for r in res:
d[r['week_num']].append(
(r['team_id'], r['team_name'], r['total_score']))
for week, datalist in d.items():
cells = [{'v': 'Week {0}'.format(week), 'f': 'Week {0}'.format(week)}]
for team_id, team_name, total_score in datalist:
cells.append({
'v': total_score,
'f': '{0:.2f}'.format(total_score)
})
rows.append({'c': cells})
teams = {(r['team_id'], r['team_name'])
for r in res} #first time using a set comprehension, pretty sweet
for id, name in teams:
cols.append({
'id': 'team_{0}'.format(id),
'label': name,
'type': 'number'
})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_freezing():
q = text("""
select R.athlete_id, A.display_name as athlete_name, sum(R.distance) as distance
from rides R
join ride_weather W on W.ride_id = R.id
join lbd_athletes A on A.id = R.athlete_id
where W.ride_temp_avg < 32
group by R.athlete_id, athlete_name
order by distance desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Miles Below Freezing', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['distance'], 'f': "{0:.2f}".format(res['distance'])}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def distance_by_lowtemp():
"""
"""
q = text("""
select date(start_date) as start_date,
avg(W.day_temp_min) as low_temp,
sum(R.distance) as distance
from rides R join ride_weather W on W.ride_id = R.id
group by date(start_date)
order by date(start_date);
""")
cols = [{'id': 'date', 'label': 'Date', 'type': 'date'},
{'id': 'distance', 'label': 'Distance', 'type': 'number'},
{'id': 'day_temp_min', 'label': 'Low Temp', 'type': 'number'},
]
rows = []
for res in meta.scoped_session().execute(q): # @UndefinedVariable
if res['low_temp'] is None:
# This probably only happens for *today* since that isn't looked up yet.
continue
# res['start_date']
dt = res['start_date']
rows.append({'date': {'year': dt.year, 'month': dt.month, 'day': dt.day},
'distance': res['distance'],
'low_temp': res['low_temp']})
return jsonify({'data': rows})
def riders_by_lowtemp():
"""
"""
q = text("""
select date(start_date) as start_date,
avg(W.day_temp_min) as low_temp,
count(distinct R.athlete_id) as riders
from rides R join ride_weather W on W.ride_id = R.id
group by date(start_date)
order by date(start_date);
""")
cols = [{'id': 'date', 'label': 'Date', 'type': 'date'},
{'id': 'riders', 'label': 'Riders', 'type': 'number'},
{'id': 'day_temp_min', 'label': 'Low Temp', 'type': 'number'},
]
rows = []
for res in meta.scoped_session().execute(q): # @UndefinedVariable
if res['low_temp'] is None:
# This probably only happens for *today* since that isn't looked up yet.
continue
cells = [{'v': res['start_date']},
{'v': res['riders'], 'f': '{0}'.format(res['riders'])},
{'v': res['low_temp'], 'f': '{0:.1f}F'.format(res['low_temp'])},
]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def team_weekly_points():
"""
"""
q = text("""
select WS.team_id, WS.team_name, WS.week_num, (sum(WS.team_distance) + sum(WS.days)*10) as total_score
from weekly_stats WS group by WS.team_id, WS.team_name, WS.week_num order by WS.team_id, WS.week_num
;
""")
cols = [{'id': 'week', 'label': 'Week No.', 'type': 'string'}]
rows = []
res = meta.scoped_session().execute(q).fetchall()
d = defaultdict(list)
for r in res:
d[r['week_num']].append((r['team_id'], r['team_name'], r['total_score']))
for week, datalist in d.items():
cells = [{'v': 'Week {0}'.format(week), 'f': 'Week {0}'.format(week)}]
for team_id, team_name, total_score in datalist:
cells.append({'v': total_score, 'f': '{0:.2f}'.format(total_score)})
rows.append({'c': cells})
teams = {(r['team_id'], r['team_name']) for r in res} #first time using a set comprehension, pretty sweet
for id, name in teams:
cols.append({'id': 'team_{0}'.format(id), 'label': name, 'type': 'number'})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_after_sunset():
q = text("""
select R.athlete_id, A.display_name as athlete_name,
sum(time_to_sec(D.after_sunset)) as dark
from ride_daylight D
join rides R on R.id = D.ride_id
join lbd_athletes A on A.id = R.athlete_id
group by R.athlete_id, athlete_name
order by dark desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'After Sunset', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['dark'], 'f': str(timedelta(seconds=int(res['dark'])))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_freezing():
q = text("""
select R.athlete_id, A.display_name as athlete_name, sum(R.distance) as distance
from rides R
join ride_weather W on W.ride_id = R.id
join lbd_athletes A on A.id = R.athlete_id
where W.ride_temp_avg < 32
group by R.athlete_id, athlete_name
order by distance desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Miles Below Freezing', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['distance'], 'f': "{0:.2f}".format(res['distance'])}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def team_avg_speed():
q = text("""
select T.id, T.name as team_name, SUM(R.distance) / (SUM(R.moving_time) / 3600) as avg_speed
from rides R
join lbd_athletes A on A.id = R.athlete_id
join teams T on T.id = A.team_id
where R.manual = false
group by T.id, T.name
order by avg_speed desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Team', 'type': 'string'},
{'id': 'score', 'label': 'Average Speed', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['team_name'], 'f': '{0} [{1}]'.format(res['team_name'], place)},
{'v': res['avg_speed'], 'f': "{0:.2f}".format(res['avg_speed'])}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_after_sunset():
q = text("""
select R.athlete_id, A.display_name as athlete_name,
sum(time_to_sec(D.after_sunset)) as dark
from ride_daylight D
join rides R on R.id = D.ride_id
join lbd_athletes A on A.id = R.athlete_id
group by R.athlete_id, athlete_name
order by dark desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'After Sunset', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['dark'], 'f': str(timedelta(seconds=int(res['dark'])))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_freeze_points():
q = indiv_freeze_query()
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [
{
'id': 'name',
'label': 'Athlete',
'type': 'string'
},
{
'id': 'score',
'label': 'Freeze Points',
'type': 'number'
},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{
'v': res['athlete_name'],
'f': '{0} [{1}]'.format(res['athlete_name'], place)
}, {
'v': res['freeze_points_total'],
'f': "{0:.2f}".format(res['freeze_points_total'])
}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def exec_and_jsonify_query(
q,
display_label,
query_label,
hover_lambda=lambda res, query_label: str(int(res[query_label]))):
cols = [
{
'id': 'name',
'label': 'Athlete',
'type': 'string'
},
{
'id': 'score',
'label': display_label,
'type': 'number'
},
]
indiv_q = meta.scoped_session().execute(q).fetchall()
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{
'v': res['athlete_name'],
'f': '{0} [{1}]'.format(res['athlete_name'], place)
}, {
'v': res[query_label],
'f': hover_lambda(res, query_label)
}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def distance_by_lowtemp():
"""
"""
q = text("""
select date(start_date) as start_date,
avg(W.day_temp_min) as low_temp,
sum(R.distance) as distance
from rides R join ride_weather W on W.ride_id = R.id
group by date(start_date)
order by date(start_date);
""")
cols = [{'id': 'date', 'label': 'Date', 'type': 'date'},
{'id': 'distance', 'label': 'Distance', 'type': 'number'},
{'id': 'day_temp_min', 'label': 'Low Temp', 'type': 'number'},
]
rows = []
for res in meta.scoped_session().execute(q): # @UndefinedVariable
if res['low_temp'] is None:
# This probably only happens for *today* since that isn't looked up yet.
continue
# res['start_date']
dt = res['start_date']
rows.append({'date': {'year': dt.year, 'month': dt.month, 'day': dt.day},
'distance': res['distance'],
'low_temp': res['low_temp']})
return jsonify({'data': rows})
def indiv_freeze():
q = indiv_freeze_query()
data = [(x['athlete_name'], x['freeze_points_total'])
for x in meta.scoped_session().execute(q).fetchall()]
return render_template('alt_scoring/indiv_freeze.html',
indiv_freeze=data,
competition_title=config.COMPETITION_TITLE)
def people_list_users():
users_list = meta.scoped_session().query(Athlete).filter(Athlete.team.has(leaderboard_exclude=0)).order_by(Athlete.name) # @UndefinedVariable
today = get_today()
week_start = today.date() - timedelta(days=(today.weekday()) % 7)
week_end = week_start + timedelta(days=6)
users = []
for u in users_list:
weekly_dist = 0
weekly_rides = 0
total_rides = 0
total_dist = 0
for r in u.rides:
total_rides += 1
total_dist += r.distance
ride_date = r.start_date.replace(tzinfo=timezone(r.timezone)).date()
if week_start <= ride_date <= week_end:
weekly_dist += r.distance
weekly_rides += 1
users.append({"name": u.display_name,
"id": u.id,
"weekrides": weekly_rides,
"weektotal": weekly_dist,
"totaldist": total_dist,
"totalrides": total_rides})
return render_template('people/list.html',
users=users,
weekstart=week_start,
weekend=week_end,
competition_title=config.COMPETITION_TITLE)
def team_daily():
q = text(
"""select a.ride_date, b.name as team_name, sum(a.points) as team_score from daily_scores a,
teams b where a.team_id=b.id and b.leaderboard_exclude=0
group by a.ride_date, b.name order by a.ride_date, team_score;""")
temp = [(x['ride_date'], x['team_name'])
for x in meta.scoped_session().execute(q).fetchall()]
temp = groupby(temp, lambda x: x[0])
team_daily = defaultdict(list)
team_total = defaultdict(int)
for date, team in temp:
score_list = enumerate([x[1] for x in team], 1)
for a, b in score_list:
if not team_daily.get(date):
team_daily[date] = {}
team_daily[date].update({a: b})
if not team_total.get(b):
team_total[b] = 0
team_total[b] += (a)
team_daily = [(a, b) for a, b in team_daily.items()]
team_daily = sorted(team_daily)
#NOTE: team_daily calculated to show the scores for each day
# chart is too big to display, but leaving the calculation here just in case
team_total = [(b, a) for a, b in team_total.items()]
team_total = sorted(team_total, reverse=True)
return render_template('alt_scoring/team_daily.html',
team_total=team_total,
competition_title=config.COMPETITION_TITLE)
def team_sleaze():
q = team_sleaze_query()
data = [(x['team_name'], x['num_sleaze_days'])
for x in meta.scoped_session().execute(q).fetchall()]
return render_template('alt_scoring/team_sleaze.html',
team_sleaze=data,
competition_title=config.COMPETITION_TITLE)
def indiv_leaderboard_data():
"""
Loads the leaderboard data broken down by team.
"""
q = text("""
select A.id as athlete_id, A.display_name as athlete_name, sum(DS.points) as total_score
from daily_scores DS
join lbd_athletes A on A.id = DS.athlete_id
group by A.id, A.display_name
order by total_score desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Score', 'type': 'number'},
# {"id":"","label":"","pattern":"","type":"number","p":{"role":"interval"}},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['total_score'], 'f': str(int(res['total_score']))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def individual_leaderboard_text():
q = text("""
select
A.id as athlete_id,
A.team_id,
A.display_name as athlete_name,
T.name as team_name,
sum(DS.distance) as total_distance,
sum(DS.points) as total_score,
count(DS.points) as days_ridden
from
daily_scores DS
join lbd_athletes A on A.id = DS.athlete_id
join teams T on T.id = A.team_id
where not T.leaderboard_exclude
group by A.id, A.display_name
order by total_score desc
;
""")
# @UndefinedVariable
indiv_rows = meta.scoped_session().execute(q).fetchall()
return render_template('leaderboard/indiv_text.html',
indiv_rows=indiv_rows,
competition_title=config.COMPETITION_TITLE)
def write_ride_photo_primary(self, strava_activity: Activity, ride: Ride):
"""
Store primary photo for activity from the main detail-level activity.
:param strava_activity: The Strava :class:`stravalib.orm.Activity` object.
:type strava_activity: :class:`stravalib.orm.Activity`
:param ride: The db model object for ride.
:type ride: bafs.orm.Ride
"""
session = meta.scoped_session()
# If we have > 1 instagram photo, then we don't do anything.
if strava_activity.photo_count > 1:
self.logger.debug(
"Ignoring basic sync for {} since there are > 1 instagram photos."
)
return
# Start by removing any priamry photos for this ride.
meta.engine.execute(RidePhoto.__table__.delete().where(
and_(RidePhoto.ride_id == strava_activity.id,
RidePhoto.primary == True)))
primary_photo = strava_activity.photos.primary
if primary_photo:
if primary_photo.source == 1:
p = self._make_photo_from_native(primary_photo, ride)
else:
p = self._make_photo_from_instagram(primary_photo, ride)
session.add(p)
session.flush()
def team_weekly_points():
"""
"""
q = text("""
select
DS.team_id as team_id,
T.name as team_name,
sum(DS.points) as total_score,
week(DS.ride_date) as week_num
from
daily_scores DS inner join
teams T on T.id = DS.team_id
group by
team_id,
week_num
;
""")
cols = [{'id': 'week', 'label': 'Week No.', 'type': 'string'}]
rows = []
res = meta.scoped_session().execute(q).fetchall()
d = defaultdict(list)
for r in res:
d[r['week_num']].append((r['team_id'], r['team_name'], r['total_score']))
for week, datalist in d.items():
cells = [{'v': 'Week {0}'.format(week + 1), 'f': 'Week {0}'.format(week + 1)}]
for team_id, team_name, total_score in datalist:
cells.append({'v': total_score, 'f': '{0:.2f}'.format(total_score)})
rows.append({'c': cells})
teams = {(r['team_id'], r['team_name']) for r in res} #first time using a set comprehension, pretty sweet
for id, name in teams:
cols.append({'id': 'team_{0}'.format(id), 'label': name, 'type': 'number'})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def team_moving_time():
q = text("""
select T.id, T.name as team_name, sum(R.moving_time) as total_moving_time
from rides R
join lbd_athletes A on A.id = R.athlete_id
join teams T on T.id = A.team_id
group by T.id, T.name
order by total_moving_time desc
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Team', 'type': 'string'},
{'id': 'score', 'label': 'Moving Time', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['team_name'], 'f': '{0} [{1}]'.format(res['team_name'], place)},
{'v': res['total_moving_time'], 'f': str(timedelta(seconds=int(res['total_moving_time'])))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def team_elev_gain():
q = text("""
select T.id, T.name as team_name, sum(R.elevation_gain) as cumul_elev_gain
from rides R
join lbd_athletes A on A.id = R.athlete_id
join teams T on T.id = A.team_id
group by T.id, team_name
order by cumul_elev_gain desc
;
""")
team_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Score', 'type': 'number'},
# {"id":"","label":"","pattern":"","type":"number","p":{"role":"interval"}},
]
rows = []
for i, res in enumerate(team_q):
place = i + 1
cells = [{'v': res['team_name'], 'f': '{0} [{1}]'.format(res['team_name'], place)},
{'v': res['cumul_elev_gain'], 'f': str(int(res['cumul_elev_gain']))}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def riders_by_lowtemp():
"""
"""
q = text("""
select date(start_date) as start_date,
avg(W.day_temp_min) as low_temp,
count(distinct R.athlete_id) as riders
from rides R join ride_weather W on W.ride_id = R.id
group by date(start_date)
order by date(start_date);
""")
cols = [{'id': 'date', 'label': 'Date', 'type': 'date'},
{'id': 'riders', 'label': 'Riders', 'type': 'number'},
{'id': 'day_temp_min', 'label': 'Low Temp', 'type': 'number'},
]
rows = []
for res in meta.scoped_session().execute(q): # @UndefinedVariable
if res['low_temp'] is None:
# This probably only happens for *today* since that isn't looked up yet.
continue
cells = [{'v': res['start_date']},
{'v': res['riders'], 'f': '{0}'.format(res['riders'])},
{'v': res['low_temp'], 'f': '{0:.1f}F'.format(res['low_temp'])},
]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def team_leaderboard_data():
"""
Loads the leaderboard data broken down by team.
"""
q = team_leaderboard_query()
team_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [
{
'id': 'team_name',
'label': 'Team',
'type': 'string'
},
{
'id': 'score',
'label': 'Score',
'type': 'number'
},
# {"id":"","label":"","pattern":"","type":"number","p":{"role":"interval"}},
]
rows = []
for i, res in enumerate(team_q):
place = i + 1
cells = [{
'v': res['team_name'],
'f': '{0} [{1}]'.format(res['team_name'], place)
}, {
'v': res['total_score'],
'f': str(int(res['total_score']))
}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def team_number_sleaze_days():
q = team_sleaze_query()
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [
{
'id': 'name',
'label': 'Team',
'type': 'string'
},
{
'id': 'score',
'label': 'Sleaze Days',
'type': 'number'
},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{
'v': res['team_name'],
'f': '{0} [{1}]'.format(res['team_name'], place)
}, {
'v': res['num_sleaze_days'],
'f': str(int(res['num_sleaze_days']))
}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def ridedays():
q = text("""
SELECT
a.id,
a.display_name,
count(b.ride_date) as rides,
sum(b.distance) as miles,
max(b.ride_date) as lastride
FROM
lbd_athletes a,
daily_scores b where a.id = b.athlete_id
group by b.athlete_id
order by
rides desc,
miles desc,
display_name
;
""")
loc_time = get_today()
loc_total_days = loc_time.timetuple().tm_yday
ride_days = [(
x['id'],
x['display_name'],
x['rides'],
x['miles'],
x['lastride'] >= loc_time.date())
for x in meta.scoped_session().execute(q).fetchall()]
return render_template(
'people/ridedays.html',
ride_days=ride_days,
num_days=loc_total_days)
def list_photos():
photos = meta.scoped_session().query(RidePhoto).join(Ride).order_by(Ride.start_date.desc()).limit(20)
schema = RidePhotoSchema()
results = []
for p in photos:
results.append(schema.dump(p).data)
return jsonify(dict(result=results, count=len(results)))
def pointlesskids():
q = text("""
select name, distance from rides where upper(name) like '%WITHKID%';
""")
rs = meta.scoped_session().execute(q)
d = defaultdict(int)
for x in rs.fetchall():
for match in re.findall('(#withkid\w+)', x['name']):
d[match.replace('#withkid', '')] += x['distance']
return render_template('pointless/pointlesskids.html', data={'tdata':sorted(d.items(), key=lambda v: v[1], reverse=True)})
def register_athlete(strava_athlete, token_dict):
"""
Ensure specified athlete is added to database, returns athlete orm.
:return: The added athlete model object.
:rtype: :class:`bafs.orm.Athlete`
"""
athlete = meta.scoped_session().query(Athlete).get(strava_athlete.id)
if athlete is None:
athlete = Athlete()
athlete.id = strava_athlete.id
athlete.name = '{0} {1}'.format(strava_athlete.firstname, strava_athlete.lastname).strip()
# Temporary; we will update this in disambiguation phase. (This isn't optimal; needs to be
# refactored....
#
# Where does the disambiguation phase get called now? Nowhere...
# so let's fix it here for now.
# * Do not override already set display_names.
# * Use a first name and last initial (if available).
# See also:
# https://github.com/freezingsaddles/freezing-web/issues/80
# https://github.com/freezingsaddles/freezing-web/issues/75
# https://github.com/freezingsaddles/freezing-web/issues/73
# - @obscurerichard]
if athlete.display_name is None:
if strava_athlete.lastname is None:
athlete.display_name = strava_athlete.firstname
else:
athlete.display_name = '{0} {1}'.format(
strava_athlete.firstname.strip(),
strava_athlete.lastname.strip(),
)
athlete.profile_photo = strava_athlete.profile
athlete.access_token = token_dict['access_token']
athlete.refresh_token = token_dict['refresh_token']
athlete.expires_at = token_dict['expires_at']
meta.scoped_session().add(athlete)
# We really shouldn't be committing here, since we want to disambiguate names after registering
meta.scoped_session().commit()
return athlete
def rides_data():
athlete_id = session.get('athlete_id')
rides_q = meta.scoped_session().query(Ride).filter(Ride.athlete_id == athlete_id).order_by(Ride.start_date.desc())
results = []
for r in rides_q:
w = r.weather
if w:
avg_temp = w.ride_temp_avg
else:
avg_temp = None
results.append(dict(id=r.id,
private=r.private,
name=r.name,
start_date=r.start_date,
elapsed_time=r.elapsed_time,
moving_time=r.moving_time,
distance=r.distance,
photos_fetched=r.photos_fetched,
avg_temp=avg_temp
))
#rides = meta.session_factory().query(Ride).all()
return bt_jsonify(results)
# athlete_id = sa.Column(sa.BigInteger, sa.ForeignKey('athletes.id', ondelete='cascade'), nullable=False, index=True)
# elapsed_time = sa.Column(sa.Integer, nullable=False) # Seconds
# # in case we want to conver that to a TIME type ... (using time for interval is kinda mysql-specific brokenness, though)
# # time.strftime('%H:%M:%S', time.gmtime(12345))
# moving_time = sa.Column(sa.Integer, nullable=False, index=True) #
# elevation_gain = sa.Column(sa.Integer, nullable=True) # 269.6 (feet)
# average_speed = sa.Column(sa.Float) # mph
# maximum_speed = sa.Column(sa.Float) # mph
# start_date = sa.Column(sa.DateTime, nullable=False, index=True) # 2010-02-28T08:31:35Z
# distance = sa.Column(sa.Float, nullable=False, index=True) # 82369.1 (meters)
# location = sa.Column(sa.String(255), nullable=True)
#
# commute = sa.Column(sa.Boolean, nullable=True)
# trainer = sa.Column(sa.Boolean, nullable=True)
#
# efforts_fetched = sa.Column(sa.Boolean, default=False, nullable=False)
#
# timezone = sa.Column(sa.String(255), nullable=True)
#
# geo = orm.relationship("RideGeo", uselist=False, backref="ride", cascade="all, delete, delete-orphan")
# weather = orm.relationship("RideWeather", uselist=False, backref="ride", cascade="all, delete, delete-orphan")
# photos = orm.relationship("RidePhoto", backref="ride", cascade="all, delete, delete-orphan")
#
# photos_fetched = sa.Column(sa.Boolean, default=False, nullable=False)
# private = sa.Column(sa.Boolean, default=False, nullable=False)
def _geo_tracks(start_date=None, end_date=None, team_id=None):
# These dates must be made naive, since we don't have TZ info stored in our ride columns.
if start_date:
start_date = arrow.get(start_date).datetime.replace(tzinfo=None)
if end_date:
end_date = arrow.get(end_date).datetime.replace(tzinfo=None)
log.debug("Filtering on start_date: {}".format(start_date))
log.debug("Filtering on end_date: {}".format(end_date))
sess = meta.scoped_session()
q = sess.query(RideTrack).join(Ride).join(Athlete)
q = q.filter(Ride.private==False)
if team_id:
q = q.filter(Athlete.team_id==team_id)
if start_date:
q = q.filter(Ride.start_date >= start_date)
if end_date:
q = q.filter(Ride.start_date < end_date)
linestrings = []
for ride_track in q:
assert isinstance(ride_track, RideTrack)
ride_tz = pytz.timezone(ride_track.ride.timezone)
wkt = sess.scalar(ride_track.gps_track.wkt)
coordinates = []
for (i, (lon, lat)) in enumerate(parse_linestring(wkt)):
elapsed_time = ride_track.ride.start_date + timedelta(seconds=ride_track.time_stream[i])
point = (
float(Decimal(lon)),
float(Decimal(lat)),
float(Decimal(ride_track.elevation_stream[i])),
ride_tz.localize(elapsed_time).isoformat()
)
coordinates.append(point)
linestrings.append(coordinates)
geojson_structure = {"type": "MultiLineString", "coordinates": linestrings}
#return geojson.dumps(geojson.MultiLineString(linestrings))
return json.dumps(geojson_structure)
def _write_strava_photo_primary(photo, ride):
"""
Writes a strava native (source=1) primary photo to db.
:param photo: The primary photo from an activity.
:type photo: stravalib.orm.ActivityPhotoPrimary
:param ride: The db model object for ride.
:type ride: bafs.orm.Ride
:return: The newly added ride photo object.
:rtype: bafs.orm.RidePhoto
"""
# 'photos': {u'count': 1,
# u'primary': {u'id': None,
# u'source': 1,
# u'unique_id': u'35453b4b-0fc1-46fd-a824-a4548426b57d',
# u'urls': {u'100': u'https://dgtzuqphqg23d.cloudfront.net/Vvm_Mcfk1SP-VWdglQJImBvKzGKRJrHlNN4BqAqD1po-128x96.jpg',
# u'600': u'https://dgtzuqphqg23d.cloudfront.net/Vvm_Mcfk1SP-VWdglQJImBvKzGKRJrHlNN4BqAqD1po-768x576.jpg'}},
# u'use_primary_photo': False},
if not photo.urls:
log.warning("Photo {} present, but has no URLs (skipping)".format(photo))
return None
p = RidePhoto()
p.id = photo.unique_id
p.primary = True
p.source = photo.source
p.ref = None
p.img_l = photo.urls['600']
p.img_t = photo.urls['100']
p.ride_id = ride.id
log.debug("Writing (primary) Strava ride photo: {}".format(p))
meta.scoped_session().add(p)
meta.scoped_session().flush()
return p
def team_cumul_mileage():
"""
"""
teams = meta.scoped_session().query(Team).all() # @UndefinedVariable
q = text("""
select team_id, ride_date, points,
(@total_points := @total_points + points) AS cumulative_points,
(@total_distance := @total_distance + points) AS cumulative_distance
from daily_scores, (select @total_points := 0, @total_distance := 0) AS vars
where team_id = :team_id
order by ride_date;
""")
cols = [{'id': 'date', 'label': 'Date', 'type': 'date'}]
for team in teams:
cols.append({'id': 'team_{0}'.format(team.id), 'label': team.name, 'type': 'number'})
start_date = config.START_DATE
start_date = start_date.replace(tzinfo=None)
tpl_dict = dict(
[(dt.strftime('%Y-%m-%d'), None) for dt in rrule.rrule(rrule.DAILY, dtstart=start_date, until=datetime.now())])
# Query for each team, build this into a multidim array
daily_cumul = defaultdict(dict)
for team in teams:
daily_cumul[team.id] = copy.copy(
tpl_dict) # Ensure that we have keys for every day (even if there were no rides for that day)
for row in meta.engine.execute(q, team_id=team.id).fetchall(): # @UndefinedVariable
daily_cumul[team.id][row['ride_date'].strftime('%Y-%m-%d')] = row['cumulative_distance']
# Fill in any None gaps with the previous non-None value
prev_value = 0
for datekey in sorted(tpl_dict.keys()):
if daily_cumul[team.id][datekey] is None:
daily_cumul[team.id][datekey] = prev_value
else:
prev_value = daily_cumul[team.id][datekey]
rows = []
for datekey in sorted(tpl_dict.keys()):
cells = [{'v': parse_competition_timestamp(datekey).date()}]
for team in teams:
cells.append({'v': daily_cumul[team.id][datekey]})
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def write_ride_efforts(strava_activity, ride):
"""
Writes out all effort associated with a ride to the database.
:param strava_activity: The :class:`stravalib.orm.Activity` that is associated with this effort.
:type strava_activity: :class:`stravalib.orm.Activity`
:param ride: The db model object for ride.
:type ride: :class:`bafs.orm.Ride`
"""
assert isinstance(strava_activity, strava_model.Activity)
assert isinstance(ride, Ride)
try:
# Start by removing any existing segments for the ride.
meta.engine.execute(RideEffort.__table__.delete().where(RideEffort.ride_id == strava_activity.id))
# Then add them back in
for se in strava_activity.segment_efforts:
effort = RideEffort(id=se.id,
ride_id=strava_activity.id,
elapsed_time=timedelta_to_seconds(se.elapsed_time),
segment_name=se.segment.name,
segment_id=se.segment.id)
log.debug("Writing ride effort: {se_id}: {effort!r}".format(se_id=se.id,
effort=effort.segment_name))
meta.scoped_session().add(effort)
meta.scoped_session().flush()
ride.efforts_fetched = True
except:
log.exception("Error adding effort for ride: {0}".format(ride))
raise
def indiv_freeze_points():
q = indiv_freeze_query()
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'name', 'label': 'Athlete', 'type': 'string'},
{'id': 'score', 'label': 'Freeze Points', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
cells = [{'v': res['athlete_name'], 'f': '{0} [{1}]'.format(res['athlete_name'], place)},
{'v': res['freeze_points_total'], 'f': "{0:.2f}".format(res['freeze_points_total'])}]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
def indiv_elev_dist():
q = text("""
select R.athlete_id, A.display_name as athlete_name,
T.name as team_name,
SUM(R.elevation_gain) as total_elevation_gain,
SUM(R.distance) as total_distance,
SUM(R.distance) / (SUM(R.moving_time) / 3600) as avg_speed
from rides R
join lbd_athletes A on A.id = R.athlete_id
left join teams T on T.id = A.team_id
where not R.manual
group by R.athlete_id, athlete_name, team_name
;
""")
indiv_q = meta.scoped_session().execute(q).fetchall() # @UndefinedVariable
cols = [{'id': 'ID', 'label': 'ID', 'type': 'string'},
{'id': 'score', 'label': 'Distance', 'type': 'number'},
{'id': 'score', 'label': 'Elevation', 'type': 'number'},
{'id': 'ID', 'label': 'Team', 'type': 'string'},
{'id': 'score', 'label': 'Average Speed', 'type': 'number'},
]
rows = []
for i, res in enumerate(indiv_q):
place = i + 1
name_parts = res['athlete_name'].split(' ')
if len(name_parts) > 1:
short_name = ' '.join([name_parts[0], name_parts[-1]])
else:
short_name = res['athlete_name']
if res['team_name'] is None:
team_name = '(No team)'
else:
team_name = res['team_name']
cells = [{'v': res['athlete_name'], 'f': short_name},
{'v': res['total_distance'], 'f': '{0:.2f}'.format(res['total_distance'])},
{'v': res['total_elevation_gain'], 'f': '{0:.2f}'.format(res['total_elevation_gain'])},
{'v': team_name, 'f': team_name},
{'v': res['avg_speed'], 'f': "{0:.2f}".format(res['avg_speed'])},
]
rows.append({'c': cells})
return gviz_api_jsonify({'cols': cols, 'rows': rows})
