function noticeBox($isSuccess, $message)
{
if ($isSuccess) {
noticeBox3(1, $message);
} else {
noticeBox3(-1, $message);
}
}
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 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";
}
array( 'Records', 'regions' ),
array( 'Competitions', 'competitions' ),
array( 'Persons', 'persons' ),
array( 'Multimedia', 'media' ),
array( 'Statistics', 'statistics' ),
array( 'Misc', 'misc' ),
);
// "Remember" administrators - try to make this link persistent if people have visited an admin page.
// (see _session.php file for implementation)
if ($is_admin) {
$sections[] = array('Admin', 'admin', 'admin/');
}
if (!preg_match( '/^www.worldcubeassociation.org$/', $_SERVER["SERVER_NAME"])) {
noticeBox3( 0, "Note: This is only a copy of the WCA results system used for testing stuff. The official WCA results are at:<br /><a href='https://www.worldcubeassociation.org/results/'>https://www.worldcubeassociation.org/results/</a>" );
}
// only show errors in admin section
if($currentSection == 'admin' && isset($installation_errors) && !empty($installation_errors)) {
showErrors($installation_errors);
}
?>
<!-- TODO: Remove or reappropriate for Bootstrap.
<div id="pageMenuFrame">
<div id="pageMenu">
<ul class="navigation">
<?php print_menu($sections, $currentSection); ?>
</ul>
</div>
</div>
$has_results = 'N';
}
$tableOddRow = false;
// global used in tableRowStyled; we don't want stripes here.
$tableAttributes = array('', '', $round['hasevent'] ? $good_cell_attr : $bad_cell_attr, $round['hasscr'] ? $good_cell_attr : $bad_cell_attr);
// global used in tableRowStyled
tableRowStyled($class, array($round['event'] . ": ", $round['round'], $has_results, $has_scrambles));
}
tableEnd();
print "<strong style='color: #900'>Please be careful removing data! Data in the above table is live.</strong><br />";
print "Remove all results and scrambles only, does not affect persons: <a class='remove_link' href='scripts/remove_data.php?t=All&c={$compIdUrl}' target='_blank' title='Remove All'>X ALL</a>";
} else {
print "No fully imported result or scramble data exists to compare.";
}
print "</li></ol></li>";
// Final scripts...
$optionalScriptsCronSchedule = `crontab -l | grep cronned_results_scripts.sh`;
print "<li><p><strong>Optional</strong>: Run Some More Scripts (These are run on the following cron schedule: <code>{$optionalScriptsCronSchedule}</code>)</p>\n <ol type='a'>\n <li><a href='../statistics.php?update8392=1' target='_blank' class='link-external external'>update_statistics_page</a></li>\n <li><a href='export_public.php' target='_blank' class='link-external external'>export_public</a></li>\n </ol>\n </li>";
print "<li><p>Good job, you're done!</p></li>";
print "</div>";
// upload form is below this; these notices go last.
if (count($competition_has_results) > 0) {
noticeBox3(0, 'This competition has result data imported. Uploading more data may cause duplicate entries.');
}
if (count($competition_has_scrambles) > 0) {
noticeBox3(0, 'This competition has scramble data uploaded. Uploading more data may cause duplicate entries.
You may remove scrambles using the interface above.');
}
if (count($competition_has_inbox_results) > 0 || count($competition_has_inbox_persons) > 0) {
noticeBox3(0, 'This competition has temporary data uploaded. Uploading more data may cause duplicate entries.');
}
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 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";
}
}
