/**
* @return int
* @param Player $player
* @desc Save the supplied player to the database, and return the id
*/
public function SavePlayer($player)
{
/* @var $player Player */
if (!$player instanceof Player) {
throw new Exception("Unable to save player");
}
# Get tables
$players = $this->GetSettings()->GetTable('Player');
$matches = $this->GetSettings()->GetTable("Match");
$team_matches = $this->GetSettings()->GetTable("MatchTeam");
# The short URL depends on the team. Ensure we have the team's short URL before calculating
# the short URL for the player.
if ($player->Team()->GetId()) {
$sql = "SELECT short_url FROM nsa_team WHERE team_id = " . Sql::ProtectNumeric($player->Team()->GetId(), false);
$result = $this->GetDataConnection()->query($sql);
if ($row = $result->fetch()) {
$player->Team()->SetShortUrl($row->short_url);
}
$result->closeCursor();
}
# 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($player, true);
$corrected_player_name = $player->GetPlayerRole() == Player::PLAYER ? $this->CapitaliseName($player->GetName()) : $player->GetName();
# if no id, it's new; otherwise update
if ($player->GetId()) {
$sql = "UPDATE {$players} SET\n\t\t\tplayer_name = " . Sql::ProtectString($this->GetDataConnection(), $corrected_player_name, false) . ",\n\t\t\tcomparable_name = " . Sql::ProtectString($this->GetDataConnection(), $player->GetComparableName(), false) . ",\n\t\t\tshort_url = " . Sql::ProtectString($this->GetDataConnection(), $player->GetShortUrl(), false) . ",\n update_search = 1, \n\t\t\tdate_changed = " . gmdate('U');
$sql .= " WHERE player_id = " . Sql::ProtectNumeric($player->GetId());
$this->LoggedQuery($sql);
# Because name or URL may have changed, update stats table
$sql = "UPDATE nsa_player_match SET \n player_name = " . Sql::ProtectString($this->GetDataConnection(), $corrected_player_name, false) . ",\n player_url = " . Sql::ProtectString($this->GetDataConnection(), $player->GetShortUrl(), false) . "\n WHERE player_id = " . Sql::ProtectNumeric($player->GetId());
$this->LoggedQuery($sql);
} else {
$sql = "INSERT INTO {$players} SET\n\t\t\tplayer_name = " . Sql::ProtectString($this->GetDataConnection(), $corrected_player_name, false) . ",\n\t\t\tcomparable_name = " . Sql::ProtectString($this->GetDataConnection(), $player->GetComparableName(), false) . ",\n\t\t\tteam_id = " . Sql::ProtectNumeric($player->Team()->GetId(), false) . ",\n\t\t\tplayer_role = " . Sql::ProtectNumeric($player->GetPlayerRole(), false) . ",\n\t\t\tshort_url = " . Sql::ProtectString($this->GetDataConnection(), $player->GetShortUrl(), false) . ",\n\t\t\tupdate_search = 1,\n total_matches = 0,\n\t\t\tdate_added = " . gmdate('U') . ",\n\t\t\tdate_changed = " . gmdate('U');
$this->LoggedQuery($sql);
# get autonumber
$player->SetId($this->GetDataConnection()->insertID());
}
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($player);
$url_manager->Save($new_short_url);
}
unset($url_manager);
return $player->GetId();
}
private function UpdateMatchUrl(Match $match, $user_is_match_admin)
{
# Ensure we have the correct details to generate a short URL.
# Particularly important for tournament matches where this may have been updated from the tournament.
$sql = "SELECT start_time FROM nsa_match WHERE match_id = " . Sql::ProtectNumeric($match->GetId(), false);
$result = $this->GetDataConnection()->query($sql);
$row = $result->fetch();
if ($row) {
$match->SetStartTime($row->start_time);
}
# URL generation scenarios
# 1. New match added by public - generate short URL
# 2. Match updated by public - regenerate allowing current url to be kept
# 3. Match added or updated by admin - if blank or generation requested, regenerate allowing current url to be kept, otherwise keep
$new_short_url = null;
if (!$user_is_match_admin or $user_is_match_admin and (!$match->GetShortUrl() or !$match->GetUseCustomShortUrl())) {
# 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($match, true);
}
# Save the URL for the match, and copy to match statistics
$sql = "UPDATE nsa_match SET\r\n short_url = " . Sql::ProtectString($this->GetDataConnection(), $match->GetShortUrl()) . "\r\n WHERE match_id = " . Sql::ProtectNumeric($match->GetId());
$this->LoggedQuery($sql);
$sql = "UPDATE nsa_player_match SET\r\n match_url = " . Sql::ProtectString($this->GetDataConnection(), $match->GetShortUrl()) . "\r\n WHERE match_id = " . Sql::ProtectNumeric($match->GetId());
$this->GetDataConnection()->query($sql);
# Regenerate short URLs, but check whether manager exists because might've been denied permission above
# and in that same case we do NOT want to regenerate short URLs
if (isset($url_manager)) {
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($match);
$url_manager->Save($new_short_url);
}
unset($url_manager);
}
}
/**
* @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 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 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);
}
/**
* @return int
* @param Season $o_season
* @desc Save the supplied season to the database, and return the id
*/
function SaveSeason($o_season)
{
/* @var $o_result MySQlRawData */
# 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_season);
# build query
$s_season = $this->GetSettings()->GetTable('Season');
$s_team_season = $this->GetSettings()->GetTable('TeamSeason');
$s_rules = $this->GetSettings()->GetTable('SeasonRule');
$s_smt = $this->GetSettings()->GetTable('SeasonMatchType');
$s_points = $this->GetSettings()->GetTable('PointsAdjustment');
$o_competition = $o_season->GetCompetition();
$i_comp_id = null;
if (is_object($o_competition)) {
$i_comp_id = $o_competition->GetId();
}
# if no id, it's new; otherwise update
if ($o_season->GetId()) {
$s_sql = 'UPDATE ' . $s_season . ' SET ' . "season_name = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetName()) . ", " . 'start_year = ' . Sql::ProtectNumeric($o_season->GetStartYear()) . ', ' . 'end_year = ' . Sql::ProtectNumeric($o_season->GetEndYear()) . ', ' . "intro = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetIntro()) . ", " . "results = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetResults()) . ", " . 'show_table = ' . Sql::ProtectBool($o_season->GetShowTable()) . ', ' . 'show_runs_scored = ' . Sql::ProtectBool($o_season->GetShowTableRunsScored()) . ', ' . 'show_runs_conceded = ' . Sql::ProtectBool($o_season->GetShowTableRunsConceded()) . ', ' . "short_url = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetShortUrl()) . ", " . 'date_changed = ' . gmdate('U') . ' ' . 'WHERE season_id = ' . Sql::ProtectNumeric($o_season->GetId());
# run query
$this->GetDataConnection()->query($s_sql);
# Update match types
$s_sql = 'DELETE FROM ' . $s_smt . ' WHERE season_id = ' . Sql::ProtectNumeric($o_season->GetId());
$this->GetDataConnection()->query($s_sql);
while ($o_season->MatchTypes()->MoveNext()) {
# build query
$s_sql = 'INSERT INTO ' . $s_smt . ' SET ' . 'match_type = ' . Sql::ProtectNumeric($o_season->MatchTypes()->GetItem()) . ', ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'date_added = ' . Sql::ProtectNumeric(gmdate('U'));
# run query
$this->GetDataConnection()->query($s_sql);
}
# Update season rules
$s_sql = 'DELETE FROM ' . $s_rules . ' WHERE season_id = ' . Sql::ProtectNumeric($o_season->GetId());
$this->GetDataConnection()->query($s_sql);
$o_season->PossibleResults()->ResetCounter();
while ($o_season->PossibleResults()->MoveNext()) {
$o_mr = $o_season->PossibleResults()->GetItem();
/* @var $o_mr MatchResult */
$s_sql = 'INSERT INTO ' . $s_rules . ' SET ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'match_result_id = ' . Sql::ProtectNumeric($o_mr->GetResultType()) . ', ' . 'home_points = ' . Sql::ProtectNumeric($o_mr->GetHomePoints()) . ', ' . 'away_points = ' . Sql::ProtectNumeric($o_mr->GetAwayPoints()) . ', ' . 'date_added = ' . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U');
$this->GetDataConnection()->query($s_sql);
}
# Update points adjustments
$s_sql = 'DELETE FROM ' . $s_points . ' WHERE season_id = ' . Sql::ProtectNumeric($o_season->GetId());
$this->GetDataConnection()->query($s_sql);
if ($o_season->PointsAdjustments()->GetCount()) {
foreach ($o_season->PointsAdjustments() as $o_point) {
/* @var $o_point PointsAdjustment */
$s_sql = 'INSERT INTO ' . $s_points . ' SET ' . 'points = ' . Sql::ProtectNumeric($o_point->GetPoints()) . ', ' . 'team_id = ' . Sql::ProtectNumeric($o_point->GetTeam()->GetId()) . ', ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'reason = ' . Sql::ProtectString($this->GetDataConnection(), $o_point->GetReason()) . ', ' . 'date_added = ' . Sql::ProtectNumeric($o_point->GetDate());
$this->GetDataConnection()->query($s_sql);
}
}
# Update teams
$s_sql = 'DELETE FROM ' . $s_team_season . ' WHERE season_id = ' . Sql::ProtectNumeric($o_season->GetId());
$this->GetDataConnection()->query($s_sql);
$a_teams = $o_season->GetTeams();
foreach ($a_teams as $o_team) {
$b_withdrawn_from_league = is_object($o_season->TeamsWithdrawnFromLeague()->GetItemByProperty('GetId', $o_team->GetId()));
# build query
$s_sql = 'INSERT INTO ' . $s_team_season . ' SET ' . 'team_id = ' . Sql::ProtectNumeric($o_team->GetId()) . ', ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'withdrawn_league ' . Sql::ProtectBool($b_withdrawn_from_league, false, true) . ', ' . 'date_added = ' . Sql::ProtectNumeric(gmdate('U'));
# run query
$this->GetDataConnection()->query($s_sql);
}
} else {
$s_sql = 'INSERT INTO ' . $s_season . ' SET ' . 'competition_id = ' . Sql::ProtectNumeric($i_comp_id, true) . ', ' . "season_name = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetName()) . ", " . 'start_year = ' . Sql::ProtectNumeric($o_season->GetStartYear()) . ', ' . 'end_year = ' . Sql::ProtectNumeric($o_season->GetEndYear()) . ', ' . "intro = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetIntro()) . ", " . "short_url = " . Sql::ProtectString($this->GetDataConnection(), $o_season->GetShortUrl()) . ", " . 'date_added = ' . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U');
# run query
$o_result = $this->GetDataConnection()->query($s_sql);
# get autonumber
$o_season->SetId($this->GetDataConnection()->insertID());
# Since this is a new season, save time by starting off with the teams from the previous season,
# excluding those marked as not playing any more
$s_sql = "SELECT team.team_id " . 'FROM (' . $s_season . ' INNER JOIN ' . $s_team_season . ' ON ' . $s_season . '.season_id = ' . $s_team_season . '.season_id) ' . "INNER JOIN nsa_team AS team ON {$s_team_season}.team_id = team.team_id " . 'WHERE ' . $s_season . '.competition_id = ' . Sql::ProtectNumeric($i_comp_id) . ' AND ' . $s_season . ".is_latest = 1 AND team.active = 1";
$o_result = $this->GetDataConnection()->query($s_sql);
if (!is_null($o_result)) {
while ($o_row = $o_result->fetch()) {
$s_sql = 'INSERT INTO ' . $s_team_season . ' SET ' . 'team_id = ' . Sql::ProtectNumeric($o_row->team_id) . ', ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'date_added = ' . Sql::ProtectNumeric(gmdate('U'));
$this->GetDataConnection()->query($s_sql);
}
}
# ...match types from the previous season too
$s_sql = 'SELECT match_type ' . 'FROM ' . $s_season . ' INNER JOIN ' . $s_smt . ' ON ' . $s_season . '.season_id = ' . $s_smt . '.season_id ' . 'WHERE ' . $s_season . '.competition_id = ' . Sql::ProtectNumeric($i_comp_id) . ' AND ' . $s_season . '.is_latest = 1';
$o_result = $this->GetDataConnection()->query($s_sql);
if (!is_null($o_result)) {
while ($o_row = $o_result->fetch()) {
$s_sql = 'INSERT INTO ' . $s_smt . ' SET ' . 'match_type = ' . Sql::ProtectNumeric($o_row->match_type) . ', ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'date_added = ' . gmdate('U');
$this->GetDataConnection()->query($s_sql);
}
}
# ...and league table settings
$s_sql = "SELECT {$s_season}.show_table,{$s_season}.show_runs_scored, {$s_season}.show_runs_conceded, {$s_rules}.match_result_id, {$s_rules}.home_points, {$s_rules}.away_points " . "FROM {$s_season} LEFT JOIN {$s_rules} ON {$s_season}.season_id = {$s_rules}.season_id " . "WHERE {$s_season}.competition_id = " . Sql::ProtectNumeric($i_comp_id) . " AND {$s_season}.is_latest = 1";
$o_result = $this->GetDataConnection()->query($s_sql);
if (!is_null($o_result)) {
$show_table_copied = false;
while ($o_row = $o_result->fetch()) {
if (!$show_table_copied) {
$s_sql = "UPDATE {$s_season} SET\n\t\t\t\t\t\tshow_table = {$o_row->show_table},\n\t\t\t\t\t\tshow_runs_scored = {$o_row->show_runs_scored},\n\t\t\t\t\t\tshow_runs_conceded = {$o_row->show_runs_conceded}\n\t\t\t\t\t\tWHERE season_id = " . Sql::ProtectNumeric($o_season->GetId());
$this->GetDataConnection()->query($s_sql);
$show_table_copied = true;
}
if (!is_null($o_row->match_result_id)) {
$s_sql = 'INSERT INTO ' . $s_rules . ' SET ' . 'season_id = ' . Sql::ProtectNumeric($o_season->GetId()) . ', ' . 'match_result_id = ' . Sql::ProtectNumeric($o_row->match_result_id) . ', ' . 'home_points = ' . Sql::ProtectNumeric($o_row->home_points) . ', ' . 'away_points = ' . Sql::ProtectNumeric($o_row->away_points) . ', ' . 'date_added = ' . gmdate('U') . ', ' . 'date_changed = ' . gmdate('U');
$this->GetDataConnection()->query($s_sql);
}
}
}
}
# Update latest season
if ($i_comp_id != null) {
$this->UpdateLatestSeason($i_comp_id);
}
# Regenerate short URLs
if (is_object($new_short_url)) {
$new_short_url->SetParameterValuesFromObject($o_season);
$o_url_manager->Save($new_short_url);
}
unset($o_url_manager);
return $o_season->GetId();
}
/**
* @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();
}
/**
* @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();
}
