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 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 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 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 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";
}
