SWDB/application/page/Stats_Page.php

 <?php
    /**
     * Statistics page file.
     *
     * Provides a class with all the properties and methods to display the page.
     *
     * @category Page
     */

    /**
     * Require dependent files if not present.
     */
    require_once(PATH::PAGE . "Page.php");
    require_once(PATH::ENTITY . "Run.php");

    /**
     * Statistics page model.
     *
     * @category Page
     */
    class Stats_Page extends Page{

        /**
         * @var bool Indicates if there is data to show.
         */
        public $show_data = false;

        /**
         * @var Run The best run for the selected area
         */
        public $best_run = null;

        /**
         * @var int[] 0 index: total victories. 1 index: total losses.
         */
        public $win_lose = [0, 0];

        /**
         * @var int[] Item type drops.
         */
        public $drop_type = [
            "rune" => 0,
            "rune_craft" => 0,
            "item" => 0,
            "sd" => 0,
            "shapeshifting" => 0,
            "unit" => 0
        ];

        /**
         * @var int[] Rune drop rate by slot.
         */
        public $drop_rune_slot = [
            "1" => 0,
            "2" => 0,
            "3" => 0,
            "4" => 0,
            "5" => 0,
            "6" => 0
        ];

        /**
         * @var int[] Rune drop rate by stars.
         */
        public $drop_rune_stars = [
            "1" => 0,
            "2" => 0,
            "3" => 0,
            "4" => 0,
            "5" => 0,
            "6" => 0
        ];

        public $drop_rune_quality = [
            QUALITY_ID::NORMAL => 0,
            QUALITY_ID::MAGIC => 0,
            QUALITY_ID::RARE => 0,
            QUALITY_ID::HERO => 0,
            QUALITY_ID::LEGEND => 0
        ];

        /**
         * Constructor.
         *
         * Retrieves the data and initializes the variables.
         *
         * @global int Player ID.
         * @global resource Database connection.
         */
        public function __construct(){
            global $UID;
            global $db;
            $best_run = null;
            $this->view = PATH::VIEW . "stats.php";
            $this->parse_filters();

            // TODO DEBUG Force NB10
            $this->filters["AREA"] = 6001;
            $this->filters["AREA_TYPE"] = 2;
            $this->filters["STAGE"] = 10;
            // TODO END DEBUG

            // Build query modifier:
            $query_where = "
              run.uid = '$UID' AND
              run.area = " . $this->filters["AREA"] . "
            ";
            if ($this->filters["STAGE"] > 0){
                $query_where = $query_where . " AND run.stage =  " . $this->filters["STAGE"] . " ";
            }
            if ($this->filters["DIFFICULTY"] > 0){
                $query_where = $query_where . " AND run.difficulty =  " . $this->filters["DIFFICULTY"] . " ";
            }
            if (!$this->filters["INCLUDE_HELPER"]){
                $query_where = $query_where . " AND run.helper = 0 ";
            }

            // Best run:
            $s = "
              SELECT id
              FROM run
              WHERE
                $query_where
              ORDER BY time DESC
              LIMIT 1;
            ";
            $q = $db->query($s);
            if ($r = $q->fetchArray(SQLITE3_ASSOC)){
                $this->best_run = new Run($r["id"]);
            }

            // Win/lose ratio
            $s = "
              SELECT
                (SELECT COUNT(win) FROM run WHERE $query_where AND win = 1 ) AS win,
                (SELECT COUNT(win) FROM run WHERE $query_where AND win = 0) AS lose;
            ";
            $q = $db->query($s);
            $r = $q->fetchArray(SQLITE3_ASSOC);
            $this->win_lose = [$r["win"], $r["lose"]];

            // Drop type rate
            $s = "
              SELECT
                (SELECT COUNT(run) FROM run, run_drop_rune WHERE $query_where AND run.id = run_drop_rune.run AND uid = '$UID') AS rune,
                (SELECT COUNT(run) FROM run, run_drop_rune_craft WHERE $query_where AND run.id = run_drop_rune_craft.run AND uid = '$UID') AS rune_craft,
                (SELECT COUNT(run) FROM run, run_drop_item WHERE $query_where AND run.id = run_drop_item.run AND uid = '$UID') AS item,
                (SELECT COUNT(run) FROM run, run_drop_sd WHERE $query_where AND run.id = run_drop_sd.run AND uid = '$UID') AS sd,
                (SELECT COUNT(run) FROM run, run_drop_shapeshifting WHERE $query_where AND run.id = run_drop_shapeshifting.run AND uid = '$UID') AS shapeshifting,
                (SELECT COUNT(run) FROM run, run_drop_unit WHERE $query_where AND run.id = run_drop_unit.run AND uid = '$UID') AS unit;
            ";
            $q = $db->query($s);
            $r = $q->fetchArray(SQLITE3_ASSOC);
            $this->drop_type["rune"] = $r["rune"];
            $this->drop_type["rune_craft"] = $r["rune_craft"];
            $this->drop_type["item"] = $r["item"];
            $this->drop_type["sd"] = $r["sd"];
            $this->drop_type["shapeshifting"] = $r["shapeshifting"];
            $this->drop_type["unit"] = $r["unit"];

            // Rune drop rate by slot
            $s = "
              SELECT 
                slot,
                count(run.id) AS count
              FROM
                run,
                run_drop_rune
              WHERE
                $query_where AND 
                run.id = run_drop_rune.run
              GROUP BY slot
              ORDER BY slot;
            ";
            $q = $db->query($s);
            while ($r = $q->fetchArray(SQLITE3_ASSOC)){
                $this->drop_rune_slot[$r["slot"]] = $r["count"];
            }

            // Rune drop rate by slot
            $s = "
              SELECT 
                stars,
                count(run.id) AS count
              FROM
                run,
                run_drop_rune
              WHERE
                $query_where AND 
                run.id = run_drop_rune.run
              GROUP BY stars
              ORDER BY stars DESC;
            ";
            $q = $db->query($s);
            while ($r = $q->fetchArray(SQLITE3_ASSOC)){
                $this->drop_rune_stars[strval($r["stars"])] = $r["count"];
            }

            // Rune drop rate by quality
            $s = "
              SELECT 
                quality,
                count(run.id) AS count
              FROM
                run,
                run_drop_rune
              WHERE
                $query_where AND 
                run.id = run_drop_rune.run
              GROUP BY quality
              ORDER BY quality DESC;
            ";
            $q = $db->query($s);
            while ($r = $q->fetchArray(SQLITE3_ASSOC)){
                $this->drop_rune_quality[strval($r["quality"])] = $r["count"];
            }
            
            
            $this->title = "Stats - SWDB";
            $this->description = "Player statistics";
            $this->canonical = URL::BASE . "stats/";
        }

        /**
         * Parses the request looking for the selected filters, validates them
         * and adds them to the $filters array.
         */
        private function parse_filters(){
            $this->filters = FILTER::STATS;
            if (isset($_GET["area_type"]) && intval($_GET["area_type"]) > 0){
                $this->filters["AREA_TYPE"] = $_GET["area_type"];
            }
            if (isset($_GET["area"]) && intval($_GET["area"]) > 0){
                $this->filters["AREA"] = $_GET["area"];
            }
            if (isset($_GET["stage"]) && intval($_GET["stage"]) > 0){
                $this->filters["STAGE"] = $_GET["stage"];
            }
            if (isset($_GET["difficulty"]) && intval($_GET["difficulty"]) >= DIFFICULTY_ID::NORMAL && intval($_GET["difficulty"]) <= DIFFICULTY_ID::HELL){
                $this->filters["DIFFICULTY"] = $_GET["difficulty"];
            }
            if (isset($_GET["include_helper"]) && $_GET["include_helper"] == "on"){
                $this->filters["INCLUDE_HELPER"] = true;
            }
            else{
                $this->filters["INCLUDE_HELPER"] = false;
            }
            return;
        }
    }
?>