SWDB/application/page/Report_Skillup_Page.php

 <?php

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


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

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

        public $filters = [
            "min_stars" => 1,
            "max_stars" => 6,
            "min_natural_stars" => 1,
            "max_natural_stars" => 5,
            "in_storage" => true,
            "without_runes" => true
        ];

        /**
         * 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->monsters, new Monster($db, $r_mon["id"]));
            }
            $this->title = "Urgent skill-ups - SWDB";
            $this->description = "Monster in need os skilling up";
            $this->canonical = $root . "report/skillups";
        }

        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 ($_GET["in_storage"] != "on"){
                $this->filters["in_storage"] = false;
            }
            if ($_GET["without_runes"] != "on"){
                $this->filters["without_runes"] = false;
            }
            return;
        }

        private function build_query(){
            $s =
              "SELECT DISTINCT " .
              "  monster.id AS id, " .
              "  sum( " .
              "    CASE k_skill.slot " .
              "      WHEN 1 THEN (monster.skill_1_level - 1) * 1 " .
              "      WHEN 2 THEN (monster.skill_2_level - 1) * 1.5 " .
              "      WHEN 3 THEN (monster.skill_3_level - 1) * 2 " .
              "      WHEN 4 THEN (monster.skill_4_level - 1) * 2.5 " .
              "      END " .
              "  ) AS ponderated_level, " .
              "  sum( " .
              "    CASE k_skill.slot " .
              "      WHEN 1 THEN (monster.skill_1_level - 1) " .
              "      WHEN 2 THEN (monster.skill_2_level - 1) " .
              "      WHEN 3 THEN (monster.skill_3_level - 1) " .
              "      WHEN 4 THEN (monster.skill_4_level - 1) " .
              "      END " .
              "    ) AS level, " .
              "  sum( " .
              "    CASE k_skill.slot " .
              "      WHEN 1 THEN (k_skill.max_level - 1) * 1 " .
              "      WHEN 2 THEN (k_skill.max_level - 1) * 1.5 " .
              "      WHEN 3 THEN (k_skill.max_level - 1) * 2 " .
              "      WHEN 4 THEN (k_skill.max_level - 1) * 2.5 " .
              "      END " .
              "  ) AS ponderated_max_level, " .
              "  sum( " .
              "    CASE k_skill.slot " .
              "      WHEN 1 THEN (k_skill.max_level - 1) " .
              "      WHEN 2 THEN (k_skill.max_level - 1) " .
              "      WHEN 3 THEN (k_skill.max_level - 1) " .
              "      WHEN 4 THEN (k_skill.max_level - 1) " .
              "      END " .
              "  ) AS max_level " .
              "FROM " .
              "  monster, " .
              "  k_monster_skill, " .
              "  k_skill, " .
              "  k_monster " .
              "WHERE " .
              "  monster.monster = k_monster_skill.monster AND " .
              "  k_skill.id = k_monster_skill.skill AND " .
              "  k_monster.id = monster.monster AND " .
              "  k_skill.max_level > 1 AND " .
              "  monster.stars >= " . $this->filters["min_stars"] . " AND " .
              "  monster.stars <= " . $this->filters["max_stars"] . " AND " .
              "  k_monster.natural_stars >= " . $this->filters["min_natural_stars"] . " AND " .
              "  k_monster.natural_stars <= " . $this->filters["max_natural_stars"] . " ";
              
            if (!$this->filters["in_storage"]){
                $s = $s . " AND monster.in_storage = 0 ";
            }
            if (!$this->filters["without_runes"]){
                $s = $s . " AND monster.id IN (SELECT DISTINCT monster FROM monster_rune) ";
            }
            $s = $s .
              "GROUP BY monster.id " .
              "ORDER BY " .
              "  ponderated_level * 100 / ponderated_max_level, " .
              "  monster.stars DESC, " .
              "  monster.level DESC, " .
              "  level * 100 / max_level, " .
              "  max_level - level DESC; ";
            return $s;
        }
    }
?>