function showMatchingPersons()
{
#----------------------------------------------------------------------
global $chosenPatternHtml, $chosenPatternMysql, $chosenEventId, $chosenRegionId;
#--- If nothing chosen yet, display a help message.
if (!$chosenPatternHtml && !$chosenEventId && !$chosenRegionId) {
echo "<div style='width:85%; margin:auto; font-size:1.00em; font-weight:bold'><p>For the name field search, enter any name or name parts and don't worry about letter variations. For example, 'or joe' (enter without the quotes) will among others also find Joël van Noort.</p></div>";
return;
}
#--- The pattern should contain at least 2 non-whitespace characters.
if (!preg_match('/\\S.*\\S/', $chosenPatternHtml)) {
noticeBox3(-1, "Please Enter at least 2 characters.");
echo "<div style='width:85%; margin:auto; font-size:1.00em; font-weight:bold'><p>For the name field search, enter any name or name parts and don't worry about letter variations. For example, 'or joe' (enter without the quotes) will among others also find Joël van Noort.</p></div>";
return;
}
#--- Otherwise, build up a query to search for people.
global $wcadb_conn;
$params = array(0 => '');
$parts = array();
$rawPattern = getRawParamThisShouldBeAnException('pattern');
#--- Build the nameCondition (all searched parts must occur).
$nameCondition = "";
foreach (explode(' ', $rawPattern) as $namePart) {
$parts[$namePart] = '%' . $namePart . '%';
$params[0] .= 's';
$params[] =& $parts[$namePart];
$likeId = '';
if (ctype_alnum($namePart)) {
$params[0] .= 's';
$params[] =& $parts[$namePart];
$likeId = ' OR person.id LIKE ?';
}
$nameCondition .= ' AND (person.name LIKE ?' . $likeId . ')';
}
#--- Build the eventCondition (if any).
if ($chosenEventId) {
$eventConditionPart1 = ', (SELECT DISTINCT personId FROM ConciseSingleResults WHERE 1 ' . eventCondition() . ') result';
$eventConditionPart2 = 'AND person.id = result.personId';
} else {
$eventConditionPart1 = '';
$eventConditionPart2 = '';
}
#--- Do the query!
$query = 'SELECT DISTINCT person.id AS personId, person.name AS personName, country.name AS countryName
FROM Persons AS person, Countries AS country' . $eventConditionPart1 . ' WHERE ' . randomDebug() . $nameCondition . regionCondition('') . ' AND country.id = person.countryId' . $eventConditionPart2 . ' ORDER BY personName, countryName, personId';
$persons = $wcadb_conn->boundQuery($query, $params);
$count = count($persons);
$ext = $count != 1 ? 's' : '';
tableBegin('results', 3);
tableCaption(false, spaced(array("{$count} person{$ext} matching:", eventName($chosenEventId), chosenRegionName($chosenRegionId), $chosenPatternHtml ? "\"{$chosenPatternHtml}\"" : '')));
tableHeader(explode('|', 'Person|WCA id|Citizen of'), array(2 => 'class="f"'));
foreach ($persons as $person) {
extract($person);
tableRow(array(personLink($personId, $personName), $personId, $countryName));
}
tableEnd();
}
function showRegionalRecordsHistory()
{
#----------------------------------------------------------------------
global $chosenRegionId, $chosenHistory, $chosenMixHist;
#--- Compute the region condition and the normal record name.
if (preg_match('/^(world)?$/i', $chosenRegionId)) {
$regionCondition = "AND recordName = 'WR'";
$normalRecordName = '';
} elseif (preg_match('/^_/', $chosenRegionId)) {
$tmp = dbQuery("SELECT recordName FROM Continents WHERE id = '{$chosenRegionId}'");
$normalRecordName = $tmp[0][0];
$regionCondition = "AND recordName in ('WR', '{$normalRecordName}' ) AND continentId = '{$chosenRegionId}'";
} else {
$regionCondition = "AND (recordName <> '') AND (result.countryId = '{$chosenRegionId}')";
$normalRecordName = 'NR';
}
#--- Order: normal history or mixed?
$order = $chosenHistory ? 'event.rank, type, value, year desc, month desc, day desc, roundId desc' : 'year desc, month desc, day desc, roundId desc, event.rank, type, value';
#--- Get the results.
$results = dbQuery("\n SELECT\n year, month, day,\n\n event.id eventId,\n event.name eventName,\n event.cellName eventCellName,\n\n result.type type,\n result.value value,\n event.format valueFormat,\n recordName,\n\n result.personId personId,\n result.personName personName,\n\n country.name countryName,\n\n competition.id competitionId,\n competition.cellName competitionName,\n\n value1, value2, value3, value4, value5\n FROM\n (SELECT Results.*, 1 type, best value, regionalSingleRecord recordName FROM Results WHERE regionalSingleRecord<>'' UNION\n SELECT Results.*, 2 type, average value, regionalAverageRecord recordName FROM Results WHERE regionalAverageRecord<>'') result,\n Events event,\n Competitions competition,\n Countries country\n WHERE " . randomDebug() . "\n AND event.id = eventId\n AND event.rank < 1000\n AND competition.id = competitionId\n AND country.id = result.countryId\n {$regionCondition}\n " . eventCondition() . yearCondition() . "\n ORDER BY\n {$order}\n ");
#--- Start the table
if ($chosenHistory) {
tableBegin('results', 7);
} else {
tableBegin('results', 9);
tableHeader(explode('|', 'Date circa|Event|What|Single|Average|Person|Citizen of|Competition|Result Details'), array(3 => 'class="R2"', 4 => 'class="R2"', 8 => 'class="f"'));
}
#--- Process the results.
$currentEventId = false;
foreach ($results as $result) {
extract($result);
#--- Announce the event (only for normal history, not mixed)
if ($chosenHistory && $eventId != $currentEventId) {
$currentEventId = $eventId;
tableCaptionNew(false, $eventId, eventLink($eventId, $eventName));
tableHeader(explode('|', '|Single|Average|Person|Citizen of|Competition|Result Details'), array(1 => 'class="R2"', 2 => 'class="R2"', 6 => 'class="f"'));
}
#--- Determine how to display the record name.
if ($recordName != $normalRecordName) {
$recordName = "<span style='color:#f93;font-weight:bold'>{$recordName}</span>";
}
#--- Prepare the table row.
$data = array($recordName, $type == 1 ? formatValue($value, $valueFormat) : '', $type == 2 ? formatValue($value, $valueFormat) : '', personLink($personId, $personName), $countryName, competitionLink($competitionId, $competitionName), formatAverageSources($type == 2, $result, $valueFormat));
if ($chosenMixHist) {
array_unshift($data, sprintf('%4d-%02d-%02d', $year, $month, $day), eventLink($eventId, $eventCellName));
}
#--- Show the table row.
tableRow($data);
}
tableEnd();
}
function showResultsByEvents()
{
#----------------------------------------------------------------------
global $chosenPersonId;
$results = dbQuery("\n SELECT\n result.*,\n event.name eventName,\n competition.cellName competitionCellName,\n event.format valueFormat,\n round.cellName roundCellName\n FROM\n Results result,\n Events event,\n Competitions competition,\n Rounds round\n WHERE " . randomDebug() . "\n AND personId = '{$chosenPersonId}'\n AND event.id = eventId\n AND event.rank < 1000\n AND competition.id = competitionId\n AND round.id = roundId\n ORDER BY\n event.rank, year DESC, month DESC, day DESC, competitionCellName, round.rank DESC\n ");
tableBegin('results', 8);
tableCaption(false, "History (<a href='person_map.php?i={$chosenPersonId}'>Map</a>)");
#--- Process results by event.
foreach (structureBy($results, 'eventId') as $eventResults) {
extract($eventResults[0]);
#--- Announce the event.
tableCaptionNew(false, $eventId, eventLink($eventId, $eventName));
tableHeader(explode('|', 'Competition|Round|Place|Best||Average||Result Details'), array(2 => 'class="r"', 3 => 'class="R"', 5 => 'class="R"', 7 => 'class="f"'));
#--- Initialize.
$currentCompetitionId = '';
#--- Compute PB Markers
//$pbMarkers = [];
$bestBest = 9999999999;
$bestAverage = 9999999999;
foreach (array_reverse($eventResults) as $result) {
extract($result);
$pbMarkers[$competitionId][$roundCellName] = 0;
if ($best > 0 && $best <= $bestBest) {
$bestBest = $best;
$pbMarkers[$competitionId][$roundCellName] += 1;
}
if ($average > 0 && $average <= $bestAverage) {
$bestAverage = $average;
$pbMarkers[$competitionId][$roundCellName] += 2;
}
}
#--- Show the results.
foreach ($eventResults as $result) {
extract($result);
$isNewCompetition = $competitionId != $currentCompetitionId;
$currentCompetitionId = $competitionId;
$formatBest = formatValue($best, $valueFormat);
if ($pbMarkers[$competitionId][$roundCellName] % 2) {
$formatBest = "<span style='color:#F60;font-weight:bold'>{$formatBest}</span>";
}
$formatAverage = formatValue($average, $valueFormat);
if ($pbMarkers[$competitionId][$roundCellName] > 1) {
$formatAverage = "<span style='color:#F60;font-weight:bold'>{$formatAverage}</span>";
}
tableRowStyled($isNewCompetition ? '' : 'color:#AAA', array($isNewCompetition ? competitionLink($competitionId, $competitionCellName) : '', $roundCellName, $isNewCompetition ? "<b>{$pos}</b>" : $pos, $formatBest, $regionalSingleRecord, $formatAverage, $regionalAverageRecord, formatAverageSources(true, $result, $valueFormat)));
}
}
tableEnd();
}
function defineAllLists()
{
#----------------------------------------------------------------------
#--- Compute some helpers.
global $WHERE, $sinceDateHtml, $sinceDateMysql, $sinceDateCondition;
$WHERE = "WHERE " . randomDebug() . " AND";
list($year, $month, $day) = explode(' ', wcaDate("Y m d"));
$year = intval($year) - 1;
$month = intval($month);
$day = intval($day);
$monthName = getMonthName($month);
$sinceDateHtml = "{$monthName} {$day}, {$year}";
$sinceDateMysql = $year * 10000 + $month * 100 + $day;
$sinceDateCondition = "(year*10000 + month*100 + day) >= {$sinceDateMysql}";
#--- Import the list definitions.
require 'includes/statistics/ALL_LISTS.php';
}
function showHistoryOfContinentalRecords()
{
#----------------------------------------------------------------------
global $chosenPersonId;
$results = dbQuery("\n SELECT\n result.*,\n event.format valueFormat,\n event.cellName eventCellName,\n competition.cellName competitionCellName,\n round.cellName roundCellName\n FROM\n Results result,\n Competitions competition,\n Events event,\n Rounds round\n WHERE " . randomDebug() . "\n AND result.personId = '{$chosenPersonId}'\n AND ((result.regionalSingleRecord != '' AND result.regionalSingleRecord != 'NR' AND result.regionalSingleRecord != 'WR') OR (result.regionalAverageRecord != '' AND result.regionalAverageRecord != 'NR' AND result.regionalAverageRecord != 'WR'))\n AND event.id = result.eventId\n AND event.rank < 1000\n AND competition.id = result.competitionId\n AND round.id = result.roundId\n ORDER BY\n event.rank, year DESC, month DESC, day DESC, roundId DESC\n ");
if (!count($results)) {
return;
}
tableBegin('results', 6);
tableCaption(false, 'History of Continental Records');
tableHeader(explode('|', 'Event|Single|Average|Competition|Round|Result Details'), array(1 => "class='R2'", 2 => "class='R2'", 5 => "class='f'"));
foreach ($results as $result) {
extract($result);
if (isset($currentEventId) && $eventId != $currentEventId) {
tableRowEmpty();
}
tableRow(array(isset($currentEventId) && $eventId == $currentEventId ? '' : eventLink($eventId, $eventCellName), ($regionalSingleRecord == '' or $regionalSingleRecord == 'NR' or $regionalSingleRecord == 'WR') ? '' : formatValue($best, $valueFormat), ($regionalAverageRecord == '' or $regionalAverageRecord == 'NR' or $regionalAverageRecord == 'WR') ? '' : formatValue($average, $valueFormat), competitionLink($competitionId, $competitionCellName), $roundCellName, formatAverageSources($regionalAverageRecord != '' and $regionalAverageRecord != 'NR' and $regionalAverageRecord != 'WR', $result, $valueFormat)));
$currentEventId = $eventId;
}
tableEnd();
}
function showWorldChampionshipPodiums()
{
#----------------------------------------------------------------------
global $chosenPersonId;
$results = dbQuery("\n SELECT\n result.*,\n event.format valueFormat,\n event.cellName eventCellName,\n competition.cellName competitionCellName,\n year\n FROM\n Results result,\n Competitions competition,\n Events event\n WHERE " . randomDebug() . "\n AND best > 0\n AND pos <= 3\n AND roundId in ('f', 'c')\n AND competition.cellName like 'World Championship %'\n AND result.personId = '{$chosenPersonId}'\n AND event.id = result.eventId\n AND competition.id = result.competitionId\n AND event.rank < 1000\n ORDER BY\n year DESC, event.rank\n ");
if (!count($results)) {
return;
}
tableBegin('results', 6);
tableCaption(false, 'World Championship Podiums');
tableHeader(explode('|', 'Year|Event|Place|Single|Average|Result Details'), array(0 => "class='R2'", 2 => "class='R2'", 3 => "class='r'", 4 => "class='r'", 5 => "class='f'"));
$lastYear = 0;
foreach ($results as $result) {
extract($result);
if ($year < $lastYear) {
tableRowEmpty();
}
tableRow(array($year != $lastYear ? $year : '', eventLink($eventId, $eventCellName), competitionLink($competitionId, $pos, $eventId, $roundId), formatValue($best, $valueFormat), formatValue($average, $valueFormat), formatAverageSources(true, $result, $valueFormat)));
$lastYear = $year;
}
tableEnd();
}
function getCompetitionResults($competitionId, $eventId, $roundId)
{
#----------------------------------------------------------------------
# NOTE: This is mostly a copy of the same function in competition_results.php
$order = "event.rank, round.rank, pos, average, best, personName";
#--- Get and return the results.
return dbQuery("\n SELECT\n result.*,\n\n event.name eventName,\n round.name roundName,\n round.cellName roundCellName,\n format.name formatName,\n country.name countryName,\n\n event.cellName eventCellName,\n event.format valueFormat\n FROM\n Results result,\n Events event,\n Rounds round,\n Formats format,\n Countries country,\n Competitions competition\n WHERE " . randomDebug() . "\n AND competitionId = '{$competitionId}'\n AND competition.id = '{$competitionId}'\n AND eventId = '{$eventId}'\n AND event.id = '{$eventId}'\n AND roundId = '{$roundId}'\n AND round.id = '{$roundId}'\n AND format.id = formatId\n AND country.id = result.countryId\n AND (( event.id <> '333mbf' ) OR (( competition.year = 2009 ) AND ( competition.month > 1 )) OR ( competition.year > 2009 ))\n ORDER BY\n {$order}\n ");
}
function getCompetitionResults ( $competitionId, $eventId, $roundId ) {
#----------------------------------------------------------------------
# NOTE: This is mostly a copy of the same function in competition_results.php
$order = "event.rank, round.rank, pos, average, best, personName";
#--- Get and return the results.
return dbQuery("
SELECT
result.*,
event.name eventName,
round.name roundName,
round.cellName roundCellName,
format.name formatName,
country.name countryName,
event.cellName eventCellName,
event.format valueFormat
FROM
Results result,
Events event,
Rounds round,
Formats format,
Countries country,
Competitions competition
WHERE ".randomDebug()."
AND competitionId = '$competitionId'
AND competition.id = '$competitionId'
AND eventId = '$eventId'
AND event.id = '$eventId'
AND roundId = '$roundId'
AND round.id = '$roundId'
AND format.id = formatId
AND country.id = result.countryId
AND (( event.id <> '333mbf' ) OR (( competition.year = 2009 ) AND ( competition.month > 1 )) OR ( competition.year > 2009 ))
ORDER BY
$order
");
}
<?php
#----------------------------------------------------------------------
# Get the results...
#----------------------------------------------------------------------
$results = dbQuery("\n SELECT\n result.personId personId,\n result.personName personName,\n\n country.id countryId,\n country.name countryName,\n\n continent.id continentId,\n continent.name continentName,\n\n competition.id competitionId,\n competition.cellName competitionName,\n\n {$valueSource} value,\n event.format valueFormat,\n\n value1, value2, value3, value4, value5\n FROM\n (SELECT countryId recordCountryId, MIN({$valueSource}) recordValue\n FROM Concise{$valueName}Results result\n WHERE 1 {$eventCondition} {$yearCondition}\n GROUP BY countryId) record,\n Results result,\n Countries country,\n Continents continent,\n Competitions competition,\n Events event\n WHERE " . randomDebug() . "\n\n #--- Only record results.\n {$eventCondition}\n {$yearCondition}\n AND result.{$valueSource} = recordValue\n AND result.countryId = recordCountryId\n\n #--- Combine the other tables.\n AND country.id = result.countryId\n AND continent.id = continentId\n AND competition.id = competitionId\n AND event.id = eventId\n ORDER BY\n value, countryId, year, month, day, personName\n");
#----------------------------------------------------------------------
# Analyze the results.
#----------------------------------------------------------------------
#--- Collect national results, remember which continents to show, continental/world record values.
foreach ($results as $result) {
extract($result);
if (!$chosenRegionId || $countryId == $chosenRegionId || $continentId == $chosenRegionId) {
$result['regionName'] = $countryName;
$bestOfCountry[] = $result;
$showContinent[$continentId] = true;
}
if (!isset($bestValueOfWorld)) {
$bestValueOfWorld = $result['value'];
}
if (!isset($bestValueOfContinent[$continentId])) {
$bestValueOfContinent[$continentId] = $result['value'];
}
}
#--- Continental and world results.
foreach ($results as $result) {
extract($result);
$result['regionName'] = $continentName;
if (isset($showContinent[$continentId]) && $value == $bestValueOfContinent[$continentId]) {
$bestOfContinent[] = $result;
}
function regionsGetCurrentRecordsQuery($valueId, $valueName)
{
return "SELECT\n '{$valueName}' type,\n result.*,\n value,\n event.name eventName,\n event.cellName eventCellName,\n format,\n country.name countryName,\n competition.cellName competitionName,\n rank, year, month, day\n FROM\n (SELECT eventId recordEventId, MIN(valueAndId) DIV 1000000000 value\n FROM Concise{$valueName}Results\n WHERE 1 " . regionCondition('') . eventCondition() . yearCondition() . "\n GROUP BY eventId) record,\n Results result,\n Events event,\n Countries country,\n Competitions competition\n WHERE " . randomDebug() . "\n\n AND result.{$valueId} = value\n " . regionCondition('result') . eventCondition() . yearCondition() . "\n\n AND result.eventId = recordEventId\n AND event.id = result.eventId\n AND country.id = result.countryId\n AND competition.id = result.competitionId\n AND event.rank < 990";
}
function getCompetitionResults($resultsTable)
{
#----------------------------------------------------------------------
global $chosenCompetitionId, $chosenByPerson, $chosenAllResults, $chosenTop3, $chosenWinners;
#--- Some filter conditions depending on the view (winners, top3, all).
$viewCondition = "";
if ($chosenTop3) {
$viewCondition = "AND roundId in ('f', 'c') AND pos <= 3 AND best > 0";
}
if ($chosenWinners) {
$viewCondition = "AND roundId in ('f', 'c') AND pos <= 1 AND best > 0";
}
if ($chosenByPerson) {
$order = "personName, event.rank, round.rank DESC";
} else {
$order = "event.rank, round.rank, pos, average, best, personName";
}
#--- Get and return the results.
return dbQuery("\n SELECT\n result.*,\n \n event.name eventName,\n round.name roundName,\n round.cellName roundCellName,\n format.name formatName,\n country.name countryName,\n\n event.cellName eventCellName,\n event.format valueFormat\n FROM\n {$resultsTable} result,\n Events event,\n Rounds round,\n Formats format,\n Countries country,\n Competitions competition\n WHERE " . randomDebug() . "\n AND competitionId = '{$chosenCompetitionId}'\n AND competition.id = '{$chosenCompetitionId}'\n AND event.id = eventId\n AND round.id = roundId\n AND format.id = formatId\n AND country.id = result.countryId\n AND (( event.id <> '333mbf' ) OR (( competition.year = 2009 ) AND ( competition.month > 1 )) OR ( competition.year > 2009 ))\n {$viewCondition}\n ORDER BY\n {$order}\n ");
}
<?php
#--- Compute $results = ( value, personId, personName, countryName, competitionId, competitioName, [value1-5] ).
if ($chosenAverage) {
$results = dbQuery("\n SELECT\n result.*,\n result.{$valueSource} value,\n competition.cellName competitionName,\n country.name countryName\n FROM\n Results result,\n Competitions competition,\n Countries country\n WHERE " . randomDebug() . "\n AND average>0 {$eventCondition} {$yearCondition} {$regionCondition}\n AND competition.id = competitionId\n AND country.id = result.countryId\n ORDER BY\n value, best, personName, competitionId, roundId\n {$limitCondition}\n ");
} else {
for ($i = 1; $i <= 5; $i++) {
$subqueryParts[] = "SELECT value{$i} value, personId, personName, country.name countryName, competitionId, competition.cellName competitionName, roundId\n FROM Results result,\n Competitions competition,\n Countries country\n WHERE " . randomDebug() . "\n AND value{$i}>0 {$eventCondition} {$yearCondition} {$regionCondition}\n AND competition.id = competitionId\n AND country.id = result.countryId\n ORDER BY value, personName\n {$limitCondition}";
}
$subquery = '(' . implode(') UNION ALL (', $subqueryParts) . ')';
$results = dbQuery("\n SELECT *\n FROM ({$subquery}) result\n ORDER BY value, personName, competitionId, roundId\n {$limitCondition}\n ");
}
<?php
#--- Get the results.
$results = dbQuery("\n SELECT\n result.*,\n result.{$valueSource} value,\n competition.cellName competitionName,\n country.name countryName\n FROM\n (SELECT MIN(valueAndId) valueAndId\n FROM Concise{$valueName}Results result\n WHERE {$valueSource}>0 {$eventCondition} {$yearCondition} {$regionCondition}\n GROUP BY personId\n ORDER BY valueAndId\n {$limitCondition}) top,\n Results result,\n Competitions competition,\n Countries country\n WHERE " . randomDebug() . "\n AND result.id = valueAndId % 1000000000\n AND competition.id = competitionId\n AND country.id = result.countryId\n ORDER BY\n value, personName\n");
