Changeset 15 for trunk/index.php


Ignore:
Timestamp:
Sep 5, 2019, 9:47:21 AM (5 years ago)
Author:
chronos
Message:
  • Added: Support for storing data for multiple years.
  • Modified: Improved data synchronization to sync data only if they were changed. Checked with hash function agains previous stored value.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/index.php

    r14 r15  
    2828  var $Database;
    2929  var $Config;
     30  var $Year;
    3031
    3132  function __construct()
    3233  {
     34  }
     35
     36  function GetLatestYear()
     37  {
     38    $Year = 0;
     39    $DbResult = $this->Database->query('SELECT DISTINCT(Year) AS Year FROM `Runner` ORDER BY Year DESC');
     40    if ($DbResult->num_rows > 0)
     41    {
     42      $DbRow = $DbResult->fetch_assoc();
     43      $Year = $DbRow['Year'];
     44    }
     45    return $Year;
     46  }
     47
     48  function YearList($Path, $SelectedYear)
     49  {
     50    $Output = '<div class="year-list">Year: ';
     51    $DbResult = $this->Database->query('SELECT DISTINCT(Year) AS Year FROM `Runner` ORDER BY Year ASC');
     52    while ($DbRow = $DbResult->fetch_assoc())
     53    {
     54      $Year = $DbRow['Year'];
     55      $Item = '<a href="'.$this->Link($Path.$Year.'/').'">'.$Year.'</a>';
     56      if ($SelectedYear == $Year) $Item = '<strong>'.$Item.'</strong>';
     57      $Output .= $Item.' ';
     58    }
     59    $Output .= '</div>';
     60    return $Output;
    3361  }
    3462
    … …  
    3765    $Output = '<div>'.
    3866      '<a href="'.$this->Link('/').'">Summary</a> '.
    39       '<a href="'.$this->Link('/runners').'">Runners</a> '.
    40       '<a href="'.$this->Link('/teams').'">Teams</a> '.
    41       '<a href="'.$this->Link('/families').'">Families</a> '.     
     67      '<a href="'.$this->Link('/runners/').'">Runners</a> '.
     68      '<a href="'.$this->Link('/teams/').'">Teams</a> '.
     69      '<a href="'.$this->Link('/families/').'">Families</a> '.
    4270      '</div>';
    4371    return($Output);
    … …  
    6593  function QueryRunners($Category, $Page = 0, $TeamId = null, $Query = null, $StartNumber = null, $Count = null)
    6694  {
    67     $URL = 'http://leaderboard.teribear.cz/Home/GetRunners?category='.$Category;
    68     if ($Page != 0) $URL .= '&page='.$Page;
    69     if ($TeamId != null) $URL .= '&teamId='.$TeamId;
    70     if ($Count != null) $URL .= '&count='.$Count;
    71     if ($Query != null) $URL .= '&query='.$Query;
    72     if ($StartNumber != null) $URL .= '&startNumber='.$StartNumber;
    73     $Content = file_get_contents($URL);
    74     $JSON = json_decode($Content, true);
    75     //print_r($JSON);
    76     return $JSON;
     95      $URL = 'http://leaderboard.teribear.cz/Home/GetRunners?category='.$Category;
     96      if ($Page != 0) $URL .= '&page='.$Page;
     97      if ($TeamId != null) $URL .= '&teamId='.$TeamId;
     98      if ($Count != null) $URL .= '&count='.$Count;
     99      if ($Query != null) $URL .= '&query='.$Query;
     100      if ($StartNumber != null) $URL .= '&startNumber='.$StartNumber;
     101      $Content = file_get_contents($URL);
     102      $JSON = json_decode($Content, true);
     103      //print_r($JSON);
     104      return $JSON;
     105  }
     106
     107  function QueryRunnersAll($Category = 'all')
     108  {
     109    $ItemsPerPage = 30;
     110    $MaxCount = 450 * 30;
     111    $Result = array();
     112    $I = 0;
     113    while (true)
     114    {
     115      $Page = $I * $MaxCount / $ItemsPerPage;
     116      $JSON = $this->QueryRunners($Category, $Page, null, null, null, $MaxCount);
     117      foreach ($JSON['items'] as $Item)
     118      {
     119        $Result[] = $Item;
     120      }
     121      if ($JSON['last'] == 'true') break;
     122      if ($I > 10) break; // Safe limit if last would not work
     123      $I++;
     124    }
     125    return $Result;
    77126  }
    78127
    … …  
    83132    $this->Database->query('DELETE FROM TeamStat');
    84133    $this->Database->query('DELETE FROM Team');
    85   }
    86 
    87   function ShowSync() 
    88   {
    89     $ItemsPerPage = 30;
    90     $MaxCount = 450 * 30;
     134    $this->Database->query('DELETE FROM Import');
     135  }
     136
     137  function Sync($Items, $Time)
     138  {
     139    $Year = date("Y", $Time);
    91140
    92141    // Load all runners
    … …  
    97146    $Runners = array();
    98147    $DbResult = $this->Database->query('SELECT Runner.Id, Runner.ChipNumber, '.
    99       '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Distance DESC LIMIT 1) AS Distance FROM Runner');
     148      '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Distance DESC LIMIT 1) AS Distance '.
     149      'FROM Runner WHERE Year='.$Year);
    100150    while ($DbRow = $DbResult->fetch_assoc())
    101151    {
    … …  
    110160    $Teams = array();
    111161    $DbResult = $this->Database->query('SELECT Team.Id, Team.WebId, '.
    112       '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Distance DESC LIMIT 1) AS Distance FROM Team');
     162      '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Distance DESC LIMIT 1) AS Distance '.
     163      'FROM Team WHERE Year='.$Year);
    113164    while ($DbRow = $DbResult->fetch_assoc())
    114165    {
    … …  
    116167    }
    117168
     169    $Queries = array();
     170    foreach ($Items as $Item)
     171    {
     172      if (($Item['Type'] == 'child') or ($Item['Type'] == 'woman') or ($Item['Type'] == 'man'))
     173      {
     174        if (!array_key_exists($Item['ChipNumber'], $Runners))
     175        {
     176          if ($Item['TeamId'] == null)
     177          {
     178            $TeamId = null;
     179          } else
     180          {
     181          if (!array_key_exists($Item['TeamId'], $Teams))
     182          {
     183            $TeamId = $NextTeamId;
     184            $Queries[] = $this->Database->GetInsert('Team', array(
     185              'Id' => $TeamId,
     186              'Name' => '',
     187              'WebId' => $Item['TeamId'],
     188              'Year' => $Year,
     189            ));
     190            $Teams[$Item['TeamId']] = array('Id' => $TeamId, 'Distance' => -1);
     191            $NextTeamId++;
     192          } else
     193            $TeamId = $Teams[$Item['TeamId']]['Id'];
     194          }
     195
     196          $Gender = 0;
     197          if ($Item['Type'] == 'man') $Gender = 1;
     198          if ($Item['Type'] == 'woman') $Gender = 2;
     199          if ($Item['Type'] == 'child') $Gender = 3;
     200          $RunnerId = $NextRunnerId;
     201          $Queries[] = $this->Database->GetInsert('Runner', array(
     202            'Id' => $RunnerId,
     203            'Name' => $Item['Name'],
     204            'Gender' => $Gender,
     205            'Team' => $TeamId,
     206            'ChipNumber' => $Item['ChipNumber'],
     207            'Year' => $Year,
     208          ));
     209          $Runners[$Item['ChipNumber']] = array('Id' => $RunnerId, 'Distance' => -1);
     210          $NextRunnerId++;
     211        } else
     212          $RunnerId = $Runners[$Item['ChipNumber']]['Id'];
     213
     214        if ($Runners[$Item['ChipNumber']]['Distance'] < $Item['OverallDistance'])
     215        $Queries[] = $this->Database->GetInsert('RunnerStat', array(
     216          'Time' => TimeToMysqlDateTime($Time),
     217          'Runner' => $RunnerId,
     218          'Distance' => $Item['OverallDistance'],
     219          'Rank' => $Item['Pos'],
     220          'Money' => $Item['Money'],
     221        ));
     222      } else
     223      if (($Item['Type'] == 'team') or ($Item['Type'] == 'rodina'))
     224      {
     225        if (!array_key_exists($Item['GroupId'], $Teams))
     226        {
     227          if ($Item['Type'] == 'rodina') $IsFamily = 1;
     228            else $IsFamily = 0;
     229          $Queries[] = $this->Database->GetInsert('Team', array(
     230            'Id' => $NextTeamId,
     231            'Name' => $Item['Name'],
     232            'WebId' => $Item['GroupId'],
     233            'IsFamily' => $IsFamily,
     234            'Year' => $Year,
     235          ));
     236          $TeamId = $NextTeamId;
     237          $Teams[$Item['GroupId']] = array('Id' => $NextTeamId, 'Distance' => -1);
     238          $NextTeamId++;
     239        } else
     240          $TeamId = $Teams[$Item['GroupId']]['Id'];
     241
     242        if ($Teams[$Item['GroupId']]['Distance'] < $Item['OverallDistance'])
     243        $Queries[] = $this->Database->GetInsert('TeamStat', array(
     244          'Time' => TimeToMysqlDateTime($Time),
     245          'Team' => $TeamId,
     246          'Distance' => $Item['OverallDistance'],
     247          'Rank' => $Item['Pos'],
     248          'Money' => $Item['Money'],
     249        ));
     250      } else
     251      {
     252        echo('Unsupported type '.$Item['Type'].'. ');
     253      }
     254    }
     255    //print_r($Queries);
     256    $this->Database->Transaction($Queries);
     257  }
     258
     259  function ShowSync()
     260  {
    118261    $Time = time();
    119     for ($i = 0; $i < 2; $i++)
    120     {
    121       $Queries = array();
    122       $Page = $i * $MaxCount / $ItemsPerPage;
    123       $Response = $this->QueryRunners('all', $Page, null, null, null, $MaxCount);
    124       foreach ($Response['items'] as $Item)
    125       {
    126         if (($Item['Type'] == 'child') or ($Item['Type'] == 'woman') or ($Item['Type'] == 'man'))
    127         {
    128           if (!array_key_exists($Item['ChipNumber'], $Runners))
    129           {
    130             if ($Item['TeamId'] == null)
    131             {
    132               $TeamId = null;
    133             } else
    134             {
    135             if (!array_key_exists($Item['TeamId'], $Teams))
    136             {
    137               $TeamId = $NextTeamId;           
    138               $Queries[] = $this->Database->GetInsert('Team', array(
    139                 'Id' => $TeamId,
    140                 'Name' => '',
    141                 'WebId' => $Item['TeamId'],
    142               ));
    143               $Teams[$Item['TeamId']] = array('Id' => $TeamId, 'Distance' => -1);
    144               $NextTeamId++;
    145             } else
    146               $TeamId = $Teams[$Item['TeamId']]['Id'];
    147             }
    148 
    149             $Gender = 0;
    150             if ($Item['Type'] == 'man') $Gender = 1;
    151             if ($Item['Type'] == 'woman') $Gender = 2;
    152             if ($Item['Type'] == 'child') $Gender = 3;
    153             $RunnerId = $NextRunnerId;
    154             $Queries[] = $this->Database->GetInsert('Runner', array(
    155               'Id' => $RunnerId,
    156               'Name' => $Item['Name'],
    157               'Gender' => $Gender,
    158               'Team' => $TeamId,
    159               'ChipNumber' => $Item['ChipNumber'],
    160             ));
    161             $Runners[$Item['ChipNumber']] = array('Id' => $RunnerId, 'Distance' => -1);
    162             $NextRunnerId++;
    163           } else
    164             $RunnerId = $Runners[$Item['ChipNumber']]['Id'];
    165 
    166           if ($Runners[$Item['ChipNumber']]['Distance'] < $Item['OverallDistance'])
    167           $Queries[] = $this->Database->GetInsert('RunnerStat', array(
    168             'Time' => TimeToMysqlDateTime($Time),
    169             'Runner' => $RunnerId,
    170             'Distance' => $Item['OverallDistance'],
    171             'Rank' => $Item['Pos'],
    172             'Money' => $Item['Money'],
    173           ));
    174         } else
    175         if (($Item['Type'] == 'team') or ($Item['Type'] == 'rodina'))
    176         {
    177           if (!array_key_exists($Item['GroupId'], $Teams))
    178           {
    179             if ($Item['Type'] == 'rodina') $IsFamily = 1;
    180               else $IsFamily = 0;
    181             $Queries[] = $this->Database->GetInsert('Team', array(
    182               'Id' => $NextTeamId,
    183               'Name' => $Item['Name'],
    184               'WebId' => $Item['GroupId'],
    185               'IsFamily' => $IsFamily,
    186             ));
    187             $TeamId = $NextTeamId;           
    188             $Teams[$Item['GroupId']] = array('Id' => $NextTeamId, 'Distance' => -1);
    189             $NextTeamId++;
    190           } else
    191             $TeamId = $Teams[$Item['GroupId']]['Id'];
    192  
    193           if ($Teams[$Item['GroupId']]['Distance'] < $Item['OverallDistance'])
    194           $Queries[] = $this->Database->GetInsert('TeamStat', array(
    195             'Time' => TimeToMysqlDateTime($Time),
    196             'Team' => $TeamId,
    197             'Distance' => $Item['OverallDistance'],
    198             'Rank' => $Item['Pos'],
    199             'Money' => $Item['Money'],
    200           ));
    201         } else
    202         {
    203           echo('Unsupported type '.$Item['Type'].'. ');
    204         }
    205       }   
    206       //print_r($Queries);
    207       $this->Database->Transaction($Queries);
     262    $Items = $this->QueryRunnersAll();
     263    //print_r($Items);
     264    $Hash = md5(serialize($Items));
     265
     266    $DbResult = $this->Database->query('SELECT * FROM Import ORDER BY Time DESC LIMIT 1');
     267    if ($DbResult->num_rows > 0)
     268    {
     269      $Import = $DbResult->fetch_assoc();
     270    } else $Import = array('Hash' => '');
     271    if ($Import['Hash'] != $Hash)
     272    {
     273      $this->Sync($Items, $Time);
     274      $this->Database->insert('Import', array(
     275        'Time' => TimeToMysqlDateTime($Time),
     276        'Hash' => $Hash,
     277        'ItemCount' => count($Items)
     278      ));
    208279    }
    209280  }
    … …  
    214285    $Output .= '<div class="page-title">Families</div>';
    215286
    216     $Where = 'IsFamily=1';
     287    $Year = 1;
     288    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
     289      $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
     290    if ($Year == 0) $Year = $this->GetLatestYear();
     291
     292    $Output .= $this->YearList('/families/', $Year);
     293    $Where = '(IsFamily=1) AND (Year='.$Year.')';
    217294
    218295    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Team` WHERE '.$Where);
    … …  
    326403    $Output .= '<div class="page-title">Teams</div>';
    327404
    328     //$Where = 'Name != ""';
    329     $Where = 'IsFamily=0';
     405    $Year = 1;
     406    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
     407      $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
     408    if ($Year == 0) $Year = $this->GetLatestYear();
     409
     410    $Output .= $this->YearList('/teams/', $Year);
     411
     412    $Where = '(IsFamily=0) AND (Year='.$Year.')';
    330413
    331414    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Team` WHERE '.$Where);
    … …  
    436519  function ShowRunners()
    437520  {
    438     $Output = '';
    439     $Output .= '<div class="page-title">Runners</div>';
    440 
    441     $Where = '1';
     521    $Output = '<div class="page-title">Runners</div>';
     522
     523    $Year = 1;
     524    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
     525      $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
     526    if ($Year == 0) $Year = $this->GetLatestYear();
     527
     528    $Output .= $this->YearList('/runners/', $Year);
     529
     530    $Where = 'Year='.$Year;
    442531    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Runner` WHERE '.$Where);
    443532    $DbRow = $DbResult->fetch_row();
    … …  
    565654    );
    566655    $Order = GetOrderTableHeader($TableColumns, 'Time', 1);
    567     $Output .= '<table class="WideTable">';
     656    $Output .= '<table class="2018/WideTable">';
    568657    $Output .= $Order['Output'];
    569658    $DbResult = $this->Database->query('SELECT * '.
    … …  
    598687    $DbResult = $this->Database->query('SELECT (SELECT COUNT(*) FROM '.$Table.' WHERE '.$Where.') AS TotalCount, '.
    599688      '(SELECT SUM(Distance) FROM '.$Table.'Stat LEFT JOIN '.$Table.' ON '.$Table.'.Id='.$Table.'Stat.'.$Table.' WHERE '.$Where.') AS TotalDistance, '.
    600       '(SELECT SUM(Money) FROM '.$Table.'Stat LEFT JOIN '.$Table.' ON '.$Table.'.Id='.$Table.'Stat.'.$Table.' WHERE '.$Where.') AS TotalMoney');     
     689      '(SELECT SUM(Money) FROM '.$Table.'Stat LEFT JOIN '.$Table.' ON '.$Table.'.Id='.$Table.'Stat.'.$Table.' WHERE '.$Where.') AS TotalMoney');
    601690    $DbRow = $DbResult->fetch_assoc();
    602691    return $DbRow;
    … …  
    606695  {
    607696    $Output = '';
    608     $Runners = $this->GetTotals();
    609     $Men = $this->GetTotals('Runner.Gender=1', 'Runner');
    610     $Women = $this->GetTotals('Runner.Gender=2', 'Runner');
    611     $Kids = $this->GetTotals('Runner.Gender=3', 'Runner');
    612     $Teams = $this->GetTotals('Team.IsFamily=0', 'Team');
    613     $Families = $this->GetTotals('Team.IsFamily=1', 'Team');
    614    
    615     $Output .= '<p>This website collects data from offical <a href="http://leaderboard.teribear.cz/">leaderboard</a> site and track and present progress of runners.</p>';
    616     $Output .= '<div class="page-title">Summary</div>';
     697    $Output .= '<p>This website collects data from offical <a href="http://leaderboard.teribear.cz/">leaderboard</a> site and tracks and presents progress of runners.</p>';
     698    $Output .= '<div class="page-title">Summary '.$Year.'</div>';
     699
     700    $Year = 0;
     701    if ((count($this->PathItems) > 0) and ($this->PathItems[count($this->PathItems) - 1] != ''))
     702      $Year = $this->PathItems[count($this->PathItems) - 1] * 1;
     703    if ($Year == 0) $Year = $this->GetLatestYear();
     704
     705    $Output .= $this->YearList('/', $Year);
     706
     707    $Runners = $this->GetTotals('(Year='.$Year.')');
     708    $Men = $this->GetTotals('(Runner.Gender=1) AND (Year='.$Year.')', 'Runner');
     709    $Women = $this->GetTotals('(Runner.Gender=2) AND (Year='.$Year.')', 'Runner');
     710    $Kids = $this->GetTotals('(Runner.Gender=3) AND (Year='.$Year.')', 'Runner');
     711    $Teams = $this->GetTotals('(Team.IsFamily=0) AND (Year='.$Year.')', 'Team');
     712    $Families = $this->GetTotals('(Team.IsFamily=1) AND (Year='.$Year.')', 'Team');
     713
    617714    $Output .= '<table class="WideTable">';
    618715    $Output .= '<tr><th>Category</th><th>Count</th><th>Distance [km]</th><th>Money [Kč]</th></tr>';
    … …  
    663760    $Output = '';
    664761
    665     if(count($this->PathItems) > 0)
    666     {
    667       if($this->PathItems[0] == 'sync') $Output .= $this->ShowSync();
    668       //else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
    669       else if($this->PathItems[0] == 'runner') $Output .= $this->ShowRunner();
    670       else if($this->PathItems[0] == 'runners') $Output .= $this->ShowRunners();
    671       else if($this->PathItems[0] == 'team') $Output .= $this->ShowTeam();
    672       else if($this->PathItems[0] == 'teams') $Output .= $this->ShowTeams();
    673       else if($this->PathItems[0] == 'family') $Output .= $this->ShowFamily();
    674       else if($this->PathItems[0] == 'families') $Output .= $this->ShowFamilies();
     762    $this->Year = 0;
     763    if (count($this->PathItems) > 0)
     764    {
     765      $Item = $this->PathItems[0];
     766      if ($Item == 'sync') $Output .= $this->ShowSync();
     767      else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
     768      else if($Item == 'runner') $Output .= $this->ShowRunner();
     769      else if($Item == 'runners') $Output .= $this->ShowRunners();
     770      else if($Item == 'team') $Output .= $this->ShowTeam();
     771      else if($Item == 'teams') $Output .= $this->ShowTeams();
     772      else if($Item == 'family') $Output .= $this->ShowFamily();
     773      else if($Item == 'families') $Output .= $this->ShowFamilies();
    675774      else $Output .= $this->ShowMain();
    676775    } else $Output .= $this->ShowMain();
Note: See TracChangeset for help on using the changeset viewer.