Added totalMoves and totalMazes to users; and isTiedForHighScore to solutions (for convenience)

1 files changed, 60 insertions, 58 deletions

diff --git a/includes/playerStats.php b/includes/playerStats.php
index b64fb8d..8cbe7bf 100644
--- a/includes/playerStats.php
+++ b/includes/playerStats.php
@@ -13,11 +13,11 @@ 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)
+function setHighScoreFlags($fromDate)
 {
 	$fromDate =  mysql_escape_string($fromDate);
 	
-	$sql = "
+	$sql1 = "
 	UPDATE solutions AS s1
 	SET s1.isHighScore = 1
 	WHERE s1.dateModified < CURDATE()
@@ -32,7 +32,24 @@ function setIsHighScoreFlag($fromDate)
 	)
 	";
 	
-	mysql_query($sql);
+	$sql2 = "
+	UPDATE solutions
+	INNER JOIN
+	(
+		SELECT mapID, MAX(moves) AS moves
+		FROM solutions AS s2
+		WHERE isHighScore = 1
+		GROUP BY mapID
+	) AS maxMoves ON maxMoves.mapID = solutions.mapID
+	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
+	SET solutions.isTiedForHighScore = 1
+	WHERE mapOfTheDay.mapDate >= '$fromDate'
+	AND mapOfTheDay.mapDate < CURDATE()
+	AND solutions.moves = maxMoves.moves
+	";
+	
+	mysql_query($sql1);
+	mysql_query($sql2);
 }
 
 /**
@@ -62,12 +79,7 @@ function calculateMapsChampionPointWorth($fromDate)
 		SELECT COUNT(*)
 		FROM solutions
 		WHERE solutions.mapID = mapOfTheDay.mapID
-		AND solutions.moves <
-		(
-			SELECT MAX(moves)
-			FROM solutions AS s2
-			WHERE s2.mapId = mapOfTheDay.mapID
-		)
+		AND solutions.isTiedForHighScore = 0
 	) / (
 		SELECT GREATEST(COUNT(*)-1, 1)
 		FROM solutions
@@ -97,18 +109,12 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 	$sql1 = "
 	-- Add the points for tying the map
 	UPDATE solutions
-	INNER JOIN
-	(
-		SELECT mapID, moves
-		FROM solutions AS s2
-		WHERE isHighScore = 1
-	) AS maxMoves ON maxMoves.mapID = solutions.mapID
 	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
 	SET solutions.championPointsWorth = mapOfTheDay.championPointsWorth
 	WHERE mapOfTheDay.mapDate >= '$fromDate'
 	AND mapOfTheDay.mapDate < CURDATE()
 	AND mapOfTheDay.mapType <> 5
-	AND solutions.moves = maxMoves.moves
+	AND solutions.isTiedForHighScore
 	";
 	
 	$sql2 = "
@@ -180,60 +186,56 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 /**
  * Sets all players wins and draws to 0.  Should be done before a total recalculation
  */
-function clearWinsAndTies()
+function clearAllStatsBeforeRecalculation()
 {
-	$sql = "
+	$sql1 = "
 	UPDATE users
-	SET totalWins = 0, totalTies = 0;
+	SET totalWins = 0, totalTies = 0, totalMazes = 0, totalMoves = 0;
+	";
+	
+	$sql2 = "
+	UPDATE solutions
+	SET championPointsWorth = 0, isHighScore = 0, isTiedForHighScore = 0
+	";
+	
+	$sql3 = "
+	UPDATE mapOfTheDay
+	SET challengePointsWorth = 0;
 	";
 
-	mysql_query($sql);
+	mysql_query($sql1);
+	mysql_query($sql2);
+	mysql_query($sql3);
 }
 
 /**
- * Calculates players' total wins and draws, adding from the given date
+ * Calculates players' total wins, draws, moves, and mazes, adding from the given date
  */
-function addWinsAndTies($fromDate)
+function addOtherStats($fromDate)
 {
-	$sql1 = "
-	-- Add the players' latest wins
-	UPDATE users
-	SET totalWins = totalWins +
-	(
-		SELECT COUNT(*)
-		FROM solutions
-		INNER JOIN mapOfTheDay ON solutions.mapID = mapOfTheDay.mapID
-		WHERE solutions.isHighScore = 1
-		AND mapOfTheDay.mapDate >= '$fromDate'
-		AND mapOfTheDay.mapDate < CURDATE()
-		AND solutions.userID = users.ID
-	);
-	";
-	
-	$sql2 = "
-	-- Add the players' latest ties
+	$sql = "
+	-- Add wins, ties, mazes, and moves all at once
 	UPDATE users
-	SET totalTies = totalTies +
+	INNER JOIN
 	(
-		SELECT COUNT(*)
+		SELECT solutions.userID,
+			SUM(isHighScore) AS totalWins,
+			SUM(isTiedForHighScore) AS totalTies,
+			SUM(solutions.moves) AS totalMoves,
+			COUNT(*) AS totalMazes
 		FROM solutions
 		INNER JOIN mapOfTheDay ON solutions.mapID = mapOfTheDay.mapID
 		WHERE mapOfTheDay.mapDate >= '$fromDate'
 		AND mapOfTheDay.mapDate < CURDATE()
-		AND solutions.userID = users.ID
-		AND solutions.moves = 
-		(
-			SELECT moves
-			FROM solutions AS s2
-			WHERE s2.mapID = mapOfTheDay.mapID
-			AND s2.isHighScore = 1
-			LIMIT 1
-		)
-	);
+		GROUP BY solutions.userID
+	) AS newPoints ON users.ID = newPoints.userID
+	SET users.totalWins = users.totalWins + newPoints.totalWins,
+	    users.totalTies = users.totalTies + newPoints.totalTies,
+		users.totalMoves = users.totalMoves + newPoints.totalMoves,
+		users.totalMazes = users.totalMazes + newPoints.totalMazes
 	";
 
-	mysql_query($sql1);
-	mysql_query($sql2);
+	mysql_query($sql);
 }
 
 /**
@@ -244,10 +246,10 @@ function addStatsForYesterdaysMaps()
 {
 	$yesterday = strtotime('-1 day', time());
 	$yesterdayStr = date('Y-m-d', $yesterday);
-	setIsHighScoreFlag($yesterday);
+	setHighScoreFlags($yesterday);
 	calculateMapsChampionPointWorth($yesterdayStr);
 	addPlayerChampionPointsForMapsOfTheDay($yesterdayStr);
-	addWinsAndTies($yesterdayStr);
+	addOtherStats($yesterdayStr);
 }
 
 /**
@@ -256,11 +258,11 @@ function addStatsForYesterdaysMaps()
  */
 function recalculateStatsForAllPlayers()
 {
-	setIsHighScoreFlag(CP_EARLIEST_DATE);
+	clearAllStatsBeforeRecalculation();
+	setHighScoreFlags(CP_EARLIEST_DATE);
 	calculateMapsChampionPointWorth(CP_EARLIEST_DATE);
 	addPlayerChampionPointsForMapsOfTheDay(CP_EARLIEST_DATE);
 	//TODO: Champion points for challenges!
-	clearWinsAndTies();
-	addWinsAndTies(CP_EARLIEST_DATE);
+	addOtherStats(CP_EARLIEST_DATE);
 }
 ?>