function showMedia()
{
    #----------------------------------------------------------------------
    global $chosenYears, $chosenRegionId, $chosenOrder;
    #--- Prepare conditions.
    $yearCondition = yearCondition();
    $regionCondition = regionCondition('competition');
    $orderCondition = $chosenOrder == 'date' ? "ORDER BY competition.year DESC,\n                                                         competition.month DESC,\n                                                         competition.day DESC" : "ORDER BY timestampDecided DESC";
    #--- Get data of the (matching) media items.
    $media = dbQuery("\n    SELECT media.*, competition.*, cellName, country.name AS countryName\n\n    FROM CompetitionsMedia media, Competitions competition, Countries country\n    WHERE 1\n      AND competition.id = competitionId\n      AND country.id = countryId\n      {$yearCondition}\n      {$regionCondition}\n      AND status = 'accepted'\n    {$orderCondition}, cellName\n  ");
    #--- Print the data.
    tableBegin('results', 6);
    #  tableCaption( false, spaced(array( eventName($chosenEventId), chosenRegionName(), $chosenYears )));
    tableHeader(explode('|', 'Insertion Date|Competition Date|Competition|Country, City|Type|Link'), array(5 => 'class="f"'));
    foreach ($media as $data) {
        extract($data);
        #--- Print the empty row.
        if ($chosenOrder == 'submission') {
            $year = preg_replace('/-.*/', '', $timestampDecided);
        }
        if (isset($previousYear) && $year != $previousYear) {
            tableRowEmpty();
        }
        $previousYear = $year;
        tableRow(array(preg_replace('/ .*/', '', $timestampDecided), competitionDate($data), competitionLink($competitionId, $cellName), "<b>{$countryName}</b>, {$cityName}", $type, externalLink($uri, $text)));
    }
    tableEnd();
}
function showCompetitionResultsByPerson($resultsTable = 'Results')
{
    #----------------------------------------------------------------------
    global $chosenByPerson, $chosenAllResults, $chosenTop3, $chosenWinners;
    global $chosenCompetitionId;
    #--- Get the results.
    $competitionResults = getCompetitionResults($resultsTable);
    startTimer();
    tableBegin('results', 8);
    foreach ($competitionResults as $result) {
        extract($result);
        $isNewPerson = !isset($previousPersonId) || $personId != $previousPersonId;
        $isNewEvent = !isset($previousEventId) || $eventId != $previousEventId || $isNewPerson;
        #--- Welcome new persons.
        if ($isNewPerson) {
            if (isset($previousPersonId)) {
                tableRowBlank();
            }
            $bo3_as_mo3 = $formatId == '3' && ($eventId == '333bf' || $eventId == '333fm' || $eventId == '333ft');
            $headerAverage = $formatId == 'a' || $formatId == 'm' || $bo3_as_mo3 ? 'Average' : '';
            $headerAllResults = $formatId != '1' ? 'Result Details' : '';
            tableCaptionNew(false, $personId, spaced(array(personLink($personId, $personName), $countryName)));
            tableHeader(explode('|', "Event|Round|Place|Best||{$headerAverage}||{$headerAllResults}"), array(2 => 'class="r"', 3 => 'class="R"', 5 => 'class="R"', 7 => 'class="f"'));
        }
        #--- One result row.
        tableRowStyled($isNewEvent ? '' : 'color:#AAA', array($isNewEvent ? eventLink($eventId, $eventCellName) : '', $roundCellName, $isNewEvent ? "<b>{$pos}</b>" : $pos, formatValue($best, $valueFormat), $regionalSingleRecord, formatValue($average, $valueFormat), $regionalAverageRecord, formatAverageSources($formatId != '1', $result, $valueFormat)));
        $previousPersonId = $personId;
        $previousEventId = $eventId;
    }
    tableEnd();
    stopTimer("printing the huge table");
}
function showRegionalRecordsSeparate()
{
    #----------------------------------------------------------------------
    global $chosenRegionId;
    require 'regions_get_current_records.php';
    tableBegin('results', 6);
    tableCaption(false, "Single");
    tableHeader(explode('|', 'Event|Result|Person|Citizen of|Competition|'), array(1 => "class='R2'", 5 => 'class="f"'));
    foreach ($results as $result) {
        extract($result);
        if ($type == 'Single') {
            $isNewEvent = !isset($currentEventId) || $eventId != $currentEventId;
            $currentEventId = $eventId;
            tableRow(array($isNewEvent ? eventLink($eventId, $eventCellName) : '', $isNewEvent ? formatValue($value, $format) : '', personLink($personId, $personName), $countryName, competitionLink($competitionId, $competitionName), ''));
        }
    }
    tableCaption(false, "Average");
    tableHeader(explode('|', 'Event|Result|Person|Citizen of|Competition|Result Details'), array(1 => "class='R2'", 5 => 'class="f"'));
    $currentEventId = '';
    foreach ($results as $result) {
        extract($result);
        if ($type == 'Average') {
            $isNewEvent = $eventId != $currentEventId;
            $currentEventId = $eventId;
            tableRow(array($isNewEvent ? eventLink($eventId, $eventCellName) : '', $isNewEvent ? formatValue($value, $format) : '', personLink($personId, $personName), $countryName, competitionLink($competitionId, $competitionName), formatAverageSources(true, $result, $format)));
        }
    }
    tableEnd();
}
function listCompetitions()
{
    #----------------------------------------------------------------------
    global $chosenEventId, $chosenYears, $chosenRegionId, $chosenPatternHtml;
    global $chosenList, $chosenMap;
    global $chosenCompetitions;
    $chosenCompetitions = getCompetitions($chosenList);
    tableBegin('results', 5);
    tableCaption(false, spaced(array(eventName($chosenEventId), chosenRegionName(), $chosenYears, $chosenPatternHtml ? "\"{$chosenPatternHtml}\"" : '')));
    if ($chosenList) {
        tableHeader(explode('|', 'Year|Date|Name|Country, City|Venue'), array(4 => 'class="f"'));
        foreach ($chosenCompetitions as $competition) {
            extract($competition);
            if (isset($previousYear) && $year != $previousYear) {
                tableRowEmpty();
            }
            $previousYear = $year;
            $isPast = wcaDate('Ymd') > 10000 * $year + 100 * $month + $day;
            tableRow(array($year, competitionDate($competition), $isPast ? competitionLink($id, $cellName) : ($showPreregForm || $showPreregList ? competitionLinkClassed('rg', $id, $cellName) : competitionLinkClassed('fc', $id, $cellName)), "<b>{$countryName}</b>, {$cityName}", processLinks($venue)));
        }
    }
    tableEnd();
    if ($chosenMap) {
        // create map markers
        $markers = array();
        foreach ($chosenCompetitions as $comp) {
            $markers[$comp['id']] = array();
            $markers[$comp['id']]['latitude'] = $comp['latitude'];
            $markers[$comp['id']]['longitude'] = $comp['longitude'];
            $markers[$comp['id']]['info'] = "<a href='c.php?i=" . $comp['id'] . "'>" . o($comp['cellName']) . "</a><br />" . date("M j, Y", mktime(0, 0, 0, $comp['month'], $comp['day'], $comp['year'])) . " - " . o($comp['cityName']);
        }
        displayMap($markers);
    }
}
function showResults()
{
    #----------------------------------------------------------------------
    global $chosenRegionId, $chosenSingle, $chosenAverage;
    #------------------------------
    # Prepare stuff for the query.
    #------------------------------
    $regionCondition = regionCondition('result');
    $limitCondition = 'LIMIT 120';
    $valueSource = $chosenAverage ? 'average' : 'best';
    $valueName = $chosenAverage ? 'Average' : 'Single';
    #------------------------------
    # Get results from database.
    #------------------------------
    $limitNumber = 300;
    $ranks = getRanks($valueName, $chosenRegionId);
    list($rows, $header) = sumOfRanks($valueName, getAllEventIds(), $ranks, $limitNumber + 20);
    $header = preg_replace('/ +/', '|', preg_replace('/\\[\\w+\\]/', '', "Rank {$header} "));
    foreach (dbQuery("SELECT id, name FROM Persons WHERE subId=1") as $person) {
        $personName[$person[0]] = $person[1];
    }
    #------------------------------
    # Show the table.
    #------------------------------
    $numColumns = count($rows[0]) + 2;
    $headerAttributes = array(0 => "class='r'", 2 => "class='R2'", $numColumns - 1 => 'class="f"');
    for ($i = 3; $i < $numColumns - 1; $i++) {
        $headerAttributes[$i] = "class='r'";
    }
    tableBegin('results', $numColumns);
    tableCaption(true, chosenRegionName(true));
    #  tableHeader( explode( '|', $header), $headerAttributes );
    $ctr = $previousSumOfRanks = 0;
    $showHeader = true;
    foreach ($rows as $row) {
        $showHeader |= $ctr % 20 == 0;
        list($personId, $sumOfRanks) = $row;
        $ctr++;
        $no = $sumOfRanks == $previousSumOfRanks ? '' : $ctr;
        if ($limitCondition && $no > $limitNumber) {
            break;
        }
        if ($showHeader && $no) {
            tableHeader(explode('|', $header), $headerAttributes);
            $showHeader = false;
        }
        for ($i = 2; $i < $numColumns - 2; $i++) {
            if (preg_match('/^(10|[1-9])$/', $row[$i])) {
                $row[$i] = "<span style='color:#0D0'>{$row[$i]}</span>";
            }
        }
        $row[0] = personLink($row[0], $personName[$row[0]]);
        $row[] = '';
        array_unshift($row, $no);
        tableRow($row);
        $previousSumOfRanks = $sumOfRanks;
    }
    tableEnd();
}
function showBody()
{
    #----------------------------------------------------------------------
    global $chosenPersonId;
    // simple validation first...
    if (!preg_match('/\\d{4}\\w{4}\\d{2}/', $chosenPersonId)) {
        showErrorMessage('Invalid WCA id Format <strong>[</strong>' . o($chosenPersonId) . '<strong>]</strong>');
        print '<p><a href="persons.php">Click here to search for people.</a></p>';
        return;
    }
    #--- Get all incarnations of the person.
    $persons = dbQuery("\n    SELECT person.name personName, country.name countryName, day, month, year, gender\n    FROM Persons person, Countries country\n    WHERE person.id = '{$chosenPersonId}' AND country.id = person.countryId\n    ORDER BY person.subId\n  ");
    #--- If there are none, show an error and do no more.
    if (!count($persons)) {
        showErrorMessage('Unknown person id <strong>[</strong>' . o($chosenPersonId) . '<strong>]</strong>');
        $namepart = substr($chosenPersonId, 4, 4);
        print '<p><a href="persons.php?pattern=' . urlEncode($namepart) . '">Click to search for people with `' . o($namepart) . '` in their name.</a></p>';
        return;
    }
    #--- Get and show the current incarnation.
    $currentPerson = array_shift($persons);
    extract($currentPerson);
    echo "<h1>{$personName}</h1>";
    #--- Show previous incarnations if any.
    if (count($persons)) {
        echo "<p class='subtitle'>(previously ";
        foreach ($persons as $person) {
            $previous[] = "{$person['personName']}/{$person['countryName']}";
        }
        echo implode(', ', $previous) . ")</p>";
    }
    #--- Show the picture if any.
    $picture = getCurrentPictureFile($chosenPersonId);
    if ($picture) {
        echo "<center><img class='person' src='{$picture}' /></center>";
    }
    #--- Show the In Memoriam if any.
    $inMemoriamArray = array("2008COUR01" => "https://www.worldcubeassociation.org/forum/viewtopic.php?t=2028", "2003LARS01" => "https://www.worldcubeassociation.org/forum/viewtopic.php?t=1982", "2012GALA02" => "https://www.worldcubeassociation.org/forum/viewtopic.php?t=1044", "2008LIMR01" => "https://www.worldcubeassociation.org/forum/viewtopic.php?t=945", "2008KIRC01" => "https://www.worldcubeassociation.org/forum/viewtopic.php?t=470");
    if (array_key_exists($chosenPersonId, $inMemoriamArray)) {
        echo "<center><a target='_blank' href='{$inMemoriamArray[$chosenPersonId]}'>In Memoriam</a></center>";
    }
    #--- Show the details.
    tableBegin('results', 4);
    tableCaption(false, 'Details');
    tableHeader(explode('|', 'Country|WCA Id|Gender|Competitions'), array(3 => 'class="f"'));
    $gender_text = genderText($gender);
    $numberOfCompetitions = dbValue("SELECT count(distinct competitionId) FROM Results where personId='{$chosenPersonId}'");
    tableRow(array($countryName, $chosenPersonId, $gender_text, $numberOfCompetitions));
    tableEnd();
    #--- Try the cache for the results
    # tryCache( 'person', $chosenPersonId );
    #--- Now the results.
    require 'includes/person_personal_records_current.php';
    require 'includes/person_world_championship_podiums.php';
    require 'includes/person_world_records_history.php';
    require 'includes/person_continent_records_history.php';
    require 'includes/person_events.php';
}
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&euml;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&euml;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 ? '&nbsp;' : $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 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 showCurrentPersonalRecords()
{
    #----------------------------------------------------------------------
    global $chosenPersonId;
    $bests = dbQuery("\n\n  SELECT * FROM\n\n   (SELECT \n      eventId,\n      best single,\n      worldRank singleRank,\n      continentRank singleRankContinent,\n      countryRank singleRankCountry\n    FROM RanksSingle\n    WHERE personId='{$chosenPersonId}') singles\n    \n    LEFT JOIN\n   \n   (SELECT \n      eventId eC,\n      best average,\n      worldRank averageRank,\n      continentRank averageRankContinent,\n      countryRank averageRankCountry\n    FROM RanksAverage\n    WHERE personId='{$chosenPersonId}') average\n    \n    ON eventId = eC,\n    Events event\n    WHERE\n      eventId = event.id\n    ORDER BY\n      event.rank\n\n\n  ");
    tableBegin('results', 10);
    tableCaption(false, "Current Personal Records");
    tableHeader(explode(" ", "Event NR CR WR Single Average WR CR NR "), array("", "class='r'", "class='r'", "class='R'", "class='R2'", "class='R2'", "class='R'", "class='r'", "class='r'", "class='f'"));
    $oddMessage = "A missing or worse country/continent rank compared to a larger region rank is due to the change of country because results from previous regions don&#39;t count for differing current regions.";
    foreach ($bests as $best) {
        extract($best);
        $odd = $singleRankCountry > $singleRankContinent || $singleRankContinent > $singleRank || $averageRankCountry > $averageRankContinent || $averageRankContinent > $averageRank || !$singleRankCountry && $singleRankContinent || !$singleRankContinent && $singleRank || !$averageRankCountry && $averageRankContinent || !$averageRankContinent && $averageRank;
        tableRow(array(internalEventLink("#{$eventId}", eventCellName($eventId)), "<span style='color:#999'>" . colorMe($singleRankCountry) . "</span>", colorMe($singleRankContinent), colorMe($singleRank), eventLink($eventId, formatValue($single, valueFormat($eventId))), eventAverageLink($eventId, formatValue($average, valueFormat($eventId))), colorMe($averageRank), colorMe($averageRankContinent), "<span style='color:#999'>" . colorMe($averageRankCountry) . "</span>", $odd ? "<a title='{$oddMessage}' style='color:#66F' onclick='alert(\"{$oddMessage}\")'>(*)</a>" : ''));
    }
    tableEnd();
}
function showRegionalRecordsSlim()
{
    #----------------------------------------------------------------------
    global $chosenYears;
    require 'regions_get_current_records.php';
    tableBegin('results', 6);
    $caption = spaced(array(chosenRegionName(), $chosenYears));
    if ($caption) {
        tableCaption(true, $caption);
    } else {
        tableRowBlank();
    }
    tableHeader(explode('|', 'Person|Single|Event|Average|Person|Result Details'), array(1 => "class='R2'", 2 => "class='c'", 3 => "class='R2'", 5 => 'class="f"'));
    #--- Process events.
    foreach (structureBy($results, 'eventId') as $eventResults) {
        $structure = structureBy($eventResults, 'type');
        $singles = $structure[0];
        $averages = isset($structure[1]) ? $structure[1] : array();
        $wasShownSinglePerson = $wasShownAveragePerson = array();
        #--- Process records for this event.
        $first = true;
        while ($singles || $averages) {
            #--- Get next single.
            $s = array_shift($singles);
            if (isset($wasShownSinglePerson[$s['personId']])) {
                $s = false;
            }
            $wasShownSinglePerson[$s['personId']] = true;
            #--- Get next average.
            $a = array_shift($averages);
            if (isset($wasShownAveragePerson[$a['personId']])) {
                $a = false;
            }
            $wasShownAveragePerson[$a['personId']] = true;
            if ($s || $a) {
                tableRow(array($s ? personLink($s['personId'], $s['personName']) : '', $first ? formatValue($s['value'], $s['format']) : '', $first ? eventLink($s['eventId'], $s['eventCellName']) : '', $first ? formatValue($a['value'], $a['format']) : '', $a ? personLink($a['personId'], $a['personName']) : '', $a ? formatAverageSources(true, $a, $a['format']) : ''));
            }
            $first = false;
        }
    }
    tableEnd();
}
function showRegionalRecordsMixed()
{
    #----------------------------------------------------------------------
    global $chosenRegionId, $chosenYears;
    require 'regions_get_current_records.php';
    tableBegin('results', 6);
    tableCaption(false, spaced(array(chosenRegionName(), $chosenYears)));
    tableHeader(explode('|', 'Type|Result|Person|Citizen of|Competition|Result Details'), array(1 => "class='R2'", 5 => 'class="f"'));
    foreach ($results as $result) {
        extract($result);
        $isNewEvent = !isset($currentEventId) || $eventId != $currentEventId;
        $currentEventId = $eventId;
        $isNewType = $isNewEvent || !isset($currentType) || $type != $currentType;
        $currentType = $type;
        if ($isNewEvent) {
            tableCaption(false, eventLink($eventId, $eventName));
        }
        tableRow(array($isNewType ? $type : '', $isNewType ? formatValue($value, $format) : '', personLink($personId, $personName), $countryName, competitionLink($competitionId, $competitionName), formatAverageSources($type == 'Average', $result, $format)));
    }
    tableEnd();
}
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 doTheDarnChecking()
{
    #----------------------------------------------------------------------
    global $differencesWereFound;
    #--- Begin form and table.
    echo "<form action='check_regional_record_markers_ACTION.php' method='post'>\n";
    tableBegin('results', 12);
    #--- Do the checking.
    computeRegionalRecordMarkers('best', 'Single');
    computeRegionalRecordMarkers('average', 'Average');
    #--- End table.
    tableEnd();
    #--- Tell the result.
    $date = wcaDate();
    noticeBox2(!$differencesWereFound, "We completely agree.<br />{$date}", "<p>Darn! We disagree!<br />{$date}</p>\n     <p>Choose the changes you agree with above, then click the 'Execute...' button below. It will result in something like the following.\n        If you then go back in your browser and refresh the page, the changes should be visible.</p>\n        \n<pre>\n            Queries similar to the following will be executed:\n              UPDATE Results SET regionalSingleRecord = ? WHERE id = ?\n              UPDATE Results SET regionalAverageRecord = ? WHERE id = ?\n          </pre>");
    #--- If differences were found, offer to fix them.
    if ($differencesWereFound) {
        echo "<center><input type='submit' value='Execute the agreed changes!' /></center>\n";
    }
    #--- Finish the form.
    echo "</form>\n";
}
function showMedia()
{
    #----------------------------------------------------------------------
    global $chosenType, $chosenStatus, $chosenRegionId, $chosenOrder;
    #--- Prepare conditions.
    $typeCondition = $chosenType ? "AND type='{$chosenType}'" : '';
    $accepted = $chosenStatus == 'accepted';
    $order = $accepted ? "ORDER BY timestampDecided    DESC" : "ORDER BY timestampSubmitted  DESC";
    $orderCondition = $chosenOrder == 'date' ? "ORDER BY competition.year DESC,\n                                                         competition.month DESC,\n                                                         competition.day DESC" : $order;
    $headerDate = $accepted ? "Insertion" : "Submission";
    #--- Get data of the (matching) media items.
    $media = dbQuery("\n    SELECT media.*,\n           competition.year, competition.month, competition.day,\n           competition.endMonth, competition.endDay,\n           competition.countryId, competition.cityName,\n           cellName,\n           country.name AS countryName\n    FROM CompetitionsMedia media, Competitions competition, Countries country\n    WHERE 1\n      AND competition.id = competitionId\n      AND country.id = countryId\n      {$typeCondition}\n      " . regionCondition('competition') . "\n      AND status='{$chosenStatus}'\n    {$orderCondition}, cellName\n  ");
    #--- Begin form and table.
    echo "<form action='validate_media_ACTION.php' method='POST'>\n";
    tableBegin('results', 7);
    tableHeader(explode('|', $headerDate . ' Date|Competition Date|Competition|Country, City|Type|Link|'), array(5 => 'class="f"'));
    #--- Print results.
    foreach ($media as $data) {
        extract($data);
        $timestamp = $accepted ? $timestampDecided : $timestampSubmitted;
        if ($chosenOrder == 'submission') {
            $year = preg_replace('/-.*/', '', $timestamp);
        }
        if (isset($previousYear) && $year != $previousYear) {
            tableRowEmpty();
        }
        $previousYear = $year;
        $button = "<input type='submit' class='butt' value='Info' name='info{$id}' /> ";
        $button .= "<input type='submit' class='butt' value='Edit' name='edit{$id}' /> ";
        $button .= $accepted ? "<input type='submit' class='butt' value='Erase' name='refuse{$id}' />" : "<input type='submit' class='butt' value='Accept' name='accept{$id}' />\n                            <input type='submit' class='butt' value='Refuse' name='refuse{$id}' />";
        tableRow(array(preg_replace('/ .*/', '', $timestamp), competitionDate($data), competitionLink($competitionId, $cellName), "<b>{$countryName}</b>, {$cityName}", $type, externalLink(htmlEscape($uri), htmlEscape($text)), $button));
    }
    #--- End form and table.
    tableEnd();
    echo "</form>";
}
function showUnfinishedPersons () {
#----------------------------------------------------------------------
  global $personsFromPersons, $personsFromResultsWithoutId, $birthdates;

  #--- Pre-compute the candidate tuples: (id, name, countryId, romanName, romanNameSimilarityPlaceHolder, countryIdSimilarityPlaceHolder)
  $candidates = array();
  foreach( $personsFromPersons as $person ){
    list( $id, $name, $countryId ) = $person;
    $candidates[] = array( $id, $name, $countryId, extractRomanName($name), 0, 0 );
  }

  #--- Begin the form and table.
  echo "<form action='persons_finish_unfinished_ACTION.php' method='post'>";
  tableBegin( 'results', 8 );
  tableHeader( explode( '|', '|personName|countryId|personId|birthdate|personName|countryId|personSemiId' ),
               array( 6=>'class="6"' ) );

  #--- Walk over all persons from the Results table.
  $caseNr = 0;
  foreach( $personsFromResultsWithoutId as $person ){
    list( $name, $countryId, $firstYear ) = $person;
    
    #--- Try to compute the semi-id.
    $quarterId = removeUglyAccentsAndStuff( extractRomanName( $name ));
    $quarterId = preg_replace( '/[^a-zA-Z ]/', '', $quarterId );
    $quarterId = strtoupper( substr( preg_replace( '/(.*)\s(.*)/', '$2$1', $quarterId ), 0, 4 ));
    if ( strlen ( $quarterId ) == 0 ) {
      // if the name comes empty, invent a quarterId
      $quarterId = 'XXXX';
    } else if ( strlen( $quarterId ) < 4 ) {
      // make sure the quarterId is 4-letter long
      while ( strlen( $quarterId ) < 4 ) {
        $quarterId .= $quarterId;
      }
      $quarterId = substr( $quarterId, 0, 4 );
    }
    $semiId = $firstYear . $quarterId;

    #--- Html-ify name and country.
    $nameHtml = htmlEscape( $name );
    $countryIdHtml = htmlEscape( $countryId );

    #--- Hidden field describing the case.
    $caseNr++;
    tableRowFull( "&nbsp;<input type='hidden' name='oldNameAndCountry$caseNr' value='$nameHtml|$countryIdHtml' />" );
    
    #--- Show the person.
    # Note that we set this input to checked, but if there's a better match
    # lower on, then it will take precendence.
    tableRowStyled( 'font-weight:bold', array(
      "<input type='radio' name='action$caseNr' value='new' checked='checked' />",
      visualize( $name ),
      visualize( $countryId ),
      peekLink( $name, $countryId ),
      'mm/dd/yyyy',
      "<input type='text' name='name$caseNr' value='$nameHtml' size='20' />",
      "<input type='text' name='country$caseNr' value='$countryIdHtml' size='20' />",
      "<input type='text' name='semiId$caseNr' value='$semiId' size='10' maxlength='8' />",
    ));

    #--- Show most similar persons.
    $similarsCtr = 0;
    foreach( getMostSimilarPersonsMax( extractRomanName($name), $countryId, $candidates, 10 ) as $similarPerson ){
      list( $other_id, $other_name, $other_countryId ) = $similarPerson;
      
      #--- If name and country match the unfinished persons, pre-select it.
      $checked = ($other_name==$name && $other_countryId==$countryId)
        ? "checked='checked'" : '';
        
      #--- Skip the unfinished person itself. 
      if( $checked && !$other_id )
        continue;

      #--- Html-ify.
      $nameHtml = htmlEscape( $other_name );
      $countryHtml = htmlEscape( $other_countryId );
      $idHtml = htmlEscape( $other_id );
      
      #--- Use "name|country|id" as action.
      $action = "$nameHtml|$countryHtml|$idHtml";
      
      #--- Show the other person.
      tableRow( array(
        "<input type='radio' name='action$caseNr' value='$action' $checked />",
#        ($other_id ? personLink( $other_id, $other_name ) : $other_name),
        visualize( $other_name ),
        visualize( $other_countryId ),
        ($other_id ? "<a class='p' href='../p.php?i=$other_id' target='_blank'>$other_id</a>" : peekLink( $other_name, $other_countryId )),
        $birthdates[ $other_id ],
        '', #sprintf( "%.2f", $similarity ),
        '',
        '',
      ));
      
      #--- Stop after five similar persons.
      if( ++$similarsCtr == 5 )
        break;
    }

    #--- Offer an explicit skip.
    tableRow( array(
      "<input type='radio' name='action$caseNr' value='skip' />",
      'I\'m not sure yet', '', '', '', '', '', ''
    ));
    
    #--- Don't show more than 20 unfinished persons.
    if( $caseNr == 20 )
      break;
  }

  #--- Show 'Update' button, finish table and form.
  tableRowEmpty();
  tableRowFull( "<input type='submit' value='Update' />" );
  tableEnd();
  echo "</form>";
}
    if (isset($showContinent[$continentId]) && $value == $bestValueOfContinent[$continentId]) {
        $bestOfContinent[] = $result;
    }
    $result['regionName'] = 'World';
    if ($value == $bestValueOfWorld) {
        $bestOfWorld[] = $result;
    }
}
#----------------------------------------------------------------------
#   Print the table.
#----------------------------------------------------------------------
startTimer();
$regionName = preg_replace('/^_/', '', $chosenRegionId);
$eventName = eventName($chosenEventId);
$headerSources = $chosenAverage ? 'Result Details' : '';
tableBegin('results', 5);
tableCaption(true, spaced(array($eventName, $chosenShow, $regionName, $chosenYears)));
tableHeader(explode('|', "Region|Result|Person|Competition|{$headerSources}"), array(0 => 'class="L"', 1 => "class='R2'", 4 => 'class="f"'));
if (isset($bestOfCountry)) {
    $all = array_merge($bestOfWorld, array(0), $bestOfContinent, array(0), $bestOfCountry);
    foreach ($all as $row) {
        if (!$row) {
            tableRowEmpty();
            continue;
        }
        extract($row);
        $isNewRegion = !isset($previousRegionName) || $regionName != $previousRegionName;
        $previousRegionName = $regionName;
        tableRow(array($isNewRegion ? $regionName : '', $isNewRegion ? formatValue($value, $valueFormat) : '', personLink($personId, $personName), competitionLink($competitionId, $competitionName), formatAverageSources($chosenAverage, $row, $valueFormat)));
    }
}
function showPsychSheet($eventId)
{
    #----------------------------------------------------------------------
    global $chosenCompetitionId;
    echo "<h1>Psych Sheet</h1><br />";
    #--- What's first, single or average?
    $order = count(dbQuery("SELECT * FROM Results WHERE eventId='{$eventId}' AND formatId in ('a','m') LIMIT 1")) ? array('Average', 'Single') : array('Single', 'Average');
    #--- Get singles, averages and preregs.
    $score1 = array();
    foreach (dbQuery("SELECT personId, best, worldRank FROM Ranks{$order[0]} WHERE eventId='{$eventId}'") as $row) {
        $score1[$row['personId']] = array($row['best'], $row['worldRank']);
    }
    $score2 = array();
    foreach (dbQuery("SELECT personId, best, worldRank FROM Ranks{$order[1]} WHERE eventId='{$eventId}'") as $row) {
        $score2[$row['personId']] = array($row['best'], $row['worldRank']);
    }
    $preregs = dbQuery("\n    SELECT personId, name, countryId\n    FROM   Preregs\n    WHERE  competitionId = '{$chosenCompetitionId}'\n      AND  status = 'a'\n      AND  eventIds rlike '[[:<:]]{$eventId}[[:>:]]'\n  ");
    #--- Add singles, averages and a comparison key to the preregs.
    foreach ($preregs as &$prereg) {
        extract($prereg);
        $prereg['score1'] = isset($score1[$personId]) ? $score1[$personId] : array(0, 0);
        # PHP suuuucks
        $prereg['score2'] = isset($score2[$personId]) ? $score2[$personId] : array(0, 0);
        $s1 = isset($score1[$personId]) ? $score1[$personId][1] : 999999999;
        $s2 = isset($score2[$personId]) ? $score2[$personId][1] : 999999999;
        $prereg['cmpKey'] = sprintf('%09d%09d', $s1, $s2);
    }
    unset($prereg);
    # Because otherwise PHP is a weirdo and messes up the table-foreach below.
    #--- Sort the preregs.
    usort($preregs, function ($a, $b) {
        return strcmp($a['cmpKey'], $b['cmpKey']);
    });
    #--- Show the preregs table.
    tableBegin('results', 8);
    tableCaption(false, eventName($eventId));
    tableHeader(explode('|', "Rank|Person|Citizen of|Best {$order[0]}|WR|Best {$order[1]}|WR|"), array(0 => 'class="r"', 3 => 'class="R"', 4 => 'class="R"', 5 => 'class="r"', 6 => 'class="r"', 7 => 'class="f"'));
    $ctr = 0;
    $lastCmpKey = '';
    foreach ($preregs as $prereg) {
        extract($prereg);
        $ctr++;
        $rank = $cmpKey > $lastCmpKey ? $ctr : '';
        $lastCmpKey = $cmpKey;
        tableRow(array($rank, $personId ? personLink($personId, $name) : $name, $countryId, formatValue($score1[0], valueFormat($eventId)), $score1[1], formatValue($score2[0], valueFormat($eventId)), $score2[1], ''));
    }
    tableEnd();
}
function changeRounds($competitionId, $eventId, $translateRounds, $checked)
{
    #----------------------------------------------------------------------
    #--- Get rounds of the event
    $roundRows = dbQuery("\n    SELECT   roundId, round.cellName\n    FROM     Results result, Rounds round\n    WHERE    result.competitionId = '{$competitionId}'\n      AND    result.eventId = '{$eventId}'\n      AND    result.roundId = round.id\n      AND    result.roundId <> 'b'\n    GROUP BY competitionId, eventId, roundId\n    ORDER BY round.rank\n  ");
    tableBegin('results', 3);
    tableHeader(explode('|', "Current round|New round|"), array(2 => 'class="f"'));
    foreach ($roundRows as $roundRow) {
        extract($roundRow);
        $formId = "setround{$competitionId}/{$eventId}/{$roundId}";
        tableRow(array($cellName, listRounds($translateRounds[$roundId], $formId), ''));
    }
    tableEnd();
    $checkbox = "<input type='checkbox' name='confirmround{$competitionId}/{$eventId}' value='1' " . ($checked ? "checked='checked'" : "") . " />";
    echo "{$checkbox} Update<br/>";
}
    tableEnd();
    print "</div>";
} else {
    print "There is no person data to finish importing.";
}
print "</li>";
// more scripts...
print "<li><p>Run some more scripts:</p>\n         <ol type='a'>\n           <li><a href='check_rounds.php?competitionId={$compIdUrl}&show=Show' target='_blank' class='link-external external'>check_rounds</a></li>\n           <li><a href='check_regional_record_markers.php?competitionId={$compIdUrl}&show=Show' target='_blank' class='link-external external'>check_regional_record_markers</a></li>\n           <li><a href='compute_auxiliary_data.php?doit=+Do+it+now+' target='_blank' class='link-external external'>compute_auxiliary_data</a></li>\n         </ol>\n       </li>";
// table to check existence of results vs scrambles
print "<li><p>Sanity Checks:</p>\n         <ol type='a'>\n           <li><a href='/competitions/{$compIdUrl}' target='_blank' class='link-external external'>View the Public competition page</a></li>\n           <li>Post the <a href='/competitions/{$compIdUrl}/post/results' target='_blank' class='link-external external'>results announcement</a>\n                </li>\n           <li>";
$checks_table = $wcadb_conn->boundQuery("SELECT e.cellName as event, d.cellName as round, c.hasscr, c.hasevent, e.id as eventId, d.id as roundId FROM (\n        SELECT s.eventId as event, s.roundId as round, s.eventId as hasscr, r.eventId as hasevent FROM\n         (SELECT DISTINCT eventId, roundId, competitionId FROM Scrambles WHERE competitionId = ?) as s\n        LEFT JOIN (SELECT DISTINCT eventId, roundId, competitionId FROM Results WHERE competitionId = ?) as r\n        ON (s.eventId=r.eventId AND s.roundId=r.roundId)\n        UNION\n        SELECT r.eventId as event, r.roundId as round, s.eventId as hasscr, r.eventId as hasevent FROM\n         (SELECT DISTINCT eventId, roundId, competitionId FROM Scrambles WHERE competitionId = ?) as s\n        RIGHT JOIN (SELECT DISTINCT eventId, roundId, competitionId FROM Results WHERE competitionId = ?) as r\n        ON (s.eventId=r.eventId AND s.roundId=r.roundId)\n    ) AS c\n    LEFT JOIN Events as e ON e.id=c.event\n    LEFT JOIN Rounds as d ON d.id=c.round\n    ORDER BY e.rank, d.rank", array('ssss', &$compId, &$compId, &$compId, &$compId));
if (count($checks_table) > 0) {
    print "Compare imported scramble vs result data:<br /><br />";
    $good_cell_attr = 'class="good_cell"';
    $bad_cell_attr = 'class="bad_cell"';
    tableBegin('scramble_help', 4);
    tableHeader(array('Event: ', 'Round', 'Fully Imported Results', 'Fully Imported Scrambles'), array());
    foreach ($checks_table as $round) {
        if ($round['hasevent'] && $round['hasscr']) {
            $class = 'has_both';
        } else {
            $class = 'missing';
        }
        if ($round['hasscr']) {
            // link to remove scrambles for this round
            // should protect this if we keep using the php system
            // jQuery attempts to load this
            $has_scrambles = "Y&nbsp;&nbsp;&nbsp;(<a class='remove_link' href='scripts/remove_data.php?t=Scrambles&c={$compIdUrl}&amp;e={$round['eventId']}&amp;r={$round['roundId']}' target='_blank' title='Remove Scrambles'>X</a>)";
        } else {
            $has_scrambles = 'N';
        }
function showUnfinishedPersons()
{
    #----------------------------------------------------------------------
    global $personsFromPersons, $personsFromResultsWithoutId, $birthdates;
    #--- Pre-compute the candidate tuples: (id, name, countryId, romanName, romanNameSimilarityPlaceHolder, countryIdSimilarityPlaceHolder)
    $candidates = array();
    foreach ($personsFromPersons as $person) {
        list($id, $name, $countryId) = $person;
        $candidates[] = array($id, $name, $countryId, extractRomanName($name), 0, 0);
    }
    #--- Begin the form and table.
    echo "<form action='persons_finish_unfinished_ACTION.php' method='post'>";
    tableBegin('results', 8);
    tableHeader(explode('|', '|personName|countryId|personId|birthdate|personName|countryId|personSemiId'), array(6 => 'class="6"'));
    #--- Walk over all persons from the Results table.
    $caseNr = 0;
    $availableSpots = array();
    // array of semiIds in progress
    foreach ($personsFromResultsWithoutId as $person) {
        list($name, $countryId, $firstYear) = $person;
        #--- Try to compute the semi-id.
        $paddingLetter = 'U';
        $neatName = strtoupper(preg_replace('/[^a-zA-Z ]/', '', removeUglyAccentsAndStuff(extractRomanName($name))));
        $nameParts = explode(' ', $neatName);
        $lastName = $nameParts[count($nameParts) - 1];
        $restOfName = implode(array_slice($nameParts, 0, count($nameParts) - 1));
        // follows a simple trick that prevents us from empty or too short restOfNames and provides the appropriate padding
        $restOfName = str_pad($restOfName, 4, $paddingLetter);
        $lettersToShift = max(0, 4 - strlen($lastName));
        $cleared = false;
        while (!$cleared && $lettersToShift <= 4) {
            $quarterId = substr($lastName, 0, 4 - $lettersToShift) . substr($restOfName, 0, $lettersToShift);
            $semiId = $firstYear . $quarterId;
            // update array of persons in progress
            if (!array_key_exists($semiId, $availableSpots)) {
                $lastIdTaken = dbQuery("SELECT id FROM Persons WHERE id LIKE '{$semiId}__' ORDER BY id DESC LIMIT 1");
                if (!count($lastIdTaken)) {
                    $counter = 0;
                } else {
                    $counter = intval(substr($lastIdTaken[0]['id'], 8, 2), 10);
                }
                $availableSpots[$semiId] = 99 - $counter;
            }
            // is there a spot available?
            if ($availableSpots[$semiId]) {
                $availableSpots[$semiId]--;
                $cleared = true;
            } else {
                $lettersToShift++;
            }
        }
        /* The script has tried all the possibilities and none of them was valid.
         * If we reach here with $cleared set to false (something that is not going to happen in centuries) then
         * the person posting will receive an error in persons_finish_unfinished_ACTION.php and the software team
         * of the future will have work to do.
         */
        if (!$cleared) {
            // if we didn't clear a spot we stick with the first combination
            $lettersToShift = max(0, 4 - strlen($lastName));
            $semiId = $firstYear . substr($lastName, 0, 4 - $lettersToShift) . substr($restOfName, 0, $lettersToShift);
            $availableSpots[$semiId] = 0;
        }
        #--- Html-ify name and country.
        $nameHtml = htmlEscape($name);
        $countryIdHtml = htmlEscape($countryId);
        #--- Hidden field describing the case.
        $caseNr++;
        tableRowFull("&nbsp;<input type='hidden' name='oldNameAndCountry{$caseNr}' value='{$nameHtml}|{$countryIdHtml}' />");
        #--- Show the person.
        # Note that we set this input to checked, but if there's a better match
        # lower on, then it will take precendence.
        tableRowStyled('font-weight:bold', array("<input type='radio' name='action{$caseNr}' value='new' checked='checked' />", visualize($name), visualize($countryId), peekLink($name, $countryId), 'mm/dd/yyyy', "<input type='text' name='name{$caseNr}' value='{$nameHtml}' size='20' />", "<input type='text' name='country{$caseNr}' value='{$countryIdHtml}' size='20' />", "<input type='text' name='semiId{$caseNr}' value='{$semiId}' size='10' maxlength='8' />"));
        #--- Show most similar persons.
        $similarsCtr = 0;
        foreach (getMostSimilarPersonsMax(extractRomanName($name), $countryId, $candidates, 10) as $similarPerson) {
            list($other_id, $other_name, $other_countryId) = $similarPerson;
            #--- If name and country match the unfinished persons, pre-select it.
            $checked = $other_name == $name && $other_countryId == $countryId ? "checked='checked'" : '';
            #--- Skip the unfinished person itself.
            if ($checked && !$other_id) {
                continue;
            }
            #--- Html-ify.
            $nameHtml = htmlEscape($other_name);
            $countryHtml = htmlEscape($other_countryId);
            $idHtml = htmlEscape($other_id);
            #--- Use "name|country|id" as action.
            $action = "{$nameHtml}|{$countryHtml}|{$idHtml}";
            #--- Show the other person.
            tableRow(array("<input type='radio' name='action{$caseNr}' value='{$action}' {$checked} />", visualize($other_name), visualize($other_countryId), $other_id ? "<a class='p' href='../p.php?i={$other_id}' target='_blank'>{$other_id}</a>" : peekLink($other_name, $other_countryId), $birthdates[$other_id], '', '', ''));
            #--- Stop after five similar persons.
            if (++$similarsCtr == 5) {
                break;
            }
        }
        #--- Offer an explicit skip.
        tableRow(array("<input type='radio' name='action{$caseNr}' value='skip' />", 'I\'m not sure yet', '', '', '', '', '', ''));
        #--- Don't show more than 20 unfinished persons.
        if ($caseNr == 20) {
            break;
        }
    }
    #--- Show 'Update' button, finish table and form.
    tableRowEmpty();
    tableRowFull("<input type='submit' value='Update' />");
    tableEnd();
    echo "</form>";
}
function checkSimilarResults()
{
    #----------------------------------------------------------------------
    global $competitionCondition, $chosenWhich;
    echo "<hr /><p>Checking <b>" . $chosenWhich . " similar results</b>... (wait for the result message box at the end)</p>\n";
    #--- Get all similar results (except old-new multiblind)
    $rows = pdo_query("\n      SELECT\n          Results.competitionId AS competitionId,\n          Results.personId AS personIdA, Results.personName AS personNameA, Results.eventId AS eventIdA, Results.roundId AS roundIdA,\n          h.personId AS personIdB, h.personName AS personNameB, h.eventId AS eventIdB, h.roundId AS roundIdB,\n          Results.value1 AS value1A, Results.value2 AS value2A, Results.value3 AS value3A, Results.value4 AS value4A, Results.value5 AS value5A,\n          h.value1 AS value1B, h.value2 AS value2B, h.value3 AS value3B, h.value4 AS value4B, h.value5 AS value5B\n      FROM Results\n      JOIN (\n          SELECT competitionId, eventId, roundId, personId, personName, value1, value2, value3, value4, value5\n          FROM Results " . ($competitionCondition ? "JOIN Competitions ON Competitions.id = competitionId " : "") . "WHERE best > 0 " . ($competitionCondition ? $competitionCondition : "") . " AND value3 <> 0\n              AND eventId <> '333mbo'\n      ) h ON Results.competitionId = h.competitionId\n          AND Results.eventId <> '333mbo'\n          AND Results.personId < h.personId\n          AND (\n              (Results.value1 = h.value1 AND h.value1 > 0) +\n              (Results.value2 = h.value2 AND h.value2 > 0) +\n              (Results.value3 = h.value3 AND h.value3 > 0) +\n              (Results.value4 = h.value4 AND h.value4 > 0) +\n              (Results.value5 = h.value5 AND h.value5 > 0) > 2\n              )\n  ");
    tableBegin('results', 4);
    foreach ($rows as $row) {
        $competition = getCompetition($row['competitionId']);
        $competitionName = $competition['cellName'];
        tableCaption(false, competitionLink($row['competitionId'], $competitionName));
        tableHeader(explode('|', "Person|Event|Round|Result Details"), array(3 => 'class="f"'));
        foreach (array('A', 'B') as $letter) {
            $otherLetter = chr(65 + 66 - ord($letter));
            $resultStr = '';
            for ($i = 1; $i <= 5; $i++) {
                $value = $row['value' . $i . $letter];
                if (!$value) {
                    break;
                }
                $resultStr .= "<span class='label label-" . ($value == $row['value' . $i . $otherLetter] ? "danger" : "success") . "'>" . formatValue($value, valueFormat($row['eventId' . $letter])) . "</span> ";
            }
            tableRow(array(personLink($row['personId' . $letter], $row['personName' . $letter]), eventCellName($row['eventId' . $letter]), roundCellName($row['roundId' . $letter]), $resultStr));
        }
    }
    tableEnd();
    #--- Tell the result.
    $date = wcaDate();
    noticeBox2(count($rows) == 0, "No similar results were found.<br />{$date}", "Similar results were found.<br />{$date}");
}
function checkDuplicatesInCompetition()
{
    #----------------------------------------------------------------------
    echo "<hr />";
    # TODO: This is only to prevent the timeout problem and should eventually be properly handled again
    $dateCondition = "AND (year*10000+month*100+day >= CURDATE() - INTERVAL 3 MONTH)";
    $duplicates = dbQuery("\n    SELECT personId, personName, Results.countryId, competitionId, eventId, roundId, count(*) AS occurances\n    FROM Results INNER JOIN Competitions WHERE Competitions.id = competitionId {$dateCondition}\n    GROUP BY competitionId, personId, eventId, roundId, personName, countryId\n    HAVING occurances > 1\n  ");
    #--- If all OK, say so and return.
    if (!$duplicates) {
        echo "<p style='color:#6C6'><strong>OK!</strong> There are no personId/personName/countryId/competitionId/eventId/roundIdAll duplicates in <strong>Results</strong>.</p>";
        return;
    }
    #--- Otherwise, show the errors
    echo "<p style='color:#F00'><strong>BAD!</strong> There are personId/personName/countryId/competitionId/eventId/roundIdAll duplicates in <strong>Results</strong>.</p>";
    tableBegin('results', 7);
    tableHeader(explode('|', 'personId|personName|countryId|competitionId|eventId|roundId|#Occurances'), array(6 => 'class="f"'));
    foreach ($duplicates as $duplicate) {
        extract($duplicate);
        tableRow(visualize(array($personId, $personName, $countryId, $competitionId, $eventId, $roundId, $occurances)));
    }
    tableEnd();
    $GLOBALS["success"] = false;
}