brusselopole / Worldopole

    // Raids

    ///////////

    public function getAllRaids($page)

    {

        $limit = ' LIMIT '.($page * 10).',10';

        $req = 'SELECT r.fort_id AS gym_id, r.level AS level, r.pokemon_id AS pokemon_id, r.cp AS cp, r.move_1 AS move_1, r.move_2 AS move_2, FROM_UNIXTIME(r.time_spawn) AS spawn, FROM_UNIXTIME(r.time_battle) AS start, FROM_UNIXTIME(r.time_end) AS end, FROM_UNIXTIME(fs.updated) AS last_scanned, f.name, f.lat AS latitude, f.lon as longitude

					FROM forts f

					LEFT JOIN fort_sightings fs ON (fs.fort_id = f.id AND fs.last_modified = (SELECT MAX(last_modified) FROM fort_sightings fs2 WHERE fs2.fort_id=f.id))

				 	LEFT JOIN raids r ON (r.fort_id = f.id AND r.time_end >= UNIX_TIMESTAMP())

					WHERE r.time_end > UNIX_TIMESTAMP()

					ORDER BY r.level DESC, r.time_battle'.$limit;

        $result = $this->mysqli->query($req);

        $raids = array();

        while ($data = $result->fetch_object()) {

            $raids[] = $data;

        }

        return $raids;

    }

    ////////////////

    // Gym History

        return array('last_page' => $last_page, 'data' => $history);

    }

    private function getHistoryForGymPokemon($gym_id, $last_modified)

    {

        $req = "SELECT ghd.defender_id, gd.pokemon_id, ghd.cp, gd.owner_name as trainer_name

					FROM gym_history_defenders ghd

					JOIN gym_defenders gd ON ghd.defender_id = gd.external_id

					WHERE ghd.fort_id = '".$gym_id."' AND date = '".$last_modified."'

					ORDER BY gd.deployment_time";

        $result = $this->mysqli->query($req);

        $pokemons = array();

        while ($data = $result->fetch_object()) {

            $pokemons[$data->defender_id] = $data;

        }

        return $pokemons;

    }

    //////////////

    // Trainers

        return $data;

    }

    public function getTop50Pokemon($pokemon_id, $top_order_by, $top_direction)

    {

        $req = "SELECT CONVERT_TZ(disappear_time, '+00:00', '".self::$time_offset."') AS distime,

				pokemon_id, disappear_time, latitude, longitude,

				cp, individual_attack, individual_defense, individual_stamina,

				ROUND(100*(individual_attack+individual_defense+individual_stamina)/45,1) AS IV,

				move_1, move_2, form

				FROM pokemon

				WHERE pokemon_id = '".$pokemon_id."' AND move_1 IS NOT NULL AND move_1 <> '0'

				ORDER BY $top_order_by $top_direction, disappear_time DESC

				LIMIT 0,50";

        $result = $this->mysqli->query($req);

        $top = array();

        while ($data = $result->fetch_object()) {

            $top[] = $data;

        }

        return $top;

    }

    public function getTop50Trainers($pokemon_id, $best_order_by, $best_direction)

    {

        return $toptrainer;

    }

    public function getPokemonHeatmap($pokemon_id, $start, $end)

    {

		$req = "SELECT latitude, longitude

				FROM pokemon

				WHERE pokemon_id = ".$pokemon_id." AND disappear_time BETWEEN '".$start."' AND '".$end."'

				LIMIT 10000";

        $result = $this->mysqli->query($req);

        $points = array();

        while ($data = $result->fetch_object()) {

            $points[] = $data;

        }

        return $points;

    }

    public function getPokemonGraph($pokemon_id)

    {

        return $data;

    }

    private function getTrainerActivePokemon($trainer_name)

    {

        $req = "SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.cp,

				DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned,

				gympokemon.trainer_name, gympokemon.iv_defense, gympokemon.iv_stamina, gympokemon.iv_attack,

				filtered_gymmember.gym_id,

				CONVERT_TZ(filtered_gymmember.deployment_time, '+00:00', '".self::$time_offset."') as deployment_time,

				'1' AS active

				FROM gympokemon INNER JOIN

				(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

				ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid

				WHERE gympokemon.trainer_name='".$trainer_name."'

				ORDER BY gympokemon.cp DESC";

        $result = $this->mysqli->query($req);

        $pokemons = array();

        while ($data = $result->fetch_object()) {

            $pokemons[] = $data;

        }

        return $pokemons;

    }

    private function getTrainerInactivePokemon($trainer_name)

    {

        return $pokemons;

    }

    private function getTrainerInactivePokemon($trainer_name)

    {

        $req = "SELECT DISTINCT gympokemon.pokemon_id, gympokemon.pokemon_uid, gympokemon.cp,

				DATEDIFF(UTC_TIMESTAMP(), gympokemon.last_seen) AS last_scanned,

				gympokemon.trainer_name, gympokemon.iv_defense, gympokemon.iv_stamina, gympokemon.iv_attack,

				null AS gym_id,

				CONVERT_TZ(filtered_gymmember.deployment_time, '+00:00', '".self::$time_offset."') as deployment_time,

				'0' AS active

				FROM gympokemon LEFT JOIN

				(SELECT * FROM gymmember HAVING gymmember.gym_id <> '') AS filtered_gymmember

				ON gympokemon.pokemon_uid = filtered_gymmember.pokemon_uid

				WHERE filtered_gymmember.pokemon_uid IS NULL AND gympokemon.trainer_name='".$trainer_name."'

				ORDER BY gympokemon.cp DESC";

        $result = $this->mysqli->query($req);

        $pokemons = array();

        while ($data = $result->fetch_object()) {

            $pokemons[] = $data;

        }

        return $pokemons;

    }

    /////////

    // Cron