SWDB/application/page/Stats_Page.php

 <?php
/**
 * Statistics page file.
 *
 * Provides a class with all the properties and methods to display the page.
 *
 * @author Iñigo Valentin <i@inigovalentin.com>
 * @license https://www.gnu.org/licenses/gpl-3.0.en.html GNU General Public License V3
 * @package SWDB
 * @todo Not workig, redo.
 */

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 = true;

    /**
     * @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
    ];
    
    /**
     * @var mixed[] Page filters
     */
    public $filters = [
        "AREA_TYPE" => null,
        "AREA" => null,
        "STAGE" => null,
        "DIFFICULTY" => null,
        "INCLUDE_HELPER" => false
    ];

    /**
     * Constructor.
     *
     * Retrieves the data and initializes the variables.
     */
    public function __construct(){
        $this->view = PATH::VIEW . "stats.php";
        $this->title = "Stats - SWDB";
        $this->description = "Player statistics";
        $this->canonical = URL::BASE . "stats/";

        $this->parse_filters();

        // If no filters, stop
        if ($this->filters["AREA"] == null){
            $this->show_data = false;
            return;
        }

        // Build query modifier:
        $query_where = "
          run.player = '" . get_context()->get_player()->get_id() . "' 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 = get_context()->get_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 = get_context()->get_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, run_drop_rune run_drop_rune WHERE $query_where AND run.id = run_drop_rune.run AND player = '" . get_context()->get_player()->get_id() . "') AS rune,
            (SELECT COUNT(run) FROM run run, run_drop_rune_craft run_drop_rune_craft WHERE $query_where AND run.id = run_drop_rune_craft.run AND player = '" . get_context()->get_player()->get_id() . "') AS rune_craft,
            (SELECT COUNT(run) FROM run run, run_drop_item run_drop_item WHERE $query_where AND run.id = run_drop_item.run AND player = '" . get_context()->get_player()->get_id() . "') AS item,
            (SELECT COUNT(run) FROM run run, run_drop_sd run_drop_sd WHERE $query_where AND run.id = run_drop_sd.run AND player = '" . get_context()->get_player()->get_id() . "') AS sd,
            (SELECT COUNT(run) FROM run run, run_drop_shapeshifting run_drop_shapeshifting WHERE $query_where AND run.id = run_drop_shapeshifting.run AND player = '" . get_context()->get_player()->get_id() . "') AS shapeshifting,
            (SELECT COUNT(run) run run, run_drop_unit run_drop_unit WHERE $query_where AND run.id = run_drop_unit.run AND player = '" . get_context()->get_player()->get_id() . "') AS unit;
        ";
        $q = get_context()->get_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,
            run_drop_rune run_drop_rune
          WHERE
            $query_where AND 
            run.id = run_drop_rune.run
          GROUP BY slot
          ORDER BY slot;
        ";
            $q = get_context()->get_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,
            run_drop_rune run_drop_rune
          WHERE
            $query_where AND 
            run.id = run_drop_rune.run
          GROUP BY stars
          ORDER BY stars DESC;
        ";
        $q = get_context()->get_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,
            run_drop_rune run_drop_rune
          WHERE
            $query_where AND 
            run.id = run_drop_rune.run
          GROUP BY quality
          ORDER BY quality DESC;
        ";
        $q = get_context()->get_db()->query($s);
        while ($r = $q->fetchArray(SQLITE3_ASSOC)){
            $this->drop_rune_quality[strval($r["quality"])] = $r["count"];
        }
    }

    /**
     * Parses the request looking for the selected filters, validates them
     * and adds them to the $filters array.
     */
    private function parse_filters(){
        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;
    }
}