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 showResults()
{
#----------------------------------------------------------------------
global $chosenEventId, $chosenRegionId, $chosenYears, $chosenShow, $chosenSingle, $chosenAverage;
#--- Try the cache
tryCache('event', $chosenEventId, preg_replace('/ /', '', $chosenRegionId), $chosenYears, preg_replace('/ /', '', $chosenShow), $chosenSingle, $chosenAverage);
#------------------------------
# Prepare stuff for the query.
#------------------------------
$eventCondition = eventCondition();
$yearCondition = yearCondition();
$regionCondition = regionCondition('result');
$limitCondition = '';
if (preg_match('/^10+/', $chosenShow, $matches)) {
$limitNumber = $matches[0];
$limitCondition = 'LIMIT ' . 2 * $limitNumber;
}
$valueSource = $chosenAverage ? 'average' : 'best';
$valueName = $chosenAverage ? 'Average' : 'Single';
#------------------------------
# Get results from database.
#------------------------------
if ($chosenShow == 'By Region') {
require 'includes/events_regions.php';
return;
}
if ($chosenShow == '100 Results' || $chosenShow == '1000 Results') {
require 'includes/events_results.php';
} else {
require 'includes/events_persons.php';
}
#------------------------------
# Show the table.
#------------------------------
startTimer();
$event = getEvent($chosenEventId);
tableBegin('results', 6);
tableCaption(true, spaced(array($event['name'], chosenRegionName(), $chosenYears, $chosenShow)));
$headerSources = $chosenAverage ? 'Result Details' : '';
tableHeader(explode('|', "Rank|Person|Result|Citizen of|Competition|{$headerSources}"), array(0 => "class='r'", 2 => "class='R2'", 5 => 'class="f"'));
$ctr = 0;
foreach ($results as $result) {
extract($result);
$ctr++;
$no = isset($previousValue) && $value == $previousValue ? ' ' : $ctr;
if ($limitCondition && $no > $limitNumber) {
break;
}
tableRow(array($no, personLink($personId, $personName), formatValue($value, $event['format']), htmlEntities($countryName), competitionLink($competitionId, $competitionName), formatAverageSources($chosenAverage, $result, $event['format'])));
$previousValue = $value;
}
tableEnd();
stopTimer("printing the table");
}
function analyzeChoices()
{
#----------------------------------------------------------------------
global $chosenEventId, $chosenCompetitionId, $chosenWhat, $chosenShow, $chosenWhich, $competitionCondition;
$chosenEventId = getNormalParam('eventId');
$chosenCompetitionId = getNormalParam('competitionId');
$chosenWhat = getNormalParam('what');
$chosenShow = getBooleanParam('show');
$chosenWhich = "";
$chosenWhich .= $chosenEventId ? $chosenEventId : "all events";
$chosenWhich .= " in " . ($chosenCompetitionId ? $chosenCompetitionId : "all competitions");
$competitionCondition = eventCondition() . competitionCondition();
}
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 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 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";
}
