def cup_list(request):
season = request.matchdict.get('season', None)
if season:
start = datetime.strptime(season.split('-')[0] + '-11-01', '%Y-%m-%d')
end = datetime.strptime(season.split('-')[1] + '-11-01', '%Y-%m-%d')
session = DBSession()
start_date = func.min(CupDate.date).label('start_date')
end_date = func.max(CupDate.date).label('end_date')
cups = session.query(Cup, func.count(CupDate.cup_id).label('total'),
func.count(CupDate.tournament_id).label('completed'),
start_date,
end_date) \
.join(CupDate)
if season:
cups = cups \
.group_by(Cup.id) \
.having(start_date >= start) \
.having(start_date <= end)
cups = cups \
.group_by(Cup) \
.order_by('end_date desc', Cup.name.desc()) \
.all()
return dict(cups=cups)
def jumper_list(request):
page = int(request.params.get('page', 1))
q = request.params.get('q', None)
sort = request.params.get('sort', 'participations')
session = DBSession()
query = session.query(Jumper, func.count(FinalStanding.id).label('participations')) \
.join(FinalStanding) \
.order_by(get_order_by_clause(sort), desc('participations')) \
.group_by(Jumper)
if q:
query = query.filter(func.lower(Jumper.name).like('%' + q.lower() + '%'))
total_count = query.count()
if total_count == 1:
return HTTPFound(location=request.route_path('jumper_details',
slug=query.one().Jumper.slug))
jumpers = query \
.offset(PAGE_SIZE * (page - 1)) \
.limit(PAGE_SIZE) \
.all()
pagination = Pagination(page, PAGE_SIZE, total_count)
return {
'total_count': total_count,
'jumpers': jumpers,
'pagination': pagination,
'q': q,
'sort': sort
}
def jumper_recent(request):
slug = request.matchdict['slug']
session = DBSession()
jumper = session.query(Jumper).filter_by(slug=slug).first()
tournaments = session.query(Tournament) \
.options(subqueryload('final_standings')) \
.join(FinalStanding) \
.filter(FinalStanding.jumper_id == jumper.id) \
.order_by(Tournament.date.desc()) \
.limit(5) \
.all()
return dict(jumper=jumper, tournaments=tournaments, tab='recent')
def jumpertip_cup(request):
jid = int(request.matchdict['jid'])
cid = int(request.matchdict['cid'])
session = DBSession()
results = session.query(FinalStanding) \
.join(Tournament, CupDate) \
.filter(FinalStanding.jumper_id == jid) \
.filter(CupDate.cup_id == cid) \
.order_by(CupDate.date) \
.options(joinedload('tournament', innerjoin=True)) \
.all()
return {
'results': results
}
def jumper_form(request):
slug = request.matchdict['slug']
session = DBSession()
jumper = session.query(Jumper).filter_by(slug=slug).first()
ranks = session.query(Tournament.date, FinalStanding.rank) \
.filter(FinalStanding.tournament_id == Tournament.id) \
.filter(FinalStanding.jumper_id == jumper.id) \
.order_by(Tournament.date.desc()) \
.limit(50) \
.all()
import calendar
max_rank = max([r for d, r in ranks])
ranks = str([[calendar.timegm(d.timetuple()) * 1000, r] for d, r in ranks]).replace('L', '')
return dict(jumper=jumper, ranks=ranks, max_rank=max_rank, tab='form')
def upload_file(request):
if request.POST:
upload_directory = request.registry.settings['upload_directory']
filename = request.POST['file'].filename
file = request.POST['file'].file
try:
session = DBSession()
stat_file = StatFile(file)
(msg, tournament_id) = process(stat_file, session, get_file_number(filename))
stat_dir = os.path.join(upload_directory,
stat_file.date.strftime('%Y-%m-%d') + ' ' + stat_file.type)
if not os.path.exists(stat_dir):
os.makedirs(stat_dir)
save_as = os.path.join(stat_dir, filename)
except Exception as err:
transaction.abort()
msg = 'Failed: ' + str(err) + traceback.format_exc()
tournament_id = None
failed_dir = os.path.join(upload_directory, 'failed')
if not os.path.exists(failed_dir):
os.makedirs(failed_dir)
save_as = os.path.join(failed_dir, filename)
with open(save_as.encode('ascii', 'ignore'), mode='w') as f:
file.seek(0)
shutil.copyfileobj(file, f)
file.close()
if tournament_id:
session.add(Upload(
tournament_id=tournament_id,
filename=save_as,
ipaddress=request.headers.get('X-Forwarded-For') or request.remote_addr))
session.flush()
else:
msg = 'Post me a file'
return msg
def jumper_details(request):
slug = request.matchdict.get('slug', None)
session = DBSession()
jumper = session.query(Jumper).filter_by(slug=slug).first()
if not jumper:
return HTTPFound(location=request.route_path('jumper_list'))
participations = session.query(TournamentType,
func.count(TournamentType.id).label('participations')) \
.join(Tournament) \
.join(FinalStanding) \
.filter(FinalStanding.jumper_id == jumper.id) \
.group_by(TournamentType) \
.order_by(desc('participations')) \
.all()
stats = session.query(
func.min(FinalStanding.rank).label('best_rank'),
func.max(FinalStanding.rank).label('worst_rank'),
func.avg(FinalStanding.rank).label('avg_rank'),
func.max(FinalStanding.rating).label('highest_rating'),
func.min(FinalStanding.rating).label('lowest_rating'),
func.avg(FinalStanding.rating).label('avg_rating'),
func.max(FinalStanding.points).label('highest_points'),
func.min(FinalStanding.points).label('lowest_points'),
func.avg(FinalStanding.points).label('avg_points')) \
.filter(FinalStanding.jumper_id == jumper.id) \
.group_by(FinalStanding.jumper_id) \
.one()
return dict(
jumper=jumper,
participations=participations,
stats=stats,
tab='stats'
)
def setUp(self):
self.session = DBSession()
def setUpClass(cls):
engine = create_engine(
'postgresql+psycopg2://janerist@localhost/dsjtournaments_test',
echo=False
)
DBSession.configure(bind=engine)
class TestProcessor(unittest.TestCase):
@classmethod
def setUpClass(cls):
engine = create_engine(
'postgresql+psycopg2://janerist@localhost/dsjtournaments_test',
echo=False
)
DBSession.configure(bind=engine)
def setUp(self):
self.session = DBSession()
def tearDown(self):
transaction.abort()
def test_new_tournament(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-12-19',
second_line='Georgia HS207 Competition Final Results')
stat_file = StatFile(input)
process(stat_file, self.session)
count = self.session.query(Tournament).count()
tournament = self.session.query(Tournament).first()
self.assertEquals(count, 1)
self.assertEquals(tournament.type.name, 'World Cup')
self.assertEquals(tournament.date, datetime.strptime('2009-12-19 20.00', '%Y-%m-%d %H.%M'))
self.assertEquals(tournament.game_version_major, '3')
def test_tournament_exists(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-12-19',
second_line='Georgia HS207 Competition Final Results')
stat_file = StatFile(input)
process(stat_file, self.session)
process(stat_file, self.session)
self.assertEquals(self.session.query(Tournament).count(), 1)
def test_standings_not_final(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Standings After 39/40 Hills')
stat_file = StatFile(input)
msg, tid = process(stat_file, self.session)
self.assertEquals(self.session.query(Tournament).count(), 0)
self.assertEquals(msg, 'Intermediary standings not supported')
def test_standings_final(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Standings After 40/40 Hills')
stat_file = StatFile(input)
process(stat_file, self.session)
tournament = self.session.query(Tournament).first()
self.assertEquals(tournament.hill_count, 40)
self.assertEquals(tournament.participant_count, 0)
def test_standings_already_uploaded(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Standings After 40/40 Hills')
stat_file = StatFile(input)
process(stat_file, self.session)
msg, tid = process(stat_file, self.session)
self.assertEquals(msg, 'This file has already been uploaded, but thanks anyway!')
def test_standings_participants(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Standings After 40/40 Hills',
participants=['3. Petri Tuominen FIN 1317 - 10 5 54 385'])
stat_file = StatFile(input)
process(stat_file, self.session)
tournament = self.session.query(Tournament).first()
final_standings = tournament.final_standings
self.assertEquals(len(final_standings), 1)
jumper = final_standings[0].jumper
self.assertEquals(final_standings[0].rank, 3)
self.assertEquals(jumper.name, 'Petri Tuominen')
self.assertEquals(jumper.country_code, 'FIN')
self.assertEquals(jumper.slug, 'petri-tuominen')
self.assertEquals(final_standings[0].rating, 1317)
self.assertEquals(final_standings[0].i, 0)
self.assertEquals(final_standings[0].ii, 10)
self.assertEquals(final_standings[0].iii, 5)
self.assertEquals(final_standings[0].n, 54)
self.assertEquals(final_standings[0].points, 385)
self.assertEquals(final_standings[0].cup_points, 60)
def test_avoid_duplicate_jumper_slugs(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Standings After 40/40 Hills',
participants=['31. Petri Tuominen FIN 1317 - 10 5 54 385',
'32. Petri-Tuominen FIN 1317 - 10 5 54 385'])
stat_file = StatFile(input)
process(stat_file, self.session)
jumpers = self.session.query(Jumper).all()
self.assertEquals(len(jumpers), 2)
self.assertTrue(jumpers[0].slug != jumpers[1].slug)
def test_qualification_new_competition(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Qualification Results')
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
self.assertEquals(competition.hill.name, 'Croatia HS278')
self.assertEquals(competition.file_number, 1)
self.assertEquals(competition.ko, False)
self.assertEquals(competition.has_qualification_results, True)
def test_qualification_new_competition_knockout(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Qualification Results (KO)')
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
self.assertEquals(competition.hill.name, 'Croatia HS278')
self.assertEquals(competition.file_number, 1)
self.assertEquals(competition.ko, True)
self.assertEquals(competition.has_qualification_results, True)
def test_unknow_hill_name(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Brazil HS120 Qualification Results')
stat_file = StatFile(input)
msg, tid = process(stat_file, self.session)
self.assertEquals(msg, 'Unknown hill: \'Brazil HS120\'')
def test_qualification_competition_exists(self):
input1 = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Qualification Results')
input2 = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Competition Final Results')
process(StatFile(input1), self.session)
process(StatFile(input2), self.session)
competitions = self.session.query(Competition).all()
self.assertEquals(len(competitions), 1)
competition = competitions[0]
self.assertEquals(competition.hill.name, 'Croatia HS278')
self.assertEquals(competition.file_number, 1)
self.assertEquals(competition.has_qualification_results, True)
def test_qualification_participants(self):
input = create_test_input(first_line='WC Saturday B - 20.00 CE(S)T 2012-01-07',
second_line='Lillehammer HS138 Qualification Results',
participants=['20. Eduardo Sanchez N/A 1226 185.04m 186.8 q'])
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
qualification_result = competition.qualification_results[0]
jumper = qualification_result.jumper
self.assertEquals(qualification_result.rank, 20)
self.assertEquals(jumper.name, 'Eduardo Sanchez')
self.assertEquals(jumper.country_code, 'N/A')
self.assertEquals(jumper.slug, 'eduardo-sanchez')
self.assertEquals(qualification_result.rating, 1226)
self.assertEquals(qualification_result.length, 185.04)
self.assertEquals(qualification_result.crashed, False)
self.assertEquals(qualification_result.points, 186.8)
self.assertEquals(qualification_result.qualified, True)
self.assertEquals(qualification_result.prequalified, False)
def test_qualification_already_uploaded(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Croatia HS278 Qualification Results')
stat_file = StatFile(input)
process(stat_file, self.session)
msg, tid = process(stat_file, self.session)
self.assertEquals(msg, 'This file has already been uploaded, but thanks anyway!')
def test_final_results_new_competition(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Competition Final Results')
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
self.assertEquals(competition.hill.name, 'Croatia HS278')
self.assertEquals(competition.file_number, 1)
self.assertEquals(competition.ko, False)
self.assertEquals(competition.has_final_results, True)
def test_final_results_new_competition_knockout(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Competition Final Results (KO)')
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
self.assertEquals(competition.hill.name, 'Croatia HS278')
self.assertEquals(competition.file_number, 1)
self.assertEquals(competition.ko, True)
self.assertEquals(competition.has_final_results, True)
def test_final_results_participants(self):
input = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Competition Final Results',
participants=['30. Laurus Kölsä POL 1307 242.50m 267.86m* 396.6'])
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
final_result = competition.final_results[0]
jumper = final_result.jumper
self.assertEquals(final_result.rank, 30)
self.assertEquals(jumper.name, u'Laurus Kölsä')
self.assertEquals(jumper.country_code, 'POL')
self.assertEquals(jumper.slug, 'laurus-koelsae')
self.assertEquals(final_result.rating, 1307)
self.assertEquals(final_result.length_first_round, 242.50)
self.assertEquals(final_result.crashed_first_round, False)
self.assertEquals(final_result.length_final_round, 267.86)
self.assertEquals(final_result.crashed_final_round, True)
self.assertEquals(final_result.points, 396.6)
def test_final_results_already_uploaded(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Croatia HS278 Competition Final Results')
stat_file = StatFile(input)
process(stat_file, self.session)
msg, tid = process(stat_file, self.session)
self.assertEquals(msg, 'This file has already been uploaded, but thanks anyway!')
def test_jumper_country_code_change(self):
input1 = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2009-02-21',
second_line='Croatia HS278 Competition Final Results',
participants=['30. Laurus Kölsä POL 1307 242.50m 267.86m* 396.6'])
input2 = create_test_input(first_line='WC - Saturday 20.00 CE(S)T 2010-04-13',
second_line='Finland HS218 Competition Final Results',
participants=['19. Laurus Kölsä FIN 1206 124.44m 129.36m 140.3'])
process(StatFile(input1), self.session)
jumper = self.session.query(Jumper).one()
self.assertEquals(jumper.country_code, 'POL')
process(StatFile(input2), self.session)
jumper = self.session.query(Jumper).one()
self.assertEquals(jumper.country_code, 'FIN')
def test_connects_to_cup(self):
input = create_test_input(first_line='Tour de DSJ - Wed 20.00 CET 2010-02-03',
second_line='Standings After 40/40 Hills')
stat_file = StatFile(input)
process(stat_file, self.session)
tournament = self.session.query(Tournament).one()
self.assertEquals(len(tournament.cup_dates), 2)
def test_team_cup_standings(self):
input = create_test_input(
first_line='Team Cup - Thursday 20.00 CE(S)T 2012-11-29',
second_line='Standings After 34/34 Hills',
participants=[
'1. Poland I POL 15 4 - 21 7900',
'2. Poland II POL 3 9 7 20 6700',
'3. Mixed Team I MIX 1 5 8 22 6500',
'4. Mixed Team II MIX 1 1 3 18 4300',
'5. Poland III POL 1 - 2 12 3150',
'6. Norway I NOR 1 2 1 4 1400',
'7. Mixed Team III MIX - - - 4 650',
'8. Poland IV POL - - - 2 400'
])
stat_file = StatFile(input)
process(stat_file, self.session)
tournament = self.session.query(Tournament).first()
final_standings = tournament.final_standings
self.assertEquals(len(final_standings), 8)
team = final_standings[0].team
self.assertEquals(final_standings[0].rank, 1)
self.assertEquals(team.name, 'Poland I')
self.assertEquals(team.country_code, 'POL')
self.assertEquals(team.slug, 'poland-i')
self.assertEquals(final_standings[0].i, 15)
self.assertEquals(final_standings[0].ii, 4)
self.assertEquals(final_standings[0].iii, 0)
self.assertEquals(final_standings[0].n, 21)
self.assertEquals(final_standings[0].points, 7900)
self.assertEquals(final_standings[0].cup_points, 100)
self.assertEquals(final_standings[0].jumper_id, None)
def test_team_cup_final_results(self):
input = create_test_input(
first_line='Team Cup - Thursday 20.00 CE(S)T 2012-11-29',
second_line='Willingen HS145 Competition Final Results',
participants=[
'1. Poland I POL 1615.5',
' Marcin Turlejski POL 1238 211.73 m* 213.12 m 414.9 (2.)',
' Maciej Majchrzyk POL 1143 206.46 m 211.66 m 406.0 (9.)',
' Andrei Vocarny POL 1089 197.79 m 205.18 m* 386.5 (11.)',
' Rafał Borgiasz POL 1132 208.94 m 209.66 m 408.1 (7.)',
'',
'2. Norway I NOR 1614.4',
' Andreas Jøranlid NOR 1071 197.56 m 197.54 m 384.0 (12.)',
' Deluxe Ski Jump 4 POL 1337 - - 0.0 (14.)',
' Jan-Erik Strøm NOR 1184 206.80 m 208.71 m 403.5 (10.)',
' Steinar Hansen NOR 1322 214.77 m 203.76 m 408.1 (7.)'
])
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
self.assertEquals(competition.hill.name, 'Willingen HS145')
self.assertEquals(competition.has_final_results, True)
final_results = competition.final_results
self.assertEquals(len(final_results), 10)
self.assertEquals(final_results[0].rank, 1)
self.assertEquals(final_results[0].team.name, 'Poland I')
self.assertEquals(final_results[0].team.country_code, 'POL')
self.assertEquals(final_results[0].points, 1615.5)
self.assertEquals(final_results[0].jumper_id, None)
self.assertEquals(final_results[0].team_result_id, None)
self.assertEquals(final_results[1].rank, 2)
self.assertEquals(final_results[1].team_id, None)
self.assertEquals(final_results[1].rating, 1238)
self.assertEquals(final_results[1].jumper.name, 'Marcin Turlejski')
self.assertEquals(final_results[1].jumper.country_code, 'POL')
self.assertEquals(final_results[1].length_first_round, 211.73)
self.assertEquals(final_results[1].crashed_first_round, True)
self.assertEquals(final_results[1].length_final_round, 213.12)
self.assertEquals(final_results[1].crashed_final_round, False)
self.assertEquals(final_results[1].points, 414.9)
self.assertEquals(final_results[1].team_result_id, final_results[0].id)
def test_team_cup_qualification_results(self):
input = create_test_input(
first_line='Team Cup - Thursday 20.00 CE(S)T 2012-11-29',
second_line='Willingen HS145 Qualification Results',
participants=['1. Dariusz Sidor POL 1472 146.14 m 146.3 POL I'])
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
self.assertEquals(competition.hill.name, 'Willingen HS145')
self.assertEquals(competition.has_qualification_results, True)
qualification_results = competition.qualification_results
self.assertEquals(len(qualification_results), 1)
self.assertEquals(qualification_results[0].rank, 1)
self.assertEquals(qualification_results[0].jumper.name, 'Dariusz Sidor')
self.assertEquals(qualification_results[0].jumper.country_code, 'POL')
self.assertEquals(qualification_results[0].rating, 1472)
self.assertEquals(qualification_results[0].length, 146.14)
self.assertEquals(qualification_results[0].crashed, False)
self.assertEquals(qualification_results[0].points, 146.3)
self.assertEquals(qualification_results[0].qualified, True)
self.assertEquals(qualification_results[0].prequalified, False)
self.assertEquals(qualification_results[0].team, 'POL I')
def test_english_team_name(self):
input = create_test_input(
first_line='Team Cup - Thursday 20.00 CE(S)T 2012-11-29',
second_line='Standings After 34/34 Hills',
participants=[
'1. Polska I POL 15 4 - 21 7900',
'2. Polska II POL 3 9 7 20 6700',
'3. Mieszane drużyny I MIX 1 5 8 22 6500',
'4. Norwegia I NOR 1 2 1 4 1400',
'5. Mixed Team III MIX - - - 4 650',
'6. Poland IV POL - - - 2 400'
])
stat_file = StatFile(input)
process(stat_file, self.session)
tournament = self.session.query(Tournament).first()
final_standings = tournament.final_standings
self.assertEquals(final_standings[0].team.name, 'Poland I')
self.assertEquals(final_standings[1].team.name, 'Poland II')
self.assertEquals(final_standings[2].team.name, 'Mixed Team I')
self.assertEquals(final_standings[3].team.name, 'Norway I')
self.assertEquals(final_standings[4].team.name, 'Mixed Team III')
self.assertEquals(final_standings[5].team.name, 'Poland IV')
def test_qualification_participants_bib(self):
input = create_test_input(
first_line='WC Saturday A - 20.00 CE(S)T 2013-11-23',
second_line='Lillehammer HS138 Qualification Results (KO)',
participants=['51. 1 Sebastian Neumann GER 1282 102.51m 114.5 Q'])
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
qualification_result = competition.qualification_results[0]
self.assertEquals(qualification_result.bib, 1)
def test_final_results_participant_bib(self):
input = create_test_input(
first_line='WC Saturday A - 20.00 CE(S)T 2013-11-23',
second_line='Lillehammer HS138 Competition Final Results (KO)',
participants=['1. 3 Bot-001 CZE 1008 242.50m 267.86m* 396.6'])
stat_file = StatFile(input)
process(stat_file, self.session)
competition = self.session.query(Competition).one()
final_result = competition.final_results[0]
self.assertEquals(final_result.bib, 3)
self.assertEquals(final_result.lucky_loser, False)
def test_future_tournament_date(self):
import datetime
d = datetime.datetime.strftime(datetime.datetime.today() + datetime.timedelta(days=1), '%Y-%m-%d')
input = create_test_input(first_line='WC Saturday A - 20.00 CE(S)T ' + d,
second_line='Standings After 40/40 Hills')
stat_file = StatFile(input)
msg, tid = process(stat_file, self.session)
self.assertEquals(self.session.query(Tournament).count(), 0)
self.assertEquals(msg, 'Invalid stats file')
