brusselopole /
Worldopole
| @@ 5-854 (lines=850) @@ | ||
| 2 | ||
| 3 | namespace Worldopole; |
|
| 4 | ||
| 5 | final class QueryManagerMysqlMAD extends QueryManagerMysql |
|
| 6 | { |
|
| 7 | public function __construct() |
|
| 8 | { |
|
| 9 | parent::__construct(); |
|
| 10 | } |
|
| 11 | ||
| 12 | public function __destruct() |
|
| 13 | { |
|
| 14 | parent::__destruct(); |
|
| 15 | } |
|
| 16 | ||
| 17 | /////////// |
|
| 18 | // Tester |
|
| 19 | /////////// |
|
| 20 | ||
| 21 | public function testTotalPokemon() |
|
| 22 | { |
|
| 23 | $req = 'SELECT COUNT(*) as total FROM pokemon'; |
|
| 24 | $result = $this->mysqli->query($req); |
|
| 25 | if (!is_object($result)) { |
|
| 26 | return 1; |
|
| 27 | } else { |
|
| 28 | $data = $result->fetch_object(); |
|
| 29 | $total = $data->total; |
|
| 30 | ||
| 31 | if (0 == $total) { |
|
| 32 | return 2; |
|
| 33 | } |
|
| 34 | } |
|
| 35 | ||
| 36 | return 0; |
|
| 37 | } |
|
| 38 | ||
| 39 | public function testTotalGyms() |
|
| 40 | { |
|
| 41 | $req = 'SELECT COUNT(*) as total FROM gym'; |
|
| 42 | $result = $this->mysqli->query($req); |
|
| 43 | if (!is_object($result)) { |
|
| 44 | return 1; |
|
| 45 | } else { |
|
| 46 | $data = $result->fetch_object(); |
|
| 47 | $total = $data->total; |
|
| 48 | ||
| 49 | if (0 == $total) { |
|
| 50 | return 2; |
|
| 51 | } |
|
| 52 | } |
|
| 53 | ||
| 54 | return 0; |
|
| 55 | } |
|
| 56 | ||
| 57 | public function testTotalPokestops() |
|
| 58 | { |
|
| 59 | $req = 'SELECT COUNT(*) as total FROM pokestop'; |
|
| 60 | $result = $this->mysqli->query($req); |
|
| 61 | if (!is_object($result)) { |
|
| 62 | return 1; |
|
| 63 | } else { |
|
| 64 | $data = $result->fetch_object(); |
|
| 65 | $total = $data->total; |
|
| 66 | ||
| 67 | if (0 == $total) { |
|
| 68 | return 2; |
|
| 69 | } |
|
| 70 | } |
|
| 71 | ||
| 72 | return 0; |
|
| 73 | } |
|
| 74 | ||
| 75 | ///////////// |
|
| 76 | // Homepage |
|
| 77 | ///////////// |
|
| 78 | ||
| 79 | public function getTotalPokemon() |
|
| 80 | { |
|
| 81 | $req = 'SELECT COUNT(*) AS total FROM pokemon WHERE disappear_time >= UTC_TIMESTAMP()'; |
|
| 82 | $result = $this->mysqli->query($req); |
|
| 83 | $data = $result->fetch_object(); |
|
| 84 | ||
| 85 | return $data; |
|
| 86 | } |
|
| 87 | ||
| 88 | public function getTotalLures() |
|
| 89 | { |
|
| 90 | $req = 'SELECT COUNT(*) AS total FROM pokestop WHERE lure_expiration >= UTC_TIMESTAMP()'; |
|
| 91 | $result = $this->mysqli->query($req); |
|
| 92 | $data = $result->fetch_object(); |
|
| 93 | ||
| 94 | return $data; |
|
| 95 | } |
|
| 96 | ||
| 97 | public function getTotalGyms() |
|
| 98 | { |
|
| 99 | $req = 'SELECT COUNT(DISTINCT(gym_id)) AS total FROM gym'; |
|
| 100 | $result = $this->mysqli->query($req); |
|
| 101 | $data = $result->fetch_object(); |
|
| 102 | ||
| 103 | return $data; |
|
| 104 | } |
|
| 105 | ||
| 106 | public function getTotalRaids() |
|
| 107 | { |
|
| 108 | $req = 'SELECT COUNT(*) AS total FROM raid WHERE start <= UTC_TIMESTAMP() AND end >= UTC_TIMESTAMP()'; |
|
| 109 | $result = $this->mysqli->query($req); |
|
| 110 | $data = $result->fetch_object(); |
|
| 111 | ||
| 112 | return $data; |
|
| 113 | } |
|
| 114 | ||
| 115 | public function getTotalGymsForTeam($team_id) |
|
| 116 | { |
|
| 117 | $req = "SELECT COUNT(DISTINCT(gym_id)) AS total FROM gym WHERE team_id = '".$team_id."'"; |
|
| 118 | $result = $this->mysqli->query($req); |
|
| 119 | $data = $result->fetch_object(); |
|
| 120 | ||
| 121 | return $data; |
|
| 122 | } |
|
| 123 | ||
| 124 | public function getRecentAll() |
|
| 125 | { |
|
| 126 | $req = "SELECT DISTINCT pokemon_id, encounter_id, disappear_time, last_modified, |
|
| 127 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 128 | latitude, longitude, cp, individual_attack, individual_defense, individual_stamina |
|
| 129 | FROM pokemon |
|
| 130 | ORDER BY last_modified DESC |
|
| 131 | LIMIT 0,12"; |
|
| 132 | $result = $this->mysqli->query($req); |
|
| 133 | $data = array(); |
|
| 134 | if ($result->num_rows > 0) { |
|
| 135 | while ($row = $result->fetch_object()) { |
|
| 136 | $data[] = $row; |
|
| 137 | } |
|
| 138 | } |
|
| 139 | ||
| 140 | return $data; |
|
| 141 | } |
|
| 142 | ||
| 143 | public function getRecentMythic($mythic_pokemons) |
|
| 144 | { |
|
| 145 | $req = "SELECT DISTINCT pokemon_id, encounter_id, disappear_time, last_modified, |
|
| 146 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 147 | latitude, longitude, cp, individual_attack, individual_defense, individual_stamina |
|
| 148 | FROM pokemon |
|
| 149 | WHERE pokemon_id IN (".implode(',', $mythic_pokemons).') |
|
| 150 | ORDER BY last_modified DESC |
|
| 151 | LIMIT 0,12'; |
|
| 152 | $result = $this->mysqli->query($req); |
|
| 153 | $data = array(); |
|
| 154 | if ($result->num_rows > 0) { |
|
| 155 | while ($row = $result->fetch_object()) { |
|
| 156 | $data[] = $row; |
|
| 157 | } |
|
| 158 | } |
|
| 159 | ||
| 160 | return $data; |
|
| 161 | } |
|
| 162 | ||
| 163 | /////////////////// |
|
| 164 | // Single Pokemon |
|
| 165 | /////////////////// |
|
| 166 | ||
| 167 | public function getGymsProtectedByPokemon($pokemon_id) |
|
| 168 | { |
|
| 169 | $req = "SELECT COUNT(DISTINCT(gym_id)) AS total FROM gym WHERE guard_pokemon_id = '".$pokemon_id."'"; |
|
| 170 | $result = $this->mysqli->query($req); |
|
| 171 | $data = $result->fetch_object(); |
|
| 172 | ||
| 173 | return $data; |
|
| 174 | } |
|
| 175 | ||
| 176 | public function getPokemonLastSeen($pokemon_id) |
|
| 177 | { |
|
| 178 | $req = "SELECT disappear_time, |
|
| 179 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 180 | latitude, longitude |
|
| 181 | FROM pokemon |
|
| 182 | WHERE pokemon_id = '".$pokemon_id."' |
|
| 183 | ORDER BY disappear_time DESC |
|
| 184 | LIMIT 0,1"; |
|
| 185 | $result = $this->mysqli->query($req); |
|
| 186 | $data = $result->fetch_object(); |
|
| 187 | ||
| 188 | return $data; |
|
| 189 | } |
|
| 190 | ||
| 191 | public function getTop50Pokemon($pokemon_id, $top_order_by, $top_direction) |
|
| 192 | { |
|
| 193 | $req = "SELECT CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS distime, |
|
| 194 | pokemon_id, disappear_time, latitude, longitude, |
|
| 195 | cp, individual_attack, individual_defense, individual_stamina, |
|
| 196 | ROUND(100*(individual_attack+individual_defense+individual_stamina)/45,1) AS IV, |
|
| 197 | move_1, move_2, form |
|
| 198 | FROM pokemon |
|
| 199 | WHERE pokemon_id = '".$pokemon_id."' AND move_1 IS NOT NULL AND move_1 <> '0' |
|
| 200 | ORDER BY $top_order_by $top_direction, disappear_time DESC |
|
| 201 | LIMIT 0,50"; |
|
| 202 | $result = $this->mysqli->query($req); |
|
| 203 | $top = array(); |
|
| 204 | while ($data = $result->fetch_object()) { |
|
| 205 | $top[] = $data; |
|
| 206 | } |
|
| 207 | ||
| 208 | return $top; |
|
| 209 | } |
|
| 210 | ||
| 211 | public function getTop50Trainers($pokemon_id, $best_order_by, $best_direction) |
|
| 212 | { |
|
| 213 | $trainer_blacklist = ''; |
|
| 214 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 215 | $trainer_blacklist = " AND trainer_name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 216 | } |
|
| 217 | $req = "SELECT trainer_name, |
|
| 218 | ROUND((100*(iv_attack+iv_defense+iv_stamina)/45),1) AS IV, |
|
| 219 | move_1, move_2, cp, |
|
| 220 | DATE_FORMAT(last_seen, '%Y-%m-%d') AS lasttime, last_seen |
|
| 221 | FROM gympokemon |
|
| 222 | WHERE pokemon_id = '".$pokemon_id."'".$trainer_blacklist." |
|
| 223 | ORDER BY $best_order_by $best_direction, trainer_name ASC |
|
| 224 | LIMIT 0,50"; |
|
| 225 | $result = $this->mysqli->query($req); |
|
| 226 | $toptrainer = array(); |
|
| 227 | while ($data = $result->fetch_object()) { |
|
| 228 | $toptrainer[] = $data; |
|
| 229 | } |
|
| 230 | ||
| 231 | return $toptrainer; |
|
| 232 | } |
|
| 233 | ||
| 234 | public function getPokemonHeatmap($pokemon_id, $start, $end) |
|
| 235 | { |
|
| 236 | $req = "SELECT latitude, longitude |
|
| 237 | FROM pokemon |
|
| 238 | WHERE pokemon_id = ".$pokemon_id." AND disappear_time BETWEEN '".$start."' AND '".$end."' |
|
| 239 | LIMIT 10000"; |
|
| 240 | $result = $this->mysqli->query($req); |
|
| 241 | $points = array(); |
|
| 242 | while ($data = $result->fetch_object()) { |
|
| 243 | $points[] = $data; |
|
| 244 | } |
|
| 245 | ||
| 246 | return $points; |
|
| 247 | } |
|
| 248 | ||
| 249 | public function getPokemonGraph($pokemon_id) |
|
| 250 | { |
|
| 251 | $req = "SELECT COUNT(*) AS total, |
|
| 252 | HOUR(CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."')) AS disappear_hour |
|
| 253 | FROM (SELECT disappear_time FROM pokemon WHERE pokemon_id = '".$pokemon_id."' LIMIT 100000) AS pokemonFiltered |
|
| 254 | GROUP BY disappear_hour |
|
| 255 | ORDER BY disappear_hour"; |
|
| 256 | $result = $this->mysqli->query($req); |
|
| 257 | $array = array_fill(0, 24, 0); |
|
| 258 | while ($result && $data = $result->fetch_object()) { |
|
| 259 | $array[$data->disappear_hour] = $data->total; |
|
| 260 | } |
|
| 261 | // shift array because AM/PM starts at 1AM not 0:00 |
|
| 262 | $array[] = $array[0]; |
|
| 263 | array_shift($array); |
|
| 264 | ||
| 265 | return $array; |
|
| 266 | } |
|
| 267 | ||
| 268 | public function getPokemonLive($pokemon_id, $ivMin, $ivMax, $inmap_pokemons) |
|
| 269 | { |
|
| 270 | $inmap_pkms_filter = ''; |
|
| 271 | $where = ' WHERE disappear_time >= UTC_TIMESTAMP() AND pokemon_id = '.$pokemon_id; |
|
| 272 | $reqTestIv = 'SELECT MAX(individual_attack) AS iv FROM pokemon '.$where; |
|
| 273 | $resultTestIv = $this->mysqli->query($reqTestIv); |
|
| 274 | $testIv = $resultTestIv->fetch_object(); |
|
| 275 | if (!is_null($inmap_pokemons) && ('' != $inmap_pokemons)) { |
|
| 276 | foreach ($inmap_pokemons as $inmap) { |
|
| 277 | $inmap_pkms_filter .= "'".$inmap."',"; |
|
| 278 | } |
|
| 279 | $inmap_pkms_filter = rtrim($inmap_pkms_filter, ','); |
|
| 280 | $where .= ' AND encounter_id NOT IN ('.$inmap_pkms_filter.') '; |
|
| 281 | } |
|
| 282 | if (null != $testIv->iv && !is_null($ivMin) && ('' != $ivMin)) { |
|
| 283 | $where .= ' AND ((100/45)*(individual_attack+individual_defense+individual_stamina)) >= ('.$ivMin.') '; |
|
| 284 | } |
|
| 285 | if (null != $testIv->iv && !is_null($ivMax) && ('' != $ivMax)) { |
|
| 286 | $where .= ' AND ((100/45)*(individual_attack+individual_defense+individual_stamina)) <= ('.$ivMax.') '; |
|
| 287 | } |
|
| 288 | $req = "SELECT pokemon_id, encounter_id, latitude, longitude, disappear_time, |
|
| 289 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 290 | individual_attack, individual_defense, individual_stamina, move_1, move_2 |
|
| 291 | FROM pokemon ".$where.' |
|
| 292 | LIMIT 5000'; |
|
| 293 | $result = $this->mysqli->query($req); |
|
| 294 | $spawns = array(); |
|
| 295 | while ($data = $result->fetch_object()) { |
|
| 296 | $spawns[] = $data; |
|
| 297 | } |
|
| 298 | ||
| 299 | return $spawns; |
|
| 300 | } |
|
| 301 | ||
| 302 | public function getPokemonSliderMinMax() |
|
| 303 | { |
|
| 304 | $req = 'SELECT MIN(disappear_time) AS min, MAX(disappear_time) AS max FROM pokemon'; |
|
| 305 | $result = $this->mysqli->query($req); |
|
| 306 | $data = $result->fetch_object(); |
|
| 307 | ||
| 308 | return $data; |
|
| 309 | } |
|
| 310 | ||
| 311 | public function getMapsCoords() |
|
| 312 | { |
|
| 313 | $req = 'SELECT MAX(latitude) AS max_latitude, MIN(latitude) AS min_latitude, |
|
| 314 | MAX(longitude) AS max_longitude, MIN(longitude) as min_longitude |
|
| 315 | FROM trs_spawn'; |
|
| 316 | $result = $this->mysqli->query($req); |
|
| 317 | $data = $result->fetch_object(); |
|
| 318 | ||
| 319 | return $data; |
|
| 320 | } |
|
| 321 | ||
| 322 | public function getPokemonCount($pokemon_id) |
|
| 323 | { |
|
| 324 | $req = 'SELECT count, last_seen, latitude, longitude |
|
| 325 | FROM pokemon_stats |
|
| 326 | WHERE pid = '.$pokemon_id; |
|
| 327 | $result = $this->mysqli->query($req); |
|
| 328 | $data = $result->fetch_object(); |
|
| 329 | ||
| 330 | return $data; |
|
| 331 | } |
|
| 332 | ||
| 333 | public function getPokemonCountAll() |
|
| 334 | { |
|
| 335 | $req = 'SELECT pid as pokemon_id, count, last_seen, latitude, longitude |
|
| 336 | FROM pokemon_stats |
|
| 337 | GROUP BY pid'; |
|
| 338 | $result = $this->mysqli->query($req); |
|
| 339 | $array = array(); |
|
| 340 | while ($data = $result->fetch_object()) { |
|
| 341 | $array[] = $data; |
|
| 342 | } |
|
| 343 | ||
| 344 | return $array; |
|
| 345 | } |
|
| 346 | ||
| 347 | public function getRaidCount($pokemon_id) |
|
| 348 | { |
|
| 349 | $req = 'SELECT count, last_seen, latitude, longitude |
|
| 350 | FROM raid_stats |
|
| 351 | WHERE pid = '.$pokemon_id; |
|
| 352 | $result = $this->mysqli->query($req); |
|
| 353 | $data = $result->fetch_object(); |
|
| 354 | ||
| 355 | return $data; |
|
| 356 | } |
|
| 357 | ||
| 358 | public function getRaidCountAll() |
|
| 359 | { |
|
| 360 | $req = 'SELECT pid as pokemon_id, count, last_seen, latitude, longitude |
|
| 361 | FROM raid_stats |
|
| 362 | GROUP BY pid'; |
|
| 363 | $result = $this->mysqli->query($req); |
|
| 364 | $array = array(); |
|
| 365 | while ($data = $result->fetch_object()) { |
|
| 366 | $array[] = $data; |
|
| 367 | } |
|
| 368 | ||
| 369 | return $array; |
|
| 370 | } |
|
| 371 | ||
| 372 | /////////////// |
|
| 373 | // Pokestops |
|
| 374 | ////////////// |
|
| 375 | ||
| 376 | public function getTotalPokestops() |
|
| 377 | { |
|
| 378 | $req = 'SELECT COUNT(*) as total FROM pokestop'; |
|
| 379 | $result = $this->mysqli->query($req); |
|
| 380 | $data = $result->fetch_object(); |
|
| 381 | ||
| 382 | return $data; |
|
| 383 | } |
|
| 384 | ||
| 385 | public function getAllPokestops() |
|
| 386 | { |
|
| 387 | $req = "SELECT latitude, longitude, lure_expiration, UTC_TIMESTAMP() AS now, |
|
| 388 | CONVERT_TZ(lure_expiration, '+00:00', '".self::$time_offset."') AS lure_expiration_real |
|
| 389 | FROM pokestop"; |
|
| 390 | $result = $this->mysqli->query($req); |
|
| 391 | $pokestops = array(); |
|
| 392 | while ($data = $result->fetch_object()) { |
|
| 393 | $pokestops[] = $data; |
|
| 394 | } |
|
| 395 | ||
| 396 | return $pokestops; |
|
| 397 | } |
|
| 398 | ||
| 399 | ///////// |
|
| 400 | // Gyms |
|
| 401 | ///////// |
|
| 402 | ||
| 403 | public function getTeamGuardians($team_id) |
|
| 404 | { |
|
| 405 | $req = "SELECT COUNT(*) AS total, guard_pokemon_id |
|
| 406 | FROM gym WHERE team_id = '".$team_id."' |
|
| 407 | GROUP BY guard_pokemon_id |
|
| 408 | ORDER BY total DESC |
|
| 409 | LIMIT 0,3"; |
|
| 410 | $result = $this->mysqli->query($req); |
|
| 411 | $datas = array(); |
|
| 412 | while ($data = $result->fetch_object()) { |
|
| 413 | $datas[] = $data; |
|
| 414 | } |
|
| 415 | ||
| 416 | return $datas; |
|
| 417 | } |
|
| 418 | ||
| 419 | public function getOwnedAndPoints($team_id) |
|
| 420 | { |
|
| 421 | $req = "SELECT COUNT(DISTINCT(gym_id)) AS total, |
|
| 422 | ROUND(AVG(total_cp),0) AS average_points |
|
| 423 | FROM gym |
|
| 424 | WHERE team_id = '".$team_id."'"; |
|
| 425 | $result = $this->mysqli->query($req); |
|
| 426 | $data = $result->fetch_object(); |
|
| 427 | ||
| 428 | return $data; |
|
| 429 | } |
|
| 430 | ||
| 431 | public function getAllGyms() |
|
| 432 | { |
|
| 433 | $req = "SELECT gym_id, team_id, latitude, longitude, |
|
| 434 | CONVERT_TZ(last_scanned, '+00:00', '".self::$time_offset."') AS last_scanned, |
|
| 435 | (6 - slots_available) AS level |
|
| 436 | FROM gym"; |
|
| 437 | $result = $this->mysqli->query($req); |
|
| 438 | $gyms = array(); |
|
| 439 | while ($data = $result->fetch_object()) { |
|
| 440 | $gyms[] = $data; |
|
| 441 | } |
|
| 442 | ||
| 443 | return $gyms; |
|
| 444 | } |
|
| 445 | ||
| 446 | public function getGymData($gym_id) |
|
| 447 | { |
|
| 448 | $req = "SELECT gymdetails.name AS name, gymdetails.description AS description, gymdetails.url AS url, gym.team_id AS team, |
|
| 449 | CONVERT_TZ(gym.last_scanned, '+00:00', '".self::$time_offset."') AS last_scanned, |
|
| 450 | gym.guard_pokemon_id AS guard_pokemon_id, |
|
| 451 | gym.total_cp AS total_cp, |
|
| 452 | (6 - gym.slots_available) AS level |
|
| 453 | FROM gymdetails |
|
| 454 | LEFT JOIN gym ON gym.gym_id = gymdetails.gym_id |
|
| 455 | WHERE gym.gym_id='".$gym_id."'"; |
|
| 456 | $result = $this->mysqli->query($req); |
|
| 457 | $data = $result->fetch_object(); |
|
| 458 | ||
| 459 | return $data; |
|
| 460 | } |
|
| 461 | ||
| 462 | public function getGymDefenders($gym_id) |
|
| 463 | { |
|
| 464 | $req = "SELECT DISTINCT gympokemon.pokemon_uid, pokemon_id, iv_attack, iv_defense, iv_stamina, MAX(cp) AS cp, gymmember.gym_id |
|
| 465 | FROM gympokemon |
|
| 466 | INNER JOIN gymmember ON gympokemon.pokemon_uid=gymmember.pokemon_uid |
|
| 467 | GROUP BY gympokemon.pokemon_uid, pokemon_id, iv_attack, iv_defense, iv_stamina, gym_id |
|
| 468 | HAVING gymmember.gym_id='".$gym_id."' |
|
| 469 | ORDER BY cp DESC"; |
|
| 470 | $result = $this->mysqli->query($req); |
|
| 471 | $defenders = array(); |
|
| 472 | while ($data = $result->fetch_object()) { |
|
| 473 | $defenders[] = $data; |
|
| 474 | } |
|
| 475 | ||
| 476 | return $defenders; |
|
| 477 | } |
|
| 478 | ||
| 479 | //////////////// |
|
| 480 | // Gym History |
|
| 481 | //////////////// |
|
| 482 | ||
| 483 | public function getGymHistories($gym_name, $team, $page, $ranking) |
|
| 484 | { |
|
| 485 | $where = ''; |
|
| 486 | if (isset($gym_name) && '' != $gym_name) { |
|
| 487 | $where = " WHERE name LIKE '%".$gym_name."%'"; |
|
| 488 | } |
|
| 489 | if (isset($team) && '' != $team) { |
|
| 490 | $where .= ('' == $where ? ' WHERE' : ' AND').' team_id = '.$team; |
|
| 491 | } |
|
| 492 | switch ($ranking) { |
|
| 493 | case 1: |
|
| 494 | $order = ' ORDER BY name, last_modified DESC'; |
|
| 495 | break; |
|
| 496 | case 2: |
|
| 497 | $order = ' ORDER BY total_cp DESC, last_modified DESC'; |
|
| 498 | break; |
|
| 499 | default: |
|
| 500 | $order = ' ORDER BY last_modified DESC, name'; |
|
| 501 | } |
|
| 502 | $req = "SELECT gymdetails.gym_id, name, team_id, total_cp, |
|
| 503 | (6 - slots_available) as pokemon_count, |
|
| 504 | CONVERT_TZ(last_modified, '+00:00', '".self::$time_offset."') as last_modified |
|
| 505 | FROM gymdetails |
|
| 506 | LEFT JOIN gym |
|
| 507 | ON gymdetails.gym_id = gym.gym_id |
|
| 508 | ".$where.$order." |
|
| 509 | LIMIT ".($page * 10).",10"; |
|
| 510 | $result = $this->mysqli->query($req); |
|
| 511 | $gym_history = array(); |
|
| 512 | while ($data = $result->fetch_object()) { |
|
| 513 | $gym_history[] = $data; |
|
| 514 | } |
|
| 515 | ||
| 516 | return $gym_history; |
|
| 517 | } |
|
| 518 | ||
| 519 | public function getGymHistoriesPokemon($gym_id) |
|
| 520 | { |
|
| 521 | $req = "SELECT DISTINCT gymmember.pokemon_uid, pokemon_id, cp, trainer_name |
|
| 522 | FROM gymmember |
|
| 523 | LEFT JOIN gympokemon |
|
| 524 | ON gymmember.pokemon_uid = gympokemon.pokemon_uid |
|
| 525 | WHERE gymmember.gym_id = '".$gym_id."' |
|
| 526 | ORDER BY deployment_time"; |
|
| 527 | $result = $this->mysqli->query($req); |
|
| 528 | $pokemons = array(); |
|
| 529 | while ($data = $result->fetch_object()) { |
|
| 530 | $pokemons[] = $data; |
|
| 531 | } |
|
| 532 | ||
| 533 | return $pokemons; |
|
| 534 | } |
|
| 535 | ||
| 536 | public function getHistoryForGym($page, $gym_id) |
|
| 537 | { |
|
| 538 | if (isset(self::$config->system->gymhistory_hide_cp_changes) && true === self::$config->system->gymhistory_hide_cp_changes) { |
|
| 539 | $pageSize = 25; |
|
| 540 | } else { |
|
| 541 | $pageSize = 10; |
|
| 542 | } |
|
| 543 | $req = "SELECT gym_id, team_id, total_cp, pokemon_uids, pokemon_count, |
|
| 544 | CONVERT_TZ(last_modified, '+00:00', '".self::$time_offset."') as last_modified |
|
| 545 | FROM gymhistory |
|
| 546 | WHERE gym_id='".$gym_id."' |
|
| 547 | ORDER BY last_modified DESC |
|
| 548 | LIMIT ".($page * $pageSize).','.($pageSize + 1); |
|
| 549 | $result = $this->mysqli->query($req); |
|
| 550 | $history = array(); |
|
| 551 | $count = 0; |
|
| 552 | while ($data = $result->fetch_object()) { |
|
| 553 | ++$count; |
|
| 554 | $pkm = array(); |
|
| 555 | if (0 == $data->total_cp) { |
|
| 556 | $data->pokemon_uids = ''; |
|
| 557 | $data->pokemon_count = 0; |
|
| 558 | } |
|
| 559 | if ('' != $data->pokemon_uids) { |
|
| 560 | $pkm_uids = explode(',', $data->pokemon_uids); |
|
| 561 | $pkm = $this->getHistoryForGymPokemon($pkm_uids); |
|
| 562 | } |
|
| 563 | $data->pokemon = $pkm; |
|
| 564 | $history[] = $data; |
|
| 565 | } |
|
| 566 | if ($count !== ($pageSize + 1)) { |
|
| 567 | $last_page = true; |
|
| 568 | } else { |
|
| 569 | $last_page = false; |
|
| 570 | } |
|
| 571 | ||
| 572 | return array('last_page' => $last_page, 'data' => $history); |
|
| 573 | } |
|
| 574 | ||
| 575 | private function getHistoryForGymPokemon($pkm_uids) |
|
| 576 | { |
|
| 577 | $req = "SELECT DISTINCT pokemon_uid, pokemon_id, cp, trainer_name |
|
| 578 | FROM gympokemon |
|
| 579 | WHERE pokemon_uid IN ('".implode("','", $pkm_uids)."') |
|
| 580 | ORDER BY FIND_IN_SET(pokemon_uid, '".implode(',', $pkm_uids)."')"; |
|
| 581 | $result = $this->mysqli->query($req); |
|
| 582 | $pokemons = array(); |
|
| 583 | while ($data = $result->fetch_object()) { |
|
| 584 | $pokemons[$data->pokemon_uid] = $data; |
|
| 585 | } |
|
| 586 | ||
| 587 | return $pokemons; |
|
| 588 | } |
|
| 589 | ||
| 590 | /////////// |
|
| 591 | // Raids |
|
| 592 | /////////// |
|
| 593 | ||
| 594 | public function getAllRaids($page) |
|
| 595 | { |
|
| 596 | $req = "SELECT raid.gym_id, raid.level, raid.pokemon_id, raid.cp, raid.move_1, raid.move_2, |
|
| 597 | CONVERT_TZ(raid.spawn, '+00:00', '".self::$time_offset."') AS spawn, |
|
| 598 | CONVERT_TZ(raid.start, '+00:00', '".self::$time_offset."') AS start, |
|
| 599 | CONVERT_TZ(raid.end, '+00:00', '".self::$time_offset."') AS end, |
|
| 600 | CONVERT_TZ(raid.last_scanned, '+00:00', '".self::$time_offset."') AS last_scanned, |
|
| 601 | gymdetails.name, gym.latitude, gym.longitude |
|
| 602 | FROM raid |
|
| 603 | JOIN gymdetails ON gymdetails.gym_id = raid.gym_id |
|
| 604 | JOIN gym ON gym.gym_id = raid.gym_id |
|
| 605 | WHERE raid.end > UTC_TIMESTAMP() |
|
| 606 | ORDER BY raid.level DESC, raid.start |
|
| 607 | LIMIT ".($page * 10).",10"; |
|
| 608 | $result = $this->mysqli->query($req); |
|
| 609 | $raids = array(); |
|
| 610 | while ($data = $result->fetch_object()) { |
|
| 611 | $raids[] = $data; |
|
| 612 | } |
|
| 613 | ||
| 614 | return $raids; |
|
| 615 | } |
|
| 616 | ||
| 617 | ////////////// |
|
| 618 | // Trainers |
|
| 619 | ////////////// |
|
| 620 | ||
| 621 | public function getTrainers($trainer_name, $team, $page, $ranking) |
|
| 622 | { |
|
| 623 | $trainers = $this->getTrainerData($trainer_name, $team, $page, $ranking); |
|
| 624 | foreach ($trainers as $trainer) { |
|
| 625 | $trainer->rank = $this->getTrainerLevelRating($trainer->level)->rank; |
|
| 626 | $active_gyms = 0; |
|
| 627 | $pkmCount = 0; |
|
| 628 | $trainer->pokemons = array(); |
|
| 629 | $active_pokemon = $this->getTrainerActivePokemon($trainer->name); |
|
| 630 | foreach ($active_pokemon as $pokemon) { |
|
| 631 | ++$active_gyms; |
|
| 632 | $trainer->pokemons[$pkmCount++] = $pokemon; |
|
| 633 | } |
|
| 634 | $inactive_pokemon = $this->getTrainerInactivePokemon($trainer->name); |
|
| 635 | foreach ($inactive_pokemon as $pokemon) { |
|
| 636 | $trainer->pokemons[$pkmCount++] = $pokemon; |
|
| 637 | } |
|
| 638 | $trainer->gyms = ''.$active_gyms; |
|
| 639 | } |
|
| 640 | ||
| 641 | return $trainers; |
|
| 642 | } |
|
| 643 | ||
| 644 | public function getTrainerLevelCount($team_id) |
|
| 645 | { |
|
| 646 | $req = "SELECT level, count(level) AS count FROM trainer WHERE team = '".$team_id."'"; |
|
| 647 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 648 | $req .= " AND name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 649 | } |
|
| 650 | $req .= ' GROUP BY level'; |
|
| 651 | $result = $this->mysqli->query($req); |
|
| 652 | $levelData = array(); |
|
| 653 | while ($data = $result->fetch_object()) { |
|
| 654 | $levelData[$data->level] = $data->count; |
|
| 655 | } |
|
| 656 | for ($i = 5; $i <= 40; ++$i) { |
|
| 657 | if (!isset($levelData[$i])) { |
|
| 658 | $levelData[$i] = 0; |
|
| 659 | } |
|
| 660 | } |
|
| 661 | // sort array again |
|
| 662 | ksort($levelData); |
|
| 663 | ||
| 664 | return $levelData; |
|
| 665 | } |
|
| 666 | ||
| 667 | private function getTrainerData($trainer_name, $team, $page, $ranking) |
|
| 668 | { |
|
| 669 | $where = ''; |
|
| 670 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 671 | $where .= ('' == $where ? ' HAVING' : ' AND')." name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 672 | } |
|
| 673 | if ('' != $trainer_name) { |
|
| 674 | $where = " HAVING name LIKE '%".$trainer_name."%'"; |
|
| 675 | } |
|
| 676 | if (0 != $team) { |
|
| 677 | $where .= ('' == $where ? ' HAVING' : ' AND').' team = '.$team; |
|
| 678 | } |
|
| 679 | switch ($ranking) { |
|
| 680 | case 1: |
|
| 681 | $order = ' ORDER BY active DESC, level DESC'; |
|
| 682 | break; |
|
| 683 | case 2: |
|
| 684 | $order = ' ORDER BY maxCp DESC, level DESC'; |
|
| 685 | break; |
|
| 686 | default: |
|
| 687 | $order = ' ORDER BY level DESC, active DESC'; |
|
| 688 | } |
|
| 689 | $order .= ', last_seen DESC, name '; |
|
| 690 | $limit = ' LIMIT '.($page * 10).',10 '; |
|
| 691 | $req = "SELECT trainer.*, COUNT(actives_pokemons.trainer_name) AS active, max(actives_pokemons.cp) AS maxCp |
|
| 692 | FROM trainer |
|
| 693 | LEFT JOIN (SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.trainer_name, gympokemon.cp, DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned |
|
| 694 | FROM gympokemon |
|
| 695 | INNER JOIN (SELECT gymmember.pokemon_uid, gymmember.gym_id FROM gymmember GROUP BY gymmember.pokemon_uid, gymmember.gym_id HAVING gymmember.gym_id <> '') AS filtered_gymmember |
|
| 696 | ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid) AS actives_pokemons ON actives_pokemons.trainer_name = trainer.name |
|
| 697 | GROUP BY trainer.name ".$where.$order.$limit; |
|
| 698 | $result = $this->mysqli->query($req); |
|
| 699 | $trainers = array(); |
|
| 700 | while ($data = $result->fetch_object()) { |
|
| 701 | $data->last_seen = date('Y-m-d', strtotime($data->last_seen)); |
|
| 702 | $trainers[$data->name] = $data; |
|
| 703 | } |
|
| 704 | ||
| 705 | return $trainers; |
|
| 706 | } |
|
| 707 | ||
| 708 | private function getTrainerLevelRating($level) |
|
| 709 | { |
|
| 710 | $req = 'SELECT COUNT(1) AS rank FROM trainer WHERE level = '.$level; |
|
| 711 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 712 | $req .= " AND name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 713 | } |
|
| 714 | $result = $this->mysqli->query($req); |
|
| 715 | $data = $result->fetch_object(); |
|
| 716 | ||
| 717 | return $data; |
|
| 718 | } |
|
| 719 | ||
| 720 | private function getTrainerActivePokemon($trainer_name) |
|
| 721 | { |
|
| 722 | $req = "SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.cp, |
|
| 723 | DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned, |
|
| 724 | gympokemon.trainer_name, gympokemon.iv_defense, gympokemon.iv_stamina, gympokemon.iv_attack, |
|
| 725 | filtered_gymmember.gym_id, |
|
| 726 | CONVERT_TZ(filtered_gymmember.deployment_time, '+00:00', '".self::$time_offset."') as deployment_time, |
|
| 727 | '1' AS active |
|
| 728 | FROM gympokemon INNER JOIN |
|
| 729 | (SELECT gymmember.pokemon_uid, gymmember.gym_id, gymmember.deployment_time FROM gymmember GROUP BY gymmember.pokemon_uid, gymmember.deployment_time, gymmember.gym_id HAVING gymmember.gym_id <> '') AS filtered_gymmember |
|
| 730 | ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid |
|
| 731 | WHERE gympokemon.trainer_name='".$trainer_name."' |
|
| 732 | ORDER BY gympokemon.cp DESC"; |
|
| 733 | $result = $this->mysqli->query($req); |
|
| 734 | $pokemons = array(); |
|
| 735 | while ($data = $result->fetch_object()) { |
|
| 736 | $pokemons[] = $data; |
|
| 737 | } |
|
| 738 | ||
| 739 | return $pokemons; |
|
| 740 | } |
|
| 741 | ||
| 742 | private function getTrainerInactivePokemon($trainer_name) |
|
| 743 | { |
|
| 744 | $req = "SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.cp, |
|
| 745 | DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned, |
|
| 746 | gympokemon.trainer_name, gympokemon.iv_defense, gympokemon.iv_stamina, gympokemon.iv_attack, |
|
| 747 | null AS gym_id, |
|
| 748 | CONVERT_TZ(filtered_gymmember.deployment_time, '+00:00', '".self::$time_offset."') as deployment_time, |
|
| 749 | '0' AS active |
|
| 750 | FROM gympokemon LEFT JOIN |
|
| 751 | (SELECT * FROM gymmember HAVING gymmember.gym_id <> '') AS filtered_gymmember |
|
| 752 | ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid |
|
| 753 | WHERE filtered_gymmember.pokemon_uid IS NULL AND gympokemon.trainer_name='".$trainer_name."' |
|
| 754 | ORDER BY gympokemon.cp DESC"; |
|
| 755 | $result = $this->mysqli->query($req); |
|
| 756 | $pokemons = array(); |
|
| 757 | while ($data = $result->fetch_object()) { |
|
| 758 | $pokemons[] = $data; |
|
| 759 | } |
|
| 760 | ||
| 761 | return $pokemons; |
|
| 762 | } |
|
| 763 | ||
| 764 | ///////// |
|
| 765 | // Cron |
|
| 766 | ///////// |
|
| 767 | ||
| 768 | public function getPokemonCountsActive() |
|
| 769 | { |
|
| 770 | $req = 'SELECT pokemon_id, COUNT(*) as total |
|
| 771 | FROM pokemon |
|
| 772 | WHERE disappear_time >= UTC_TIMESTAMP() |
|
| 773 | GROUP BY pokemon_id'; |
|
| 774 | $result = $this->mysqli->query($req); |
|
| 775 | $counts = array(); |
|
| 776 | while ($data = $result->fetch_object()) { |
|
| 777 | $counts[$data->pokemon_id] = $data->total; |
|
| 778 | } |
|
| 779 | ||
| 780 | return $counts; |
|
| 781 | } |
|
| 782 | ||
| 783 | ||
| 784 | public function getTotalPokemonIV() |
|
| 785 | { |
|
| 786 | $req = 'SELECT COUNT(*) as total |
|
| 787 | FROM pokemon |
|
| 788 | WHERE disappear_time >= UTC_TIMESTAMP() AND cp IS NOT NULL'; |
|
| 789 | $result = $this->mysqli->query($req); |
|
| 790 | $data = $result->fetch_object(); |
|
| 791 | ||
| 792 | return $data; |
|
| 793 | } |
|
| 794 | ||
| 795 | public function getPokemonCountsLastDay() |
|
| 796 | { |
|
| 797 | $req = 'SELECT pokemon_id, COUNT(*) AS spawns_last_day |
|
| 798 | FROM pokemon |
|
| 799 | WHERE disappear_time >= (SELECT MAX(disappear_time) FROM pokemon) - INTERVAL 1 DAY |
|
| 800 | GROUP BY pokemon_id |
|
| 801 | ORDER BY pokemon_id ASC'; |
|
| 802 | $result = $this->mysqli->query($req); |
|
| 803 | $counts = array(); |
|
| 804 | while ($data = $result->fetch_object()) { |
|
| 805 | $counts[$data->pokemon_id] = $data->spawns_last_day; |
|
| 806 | } |
|
| 807 | ||
| 808 | return $counts; |
|
| 809 | } |
|
| 810 | ||
| 811 | public function getCaptchaCount() |
|
| 812 | { |
|
| 813 | $req = 'SELECT SUM(accounts_captcha) AS total FROM mainworker'; |
|
| 814 | $result = $this->mysqli->query($req); |
|
| 815 | $data = $result->fetch_object(); |
|
| 816 | ||
| 817 | return $data; |
|
| 818 | } |
|
| 819 | ||
| 820 | public function getNestData($time, $minLatitude, $maxLatitude, $minLongitude, $maxLongitude) |
|
| 821 | { |
|
| 822 | $pokemon_exclude_sql = ''; |
|
| 823 | if (!empty(self::$config->system->nest_exclude_pokemon)) { |
|
| 824 | $pokemon_exclude_sql = 'AND pokemon_id NOT IN ('.implode(',', self::$config->system->nest_exclude_pokemon).')'; |
|
| 825 | } |
|
| 826 | $req = 'SELECT spawnpoint_id, pokemon_id, MAX(latitude) AS latitude, MAX(longitude) AS longitude, count(pokemon_id) AS total_pokemon, |
|
| 827 | MAX(UNIX_TIMESTAMP(disappear_time)) as latest_seen |
|
| 828 | FROM pokemon |
|
| 829 | WHERE disappear_time > (UTC_TIMESTAMP() - INTERVAL '.$time.' HOUR) |
|
| 830 | AND latitude >= '.$minLatitude.' AND latitude < '.$maxLatitude.' AND longitude >= '.$minLongitude.' AND longitude < '.$maxLongitude.' |
|
| 831 | '.$pokemon_exclude_sql.' |
|
| 832 | GROUP BY spawnpoint_id, pokemon_id |
|
| 833 | HAVING COUNT(pokemon_id) >= '.($time / 4).' |
|
| 834 | ORDER BY pokemon_id'; |
|
| 835 | $result = $this->mysqli->query($req); |
|
| 836 | $nests = array(); |
|
| 837 | while ($data = $result->fetch_object()) { |
|
| 838 | $nests[] = $data; |
|
| 839 | } |
|
| 840 | ||
| 841 | return $nests; |
|
| 842 | } |
|
| 843 | ||
| 844 | public function getSpawnpointCount($minLatitude, $maxLatitude, $minLongitude, $maxLongitude) |
|
| 845 | { |
|
| 846 | $req = 'SELECT COUNT(*) as total |
|
| 847 | FROM trs_spawn |
|
| 848 | WHERE latitude >= '.$minLatitude.' AND latitude < '.$maxLatitude.' AND longitude >= '.$minLongitude.' AND longitude < '.$maxLongitude; |
|
| 849 | $result = $this->mysqli->query($req); |
|
| 850 | $data = $result->fetch_object(); |
|
| 851 | ||
| 852 | return $data; |
|
| 853 | } |
|
| 854 | } |
|
| 855 | ||
| @@ 5-854 (lines=850) @@ | ||
| 2 | ||
| 3 | namespace Worldopole; |
|
| 4 | ||
| 5 | final class QueryManagerMysqlRocketmap extends QueryManagerMysql |
|
| 6 | { |
|
| 7 | public function __construct() |
|
| 8 | { |
|
| 9 | parent::__construct(); |
|
| 10 | } |
|
| 11 | ||
| 12 | public function __destruct() |
|
| 13 | { |
|
| 14 | parent::__destruct(); |
|
| 15 | } |
|
| 16 | ||
| 17 | /////////// |
|
| 18 | // Tester |
|
| 19 | /////////// |
|
| 20 | ||
| 21 | public function testTotalPokemon() |
|
| 22 | { |
|
| 23 | $req = 'SELECT COUNT(*) as total FROM pokemon'; |
|
| 24 | $result = $this->mysqli->query($req); |
|
| 25 | if (!is_object($result)) { |
|
| 26 | return 1; |
|
| 27 | } else { |
|
| 28 | $data = $result->fetch_object(); |
|
| 29 | $total = $data->total; |
|
| 30 | ||
| 31 | if (0 == $total) { |
|
| 32 | return 2; |
|
| 33 | } |
|
| 34 | } |
|
| 35 | ||
| 36 | return 0; |
|
| 37 | } |
|
| 38 | ||
| 39 | public function testTotalGyms() |
|
| 40 | { |
|
| 41 | $req = 'SELECT COUNT(*) as total FROM gym'; |
|
| 42 | $result = $this->mysqli->query($req); |
|
| 43 | if (!is_object($result)) { |
|
| 44 | return 1; |
|
| 45 | } else { |
|
| 46 | $data = $result->fetch_object(); |
|
| 47 | $total = $data->total; |
|
| 48 | ||
| 49 | if (0 == $total) { |
|
| 50 | return 2; |
|
| 51 | } |
|
| 52 | } |
|
| 53 | ||
| 54 | return 0; |
|
| 55 | } |
|
| 56 | ||
| 57 | public function testTotalPokestops() |
|
| 58 | { |
|
| 59 | $req = 'SELECT COUNT(*) as total FROM pokestop'; |
|
| 60 | $result = $this->mysqli->query($req); |
|
| 61 | if (!is_object($result)) { |
|
| 62 | return 1; |
|
| 63 | } else { |
|
| 64 | $data = $result->fetch_object(); |
|
| 65 | $total = $data->total; |
|
| 66 | ||
| 67 | if (0 == $total) { |
|
| 68 | return 2; |
|
| 69 | } |
|
| 70 | } |
|
| 71 | ||
| 72 | return 0; |
|
| 73 | } |
|
| 74 | ||
| 75 | ///////////// |
|
| 76 | // Homepage |
|
| 77 | ///////////// |
|
| 78 | ||
| 79 | public function getTotalPokemon() |
|
| 80 | { |
|
| 81 | $req = 'SELECT COUNT(*) AS total FROM pokemon WHERE disappear_time >= UTC_TIMESTAMP()'; |
|
| 82 | $result = $this->mysqli->query($req); |
|
| 83 | $data = $result->fetch_object(); |
|
| 84 | ||
| 85 | return $data; |
|
| 86 | } |
|
| 87 | ||
| 88 | public function getTotalLures() |
|
| 89 | { |
|
| 90 | $req = 'SELECT COUNT(*) AS total FROM pokestop WHERE lure_expiration >= UTC_TIMESTAMP()'; |
|
| 91 | $result = $this->mysqli->query($req); |
|
| 92 | $data = $result->fetch_object(); |
|
| 93 | ||
| 94 | return $data; |
|
| 95 | } |
|
| 96 | ||
| 97 | public function getTotalGyms() |
|
| 98 | { |
|
| 99 | $req = 'SELECT COUNT(DISTINCT(gym_id)) AS total FROM gym'; |
|
| 100 | $result = $this->mysqli->query($req); |
|
| 101 | $data = $result->fetch_object(); |
|
| 102 | ||
| 103 | return $data; |
|
| 104 | } |
|
| 105 | ||
| 106 | public function getTotalRaids() |
|
| 107 | { |
|
| 108 | $req = 'SELECT COUNT(*) AS total FROM raid WHERE start <= UTC_TIMESTAMP() AND end >= UTC_TIMESTAMP()'; |
|
| 109 | $result = $this->mysqli->query($req); |
|
| 110 | $data = $result->fetch_object(); |
|
| 111 | ||
| 112 | return $data; |
|
| 113 | } |
|
| 114 | ||
| 115 | public function getTotalGymsForTeam($team_id) |
|
| 116 | { |
|
| 117 | $req = "SELECT COUNT(DISTINCT(gym_id)) AS total FROM gym WHERE team_id = '".$team_id."'"; |
|
| 118 | $result = $this->mysqli->query($req); |
|
| 119 | $data = $result->fetch_object(); |
|
| 120 | ||
| 121 | return $data; |
|
| 122 | } |
|
| 123 | ||
| 124 | public function getRecentAll() |
|
| 125 | { |
|
| 126 | $req = "SELECT DISTINCT pokemon_id, encounter_id, disappear_time, last_modified, |
|
| 127 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 128 | latitude, longitude, cp, individual_attack, individual_defense, individual_stamina |
|
| 129 | FROM pokemon |
|
| 130 | ORDER BY last_modified DESC |
|
| 131 | LIMIT 0,12"; |
|
| 132 | $result = $this->mysqli->query($req); |
|
| 133 | $data = array(); |
|
| 134 | if ($result->num_rows > 0) { |
|
| 135 | while ($row = $result->fetch_object()) { |
|
| 136 | $data[] = $row; |
|
| 137 | } |
|
| 138 | } |
|
| 139 | ||
| 140 | return $data; |
|
| 141 | } |
|
| 142 | ||
| 143 | public function getRecentMythic($mythic_pokemons) |
|
| 144 | { |
|
| 145 | $req = "SELECT DISTINCT pokemon_id, encounter_id, disappear_time, last_modified, |
|
| 146 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 147 | latitude, longitude, cp, individual_attack, individual_defense, individual_stamina |
|
| 148 | FROM pokemon |
|
| 149 | WHERE pokemon_id IN (".implode(',', $mythic_pokemons).') |
|
| 150 | ORDER BY last_modified DESC |
|
| 151 | LIMIT 0,12'; |
|
| 152 | $result = $this->mysqli->query($req); |
|
| 153 | $data = array(); |
|
| 154 | if ($result->num_rows > 0) { |
|
| 155 | while ($row = $result->fetch_object()) { |
|
| 156 | $data[] = $row; |
|
| 157 | } |
|
| 158 | } |
|
| 159 | ||
| 160 | return $data; |
|
| 161 | } |
|
| 162 | ||
| 163 | /////////////////// |
|
| 164 | // Single Pokemon |
|
| 165 | /////////////////// |
|
| 166 | ||
| 167 | public function getGymsProtectedByPokemon($pokemon_id) |
|
| 168 | { |
|
| 169 | $req = "SELECT COUNT(DISTINCT(gym_id)) AS total FROM gym WHERE guard_pokemon_id = '".$pokemon_id."'"; |
|
| 170 | $result = $this->mysqli->query($req); |
|
| 171 | $data = $result->fetch_object(); |
|
| 172 | ||
| 173 | return $data; |
|
| 174 | } |
|
| 175 | ||
| 176 | public function getPokemonLastSeen($pokemon_id) |
|
| 177 | { |
|
| 178 | $req = "SELECT disappear_time, |
|
| 179 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 180 | latitude, longitude |
|
| 181 | FROM pokemon |
|
| 182 | WHERE pokemon_id = '".$pokemon_id."' |
|
| 183 | ORDER BY disappear_time DESC |
|
| 184 | LIMIT 0,1"; |
|
| 185 | $result = $this->mysqli->query($req); |
|
| 186 | $data = $result->fetch_object(); |
|
| 187 | ||
| 188 | return $data; |
|
| 189 | } |
|
| 190 | ||
| 191 | public function getTop50Pokemon($pokemon_id, $top_order_by, $top_direction) |
|
| 192 | { |
|
| 193 | $req = "SELECT CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS distime, |
|
| 194 | pokemon_id, disappear_time, latitude, longitude, |
|
| 195 | cp, individual_attack, individual_defense, individual_stamina, |
|
| 196 | ROUND(100*(individual_attack+individual_defense+individual_stamina)/45,1) AS IV, |
|
| 197 | move_1, move_2, form |
|
| 198 | FROM pokemon |
|
| 199 | WHERE pokemon_id = '".$pokemon_id."' AND move_1 IS NOT NULL AND move_1 <> '0' |
|
| 200 | ORDER BY $top_order_by $top_direction, disappear_time DESC |
|
| 201 | LIMIT 0,50"; |
|
| 202 | $result = $this->mysqli->query($req); |
|
| 203 | $top = array(); |
|
| 204 | while ($data = $result->fetch_object()) { |
|
| 205 | $top[] = $data; |
|
| 206 | } |
|
| 207 | ||
| 208 | return $top; |
|
| 209 | } |
|
| 210 | ||
| 211 | public function getTop50Trainers($pokemon_id, $best_order_by, $best_direction) |
|
| 212 | { |
|
| 213 | $trainer_blacklist = ''; |
|
| 214 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 215 | $trainer_blacklist = " AND trainer_name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 216 | } |
|
| 217 | $req = "SELECT trainer_name, |
|
| 218 | ROUND((100*(iv_attack+iv_defense+iv_stamina)/45),1) AS IV, |
|
| 219 | move_1, move_2, cp, |
|
| 220 | DATE_FORMAT(last_seen, '%Y-%m-%d') AS lasttime, last_seen |
|
| 221 | FROM gympokemon |
|
| 222 | WHERE pokemon_id = '".$pokemon_id."'".$trainer_blacklist." |
|
| 223 | ORDER BY $best_order_by $best_direction, trainer_name ASC |
|
| 224 | LIMIT 0,50"; |
|
| 225 | $result = $this->mysqli->query($req); |
|
| 226 | $toptrainer = array(); |
|
| 227 | while ($data = $result->fetch_object()) { |
|
| 228 | $toptrainer[] = $data; |
|
| 229 | } |
|
| 230 | ||
| 231 | return $toptrainer; |
|
| 232 | } |
|
| 233 | ||
| 234 | public function getPokemonHeatmap($pokemon_id, $start, $end) |
|
| 235 | { |
|
| 236 | $req = "SELECT latitude, longitude |
|
| 237 | FROM pokemon |
|
| 238 | WHERE pokemon_id = ".$pokemon_id." AND disappear_time BETWEEN '".$start."' AND '".$end."' |
|
| 239 | LIMIT 10000"; |
|
| 240 | $result = $this->mysqli->query($req); |
|
| 241 | $points = array(); |
|
| 242 | while ($data = $result->fetch_object()) { |
|
| 243 | $points[] = $data; |
|
| 244 | } |
|
| 245 | ||
| 246 | return $points; |
|
| 247 | } |
|
| 248 | ||
| 249 | public function getPokemonGraph($pokemon_id) |
|
| 250 | { |
|
| 251 | $req = "SELECT COUNT(*) AS total, |
|
| 252 | HOUR(CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."')) AS disappear_hour |
|
| 253 | FROM (SELECT disappear_time FROM pokemon WHERE pokemon_id = '".$pokemon_id."' LIMIT 100000) AS pokemonFiltered |
|
| 254 | GROUP BY disappear_hour |
|
| 255 | ORDER BY disappear_hour"; |
|
| 256 | $result = $this->mysqli->query($req); |
|
| 257 | $array = array_fill(0, 24, 0); |
|
| 258 | while ($result && $data = $result->fetch_object()) { |
|
| 259 | $array[$data->disappear_hour] = $data->total; |
|
| 260 | } |
|
| 261 | // shift array because AM/PM starts at 1AM not 0:00 |
|
| 262 | $array[] = $array[0]; |
|
| 263 | array_shift($array); |
|
| 264 | ||
| 265 | return $array; |
|
| 266 | } |
|
| 267 | ||
| 268 | public function getPokemonLive($pokemon_id, $ivMin, $ivMax, $inmap_pokemons) |
|
| 269 | { |
|
| 270 | $inmap_pkms_filter = ''; |
|
| 271 | $where = ' WHERE disappear_time >= UTC_TIMESTAMP() AND pokemon_id = '.$pokemon_id; |
|
| 272 | $reqTestIv = 'SELECT MAX(individual_attack) AS iv FROM pokemon '.$where; |
|
| 273 | $resultTestIv = $this->mysqli->query($reqTestIv); |
|
| 274 | $testIv = $resultTestIv->fetch_object(); |
|
| 275 | if (!is_null($inmap_pokemons) && ('' != $inmap_pokemons)) { |
|
| 276 | foreach ($inmap_pokemons as $inmap) { |
|
| 277 | $inmap_pkms_filter .= "'".$inmap."',"; |
|
| 278 | } |
|
| 279 | $inmap_pkms_filter = rtrim($inmap_pkms_filter, ','); |
|
| 280 | $where .= ' AND encounter_id NOT IN ('.$inmap_pkms_filter.') '; |
|
| 281 | } |
|
| 282 | if (null != $testIv->iv && !is_null($ivMin) && ('' != $ivMin)) { |
|
| 283 | $where .= ' AND ((100/45)*(individual_attack+individual_defense+individual_stamina)) >= ('.$ivMin.') '; |
|
| 284 | } |
|
| 285 | if (null != $testIv->iv && !is_null($ivMax) && ('' != $ivMax)) { |
|
| 286 | $where .= ' AND ((100/45)*(individual_attack+individual_defense+individual_stamina)) <= ('.$ivMax.') '; |
|
| 287 | } |
|
| 288 | $req = "SELECT pokemon_id, encounter_id, latitude, longitude, disappear_time, |
|
| 289 | CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS disappear_time_real, |
|
| 290 | individual_attack, individual_defense, individual_stamina, move_1, move_2 |
|
| 291 | FROM pokemon ".$where.' |
|
| 292 | LIMIT 5000'; |
|
| 293 | $result = $this->mysqli->query($req); |
|
| 294 | $spawns = array(); |
|
| 295 | while ($data = $result->fetch_object()) { |
|
| 296 | $spawns[] = $data; |
|
| 297 | } |
|
| 298 | ||
| 299 | return $spawns; |
|
| 300 | } |
|
| 301 | ||
| 302 | public function getPokemonSliderMinMax() |
|
| 303 | { |
|
| 304 | $req = 'SELECT MIN(disappear_time) AS min, MAX(disappear_time) AS max FROM pokemon'; |
|
| 305 | $result = $this->mysqli->query($req); |
|
| 306 | $data = $result->fetch_object(); |
|
| 307 | ||
| 308 | return $data; |
|
| 309 | } |
|
| 310 | ||
| 311 | public function getMapsCoords() |
|
| 312 | { |
|
| 313 | $req = 'SELECT MAX(latitude) AS max_latitude, MIN(latitude) AS min_latitude, |
|
| 314 | MAX(longitude) AS max_longitude, MIN(longitude) as min_longitude |
|
| 315 | FROM spawnpoint'; |
|
| 316 | $result = $this->mysqli->query($req); |
|
| 317 | $data = $result->fetch_object(); |
|
| 318 | ||
| 319 | return $data; |
|
| 320 | } |
|
| 321 | ||
| 322 | public function getPokemonCount($pokemon_id) |
|
| 323 | { |
|
| 324 | $req = 'SELECT count, last_seen, latitude, longitude |
|
| 325 | FROM pokemon_stats |
|
| 326 | WHERE pid = '.$pokemon_id; |
|
| 327 | $result = $this->mysqli->query($req); |
|
| 328 | $data = $result->fetch_object(); |
|
| 329 | ||
| 330 | return $data; |
|
| 331 | } |
|
| 332 | ||
| 333 | public function getPokemonCountAll() |
|
| 334 | { |
|
| 335 | $req = 'SELECT pid as pokemon_id, count, last_seen, latitude, longitude |
|
| 336 | FROM pokemon_stats |
|
| 337 | GROUP BY pid'; |
|
| 338 | $result = $this->mysqli->query($req); |
|
| 339 | $array = array(); |
|
| 340 | while ($data = $result->fetch_object()) { |
|
| 341 | $array[] = $data; |
|
| 342 | } |
|
| 343 | ||
| 344 | return $array; |
|
| 345 | } |
|
| 346 | ||
| 347 | public function getRaidCount($pokemon_id) |
|
| 348 | { |
|
| 349 | $req = 'SELECT count, last_seen, latitude, longitude |
|
| 350 | FROM raid_stats |
|
| 351 | WHERE pid = '.$pokemon_id; |
|
| 352 | $result = $this->mysqli->query($req); |
|
| 353 | $data = $result->fetch_object(); |
|
| 354 | ||
| 355 | return $data; |
|
| 356 | } |
|
| 357 | ||
| 358 | public function getRaidCountAll() |
|
| 359 | { |
|
| 360 | $req = 'SELECT pid as pokemon_id, count, last_seen, latitude, longitude |
|
| 361 | FROM raid_stats |
|
| 362 | GROUP BY pid'; |
|
| 363 | $result = $this->mysqli->query($req); |
|
| 364 | $array = array(); |
|
| 365 | while ($data = $result->fetch_object()) { |
|
| 366 | $array[] = $data; |
|
| 367 | } |
|
| 368 | ||
| 369 | return $array; |
|
| 370 | } |
|
| 371 | ||
| 372 | /////////////// |
|
| 373 | // Pokestops |
|
| 374 | ////////////// |
|
| 375 | ||
| 376 | public function getTotalPokestops() |
|
| 377 | { |
|
| 378 | $req = 'SELECT COUNT(*) as total FROM pokestop'; |
|
| 379 | $result = $this->mysqli->query($req); |
|
| 380 | $data = $result->fetch_object(); |
|
| 381 | ||
| 382 | return $data; |
|
| 383 | } |
|
| 384 | ||
| 385 | public function getAllPokestops() |
|
| 386 | { |
|
| 387 | $req = "SELECT latitude, longitude, lure_expiration, UTC_TIMESTAMP() AS now, |
|
| 388 | CONVERT_TZ(lure_expiration, '+00:00', '".self::$time_offset."') AS lure_expiration_real |
|
| 389 | FROM pokestop"; |
|
| 390 | $result = $this->mysqli->query($req); |
|
| 391 | $pokestops = array(); |
|
| 392 | while ($data = $result->fetch_object()) { |
|
| 393 | $pokestops[] = $data; |
|
| 394 | } |
|
| 395 | ||
| 396 | return $pokestops; |
|
| 397 | } |
|
| 398 | ||
| 399 | ///////// |
|
| 400 | // Gyms |
|
| 401 | ///////// |
|
| 402 | ||
| 403 | public function getTeamGuardians($team_id) |
|
| 404 | { |
|
| 405 | $req = "SELECT COUNT(*) AS total, guard_pokemon_id |
|
| 406 | FROM gym WHERE team_id = '".$team_id."' |
|
| 407 | GROUP BY guard_pokemon_id |
|
| 408 | ORDER BY total DESC |
|
| 409 | LIMIT 0,3"; |
|
| 410 | $result = $this->mysqli->query($req); |
|
| 411 | $datas = array(); |
|
| 412 | while ($data = $result->fetch_object()) { |
|
| 413 | $datas[] = $data; |
|
| 414 | } |
|
| 415 | ||
| 416 | return $datas; |
|
| 417 | } |
|
| 418 | ||
| 419 | public function getOwnedAndPoints($team_id) |
|
| 420 | { |
|
| 421 | $req = "SELECT COUNT(DISTINCT(gym_id)) AS total, |
|
| 422 | ROUND(AVG(total_cp),0) AS average_points |
|
| 423 | FROM gym |
|
| 424 | WHERE team_id = '".$team_id."'"; |
|
| 425 | $result = $this->mysqli->query($req); |
|
| 426 | $data = $result->fetch_object(); |
|
| 427 | ||
| 428 | return $data; |
|
| 429 | } |
|
| 430 | ||
| 431 | public function getAllGyms() |
|
| 432 | { |
|
| 433 | $req = "SELECT gym_id, team_id, latitude, longitude, |
|
| 434 | CONVERT_TZ(last_scanned, '+00:00', '".self::$time_offset."') AS last_scanned, |
|
| 435 | (6 - slots_available) AS level |
|
| 436 | FROM gym"; |
|
| 437 | $result = $this->mysqli->query($req); |
|
| 438 | $gyms = array(); |
|
| 439 | while ($data = $result->fetch_object()) { |
|
| 440 | $gyms[] = $data; |
|
| 441 | } |
|
| 442 | ||
| 443 | return $gyms; |
|
| 444 | } |
|
| 445 | ||
| 446 | public function getGymData($gym_id) |
|
| 447 | { |
|
| 448 | $req = "SELECT gymdetails.name AS name, gymdetails.description AS description, gymdetails.url AS url, gym.team_id AS team, |
|
| 449 | CONVERT_TZ(gym.last_scanned, '+00:00', '".self::$time_offset."') AS last_scanned, |
|
| 450 | gym.guard_pokemon_id AS guard_pokemon_id, |
|
| 451 | gym.total_cp AS total_cp, |
|
| 452 | (6 - gym.slots_available) AS level |
|
| 453 | FROM gymdetails |
|
| 454 | LEFT JOIN gym ON gym.gym_id = gymdetails.gym_id |
|
| 455 | WHERE gym.gym_id='".$gym_id."'"; |
|
| 456 | $result = $this->mysqli->query($req); |
|
| 457 | $data = $result->fetch_object(); |
|
| 458 | ||
| 459 | return $data; |
|
| 460 | } |
|
| 461 | ||
| 462 | public function getGymDefenders($gym_id) |
|
| 463 | { |
|
| 464 | $req = "SELECT DISTINCT gympokemon.pokemon_uid, pokemon_id, iv_attack, iv_defense, iv_stamina, MAX(cp) AS cp, gymmember.gym_id |
|
| 465 | FROM gympokemon |
|
| 466 | INNER JOIN gymmember ON gympokemon.pokemon_uid=gymmember.pokemon_uid |
|
| 467 | GROUP BY gympokemon.pokemon_uid, pokemon_id, iv_attack, iv_defense, iv_stamina, gym_id |
|
| 468 | HAVING gymmember.gym_id='".$gym_id."' |
|
| 469 | ORDER BY cp DESC"; |
|
| 470 | $result = $this->mysqli->query($req); |
|
| 471 | $defenders = array(); |
|
| 472 | while ($data = $result->fetch_object()) { |
|
| 473 | $defenders[] = $data; |
|
| 474 | } |
|
| 475 | ||
| 476 | return $defenders; |
|
| 477 | } |
|
| 478 | ||
| 479 | //////////////// |
|
| 480 | // Gym History |
|
| 481 | //////////////// |
|
| 482 | ||
| 483 | public function getGymHistories($gym_name, $team, $page, $ranking) |
|
| 484 | { |
|
| 485 | $where = ''; |
|
| 486 | if (isset($gym_name) && '' != $gym_name) { |
|
| 487 | $where = " WHERE name LIKE '%".$gym_name."%'"; |
|
| 488 | } |
|
| 489 | if (isset($team) && '' != $team) { |
|
| 490 | $where .= ('' == $where ? ' WHERE' : ' AND').' team_id = '.$team; |
|
| 491 | } |
|
| 492 | switch ($ranking) { |
|
| 493 | case 1: |
|
| 494 | $order = ' ORDER BY name, last_modified DESC'; |
|
| 495 | break; |
|
| 496 | case 2: |
|
| 497 | $order = ' ORDER BY total_cp DESC, last_modified DESC'; |
|
| 498 | break; |
|
| 499 | default: |
|
| 500 | $order = ' ORDER BY last_modified DESC, name'; |
|
| 501 | } |
|
| 502 | $req = "SELECT gymdetails.gym_id, name, team_id, total_cp, |
|
| 503 | (6 - slots_available) as pokemon_count, |
|
| 504 | CONVERT_TZ(last_modified, '+00:00', '".self::$time_offset."') as last_modified |
|
| 505 | FROM gymdetails |
|
| 506 | LEFT JOIN gym |
|
| 507 | ON gymdetails.gym_id = gym.gym_id |
|
| 508 | ".$where.$order." |
|
| 509 | LIMIT ".($page * 10).",10"; |
|
| 510 | $result = $this->mysqli->query($req); |
|
| 511 | $gym_history = array(); |
|
| 512 | while ($data = $result->fetch_object()) { |
|
| 513 | $gym_history[] = $data; |
|
| 514 | } |
|
| 515 | ||
| 516 | return $gym_history; |
|
| 517 | } |
|
| 518 | ||
| 519 | public function getGymHistoriesPokemon($gym_id) |
|
| 520 | { |
|
| 521 | $req = "SELECT DISTINCT gymmember.pokemon_uid, pokemon_id, cp, trainer_name |
|
| 522 | FROM gymmember |
|
| 523 | LEFT JOIN gympokemon |
|
| 524 | ON gymmember.pokemon_uid = gympokemon.pokemon_uid |
|
| 525 | WHERE gymmember.gym_id = '".$gym_id."' |
|
| 526 | ORDER BY deployment_time"; |
|
| 527 | $result = $this->mysqli->query($req); |
|
| 528 | $pokemons = array(); |
|
| 529 | while ($data = $result->fetch_object()) { |
|
| 530 | $pokemons[] = $data; |
|
| 531 | } |
|
| 532 | ||
| 533 | return $pokemons; |
|
| 534 | } |
|
| 535 | ||
| 536 | public function getHistoryForGym($page, $gym_id) |
|
| 537 | { |
|
| 538 | if (isset(self::$config->system->gymhistory_hide_cp_changes) && true === self::$config->system->gymhistory_hide_cp_changes) { |
|
| 539 | $pageSize = 25; |
|
| 540 | } else { |
|
| 541 | $pageSize = 10; |
|
| 542 | } |
|
| 543 | $req = "SELECT gym_id, team_id, total_cp, pokemon_uids, pokemon_count, |
|
| 544 | CONVERT_TZ(last_modified, '+00:00', '".self::$time_offset."') as last_modified |
|
| 545 | FROM gymhistory |
|
| 546 | WHERE gym_id='".$gym_id."' |
|
| 547 | ORDER BY last_modified DESC |
|
| 548 | LIMIT ".($page * $pageSize).','.($pageSize + 1); |
|
| 549 | $result = $this->mysqli->query($req); |
|
| 550 | $history = array(); |
|
| 551 | $count = 0; |
|
| 552 | while ($data = $result->fetch_object()) { |
|
| 553 | ++$count; |
|
| 554 | $pkm = array(); |
|
| 555 | if (0 == $data->total_cp) { |
|
| 556 | $data->pokemon_uids = ''; |
|
| 557 | $data->pokemon_count = 0; |
|
| 558 | } |
|
| 559 | if ('' != $data->pokemon_uids) { |
|
| 560 | $pkm_uids = explode(',', $data->pokemon_uids); |
|
| 561 | $pkm = $this->getHistoryForGymPokemon($pkm_uids); |
|
| 562 | } |
|
| 563 | $data->pokemon = $pkm; |
|
| 564 | $history[] = $data; |
|
| 565 | } |
|
| 566 | if ($count !== ($pageSize + 1)) { |
|
| 567 | $last_page = true; |
|
| 568 | } else { |
|
| 569 | $last_page = false; |
|
| 570 | } |
|
| 571 | ||
| 572 | return array('last_page' => $last_page, 'data' => $history); |
|
| 573 | } |
|
| 574 | ||
| 575 | private function getHistoryForGymPokemon($pkm_uids) |
|
| 576 | { |
|
| 577 | $req = "SELECT DISTINCT pokemon_uid, pokemon_id, cp, trainer_name |
|
| 578 | FROM gympokemon |
|
| 579 | WHERE pokemon_uid IN ('".implode("','", $pkm_uids)."') |
|
| 580 | ORDER BY FIND_IN_SET(pokemon_uid, '".implode(',', $pkm_uids)."')"; |
|
| 581 | $result = $this->mysqli->query($req); |
|
| 582 | $pokemons = array(); |
|
| 583 | while ($data = $result->fetch_object()) { |
|
| 584 | $pokemons[$data->pokemon_uid] = $data; |
|
| 585 | } |
|
| 586 | ||
| 587 | return $pokemons; |
|
| 588 | } |
|
| 589 | ||
| 590 | /////////// |
|
| 591 | // Raids |
|
| 592 | /////////// |
|
| 593 | ||
| 594 | public function getAllRaids($page) |
|
| 595 | { |
|
| 596 | $req = "SELECT raid.gym_id, raid.level, raid.pokemon_id, raid.cp, raid.move_1, raid.move_2, |
|
| 597 | CONVERT_TZ(raid.spawn, '+00:00', '".self::$time_offset."') AS spawn, |
|
| 598 | CONVERT_TZ(raid.start, '+00:00', '".self::$time_offset."') AS start, |
|
| 599 | CONVERT_TZ(raid.end, '+00:00', '".self::$time_offset."') AS end, |
|
| 600 | CONVERT_TZ(raid.last_scanned, '+00:00', '".self::$time_offset."') AS last_scanned, |
|
| 601 | gymdetails.name, gym.latitude, gym.longitude |
|
| 602 | FROM raid |
|
| 603 | JOIN gymdetails ON gymdetails.gym_id = raid.gym_id |
|
| 604 | JOIN gym ON gym.gym_id = raid.gym_id |
|
| 605 | WHERE raid.end > UTC_TIMESTAMP() |
|
| 606 | ORDER BY raid.level DESC, raid.start |
|
| 607 | LIMIT ".($page * 10).",10"; |
|
| 608 | $result = $this->mysqli->query($req); |
|
| 609 | $raids = array(); |
|
| 610 | while ($data = $result->fetch_object()) { |
|
| 611 | $raids[] = $data; |
|
| 612 | } |
|
| 613 | ||
| 614 | return $raids; |
|
| 615 | } |
|
| 616 | ||
| 617 | ////////////// |
|
| 618 | // Trainers |
|
| 619 | ////////////// |
|
| 620 | ||
| 621 | public function getTrainers($trainer_name, $team, $page, $ranking) |
|
| 622 | { |
|
| 623 | $trainers = $this->getTrainerData($trainer_name, $team, $page, $ranking); |
|
| 624 | foreach ($trainers as $trainer) { |
|
| 625 | $trainer->rank = $this->getTrainerLevelRating($trainer->level)->rank; |
|
| 626 | $active_gyms = 0; |
|
| 627 | $pkmCount = 0; |
|
| 628 | $trainer->pokemons = array(); |
|
| 629 | $active_pokemon = $this->getTrainerActivePokemon($trainer->name); |
|
| 630 | foreach ($active_pokemon as $pokemon) { |
|
| 631 | ++$active_gyms; |
|
| 632 | $trainer->pokemons[$pkmCount++] = $pokemon; |
|
| 633 | } |
|
| 634 | $inactive_pokemon = $this->getTrainerInactivePokemon($trainer->name); |
|
| 635 | foreach ($inactive_pokemon as $pokemon) { |
|
| 636 | $trainer->pokemons[$pkmCount++] = $pokemon; |
|
| 637 | } |
|
| 638 | $trainer->gyms = ''.$active_gyms; |
|
| 639 | } |
|
| 640 | ||
| 641 | return $trainers; |
|
| 642 | } |
|
| 643 | ||
| 644 | public function getTrainerLevelCount($team_id) |
|
| 645 | { |
|
| 646 | $req = "SELECT level, count(level) AS count FROM trainer WHERE team = '".$team_id."'"; |
|
| 647 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 648 | $req .= " AND name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 649 | } |
|
| 650 | $req .= ' GROUP BY level'; |
|
| 651 | $result = $this->mysqli->query($req); |
|
| 652 | $levelData = array(); |
|
| 653 | while ($data = $result->fetch_object()) { |
|
| 654 | $levelData[$data->level] = $data->count; |
|
| 655 | } |
|
| 656 | for ($i = 5; $i <= 40; ++$i) { |
|
| 657 | if (!isset($levelData[$i])) { |
|
| 658 | $levelData[$i] = 0; |
|
| 659 | } |
|
| 660 | } |
|
| 661 | // sort array again |
|
| 662 | ksort($levelData); |
|
| 663 | ||
| 664 | return $levelData; |
|
| 665 | } |
|
| 666 | ||
| 667 | private function getTrainerData($trainer_name, $team, $page, $ranking) |
|
| 668 | { |
|
| 669 | $where = ''; |
|
| 670 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 671 | $where .= ('' == $where ? ' HAVING' : ' AND')." name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 672 | } |
|
| 673 | if ('' != $trainer_name) { |
|
| 674 | $where = " HAVING name LIKE '%".$trainer_name."%'"; |
|
| 675 | } |
|
| 676 | if (0 != $team) { |
|
| 677 | $where .= ('' == $where ? ' HAVING' : ' AND').' team = '.$team; |
|
| 678 | } |
|
| 679 | switch ($ranking) { |
|
| 680 | case 1: |
|
| 681 | $order = ' ORDER BY active DESC, level DESC'; |
|
| 682 | break; |
|
| 683 | case 2: |
|
| 684 | $order = ' ORDER BY maxCp DESC, level DESC'; |
|
| 685 | break; |
|
| 686 | default: |
|
| 687 | $order = ' ORDER BY level DESC, active DESC'; |
|
| 688 | } |
|
| 689 | $order .= ', last_seen DESC, name '; |
|
| 690 | $limit = ' LIMIT '.($page * 10).',10 '; |
|
| 691 | $req = "SELECT trainer.*, COUNT(actives_pokemons.trainer_name) AS active, max(actives_pokemons.cp) AS maxCp |
|
| 692 | FROM trainer |
|
| 693 | LEFT JOIN (SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.trainer_name, gympokemon.cp, DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned |
|
| 694 | FROM gympokemon |
|
| 695 | INNER JOIN (SELECT gymmember.pokemon_uid, gymmember.gym_id FROM gymmember GROUP BY gymmember.pokemon_uid, gymmember.gym_id HAVING gymmember.gym_id <> '') AS filtered_gymmember |
|
| 696 | ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid) AS actives_pokemons ON actives_pokemons.trainer_name = trainer.name |
|
| 697 | GROUP BY trainer.name ".$where.$order.$limit; |
|
| 698 | $result = $this->mysqli->query($req); |
|
| 699 | $trainers = array(); |
|
| 700 | while ($data = $result->fetch_object()) { |
|
| 701 | $data->last_seen = date('Y-m-d', strtotime($data->last_seen)); |
|
| 702 | $trainers[$data->name] = $data; |
|
| 703 | } |
|
| 704 | ||
| 705 | return $trainers; |
|
| 706 | } |
|
| 707 | ||
| 708 | private function getTrainerLevelRating($level) |
|
| 709 | { |
|
| 710 | $req = 'SELECT COUNT(1) AS rank FROM trainer WHERE level = '.$level; |
|
| 711 | if (!empty(self::$config->system->trainer_blacklist)) { |
|
| 712 | $req .= " AND name NOT IN ('".implode("','", self::$config->system->trainer_blacklist)."')"; |
|
| 713 | } |
|
| 714 | $result = $this->mysqli->query($req); |
|
| 715 | $data = $result->fetch_object(); |
|
| 716 | ||
| 717 | return $data; |
|
| 718 | } |
|
| 719 | ||
| 720 | private function getTrainerActivePokemon($trainer_name) |
|
| 721 | { |
|
| 722 | $req = "SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.cp, |
|
| 723 | DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned, |
|
| 724 | gympokemon.trainer_name, gympokemon.iv_defense, gympokemon.iv_stamina, gympokemon.iv_attack, |
|
| 725 | filtered_gymmember.gym_id, |
|
| 726 | CONVERT_TZ(filtered_gymmember.deployment_time, '+00:00', '".self::$time_offset."') as deployment_time, |
|
| 727 | '1' AS active |
|
| 728 | FROM gympokemon INNER JOIN |
|
| 729 | (SELECT gymmember.pokemon_uid, gymmember.gym_id, gymmember.deployment_time FROM gymmember GROUP BY gymmember.pokemon_uid, gymmember.deployment_time, gymmember.gym_id HAVING gymmember.gym_id <> '') AS filtered_gymmember |
|
| 730 | ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid |
|
| 731 | WHERE gympokemon.trainer_name='".$trainer_name."' |
|
| 732 | ORDER BY gympokemon.cp DESC"; |
|
| 733 | $result = $this->mysqli->query($req); |
|
| 734 | $pokemons = array(); |
|
| 735 | while ($data = $result->fetch_object()) { |
|
| 736 | $pokemons[] = $data; |
|
| 737 | } |
|
| 738 | ||
| 739 | return $pokemons; |
|
| 740 | } |
|
| 741 | ||
| 742 | private function getTrainerInactivePokemon($trainer_name) |
|
| 743 | { |
|
| 744 | $req = "SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.cp, |
|
| 745 | DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned, |
|
| 746 | gympokemon.trainer_name, gympokemon.iv_defense, gympokemon.iv_stamina, gympokemon.iv_attack, |
|
| 747 | null AS gym_id, |
|
| 748 | CONVERT_TZ(filtered_gymmember.deployment_time, '+00:00', '".self::$time_offset."') as deployment_time, |
|
| 749 | '0' AS active |
|
| 750 | FROM gympokemon LEFT JOIN |
|
| 751 | (SELECT * FROM gymmember HAVING gymmember.gym_id <> '') AS filtered_gymmember |
|
| 752 | ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid |
|
| 753 | WHERE filtered_gymmember.pokemon_uid IS NULL AND gympokemon.trainer_name='".$trainer_name."' |
|
| 754 | ORDER BY gympokemon.cp DESC"; |
|
| 755 | $result = $this->mysqli->query($req); |
|
| 756 | $pokemons = array(); |
|
| 757 | while ($data = $result->fetch_object()) { |
|
| 758 | $pokemons[] = $data; |
|
| 759 | } |
|
| 760 | ||
| 761 | return $pokemons; |
|
| 762 | } |
|
| 763 | ||
| 764 | ///////// |
|
| 765 | // Cron |
|
| 766 | ///////// |
|
| 767 | ||
| 768 | public function getPokemonCountsActive() |
|
| 769 | { |
|
| 770 | $req = 'SELECT pokemon_id, COUNT(*) as total |
|
| 771 | FROM pokemon |
|
| 772 | WHERE disappear_time >= UTC_TIMESTAMP() |
|
| 773 | GROUP BY pokemon_id'; |
|
| 774 | $result = $this->mysqli->query($req); |
|
| 775 | $counts = array(); |
|
| 776 | while ($data = $result->fetch_object()) { |
|
| 777 | $counts[$data->pokemon_id] = $data->total; |
|
| 778 | } |
|
| 779 | ||
| 780 | return $counts; |
|
| 781 | } |
|
| 782 | ||
| 783 | ||
| 784 | public function getTotalPokemonIV() |
|
| 785 | { |
|
| 786 | $req = 'SELECT COUNT(*) as total |
|
| 787 | FROM pokemon |
|
| 788 | WHERE disappear_time >= UTC_TIMESTAMP() AND cp IS NOT NULL'; |
|
| 789 | $result = $this->mysqli->query($req); |
|
| 790 | $data = $result->fetch_object(); |
|
| 791 | ||
| 792 | return $data; |
|
| 793 | } |
|
| 794 | ||
| 795 | public function getPokemonCountsLastDay() |
|
| 796 | { |
|
| 797 | $req = 'SELECT pokemon_id, COUNT(*) AS spawns_last_day |
|
| 798 | FROM pokemon |
|
| 799 | WHERE disappear_time >= (SELECT MAX(disappear_time) FROM pokemon) - INTERVAL 1 DAY |
|
| 800 | GROUP BY pokemon_id |
|
| 801 | ORDER BY pokemon_id ASC'; |
|
| 802 | $result = $this->mysqli->query($req); |
|
| 803 | $counts = array(); |
|
| 804 | while ($data = $result->fetch_object()) { |
|
| 805 | $counts[$data->pokemon_id] = $data->spawns_last_day; |
|
| 806 | } |
|
| 807 | ||
| 808 | return $counts; |
|
| 809 | } |
|
| 810 | ||
| 811 | public function getCaptchaCount() |
|
| 812 | { |
|
| 813 | $req = 'SELECT SUM(accounts_captcha) AS total FROM mainworker'; |
|
| 814 | $result = $this->mysqli->query($req); |
|
| 815 | $data = $result->fetch_object(); |
|
| 816 | ||
| 817 | return $data; |
|
| 818 | } |
|
| 819 | ||
| 820 | public function getNestData($time, $minLatitude, $maxLatitude, $minLongitude, $maxLongitude) |
|
| 821 | { |
|
| 822 | $pokemon_exclude_sql = ''; |
|
| 823 | if (!empty(self::$config->system->nest_exclude_pokemon)) { |
|
| 824 | $pokemon_exclude_sql = 'AND pokemon_id NOT IN ('.implode(',', self::$config->system->nest_exclude_pokemon).')'; |
|
| 825 | } |
|
| 826 | $req = 'SELECT spawnpoint_id, pokemon_id, MAX(latitude) AS latitude, MAX(longitude) AS longitude, count(pokemon_id) AS total_pokemon, |
|
| 827 | MAX(UNIX_TIMESTAMP(disappear_time)) as latest_seen |
|
| 828 | FROM pokemon |
|
| 829 | WHERE disappear_time > (UTC_TIMESTAMP() - INTERVAL '.$time.' HOUR) |
|
| 830 | AND latitude >= '.$minLatitude.' AND latitude < '.$maxLatitude.' AND longitude >= '.$minLongitude.' AND longitude < '.$maxLongitude.' |
|
| 831 | '.$pokemon_exclude_sql.' |
|
| 832 | GROUP BY spawnpoint_id, pokemon_id |
|
| 833 | HAVING COUNT(pokemon_id) >= '.($time / 4).' |
|
| 834 | ORDER BY pokemon_id'; |
|
| 835 | $result = $this->mysqli->query($req); |
|
| 836 | $nests = array(); |
|
| 837 | while ($data = $result->fetch_object()) { |
|
| 838 | $nests[] = $data; |
|
| 839 | } |
|
| 840 | ||
| 841 | return $nests; |
|
| 842 | } |
|
| 843 | ||
| 844 | public function getSpawnpointCount($minLatitude, $maxLatitude, $minLongitude, $maxLongitude) |
|
| 845 | { |
|
| 846 | $req = 'SELECT COUNT(*) as total |
|
| 847 | FROM spawnpoint |
|
| 848 | WHERE latitude >= '.$minLatitude.' AND latitude < '.$maxLatitude.' AND longitude >= '.$minLongitude.' AND longitude < '.$maxLongitude; |
|
| 849 | $result = $this->mysqli->query($req); |
|
| 850 | $data = $result->fetch_object(); |
|
| 851 | ||
| 852 | return $data; |
|
| 853 | } |
|
| 854 | } |
|
| 855 | ||
