brusselopole / Worldopole

    // Homepage

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

    public function getTotalPokemon()

    {

        $req = 'SELECT COUNT(*) AS total FROM sightings WHERE expire_timestamp >= EXTRACT(EPOCH FROM NOW())';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getTotalGyms()

    {

        $req = 'SELECT COUNT(*) AS total FROM forts';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getTotalRaids()

    {

        $req = 'SELECT COUNT(*) AS total FROM raids WHERE time_battle <= EXTRACT(EPOCH FROM NOW()) AND time_end >= EXTRACT(EPOCH FROM NOW())';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getTotalGymsForTeam($team_id)

    {

        $req = "SELECT COUNT(*) AS total

					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))

					WHERE team = '$team_id'";

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

    // Single Pokemon

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

    public function getGymsProtectedByPokemon($pokemon_id)

    {

        $req = "SELECT COUNT(f.id) AS total

					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))

					WHERE guard_pokemon_id = '".$pokemon_id."'";

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getPokemonLastSeen($pokemon_id)

    {

        $req = "SELECT TO_TIMESTAMP(expire_timestamp) AS expire_timestamp, TO_TIMESTAMP(expire_timestamp) AS disappear_time_real, lat AS latitude, lon AS longitude

                FROM sightings

                WHERE pokemon_id = '".$pokemon_id."'

                ORDER BY expire_timestamp DESC

                LIMIT 1 OFFSET 0";

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $spawns;

    }

    public function getPokemonSliderMinMax()

    {

        $req = 'SELECT TO_TIMESTAMP(MIN(expire_timestamp)) AS min, TO_TIMESTAMP(MAX(expire_timestamp)) AS max FROM sightings';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getMapsCoords()

    {

        $req = 'SELECT MAX(lat) AS max_latitude, MIN(lat) AS min_latitude, MAX(lon) AS max_longitude, MIN(lon) as min_longitude FROM spawnpoints';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getPokemonCount($pokemon_id)

    {

        $req = 'SELECT count, last_seen, latitude, longitude

					FROM pokemon_stats

					WHERE pid = '.$pokemon_id;

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $data;

    }

    public function getRaidCount($pokemon_id)

    {

        $req = 'SELECT count, last_seen, latitude, longitude

					FROM raid_stats

					WHERE pid = '.$pokemon_id;

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

    // Pokestops

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

    public function getTotalPokestops()

    {

        $req = 'SELECT COUNT(*) as total FROM pokestops';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $datas;

    }

    public function getOwnedAndPoints($team_id)

    {

        $req = "SELECT COUNT(f.id) AS total, ROUND(AVG(fs.total_cp))AS average_points

        			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))

        			WHERE fs.team = '".$team_id."'";

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $gyms;

    }

    public function getGymData($gym_id)

    {

        $req = "SELECT f.name AS name, null AS description, f.url AS url, fs.team AS team, TO_TIMESTAMP(fs.updated) AS last_scanned, fs.guard_pokemon_id AS guard_pokemon_id, (6 - fs.slots_available) AS level, fs.total_cp

			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))

			WHERE f.id ='".$gym_id."'

			GROUP BY f.name, f.url, fs.team, fs.updated, fs.guard_pokemon_id, fs.slots_available, gd.cp";

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }

        return $counts;

    }

    public function getCaptchaCount()

    {

        $req = ' SELECT COUNT(*) as total FROM accounts WHERE captchaed IS NOT NULL AND reason IS NULL';

        $result = pg_query($this->db, $req);

        $data = pg_fetch_object($result);

        return $data;

    }