コード例 #1
0
def team_cumul_mileage():
    """
    """
    teams = db.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 = parse_competition_timestamp(app.config['BAFS_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 db.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})
コード例 #2
0
    def execute(self, options, args):

        sess = db.session

        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(model.Athlete)
        q = q.filter(model.Athlete.access_token != None)

        if options.athlete_id:
            q = q.filter(model.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(model.Athlete.team_id != None)

        for athlete in q.all():
            assert isinstance(athlete, model.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()
コード例 #3
0
def team_weekly_points():
    """
    """
    teams = db.session.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 = db.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})
コード例 #4
0
def user_daily_points(athlete_id):
    """
    """
    teams = db.session.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 = db.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})
コード例 #5
0
ファイル: sync_rides.py プロジェクト: schvin/freezingsaddles
    def execute(self, options, args):

        sess = db.session

        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(model.Athlete)
        q = q.filter(model.Athlete.access_token != None)

        if options.athlete_id:
            q = q.filter(model.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(model.Athlete.team_id != None)

        for athlete in q.all():
            assert isinstance(athlete, model.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()
コード例 #6
0
def team_cumul_mileage():
    """
    """
    teams = db.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 = parse_competition_timestamp(app.config['BAFS_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 db.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})
コード例 #7
0
def team_weekly_points():
    """
    """
    teams = db.session.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 = db.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})
コード例 #8
0
def user_daily_points(athlete_id):
    """
    """
    teams = db.session.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 = db.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})