Proyectos de Subversion Moodle

<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * Builds a query.
 *
 * @package    block_dash
 * @copyright  2019 bdecent gmbh <https://bdecent.de>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

namespace block_dash\local\dash_framework\query_builder;

use coding_exception;
use dml_exception;

/**
 * Builds a query.
 *
 * @package block_dash
 */
class builder {

    /**
     * @var string
     */
    private $table;

    /**
     * @var string
     */
    private $tablealias;

    /**
     * @var string[]
     */
    private $selects = [];

    /**
     * @var array
     */
    private $wheres = [];

    /**
     * @var array
     */
    private $rawwhere;

    /**
     * @var array
     */
    private $rawwhereparameters = [];

    /**
     * @var int Return a subset of records, starting at this point (optional).
     */
    private $limitfrom = 0;

    /**
     * @var int Return a subset comprising this many records in total (optional, required if $limitfrom is set).
     */
    private $limitnum = 0;

    /**
     * @var array ['field1' => 'ASC', 'field2' => 'DESC', ...]
     */
    private $orderby = [];

    /**
     * @var join[]
     */
    private $joins = [];

    /**
     * @var array ['field1', 'field2', ...]
     */
    private $groupby = [];

    /**
     * Extra conditions to be added in WHERE clause.
     *
     * @var array
     */
    private $rawconditions = [];

    /**
     * @var array
     */
    private $rawconditionparameters = [];

    /**
     * @var array
     */
    private $rawjoins = [];

    /**
     * @var array
     */
    private $rawjoinsparameters = [];

    /**
     * Fields to retried from sql query. Sql select field.
     * @param string $field
     * @param string $alias
     * @return builder
     */
    public function select(string $field, string $alias): builder {
        $this->selects[$alias] = $field;
        return $this;
    }

    /**
     * Set all selects on builder.
     *
     * @param array $selects [alias => field, ...]
     * @return $this
     */
    public function set_selects(array $selects): builder {
        $this->selects = [];
        foreach ($selects as $alias => $select) {
            $this->selects[$alias] = $select;
        }
        return $this;
    }

    /**
     * Set main table of query.
     *
     * @param string $table
     * @param string $alias
     * @return builder
     */
    public function from(string $table, string $alias): builder {
        $this->table = $table;
        $this->tablealias = $alias;
        return $this;
    }

    /**
     * Join table in query.
     *
     * @param string $table Table name of joined table.
     * @param string $alias Joined table alias.
     * @param string $jointablefield Field of joined table to reference in join condition.
     * @param string $origintablefield Field of origin table to join to.
     * @param string $jointype SQL join type. See self::TYPE_*
     * @param array $extraparameters Extra parameters used in join SQL.
     * @return $this
     */
    public function join(string $table, string $alias, string $jointablefield, string $origintablefield,
                         $jointype = join::TYPE_INNER_JOIN, array $extraparameters = []): builder {
        $this->joins[] = new join($table, $alias, $jointablefield, $origintablefield, $jointype, $extraparameters);
        return $this;
    }

    /**
     * Join raw in query.
     *
     * @param string $joinsql SQL join type. See self::TYPE_*
     * @param array $parameters Extra parameters used in join SQL.
     * @return $this
     */
    public function join_raw(string $joinsql, array $parameters = []): builder {
        $this->rawjoins[] = [$joinsql, $parameters];
        return $this;
    }

    /**
     * Add additional join condition to existing join.
     *
     * @param string $alias
     * @param string $condition
     * @return $this
     * @throws coding_exception
     */
    public function join_condition(string $alias, string $condition): builder {
        $added = false;
        foreach ($this->joins as $join) {
            if ($join->get_alias() == $alias) {
                $join->add_join_condition($condition);
                $added = true;
                break;
            }
        }

        if (!$added) {
            throw new coding_exception('Table alias not found: ' . $alias);
        }

        return $this;
    }

    /**
     * Add where clause to query.
     *
     * @param string $selector Field or alias of where clause.
     * @param array $values Values that where clause will compare to.
     * @param string $operator Equals, greater than, in, etc etc. See where::OPERATOR_*
     * @param string $conjunctive AND, OR etc etc. See where::CONJUCTIVE_OPERATOR_*
     *
     * @return where
     */
    public function where(string $selector, array $values, string $operator = where::OPERATOR_EQUAL,
        string $conjunctive = where::CONJUNCTIVE_OPERATOR_AND): where {
        $where = new where($selector, $values, $operator, $conjunctive);
        $this->wheres[] = $where;
        return $where;
    }

    /**
     * Add where (in subquery) clause to query.
     *
     * @param string $selector Field or alias of where clause.
     * @param string $query Subquery of WHERE IN (subquery) clause.
     * @param array $params Any extra parameters used in subquery.
     * @return where
     */
    public function where_in_query(string $selector, string $query, array $params = []): where {
        $where = new where($selector, []);
        $where->set_query($query, $params);
        $this->wheres[] = $where;
        return $where;
    }

    /**
     * Add where clause to query.
     *
     * @param string $wheresql
     * @param array $parameters
     * @return builder
     */
    public function where_raw(string $wheresql, array $parameters = []): builder {
        $this->rawwhere[] = $wheresql;
        $this->rawwhereparameters = array_merge($this->rawwhereparameters, $parameters);

        return $this;
    }

    /**
     * Order by a field.
     *
     * @param string $field Field or alias to order by.
     * @param string $direction 'ASC' or 'DESC'
     * @return builder
     * @throws coding_exception
     */
    public function orderby(string $field, string $direction): builder {
        if (!in_array(strtolower($direction), ['asc', 'desc'])) {
            throw new coding_exception('Invalid order by direction ' . $direction);
        }

        $this->orderby[$field] = $direction;
        return $this;
    }

    /**
     * Remove order by conditions.
     *
     * @return builder
     */
    public function remove_orderby(): builder {
        $this->orderby = [];
        return $this;
    }

    /**
     * Group by field for aggregations.
     *
     * @param string $field
     * @return builder
     */
    public function groupby(string $field): builder {
        $this->groupby[] = $field;
        return $this;
    }

    /**
     * Add raw condition to builder.
     *
     * @param string $condition
     * @param array $parameters
     * @return builder
     */
    public function rawcondition(string $condition, array $parameters = []): builder {
        $this->rawconditions[] = $condition;
        $this->rawconditionparameters = $parameters;
        return $this;
    }

    /**
     * Get the query where conditions.
     * @return where[]
     */
    public function get_wheres(): array {
        return $this->wheres;
    }

    /**
     * Get the query limit from.
     *
     * @return int
     */
    public function get_limitfrom(): int {
        return $this->limitfrom;
    }

    /**
     * Set the query limit from.
     *
     * @param int $limitfrom
     * @return $this
     */
    public function limitfrom(int $limitfrom): builder {
        $this->limitfrom = $limitfrom;
        return $this;
    }

    /**
     * Get the query limit number.
     *
     * @return int
     */
    public function get_limitnum(): int {
        return $this->limitnum;
    }

    /**
     * Set the query limit number.
     *
     * @param int $limitnum
     * @return $this
     */
    public function limitnum(int $limitnum): builder {
        $this->limitnum = $limitnum;
        return $this;
    }

    /**
     * Build and return complete query select SQL.
     *
     * @return string
     */
    protected function build_select(): string {
        $selects = [];
        foreach ($this->selects as $alias => $select) {
            $selects[] = $select . ' AS ' . $alias;
        }

        return implode(',', $selects);
    }

    /**
     * Get the query where condition and it parameters.
     * @return array
     * @throws exception\invalid_operator_exception
     */
    protected function get_where_sql_and_params(): array {
        $wheresql = [];
        $params = [];
        $wsql = ''; // Where builder queryies.
        foreach ($this->get_wheres() as $where) {
            [$sql, $wparams] = $where->get_sql_and_params();
            $conjunc = $where->get_conjunctive_operator() ?: 'AND';
            $wsql .= !empty($wsql) ? sprintf(' %s %s ', $conjunc, $sql) : $sql;
            $params = array_merge($params, $wparams);
        }

        $wheresql[] = $wsql;

        if ($this->rawwhere) {
            foreach ($this->rawwhere as $where) {
                $wheresql[] = $where;
            }
            $params = array_merge($params, $this->rawwhereparameters);
        }

        return [implode(' AND ', array_filter($wheresql)), $params];
    }

    /**
     * Get the query and required parameters.
     *
     * @return array<string, array>
     * @throws exception\invalid_operator_exception
     */
    final public function get_sql_and_params(): array {
        $sql = 'SELECT DISTINCT ' . $this->build_select() . ' FROM {' . $this->table . '} ' . $this->tablealias;
        $params = [];

        foreach ($this->joins as $join) {
            [$jsql, $jparams] = $join->get_sql_and_params();
            $sql .= ' ' . $jsql . ' ';
            $params = array_merge($params, $jparams);
        }

        foreach ($this->rawjoins as $join) {
            [$jsql, $jparams] = $join;
            $sql .= ' ' . $jsql . ' ';
            $params = array_merge($params, $jparams);
        }

        [$wsql, $wparams] = $this->get_where_sql_and_params();

        if ($wsql) {
            $sql .= ' WHERE ' . $wsql;
            $params = array_merge($params, $wparams);
        }

        if (count($this->groupby) > 0) {
            $sql .= ' GROUP BY ' . implode(', ', $this->groupby);
        }

        if ($this->orderby) {
            $orderbys = [];
            foreach ($this->orderby as $field => $direction) {
                $orderbys[] = sprintf('%s %s', $field, $direction);
            }

            $sql .= ' ORDER BY ' . implode(', ', $orderbys);
        }

        return [$sql, $params];
    }

    /**
     * Execute query and return results.
     *
     * @return array
     * @throws dml_exception
     * @throws exception\invalid_operator_exception
     */
    public function query() {
        global $DB;

        [$sql, $params] = $this->get_sql_and_params();

        return $DB->get_records_sql($sql, $params, $this->get_limitfrom(), $this->get_limitnum());
    }

    /**
     * Get number of records this query will return.
     *
     * @return int
     * @throws dml_exception
     * @throws exception\invalid_operator_exception
     */
    public function count(): int {
        $builder = clone $this;
        $builder->set_selects(['count' => 'COUNT(DISTINCT ' . $this->tablealias . '.id)']);
        $builder->limitfrom(0)->limitnum(0)->remove_orderby();
        if (!$records = $builder->query()) {
            return 0;
        }
        return array_values($records)[0]->count;
    }
}