Changeset 43 for trunk/index.php


Ignore:
Timestamp:
Sep 17, 2020, 9:51:29 AM (4 years ago)
Author:
chronos
Message:
  • Fixed: Insert new stat record only if distance of last record is different. Not if highest distance is different to current distance. Distance can go down, not just up.
  • Fixed: Reduce number of loaded items in one request to avoid remove internal server error.
  • Fixed: Error showing teams with quotes in their name.
  • Added: Preparation for drawing charts.
  • Added: Show how many items were loaded from remote leaderboard.
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/index.php

    r42 r43  
    147147      if ($StartNumber != null) $URL .= '&startNumber='.$StartNumber;
    148148      $Content = file_get_contents($URL);
    149       $JSON = json_decode($Content, true);
     149      if (($Content !== false) and !empty($Content))
     150      {
     151        $JSON = json_decode($Content, true);
     152      } else
     153      {
     154        $JSON = null;
     155        echo('Cannot load data from remote server ('.$URL.').<br/>'."\n");
     156      }
    150157      return $JSON;
    151158  }
    … …  
    154161  {
    155162    $ItemsPerPage = 30;
    156     $MaxCount = 350 * 30;
     163    $MaxCount = 250 * 30;
    157164    if ($Count > 0) $MaxCount = $Count;
    158165    $Result = array();
    … …  
    162169      $Page = $I * floor($MaxCount / $ItemsPerPage);
    163170      $JSON = $this->QueryRunners($Category, $Page, null, null, null, $MaxCount);
    164       foreach ($JSON['items'] as $Item)
     171      if ($JSON != null)
    165172      {
    166         $Result[] = $Item;
    167       }
    168       if (($JSON['last'] == 'true') or (count($JSON) == 0)) break;
    169       if ($I > 10) break; // Safe limit if last would not work
    170       $I++;
     173        foreach ($JSON['items'] as $Item)
     174        {
     175          $Result[] = $Item;
     176        }
     177        if (($JSON['last'] == 'true') or (count($JSON) == 0)) break;
     178        if ($I > 10) break; // Safe limit if last would not work
     179        $I++;
     180      } else break;
    171181    }
    172182    return $Result;
    … …  
    193203    $Runners = array();
    194204    $DbResult = $this->Database->query('SELECT Runner.Id, Runner.ChipNumber, Runner.Team, '.
    195       '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Distance DESC LIMIT 1) AS Distance '.
     205      '(SELECT RunnerStat.Distance FROM RunnerStat WHERE (RunnerStat.Runner = Runner.Id) ORDER BY RunnerStat.Id DESC LIMIT 1) AS Distance '.
    196206      'FROM Runner WHERE Year='.$Year);
    197207    while ($DbRow = $DbResult->fetch_assoc())
    … …  
    207217    $Teams = array();
    208218    $DbResult = $this->Database->query('SELECT Team.Id, Team.WebId, Team.Name, '.
    209       '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Distance DESC LIMIT 1) AS Distance '.
     219      '(SELECT TeamStat.Distance FROM TeamStat WHERE (TeamStat.Team = Team.Id) ORDER BY TeamStat.Id DESC LIMIT 1) AS Distance '.
    210220      'FROM Team WHERE Year='.$Year);
    211221    while ($DbRow = $DbResult->fetch_assoc())
    … …  
    277287
    278288        if ($Runners[$Item['ChipNumber']]['Distance'] != $Item['OverallDistance'])
    279         $Queries[] = $this->Database->GetInsert('RunnerStat', array(
    280           'Time' => TimeToMysqlDateTime($Time),
    281           'Runner' => $RunnerId,
    282           'Distance' => $Item['OverallDistance'],
    283           'Rank' => $Item['Pos'],
    284           'Money' => $Item['Money'],
    285         ));
     289        {
     290          $Queries[] = $this->Database->GetInsert('RunnerStat', array(
     291            'Time' => TimeToMysqlDateTime($Time),
     292            'Runner' => $RunnerId,
     293            'Distance' => $Item['OverallDistance'],
     294            'Rank' => $Item['Pos'],
     295             'Money' => $Item['Money'],
     296          ));
     297        }
    286298      } else
    287299      if (($Item['Type'] == 'team') or ($Item['Type'] == 'rodina'))
    … …  
    341353    $Time = time();
    342354    $Items = $this->QueryRunnersAll('all');
     355    $Output = T('Loaded items count: '.count($Items).'<br/>');
    343356    $ItemsWithoutProgress = array();
    344357    foreach ($Items as $Item)
    … …  
    364377      //file_put_contents('import/'.$Hash.'.txt', print_r($Items, true));
    365378    }
    366     $Output = T('Data synchronization from leaderboard finished.');
     379    $Output .= T('Data synchronization from leaderboard finished.<br/>');
    367380    return $Output;
    368381  }
    … …  
    475488    $this->Title = $Title.' '.$DbRow['Name'].' - '.$this->Title;
    476489    $Output .= '<div class="page-title">'.$Title.' '.$DbRow['Name'].'</div>';
    477     $Output .= '<div class="section-title">'.$this->ItemsYearList('/'.$UrlDir.'/', $TeamId, 'Team', 'Name="'.$DbRow['Name'].'"').'</div>';
     490    $Output .= '<div class="section-title">'.$this->ItemsYearList('/'.$UrlDir.'/', $TeamId, 'Team', 'Name="'.$this->Database->real_escape_string($DbRow['Name']).'"').'</div>';
    478491    $this->LoadYearParameters($DbRow['Year']);
    479492    $Output .= '<div class="section-title">'.
    … …  
    526539
    527540    $Output .= $this->ShowDetailed('Team', $TeamId);
     541    //$Output .= $this->ShowDetailedChart('Team', $TeamId);
    528542    $Output .= $this->ShowDaily('Team', $TeamId);
     543    //$Output .= $this->ShowDailyChart('Team', $TeamId);
    529544
    530545    return $Output;
    … …  
    535550    $PrefixMultiplier = new PrefixMultiplier();
    536551
     552    $TableStat = $Table.'Stat';
    537553    $Output = '<div class="section-title">'.T('Lap progress').'</div>';
    538     $Where = $Table.'Stat.'.$Table.'='.$Id;
    539     $DbResult = $this->Database->query('SELECT COUNT(*) FROM `'.$Table.'Stat` WHERE '.$Where);
     554    $Where = $TableStat.'.'.$Table.'='.$Id;
     555    $DbResult = $this->Database->query('SELECT COUNT(*) FROM '.$TableStat.' WHERE '.$Where);
    540556    $DbRow = $DbResult->fetch_row();
    541557    $PageList = GetPageList($DbRow[0]);
    … …  
    555571    $Output .= '<table class="WideTable">';
    556572    $Output .= $Order['Output'];
    557     $TableStat = $Table.'Stat';
    558573    $DbResult = $this->Database->query('SELECT *'.
    559574      ', (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'.
    … …  
    583598    $Output .= $PageList['Output'];
    584599    $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'));
    585618    return $Output;
    586619  }
    … …  
    649682    $Output .= $PageList['Output'];
    650683    $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'));
    651701    return $Output;
    652702  }
    … …  
    734784    if ($DbRow['TeamName'] != '')
    735785      $Output .= '<div class="section-title"><a href="'.$this->Link('/team/'.$DbRow['TeamId']).'">'.$DbRow['TeamName'].'</a></div>';
    736     $Output .= '<div class="section-title">'.$this->ItemsYearList('/runner/', $RunnerId, 'Runner', 'Name="'.$DbRow['Name'].'"').'</div>';
     786    $Output .= '<div class="section-title">'.$this->ItemsYearList('/runner/', $RunnerId, 'Runner', 'Name="'.$this->Database->real_escape_string($DbRow['Name']).'"').'</div>';
    737787    $this->LoadYearParameters($DbRow['Year']);
    738788
    739789    $Output .= $this->ShowDetailed('Runner', $RunnerId);
     790    //$Output .= $this->ShowDetailedChart('Runner', $RunnerId);
    740791    $Output .= $this->ShowDaily('Runner', $RunnerId);
     792    //$Output .= $this->ShowDailyChart('Runner', $RunnerId);
    741793    return $Output;
    742794  }
    … …  
    800852    $Lang = 'en';
    801853    $Output = '<?xml version="1.0" encoding="'.$this->Config['Encoding'].'"?>'."\n".
    802       '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">'.
    803       '<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="'.$Lang.'" lang="'.$Lang.'">'.
     854      '<!doctype html>'.
     855      '<html>'.
    804856      '<head>'.
    805857      '<link rel="stylesheet" href="'.$this->Link('/style/style.css').'" type="text/css" media="all" />'.
    806858      '<meta http-equiv="content-type" content="application/xhtml+xml; charset='.$this->Config['Encoding'].'" />'.
    807       '<meta name="viewport" content="width=device-width, initial-scale=1">';
     859      '<meta name="viewport" content="width=device-width, initial-scale=1">'.
     860      '<script type="text/javascript" src="'.$this->Link('/Packages/Chart.js/Chart.js').'"></script>';
    808861      //'<script src="'.$this->Link('/jquery.js').'"></script>';
    809862    $Output .= '<title>'.$this->Title.'</title>'.
    … …  
    915968      $this->MoneyKm = $DbRow['MoneyKm'];
    916969    }
     970  }
     971
     972  function ShowChart($ChartName, $Values, $Title)
     973  {
     974    $Output = '<div style="width:50%; text-align: center; margin-left: auto; margin-right: auto;">'.
     975                '  <canvas id="'.$ChartName.'"></canvas>'.
     976    '</div>'.
     977    '<script type="text/javascript">';
     978    $Output .= "var ctx = document.getElementById('".$ChartName."').getContext('2d');
     979    window.chartColors = {
     980      red: 'rgb(255, 99, 132)',
     981      orange: 'rgb(255, 159, 64)',
     982      yellow: 'rgb(255, 205, 86)',
     983      green: 'rgb(75, 192, 192)',
     984      blue: 'rgb(54, 162, 235)',
     985      purple: 'rgb(153, 102, 255)',
     986      grey: 'rgb(201, 203, 207)'
     987    };
     988    var config = {
     989      type: 'line',
     990      data: {
     991        labels: [";
     992      foreach ($Values as $Key => $Value)
     993      {
     994        $Output .= "'".$Key."', ";
     995      }
     996      $Output .= "],
     997        datasets: [{
     998          label: '',
     999          backgroundColor: window.chartColors.red,
     1000          borderColor: window.chartColors.red,
     1001          data: [";
     1002          foreach ($Values as $Key => $Value)
     1003          {
     1004            $Output .= "{ x: ".$Key.", ";
     1005            $Output .= "y: ".$Value." }, ";
     1006          }
     1007          $Output .= "
     1008          ],
     1009          fill: false,
     1010        }]
     1011      },
     1012      options: {
     1013        responsive: true,
     1014        title: {
     1015          display: true,
     1016          text: '".$Title."'
     1017        },
     1018        tooltips: {
     1019          mode: 'index',
     1020          intersect: false,
     1021        },
     1022        hover: {
     1023          mode: 'nearest',
     1024          intersect: true
     1025        },
     1026        scales: {
     1027          xAxes: [{
     1028                                                type: 'time',
     1029                                                display: true,
     1030                                                scaleLabel: {
     1031                                                        display: true,
     1032                                                        labelString: 'Date'
     1033                                                },
     1034                                                ticks: {
     1035                                                        major: {
     1036                                                                fontStyle: 'bold',
     1037                                                                fontColor: '#FF0000'
     1038                                                        }
     1039                                                }
     1040                                        }],
     1041          yAxes: [{
     1042                                                display: true,
     1043                                                scaleLabel: {
     1044                                                        display: true,
     1045                                                        labelString: 'value'
     1046                                                }
     1047          }]
     1048        }
     1049      }
     1050    };
     1051    var ".$ChartName." = new Chart(ctx, config);
     1052    </script>";
     1053    return $Output;
    9171054  }
    9181055
    … …  
    9371074    );
    9381075    $GlobalLocaleManager = $this->LocaleManager;
    939     $this->LinkLocaleExceptions = array('style');
     1076    $this->LinkLocaleExceptions = array('style', 'Packages');
    9401077
    9411078    $this->PathItems = $this->ProcessURL();
Note: See TracChangeset for help on using the changeset viewer.