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.session_factory().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.session_factory().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 execute(self, options, args):
# if options.rewrite:
# meta.engine.execute(model.RidePhoto.__table__.delete())
# meta.session_factory().query(model.Ride).update({"photos_fetched": False})
q = meta.session_factory().query(RidePhoto)
q = q.filter_by(img_t=None)
insta_client = configured_instagram_client()
del_q = []
for ride_photo in q:
self.logger.debug("Updating URLs for photo {}".format(ride_photo))
try:
media = insta_client.media(ride_photo.id)
ride_photo.img_l = media.get_standard_resolution_url()
ride_photo.img_t = media.get_thumbnail_url()
meta.session_factory().commit()
except InstagramAPIError as e:
if e.status_code == 400:
self.logger.error("Skipping photo {}; user is set to private".format(ride_photo))
del_q.append(ride_photo.id)
else:
self.logger.exception("Error fetching instagram photo {0} (skipping)".format(ride_photo))
if del_q:
meta.engine.execute(RidePhoto.__table__.delete().where(RidePhoto.id.in_(del_q)))
meta.session_factory().commit()
def people_show_person(user_id):
our_user = meta.session_factory().query(Athlete).filter_by(id=user_id).first()
if not our_user:
abort(404)
our_team = meta.session_factory().query(Team).filter_by(id=our_user.team_id).first()
tdy = get_today()
week_start = tdy - timedelta(days=(tdy.weekday() + 1) % 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
if week_start <= r.start_date.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})
def ride_refetch_photos():
ride_id = request.form['id']
ride = meta.session_factory().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.session_factory().commit()
return jsonify(
success=True) # I don't really have anything useful to spit back.
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.session_factory().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():
"""
"""
teams = meta.session_factory().query(Team).all() # @UndefinedVariable
week_q = text("""
select sum(DS.points) as total_score
from daily_scores DS
join teams T on T.id = DS.team_id
where T.id = :team_id and week(DS.ride_date) = :week
;
""")
cols = [{'id': 'week', 'label': 'Week No.', 'type': 'string'}]
for t in teams:
cols.append({'id': 'team_{0}'.format(t.id), 'label': t.name, 'type': 'number'})
# This is a really inefficient way to do this, but it's also super simple. And I'm feeling lazy :)
start_date = parse_competition_timestamp(app.config['BAFS_START_DATE'])
start_date = start_date.replace(tzinfo=None)
week_r = rrule.rrule(rrule.WEEKLY, dtstart=start_date, until=datetime.now())
rows = []
for i, dt in enumerate(week_r):
week_no = dt.date().isocalendar()[1]
# these are 1-based, whereas mysql uses 0-based
cells = [{'v': 'Week {0}'.format(i + 1), 'f': 'Week {0}'.format(i + 1)}, # Competition always starts at week 1, regardless of isocalendar week no
]
for t in teams:
total_score = meta.engine.execute(week_q, team_id=t.id, week=week_no-1).scalar() # @UndefinedVariable
if total_score is None:
total_score = 0
cells.append({'v': total_score, 'f': '{0:.2f}'.format(total_score)})
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.session_factory().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 user_daily_points(athlete_id):
"""
"""
teams = meta.session_factory().query(Team).all() # @UndefinedVariable
day_q = text("""
select DS.points
from daily_scores DS
where DAYOFYEAR(DS.ride_date) = :yday
and DS.athlete_id = :id
;
""")
cols = [{'id': 'day', 'label': 'Day No.', 'type': 'string'}]
cols.append({'id': 'athlete_{0}'.format(athlete_id), 'label': '', 'type': 'number'})
# This is a really inefficient way to do this, but it's also super simple. And I'm feeling lazy :)
start_date = parse_competition_timestamp(app.config['BAFS_START_DATE'])
start_date = start_date.replace(tzinfo=None)
day_r = rrule.rrule(rrule.DAILY, dtstart=start_date, until=datetime.now())
rows = []
for i, dt in enumerate(day_r):
day_no = dt.timetuple().tm_yday
# these are 1-based, whereas mysql uses 0-based
cells = [{'v': '{0}'.format(dt.strftime('%b %d')), 'f': '{0}'.format(dt.strftime('%m/%d'))}, # Competition always starts at day 1, regardless of isocalendar day no
]
points = meta.engine.execute(day_q, id=athlete_id, yday=day_no).scalar() # @UndefinedVariable
if points is None:
points = 0
cells.append({'v': points, 'f': '{0:.2f}'.format(points)})
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.session_factory().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.session_factory().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.session_factory().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 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.session_factory().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 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.session_factory().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 averagespeed():
q = text("""
select a.id, a.display_name, avg(b.average_speed) as speed from lbd_athletes a, rides b where a.id = b.athlete_id group by a.id order by speed;
""")
avgspeed = [(x['id'], x['display_name'], x['speed'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('pointless/averagespeed.html', avg=avgspeed)
def team_leaderboard_data():
"""
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
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_q = meta.session_factory().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_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.session_factory().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 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.session_factory().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)
def tortoiseteam():
q = text("""
select avg(a.average_speed) as spd, c.name from rides a, lbd_athletes b, teams c where a.athlete_id=b.id and b.team_id=c.id group by c.name order by spd asc;
""")
goat = [(x['name'], x['spd'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('pointless/tortoiseteam.html', data=goat)
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.session_factory().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)
def billygoat():
q = text("""
select sum(a.elevation_gain) as elev,sum(a.distance) as dist, (sum(a.elevation_gain)/sum(a.distance)) as gainpermile,
c.name from rides a, lbd_athletes b, teams c where a.athlete_id=b.id and b.team_id=c.id group by c.name order by gainpermile desc;
""")
goat = [(x['name'], x['gainpermile'], x['dist'], x['elev'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('pointless/billygoat.html', data=goat)
def team_riders():
q = text("""
select b.name, count(a.athlete_id) as ride_days from daily_scores a join teams b
on a.team_id = b.id where a.distance > 1 and b.leaderboard_exclude=0 group by a.team_id order by ride_days desc;
""")
team_riders = [(x['name'], x['ride_days'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('alt_scoring/team_riders.html',
team_riders=team_riders)
def list_photos():
photos = meta.session_factory().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 shortride():
q = text("""
select a.id, a.display_name, avg(b.distance) as dist, count(distinct(date(b.start_date))) as distrides from lbd_athletes a,
rides b where a.id = b.athlete_id group by a.id order by dist;
""")
avgdist = [(x['id'], x['display_name'], x['dist'])
for x in meta.session_factory().execute(q).fetchall()
if x['distrides'] >= 10]
return render_template('pointless/distance.html', avg=avgdist)
def execute(self, options, args):
sess = meta.session_factory()
if options.start_date:
start = parse_competition_timestamp(options.start_date)
self.logger.info("Fetching rides newer than {0}".format(start))
else:
start = None
self.logger.info("Fetching all rides (since competition start)")
end_date = parse_competition_timestamp(app.config['BAFS_END_DATE'])
grace_days = app.config['BAFS_UPLOAD_GRACE_PERIOD_DAYS']
grace_delta = timedelta(days=grace_days)
if (datetime.now(utc) >
(end_date + grace_delta)) and not options.force:
raise CommandError(
"Current time is after competition end date + grace period, not syncing rides. (Use --force to override.)"
)
if options.rewrite:
self.logger.info("Rewriting existing ride data.")
# We iterate over all of our athletes that have access tokens. (We can't fetch anything
# for those that don't.)
q = sess.query(orm.Athlete)
q = q.filter(orm.Athlete.access_token != None)
if options.athlete_id:
q = q.filter(orm.Athlete.id == options.athlete_id)
# Also only fetch athletes that have teams configured. This may not be strictly necessary
# but this is a team competition, so not a lot of value in pulling in data for those
# without teams.
# (The way the athlete sync works, athletes will only be configured for a single team
# that is one of the configured competition teams.)
q = q.filter(orm.Athlete.team_id != None)
for athlete in q.all():
assert isinstance(athlete, orm.Athlete)
self.logger.info("Fetching rides for athlete: {0}".format(athlete))
try:
self._write_rides(start,
end_date,
athlete=athlete,
rewrite=options.rewrite)
except InvalidAuthorizationToken:
self.logger.error(
"Invalid authorization token for {} (removing)".format(
athlete))
athlete.access_token = None
sess.add(athlete)
sess.commit()
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
;
"""
)
total_days = datetime.now().timetuple().tm_yday
ride_days = [(x['id'], x['display_name'], x['rides'], x['miles'], x['lastride'] >= date.today()) for x in
meta.session_factory().execute(q).fetchall()]
return render_template('people/ridedays.html', ride_days=ride_days, num_days=total_days)
def rides_data():
athlete_id = session.get('athlete_id')
rides_q = meta.session_factory().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 avgtemp():
""" sum of ride distance * ride avg temp divided by total distance """
q = text("""
select athlete_id, athlete_name, sum(temp_dist)/sum(distance) as avgtemp from (
select A.id as athlete_id, A.display_name as athlete_name, W.ride_temp_avg, R.distance,
W.ride_temp_avg * R.distance as temp_dist
from lbd_athletes A, ride_weather W, rides R where R.athlete_id = A.id and R.id=W.ride_id) as T
group by athlete_id, athlete_name order by avgtemp asc;
""")
tdata = [(x['athlete_id'], x['athlete_name'], x['avgtemp'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('pointless/averagetemp.html', data=tdata)
def weekendwarrior():
q = text("""
select A.id as athlete_id, A.display_name as athlete_name, sum(DS.points) as total_score,
sum(if((dayofweek(DS.ride_date)=7 or (dayofweek(DS.ride_date)=1)) , DS.points, 0)) as 'weekend',
sum(if((dayofweek(DS.ride_date)<7 and (dayofweek(DS.ride_date)>1)) , DS.points, 0)) as 'weekday'
from daily_scores DS join lbd_athletes A on A.id = DS.athlete_id group by A.id
order by weekend desc;
""")
weekend = [(x['athlete_id'], x['athlete_name'], x['total_score'],
x['weekend'], x['weekday'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('people/weekend.html', data=weekend)
def team_leaderboard_classic():
# Get teams sorted by points
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.session_factory().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 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.session_factory().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)
def opmdays():
"""
If OPM doesn't close this year, just use Michigan's birthday for Kitty's prize
"""
q = text("""
select A.id, A.display_name as athlete_name, count(distinct(date(R.start_date))) as days, sum(R.distance) as distance
from lbd_athletes A join rides R on R.athlete_id=A.id
where date(R.start_date) in ('2017-01-26') group by R.athlete_id
order by days desc, distance desc;
""")
opm = [(x['id'], x['athlete_name'], x['days'], x['distance'])
for x in meta.session_factory().execute(q).fetchall()]
return render_template('pointless/opmdays.html', data=opm)