Changeset 45 for trunk


Ignore:
Timestamp:
Oct 27, 2020, 11:08:29 AM (4 years ago)
Author:
chronos
Message:
  • Added: Allow to hide runners and teams.
Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed

  • trunk/Locale/cs.php

    r44 r45  
    7979        'millennium' => 'tisíciletí',
    8080        'Loaded items count' => 'Počet načtených položek',
     81        'Runner not found.' => 'Běžec nenalezen.',
     82        'Team not found' => 'Tým nenalezen.',
    8183      ),
    8284      'URL' => array(

  • trunk/Locale/en.php

    r44 r45  
    5151        'hour' => '',
    5252        'Loaded items count' => '',
     53        'Runner not found.' => '',
     54        'Team not found' => '',
    5355      ),
    5456      'URL' => array(

  • trunk/SQL/Upgrade.txt

    r41 r45  
    3434(2, 2019, 30, '1.5'),
    3535(3, 2020, 10, '0.0');
     36
     37-- 45
     38
     39ALTER TABLE `Runner` ADD `Hidden` INT NOT NULL DEFAULT '0' AFTER `Year`;
     40ALTER TABLE `Team` ADD `Hidden` INT NOT NULL DEFAULT '0' AFTER `Year`;

  • trunk/index.php

    r44 r45  
    176176        }
    177177        if (($JSON['last'] == 'true') or (count($JSON) == 0)) break;
    178         if ($I > 10) break; // Safe limit if last would not work
     178        if ($I > 30) break; // Safe limit if last would not work
    179179        $I++;
    180180      } else break;
    … …  
    185185  function ShowEmpty()
    186186  {
     187    $Output = '';
     188  /*
    187189    $this->Database->query('DELETE FROM RunnerStat');
    188190    $this->Database->query('DELETE FROM Runner');
    … …  
    190192    $this->Database->query('DELETE FROM Team');
    191193    $this->Database->query('DELETE FROM Import');
     194    */
     195
     196    // Find duplicates
     197    /*
     198    // ALTER TABLE `RunnerStat` ADD `Prev` INT NULL AFTER `Money`;
     199    // UPDATE RunnerStat AS A SET Prev = (SELECT Id FROM RunnerStat AS B WHERE A.Runner = B.Runner AND B.Id < A.Id ORDER BY Id DESC LIMIT 1)
     200    // SELECT * FROM `RunnerStat` AS A LEFT JOIN RunnerStat AS B ON B.ID=A.Prev WHERE A.Distance=B.Distance
     201
     202    $Table = 'Runner';
     203    $TableStat = $Table.'Stat';
     204    //$Queries = array();
     205    $Output .= 'DELETE FROM RunnerStat WHERE Id IN (';
     206    $DbResult = $this->Database->query('SELECT A.Id FROM `RunnerStat` AS A LEFT JOIN RunnerStat AS B ON B.ID=A.Prev WHERE A.Distance=B.Distance');
     207    while ($Item = $DbResult->fetch_assoc())
     208    {
     209      $Output .= $Item[Id].', ';
     210    }
     211    $Output .= ');<br/>';
     212
     213    */
     214
     215  /*
     216    // ALTER TABLE `TeamStat` ADD `Prev` INT NULL AFTER `Money`;
     217    // UPDATE TeamStat AS A SET Prev = (SELECT Id FROM TeamStat AS B WHERE A.Team = B.Team AND B.Id < A.Id ORDER BY Id DESC LIMIT 1)
     218    // SELECT * FROM `TeamStat` AS A LEFT JOIN TeamStat AS B ON B.ID=A.Prev WHERE A.Distance=B.Distance
     219
     220    $Table = 'Team';
     221    $TableStat = $Table.'Stat';
     222    //$Queries = array();
     223    $Output .= 'DELETE FROM TeamStat WHERE Id IN (';
     224    $DbResult = $this->Database->query('SELECT A.Id FROM `TeamStat` AS A LEFT JOIN TeamStat AS B ON B.ID=A.Prev WHERE A.Distance=B.Distance');
     225    while ($Item = $DbResult->fetch_assoc())
     226    {
     227      $Output .= $Item[Id].', ';
     228    }
     229    $Output .= ');<br/>';
     230    */
     231/*
     232    $I = 0;
     233    $DbResult2 = $this->Database->query('SELECT * FROM Runner WHERE Year=2020');
     234    while ($Runner = $DbResult2->fetch_assoc())
     235    {
     236      $Output .= $Runner['Id'].' '.$Runner['Name'].'<br/>';
     237      $Where = '('.$TableStat.'.Runner='.$Runner['Id'].')';
     238      $DbResult = $this->Database->query('SELECT *'.
     239        ', (SELECT '.$TableStat.'.Distance - B.Distance FROM '.$TableStat.' AS B WHERE (B.Id < '.$TableStat.'.Id) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Id DESC LIMIT 1) AS Length'.
     240        ' FROM '.$TableStat.
     241        ' WHERE '.$Where);
     242      while ($Item = $DbResult->fetch_assoc())
     243      {
     244        if (($Item['Length'] != null) and ($Item['Length'] == 0))
     245        {
     246          $Output .= ' '.$Item['Id'].' '.$Item['Time'].' '.$Item['Distance'].' '.$Item['Length'].'<br/>';
     247          $Queries[] = 'DELETE FROM RunnerStat WHERE Id='.$Item[Id];
     248        }
     249      }
     250      flush();
     251      $I++;
     252      if ($I > 500) break;
     253    }
     254    echo('START TRANSACTION;<br/>');
     255    foreach ($Queries as $Query)
     256    {
     257      echo($Query.";<br/>");
     258    }
     259    echo('COMMIT;<br/>');
     260*/
     261    return $Output;
    192262  }
    193263
    … …  
    328398
    329399        if ($Teams[$Item['GroupId']]['Distance'] != $Item['OverallDistance'])
    330         $Queries[] = $this->Database->GetInsert('TeamStat', array(
    331           'Time' => TimeToMysqlDateTime($Time),
    332           'Team' => $TeamId,
    333           'Distance' => $Item['OverallDistance'],
    334           'Rank' => $Item['Pos'],
    335           'Money' => $Item['Money'],
    336         ));
     400        {
     401          $Queries[] = $this->Database->GetInsert('TeamStat', array(
     402            'Time' => TimeToMysqlDateTime($Time),
     403            'Team' => $TeamId,
     404            'Distance' => $Item['OverallDistance'],
     405            'Rank' => $Item['Pos'],
     406            'Money' => $Item['Money'],
     407          ));
     408        }
    337409      } else
    338410      if ($Item['Type'] == '')
    … …  
    398470    $Year = $this->GetYear();
    399471
    400     $Where = '(Year='.$Year.') AND '.$Where;
     472    $Where .= ' AND (Year='.$Year.') AND (Hidden=0)';
    401473    if (array_key_exists('query', $_GET) and ($_GET['query'] != ''))
    402474    {
    … …  
    480552
    481553    $DbResult = $this->Database->query('SELECT Team.*, '.
    482       '(SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id) AS RunnerCount, '.
     554      '(SELECT COUNT(*) FROM Runner WHERE (Runner.Team=Team.Id) AND (Runner.Hidden=0)) AS RunnerCount, '.
    483555      '(SELECT TeamStat.Distance FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY TeamStat.Time DESC LIMIT 1) AS Distance, '.
    484556      '(SELECT TeamStat.Money FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Money, '.
    485557      '(SELECT TeamStat.Rank FROM TeamStat WHERE TeamStat.Team=Team.Id ORDER BY Time DESC LIMIT 1) AS Rank '.
    486       'FROM Team WHERE Id='.$TeamId);
    487     $DbRow = $DbResult->fetch_assoc();
    488     $this->Title = $Title.' '.$DbRow['Name'].' - '.$this->Title;
    489     $Output .= '<div class="page-title">'.$Title.' '.$DbRow['Name'].'</div>';
    490     $Output .= '<div class="section-title">'.$this->ItemsYearList('/'.$UrlDir.'/', $TeamId, 'Team', 'Name="'.$this->Database->real_escape_string($DbRow['Name']).'"').'</div>';
    491     $this->LoadYearParameters($DbRow['Year']);
     558      'FROM Team WHERE (Hidden=0) AND (Id='.$TeamId.')');
     559    if ($DbResult->num_rows > 0)
     560    {
     561      $DbRow = $DbResult->fetch_assoc();
     562      $this->Title = $Title.' '.$DbRow['Name'].' - '.$this->Title;
     563      $Output .= '<div class="page-title">'.$Title.' '.$DbRow['Name'].'</div>';
     564      $Output .= '<div class="section-title">'.$this->ItemsYearList('/'.$UrlDir.'/', $TeamId, 'Team', 'Name="'.$this->Database->real_escape_string($DbRow['Name']).'"').'</div>';
     565      $this->LoadYearParameters($DbRow['Year']);
     566      $Output .= '<div class="section-title">'.
     567        T('Runners').': '.$DbRow['RunnerCount'].', '.
     568        T('Distance').': '.$DbRow['Distance'].' km, '.
     569        T('Money').': '.$DbRow['Money'].' Kč, '.
     570        T('Rank').': '.$DbRow['Rank'].'</div>';
     571
     572      $Where = '(Hidden=0) AND (Team='.$TeamId.')';
     573
     574      // Show runners
     575      $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
     576      $DbRow = $DbResult->fetch_row();
     577      $PageList = GetPageList($DbRow[0]);
     578
     579      $Gender = array('', T('Man'), T('Woman'), T('Kid'));
     580      $Output .= '<div id="list_content">';
     581      $Output .= $PageList['Output'];
     582      $TableColumns = array(
     583        array('Name' => 'Name', 'Title' => T('Name')),
     584        array('Name' => 'Gender', 'Title' => T('Category')),
     585        array('Name' => 'Distance', 'Title' => T('Distance')),
     586        array('Name' => 'Money', 'Title' => T('Money')),
     587        array('Name' => 'Rank', 'Title' => T('Rank')),
     588        array('Name' => 'Time', 'Title' => T('Last change')),
     589      );
     590      $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
     591      $Output .= '<table class="WideTable">';
     592      $Output .= $Order['Output'];
     593      $DbResult = $this->Database->select('Runner', '*, '.
     594        '(SELECT RunnerStat.Distance FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Distance'.
     595        ', (SELECT RunnerStat.Money FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Money'.
     596        ', (SELECT RunnerStat.Time FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Time'.
     597        ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
     598        $Where.$Order['SQL'].$PageList['SQLLimit']);
     599      while ($Item = $DbResult->fetch_assoc())
     600      {
     601        $Output .= '<tr>'.
     602          '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
     603          '<td>'.$Gender[$Item['Gender']].'</td>'.
     604          '<td>'.$Item['Distance'].'</td>'.
     605          '<td>'.$Item['Money'].'</td>'.
     606          '<td>'.$Item['Rank'].'</td>'.
     607          '<td>'.$this->RunningState(MysqlDateTimeToTime($Item['Time'])).'</td>'.
     608          '</tr>';
     609      }
     610      $Output .= '</table>';
     611      $Output .= $PageList['Output'];
     612      $Output .= '</div><br/>';
     613
     614      $Output .= $this->ShowDetailed('Team', $TeamId);
     615      //$Output .= $this->ShowDetailedChart('Team', $TeamId);
     616      $Output .= $this->ShowDaily('Team', $TeamId);
     617      //$Output .= $this->ShowDailyChart('Team', $TeamId);
     618    } else $Output .= T('Team not found.');
     619    return $Output;
     620  }
     621
     622  function ShowDetailed($Table, $Id)
     623  {
     624    $PrefixMultiplier = new PrefixMultiplier();
     625
     626    $TableStat = $Table.'Stat';
     627    $Output = '<div class="section-title">'.T('Lap progress').'</div>';
     628    $Where = $TableStat.'.'.$Table.'='.$Id;
     629    $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$TableStat.' WHERE '.$Where);
     630    $DbRow = $DbResult->fetch_row();
     631    $PageList = GetPageList($DbRow[0]);
     632
     633    $Output .= '<div id="list_content">';
     634    $Output .= $PageList['Output'];
     635    $TableColumns = array(
     636      array('Name' => 'Time', 'Title' => T('Time')),
     637      array('Name' => 'Distance', 'Title' => T('Distance').' [km]'),
     638      array('Name' => 'Money', 'Title' => T('Money').' [Kč]'),
     639      array('Name' => 'Rank', 'Title' => T('Rank')),
     640      array('Name' => 'Duration', 'Title' => T('Duration')),
     641      array('Name' => 'Length', 'Title' => T('Length').' [km]'),
     642      array('Name' => 'Speed', 'Title' => T('Speed').' [km/'.T('hour').']'),
     643    );
     644    $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
     645    $Output .= '<table class="WideTable">';
     646    $Output .= $Order['Output'];
     647    $DbResult = $this->Database->query('SELECT *'.
     648      ', (SELECT '.$TableStat.'.Distance - B.Distance FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Length'.
     649      ', (SELECT TIME_TO_SEC(TIMEDIFF('.$TableStat.'.Time, B.Time)) FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Duration'.
     650      ', NULL AS Speed'.
     651      ' FROM '.$TableStat.
     652      ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
     653    while ($Item = $DbResult->fetch_assoc())
     654    {
     655      $Output .= '<tr>'.
     656        '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
     657        '<td>'.$Item['Distance'].'</td>'.
     658        '<td>'.$Item['Money'].'</td>'.
     659        '<td>'.$Item['Rank'].'</td>';
     660      if (($Item['Duration'] != null) and ($Item['Duration'] < $Item['Length'] / $this->MinRunnerSpeed * 3600)) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
     661        else $Output .= '<td>&nbsp;</td>';
     662      $Output .= '<td>'.$Item['Length'].'</td>';
     663
     664      if (($Item['Duration'] > 0) and ($Item['Duration'] < $Item['Length'] * 3600 / $this->MinRunnerSpeed))
     665      {
     666        $Speed = $Item['Length'] / $Item['Duration'] * 3600;
     667        $Output .= '<td>'.$PrefixMultiplier->Add($Speed, '', 4, 'Decimal').'</td>';
     668      } else $Output .= '<td>&nbsp;</td>';
     669      $Output .= '</tr>';
     670    }
     671    $Output .= '</table>';
     672    $Output .= $PageList['Output'];
     673    $Output .= '</div><br/>';
     674    return $Output;
     675  }
     676
     677  function ShowDetailedChart($Table, $Id)
     678  {
     679    $TableStat = $Table.'Stat';
     680    $DbResult = $this->Database->query('SELECT *'.
     681      ', (SELECT '.$TableStat.'.Distance - B.Distance FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Length'.
     682      ', (SELECT TIME_TO_SEC(TIMEDIFF('.$TableStat.'.Time, B.Time)) FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Duration'.
     683      ', NULL AS Speed'.
     684      ' FROM '.$TableStat.
     685      ' WHERE '.$TableStat.'.'.$Table.'='.$Id.' ORDER BY Time');
     686    $ChartValues = array();
     687    while ($Item = $DbResult->fetch_assoc())
     688    {
     689      $ChartValues[MysqlDateTimeToTime($Item['Time'])] = $Item['Distance'];
     690    }
     691    $Output = $this->ShowChart($Table.'Detailed', $ChartValues, T('Lap progress'));
     692    return $Output;
     693  }
     694
     695  function ShowDaily($Table, $Id)
     696  {
     697    $PrefixMultiplier = new PrefixMultiplier();
     698    $Where = '1';
     699
     700    $TableStat = $Table.'Stat';
     701    $DailyTableMaxId = 'SELECT * FROM (SELECT MAX(Id) AS MaxId FROM '.$TableStat.' AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$TableStat.' AS T3 ON T3.Id=T2.MaxId';
     702    $DailyTableMinId = 'SELECT * FROM (SELECT MIN(Id) AS MinId FROM '.$TableStat.' AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$TableStat.' AS T3 ON T3.Id=T2.MinId';
     703    $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$DailyTableMaxId.') AS B');
     704    $DbRow = $DbResult->fetch_row();
     705    $PageList = GetPageList($DbRow[0]);
     706
     707    $Output = '<div class="section-title">'.T('Daily progress').'</div>';
     708    $Output .= '<div id="list_content">';
     709    $Output .= $PageList['Output'];
     710    $TableColumns = array(
     711      array('Name' => 'Time', 'Title' => T('Time')),
     712      array('Name' => 'Distance', 'Title' => T('Distance').' [km]'),
     713      array('Name' => 'Money', 'Title' => T('Money').' [Kč]'),
     714      array('Name' => 'Rank', 'Title' => T('Rank')),
     715      array('Name' => 'Duration', 'Title' => T('Duration')),
     716      array('Name' => 'Length', 'Title' => T('Length').' [km]'),
     717      array('Name' => 'Speed', 'Title' => T('Speed').' [km/'.T('hour').']'),
     718    );
     719    $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
     720    $Output .= '<table class="WideTable">';
     721    $Output .= $Order['Output'];
     722
     723    if ($this->LapLength == 0)
     724    {
     725      $DbResult = $this->Database->query('SELECT * '.
     726        ', COALESCE((SELECT T4.Distance - B.Distance FROM ('.$DailyTableMaxId.') AS B WHERE (DATE(B.Time) < DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1), T4.Distance) AS Length'.
     727        ', NULL AS Duration'.
     728        ', NULL AS Speed'.
     729        ' FROM ('.$DailyTableMaxId.') AS T4'.
     730        ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
     731    } else
     732    {
     733      $DbResult = $this->Database->query('SELECT * '.
     734        ', COALESCE((SELECT T4.Distance - B.Distance FROM ('.$DailyTableMaxId.') AS B WHERE (DATE(B.Time) < DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1), T4.Distance) AS Length'.
     735        //', (SELECT COUNT(*) * '.$this->LapLength.' FROM '.$TableStat.' AS B WHERE (DATE(B.Time) = DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.')) AS LapLength'.
     736        ', (SELECT TIME_TO_SEC(TIMEDIFF(T4.Time, B.Time)) / (Length - '.$this->LapLength.') * 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'.
     737        ', NULL AS Speed'.
     738        ' FROM ('.$DailyTableMaxId.') AS T4'.
     739        ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
     740    }
     741    while ($Item = $DbResult->fetch_assoc())
     742    {
     743      $Output .= '<tr>'.
     744        '<td>'.HumanDate(MysqlDateTimeToTime($Item['Time'])).'</td>'.
     745        '<td>'.$Item['Distance'].'</td>'.
     746        '<td>'.$Item['Money'].'</td>'.
     747        '<td>'.$Item['Rank'].'</td>';
     748      if ($Item['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
     749        else $Output .= '<td>&nbsp;</td>';
     750      $Output .= '<td>'.$Item['Length'].'</td>';
     751      if ($Item['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Length'] / $Item['Duration'] * 3600, '', 4, 'Decimal').'</td>';
     752        else $Output .= '<td>&nbsp;</td>';
     753      $Output .= '</tr>';
     754    }
     755    $Output .= '</table>';
     756    $Output .= $PageList['Output'];
     757    $Output .= '</div>';
     758    return $Output;
     759  }
     760
     761  function ShowDailyChart($Table, $Id)
     762  {
     763    $TableStat = $Table.'Stat';
     764    $DailyTableMaxId = 'SELECT * FROM (SELECT MAX(Id) AS MaxId FROM '.$TableStat.' AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$TableStat.' AS T3 ON T3.Id=T2.MaxId';
     765    $DbResult = $this->Database->query('SELECT * '.
     766    ', COALESCE((SELECT T4.Distance - B.Distance FROM ('.$DailyTableMaxId.') AS B WHERE (DATE(B.Time) < DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1), T4.Distance) AS Length'.
     767    ' FROM ('.$DailyTableMaxId.') AS T4'.
     768    ' ORDER BY Time');
     769    $ChartValues = array();
     770    while ($Item = $DbResult->fetch_assoc())
     771    {
     772      $ChartValues[MysqlDateTimeToTime($Item['Time'])] = $Item['Length'];
     773    }
     774    $Output = $this->ShowChart($Table.'Daily', $ChartValues, T('Daily progress'));
     775    return $Output;
     776  }
     777
     778  function ShowSearch()
     779  {
     780    if (array_key_exists('query', $_GET)) $Query = $_GET['query'];
     781      else $Query = '';
     782    $Output = '<form action="?" method="get" style="display: inline;">';
     783    $Output .= '<input type="text" size="10" name="query" value="'.$Query.'"/> '.
     784      '<input type="submit" value="'.T('Search').'"/>';
     785    $Output .= '</form>';
     786    return $Output;
     787  }
     788
     789  function ShowRunnersAll()
     790  {
     791    return $this->ShowRunners();
     792  }
     793
     794  function ShowRunnersMen()
     795  {
     796    return $this->ShowRunners('(Gender=1)');
     797  }
     798
     799  function ShowRunnersWomen()
     800  {
     801    return $this->ShowRunners('(Gender=2)');
     802  }
     803
     804  function ShowRunnersKids()
     805  {
     806    return $this->ShowRunners('(Gender=3)');
     807  }
     808
     809  function ShowRunners($Where = '1')
     810  {
     811    $this->Title = T('Runners').' - '.$this->Title;
     812    $Output = '<div class="page-title">'.T('Runners').'</div>';
    492813    $Output .= '<div class="section-title">'.
    493       T('Runners').': '.$DbRow['RunnerCount'].', '.
    494       T('Distance').': '.$DbRow['Distance'].' km, '.
    495       T('Money').': '.$DbRow['Money'].' Kč, '.
    496       T('Rank').': '.$DbRow['Rank'].'</div>';
    497 
    498     $Where = 'Team='.$TeamId;
    499 
    500     // Show runners
     814      '<a href="'.$this->Link('/runners/').'">'.T('All').'</a> '.
     815      '<a href="'.$this->Link('/men/').'">'.T('Men').'</a> '.
     816      '<a href="'.$this->Link('/women/').'">'.T('Women').'</a> '.
     817      '<a href="'.$this->Link('/kids/').'">'.T('Kids').'</a>'.
     818      '</div>';
     819    $Year = $this->GetYear();
     820
     821    $Output .= '<div class="section-title">'.$this->YearList('/runners/', $Year, 'Runner').' '.T('Name').': '.$this->ShowSearch().'</div>';
     822    $Where .= ' AND (Year='.$Year.') AND (Hidden=0)';
     823    if (array_key_exists('query', $_GET) and ($_GET['query'] != ''))
     824    {
     825      $Where .= ' AND (Name LIKE "%'.addslashes($_GET['query']).'%")';
     826    }
     827
    501828    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
    502829    $DbRow = $DbResult->fetch_row();
    … …  
    536863    $Output .= '</table>';
    537864    $Output .= $PageList['Output'];
    538     $Output .= '</div><br/>';
    539 
    540     $Output .= $this->ShowDetailed('Team', $TeamId);
    541     //$Output .= $this->ShowDetailedChart('Team', $TeamId);
    542     $Output .= $this->ShowDaily('Team', $TeamId);
    543     //$Output .= $this->ShowDailyChart('Team', $TeamId);
    544 
    545     return $Output;
    546   }
    547 
    548   function ShowDetailed($Table, $Id)
    549   {
    550     $PrefixMultiplier = new PrefixMultiplier();
    551 
    552     $TableStat = $Table.'Stat';
    553     $Output = '<div class="section-title">'.T('Lap progress').'</div>';
    554     $Where = $TableStat.'.'.$Table.'='.$Id;
    555     $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$TableStat.' WHERE '.$Where);
    556     $DbRow = $DbResult->fetch_row();
    557     $PageList = GetPageList($DbRow[0]);
    558 
    559     $Output .= '<div id="list_content">';
    560     $Output .= $PageList['Output'];
    561     $TableColumns = array(
    562       array('Name' => 'Time', 'Title' => T('Time')),
    563       array('Name' => 'Distance', 'Title' => T('Distance').' [km]'),
    564       array('Name' => 'Money', 'Title' => T('Money').' [Kč]'),
    565       array('Name' => 'Rank', 'Title' => T('Rank')),
    566       array('Name' => 'Duration', 'Title' => T('Duration')),
    567       array('Name' => 'Length', 'Title' => T('Length').' [km]'),
    568       array('Name' => 'Speed', 'Title' => T('Speed').' [km/'.T('hour').']'),
    569     );
    570     $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
    571     $Output .= '<table class="WideTable">';
    572     $Output .= $Order['Output'];
    573     $DbResult = $this->Database->query('SELECT *'.
    574       ', (SELECT '.$TableStat.'.Distance - B.Distance FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Length'.
    575       ', (SELECT TIME_TO_SEC(TIMEDIFF('.$TableStat.'.Time, B.Time)) FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Duration'.
    576       ', NULL AS Speed'.
    577       ' FROM '.$TableStat.
    578       ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
    579     while ($Item = $DbResult->fetch_assoc())
    580     {
    581       $Output .= '<tr>'.
    582         '<td>'.HumanDateTime(MysqlDateTimeToTime($Item['Time'])).'</td>'.
    583         '<td>'.$Item['Distance'].'</td>'.
    584         '<td>'.$Item['Money'].'</td>'.
    585         '<td>'.$Item['Rank'].'</td>';
    586       if (($Item['Duration'] != null) and ($Item['Duration'] < $Item['Length'] / $this->MinRunnerSpeed * 3600)) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
    587         else $Output .= '<td>&nbsp;</td>';
    588       $Output .= '<td>'.$Item['Length'].'</td>';
    589 
    590       if (($Item['Duration'] > 0) and ($Item['Duration'] < $Item['Length'] * 3600 / $this->MinRunnerSpeed))
    591       {
    592         $Speed = $Item['Length'] / $Item['Duration'] * 3600;
    593         $Output .= '<td>'.$PrefixMultiplier->Add($Speed, '', 4, 'Decimal').'</td>';
    594       } else $Output .= '<td>&nbsp;</td>';
    595       $Output .= '</tr>';
    596     }
    597     $Output .= '</table>';
    598     $Output .= $PageList['Output'];
    599     $Output .= '</div><br/>';
    600     return $Output;
    601   }
    602 
    603   function ShowDetailedChart($Table, $Id)
    604   {
    605     $TableStat = $Table.'Stat';
    606     $DbResult = $this->Database->query('SELECT *'.
    607       ', (SELECT '.$TableStat.'.Distance - B.Distance FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Length'.
    608       ', (SELECT TIME_TO_SEC(TIMEDIFF('.$TableStat.'.Time, B.Time)) FROM '.$TableStat.' AS B WHERE (B.Time < '.$TableStat.'.Time) AND (B.'.$Table.' = '.$TableStat.'.'.$Table.') ORDER BY B.Time DESC LIMIT 1) AS Duration'.
    609       ', NULL AS Speed'.
    610       ' FROM '.$TableStat.
    611       ' WHERE '.$TableStat.'.'.$Table.'='.$Id.' ORDER BY Time');
    612     $ChartValues = array();
    613     while ($Item = $DbResult->fetch_assoc())
    614     {
    615       $ChartValues[MysqlDateTimeToTime($Item['Time'])] = $Item['Distance'];
    616     }
    617     $Output = $this->ShowChart($Table.'Detailed', $ChartValues, T('Lap progress'));
    618     return $Output;
    619   }
    620 
    621   function ShowDaily($Table, $Id)
    622   {
    623     $PrefixMultiplier = new PrefixMultiplier();
    624     $Where = '1';
    625 
    626     $TableStat = $Table.'Stat';
    627     $DailyTableMaxId = 'SELECT * FROM (SELECT MAX(Id) AS MaxId FROM '.$TableStat.' AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$TableStat.' AS T3 ON T3.Id=T2.MaxId';
    628     $DailyTableMinId = 'SELECT * FROM (SELECT MIN(Id) AS MinId FROM '.$TableStat.' AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$TableStat.' AS T3 ON T3.Id=T2.MinId';
    629     $DbResult = $this->Database->query('SELECT COUNT(*) FROM ('.$DailyTableMaxId.') AS B');
    630     $DbRow = $DbResult->fetch_row();
    631     $PageList = GetPageList($DbRow[0]);
    632 
    633     $Output = '<div class="section-title">'.T('Daily progress').'</div>';
    634     $Output .= '<div id="list_content">';
    635     $Output .= $PageList['Output'];
    636     $TableColumns = array(
    637       array('Name' => 'Time', 'Title' => T('Time')),
    638       array('Name' => 'Distance', 'Title' => T('Distance').' [km]'),
    639       array('Name' => 'Money', 'Title' => T('Money').' [Kč]'),
    640       array('Name' => 'Rank', 'Title' => T('Rank')),
    641       array('Name' => 'Duration', 'Title' => T('Duration')),
    642       array('Name' => 'Length', 'Title' => T('Length').' [km]'),
    643       array('Name' => 'Speed', 'Title' => T('Speed').' [km/'.T('hour').']'),
    644     );
    645     $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
    646     $Output .= '<table class="WideTable">';
    647     $Output .= $Order['Output'];
    648 
    649     if ($this->LapLength == 0)
    650     {
    651       $DbResult = $this->Database->query('SELECT * '.
    652         ', COALESCE((SELECT T4.Distance - B.Distance FROM ('.$DailyTableMaxId.') AS B WHERE (DATE(B.Time) < DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1), T4.Distance) AS Length'.
    653         ', NULL AS Duration'.
    654         ', NULL AS Speed'.
    655         ' FROM ('.$DailyTableMaxId.') AS T4'.
    656         ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
    657     } else
    658     {
    659       $DbResult = $this->Database->query('SELECT * '.
    660         ', COALESCE((SELECT T4.Distance - B.Distance FROM ('.$DailyTableMaxId.') AS B WHERE (DATE(B.Time) < DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1), T4.Distance) AS Length'.
    661         //', (SELECT COUNT(*) * '.$this->LapLength.' FROM '.$TableStat.' AS B WHERE (DATE(B.Time) = DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.')) AS LapLength'.
    662         ', (SELECT TIME_TO_SEC(TIMEDIFF(T4.Time, B.Time)) / (Length - '.$this->LapLength.') * 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'.
    663         ', NULL AS Speed'.
    664         ' FROM ('.$DailyTableMaxId.') AS T4'.
    665         ' WHERE '.$Where.$Order['SQL'].$PageList['SQLLimit']);
    666     }
    667     while ($Item = $DbResult->fetch_assoc())
    668     {
    669       $Output .= '<tr>'.
    670         '<td>'.HumanDate(MysqlDateTimeToTime($Item['Time'])).'</td>'.
    671         '<td>'.$Item['Distance'].'</td>'.
    672         '<td>'.$Item['Money'].'</td>'.
    673         '<td>'.$Item['Rank'].'</td>';
    674       if ($Item['Duration'] != null) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Duration'], '', 4, 'Time').'</td>';
    675         else $Output .= '<td>&nbsp;</td>';
    676       $Output .= '<td>'.$Item['Length'].'</td>';
    677       if ($Item['Duration'] > 0) $Output .= '<td>'.$PrefixMultiplier->Add($Item['Length'] / $Item['Duration'] * 3600, '', 4, 'Decimal').'</td>';
    678         else $Output .= '<td>&nbsp;</td>';
    679       $Output .= '</tr>';
    680     }
    681     $Output .= '</table>';
    682     $Output .= $PageList['Output'];
    683     $Output .= '</div>';
    684     return $Output;
    685   }
    686 
    687   function ShowDailyChart($Table, $Id)
    688   {
    689     $TableStat = $Table.'Stat';
    690     $DailyTableMaxId = 'SELECT * FROM (SELECT MAX(Id) AS MaxId FROM '.$TableStat.' AS T1 WHERE T1.'.$Table.'='.$Id.' GROUP BY DATE(Time)) AS T2 LEFT JOIN '.$TableStat.' AS T3 ON T3.Id=T2.MaxId';
    691     $DbResult = $this->Database->query('SELECT * '.
    692     ', COALESCE((SELECT T4.Distance - B.Distance FROM ('.$DailyTableMaxId.') AS B WHERE (DATE(B.Time) < DATE(T4.Time)) AND (B.'.$Table.' = T4.'.$Table.') ORDER BY B.Time DESC LIMIT 1), T4.Distance) AS Length'.
    693     ' FROM ('.$DailyTableMaxId.') AS T4'.
    694     ' ORDER BY Time');
    695     $ChartValues = array();
    696     while ($Item = $DbResult->fetch_assoc())
    697     {
    698       $ChartValues[MysqlDateTimeToTime($Item['Time'])] = $Item['Length'];
    699     }
    700     $Output = $this->ShowChart($Table.'Daily', $ChartValues, T('Daily progress'));
    701     return $Output;
    702   }
    703 
    704   function ShowSearch()
    705   {
    706     if (array_key_exists('query', $_GET)) $Query = $_GET['query'];
    707       else $Query = '';
    708     $Output = '<form action="?" method="get" style="display: inline;">';
    709     $Output .= '<input type="text" size="10" name="query" value="'.$Query.'"/> '.
    710       '<input type="submit" value="'.T('Search').'"/>';
    711     $Output .= '</form>';
    712     return $Output;
    713   }
    714 
    715   function ShowRunnersAll()
    716   {
    717     return $this->ShowRunners();
    718   }
    719 
    720   function ShowRunnersMen()
    721   {
    722     return $this->ShowRunners('(Gender=1)');
    723   }
    724 
    725   function ShowRunnersWomen()
    726   {
    727     return $this->ShowRunners('(Gender=2)');
    728   }
    729 
    730   function ShowRunnersKids()
    731   {
    732     return $this->ShowRunners('(Gender=3)');
    733   }
    734 
    735   function ShowRunners($Where = '1')
    736   {
    737     $this->Title = T('Runners').' - '.$this->Title;
    738     $Output = '<div class="page-title">'.T('Runners').'</div>';
    739     $Output .= '<div class="section-title">'.
    740       '<a href="'.$this->Link('/runners/').'">'.T('All').'</a> '.
    741       '<a href="'.$this->Link('/men/').'">'.T('Men').'</a> '.
    742       '<a href="'.$this->Link('/women/').'">'.T('Women').'</a> '.
    743       '<a href="'.$this->Link('/kids/').'">'.T('Kids').'</a>'.
    744       '</div>';
    745     $Year = $this->GetYear();
    746 
    747     $Output .= '<div class="section-title">'.$this->YearList('/runners/', $Year, 'Runner').' '.T('Name').': '.$this->ShowSearch().'</div>';
    748     $Where .= ' AND (Year='.$Year.')';
    749     if (array_key_exists('query', $_GET) and ($_GET['query'] != ''))
    750     {
    751       $Where .= ' AND (Name LIKE "%'.addslashes($_GET['query']).'%")';
    752     }
    753 
    754     $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
    755     $DbRow = $DbResult->fetch_row();
    756     $PageList = GetPageList($DbRow[0]);
    757 
    758     $Gender = array('', T('Man'), T('Woman'), T('Kid'));
    759     $Output .= '<div id="list_content">';
    760     $Output .= $PageList['Output'];
    761     $TableColumns = array(
    762       array('Name' => 'Name', 'Title' => T('Name')),
    763       array('Name' => 'Gender', 'Title' => T('Category')),
    764       array('Name' => 'Distance', 'Title' => T('Distance')),
    765       array('Name' => 'Money', 'Title' => T('Money')),
    766       array('Name' => 'Rank', 'Title' => T('Rank')),
    767       array('Name' => 'Time', 'Title' => T('Last change')),
    768     );
    769     $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
    770     $Output .= '<table class="WideTable">';
    771     $Output .= $Order['Output'];
    772     $DbResult = $this->Database->select('Runner', '*, '.
    773       '(SELECT RunnerStat.Distance FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Distance'.
    774       ', (SELECT RunnerStat.Money FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Money'.
    775       ', (SELECT RunnerStat.Time FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Time'.
    776       ', (SELECT RunnerStat.Rank FROM RunnerStat WHERE RunnerStat.Runner=Runner.Id ORDER BY Time DESC LIMIT 1) AS Rank',
    777       $Where.$Order['SQL'].$PageList['SQLLimit']);
    778     while ($Item = $DbResult->fetch_assoc())
    779     {
    780       $Output .= '<tr>'.
    781         '<td><a href="'.$this->Link('/runner/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
    782         '<td>'.$Gender[$Item['Gender']].'</td>'.
    783         '<td>'.$Item['Distance'].'</td>'.
    784         '<td>'.$Item['Money'].'</td>'.
    785         '<td>'.$Item['Rank'].'</td>'.
    786         '<td>'.$this->RunningState(MysqlDateTimeToTime($Item['Time'])).'</td>'.
    787         '</tr>';
    788     }
    789     $Output .= '</table>';
    790     $Output .= $PageList['Output'];
    791865    $Output .= '</div>';
    792866
    … …  
    804878    if (!is_numeric($RunnerId)) die(T('Runner id needs to be numeric'));
    805879
    806     $DbResult = $this->Database->query('SELECT Runner.Name, Team.Name AS TeamName, Team.Id AS TeamId, Runner.Year FROM Runner LEFT JOIN Team ON Team.Id=Runner.Team WHERE Runner.Id='.$RunnerId);
    807     $DbRow = $DbResult->fetch_assoc();
    808     $this->Title = T('Runner').' '.$DbRow['Name'].' - '.$this->Title;
    809     $Output .= '<div class="page-title">'.T('Runner').' '.$DbRow['Name'].'</div>';
    810     if ($DbRow['TeamName'] != '')
    811       $Output .= '<div class="section-title"><a href="'.$this->Link('/team/'.$DbRow['TeamId']).'">'.$DbRow['TeamName'].'</a></div>';
    812     $Output .= '<div class="section-title">'.$this->ItemsYearList('/runner/', $RunnerId, 'Runner', 'Name="'.$this->Database->real_escape_string($DbRow['Name']).'"').'</div>';
    813     $this->LoadYearParameters($DbRow['Year']);
    814 
    815     $Output .= $this->ShowDetailed('Runner', $RunnerId);
    816     //$Output .= $this->ShowDetailedChart('Runner', $RunnerId);
    817     $Output .= $this->ShowDaily('Runner', $RunnerId);
    818     //$Output .= $this->ShowDailyChart('Runner', $RunnerId);
     880    $DbResult = $this->Database->query('SELECT Runner.Name, Team.Name AS TeamName, Team.Id AS TeamId, Runner.Year FROM Runner '.
     881      'LEFT JOIN Team ON Team.Id=Runner.Team WHERE (Runner.Hidden=0) AND (Runner.Id='.$RunnerId.')');
     882    if ($DbResult->num_rows > 0)
     883    {
     884      $DbRow = $DbResult->fetch_assoc();
     885      $this->Title = T('Runner').' '.$DbRow['Name'].' - '.$this->Title;
     886      $Output .= '<div class="page-title">'.T('Runner').' '.$DbRow['Name'].'</div>';
     887      if ($DbRow['TeamName'] != '')
     888        $Output .= '<div class="section-title"><a href="'.$this->Link('/team/'.$DbRow['TeamId']).'">'.$DbRow['TeamName'].'</a></div>';
     889      $Output .= '<div class="section-title">'.$this->ItemsYearList('/runner/', $RunnerId, 'Runner', 'Name="'.$this->Database->real_escape_string($DbRow['Name']).'"').'</div>';
     890      $this->LoadYearParameters($DbRow['Year']);
     891
     892      $Output .= $this->ShowDetailed('Runner', $RunnerId);
     893      //$Output .= $this->ShowDetailedChart('Runner', $RunnerId);
     894      $Output .= $this->ShowDaily('Runner', $RunnerId);
     895      //$Output .= $this->ShowDailyChart('Runner', $RunnerId);
     896    } else $Output .= T('Runner not found.');
    819897    return $Output;
    820898  }
    … …  
    11381216      $Item = $this->LocaleManager->CurrentLocale->Texts->TranslateReverse($Item, 'URL');
    11391217      if ($Item == 'sync') $Output .= $this->ShowSync();
    1140       //else if ($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
     1218      //else if ($Item == 'empty') $Output .= $this->ShowEmpty();
    11411219      else if ($Item == 'runner') $Output .= $this->ShowRunner();
    11421220      else if ($Item == 'runners') $Output .= $this->ShowRunnersAll();
Note: See TracChangeset for help on using the changeset viewer.