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 getCompetitions($sortList)
{
#----------------------------------------------------------------------
global $chosenEventId, $chosenRegionId, $chosenPatternMysql;
#--- Prepare stuff for the query.
$eventCondition = "";
$regionCondition = "";
$nameCondition = "";
if ($chosenEventId) {
$eventCondition = "AND eventSpecs REGEXP '[[:<:]]{$chosenEventId}[[:>:]]'";
}
$yearCondition = yearCondition();
if ($chosenRegionId && $chosenRegionId != 'World') {
$regionCondition = "AND (competition.countryId = '{$chosenRegionId}' OR continentId = '{$chosenRegionId}')";
}
#TODP: remove the 'competition.' once we get countryId out of the Results table.
foreach (explode(' ', $chosenPatternMysql) as $namePart) {
$nameCondition .= " AND (competition.cellName like '%{$namePart}%' OR\n cityName like '%{$namePart}%' OR\n venue like '%{$namePart}%')";
}
$orderBy = $sortList ? 'year DESC, month DESC, day DESC' : 'longitude, year, month, day';
#--- Get data of the (matching) competitions.
$competitions = dbQuery("\n SELECT DISTINCT\n competition.*,\n country.name AS countryName\n FROM\n Competitions competition,\n Countries country\n WHERE 1\n AND country.id = countryId\n AND showAtAll = 1\n {$eventCondition}\n {$yearCondition}\n {$regionCondition}\n {$nameCondition}\n ORDER BY\n {$orderBy}\n ");
#--- Return them
return $competitions;
}
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 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 getCompetitions ( $sortList ) {
#----------------------------------------------------------------------
global $chosenEventId, $chosenRegionId, $chosenPatternMysql;
#--- Prepare stuff for the query.
$eventCondition = "";
$regionCondition = "";
$nameCondition = "";
if( $chosenEventId )
$eventCondition = "AND eventSpecs REGEXP '[[:<:]]${chosenEventId}[[:>:]]'";
$yearCondition = yearCondition();
if( $chosenRegionId && $chosenRegionId != 'World' )
$regionCondition = "AND (competition.countryId = '$chosenRegionId' OR continentId = '$chosenRegionId')"; #TODP: remove the 'competition.' once we get countryId out of the Results table.
foreach( explode( ' ', $chosenPatternMysql ) as $namePart )
$nameCondition .= " AND (competition.cellName like '%$namePart%' OR
cityName like '%$namePart%' OR
venue like '%$namePart%')";
$orderBy = $sortList ? 'year DESC, month DESC, day DESC' : 'longitude, year, month, day';
#--- Get data of the (matching) competitions.
$competitions = dbQuery("
SELECT DISTINCT
competition.*,
country.name AS countryName
FROM
Competitions competition,
Countries country
WHERE 1
AND country.id = countryId
AND showAtAll = 1
$eventCondition
$yearCondition
$regionCondition
$nameCondition
ORDER BY
$orderBy
");
#--- Return them
return $competitions;
}
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";
}