path: root/includes/playerStats.php

<?php

include_once('constants.php');

/**
 * Sets a flag on all solutions which are the high score (first to tie the best length)
 */
function setHighScoreFlags($mapExpireTime)
{
	$mapExpireTime =  mysql_escape_string($mapExpireTime);
	
	$sql1 = "
	UPDATE solutions AS s1
	INNER JOIN mapOfTheDay ON s1.mapID = mapOfTheDay.mapID
	SET s1.isHighScore = 1
	WHERE mapOfTheDay.mapExpireTime < NOW()
	AND mapOfTheDay.mapExpireTime >= '$mapExpireTime'
	AND s1.ID =
	(
		SELECT s2.ID
		FROM (SELECT ID, mapID, moves, dateModified FROM solutions) AS s2
		WHERE s2.mapID = s1.mapID
		ORDER BY moves DESC, dateModified ASC
		LIMIT 1
	)
	";
	
	$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.mapExpireTime >= '$mapExpireTime'
	AND mapOfTheDay.mapExpireTime < NOW()
	AND solutions.moves = maxMoves.moves
	";
	
	mysql_query($sql1);
	mysql_query($sql2);
}

/**
 * Calculates the worth of all maps' champion points from after the given date
 * @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($mapExpireTime)
{
	$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)
	$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.isTiedForHighScore = 0
	) / (
		SELECT GREATEST(COUNT(*)-1, 1)
		FROM solutions
		WHERE solutions.mapID = mapOfTheDay.mapID
	))
	WHERE mapExpireTime >= '$mapExpireTime'
	";
	
	mysql_query($sql);
}

/**
 * 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 $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($mapExpireTime)
{
	$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/
	$sql1 = "
	-- Add the points for tying the map
	UPDATE solutions
	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
	SET solutions.championPointsWorth = mapOfTheDay.championPointsWorth
	WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime'
	AND mapOfTheDay.mapExpireTime < NOW()
	AND mapOfTheDay.mapType <> 5
	AND solutions.isTiedForHighScore
	";

	$sql2 = "
	-- Add the points for doing the weekly maps
	UPDATE solutions
	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
	SET championPointsWorth =
	(
		SELECT IFNULL(".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 (SELECT * FROM solutions) AS usersSolutions ON usersSolutions.mapID = mapOfTheDay.mapID
		WHERE usersSolutions.ID = solutions.ID
	)
	WHERE mapOfTheDay.mapExpireTime >= '$mapExpireTime'
	AND mapOfTheDay.mapExpireTime < NOW()
	AND mapOfTheDay.mapType = 5
	";
	
	$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 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
	//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 clearAllStatsBeforeRecalculation()
{
	$sql1 = "
	UPDATE users
	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($sql1);
	mysql_query($sql2);
	mysql_query($sql3);
}

/**
 * Calculates players' total wins, draws, moves, and mazes, adding from the given date
 */
function addOtherStats($mapExpireTime)
{
	$mapExpireTime =  mysql_escape_string($mapExpireTime);
	
	$sql = "
	-- Add wins, ties, mazes, and moves all at once
	UPDATE users
	INNER JOIN
	(
		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.mapExpireTime >= '$mapExpireTime'
		AND mapOfTheDay.mapExpireTime < NOW()
		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($sql);
}

/**
 * Calculates the worth of the maps for yesterday, and distributes points for them.  Also adds wins and ties to
 * the correct players
 */
function addStatsForYesterdaysMaps()
{
	$today = time();
	addStatsForDateTimeInternal($today);
}

/**
 * Adds stats for maps that end mid-day (namely, weekly maps)
 */
function addStatsForMiddayMaps()
{
	$noonToday = strtotime('noon', time());
	addStatsForDateTimeInternal($noonToday);
}

/**
 * Adds stats for maps that expire after the given date-time.  Should not be called outside of playerStats.php
 */
function addStatsForDateTimeInternal($dateTime)
{
	$dateTimeStr = date('Y-m-d H:i:s', $dateTime);
	setHighScoreFlags($dateTimeStr);
	calculateMapsChampionPointWorth($dateTimeStr);
	addPlayerChampionPointsForMapsOfTheDay($dateTimeStr);
	addOtherStats($dateTimeStr);
}

/**
 * Recalculates all player's total champion points, wins, and ties.
 * Will probably be extremely slow, so should not be called often!
 */
function recalculateStatsForAllPlayers()
{
	clearAllStatsBeforeRecalculation();
	setHighScoreFlags(STATS_EARLIEST_DATE);
	calculateMapsChampionPointWorth(STATS_EARLIEST_DATE);
	addPlayerChampionPointsForMapsOfTheDay(STATS_EARLIEST_DATE);
	//TODO: Champion points for challenges!
	addOtherStats(STATS_EARLIEST_DATE);
}
?>