Everything needed is in the data tables. PM me a database dump so I can play with the query.
Sample query only. This is proof of concept:
[php]SELECT
PlayerID,
FirstName,
LastName,
Hometown,
CurrentTown,
Height,
Picture,
College,
LocalBeach,
IsAVAVolunteer,
count(events.StartDate) as CurrentPlayed,
CurrentPoints,
(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating
WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1)
WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2)
ELSE (CurrentUnadjustedRating + 3) END) AS CurrentRating,
(CASE WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating
WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1)
WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2)
ELSE (CurrentUnadjustedRating + 3) END) AS CurrentRating,
COUNT(ResultID) AS AllTimePlayed,
NumberOfTeams,
Result
FROM
Players
INNER JOIN
Teams USING (PlayerID)
INNER JOIN
Results USING (TeamID)
INNER JOIN
Events USING (EventID)
WHERE PlayerID = 1041
AND year(events.StartDate) = 2015;
[/php]
I need to know the criteria for the date for it to be dynamic. Is it only going to look for the current year, or other years as well?
Ideally it should count the past 7 events…
when a new date is added it drops the oldest one… etc… always counting 7 events points.
The currentPlayed should go no higher than 7? That seems odd if they played say 20 games in that year.
There are only max 7 events each year we host. Some players play less some all.
Points only get counted for the past 7 events max a player played. so instead of counting the current year… the code should count points earned (and how many they played) in the last 256 days… starting point for counting back 256 days is the last event date entered.
make sense?
Possibility 2:
[php]# DATEDIFF()
SELECT
PlayerID,
FirstName,
LastName,
Hometown,
CurrentTown,
Height,
Picture,
College,
LocalBeach,
IsAVAVolunteer,
count(events.StartDate) as CurrentPlayed,
CurrentPoints,
(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating
WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1)
WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2)
ELSE (CurrentUnadjustedRating + 3) END) AS CurrentRating,
(CASE WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating
WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1)
WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2)
ELSE (CurrentUnadjustedRating + 3) END) AS CurrentRating,
COUNT(ResultID) AS AllTimePlayed,
NumberOfTeams,
Result
FROM
Players
INNER JOIN
Teams USING (PlayerID)
INNER JOIN
Results USING (TeamID)
INNER JOIN
Events USING (EventID)
WHERE
datediff(CURDATE(), events.StartDate) <= 256
AND PlayerID = 1041;
[/php]
Will this effect the “Ratings” logic? I’m hoping it doesn’t.
There are 3 things:
- How many Events Played (max 7 in the last 256 days)
- Points earned - total points earned by adding each of the 7 or (how many events played) in the last 256 days.
- Rating… different logic.
explained here: http://www.alkibeachvolleyball.com/faq-seeding-rating-points/
The only thing that was really modified was the HOW it gets the data. So, it only pulls the last 256 days data for the specific player.
ok, but what new code should I paste in what file? Is the Players.class.php file the only one needing modification?
What do I paste into it? sorry I’m not a developer, I’m clueless… thanks for your help
Here’s the current code in the Players.class.php… on line 378 seems to be the first reference to date…
What lines do I modify with what?
[php]
<?php //------------------------------------------------------------------ // File: Player.class.php // Author: // Date Created: 2010-03-25 // // Player Management //------------------------------------------------------------------ // Lightweight Object representing player name & id. class PlayerBaseData { private $playerId; private $firstName; private $lastName; protected function setPlayerId($playerId) { $this->playerId = $playerId; } public function GetPlayerId() { return $this->playerId; } protected function setFirstName($firstName) { $this->firstName = $firstName; } public function GetFirstName() { return $this->firstName; } protected function setLastName($lastName) { $this->lastName = $lastName; } public function GetLastName() { return $this->lastName; } public function GetFullName() { return sprintf('%s %s', $this->GetFirstName(), $this->GetLastName()); } } // Extends to include all values required to display player previews. class PlayerPreviewData extends PlayerBaseData { private $genderId; private $currentPlayed; private $currentPoints; private $currentRating; private $eventsPlayedCollection; // Array of events. protected function setGenderId($genderId) { $this->genderId = $genderId; } public function GetGenderId() { return $this->genderId; } protected function setCurrentPlayed($currentPlayed) { $this->currentPlayed = $currentPlayed; } public function GetCurrentPlayed() { return $this->currentPlayed; } protected function setCurrentPoints($currentPoints) { $this->currentPoints = $currentPoints; } public function GetCurrentPoints() { return $this->currentPoints; } protected function setCurrentRating($currentRating) { $this->currentRating = $currentRating; } public function GetCurrentRating() { return $this->currentRating; } public function GetCurrentRatingName() { $ratings = GetRatings(); return $ratings[$this->currentRating]; } } // Extends to inclue all remaining player data (Player Details). class PlayerData extends PlayerPreviewData { private $profilePicture; private $hometown; private $currentTown; private $height; private $localBeach; private $college; private $isAVAVolunteer; private $currentBestFinish; private $allTimePlayed; private $allTimePoints; private $allTimeRating; private $allTimeBestFinish; private $currentOpenWins; private $allTimeOpenWins; protected function setProfilePicture($profilePicture) { $this->profilePicture = $profilePicture; } public function GetProfilePicture() { return $this->profilePicture; } protected function setHometown($hometown) { $this->hometown = $hometown; } public function GetHometown() { return $this->hometown; } protected function setCurrentTown($currentTown) { $this->currentTown = $currentTown; } public function GetCurrentTown() { return $this->currentTown; } protected function setHeight($height) { $this->height = $height; } public function GetHeight() { return $this->height; } protected function setLocalBeach($localBeach) { $this->localBeach = $localBeach; } public function GetLocalBeach() { return $this->localBeach; } protected function setCollege($college) { $this->college = $college; } public function GetCollege() { return $this->college; } protected function setIsAVAVolunteer($isAVAVolunteer) { $this->isAVAVolunteer = $isAVAVolunteer; } public function GetIsAVAVolunteer() { return $this->isAVAVolunteer; } protected function setCurrentBestFinish($currentBestFinish) { $this->currentBestFinish = $currentBestFinish; } public function GetCurrentBestFinish() { return $this->currentBestFinish; } protected function setAllTimePlayed($allTimePlayed) { $this->allTimePlayed = $allTimePlayed; } public function GetAllTimePlayed() { return $this->allTimePlayed; } protected function setAllTimePoints($allTimePoints) { $this->allTimePoints = $allTimePoints; } public function GetAllTimePoints() { return $this->allTimePoints; } protected function setAllTimeRating($allTimeRating) { $this->allTimeRating = $allTimeRating; } public function GetAllTimeRating() { return $this->allTimeRating; } protected function setAllTimeBestFinish($allTimeBestFinish) { $this->allTimeBestFinish = $allTimeBestFinish; } public function GetAllTimeBestFinish() { return $this->allTimeBestFinish; } protected function setCurrentOpenWins($currentOpenWins) { $this->currentOpenWins = $currentOpenWins; } public function GetCurrentOpenWins() { return $this->currentOpenWins; } protected function setAllTimeOpenWins($allTimeOpenWins) { $this->allTimeOpenWins = $allTimeOpenWins; } public function GetAllTimeOpenWins() { return $this->allTimeOpenWins; } public function GetLocalBeachName() { $beachNames = GetBeaches(); return $beachNames[$this->localBeach]; } public function GetAllTimeRatingName() { $ratings = GetRatings(); return $ratings[$this->allTimeRating]; } public function GetCurrentBestFinishOrdinal() { return ConvertIntegerToOrdinal($this->currentBestFinish); } public function GetAllTimeBestFinishOrdinal() { return ConvertIntegerToOrdinal($this->allTimeBestFinish); } } // Core Player logic. class Player extends PlayerData { // Collection of all PlayerPreviewData public static function GetPlayerPreviews() { $playerPreviewCollection = array(); $query = "SELECT PlayerID, FirstName, LastName, GenderID, CurrentPlayed, CurrentPoints, "; //TODO: MAKE SQL CAP RATING AT 4. $query .= "(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) "; $query .= " ELSE (CurrentUnadjustedRating + 3) "; $query .= "END) AS CurrentRating "; $query .= "FROM Players "; $query .= "ORDER BY CurrentRating ASC, CurrentPoints DESC, FirstName, LastName"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $pp = new PlayerPreviewData(); $pp->setPlayerId($row['PlayerID']); $pp->setFirstName($row['FirstName']); $pp->setLastName($row['LastName']); $pp->setGenderId($row['GenderID']); $pp->setCurrentPlayed($row['CurrentPlayed']); $pp->setCurrentPoints($row['CurrentPoints']); //$pp->setCurrentRating($row['CurrentRating']); //$currentUnadjustedRating = $row['CurrentUnadjustedRating']; //$currentUnadjustedRatingYear = $row['CurrentUnadjustedRatingYear']; //$currentRating = AdjustRatingBasedOnTime($row['CurrentUnadjustedRatingYear'], $row['CurrentUnadjustedRating']); $currentRating = $row['CurrentRating']; if ($currentRating > 4) $currentRating = 4; $pp->setCurrentRating($currentRating); $playerPreviewCollection[] = $pp; } return $playerPreviewCollection; } // Return base player object (teamate). public static function GetPlayerBaseData($playerId, $firstName, $lastName) { $pbd = new PlayerBaseData(); $pbd->setPlayerId($playerId); $pbd->setFirstName($firstName); $pbd->setLastName($lastName); return $pbd; } // Get All Player Data based on Player Id. public static function GetPlayerData($playerDataId) { $pd = new PlayerData(); $query = "SELECT PlayerID, FirstName, LastName, Hometown, CurrentTown, Height, Picture, College, "; $query .= "LocalBeach, IsAVAVolunteer, CurrentPlayed, CurrentPoints, "; //TODO: MAKE SQL CAP RATING AT 4. $query .= "(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) "; $query .= " ELSE (CurrentUnadjustedRating + 3) "; $query .= "END) AS CurrentRating "; // $query .= "(CASE WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating "; // $query .= " WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) "; // $query .= " WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) "; // $query .= " ELSE (CurrentUnadjustedRating + 3) "; // $query .= "END) AS CurrentRating "; //GET TotalEventsPlayed $query .= ", COUNT(ResultID) AS AllTimePlayed "; //GET TotalPoints $query .= ", NumberOfTeams, Result "; $query .= "FROM Players "; $query .= "INNER JOIN Teams USING (PlayerID) "; $query .= "INNER JOIN Results USING (TeamID) "; $query .= "INNER JOIN Events USING (EventID) "; $query .= "WHERE PlayerID = $playerDataId "; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $pd->setPlayerId($row['PlayerID']); $pd->setFirstName($row['FirstName']); $pd->setLastName($row['LastName']); $pd->setHometown($row['Hometown']); $pd->setCurrentTown($row['CurrentTown']); $pd->setHeight($row['Height']); $pd->setProfilePicture($row['Picture']); $pd->setCollege($row['College']); $pd->setLocalBeach($row['LocalBeach']); $pd->setIsAVAVolunteer($row['IsAVAVolunteer']); $pd->setCurrentPlayed($row['CurrentPlayed']); $pd->setCurrentPoints($row['CurrentPoints']); //$pd->setCurrentRating($row['CurrentRating']); //$currentRating = AdjustRatingBasedOnTime($row['CurrentUnadjustedRatingYear'], $row['CurrentUnadjustedRating']); $currentRating = $row['CurrentRating']; if ($currentRating > 4) $currentRating = 4; $pd->setCurrentRating($currentRating); $pd->setAllTimePlayed($row['AllTimePlayed']); //$pd->setAllTimePoints($row['AllTimePoints']); //$pd->setAllTimeRating($currentRating); //$allTimeRating = CalculateRatingEarned($row['NumberOfTeams'], $row['Result']); // $pd->setAllTimeRating($allTimeRating); } $pd->eventsPlayedCollection = Player::GetEventsPlayedComplete($playerDataId); foreach($pd->eventsPlayedCollection as $key => $value) { $eventDate = $pd->eventsPlayedCollection[$key]->eventDate; $numberOfTeams = $pd->eventsPlayedCollection[$key]->numberOfTeams; $finish = $pd->eventsPlayedCollection[$key]->finish; $allTimePoints += $pd->eventsPlayedCollection[$key]->points; // Calculate Best Rating //$rating = CalculateRatingEarned($numberOfTeams, $finish); $genderId = $pd->eventsPlayedCollection[$key]->genderId; $divisionId = $pd->eventsPlayedCollection[$key]->divisionId; //$rating = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $finish); $rating = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $finish); if ($allTimeRating == 0) $allTimeRating = $rating; elseif ($rating < $allTimeRating) $allTimeRating = $rating; // Calculate Best Finish if (IsDateInCurrentSeason($eventDate)) { if ($currentBestFinish == 0) { $currentBestFinish = $finish; } elseif ($finish < $currentBestFinish) { $currentBestFinish = $finish; } } if ($allTimeBestFinish == 0) { $allTimeBestFinish = $finish; } elseif ($finish < $allTimeBestFinish) { $allTimeBestFinish = $finish; } // Calculate Number of Open Wins. if(IsDateInCurrentSeason($eventDate)) { if ($finish == 1) $currentOpenWins++; } if ($finish == 1) $allTimeOpenWins++; } $pd->setAllTimePoints($allTimePoints); $pd->setAllTimeRating($allTimeRating); $pd->setCurrentBestFinish($currentBestFinish); $pd->setAllTimeBestFinish($allTimeBestFinish); $pd->setCurrentOpenWins($currentOpenWins); $pd->setAllTimeOpenWins($allTimeOpenWins); return $pd; } // // Get Collection of all played events. // private static function GetEventsPlayed($playerId) // { // $eventsPlayedCollection = array(); // // $query = "SELECT StartDate, Result, NumberOfTeams FROM Players "; // $query .= "INNER JOIN Teams T USING (PlayerID) "; // $query .= "INNER JOIN Results R USING (TeamID) "; // $query .= "INNER JOIN Events E USING (EventID) "; // $query .= "INNER JOIN Divisions D USING (DivisionID) "; // $query .= "INNER JOIN Tournaments TOUR USING (TournamentID) "; // $query .= "WHERE P.PlayerID = $playerId"; // $result = GetQueryResult($query); // // while($row = mysqli_fetch_array($result)) // { // $eventDate = $row['StartDate']; // $results = $row['Result']; // $numberOfTeams = $row['NumberOfTeams']; // //$points = CalculatePoints($numberOfTeams, $results); ////$points = CalculatePoints($numberOfTeams); // $event = PlayerEvent::GetPlayerEvent($eventDate, $results, $points, $numberOfTeams); // $eventsPlayedCollection[] = $event; // } // // return $eventsPlayedCollection; // } // Get Collection of all played events. private function GetEventsPlayedComplete($playerId) { $eventsPlayedCompleteCollection = array(); //$result = GetEventsPlayed($playerId); //$query = "SELECT * FROM Players P "; //$query = "SELECT TOUR.StartDate, R.Result, E.NumberOfTeams, E.DivisionID, E.GenderID, E.EventID, D.Division, T.TeamID, Tour.TournamentID "; $query = "SELECT PlayerID, TOUR.StartDate, E.StartDate, Result, NumberOfTeams, DivisionID, E.GenderID, EventID, Division, TeamID, Year(E.StartDate) AS Year "; $query .= "FROM Players P "; $query .= "INNER JOIN Teams T USING (PlayerID) "; $query .= "INNER JOIN Results R USING (TeamID) "; $query .= "INNER JOIN Events E USING (EventID) "; $query .= "INNER JOIN Divisions D USING (DivisionID) "; $query .= "INNER JOIN Tournaments TOUR USING (TournamentID) "; $query .= "WHERE P.PlayerID = $playerId "; //$query .= " ORDER BY Year DESC"; //$query .= " ORDER BY TOUR.StartDate DESC"; $query .= "ORDER BY Year DESC, E.GenderID, E.DivisionID"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $eventDate = $row['StartDate']; $results = $row['Result']; $numberOfTeams = $row['NumberOfTeams']; $divisionId = $row['DivisionID']; $genderId = $row['GenderID']; //$points = CalculatePoints($numberOfTeams, $results); $points = CalculatePoints($genderId, $divisionId, $numberOfTeams, $results); // Additional fields required $eventId = $row['EventID']; $division = $row['Division']; $partner = Player::GetTeamate($row['TeamID'], $playerId); //$genderId = $row['GenderID']; $tournamentId = $row['TournamentID']; // $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $division, $partner, $results, $points, $numberOfTeams, $genderId); //$ratingEarned = CalculateRatingEarned($numberOfTeams, $results); $ratingEarned = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $results); // $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $division, $partner, $results, $points, $numberOfTeams, $genderId, $ratingEarned); $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $divisionId, $partner, $results, $points, $numberOfTeams, $genderId, $ratingEarned); $eventsPlayedCompleteCollection[] = $event; } return $eventsPlayedCompleteCollection; } // TODO: Refactor when we're ready to allow > 2 person teams. private function GetTeamate($teamId, $playerId) { $query = "SELECT PlayerID, FirstName, LastName "; $query .= "FROM Players INNER JOIN Teams USING(PlayerID) "; $query .= "WHERE TeamID = $teamId AND PlayerID <> $playerId"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $partner = Player::GetPlayerBaseData($row['PlayerID'], $row['FirstName'], $row['LastName']); } return $partner; } } ?>[/php]
Comment out lines 215 to 242 with this,
[php]$query = <<<SQL
SELECT
PlayerID,
FirstName,
LastName,
Hometown,
CurrentTown,
Height,
Picture,
College,
LocalBeach,
IsAVAVolunteer,
count(events.StartDate) as CurrentPlayed,
CurrentPoints,
(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating
WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1)
WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2)
ELSE (CurrentUnadjustedRating + 3) END) AS CurrentRating,
(CASE WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating
WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1)
WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2)
ELSE (CurrentUnadjustedRating + 3) END) AS CurrentRating,
COUNT(ResultID) AS AllTimePlayed,
NumberOfTeams,
Result
FROM
Players
INNER JOIN
Teams USING (PlayerID)
INNER JOIN
Results USING (TeamID)
INNER JOIN
Events USING (EventID)
WHERE
datediff(CURDATE(), events.StartDate) <= 256
AND PlayerID = $playerDataId;
SQL;[/php]
The way this is formatted is SO MUCH cleaner than several
[php]
$query .=
$query .=
$query .=
$query .=[/php]
and easier to modify. You just have to make sure that the SQL; portion has no whitespace to the left of it, otherwise it keeps interpreting what comes next as part of the string.
And a comment is this //
Thanks, but there seems to be something off with the syntax… see the screen capture…
Am I missing something? also lots of red errors showed up further down the page
Got the commenting out part right.
Looks like you have a little whitespace on line 281. The HEREDOC syntax is very particular. The SQL; part has to be the very first thing on the line.
Removing the white space fixed the red syntax errors…
but after uploading the modified code file the All Player page still shows the same… still shows played 4 etc
http://phillipvanrooyen.com/ava-table/content/players_13.php
however, what changed was on the Player Detail page when you click on Franklin’s name:
http://phillipvanrooyen.com/ava-table/content/playerDetail.php?player=1041
- the overview data in the little gray box is now just missing. before it was there but incorrect…
- the image link is now broken
- the players name is also missing from above the image
strange… what could be going on…??
It was the All Player page I was trying to fix, but that stayed the same and the detail page got messed up.
strange…
That is odd, I run the query ( for the individual player ) and get all the data,
Has it got something to do with the code on lines… 156 to 195 …higher up in the same file that maybe also needs to change?
Seems like similar code
On line 387 of the original code (below) there’s a reference to TOUR.StartDate…
Has that maybe got something to do with why it’s not working when I make the modifications you suggested to lines 214 to 241?
[php]
<?php //------------------------------------------------------------------ // File: Player.class.php // Author: Bryan Frost // Date Created: 2010-03-25 // // Player Management //------------------------------------------------------------------ // Lightweight Object representing player name & id. class PlayerBaseData { private $playerId; private $firstName; private $lastName; protected function setPlayerId($playerId) { $this->playerId = $playerId; } public function GetPlayerId() { return $this->playerId; } protected function setFirstName($firstName) { $this->firstName = $firstName; } public function GetFirstName() { return $this->firstName; } protected function setLastName($lastName) { $this->lastName = $lastName; } public function GetLastName() { return $this->lastName; } public function GetFullName() { return sprintf('%s %s', $this->GetFirstName(), $this->GetLastName()); } } // Extends to include all values required to display player previews. class PlayerPreviewData extends PlayerBaseData { private $genderId; private $currentPlayed; private $currentPoints; private $currentRating; private $eventsPlayedCollection; // Array of events. protected function setGenderId($genderId) { $this->genderId = $genderId; } public function GetGenderId() { return $this->genderId; } protected function setCurrentPlayed($currentPlayed) { $this->currentPlayed = $currentPlayed; } public function GetCurrentPlayed() { return $this->currentPlayed; } protected function setCurrentPoints($currentPoints) { $this->currentPoints = $currentPoints; } public function GetCurrentPoints() { return $this->currentPoints; } protected function setCurrentRating($currentRating) { $this->currentRating = $currentRating; } public function GetCurrentRating() { return $this->currentRating; } public function GetCurrentRatingName() { $ratings = GetRatings(); return $ratings[$this->currentRating]; } } // Extends to inclue all remaining player data (Player Details). class PlayerData extends PlayerPreviewData { private $profilePicture; private $hometown; private $currentTown; private $height; private $localBeach; private $college; private $isAVAVolunteer; private $currentBestFinish; private $allTimePlayed; private $allTimePoints; private $allTimeRating; private $allTimeBestFinish; private $currentOpenWins; private $allTimeOpenWins; protected function setProfilePicture($profilePicture) { $this->profilePicture = $profilePicture; } public function GetProfilePicture() { return $this->profilePicture; } protected function setHometown($hometown) { $this->hometown = $hometown; } public function GetHometown() { return $this->hometown; } protected function setCurrentTown($currentTown) { $this->currentTown = $currentTown; } public function GetCurrentTown() { return $this->currentTown; } protected function setHeight($height) { $this->height = $height; } public function GetHeight() { return $this->height; } protected function setLocalBeach($localBeach) { $this->localBeach = $localBeach; } public function GetLocalBeach() { return $this->localBeach; } protected function setCollege($college) { $this->college = $college; } public function GetCollege() { return $this->college; } protected function setIsAVAVolunteer($isAVAVolunteer) { $this->isAVAVolunteer = $isAVAVolunteer; } public function GetIsAVAVolunteer() { return $this->isAVAVolunteer; } protected function setCurrentBestFinish($currentBestFinish) { $this->currentBestFinish = $currentBestFinish; } public function GetCurrentBestFinish() { return $this->currentBestFinish; } protected function setAllTimePlayed($allTimePlayed) { $this->allTimePlayed = $allTimePlayed; } public function GetAllTimePlayed() { return $this->allTimePlayed; } protected function setAllTimePoints($allTimePoints) { $this->allTimePoints = $allTimePoints; } public function GetAllTimePoints() { return $this->allTimePoints; } protected function setAllTimeRating($allTimeRating) { $this->allTimeRating = $allTimeRating; } public function GetAllTimeRating() { return $this->allTimeRating; } protected function setAllTimeBestFinish($allTimeBestFinish) { $this->allTimeBestFinish = $allTimeBestFinish; } public function GetAllTimeBestFinish() { return $this->allTimeBestFinish; } protected function setCurrentOpenWins($currentOpenWins) { $this->currentOpenWins = $currentOpenWins; } public function GetCurrentOpenWins() { return $this->currentOpenWins; } protected function setAllTimeOpenWins($allTimeOpenWins) { $this->allTimeOpenWins = $allTimeOpenWins; } public function GetAllTimeOpenWins() { return $this->allTimeOpenWins; } public function GetLocalBeachName() { $beachNames = GetBeaches(); return $beachNames[$this->localBeach]; } public function GetAllTimeRatingName() { $ratings = GetRatings(); return $ratings[$this->allTimeRating]; } public function GetCurrentBestFinishOrdinal() { return ConvertIntegerToOrdinal($this->currentBestFinish); } public function GetAllTimeBestFinishOrdinal() { return ConvertIntegerToOrdinal($this->allTimeBestFinish); } } // Core Player logic. class Player extends PlayerData { // Collection of all PlayerPreviewData public static function GetPlayerPreviews() { $playerPreviewCollection = array(); $query = "SELECT PlayerID, FirstName, LastName, GenderID, CurrentPlayed, CurrentPoints, "; //TODO: MAKE SQL CAP RATING AT 4. $query .= "(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) "; $query .= " ELSE (CurrentUnadjustedRating + 3) "; $query .= "END) AS CurrentRating "; $query .= "FROM Players "; $query .= "ORDER BY CurrentRating ASC, CurrentPoints DESC, FirstName, LastName"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $pp = new PlayerPreviewData(); $pp->setPlayerId($row['PlayerID']); $pp->setFirstName($row['FirstName']); $pp->setLastName($row['LastName']); $pp->setGenderId($row['GenderID']); $pp->setCurrentPlayed($row['CurrentPlayed']); $pp->setCurrentPoints($row['CurrentPoints']); //$pp->setCurrentRating($row['CurrentRating']); //$currentUnadjustedRating = $row['CurrentUnadjustedRating']; //$currentUnadjustedRatingYear = $row['CurrentUnadjustedRatingYear']; //$currentRating = AdjustRatingBasedOnTime($row['CurrentUnadjustedRatingYear'], $row['CurrentUnadjustedRating']); $currentRating = $row['CurrentRating']; if ($currentRating > 4) $currentRating = 4; $pp->setCurrentRating($currentRating); $playerPreviewCollection[] = $pp; } return $playerPreviewCollection; } // Return base player object (teamate). public static function GetPlayerBaseData($playerId, $firstName, $lastName) { $pbd = new PlayerBaseData(); $pbd->setPlayerId($playerId); $pbd->setFirstName($firstName); $pbd->setLastName($lastName); return $pbd; } // Get All Player Data based on Player Id. public static function GetPlayerData($playerDataId) { $pd = new PlayerData(); $query = "SELECT PlayerID, FirstName, LastName, Hometown, CurrentTown, Height, Picture, College, "; $query .= "LocalBeach, IsAVAVolunteer, CurrentPlayed, CurrentPoints, "; //TODO: MAKE SQL CAP RATING AT 4. $query .= "(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) "; $query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) "; $query .= " ELSE (CurrentUnadjustedRating + 3) "; $query .= "END) AS CurrentRating "; // $query .= "(CASE WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating "; // $query .= " WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) "; // $query .= " WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) "; // $query .= " ELSE (CurrentUnadjustedRating + 3) "; // $query .= "END) AS CurrentRating "; //GET TotalEventsPlayed $query .= ", COUNT(ResultID) AS AllTimePlayed "; //GET TotalPoints $query .= ", NumberOfTeams, Result "; $query .= "FROM Players "; $query .= "INNER JOIN Teams USING (PlayerID) "; $query .= "INNER JOIN Results USING (TeamID) "; $query .= "INNER JOIN Events USING (EventID) "; $query .= "WHERE PlayerID = $playerDataId "; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $pd->setPlayerId($row['PlayerID']); $pd->setFirstName($row['FirstName']); $pd->setLastName($row['LastName']); $pd->setHometown($row['Hometown']); $pd->setCurrentTown($row['CurrentTown']); $pd->setHeight($row['Height']); $pd->setProfilePicture($row['Picture']); $pd->setCollege($row['College']); $pd->setLocalBeach($row['LocalBeach']); $pd->setIsAVAVolunteer($row['IsAVAVolunteer']); $pd->setCurrentPlayed($row['CurrentPlayed']); $pd->setCurrentPoints($row['CurrentPoints']); //$pd->setCurrentRating($row['CurrentRating']); //$currentRating = AdjustRatingBasedOnTime($row['CurrentUnadjustedRatingYear'], $row['CurrentUnadjustedRating']); $currentRating = $row['CurrentRating']; if ($currentRating > 4) $currentRating = 4; $pd->setCurrentRating($currentRating); $pd->setAllTimePlayed($row['AllTimePlayed']); //$pd->setAllTimePoints($row['AllTimePoints']); //$pd->setAllTimeRating($currentRating); //$allTimeRating = CalculateRatingEarned($row['NumberOfTeams'], $row['Result']); // $pd->setAllTimeRating($allTimeRating); } $pd->eventsPlayedCollection = Player::GetEventsPlayedComplete($playerDataId); foreach($pd->eventsPlayedCollection as $key => $value) { $eventDate = $pd->eventsPlayedCollection[$key]->eventDate; $numberOfTeams = $pd->eventsPlayedCollection[$key]->numberOfTeams; $finish = $pd->eventsPlayedCollection[$key]->finish; $allTimePoints += $pd->eventsPlayedCollection[$key]->points; // Calculate Best Rating //$rating = CalculateRatingEarned($numberOfTeams, $finish); $genderId = $pd->eventsPlayedCollection[$key]->genderId; $divisionId = $pd->eventsPlayedCollection[$key]->divisionId; //$rating = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $finish); $rating = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $finish); if ($allTimeRating == 0) $allTimeRating = $rating; elseif ($rating < $allTimeRating) $allTimeRating = $rating; // Calculate Best Finish if (IsDateInCurrentSeason($eventDate)) { if ($currentBestFinish == 0) { $currentBestFinish = $finish; } elseif ($finish < $currentBestFinish) { $currentBestFinish = $finish; } } if ($allTimeBestFinish == 0) { $allTimeBestFinish = $finish; } elseif ($finish < $allTimeBestFinish) { $allTimeBestFinish = $finish; } // Calculate Number of Open Wins. if(IsDateInCurrentSeason($eventDate)) { if ($finish == 1) $currentOpenWins++; } if ($finish == 1) $allTimeOpenWins++; } $pd->setAllTimePoints($allTimePoints); $pd->setAllTimeRating($allTimeRating); $pd->setCurrentBestFinish($currentBestFinish); $pd->setAllTimeBestFinish($allTimeBestFinish); $pd->setCurrentOpenWins($currentOpenWins); $pd->setAllTimeOpenWins($allTimeOpenWins); return $pd; } // // Get Collection of all played events. // private static function GetEventsPlayed($playerId) // { // $eventsPlayedCollection = array(); // // $query = "SELECT StartDate, Result, NumberOfTeams FROM Players "; // $query .= "INNER JOIN Teams T USING (PlayerID) "; // $query .= "INNER JOIN Results R USING (TeamID) "; // $query .= "INNER JOIN Events E USING (EventID) "; // $query .= "INNER JOIN Divisions D USING (DivisionID) "; // $query .= "INNER JOIN Tournaments TOUR USING (TournamentID) "; // $query .= "WHERE P.PlayerID = $playerId"; // $result = GetQueryResult($query); // // while($row = mysqli_fetch_array($result)) // { // $eventDate = $row['StartDate']; // $results = $row['Result']; // $numberOfTeams = $row['NumberOfTeams']; // //$points = CalculatePoints($numberOfTeams, $results); ////$points = CalculatePoints($numberOfTeams); // $event = PlayerEvent::GetPlayerEvent($eventDate, $results, $points, $numberOfTeams); // $eventsPlayedCollection[] = $event; // } // // return $eventsPlayedCollection; // } // Get Collection of all played events. private function GetEventsPlayedComplete($playerId) { $eventsPlayedCompleteCollection = array(); //$result = GetEventsPlayed($playerId); //$query = "SELECT * FROM Players P "; //$query = "SELECT TOUR.StartDate, R.Result, E.NumberOfTeams, E.DivisionID, E.GenderID, E.EventID, D.Division, T.TeamID, Tour.TournamentID "; $query = "SELECT PlayerID, TOUR.StartDate, E.StartDate, Result, NumberOfTeams, DivisionID, E.GenderID, EventID, Division, TeamID, Year(E.StartDate) AS Year "; $query .= "FROM Players P "; $query .= "INNER JOIN Teams T USING (PlayerID) "; $query .= "INNER JOIN Results R USING (TeamID) "; $query .= "INNER JOIN Events E USING (EventID) "; $query .= "INNER JOIN Divisions D USING (DivisionID) "; $query .= "INNER JOIN Tournaments TOUR USING (TournamentID) "; $query .= "WHERE P.PlayerID = $playerId "; //$query .= " ORDER BY Year DESC"; //$query .= " ORDER BY TOUR.StartDate DESC"; $query .= "ORDER BY Year DESC, E.GenderID, E.DivisionID"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $eventDate = $row['StartDate']; $results = $row['Result']; $numberOfTeams = $row['NumberOfTeams']; $divisionId = $row['DivisionID']; $genderId = $row['GenderID']; //$points = CalculatePoints($numberOfTeams, $results); $points = CalculatePoints($genderId, $divisionId, $numberOfTeams, $results); // Additional fields required $eventId = $row['EventID']; $division = $row['Division']; $partner = Player::GetTeamate($row['TeamID'], $playerId); //$genderId = $row['GenderID']; $tournamentId = $row['TournamentID']; // $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $division, $partner, $results, $points, $numberOfTeams, $genderId); //$ratingEarned = CalculateRatingEarned($numberOfTeams, $results); $ratingEarned = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $results); // $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $division, $partner, $results, $points, $numberOfTeams, $genderId, $ratingEarned); $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $divisionId, $partner, $results, $points, $numberOfTeams, $genderId, $ratingEarned); $eventsPlayedCompleteCollection[] = $event; } return $eventsPlayedCompleteCollection; } // TODO: Refactor when we're ready to allow > 2 person teams. private function GetTeamate($teamId, $playerId) { $query = "SELECT PlayerID, FirstName, LastName "; $query .= "FROM Players INNER JOIN Teams USING(PlayerID) "; $query .= "WHERE TeamID = $teamId AND PlayerID <> $playerId"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $partner = Player::GetPlayerBaseData($row['PlayerID'], $row['FirstName'], $row['LastName']); } return $partner; } } ?>[/php]
That is a different method and does not have a bearing on the other. I’ll run through the code again and see if I can work with it.
Originally, The method calls for a column in the database that has the erroneous data. What I am working to do, is extract the proper data without relying on that column.
makes sense… Let me know if you want me to send you any other associated files or search for any other calls in the rest of the code files…
I was uploading the Players.class.php file with the changes in the original file structure…
then reloading these two pages in the browser to see the changes:
-
Overall Player Rankings page: http://phillipvanrooyen.com/ava-table/content/players_13.php
-
Player Detail page:http://phillipvanrooyen.com/ava-table/content/playerDetail.php?player=1041
Any luck with this?