SWDB/application/page/Report_Runecraft_Page.php

 <?php

    require_once($path["page"] . "Page.php");
    require_once($path["entity"] . "Unit.php");
    require_once($path["entity"] . "Rune.php");
    require_once($path["entity"] . "Rune_Craft.php");
    //include($path["application"] . "Filter.php");

    /**
     * Upgradeable runes report page.
     */
    class Report_Runecraft_Page extends Page{

        /**
         * Filters the report can handle.
         */
        public $filters;

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

        /**
         * 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_runecraft.php";
            $this->parse_filters();
            $s = $this->build_query();
error_log($s);
            $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["rune"]),
                        "craft" => [],
                    ];
                }
                if ($r["rune"] != $prev_rune){
                    if ($prev_rune != ""){
                        array_push($upgrade["upgrade"], $rupgrade);
                    }
                    $prev_rune = $r["rune"];
                    $rupgrade = [
                        "rune" => new Rune($this->db, $r["rune"]),
                        "craft" => [],
                    ];
                }
                $rune_craft = new Rune_craft($this->db, $r["craft"]);
                array_push($rupgrade["craft"], $rune_craft);
            }
            // Last entries
            array_push($upgrade["upgrade"], $rupgrade);
            array_push($this->upgrades, $upgrade);
            $this->title = "Rune craft - SWDB";
            $this->description = "Find runes that can be upgraded via crafting.";
            $this->canonical = $root . "report/runecraft/";
        }

        /**
         * 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(){
            //global $RUNE_STAT;
            global $FILTER_RUNECRAFT;
            global $FILTER_SKILLUP;
            $this->filters = $FILTER_RUNECRAFT;
            // 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_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["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["NORMAL"] && intval($_GET["rune_min_quality"]) <= $QUALITY["LEGEND"]){
                $this->filters["rune_min_quality"] = $_GET["rune_min_quality"];
            }
            if (isset($_GET["rune_max_quality"]) && intval($_GET["rune_max_quality"]) >= $QUALITY["NORMAL"] && intval($_GET["rune_max_quality"]) <= $QUALITY["LEGEND"]){
                $this->filters["rune_max_quality"] = $_GET["rune_max_quality"];
            }
            if (isset($_GET["rune_min_original_quality"]) && intval($_GET["rune_min_original_quality"]) >= $QUALITY["NORMAL"] && intval($_GET["rune_min_original_quality"]) <= $QUALITY["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["NORMAL"] && intval($_GET["rune_max_original_quality"]) <= $QUALITY["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));
                    }
                }
            }
            // Grindstones
            if (isset($_GET["grind_min_quality"]) && intval($_GET["grind_min_quality"]) >= $QUALITY["NORMAL"] && intval($_GET["grind_min_quality"]) < $QUALITY["LEGEND"]){
                $this->filters["grind_min_quality"] = $_GET["grind_min_quality"];
            }
            if (isset($_GET["grind_stat"])){
                $stats = $_GET["grind_stat"];
                $this->filters["grind_stat"] = [];
                foreach ($stats as $stat){
                    array_push($this->filters["grind_stat"], intval($stat));
                }
            }
            if (isset($_GET["gem_stat_from"])){
                $stats = $_GET["gem_stat_from"];
                $this->filters["gem_stat_from"] = [];
                foreach ($stats as $stat){
                    array_push($this->filters["gem_stat_from"], intval($stat));
                }
            }
            if (isset($_GET["gem_stat_to"])){
                $stats = $_GET["gem_stat_to"];
                $this->filters["gem_stat_to"] = [];
                foreach ($stats as $stat){
                    array_push($this->filters["gem_stat_to"], intval($stat));
                }
            }
            return;
        }

        /**
         * Builds the query for the page, using the providded or default filters.
         *
         * @return The query to be executed.
         */
        private function build_query(){
            $s = "
                SELECT
                  unit.id AS unit,
                  rune.id AS rune,
                  rune_craft.id AS craft
                FROM
                  unit,
                  k_unit,
                  rune,
                  rune_craft,
                  k_rune_craft_type
                WHERE
                  unit.id = rune.assigned_to AND
                  unit.unit = k_unit.id AND
                  rune_craft.type = k_rune_craft_type.id AND
                  unit.stars >= " . $this->filters["rune_min_stars"] . " AND
                  unit.stars <= " . $this->filters["rune_max_stars"] . " AND
                  -- RUNE FILTERS
                  rune.quality >= " . $this->filters["rune_min_quality"] . " AND
                  rune.quality <= " . $this->filters["rune_max_quality"] . " AND
                  rune.level >= " . $this->filters["rune_min_level"] . " AND
                  rune.level <= " . $this->filters["rune_max_level"] . " AND
                  rune.stars >= " . $this->filters["rune_min_stars"] . " AND
                  rune.stars <= " . $this->filters["rune_max_stars"] . " 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_craft.rune OR 
                    k_rune_craft_type.inmemorial = 1
                  ) AND
                  k_rune_craft_type.ancient = rune.ancient AND
                ";
            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.in_storage = 0 ";
            }
            if (count($this->filters["rune_slot"]) > 0){
                $s = $s . " rune.slot IN ( ";
                foreach($this->filters["rune_slot"] as $t){
                    $s = $s . "$t, ";
                }
                $s = $s . "-1) AND ";
            }
            $s .= "
                rune_craft.stat <> rune.main_stat AND
                rune_craft.stat <> rune.innate_stat AND
                  (
                    k_rune_craft_type.gem = 1 AND
                    (
                      -- GEM FILTERS
                      rune_craft.quality >= " . $this->filters["gem_min_quality"] . " AND
            ";
            if (count($this->filters["gem_stat_from"]) > 0){
                $in = "(";
                foreach($this->filters["gem_stat_from"] as $t){
                    $in = $in . "$t, ";
                }
                $in = $in . "-1)";
                $s .= " (rune.substat_1 IN $in OR rune.substat_2 IN $in OR rune.substat_3 IN $in OR rune.substat_4 IN $in) AND ";
            }
            if (count($this->filters["gem_stat_to"]) > 0){
                $in = "(";
                foreach($this->filters["gem_stat_to"] as $t){
                    $in = $in . "$t, ";
                }
                $in = $in . "-1)";
                $s .= " rune_craft.stat IN $in AND ";
            }
            $s .= "
                      (
                        rune_craft.stat <> rune.substat_1 AND
                        rune_craft.stat <> rune.substat_2 AND
                        rune_craft.stat <> rune.substat_3 AND
                        rune_craft.stat <> rune.substat_4
                      )
                    )
                  ) |
                  (
                    k_rune_craft_type.gem = 0 AND
                    (
                      -- GRINDSTONE FILTERS
            ";
            if (count($this->filters["grind_stat"]) > 0){
                $in = "(";
                foreach($this->filters["grind_stat"] as $t){
                    $in = $in . "$t, ";
                }
                $in = $in . "-1)";
                $s .= " rune_craft.stat IN $in AND ";
            }
            $s .= "
                      rune_craft.quality >= " . $this->filters["grind_min_quality"] . " AND
                      (
                        (
                          rune_craft.stat = rune.substat_1 AND
                          rune_craft.stat <> rune.substat_2 AND
                          rune_craft.stat <> rune.substat_3 AND
                          rune_craft.stat <> rune.substat_4
                        ) OR
                        (
                          rune_craft.stat <> rune.substat_1 AND
                          rune_craft.stat = rune.substat_2 AND
                          rune_craft.stat <> rune.substat_3 AND
                          rune_craft.stat <> rune.substat_4
                        ) OR
                        (
                          rune_craft.stat <> rune.substat_1 AND
                          rune_craft.stat <> rune.substat_2 AND
                          rune_craft.stat = rune.substat_3 AND
                          rune_craft.stat <> rune.substat_4
                        ) OR
                        (
                          rune_craft.stat <> rune.substat_1 AND
                          rune_craft.stat <> rune.substat_2 AND
                          rune_craft.stat <> rune.substat_3 AND
                          rune_craft.stat = rune.substat_4
                        )
                      )
                    )
                  )
                ";
            $s .= "
                ORDER BY
                  unit.stars DESC,
                  unit.level DESC,
                  unit.id,
                  rune.stars DESC,
                  rune.original_quality DESC,
                  rune.level DESC,
                  rune.id, 
                  rune.slot, 
                  rune_craft.quality DESC,
                  rune_craft.stat DESC;
                ";
            return $s;
        }
    }
?>