function addOneStatistic($statisticFile)
{
#----------------------------------------------------------------------
global $lists;
global $WHERE, $sinceDateHtml, $sinceDateMysql, $sinceDateCondition;
startTimer();
require "{$statisticFile}.php";
stopTimer("STATISTIC: {$statisticFile}");
}
function computeRanks ( $valueSource, $valueName ) {
#----------------------------------------------------------------------
startTimer();
echo "<br />Building table Ranks$valueName...<br />\n";
#--- Create empty table
dbCommand( "DROP TABLE IF EXISTS Ranks$valueName" );
dbCommand( "CREATE TABLE Ranks$valueName (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`personId` VARCHAR(10) NOT NULL DEFAULT '',
`eventId` VARCHAR(6) NOT NULL DEFAULT '',
`best` INTEGER NOT NULL DEFAULT '0',
`worldRank` INTEGER NOT NULL DEFAULT '0',
`continentRank` INTEGER NOT NULL DEFAULT '0',
`countryRank` INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_persons` (`personId`),
KEY `fk_events` (`eventId`)) COLLATE latin1_swedish_ci
" );
#--- Determine everybody's current country and continent
$persons = dbQuery( "
SELECT person.id personId, countryId, continentId
FROM Persons person, Countries country
WHERE country.id=countryId
AND person.subId=1
" );
foreach( $persons as $person ) {
extract( $person );
$currentCountry [$personId] = $countryId;
$currentContinent[$personId] = $continentId;
}
unset( $persons );
#--- Get all personal records (note: country-switchers appear once for each country)
$personalRecords = dbQueryHandle( "
SELECT personId, countryId, continentId, eventId, min($valueSource) value
FROM Concise${valueName}Results
WHERE eventId <> '333mbo'
GROUP BY personId, countryId, eventId
ORDER BY eventId, value
" );
#--- Process the personal records
$missingPersonIDs = false;
while( $row = mysql_fetch_row( $personalRecords )){
list( $personId, $countryId, $continentId, $eventId, $value ) = $row;
if( ! $personId ){
$missingPersonIDs = true;
continue;
}
#--- At new event, store the ranks of the previous and reset
if ( isset($latestEventId) && $eventId != $latestEventId ) {
storeRanks( $valueName, $latestEventId, $personRecord, $personWR, $personCR, $personNR );
unset( $ctr, $rank, $record, $ranked, $personRecord, $personWR, $personCR, $personNR );
}
#--- Update the region states (unless we have ranked this person there already, for
#--- example 2008SEAR01 twice in North America and World because of his two countries)
foreach( array( 'World', $continentId, $countryId ) as $region ){
if ( ! isset($ranked[$region][$personId]) ) {
$ctr[$region] = isset($ctr[$region]) ? $ctr[$region] + 1 : 1; # ctr always increases
if ( !isset($record[$region]) || $value > $record[$region] ) # rank only if value worse than previous
$rank[$region] = $ctr[$region];
$record[$region] = $value;
$ranked[$region][$personId] = true;
}
}
#--- Set the person's data (first time the current location is matched)
if ( ! isset($personRecord[$personId]) ) {
$personRecord[$personId] = $value;
$personWR[$personId] = $rank['World'];
}
if ( $continentId==$currentContinent[$personId] && ! isset($personCR[$personId]) )
$personCR[$personId] = $rank[$continentId];
if ( $countryId==$currentCountry[$personId] && ! isset($personNR[$personId]) )
$personNR[$personId] = $rank[$countryId];
$latestEventId = $eventId;
}
#--- Free the result handle
mysql_free_result( $personalRecords );
#--- Store the ranks of the last event
storeRanks( $valueName, $latestEventId, $personRecord, $personWR, $personCR, $personNR );
if( $missingPersonIDs )
noticeBox3( 0, 'Warning: some results are ignored because they are missing a personId' );
stopTimer( "Ranks$valueName" );
echo "... done<br /><br />\n";
}
function deleteCaches () {
#----------------------------------------------------------------------
startTimer();
cacheLog( "delete all" );
global $config;
$cacheFiles = glob( $config->get('filesPath') . 'generated/cache/*.cache' );
echo "Deleting " . count($cacheFiles) . " cache files...<br />\n";
foreach ( $cacheFiles as $cacheFile ) {
#echo "deleting cache file [$cacheFile]<br />";
unlink( $cacheFile );
}
stopTimer( "deleteCaches" );
echo "... done<br /><br />\n";
}
function dbCommand ( $command ) {
#----------------------------------------------------------------------
if( wcaDebug() ){
startTimer();
global $dbCommandCtr;
$dbCommandCtr++;
$commandForShow = strlen($command) < 1010
? $command
: substr($command,0,1000) . '[...' . (strlen($command)-1000) . '...]';
echo "\n\n<!-- dbCommand(\n$commandForShow\n) -->\n\n";
stopTimer( 'printing the database command' );
}
#--- Execute the command.
startTimer();
$dbResult = mysql_query( $command )
or showDatabaseError( "Unable to perform database command." );
global $dbCommandTotalTime;
$dbCommandTotalTime += stopTimer( "executing database command" );
}
function exportPublic ( $sources ) {
#----------------------------------------------------------------------
#--- No time limit
set_time_limit( 0 );
#--- We'll work in the /admin/export directory
chdir( 'export' );
#------------------------------------------
# PREPARATION
#------------------------------------------
#--- Get old and new serial number
$oldSerial = file_get_contents( 'serial.txt' );
$serial = $oldSerial + 1;
file_put_contents( 'serial.txt', $serial );
#--- Build the file basename
$basename = sprintf( 'WCA_export%03d_%s', $serial, wcaDate( 'Ymd' ) );
$oldBasenameStart = sprintf( 'WCA_export%03d_', $oldSerial );
#------------------------------------------
# SQL + TSVs
#------------------------------------------
#--- Build SQL and TSV files
echo "<p><b>Build SQL and TSV files</b></p>";
#--- Start the SQL file
$sqlFile = "WCA_export.sql";
file_put_contents( $sqlFile, "--\n-- $basename\n-- Also read the README.txt\n--\n" );
#--- Walk the tables, create SQL file and TSV files
foreach ( $sources as $tableName => $tableSource ) {
startTimer();
echo "$tableName ";
#--- Get the query
$query = ($tableSource != '*') ? $tableSource : "SELECT * FROM $tableName";
#--- Add the SQL for creating the table
file_put_contents( $sqlFile, getTableCreationSql( $tableName, $query ), FILE_APPEND );
#--- Do the query
$dbResult = mysql_unbuffered_query( $query )
or die( '<p>Unable to perform database query.<br/>\n(' . mysql_error() . ')</p>\n' );
#--- Start the TSV file
$tsvFile = "WCA_export_$tableName.tsv";
file_put_contents( $tsvFile, getTsvHeader( $dbResult ) );
#--- Add data rows
$sqlStart = "INSERT INTO `$tableName` VALUES ";
$tsv = '';
$sqlInserts = array();
while ( $row = mysql_fetch_array( $dbResult, MYSQL_NUM ) ) {
// Polish the whitespace (especially remove characters that would break the tsv file format)
$niceValues = preg_replace( '/\s+/', ' ', array_map( 'trim', $row ) );
// Data to write
$tsv .= implode( "\t", $niceValues ) . "\n";
$sqlInserts[] = "('" . implode( "','", array_map( 'addslashes', $niceValues ) ) . "')";
// Periodically write data so variable size doesn't explode
if ( strlen($tsv) > 200000 ) {
$sql = $sqlStart . implode( ",\n", $sqlInserts ) . ";\n";
file_put_contents( $tsvFile, $tsv, FILE_APPEND );
file_put_contents( $sqlFile, $sql, FILE_APPEND );
$tsv = '';
$sqlInserts = array();
echo '.'; # shows both Apache and the user that the script is doing stuff and not hanging
}
}
//Check if any sql need to be exported
if ($sqlInserts !== array()) {
$sql = $sqlStart . "\n" . implode( ",\n", $sqlInserts ) . ";\n";
file_put_contents( $tsvFile, $tsv, FILE_APPEND );
file_put_contents( $sqlFile, $sql, FILE_APPEND );
}
#--- Free the query result
mysql_free_result( $dbResult );
echo "<br />\n";
stopTimer( $tableName );
}
#------------------------------------------
# README
#------------------------------------------
#--- Build the README file
echo "<p><b>Build the README file</b></p>";
instantiateTemplate( 'README.txt', array( 'longDate' => wcaDate( 'F j, Y' ) ) );
#------------------------------------------
# ZIPs
#------------------------------------------
#--- Build the ZIP files
echo "<p><b>Build the ZIP files</b></p>";
$sqlZipFile = "$basename.sql.zip";
$tsvZipFile = "$basename.tsv.zip";
mySystem( "zip $sqlZipFile README.txt $sqlFile" );
mySystem( "zip $tsvZipFile README.txt *.tsv" );
#------------------------------------------
# HTML
#------------------------------------------
#--- Build the HTML file
echo '<p><b>Build the HTML file</b></p>';
instantiateTemplate( 'export.html', array(
'sqlZipFile' => $sqlZipFile,
'sqlZipFileSize' => sprintf( '%.1f MB', filesize( $sqlZipFile ) / 1000000 ),
'tsvZipFile' => $tsvZipFile,
'tsvZipFileSize' => sprintf( '%.1f MB', filesize( $tsvZipFile ) / 1000000 ),
'README' => file_get_contents( 'README.txt' ) ) );
#------------------------------------------
# DEPLOY
#------------------------------------------
#--- Move new files to public directory
echo '<p><b>Move new files to public directory</b></p>';
mySystem( "mv $sqlZipFile $tsvZipFile ../../misc/" );
mySystem( "mv export.html ../../misc/" );
#------------------------------------------
# CLEAN UP
#------------------------------------------
#--- Delete temporary and old stuff we don't need anymore
echo "<p><b>Delete temporary and old stuff we don't need anymore</b></p>";
mySystem( "rm README.txt $sqlFile *.tsv" );
mySystem( "rm ../../misc/$oldBasenameStart*" );
#------------------------------------------
# FINISHED
#------------------------------------------
#--- Tell the result
noticeBox ( true, "Finished $basename.<br />Have a look at <a href='../misc/export.html'>the results</a>." );
#--- Return to /admin
chdir( '..' );
}
$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)));
}
}
tableEnd();
stopTimer("printing the table");
<?php
showDatabaseStatistics();
stopTimer("whole page generation");
?>
<?php
if (!$standAlone) {
?>
</div>
</div>
<?php
}
?>
</body>
</html>
<?php
finishCache();
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 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");
}
