Made some calculations significantly simpler by adding a 'isHighScore' column to the solutions table.

1 files changed, 39 insertions, 16 deletions

diff --git a/includes/championPoints.php b/includes/championPoints.php
index b99fde3..28b2b78 100644
--- a/includes/championPoints.php
+++ b/includes/championPoints.php
@@ -9,7 +9,37 @@ define('CP_MAX_WORTH_WEEKLY', 350);
 define('CP_MIN_WORTH_WEEKLY', 50);
 define('CP_POINTS_FOR_ATTEMPTING', 5);
 define('CP_EXTRA_PERCENT_FOR_FIRST', 0.05);
+
+/**
+ * Sets a flag on all solutions which are the high score (first to tie the best length)
+ */
+function setIsHighScoreFlag($fromDate)
+{
+	$fromDate =  mysql_escape_string($fromDate);
+	
+	$sql = "
+	UPDATE solutions
+	SET solutions.isHighScore = 1
+	WHERE solutions.ID IN
+	(
+		SELECT s2.ID
+		FROM (SELECT * FROM solutions) AS s2
+		INNER JOIN mapOfTheDay ON s2.mapID = mapOfTheDay.mapID
+		WHERE s2.ID =
+		(
+			SELECT s3.ID
+			FROM (SELECT * FROM solutions) AS s3
+			WHERE s3.mapID = s2.mapID
+			ORDER BY moves DESC, dateModified ASC
+			LIMIT 1
+		)
+		AND mapOfTheDay.mapDate >= '$fromDate'
+	)
+	";
 	
+	mysql_query($sql);
+}
+
 /**
  * Calculates the worth of all maps' champion points from after the given date
  * @param $fromDate The earliest date to count from.  Use SQL date-string format.
@@ -74,9 +104,9 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 	UPDATE solutions
 	INNER JOIN
 	(
-		SELECT mapID, MAX(moves) AS moves
+		SELECT mapID, moves
 		FROM solutions AS s2
-		GROUP BY mapID
+		WHERE isHighScore = 1
 	) AS maxMoves ON maxMoves.mapID = solutions.mapID
 	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
 	SET solutions.championPointsWorth = mapOfTheDay.championPointsWorth
@@ -89,21 +119,11 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 	-- Add the extra for being the first to tie the map
 	UPDATE solutions
 	SET solutions.championPointsWorth = solutions.championPointsWorth * (1 + ".CP_EXTRA_PERCENT_FOR_FIRST.")
-	WHERE solutions.ID IN
-	(
-		SELECT s2.ID
-		FROM (SELECT * FROM solutions) AS s2
-		INNER JOIN mapOfTheDay ON s2.mapID = mapOfTheDay.mapID
-		WHERE s2.moves =
-		(
-			SELECT MAX(moves)
-			FROM (SELECT * FROM solutions) AS s3
-			WHERE s3.mapID = mapOfTheDay.mapID
-		)
-		AND mapOfTheDay.mapDate >= '$fromDate'
-	)
+	WHERE solutions.isHighScore = 1
+	AND solutions.dateModified >= $fromDate
 	";
 	
+	//TODO: This doesn't work right, weekly maps last more than a day...
 	$sql3 = "
 	-- Add the points for doing the weekly maps
 	UPDATE solutions
@@ -125,7 +145,8 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 		WHERE usersSolutions.ID = solutions.ID
 		AND mapOfTheDay.mapType = 5
 		AND mapOfTheDay.mapDate >= '$fromDate'
-	);
+	)
+	WHERE solutions.dateModified >= $fromDate;
 	";
 	
 	$sql4 = "
@@ -164,6 +185,7 @@ function addChampionPointsForYesterdaysMaps()
 {
 	$yesterday = strtotime('-1 day', time());
 	$yesterdayStr = date('Y-m-d', $yesterday);
+	setIsHighScoreFlag($yesterday);
 	calculateMapsChampionPointWorth($yesterdayStr);
 	addPlayerChampionPointsForMapsOfTheDay($yesterdayStr);
 }
@@ -174,6 +196,7 @@ function addChampionPointsForYesterdaysMaps()
  */
 function recalculateAllPlayersChampionPoints()
 {
+	setIsHighScoreFlag(CP_EARLIEST_DATE);
 	calculateMapsChampionPointWorth(CP_EARLIEST_DATE);
 	addPlayerChampionPointsForMapsOfTheDay(CP_EARLIEST_DATE);
 	//TODO: Champion points for challenges!