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')