function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Alter field set");
dbCommand("\n ALTER TABLE Competitions\n DROP COLUMN `articles`,\n DROP COLUMN `reports`,\n DROP COLUMN `multimedia`\n ");
}
function alterTablePreregs()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Preregs", "Alter field set");
dbCommand("\n ALTER TABLE Preregs\n ADD COLUMN `eventIds` TEXT NOT NULL DEFAULT ''\n ");
$i = 0;
$len = 1000;
$preregs = dbQuery(" SELECT * FROM Preregs LIMIT {$i},{$len}");
while (count($preregs) != 0) {
foreach ($preregs as $prereg) {
$id = $prereg['id'];
$eventIds = '';
foreach (array_merge(getAllEventIds(), getAllUnofficialEventIds()) as $eventId) {
if ($prereg["E{$eventId}"] != 0) {
$eventIds .= "{$eventId} ";
}
}
rtrim($eventIds);
dbCommand("UPDATE Preregs SET eventIds='{$eventIds}' WHERE id='{$id}'");
}
$i += $len;
$preregs = dbQuery(" SELECT * FROM Preregs LIMIT {$i},{$len}");
}
foreach (array_merge(getAllEventIds(), getAllUnofficialEventIds()) as $eventId) {
dbCommand("ALTER TABLE Preregs\n DROP COLUMN `E{$eventId}`");
}
}
function alterTablePersons()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Persons", "Remove columns");
dbCommand("ALTER TABLE Persons\n DROP COLUMN `localName`,\n DROP COLUMN `romanName`");
}
function createTablePreregs()
{
#----------------------------------------------------------------------
#--- Create the table.
reportAction("Preregs", "Create");
foreach (getAllEvents() as $event) {
extract($event);
$eventFields .= "E{$id} BOOLEAN NOT NULL DEFAULT 0,";
}
dbCommand("\n CREATE TABLE Preregs (\n id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,\n competitionId VARCHAR(32) NOT NULL,\n name VARCHAR(80) NOT NULL,\n personId VARCHAR(10) NOT NULL,\n countryId VARCHAR(50) NOT NULL,\n gender CHAR(1) NOT NULL,\n birthYear SMALLINT(6) UNSIGNED NOT NULL,\n birthMonth TINYINT(4) UNSIGNED NOT NULL,\n birthDay TINYINT(4) UNSIGNED NOT NULL,\n email VARCHAR(80) NOT NULL,\n guests TEXT NOT NULL,\n comments TEXT NOT NULL,\n ip VARCHAR(16) NOT NULL,\n status CHAR(1) NOT NULL,\n {$eventFields}\n PRIMARY KEY ( id )\n )");
}
function alterTablePersons()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Persons", "Alter field set");
dbCommand("\n ALTER TABLE Persons\n ADD COLUMN `romanName` VARCHAR(80) CHARACTER SET utf8 NOT NULL DEFAULT ''\n ");
dbCommand("\n ALTER TABLE Persons\n CHANGE `name` `name` VARCHAR(80) CHARACTER SET utf8\n ");
dbCommand("\n ALTER TABLE Results\n CHANGE `personName` `personName` VARCHAR(80) CHARACTER SET utf8\n ");
dbCommand("\n ALTER TABLE Preregs\n CHANGE `name` `name` VARCHAR(80) CHARACTER SET utf8\n ");
dbCommand("\n UPDATE Persons\n SET romanName=name\n ");
}
function establishDatabaseAccess()
{
#----------------------------------------------------------------------
global $config;
$db_config = $config->get('database');
#--- Connect to the database server.
# TODO: Upgrade
mysql_connect($db_config['host'], $db_config['user'], $db_config['pass']) or showDatabaseError("Unable to connect to the database.");
#--- Select the database.
mysql_select_db($db_config['name']) or showDatabaseError("Unable to access the database.");
dbCommand("SET NAMES 'utf8'");
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Drop comments, rename eventIds to eventSpecs");
dbCommand("\n ALTER TABLE Competitions\n DROP COLUMN `comments`,\n CHANGE COLUMN `eventIds` `eventSpecs` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL\n ");
#--- Repace endMonth/endDay zeroes with positive numbers.
reportAction("Competitions", "Replace endMonth zeroes");
dbCommand("UPDATE Competitions SET endMonth=month WHERE endMonth=0");
#--- Repace endMonth/endDay zeroes with positive numbers.
reportAction("Competitions", "Replace endDay zeroes");
dbCommand("UPDATE Competitions SET endDay=day WHERE endDay=0");
}
function showUpdateSQL()
{
#----------------------------------------------------------------------
echo "<pre>I'm doing this:\n";
foreach (getRawParamsThisShouldBeAnException() as $key => $value) {
if (preg_match('/^setpos([1-9]\\d*)$/', $key, $match) && preg_match('/^[1-9]\\d*$/', $value)) {
$id = $match[1];
$command = "UPDATE Results SET pos={$value} WHERE id={$id}";
echo "{$command}\n";
dbCommand($command);
}
}
echo "\nFinished.</pre>\n";
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Alter field set");
dbCommand("\n ALTER TABLE Competitions\n ADD COLUMN `showAtAll` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showResults` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `password` VARCHAR(45) NOT NULL;\n ");
#--- Make showAtAll true, and showResults true for all competitions with results.
reportAction("Competitions", "Set {showAtAll,showResults}");
dbCommand("UPDATE Competitions SET showAtAll=1");
dbCommand("\n UPDATE Competitions competition, Results result\n SET competition.showResults = 1\n WHERE competition.id = result.competitionId;\n ");
#--- Generate passwords for all competitions.
reportAction("Competitions", "Generate passwords");
$competitions = dbQuery("SELECT id FROM Competitions");
foreach ($competitions as $competition) {
extract($competition);
$password = generateNewPassword($id);
dbCommand("\n UPDATE Competitions\n SET password='******'\n WHERE id='{$id}'\n ");
}
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Change columns");
dbCommand("ALTER TABLE Competitions\n ALTER COLUMN `showAtAll` SET DEFAULT '0'");
dbCommand("ALTER TABLE Competitions\n ADD COLUMN `adminPassword` VARCHAR(45) NOT NULL DEFAULT ''");
dbCommand("ALTER TABLE Competitions\n ADD COLUMN `isConfirmed` TINYINT(1) NOT NULL DEFAULT '0'");
dbCommand("ALTER TABLE Competitions\n CHANGE password organiserPassword VARCHAR(45)");
dbCommand("ALTER TABLE Competitions\n DROP COLUMN `showResults`");
#--- Generate admin passwords for all competitions.
reportAction("Competitions", "Generate admin passwords");
$competitions = dbQuery("SELECT id FROM Competitions");
foreach ($competitions as $competition) {
extract($competition);
$password = generateNewPassword($id);
dbCommand("\n UPDATE Competitions\n SET adminPassword='******'\n WHERE id='{$id}'\n ");
}
#--- Set all competitions to confirmed status.
reportAction("Competitions", "Change to confirmed");
dbCommand("\n UPDATE Competitions\n SET isConfirmed='1'\n ");
}
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";
}
function alterTableCountries()
{
#----------------------------------------------------------------------
reportAction("Countries", "Alter field set");
dbCommand("\n ALTER TABLE Countries\n ADD COLUMN `latitude` INTEGER NOT NULL DEFAULT 0,\n ADD COLUMN `longitude` INTEGER NOT NULL DEFAULT 0,\n ADD COLUMN `zoom` TINYINT NOT NULL DEFAULT 0\n ");
reportAction("Countries", "Fill");
dbCommand("\n UPDATE Countries\n SET latitude='50503887',\n longitude='4469936',\n zoom='7'\n WHERE id = 'Belgium'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='-14235004',\n longitude='-51925280',\n zoom='4'\n WHERE id = 'Brazil'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='56130366',\n longitude='-106346771',\n zoom='3'\n WHERE id = 'Canada'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='35861660',\n longitude='104195397',\n zoom='4'\n WHERE id = 'China'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='49817492',\n longitude='15472962',\n zoom='7'\n WHERE id = 'Czech Republic'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='56263920',\n longitude='9501785',\n zoom='6'\n WHERE id = 'Denmark'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='61924110',\n longitude='25748151',\n zoom='5'\n WHERE id = 'Finland'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='46227638',\n longitude='2213749',\n zoom='5'\n WHERE id = 'France'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='51165691',\n longitude='10451526',\n zoom='5'\n WHERE id = 'Germany'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='22396428',\n longitude='114109497',\n zoom='10'\n WHERE id = 'Hong Kong'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='47162494',\n longitude='19503304',\n zoom='7'\n WHERE id = 'Hungary'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='20593684',\n longitude='78962880',\n zoom='4'\n WHERE id = 'India'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='41871940',\n longitude='12567380',\n zoom='5'\n WHERE id = 'Italy'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='36204824',\n longitude='138252924',\n zoom='5'\n WHERE id = 'Japan'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='35907757',\n longitude='127766922',\n zoom='6'\n WHERE id = 'Korea'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='23634501',\n longitude='-102552784',\n zoom='5'\n WHERE id = 'Mexico'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='52132633',\n longitude='5291266',\n zoom='7'\n WHERE id = 'Netherlands'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='65146114',\n longitude='13183593',\n zoom='4'\n WHERE id = 'Norway'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='12879721',\n longitude='121774017',\n zoom='5'\n WHERE id = 'Philippines'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='51919438',\n longitude='19145136',\n zoom='6'\n WHERE id = 'Poland'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='39399872',\n longitude='-8224454',\n zoom='6'\n WHERE id = 'Portugal'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='40463667',\n longitude='-3749220',\n zoom='6'\n WHERE id = 'Spain'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='60128161',\n longitude='18643501',\n zoom='4'\n WHERE id = 'Sweden'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='46818188',\n longitude='8227512',\n zoom='7'\n WHERE id = 'Switzerland'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='23697810',\n longitude='120960515',\n zoom='7'\n WHERE id = 'Taiwan'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='55378051',\n longitude='-3435973',\n zoom='5'\n WHERE id = 'United Kingdom'\n ");
dbCommand("\n UPDATE Countries\n SET latitude='37090240',\n longitude='-95712891',\n zoom='4'\n WHERE id = 'USA'\n ");
}
function convertToUTF8()
{
#----------------------------------------------------------------------
reportAction("Persons", "Change names to UTF-8");
$persons = dbQuery("SELECT id, name FROM Persons");
foreach ($persons as $person) {
extract($person);
$utfname = mysql_real_escape_string(utf8_encode($name));
if ($utfname != $name) {
dbCommand("UPDATE Persons SET name='{$utfname}' WHERE id='{$id}'");
}
}
reportAction("Preregs", "Change names to UTF-8");
$preregs = dbQuery("SELECT id, name FROM Preregs");
foreach ($preregs as $prereg) {
extract($prereg);
$utfname = mysql_real_escape_string(utf8_encode($name));
if ($utfname != $name) {
dbCommand("UPDATE Preregs SET name='{$utfname}' WHERE id='{$id}'");
}
}
reportAction("Results", "Change names to UTF-8");
$step = 10000;
$from = 0;
$results = dbQuery("SELECT id, personName FROM Results ORDER BY id LIMIT {$from},{$step}");
# Can't do it in one query. Too big.
while (count($results) != 0) {
foreach ($results as $result) {
extract($result);
$utfname = mysql_real_escape_string(utf8_encode($personName));
if ($utfname != $personName) {
dbCommand("UPDATE Results SET personName='{$utfname}' WHERE id='{$id}'");
}
}
$from += $step;
$results = dbQuery("SELECT id, personName FROM Results ORDER BY id LIMIT {$from},{$step}");
}
reportAction("Competitions", "Change names to UTF-8");
$competitions = dbQuery("SELECT id, name, cityName, countryId FROM Preregs");
foreach ($preregs as $prereg) {
extract($prereg);
$utfname = mysql_real_escape_string(utf8_encode($name));
if ($utfname != $name) {
dbCommand("UPDATE Preregs SET name='{$utfname}' WHERE id='{$id}'");
}
}
}
function importLocalNames () {
#----------------------------------------------------------------------
global $chosenUpload, $chosenConfirm, $chosenNamesFile, $chosenFilename;
$oneBad = false;
$oneGood = false;
if( $chosenUpload ){
$upload_path = '../upload/';
if( $chosenFilename == '' )
$chosenFilename = 'tmp' . rand();
if( ! $chosenConfirm )
move_uploaded_file( $_FILES['namesFile']['tmp_name'], $upload_path . $chosenFilename . '.txt' );
$nameLines = file( $upload_path . $chosenFilename . '.txt', FILE_SKIP_EMPTY_LINES );
foreach( $nameLines as $nameLine ){
$nameLine = rtrim( $nameLine );
if( count( explode( ',', $nameLine )) != 2 ){
echo "<span style='color:#F00'>Wrong line syntax: <br /> " . htmlEscape( $nameLine ) . "</span><br />\n";
$oneBad = true;
continue;
}
list( $wcaId, $localName ) = explode( ',', $nameLine );
$wcaId = utf8_decode( $wcaId );
$persons = dbQuery( "SELECT name personName FROM Persons WHERE id='$wcaId' AND subId=1" );
if( count( $persons ) == 0 ){
echo "<span style='color:#DB0'>Unknown WCA id " . htmlEscape( $wcaId ) . "</span><br />\n";
$oneBad = true;
continue;
}
$person = array_shift( $persons );
extract( $person );
if( $chosenConfirm ){
$localName = mysql_real_escape_string( $localName );
$name = mysql_real_escape_string( extractRomanName( $personName )) . ' (' . $localName . ')';
$personName = mysql_real_escape_string( $personName );
dbCommand( "UPDATE Persons SET name='$name' WHERE id='$wcaId' AND subId=1" );
dbCommand( "UPDATE Results SET personName='$name' WHERE personId='$wcaId' AND personName='$personName'" );
$oneGood = true;
}
else{
$personLocalName = extractLocalName( $personName );
if( $localName == ''){
if( $personLocalName == '' ){}
else{
echo "<span style='color:#3C3'>I will remove name ".htmlEscape( $personLocalName )." from ".htmlEscape( $personName )."($wcaId)</span><br />\n";
}
}
else{
if( $personLocalName == '' ){
echo "<span style='color:#3C3'>I will add name ".htmlEscape( $localName )." to ".htmlEscape( $personName )."($wcaId)</span><br />\n";
}
else{
echo "<span style='color:#3C3'>I will change name ".htmlEscape( $personLocalName )." to ".htmlEscape( $localName )." for ".htmlEscape( $personName )."($wcaId)</span><br />\n";
}
}
}
}
if( $chosenConfirm ){
if(( $oneGood ) and ( ! $oneBad ))
noticeBox3( 1, "Complete. All names were updated." );
if(( $oneGood ) and ( $oneBad ))
noticeBox3( 0, "Complete. However, some lines were skipped." );
if(( ! $oneGood ) and ( $oneBad ))
noticeBox3( -1, "Cound't update anything." );
if(( ! $oneGood ) and ( ! $oneBad ))
noticeBox3( 0, "I found an empty text !?" );
$chosenUpload = false;
unlink( $upload_path . $chosenFilename . '.txt' );
}
else{
echo "<form method='POST'>\n";
echo "<input type='hidden' id='namesFile' name='namesFile' value='".htmlEscape($chosenNamesFile)."' />\n";
echo "<input type='hidden' id='upload' name='upload' value='$chosenUpload' />\n";
echo "<input type='hidden' id='filename' name='filename' value='".htmlEscape($chosenFilename)."' />\n";
echo "<input type='submit' id='confirm' name='confirm' value='Confirm' /></form>\n";
}
}
if( ! $chosenUpload ){
adminHeadline( 'Add local names' );
echo "<p>You can add or modify local names here, by upload a file containing the names. The file must be a plain text file encoded in UTF-8. Each line must contain: the WCA id, a comma (',') and the name you would like to add. If you want to remove a name from the database, just leave the name part blank.</p>\n";
echo "<p>Example: <br /><br />2009WANG20,王超<br />2009WANG62,王宇欣<br />2009WANG13,王宇轩<br />etc.</p>\n";
echo "<hr>\n";
echo "<table class='prereg'>\n";
echo " <form method='POST' enctype='multipart/form-data'>\n";
echo " <tr><td width='30%'><label for='namesFile'>Upload file: </label></td>\n";
echo " <td><input type='file' id='namesFile' name='namesFile' /></td>\n";
echo " <td><input type='submit' id='upload' name='upload' value='Upload' /></td></tr></form>\n";
echo "</table>\n";
}
}
function saveMedium()
{
#----------------------------------------------------------------------
global $chosenCompetitionId, $chosenType, $chosenText, $chosenUri;
global $chosenSubmitterName, $chosenSubmitterEmail, $chosenSubmitterComment;
global $chosenRecaptchaChallenge, $chosenRecaptchaResponse;
global $config;
$recaptcha = $config->get('recaptcha');
$resp = recaptcha_check_answer($recaptcha['privatekey'], $_SERVER["REMOTE_ADDR"], $chosenRecaptchaChallenge, $chosenRecaptchaResponse);
if (!$resp->is_valid) {
return false;
} else {
$command = "\n INSERT INTO CompetitionsMedia\n (competitionId, type, text, uri, submitterName, submitterEmail, submitterComment, status)\n VALUES\n ('{$chosenCompetitionId}', '{$chosenType}', '{$chosenText}', '{$chosenUri}',\n '{$chosenSubmitterName}', '{$chosenSubmitterEmail}', '{$chosenSubmitterComment}', 'pending')";
dbCommand($command);
return true;
}
}
function getTableCreationSql ( $tableName, $query ) {
#----------------------------------------------------------------------
#--- Get the creator code
dbCommand( "DROP TABLE IF EXISTS wca_export_helper" );
dbCommand( "CREATE TABLE wca_export_helper $query LIMIT 0" );
$rows = dbQuery( "SHOW CREATE TABLE wca_export_helper" );
dbCommand( "DROP TABLE IF EXISTS wca_export_helper" );
$creator = str_replace( 'wca_export_helper', $tableName, $rows[0][1] );
#--- Return DROP and CREATE
return "\nDROP TABLE IF EXISTS `$tableName`;\n$creator;\n\n";
}
function saveChoices () {
#----------------------------------------------------------------------
global $chosenId, $chosenConfirm, $chosenName, $chosenNameHtml, $chosenCountryId, $chosenGender, $chosenDay, $chosenMonth, $chosenYear, $chosenUpdate, $chosenFix;
if( $chosenFix ){
#--- Change the Results table if the name has been changed.
$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 );
$recordsMayHaveChanged = false;
// do not allow country to be 'fixed' (affects records).
$oldCountryId = $person['countryId'];
if($oldCountryId != $chosenCountryId) {
$hasBeenACitizenOfThisCountryAlready = count( dbQuery( "SELECT * FROM Persons WHERE id='$chosenId' AND countryId='$chosenCountryId'" ) ) > 0;
if( $hasBeenACitizenOfThisCountryAlready ) {
// If someone represented country A, and now represents country B, it's
// easy to tell which solves are which (results have a countryId).
// Fixing their country (B) to a new country C is easy to undo, just change
// all Bs to As. However, if someone accidentally fixes their country from B
// to A, then we cannot go back, as all their results are now for
// country A.
noticeBox(false, "Cannot change the country of someone to a country they have already represented in the past.");
return;
} else {
dbCommand( "UPDATE Results SET countryId='$chosenCountryId' WHERE personId='$chosenId' AND countryId='$oldCountryId'" );
$recordsMayHaveChanged = true;
}
}
$oldPersonName = $person['name'];
if( $oldPersonName != $chosenName ) {
dbCommand( "UPDATE Results SET personName='$chosenName' WHERE personId='$chosenId' AND personName='$oldPersonName'" );
}
#--- Change the Persons table
dbCommand( "UPDATE Persons SET name='$chosenName', countryId='$chosenCountryId', gender='$chosenGender', year='$chosenYear', month='$chosenMonth', day='$chosenDay' WHERE id='$chosenId' AND subId='1'" );
$msg = "Successfully fixed $chosenNameHtml($chosenId).";
if( $recordsMayHaveChanged ) {
$msg .= " The change you made may have affected records, be sure to run <a href='/results/admin/check_regional_record_markers.php'>check_regional_record_markers</a>.";
}
noticeBox( true, $msg );
}
if( $chosenUpdate ){
$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 );
if(( $person['name'] == $chosenName ) and ( $person['countryId'] == $chosenCountryId )){
noticeBox(false, 'The name or the country must be different.');
return;
}
dbCommand( "UPDATE Persons SET subId=subId+1 WHERE id='$chosenId'" );
dbCommand( "INSERT INTO Persons(id, subId, name, countryId, gender, year, month, day) VALUES( '$chosenId', '1', '$chosenName', '$chosenCountryId', '$chosenGender', '$chosenYear', '$chosenMonth', '$chosenDay')" );
noticeBox( true, "Successfully updated $chosenNameHtml($chosenId).");
}
}
function alterTableCompetitions()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Competitions", "Alter field set");
dbCommand("\n ALTER TABLE Competitions\n DROP COLUMN `comments`,\n DROP COLUMN `articles`,\n DROP COLUMN `reports`,\n DROP COLUMN `multimedia`,\n CHANGE COLUMN `eventIds` `eventSpecs` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,\n ADD COLUMN `preregPreface` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,\n ADD COLUMN `showAtAll` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showResults` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showPreregForm` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `showPreregList` BOOLEAN NOT NULL DEFAULT 0,\n ADD COLUMN `viewPassword` VARCHAR(45) NOT NULL,\n ADD COLUMN `editPassword` VARCHAR(45) NOT NULL;\n ");
#--- Make showAtAll true, and showResults true for all competitions with results.
reportAction("Competitions", "Set {showAtAll,showResults}");
dbCommand("UPDATE Competitions SET showAtAll=1");
dbCommand("\n UPDATE Competitions competition, Results result\n SET competition.showResults = 1\n WHERE competition.id = result.competitionId;\n ");
#--- Generate passwords for all competitions.
reportAction("Competitions", "Generate passwords");
$competitions = dbQuery("SELECT id FROM Competitions");
foreach ($competitions as $competition) {
extract($competition);
$password = generateNewPassword($id);
dbCommand("\n UPDATE Competitions\n SET viewPassword='******', editPassword='******'\n WHERE id='{$id}'\n ");
}
#--- Repace endMonth/endDay zeroes with positive numbers.
reportAction("Competitions", "Replace endMonth/endDay zeroes");
dbCommand("UPDATE Competitions SET endMonth=month WHERE endMonth=0");
dbCommand("UPDATE Competitions SET endDay=day WHERE endDay=0");
}
function saveMedium()
{
#----------------------------------------------------------------------
global $mediumId;
$type = getNormalParam('type');
$competitionId = getNormalParam('competitionId');
$text = getMysqlParam('text');
$link = getMysqlParam('link');
$submitterName = getMysqlParam('submitterName');
$submitterEmail = getMysqlParam('submitterEmail');
$submitterComment = getMysqlParam('submitterComment');
$command = "\n UPDATE CompetitionsMedia\n SET type='{$type}',\n competitionId='{$competitionId}',\n text='{$text}',\n uri='{$link}',\n submitterName='{$submitterName}',\n submitterEmail='{$submitterEmail}',\n submitterComment='{$submitterComment}'\n WHERE id={$mediumId}";
dbCommand($command);
echo "I just did this : \n";
echo $command;
}
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;
}
function moveOldMulti()
{
#----------------------------------------------------------------------
#--- Alter the field set.
reportAction("Results", "Move");
$results = dbQuery(" SELECT * FROM Results WHERE eventId='333mbo' ");
foreach ($results as $result) {
$oneGood = false;
$theBest = 0;
$values = array(0, 0, 0);
foreach (range(1, $result['formatId']) as $n) {
$value = $result["value{$n}"];
if ($value <= 0) {
$values[$n] = $value;
continue;
}
$old = intval($value / 1000000000);
if ($old) {
$time = $value % 100000;
$value = intval($value / 100000);
$attempted = $value % 100;
$value = intval($value / 100);
$solved = 99 - $value % 100;
$value = intval($value / 100);
$difference = 2 * $solved - $attempted;
} else {
$missed = $value % 100;
$value = intval($value / 100);
$time = $value % 100000;
$value = intval($value / 100000);
$difference = 99 - $value % 100;
$solved = $difference + $missed;
$attempted = $solved + $missed;
}
if ($time <= 3600 && $time <= 600 * $attempted && $difference >= 0) {
$oneGood = true;
$missed = $attempted - $solved;
$difference = $solved - $missed;
$value = 99 - $difference;
$value = $value * 100000 + $time;
$value = $value * 100 + $missed;
$values[$n] = $value;
if ($theBest > 0) {
$theBest = min($theBest, $value);
} else {
$theBest = $value;
}
} else {
$values[$n] = -2;
}
}
if ($oneGood) {
extract($result);
dbCommand(" INSERT INTO Results (pos, personId, personName, countryId, competitionId, eventId, roundId, formatId, value1, value2, value3, value4, value5, best, average, regionalSingleRecord, regionalAverageRecord)\n VALUES ('{$pos}', '{$personId}', '{$personName}', '{$countryId}', '{$competitionId}', '333mbf', '{$roundId}', '{$formatId}', '{$values['1']}', '{$values['2']}', '{$values['3']}', '0', '0', '{$theBest}', '{$average}', '{$regionalSingleRecord}', '{$regionalAverageRecord}')");
}
}
}
function checkEvents()
{
#----------------------------------------------------------------------
global $competitionCondition, $competitionDescription;
echo "<hr /><p>Checking <b> events for {$competitionDescription}</b>... (wait for the result message box at the end)</p>\n";
#--- Get events from Results and Competitions
$eventsResults = dbQuery("SELECT r.competitionId, r.eventId, c.eventSpecs FROM Results r, Competitions c WHERE c.id = r.competitionId AND r.eventId != '333mbo' {$competitionCondition} GROUP BY r.competitionId, r.eventId");
# Grossness to handle the fact that the competition id in the Competitions table
# is the "id" column, not the "competitionId" column.
$competitionConditionForCompetitionsTable = str_replace("competitionId", "id", $competitionCondition);
$eventsCompetition = dbQuery("SELECT id, eventSpecs FROM Competitions WHERE 1 {$competitionConditionForCompetitionsTable}");
#--- Group events by competitions.
foreach ($eventsResults as $eventResults) {
extract($eventResults);
$arrayEventsResults[$competitionId][] = $eventId;
}
foreach ($eventsCompetition as $eventCompetition) {
extract($eventCompetition);
$arrayEventsCompetition[$id] = getEventSpecsEventIds($eventSpecs);
}
$ok = true;
#--- Compare events.
if ($arrayEventsResults) {
foreach (array_keys($arrayEventsResults) as $competitionId) {
# Sort tables to compare them.
sort($arrayEventsResults[$competitionId], SORT_STRING);
sort($arrayEventsCompetition[$competitionId], SORT_STRING);
if ($arrayEventsResults[$competitionId] != $arrayEventsCompetition[$competitionId]) {
$ok = false;
echo "<p>Update competition {$competitionId}.<br />\n";
$intersect = array_intersect($arrayEventsResults[$competitionId], $arrayEventsCompetition[$competitionId]);
$resultsOnly = array_diff($arrayEventsResults[$competitionId], $arrayEventsCompetition[$competitionId]);
$competitionOnly = array_diff($arrayEventsCompetition[$competitionId], $arrayEventsResults[$competitionId]);
echo " Old events list: " . implode(' ', $intersect) . " <b style='color:#F00'>" . implode(' ', $competitionOnly) . "</b><br />\n";
echo " New events list: " . implode(' ', $intersect) . " <b style='color:#3C3'>" . implode(' ', $resultsOnly) . "</b><br />\n";
dbCommand("UPDATE Competitions SET eventSpecs='" . implode(' ', $arrayEventsResults[$competitionId]) . "' WHERE id='{$competitionId}'");
}
}
}
noticeBox2($ok, 'No mistakes found in the database', 'Some errors were fixed, you *should* check what has been updated');
}
function saveChoices()
{
#----------------------------------------------------------------------
global $chosenId, $chosenConfirm, $chosenName, $chosenNameHtml, $chosenCountryId, $chosenGender, $chosenDay, $chosenMonth, $chosenYear, $chosenUpdate, $chosenFix;
if ($chosenFix) {
#--- Change the Results table if the name has been changed.
$persons = pdo_query("SELECT * FROM Persons WHERE id=? AND subId=1", array($chosenId));
if (count($persons) == 0) {
noticeBox(false, 'Unknown WCA Id ' . $chosenId);
return;
}
$person = array_shift($persons);
$recordsMayHaveChanged = false;
// do not allow country to be 'fixed' (affects records).
$oldCountryId = $person['countryId'];
if ($oldCountryId != $chosenCountryId) {
$hasBeenACitizenOfThisCountryAlready = count(pdo_query("SELECT * FROM Persons WHERE id=? AND countryId=?", array($chosenId, $chosenCountryId))) > 0;
if ($hasBeenACitizenOfThisCountryAlready) {
// If someone represented country A, and now represents country B, it's
// easy to tell which solves are which (results have a countryId).
// Fixing their country (B) to a new country C is easy to undo, just change
// all Bs to As. However, if someone accidentally fixes their country from B
// to A, then we cannot go back, as all their results are now for
// country A.
noticeBox(false, "Cannot change the country of someone to a country they have already represented in the past.");
return;
} else {
pdo_query('UPDATE Results SET countryId=? WHERE personId=? AND countryId=?', array($chosenCountryId, $chosenId, $oldCountryId));
$recordsMayHaveChanged = true;
}
}
$oldPersonName = $person['name'];
if ($oldPersonName != $chosenName) {
pdo_query("UPDATE Results SET personName=? WHERE personId=? AND personName=?", array($chosenName, $chosenId, $oldPersonName));
}
#--- Change the Persons table
echo "changing name to {$chosenName}";
//<<<
pdo_query("UPDATE Persons SET name=?, countryId=?, gender=?, year=?, month=?, day=? WHERE id=? AND subId='1'", array($chosenName, $chosenCountryId, $chosenGender, $chosenYear, $chosenMonth, $chosenDay, $chosenId));
$msg = "Successfully fixed {$chosenNameHtml}({$chosenId}).";
if ($recordsMayHaveChanged) {
$msg .= " The change you made may have affected records, be sure to run <a href='/results/admin/check_regional_record_markers.php'>check_regional_record_markers</a>.";
}
noticeBox(true, $msg);
}
if ($chosenUpdate) {
$persons = pdo_query("SELECT * FROM Persons WHERE id=? AND subId=1", array($chosenId));
if (count($persons) == 0) {
noticeBox(false, 'Unknown WCA Id ' . $chosenId);
return;
}
$person = array_shift($persons);
if ($person['name'] == $chosenName and $person['countryId'] == $chosenCountryId) {
noticeBox(false, 'The name or the country must be different.');
return;
}
dbCommand("UPDATE Persons SET subId=subId+1 WHERE id='{$chosenId}'");
dbCommand("INSERT INTO Persons(id, subId, name, countryId, gender, year, month, day) VALUES( '{$chosenId}', '1', '{$chosenName}', '{$chosenCountryId}', '{$chosenGender}', '{$chosenYear}', '{$chosenMonth}', '{$chosenDay}')");
noticeBox(true, "Successfully updated {$chosenNameHtml}({$chosenId}).");
}
$users = pdo_query("SELECT * FROM users WHERE wca_id=?", array($person['id']));
if (count($users) > 0) {
$user = array_shift($users);
noticeBox(true, "Don't forget to re-save the user <a href='/users/{$user['id']}/edit'>here</a>! (simply clicking save on that page will copy over the data from the Persons table to the users table)");
}
}
function storeRanks ( $valueName, $eventId, &$personRecord, &$personWR, &$personCR, &$personNR ) {
if ( ! count( $personRecord ) )
return;
$values = array();
foreach ( $personRecord as $personId => $record ) {
$cr = isset($personCR[$personId]) ? $personCR[$personId] : 0; # It's possible have a world/continental rank (from
$nr = isset($personNR[$personId]) ? $personNR[$personId] : 0; # previous country) but no continental/national rank
$v = array( $personId, $eventId, $record, $personWR[$personId], $cr, $nr );
array_push( $values, "('" . implode( "', '", $v ) . "')" );
}
$values = implode( ",\n", $values );
dbCommand( "INSERT INTO Ranks$valueName (personId, eventId, best, worldRank, continentRank, countryRank) VALUES\n$values" );
}
function adaptResults ( $oldName, $oldCountry, $newName, $newCountry, $newId ) {
#----------------------------------------------------------------------
#--- Mysql-ify.
$oldName = mysqlEscape( $oldName );
$oldCountry = mysqlEscape( $oldCountry );
$newName = mysqlEscape( $newName );
$newCountry = mysqlEscape( $newCountry );
$newId = mysqlEscape( $newId );
#--- Build the command.
$command = "
UPDATE Results
SET personName='$newName', countryId='$newCountry', personId='$newId'
WHERE personName='$oldName' AND countryId='$oldCountry' AND personId=''
";
#--- Show the command.
echo colorize( $command );
#--- Execute the command.
dbCommand( $command );
}
