Using mapExpireTime for basically all playerStats now - champion points for weekly map should be about 99% there, just one more thing to do...

1 files changed, 52 insertions, 53 deletions

diff --git a/includes/playerStats.php b/includes/playerStats.php
index be0b649..6cf42fe 100644
--- a/includes/playerStats.php
+++ b/includes/playerStats.php
@@ -5,15 +5,16 @@ include_once('constants.php');
 /**
  * Sets a flag on all solutions which are the high score (first to tie the best length)
  */
-function setHighScoreFlags($fromDate)
+function setHighScoreFlags($mapExpireTime)
 {
-	$fromDate =  mysql_escape_string($fromDate);
+	$mapExpireTime =  mysql_escape_string($mapExpireTime);
 	
 	$sql1 = "
 	UPDATE solutions AS s1
+	INNER JOIN mapOfTheDay ON s1.mapID = mapOfTheDay.mapID
 	SET s1.isHighScore = 1
-	WHERE s1.dateModified < CURDATE()
-	AND s1.dateModified >= '$fromDate'
+	WHERE mapOfTheDay.mapExpireTime < NOW()
+	AND mapOfTheDay.mapExpireTime >= '$mapExpireTime'
 	AND s1.ID =
 	(
 		SELECT s2.ID
@@ -35,8 +36,8 @@ function setHighScoreFlags($fromDate)
 	) 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()
+	WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime'
+	AND mapOfTheDay.mapExpireTime < NOW()
 	AND solutions.moves = maxMoves.moves
 	";
 	
@@ -46,12 +47,12 @@ function setHighScoreFlags($fromDate)
 
 /**
  * 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.
- *                  Pass in yesterday's date, for example, to add champion points from yesterday's maps
+ * @param $mapExpireTime The earliest mapExpireTime to count from.  Use SQL date-string format.
+ *                  Pass in today's date, for example, for all maps that expired at midnight today
  */
-function calculateMapsChampionPointWorth($fromDate)
+function calculateMapsChampionPointWorth($mapExpireTime)
 {
-	$fromDate =  mysql_escape_string($fromDate);
+	$mapExpireTime =  mysql_escape_string($mapExpireTime);
 	
 	//We take the worth of the map, multiply by the number of people who tied the high score, then divide by the
 	//total number of people who attempted the map (minus 1)
@@ -77,24 +78,22 @@ function calculateMapsChampionPointWorth($fromDate)
 		FROM solutions
 		WHERE solutions.mapID = mapOfTheDay.mapID
 	))
-	WHERE mapDate >= '$fromDate'
+	WHERE mapExpireTime >= '$mapExpireTime'
 	";
 	
 	mysql_query($sql);
 }
 
 /**
- * Adds the champion points for each player for every day after the given date, but before today.
+ * Adds the champion points for each player for every map that expired on or after the given date.
  * Thus, if you're using this to add points from yesterday's maps, this should be done
  * after the date has changed.
- * @param $fromDate The earliest date to count from.  SQL date-string format.
- *                  Pass in yesterday's date, for example, to add champion points from yesterday's maps
+ * @param $mapExpireTime The earliest mapExpireTime to count from.  Use SQL date-string format.
+ *                  Pass in today's date, for example, for all maps that expired at midnight today
  */
-function addPlayerChampionPointsForMapsOfTheDay($fromDate)
+function addPlayerChampionPointsForMapsOfTheDay($mapExpireTime)
 {
-	$fromDate =  mysql_escape_string($fromDate);
-	
-	//TODO: How do you know when an ultra-complex map has completed?
+	$mapExpireTime =  mysql_escape_string($mapExpireTime);
 	
 	//Weird syntax of these queries is necessary due to a bug in MySQL with updating a table and selecting a table
 	//in the same query.  See http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/
@@ -103,28 +102,19 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 	UPDATE solutions
 	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
 	SET solutions.championPointsWorth = mapOfTheDay.championPointsWorth
-	WHERE mapOfTheDay.mapDate >= '$fromDate'
-	AND mapOfTheDay.mapDate < CURDATE()
+	WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime'
+	AND mapOfTheDay.mapExpireTime < NOW()
 	AND mapOfTheDay.mapType <> 5
 	AND solutions.isTiedForHighScore
 	";
-	
+
 	$sql2 = "
-	-- 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.isHighScore = 1
-	AND solutions.dateModified >= $fromDate
-	AND solutions.dateModified < CURDATE()
-	";
-	
-	//TODO: This doesn't work right, weekly maps last more than a day...
-	$sql3 = "
 	-- Add the points for doing the weekly maps
 	UPDATE solutions
-	SET championPointsWorth = championPointsWorth +
+	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
+	SET championPointsWorth =
 	(
-		SELECT IFNULL(SUM(".CP_MAX_WORTH_WEEKLY." * 
+		SELECT IFNULL(".CP_MAX_WORTH_WEEKLY." * 
 			(
 				SELECT COUNT(*)
 				FROM (SELECT * FROM solutions) AS s2
@@ -134,25 +124,33 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
 				SELECT GREATEST(COUNT(*)-1, 1)
 				FROM (SELECT * FROM solutions) AS s2
 				WHERE s2.mapID = mapOfTheDay.mapID
-			) + ".CP_MIN_WORTH_WEEKLY."), 0)
-		FROM mapOfTheDay
-		INNER JOIN (SELECT * FROM solutions) AS usersSolutions ON usersSolutions.mapID = mapOfTheDay.mapID
+			) + ".CP_MIN_WORTH_WEEKLY.", 0)
+		FROM (SELECT * FROM solutions) AS usersSolutions ON usersSolutions.mapID = mapOfTheDay.mapID
 		WHERE usersSolutions.ID = solutions.ID
-		AND mapOfTheDay.mapType = 5
-		AND mapOfTheDay.mapDate >= '$fromDate'
-		AND mapOfTheDay.mapDate < CURDATE()
 	)
-	WHERE solutions.dateModified >= $fromDate;
-	AND solutions.dateModified < CURDATE()
+	WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime'
+	AND mapOfTheDay.mapExpireTime < NOW()
+	AND mapOfTheDay.mapType = 5
 	";
 	
-	$sql4 = "
+	$sql3 = "
 	-- Add the points for attempting the map
 	UPDATE solutions
+	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
 	SET championPointsWorth = ".CP_POINTS_FOR_ATTEMPTING."
 	WHERE championPointsWorth = 0
-	AND dateModified >= $fromDate
-	AND dateModified < CURDATE()
+	AND mapOfTheDay.mapExpireTime >= '$mapExpireTime'
+	AND mapOfTheDay.mapExpireTime < NOW()
+	";
+	
+	$sql4 = "
+	-- Add the extra for being the first to tie the map
+	UPDATE solutions
+	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
+	SET solutions.championPointsWorth = solutions.championPointsWorth * (1 + ".CP_EXTRA_PERCENT_FOR_FIRST.")
+	WHERE solutions.isHighScore = 1
+	AND mapOfTheDay.mapExpireTime >= '$mapExpireTime'
+	AND mapOfTheDay.mapExpireTime < NOW()
 	";
 	
 	//TODO: Points for doing challenges too
@@ -203,8 +201,10 @@ function clearAllStatsBeforeRecalculation()
 /**
  * Calculates players' total wins, draws, moves, and mazes, adding from the given date
  */
-function addOtherStats($fromDate)
+function addOtherStats($mapExpireTime)
 {
+	$mapExpireTime =  mysql_escape_string($mapExpireTime);
+	
 	$sql = "
 	-- Add wins, ties, mazes, and moves all at once
 	UPDATE users
@@ -217,8 +217,8 @@ function addOtherStats($fromDate)
 			COUNT(*) AS totalMazes
 		FROM solutions
 		INNER JOIN mapOfTheDay ON solutions.mapID = mapOfTheDay.mapID
-		WHERE mapOfTheDay.mapDate >= '$fromDate'
-		AND mapOfTheDay.mapDate < CURDATE()
+		WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime'
+		AND mapOfTheDay.mapExpireTime < NOW()
 		GROUP BY solutions.userID
 	) AS newPoints ON users.ID = newPoints.userID
 	SET users.totalWins = users.totalWins + newPoints.totalWins,
@@ -236,12 +236,11 @@ function addOtherStats($fromDate)
  */
 function addStatsForYesterdaysMaps()
 {
-	$yesterday = strtotime('-1 day', time());
-	$yesterdayStr = date('Y-m-d', $yesterday);
-	setHighScoreFlags($yesterday);
-	calculateMapsChampionPointWorth($yesterdayStr);
-	addPlayerChampionPointsForMapsOfTheDay($yesterdayStr);
-	addOtherStats($yesterdayStr);
+	$todayStr = date('Y-m-d', time());
+	setHighScoreFlags($todayStr);
+	calculateMapsChampionPointWorth($todayStr);
+	addPlayerChampionPointsForMapsOfTheDay($todayStr);
+	addOtherStats($todayStr);
 }
 
 /**