SWDB/application/page/Report_Runeupgrade_Page.php

 <?php
    /**
     * Upgradeable runes report page model.
     *
     * 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 . "Custom_Filter.php");
    require_once(PATH::ENTITY . "Unit.php");
    require_once(PATH::ENTITY . "Rune.php");

    /**
     * Upgradeable runes report page model.
     *
     * @category Page
     */
    class Report_Runeupgrade_Page extends Page{

        /**
         * @var \Filter[] Custom filters for this page.
         */
        public $custom_filters = [];

        /**
         * @var mixed[] List of available upgrades.
         *
         * Can be described as:
         * $upgrades = [
         *   [
         *     unit (Unit),
         *     upgrade = [
         *       [
         *         rune (Rune),
         *         alternative = [(Rune)]
         *       ]
         *     ]
         *   ]
         * ]
         */
        public $upgrades = [];

        /**
         * Constructor.
         *
         * Retrieves the data and initializes the variables.
         *
         * @param resource $db Connection to the database.
         * @global int Player ID.
         */
        public function __construct($db){
            global $UID;
            parent::__construct($db);
            $this->view = PATH::VIEW . "report_runeupgrade.php";
            $this->parse_filters();
            if (!isset($_GET["custom_filter"])){
                $s = $this->build_query();
            }
            else{
                $s = $this->build_custom_query($_GET["custom_filter"]);
            }
            $q = $this->db->query($s);
            $prev_monster = "";
            $prev_rune = "";
            $upgrade = null;
            $rupgrade = null;
            while ($r = $q->fetchArray(SQLITE3_ASSOC)){
                if ($r["unit"] != $prev_monster){
                    if ($prev_rune != ""){
                        array_push($upgrade["upgrade"], $rupgrade);
                    }
                    if ($prev_monster != ""){
                        array_push($this->upgrades, $upgrade);
                    }
                    $prev_monster = $r["unit"];
                    $prev_rune = "";
                    $upgrade = [
                        "unit" => new Unit($this->db, $r["unit"]),
                        "upgrade" => [],
                    ];
                    $rupgrade = [
                        "rune" => new Rune($this->db, $r["id"]),
                        "alternative" => [],
                    ];
                }
                if ($r["id"] != $prev_rune){
                    if ($prev_rune != ""){
                        array_push($upgrade["upgrade"], $rupgrade);
                    }
                    $prev_rune = $r["id"];
                    $rupgrade = [
                        "rune" => new Rune($this->db, $r["id"]),
                        "alternative" => [],
                    ];
                }
                $rune_alt = new Rune($this->db, $r["alternative"]);
                // If the alternative rune isassigned
                if (strlen($rune_alt->assigned_to) > 0){
                    $rune_alt->assigned_to = new Unit($this->db, $rune_alt->assigned_to);
                }
                array_push($rupgrade["alternative"], $rune_alt);
            }
            // Last entries
            array_push($upgrade["upgrade"], $rupgrade);
            array_push($this->upgrades, $upgrade);

            // Get custom filters for the form
            $s = "
              SELECT id
              FROM filter
              WHERE
                uid = '$UID' AND
                page = 'REPORT_RUNEUPGRADE';
            ";
            $q = $this->db->query($s);
            while ($r = $q->fetchArray(SQLITE3_ASSOC)){
                array_push($this->custom_filters, new Custom_Filter($this->db, $r["id"]));
            }
            $this->title = "Replaceable runes - SWDB";
            $this->description = "Find equiped runes that can be substituted for better ones.";
            $this->canonical = URL::BASE . "report/runeupgrade/";
        }

        /**
         * Parses the filters passed in the request and sets $filters.
         * Filters must be in $_GET, nd the available ones are max_stars (1-6),
         * min_stars (1-6) and in_storage (on/off).
         */
        private function parse_filters(){
            $this->filters = FILTER::RUNEUPGRADE;
            // Monster
            if (isset($_GET["monster_min_stars"]) && intval($_GET["monster_min_stars"]) > 0 && intval($_GET["monster_min_stars"]) < 7){
                $this->filters["MONSTER_MIN_STARS"] = $_GET["monster_min_stars"];
            }
            if (isset($_GET["monster_max_stars"]) && intval($_GET["monster_max_stars"]) > 0 && intval($_GET["monster_max_stars"]) < 7){
                $this->filters["MONSTER_MAX_STARS"] = $_GET["monster_max_stars"];
            }
            if (isset($_GET["monster_in_storage"]) && $_GET["monster_in_storage"] == "on"){
                $this->filters["MONSTER_IN_STORAGE"] = true;
            }
            else{
                $this->filters["MONSTER_IN_STORAGE"] = false;
            }
            if (isset($_GET["monster_teams"]) && $_GET["monster_teams"] == "on"){
                $this->filters["MONSTER_TEAMS"] = true;
            }
            else{
                $this->filters["MONSTER_TEAMS"] = false;
            }
            if (isset($_GET["monster_name"]) && strlen($_GET["monster_name"]) > 0){
                $this->filters["MONSTER_NAME"] = SQLite3::escapeString($_GET["monster_name"]);
            }
            // Runes
            if (isset($_GET["rune_min_stars"]) && intval($_GET["rune_min_stars"]) > 0 && intval($_GET["rune_min_stars"]) < 7){
                $this->filters["RUNE_MIN_STARS"] = $_GET["rune_min_stars"];
            }
            if (isset($_GET["rune_max_stars"]) && intval($_GET["rune_max_stars"]) > 0 && intval($_GET["rune_max_stars"]) < 7){
                $this->filters["RUNE_MAX_STARS"] = $_GET["rune_max_stars"];
            }
            if (isset($_GET["rune_min_level"]) && intval($_GET["rune_min_level"]) >= 0 && intval($_GET["rune_min_level"]) <= 15){
                $this->filters["RUNE_MIN_LEVEL"] = $_GET["rune_min_level"];
            }
            if (isset($_GET["rune_max_level"]) && intval($_GET["rune_max_level"]) >= 0 && intval($_GET["rune_max_level"]) <= 15){
                $this->filters["RUNE_MAX_LEVEL"] = $_GET["rune_max_level"];
            }
            if (isset($_GET["rune_min_quality"]) && intval($_GET["rune_min_quality"]) >= QUALITY_ID::NORMAL && intval($_GET["rune_min_quality"]) <= QUALITY_ID::LEGEND){
                $this->filters["RUNE_MIN_QUALITY"] = $_GET["rune_min_quality"];
            }
            if (isset($_GET["rune_max_quality"]) && intval($_GET["rune_max_quality"]) >= QUALITY_ID::NORMAL && intval($_GET["rune_max_quality"]) <= QUALITY_ID::LEGEND){
                $this->filters["RUNE_MAX_QUALITY"] = $_GET["rune_max_quality"];
            }
            if (isset($_GET["rune_min_original_quality"]) && intval($_GET["rune_min_original_quality"]) >= QUALITY_ID::NORMAL && intval($_GET["rune_min_original_quality"]) <= QUALITY_ID::LEGEND){
                $this->filters["RUNE_MIN_ORIGINAL_QUALITY"] = $_GET["rune_min_original_quality"];
            }
            if (isset($_GET["rune_max_original_quality"]) && intval($_GET["rune_max_original_quality"]) >= QUALITY_ID::NORMAL && intval($_GET["rune_max_original_quality"]) <= QUALITY_ID::LEGEND){
                $this->filters["RUNE_MAX_ORIGINAL_QUALITY"] = $_GET["rune_max_original_quality"];
            }
            if (isset($_GET["rune_min_efficiency"]) && intval($_GET["rune_min_efficiency"]) >= 0 && intval($_GET["rune_min_efficiency"]) <= 100){
                $this->filters["RUNE_MIN_EFFICIENCY"] = $_GET["rune_min_efficiency"];
            }
            if (isset($_GET["rune_max_efficiency"]) && intval($_GET["rune_max_efficiency"]) >= 0 && intval($_GET["rune_max_efficiency"]) <= 100){
                $this->filters["RUNE_MAX_EFFICIENCY"] = $_GET["rune_max_efficiency"];
            }
            if (isset($_GET["rune_min_max_efficiency"]) && intval($_GET["rune_min_max_efficiency"]) >= 0 && intval($_GET["rune_min_max_efficiency"]) <= 100){
                $this->filters["RUNE_MIN_MAX_EFFICIENCY"] = $_GET["rune_min_max_efficiency"];
            }
            if (isset($_GET["rune_max_max_efficiency"]) && intval($_GET["rune_max_max_efficiency"]) >= 0 && intval($_GET["rune_max_max_efficiency"]) <= 100){
                $this->filters["RUNE_MAX_MAX_EFFICIENCY"] = $_GET["rune_max_max_efficiency"];
            }
            if (isset($_GET["rune_slot"])){
                $slots = $_GET["rune_slot"];
                foreach ($slots as $slot){
                    if (intval($slot) >= 1 && intval($slot) < 7){
                        array_push($this->filters["RUNE_SLOT"], intval($slot));
                    }
                }
            }
            // Alternatives
            if (isset($_GET["alternative_min_stars"]) && intval($_GET["alternative_min_stars"]) > 0 && intval($_GET["alternative_min_stars"]) < 7){
                $this->filters["ALTERNATIVE_MIN_STARS"] = $_GET["alternative_min_stars"];
            }
            if (isset($_GET["alternative_min_level"]) && intval($_GET["alternative_min_level"]) >= 0 && intval($_GET["alternative_min_level"]) <= 15){
                $this->filters["ALTERNATIVE_MIN_LEVEL"] = $_GET["alternative_min_level"];
            }
            if (isset($_GET["alternative_min_original_quality"]) && intval($_GET["alternative_min_original_quality"]) >= QUALITY_ID::NORMAL && intval($_GET["alternative_min_original_quality"]) <= QUALITY_ID::LEGEND){
                $this->filters["ALTERNATIVE_MIN_ORIGINAL_QUALITY"] = $_GET["alternative_min_original_quality"];
            }
            if (isset($_GET["alternative_condition"]) && intval($_GET["alternative_condition"]) >= 0 && intval($_GET["alternative_condition"]) <= QUALITY_ID::LEGEND){
                $this->filters["ALTERNATIVE_CONDITION"] = $_GET["alternative_condition"];
            }
            if (isset($_GET["alternative_source"]) && intval($_GET["alternative_source"]) >= 0 && intval($_GET["alternative_source"]) <= 2){
                $this->filters["ALTERNATIVE_SOURCE"] = $_GET["alternative_source"];
            }
            return;
        }

        /**
         * Builds the query to the rune table using the a custom filter.
         * 
         * @param int $filter Custom filter ID.
         * @return string The query to be executed.
         * @global int Player ID.
         */
        private function build_custom_query($filter){
            global $UID;
            $s = "
              SELECT condition
              FROM filter
              WHERE
                uid = '$UID' AND
                id = $filter;
            ";
            $q = $this->db->query($s);
            $r = $q->fetchArray(SQLITE3_ASSOC);
            $cond = $r["condition"];
            $s = "
              SELECT
                unit.id AS unit,
                rune.id AS id,
                rune_alt.id AS alternative
              FROM
                rune,
                rune rune_alt,
                unit,
                k_unit
              WHERE
                unit.uid = '$UID' AND
                rune.uid = '$UID' AND
                rune_alt.uid = '$UID' AND
                unit.id = rune.assigned_to AND
                unit.unit = k_unit.id AND
                rune.type = rune_alt.type AND 
                rune.slot = rune_alt.slot AND 
                rune.main_stat = rune_alt.main_stat AND 
                rune.id <> rune_alt.id AND
                (
                  $cond
                )
              ORDER BY
                (SELECT COUNT(DISTINCT(area_type || area)) FROM team, team_unit WHERE team.id = team_unit.team AND team_unit.unit = unit.id AND team.uid = '$UID') DESC,
                unit.stars DESC,
                unit.level DESC,
                unit.id,
                rune.slot,
                id,
                rune_alt.stars DESC,
                rune_alt.original_quality DESC,
                rune_alt.main_stat_value DESC;
            ";
            return $s;
        }

        /**
         * Builds the query for the page, using the providded or default filters.
         *
         * @return string The query to be executed.
         * @global int Player ID.
         */
        private function build_query(){
            global $UID;
            $s = "
              SELECT
                unit.id AS unit,
                rune.id AS id,
                rune_alt.id AS alternative
              FROM
                rune,
                rune rune_alt,
                unit,
                k_unit
              WHERE
                unit.uid = '$UID' AND
                rune.uid = '$UID' AND
                rune_alt.uid = '$UID' AND
                unit.id = rune.assigned_to AND
                unit.unit = k_unit.id AND
                unit.stars >= " . $this->filters["MONSTER_MIN_STARS"] . " AND
                unit.stars <= " . $this->filters["MONSTER_MAX_STARS"] . " AND
                rune.stars >= " . $this->filters["RUNE_MIN_STARS"] . " AND 
                rune.stars <= " . $this->filters["RUNE_MAX_STARS"] . " AND 
                rune.level >= " . $this->filters["RUNE_MIN_LEVEL"] . " AND 
                rune.level <= " . $this->filters["RUNE_MAX_LEVEL"] . " AND 
                rune.quality >= " . $this->filters["RUNE_MIN_QUALITY"] . " AND 
                rune.quality <= " . $this->filters["RUNE_MAX_QUALITY"] . " AND 
                rune.original_quality >= " . $this->filters["RUNE_MIN_ORIGINAL_QUALITY"] . " AND 
                rune.original_quality <= " . $this->filters["RUNE_MAX_ORIGINAL_QUALITY"] . " AND 
                rune.efficiency >= " . $this->filters["RUNE_MIN_EFFICIENCY"] . " AND 
                rune.efficiency <= " . $this->filters["RUNE_MAX_EFFICIENCY"] . " AND 
                rune.max_efficiency >= " . $this->filters["RUNE_MIN_MAX_EFFICIENCY"] . " AND 
                rune.max_efficiency <= " . $this->filters["RUNE_MAX_MAX_EFFICIENCY"] . " AND 
                rune.type = rune_alt.type AND 
                rune.slot = rune_alt.slot AND 
                rune.main_stat = rune_alt.main_stat AND 
                rune.id <> rune_alt.id AND
                rune_alt.stars >= " . $this->filters["ALTERNATIVE_MIN_STARS"] . " AND 
                rune_alt.level >= " . $this->filters["ALTERNATIVE_MIN_LEVEL"] . " AND 
                rune_alt.original_quality >= " . $this->filters["ALTERNATIVE_MIN_ORIGINAL_QUALITY"] . " AND
            ";
            switch ($this->filters["ALTERNATIVE_SOURCE"]){
                case 0: // Unassigned
                    $s = $s . " rune_alt.assigned_to IS NULL AND ";
                    break;
                case 1: // Unassigned or assigned to lower star monsters
                    $s = $s . "
                      (
                        rune_alt.assigned_to IS NULL OR
                        rune_alt.assigned_to IN (SELECT id FROM unit m WHERE m.stars < unit.stars)
                      ) AND
                    ";
                    break;
                // Case 2: Assigned or unassigned.
            }  
            switch ($this->filters["ALTERNATIVE_CONDITION"]){
                case 0: // Higher grade, any quality
                    $s = $s .
                    "  rune_alt.stars >= rune.stars ";
                    break;
                case 1: // Higher grade or higher quality
                    $s = $s .
                    "  ( " .
                    "    rune_alt.stars > rune.stars OR " .
                    "    rune_alt.original_quality > rune.original_quality " .
                    "  ) ";
                    break;
                case 2: // Equal or higher grade and equal or higher quality
                    $s = $s . 
                       "  rune_alt.stars >= rune.stars AND " .
                       "  rune_alt.original_quality >= rune.original_quality ";
                    break;
                case 3: // Equal or higher grade and higher quality
                    $s = $s . 
                       "  rune_alt.stars >= rune.stars AND " .
                       "  rune_alt.original_quality > rune.original_quality ";
                    break;
                case 4: // Equal grade and equal or higher quality
                    $s = $s . 
                       "  rune_alt.stars > rune.stars AND " .
                       "  rune_alt.original_quality >= rune.original_quality ";
                    break;
                default: // Case 5: Higher grade and higher quality
                    $s = $s . 
                       "  rune_alt.stars > rune.stars AND " .
                       "  rune_alt.original_quality > rune.original_quality ";
                    break;
            }
            if (strlen($this->filters["MONSTER_NAME"]) > 0){
                $s = $s . " AND upper(k_unit.name) LIKE = upper('%" . $this->filters["MONSTER_NAME"] . "%') ";
            }
            if (!$this->filters["MONSTER_IN_STORAGE"]){
                $s = $s . " AND unit.building <> " . BUILDING_ID::MONSTER_STORAGE;
            }
            if (!$this->filters["MONSTER_TEAMS"]){
                $s = $s . " AND unit.id in (SELECT unit FROM team, team_unit WHERE team.id = team_unit.team AND team_unit.unit = unit.id AND team.uid = '$UID') ";
            }
            if (count($this->filters["RUNE_SLOT"]) > 0){
                $s = $s . " AND rune.slot IN ( ";
                foreach($this->filters["RUNE_SLOT"] as $t){
                    $s = $s . "$t, ";
                }
                $s = $s . "-1) ";
            }
            $s = $s . "
              ORDER BY
                (SELECT COUNT(DISTINCT(area_type || area)) FROM team, team_unit WHERE team.id = team_unit.team AND team_unit.unit = unit.id AND team.uid = '$UID') DESC,
                unit.stars DESC,
                unit.level DESC,
                unit.id,
                rune.slot,
                id,
                rune_alt.stars DESC,
                rune_alt.original_quality DESC,
                rune_alt.main_stat_value DESC;
            ";
            return $s;
        }
    }
?>