SWDB/application/page/Report_Skillup_Page.php

 <?php

    require_once($path["page"] . "Page.php");
    require_once($path["entity"] . "Unit.php");

    /**
     * Urgent Skillup report page.
     */
    class Report_Skillup_Page extends Page{

        /**
         * Array of @{see Monsters}s.
         */
        public $units = [];

        /**
         * The filters the page can handle.
         */
        public $filters = [
            "min_stars" => 5,
            "max_stars" => 6,
            "min_natural_stars" => 5,
            "max_natural_stars" => 5,
            "in_storage" => false,
            "without_runes" => false,
            "family" => false,
            "maxed" => false,
            "2a" => false,
            "homunculus" => false
        ];

        /**
         * Constructor.
         *
         * Retrieves the data and initializes the variables.
         *
         * @param SQLite3 $db Connection to the database.
         */
        public function __construct($db){
            global $path;
            global $root;
            parent::__construct($db);
            $this->view = $path["view"] . "report_skillup.php";
            $this->parse_filters();
            $s_mon = $this->build_query();
            error_log($s_mon);
            $q_mon = $this->db->query($s_mon);
            while ($r_mon = $q_mon->fetchArray(SQLITE3_ASSOC)){
                array_push($this->units, new Unit($db, $r_mon["id"]));
            }
            $this->title = "Urgent skill-ups - SWDB";
            $this->description = "Monster in need os skilling up";
            $this->canonical = $root . "report/skillups";
        }

        /**
         * Parses the request looking for the selected filters, validates them
         * and adds them to the $filters array.
         */
        private function parse_filters(){
            if (isset($_GET["min_stars"]) && intval($_GET["min_stars"]) > 0 && intval($_GET["min_stars"]) < 7){
                $this->filters["min_stars"] = $_GET["min_stars"];
            }
            if (isset($_GET["max_stars"]) && intval($_GET["max_stars"]) > 0 && intval($_GET["max_stars"]) < 7){
                $this->filters["max_stars"] = $_GET["max_stars"];
            }
            if (isset($_GET["min_natural_stars"]) && intval($_GET["min_natural_stars"]) > 0 && intval($_GET["min_natural_stars"]) < 6){
                $this->filters["min_natural_stars"] = $_GET["min_natural_stars"];
            }
            if (isset($_GET["max_natural_stars"]) && intval($_GET["max_natural_stars"]) > 0 && intval($_GET["max_natural_stars"]) < 6){
                $this->filters["max_natural_stars"] = $_GET["max_natural_stars"];
            }
            if (isset($_GET["in_storage"]) && $_GET["in_storage"] == "on"){
                $this->filters["in_storage"] = true;
            }
            if (isset($_GET["without_runes"]) && $_GET["without_runes"] == "on"){
                $this->filters["without_runes"] = true;
            }
            if (isset($_GET["family"]) && $_GET["family"] == "on"){
                $this->filters["family"] = true;
            }
            if (isset($_GET["maxed"]) && $_GET["maxed"] == "on"){
                $this->filters["maxed"] = true;
            }
            if (isset($_GET["2a"]) && $_GET["2a"] == "on"){
                $this->filters["2a"] = true;
            }
            if (isset($_GET["homunculus"]) && $_GET["homunculus"] == "on"){
                $this->filters["homunculus"] = true;
            }

            return;
        }

        /**
         * Builds the query to the rune table using the selected or default
         * filters.
         *
         * @return The query to be executed.
         */
        private function build_query(){
            $s =
              "SELECT DISTINCT " .
              "  unit.id AS id, " .
              "  sum(unit_skill.level * ((k_skill.slot + 1) / 2)) AS ponderated_lvl, " .
              "  sum(unit_skill.level) AS lvl, " .
              "  sum(k_skill.max_level * ((k_skill.slot + 1) / 2)) AS ponderated_max_lvl, " .
              "  sum(k_skill.max_level) AS max_lvl " .
              "FROM " .
              "  unit, " .
              "  k_unit, " .
              "  unit_skill, " .
              "  k_unit_skill, " .
              "  k_skill " .
              "WHERE " .
              "  unit.unit = k_unit.id AND " .
              "  unit_skill.unit = unit.id AND " .
              "  unit_skill.skill = k_unit_skill.skill AND " .
              "  k_unit.id = k_unit_skill.unit AND " .
              "  k_skill.id = k_unit_skill.skill AND " .
              "  unit.stars >= " . $this->filters["min_stars"] . " AND " .
              "  unit.stars <= " . $this->filters["max_stars"] . " AND " .
              "  k_unit.natural_stars >= " . $this->filters["min_natural_stars"] . " AND " .
              "  k_unit.natural_stars <= " . $this->filters["max_natural_stars"] . " ";
            if (!$this->filters["in_storage"]){
                // TODO
                //$s = $s . " AND unit.in_storage = 0 ";
            }
            if (!$this->filters["without_runes"]){
                $s = $s . " AND unit.id IN (SELECT DISTINCT assigned_to FROM rune) ";
            }
            if ($this->filters["family"]){
                $s = $s . " AND unit.unit NOT IN (SELECT id FROM k_unit m WHERE family IN (SELECT family FROM k_unit k WHERE k.family = m.family AND k.natural_stars < m.natural_stars AND k.natural_stars < " . $this->filters["min_natural_stars"] . ")) ";
                // Special case: Eirgar (Dark Vampire lord). Nat5 can be powered with 4 star Vampires.
                if ($this->filters["min_natural_stars"] >= 5){
                    $s = $s . " AND k_unit.id <> 19114 AND k_unit.id <> 19104 ";
                }
                // Special case: Fran (Light Fairy Queen). Nat3 can be powered with 2 star Fairies.
                if ($this->filters["min_natural_stars"] >= 3){
                    $s = $s . " AND k_unit.id <> 23015 AND k_unit.id <> 23005 ";
                }
            }
            if (!$this->filters["2a"]){
                $s = $s . " AND (k_unit.awakens_from IS NULL OR k_unit.awakens_to IS NOT NULL OR k_unit.base_stars - k_unit.natural_stars = 1) ";
            }
            if (!$this->filters["homunculus"]){
                $s = $s . " AND k_unit.homunculus <> 1 ";
            }
            $s = $s .
              "GROUP BY unit.id ";
            if (!$this->filters["maxed"]){
                $s = $s . " HAVING ponderated_lvl < ponderated_max_lvl ";
            }
            $s = $s .
              "ORDER BY " .
              "  ponderated_lvl * 100 / ponderated_max_lvl, " .
              "  unit.stars DESC; " .
              "  unit.level DESC, " .
              "  lvl * 100 / max_lvl, " .
              "  max_lvl - lvl DESC; ";
            return $s;
        }
    }
?>