summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--db updates.sql8
-rw-r--r--includes/datas.php34
-rw-r--r--includes/playerStats.php118
-rw-r--r--pages/achievements.php4
-rw-r--r--pages/memberlist.php4
-rw-r--r--update notes.txt5
6 files changed, 85 insertions, 88 deletions
diff --git a/db updates.sql b/db updates.sql
index e038771..b8b63a2 100644
--- a/db updates.sql
+++ b/db updates.sql
@@ -1,5 +1,9 @@
ALTER TABLE `db428221563`.`mapOfTheDay` DROP INDEX `UniqueTypeDate` ,
-ADD UNIQUE `UniqueTypeDate` ( `mapDate` , `mapType` )
+ADD UNIQUE `UniqueTypeDate` ( `mapDate` , `mapType` );
-ALTER TABLE `mapOfTheDay` ADD `championPointsWorth` INT NOT NULL DEFAULT '0'
+ALTER TABLE `mapOfTheDay` ADD `championPointsWorth` INT NOT NULL DEFAULT '0';
+ALTER TABLE `users` ADD `totalMazes` INT NOT NULL DEFAULT '0',
+ADD `totalMoves` INT NOT NULL DEFAULT '0';
+
+ALTER TABLE `solutions` ADD `isTiedForHighScore` BOOLEAN NOT NULL DEFAULT '0'; \ No newline at end of file
diff --git a/includes/datas.php b/includes/datas.php
index cdaf0c8..4415eb4 100644
--- a/includes/datas.php
+++ b/includes/datas.php
@@ -188,10 +188,9 @@ function getNotified($userID) {
// Usage example: applyCareerAchievement($userID);
// TODO: DEPRECIATED
function applyCareerPathAchievements($userID) {
- $sql = "SELECT
- SUM(solutions.moves) as totalMoves
- FROM `solutions`
- WHERE userID = '$userID'";
+ $sql = "SELECT totalMoves
+ FROM `users`
+ WHERE ID = '$userID'";
$result = mysql_query($sql);
// --------- User hasn't played yet.
@@ -558,18 +557,17 @@ function getAchievementCurrency($userID, $aType) {
// 4, Champion Points!
$r = 0;
$sql = '';
+ //TODO: These can all be combined into a single query...
switch ($aType) {
case 1:
- $sql = "SELECT
- SUM(solutions.moves) as t
- FROM `solutions`
- WHERE userID = '$userID'";
+ $sql = "SELECT totalMoves AS t
+ FROM `users`
+ WHERE `ID` = '$userID'";
break;
case 2:
- $sql = "SELECT
- COUNT(*) as t
- FROM `solutions`
- WHERE userID = '$userID'";
+ $sql = "SELECT totalMazes as t
+ FROM `users`
+ WHERE `ID` = '$userID'";
break;
case 3:
$sql = "SELECT totalTies
@@ -741,21 +739,13 @@ function getMembers($order, $pageNumber = 1, $pageDivide = 50) {
SELECT
users.ID as ID,
users.displayName as display,
- IfNull(
- (SELECT SUM(moves) FROM solutions
- WHERE solutions.userID = users.ID AND
- solutions.dateModified < CURRENT_DATE)
- , 0) AS totalMoves,
+ users.totalMoves,
IfNull(
(SELECT SUM(moves) FROM solutions
WHERE solutions.userID = users.ID AND
solutions.dateModified BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AND CURRENT_DATE)
, 0) AS totalMovesThisWeek,
- IfNull(
- (SELECT COUNT(*) FROM solutions
- WHERE solutions.userID = users.ID AND
- solutions.dateModified < CURRENT_DATE)
- , 0) AS totalMazes,
+ users.totalMazes,
users.totalWins,
users.totalTies,
IfNull(userData.wallColor, '#666666') as wallColor,
diff --git a/includes/playerStats.php b/includes/playerStats.php
index b64fb8d..8cbe7bf 100644
--- a/includes/playerStats.php
+++ b/includes/playerStats.php
@@ -13,11 +13,11 @@ 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)
+function setHighScoreFlags($fromDate)
{
$fromDate = mysql_escape_string($fromDate);
- $sql = "
+ $sql1 = "
UPDATE solutions AS s1
SET s1.isHighScore = 1
WHERE s1.dateModified < CURDATE()
@@ -32,7 +32,24 @@ function setIsHighScoreFlag($fromDate)
)
";
- mysql_query($sql);
+ $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.mapDate >= '$fromDate'
+ AND mapOfTheDay.mapDate < CURDATE()
+ AND solutions.moves = maxMoves.moves
+ ";
+
+ mysql_query($sql1);
+ mysql_query($sql2);
}
/**
@@ -62,12 +79,7 @@ function calculateMapsChampionPointWorth($fromDate)
SELECT COUNT(*)
FROM solutions
WHERE solutions.mapID = mapOfTheDay.mapID
- AND solutions.moves <
- (
- SELECT MAX(moves)
- FROM solutions AS s2
- WHERE s2.mapId = mapOfTheDay.mapID
- )
+ AND solutions.isTiedForHighScore = 0
) / (
SELECT GREATEST(COUNT(*)-1, 1)
FROM solutions
@@ -97,18 +109,12 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
$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
+ AND solutions.isTiedForHighScore
";
$sql2 = "
@@ -180,60 +186,56 @@ function addPlayerChampionPointsForMapsOfTheDay($fromDate)
/**
* Sets all players wins and draws to 0. Should be done before a total recalculation
*/
-function clearWinsAndTies()
+function clearAllStatsBeforeRecalculation()
{
- $sql = "
+ $sql1 = "
UPDATE users
- SET totalWins = 0, totalTies = 0;
+ 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($sql);
+ mysql_query($sql1);
+ mysql_query($sql2);
+ mysql_query($sql3);
}
/**
- * Calculates players' total wins and draws, adding from the given date
+ * Calculates players' total wins, draws, moves, and mazes, adding from the given date
*/
-function addWinsAndTies($fromDate)
+function addOtherStats($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
+ $sql = "
+ -- Add wins, ties, mazes, and moves all at once
UPDATE users
- SET totalTies = totalTies +
+ INNER JOIN
(
- SELECT COUNT(*)
+ 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.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
- )
- );
+ 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($sql1);
- mysql_query($sql2);
+ mysql_query($sql);
}
/**
@@ -244,10 +246,10 @@ function addStatsForYesterdaysMaps()
{
$yesterday = strtotime('-1 day', time());
$yesterdayStr = date('Y-m-d', $yesterday);
- setIsHighScoreFlag($yesterday);
+ setHighScoreFlags($yesterday);
calculateMapsChampionPointWorth($yesterdayStr);
addPlayerChampionPointsForMapsOfTheDay($yesterdayStr);
- addWinsAndTies($yesterdayStr);
+ addOtherStats($yesterdayStr);
}
/**
@@ -256,11 +258,11 @@ function addStatsForYesterdaysMaps()
*/
function recalculateStatsForAllPlayers()
{
- setIsHighScoreFlag(CP_EARLIEST_DATE);
+ clearAllStatsBeforeRecalculation();
+ setHighScoreFlags(CP_EARLIEST_DATE);
calculateMapsChampionPointWorth(CP_EARLIEST_DATE);
addPlayerChampionPointsForMapsOfTheDay(CP_EARLIEST_DATE);
//TODO: Champion points for challenges!
- clearWinsAndTies();
- addWinsAndTies(CP_EARLIEST_DATE);
+ addOtherStats(CP_EARLIEST_DATE);
}
?>
diff --git a/pages/achievements.php b/pages/achievements.php
index 6dbabe7..e3f9e8d 100644
--- a/pages/achievements.php
+++ b/pages/achievements.php
@@ -328,8 +328,8 @@ $sql = "
SELECT
users.displayName,
users.isAdmin,
- SUM(solutions.moves) as totalMoves,
- COUNT(*) as totalSolutions,
+ users.totalMoves,
+ users.totalMazes AS totalSolutions,
IfNull(userData.wallColor, '#666666'),
IfNull(userData.displayColor, '#cccccc'),
userData.wallEmblem
diff --git a/pages/memberlist.php b/pages/memberlist.php
index 2fa2cdc..038b023 100644
--- a/pages/memberlist.php
+++ b/pages/memberlist.php
@@ -110,11 +110,11 @@ exit;
$sql = "SELECT
users.ID,
users.displayName,
- (SELECT SUM(moves) FROM solutions WHERE solutions.userID = users.ID AND solutions.dateModified < CURRENT_DATE) AS totalMoves,
+ users.totalMoves,
(SELECT SUM(moves) FROM solutions
WHERE solutions.userID = users.ID AND
solutions.dateModified BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AND CURRENT_DATE) AS totalMovesThisWeek,
- (SELECT COUNT(*) FROM solutions WHERE solutions.userID = users.ID AND solutions.dateModified < CURRENT_DATE) AS totalMazes,
+ users.totalMazes,
users.totalWins,
users.totalTies,
IfNull(userData.wallColor, '#666666') as wallColor,
diff --git a/update notes.txt b/update notes.txt
index 7c57a30..1ff219c 100644
--- a/update notes.txt
+++ b/update notes.txt
@@ -1,6 +1,7 @@
For next update:
-- Move the new columns from BlueRaja's database over to your other databases. Make sure to get not only the type, but nullable/default as well.
- (We need to do this in a more sensible way in the future... sorry)
+- Move the new columns from BlueRaja's database over to your other databases (see also: db updates.sql. Most, but not all, are in there.
+ I don't think Snap added his stuff there). Make sure to get not only the type, but nullable/default as well.
+ (We need to do this in a more sensible way in the future... sorry)
- Drop the 'statistics' table. It's not needed anymore.
- Delete the lines relating to '$mysqlid' from db.inc.php (which I can't do, since that file is not versioned).
Those lines have been moved to sqlEmbedded.php. The only thing that should be left are the four lines defining the servername/username/etc.
generated by cgit v1.2.3 (git 2.46.0) at 2025-06-28 21:44:48 +0000