Changeset 22


Ignore:
Timestamp:
Sep 6, 2019, 10:15:26 PM (5 years ago)
Author:
chronos
Message:
  • Added: Show team overall results on team page.
  • Added: Show detailed and dalily stats for teams.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/index.php

    r21 r22  
    401401      ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
    402402      $Where.$Order['SQL'].$PageList['SQLLimit']);
    403     while($MeetItem = $DbResult->fetch_assoc())
     403    while($Item = $DbResult->fetch_assoc())
    404404    {
    405405      $Output .= '<tr>'.
    406         '<td><a href="'.$this->Link('/runner/'.$MeetItem['Id'].'/').'">'.$MeetItem['Name'].'</a></td>'.
    407         '<td>'.$Gender[$MeetItem['Gender']].'</td>'.
    408         '<td>'.$MeetItem['Distance'].'</td>'.
    409         '<td>'.$MeetItem['Money'].'</td>'.
    410         '<td>'.$MeetItem['Rank'].'</td>'.
    411         '<td>'.HumanDateTime(MysqlDateTimeToTime($MeetItem['Time'])).'</td>'.
     406        '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
     407        '<td>'.$Gender[$Item['Gender']].'</td>'.
     408        '<td>'.$Item['Distance'].'</td>'.
     409        '<td>'.$Item['Money'].'</td>'.
     410        '<td>'.$Item['Rank'].'</td>'.
     411        '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
    412412        '</tr>';
    413413    }
    414414    $Output .= '</table>';
    415415    $Output .= $PageList['Output'];
    416     $Output .= '</div>';
     416    $Output .= '</div><br>';
     417
     418    $Output .= $this->ShowDetailed('Team', $TeamId);
     419    $Output .= $this->ShowDaily('Team', $TeamId);
    417420
    418421    return($Output);
    … …  
    484487    $Output = '';
    485488
    486     $TeamId = 1;
     489    $TeamId = 0;
    487490    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
    488491      $TeamId = $this->PathItems[count($this->PathItems) - 1];
    489492    if (!is_numeric($TeamId)) die('TeamId needs to be numeric');
    490493
    491     $DbResult = $this->Database->query('SELECT * FROM Team WHERE Id='.$TeamId);
     494    $DbResult = $this->Database->query('SELECT Team.*, '.
     495      '(SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id) AS RunnerCount, '.
     496      '(SELECT TeamStat.Distance FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY TeamStat.Time DESC LIMIT 1) AS Distance, '.
     497      '(SELECT TeamStat.Money FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Money, '.
     498      '(SELECT TeamStat.Rank FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Rank '.
     499      'FROM Team WHERE Id='.$TeamId);
    492500    $DbRow = $DbResult->fetch_assoc();
    493501    $Output .= '<div class="page-title">Team '.$DbRow['Name'].'</div>';
    494 
     502    $Output .= '<div class="section-title">Runners: '.$DbRow['RunnerCount'].', Distance: '.$DbRow['Distance'].' km, Money: '.$DbRow['Money'].' Kč, Rank: '.$DbRow['Rank'].'</div>';
    495503
    496504    $Where = 'Team='.$TeamId;
    497505
     506    // Show runners
    498507    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
    499508    $DbRow = $DbResult->fetch_row();
    … …  
    501510
    502511    $Gender = array('', 'Men', 'Woman', 'Kid');
    503     $Output .= '<div id="list_content">';
    504     $Output .= $PageList['Output'];
    505     $TableColumns = array(
    506       array('Name' => 'Name', 'Title' => 'Name'),
    507       array('Name' => 'Gender', 'Title' => 'Category'),
    508       array('Name' => 'Distance', 'Title' => 'Distance'),
    509       array('Name' => 'Money', 'Title' => 'Money'),
    510       array('Name' => 'Rank', 'Title' => 'Rank'),
    511       array('Name' => 'Time', 'Title' => 'Last change'),
    512     );
    513     $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
    514     $Output .= '<table class="WideTable">';
    515     $Output .= $Order['Output'];
    516     $DbResult = $this->Database->select('Runner', '*, '.
    517       '(SELECT RunnerStat.Distance FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Distance'.
    518       ', (SELECT RunnerStat.Money FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Money'.
    519       ', (SELECT RunnerStat.Time FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Time'.
    520       ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
    521       $Where.$Order['SQL'].$PageList['SQLLimit']);
    522     while($MeetItem = $DbResult->fetch_assoc())
    523     {
    524       $Output .= '<tr>'.
    525         '<td><a href="'.$this->Link('/runner/'.$MeetItem['Id'].'/').'">'.$MeetItem['Name'].'</a></td>'.
    526         '<td>'.$Gender[$MeetItem['Gender']].'</td>'.
    527         '<td>'.$MeetItem['Distance'].'</td>'.
    528         '<td>'.$MeetItem['Money'].'</td>'.
    529         '<td>'.$MeetItem['Rank'].'</td>'.
    530         '<td>'.HumanDateTime(MysqlDateTimeToTime($MeetItem['Time'])).'</td>'.
    531         '</tr>';
    532     }
    533     $Output .= '</table>';
    534     $Output .= $PageList['Output'];
    535     $Output .= '</div>';
    536 
    537     return($Output);
    538   }
    539 
    540   function ShowRunners()
    541   {
    542     $Output = '<div class="page-title">Runners</div>';
    543 
    544     $Year = 0;
    545     if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
    546       $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
    547     if ($Year == 0) $Year = $this->GetLatestYear();
    548 
    549     $Output .= $this->YearList('/runners/', $Year);
    550 
    551     $Where = 'Year='.$Year;
    552     $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
    553     $DbRow = $DbResult->fetch_row();
    554     $PageList = GetPageList($DbRow[0]);
    555 
    556     $Gender = array('', 'Man', 'Woman', 'Kid');
    557512    $Output .= '<div id="list_content">';
    558513    $Output .= $PageList['Output'];
    … …  
    577532    {
    578533      $Output .= '<tr>'.
    579         '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
     534        '<td><a href="'.$this->Link('/runner/'.$tem['Id'].'/').'">'.$Item['Name'].'</a></td>'.
    580535        '<td>'.$Gender[$Item['Gender']].'</td>'.
    581536        '<td>'.$Item['Distance'].'</td>'.
    … …  
    587542    $Output .= '</table>';
    588543    $Output .= $PageList['Output'];
    589     $Output .= '</div>';
     544    $Output .= '</div><br/>';
     545
     546    $Output .= $this->ShowDetailed('Team', $TeamId);
     547    $Output .= $this->ShowDaily('Team', $TeamId);
    590548
    591549    return($Output);
    592550  }
    593551
    594   function ShowRunner()
     552  function ShowDetailed($Table, $Id)
    595553  {
    596554    $PrefixMultiplier = new PrefixMultiplier();
    597     $Output = '';
    598 
    599     $RunnerId = 1;
    600     if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
    601       $RunnerId = $this->PathItems[count($this->PathItems) - 1];
    602     if (!is_numeric($RunnerId)) die('Runner id needs to be numeric');
    603 
    604     $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);
    605     $DbRow = $DbResult->fetch_assoc();
    606     $Output .= '<div class="page-title">Runner '.$DbRow['Name'].'</div>';
    607     if ($DbRow['TeamName'] != '')
    608       $Output .= '<div class="section-title"><a href="'.$this->Link('/team/'.$DbRow['TeamId']).'">'.$DbRow['TeamName'].'</a></div>';
    609 
    610     $Where = 'RunnerStat.Runner='.$RunnerId;
    611 
    612     // Show detailed stats
    613     $Output .= '<div class="section-title">Detailed</div>';
    614     $DbResult = $this->Database->query('SELECT COUNT(*) FROM `RunnerStat` WHERE '.$Where);
     555
     556    $Output = '<div class="section-title">Detailed</div>';
     557    $Where = $Table.'Stat.'.$Table.'='.$Id;
     558    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `'.$Table.'Stat` WHERE '.$Where);
    615559    $DbRow = $DbResult->fetch_row();
    616560    $PageList = GetPageList($DbRow[0]);
    … …  
    631575    $Output .= $Order['Output'];
    632576    $DbResult = $this->Database->query('SELECT *'.
    633       ', (SELECT TIME_TO_SEC(TIMEDIFF(RunnerStat.Time, B.Time)) FROM RunnerStat AS B WHERE (B.Time < RunnerStat.Time) AND (B.Runner = RunnerStat.Runner) ORDER BY B.Time DESC LIMIT 1) AS Duration'.
    634       ', (SELECT RunnerStat.Distance - B.Distance FROM RunnerStat AS B WHERE (B.Time < RunnerStat.Time) AND (B.Runner = RunnerStat.Runner) ORDER BY B.Time DESC LIMIT 1) AS Length'.
     577      ', (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'.
     578      ', (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'.
    635579      ', NULL AS Speed'.
    636       ' FROM RunnerStat'.
     580      ' FROM '.$Table.'Stat'.
    637581      ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
    638     while($MeetItem = $DbResult->fetch_assoc())
     582    while($Item = $DbResult->fetch_assoc())
    639583    {
    640584      $Output .= '<tr>'.
    641         '<td>'.HumanDateTime(MysqlDateTimeToTime($MeetItem['Time'])).'</td>'.
    642         '<td>'.$MeetItem['Distance'].'</td>'.
    643         '<td>'.$MeetItem['Money'].'</td>'.
    644         '<td>'.$MeetItem['Rank'].'</td>';
    645       if ($MeetItem['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($MeetItem['Duration'], '', 4, 'Time').'</td>';
     585        '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
     586        '<td>'.$Item['Distance'].'</td>'.
     587        '<td>'.$Item['Money'].'</td>'.
     588        '<td>'.$Item['Rank'].'</td>';
     589      if ($Item['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
    646590        else $Output .= '<td>&nbsp;</td>';
    647       $Output .= '<td>'.$MeetItem['Length'].'</td>';
    648       if ($MeetItem['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($MeetItem['Length'] / $MeetItem['Duration'] * 3600, '', 4, 'Decimal').'</td>';
     591      $Output .= '<td>'.$Item['Length'].'</td>';
     592      if ($Item['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Length'] / $Item['Duration'] * 3600, '', 4, 'Decimal').'</td>';
    649593        else $Output .= '<td>&nbsp;</td>';
    650594      $Output .= '</tr>';
    … …  
    653597    $Output .= $PageList['Output'];
    654598    $Output .= '</div><br/>';
    655 
    656     // Show daily stats
     599    return $Output;
     600  }
     601
     602  function ShowDaily($Table, $Id)
     603  {
     604    $PrefixMultiplier = new PrefixMultiplier();
    657605    $Where = '1';
    658606
    659     $DailyTableMaxId = 'SELECT * FROM (SELECT MAX(Id) AS MaxId FROM `RunnerStat` AS T1 WHERE T1.Runner='.$RunnerId.' GROUP BY DATE(Time)) AS T2 LEFT JOIN RunnerStat AS T3 ON T3.Id=T2.MaxId';
    660     $DailyTableMinId = 'SELECT * FROM (SELECT MIN(Id) AS MinId FROM `RunnerStat` AS T1 WHERE T1.Runner='.$RunnerId.' GROUP BY DATE(Time)) AS T2 LEFT JOIN RunnerStat AS T3 ON T3.Id=T2.MinId';
     607    $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';
     608    $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';
    661609    $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$DailyTableMaxId.') AS B');
    662610    $DbRow = $DbResult->fetch_row();
    663611    $PageList = GetPageList($DbRow[0]);
    664612
    665     $Output .= '<div class="section-title">Daily</div>';
     613    $Output = '<div class="section-title">Daily</div>';
    666614    $Output .= '<div id="list_content">';
    667615    $Output .= $PageList['Output'];
    … …  
    679627    $Output .= $Order['Output'];
    680628    $DbResult = $this->Database->query('SELECT * '.
    681       ', (SELECT T4.Distance - B.Distance + 1.5 FROM ('.$DailyTableMinId.') AS B WHERE (DATE(B.Time) = DATE(T4.Time)) AND (B.Runner = T4.Runner) ORDER BY B.Time DESC LIMIT 1) AS Length'.
    682       ', (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.Runner = T4.Runner) ORDER BY B.Time DESC LIMIT 1) AS Duration'.
     629      ', (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'.
     630      ', (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'.
    683631      ', NULL AS Speed'.
    684632      ' FROM ('.$DailyTableMaxId.') AS T4'.
    685633      ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
    686     while($MeetItem = $DbResult->fetch_assoc())
     634    while($Item = $DbResult->fetch_assoc())
    687635    {
    688636      $Output .= '<tr>'.
    689         '<td>'.HumanDateTime(MysqlDateTimeToTime($MeetItem['Time'])).'</td>'.
    690         '<td>'.$MeetItem['Distance'].'</td>'.
    691         '<td>'.$MeetItem['Money'].'</td>'.
    692         '<td>'.$MeetItem['Rank'].'</td>';
    693       if ($MeetItem['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($MeetItem['Duration'], '', 4, 'Time').'</td>';
     637        '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
     638        '<td>'.$Item['Distance'].'</td>'.
     639        '<td>'.$Item['Money'].'</td>'.
     640        '<td>'.$Item['Rank'].'</td>';
     641      if ($Item['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
    694642        else $Output .= '<td>&nbsp;</td>';
    695       $Output .= '<td>'.$MeetItem['Length'].'</td>';
    696       if ($MeetItem['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($MeetItem['Length'] / $MeetItem['Duration'] * 3600, '', 4, 'Decimal').'</td>';
     643      $Output .= '<td>'.$Item['Length'].'</td>';
     644      if ($Item['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Length'] / $Item['Duration'] * 3600, '', 4, 'Decimal').'</td>';
    697645        else $Output .= '<td>&nbsp;</td>';
    698646      $Output .= '</tr>';
    … …  
    701649    $Output .= $PageList['Output'];
    702650    $Output .= '</div>';
    703 
     651    return $Output;
     652  }
     653
     654  function ShowRunners()
     655  {
     656    $Output = '<div class="page-title">Runners</div>';
     657
     658    $Year = 0;
     659    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
     660      $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
     661    if ($Year == 0) $Year = $this->GetLatestYear();
     662
     663    $Output .= $this->YearList('/runners/', $Year);
     664
     665    $Where = 'Year='.$Year;
     666    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
     667    $DbRow = $DbResult->fetch_row();
     668    $PageList = GetPageList($DbRow[0]);
     669
     670    $Gender = array('', 'Man', 'Woman', 'Kid');
     671    $Output .= '<div id="list_content">';
     672    $Output .= $PageList['Output'];
     673    $TableColumns = array(
     674      array('Name' => 'Name', 'Title' => 'Name'),
     675      array('Name' => 'Gender', 'Title' => 'Category'),
     676      array('Name' => 'Distance', 'Title' => 'Distance'),
     677      array('Name' => 'Money', 'Title' => 'Money'),
     678      array('Name' => 'Rank', 'Title' => 'Rank'),
     679      array('Name' => 'Time', 'Title' => 'Last change'),
     680    );
     681    $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
     682    $Output .= '<table class="WideTable">';
     683    $Output .= $Order['Output'];
     684    $DbResult = $this->Database->select('Runner', '*, '.
     685      '(SELECT RunnerStat.Distance FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Distance'.
     686      ', (SELECT RunnerStat.Money FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Money'.
     687      ', (SELECT RunnerStat.Time FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Time'.
     688      ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
     689      $Where.$Order['SQL'].$PageList['SQLLimit']);
     690    while($Item = $DbResult->fetch_assoc())
     691    {
     692      $Output .= '<tr>'.
     693        '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
     694        '<td>'.$Gender[$Item['Gender']].'</td>'.
     695        '<td>'.$Item['Distance'].'</td>'.
     696        '<td>'.$Item['Money'].'</td>'.
     697        '<td>'.$Item['Rank'].'</td>'.
     698        '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
     699        '</tr>';
     700    }
     701    $Output .= '</table>';
     702    $Output .= $PageList['Output'];
     703    $Output .= '</div>';
     704
     705    return($Output);
     706  }
     707
     708  function ShowRunner()
     709  {
     710    $PrefixMultiplier = new PrefixMultiplier();
     711    $Output = '';
     712
     713    $RunnerId = 0;
     714    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
     715      $RunnerId = $this->PathItems[count($this->PathItems) - 1];
     716    if (!is_numeric($RunnerId)) die('Runner id needs to be numeric');
     717
     718    $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);
     719    $DbRow = $DbResult->fetch_assoc();
     720    $Output .= '<div class="page-title">Runner '.$DbRow['Name'].'</div>';
     721    if ($DbRow['TeamName'] != '')
     722      $Output .= '<div class="section-title"><a href="'.$this->Link('/team/'.$DbRow['TeamId']).'">'.$DbRow['TeamName'].'</a></div>';
     723
     724    $Output .= $this->ShowDetailed('Runner', $RunnerId);
     725    $Output .= $this->ShowDaily('Runner', $RunnerId);
    704726    return($Output);
    705727  }
    … …  
    779801    $this->Database->charset($this->Config['Database']['Charset']);
    780802    //$this->Database->ShowSQLError = true;
     803    //$this->Database->ShowSQLQuery = true;
    781804    $this->PathItems = $this->ProcessURL();
    782805
Note: See TracChangeset for help on using the changeset viewer.