function update_match_stats_entered($team_id1, $team_id2, $team1_points, $team2_points, $site, $connection)
{
// increase match count for teams that participated
$query = 'UPDATE `teams_profile` SET `num_matches_total`=`num_matches_total`+1';
$query .= ' WHERE (`teamid`=' . sqlSafeStringQuotes($team_id1) . ' OR `teamid`=' . sqlSafeStringQuotes($team_id2) . ')';
if (!($result = $site->execute_query('teams_overview', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('The match count for the teams with id' . sqlSafeString($team_id1) . ' and ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!');
}
// mark both participating teams as active
$query = 'UPDATE `teams_overview` SET `deleted`=' . sqlSafeStringQuotes('1') . ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id1) . ' OR `teamid`=' . sqlSafeStringQuotes($team_id2) . ' LIMIT 2';
if (!($result = @$site->execute_query('teams_overview', $query, $connection))) {
$site->dieAndEndPage('Could not mark team with id ' . sqlSafeString($teamid) . ' as active!');
}
// increase match win count for teams that participated
if ($team1_points > $team2_points) {
// team 1 won
$query = 'UPDATE `teams_profile` SET `num_matches_won`=`num_matches_won`+1';
$query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id1);
if (!($result = $site->execute_query('teams_profile', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('The match win count for team ' . sqlSafeString($team_id1) . ' could not be updated due to a sql problem!');
}
// team 2 lost
$query = 'UPDATE `teams_profile` SET `num_matches_lost`=`num_matches_lost`+1';
$query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id2);
if (!($result = $site->execute_query('teams_profile', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('The match lose count for team ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!');
}
}
if ($team1_points < $team2_points) {
// team 2 won
$query = 'UPDATE `teams_profile` SET `num_matches_won`=`num_matches_won`+1';
$query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id2);
if (!($result = $site->execute_query('teams_profile', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('The match win count for team ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!');
}
// team 1 lost
$query = 'UPDATE `teams_profile` SET `num_matches_lost`=`num_matches_lost`+1';
$query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id1);
if (!($result = $site->execute_query('teams_profile', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('The match lose count for team ' . sqlSafeString($team_id1) . ' could not be updated due to a sql problem!');
}
}
// match entered ended in a draw
if ((int) $team1_points === (int) $team2_points) {
$query = 'UPDATE `teams_profile` SET `num_matches_draw`=`num_matches_draw`+1';
$query .= ' WHERE (`teamid`=' . sqlSafeStringQuotes($team_id1) . ' OR `teamid`=' . sqlSafeStringQuotes($team_id2) . ')';
if (!($result = $site->execute_query('teams_profile', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('The match draw count for the teams with id' . sqlSafeString($team_id1) . ' and ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!');
}
}
}
// the range of shown matches is set by the GET variable i
if (isset($_GET['i'])) {
if ((int) $_GET['i'] > 0) {
$view_range = (int) $_GET['i'];
$query .= $view_range . ',';
} else {
// force write 0 for value 0 (speed saving due to no casting to string)
// and 0 for negative values (security: DBMS error handling prevention)
$query .= '0,';
}
} else {
// no special value set -> write 0 for value 0 (speed)
$query .= '0,';
}
// limit the number of displayed rows regarding the user's wish
$query .= sqlSafeString($num_results + 1);
if (!($result = @$site->execute_query('matches', $query, $connection))) {
$site->dieAndEndPageNoBox('The list of matches could not be displayed because of an SQL/database connectivity problem.');
}
$rows = (int) mysql_num_rows($result);
$show_next_matches_button = false;
// more than wished match entries per page available in total
if ($rows > $num_results) {
$show_next_matches_button = true;
}
if ($rows === (int) 0) {
echo '<p>No matches have been played yet.</p>' . "\n";
setTableUnchanged($site, $connection);
$site->dieAndEndPageNoBox();
}
unset($rows);
private function sanityCheck(&$confirmed)
{
global $randomkey_name;
global $team_id1;
global $team_id2;
global $team1_caps;
global $team2_caps;
global $timestamp;
global $match_id;
global $similarMatchFound;
global $db;
// sanitise match id
if (isset($_GET['edit'])) {
$match_id = intval($_GET['edit']);
}
if (isset($_GET['delete'])) {
$match_id = intval($_GET['delete']);
}
// sanitise team variables
if (isset($_POST['match_team_id1'])) {
$team_id1 = intval($_POST['match_team_id1']);
} elseif (isset($_POST['team_id1'])) {
$team_id1 = intval($_POST['team_id1']);
} else {
$team_id1 = 0;
}
if ($team_id1 < 1) {
$team_id1 = 0;
}
if (isset($_POST['match_team_id2'])) {
$team_id2 = intval($_POST['match_team_id2']);
} elseif (isset($_POST['team_id2'])) {
$team_id2 = intval($_POST['team_id2']);
} else {
$team_id2 = 0;
}
if ($team_id2 < 1) {
$team_id2 = 0;
}
// do the teams exist?
// teams specified?
if (!isset($_GET['delete']) && ($team_id1 > 0 && $team_id2 > 0)) {
$team_exists = 0;
$query = $db->prepare('SELECT COUNT(`id`) as `team_exists` FROM `teams` WHERE `id`=? LIMIT 1');
if (!($result = $db->execute($query, $team_id1))) {
$db->logError('Could not find out name of team #' . $team_id1 . '.');
}
while ($row = $db->fetchRow($query)) {
$team_exits = intval($row['team_exists']);
}
$db->free($query);
if ($team_exits === 0) {
echo '<p>Error: The specified team #1 does not exist</p>';
$confirmed = 'checkTeam1';
return;
}
// reset variable for team 2
$team_exits = 0;
$query = $db->prepare('SELECT COUNT(`id`) as `team_exists` FROM `teams` WHERE `id`=? LIMIT 1');
if (!($result = $db->execute($query, $team_id2))) {
$db->logError('Could not find out name of team #' . sqlSafeString($team_id2) . '.');
}
while ($row = $db->fetchRow($query)) {
$team_exits = intval($row['team_exists']);
}
$db->free($query);
if ($team_exits === 0) {
echo '<p>Error: The specified team #2 does not exist</p>';
$confirmed = 'checkTeam2';
return;
}
// teams are the same (and chosen by user)
if ($team_id1 > 0 && $team_id2 > 0 && $team_id1 === $team_id2) {
echo '<p>In order to be an official match, teams would have to be different!</p>';
$confirmed = 'checkDifferentTeams';
return;
}
}
// sanitise score variables
if (isset($_POST['team1_points'])) {
$team1_caps = intval($_POST['team1_points']);
} else {
$team1_caps = 0;
}
if (isset($_POST['team2_points'])) {
$team2_caps = intval($_POST['team2_points']);
} else {
$team2_caps = 0;
}
// sanitise day and time variables
if (isset($_POST['match_day'])) {
$match_day = $_POST['match_day'];
} else {
$match_day = date('Y-m-d');
}
if (isset($_POST['match_time'])) {
$match_time = $_POST['match_time'];
} else {
$match_time = date('H:i:s');
}
if (isset($_POST['match_day']) && isset($_POST['match_time'])) {
$timestamp = $_POST['match_day'] . ' ' . $_POST['match_time'];
}
// user wants to edit match data again
if (isset($_POST['match_cancel'])) {
$confirmed = 'edit';
return;
}
if (isset($_POST['$match_id'])) {
$match_id = intval($_POST['$match_id']);
}
// does the match exit?
if (isset($match_id)) {
$query = $db->prepare('SELECT `id` FROM `matches` WHERE `id`=?');
if (!($result = $db->execute($query, $match_id))) {
$db->logError('Could not find out id for team 1 given match id ' . $match_id . ' due to a sql problem!');
}
if (intval($db->rowCount($query)) < 1) {
// match did not exist!
$confirmed = 'checkMatch';
}
}
// sanitise date and time specified
// sanity checks regarding day format
// sample day: 2009-12-15
if (!preg_match('/(2)(0|1|2|3|4|5|6|7|8|9){3,}-(0|1)(0|1|2|3|4|5|6|7|8|9)-(0|1|2|3)(0|1|2|3|4|5|6|7|8|9)/', $match_day)) {
echo '<p>Please make sure your specified date is in correct format. Do not forget leading zeros.</p>' . "\n";
$confirmed = 'no';
return;
}
// sanity checks regarding time format
// sample time: 15:21:35
if (!preg_match('/(0|1|2)([0-9]):([0-5])([0-9]):([0-5])([0-9])/', $match_time)) {
echo '<p>Please make sure your specified time is in correct format. Do not forget leading zeros.</p>' . "\n";
$confirmed = 'no';
return;
}
// get the unix timestamp from the date and time
if (!($specifiedTime = strtotime($match_day . ' ' . $match_time))) {
echo '<p>Please make sure your specified date and time is valid!</p>' . "\n";
$confirmed = 'no';
return;
}
// look up if the day does exist in Gregorian calendar
// checkdate expects order to be month, day, year
if (!checkdate(date('m', $specifiedTime), date('d', $specifiedTime), date('Y', $specifiedTime))) {
echo '<p>Please make sure your specified date and time is a valid Gregorian date.</p>' . "\n";
$confirmed = 'no';
return;
}
// is match in the future?
if (isset($timestamp)) {
$curTime = (int) strtotime('now');
if ((int) $specifiedTime - $curTime >= 0) {
echo '<p>You tried to enter, edit or delete a match that would have been played in the future.';
echo ' Only matches in the past can be entered, edited or deleted.</p>' . "\n";
$confirmed = 'no';
return;
}
}
// is match older than 2 months?
$eightWeeksAgo = (int) strtotime('now -8 weeks');
if ((int) $specifiedTime <= $eightWeeksAgo) {
echo '<p>You tried to enter, edit or delete a match that is older than 8 weeks.' . 'Only matches played in the last 8 weeks can be entered, edited or deleted.</p>' . "\n";
$confirmed = 'no';
return;
}
// check if there is already a match entered at that time
// scores depend on the order, two matches done at the same time lead to undefined behaviour
$query = $db->prepare('SELECT `timestamp` FROM `matches` WHERE `timestamp`=?');
if (!($result = $db->execute($query, $timestamp))) {
unlock_tables();
$db->logError('Unfortunately there seems to be a database problem' . ' and thus comparing timestamps (using equal operator) of matches failed.');
}
$rows = (int) $db->rowCount($query);
$db->free($query);
if ($rows > 0 && !isset($_GET['edit']) && !isset($_GET['delete'])) {
// go back to the first step of entering a match
echo '<p>There is already a match entered at that exact time.';
echo ' There can be only one finished at the same time because the scores depend on the order of the played matches.</p>' . "\n";
// just warn them and let them enter it all again by hand
echo 'Please enter the match with a different time.</p>' . "\n";
echo '<form enctype="application/x-www-form-urlencoded" method="post" action="?enter">' . "\n";
echo '<div>';
$site->write_self_closing_tag('input type="hidden" name="confirmed" value="0"');
echo '</div>' . "\n";
// pass the match values to the next page so the previously entered data can be set default for the new form
show_form($team_id1, $team_id2, $team1_caps, $team2_caps, $readonly = false);
echo '<div>';
$site->write_self_closing_tag('input type="submit" name="match_cancel" value="Cancel and change match data" id="send"');
echo '</div>' . "\n";
echo '</form>' . "\n";
$site->dieAndEndPage();
}
// random key validity check
if ($confirmed === 'action') {
$new_randomkey_name = '';
if (isset($_POST['key_name'])) {
$new_randomkey_name = html_entity_decode($_POST['key_name']);
}
$randomkeysmatch = $site->compare_keys($randomkey_name, $new_randomkey_name);
if (!$randomkeysmatch) {
echo '<p>The magic key did not match. It looks like you came from somewhere else. Going back to compositing mode.</p>';
// reset the confirmed value
$confirmed = 'no';
}
}
// check for similar match in database and warn user if at least one was found
// skip warning if already warned (no infinite warning loop)
if ($confirmed === 'action' && !isset($_POST['similar_match'])) {
// find out if there are similar matches
$similarMatchFound = false;
$similarMatchFound = similarMatchEntered(true);
if (!$similarMatchFound) {
// look for a possible last show stopper
$similarMatchFound = similarMatchEntered(false);
} else {
// add space between last similar match and the one probably following
$site->write_self_closing_tag('br');
// only call the function for user information, ignore result
similarMatchEntered(false);
}
if ($similarMatchFound) {
// ask for confirmation again and do not go ahead automatically
$confirmed = 'no';
}
}
// no double confirmation about deletion - user saw confirmation step with $confirmed = 0 already
if ($confirmed === 'action' && isset($_GET['delete'])) {
$confirmed = 'action';
}
}
function sqlSafeStringQuotes($param)
{
// use sqlSafeString and append quotes before and after the result
return "'" . sqlSafeString($param) . "'";
}
function decrease_draw_match_count($teamid)
{
global $connection;
global $site;
$query = 'UPDATE `teams_profile` SET ';
$query .= '`num_matches_draw`=`num_matches_draw`-' . sqlSafeStringQuotes('1');
$query .= ' WHERE (`teamid`=' . sqlSafeStringQuotes($teamid) . ')';
// only one team needs to be updated
$query .= ' LIMIT 1';
if (!($result = $site->execute_query('teams_profile', $query, $connection))) {
unlock_tables($site, $connection);
$site->dieAndEndPage('Could not update win/play count for team with id ' . sqlSafeString($teamid) . ' due to a sql problem!');
}
}
}
}
echo '<p>The player profile has been updated successfully.</p>' . "\n";
$site->dieAndEndPage('');
}
// display editing form
echo '<form enctype="application/x-www-form-urlencoded" method="post" action="?edit=' . $profile . '">' . "\n";
echo '<div><input type="hidden" name="confirmed" value="1"></div>' . "\n";
$new_randomkey_name = $randomkey_name . microtime();
$new_randomkey = $site->set_key($new_randomkey_name);
echo '<div><input type="hidden" name="key_name" value="' . htmlspecialchars($new_randomkey_name) . '"></div>' . "\n";
echo '<div><input type="hidden" name="' . htmlspecialchars($randomkey_name) . '" value="';
echo urlencode($_SESSION[$new_randomkey_name]) . '"></div>' . "\n";
$query = 'SELECT `location`, `UTC`';
$query .= ', `raw_user_comment`, `raw_admin_comments`';
$query .= ', `logo_url` FROM `users_profile` WHERE `id`=' . "'" . sqlSafeString($profile) . "'";
$query .= ' LIMIT 1';
if (!($result = @$site->execute_query('users_profile', $query, $connection))) {
// query was bad, error message was already given in $site->execute_query(...)
$site->dieAndEndPage('');
}
$location = 0;
$timezone = 0;
$user_comment = '';
$admin_comments = '';
while ($row = mysql_fetch_array($result)) {
$location = (int) $row['location'];
$timezone = (int) $row['UTC'];
$user_comment = $row['raw_user_comment'];
$admin_comments = $row['raw_admin_comments'];
$logo_url = $row['logo_url'];
if ($site->use_xtml()) {
echo '<br />' . "\n";
} else {
echo '<br>' . "\n";
}
echo '<a class="button" href="../PM/?add&userid=' . intval($profile) . '">Send private message to player</a>' . "\n";
$allow_invite_in_any_team = false;
if (isset($_SESSION['allow_invite_in_any_team'])) {
if ($_SESSION['allow_invite_in_any_team'] === true) {
$allow_invite_in_any_team = true;
}
}
// 0 is a reserved value and stands for no team
$leader_of_team_with_id = 0;
if (!$allow_invite_in_any_team) {
$query = 'SELECT `id` FROM `teams` WHERE `leader_userid`=' . "'" . sqlSafeString($viewerid) . "'" . ' LIMIT 1';
if (!($result = @$site->execute_query('teams', $query, $connection))) {
$site->dieAndEndPage('A database related problem prevented to find out if the viewer of this site is the leader of a team.');
}
// if the viewer is leader of a team, a value other than 0 will be the result of the query
// and that value will be the id of the team the viewer is leader
while ($row = mysql_fetch_array($result)) {
$leader_of_team_with_id = $row['id'];
}
}
// users are not supposed to invite themselves
if (($allow_invite_in_any_team || $leader_of_team_with_id > 0 && $viewerid !== $profile) && strcmp($suspended_status, 'deleted') !== 1) {
echo '<a class="button" href="?invite=' . htmlspecialchars(urlencode($profile)) . '">Invite player to team</a>' . "\n";
}
if (isset($_SESSION['allow_view_user_visits']) && $_SESSION['allow_view_user_visits'] === true && strcmp($suspended_status, 'deleted') !== 1) {
echo '<a class="button" href="../Visits/?profile=' . htmlspecialchars($profile) . '">View visits log</a>' . "\n";
function delete_match($match_id)
{
global $site;
global $connection;
global $tables_locked;
global $viewerid;
lock_tables();
// who entered/edited the match before?
$userid = 0;
// find out the appropriate team id list for the edited match (to modify total/win/draw/loose count)
$query = 'SELECT `userid`, `timestamp`, `team1_id`, `team2_id`,' . ' `team1_points`, `team2_points`, `team1_new_score`, `team2_new_score`, `duration` FROM `matches`' . ' WHERE `id`=' . sqlSafeStringQuotes($match_id);
if (!($result = $site->execute_query('matches', $query, $connection))) {
unlock_tables();
$site->dieAndEndPage('Could not find out id for team 1 given match id ' . sqlSafeString($match_id) . ' due to a sql problem!');
}
while ($row = mysql_fetch_array($result)) {
$userid = intval($row['userid']);
$timestamp = $row['timestamp'];
$team_id1 = intval($row['team1_id']);
$team_id2 = intval($row['team2_id']);
$team1_caps = intval($row['team1_points']);
$team2_caps = intval($row['team2_points']);
$duration = intval($row['duration']);
}
mysql_free_result($result);
// prepare to update win/draw/loose count
// create array that keeps track of team score changes
$team_stats_changes = array();
// mark the participating teams as potentially having a changed score
$team_stats_changes[$team_id1] = '';
$team_stats_changes[$team_id2] = '';
// save old match into edit history table
$query = 'INSERT INTO `matches_edit_stats` (`match_id`, `userid`, `timestamp`, `team1_id`,' . ' `team2_id`, `team1_points`, `team2_points`, `duration`) VALUES (' . sqlSafeStringQuotes($match_id) . ', ' . sqlSafeStringQuotes($userid) . ', ' . sqlSafeStringQuotes($timestamp) . ', ' . sqlSafeStringQuotes($team_id1) . ', ' . sqlSafeStringQuotes($team_id2) . ', ' . sqlSafeStringQuotes($team1_caps) . ', ' . sqlSafeStringQuotes($team2_caps) . ', ' . sqlSafeStringQuotes($duration) . ')';
if (!($result = $site->execute_query('matches_edit_stats', $query, $connection))) {
unlock_tables();
$site->dieAndEndPage('The match reported by user #' . sqlSafeString($viewerid) . ' could not be entered due to a sql problem!');
}
// we saved the old match in the editing stats thus we can now delete the actual match
// update match table (perform the actual editing)
// use current row id to access the entry
// only one row needs to be updated
$query = 'DELETE FROM `matches` WHERE `id`=' . sqlSafeStringQuotes($match_id) . ' LIMIT 1';
if (!($result = $site->execute_query('matches', $query, $connection))) {
unlock_tables();
$site->dieAndEndPage('The match reported by user #' . sqlSafeString($viewerid) . ' could not be deleted due to a sql problem!');
}
// update win/draw/loose count
require_once 'team_match_count.php';
// both teams didn't play
decrease_total_match_count($team_id1);
decrease_total_match_count($team_id2);
// team 1 won
if ($team1_caps > $team2_caps) {
// update team 1 data
decrease_won_match_count($team_id1);
// update team 2 data
decrease_lost_match_count($team_id2);
}
// team 2 won
if ($team1_caps < $team2_caps) {
// update team 1 data
decrease_lost_match_count($team_id1);
// update team 2 data
decrease_won_match_count($team_id2);
}
// the match ended in a draw
if ($team1_caps === $team2_caps) {
// update team 1 data
decrease_draw_match_count($team_id1);
// update team 2 data
decrease_draw_match_count($team_id2);
}
// old match data variables no longer needed
unset($userid_old);
// trigger score updates for newer matches
update_later_matches($team_id1, $team_id2, $team1_caps, $team2_caps, $timestamp, $team_stats_changes, $viewerid, $duration);
show_score_changes($team_stats_changes, array_keys($team_stats_changes));
// done with deleting that match
unlock_tables();
require_once '../CMS/maintenance/index.php';
echo '<p>The match was deleted successfully.</p>' . "\n";
$site->dieAndEndPage();
}
