summaryrefslogtreecommitdiff
path: root/jfr_playoff/sql.py
blob: 1eac4142750cd90a162f6e0c90fff8726fc6c41c (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
MATCH_RESULTS = '''
SELECT t1.fullname, t2.fullname, matches.carry,
 matches.vph, matches.vpv, matches.corrh, matches.corrv
FROM #db#.matches matches
JOIN #db#.teams t1
 ON t1.id = #db#.matches.homet
JOIN #db#.teams t2
 ON t2.id = #db#.matches.visit
WHERE matches.tabl = %s AND matches.rnd = %s
'''

BOARD_COUNT = '''
SELECT segmentsperround*boardspersegment,
 SUM(sc1.score IS NOT NULL AND sc2.score IS NOT NULL)
FROM #db#.scores sc1
JOIN #db#.scores sc2
 ON sc1.rnd = sc2.rnd
 AND sc1.segment = sc2.segment
 AND sc1.tabl = sc2.tabl
 AND sc1.board = sc2.board
 AND sc1.room = 1
 AND sc2.room = 2
JOIN #db#.admin
WHERE sc1.tabl = %s AND sc1.rnd = %s
GROUP BY sc1.tabl, sc1.rnd
'''

TOWEL_COUNT = '''
SELECT #db#.admin.boardspersegment * SUM(#db#.segments.towel > 0)
FROM #db#.segments
JOIN #db#.admin
WHERE #db#.segments.tabl = %s AND #db#.segments.rnd = %s
GROUP BY #db#.segments.tabl, #db#.segments.rnd
'''

CURRENT_SEGMENT = '''
SELECT LEAST(segmentsperround, segm+1)
FROM #db#.admin
'''

PREFIX = '''
SELECT shortname FROM #db#.admin
'''

SWISS_ENDED = '''
SELECT (rnd = roundcnt) AND (segm = segmentsperround) FROM #db#.admin
'''

SWISS_RESULTS = '''
SELECT #db#.teams.fullname,
 SUM(IF(#db#.matches.homet = #db#.teams.id, vph+corrh, vpv+corrv))
  + #db#.teams.score,
 #db#.teams.grupa,
 #db#.teams.shortname,
 #db#.teams.gif
FROM #db#.teams
LEFT JOIN #db#.matches
 ON (#db#.teams.id = #db#.matches.homet OR #db#.teams.id = #db#.matches.visit)
WHERE #db#.teams.bye = 0
GROUP BY #db#.teams.id
'''