function migrate()
{
#----------------------------------------------------------------------
#--- Leave if we've done this migration already.
if (!databaseTableExists('ResultsStatus')) {
noticeBox(false, "You need to apply migation 1 first.");
return;
}
#--- Leave if we are already up-to-date
$number = dbQuery("\n SELECT value FROM ResultsStatus\n WHERE id = 'migration'\n ");
$number = $number[0]['value'];
if ($number != '4') {
noticeBox(false, "Wrong version number : " . $number . ". Must be 4");
return;
}
#--- ResultsStatus table: Update migration number.
reportAction("ResultsStatus", "Set migration number to 5");
dbCommand("\n UPDATE ResultsStatus\n SET value = '5'\n WHERE id = 'migration'\n ");
#--- Apply the migration changes.
alterTableCompetitions();
alterTableCountries();
alterTableContinents();
#--- Yippie, we did it!
noticeBox(true, "Migration completed.");
}
function showErrors($errors, $message = "Uh-oh! The following errors were encountered:")
{
if (!empty($errors)) {
$message = '<p>' . $message . '</p>';
$message .= '<ul>';
foreach ($errors as $error) {
$message .= '<li>' . $error . '</li>';
}
$message .= '</ul>';
noticeBox(FALSE, $message);
}
}
function migrate()
{
#----------------------------------------------------------------------
#--- Leave if we've done this migration already.
if (databaseTableExists('ResultsStatus')) {
noticeBox(false, "This migration has already been applied.");
return;
}
#--- ResultsStatus table: Create it.
reportAction("ResultsStatus", "Create");
dbCommand("\n CREATE TABLE ResultsStatus (\n id VARCHAR(50) NOT NULL,\n value VARCHAR(50) NOT NULL\n )\n ");
#--- ResultsStatus table: Set migration number.
reportAction("ResultsStatus", "Set migration number to 1");
dbCommand("INSERT INTO ResultsStatus (id, value) VALUES ('migration', '1')");
#--- Apply the migration changes.
alterTableCompetitions();
#--- Yippie, we did it!
noticeBox(true, "Migration completed.");
}
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( '..' );
}
function offerChoices () {
#----------------------------------------------------------------------
global $chosenId, $chosenConfirm;
adminHeadline( 'Change Person' );
echo "<p style='width:45em'>Choose 'Fix' if you want to replace a person's information in the database. It will modify the Persons table accordingly and the Results table if the person's name is different. This should be used to fix mistakes in the database.</p>\n";
echo "<p style='width:45em'>Choose 'Update' if the person's name or country has been changed. It will add a new entry in the Persons table and make it the current information for that person (subId=1) but it will not modify the Results table so previous results keep the old name.</p>\n";
echo "<hr />";
echo "<form method='POST'>\n";
echo "<table class='prereg'>\n";
textField( 'id', 'WCA Id', $chosenId, 11 );
if( ! $chosenConfirm ) {
echo "<tr><td> </td><td style='text-align:center'>";
echo "<input type='submit' id='confirm' name='confirm' value='Confirm' style='background-color:#9F3;font-weight:bold' /> ";
echo "</td></tr></table></form>";
return;
}
$persons = dbQuery( "SELECT * FROM Persons WHERE id='$chosenId' AND subId=1" );
if( count( $persons ) == 0 ){
noticeBox(false, 'Unknown WCA Id '.$chosenId);
return;
}
$person = array_shift( $persons );
extract( $person );
#--- Name
textField( 'name', 'Name', $name, 50 );
#--- Country
$countries = dbQuery( "SELECT * FROM Countries" );
$fieldC = '';
foreach( $countries as $country ){
$cId = $country['id' ];
$cName = $country['name'];
if( $cId == $countryId )
$fieldC .= " <option value=\"$cId\" selected='selected' >$cName</option>\n";
else
$fieldC .= " <option value=\"$cId\">$cName</option>\n";
}
echo " <tr><td width='30%'><label for='countryId'><b>Country</b></label></td>\n";
echo " <td><select id='countryId' name='countryId'>\n";
echo $fieldC;
echo " </select></td></tr>\n";
#--- Gender
if( $gender == 'm' )
$fieldG = "Male : <input type='radio' id='gender' name='gender' value='m' checked='checked' /> Female : <input type='radio' id='gender' name='gender' value='f' />";
else if( $gender == 'f' )
$fieldG = "Male : <input type='radio' id='gender' name='gender' value='m' /> Female : <input type='radio' id='gender' name='gender' value='f' checked='checked' />";
else
$fieldG = "Male : <input type='radio' id='gender' name='gender' value='m' /> Female : <input type='radio' id='gender' name='gender' value='f' />";
echo " <tr><td width='30%'><label for='gender'><b>Gender</b></label></td><td>$fieldG</td></tr>\n";
#--- DoB
echo " <tr><td><b>Date of birth</b></td><td>";
echo numberSelect( "day", "Day", 1, 31, $day );
echo numberSelect( "month", "Month", 1, 12, $month );
echo numberSelect( "year", "Year", date("Y"), date("Y")-100, $year );
echo "</td></tr>\n";
#--- Submit
echo "<tr><td> </td><td style='text-align:center'>";
echo "<input type='submit' id='update' name='update' value='Update' style='background-color:#9F3;font-weight:bold' /> ";
?>
<script>
(function() {
$('#update').click(function(e) {
if(!confirm("Are you sure that you want to \"update\", not \"fix\", the competitor's data? (see information above)")) {
e.preventDefault();
}
});
})();
</script>
<?php
echo "<input type='submit' id='fix' name='fix' value='Fix' style='background-color:#9F3;font-weight:bold' /> ";
echo "</td></tr></table></form>";
}
function savePreregForm()
{
#----------------------------------------------------------------------
global $chosenCompetitionId, $competition, $config;
$personId = getMysqlParam('personId');
$name = getMysqlParam('name');
$countryId = getMysqlParam('countryId');
$gender = getMysqlParam('gender');
$birthYear = getMysqlParam('birthYear');
$birthMonth = getMysqlParam('birthMonth');
$birthDay = getMysqlParam('birthDay');
$email = getMysqlParam('email');
$guests = getMysqlParam('guests');
$comments = getMysqlParam('comments');
$ip = getMysqlParam('ip');
if (!$name or !$email or !$gender) {
noticeBox(false, "Fields 'name', 'gender' and 'email' are required.");
return false;
}
if (!preg_match("/^[_0-9a-zA-Z-]+(\\.[_0-9a-zA-Z-]+)*@[0-9a-zA-Z-]+(\\.[0-9a-zA-Z-]+)*(\\.[a-zA-Z]{2,3})\$/", $email)) {
noticeBox(false, "Incorrect email address.");
return false;
}
if ($birthYear == date('Y')) {
noticeBox(false, "Please enter your date of birth.");
return false;
}
if (!$birthYear) {
$chosenPerson = dbQuery("SELECT * FROM Persons WHERE id='{$personId}'");
$chosenPerson = $chosenPerson[0];
$birthYear = $chosenPerson['year'];
$birthMonth = $chosenPerson['month'];
$birthDay = $chosenPerson['day'];
}
$guests = str_replace(array("\r\n", "\n", "\r", ","), ";", $guests);
#--- Building query
$eventIds = '';
foreach (getAllEventIds() as $eventId) {
if (getBooleanParam("E{$eventId}")) {
$eventIds .= "{$eventId} ";
}
}
rtrim($eventIds);
# Remove last space
$into = "competitionId, name, personId, countryId, gender, birthYear, birthMonth, birthDay, email, guests, comments, ip, status, eventIds";
$values = "'{$chosenCompetitionId}', '{$name}', '{$personId}', '{$countryId}', '{$gender}', '{$birthYear}', '{$birthMonth}', '{$birthDay}', '{$email}', '{$guests}', '{$comments}', '{$ip}', 'p', '{$eventIds}'";
dbCommand("INSERT INTO Preregs ({$into}) VALUES ({$values})");
$organizers = getCompetitionOrganizers($competition['id']);
foreach ($organizers as $organizer) {
$mailEmail = $organizer['email'];
// load more competition data for a nicer email
$result = dbQuery("SELECT * FROM Competitions WHERE id='{$chosenCompetitionId}'");
$competition_data = $result[0];
$mailBody = "A new competitor has registered for your competition - " . $competition['cellName'] . "! ";
$mailBody .= "Their information is below.\n-------------------\n";
if ($personId) {
$mailBody .= "Name : {$name}";
$mailBody .= " {$personId} - https://www.worldcubeassociation.org/results/p.php?i={$personId}\n";
} else {
$mailBody .= "Name : {$name}\n";
}
$mailBody .= "Country : {$countryId}\n";
$mailBody .= "Gender : {$gender}\n";
$mailBody .= "Date of birth : {$birthYear}/{$birthMonth}/{$birthDay}\n";
$mailBody .= "Email : {$email}\n";
$mailBody .= "Events : {$eventIds}\n";
$mailBody .= "Guests : {$guests}\n";
$mailBody .= "Comments : {$comments}\n";
$mailBody .= "Ip : {$ip}\n";
$mailBody .= "-------------------\n";
$mailBody .= "You may edit this registration (and others) at:\n";
$mailBody .= "https://www.worldcubeassociation.org/competitions/{$chosenCompetitionId}/registrations";
$mailSubject = $competition['cellName'] . " - New registration";
$mail_config = $config->get('mail');
// only send mails on the real website
if (preg_match('/^www.worldcubeassociation.org$/', $_SERVER["SERVER_NAME"])) {
if ($mail_config['pear']) {
// send smtp mail
$headers = array('From' => $mail_config['from'], 'To' => $mailEmail, 'Subject' => $mailSubject);
$smtp = Mail::factory('smtp', array('host' => $mail_config['host'], 'port' => $mail_config['port'], 'auth' => true, 'username' => $mail_config['user'], 'password' => $mail_config['pass']));
$mail = $smtp->send($mailEmail, $headers, $mailBody);
} else {
// normal php mail
$mailHeaders = "From: \"WCA\" <" . $mail_config['from'] . ">\r\n";
$mailHeaders .= "Reply-To: board@worldcubeassociation.org\r\n";
$mailHeaders .= "MIME-Version: 1.0\r\n";
$mailHeaders .= "Content-Type: text/plain; charset=UTF-8\r\n";
mail($mailEmail, $mailSubject, $mailBody, $mailHeaders, "-f" . $mail_config['from']);
}
} else {
// just print out message when testing
noticeBox3(0, "Mail not sent (test website): " . $mailBody);
}
}
noticeBox(true, "Registration complete.<br />Please note that all registrations must be approved by the organizer.<br/>Your registration will appear here within a few days.");
return true;
}
<?php
#----------------------------------------------------------------------
# Initialization and page contents.
#----------------------------------------------------------------------
$currentSection = 'competitions';
require 'includes/_header.php';
#--- Get the parameters.
analyzeChoices();
#--- Get all competition infos.
$competition = getFullCompetitionInfos($chosenCompetitionId);
#--- If competition not found, say so and stop.
if (!$competition || !$competition['showAtAll']) {
noticeBox(false, "Unknown competition ID \"{$chosenCompetitionId}\"");
require 'includes/_footer.php';
exit(0);
}
#--- Show competition infos.
require 'includes/competition_infos.php';
showCompetitionInfos();
if (wcaDate('Ymd') >= 10000 * $competition['year'] + 100 * $competition['month'] + $competition['day']) {
#--- Try the cache
# tryCache( 'competition', $chosenCompetitionId, $chosenByPerson, $chosenAllResults, $chosenTop3, $chosenWinners );
#--- Show competition results...
offerChoicesResults();
require 'includes/competition_results.php';
if ($chosenByPerson) {
showCompetitionResultsByPerson();
} else {
showCompetitionResults();
}
function showUpdateSQL()
{
#----------------------------------------------------------------------
echo "<pre>I'm doing this:\n";
foreach (getRawParamsThisShouldBeAnException() as $key => $value) {
if (preg_match('/^setround(\\w*)\\/(\\w*)\\/(\\w*)$/', $key, $match)) {
$competitionId = $match[1];
$eventId = $match[2];
$roundId = $match[3];
$updateRounds[$competitionId][$eventId][$roundId] = $value;
}
if (preg_match('/^confirmround(\\w*)\\/(\\w*)$/', $key, $match)) {
$competitionId = $match[1];
$eventId = $match[2];
$updateRounds[$competitionId][$eventId]['confirm'] = 1;
// 'confirm' should not be a valid roundId
}
if (preg_match('/^deleteres([1-9]\\d*)$/', $key, $match)) {
$id = $match[1];
$command = "DELETE FROM Results WHERE id={$id}";
echo "{$command}\n";
dbCommand($command);
}
}
foreach ($updateRounds as $competitionId => $eventIds) {
foreach ($eventIds as $eventId => $roundIds) {
if ($roundIds['confirm'] != 1) {
continue;
}
unset($roundIds['confirm']);
// We have to make the replacement in the right order
// Remove trivial replacements
foreach ($roundIds as $roundIdOld => $roundIdNew) {
if ($roundIdOld == $roundIdNew) {
unset($roundIds[$roundIdOld]);
}
}
// Delete rounds
foreach ($roundIds as $roundIdOld => $roundIdNew) {
if ($roundIdNew == 'del') {
$command = "DELETE FROM Results\n WHERE competitionId='{$competitionId}'\n AND eventId='{$eventId}'\n AND roundId='{$roundIdOld}'";
echo "{$command}\n";
dbCommand($command);
unset($roundIds[$roundIdOld]);
}
}
foreach (range(0, 5) as $i) {
// Safer to use a for statement
if (count($roundIds) == 0) {
break;
}
foreach ($roundIds as $roundIdOld => $roundIdNew) {
// We can replace a roundId with another one if the new one will not be replaced again
if (!in_array($roundIdNew, array_keys($roundIds))) {
// Replace
$command = "UPDATE Results\n SET roundId='{$roundIdNew}'\n WHERE competitionId='{$competitionId}'\n AND eventId='{$eventId}'\n AND roundId='{$roundIdOld}'";
echo "{$command}\n";
dbCommand($command);
unset($roundIds[$roundIdOld]);
// Remove from the list of replacements
}
}
}
if ($i == 5) {
noticeBox(false, "Found a loop for competition {$competitionId} and event {$eventId}");
}
}
}
echo "\nFinished.</pre>\n";
}
