1
|
|
|
<?php |
2
|
|
|
$defflip = (!cfip()) ? exit(header('HTTP/1.1 401 Unauthorized')) : 1; |
3
|
|
|
|
4
|
|
|
|
5
|
|
|
/* |
6
|
|
|
* We give access to plenty of statistics through this class |
7
|
|
|
* Statistics should be non-intrusive and not change any |
8
|
|
|
* rows in our database to ensure data integrity for the backend |
9
|
|
|
**/ |
10
|
|
|
class Statistics extends Base { |
11
|
|
|
protected $table = 'statistics_shares'; |
12
|
|
|
protected $table_user_stats = 'statistics_users'; |
13
|
|
|
private $getcache = true; |
14
|
|
|
|
15
|
|
|
// Disable fetching values from cache |
16
|
|
|
public function setGetCache($set=false) { |
17
|
|
|
$this->getcache = $set; |
18
|
|
|
} |
19
|
|
|
public function getGetCache() { |
20
|
|
|
return $this->getcache; |
21
|
|
|
} |
22
|
|
|
public function getAllUserMiningStats() { |
23
|
|
|
return $this->allUserMiningStats; |
|
|
|
|
24
|
|
|
} |
25
|
|
|
public function getUserStatsTableName() { |
26
|
|
|
return $this->table_user_stats; |
27
|
|
|
} |
28
|
|
|
|
29
|
|
|
/** |
30
|
|
|
* Get our first block found |
31
|
|
|
* |
32
|
|
|
**/ |
33
|
|
|
public function getFirstBlockFound() { |
34
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
35
|
|
|
if ($data = $this->memcache->get(__FUNCTION__)) return $data; |
|
|
|
|
36
|
|
|
$stmt = $this->mysqli->prepare(" |
37
|
|
|
SELECT IFNULL(MIN(time), 0) AS time FROM " . $this->block->getTableName()); |
|
|
|
|
38
|
|
|
if ($this->checkStmt($stmt) && $stmt->execute() && $result = $stmt->get_result()) |
39
|
|
|
return $result->fetch_object()->time; |
40
|
|
|
return false; |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* Fetch last found blocks by time |
45
|
|
|
**/ |
46
|
|
|
function getLastBlocksbyTime() { |
|
|
|
|
47
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
48
|
|
|
if ($data = $this->memcache->get(__FUNCTION__)) return $data; |
49
|
|
|
$stmt = $this->mysqli->prepare(" |
50
|
|
|
SELECT |
51
|
|
|
COUNT(id) AS Total, |
52
|
|
|
IFNULL(SUM(IF(confirmations > 0, 1, 0)), 0) AS TotalValid, |
53
|
|
|
IFNULL(SUM(IF(confirmations = -1, 1, 0)), 0) AS TotalOrphan, |
54
|
|
|
IFNULL(SUM(IF(confirmations > 0, difficulty, 0)), 0) AS TotalDifficulty, |
55
|
|
|
IFNULL(SUM(IF(confirmations > -1, shares, 0)), 0) AS TotalShares, |
56
|
|
|
IFNULL(SUM(IF(confirmations > -1, amount, 0)), 0) AS TotalAmount, |
57
|
|
|
IFNULL(SUM(IF(FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 3600 SECOND), 1, 0)), 0) AS 1HourTotal, |
58
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 3600 SECOND), 1, 0)), 0) AS 1HourValid, |
59
|
|
|
IFNULL(SUM(IF(confirmations = -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 3600 SECOND), 1, 0)), 0) AS 1HourOrphan, |
60
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 3600 SECOND), difficulty, 0)), 0) AS 1HourDifficulty, |
61
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 3600 SECOND), shares, 0)), 0) AS 1HourShares, |
62
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 3600 SECOND), amount, 0)), 0) AS 1HourAmount, |
63
|
|
|
IFNULL(SUM(IF(FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 86400 SECOND), 1, 0)), 0) AS 24HourTotal, |
64
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 86400 SECOND), 1, 0)), 0) AS 24HourValid, |
65
|
|
|
IFNULL(SUM(IF(confirmations = -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 86400 SECOND), 1, 0)), 0) AS 24HourOrphan, |
66
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 86400 SECOND), difficulty, 0)), 0) AS 24HourDifficulty, |
67
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 86400 SECOND), shares, 0)), 0) AS 24HourShares, |
68
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 86400 SECOND), amount, 0)), 0) AS 24HourAmount, |
69
|
|
|
IFNULL(SUM(IF(FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 604800 SECOND), 1, 0)), 0) AS 7DaysTotal, |
70
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 604800 SECOND), 1, 0)), 0) AS 7DaysValid, |
71
|
|
|
IFNULL(SUM(IF(confirmations = -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 604800 SECOND), 1, 0)), 0) AS 7DaysOrphan, |
72
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 604800 SECOND), difficulty, 0)), 0) AS 7DaysDifficulty, |
73
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 604800 SECOND), shares, 0)), 0) AS 7DaysShares, |
74
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 604800 SECOND), amount, 0)), 0) AS 7DaysAmount, |
75
|
|
|
IFNULL(SUM(IF(FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 2419200 SECOND), 1, 0)), 0) AS 4WeeksTotal, |
76
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 2419200 SECOND), 1, 0)), 0) AS 4WeeksValid, |
77
|
|
|
IFNULL(SUM(IF(confirmations = -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 2419200 SECOND), 1, 0)), 0) AS 4WeeksOrphan, |
78
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 2419200 SECOND), difficulty, 0)), 0) AS 4WeeksDifficulty, |
79
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 2419200 SECOND), shares, 0)), 0) AS 4WeeksShares, |
80
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 2419200 SECOND), amount, 0)), 0) AS 4WeeksAmount, |
81
|
|
|
IFNULL(SUM(IF(FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 29030400 SECOND), 1, 0)), 0) AS 12MonthTotal, |
82
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 29030400 SECOND), 1, 0)), 0) AS 12MonthValid, |
83
|
|
|
IFNULL(SUM(IF(confirmations = -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 29030400 SECOND), 1, 0)), 0) AS 12MonthOrphan, |
84
|
|
|
IFNULL(SUM(IF(confirmations > 0 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 29030400 SECOND), difficulty, 0)), 0) AS 12MonthDifficulty, |
85
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 29030400 SECOND), shares, 0)), 0) AS 12MonthShares, |
86
|
|
|
IFNULL(SUM(IF(confirmations > -1 AND FROM_UNIXTIME(time) >= DATE_SUB(now(), INTERVAL 29030400 SECOND), amount, 0)), 0) AS 12MonthAmount |
87
|
|
|
FROM " . $this->block->getTableName()); |
88
|
|
|
if ($this->checkStmt($stmt) && $stmt->execute() && $result = $stmt->get_result()) { |
89
|
|
|
$aData = $result->fetch_assoc(); |
90
|
|
|
$aData['TotalEstimatedShares'] = $this->coin->calcEstaimtedShares($aData['TotalDifficulty']); |
|
|
|
|
91
|
|
|
$aData['1HourEstimatedShares'] = $this->coin->calcEstaimtedShares($aData['1HourDifficulty']); |
92
|
|
|
$aData['24HourEstimatedShares'] = $this->coin->calcEstaimtedShares($aData['24HourDifficulty']); |
93
|
|
|
$aData['7DaysEstimatedShares'] = $this->coin->calcEstaimtedShares($aData['7DaysDifficulty']); |
94
|
|
|
$aData['4WeeksEstimatedShares'] = $this->coin->calcEstaimtedShares($aData['4WeeksDifficulty']); |
95
|
|
|
$aData['12MonthEstimatedShares'] = $this->coin->calcEstaimtedShares($aData['12MonthDifficulty']); |
96
|
|
|
return $this->memcache->setCache(__FUNCTION__, $aData); |
97
|
|
|
} |
98
|
|
|
return $this->sqlError(); |
99
|
|
|
} |
100
|
|
|
|
101
|
|
|
/** |
102
|
|
|
* Get our last $limit blocks found |
103
|
|
|
* @param limit int Last limit blocks |
104
|
|
|
* @return array |
105
|
|
|
**/ |
106
|
|
View Code Duplication |
public function getBlocksFound($limit=10) { |
|
|
|
|
107
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
108
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $limit)) return $data; |
109
|
|
|
$stmt = $this->mysqli->prepare(" |
110
|
|
|
SELECT |
111
|
|
|
b.*, |
112
|
|
|
a.username AS finder, |
113
|
|
|
a.is_anonymous AS is_anonymous, |
114
|
|
|
ROUND(difficulty * POW(2, 32 - " . $this->coin->getTargetBits() . "), " . $this->coin->getShareDifficultyPrecision() . ") AS estshares |
115
|
|
|
FROM " . $this->block->getTableName() . " AS b |
116
|
|
|
LEFT JOIN " . $this->user->getTableName() . " AS a |
|
|
|
|
117
|
|
|
ON b.account_id = a.id |
118
|
|
|
ORDER BY height DESC LIMIT ?"); |
119
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("i", $limit) && $stmt->execute() && $result = $stmt->get_result()) |
120
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $limit, $result->fetch_all(MYSQLI_ASSOC), 5); |
121
|
|
|
return $this->sqlError(); |
|
|
|
|
122
|
|
|
} |
123
|
|
|
|
124
|
|
|
/** |
125
|
|
|
* Get our last $limit blocks found by height |
126
|
|
|
* @param limit int Last limit blocks |
127
|
|
|
* @return array |
128
|
|
|
**/ |
129
|
|
|
public function getBlocksFoundHeight($iHeight=0, $limit=10) { |
130
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
131
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $iHeight . $limit)) return $data; |
132
|
|
|
$stmt = $this->mysqli->prepare(" |
133
|
|
|
SELECT |
134
|
|
|
b.*, |
135
|
|
|
a.username AS finder, |
136
|
|
|
a.is_anonymous AS is_anonymous, |
137
|
|
|
ROUND(difficulty * POW(2, 32 - " . $this->coin->getTargetBits() . "), 4) AS estshares |
138
|
|
|
FROM " . $this->block->getTableName() . " AS b |
139
|
|
|
LEFT JOIN " . $this->user->getTableName() . " AS a |
140
|
|
|
ON b.account_id = a.id |
141
|
|
|
WHERE b.height <= ? |
142
|
|
|
ORDER BY height DESC LIMIT ?"); |
143
|
|
View Code Duplication |
if ($this->checkStmt($stmt) && $stmt->bind_param("ii", $iHeight, $limit) && $stmt->execute() && $result = $stmt->get_result()) |
|
|
|
|
144
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $iHeight . $limit, $result->fetch_all(MYSQLI_ASSOC), 5); |
145
|
|
|
return $this->sqlError(); |
|
|
|
|
146
|
|
|
} |
147
|
|
|
|
148
|
|
|
/** |
149
|
|
|
* Get SUM of blocks found and generated Coins for each Account |
150
|
|
|
* @param limit int Last limit blocks |
151
|
|
|
* @return array |
152
|
|
|
**/ |
153
|
|
|
public function getBlocksSolvedbyAccount($limit=25) { |
154
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
155
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $limit)) return $data; |
156
|
|
|
$stmt = $this->mysqli->prepare(" |
157
|
|
|
SELECT |
158
|
|
|
b.*, |
159
|
|
|
a.username AS finder, |
160
|
|
|
a.is_anonymous AS is_anonymous, |
161
|
|
|
COUNT(b.id) AS solvedblocks, |
162
|
|
|
SUM(b.amount) AS generatedcoins |
163
|
|
|
FROM " . $this->block->getTableName() . " AS b |
164
|
|
|
LEFT JOIN " . $this->user->getTableName() . " AS a |
165
|
|
|
ON b.account_id = a.id |
166
|
|
|
WHERE confirmations > 0 |
167
|
|
|
GROUP BY finder |
168
|
|
|
ORDER BY solvedblocks DESC LIMIT ?"); |
169
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("i", $limit) && $stmt->execute() && $result = $stmt->get_result()) |
170
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $limit, $result->fetch_all(MYSQLI_ASSOC), 5); |
171
|
|
|
return $this->sqlError(); |
|
|
|
|
172
|
|
|
} |
173
|
|
|
|
174
|
|
|
/** |
175
|
|
|
* Get SUM of blocks found and generated Coins for each worker |
176
|
|
|
* @param limit int Last limit blocks |
177
|
|
|
* @return array |
178
|
|
|
**/ |
179
|
|
|
public function getBlocksSolvedbyWorker($account_id, $limit=25) { |
180
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
181
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $account_id . $limit)) return $data; |
182
|
|
|
$stmt = $this->mysqli->prepare(" |
183
|
|
|
SELECT |
184
|
|
|
worker_name AS finder, |
185
|
|
|
COUNT(id) AS solvedblocks, |
186
|
|
|
SUM(amount) AS generatedcoins |
187
|
|
|
FROM " . $this->block->getTableName() . " |
188
|
|
|
WHERE account_id = ? AND worker_name != 'unknown' |
189
|
|
|
GROUP BY finder |
190
|
|
|
ORDER BY solvedblocks DESC LIMIT ?"); |
191
|
|
View Code Duplication |
if ($this->checkStmt($stmt) && $stmt->bind_param("ii", $account_id, $limit) && $stmt->execute() && $result = $stmt->get_result()) |
|
|
|
|
192
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $account_id . $limit, $result->fetch_all(MYSQLI_ASSOC), 5); |
193
|
|
|
return $this->sqlError(); |
|
|
|
|
194
|
|
|
} |
195
|
|
|
|
196
|
|
|
/** |
197
|
|
|
* Currently the only function writing to the database |
198
|
|
|
* Stored per block user statistics of valid and invalid shares |
199
|
|
|
* @param aStats array Array with user id, valid and invalid shares |
200
|
|
|
* @param iBlockId int Block ID as store in the Block table |
201
|
|
|
* @return bool |
202
|
|
|
**/ |
203
|
|
|
public function updateShareStatistics($aStats, $iBlockId) { |
204
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
205
|
|
|
$stmt = $this->mysqli->prepare("INSERT INTO $this->table (account_id, valid, invalid, block_id) VALUES (?, ?, ?, ?)"); |
206
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('iddi', $aStats['id'], $aStats['valid'], $aStats['invalid'], $iBlockId) && $stmt->execute()) return true; |
207
|
|
|
return $this->sqlError(); |
208
|
|
|
} |
209
|
|
|
|
210
|
|
|
/** |
211
|
|
|
* insert user round and pplns shares merged array |
212
|
|
|
**/ |
213
|
|
|
public function insertPPLNSStatistics($aStats, $iBlockId) { |
214
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
215
|
|
|
$stmt = $this->mysqli->prepare("INSERT INTO $this->table (account_id, valid, invalid, pplns_valid, pplns_invalid, block_id) VALUES (?, ?, ?, ?, ?, ?)"); |
216
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('iddddi', $aStats['id'], $aStats['valid'], $aStats['invalid'], $aStats['pplns_valid'], $aStats['pplns_invalid'], $iBlockId) && $stmt->execute()) return true; |
217
|
|
|
return $this->sqlError(); |
218
|
|
|
} |
219
|
|
|
|
220
|
|
|
/** |
221
|
|
|
* Get our current pool hashrate for the past 10 minutes across both |
222
|
|
|
* shares and shares_archive table |
223
|
|
|
* @param none |
224
|
|
|
* @return data object Return our hashrateas an object |
225
|
|
|
**/ |
226
|
|
|
public function getCurrentHashrate($interval=180) { |
227
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
228
|
|
|
if ($this->getGetCache() && $data = $this->memcache->getStatic(__FUNCTION__)) return $data; |
229
|
|
|
$stmt = $this->mysqli->prepare(" |
230
|
|
|
SELECT |
231
|
|
|
( |
232
|
|
|
( |
233
|
|
|
SELECT IFNULL(SUM(IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty)), 0) AS shares |
|
|
|
|
234
|
|
|
FROM " . $this->share->getTableName() . " |
|
|
|
|
235
|
|
|
WHERE time > DATE_SUB(now(), INTERVAL ? SECOND) |
236
|
|
|
AND our_result = 'Y' |
237
|
|
|
) + ( |
238
|
|
|
SELECT IFNULL(SUM(IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty)), 0) AS shares |
239
|
|
|
FROM " . $this->share->getArchiveTableName() . " |
240
|
|
|
WHERE time > DATE_SUB(now(), INTERVAL ? SECOND) |
241
|
|
|
AND our_result = 'Y' |
242
|
|
|
) |
243
|
|
|
) AS shares |
244
|
|
|
FROM DUAL"); |
245
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('ii', $interval, $interval) && $stmt->execute() && $result = $stmt->get_result() ) { |
246
|
|
|
$hashrate = $this->coin->calcHashrate($result->fetch_object()->shares, $interval); |
247
|
|
|
return $this->memcache->setStaticCache(__FUNCTION__, $hashrate); |
248
|
|
|
} |
249
|
|
|
return $this->sqlError(); |
|
|
|
|
250
|
|
|
} |
251
|
|
|
|
252
|
|
|
/** |
253
|
|
|
* Same as getCurrentHashrate but for Shares |
254
|
|
|
* @param none |
255
|
|
|
* @return data object Our share rate in shares per second |
256
|
|
|
**/ |
257
|
|
View Code Duplication |
public function getCurrentShareRate($interval=180) { |
|
|
|
|
258
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
259
|
|
|
if ($data = $this->memcache->getStatic(__FUNCTION__)) return $data; |
260
|
|
|
$stmt = $this->mysqli->prepare(" |
261
|
|
|
SELECT |
262
|
|
|
( |
263
|
|
|
( |
264
|
|
|
SELECT ROUND(SUM(difficulty) / ?, " . $this->coin->getShareDifficultyPrecision() . ") AS sharerate |
265
|
|
|
FROM " . $this->share->getTableName() . " |
266
|
|
|
WHERE time > DATE_SUB(now(), INTERVAL ? SECOND) |
267
|
|
|
AND our_result = 'Y' |
268
|
|
|
) + ( |
269
|
|
|
SELECT ROUND(SUM(difficulty) / ?, " . $this->coin->getShareDifficultyPrecision() . ") AS sharerate |
270
|
|
|
FROM " . $this->share->getArchiveTableName() . " |
271
|
|
|
WHERE time > DATE_SUB(now(), INTERVAL ? SECOND) |
272
|
|
|
AND our_result = 'Y' |
273
|
|
|
) |
274
|
|
|
) AS sharerate |
275
|
|
|
FROM DUAL"); |
276
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('iiii', $interval, $interval, $interval, $interval) && $stmt->execute() && $result = $stmt->get_result() ) return $this->memcache->setStaticCache(__FUNCTION__, $result->fetch_object()->sharerate); |
277
|
|
|
return $this->sqlError(); |
|
|
|
|
278
|
|
|
} |
279
|
|
|
|
280
|
|
|
/** |
281
|
|
|
* Get total shares for this round, since last block found |
282
|
|
|
* @param none |
283
|
|
|
* @return data array invalid and valid shares |
284
|
|
|
**/ |
285
|
|
|
public function getRoundShares() { |
286
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
287
|
|
|
// Try the statistics cron cache, then function cache, then fallback to SQL |
288
|
|
|
if ($data = $this->memcache->get(STATISTICS_ALL_USER_SHARES)) { |
289
|
|
|
$this->debug->append("Found data in statistics cache", 2); |
290
|
|
|
$total = array('valid' => 0, 'invalid' => 0); |
291
|
|
|
foreach ($data['data'] as $aUser) { |
292
|
|
|
$total['valid'] += $aUser['valid']; |
293
|
|
|
$total['invalid'] += $aUser['invalid']; |
294
|
|
|
} |
295
|
|
|
return $total; |
|
|
|
|
296
|
|
|
} |
297
|
|
|
if ($data = $this->memcache->get(STATISTICS_ROUND_SHARES)) { |
298
|
|
|
$this->debug->append("Found data in local cache", 2); |
299
|
|
|
return $data; |
300
|
|
|
} |
301
|
|
|
$stmt = $this->mysqli->prepare(" |
302
|
|
|
SELECT |
303
|
|
|
IFNULL(SUM(IF(our_result='Y', IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty), 0)), 0) AS valid, |
304
|
|
|
IFNULL(SUM(IF(our_result='N', IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty), 0)), 0) AS invalid |
305
|
|
|
FROM " . $this->share->getTableName() . " |
306
|
|
|
WHERE UNIX_TIMESTAMP(time) > IFNULL((SELECT MAX(time) FROM " . $this->block->getTableName() . "), 0)"); |
307
|
|
|
if ( $this->checkStmt($stmt) && $stmt->execute() && $result = $stmt->get_result() ) |
308
|
|
|
return $this->memcache->setCache(STATISTICS_ROUND_SHARES, $result->fetch_assoc()); |
309
|
|
|
return $this->sqlError(); |
|
|
|
|
310
|
|
|
} |
311
|
|
|
|
312
|
|
|
/** |
313
|
|
|
* Get amount of shares for a all users |
314
|
|
|
* Used in statistics cron to refresh memcache data |
315
|
|
|
* @param account_id int User ID |
316
|
|
|
* @return data array invalid and valid share counts |
317
|
|
|
**/ |
318
|
|
|
public function getAllUserShares() { |
319
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
320
|
|
|
if (! $data = $this->memcache->get(STATISTICS_ALL_USER_SHARES)) { |
321
|
|
|
$data['share_id'] = 0; |
322
|
|
|
$data['data'] = array(); |
323
|
|
|
} |
324
|
|
|
$stmt = $this->mysqli->prepare(" |
325
|
|
|
SELECT |
326
|
|
|
IFNULL(SUM(IF(our_result='Y', IF(s.difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), s.difficulty), 0)), 0) AS valid, |
327
|
|
|
IFNULL(SUM(IF(our_result='N', IF(s.difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), s.difficulty), 0)), 0) AS invalid, |
328
|
|
|
u.id AS id, |
329
|
|
|
u.donate_percent AS donate_percent, |
330
|
|
|
u.is_anonymous AS is_anonymous, |
331
|
|
|
u.username AS username |
332
|
|
|
FROM " . $this->share->getTableName() . " AS s, |
333
|
|
|
" . $this->user->getTableName() . " AS u |
334
|
|
|
WHERE u.username = SUBSTRING_INDEX( s.username, '.', 1 ) |
335
|
|
|
AND UNIX_TIMESTAMP(s.time) > IFNULL( |
336
|
|
|
( |
337
|
|
|
SELECT MAX(b.time) |
338
|
|
|
FROM " . $this->block->getTableName() . " AS b |
339
|
|
|
) ,0 ) |
340
|
|
|
AND s.id > ? |
341
|
|
|
GROUP BY u.id"); |
342
|
|
|
if ($stmt && $stmt->bind_param('i', $data['share_id']) && $stmt->execute() && $result = $stmt->get_result()) { |
343
|
|
|
$data_new = array(); |
|
|
|
|
344
|
|
|
while ($row = $result->fetch_assoc()) { |
345
|
|
|
if (! array_key_exists($row['id'], $data['data'])) { |
346
|
|
|
$data['data'][$row['id']] = $row; |
347
|
|
|
} else { |
348
|
|
|
$data['data'][$row['id']]['valid'] += $row['valid']; |
349
|
|
|
$data['data'][$row['id']]['invalid'] += $row['invalid']; |
350
|
|
|
$data['data'][$row['id']]['donate_percent'] = $row['donate_percent']; |
351
|
|
|
$data['data'][$row['id']]['is_anonymous'] = $row['is_anonymous']; |
352
|
|
|
} |
353
|
|
|
} |
354
|
|
|
$data['share_id'] = $this->share->getLastInsertedShareId(); |
355
|
|
|
return $this->memcache->setCache(STATISTICS_ALL_USER_SHARES, $data); |
356
|
|
|
} |
357
|
|
|
return $this->sqlError(); |
|
|
|
|
358
|
|
|
} |
359
|
|
|
|
360
|
|
|
/** |
361
|
|
|
* Get amount of shares for a specific user |
362
|
|
|
* @param username str username |
363
|
|
|
* @param account_id int account id |
364
|
|
|
* @return data array invalid and valid share counts |
365
|
|
|
**/ |
366
|
|
|
public function getUserShares($username, $account_id=NULL) { |
367
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
368
|
|
|
// Dual-caching, try statistics cron first, then fallback to local, then fallbock to SQL |
369
|
|
|
if ($data = $this->memcache->get(STATISTICS_ALL_USER_SHARES)) { |
370
|
|
|
if (array_key_exists($account_id, $data['data'])) |
371
|
|
|
return $data['data'][$account_id]; |
372
|
|
|
// We have no cached value, we return defaults |
373
|
|
|
return array('valid' => 0, 'invalid' => 0, 'donate_percent' => 0, 'is_anonymous' => 0); |
|
|
|
|
374
|
|
|
} |
375
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $account_id)) return $data; |
376
|
|
|
$stmt = $this->mysqli->prepare(" |
377
|
|
|
SELECT |
378
|
|
|
IFNULL(SUM(IF(our_result='Y', IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty), 0)), 0) AS valid, |
379
|
|
|
IFNULL(SUM(IF(our_result='N', IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty), 0)), 0) AS invalid |
380
|
|
|
FROM " . $this->share->getTableName() . " |
381
|
|
|
WHERE username LIKE ? |
382
|
|
|
AND UNIX_TIMESTAMP(time) >IFNULL((SELECT MAX(b.time) FROM " . $this->block->getTableName() . " AS b),0)"); |
383
|
|
|
$username = $username . ".%"; |
384
|
|
|
if ($stmt && $stmt->bind_param("s", $username) && $stmt->execute() && $result = $stmt->get_result()) |
385
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $account_id, $result->fetch_assoc()); |
386
|
|
|
return $this->sqlError(); |
|
|
|
|
387
|
|
|
} |
388
|
|
|
|
389
|
|
|
/** |
390
|
|
|
* Admin panel specific query |
391
|
|
|
* @return data array User settings and shares |
392
|
|
|
**/ |
393
|
|
|
public function getAllUserStats($filter='%',$limit=1,$start=0) { |
394
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
395
|
|
|
$sql = " |
396
|
|
|
SELECT |
397
|
|
|
a.id AS id, |
398
|
|
|
a.is_admin as is_admin, |
399
|
|
|
a.is_locked as is_locked, |
400
|
|
|
a.no_fees as no_fees, |
401
|
|
|
a.username AS username, |
402
|
|
|
a.donate_percent AS donate_percent, |
403
|
|
|
a.email AS email, |
404
|
|
|
a.last_login as last_login |
405
|
|
|
FROM " . $this->user->getTableName() . " AS a"; |
406
|
|
|
if (is_array($filter)) { |
407
|
|
|
$aFilter = array(); |
408
|
|
|
foreach ($filter as $key => $value) { |
409
|
|
|
if (isset($value) && $value != "" ) { |
410
|
|
|
switch ($key) { |
411
|
|
|
case 'account': |
412
|
|
|
$aFilter[] = "a.username LIKE ?"; |
413
|
|
|
$this->addParam('s', $value); |
414
|
|
|
break; |
415
|
|
|
case 'email': |
416
|
|
|
$aFilter[] = "a.email LIKE ?"; |
417
|
|
|
$this->addParam('s', $value); |
418
|
|
|
break; |
419
|
|
|
case 'is_admin': |
420
|
|
|
$aFilter[] = "a.is_admin = ?"; |
421
|
|
|
$this->addParam('i', $value); |
422
|
|
|
break; |
423
|
|
|
case 'is_locked': |
424
|
|
|
$aFilter[] = "a.is_locked = ?"; |
425
|
|
|
$this->addParam('i', $value); |
426
|
|
|
break; |
427
|
|
|
case 'no_fees': |
428
|
|
|
$aFilter[] = "a.no_fees = ?"; |
429
|
|
|
$this->addParam('i', $value); |
430
|
|
|
break; |
431
|
|
|
} |
432
|
|
|
} |
433
|
|
|
} |
434
|
|
|
} |
435
|
|
|
if (!empty($aFilter)) { |
436
|
|
|
$sql .= " WHERE "; |
437
|
|
|
$sql .= implode(' AND ', $aFilter); |
438
|
|
|
} |
439
|
|
|
$sql .= " |
440
|
|
|
ORDER BY username |
441
|
|
|
LIMIT ?,?"; |
442
|
|
|
$this->addParam('i', $start); |
443
|
|
|
$this->addParam('i', $limit); |
444
|
|
|
$stmt = $this->mysqli->prepare($sql); |
445
|
|
|
if ($this->checkStmt($stmt) && call_user_func_array( array($stmt, 'bind_param'), $this->getParam()) && $stmt->execute() && $result = $stmt->get_result()) { |
446
|
|
|
// Add our cached shares to the users |
447
|
|
|
$aUsers = array(); |
448
|
|
|
while ($row = $result->fetch_assoc()) { |
449
|
|
|
$row['shares'] = $this->getUserShares($row['username'], $row['id']); |
450
|
|
|
$aUsers[] = $row; |
451
|
|
|
} |
452
|
|
|
if (count($aUsers) > 0) { |
453
|
|
|
return $aUsers; |
454
|
|
|
} |
455
|
|
|
} |
456
|
|
|
return $this->sqlError(); |
457
|
|
|
} |
458
|
|
|
|
459
|
|
|
/** |
460
|
|
|
* Fetch all user hashrates based on shares and archived shares |
461
|
|
|
* Store it in cache, also keep a copy of the data internally to |
462
|
|
|
* return it for further processing |
463
|
|
|
* @return data array Set of all user stats |
464
|
|
|
**/ |
465
|
|
|
public function fetchAllUserMiningStats($interval=180) { |
466
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
467
|
|
|
$stmt = $this->mysqli->prepare(" |
468
|
|
|
SELECT |
469
|
|
|
a.id AS id, |
470
|
|
|
a.username AS account, |
471
|
|
|
COUNT(DISTINCT t1.username) AS workers, |
472
|
|
|
IFNULL(SUM(t1.difficulty), 0) AS shares, |
473
|
|
|
ROUND(SUM(t1.difficulty) / ?, " . $this->coin->getShareDifficultyPrecision() . ") AS sharerate, |
474
|
|
|
IFNULL(AVG(IF(difficulty=0, pow(2, (" . $this->config['difficulty'] . " - 16)), difficulty)), 0) AS avgsharediff |
475
|
|
|
FROM ( |
476
|
|
|
SELECT |
477
|
|
|
id, |
478
|
|
|
IF(difficulty = 0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty) AS difficulty, |
479
|
|
|
username |
480
|
|
|
FROM " . $this->share->getTableName() . " |
481
|
|
|
WHERE time > DATE_SUB(now(), INTERVAL ? SECOND) AND our_result = 'Y' |
482
|
|
|
UNION |
483
|
|
|
SELECT |
484
|
|
|
share_id, |
485
|
|
|
IF(difficulty = 0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty) AS difficulty, |
486
|
|
|
username |
487
|
|
|
FROM " . $this->share->getArchiveTableName() . " |
488
|
|
|
WHERE time > DATE_SUB(now(), INTERVAL ? SECOND) AND our_result = 'Y' |
489
|
|
|
) AS t1 |
490
|
|
|
LEFT JOIN " . $this->user->getTableName() . " AS a |
491
|
|
|
ON SUBSTRING_INDEX( t1.username, '.', 1 ) = a.username |
492
|
|
|
WHERE a.id IS NOT NULL |
493
|
|
|
GROUP BY account |
494
|
|
|
ORDER BY shares DESC |
495
|
|
|
"); |
496
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("iii", $interval, $interval, $interval) && $stmt->execute() && $result = $stmt->get_result() ) { |
497
|
|
|
$aData = array(); |
498
|
|
|
while ($row = $result->fetch_assoc()) { |
499
|
|
|
$aData['data'][$row['id']] = $row; |
500
|
|
|
$aData['data'][$row['id']]['hashrate'] = $this->coin->calcHashrate($row['shares'], $interval); |
501
|
|
|
} |
502
|
|
|
$this->allUserMiningStats = $aData; |
503
|
|
|
return $this->memcache->setStaticCache(STATISTICS_ALL_USER_HASHRATES, $aData, 600); |
504
|
|
|
} else { |
505
|
|
|
return $this->sqlError(); |
|
|
|
|
506
|
|
|
} |
507
|
|
|
} |
508
|
|
|
|
509
|
|
|
/** |
510
|
|
|
* Store our gathered data into our statistic table for users |
511
|
|
|
* @param aData array Data created by fetchAllUserMiningStats |
512
|
|
|
* @return bool true or false |
513
|
|
|
**/ |
514
|
|
|
public function storeAllUserMiningStatsSnapshot($aData) { |
515
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
516
|
|
|
if (!isset($aData['data'])) return false; |
517
|
|
|
// initilize |
518
|
|
|
$timestamp = time(); // Store all entries with the same timestamp to reduce cardinality |
519
|
|
|
$ok = 0; |
520
|
|
|
$failed = 0; |
521
|
|
|
foreach ($aData['data'] as $key => $aUserData) { |
522
|
|
|
$stmt = $this->mysqli->prepare(" |
523
|
|
|
INSERT INTO " . $this->getUserStatsTableName() . " |
524
|
|
|
( account_id, hashrate, workers, sharerate, timestamp ) VALUES ( ?, ?, ?, ?, ?)"); |
525
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("ididi", $aUserData['id'], $aUserData['hashrate'], $aUserData['workers'], $aUserData['sharerate'], $timestamp) && $stmt->execute() ) { |
526
|
|
|
$ok++; |
527
|
|
|
} else { |
528
|
|
|
$failed++; |
529
|
|
|
} |
530
|
|
|
} |
531
|
|
|
return array('ok' => $ok, 'failed' => $failed); |
532
|
|
|
} |
533
|
|
|
|
534
|
|
|
/** |
535
|
|
|
* Fetch unpaid PPS shares for an account |
536
|
|
|
* @param username string Username |
537
|
|
|
* @param account_id int User ID |
538
|
|
|
* @param last_paid_pps_id int Last paid out share by pps_payout cron |
539
|
|
|
* @return data int Sum of unpaid diff1 shares |
540
|
|
|
**/ |
541
|
|
|
public function getUserUnpaidPPSShares($username, $account_id=NULL, $last_paid_pps_id) { |
542
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
543
|
|
|
if ($this->getGetCache() && $data = $this->memcache->get(__FUNCTION__ . $account_id)) return $data; |
544
|
|
|
$stmt = $this->mysqli->prepare(" |
545
|
|
|
SELECT |
546
|
|
|
IFNULL(SUM(IF(difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty)), 0) AS total |
547
|
|
|
FROM " . $this->share->getTableName() . " |
548
|
|
|
WHERE username LIKE ? |
549
|
|
|
AND id > ? |
550
|
|
|
AND our_result = 'Y'"); |
551
|
|
|
$username = $username . ".%"; |
552
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("si", $username, $last_paid_pps_id) && $stmt->execute() && $result = $stmt->get_result() ) |
553
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $account_id, $result->fetch_object()->total); |
554
|
|
|
return $this->sqlError(); |
|
|
|
|
555
|
|
|
} |
556
|
|
|
|
557
|
|
|
/** |
558
|
|
|
* Get Shares per x interval by user |
559
|
|
|
* @param username string username |
560
|
|
|
* @param $account_id int account id |
561
|
|
|
* @return data integer Current Sharerate in diff1 shares/s |
562
|
|
|
**/ |
563
|
|
|
public function getUserMiningStats($username, $account_id=NULL, $interval=180) { |
564
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
565
|
|
|
// Dual-caching, try statistics cron first, then fallback to local, then fallbock to SQL |
566
|
|
|
if ($this->getGetCache() && $data = $this->memcache->getStatic(STATISTICS_ALL_USER_HASHRATES)) { |
567
|
|
|
if (array_key_exists($account_id, $data['data'])) { |
568
|
|
|
$retData['hashrate'] = $data['data'][$account_id]['hashrate']; |
|
|
|
|
569
|
|
|
$retData['sharerate'] = $data['data'][$account_id]['sharerate']; |
570
|
|
|
$retData['avgsharediff'] = $data['data'][$account_id]['avgsharediff']; |
571
|
|
|
return $retData; |
|
|
|
|
572
|
|
|
} |
573
|
|
|
return array('hashrate' => (float)0, 'sharerate' => (float)0, 'avgsharediff' => (float)0); |
|
|
|
|
574
|
|
|
} |
575
|
|
|
if ($this->getGetCache() && $data = $this->memcache->get(__FUNCTION__ . $account_id)) return $data; |
576
|
|
|
$stmt = $this->mysqli->prepare(" |
577
|
|
|
SELECT |
578
|
|
|
IFNULL(SUM(difficulty) / ?, 0) AS sharerate, |
579
|
|
|
IFNULL(SUM(difficulty), 0) AS shares, |
580
|
|
|
IFNULL(AVG(difficulty), 0) AS avgsharediff |
581
|
|
|
FROM ( |
582
|
|
|
SELECT |
583
|
|
|
id, our_result, IF(difficulty = 0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty) AS difficulty |
584
|
|
|
FROM |
585
|
|
|
" . $this->share->getTableName() . " |
586
|
|
|
WHERE username LIKE ? |
587
|
|
|
AND time > DATE_SUB(now(), INTERVAL ? SECOND) |
588
|
|
|
AND our_result = 'Y' |
589
|
|
|
UNION |
590
|
|
|
SELECT |
591
|
|
|
share_id, our_result, IF(difficulty = 0, POW(2, (" . $this->config['difficulty'] . " - 16)), difficulty) AS difficulty |
592
|
|
|
FROM |
593
|
|
|
" . $this->share->getArchiveTableName() . " |
594
|
|
|
WHERE username LIKE ? |
595
|
|
|
AND time > DATE_SUB(now(), INTERVAL ? SECOND) |
596
|
|
|
AND our_result = 'Y' |
597
|
|
|
) AS temp"); |
598
|
|
|
$username = $username . ".%"; |
599
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("isisi", $interval, $username, $interval, $username, $interval) && $stmt->execute() && $result = $stmt->get_result() ) { |
600
|
|
|
$aData = $result->fetch_assoc(); |
601
|
|
|
$aData['hashrate'] = $this->coin->calcHashrate($aData['shares'], $interval); |
602
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $account_id, $aData); |
603
|
|
|
} |
604
|
|
|
return $this->sqlError(); |
|
|
|
|
605
|
|
|
} |
606
|
|
|
|
607
|
|
|
/** |
608
|
|
|
* get our top contributors for either shares or hashrate |
609
|
|
|
* @param type string shares or hashes |
610
|
|
|
* @param limit int Limit result to $limit |
611
|
|
|
* @return data array Users with shares, account or hashrate, account |
612
|
|
|
**/ |
613
|
|
|
public function getTopContributors($type='shares', $limit=15) { |
614
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
615
|
|
|
switch ($type) { |
616
|
|
|
case 'shares': |
617
|
|
View Code Duplication |
if ($this->getGetCache() && $data = $this->memcache->get(__FUNCTION__ . $type . $limit)) return $data; |
|
|
|
|
618
|
|
|
if ($data = $this->memcache->get(STATISTICS_ALL_USER_SHARES)) { |
619
|
|
|
// Use global cache to build data, if we have any data there |
620
|
|
|
if (!empty($data['data']) && is_array($data['data'])) { |
621
|
|
|
foreach($data['data'] as $key => $aUser) { |
622
|
|
|
$shares[$key] = $aUser['valid']; |
|
|
|
|
623
|
|
|
$username[$key] = $aUser['username']; |
|
|
|
|
624
|
|
|
} |
625
|
|
|
array_multisort($shares, SORT_DESC, $username, SORT_ASC, $data['data']); |
|
|
|
|
626
|
|
|
$count = 0; |
627
|
|
|
foreach ($data['data'] as $key => $aUser) { |
628
|
|
|
if ($count == $limit) break; |
629
|
|
|
$count++; |
630
|
|
|
$data_new[$key]['shares'] = $aUser['valid']; |
|
|
|
|
631
|
|
|
$data_new[$key]['account'] = $aUser['username']; |
632
|
|
|
$data_new[$key]['donate_percent'] = $aUser['donate_percent']; |
633
|
|
|
$data_new[$key]['is_anonymous'] = $aUser['is_anonymous']; |
634
|
|
|
} |
635
|
|
|
return $data_new; |
|
|
|
|
636
|
|
|
} |
637
|
|
|
} |
638
|
|
|
// No cached data, fallback to SQL ONLY if we don't use memcache |
639
|
|
|
if ($this->config['memcache']['enabled'] && $this->config['memcache']['force']['contrib_shares']) { |
640
|
|
|
// Do not use SQL queries and a second layer of caching |
641
|
|
|
$this->debug->append('Skipping SQL queries due to config option', 4); |
642
|
|
|
return false; |
|
|
|
|
643
|
|
|
} |
644
|
|
|
$stmt = $this->mysqli->prepare(" |
645
|
|
|
SELECT |
646
|
|
|
a.username AS account, |
647
|
|
|
a.donate_percent AS donate_percent, |
648
|
|
|
a.is_anonymous AS is_anonymous, |
649
|
|
|
IFNULL(SUM(IF(s.difficulty=0, POW(2, (" . $this->config['difficulty'] . " - 16)), s.difficulty)), 0) AS shares |
650
|
|
|
FROM " . $this->share->getTableName() . " AS s |
651
|
|
|
LEFT JOIN " . $this->user->getTableName() . " AS a |
652
|
|
|
ON SUBSTRING_INDEX( s.username, '.', 1 ) = a.username |
653
|
|
|
WHERE our_result = 'Y' |
654
|
|
|
GROUP BY account |
655
|
|
|
ORDER BY shares DESC |
656
|
|
|
LIMIT ?"); |
657
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("i", $limit) && $stmt->execute() && $result = $stmt->get_result()) |
658
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $type . $limit, $result->fetch_all(MYSQLI_ASSOC)); |
659
|
|
|
return $this->sqlError(); |
|
|
|
|
660
|
|
|
break; |
|
|
|
|
661
|
|
|
|
662
|
|
|
case 'hashes': |
663
|
|
View Code Duplication |
if ($this->getGetCache() && $data = $this->memcache->getStatic(__FUNCTION__ . $type . $limit)) return $data; |
|
|
|
|
664
|
|
|
$stmt = $this->mysqli->prepare(" |
665
|
|
|
SELECT |
666
|
|
|
a.username AS account, |
667
|
|
|
a.donate_percent AS donate_percent, |
668
|
|
|
a.is_anonymous AS is_anonymous, |
669
|
|
|
IFNULL(SUM(t1.difficulty), 0) AS shares |
670
|
|
|
FROM |
671
|
|
|
( |
672
|
|
|
SELECT id, IFNULL(IF(difficulty=0, pow(2, (" . $this->config['difficulty'] . " - 16)), difficulty), 0) AS difficulty, username FROM " . $this->share->getTableName() . " WHERE time > DATE_SUB(now(), INTERVAL 10 MINUTE) AND our_result = 'Y' |
673
|
|
|
UNION |
674
|
|
|
SELECT share_id, IFNULL(IF(difficulty=0, pow(2, (" . $this->config['difficulty'] . " - 16)), difficulty), 0) AS difficulty, username FROM " . $this->share->getArchiveTableName() ." WHERE time > DATE_SUB(now(), INTERVAL 10 MINUTE) AND our_result = 'Y' |
675
|
|
|
) AS t1 |
676
|
|
|
LEFT JOIN " . $this->user->getTableName() . " AS a |
677
|
|
|
ON SUBSTRING_INDEX( t1.username, '.', 1 ) = a.username |
678
|
|
|
GROUP BY account |
679
|
|
|
ORDER BY shares DESC LIMIT ?"); |
680
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("i", $limit) && $stmt->execute() && $result = $stmt->get_result()) { |
681
|
|
|
$aData = array(); |
682
|
|
|
$count = 0; |
683
|
|
|
while ($row = $result->fetch_assoc()) { |
684
|
|
|
$aData[$count] = $row; |
685
|
|
|
$aData[$count]['hashrate'] = $this->coin->calcHashrate($row['shares'], 600); |
686
|
|
|
$count++; |
687
|
|
|
} |
688
|
|
|
return $this->memcache->setStaticCache(__FUNCTION__ . $type . $limit, $aData); |
689
|
|
|
} |
690
|
|
|
return $this->sqlError(); |
|
|
|
|
691
|
|
|
break; |
|
|
|
|
692
|
|
|
} |
693
|
|
|
} |
694
|
|
|
|
695
|
|
|
/** |
696
|
|
|
* get Hourly hashrate for a user |
697
|
|
|
* @param username string Username |
698
|
|
|
* @param $account_id int account id |
699
|
|
|
* @return data array NOT FINISHED YET |
700
|
|
|
**/ |
701
|
|
|
public function getHourlyMiningStatsByAccount($account_id, $format='array', $days = 1) { |
702
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
703
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $account_id)) return $data; |
704
|
|
|
$stmt = $this->mysqli->prepare(" |
705
|
|
|
SELECT |
706
|
|
|
timestamp, |
707
|
|
|
FROM_UNIXTIME(timestamp, '%Y-%m-%d %H:%i') AS time, |
708
|
|
|
AVG(hashrate) AS hashrate, |
709
|
|
|
AVG(workers) AS workers, |
710
|
|
|
AVG(sharerate) AS sharerate |
711
|
|
|
FROM " . $this->getUserStatsTableName() . " |
712
|
|
|
WHERE FROM_UNIXTIME(timestamp) >= DATE_SUB(NOW(), INTERVAL $days DAY) |
713
|
|
|
AND account_id = ? |
714
|
|
|
GROUP BY DAY(FROM_UNIXTIME(timestamp)), HOUR(FROM_UNIXTIME(timestamp))"); |
715
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('i', $account_id) && $stmt->execute() && $result = $stmt->get_result()) { |
716
|
|
|
$aData = $result->fetch_all(MYSQLI_ASSOC); |
717
|
|
|
if ($format == 'json') $aData = json_encode($aData); |
718
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $account_id . $format, $aData); |
719
|
|
|
} |
720
|
|
|
return $this->sqlError(); |
|
|
|
|
721
|
|
|
} |
722
|
|
|
|
723
|
|
|
/** |
724
|
|
|
* get user estimated payouts based on share counts |
725
|
|
|
* @param value1 mixed Round shares OR share rate |
726
|
|
|
* @param value2 mixed User shares OR share difficulty |
727
|
|
|
* @param dDonate double User donation setting |
728
|
|
|
* @param bNoFees bool User no-fees option setting |
729
|
|
|
* @return aEstimates array User estimations |
730
|
|
|
**/ |
731
|
|
|
public function getUserEstimates($value1, $value2, $dDonate, $bNoFees, $ppsvalue=0) { |
732
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
733
|
|
|
if ($this->config['payout_system'] != 'pps') { |
734
|
|
|
if (@$value1['valid'] > 0 && @$value2['valid'] > 0) { |
735
|
|
|
$this->config['reward_type'] == 'fixed' ? $reward = $this->config['reward'] : $reward = $this->block->getAverageAmount(); |
736
|
|
|
$aEstimates['block'] = round(( (float)$value2['valid'] / (float)$value1['valid'] ) * (float)$reward, 8); |
|
|
|
|
737
|
|
|
$bNoFees == 0 ? $aEstimates['fee'] = round(((float)$this->config['fees'] / 100) * (float)$aEstimates['block'], 8) : $aEstimates['fee'] = 0; |
738
|
|
|
$aEstimates['donation'] = round((( (float)$dDonate / 100) * ((float)$aEstimates['block'] - (float)$aEstimates['fee'])), 8); |
739
|
|
|
$aEstimates['payout'] = round((float)$aEstimates['block'] - (float)$aEstimates['donation'] - (float)$aEstimates['fee'], 8); |
740
|
|
|
} else { |
741
|
|
|
$aEstimates['block'] = 0; |
|
|
|
|
742
|
|
|
$aEstimates['fee'] = 0; |
743
|
|
|
$aEstimates['donation'] = 0; |
744
|
|
|
$aEstimates['payout'] = 0; |
745
|
|
|
} |
746
|
|
|
} else { |
747
|
|
|
// Hack so we can use this method for PPS estimates too |
748
|
|
|
// value1 = shares/s |
749
|
|
|
// value2 = avg share difficulty |
750
|
|
|
if (@$value1 > 0 && @$value2 > 0) { |
751
|
|
|
$hour = 60 * 60; |
752
|
|
|
$pps = $value1 * $value2 * $ppsvalue; |
753
|
|
|
$aEstimates['hours1'] = $pps * $hour; |
|
|
|
|
754
|
|
|
$aEstimates['hours24'] = $pps * 24 * $hour; |
755
|
|
|
$aEstimates['days7'] = $pps * 24 * 7 * $hour; |
756
|
|
|
$aEstimates['days14'] = $pps * 14 * 24 * $hour; |
757
|
|
|
$aEstimates['days30'] = $pps * 30 * 24 * $hour; |
758
|
|
|
} else { |
759
|
|
|
$aEstimates['hours1'] = 0; |
|
|
|
|
760
|
|
|
$aEstimates['hours24'] = 0; |
761
|
|
|
$aEstimates['days7'] = 0; |
762
|
|
|
$aEstimates['days14'] = 0; |
763
|
|
|
$aEstimates['days30'] = 0; |
764
|
|
|
} |
765
|
|
|
} |
766
|
|
|
return $aEstimates; |
767
|
|
|
} |
768
|
|
|
|
769
|
|
|
/** |
770
|
|
|
* Get pool stats last 24 hours |
771
|
|
|
* @param limit int Last number of hours |
772
|
|
|
* @return array |
773
|
|
|
**/ |
774
|
|
|
public function getPoolStatsHours($hour=24) { |
775
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
776
|
|
|
if ($data = $this->memcache->get(__FUNCTION__ . $hour)) return $data; |
777
|
|
|
$stmt = $this->mysqli->prepare(" |
778
|
|
|
SELECT |
779
|
|
|
IFNULL(COUNT(id), 0) as count, |
780
|
|
|
IFNULL(AVG(difficulty), 0) as average, |
781
|
|
|
IFNULL(SUM(shares), 0) as shares, |
782
|
|
|
IFNULL(SUM(amount), 0) as rewards |
783
|
|
|
FROM " . $this->block->getTableName() . " |
784
|
|
|
WHERE FROM_UNIXTIME(time) > DATE_SUB(now(), INTERVAL ? HOUR) |
785
|
|
|
AND confirmations >= 1"); |
786
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param("i", $hour) && $stmt->execute() && $result = $stmt->get_result()) { |
787
|
|
|
$aData = $result->fetch_assoc(); |
788
|
|
|
$aData['expected'] = $this->coin->calcEstaimtedShares($aData['average']); |
789
|
|
|
return $this->memcache->setCache(__FUNCTION__ . $hour, $aData); |
790
|
|
|
} |
791
|
|
|
return $this->sqlError(); |
|
|
|
|
792
|
|
|
} |
793
|
|
|
|
794
|
|
|
/** |
795
|
|
|
* Caclulate estimated shares based on network difficulty and pool difficulty |
796
|
|
|
* @param dDiff double Network difficulty |
797
|
|
|
* @return shares integer Share count |
798
|
|
|
**/ |
799
|
|
|
public function getEstimatedShares($dDiff) { |
800
|
|
|
return $this->coin->calcEstaimtedShares($dDiff); |
801
|
|
|
} |
802
|
|
|
|
803
|
|
|
/** |
804
|
|
|
* Get the Expected Time per Block in the whole Network in seconde |
805
|
|
|
* @return seconds double Seconds per Block |
806
|
|
|
*/ |
807
|
|
|
public function getExpectedTimePerBlock($type='network',$hashrate = 0){ |
808
|
|
|
if ($data = $this->memcache->get(__FUNCTION__)) return $data; |
809
|
|
|
|
810
|
|
|
if ($this->bitcoin->can_connect() === true) { |
811
|
|
|
if ($type == 'network') { |
812
|
|
|
$hashrate = $this->bitcoin->getnetworkhashps(); |
|
|
|
|
813
|
|
|
} else { |
814
|
|
|
// We need hashes/second and expect khash as input |
815
|
|
|
$hashrate = $hashrate * 1000; |
816
|
|
|
} |
817
|
|
|
$dDifficulty = $this->bitcoin->getdifficulty(); |
818
|
|
|
} else { |
819
|
|
|
$hashrate = 1; |
820
|
|
|
$dDifficulty = 1; |
821
|
|
|
} |
822
|
|
|
if ($hashrate <= 0) $hashrate = 1; |
823
|
|
|
return $this->memcache->setCache(__FUNCTION__ . '_' . $type, $this->coin->calcNetworkExpectedTimePerBlock($dDifficulty, $hashrate)); |
824
|
|
|
} |
825
|
|
|
|
826
|
|
|
/** |
827
|
|
|
* Get the Expected next Difficulty |
828
|
|
|
* @return difficulty double Next difficulty |
829
|
|
|
**/ |
830
|
|
|
public function getExpectedNextDifficulty(){ |
831
|
|
|
if ($data = $this->memcache->get(__FUNCTION__)) return $data; |
832
|
|
|
|
833
|
|
|
if ($this->bitcoin->can_connect() === true) { |
834
|
|
|
$dDifficulty = $this->bitcoin->getdifficulty(); |
835
|
|
|
$dNetworkHashrate = $this->bitcoin->getnetworkhashps(); |
836
|
|
|
} else { |
837
|
|
|
$dDifficulty = 1; |
838
|
|
|
$dNetworkHashrate = 1; |
839
|
|
|
} |
840
|
|
|
|
841
|
|
|
return $this->memcache->setCache(__FUNCTION__, $this->coin->calcExpectedNextDifficulty($dDifficulty, $dNetworkHashrate)); |
842
|
|
|
} |
843
|
|
|
|
844
|
|
|
/** |
845
|
|
|
* Get Number of blocks until next difficulty change |
846
|
|
|
* @return blocks int blocks until difficulty change |
847
|
|
|
**/ |
848
|
|
|
public function getBlocksUntilDiffChange(){ |
849
|
|
|
if ($data = $this->memcache->get(__FUNCTION__)) return $data; |
850
|
|
|
|
851
|
|
|
if ($this->bitcoin->can_connect() === true) { |
852
|
|
|
$iBlockcount = $this->bitcoin->getblockcount(); |
853
|
|
|
} else { |
854
|
|
|
$iBlockcount = 1; |
855
|
|
|
} |
856
|
|
|
|
857
|
|
|
return $this->memcache->setCache(__FUNCTION__, $this->config['coindiffchangetarget'] - ($iBlockcount % $this->config['coindiffchangetarget'])); |
858
|
|
|
} |
859
|
|
|
|
860
|
|
|
/** |
861
|
|
|
* Get current PPS value |
862
|
|
|
* @return value double PPS Value |
863
|
|
|
**/ |
864
|
|
|
|
865
|
|
|
public function getPPSValue() { |
866
|
|
|
// Fetch RPC difficulty |
867
|
|
|
if ($this->bitcoin->can_connect() === true) { |
868
|
|
|
$dDifficulty = $this->bitcoin->getdifficulty(); |
869
|
|
|
} else { |
870
|
|
|
$dDifficulty = 1; |
871
|
|
|
} |
872
|
|
|
|
873
|
|
|
if ($this->config['pps']['reward']['type'] == 'blockavg' && $this->block->getBlockCount() > 0) { |
874
|
|
|
$pps_reward = round($this->block->getAvgBlockReward($this->config['pps']['blockavg']['blockcount'])); |
875
|
|
|
} else { |
876
|
|
|
if ($this->config['pps']['reward']['type'] == 'block') { |
877
|
|
|
if ($aLastBlock = $this->block->getLast()) { |
878
|
|
|
$pps_reward = $aLastBlock['amount']; |
879
|
|
|
} else { |
880
|
|
|
$pps_reward = $this->config['pps']['reward']['default']; |
881
|
|
|
} |
882
|
|
|
} else { |
883
|
|
|
$pps_reward = $this->config['pps']['reward']['default']; |
884
|
|
|
} |
885
|
|
|
} |
886
|
|
|
return round($this->coin->calcPPSValue($pps_reward, $dDifficulty), 12); |
887
|
|
|
} |
888
|
|
|
|
889
|
|
|
/** |
890
|
|
|
* Get all currently active users in the past 2 minutes |
891
|
|
|
* @param interval int Time in seconds to fetch shares from |
892
|
|
|
* @return data mixed int count if any users are active, false otherwise |
893
|
|
|
**/ |
894
|
|
View Code Duplication |
public function getCountAllActiveUsers($interval=120) { |
|
|
|
|
895
|
|
|
$this->debug->append("STA " . __METHOD__, 4); |
896
|
|
|
if ($data = $this->memcache->get(__FUNCTION__)) return $data; |
897
|
|
|
$stmt = $this->mysqli->prepare(" |
898
|
|
|
SELECT COUNT(DISTINCT(SUBSTRING_INDEX( `username` , '.', 1 ))) AS total |
899
|
|
|
FROM " . $this->share->getTableName() . " |
900
|
|
|
WHERE our_result = 'Y' |
901
|
|
|
AND time > DATE_SUB(now(), INTERVAL ? SECOND)"); |
902
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('i', $interval) && $stmt->execute() && $result = $stmt->get_result()) |
903
|
|
|
return $this->memcache->setCache(__FUNCTION__, $result->fetch_object()->total); |
904
|
|
|
return $this->sqlError(); |
|
|
|
|
905
|
|
|
} |
906
|
|
|
|
907
|
|
|
/** |
908
|
|
|
* Purge older entries from our statistics_users table |
909
|
|
|
**/ |
910
|
|
|
public function purgeUserStats($days = 1) { |
911
|
|
|
// Fallbacks if unset |
912
|
|
|
$stmt = $this->mysqli->prepare("DELETE FROM " . $this->getUserStatsTableName() . " WHERE FROM_UNIXTIME(timestamp) <= DATE_SUB(NOW(), INTERVAL ? DAY)"); |
913
|
|
|
if ($this->checkStmt($stmt) && $stmt->bind_param('i', $days) && $stmt->execute()) |
914
|
|
|
return $stmt->affected_rows; |
915
|
|
|
return $this->sqlError(); |
916
|
|
|
} |
917
|
|
|
} |
918
|
|
|
|
919
|
|
|
$statistics = new Statistics(); |
920
|
|
|
$statistics->setDebug($debug); |
921
|
|
|
$statistics->setMysql($mysqli); |
922
|
|
|
$statistics->setShare($share); |
923
|
|
|
$statistics->setUser($user); |
924
|
|
|
$statistics->setBlock($block); |
925
|
|
|
$statistics->setMemcache($memcache); |
926
|
|
|
$statistics->setConfig($config); |
927
|
|
|
$statistics->setBitcoin($bitcoin); |
928
|
|
|
$statistics->setErrorCodes($aErrorCodes); |
929
|
|
|
$statistics->setCoin($coin); |
930
|
|
|
|
In PHP it is possible to write to properties without declaring them. For example, the following is perfectly valid PHP code:
Generally, it is a good practice to explictly declare properties to avoid accidental typos and provide IDE auto-completion: