source: trunk/index.php@ 31

Last change on this file since 31 was 31, checked in by chronos, 6 years ago
  • Modified: Merged almost same code for teams and families.
File size: 31.3 KB
Line 
1<?php
2
3include_once('Database.php');
4include_once('Config.php');
5include_once('Global.php');
6include_once('Run.php');
7include_once('PrefixMultiplier.php');
8
9session_start();
10
11class Application
12{
13 var $NoFullPage = false;
14
15 function Link($URL)
16 {
17 return($this->Config['BaseURL'].$URL);
18 }
19
20 function Run()
21 {
22
23 }
24}
25
26class MyApplication extends Application
27{
28 var $Database;
29 var $Config;
30 var $LeaderboardURL;
31
32 function __construct()
33 {
34 $this->LeaderboardURL = 'https://registrace.teribear.cz/Leaderboard';
35 }
36
37 function GetLatestYear()
38 {
39 $Year = 0;
40 $DbResult = $this->Database->query('SELECT DISTINCT(Year) AS Year FROM `Runner` ORDER BY Year DESC');
41 if ($DbResult->num_rows > 0)
42 {
43 $DbRow = $DbResult->fetch_assoc();
44 $Year = $DbRow['Year'];
45 }
46 return $Year;
47 }
48
49 function YearList($Path, $SelectedYear, $Table = 'Runner', $Where = '1')
50 {
51 $Output = 'Year: ';
52 $DbResult = $this->Database->query('SELECT DISTINCT(Year) AS Year FROM `'.$Table.'` WHERE '.$Where.' ORDER BY Year ASC');
53 while ($DbRow = $DbResult->fetch_assoc())
54 {
55 $Year = $DbRow['Year'];
56 $Item = '<a href="'.$this->Link($Path.$Year.'/').'">'.$Year.'</a>';
57 if ($SelectedYear == $Year) $Item = '<strong>'.$Item.'</strong>';
58 $Output .= $Item.' ';
59 }
60 return $Output;
61 }
62
63 function ItemsYearList($Path, $SelectedId, $Table = 'Runner', $Where = '1')
64 {
65 $Output = 'Year: ';
66 $DbResult = $this->Database->query('SELECT T1.Id AS Id, T2.Year AS Year FROM (SELECT DISTINCT(Id) AS Id FROM `'.$Table.'` WHERE '.$Where.' ORDER BY Year ASC) AS T1 '.
67 'LEFT JOIN '.$Table.' AS T2 ON T1.Id=T2.Id');
68 while ($DbRow = $DbResult->fetch_assoc())
69 {
70 $Item = '<a href="'.$this->Link($Path.$DbRow['Id'].'/').'">'.$DbRow['Year'].'</a>';
71 if ($SelectedId == $DbRow['Id']) $Item = '<strong>'.$Item.'</strong>';
72 $Output .= $Item.' ';
73 }
74 return $Output;
75 }
76
77 function ShowMenu()
78 {
79 $Output = '<div>'.
80 '<a href="'.$this->Link('/').'">Summary</a> '.
81 '<a href="'.$this->Link('/runners/').'">Runners</a> '.
82 '<a href="'.$this->Link('/teams/').'">Teams</a> '.
83 '<a href="'.$this->Link('/families/').'">Families</a> '.
84 '</div>';
85 return($Output);
86 }
87
88 function ProcessURL()
89 {
90 if(array_key_exists('REDIRECT_QUERY_STRING', $_SERVER))
91 $PathString = $_SERVER['REDIRECT_QUERY_STRING'];
92 else $PathString = '';
93 if(substr($PathString, -1, 1) == '/') $PathString = substr($PathString, 0, -1);
94 $PathItems = explode('/', $PathString);
95 if(array_key_exists('REQUEST_URI', $_SERVER) and (strpos($_SERVER['REQUEST_URI'], '?') !== false))
96 $_SERVER['QUERY_STRING'] = substr($_SERVER['REQUEST_URI'], strpos($_SERVER['REQUEST_URI'], '?') + 1);
97 else $_SERVER['QUERY_STRING'] = '';
98 parse_str($_SERVER['QUERY_STRING'], $_GET);
99 return($PathItems);
100 }
101
102 // Query: text from name
103 // Page: index of page, one page is 30 items
104 // Category: '', all, men, women, kids, families, teams
105 // TeamId: id of team
106 // Count: number of items per page, default 30
107 function QueryRunners($Category, $Page = 0, $TeamId = null, $Query = null, $StartNumber = null, $Count = null)
108 {
109 $URL = $this->LeaderboardURL.'/Home/GetRunners?category='.$Category;
110 if ($Page != 0) $URL .= '&page='.$Page;
111 if ($TeamId != null) $URL .= '&teamId='.$TeamId;
112 if ($Count != null) $URL .= '&count='.$Count;
113 if ($Query != null) $URL .= '&query='.$Query;
114 if ($StartNumber != null) $URL .= '&startNumber='.$StartNumber;
115 $Content = file_get_contents($URL);
116 $JSON = json_decode($Content, true);
117 return $JSON;
118 }
119
120 function QueryRunnersAll($Category = 'all', $Count = 0)
121 {
122 $ItemsPerPage = 30;
123 $MaxCount = 450 * 30;
124 if ($Count > 0) $MaxCount = $Count;
125 $Result = array();
126 $I = 0;
127 while (true)
128 {
129 $Page = $I * floor($MaxCount / $ItemsPerPage);
130 $JSON = $this->QueryRunners($Category, $Page, null, null, null, $MaxCount);
131 foreach ($JSON['items'] as $Item)
132 {
133 $Result[] = $Item;
134 }
135 if (($JSON['last'] == 'true') or (count($JSON) == 0)) break;
136 if ($I > 10) break; // Safe limit if last would not work
137 $I++;
138 }
139 return $Result;
140 }
141
142 function ShowEmpty()
143 {
144 $this->Database->query('DELETE FROM RunnerStat');
145 $this->Database->query('DELETE FROM Runner');
146 $this->Database->query('DELETE FROM TeamStat');
147 $this->Database->query('DELETE FROM Team');
148 $this->Database->query('DELETE FROM Import');
149 }
150
151 function Sync($Items, $Time)
152 {
153 $Year = date("Y", $Time);
154
155 // Load all runners
156 $DbResult = $this->Database->query('SELECT MAX(Id) AS Id FROM Runner');
157 $DbRow = $DbResult->fetch_assoc();
158 $NextRunnerId = $DbRow['Id'] + 1;
159
160 $Runners = array();
161 $DbResult = $this->Database->query('SELECT Runner.Id, Runner.ChipNumber, '.
162 '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Distance DESC LIMIT 1) AS Distance '.
163 'FROM Runner WHERE Year='.$Year);
164 while ($DbRow = $DbResult->fetch_assoc())
165 {
166 $Runners[$DbRow['ChipNumber']] = $DbRow;
167 }
168
169 // Load all teams
170 $DbResult = $this->Database->query('SELECT MAX(Id) AS Id FROM Team');
171 $DbRow = $DbResult->fetch_assoc();
172 $NextTeamId = $DbRow['Id'] + 1;
173
174 $Teams = array();
175 $DbResult = $this->Database->query('SELECT Team.Id, Team.WebId, Team.Name, '.
176 '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Distance DESC LIMIT 1) AS Distance '.
177 'FROM Team WHERE Year='.$Year);
178 while ($DbRow = $DbResult->fetch_assoc())
179 {
180 $Teams[$DbRow['WebId']] = $DbRow;
181 }
182
183 $Queries = array();
184 foreach ($Items as $Item)
185 {
186 if (($Item['Type'] == 'child') or ($Item['Type'] == 'woman') or ($Item['Type'] == 'man'))
187 {
188 if (!array_key_exists($Item['ChipNumber'], $Runners))
189 {
190 if ($Item['TeamId'] == null)
191 {
192 $TeamId = null;
193 } else
194 {
195 if (!array_key_exists($Item['TeamId'], $Teams))
196 {
197 $TeamId = $NextTeamId;
198 $Queries[] = $this->Database->GetInsert('Team', array(
199 'Id' => $TeamId,
200 'Name' => '',
201 'WebId' => $Item['TeamId'],
202 'Year' => $Year,
203 'IsFamily' => 0,
204 ));
205 $Teams[$Item['TeamId']] = array('Id' => $TeamId, 'Distance' => -1);
206 $NextTeamId++;
207 } else
208 $TeamId = $Teams[$Item['TeamId']]['Id'];
209 }
210
211 $Gender = 0;
212 if ($Item['Type'] == 'man') $Gender = 1;
213 if ($Item['Type'] == 'woman') $Gender = 2;
214 if ($Item['Type'] == 'child') $Gender = 3;
215 $RunnerId = $NextRunnerId;
216 $Queries[] = $this->Database->GetInsert('Runner', array(
217 'Id' => $RunnerId,
218 'Name' => $Item['Name'],
219 'Gender' => $Gender,
220 'Team' => $TeamId,
221 'ChipNumber' => $Item['ChipNumber'],
222 'Year' => $Year,
223 ));
224 $Runners[$Item['ChipNumber']] = array('Id' => $RunnerId, 'Distance' => -1);
225 $NextRunnerId++;
226 } else
227 $RunnerId = $Runners[$Item['ChipNumber']]['Id'];
228
229 if ($Runners[$Item['ChipNumber']]['Distance'] < $Item['OverallDistance'])
230 $Queries[] = $this->Database->GetInsert('RunnerStat', array(
231 'Time' => TimeToMysqlDateTime($Time),
232 'Runner' => $RunnerId,
233 'Distance' => $Item['OverallDistance'],
234 'Rank' => $Item['Pos'],
235 'Money' => $Item['Money'],
236 ));
237 } else
238 if (($Item['Type'] == 'team') or ($Item['Type'] == 'rodina'))
239 {
240 if ($Item['Type'] == 'rodina') $IsFamily = 1;
241 else $IsFamily = 0;
242 if (!array_key_exists($Item['GroupId'], $Teams))
243 {
244 $Queries[] = $this->Database->GetInsert('Team', array(
245 'Id' => $NextTeamId,
246 'Name' => $Item['Name'],
247 'WebId' => $Item['GroupId'],
248 'IsFamily' => $IsFamily,
249 'Year' => $Year,
250 ));
251 $TeamId = $NextTeamId;
252 $Teams[$Item['GroupId']] = array('Id' => $NextTeamId, 'Distance' => -1);
253 $NextTeamId++;
254 } else
255 $TeamId = $Teams[$Item['GroupId']]['Id'];
256
257
258 // Update missing team names
259 if ($Teams[$Item['GroupId']]['Name'] == "")
260 {
261 $Queries[] = $this->Database->GetUpdate('Team', 'Id='.$TeamId, array(
262 'Name' => $Item['Name'],
263 'IsFamily' => $IsFamily
264 ));
265 $Teams[$Item['GroupId']]['Name'] = $Item['Name'];
266 $Teams[$Item['GroupId']]['IsFamily'] = $IsFamily;
267 }
268
269 if ($Teams[$Item['GroupId']]['Distance'] < $Item['OverallDistance'])
270 $Queries[] = $this->Database->GetInsert('TeamStat', array(
271 'Time' => TimeToMysqlDateTime($Time),
272 'Team' => $TeamId,
273 'Distance' => $Item['OverallDistance'],
274 'Rank' => $Item['Pos'],
275 'Money' => $Item['Money'],
276 ));
277 } else
278 if ($Item['Type'] == '')
279 {
280 // Skip empty type
281 } else
282 {
283 echo('Unsupported type "'.$Item['Type'].'".<br/>');
284 }
285 }
286 //print_r($Queries);
287 //foreach ($Queries as $Query) $this->Database->query($Query);
288 $this->Database->Transaction($Queries);
289 }
290
291 function ShowSync()
292 {
293 $Time = time();
294 $Items = $this->QueryRunnersAll('all');
295 $ItemsWithoutProgress = array();
296 foreach ($Items as $Item)
297 {
298 unset($Item['Progress']);
299 $ItemsWithoutProgress[] = $Item;
300 }
301 $Hash = md5(serialize($ItemsWithoutProgress));
302
303 $DbResult = $this->Database->query('SELECT * FROM Import ORDER BY Time DESC LIMIT 1');
304 if ($DbResult->num_rows > 0)
305 {
306 $Import = $DbResult->fetch_assoc();
307 } else $Import = array('Hash' => '');
308 if ($Import['Hash'] != $Hash)
309 {
310 $this->Sync($Items, $Time);
311 $this->Database->insert('Import', array(
312 'Time' => TimeToMysqlDateTime($Time),
313 'Hash' => $Hash,
314 'ItemCount' => count($Items)
315 ));
316 //file_put_contents('import/'.$Hash.'.txt', print_r($Items, true));
317 }
318 }
319
320 function ShowTeams()
321 {
322 return $this->ShowTeamsInternal('Teams', 'Team', 'teams', 'team', 'Team', '(IsFamily=0)');
323 }
324
325 function ShowFamilies()
326 {
327 return $this->ShowTeamsInternal('Families', 'Family', 'families', 'family', 'Team', '(IsFamily=1)');
328 }
329
330 function ShowTeamsInternal($Title, $TitleItem, $UrlDir, $UrlDirItem, $Table, $Where = '1')
331 {
332 $Output = '';
333 $Output .= '<div class="page-title">'.$Title.'</div>';
334 $Year = $this->GetYear();
335
336 $Where = '(Year='.$Year.') AND '.$Where;
337 if (array_key_exists('query', $_GET) and ($_GET['query'] != ''))
338 {
339 $Where .= ' AND (Name LIKE "%'.addslashes($_GET['query']).'%")';
340 }
341 $Output .= '<div class="section-title">'.$this->YearList('/'.$UrlDir.'/', $Year, $Table).' Name: '.$this->ShowSearch().'</div>';
342
343 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `'.$Table.'` WHERE '.$Where);
344 $DbRow = $DbResult->fetch_row();
345 $PageList = GetPageList($DbRow[0]);
346
347 $Output .= '<div id="list_content">';
348 $Output .= $PageList['Output'];
349 $TableColumns = array(
350 array('Name' => 'Name', 'Title' => 'Name'),
351 array('Name' => 'RunnersCount', 'Title' => 'Runners'),
352 array('Name' => 'Distance', 'Title' => 'Distance'),
353 array('Name' => 'Money', 'Title' => 'Money'),
354 array('Name' => 'DistanceRunner', 'Title' => 'Distance per runner'),
355 array('Name' => 'MoneyRunner', 'Title' => 'Money per runner'),
356 array('Name' => 'Rank', 'Title' => 'Rank'),
357 );
358 $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
359 $Output .= '<table class="WideTable">';
360 $Output .= $Order['Output'];
361 $DbResult = $this->Database->select('Team', '*, '.
362 '(SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id) AS RunnersCount, '.
363 '(SELECT TeamStat.Distance FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY TeamStat.Time DESC LIMIT 1) AS Distance, '.
364 '(SELECT TeamStat.Money FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Money, '.
365 '(SELECT TeamStat.Time FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Time, '.
366 '(SELECT TeamStat.Rank FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Rank, '.
367 'ROUND((SELECT TeamStat.Distance FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY TeamStat.Time DESC LIMIT 1) / (SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id), 1) AS DistanceRunner, '.
368 'ROUND((SELECT TeamStat.Money FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) / (SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id)) AS MoneyRunner',
369 $Where.$Order['SQL'].$PageList['SQLLimit']);
370 while($Item = $DbResult->fetch_assoc())
371 {
372 if ($Item['Name'] == '') $Item['Name'] = $TitleItem.' '.$Item['WebId'];
373 $Output .= '<tr>'.
374 '<td><a href="'.$this->Link('/'.$UrlDirItem.'/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
375 '<td>'.$Item['RunnersCount'].'</td>'.
376 '<td>'.$Item['Distance'].'</td>'.
377 '<td>'.$Item['Money'].'</td>'.
378 '<td>'.$Item['DistanceRunner'].'</td>'.
379 '<td>'.$Item['MoneyRunner'].'</td>'.
380 '<td>'.$Item['Rank'].'</td>'.
381 '</tr>';
382 }
383 $Output .= '</table>';
384 $Output .= $PageList['Output'];
385 $Output .= '</div>';
386
387 return($Output);
388 }
389
390 function RunningState($Time)
391 {
392 $Output = HumanDateTime($Time);
393 if ($Time > time() - 30 * 60)
394 $Output = '<span style="color: green; font-weight: bold; ">'.$Output.'</span>';
395 return $Output;
396 }
397
398 function ShowTeam()
399 {
400 return $this->ShowTeamInternal('Team', 'team');
401 }
402
403 function ShowFamily()
404 {
405 return $this->ShowTeamInternal('Family', 'family');
406 }
407
408 function ShowTeamInternal($Title, $UrlDir)
409 {
410 $Output = '';
411
412 $TeamId = 0;
413 if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
414 $TeamId = $this->PathItems[count($this->PathItems) - 1];
415 if (!is_numeric($TeamId)) die('TeamId needs to be numeric');
416
417 $DbResult = $this->Database->query('SELECT Team.*, '.
418 '(SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id) AS RunnerCount, '.
419 '(SELECT TeamStat.Distance FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY TeamStat.Time DESC LIMIT 1) AS Distance, '.
420 '(SELECT TeamStat.Money FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Money, '.
421 '(SELECT TeamStat.Rank FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Rank '.
422 'FROM Team WHERE Id='.$TeamId);
423 $DbRow = $DbResult->fetch_assoc();
424 $Output .= '<div class="page-title">'.$Title.' '.$DbRow['Name'].'</div>';
425 $Output .= '<div class="section-title">'.$this->ItemsYearList('/'.$UrlDir.'/', $TeamId, 'Team', 'Name="'.$DbRow['Name'].'"').'</div>';
426 $Output .= '<div class="section-title">Runners: '.$DbRow['RunnerCount'].', Distance: '.$DbRow['Distance'].' km, Money: '.$DbRow['Money'].' Kč, Rank: '.$DbRow['Rank'].'</div>';
427
428 $Where = 'Team='.$TeamId;
429
430 // Show runners
431 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
432 $DbRow = $DbResult->fetch_row();
433 $PageList = GetPageList($DbRow[0]);
434
435 $Gender = array('', 'Men', 'Woman', 'Kid');
436 $Output .= '<div id="list_content">';
437 $Output .= $PageList['Output'];
438 $TableColumns = array(
439 array('Name' => 'Name', 'Title' => 'Name'),
440 array('Name' => 'Gender', 'Title' => 'Category'),
441 array('Name' => 'Distance', 'Title' => 'Distance'),
442 array('Name' => 'Money', 'Title' => 'Money'),
443 array('Name' => 'Rank', 'Title' => 'Rank'),
444 array('Name' => 'Time', 'Title' => 'Last change'),
445 );
446 $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
447 $Output .= '<table class="WideTable">';
448 $Output .= $Order['Output'];
449 $DbResult = $this->Database->select('Runner', '*, '.
450 '(SELECT RunnerStat.Distance FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Distance'.
451 ', (SELECT RunnerStat.Money FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Money'.
452 ', (SELECT RunnerStat.Time FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Time'.
453 ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
454 $Where.$Order['SQL'].$PageList['SQLLimit']);
455 while($Item = $DbResult->fetch_assoc())
456 {
457 $Output .= '<tr>'.
458 '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
459 '<td>'.$Gender[$Item['Gender']].'</td>'.
460 '<td>'.$Item['Distance'].'</td>'.
461 '<td>'.$Item['Money'].'</td>'.
462 '<td>'.$Item['Rank'].'</td>'.
463 '<td>'.$this->RunningState(MysqlDateTimeToTime($Item['Time'])).'</td>'.
464 '</tr>';
465 }
466 $Output .= '</table>';
467 $Output .= $PageList['Output'];
468 $Output .= '</div><br/>';
469
470 $Output .= $this->ShowDetailed('Team', $TeamId);
471 $Output .= $this->ShowDaily('Team', $TeamId);
472
473 return($Output);
474 }
475
476 function ShowDetailed($Table, $Id)
477 {
478 $PrefixMultiplier = new PrefixMultiplier();
479
480 $Output = '<div class="section-title">Lap progress</div>';
481 $Where = $Table.'Stat.'.$Table.'='.$Id;
482 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `'.$Table.'Stat` WHERE '.$Where);
483 $DbRow = $DbResult->fetch_row();
484 $PageList = GetPageList($DbRow[0]);
485
486 $Output .= '<div id="list_content">';
487 $Output .= $PageList['Output'];
488 $TableColumns = array(
489 array('Name' => 'Time', 'Title' => 'Time'),
490 array('Name' => 'Distance', 'Title' => 'Distance [km]'),
491 array('Name' => 'Money', 'Title' => 'Money [Kč]'),
492 array('Name' => 'Rank', 'Title' => 'Rank'),
493 array('Name' => 'Duration', 'Title' => 'Duration'),
494 array('Name' => 'Length', 'Title' => 'Length [km]'),
495 array('Name' => 'Speed', 'Title' => 'Speed [km/hour]'),
496 );
497 $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
498 $Output .= '<table class="WideTable">';
499 $Output .= $Order['Output'];
500 $DbResult = $this->Database->query('SELECT *'.
501 ', (SELECT TIME_TO_SEC(TIMEDIFF('.$Table.'Stat.Time, B.Time)) FROM '.$Table.'Stat AS B WHERE (B.Time < '.$Table.'Stat.Time) AND (B.'.$Table.' = '.$Table.'Stat.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Duration'.
502 ', (SELECT '.$Table.'Stat.Distance - B.Distance FROM '.$Table.'Stat AS B WHERE (B.Time < '.$Table.'Stat.Time) AND (B.'.$Table.' = '.$Table.'Stat.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Length'.
503 ', NULL AS Speed'.
504 ' FROM '.$Table.'Stat'.
505 ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
506 while($Item = $DbResult->fetch_assoc())
507 {
508 $Output .= '<tr>'.
509 '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
510 '<td>'.$Item['Distance'].'</td>'.
511 '<td>'.$Item['Money'].'</td>'.
512 '<td>'.$Item['Rank'].'</td>';
513 if ($Item['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
514 else $Output .= '<td>&nbsp;</td>';
515 $Output .= '<td>'.$Item['Length'].'</td>';
516 if ($Item['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Length'] / $Item['Duration'] * 3600, '', 4, 'Decimal').'</td>';
517 else $Output .= '<td>&nbsp;</td>';
518 $Output .= '</tr>';
519 }
520 $Output .= '</table>';
521 $Output .= $PageList['Output'];
522 $Output .= '</div><br/>';
523 return $Output;
524 }
525
526 function ShowDaily($Table, $Id)
527 {
528 $PrefixMultiplier = new PrefixMultiplier();
529 $Where = '1';
530
531 $DailyTableMaxId = 'SELECT * FROM (SELECT MAX(Id) AS MaxId FROM `'.$Table.'Stat` AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$Table.'Stat AS T3 ON T3.Id=T2.MaxId';
532 $DailyTableMinId = 'SELECT * FROM (SELECT MIN(Id) AS MinId FROM `'.$Table.'Stat` AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$Table.'Stat AS T3 ON T3.Id=T2.MinId';
533 $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$DailyTableMaxId.') AS B');
534 $DbRow = $DbResult->fetch_row();
535 $PageList = GetPageList($DbRow[0]);
536
537 $Output = '<div class="section-title">Daily progress</div>';
538 $Output .= '<div id="list_content">';
539 $Output .= $PageList['Output'];
540 $TableColumns = array(
541 array('Name' => 'Time', 'Title' => 'Time'),
542 array('Name' => 'Distance', 'Title' => 'Distance [km]'),
543 array('Name' => 'Money', 'Title' => 'Money [Kč]'),
544 array('Name' => 'Rank', 'Title' => 'Rank'),
545 array('Name' => 'Duration', 'Title' => 'Duration'),
546 array('Name' => 'Length', 'Title' => 'Length [km]'),
547 array('Name' => 'Speed', 'Title' => 'Speed [km/hour]'),
548 );
549 $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
550 $Output .= '<table class="WideTable">';
551 $Output .= $Order['Output'];
552 $DbResult = $this->Database->query('SELECT * '.
553 ', (SELECT T4.Distance - B.Distance + 1.5 FROM ('.$DailyTableMinId.') AS B WHERE (DATE(B.Time) = DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Length'.
554 ', (SELECT TIME_TO_SEC(TIMEDIFF(T4.Time, B.Time)) / (Length - 1.5) * Length FROM ('.$DailyTableMinId.') AS B WHERE (DATE(B.Time) = DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Duration'.
555 ', NULL AS Speed'.
556 ' FROM ('.$DailyTableMaxId.') AS T4'.
557 ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
558 while($Item = $DbResult->fetch_assoc())
559 {
560 $Output .= '<tr>'.
561 '<td>'.HumanDate(MysqlDateTimeToTime($Item['Time'])).'</td>'.
562 '<td>'.$Item['Distance'].'</td>'.
563 '<td>'.$Item['Money'].'</td>'.
564 '<td>'.$Item['Rank'].'</td>';
565 if ($Item['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
566 else $Output .= '<td>&nbsp;</td>';
567 $Output .= '<td>'.$Item['Length'].'</td>';
568 if ($Item['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Length'] / $Item['Duration'] * 3600, '', 4, 'Decimal').'</td>';
569 else $Output .= '<td>&nbsp;</td>';
570 $Output .= '</tr>';
571 }
572 $Output .= '</table>';
573 $Output .= $PageList['Output'];
574 $Output .= '</div>';
575 return $Output;
576 }
577
578 function ShowSearch()
579 {
580 if (array_key_exists('query', $_GET)) $Query = $_GET['query'];
581 else $Query = '';
582 $Output = '<form action="?" method="get" style="display: inline;">';
583 $Output .= '<input type="text" size="10" name="query" value="'.$Query.'"/> '.
584 '<input type="submit" value="Search"/>';
585 $Output .= '</form>';
586 return $Output;
587 }
588
589 function ShowRunners()
590 {
591 $Output = '<div class="page-title">Runners</div>';
592 $Year = $this->GetYear();
593
594 $Output .= '<div class="section-title">'.$this->YearList('/runners/', $Year, 'Runner').' Name: '.$this->ShowSearch().'</div>';
595 $Where = '(Year='.$Year.')';
596 if (array_key_exists('query', $_GET) and ($_GET['query'] != ''))
597 {
598 $Where .= ' AND (Name LIKE "%'.addslashes($_GET['query']).'%")';
599 }
600
601 $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
602 $DbRow = $DbResult->fetch_row();
603 $PageList = GetPageList($DbRow[0]);
604
605 $Gender = array('', 'Man', 'Woman', 'Kid');
606 $Output .= '<div id="list_content">';
607 $Output .= $PageList['Output'];
608 $TableColumns = array(
609 array('Name' => 'Name', 'Title' => 'Name'),
610 array('Name' => 'Gender', 'Title' => 'Category'),
611 array('Name' => 'Distance', 'Title' => 'Distance'),
612 array('Name' => 'Money', 'Title' => 'Money'),
613 array('Name' => 'Rank', 'Title' => 'Rank'),
614 array('Name' => 'Time', 'Title' => 'Last change'),
615 );
616 $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
617 $Output .= '<table class="WideTable">';
618 $Output .= $Order['Output'];
619 $DbResult = $this->Database->select('Runner', '*, '.
620 '(SELECT RunnerStat.Distance FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Distance'.
621 ', (SELECT RunnerStat.Money FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Money'.
622 ', (SELECT RunnerStat.Time FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Time'.
623 ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
624 $Where.$Order['SQL'].$PageList['SQLLimit']);
625 while($Item = $DbResult->fetch_assoc())
626 {
627 $Output .= '<tr>'.
628 '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
629 '<td>'.$Gender[$Item['Gender']].'</td>'.
630 '<td>'.$Item['Distance'].'</td>'.
631 '<td>'.$Item['Money'].'</td>'.
632 '<td>'.$Item['Rank'].'</td>'.
633 '<td>'.$this->RunningState(MysqlDateTimeToTime($Item['Time'])).'</td>'.
634 '</tr>';
635 }
636 $Output .= '</table>';
637 $Output .= $PageList['Output'];
638 $Output .= '</div>';
639
640 return($Output);
641 }
642
643 function ShowRunner()
644 {
645 $PrefixMultiplier = new PrefixMultiplier();
646 $Output = '';
647
648 $RunnerId = 0;
649 if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
650 $RunnerId = $this->PathItems[count($this->PathItems) - 1];
651 if (!is_numeric($RunnerId)) die('Runner id needs to be numeric');
652
653 $DbResult = $this->Database->query('SELECT Runner.Name, Team.Name AS TeamName, Team.Id AS TeamId FROM Runner LEFT JOIN Team ON Team.Id=Runner.Team WHERE Runner.Id='.$RunnerId);
654 $DbRow = $DbResult->fetch_assoc();
655 $Output .= '<div class="page-title">Runner '.$DbRow['Name'].'</div>';
656 if ($DbRow['TeamName'] != '')
657 $Output .= '<div class="section-title"><a href="'.$this->Link('/team/'.$DbRow['TeamId']).'">'.$DbRow['TeamName'].'</a></div>';
658 $Output .= '<div class="section-title">'.$this->ItemsYearList('/runner/', $RunnerId, 'Runner', 'Name="'.$DbRow['Name'].'"').'</div>';
659
660 $Output .= $this->ShowDetailed('Runner', $RunnerId);
661 $Output .= $this->ShowDaily('Runner', $RunnerId);
662 return($Output);
663 }
664
665 function GetTotals($Where = '1', $Table = 'Runner')
666 {
667 $DbResult = $this->Database->query('SELECT (SELECT COUNT(*) FROM '.$Table.' WHERE '.$Where.') AS TotalCount, '.
668 '(SELECT SUM(T1.Distance) FROM (SELECT (SELECT Distance FROM '.$Table.'Stat WHERE '.$Table.'Stat.'.$Table.' = '.$Table.'.Id ORDER BY Time DESC LIMIT 1) AS Distance FROM '.$Table.' WHERE '.$Where.') AS T1) AS TotalDistance, '.
669 '(SELECT SUM(T2.Money) FROM (SELECT (SELECT Money FROM '.$Table.'Stat WHERE '.$Table.'Stat.'.$Table.' = '.$Table.'.Id ORDER BY Time DESC LIMIT 1) AS Money FROM '.$Table.' WHERE '.$Where.') AS T2) AS TotalMoney');
670 $DbRow = $DbResult->fetch_assoc();
671 return $DbRow;
672 }
673
674 function GetYear()
675 {
676 $Year = 0;
677 if (count($this->PathItems) > 0)
678 {
679 $Param = $this->PathItems[count($this->PathItems) - 1];
680 if (is_numeric($this->PathItems[count($this->PathItems) - 1]))
681 $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
682 }
683 if ($Year == 0) $Year = $this->GetLatestYear();
684 return $Year;
685 }
686
687 function ShowMain()
688 {
689 $Output = '';
690 $Output .= '<p>This website collects data from official <a href="'.$this->LeaderboardURL.'">leaderboard</a> site and tracks and presents progress of runners and teams.</p>';
691 $Output .= '<div class="page-title">Summary</div>';
692 $Year = $this->GetYear();
693
694 $Output .= '<div class="section-title">'.$this->YearList('/', $Year).'</div>';
695
696 $Runners = $this->GetTotals('(Year='.$Year.')');
697 $Men = $this->GetTotals('(Runner.Gender=1) AND (Year='.$Year.')', 'Runner');
698 $Women = $this->GetTotals('(Runner.Gender=2) AND (Year='.$Year.')', 'Runner');
699 $Kids = $this->GetTotals('(Runner.Gender=3) AND (Year='.$Year.')', 'Runner');
700 $Teams = $this->GetTotals('(Team.IsFamily=0) AND (Year='.$Year.')', 'Team');
701 $Families = $this->GetTotals('(Team.IsFamily=1) AND (Year='.$Year.')', 'Team');
702
703 $Output .= '<table class="WideTable">';
704 $Output .= '<tr><th>Category</th><th>Count</th><th>Distance [km]</th><th>Money [Kč]</th></tr>';
705 $Output .= '<tr><td>Everyone</td><td>'.$Runners['TotalCount'].'</td><td>'.$Runners['TotalDistance'].'</td><td>'.$Runners['TotalMoney'].'</td></tr>';
706 $Output .= '<tr><td>Men</td><td>'.$Men['TotalCount'].'</td><td>'.$Men['TotalDistance'].'</td><td>'.$Men['TotalMoney'].'</td></tr>';
707 $Output .= '<tr><td>Women</td><td>'.$Women['TotalCount'].'</td><td>'.$Women['TotalDistance'].'</td><td>'.$Women['TotalMoney'].'</td></tr>';
708 $Output .= '<tr><td>Kids</td><td>'.$Kids['TotalCount'].'</td><td>'.$Kids['TotalDistance'].'</td><td>'.$Kids['TotalMoney'].'</td></tr>';
709 $Output .= '<tr><td>Teams</td><td>'.$Teams['TotalCount'].'</td><td>'.$Teams['TotalDistance'].'</td><td>'.$Teams['TotalMoney'].'</td></tr>';
710 $Output .= '<tr><td>Families</td><td>'.$Families['TotalCount'].'</td><td>'.$Families['TotalDistance'].'</td><td>'.$Families['TotalMoney'].'</td></tr>';
711 $Output .= '</table>';
712
713 return $Output;
714 }
715
716 function ShowPage($Content)
717 {
718 global $Config;
719
720 $Lang = 'en';
721 $Output = '<?xml version="1.0" encoding="'.$this->Config['Encoding'].'"?>'."\n".
722 '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">'.
723 '<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="'.$Lang.'" lang="'.$Lang.'">'.
724 '<head>'.
725 '<link rel="stylesheet" href="'.$this->Link('/style.css').'" type="text/css" media="all" />'.
726 '<meta http-equiv="content-type" content="application/xhtml+xml; charset='.$this->Config['Encoding'].'" />'.
727 '<meta name="viewport" content="width=device-width, initial-scale=1">'.
728 '<script src="'.$this->Link('/jquery.js').'"></script>';
729 $Output .= '<title>Teribear stats</title>'.
730 '</head><body>';
731 $Output .= $Content;
732 $Output .= '<br/><div class="footer">Contact: <a href="mailto:'.$Config['Contact'].'">'.$Config['Contact'].'</a> '.
733 '<a href="https://app.zdechov.net/teribear/">Source code</a></div>';
734 $Output .= '</body></html>';
735 return($Output);
736 }
737
738 function Run()
739 {
740 global $Config;
741
742 $this->Config = $Config;
743 $this->Database = new Database();
744 $this->Database->Connect($this->Config['Database']['Host'], $this->Config['Database']['User'],
745 $this->Config['Database']['Password'], $this->Config['Database']['Database']);
746 $this->Database->Prefix = $this->Config['Database']['Prefix'];
747 $this->Database->charset($this->Config['Database']['Charset']);
748 //$this->Database->ShowSQLError = true;
749 //$this->Database->ShowSQLQuery = true;
750 $this->PathItems = $this->ProcessURL();
751
752 $Output = '';
753
754 $this->Year = 0;
755 if (count($this->PathItems) > 0)
756 {
757 $Item = $this->PathItems[0];
758 if ($Item == 'sync') $Output .= $this->ShowSync();
759 //else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
760 else if($Item == 'runner') $Output .= $this->ShowRunner();
761 else if($Item == 'runners') $Output .= $this->ShowRunners();
762 else if($Item == 'team') $Output .= $this->ShowTeam();
763 else if($Item == 'teams') $Output .= $this->ShowTeams();
764 else if($Item == 'family') $Output .= $this->ShowFamily();
765 else if($Item == 'families') $Output .= $this->ShowFamilies();
766 else $Output .= $this->ShowMain();
767 } else $Output .= $this->ShowMain();
768 if (!$this->NoFullPage)
769 {
770 $Output = $this->ShowMenu().$Output;
771 echo($this->ShowPage($Output));
772 } else echo($Output);
773 }
774}
775
776$Application = new MyApplication();
777$Application->Run();
Note: See TracBrowser for help on using the repository browser.