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

6 files changed, 85 insertions, 88 deletions

diff --git a/db updates.sql b/db updates.sql
index e038771..b8b63a2 100644
--- a/db updates.sql
+++ b/db updates.sql
@@ -1,5 +1,9 @@
 ALTER TABLE `db428221563`.`mapOfTheDay` DROP INDEX `UniqueTypeDate` ,
-ADD UNIQUE `UniqueTypeDate` ( `mapDate` , `mapType` ) 
+ADD UNIQUE `UniqueTypeDate` ( `mapDate` , `mapType` );
 
-ALTER TABLE `mapOfTheDay` ADD `championPointsWorth` INT NOT NULL DEFAULT '0'
+ALTER TABLE `mapOfTheDay` ADD `championPointsWorth` INT NOT NULL DEFAULT '0';
 
+ALTER TABLE `users` ADD `totalMazes` INT NOT NULL DEFAULT '0',
+ADD `totalMoves` INT NOT NULL DEFAULT '0';
+
+ALTER TABLE `solutions` ADD `isTiedForHighScore` BOOLEAN NOT NULL DEFAULT '0';
\ No newline at end of file
diff --git a/includes/datas.php b/includes/datas.php
index cdaf0c8..4415eb4 100644
--- a/includes/datas.php
+++ b/includes/datas.php
@@ -188,10 +188,9 @@ function getNotified($userID) {
 // Usage example: applyCareerAchievement($userID);
 // TODO: DEPRECIATED
 function applyCareerPathAchievements($userID) {
-	$sql = "SELECT 
-	SUM(solutions.moves) as totalMoves
-	FROM `solutions`
-	WHERE userID = '$userID'";
+	$sql = "SELECT totalMoves
+	FROM `users`
+	WHERE ID = '$userID'";
 	$result = mysql_query($sql);
 	
 	// --------- User hasn't played yet.
@@ -558,18 +557,17 @@ function getAchievementCurrency($userID, $aType) {
 // 4, Champion Points!
 	$r = 0;
 	$sql = '';
+	//TODO: These can all be combined into a single query...
 	switch ($aType) {
 		case 1:
-			$sql = "SELECT 
-			SUM(solutions.moves) as t
-			FROM `solutions`
-			WHERE userID = '$userID'";
+			$sql = "SELECT totalMoves AS t
+			FROM `users`
+			WHERE `ID` = '$userID'";
 		break;
 		case 2:
-			$sql = "SELECT 
-			COUNT(*) as t
-			FROM `solutions`
-			WHERE userID = '$userID'";
+			$sql = "SELECT totalMazes as t
+			FROM `users`
+			WHERE `ID` = '$userID'";
 		break;
 		case 3:
 			$sql = "SELECT totalTies
@@ -741,21 +739,13 @@ function getMembers($order, $pageNumber = 1, $pageDivide = 50) {
 	SELECT
 		users.ID as ID,
 		users.displayName as display,
-		IfNull(
-			(SELECT SUM(moves) FROM solutions 
-				WHERE solutions.userID = users.ID AND 
-				solutions.dateModified < CURRENT_DATE)
-			, 0) AS totalMoves,
+		users.totalMoves,
 		IfNull(
 			(SELECT SUM(moves) FROM solutions
 				WHERE solutions.userID = users.ID AND
 				solutions.dateModified BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AND CURRENT_DATE)
 			, 0) AS totalMovesThisWeek,
-		IfNull(
-			(SELECT COUNT(*) FROM solutions 
-			WHERE solutions.userID = users.ID AND 
-			solutions.dateModified < CURRENT_DATE) 
-			, 0) AS totalMazes,
+		users.totalMazes,
 		users.totalWins,
 		users.totalTies,
 		IfNull(userData.wallColor, '#666666') as wallColor,
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);
 }
 ?>
diff --git a/pages/achievements.php b/pages/achievements.php
index 6dbabe7..e3f9e8d 100644
--- a/pages/achievements.php
+++ b/pages/achievements.php
@@ -328,8 +328,8 @@ $sql = "
 SELECT 

 	users.displayName, 

 	users.isAdmin,

-	SUM(solutions.moves) as totalMoves,

-	COUNT(*) as totalSolutions,

+	users.totalMoves,

+	users.totalMazes AS totalSolutions,

 	IfNull(userData.wallColor, '#666666'),

 	IfNull(userData.displayColor, '#cccccc'),

 	userData.wallEmblem

diff --git a/pages/memberlist.php b/pages/memberlist.php
index 2fa2cdc..038b023 100644
--- a/pages/memberlist.php
+++ b/pages/memberlist.php
@@ -110,11 +110,11 @@ exit;
 $sql = "SELECT

   users.ID,

   users.displayName,

-  (SELECT SUM(moves) FROM solutions WHERE solutions.userID = users.ID AND solutions.dateModified < CURRENT_DATE) AS totalMoves,

+  users.totalMoves,

   (SELECT SUM(moves) FROM solutions

 		WHERE solutions.userID = users.ID AND

 		solutions.dateModified BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AND CURRENT_DATE) AS totalMovesThisWeek,

-  (SELECT COUNT(*) FROM solutions WHERE solutions.userID = users.ID AND solutions.dateModified < CURRENT_DATE) AS totalMazes,

+  users.totalMazes,

   users.totalWins,

   users.totalTies,

   IfNull(userData.wallColor, '#666666') as wallColor,

diff --git a/update notes.txt b/update notes.txt
index 7c57a30..1ff219c 100644
--- a/update notes.txt
+++ b/update notes.txt
@@ -1,6 +1,7 @@
 For next update:
-- Move the new columns from BlueRaja's database over to your other databases.  Make sure to get not only the type, but nullable/default as well.
-      (We need to do this in a more sensible way in the future... sorry)
+- Move the new columns from BlueRaja's database over to your other databases (see also: db updates.sql.  Most, but not all, are in there.
+    I don't think Snap added his stuff there).  Make sure to get not only the type, but nullable/default as well.
+    (We need to do this in a more sensible way in the future... sorry)
 - Drop the 'statistics' table.  It's not needed anymore.
 - Delete the lines relating to '$mysqlid' from db.inc.php (which I can't do, since that file is not versioned).
   Those lines have been moved to sqlEmbedded.php.  The only thing that should be left are the four lines defining the servername/username/etc.