Changeset 10 for trunk/index.php

Timestamp:

Aug 28, 2019, 1:30:12 AM (5 years ago)

Author:

chronos

Message:

• Modified: Sync data for all runners and teams from official server.
• Modified: Optimize sync inserts with transaction.
• Added: Show table of all teams.

File:

• trunk/index.php (modified) (6 diffs)

trunk/index.php

@@ -59 +59 @@
   // Query: text from name
   // Page: index of page, one page is 30 items
-  // Category: '', all, men, woman, kids, families, teams
+  // Category: '', all, men, women, kids, families, teams
   // TeamId: id of team
   // Count: number of items per page, default 30
-  function QueryRunners($Category, $Page, $TeamId, $Query = null, $StartNumber = null, $Count = null)
-  {
-    $URL = 'http://leaderboard.teribear.cz/Home/GetRunners?category='.$Category.'&page='.$Page.'&teamId='.$TeamId;
+  function QueryRunners($Category, $Page = 0, $TeamId = null, $Query = null, $StartNumber = null, $Count = null)
+  {
+    $URL = 'http://leaderboard.teribear.cz/Home/GetRunners?category='.$Category;
+    if ($Page != 0) $URL .= '&page='.$Page;
+    if ($TeamId != null) $URL .= '&teamId='.$TeamId;
     if ($Count != null) $URL .= '&count='.$Count;
     if ($Query != null) $URL .= '&query='.$Query;
-    if ($Category != null) $URL .= '&startNumber='.$StartNumber;
+    if ($StartNumber != null) $URL .= '&startNumber='.$StartNumber;
     $Content = file_get_contents($URL);
     $JSON = json_decode($Content, true);
@@ -74 +76 @@
   }
 
-  function ShowTeamUpdate()
-  {
+  function ShowEmpty()
+  {
+    $this->Database->query('DELETE FROM RunnerStat');
+    $this->Database->query('DELETE FROM Runner');
+    $this->Database->query('DELETE FROM TeamStat');
+    $this->Database->query('DELETE FROM Team');
+  }
+
+  function ShowSync()  
+  {
+    $ItemsPerPage = 30;
+    $MaxCount = 450 * 30;
+
+    // Load all runners
+    $DbResult = $this->Database->query('SELECT MAX(Id) AS Id FROM Runner');
+    $DbRow = $DbResult->fetch_assoc();
+    $NextRunnerId = $DbRow['Id'] + 1;
+
+    $Runners = array();
+    $DbResult = $this->Database->query('SELECT Runner.Id, Runner.ChipNumber, '.
+      '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Distance DESC LIMIT 1) AS Distance FROM Runner');
+    while ($DbRow = $DbResult->fetch_assoc())
+    {
+      $Runners[$DbRow['ChipNumber']] = $DbRow;
+    }
+
+    // Load all teams
+    $DbResult = $this->Database->query('SELECT MAX(Id) AS Id FROM Team');
+    $DbRow = $DbResult->fetch_assoc();
+    $NextTeamId = $DbRow['Id'] + 1;
+
+    $Teams = array();
+    $DbResult = $this->Database->query('SELECT Team.Id, Team.WebId, '.
+      '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Distance DESC LIMIT 1) AS Distance FROM Team');
+    while ($DbRow = $DbResult->fetch_assoc())
+    {
+      $Teams[$DbRow['WebId']] = $DbRow;
+    }
+
     $Time = time();
+    for ($i = 0; $i < 2; $i++)
+    {
+    $Queries = array();
+    $Page = $i * $MaxCount / $ItemsPerPage;
+    echo($Page.' ');
+    $Response = $this->QueryRunners('all', $Page, null, null, null, $MaxCount);
+    foreach ($Response['items'] as $Item)
+    {
+      if (($Item['Type'] == 'child') or ($Item['Type'] == 'woman') or ($Item['Type'] == 'man'))
+      {
+        if (!array_key_exists($Item['ChipNumber'], $Runners))
+        {
+          if ($Item['TeamId'] == null) 
+          {
+            $TeamId = null;
+          } else
+          {
+          if (!array_key_exists($Item['TeamId'], $Teams))
+          {
+            $TeamId = $NextTeamId;            
+            $Queries[] = $this->Database->GetInsert('Team', array(
+              'Id' => $TeamId,
+              'Name' => '',
+              'WebId' => $Item['TeamId'],
+            ));
+            $Teams[$Item['TeamId']] = array('Id' => $TeamId, 'Distance' => -1);
+            $NextTeamId++;
+          } else 
+            $TeamId = $Teams[$Item['TeamId']]['Id'];
+          }
+
+          $Gender = 0;
+          if ($Item['Type'] == 'man') $Gender = 1;
+          if ($Item['Type'] == 'woman') $Gender = 2;
+          if ($Item['Type'] == 'child') $Gender = 3;
+          $RunnerId = $NextRunnerId;
+          $Queries[] = $this->Database->GetInsert('Runner', array(
+            'Id' => $RunnerId,
+            'Name' => $Item['Name'],
+            'Gender' => $Gender,
+            'Team' => $TeamId,
+            'ChipNumber' => $Item['ChipNumber'],
+          ));
+          $Runners[$Item['ChipNumber']] = array('Id' => $RunnerId, 'Distance' => -1);
+          $NextRunnerId++;
+        } else 
+          $RunnerId = $Runners[$Item['ChipNumber']]['Id'];
+
+        if ($Runners[$Item['ChipNumber']]['Distance'] < $Item['OverallDistance'])
+        $Queries[] = $this->Database->GetInsert('RunnerStat', array(
+          'Time' => TimeToMysqlDateTime($Time),
+          'Runner' => $RunnerId,
+          'Distance' => $Item['OverallDistance'],
+          'Rank' => $Item['Pos'],
+          'Money' => $Item['Money'],
+        ));
+      } else
+      if ($Item['Type'] == 'team')
+      {
+        if (!array_key_exists($Item['GroupId'], $Teams))
+        {
+          $Queries[] = $this->Database->GetInsert('Team', array(
+            'Id' => $NextTeamId,
+            'Name' => $Item['Name'],
+            'WebId' => $Item['GroupId'],
+          ));
+          $TeamId = $NextTeamId;            
+          $Teams[$Item['GroupId']] = array('Id' => $NextTeamId, 'Distance' => -1);
+          $NextTeamId++;
+        } else 
+          $TeamId = $Teams[$Item['GroupId']]['Id'];
+
+        if ($Teams[$Item['GroupId']]['Distance'] < $Item['OverallDistance'])
+        $Queries[] = $this->Database->GetInsert('TeamStat', array(
+          'Time' => TimeToMysqlDateTime($Time),
+          'Team' => $TeamId,
+          'Distance' => $Item['OverallDistance'],
+          'Rank' => $Item['Pos'],
+          'Money' => $Item['Money'],
+        ));
+      }
+    }    
+    print_r($Queries);
+    $this->Database->Transaction($Queries);
+    }
+  }
+
+  function ShowTeams()
+  {
+    $Output = '';
+
     $DbResult = $this->Database->query('SELECT * FROM Team');
-    while ($Team = $DbResult->fetch_assoc())
-    {
-      echo('Checking team '.$Team['WebId'].'...');
-      $Runners = $this->QueryRunners('teams', 0, $Team['WebId']);
-      foreach ($Runners['items'] as $Runner)
-      {
-        //print_r($Runner);
-        $RunnerItem = new Runner();
-        $RunnerItem->Database = $this->Database;
-        $RunnerItem->Name = $Runner['Name'];
-        $RunnerItem->ChipNumber = $Runner['ChipNumber'];
-        if ($Runner['Type'] == 'man') $RunnerItem->Gender = 1;
-        if ($Runner['Type'] == 'woman') $RunnerItem->Gender = 2;
-        $RunnerItem->Team = $Team['Id'];
-        $RunnerItem->AddIfNotExist();
-
-        $DbResult = $this->Database->query('SELECT * FROM RunnerStat WHERE Runner='.$RunnerItem->Id.' ORDER BY TIME DESC LIMIT 1');
-        if ($DbResult->num_rows > 0)
-        {
-          $RunnerStat = $DbResult->fetch_assoc();
-          if ($RunnerStat['Distance'] < $Runner['OverallDistance'])
-          {
-            $this->Database->insert('RunnerStat', array(
-            'Time' => TimeToMysqlDateTime($Time),
-            'Runner' => $RunnerItem->Id,
-            'Distance' => $Runner['OverallDistance'],
-            'Rank' => $Runner['Pos'],
-            'Money' => $Runner['Money'],
-          ));
-          }
-        } else
-        {
-          $this->Database->insert('RunnerStat', array(
-            'Time' => TimeToMysqlDateTime($Time),
-            'Runner' => $RunnerItem->Id,
-            'Distance' => $Runner['OverallDistance'],
-            'Rank' => $Runner['Pos'],
-            'Money' => $Runner['Money'],
-          ));
-        }
-      }
-      echo("<br/>\n");
-    }
+    $DbRow = $DbResult->fetch_assoc();
+    $Output .= '<h4 style="text-align: center;">Teams</h4>';
+
+    $Where = '1';
+
+    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Team` WHERE '.$Where);
+    $DbRow = $DbResult->fetch_row();
+    $PageList = GetPageList($DbRow[0]);
+
+    $Output .= '<div id="list_content">';
+    $Output .= $PageList['Output'];
+    $TableColumns = array(
+      array('Name' => 'Name', 'Title' => 'Name'),
+      array('Name' => 'RunnersCount', 'Title' => 'Runners'),
+      array('Name' => 'Distance', 'Title' => 'Distance'),
+      array('Name' => 'Money', 'Title' => 'Money'),
+      array('Name' => 'Rank', 'Title' => 'Rank'),
+    );
+    $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
+    $Output .= '<table class="WideTable">';
+    $Output .= $Order['Output'];
+    $DbResult = $this->Database->select('Team', '*, '.
+      '(SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id) AS RunnersCount, '.
+      '0 AS Distance, '.
+      '0 AS Money, '.
+      '0 AS Rank',
+      //'(SELECT SUM(Distance) FROM Runner WHERE Runner.Team=Team.Id) AS Distance, '.
+      //'(SELECT SUM(Money) FROM Runner WHERE Runner.Team=Team.Id) AS Money ',
+      $Where.$Order['SQL'].$PageList['SQLLimit']);
+    while($Item = $DbResult->fetch_assoc())
+    {
+      if ($Item['Name'] == '') $Item['Name'] = 'Without team';
+      $Output .= '<tr>'.
+        '<td><a href="'.$this->Link('/team/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
+        '<td>'.$Item['RunnersCount'].'</td>'.
+        '<td>'.$Item['Distance'].'</td>'.
+        '<td>'.$Item['Money'].'</td>'.
+        '<td>'.$Item['Rank'].'</td>'.
+        '</tr>';
+    }
+    $Output .= '</table>';
+    $Output .= $PageList['Output'];
+    $Output .= '</div>';
+
+    return($Output);
   }
 
@@ -134 +266 @@
     $DbResult = $this->Database->query('SELECT * FROM Team WHERE Id='.$TeamId);
     $DbRow = $DbResult->fetch_assoc();
-    $Output .= '<h4 style="text-align: center;">Team '.$DbRow['Name'].'</h4></br>';
+    $Output .= '<h4 style="text-align: center;">Team '.$DbRow['Name'].'</h4>';
 
 
@@ -143 +275 @@
     $PageList = GetPageList($DbRow[0]);
 
-    $Gender = array('', 'Male', 'Female');
+    $Gender = array('', 'Male', 'Female', 'Child');
     $Output .= '<div id="list_content">';
     $Output .= $PageList['Output'];
@@ -308 +440 @@
     $Output .= $Content;
     $Output .= '<br/><div class="footer">Contact: <a href="mailto:'.$Config['Contact'].'">'.$Config['Contact'].'</a> '.
-       '<a href="https://svn.zdechov.net/trac/teribear/">Source code</a></div>';
+       '<a href="https://app.zdechov.net/teribear/">Source code</a></div>';
     $Output .= '</body></html>';
     return($Output);
@@ -329 +461 @@
     if(count($this->PathItems) > 0)
     {
-      if($this->PathItems[0] == 'team-update') $Output .= $this->ShowTeamUpdate();
-      if($this->PathItems[0] == 'runner') $Output .= $this->ShowRunner();
-      else $Output .= $this->ShowTeam();
-    } else $Output .= $this->ShowTeam();
+      if($this->PathItems[0] == 'sync') $Output .= $this->ShowSync();
+      //else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
+      else if($this->PathItems[0] == 'runner') $Output .= $this->ShowRunner();
+      else if($this->PathItems[0] == 'team') $Output .= $this->ShowTeam();
+      else $Output .= $this->ShowTeams();
+    } else $Output .= $this->ShowTeams();
     if (!$this->NoFullPage)
     {