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.
 *
 * @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
 */

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.
     */
    private $units = [];

    /**
     * @var mixed[] Default filter values.
     */
    private $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" => 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.
     */
    public function __construct(){
        $this->view = PATH::VIEW . "report_skillup.php";
        $this->parse_filters();
        $result_set = $this->prepare_statement()->execute();
        while ($result = $result_set->fetchArray(SQLITE3_ASSOC)){
            array_push($this->units, new Unit($result["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(){

        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;
        }
        else{
            $this->filters["2A"] = true;
        }
        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;
    }

    /**
     * Prepares the statement to execute against the database using the filter values.
     *
     * @return SQLite3Stmt prepared statement.
     */
    private function prepare_statement(){
        // Common items
        $query = "
          SELECT DISTINCT
            unit.id AS id,
            --sum(unit_skill.level * (CAST(skill.slot + 1 AS FLOAT) / 2.0)) AS ponderated_lvl,
            sum(unit_skill.level *
              CASE
                WHEN skill.slot = 1 THEN :ponderation_0
                WHEN skill.slot = 2 THEN :ponderation_1
                WHEN skill.slot = 3 THEN :ponderation_2
                WHEN skill.slot = 4 THEN :ponderation_3
                ELSE 1
                END
            ) AS ponderated_lvl,
            sum(unit_skill.level) AS lvl,
            --sum(skill.max_level * (CAST(skill.slot + 1 AS FLOAT) / 2.0)) AS ponderated_max_lvl,
            sum(skill.max_level * 
              CASE
                WHEN skill.slot = 1 THEN :ponderation_0
                WHEN skill.slot = 2 THEN :ponderation_1
                WHEN skill.slot = 3 THEN :ponderation_2
                WHEN skill.slot = 4 THEN :ponderation_3
                ELSE 1
              END
            ) AS ponderated_max_lvl,
            sum(skill.max_level) AS max_lvl
          FROM
            unit,
            monster,
            unit_skill,
            monster_skill,
            skill
          WHERE
            unit.player = :player AND
            unit.monster = monster.id AND
            unit_skill.unit = unit.id  ";
        switch ($this->filters["UNIT"]){
            case 0: // All units, do not fiter.
                break;
            case 1: // Units in teams.
                $query .= "
                  AND (
                    unit.id IN (
                      SELECT DISTINCT unit
                      FROM
                        team,
                        team_unit
                      WHERE
                        team.id = team_unit.team AND
                        team.player = unit.player
                    )
                  ) AND ";
                break;
            case 2: // Units in teams with score.
                $query .= "
                  AND (
                    unit.id IN (
                      SELECT DISTINCT unit
                      FROM
                        team,
                        team_unit
                      WHERE
                        team.id = team_unit.team AND
                        team.player = unit.player AND
                        team.score > 0
                    )
                  ) ";
                break;
        }
        if (!$this->filters["HOMUNCULUS"]){
            $query .= "
              -- TODO: filter by units id
              --monster.homunculus <> 1 AND
              AND unit_skill.skill = monster_skill.skill AND
              monster.id = monster_skill.monster AND
              skill.id = monster_skill.monster
              ";
        }
        else{
            $query .= "
              AND  (
                (
                  monster.homunculus <> 1 AND 
                  unit_skill.skill = monster_skill.skill AND
                  monster.id = monster_skill.monster AND
                  skill.id = monster_skill.skill
                ) OR
                (
                  -- TODO: Flter by unit IDs
                  --unit.homunculus = 1 AND
                  unit.monster = monster.id AND
                  unit_skill.unit = unit.id AND
                  unit_skill.skill = skill.id AND
                  unit.id = unit_skill.unit
                )
              )
              ";
        }
          
        $query .= "
          AND skill.max_level > 1 AND
          unit.stars BETWEEN :min_stars AND :max_stars
        ";
        if ($this->filters["2A"]){
            $query .= "
                AND ((
                  monster.natural_stars BETWEEN :min_natural_stars AND :max_natural_stars
                ) OR (
                  monster.base_stars - monster.natural_stars > 1
                ))
            ";
        }
        else{
            $query .= "
                AND monster.natural_stars BETWEEN :min_natural_stars AND :max_natural_stars
            ";
        }
        if (!$this->filters["IN_STORAGE"]){
            $query .= "
              AND unit.building <> :storage_building
            ";
        }
        if (! $this->filters["WITHOUT_RUNES"]){
            $query .= "
              AND unit.id IN (SELECT DISTINCT unit FROM unit_rune WHERE rta = :rta)
            ";
        }
        if (! $this->filters["FAMILY"]){
            $query .= "
              AND unit.monster NOT IN (SELECT id FROM monster m WHERE family IN (SELECT family FROM monster k WHERE k.family = m.family AND k.natural_stars < m.natural_stars AND k.natural_stars < :min_natural_stars))
            ";
            // Special case: Eirgar (Dark Vampire lord). Nat5 can be powered with 4 star Vampires.
            if ($this->filters["MIN_NATURAL_STARS"] >= 5){
                $query .= " AND monster.id <> 19114 AND monster.id <> 19104 ";
            }
            // Special case: Fran (Light Fairy Queen). Nat3 can be powered with 2 star Fairies.
            if ($this->filters["MIN_NATURAL_STARS"] >= 3){
                $query .= " AND monster.id <> 23015 AND monster.id <> 23005 ";
            }
        }
        $query .= " GROUP BY unit.id ";
        if (! $this->filters["MAXED"]){
            $query .= " HAVING ponderated_lvl < ponderated_max_lvl ";
        }
        $query .= "
          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
        ";
        $statement = get_context()->get_db()->prepare($query);
        for ($i = 0; $i < 4; $i ++){
            $statement->bindValue(":ponderation_$i", $this->filters["PONDERATION"][$i], SQLITE3_FLOAT);
        }
        $statement->bindValue(":player", get_context()->get_player()->get_id(), SQLITE3_TEXT);
        $statement->bindValue(":min_stars", $this->filters["MIN_STARS"], SQLITE3_INTEGER);
        $statement->bindValue(":max_stars", $this->filters["MAX_STARS"], SQLITE3_INTEGER);
        $statement->bindValue(":min_natural_stars", $this->filters["MIN_NATURAL_STARS"], SQLITE3_INTEGER);
        $statement->bindValue(":max_natural_stars", $this->filters["MAX_NATURAL_STARS"], SQLITE3_INTEGER);
        $statement->bindValue(":storage_building", BUILDING_ID::MONSTER_STORAGE, SQLITE3_INTEGER);
        $statement->bindValue(":rta", get_context()->get_game_mode(), SQLITE3_INTEGER);
        return $statement;
    }
    
    /**
     * Retrieves the selected units, sorted by urgency.
     *
     * @return Unit[] Selected units. 
     */
    public function get_units(){
        return $this->units;
    }
    
    /**
     * Retrieves the page filters.
     *
     * @return mixed[] Page filtes
     */
    public function get_filters(){
        return $this->filters;
    }
    
    /**
     * Retrieves one filter.
     *
     * @param string $key
     * @return mixed Filter value, null if if doesn't exist.
     */
    public function get_filter($key){
        if (array_key_exists($key, $this->filters)){
            return $this->filters[$key];
        }
        else{
            return null;
        }
    }

}
?>