SWDB/application/page/Report_Skillup_Page.php

 <?php
    /**
     * Urgent Skillup report 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 . "Unit.php");

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

        /**
         * @var \Unit[] Sorted list of units.
         */
        public $units = [];

        /**
         * @var mixed[] Default filter values.
         */
        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" => true,
            "HOMUNCULUS" => true,
            "UNIT" => 0, // 0: All, 1: In teams, 2: In teams (score > 0)
            "PONDERATION" => [1, 1.5, 2, 2.5]
        ];

        /**
         * Constructor.
         *
         * Retrieves the data and initializes the variables.
         *
         * @global resource Database connection.
         */
        public function __construct(){
            global $db;
            $this->view = PATH::VIEW . "report_skillup.php";
            $this->parse_filters();
            $s = $this->build_query();
            $q = $db->query($s);
            while ($r = $q->fetchArray(SQLITE3_ASSOC)){
                array_push($this->units, new Unit($r["id"]));
            }
            $this->title = "Urgent skill-ups - SWDB";
            $this->description = "Monster in need of skilling up";
            $this->canonical = URL::BASE . "report/skillups";
        }

        /**
         * Parses the request looking for the selected filters, validates them
         * and adds them to the $filters array.
         */
        private function parse_filters(){
            //$this->filters = FILTER::SKILLUP;
            if (isset($_GET["min_stars"]) && intval($_GET["min_stars"]) > 0 && intval($_GET["min_stars"]) < 7){
                $this->filters["MIN_STARS"] = intval($_GET["min_stars"]);
            }
            if (isset($_GET["max_stars"]) && intval($_GET["max_stars"]) > 0 && intval($_GET["max_stars"]) < 7){
                $this->filters["MAX_STARS"] = intval($_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"] = intval($_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"] = intval($_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"] = false;
            }
            if (isset($_GET["homunculus"]) && $_GET["homunculus"] == "on"){
                $this->filters["HOMUNCULUS"] = true;
            }
            if (isset($_GET["unit"]) && intval($_GET["unit"]) >= 0 && intval($_GET["unit"]) <= 2){
                $this->filters["UNIT"] = intval($_GET["unit"]);
            }
            if (isset($_GET["pond_1"]) && floatval($_GET["pond_1"]) >= 0 && floatval($_GET["pond_1"]) <= 4){
                $this->filters["PONDERATION"][0] = floatval($_GET["pond_1"]);
            }
            if (isset($_GET["pond_2"]) && floatval($_GET["pond_2"]) >= 0 && floatval($_GET["pond_2"]) <= 4){
                $this->filters["PONDERATION"][1] = floatval($_GET["pond_2"]);
            }
            if (isset($_GET["pond_3"]) && floatval($_GET["pond_3"]) >= 0 && floatval($_GET["pond_3"]) <= 4){
                $this->filters["PONDERATION"][2] = floatval($_GET["pond_3"]);
            }
            if (isset($_GET["pond_4"]) && floatval($_GET["pond_4"]) >= 0 && floatval($_GET["pond_4"]) <= 4){
                $this->filters["PONDERATION"][3] = floatval($_GET["pond_4"]);
            }
            return;
        }

        /**
         * Builds the query to the rune table using the selected or default
         * filters.
         *
         * @return string The query to be executed.
         * @global int Player ID.
         */
        private function build_query(){
            global $UID;
            $s = "
              SELECT DISTINCT
                unit.id AS id,
                --sum(unit_skill.level * (CAST(k_skill.slot + 1 AS FLOAT) / 2.0)) AS ponderated_lvl,
                sum(unit_skill.level *
                  CASE
                    WHEN k_skill.slot = 1 THEN  " . $this->filters["PONDERATION"][0] . "
                    WHEN k_skill.slot = 2 THEN  " . $this->filters["PONDERATION"][1] . "
                    WHEN k_skill.slot = 3 THEN  " . $this->filters["PONDERATION"][2] . "
                    WHEN k_skill.slot = 4 THEN  " . $this->filters["PONDERATION"][3] . "
                    ELSE 1
                    END
                ) AS ponderated_lvl,
                sum(unit_skill.level) AS lvl,
                --sum(k_skill.max_level * (CAST(k_skill.slot + 1 AS FLOAT) / 2.0)) AS ponderated_max_lvl,
                sum(k_skill.max_level * 
                  CASE
                    WHEN k_skill.slot = 1 THEN  " . $this->filters["PONDERATION"][0] . "
                    WHEN k_skill.slot = 2 THEN  " . $this->filters["PONDERATION"][1] . "
                    WHEN k_skill.slot = 3 THEN  " . $this->filters["PONDERATION"][2] . "
                    WHEN k_skill.slot = 4 THEN  " . $this->filters["PONDERATION"][3] . "
                    ELSE 1
                  END
                ) 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.uid = '$UID' AND
                unit.unit = k_unit.id AND
                unit_skill.unit = unit.id AND ";
            switch ($this->filters["UNIT"]){
                case 0: // All units, do ot fiter.
                    break;
                case 1: // Units in teams.
                    $s .= "
                      (
                        unit.id IN (
                          SELECT DISTINCT unit 
                          FROM
                            team,
                            team_unit 
                          WHERE
                            team.id = team_unit.team AND
                            team.uid = '$UID'
                        )
                      ) AND ";
                    break;
                case 2: // Units in teams with score.
                    $s .= "
                      (
                        unit.id IN (
                          SELECT DISTINCT unit 
                          FROM
                            team,
                            team_unit 
                          WHERE
                            team.id = team_unit.team AND
                            team.uid = '$UID' AND
                            team.score > 0
                        )
                      ) AND ";
                    break;
            }
            if (!$this->filters["HOMUNCULUS"]){
                $s .= "
                  k_unit.homunculus <> 1 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
                  ";
              }
              else{
                $s .= "
                  (
                    (
                      k_unit.homunculus <> 1 AND 
                      unit_skill.skill = k_unit_skill.skill AND
                      k_unit.id = k_unit_skill.unit AND
                      k_skill.id = k_unit_skill.skill
                    ) OR
                    (
                      unit.homunculus = 1 AND 
                      unit.unit = k_unit.id AND
                      unit_skill.unit = unit.id AND
                      unit_skill.skill = k_skill.id AND
                      unit.id = unit_skill.unit
                    )
                  ) AND
                  ";
              }
              $s .= "
                k_skill.max_level > 1 AND
                unit.stars >= " . $this->filters["MIN_STARS"] . " AND
                unit.stars <= " . $this->filters["MAX_STARS"]
            ;
            if ($this->filters["2A"]){
                $s = $s . "
                    AND ((
                      k_unit.natural_stars >= " . $this->filters["MIN_NATURAL_STARS"] . " AND
                      k_unit.natural_stars <= " . $this->filters["MAX_NATURAL_STARS"] . "
                    ) OR (
                      k_unit.base_stars - k_unit.natural_stars > 1
                    ))
                ";
            }
            else{
                $s = $s . "
                    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"]){
                $s = $s . " AND unit.building <> " . BUILDING_ID::MONSTER_STORAGE;
            }
            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 ";
                }
            }
            $s = $s .
              "GROUP BY unit.id ";
            if (!$this->filters["MAXED"]){
                $s = $s . " HAVING ponderated_lvl < ponderated_max_lvl ";
            }
            $s = $s . "
              ORDER BY
                CAST(ponderated_lvl * 100 AS FLOAT) / CAST(ponderated_max_lvl * 100 AS FLOAT),
                unit.stars DESC,
                unit.level DESC,
                CAST(lvl AS FLOAT) / CAST(max_lvl AS FLOAT),
                max_lvl - lvl DESC
            ";
            return $s;
        }
    }
?>