path: root/includes/playerStats.php

<?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);
}
?>