1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
<?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);
/**
* 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, MAX(moves) AS moves
FROM solutions AS s2
GROUP BY mapID
) 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.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.ID IN
(
SELECT s2.ID
FROM (SELECT * FROM solutions) AS s2
INNER JOIN mapOfTheDay ON s2.mapID = mapOfTheDay.mapID
WHERE s2.moves =
(
SELECT MAX(moves)
FROM (SELECT * FROM solutions) AS s3
WHERE s3.mapID = mapOfTheDay.mapID
)
AND mapOfTheDay.mapDate >= '$fromDate'
)
";
$sql3 = "
-- Add the points for doing the weekly maps
UPDATE solutions
SET championPointsWorth =
(
SELECT 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.")
FROM mapOfTheDay
INNER JOIN (SELECT * FROM solutions) AS usersSolutions ON usersSolutions.mapID = mapOfTheDay.mapID
WHERE usersSolutions.ID = solutions.ID
AND mapOfTheDay.mapType = 5
);
";
$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);
}
/**
* Calculates the worth of the maps for yesterday, and distributes points for them
*/
function addChampionPointsForYesterdaysMaps()
{
$yesterday = strtotime('-1 day', time());
$yesterdayStr = date('Y-m-d', $yesterday);
calculateMapsChampionPointWorth($yesterdayStr);
addPlayerChampionPointsForMapsOfTheDay($yesterdayStr);
}
/**
* Recalculates all player's total champion points.
* Will probably be extremely slow, so should not be called often!
*/
function recalculateAllPlayersChampionPoints()
{
calculateMapsChampionPointWorth(CP_EARLIEST_DATE);
addPlayerChampionPointsForMapsOfTheDay(CP_EARLIEST_DATE);
//TODO: Champion points for challenges!
}
?>
|
