-
Notifications
You must be signed in to change notification settings - Fork 0
/
league.php
287 lines (246 loc) · 8.58 KB
/
league.php
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
<?php
// First, validate region and season#
include("includes/sql.inc");
$region = htmlspecialchars($_GET["region"] ?? null);
$season = htmlspecialchars($_GET['season'] ?? $currentseason); // default to current season if not specified.
$tsql="
SELECT
Region.Name AS 'RegionName',
Season.Year AS 'SeasonYear',
(SELECT COUNT(*) FROM LeagueMeet WHERE LeagueMeet.SeasonId = Season.Id AND LeagueMeet.RegionId = Region.Id AND LeagueMeet.Status = 3) AS 'TotalMeets'
FROM Season, Region
WHERE Region.Synonym = ? -- $region
AND Season.SeasonNumber = ? -- $season";
$result = sqlsrv_query($sqlConnection, $tsql, array($region, $season));
if ($result == FALSE)
{
echo "query borken.";
}
$row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
$regionName = $row['RegionName'];
$seasonYear = $row['SeasonYear'];
$totalMeets = $row['TotalMeets'];
if (is_null($regionName))
{
echo '<p>Unexpected region or season number.</p>';
exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="description" content="UK Pinball League Table" />
<title>UK Pinball League - <?=$regionName;?> League <?=$seasonYear;?></title>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<!-- Header and menu -->
<?php include("includes/header.inc"); ?>
<div class="panel">
<?php
include("functions/leagueinfo.inc");
$info = GetLeagueInfo($region, $season);
echo "<h1>$regionName League $seasonYear ";
// Display a drop down to allow user to change season that is displayed.
echo "<span class='dropdown'>
<h1 class='dropbtn'>Season $season</h1>
<div class='dropdown-content'>";
$seasonLoop=$currentseason;
while ($seasonLoop > 0)
{
echo "<a href='league.php?region=$region&season=$seasonLoop'>Season $seasonLoop</a>";
$seasonLoop--;
}
echo "</span></div></h1>";
echo "<p>$info->note</p>";
if ($totalMeets < 1 && $season == $currentseason)
{
echo "<p>No results have been submitted for the current season yet. You can view previous seasons using the button above.</p>";
}
?>
<?php
$tsql= "
DECLARE @region NVARCHAR(3) = ?; -- $region
DECLARE @season INTEGER = ?; -- $season
DECLARE @qualifyingMeets INTEGER = ?; -- $info->numberOfQualifyingMeets
-- Simplify the region and season into id values
WITH Query (RegionId, SeasonId) AS
(
SELECT
Region.Id AS RegionId,
Season.Id AS SeasonId
FROM Region, Season
WHERE Region.Synonym = @region
AND Season.SeasonNumber = @season
),
-- Select completed league meets for this region/season
LeagueMeets (Id, MeetNumber, CompetitionId) AS
(
SELECT
Id,
MeetNumber,
CompetitionId
FROM LeagueMeet
INNER JOIN Query on LeagueMeet.RegionId = Query.RegionId AND LeagueMeet.SeasonId = Query.SeasonId
WHERE LeagueMeet.Status = 3
),
-- Select results for this region/season
Results (MeetNumber, CompetitionId, PlayerId, Score, Points, Position, Rnk) AS
(
SELECT
LeagueMeets.MeetNumber,
LeagueMeets.CompetitionId,
Result.PlayerId,
Result.Score,
Result.Points,
Result.Position,
ROW_NUMBER() OVER (PARTITION BY PlayerId ORDER BY Points DESC) AS Rnk
FROM Result
INNER JOIN LeagueMeets ON Result.CompetitionId = LeagueMeets.CompetitionId
),
-- Select players for this region/season
SeasonPlayers (PlayerId, PlayerName) AS
(
SELECT DISTINCT
Player.Id,
Player.Name
FROM Result
INNER JOIN LeagueMeets ON Result.CompetitionId = LeagueMeets.CompetitionId
INNER JOIN Player ON Player.Id = Result.PlayerId
)
SELECT
SeasonPlayers.PlayerId AS playerid,
SeasonPlayers.PlayerName AS player,
(
SELECT COUNT(*) FROM Results WHERE Results.PlayerId = SeasonPlayers.PlayerId
) AS played,
MeetOne.Points as meet1,
MeetTwo.Points as meet2,
MeetThree.Points as meet3,
MeetFour.Points as meet4,
MeetFive.Points as meet5,
MeetSix.Points as meet6,
COALESCE(MeetOne.Points,0) + COALESCE(MeetTwo.Points,0) + COALESCE(MeetThree.Points,0) + COALESCE(MeetFour.Points,0) + COALESCE(MeetFive.Points,0) + COALESCE(MeetSix.Points,0) AS total,
(
SELECT
SUM(Results.Points)
FROM Results
WHERE Results.PlayerId = SeasonPlayers.PlayerId AND Results.Rnk <= @qualifyingMeets
) AS best4,
(
SELECT
SUM(Results.Points)
FROM Results
WHERE Results.PlayerId = SeasonPlayers.PlayerId AND Results.Rnk <= (@qualifyingMeets + 1)
) AS best5,
(
SELECT
SUM(Results.Points)
FROM Results
WHERE Results.PlayerId = SeasonPlayers.PlayerId AND Results.Rnk <= (@qualifyingMeets + 2)
) AS best6
FROM SeasonPlayers
LEFT OUTER JOIN Results AS MeetOne ON MeetOne.MeetNumber = 1 AND MeetOne.PlayerId = SeasonPlayers.PlayerId
LEFT OUTER JOIN Results AS MeetTwo ON MeetTwo.MeetNumber = 2 AND MeetTwo.PlayerId = SeasonPlayers.PlayerId
LEFT OUTER JOIN Results AS MeetThree ON MeetThree.MeetNumber = 3 AND MeetThree.PlayerId = SeasonPlayers.PlayerId
LEFT OUTER JOIN Results AS MeetFour ON MeetFour.PlayerId = SeasonPlayers.PlayerId AND MeetFour.MeetNumber = 4
LEFT OUTER JOIN Results AS MeetFive ON MeetFive.PlayerId = SeasonPlayers.PlayerId AND MeetFive.MeetNumber = 5
LEFT OUTER JOIN Results AS MeetSix ON MeetSix.PlayerId = SeasonPlayers.PlayerId AND MeetSix.MeetNumber = 6
ORDER BY best4 DESC, best5 DESC, best6 DESC, played ASC";
$result = sqlsrv_query($sqlConnection, $tsql, array($region, $season, $info->numberOfQualifyingMeets));
if ($result == FALSE)
{
//echo "query borken.";
echo (sqlsrv_errors());
}
echo "<div class='table-holder'>";
echo "<table>";
echo "<thead>
<tr class='white'>
<th> </th>
<th>Player</th>
<th>Played</th>";
$meetNumber = 1;
while ($meetNumber <= $totalMeets)
{
echo "<th><a href=\"leaguemeet.php?season=$season&region=$region&meet=$meetNumber\" class='link'>Meet $meetNumber</a></th>";
$meetNumber++;
}
echo "<th>Total</th>
<th class='paddidge'>Best $info->numberOfQualifyingMeets</th>
</tr>
</thead>";
$counter = 0;
$total = '';
$position = 0;
$previousRowBest = 0;
$hiddenPositions = 0;
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{
$player = $row['player'];
$playerid = $row['playerid'];
$played = $row['played'];
$meet1 = (is_null($row['meet1']) ? "-" : (float)$row['meet1']);
$meet2 = (is_null($row['meet2']) ? "-" : (float)$row['meet2']);
$meet3 = (is_null($row['meet3']) ? "-" : (float)$row['meet3']);
$meet4 = (is_null($row['meet4']) ? "-" : (float)$row['meet4']);
$meet5 = (is_null($row['meet5']) ? "-" : (float)$row['meet5']);
$meet6 = (is_null($row['meet6']) ? "-" : (float)$row['meet6']);
$best4 = $row['best4'];
$best5 = $row['best5'];
$best6 = $row['best6'];
$total = $row['total'];
$best4 = round($best4,"1");
$best5 = round($best5,"1");
$best6 = round($best6,"1");
$total = round($total,"1");
// Calculate rank. Ties on best4 are split by best5 and then best6. This is a non-optimal way of doing that..
if ($best4 + $best5 + $best6 == $previousRowBest)
{
$hiddenPositions++;
}
else
{
$position = $position + $hiddenPositions;
$hiddenPositions = 0;
$previousRowBest = $best4 + $best5 + $best6;
$position++;
}
// Calculate row highlighting
$counter++;
if ($counter <= $info->aQualifyingPlaces) $bgcolor = "#fec171";
else if ($counter <= ($info->aQualifyingPlaces + $info->bQualifyingPlaces)) $bgcolor = "#fee0b8";
else $bgcolor = ($counter % 2)?"#f7f7f7":"#ffffff";
echo "<tr bgcolor='".$bgcolor."'>\n
<td bgcolor='".$bgcolor."'>$position</td>\n
<td bgcolor='".$bgcolor."'><a href=\"player-info.php?playerid=$playerid\" class='player-link'>$player</a></td>\n
<td bgcolor='".$bgcolor."'>$played</td>\n";
if ($totalMeets >= 1) echo "<td bgcolor='".$bgcolor."'>$meet1</td>\n";
if ($totalMeets >= 2) echo "<td bgcolor='".$bgcolor."'>$meet2</td>\n";
if ($totalMeets >= 3) echo "<td bgcolor='".$bgcolor."'>$meet3</td>\n";
if ($totalMeets >= 4) echo "<td bgcolor='".$bgcolor."'>$meet4</td>\n";
if ($totalMeets >= 5) echo "<td bgcolor='".$bgcolor."'>$meet5</td>\n";
if ($totalMeets >= 6) echo "<td bgcolor='".$bgcolor."'>$meet6</td>\n";
echo "<td bgcolor='".$bgcolor."'>$total</td>\n
<td bgcolor='".$bgcolor."'>$best4</td>\n
</tr>\n";
}
echo "</table>\n";
echo "</div>";
// Qualifying places descriptions.
echo "<p class='qualifier'>";
if ($info->aQualifyingPlaces > 0)
{
echo "<span class='qual'>$info->aQualifyingDescription</span>";
}
if ($info->bQualifyingPlaces > 0)
{
echo " <span style='white-space:nowrap'><span class='qual-b'>$info->bQualifyingDescription</span>";
}
echo "</p>";
sqlsrv_free_stmt($result);
?>
</div>
<!-- Header and menu -->
<?php include("includes/footer.inc"); ?>
</body>
</html>