Added totalWins and totalTies columns to the users table. Also, removed all references to the 'statistics' table - it's not needed anymore.

1 files changed, 271 insertions, 0 deletions

diff --git a/includes/playerStats.php b/includes/playerStats.php
new file mode 100644
index 0000000..b302812
--- /dev/null
+++ b/includes/playerStats.php
@@ -0,0 +1,271 @@
+<?php
+define('CP_EARLIEST_DATE', '2012-01-01');
+define('CP_MIN_WORTH', 10);
+define('CP_MAX_WORTH_SIMPLE', 100);
+define('CP_MAX_WORTH_NORMAL', 150);
+define('CP_MAX_WORTH_COMPLEX', 200);
+define('CP_MAX_WORTH_SPECIAL', 200);
+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.
+ *                  Pass in yesterday's date, for example, to add champion points from yesterday's maps
+ */
+function calculateMapsChampionPointWorth($fromDate)
+{
+	$fromDate =  mysql_escape_string($fromDate);
+	
+	//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)
+	$sql = "
+	UPDATE mapOfTheDay
+	SET championPointsWorth = GREATEST(".CP_MIN_WORTH.",
+	(
+		CASE mapType
+		WHEN 1 THEN ".CP_MAX_WORTH_SIMPLE."
+		WHEN 2 THEN ".CP_MAX_WORTH_NORMAL."
+		WHEN 3 THEN ".CP_MAX_WORTH_COMPLEX."
+		WHEN 4 THEN ".CP_MAX_WORTH_SPECIAL."
+		WHEN 5 THEN ".CP_MAX_WORTH_WEEKLY."
+		ELSE 0
+		END
+	) * (
+		SELECT COUNT(*)
+		FROM solutions
+		WHERE solutions.mapID = mapOfTheDay.mapID
+		AND solutions.moves <
+		(
+			SELECT MAX(moves)
+			FROM solutions AS s2
+			WHERE s2.mapId = mapOfTheDay.mapID
+		)
+	) / (
+		SELECT GREATEST(COUNT(*)-1, 1)
+		FROM solutions
+		WHERE solutions.mapID = mapOfTheDay.mapID
+	))
+	WHERE mapDate >= '$fromDate'
+	";
+	
+	mysql_query($sql);
+}
+
+/**
+ * Adds the champion points for each player for every day after the given date, but before today.
+ * 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
+ */
+function addPlayerChampionPointsForMapsOfTheDay($fromDate)
+{
+	$fromDate =  mysql_escape_string($fromDate);
+	
+	//TODO: How do you know when an ultra-complex map has completed?
+	
+	//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/
+	$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
+	";
+	
+	$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 +
+	(
+		SELECT IFNULL(SUM(".CP_MAX_WORTH_WEEKLY." * 
+			(
+				SELECT COUNT(*)
+				FROM (SELECT * FROM solutions) AS s2
+				WHERE s2.mapID = mapOfTheDay.mapID
+				AND s2.moves < usersSolutions.moves
+			) / (
+				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
+		WHERE usersSolutions.ID = solutions.ID
+		AND mapOfTheDay.mapType = 5
+		AND mapOfTheDay.mapDate >= '$fromDate'
+		AND mapOfTheDay.mapDate < CURDATE()
+	)
+	WHERE solutions.dateModified >= $fromDate;
+	AND mapOfTheDay.mapDate < CURDATE()
+	";
+	
+	$sql4 = "
+	-- Add the points for attempting the map
+	UPDATE solutions
+	SET championPointsWorth = ".CP_POINTS_FOR_ATTEMPTING."
+	WHERE championPointsWorth = 0
+	AND dateModified >= $fromDate
+	AND dateModified < CURDATE()
+	";
+	
+	//TODO: Points for doing challenges too
+	//TODO: Is summing the entire table every time slow?  If so, we should just add the points instead
+	$sql5 = "
+	-- Simply re-sum all the points
+	UPDATE users
+	SET championPoints =
+	(
+		SELECT SUM(championPointsWorth)
+		FROM solutions
+		WHERE solutions.userID = users.ID
+	)
+	";
+
+	mysql_query($sql1);
+	mysql_query($sql2);
+	mysql_query($sql3);
+	mysql_query($sql4);
+	mysql_query($sql5);
+}
+
+/**
+ * Sets all players wins and draws to 0.  Should be done before a total recalculation
+ */
+function clearWinsAndTies()
+{
+	$sql = "
+	UPDATE users
+	SET totalWins = 0, totalTies = 0;
+	";
+
+	mysql_query($sql);
+}
+
+/**
+ * Calculates players' total wins and draws, adding from the given date
+ */
+function addWinsAndTies($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
+	UPDATE users
+	SET totalTies = totalTies +
+	(
+		SELECT COUNT(*)
+		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
+		)
+	);
+	";
+
+	mysql_query($sql1);
+	mysql_query($sql2);
+}
+
+/**
+ * Calculates the worth of the maps for yesterday, and distributes points for them.  Also adds wins and ties to
+ * the correct players
+ */
+function addStatsForYesterdaysMaps()
+{
+	$yesterday = strtotime('-1 day', time());
+	$yesterdayStr = date('Y-m-d', $yesterday);
+	setIsHighScoreFlag($yesterday);
+	calculateMapsChampionPointWorth($yesterdayStr);
+	addPlayerChampionPointsForMapsOfTheDay($yesterdayStr);
+	addWinsAndTies($yesterdayStr);
+}
+
+/**
+ * Recalculates all player's total champion points, wins, and ties.
+ * Will probably be extremely slow, so should not be called often!
+ */
+function recalculateStatsForAllPlayers()
+{
+	setIsHighScoreFlag(CP_EARLIEST_DATE);
+	calculateMapsChampionPointWorth(CP_EARLIEST_DATE);
+	addPlayerChampionPointsForMapsOfTheDay(CP_EARLIEST_DATE);
+	//TODO: Champion points for challenges!
+	clearWinsAndTies();
+	addWinsAndTies(CP_EARLIEST_DATE);
+}
+?>