function add_333_consecutive_sub20()
{
global $lists, $WHERE;
#--- Get ...
$results = dbQuery("\n SELECT personName, personId, value1, value2, value3, value4, value5\n FROM Results result, Competitions competition\n {$WHERE} 1\n AND eventId = '333'\n AND competition.id = competitionId\n ORDER BY personId, year, month, day, roundId\n ");
foreach (structureBy($results, 'personId') as $personResults) {
extract($personResults[0]);
#--- Collect all values of this person, add a DNF at the end.
$values = array();
foreach ($personResults as $personResult) {
foreach (range(1, 5) as $i) {
$v = $personResult["value{$i}"];
if ($v > 0 || $v == -1) {
$values[] = $v;
}
}
}
$values[] = -1;
#--- Find longest streak.
$streak = array();
$bestStreak = array();
foreach ($values as $v) {
if ($v > 0 && $v < 2000) {
$streak[] = $v;
} else {
if (count($streak) >= count($bestStreak)) {
$bestStreak = $streak;
}
$ongoingStreak = $streak;
$streak = array();
}
}
#--- Build the data for the person.
if ($bestStreak) {
$persons[] = analyzeSub20Streak($personId, $personName, $bestStreak);
}
if ($ongoingStreak) {
$ongoing[] = analyzeSub20Streak($personId, $personName, $ongoingStreak);
}
}
#--- Sort and cut.
usort($persons, 'compareSub20Streaks');
usort($ongoing, 'compareSub20Streaks');
$persons = array_slice($persons, 0, 10);
$ongoing = array_slice($ongoing, 0, 10);
$persons = array_map('formatSub20Streak', $persons);
$ongoing = array_map('formatSub20Streak', $ongoing);
#--- Specify the list.
$lists[] = array("sub20_streak_3x3", "3x3x3 longest sub20 streak", "all-time / ongoing", "[P] Person [N] Length [N] Best [N] Average [N] Worst [T] | [P] Person [N] Length [N] Best [N] Average [N] Worst [T]", my_merge($persons, $ongoing));
}
<?php
for ($s = 9; $s >= 5; $s--) {
$temp[] = dbQuery("\n SELECT *\n FROM (\n SELECT personId,\n sum((value1 between 1 and {$s}99) + (value2 between 1 and {$s}99) + (value3 between 1 and {$s}99) + (value4 between 1 and {$s}99) + (value5 between 1 and {$s}99) ) ctr\n FROM (SELECT * FROM Results WHERE eventId='333' AND best<1000) helper\n GROUP BY personId) helper2\n WHERE ctr > 0\n ORDER BY ctr DESC, personId\n LIMIT 10\n ");
}
$lists[] = array("subx_3x3_solves", "Most Sub-X solves in Rubik's Cube", "", "[P] Name [N] <10 [T] | [P] Name [N] <9 [T] | [P] Name [N] <8 [T] | [P] Name [N] <7 [T] | [P] Name [N] <6", my_merge($temp[0], $temp[1], $temp[2], $temp[3], $temp[4]), "");
<?php
if (!isset($onlyProvideFunctions)) {
#--- Get event ranks
$ranksSingle = getRanks('Single');
$ranksAverage = getRanks('Average');
#--- Sum of 3x3/4x4/5x5 ranks, single and average
list($single) = sumOfRanks('Single', array('333', '444', '555'), $ranksSingle);
list($average) = sumOfRanks('Average', array('333', '444', '555'), $ranksAverage);
$lists[] = array("sum_ranks_345", "Sum of 3x3/4x4/5x5 ranks", "Single | Average", "[P] Person [N] Sum [n] 3x3 [n] 4x4 [n] 5x5 [T] | [P] Person [N] Sum [n] 3x3 [n] 4x4 [n] 5x5", my_merge($single, $average));
#--- Sum of all single ranks
list($rows, $header) = sumOfRanks('Single', getAllEventIds(), $ranksSingle);
$lists[] = array("sum_ranks_single", "Sum of all single ranks", "", $header, $rows);
#--- Sum of all average ranks
list($rows, $header) = sumOfRanks('Average', getAllEventIds(), $ranksAverage);
$lists[] = array("sum_ranks_average", "Sum of all average ranks", "", $header, $rows);
}
#----------------------------------------------------------------------
function getRanks($sourceName, $regionId = '')
{
#----------------------------------------------------------------------
#--- Build query for the requested region
$query = "SELECT eventId, personId, worldRank FROM Ranks{$sourceName}";
if (in_array($regionId, getAllUsedCountriesIds())) {
$query = "SELECT eventId, personId, countryRank FROM Ranks{$sourceName}, Persons WHERE Persons.id=personId AND subId=1 AND countryId='{$regionId}'";
}
if (in_array($regionId, getAllUsedContinentIds())) {
$query = "SELECT eventId, personId, continentRank FROM Ranks{$sourceName}, Persons, Countries WHERE Persons.id=personId AND subId=1 AND Countries.id=countryId AND continentId='{$regionId}'";
}
#--- Process the personal records, build ranks[event][person]
foreach (dbQuery($query) as $row) {
<?php
$persons = dbQuery("\n SELECT personId, count(DISTINCT competition.countryId) numberOfCountries\n FROM Results result, Competitions competition\n {$WHERE} competition.id = competitionId\n AND competition.countryId NOT REGEXP '^X[A-Z]{1}\$'\n GROUP BY personId\n ORDER BY numberOfCountries DESC, personName\n LIMIT 10\n");
$events = dbQuery("\n SELECT eventId, count(DISTINCT competition.countryId) numberOfCountries\n FROM Results result, Competitions competition\n {$WHERE} competition.id = competitionId\n AND competition.countryId NOT REGEXP '^X[A-Z]{1}\$'\n GROUP BY eventId\n ORDER BY numberOfCountries DESC, eventId\n LIMIT 10\n");
$competitions = dbQuery("\n SELECT competitionId, count(DISTINCT result.countryId) numberOfCountries\n FROM Results result\n WHERE result.countryId NOT REGEXP '^X[A-Z]{1}\$'\n GROUP BY competitionId\n ORDER BY numberOfCountries DESC, competitionId\n LIMIT 10\n");
$lists[] = array("most_countries", "Most Countries", "", "[P] Person [N] Countries [T] | [E] Event [N] Countries [T] | [C] Competition [N] Countries", my_merge($persons, $events, $competitions), "[Person] In how many countries the person participated. [Event] In how many countries the event has been offered. [Competition] Of how many countries persons participated.");
static function json_array_merge($arr1, $arr2)
{
$keys = array_keys($arr2);
foreach ($keys as $key) {
if (isset($arr1[$key]) && is_array($arr1[$key]) && is_array($arr2[$key])) {
$arr1[$key] = my_merge($arr1[$key], $arr2[$key]);
} else {
$arr1[$key] = $arr2[$key];
}
}
return $arr1;
}
$averageBound = 1181;
#--- Fetch all results that have a chance to be in the top 100
$candidates = dbQuery("\n SELECT personId,\n value1, value2, value3, value4, value5,\n average\n FROM Results\n WHERE eventId='333' AND (best>0 AND best<={$singleBound} OR average>0 AND average<={$averageBound})\n");
#--- Extract (person,single) pairs and (person,average) pairs
foreach ($candidates as $candidate) {
if ($candidate['average'] > 0) {
$personAveragePairs[] = array($candidate['personId'], $candidate['average']);
}
for ($i = 1; $i <= 5; $i++) {
if ($candidate["value{$i}"] > 0) {
$personSinglePairs[] = array($candidate['personId'], $candidate["value{$i}"]);
}
}
}
#--- Build and add this statistic
$lists[] = array("appearances_top100_3x3", "Appearances in Rubik's Cube top 100 results", "Single | Average", "[P] Person [N] Appearances [T] | [P] Person [N] Appearances", my_merge(countTop100Appearances($personSinglePairs), countTop100Appearances($personAveragePairs)));
#--------------------------------------------------------------------------
# helper...
#--------------------------------------------------------------------------
function countTop100Appearances($personValuePairs)
{
usort($personValuePairs, create_function('$a,$b', 'return $a[1]-$b[1];'));
for ($i = 0; $i < 100 || $i < count($personValuePairs) && $personValuePairs[$i][1] == $personValuePairs[$i - 1][1]; $i++) {
if (!isset($appearances[$personValuePairs[$i][0]])) {
$appearances[$personValuePairs[$i][0]] = 0;
}
$appearances[$personValuePairs[$i][0]]++;
}
arsort($appearances);
foreach ($appearances as $personId => $counter) {
$result[] = array($personId, $counter);
<?php
#--- Get event ranks
$ranksSingle = getRecentRanks('best');
$ranksAverage = getRecentRanks('average');
#--- Sum of 3x3/4x4/5x5 ranks, single and average
list($single) = sumOfRecentRanks('Single', array('333', '444', '555'), $ranksSingle);
list($average) = sumOfRecentRanks('Average', array('333', '444', '555'), $ranksAverage);
$lists[] = array("sum_recent_ranks_345", "Sum of recent 3x3/4x4/5x5 ranks", "Single | Average - considering results since {$sinceDateHtml}", "[P] Person [N] Sum [n] 3x3 [n] 4x4 [n] 5x5 [T] | [P] Person [N] Sum [n] 3x3 [n] 4x4 [n] 5x5", my_merge($single, $average));
#--- Sum of all single ranks
list($rows, $header) = sumOfRecentRanks('Single', getAllEventIds(), $ranksSingle);
$lists[] = array("sum_recent_ranks_single", "Sum of recent single ranks", "considering results since {$sinceDateHtml}", $header, $rows);
#--- Sum of all average ranks
list($rows, $header) = sumOfRecentRanks('Average', getAllEventIds(), $ranksAverage);
$lists[] = array("sum_recent_ranks_average", "Sum of recent average ranks", "considering results since {$sinceDateHtml}", $header, $rows);
#----------------------------------------------------------------------
function getRecentRanks($sourceId)
{
#----------------------------------------------------------------------
global $WHERE, $sinceDateCondition;
#--- Get all personal records sorted by event and value
$records = dbQuery("\n SELECT personId, eventId, min({$sourceId}) value\n FROM Results result, Competitions competition\n {$WHERE} competition.id=competitionId\n AND {$sinceDateCondition}\n AND {$sourceId}>0\n GROUP BY eventId, personId\n ORDER BY eventId, value\n ");
#--- Append a sentinel
$records[] = array('nobody', 'ThisWillCauseTheFinishEventCodeForTheLastEvent');
#--- Process the personal records, build ranks[event][person]
foreach ($records as $record) {
list($personId, $eventId, $value) = $record;
#--- At new events, finish the previous and reset for the new one
if ($eventId != $currentEventId) {
#--- Memorize the previous event's ranks (if any, and if that event is official)
if ($currentEventId && in_array($currentEventId, getAllEventIds())) {
<?php
$events = dbQuery("\n SELECT eventId, count(DISTINCT personId) numberOfPersons\n FROM Results\n {$WHERE} 1\n GROUP BY eventId\n ORDER BY numberOfPersons DESC, eventId\n LIMIT 10\n");
$competitions = dbQuery("\n SELECT competitionId, count(DISTINCT personId) numberOfPersons\n FROM Results\n {$WHERE} 1\n GROUP BY competitionId\n ORDER BY numberOfPersons DESC, competitionId\n LIMIT 10\n");
$countries = dbQuery("\n SELECT countryId, count(DISTINCT personId) numberOfPersons\n FROM Results\n {$WHERE} 1\n GROUP BY countryId\n ORDER BY numberOfPersons DESC, countryId\n LIMIT 10\n");
$lists[] = array("most_persons", "Most Persons", "", "[E] Event [N] Persons [T] | [C] Competition [N] Persons [T] | [T] Country [N] Persons", my_merge($events, $competitions, $countries), "[Event] How many persons participated in the event. [Competition] How many persons participated in the competition. [Country] How many citizens of this country participated.");
<?php
$solves = polishMostSolvesAttempts("\n SELECT personId,\n competitionId whereId,\n count(value1>0 or null)+\n count(value2>0 or null)+\n count(value3>0 or null)+\n count(value4>0 or null)+\n count(value5>0 or null) solves,\n count(value1 and value1<>-2 or null)+\n count(value2 and value2<>-2 or null)+\n count(value3 and value3<>-2 or null)+\n count(value4 and value4<>-2 or null)+\n count(value5 and value5<>-2 or null) attempts\n FROM Results\n GROUP BY personId, competitionId\n ORDER BY solves DESC, attempts\n LIMIT 50\n");
$attempts = polishMostSolvesAttempts("\n SELECT personId,\n year whereId,\n count(value1>0 or null)+\n count(value2>0 or null)+\n count(value3>0 or null)+\n count(value4>0 or null)+\n count(value5>0 or null) solves,\n count(value1 and value1<>-2 or null)+\n count(value2 and value2<>-2 or null)+\n count(value3 and value3<>-2 or null)+\n count(value4 and value4<>-2 or null)+\n count(value5 and value5<>-2 or null) attempts\n FROM Results, Competitions competition\n WHERE competition.id = competitionId\n GROUP BY personId, year\n ORDER BY solves DESC, attempts\n LIMIT 50\n");
function polishMostSolvesAttempts($query)
{
$result = array();
foreach (dbQuery($query) as $row) {
list($personId, $whereId, $ctr, $attempts) = $row;
if (!isset($listed[$personId]) && count($result) < 10) {
$result[] = array($personId, "<b>{$ctr}</b> / {$attempts}", $whereId);
$listed[$personId] = true;
}
}
return $result;
}
$lists[] = array("most_solves", "Most solves in one competition or year", "", "[P] Person [n] Solves [C] Competition [T] | [P] Person [n] Solves [N] Year", my_merge($solves, $attempts));
<?php
$persons = dbQuery("\n SELECT personId, count(distinct eventId) worldRecordEvents\n FROM Results\n {$WHERE} regionalSingleRecord='WR' or regionalAverageRecord='WR'\n GROUP BY personId\n ORDER BY worldRecordEvents DESC, personName\n LIMIT 10\n");
$countries = dbQuery("\n SELECT countryId, count(distinct eventId) worldRecordEvents\n FROM Results\n {$WHERE} regionalSingleRecord='WR' or regionalAverageRecord='WR'\n GROUP BY countryId\n ORDER BY worldRecordEvents DESC, countryId\n LIMIT 10\n");
$competitions = dbQuery("\n SELECT competitionId, count(distinct eventId) worldRecordEvents\n FROM Results\n {$WHERE} regionalSingleRecord='WR' or regionalAverageRecord='WR'\n GROUP BY competitionId\n ORDER BY worldRecordEvents DESC, competitionId\n LIMIT 10\n");
$lists[] = array("wrs_in_most_events", "World records in most events", "current and past", "[P] Person [N] Events [T] | [C] Competition [N] Events [T] | [T] Country [N] Events", my_merge($persons, $competitions, $countries));
<?php
$person = dbQuery("\n SELECT personId, count(DISTINCT competitionId) numberOfCompetitions\n FROM Results\n GROUP BY personId\n ORDER BY numberOfCompetitions DESC, personId\n LIMIT 10\n");
$event = dbQuery("\n SELECT eventId, count(DISTINCT competitionId) numberOfCompetitions\n FROM Results\n GROUP BY eventId\n ORDER BY numberOfCompetitions DESC, eventId\n LIMIT 10\n");
$country = dbQuery("\n SELECT countryId, count(*) numberOfCompetitions\n FROM Competitions\n WHERE showAtAll\n AND datediff( year*10000+month*100+day, curdate() ) < 0\n GROUP BY countryId\n ORDER BY numberOfCompetitions DESC, countryId\n LIMIT 10\n");
$lists[] = array("most_competitions", "Most Competitions", "", "[P] Person [N] Competitions [T] | [E] Event [N] Competitions [T] | [T] Country [N] Competitions", my_merge($person, $event, $country), "[Person] In how many competitions the person participated. [Event] In how many competitions the event was included. [Country] How many competitions took place in the country.");
<?php
$just3x3 = dbQuery("\n SELECT\n personId,\n count(pos=1 or null) gold,\n count(pos=2 or null) silver,\n count(pos=3 or null) bronze\n FROM Results\n {$WHERE} roundId IN ('f', 'c') AND eventId='333'\n GROUP BY personId\n ORDER BY gold DESC, silver DESC, bronze DESC, personName\n LIMIT 10\n");
$overall = dbQuery("\n SELECT\n personId,\n count(pos=1 or null) gold,\n count(pos=2 or null) silver,\n count(pos=3 or null) bronze\n FROM Results\n {$WHERE} roundId IN ('f', 'c') AND best>0\n GROUP BY personId\n ORDER BY gold DESC, silver DESC, bronze DESC, personName\n LIMIT 10\n");
$lists[] = array("medal_collection", "Best \"medal collection\"", "3x3x3 and overall", "[P] Person [N] Gold [n] Silver [n] Bronze [T] | [P] Person [N] Gold [n] Silver [n] Bronze", my_merge($just3x3, $overall));