Changeset 10


Ignore:
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.
Location:
trunk
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/Database.php

    r2 r10  
    22
    33// Extended database class
    4 // Date: 2011-11-25
     4// Date: 2016-01-11
     5
     6function microtime_float()
     7{
     8  list($usec, $sec) = explode(" ", microtime());
     9  return ((float)$usec + (float)$sec);
     10}
    511
    612class DatabaseResult
    … …  
    2733class Database
    2834{
    29   var $Prefix = '';
     35  var $Prefix;
    3036  var $Functions;
    3137  var $Type;
    3238  var $PDO;
    33   var $Error = '';
     39  var $Error;
    3440  var $insert_id;
    35   var $LastQuery = '';
     41  var $LastQuery;
    3642  var $ShowSQLError;
    3743  var $ShowSQLQuery;
     44  var $LogSQLQuery;
     45  var $LogFile;
    3846
    3947  function __construct()
    4048  {
     49    $this->Prefix = '';
     50    $this->Functions = array('NOW()', 'CURDATE()', 'CURTIME()', 'UUID()');
    4151    $this->Type = 'mysql';  // mysql, pgsql
     52    $this->Error = '';
     53    $this->LastQuery = '';
    4254    $this->ShowSQLError = false;
    4355    $this->ShowSQLQuery = false;
    44     $this->Functions = array('NOW()', 'CURDATE()', 'CURTIME()', 'UUID()');
    45   }
     56    $this->LogSQLQuery = false;
     57    $this->LogFile = dirname(__FILE__).'/../../Query.log';
     58  }
     59 
    4660
    4761  function Connect($Host, $User, $Password, $Database)
    … …  
    7791  function query($Query)
    7892  {
    79     if(!$this->Connected()) throw new Exception('Not connected to database');
     93    if(!$this->Connected()) throw new Exception(T('Not connected to database'));
     94    if(($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime_float();
    8095    $this->LastQuery = $Query;
     96    //echo('a'.$this->ShowSQLQuery.'<'.$QueryStartTime.', '.microtime_float());
     97    if(($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true))
     98      $Duration = ' ; '.round(microtime_float() - $QueryStartTime, 4). ' s';
     99    if($this->LogSQLQuery == true)
     100      file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND);
    81101    if($this->ShowSQLQuery == true)
    82       echo('<div style="border-bottom-width: 1px; border-bottom-style: solid; padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.'</div>'."\n");
     102      echo('<div style="border-bottom-width: 1px; border-bottom-style: solid; '.
     103      'padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.$Duration.'</div>'."\n");
    83104    $Result = new DatabaseResult();
    84105    $Result->PDOStatement = $this->PDO->query($Query);
    … …  
    88109      $this->insert_id = $this->PDO->lastInsertId();
    89110    } else
    90     {
     111    {     
    91112      $this->Error = $this->PDO->errorInfo();
    92113      $this->Error = $this->Error[2];
    … …  
    110131  function insert($Table, $Data)
    111132  {
     133    $this->query($this->GetInsert($Table, $Data));
     134    $this->insert_id = $this->PDO->lastInsertId();
     135  }
     136 
     137  function GetInsert($Table, $Data)
     138  {
    112139    $Name = '';
    113140    $Values = '';
    … …  
    124151    $Name = substr($Name, 1);
    125152    $Values = substr($Values, 1);
    126     $this->query('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
    127     $this->insert_id = $this->PDO->lastInsertId();
     153    return('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
    128154  }
    129155
    130156  function update($Table, $Condition, $Data)
     157  {
     158    $this->query($this->GetUpdate($Table, $Condition, $Data));
     159  }
     160 
     161  function GetUpdate($Table, $Condition, $Data)
    131162  {
    132163    $Values = '';
    … …  
    141172    }
    142173    $Values = substr($Values, 2);
    143     $this->query('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');
     174    return('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');
    144175  }
    145176
    … …  
    175206  }
    176207
     208  function quote($Text)
     209  {
     210    return($this->PDO->quote($Text));
     211  }
     212
    177213  public function __sleep()
    178214  {
    … …  
    182218  public function __wakeup()
    183219  {
     220  }
     221 
     222  public function Transaction($Queries)
     223  {
     224      $this->PDO->beginTransaction();
     225      foreach ($Queries as $Query)
     226      {
     227        $Statement = $this->PDO->prepare($Query);
     228        $Statement->execute();
     229      }         
     230      $this->PDO->commit();
    184231  }
    185232}

  • trunk/index.php

    r9 r10  
    5959  // Query: text from name
    6060  // Page: index of page, one page is 30 items
    61   // Category: '', all, men, woman, kids, families, teams
     61  // Category: '', all, men, women, kids, families, teams
    6262  // TeamId: id of team
    6363  // Count: number of items per page, default 30
    64   function QueryRunners($Category, $Page, $TeamId, $Query = null, $StartNumber = null, $Count = null)
    65   {
    66     $URL = 'http://leaderboard.teribear.cz/Home/GetRunners?category='.$Category.'&page='.$Page.'&teamId='.$TeamId;
     64  function QueryRunners($Category, $Page = 0, $TeamId = null, $Query = null, $StartNumber = null, $Count = null)
     65  {
     66    $URL = 'http://leaderboard.teribear.cz/Home/GetRunners?category='.$Category;
     67    if ($Page != 0) $URL .= '&page='.$Page;
     68    if ($TeamId != null) $URL .= '&teamId='.$TeamId;
    6769    if ($Count != null) $URL .= '&count='.$Count;
    6870    if ($Query != null) $URL .= '&query='.$Query;
    69     if ($Category != null) $URL .= '&startNumber='.$StartNumber;
     71    if ($StartNumber != null) $URL .= '&startNumber='.$StartNumber;
    7072    $Content = file_get_contents($URL);
    7173    $JSON = json_decode($Content, true);
    … …  
    7476  }
    7577
    76   function ShowTeamUpdate()
    77   {
     78  function ShowEmpty()
     79  {
     80    $this->Database->query('DELETE FROM RunnerStat');
     81    $this->Database->query('DELETE FROM Runner');
     82    $this->Database->query('DELETE FROM TeamStat');
     83    $this->Database->query('DELETE FROM Team');
     84  }
     85
     86  function ShowSync() 
     87  {
     88    $ItemsPerPage = 30;
     89    $MaxCount = 450 * 30;
     90
     91    // Load all runners
     92    $DbResult = $this->Database->query('SELECT MAX(Id) AS Id FROM Runner');
     93    $DbRow = $DbResult->fetch_assoc();
     94    $NextRunnerId = $DbRow['Id'] + 1;
     95
     96    $Runners = array();
     97    $DbResult = $this->Database->query('SELECT Runner.Id, Runner.ChipNumber, '.
     98      '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Distance DESC LIMIT 1) AS Distance FROM Runner');
     99    while ($DbRow = $DbResult->fetch_assoc())
     100    {
     101      $Runners[$DbRow['ChipNumber']] = $DbRow;
     102    }
     103
     104    // Load all teams
     105    $DbResult = $this->Database->query('SELECT MAX(Id) AS Id FROM Team');
     106    $DbRow = $DbResult->fetch_assoc();
     107    $NextTeamId = $DbRow['Id'] + 1;
     108
     109    $Teams = array();
     110    $DbResult = $this->Database->query('SELECT Team.Id, Team.WebId, '.
     111      '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Distance DESC LIMIT 1) AS Distance FROM Team');
     112    while ($DbRow = $DbResult->fetch_assoc())
     113    {
     114      $Teams[$DbRow['WebId']] = $DbRow;
     115    }
     116
    78117    $Time = time();
     118    for ($i = 0; $i < 2; $i++)
     119    {
     120    $Queries = array();
     121    $Page = $i * $MaxCount / $ItemsPerPage;
     122    echo($Page.' ');
     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')
     176      {
     177        if (!array_key_exists($Item['GroupId'], $Teams))
     178        {
     179          $Queries[] = $this->Database->GetInsert('Team', array(
     180            'Id' => $NextTeamId,
     181            'Name' => $Item['Name'],
     182            'WebId' => $Item['GroupId'],
     183          ));
     184          $TeamId = $NextTeamId;           
     185          $Teams[$Item['GroupId']] = array('Id' => $NextTeamId, 'Distance' => -1);
     186          $NextTeamId++;
     187        } else
     188          $TeamId = $Teams[$Item['GroupId']]['Id'];
     189
     190        if ($Teams[$Item['GroupId']]['Distance'] < $Item['OverallDistance'])
     191        $Queries[] = $this->Database->GetInsert('TeamStat', array(
     192          'Time' => TimeToMysqlDateTime($Time),
     193          'Team' => $TeamId,
     194          'Distance' => $Item['OverallDistance'],
     195          'Rank' => $Item['Pos'],
     196          'Money' => $Item['Money'],
     197        ));
     198      }
     199    }   
     200    print_r($Queries);
     201    $this->Database->Transaction($Queries);
     202    }
     203  }
     204
     205  function ShowTeams()
     206  {
     207    $Output = '';
     208
    79209    $DbResult = $this->Database->query('SELECT * FROM Team');
    80     while ($Team = $DbResult->fetch_assoc())
    81     {
    82       echo('Checking team '.$Team['WebId'].'...');
    83       $Runners = $this->QueryRunners('teams', 0, $Team['WebId']);
    84       foreach ($Runners['items'] as $Runner)
    85       {
    86         //print_r($Runner);
    87         $RunnerItem = new Runner();
    88         $RunnerItem->Database = $this->Database;
    89         $RunnerItem->Name = $Runner['Name'];
    90         $RunnerItem->ChipNumber = $Runner['ChipNumber'];
    91         if ($Runner['Type'] == 'man') $RunnerItem->Gender = 1;
    92         if ($Runner['Type'] == 'woman') $RunnerItem->Gender = 2;
    93         $RunnerItem->Team = $Team['Id'];
    94         $RunnerItem->AddIfNotExist();
    95 
    96         $DbResult = $this->Database->query('SELECT * FROM RunnerStat WHERE Runner='.$RunnerItem->Id.' ORDER BY TIME DESC LIMIT 1');
    97         if ($DbResult->num_rows > 0)
    98         {
    99           $RunnerStat = $DbResult->fetch_assoc();
    100           if ($RunnerStat['Distance'] < $Runner['OverallDistance'])
    101           {
    102             $this->Database->insert('RunnerStat', array(
    103             'Time' => TimeToMysqlDateTime($Time),
    104             'Runner' => $RunnerItem->Id,
    105             'Distance' => $Runner['OverallDistance'],
    106             'Rank' => $Runner['Pos'],
    107             'Money' => $Runner['Money'],
    108           ));
    109           }
    110         } else
    111         {
    112           $this->Database->insert('RunnerStat', array(
    113             'Time' => TimeToMysqlDateTime($Time),
    114             'Runner' => $RunnerItem->Id,
    115             'Distance' => $Runner['OverallDistance'],
    116             'Rank' => $Runner['Pos'],
    117             'Money' => $Runner['Money'],
    118           ));
    119         }
    120       }
    121       echo("<br/>\n");
    122     }
     210    $DbRow = $DbResult->fetch_assoc();
     211    $Output .= '<h4 style="text-align: center;">Teams</h4>';
     212
     213    $Where = '1';
     214
     215    $DbResult = $this->Database->query('SELECT COUNT(*) FROM `Team` WHERE '.$Where);
     216    $DbRow = $DbResult->fetch_row();
     217    $PageList = GetPageList($DbRow[0]);
     218
     219    $Output .= '<div id="list_content">';
     220    $Output .= $PageList['Output'];
     221    $TableColumns = array(
     222      array('Name' => 'Name', 'Title' => 'Name'),
     223      array('Name' => 'RunnersCount', 'Title' => 'Runners'),
     224      array('Name' => 'Distance', 'Title' => 'Distance'),
     225      array('Name' => 'Money', 'Title' => 'Money'),
     226      array('Name' => 'Rank', 'Title' => 'Rank'),
     227    );
     228    $Order = GetOrderTableHeader($TableColumns, 'Distance', 1);
     229    $Output .= '<table class="WideTable">';
     230    $Output .= $Order['Output'];
     231    $DbResult = $this->Database->select('Team', '*, '.
     232      '(SELECT COUNT(*) FROM Runner WHERE Runner.Team=Team.Id) AS RunnersCount, '.
     233      '0 AS Distance, '.
     234      '0 AS Money, '.
     235      '0 AS Rank',
     236      //'(SELECT SUM(Distance) FROM Runner WHERE Runner.Team=Team.Id) AS Distance, '.
     237      //'(SELECT SUM(Money) FROM Runner WHERE Runner.Team=Team.Id) AS Money ',
     238      $Where.$Order['SQL'].$PageList['SQLLimit']);
     239    while($Item = $DbResult->fetch_assoc())
     240    {
     241      if ($Item['Name'] == '') $Item['Name'] = 'Without team';
     242      $Output .= '<tr>'.
     243        '<td><a href="'.$this->Link('/team/'.$Item['Id'].'/').'">'.$Item['Name'].'</a></td>'.
     244        '<td>'.$Item['RunnersCount'].'</td>'.
     245        '<td>'.$Item['Distance'].'</td>'.
     246        '<td>'.$Item['Money'].'</td>'.
     247        '<td>'.$Item['Rank'].'</td>'.
     248        '</tr>';
     249    }
     250    $Output .= '</table>';
     251    $Output .= $PageList['Output'];
     252    $Output .= '</div>';
     253
     254    return($Output);
    123255  }
    124256
    … …  
    134266    $DbResult = $this->Database->query('SELECT * FROM Team WHERE Id='.$TeamId);
    135267    $DbRow = $DbResult->fetch_assoc();
    136     $Output .= '<h4 style="text-align: center;">Team '.$DbRow['Name'].'</h4></br>';
     268    $Output .= '<h4 style="text-align: center;">Team '.$DbRow['Name'].'</h4>';
    137269
    138270
    … …  
    143275    $PageList = GetPageList($DbRow[0]);
    144276
    145     $Gender = array('', 'Male', 'Female');
     277    $Gender = array('', 'Male', 'Female', 'Child');
    146278    $Output .= '<div id="list_content">';
    147279    $Output .= $PageList['Output'];
    … …  
    308440    $Output .= $Content;
    309441    $Output .= '<br/><div class="footer">Contact: <a href="mailto:'.$Config['Contact'].'">'.$Config['Contact'].'</a> '.
    310        '<a href="https://svn.zdechov.net/trac/teribear/">Source code</a></div>';
     442       '<a href="https://app.zdechov.net/teribear/">Source code</a></div>';
    311443    $Output .= '</body></html>';
    312444    return($Output);
    … …  
    329461    if(count($this->PathItems) > 0)
    330462    {
    331       if($this->PathItems[0] == 'team-update') $Output .= $this->ShowTeamUpdate();
    332       if($this->PathItems[0] == 'runner') $Output .= $this->ShowRunner();
    333       else $Output .= $this->ShowTeam();
    334     } else $Output .= $this->ShowTeam();
     463      if($this->PathItems[0] == 'sync') $Output .= $this->ShowSync();
     464      //else if($this->PathItems[0] == 'empty') $Output .= $this->ShowEmpty();
     465      else if($this->PathItems[0] == 'runner') $Output .= $this->ShowRunner();
     466      else if($this->PathItems[0] == 'team') $Output .= $this->ShowTeam();
     467      else $Output .= $this->ShowTeams();
     468    } else $Output .= $this->ShowTeams();
    335469    if (!$this->NoFullPage)
    336470    {
Note: See TracChangeset for help on using the changeset viewer.