function getPersons () {
#----------------------------------------------------------------------
global $personsFromPersons, $personsFromResultsWithoutId;
$personsFromResultsWithoutId = [];
$persons = dbQueryHandle("
SELECT DISTINCT personName, Results.countryId, year
FROM Results
JOIN Competitions ON Competitions.id=Results.competitionId
WHERE personId=''
ORDER BY personName
");
while( $row = mysql_fetch_row( $persons ))
$personsFromResultsWithoutId[] = $row;
mysql_free_result( $persons );
$personsFromPersons = [];
if (count( $personsFromResultsWithoutId )) {
$persons = dbQueryHandle("
SELECT id, name, countryId
FROM Persons
ORDER BY name
");
while( $row = mysql_fetch_row( $persons ))
$personsFromPersons[] = $row;
mysql_free_result( $persons );
}
}
function computeRanks ( $valueSource, $valueName ) {
#----------------------------------------------------------------------
startTimer();
echo "<br />Building table Ranks$valueName...<br />\n";
#--- Create empty table
dbCommand( "DROP TABLE IF EXISTS Ranks$valueName" );
dbCommand( "CREATE TABLE Ranks$valueName (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`personId` VARCHAR(10) NOT NULL DEFAULT '',
`eventId` VARCHAR(6) NOT NULL DEFAULT '',
`best` INTEGER NOT NULL DEFAULT '0',
`worldRank` INTEGER NOT NULL DEFAULT '0',
`continentRank` INTEGER NOT NULL DEFAULT '0',
`countryRank` INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_persons` (`personId`),
KEY `fk_events` (`eventId`)) COLLATE latin1_swedish_ci
" );
#--- Determine everybody's current country and continent
$persons = dbQuery( "
SELECT person.id personId, countryId, continentId
FROM Persons person, Countries country
WHERE country.id=countryId
AND person.subId=1
" );
foreach( $persons as $person ) {
extract( $person );
$currentCountry [$personId] = $countryId;
$currentContinent[$personId] = $continentId;
}
unset( $persons );
#--- Get all personal records (note: country-switchers appear once for each country)
$personalRecords = dbQueryHandle( "
SELECT personId, countryId, continentId, eventId, min($valueSource) value
FROM Concise${valueName}Results
WHERE eventId <> '333mbo'
GROUP BY personId, countryId, eventId
ORDER BY eventId, value
" );
#--- Process the personal records
$missingPersonIDs = false;
while( $row = mysql_fetch_row( $personalRecords )){
list( $personId, $countryId, $continentId, $eventId, $value ) = $row;
if( ! $personId ){
$missingPersonIDs = true;
continue;
}
#--- At new event, store the ranks of the previous and reset
if ( isset($latestEventId) && $eventId != $latestEventId ) {
storeRanks( $valueName, $latestEventId, $personRecord, $personWR, $personCR, $personNR );
unset( $ctr, $rank, $record, $ranked, $personRecord, $personWR, $personCR, $personNR );
}
#--- Update the region states (unless we have ranked this person there already, for
#--- example 2008SEAR01 twice in North America and World because of his two countries)
foreach( array( 'World', $continentId, $countryId ) as $region ){
if ( ! isset($ranked[$region][$personId]) ) {
$ctr[$region] = isset($ctr[$region]) ? $ctr[$region] + 1 : 1; # ctr always increases
if ( !isset($record[$region]) || $value > $record[$region] ) # rank only if value worse than previous
$rank[$region] = $ctr[$region];
$record[$region] = $value;
$ranked[$region][$personId] = true;
}
}
#--- Set the person's data (first time the current location is matched)
if ( ! isset($personRecord[$personId]) ) {
$personRecord[$personId] = $value;
$personWR[$personId] = $rank['World'];
}
if ( $continentId==$currentContinent[$personId] && ! isset($personCR[$personId]) )
$personCR[$personId] = $rank[$continentId];
if ( $countryId==$currentCountry[$personId] && ! isset($personNR[$personId]) )
$personNR[$personId] = $rank[$countryId];
$latestEventId = $eventId;
}
#--- Free the result handle
mysql_free_result( $personalRecords );
#--- Store the ranks of the last event
storeRanks( $valueName, $latestEventId, $personRecord, $personWR, $personCR, $personNR );
if( $missingPersonIDs )
noticeBox3( 0, 'Warning: some results are ignored because they are missing a personId' );
stopTimer( "Ranks$valueName" );
echo "... done<br /><br />\n";
}
function checkRelatively()
{
#----------------------------------------------------------------------
global $competitionCondition, $chosenWhich;
echo "<hr /><p>Checking <b>" . $chosenWhich . " ranks</b>... (wait for the result message box at the end)</p>\n";
#--- Get all results (except the trick-duplicated (old) multiblind)
$rows = dbQueryHandle("\n SELECT result.id, competitionId, eventId, roundId, formatId, average, best, pos, personName\n FROM Results result, Competitions competition\n WHERE competition.id = competitionId\n {$competitionCondition}\n AND (( eventId <> '333mbf' ) OR (( competition.year = 2009 ) AND ( competition.month > 1 )) OR ( competition.year > 2009 ))\n ORDER BY year desc, month desc, day desc, competitionId, eventId, roundId, IF(formatId IN ('a','m') AND average>0, average, 2147483647), if(best>0, best, 2147483647), pos\n ");
#--- Begin the form
echo "<form action='check_results_ACTION.php' method='post'>\n";
#--- Check the pos values
$prevRound = $shownRound = '';
$wrongs = $wrongRounds = 0;
$wrongComp = array();
while ($row = mysql_fetch_row($rows)) {
list($resultId, $competitionId, $eventId, $roundId, $formatId, $average, $best, $storedPos, $personName) = $row;
$round = "{$competitionId}|{$eventId}|{$roundId}";
if ($formatId == 'm' || $formatId == 'a') {
$result = "{$average}|{$best}";
} else {
$result = $best;
}
if ($round != $prevRound) {
$ctr = $calcedPos = 1;
}
if ($ctr > 1 && $result != $prevResult) {
$calcedPos = $ctr;
}
if ($storedPos != $calcedPos) {
#--- Before the first difference in a round, show the round's full results
if ($round != $shownRound) {
$wrongRounds++;
$wrongComp[$competitionId] = true;
echo "<p style='margin-top:2em; margin-bottom:0'><a href='https://www.worldcubeassociation.org/results/c.php?i={$competitionId}&allResults=1#e{$eventId}_{$roundId}'>{$competitionId} - {$eventId} - {$roundId}</a></p>";
showCompetitionResults($competitionId, $eventId, $roundId);
$shownRound = $round;
#--- Show a check all and a check none button.
printf("<button class='js-check-all' data-round='{$round}'>Check all</button>");
printf("<button class='js-check-none' data-round='{$round}'>Check none</button>");
printf("<br>");
}
#--- Show each difference, with a checkbox to agree
$change = sprintf('%+d', $calcedPos - $storedPos);
$checkbox = "<input type='checkbox' name='setpos{$resultId}' value='{$calcedPos}' data-round='{$round}' />";
printf("{$checkbox} Place {$storedPos} should be place {$calcedPos} (change by {$change}) --- {$personName}<br />");
$wrongs++;
}
$prevRound = $round;
$prevResult = $result;
$ctr++;
}
mysql_free_result($rows);
#--- Tell the result.
$date = wcaDate();
noticeBox2(!$wrongs, "We agree about all checked places.<br />{$date}", "<p>Darn! We disagree: {$wrongs} possibly wrong places, in {$wrongRounds} rounds, in " . count($wrongComp) . " competitions<br /><br />{$date}</p>" . "<p>Choose the changes you agree with above, then click the 'Execute...' button below. It will result in something like the following.</p>" . "<pre>I'm doing this:\n" . "UPDATE Results SET pos=111 WHERE id=11111\n" . "UPDATE Results SET pos=222 WHERE id=22222\n" . "UPDATE Results SET pos=333 WHERE id=33333\n" . "</pre>");
#--- If differences were found, offer to fix them.
if ($wrongs) {
echo "<center><input type='submit' value='Execute the agreed changes!' /></center>\n";
}
#--- Finish the form.
echo "</form>\n";
}
function computeRegionalRecordMarkers($valueId, $valueName)
{
#----------------------------------------------------------------------
global $chosenAnything, $chosenCompetitionId, $differencesWereFound, $previousRecord, $pendingCompetitions, $startDate;
# -----------------------------
# Description of the main idea:
# -----------------------------
# Get all results that are potential regional records. Process them one
# event at a time. Inside, process them one competition at a time, in
# chronological order of start date. Each competition's results are only
# compared against records of strictly previous competitions, not against
# parallel competitions. For this, there are these main arrays:
#
# - $previousRecord[regionId] is a running collection of each region's record,
# covering all competitions *before* the current one.
#
# - $record[regionId] is based on $previousRecord and is used and updated
# inside the current competition.
#
# - $pendingCompetitions[regionId] holds $record of competitions already
# processed but not merged into $previousRecord. When a new competition is
# encountered, we merge those that ended before the new one into $previousRecord.
#
# - $baseRecord[eventId][regionId] is for when a user chose a specific
# competition to check. Then we quickly ask the database for the current
# region records from before that competition. This could be used for
# giving the user a year-option as well, but we don't have that (yet?).
# -----------------------------
#--- If a competition was chosen, we need all records from before it
if ($chosenCompetitionId) {
$startDate = getCompetitionValue($chosenCompetitionId, "year*10000 + month*100 + day");
$results = dbQueryHandle("\n SELECT eventId, result.countryId, continentId, min({$valueId}) value, event.format valueFormat\n FROM Results result, Competitions competition, Countries country, Events event\n WHERE {$valueId} > 0\n AND competition.id = result.competitionId\n AND country.id = result.countryId\n AND event.id = result.eventId\n AND year*10000 + if(endMonth,endMonth,month)*100 + if(endDay,endDay,day) < {$startDate}\n GROUP BY eventId, countryId");
while ($row = mysql_fetch_row($results)) {
list($eventId, $countryId, $continentId, $value, $valueFormat) = $row;
if (isSuccessValue($value, $valueFormat)) {
foreach (array($countryId, $continentId, 'World') as $regionId) {
if (!isset($baseRecord[$eventId][$regionId]) || $value < $baseRecord[$eventId][$regionId]) {
$baseRecord[$eventId][$regionId] = $value;
}
}
}
}
mysql_free_result($results);
} else {
$competitions = dbQuery("\n SELECT id, year*10000 + if(endMonth,endMonth,month)*100 + if(endDay,endDay,day) endDate\n FROM Competitions competition");
foreach ($competitions as $competition) {
$endDate[$competition['id']] = $competition['endDate'];
}
}
#--- The IDs of relevant results (those already marked as region record and those that could be)
$queryRelevantIds = "\n (SELECT id FROM Results WHERE regional{$valueName}Record<>'' " . eventCondition() . competitionCondition() . ")\n UNION\n (SELECT id\n FROM\n Results result,\n (SELECT eventId, competitionId, roundId, countryId, min({$valueId}) value\n FROM Results\n WHERE {$valueId} > 0\n " . eventCondition() . competitionCondition() . "\n GROUP BY eventId, competitionId, roundId, countryId) helper\n WHERE result.eventId = helper.eventId\n AND result.competitionId = helper.competitionId\n AND result.roundId = helper.roundId\n AND result.countryId = helper.countryId\n AND result.{$valueId} = helper.value)";
#--- Get the results, ordered appropriately
$results = dbQueryHandle("\n SELECT\n year*10000 + month*100 + day startDate,\n result.id resultId,\n result.eventId,\n result.competitionId,\n result.roundId,\n result.personId,\n result.personName,\n result.countryId,\n result.regional{$valueName}Record storedMarker,\n {$valueId} value,\n continentId,\n continent.recordName continentalRecordName,\n event.format valueFormat\n FROM\n ({$queryRelevantIds}) relevantIds,\n Results result,\n Competitions competition,\n Countries country,\n Continents continent,\n Events event,\n Rounds round\n WHERE 1\n AND result.id = relevantIds.id\n AND competition.id = result.competitionId\n AND round.id = result.roundId\n AND country.id = result.countryId\n AND continent.id = country.continentId\n AND event.id = result.eventId\n ORDER BY event.rank, startDate, competitionId, round.rank, {$valueId}\n ");
#--- For displaying the dates, fetch all competitions
$allCompetitions = array();
foreach (dbQuery("SELECT * FROM Competitions") as $row) {
$allCompetitions[$row['id']] = $row;
}
#--- Process each result.
$currentEventId = $announcedEventId = $announcedRoundId = $announcedCompoId = NULL;
while ($row = mysql_fetch_row($results)) {
list($startDate, $resultId, $eventId, $competitionId, $roundId, $personId, $personName, $countryId, $storedMarker, $value, $continentId, $continentalRecordName, $valueFormat) = $row;
#--- Handle failures of multi-attempts.
if (!isSuccessValue($value, $valueFormat)) {
continue;
}
#--- At new events, reset everything
if ($eventId != $currentEventId) {
$currentEventId = $eventId;
$currentCompetitionId = false;
$record = $previousRecord = isset($baseRecord[$eventId]) ? $baseRecord[$eventId] : array();
$pendingCompetitions = array();
}
#--- Handle new competitions.
if ($competitionId != $currentCompetitionId) {
#--- Add the records of the previously current competition to the set of pending competition records
if ($currentCompetitionId) {
$pendingCompetitions[] = array($endDate[$currentCompetitionId], $record);
}
#--- Note the current competition
$currentCompetitionId = $competitionId;
#--- Prepare the records this competition will be based on
$pendingCompetitions = array_filter($pendingCompetitions, "handlePendingCompetition");
$record = $previousRecord;
}
#--- Calculate whether it's a new region record and update the records.
$calcedMarker = '';
if (!isset($record[$countryId]) || $value <= $record[$countryId]) {
$calcedMarker = 'NR';
$record[$countryId] = $value;
if (!isset($record[$continentId]) || $value <= $record[$continentId]) {
$calcedMarker = $continentalRecordName;
$record[$continentId] = $value;
if (!isset($record['World']) || $value <= $record['World']) {
$calcedMarker = 'WR';
$record['World'] = $value;
}
}
}
#--- If stored or calculated marker say it's some regional record at all...
if ($storedMarker || $calcedMarker) {
#--- Do stored and calculated agree? Choose colors and update list of differences.
$same = $storedMarker == $calcedMarker;
$storedColor = $same ? '999' : 'F00';
$calcedColor = $same ? '999' : '0E0';
if (!$same) {
$selectedIds[] = $resultId;
$differencesWereFound = true;
}
#--- If no filter was chosen, only show differences.
if (!$chosenAnything && $same) {
continue;
}
#--- Highlight regions if the calculated marker thinks it's a record for them.
$countryName = $countryId;
$continentName = substr($continentId, 1);
$worldName = 'World';
if ($calcedMarker) {
$countryName = "<b>{$countryName}</b>";
}
if ($calcedMarker && $calcedMarker != 'NR') {
$continentName = "<b>{$continentName}</b>";
}
if ($calcedMarker == 'WR') {
$worldName = "<b>{$worldName}</b>";
}
#--- Recognize new events/rounds/competitions.
$announceEvent = $eventId != $announcedEventId;
$announcedEventId = $eventId;
$announceRound = $roundId != $announcedRoundId;
$announcedRoundId = $roundId;
$announceCompo = $competitionId != $announcedCompoId;
$announcedCompoId = $competitionId;
#--- If new event, announce it.
if ($announceEvent) {
tableCaption(false, "{$eventId} {$valueName}");
tableHeader(explode('|', 'Date|Competition|Round|Person|Event|Country|Continent|World|Value|Stored|Computed|Agree'), array(7 => "class='R2'"));
}
#--- If new round/competition inside an event, add a separator row.
if (($announceRound || $announceCompo) && !$announceEvent) {
tableRowEmpty();
}
#--- Prepare the checkbox.
$checkbox = "<input type='checkbox' name='update{$valueName}{$resultId}' value='{$calcedMarker}' />";
#--- Show the result.
tableRow(array(competitionDate($allCompetitions[$competitionId]), competitionLink($competitionId, $competitionId), $roundId, personLink($personId, $personName), $eventId, $countryName, $continentName, $worldName, formatValue($value, $valueFormat), "<span style='font-weight:bold;color:#{$storedColor}'>{$storedMarker}</span>", "<span style='font-weight:bold;color:#{$calcedColor}'>{$calcedMarker}</span>", $same ? '' : $checkbox));
}
}
mysql_free_result($results);
}
function getIso2FromCountryId($countryId) {
#----------------------------------------------------------------------
$country = dbQueryHandle("
SELECT iso2
FROM Countries
WHERE id='$countryId'
");
$row = mysql_fetch_row( $country );
if ( !$row ){
showErrorMessage( "'$countryId' is not a known country ID'" );
return null;
}
return $row[0];
}
function buildGraph($eventName, $eventId, $divide)
{
#----------------------------------------------------------------------
#pretty("buildGraph( $eventName, $eventId, $divide ) ...");
$series = array();
$highestCurrent = 0;
$yMax = 0;
#--- Build the series of this event
foreach (array('1 best green', '1 average green', '10 best blue', '10 average blue', '100 best red', '100 average red') as $lineData) {
list($n, $valueId, $color) = explode(' ', $lineData);
#--- Get this event's results
$rows = dbQueryHandle("\n SELECT personId, {$valueId} value, year + datediff(year*10000+month*100+day,year*10000+101)/365.25 date\n FROM Results result, Competitions competition\n WHERE competition.id = competitionId\n AND eventId = '{$eventId}' AND {$valueId} > 0\n ORDER BY year, month, day\n ");
// Gather the data
$xdata = array();
// x-coordinates for the line-points of the graph
$ydata = array();
// y-coordinates for the line-points of the graph
$data = array();
// coordinates for the line-points of the graph
$records = array();
// $records[$personId] = record of that person
$nthPlaceRecord = false;
// record of the n-th place person
$direction = $eventId == '333mbf' ? -1 : 1;
while ($row = mysql_fetch_row($rows)) {
list($personId, $value, $date) = $row;
if ($eventId == '333mbf') {
$value = ($value - $value % 10000000) / 10000000 - 99;
}
if ($eventId == '333fm' && $valueId == 'average') {
$value /= 100;
}
# if ( $eventId == '333mbf' ) echo "$value ";
//--- Skip values worse than n-th place
if ($nthPlaceRecord && $value >= $nthPlaceRecord) {
continue;
}
//--- Update this person's record
$oldRecord = isset($records[$personId]) ? $records[$personId] : 1000000000;
if ($value < $oldRecord) {
$records[$personId] = $value;
}
//--- If we just reached n persons or this person just entered the top n...
if (!$nthPlaceRecord && count($records) == $n || $oldRecord >= $nthPlaceRecord && $value < $nthPlaceRecord) {
#echo "$nthPlaceRecord $oldRecord $value<br />";
//--- Sort the records, determine the n-th place value, and just keep the top n
asort($records);
$keep = array();
foreach ($records as $personId => $rec) {
$nthPlaceRecord = $keep[$personId] = $rec;
if (count($keep) == $n) {
break;
}
}
$records = $keep;
//--- Remove previous line points for the same date
while (end($xdata) == $date) {
array_pop($xdata);
array_pop($ydata);
array_pop($data);
}
if ($nthPlaceRecord * $direction == end($ydata)) {
continue;
}
//--- Add the line point
$xdata[] = $date;
$ydata[] = $nthPlaceRecord * $direction;
$data[] = array(round($date, 2), $nthPlaceRecord * $direction);
#printf( "<pre>%6.2f %6.2f</pre>", $date, $nthPlaceRecord );
#echo "<pre>"; print_r( $records ); echo "</pre>";
}
}
//--- Free the query handle
mysql_free_result($rows);
//--- Skip if we didn't even find n persons
if (!$nthPlaceRecord) {
continue;
}
//--- Remember the worst current shown record for later y-scale adjustment (worst will be at 1/3 height)
$highestCurrent = max($highestCurrent, $nthPlaceRecord * $direction);
#echo "<p>$eventName: $highestCurrent</p>";
//--- Repeat the last height in the far future to finish with a straight line
$xdata[] = 2222;
$ydata[] = end($ydata);
$data[] = array(2222, end($ydata));
$yMax = max($yMax, max($ydata));
$valueName = $valueId == 'best' ? 'Single' : 'Average';
$series[] = "{ name: '#{$n} {$valueName}', color:'{$color}', data: " . json_encode($data) . '}';
}
#--- Build and return the javascript for this event's graph
$chartJs = file_get_contents('charts_template.js');
$chartJs = str_replace('$eventId', $eventId, $chartJs);
$chartJs = str_replace('$xMax', 1970 + time() / 60 / 60 / 24 / 365.25, $chartJs);
$chartJs = str_replace('$yMax', min($yMax, ($direction > 0 ? 3 : 1) * $highestCurrent), $chartJs);
$chartJs = str_replace('$divide', $divide, $chartJs);
$chartJs = str_replace('$eventName', json_encode(eventName($eventId)), $chartJs);
$chartJs = str_replace('$series', implode(",\n ", $series), $chartJs);
return $chartJs;
}
