/**
* Searches the index for the given search term
* @return SearchItem[]
*/
public function Search(SearchQuery $query)
{
$terms = $query->GetSanitisedTerms();
$len = count($terms);
for ($i = 0; $i < $len; $i++) {
$terms[$i] = Sql::ProtectString($this->connection, $terms[$i]);
$terms[$i] = "LIKE '%" . trim($terms[$i], "'") . "%'";
}
$sql = "SELECT field_weight, weight_of_type, weight_within_type, weight, url, title, description, related_links_html \n FROM\n (\n SELECT SUM(field_weight) AS field_weight, weight_of_type, weight_within_type AS weight_within_type,\n SUM(field_weight) + weight_of_type + weight_within_type AS weight,\n url, title, description, related_links_html \n FROM\n (\n SELECT search_index_id, 500 as field_weight, weight_of_type, weight_within_type, url, title, description, related_links_html \n FROM nsa_search_index \n WHERE title " . implode(" AND title ", $terms) . " \n \n UNION\n \n SELECT search_index_id, 500 as field_weight, weight_of_type, weight_within_type, url, title, description, related_links_html \n FROM nsa_search_index \n WHERE keywords " . implode(" AND keywords ", $terms) . " \n \n UNION\n \n SELECT search_index_id, 50 as field_weight, weight_of_type, weight_within_type, url, title, description, related_links_html \n FROM nsa_search_index \n WHERE description " . implode(" AND description ", $terms) . " \n \n UNION\n \n SELECT search_index_id, 1 as field_weight, weight_of_type, weight_within_type, url, title, description, related_links_html \n FROM nsa_search_index \n WHERE full_text " . implode(" AND full_text ", $terms) . " \n )\n AS unsorted_results\n GROUP BY search_index_id\n ORDER BY SUM(field_weight) DESC, SUM(weight_of_type) DESC, SUM(weight_within_type) DESC\n ) \n AS weighted_results\n ORDER BY weight DESC";
# Get the total results without paging
$total = $this->connection->query("SELECT COUNT(*) AS total FROM ({$sql}) AS total");
$row = $total->fetch();
$this->total = $row->total;
# Add paging and get the data
if ($query->GetFirstResult() && $query->GetPageSize()) {
$sql .= " LIMIT " . Sql::ProtectNumeric($query->GetFirstResult() - 1, false, false) . "," . Sql::ProtectNumeric($query->GetPageSize(), false, false);
}
$query_results = $this->connection->query($sql);
require_once "search/search-item.class.php";
$search_results = array();
while ($row = $query_results->fetch()) {
$result = new SearchItem();
$result->Url($row->url);
$result->Title($row->title);
$result->Description($row->description);
$result->RelatedLinksHtml($row->related_links_html);
$result->WeightOfMatchedField($row->field_weight);
$result->WeightOfType($row->weight_of_type);
$result->WeightWithinType($row->weight_within_type);
$result->Weight($row->weight);
$search_results[] = $result;
}
return $search_results;
}
function OnLoadPageData()
{
# if activation code specified, try to activate
if (isset($_GET['c']) and isset($_GET['p']) and is_numeric($_GET['p'])) {
$o_activator = $this->GetAuthenticationManager();
$this->b_success = $o_activator->Activate($_GET['p'], $_GET['c']);
# expire old activation requests while we're here
$o_activator->ExpireRequests();
unset($o_activator);
# if activation succeeded...
if ($this->b_success) {
# get registered details
$s_sql = 'SELECT known_as, email FROM ' . $this->GetSettings()->GetTable('User') . ' ' . "WHERE user_id = " . Sql::ProtectNumeric($_GET['p']);
$s_email = '';
$s_name = '';
$o_data = $this->GetDataConnection()->query($s_sql);
$o_row = $o_data->fetch();
if (is_object($o_row)) {
$s_email = $o_row->email;
$s_name = $o_row->known_as;
# send email with details
require_once 'Zend/Mail.php';
$o_email = new Zend_Mail('UTF-8');
$o_email->addTo($s_email);
$o_email->setSubject('Your ' . $this->GetSettings()->GetSiteName() . ' registration details');
$o_email->setFrom($this->GetSettings()->GetEmailAddress(), $this->GetSettings()->GetSiteName());
$o_email->setBodyText('Hi ' . $s_name . "!\n\n" . 'Thanks for registering with ' . $this->GetSettings()->GetSiteName() . '.' . "\n\n" . "You can sign in at https://" . $this->GetSettings()->GetDomain() . $this->GetSettings()->GetFolder('Account') . "\n\n" . "Please keep this email as a reminder of the email address you signed up with.\n\n" . 'You can change your details at any time by signing in to ' . $this->GetSettings()->GetSiteName() . "\n" . "and editing your profile. You can also reset your password if you lose it.\n\n" . 'We hope to see you back at ' . $this->GetSettings()->GetSiteName() . ' soon!' . $this->GetSettings()->GetEmailSignature() . "\n\n" . '(We sent you this email because someone signed up to ' . $this->GetSettings()->GetSiteName() . "\n" . 'using the email address ' . $s_email . ". If it wasn't you, please contact us\n" . 'via our website and ask for your address to be removed.)');
$b_email_result = true;
try {
$o_email->send();
} catch (Zend_Mail_Transport_Exception $e) {
$b_email_result = false;
}
} else {
$b_email_result = false;
}
# ensure user is not signed in as someone else, then show a welcome message below
$this->GetAuthenticationManager()->SignOut();
}
}
}
/**
* @access public
* @return void
* @param int[] $player_ids
* @desc Delete from the database the players matching the supplied ids
*/
public function Delete($player_ids)
{
# check parameter
$this->ValidateNumericArray($player_ids);
# Get tables
$players = $this->GetSettings()->GetTable('Player');
$matches = $this->GetSettings()->GetTable("Match");
$batting = $this->GetSettings()->GetTable("Batting");
$bowling = $this->GetSettings()->GetTable("Bowling");
$stats = $this->GetSettings()->GetTable('PlayerMatch');
$player_id_list = implode(', ', $player_ids);
# delete from short URL cache
require_once 'http/short-url-manager.class.php';
$o_url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$sql = "SELECT short_url FROM {$players} WHERE player_id IN ({$player_id_list})";
$result = $this->GetDataConnection()->query($sql);
while ($row = $result->fetch()) {
$o_url_manager->Delete($row->short_url);
}
$result->closeCursor();
unset($o_url_manager);
# remove player of the match awards
$sql = "UPDATE {$matches} SET player_of_match_id = NULL WHERE player_of_match_id IN ({$player_id_list})";
$this->LoggedQuery($sql);
$sql = "UPDATE {$matches} SET player_of_match_home_id = NULL WHERE player_of_match_home_id IN ({$player_id_list})";
$this->LoggedQuery($sql);
$sql = "UPDATE {$matches} SET player_of_match_away_id = NULL WHERE player_of_match_away_id IN ({$player_id_list})";
$this->LoggedQuery($sql);
# Reassign batting and bowling to 'unknown' player
$unknown_player = new Player($this->GetSettings());
$unknown_player->SetName("Unknown");
foreach ($player_ids as $player_id) {
$sql = "SELECT team_id FROM {$players} WHERE player_id = " . Sql::ProtectNumeric($player_id);
$result = $this->GetDataConnection()->query($sql);
$row = $result->fetch();
$unknown_player->Team()->SetId($row->team_id);
$unknown_player->SetId($this->SaveOrMatchPlayer($unknown_player));
$player = new Player($this->GetSettings());
$player->SetId($player_id);
$this->is_internal_delete = true;
$this->MergePlayers($player, $unknown_player);
$this->is_internal_delete = false;
}
# Delete statistics
$sql = "DELETE FROM {$stats} WHERE player_id IN ({$player_id_list})";
$this->LoggedQuery($sql);
# delete the player
$sql = "DELETE FROM {$players} WHERE player_id IN ({$player_id_list})";
$this->LoggedQuery($sql);
}
/**
* Gets the id of a user using an up-to-date auto-sign-in cookie if one is found
* @return int User id if the cookie is found, null otherwise
*/
public function TryAutoSignIn()
{
if (isset($_COOKIE['user']) and is_string($_COOKIE['user']) and $_COOKIE['user']) {
$cookie = $this->ParseAutoSignInCookie($_COOKIE['user']);
# Don't assume 'user' cookie was set by this site. Could be hacker value.
if (isset($cookie['device']) and $cookie['device'] and isset($cookie['token']) and $cookie['token']) {
$sql = "SELECT COUNT(user_id) AS total, user_id FROM nsa_auto_sign_in \r\n WHERE device = " . Sql::ProtectNumeric($cookie['device']) . " \r\n AND token = " . Sql::ProtectString($this->connection, $cookie['token']) . "\r\n AND expires >= " . gmdate('U');
$result = $this->connection->query($sql);
$row = $result->fetch();
if ($row and $row->total == 1) {
return (int) $row->user_id;
}
}
}
return null;
}
/**
* @return int
* @param Ground $o_ground
* @desc Save the supplied Ground to the database, and return the id
*/
function SaveGround($o_ground)
{
# Set up short URL manager
require_once 'http/short-url-manager.class.php';
$o_url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$new_short_url = $o_url_manager->EnsureShortUrl($o_ground);
# build query
$o_address = $o_ground->GetAddress();
# if no id, it's a new ground; otherwise update the ground
if ($o_ground->GetId()) {
$s_sql = 'UPDATE ' . $this->GetSettings()->GetTable('Ground') . ' SET ' . "sort_name = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GenerateSortName()) . ", " . "saon = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetSaon()) . ", " . "paon = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetPaon()) . ", " . "street_descriptor = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetStreetDescriptor()) . ", " . "locality = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetLocality()) . ", " . "town = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetTown()) . ", " . "administrative_area = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetAdministrativeArea()) . ", " . "postcode = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetPostcode()) . ", " . "directions = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetDirections()) . ", " . "parking = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetParking()) . ", " . "facilities = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetFacilities()) . ", " . "short_url = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetShortUrl()) . ", " . 'latitude' . Sql::ProtectFloat($o_ground->GetAddress()->GetLatitude(), true, true) . ', ' . 'longitude' . Sql::ProtectFloat($o_ground->GetAddress()->GetLongitude(), true, true) . ', ' . 'geo_precision = ' . Sql::ProtectNumeric($o_ground->GetAddress()->GetGeoPrecision(), true, false) . ', ' . "update_search = 1, " . 'date_changed = ' . gmdate('U') . ' ' . 'WHERE ground_id = ' . Sql::ProtectNumeric($o_ground->GetId());
# run query
$this->GetDataConnection()->query($s_sql);
} else {
$s_sql = 'INSERT INTO ' . $this->GetSettings()->GetTable('Ground') . ' SET ' . "sort_name = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GenerateSortName()) . ", " . "saon = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetSaon()) . ", " . "paon = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetPaon()) . ", " . "street_descriptor = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetStreetDescriptor()) . ", " . "locality = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetLocality()) . ", " . "town = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetTown()) . ", " . "administrative_area = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetAdministrativeArea()) . ", " . "postcode = " . Sql::ProtectString($this->GetDataConnection(), $o_address->GetPostcode()) . ", " . "directions = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetDirections()) . ", " . "parking = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetParking()) . ", " . "facilities = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetFacilities()) . ", " . "short_url = " . Sql::ProtectString($this->GetDataConnection(), $o_ground->GetShortUrl()) . ", " . 'latitude' . Sql::ProtectFloat($o_ground->GetAddress()->GetLatitude(), true, true) . ', ' . 'longitude' . Sql::ProtectFloat($o_ground->GetAddress()->GetLongitude(), true, true) . ', ' . 'geo_precision = ' . Sql::ProtectNumeric($o_ground->GetAddress()->GetGeoPrecision(), true, false) . ', ' . "update_search = 1, " . 'date_added = ' . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U');
# run query
$o_result = $this->GetDataConnection()->query($s_sql);
# get autonumber
$o_ground->SetId($this->GetDataConnection()->insertID());
}
# Request search update for objects which mention the ground
$sql = "UPDATE nsa_team SET update_search = 1 WHERE ground_id = " . SQL::ProtectNumeric($o_ground->GetId(), false);
$this->GetDataConnection()->query($sql);
$matches = $this->GetSettings()->GetTable("Match");
$sql = "UPDATE {$matches} SET update_search = 1 WHERE ground_id = " . SQL::ProtectNumeric($o_ground->GetId(), false);
$this->GetDataConnection()->query($sql);
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($o_ground);
$o_url_manager->Save($new_short_url);
}
unset($o_url_manager);
return $o_ground->GetId();
}
/**
* @return void
* @param Match $o_match
* @desc Save the result and player(s) of the match to the database
*/
public function SaveHighlights(Match $o_match)
{
# To add a result there must always already be a match to update
if (!$o_match->GetId()) {
return;
}
require_once "stoolball/player-manager.class.php";
$player_manager = new PlayerManager($this->GetSettings(), $this->GetDataConnection());
# build query
$s_match = $this->GetSettings()->GetTable('Match');
$statistics = $this->GetSettings()->GetTable('PlayerMatch');
$i_result = $o_match->Result()->GetResultType() <= 0 ? null : $o_match->Result()->GetResultType();
$player_of_match = $player_manager->SaveOrMatchPlayer($o_match->Result()->GetPlayerOfTheMatch());
$player_of_match_home = $player_manager->SaveOrMatchPlayer($o_match->Result()->GetPlayerOfTheMatchHome());
$player_of_match_away = $player_manager->SaveOrMatchPlayer($o_match->Result()->GetPlayerOfTheMatchAway());
# Check whether anything's changed and don't re-save if not
$s_sql = 'SELECT match_id FROM ' . $s_match . ' ';
$s_where = $this->SqlAddCondition("", 'match_result_id' . Sql::ProtectNumeric($i_result, true, true));
$s_where = $this->SqlAddCondition($s_where, 'player_of_match_id ' . Sql::ProtectNumeric($player_of_match, true, true));
$s_where = $this->SqlAddCondition($s_where, 'player_of_match_home_id ' . Sql::ProtectNumeric($player_of_match_home, true, true));
$s_where = $this->SqlAddCondition($s_where, 'player_of_match_away_id ' . Sql::ProtectNumeric($player_of_match_away, true, true));
$s_where = $this->SqlAddCondition($s_where, 'match_id = ' . Sql::ProtectNumeric($o_match->GetId()));
$s_sql = $this->SqlAddWhereClause($s_sql, $s_where);
$o_result = $this->GetDataConnection()->query($s_sql);
if ($o_result->fetch()) {
return;
}
# Should the match_title be regenerated?
$s_sql = "SELECT custom_title FROM {$s_match} WHERE {$s_match}.match_id = " . Sql::ProtectNumeric($o_match->GetId());
$o_result = $this->GetDataConnection()->query($s_sql);
$o_row = $o_result->fetch();
if (!is_null($o_row)) {
$o_match->SetUseCustomTitle($o_row->custom_title);
}
# Save IDs of players affected by any change
$affected_players = array();
if (!is_null($player_of_match)) {
$affected_players[] = $player_of_match;
}
if (!is_null($player_of_match_home)) {
$affected_players[] = $player_of_match_home;
}
if (!is_null($player_of_match_away)) {
$affected_players[] = $player_of_match_away;
}
$s_sql = "SELECT player_of_match_id, player_of_match_home_id, player_of_match_away_id FROM {$s_match} WHERE {$s_match}.match_id = " . Sql::ProtectNumeric($o_match->GetId());
$o_result = $this->GetDataConnection()->query($s_sql);
$row = $o_result->fetch();
if (!is_null($row)) {
if (!is_null($row->player_of_match_id)) {
$affected_players[] = $row->player_of_match_id;
}
if (!is_null($row->player_of_match_home_id)) {
$affected_players[] = $row->player_of_match_home_id;
}
if (!is_null($row->player_of_match_away_id)) {
$affected_players[] = $row->player_of_match_away_id;
}
}
# Update the main match record
# All changes from here to master data are logged, because this method can be called from the public interface
$sql = "UPDATE {$s_match} SET ";
if (!$o_match->GetUseCustomTitle()) {
$sql .= "match_title = " . Sql::ProtectString($this->GetDataConnection(), $o_match->GetTitle()) . ", ";
}
$sql .= 'match_result_id = ' . Sql::ProtectNumeric($i_result, true) . ",\r\n\t\t\tplayer_of_match_id = " . Sql::ProtectNumeric($player_of_match, true) . ', ' . "player_of_match_home_id = " . Sql::ProtectNumeric($player_of_match_home, true) . ', ' . "player_of_match_away_id = " . Sql::ProtectNumeric($player_of_match_away, true) . ',
update_search = 1,
date_changed = ' . gmdate('U') . ", \r\n modified_by_id = " . Sql::ProtectNumeric(AuthenticationManager::GetUser()->GetId()) . ' ' . 'WHERE match_id = ' . Sql::ProtectNumeric($o_match->GetId());
$this->LoggedQuery($sql);
# Copy updated match to statistics
require_once 'stoolball/statistics/statistics-manager.class.php';
$statistics_manager = new StatisticsManager($this->GetSettings(), $this->GetDataConnection());
$statistics_manager->UpdateMatchDataInStatistics($this, $o_match->GetId());
# Save IDs of players affected by any change
if (count($affected_players)) {
$statistics_manager->UpdatePlayerOfTheMatchStatistics($o_match->GetId());
$statistics_manager->DeleteObsoleteStatistics($o_match->GetId());
$statistics_manager->UpdatePlayerStatistics($affected_players);
}
unset($statistics_manager);
# Match data has changed so notify moderator
$this->QueueForNotification($o_match->GetId(), false);
}
/**
* Queues the item to be added to the index when CommitChanges is called
*/
public function Index(SearchItem $item)
{
$this->index_queue[] = "INSERT INTO nsa_search_index SET \n search_index_id = " . Sql::ProtectString($this->connection, $item->SearchItemId()) . ",\n indexed_item_type = " . Sql::ProtectString($this->connection, $item->SearchItemType()) . ",\n url = " . Sql::ProtectString($this->connection, $item->Url()) . ",\n title = " . Sql::ProtectString($this->connection, $item->Title()) . ",\n keywords = " . Sql::ProtectString($this->connection, $item->Keywords()) . ",\n description = " . Sql::ProtectString($this->connection, $item->Description()) . ",\n related_links_html = " . Sql::ProtectString($this->connection, $item->RelatedLinksHtml()) . ",\n full_text = " . Sql::ProtectString($this->connection, $item->FullText()) . ",\n weight_of_type = " . Sql::ProtectNumeric($item->WeightOfType(), false, false) . ",\n weight_within_type = " . Sql::ProtectNumeric($item->WeightWithinType(), false, false);
}
/**
* Deletes a subscription for the current user
* @param int $item_subscribed_to_id
* @param int $content_type
* @param int $user_id
* @return void
*/
public function DeleteSubscription($item_subscribed_to_id, $content_type, $user_id)
{
$s_sub = $this->GetSettings()->GetTable('EmailSubscription');
$s_sql = 'DELETE FROM ' . $s_sub . ' WHERE ' . 'item_id = ' . Sql::ProtectNumeric($item_subscribed_to_id, false) . ' ' . 'AND user_id = ' . Sql::ProtectNumeric($user_id, false) . ' ' . "AND item_type = " . Sql::ProtectNumeric($content_type, false);
$this->GetDataConnection()->query($s_sql);
}
/**
* Saves a comment on an item
*
* @param ReviewItem $item_to_comment_on
* @param string $s_body
* @return ForumMessage
*/
public function SaveComment(ReviewItem $item_to_comment_on, $s_body)
{
$user = AuthenticationManager::GetUser();
# create new message
$o_message = new ForumMessage($this->GetSettings(), $user);
$o_message->SetBody($s_body);
$o_message->SetReviewItem($item_to_comment_on);
# add new message to db, either as reply or as new topic
/* @var $o_result MySQLRawData */
# Create table aliases
$s_message = $this->o_settings->GetTable('ForumMessage');
$s_reg = $this->o_settings->GetTable('User');
$s_ip = isset($_SERVER['REMOTE_ADDR']) ? $this->SqlString($_SERVER['REMOTE_ADDR']) : 'NULL';
$s_sql = 'INSERT INTO ' . $s_message . ' SET ' . 'user_id = ' . Sql::ProtectNumeric(AuthenticationManager::GetUser()->GetId()) . ', ' . 'date_added = ' . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U') . ', ' . "message = " . $this->SqlHtmlString($o_message->GetBody()) . ", " . 'ip = ' . $s_ip . ', ' . 'sort_override = 0, ' . 'item_id = ' . Sql::ProtectNumeric($item_to_comment_on->GetId()) . ', ' . "item_type = " . Sql::ProtectNumeric($item_to_comment_on->GetType());
$this->Lock(array($s_message, $s_reg));
$o_result = $this->GetDataConnection()->query($s_sql);
if ($this->GetDataConnection()->isError()) {
die('Failed to create message.');
}
$o_message->SetId($this->GetDataConnection()->insertID());
# increment personal message count
$user_id = Sql::ProtectNumeric(AuthenticationManager::GetUser()->GetId());
$s_sql = "UPDATE nsa_user SET total_messages = (SELECT COUNT(id) FROM nsa_forum_message WHERE user_id = {$user_id}) WHERE user_id = {$user_id}";
$o_result = $this->GetDataConnection()->query($s_sql);
if ($this->GetDataConnection()->isError()) {
die('Failed to update your message count.');
}
# release db
$this->Unlock();
return $o_message;
}
/**
* Log and execute a query
*
* @param string $sql
*/
protected function LoggedQuery($sql)
{
# Clear out any audit records older than six months, otherwise the database will get too big
$six_months_ago = gmdate('U') - 60 * 60 * 24 * 31 * 6;
$this->GetDataConnection()->query("DELETE FROM nsa_audit WHERE date_changed < {$six_months_ago}");
$log_sql = "INSERT INTO nsa_audit SET \n user_id = " . Sql::ProtectNumeric(AuthenticationManager::GetUser()->GetId(), true) . ",\n ip_address = " . Sql::ProtectString($this->GetDataConnection(), $_SERVER['REMOTE_ADDR']) . ", \n date_changed = " . gmdate('U') . ", \n query_sql = " . Sql::ProtectString($this->GetDataConnection(), $sql) . ", \n request_url = " . Sql::ProtectString($this->GetDataConnection(), $_SERVER['REQUEST_URI']);
$this->GetDataConnection()->query($log_sql);
# Run the actual query last, so that the insert ID and rows affected are available if required
$this->GetDataConnection()->query($sql);
}
/**
* @return int
* @param Club $school
* @desc Update the supplied school in the database
*/
public function SaveSchool(Club $school)
{
if (!$school->GetId()) {
throw new Exception("SaveSchool is for updates only. To save a new school, use Save()");
}
# Set up short URL manager
require_once 'http/short-url-manager.class.php';
$url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$new_short_url = $url_manager->EnsureShortUrl($school);
$sql = 'UPDATE nsa_club SET ' . "club_name = " . Sql::ProtectString($this->GetDataConnection(), $school->GetName()) . ", \r\n club_type = " . Club::SCHOOL . ", \r\n short_url = " . Sql::ProtectString($this->GetDataConnection(), $school->GetShortUrl()) . ", \r\n date_changed = " . gmdate('U') . ' ' . 'WHERE club_id = ' . Sql::ProtectNumeric($school->GetId());
$this->GetDataConnection()->query($sql);
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($school);
$url_manager->Save($new_short_url);
}
unset($url_manager);
}
/**
* @access public
* @return void
* @param int[] $a_ids
* @desc Delete from the db the seasons matching the supplied ids
*/
function Delete($a_ids)
{
# check parameter
$this->ValidateNumericArray($a_ids);
# build query
$s_season = $this->GetSettings()->GetTable('Season');
$s_season_link = $this->GetSettings()->GetTable('TeamSeason');
$s_rule = $this->GetSettings()->GetTable('SeasonRule');
$s_smt = $this->GetSettings()->GetTable('SeasonMatchType');
$s_points = $this->GetSettings()->GetTable('PointsAdjustment');
$s_match = $this->GetSettings()->GetTable('SeasonMatch');
# delete from short URL cache
require_once 'http/short-url-manager.class.php';
$o_url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$s_ids = join(', ', $a_ids);
$s_sql = "SELECT short_url FROM {$s_season} WHERE season_id IN ({$s_ids})";
$result = $this->GetDataConnection()->query($s_sql);
while ($row = $result->fetch()) {
$o_url_manager->Delete($row->short_url);
}
$result->closeCursor();
unset($o_url_manager);
foreach ($a_ids as $i_season_id) {
$i_season_id = Sql::ProtectNumeric($i_season_id);
# Find out what competition we're dealing with
$s_sql = 'SELECT competition_id FROM ' . $s_season . ' WHERE season_id = ' . $i_season_id;
$o_result = $this->GetDataConnection()->query($s_sql);
$o_row = $o_result->fetch();
if (is_object($o_row)) {
$i_competition_id = $o_row->competition_id;
# Delete matches
$s_sql = 'DELETE FROM ' . $s_match . ' WHERE season_id = ' . $i_season_id;
$this->GetDataConnection()->query($s_sql);
# Delete match types
$s_sql = 'DELETE FROM ' . $s_smt . ' WHERE season_id = ' . $i_season_id;
$this->GetDataConnection()->query($s_sql);
# Delete points adjustments
$s_sql = 'DELETE FROM ' . $s_points . ' WHERE season_id = ' . $i_season_id;
$this->GetDataConnection()->query($s_sql);
# delete rules
$s_sql = 'DELETE FROM ' . $s_rule . ' WHERE season_id = ' . $i_season_id;
$this->GetDataConnection()->query($s_sql);
# delete relationships to teams
$s_sql = 'DELETE FROM ' . $s_season_link . ' WHERE season_id = ' . $i_season_id;
$this->GetDataConnection()->query($s_sql);
# delete season(s)
$s_sql = 'DELETE FROM ' . $s_season . ' WHERE season_id = ' . $i_season_id;
$this->GetDataConnection()->query($s_sql);
# update latest season
$this->UpdateLatestSeason($i_competition_id);
}
}
unset($o_result);
}
/**
* Calculate bowling figures based on overs and wickets taken
* @param int[] $player_ids
* @param int[] $bowling_match_team_ids
*/
public function UpdateBowlingStatistics($player_ids, $bowling_match_team_ids)
{
$this->ValidateNumericArray($player_ids);
$this->ValidateNumericArray($bowling_match_team_ids);
$batting_table = $this->GetSettings()->GetTable("Batting");
$bowling_table = $this->GetSettings()->GetTable("Bowling");
$stats_table = $this->GetSettings()->GetTable("PlayerMatch");
$mt = $this->GetSettings()->GetTable('MatchTeam');
$match_table = $this->GetSettings()->GetTable("Match");
$player_id_list = implode(", ", $player_ids);
$bowling_match_team_id_list = implode(",", $bowling_match_team_ids);
$has_bowling_data = array();
# reset bowling stats for these players
$sql = "UPDATE {$stats_table} SET first_over = NULL, balls_bowled = NULL, overs = NULL, overs_decimal = NULL, maidens = NULL,\r\n\t\t\t\truns_conceded = NULL, has_runs_conceded = NULL, wickets = NULL, wickets_with_bowling = NULL\r\n\t\t\t\tWHERE player_id IN ({$player_id_list}) AND match_team_id IN ({$bowling_match_team_id_list})";
$this->GetDataConnection()->query($sql);
# Get the teams involved, with enough detail to work out later which is the
# player's team and which is the opposition
$sql = "SELECT nsa_match_team.match_id, nsa_match_team.match_team_id, nsa_match_team.team_role, team.team_id, team.team_name\r\n FROM nsa_match_team \r\n LEFT JOIN nsa_team AS team ON nsa_match_team.team_id = team.team_id \r\n WHERE match_id IN (SELECT match_id FROM nsa_match_team WHERE match_team_id IN ({$bowling_match_team_id_list}))";
$result = $this->GetDataConnection()->query($sql);
$team_data = array();
while ($row = $result->fetch()) {
if (!array_key_exists($row->match_id, $team_data)) {
$team_data[$row->match_id] = array();
}
$team_data[$row->match_id][$row->match_team_id] = array();
$team_data[$row->match_id][$row->match_team_id]["team_id"] = $row->team_id;
$team_data[$row->match_id][$row->match_team_id]["team_name"] = $row->team_name;
$team_data[$row->match_id][$row->match_team_id]["team_role"] = $row->team_role;
}
# Now generate bowling figures based on the data entered
$sql = "SELECT nsa_player.player_id, nsa_player.player_role, nsa_player.player_name, nsa_player.short_url, \r\n\t\t{$mt}.match_id, {$mt}.match_team_id, \r\n\t\tm.match_type, m.player_type_id, m.start_time, m.match_title, m.short_url AS match_url, m.tournament_match_id, m.ground_id, m.home_bat_first, m.match_result_id,\r\n\t\tMIN(position) AS first_over, SUM(runs_in_over) AS runs_conceded, SUM(balls_bowled) AS balls_total,\r\n\t\tCONCAT(FLOOR(SUM(balls_bowled)/8), '.', FLOOR(SUM(balls_bowled) MOD 8)) AS overs, SUM(balls_bowled)/8 AS overs_decimal,\r\n\t\t(SELECT COUNT(bowling_id) FROM {$bowling_table} AS maiden_overs WHERE runs_in_over = 0 AND balls_bowled >= 8 AND maiden_overs.match_team_id = {$bowling_table}.match_team_id AND maiden_overs.player_id = {$bowling_table}.player_id) AS maidens\r\n\t\tFROM {$bowling_table} INNER JOIN {$mt} ON {$bowling_table}.match_team_id = {$mt}.match_team_id\r\n\t\tINNER JOIN nsa_player ON {$bowling_table}.player_id = nsa_player.player_id\r\n\t\tINNER JOIN {$match_table} m ON {$mt}.match_id = m.match_id\r\n\t\tWHERE nsa_player.player_id IN ({$player_id_list})\r\n\t\tAND {$bowling_table}.match_team_id IN ({$bowling_match_team_id_list})\r\n\t\tGROUP BY nsa_player.player_id, {$bowling_table}.match_team_id";
$result = $this->GetDataConnection()->query($sql);
while ($row = $result->fetch()) {
$balls_bowled = Sql::ProtectNumeric($row->balls_total, true);
$overs = Sql::ProtectNumeric($row->overs, true);
$overs_decimal = Sql::ProtectNumeric($row->overs_decimal, true);
$maidens = is_null($row->overs) ? "NULL" : Sql::ProtectNumeric($row->maidens, true);
$runs_conceded = Sql::ProtectNumeric($row->runs_conceded, true);
$has_runs_conceded = is_null($row->runs_conceded) ? "0" : "1";
# Set wickets to 0; for those who took them, it'll be updated next
$sql = "UPDATE {$stats_table} SET\r\n\t\t\t\t\tfirst_over = {$row->first_over},\r\n\t\t\t\t\tballs_bowled = {$balls_bowled},\r\n\t\t\t\t\tovers = {$overs},\r\n\t\t\t\t\tovers_decimal = {$overs_decimal},\r\n\t\t\t\t\tmaidens = {$maidens},\r\n\t\t\t\t\truns_conceded = {$runs_conceded},\r\n\t\t\t\t\thas_runs_conceded = {$has_runs_conceded}, \r\n\t\t\t\t\twickets = 0,\r\n\t\t\t\t\twickets_with_bowling = 0\r\n\t\t\t\t\tWHERE match_team_id = {$row->match_team_id}\r\n\t\t\t\t\tAND player_id = {$row->player_id}\r\n\t\t\t\t\tAND (player_innings IS NULL OR player_innings = 1)";
$update_result = $this->GetDataConnection()->query($sql);
if (!$this->GetDataConnection()->GetAffectedRows()) {
# This is the first record of the player in the match. Everyone is a fielder, so
# set fielding statistics to defaults as well.
$player_role = Sql::ProtectNumeric($row->player_role, false, false);
$player_name = Sql::ProtectString($this->GetDataConnection(), $row->player_name);
$player_url = Sql::ProtectString($this->GetDataConnection(), $row->short_url);
$match_type = Sql::ProtectNumeric($row->match_type, true, false);
$match_player_type = Sql::ProtectNumeric($row->player_type_id, true, false);
$match_time = Sql::ProtectNumeric($row->start_time, true, false);
$match_title = Sql::ProtectString($this->GetDataConnection(), $row->match_title);
$match_url = Sql::ProtectString($this->GetDataConnection(), $row->match_url);
$tournament_id = Sql::ProtectNumeric($row->tournament_match_id, true, false);
$ground_id = Sql::ProtectNumeric($row->ground_id, true, false);
$teams_in_match = $team_data[$row->match_id];
$players_team = $teams_in_match[$row->match_team_id];
unset($teams_in_match[$row->match_team_id]);
$remaining_teams_in_match = array_keys($teams_in_match);
$opposition_team = $teams_in_match[$remaining_teams_in_match[0]];
$team_id = Sql::ProtectNumeric($players_team["team_id"], false, false);
$team_name = Sql::ProtectString($this->GetDataConnection(), $players_team["team_name"]);
$opposition_id = Sql::ProtectNumeric($opposition_team["team_id"], false, false);
$opposition_name = Sql::ProtectString($this->GetDataConnection(), $players_team["team_name"]);
$won_match = Sql::ProtectNumeric($this->DidThePlayerWinTheMatch($row->match_result_id, $players_team["team_role"]), true, false);
$batting_first = "NULL";
if ($row->home_bat_first === '0') {
$batting_first = $players_team["team_role"] == TeamRole::Away() ? 1 : 0;
} else {
if ($row->home_bat_first === '1') {
$batting_first = $players_team["team_role"] == TeamRole::Home() ? 1 : 0;
}
}
$sql = "INSERT INTO {$stats_table}\r\n\t\t\t\t\t(player_id, player_role, player_name, player_url, match_id, match_team_id, match_type, match_player_type, match_time, \r\n\t\t\t\t\t match_title, match_url, tournament_id, ground_id, team_id, team_name, opposition_id, opposition_name, batting_first, won_match,\r\n\t\t\t\t\t first_over, balls_bowled, overs, overs_decimal, maidens, runs_conceded, has_runs_conceded, wickets, wickets_with_bowling, \r\n\t\t\t\t\t catches, run_outs, player_of_match)\r\n\t\t\t\t\tVALUES\r\n\t\t\t\t\t({$row->player_id}, {$player_role}, {$player_name}, {$player_url}, {$row->match_id}, {$row->match_team_id}, {$match_type}, {$match_player_type}, {$match_time}, \r\n\t\t\t\t\t {$match_title}, {$match_url}, {$tournament_id}, {$ground_id}, {$team_id}, {$team_name}, {$opposition_id}, {$opposition_name}, {$batting_first}, {$won_match},\r\n\t\t\t\t\t {$row->first_over}, {$balls_bowled}, {$overs}, {$overs_decimal}, {$maidens}, \r\n\t\t\t\t\t {$runs_conceded}, {$has_runs_conceded}, 0, 0, 0, 0, 0)";
$this->GetDataConnection()->query($sql);
}
# Note that this player had bowling data
if (!array_key_exists($row->match_team_id, $has_bowling_data)) {
$has_bowling_data[$row->match_team_id] = array();
}
$has_bowling_data[$row->match_team_id][] = $row->player_id;
}
# Now get the wickets for each bowler from the batting data
foreach ($bowling_match_team_ids as $bowling_match_team_id) {
# get the match_team_id for the batting that goes with this bowling
$sql = "SELECT m.match_id, m.match_type, m.player_type_id, m.start_time, m.match_title, m.short_url AS match_url, \r\n\t\t\tm.tournament_match_id, m.ground_id, m.home_bat_first, m.match_result_id, match_team_id, team_id\r\n\t\t\tFROM {$mt} INNER JOIN {$match_table} m ON {$mt}.match_id = m.match_id\r\n\t\t\tWHERE m.match_id = (SELECT match_id FROM {$mt} WHERE match_team_id = {$bowling_match_team_id})\r\n\t\t\tAND match_team_id != {$bowling_match_team_id}\r\n\t\t\tAND team_role IN (" . TeamRole::Home() . ", " . TeamRole::Away() . ")";
$result = $this->GetDataConnection()->query($sql);
$row = $result->fetch();
$match_id = $row->match_id;
$batting_match_team_id = $row->match_team_id;
$match_type = Sql::ProtectNumeric($row->match_type, true, false);
$match_player_type = Sql::ProtectNumeric($row->player_type_id, true, false);
$match_time = Sql::ProtectNumeric($row->start_time, true, false);
$match_title = Sql::ProtectString($this->GetDataConnection(), $row->match_title);
$match_url = Sql::ProtectString($this->GetDataConnection(), $row->match_url);
$tournament_id = Sql::ProtectNumeric($row->tournament_match_id, true, false);
$ground_id = Sql::ProtectNumeric($row->ground_id, true, false);
$opposition_id = Sql::ProtectNumeric($row->team_id, true, false);
$home_team_batted_first = $row->home_bat_first;
$match_result_id = $row->match_result_id;
# get the wickets for the bowlers in that innings
# (don't filter by players here as we need to pick up unexpected players who are
# recorded as taking wickets but not bowling any overs)
$sql = "SELECT bowler_id, COUNT(batting_id) AS wickets, player_role, player_name, short_url\r\n\t\t\t\tFROM {$batting_table} INNER JOIN nsa_player ON {$batting_table}.player_id = nsa_player.player_id\r\n\t\t\t\tWHERE how_out IN (" . Batting::CAUGHT . "," . Batting::CAUGHT_AND_BOWLED . "," . Batting::BOWLED . "," . Batting::BODY_BEFORE_WICKET . "," . Batting::HIT_BALL_TWICE . ")\r\n\t\t\t\tAND match_team_id = {$batting_match_team_id}\r\n\t\t\t\tAND bowler_id IS NOT NULL\r\n\t\t\t\tGROUP BY bowler_id";
$result = $this->GetDataConnection()->query($sql);
# Add wickets to their bowling figures for the match, bearing in mind they may
# not have any yet if the scorecard is incomplete
$wickets_taken = array();
while ($row = $result->fetch()) {
$wickets_taken[$row->bowler_id] = array();
$wickets_taken[$row->bowler_id]["player_role"] = $row->player_role;
$wickets_taken[$row->bowler_id]["player_name"] = $row->player_name;
$wickets_taken[$row->bowler_id]["player_url"] = $row->short_url;
$wickets_taken[$row->bowler_id]["wickets"] = $row->wickets;
}
if (count($wickets_taken)) {
# reset wickets for these players - this ensures that the number of affected rows
# on update
# tells us whether or not there was an existing record. If we update a record
# with the same
# information it already had, the number of affected rows is zero, which is the
# same as if
# there had been no record.
#
# BUT don't reset wickets_with_bowling column, because that'll happen for all
# wicket-takers in
# the innings but only the ones whose wickets have been updated will get the data
# put back in.
$sql = "UPDATE {$stats_table} SET wickets = NULL\r\n\t\t\t\tWHERE player_id IN (" . implode(", ", array_keys($wickets_taken)) . ") AND match_team_id = {$bowling_match_team_id}";
$this->GetDataConnection()->query($sql);
foreach ($wickets_taken as $bowler_id => $bowler) {
$wickets = Sql::ProtectNumeric($bowler["wickets"], false, false);
$sql = "UPDATE {$stats_table} SET\r\n\t\t\t\t\twickets = {$wickets},\r\n\t\t\t\t\topposition_id = {$opposition_id}\r\n\t\t\t\t\tWHERE match_team_id = {$bowling_match_team_id}\r\n\t\t\t\t\tAND player_id = {$bowler_id}\r\n\t\t\t\t\tAND (player_innings IS NULL OR player_innings = 1)";
$update_result = $this->GetDataConnection()->query($sql);
if (!$this->GetDataConnection()->GetAffectedRows()) {
# This is the first record of this player in the match. They have been a fielder,
# so set catches and run outs to be zero as well.
$player_role = Sql::ProtectNumeric($bowler["player_role"], false, false);
$player_name = Sql::ProtectString($this->GetDataConnection(), $bowler["player_name"]);
$player_url = Sql::ProtectString($this->GetDataConnection(), $bowler["player_url"]);
$teams_in_match = $team_data[$match_id];
$players_team = $teams_in_match[$bowling_match_team_id];
$team_id = Sql::ProtectNumeric($players_team["team_id"], false, false);
$team_name = Sql::ProtectString($this->GetDataConnection(), $players_team["team_name"]);
$opposition_id = Sql::ProtectNumeric($teams_in_match[$batting_match_team_id]["team_id"], false, false);
$opposition_name = Sql::ProtectString($this->GetDataConnection(), $players_team["team_name"]);
$batting_first = "NULL";
if ($home_team_batted_first === '0') {
$batting_first = $players_team["team_role"] == TeamRole::Away() ? 1 : 0;
} else {
if ($home_team_batted_first === '1') {
$batting_first = $players_team["team_role"] == TeamRole::Home() ? 1 : 0;
}
}
$won_match = Sql::ProtectNumeric($this->DidThePlayerWinTheMatch($match_result_id, $players_team["team_role"]), true, false);
$sql = "INSERT INTO {$stats_table}\r\n\t\t\t\t\t\t\t\t(player_id, player_role, player_name, player_url, match_id, match_team_id, match_type, match_player_type, match_time, \r\n\t\t\t\t\t\t\t\t match_title, match_url, tournament_id, ground_id, team_id, team_name, opposition_id, opposition_name, batting_first, won_match,\r\n\t\t\t\t\t\t\t\t has_runs_conceded, wickets, catches, run_outs, player_of_match)\r\n\t\t\t\t\t\t\t\tVALUES\r\n\t\t\t\t\t\t\t\t({$bowler_id}, {$player_role}, {$player_name}, {$player_url}, {$match_id}, {$bowling_match_team_id}, {$match_type}, {$match_player_type}, {$match_time}, \r\n\t\t\t\t\t\t\t\t {$match_title}, {$match_url}, {$tournament_id}, {$ground_id}, {$team_id}, {$team_name}, {$opposition_id}, {$opposition_name}, {$batting_first}, {$won_match},\r\n\t\t\t\t\t\t\t\t 0, {$wickets}, 0, 0, 0)";
$this->GetDataConnection()->query($sql);
}
# Record wickets again for those who had bowling data.
# Only these players have enough information for all bowling statistics.
if (array_key_exists($bowling_match_team_id, $has_bowling_data) and in_array($bowler_id, $has_bowling_data[$bowling_match_team_id])) {
$sql = "UPDATE {$stats_table} SET\r\n\t\t\t\t\t\twickets_with_bowling = {$wickets}\r\n\t\t\t\t\t\tWHERE match_team_id = {$bowling_match_team_id}\r\n\t\t\t\t\t\tAND player_id = {$bowler_id}\r\n\t\t\t\t\t\tAND (player_innings IS NULL OR player_innings = 1)";
$this->GetDataConnection()->query($sql);
}
}
}
}
}
/**
* @return int
* @param Club $club
* @desc Save the supplied Club to the database, and return the id
*/
public function Save(Club $club)
{
# Set up short URL manager
require_once 'http/short-url-manager.class.php';
$url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$new_short_url = $url_manager->EnsureShortUrl($club);
# if no id, it's a new club; otherwise update the club
if ($club->GetId()) {
$s_sql = 'UPDATE ' . $this->GetSettings()->GetTable('Club') . ' SET ' . "club_name = " . Sql::ProtectString($this->GetDataConnection(), $club->GetName()) . ", \r\n club_type = " . Sql::ProtectNumeric($club->GetTypeOfClub(), false, false) . ", \r\n how_many_players = " . Sql::ProtectNumeric($club->GetHowManyPlayers(), true, false) . ", \r\n age_range_lower = " . Sql::ProtectNumeric($club->GetAgeRangeLower(), true, false) . ", \r\n age_range_upper = " . Sql::ProtectNumeric($club->GetAgeRangeUpper(), true, false) . ", \r\n plays_outdoors = " . Sql::ProtectBool($club->GetPlaysOutdoors(), true, false) . ",\r\n plays_indoors = " . Sql::ProtectBool($club->GetPlaysIndoors(), true, false) . ",\r\n twitter = " . Sql::ProtectString($this->GetDataConnection(), $club->GetTwitterAccount()) . ", \r\n facebook = " . Sql::ProtectString($this->GetDataConnection(), $club->GetFacebookUrl()) . ", \r\n instagram = " . Sql::ProtectString($this->GetDataConnection(), $club->GetInstagramAccount()) . ", \r\n clubmark = " . Sql::ProtectBool($club->GetClubmarkAccredited()) . ",\r\n\t\t\tshort_url = " . Sql::ProtectString($this->GetDataConnection(), $club->GetShortUrl()) . ", \r\n\t\t\tdate_changed = " . gmdate('U') . ' ' . 'WHERE club_id = ' . Sql::ProtectNumeric($club->GetId());
# run query
$this->GetDataConnection()->query($s_sql);
} else {
$s_sql = 'INSERT INTO ' . $this->GetSettings()->GetTable('Club') . ' SET ' . "club_name = " . Sql::ProtectString($this->GetDataConnection(), $club->GetName()) . ", \r\n club_type = " . Sql::ProtectNumeric($club->GetTypeOfClub(), false, false) . ", \r\n how_many_players = " . Sql::ProtectNumeric($club->GetHowManyPlayers(), true, false) . ", \r\n age_range_lower = " . Sql::ProtectNumeric($club->GetAgeRangeLower(), true, false) . ", \r\n age_range_upper = " . Sql::ProtectNumeric($club->GetAgeRangeUpper(), true, false) . ", \r\n plays_outdoors = " . Sql::ProtectBool($club->GetPlaysOutdoors(), true, false) . ",\r\n plays_indoors = " . Sql::ProtectBool($club->GetPlaysIndoors(), true, false) . ",\r\n twitter = " . Sql::ProtectString($this->GetDataConnection(), $club->GetTwitterAccount()) . ", \r\n facebook = " . Sql::ProtectString($this->GetDataConnection(), $club->GetFacebookUrl()) . ", \r\n instagram = " . Sql::ProtectString($this->GetDataConnection(), $club->GetInstagramAccount()) . ", \r\n clubmark = " . Sql::ProtectBool($club->GetClubmarkAccredited()) . ",\r\n\t\t\tshort_url = " . Sql::ProtectString($this->GetDataConnection(), $club->GetShortUrl()) . ", \r\n\t\t\tdate_added = " . gmdate('U') . ',
date_changed = ' . gmdate('U');
# run query
$result = $this->GetDataConnection()->query($s_sql);
# get autonumber
$club->SetId($this->GetDataConnection()->insertID());
}
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($club);
$url_manager->Save($new_short_url);
}
unset($url_manager);
return $club->GetId();
}
/**
* Saves personal information about a user
* @param User $user
* @return void
*/
public function SavePersonalInfo(User $user)
{
# Prepare filter
require_once 'text/bad-language-filter.class.php';
$language = new BadLanguageFilter();
$users = $this->GetSettings()->GetTable('User');
$s_sql = 'UPDATE ' . $users . ' SET ' . 'date_changed = ' . gmdate('U') . ', ' . "gender = " . ($user->GetGender() ? $this->SqlString($user->GetGender()) : "NULL") . ", " . "occupation = " . $this->SqlString($language->Filter($user->GetOccupation())) . ", " . "interests = " . $this->SqlHtmlString($language->Filter($user->GetInterests())) . ", " . "location = " . $this->SqlString($language->Filter($user->GetLocation())) . " " . 'WHERE user_id = ' . Sql::ProtectNumeric($user->GetId(), false);
$this->Lock(array($users));
$this->GetDataConnection()->query($s_sql);
$this->Unlock();
}
/**
* Updates the derived hierarchy data stored in the database
*
* @param Object which can generate the category URLs $url_manager
* @param Name of method to generate the category URLs $url_method
*/
public function UpdateHierarchyData($url_manager, $url_method)
{
# Fresh start
$this->Clear();
$categories = array();
$o_sorted_categories = new CategoryCollection();
$category_table = $this->GetSettings()->GetTable('Category');
# First, get all the categories from the db
$s_sql = "SELECT id, parent, code FROM {$category_table} ORDER BY sort_override, name";
$result = $this->GetDataConnection()->query($s_sql);
while ($o_row = $result->fetch()) {
$o_category = new Category();
$o_category->SetId($o_row->id);
$o_category->SetParentId($o_row->parent);
$o_category->SetUrl($o_row->code);
$categories[] = $o_category;
}
$result->closeCursor();
# Sort the categories, generating hierarchy data including a URL
$a_stack = array();
$this->GatherChildCategories($a_stack, $categories, $o_sorted_categories, $url_manager, $url_method);
# Now write that hierarchy data back to the db
$i = 0;
foreach ($o_sorted_categories as $category) {
/* @var $category Category */
$s_sql = "UPDATE {$category_table} SET " . "navigate_url = " . Sql::ProtectString($this->GetDataConnection(), $category->GetNavigateUrl(), false) . ", " . "hierarchy_level = " . Sql::ProtectNumeric($category->GetHierarchyLevel()) . ", " . "hierarchy_sort = {$i} " . "WHERE id = " . Sql::ProtectNumeric($category->GetId());
$i++;
$this->GetDataConnection()->query($s_sql);
}
}
/**
* @return int
* @param Team $team
* @desc Save the supplied Team to the database, and return the id
*/
public function SaveTeam(Team $team)
{
# First job is to check permissions. There are several scenarios:
# - adding regular teams requires the highest privileges
# - adding once-only teams requires low privileges
# - editing teams has less access for a team owner than for a site admin
#
# Important to check the previous team type from the database before trusting
# the one submitted, as changing the team type changes editing privileges
$user = AuthenticationManager::GetUser();
$is_admin = $user->Permissions()->HasPermission(PermissionType::MANAGE_TEAMS);
$is_team_owner = $user->Permissions()->HasPermission(PermissionType::MANAGE_TEAMS, $team->GetLinkedDataUri());
$adding = !(bool) $team->GetId();
$old_team = null;
if (!$adding) {
$this->ReadById(array($team->GetId()));
$old_team = $this->GetFirst();
$team->SetTeamType($this->GetPermittedTeamType($old_team->GetTeamType(), $team->GetTeamType()));
}
$is_once_only = $team->GetTeamType() == Team::ONCE;
# To add a regular team we need global manage teams permission
if ($adding and !$is_once_only and !$is_admin) {
throw new Exception("Unauthorised");
}
# To edit a team we need global manage teams permission, or team owner permission
if (!$adding and !$is_admin and !$is_team_owner) {
throw new Exception("Unauthorised");
}
# Only an admin can change the short URL after the team is created
if ($adding or $is_admin) {
# Set up short URL manager
# Before changing the short URL, important that $old_team has a note of the current resource URI
require_once 'http/short-url-manager.class.php';
$o_url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$new_short_url = $o_url_manager->EnsureShortUrl($team);
}
# build query
$i_club_id = !is_null($team->GetClub()) ? $team->GetClub()->GetId() : null;
$allowed_html = array('p', 'br', 'strong', 'em', 'a[href]', 'ul', 'ol', 'li');
$school_years = $team->GetSchoolYears();
$school_years_sql = "year1 = " . Sql::ProtectBool(array_key_exists(1, $school_years) and $school_years[1], false, false) . ", \r\n year2 = " . Sql::ProtectBool(array_key_exists(2, $school_years) and $school_years[2], false, false) . ", \r\n year3 = " . Sql::ProtectBool(array_key_exists(3, $school_years) and $school_years[3], false, false) . ", \r\n year4 = " . Sql::ProtectBool(array_key_exists(4, $school_years) and $school_years[4], false, false) . ", \r\n year5 = " . Sql::ProtectBool(array_key_exists(5, $school_years) and $school_years[5], false, false) . ", \r\n year6 = " . Sql::ProtectBool(array_key_exists(6, $school_years) and $school_years[6], false, false) . ", \r\n year7 = " . Sql::ProtectBool(array_key_exists(7, $school_years) and $school_years[7], false, false) . ", \r\n year8 = " . Sql::ProtectBool(array_key_exists(8, $school_years) and $school_years[8], false, false) . ", \r\n year9 = " . Sql::ProtectBool(array_key_exists(9, $school_years) and $school_years[9], false, false) . ", \r\n year10 = " . Sql::ProtectBool(array_key_exists(10, $school_years) and $school_years[10], false, false) . ", \r\n year11 = " . Sql::ProtectBool(array_key_exists(11, $school_years) and $school_years[11], false, false) . ", \r\n year12 = " . Sql::ProtectBool(array_key_exists(12, $school_years) and $school_years[12], false, false) . ", ";
# if no id, it's a new Team; otherwise update the Team
if ($adding) {
$sql = 'INSERT INTO nsa_team SET ' . "team_name = " . $this->SqlString($team->GetName()) . ", \r\n comparable_name = " . Sql::ProtectString($this->GetDataConnection(), $team->GetComparableName(), false) . ",\r\n club_id = " . Sql::ProtectNumeric($i_club_id, true) . ", \r\n website = " . $this->SqlString($team->GetWebsiteUrl()) . ", " . 'ground_id = ' . Sql::ProtectNumeric($team->GetGround()->GetId(), true) . ', ' . 'active = ' . Sql::ProtectBool($team->GetIsActive()) . ", \r\n team_type = " . Sql::ProtectNumeric($team->GetTeamType()) . ", \r\n {$school_years_sql}\r\n player_type_id = " . Sql::ProtectNumeric($team->GetPlayerType()) . ",\r\n intro = " . $this->SqlHtmlString($team->GetIntro(), $allowed_html) . ",\r\n playing_times = " . $this->SqlHtmlString($team->GetPlayingTimes(), $allowed_html) . ", \r\n cost = " . $this->SqlHtmlString($team->GetCost(), $allowed_html) . ", " . "contact = " . $this->SqlHtmlString($team->GetContact(), $allowed_html) . ", " . "contact_nsa = " . $this->SqlHtmlString($team->GetPrivateContact(), $allowed_html) . ", " . "short_url = " . $this->SqlString($team->GetShortUrl()) . ", \r\n update_search = " . ($is_once_only ? "0" : "1") . ", \r\n date_added = " . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U') . ", " . "modified_by_id = " . Sql::ProtectNumeric($user->GetId());
# run query
$this->LoggedQuery($sql);
# get autonumber
$team->SetId($this->GetDataConnection()->insertID());
# Create default extras players
require_once "player-manager.class.php";
$player_manager = new PlayerManager($this->GetSettings(), $this->GetDataConnection());
$player_manager->CreateExtrasPlayersForTeam($team->GetId());
unset($player_manager);
# Create owner role
require_once "authentication/authentication-manager.class.php";
require_once "authentication/role.class.php";
$authentication_manager = new AuthenticationManager($this->GetSettings(), $this->GetDataConnection(), null);
$role = new Role();
$role->setRoleName("Team owner: " . $team->GetName());
$role->Permissions()->AddPermission(PermissionType::MANAGE_TEAMS, $team->GetLinkedDataUri());
$authentication_manager->SaveRole($role);
$sql = "UPDATE nsa_team SET owner_role_id = " . Sql::ProtectNumeric($role->getRoleId(), false, false) . ' WHERE team_id = ' . Sql::ProtectNumeric($team->GetId());
$this->LoggedQuery($sql);
# If creating a once-only team, make the current user an owner
if ($is_once_only and !$is_admin) {
$authentication_manager->AddUserToRole($user->GetId(), $role->getRoleId());
$authentication_manager->LoadUserPermissions();
}
unset($authentication_manager);
} else {
# Now update the team, depending on permissions
$sql = 'UPDATE nsa_team SET ' . "website = " . $this->SqlString($team->GetWebsiteUrl()) . ", " . "intro = " . $this->SqlHtmlString($team->GetIntro(), $allowed_html) . ", " . "cost = " . $this->SqlHtmlString($team->GetCost(), $allowed_html) . ", " . "contact = " . $this->SqlHtmlString($team->GetContact(), $allowed_html) . ", " . "contact_nsa = " . $this->SqlHtmlString($team->GetPrivateContact(), $allowed_html) . ", \r\n update_search = " . ($is_once_only ? "0" : "1") . ", \r\n date_changed = " . gmdate('U') . ", \r\n modified_by_id = " . Sql::ProtectNumeric($user->GetId()) . ' ';
if (!$is_once_only) {
$sql .= ", \r\n active = " . Sql::ProtectBool($team->GetIsActive()) . ", \r\n team_type = " . Sql::ProtectNumeric($team->GetTeamType()) . ",\r\n {$school_years_sql}\r\n ground_id = " . Sql::ProtectNumeric($team->GetGround()->GetId(), true) . ", \r\n playing_times = " . $this->SqlHtmlString($team->GetPlayingTimes(), $allowed_html);
}
if ($is_admin or $is_once_only) {
$sql .= ",\r\n team_name = " . $this->SqlString($team->GetName());
}
if ($is_admin) {
$sql .= ",\r\n club_id = " . Sql::ProtectNumeric($i_club_id, true) . ", \r\n player_type_id = " . Sql::ProtectNumeric($team->GetPlayerType()) . ", \r\n comparable_name = " . Sql::ProtectString($this->GetDataConnection(), $team->GetComparableName(), false) . ",\r\n short_url = " . $this->SqlString($team->GetShortUrl()) . " ";
}
$sql .= "WHERE team_id = " . Sql::ProtectNumeric($team->GetId());
$this->LoggedQuery($sql);
# In case team name changed, update stats table
if ($is_admin or $is_once_only) {
$sql = "UPDATE nsa_player_match SET team_name = " . $this->SqlString($team->GetName()) . " WHERE team_id = " . Sql::ProtectNumeric($team->GetId());
$this->LoggedQuery($sql);
$sql = "UPDATE nsa_player_match SET opposition_name = " . $this->SqlString($team->GetName()) . " WHERE opposition_id = " . Sql::ProtectNumeric($team->GetId());
$this->LoggedQuery($sql);
}
}
if ($adding or $is_admin) {
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($team);
$o_url_manager->Save($new_short_url);
if (!$adding) {
$o_url_manager->ReplacePrefixForChildUrls(Player::GetShortUrlFormatForType($this->GetSettings()), $old_team->GetShortUrl(), $team->GetShortUrl());
$old_prefix = $this->SqlString($old_team->GetShortUrl() . "/%");
$new_prefix = $this->SqlString($team->GetShortUrl());
$sql = "UPDATE nsa_player_match SET\r\n player_url = CONCAT({$new_prefix}, RIGHT(player_url,CHAR_LENGTH(player_url)-LOCATE('/',player_url)+1))\r\n WHERE player_url LIKE {$old_prefix}";
$this->LoggedQuery($sql);
}
}
unset($o_url_manager);
# Owner permission is based on the resource URI, which in turn is based on short URL,
# so if it's changed update the permissions
if ($old_team instanceof Team) {
$old_resource_uri = $old_team->GetLinkedDataUri();
$new_resource_uri = $team->GetLinkedDataUri();
if ($old_resource_uri != $new_resource_uri) {
$permissions_table = $this->GetSettings()->GetTable("PermissionRoleLink");
$sql = "UPDATE {$permissions_table} SET resource_uri = " . $this->SqlString($new_resource_uri) . " WHERE resource_uri = " . $this->SqlString($old_resource_uri);
$this->LoggedQuery($sql);
}
}
}
if (!$is_once_only) {
# Request search update for affected competitions
$sql = "UPDATE nsa_competition SET update_search = 1 WHERE competition_id IN \r\n (\r\n SELECT competition_id FROM nsa_season WHERE season_id IN\r\n (\r\n SELECT season_id FROM nsa_team_season WHERE team_id = " . SQL::ProtectNumeric($team->GetId(), false) . " \r\n )\r\n )";
$this->LoggedQuery($sql);
# Request searched update for effects of changing the team name
$sql = "UPDATE nsa_player SET update_search = 1 WHERE team_id = " . SQL::ProtectNumeric($team->GetId(), false);
$this->LoggedQuery($sql);
$sql = "UPDATE nsa_match SET update_search = 1 WHERE match_id IN ( SELECT match_id FROM nsa_match_team WHERE team_id = " . SQL::ProtectNumeric($team->GetId(), false) . ")";
$this->LoggedQuery($sql);
# Request search update for changing the team home ground
$sql = "UPDATE nsa_ground SET update_search = 1 WHERE ground_id = " . Sql::ProtectNumeric($team->GetGround()->GetId(), false);
$this->LoggedQuery($sql);
}
return $team->GetId();
}
/**
* @return int
* @param Competition $o_competition
* @desc Save the supplied Competition to the database, and return the id
*/
function SaveCompetition($o_competition)
{
# Set up short URL manager
require_once 'http/short-url-manager.class.php';
$o_url_manager = new ShortUrlManager($this->GetSettings(), $this->GetDataConnection());
$new_short_url = $o_url_manager->EnsureShortUrl($o_competition);
# build query
$category_id = is_null($o_competition->GetCategory()) ? null : $o_competition->GetCategory()->GetId();
$allowed_html = array('p', 'br', 'strong', 'em', 'a[href]', 'ul', 'ol', 'li');
# if no id, it's a new Competition; otherwise update the Competition
$is_new = !$o_competition->GetId();
if ($is_new) {
$s_sql = 'INSERT INTO ' . $this->GetSettings()->GetTable('Competition') . ' SET ' . "competition_name = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetName()) . ", " . "category_id = " . Sql::ProtectNumeric($category_id, true, false) . ', ' . "intro = " . $this->SqlHtmlString($o_competition->GetIntro(), $allowed_html) . ", " . "contact = " . $this->SqlHtmlString($o_competition->GetContact(), $allowed_html) . ", " . "notification_email = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetNotificationEmail()) . ", " . "website = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetWebsiteUrl()) . ", " . 'active = ' . Sql::ProtectBool($o_competition->GetIsActive()) . ', ' . 'player_type_id = ' . Sql::ProtectNumeric($o_competition->GetPlayerType()) . ", " . 'players_per_team = ' . Sql::ProtectNumeric($o_competition->GetMaximumPlayersPerTeam()) . ", " . 'overs = ' . Sql::ProtectNumeric($o_competition->GetOvers()) . ", " . "short_url = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetShortUrl()) . ", " . "update_search = 1, " . 'date_added = ' . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U');
# run query
$o_result = $this->GetDataConnection()->query($s_sql);
# get autonumber
$o_competition->SetId($this->GetDataConnection()->insertID());
# create a default season
require_once 'stoolball/season-manager.class.php';
$o_season = new Season($this->GetSettings());
$o_season->SetCompetition($o_competition);
$o_season->SetStartYear(gmdate('Y', gmdate('U')));
$o_season->SetEndYear(gmdate('Y', gmdate('U')));
$o_season->SetIsLatest(true);
$o_season_mgr = new SeasonManager($this->GetSettings(), $this->GetDataConnection());
$o_season_mgr->SaveSeason($o_season);
unset($o_season_mgr);
} else {
$s_sql = 'UPDATE ' . $this->GetSettings()->GetTable('Competition') . ' SET ' . "competition_name = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetName()) . ", " . "category_id = " . Sql::ProtectNumeric($category_id, true, false) . ', ' . "intro = " . $this->SqlHtmlString($o_competition->GetIntro(), $allowed_html) . ", " . "contact = " . $this->SqlHtmlString($o_competition->GetContact(), $allowed_html) . ", " . "notification_email = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetNotificationEmail()) . ", " . "website = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetWebsiteUrl()) . ", " . 'active = ' . Sql::ProtectBool($o_competition->GetIsActive()) . ', ' . 'player_type_id = ' . Sql::ProtectNumeric($o_competition->GetPlayerType()) . ", " . 'players_per_team = ' . Sql::ProtectNumeric($o_competition->GetMaximumPlayersPerTeam()) . ", " . 'overs = ' . Sql::ProtectNumeric($o_competition->GetOvers()) . ", " . "short_url = " . Sql::ProtectString($this->GetDataConnection(), $o_competition->GetShortUrl()) . ", " . "update_search = 1, " . 'date_changed = ' . gmdate('U') . ' ' . 'WHERE competition_id = ' . Sql::ProtectNumeric($o_competition->GetId());
# run query
$this->GetDataConnection()->query($s_sql);
}
# Request search update for related objects which mention the competition
$seasons = array();
$sql = "SELECT season_id FROM nsa_season WHERE competition_id = " . SQL::ProtectNumeric($o_competition->GetId(), false);
$result = $this->GetDataConnection()->query($sql);
while ($row = $result->fetch()) {
$seasons[] = $row->season_id;
}
$result->closeCursor();
$seasons = implode(", ", $seasons);
$sql = "UPDATE nsa_team SET update_search = 1 WHERE team_id IN \n ( \n SELECT team_id FROM nsa_team_season WHERE season_id IN ({$seasons})\n )";
$this->GetDataConnection()->query($sql);
$sql = "UPDATE nsa_match SET update_search = 1 WHERE match_id IN \n ( \n SELECT match_id FROM nsa_season_match WHERE season_id IN ({$seasons})\n )";
$this->GetDataConnection()->query($sql);
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($o_competition);
$o_url_manager->Save($new_short_url);
# season URLs are generated from the competition, so regenerate those too
if (!$is_new) {
$o_season_mgr = new SeasonManager($this->GetSettings(), $this->GetDataConnection());
$o_season_mgr->ReadByCompetitionId(array($o_competition->GetId()));
$seasons = $o_season_mgr->GetItems();
unset($o_season_mgr);
foreach ($seasons as $season) {
/* @var $season Season */
$new_short_url = $o_url_manager->EnsureShortUrl($season, true);
if (is_object($new_short_url)) {
$s_sql = "UPDATE " . $this->GetSettings()->GetTable('Season') . " SET short_url = " . Sql::ProtectString($this->GetDataConnection(), $new_short_url->GetShortUrl()) . " WHERE season_id = " . Sql::ProtectNumeric($season->GetId());
$this->GetDataConnection()->query($s_sql);
$new_short_url->SetParameterValuesFromObject($season);
$o_url_manager->Save($new_short_url);
}
}
}
}
unset($o_url_manager);
return $o_competition->GetId();
}
