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 block_dash\local\dash_framework\query_builder\exception\invalid_operator_exception;
use block_dash\local\dash_framework\query_builder\exception\invalid_where_clause_exception;
use coding_exception;
use dml_exception;

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

    /**
     * @var int Unique counter for param/placeholder names.
     */
    public static $paramcounter = 0;

    /**
     * Sql equal sign.
     */
    const OPERATOR_EQUAL = '=';
    /**
     * SQL not equal.
     */
    const OPERATOR_NOT_EQUAL = '!=';
    /**
     * SQL IN operator.
     */
    const OPERATOR_IN = 'in';
    /**
     * SQL IN query.
     */
    const OPERATOR_IN_QUERY = 'in_query';

    /**
     * SQL grader than.
     */
    const OPERATOR_GREATERTHAN = '>';

    /**
     * SQL greater than or equal.
     */
    const OPERATOR_GREATERTHAN_EQUAL = '>=';

    /**
     * SQL less than.
     */
    const OPERATOR_LESSTHAN = '<';

    /**
     * SQL less than or equal.
     */
    const OPERATOR_LESSTHAN_EQUAL = '<=';

    /**
     * SQL Like query.
     */
    const OPERATOR_LIKE = 'like';

    /**
     * SQL Not like query.
     */
    const OPERATOR_NOT_LIKE = 'not_like';

    /**
     * SQL Not IN.
     */
    const OPERATOR_NOT_IN = 'not_in';

    /**
     * SQL Not IN query.
     */
    const OPERATOR_NOT_IN_QUERY = 'not_in_query';

    /**
     * SQL conjection of OR.
     */
    const CONJUNCTIVE_OPERATOR_OR = 'OR';

    /**
     * SQL conjection of AND.
     */
    const CONJUNCTIVE_OPERATOR_AND = 'AND';

    /**
     * @var string Field or subquery.
     */
    private $selector;

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

    /**
     * @var string See self::OPERATION_*
     */
    private $operator;

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

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

    /**
     * Conjunctive operator.
     *
     * @var string
     */
    private $conjunctive;

    /**
     * Create new where condition.
     *
     * @param string $selector
     * @param array $values
     * @param string $operator
     * @param string $conjunctive
     */
    public function __construct(string $selector, array $values, string $operator = self::OPERATOR_EQUAL,
        string $conjunctive = self::CONJUNCTIVE_OPERATOR_AND) {
        $this->selector = $selector;
        $this->values = array_values($values);
        $this->operator = $operator;
        $this->conjunctive = $conjunctive;
    }

    /**
     * Set sql query to build.
     *
     * @param string $query
     * @param array $params
     */
    public function set_query(string $query, array $params = []) {
        $this->query = $query;
        $this->queryparams = $params;
        $this->operator = self::OPERATOR_IN_QUERY;
    }

    /**
     * Conjunctive operator for this where clasuse with previous.
     *
     * @return string
     */
    public function get_conjunctive_operator() {
        return $this->conjunctive ?: self::CONJUNCTIVE_OPERATOR_AND;
    }

    /**
     * Get sql query and parameters with processed operators.
     *
     * @return array<string, array>
     * @throws invalid_operator_exception
     * @throws invalid_where_clause_exception
     * @throws dml_exception|coding_exception
     */
    public function get_sql_and_params(): array {
        global $DB;

        $sql = '';
        // Named parameters.
        $params = [];

        // First ensure this where clause is valid.
        switch ($this->operator) {
            case self::OPERATOR_EQUAL:
            case self::OPERATOR_IN:
                if (empty($this->values)) {
                    throw new invalid_where_clause_exception();
                }
                break;
        }

        // Build SQL and params.
        switch ($this->operator) {
            case self::OPERATOR_EQUAL:
            case self::OPERATOR_NOT_EQUAL:
            case self::OPERATOR_GREATERTHAN:
            case self::OPERATOR_LESSTHAN:
            case self::OPERATOR_GREATERTHAN_EQUAL:
            case self::OPERATOR_LESSTHAN_EQUAL:
                $placeholder = self::get_param_name();
                $sql = sprintf('%s %s :%s', $this->selector, $this->operator, $placeholder);
                $params[$placeholder] = $this->values[0];
                break;
            case self::OPERATOR_IN:
                // At this point we are guaranteed at least one value being applied.
                [$psql, $pparams] = $DB->get_in_or_equal($this->values, SQL_PARAMS_NAMED, 'p');
                $sql = sprintf('%s %s', $this->selector, $psql);
                $params = array_merge($params, $pparams);
                break;
            case self::OPERATOR_NOT_IN:
                // At this point we are guaranteed at least one value being applied.
                [$psql, $pparams] = $DB->get_in_or_equal($this->values, SQL_PARAMS_NAMED, 'p', false);
                $sql = sprintf('%s %s', $this->selector, $psql);
                $params = array_merge($params, $pparams);
                break;
            case self::OPERATOR_IN_QUERY:
                $sql = sprintf('%s IN (%s)', $this->selector, $this->query);
                $params = array_merge($params, $this->queryparams);
                break;
            case self::OPERATOR_NOT_IN_QUERY:
                $sql = sprintf('%s NOT IN (%s)', $this->selector, $this->query);
                $params = array_merge($params, $this->queryparams);
                break;
            case self::OPERATOR_LIKE:
                $placeholder = self::get_param_name();
                $sql = $DB->sql_like($this->selector, ':'.$placeholder);
                $params[$placeholder] = isset($this->values[0]) ? '%'.$this->values[0].'%' : '';
                break;
            case self::OPERATOR_NOT_LIKE:
                $placeholder = self::get_param_name();
                $sql = $DB->sql_like($this->selector, ':'.$placeholder, true, true, true);
                $params[$placeholder] = isset($this->values[0]) ? '%'.$DB->sql_like_escape($this->values[0]).'%' : '';
                break;
            default:
                throw new invalid_operator_exception('', ['operator' => $this->operator]);
        }

        return [$sql, $params];
    }

    /**
     * Get unique parameter name.
     *
     * @param string $prefix
     * @return string
     */
    public static function get_param_name(string $prefix = 'p_'): string {
        return $prefix . self::$paramcounter++;
    }
}