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($mapExpireStartRange, $mapExpireEndRange = "NOW()")
{
	$mapExpireStartRange =  quoteSQLDateString($mapExpireStartRange);
	$mapExpireEndRange =  quoteSQLDateString($mapExpireEndRange);
	
	$sql1 = "
	UPDATE solutions
	INNER JOIN
	(
		SELECT a1.id FROM solutions AS a1
		INNER JOIN mapOfTheDay ON a1.mapID = mapOfTheDay.mapID
		WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange
		AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
		AND NOT EXISTS
		(
			-- Only select rows which have highest score for map, or tied for highest with earliest date, or tied for date too with earliest ID
			SELECT 0 FROM solutions AS a2
			WHERE a2.mapID = a1.mapID AND 
				 (a2.moves > a1.moves OR (a2.moves = a1.moves AND (a2.dateModified < a1.dateModified OR (a2.dateModified = a1.dateModified AND a2.ID > a1.ID))))
		)
	) tmp ON tmp.id = solutions.id
	SET solutions.isHighScore = 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 AND solutions.moves = maxMoves.moves
	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
	SET solutions.isTiedForHighScore = 1
	WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange
	AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
	";
	
	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($mapExpireStartRange, $mapExpireEndRange = "NOW()")
{
	$mapExpireStartRange =  quoteSQLDateString($mapExpireStartRange);
	$mapExpireEndRange =  quoteSQLDateString($mapExpireEndRange);
	
	//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 >= $mapExpireStartRange
	AND mapExpireTime <= $mapExpireEndRange
	";
	
	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($mapExpireStartRange, $mapExpireEndRange = "NOW()")
{
	$mapExpireStartRange =  quoteSQLDateString($mapExpireStartRange);
	$mapExpireEndRange =  quoteSQLDateString($mapExpireEndRange);
	
	//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 >= $mapExpireStartRange
	AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
	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 solutions.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
		WHERE usersSolutions.ID = solutions.ID
	)
	WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange
	AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
	AND mapOfTheDay.mapType = 5
	";
	
	$sql3 = "
	-- Add the points for attempting the map
	UPDATE solutions
	INNER JOIN mapOfTheDay ON mapOfTheDay.mapID = solutions.mapID
	SET solutions.championPointsWorth = ".CP_POINTS_FOR_ATTEMPTING."
	WHERE solutions.championPointsWorth = 0
	AND mapOfTheDay.mapExpireTime >= $mapExpireStartRange
	AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
	";
	
	$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 >= $mapExpireStartRange
	AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
	";
	
	//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
	INNER JOIN
	(
		SELECT SUM(solutions.championPointsWorth) AS championPointsWorth, solutions.userID
		FROM solutions
		INNER JOIN mapOfTheDay ON solutions.mapID = mapOfTheDay.mapID
		WHERE mapOfTheDay.mapExpireTime >= $mapExpireStartRange
		AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
	    GROUP BY solutions.userID
	) AS newPointsEarned ON users.ID = newPointsEarned.userID
	SET users.championPoints = users.championPoints + newPointsEarned.championPointsWorth
	";

	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($mapExpireStartRange, $mapExpireEndRange = "NOW()")
{
	$mapExpireStartRange =  quoteSQLDateString($mapExpireStartRange);
	$mapExpireEndRange =  quoteSQLDateString($mapExpireEndRange);
	
	$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 >= $mapExpireStartRange
		AND mapOfTheDay.mapExpireTime <= $mapExpireEndRange
		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);
}

/**
 * Adds quotes around an SQL date string and escapes it, if necessary
 */
function quoteSQLDateString($dateString)
{
	if($dateString == "NOW()")
	{
		return $dateString;
	}
	return "'" . mysql_escape_string($dateString) . "'";
}

/**
 * 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 = strtotime('today');
	addStatsForDateTimeInternal($today);
}

/**
 * Adds stats for maps that end mid-day (namely, weekly maps)
 */
function addStatsForMiddayMaps()
{
	$noonToday = strtotime('noon');
	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();
	
	$startDate = new DateTime(STATS_EARLIEST_DATE);
	$currentEnd = $startDate;
	$currentEndStr = "";
	$now = new DateTime("now");
	
	while($currentEndStr != "NOW()")
	{
		$currentStart = new DateTime($currentEnd->format("Y-m-d"));
		$currentStart->add(new DateInterval("PT1S")); //Add one second to start time, since it's inclusive
		
		$currentEnd = new DateTime($currentEnd->format("Y-m-d"));
		$currentEnd->add(new DateInterval("P1M")); //Run queries for one month at a time
		
		$currentStartStr = $currentStart->format("Y-m-d H:i:s");
		$currentEndStr = $currentEnd->format("Y-m-d H:i:s");
		
		if($currentEnd > $now)
		{
			$currentEndStr = "NOW()";
		}
		
		setHighScoreFlags($currentStartStr, $currentEndStr);
		calculateMapsChampionPointWorth($currentStartStr, $currentEndStr);
		addPlayerChampionPointsForMapsOfTheDay($currentStartStr, $currentEndStr);
		//TODO: Champion points for challenges!
		addOtherStats($currentStartStr, $currentEndStr);
	}
}
?>