FacturaScripts\Core\DbQuery

Complexity

Total Complexity

76

Size/Duplication

Total Lines	452
Duplicated Lines	0 %

Importance

Changes	4
Bugs	0	Features	0

44 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	3	1
A	array()	0	8	2
A	avg()	0	8	2
A	__call()	0	14	3
A	avgArray()	0	5	1
A	selectRaw()	0	5	1
A	delete()	0	9	2
A	whereNull()	0	5	1
A	whereLt()	0	5	1
A	table()	0	3	1
A	whereBetween()	0	5	1
A	select()	0	10	2
A	minArray()	0	5	1
A	maxArray()	0	5	1
A	insertGetId()	0	7	2
A	orderMulti()	0	7	2
A	max()	0	8	2
A	insert()	0	23	5
A	reorder()	0	5	1
A	offset()	0	5	1
A	where()	0	17	4
A	whereNotEq()	0	5	1
A	whereNotNull()	0	5	1
A	count()	0	15	5
A	sql()	0	21	5
A	limit()	0	5	1
A	orderBy()	0	10	2
A	min()	0	8	2
A	whereGte()	0	5	1
A	countArray()	0	5	1
A	sum()	0	8	2
A	groupBy()	0	10	2
A	whereNotIn()	0	5	1
A	whereGt()	0	5	1
A	whereLte()	0	5	1
A	get()	0	3	1
A	whereLike()	0	5	1
A	having()	0	5	1
A	whereIn()	0	5	1
A	first()	0	10	2
A	whereEq()	0	5	1
A	db()	0	7	2
A	sumArray()	0	5	1
A	update()	0	13	3

<?php
/**
 * This file is part of FacturaScripts
 * Copyright (C) 2023-2024 Carlos Garcia Gomez <carlos@facturascripts.com>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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 Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

namespace FacturaScripts\Core;

use Exception;
use FacturaScripts\Core\Base\DataBase;

/**
 * @author Carlos García Gómez <carlos@facturascripts.com>
 */
final class DbQuery
{
    /** @var DataBase */
    private static $db;

    /** @var string */
    public $fields = '*';

    /** @var string */
    public $groupBy;

    /** @var string */
    public $having;

    /** @var int */
    public $limit = 0;

    /** @var int */
    public $offset = 0;

    /** @var array */
    public $orderBy = [];

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

    /** @var Where[] */
    private $where = [];

    public function __construct(string $table)
    {
        $this->table = $table;
    }

    public function __call($method, $parameters)
    {
        // Si se llama al where dinámicamente
        // whereNombre(), whereCiudad()
        if (str_starts_with($method, 'where')) {
            $field = strtolower(substr($method, 5));
            return $this->whereEq($field, $parameters[0]);
        }

        if (false === method_exists($this, $method)) {
            throw new Exception('Call to undefined method ' . get_class($this) . '::' . $method . '()');
        }

        return $this->$method(...$parameters);
    }

    public function array(string $key, string $value): array
    {
        $result = [];
        foreach ($this->get() as $row) {
            $result[$row[$key]] = $row[$value];
        }

        return $result;
    }

    public function avg(string $field, ?int $decimals = null): float
    {
        $this->fields = 'AVG(' . self::db()->escapeColumn($field) . ') as _avg';

        $row = $this->first();
        return is_null($decimals) ?
            (float)$row['_avg'] :
            round((float)$row['_avg'], $decimals);
    }

    public function avgArray(string $field, string $groupByKey): array
    {
        $this->fields = self::db()->escapeColumn($groupByKey) . ', AVG(' . self::db()->escapeColumn($field) . ') as _avg';

        return $this->groupBy($groupByKey)->array($groupByKey, '_avg');
    }

    public function count(string $field = ''): int
    {
        if ($field !== '') {
            $this->fields = 'COUNT(DISTINCT ' . self::db()->escapeColumn($field) . ') as _count';
        } elseif ($this->fields === '*' || empty($this->fields)) {
            $this->fields = 'COUNT(*) as _count';
        } else {
            $this->fields = 'COUNT(' . $this->fields . ') as _count';
        }

        foreach ($this->first() as $value) {
            return (int)$value;
        }

        return 0;
    }

    public function countArray(string $field, string $groupByKey): array
    {
        $this->fields = self::db()->escapeColumn($groupByKey) . ', COUNT(' . self::db()->escapeColumn($field) . ') as _count';

        return $this->groupBy($groupByKey)->array($groupByKey, '_count');
    }

    public function delete(): bool
    {
        $sql = 'DELETE FROM ' . self::db()->escapeColumn($this->table);

        if (!empty($this->where)) {
            $sql .= ' WHERE ' . Where::multiSql($this->where);
        }

        return self::db()->exec($sql);
    }

    public function first(): array
    {
        $this->limit = 1;
        $this->offset = 0;

        foreach ($this->get() as $row) {
            return $row;
        }

        return [];
    }

    public function get(): array
    {
        return self::db()->selectLimit($this->sql(), $this->limit, $this->offset);
    }

    public function groupBy(string $fields): self
    {
        $list = [];
        foreach (explode(',', $fields) as $field) {
            $list[] = self::db()->escapeColumn(trim($field));
        }

        $this->groupBy = implode(', ', $list);

        return $this;
    }

    public function having(string $having): self
    {
        $this->having = $having;

        return $this;
    }

    public function insert(array $data): bool
    {
        if (empty($data)) {
            return false;
        }

        $fields = [];
        foreach (array_keys($data[0]) as $field) {
            $fields[] = self::db()->escapeColumn($field);
        }

        $values = [];
        foreach ($data as $row) {
            $line = [];
            foreach ($row as $value) {
                $line[] = self::db()->var2str($value);
            }
            $values[] = '(' . implode(', ', $line) . ')';
        }

        $sql = 'INSERT INTO ' . self::db()->escapeColumn($this->table)
            . ' (' . implode(', ', $fields) . ') VALUES ' . implode(', ', $values) . ';';
        return self::db()->exec($sql);
    }

    public function insertGetId(array $data): ?int
    {
        if ($this->insert($data)) {
            return self::db()->lastval();

The expression return self::db()->lastval() could return the type boolean which is incompatible with the type-hinted return integer|null. Consider adding an additional type-check to rule them out.

        }

        return null;
    }

    public function limit(int $limit): self
    {
        $this->limit = $limit;

        return $this;
    }

    public function max(string $field, ?int $decimals = null): float
    {
        $this->fields = 'MAX(' . self::db()->escapeColumn($field) . ') as _max';

        $row = $this->first();
        return is_null($decimals) ?
            (float)$row['_max'] :
            round((float)$row['_max'], $decimals);
    }

    public function maxArray(string $field, string $groupByKey): array
    {
        $this->fields = self::db()->escapeColumn($groupByKey) . ', MAX(' . self::db()->escapeColumn($field) . ') as _max';

        return $this->groupBy($groupByKey)->array($groupByKey, '_max');
    }

    public function min(string $field, ?int $decimals = null): float
    {
        $this->fields = 'MIN(' . self::db()->escapeColumn($field) . ') as _min';

        $row = $this->first();
        return is_null($decimals) ?
            (float)$row['_min'] :
            round((float)$row['_min'], $decimals);
    }

    public function minArray(string $field, string $groupByKey): array
    {
        $this->fields = self::db()->escapeColumn($groupByKey) . ', MIN(' . self::db()->escapeColumn($field) . ') as _min';

        return $this->groupBy($groupByKey)->array($groupByKey, '_min');
    }

    public function offset(int $offset): self
    {
        $this->offset = $offset;

        return $this;
    }

    public function orderBy(string $field, string $order = 'ASC'): self
    {
        // si el campo comienza por integer: hacemos el cast a integer
        if (0 === strpos($field, 'integer:')) {
            $field = self::db()->castInteger(substr($field, 8));
        }

        $this->orderBy[] = self::db()->escapeColumn($field) . ' ' . $order;

        return $this;
    }

    public function orderMulti(array $fields): self
    {
        foreach ($fields as $field => $order) {
            $this->orderBy($field, $order);
        }

        return $this;
    }

    public function reorder(): self
    {
        $this->orderBy = [];

        return $this;
    }

    public function select(string $fields): self
    {
        $list = [];
        foreach (explode(',', $fields) as $field) {
            $list[] = self::db()->escapeColumn(trim($field));
        }

        $this->fields = implode(', ', $list);

        return $this;
    }

    public function selectRaw(string $fields): self
    {
        $this->fields = $fields;

        return $this;
    }

    public function sql(): string
    {
        $sql = 'SELECT ' . $this->fields . ' FROM ' . self::db()->escapeColumn($this->table);

        if (!empty($this->where)) {
            $sql .= ' WHERE ' . Where::multiSql($this->where);
        }

        if (!empty($this->groupBy)) {
            $sql .= ' GROUP BY ' . $this->groupBy;
        }

        if (!empty($this->having)) {
            $sql .= ' HAVING ' . $this->having;
        }

        if (!empty($this->orderBy)) {
            $sql .= ' ORDER BY ' . implode(', ', $this->orderBy);
        }

        return $sql;
    }

    public function sum(string $field, ?int $decimals = null): float
    {
        $this->fields = 'SUM(' . self::db()->escapeColumn($field) . ') as _sum';

        $row = $this->first();
        return is_null($decimals) ?
            (float)$row['_sum'] :
            round((float)$row['_sum'], $decimals);
    }

    public function sumArray(string $field, string $groupByKey): array
    {
        $this->fields = self::db()->escapeColumn($groupByKey) . ', SUM(' . self::db()->escapeColumn($field) . ') as _sum';

        return $this->groupBy($groupByKey)->array($groupByKey, '_sum');
    }

    public static function table(string $table): self
    {
        return new self($table);
    }

    public function update(array $data): bool
    {
        if (empty($data)) {
            return false;
        }

        $fields = [];
        foreach ($data as $field => $value) {
            $fields[] = self::db()->escapeColumn($field) . ' = ' . self::db()->var2str($value);
        }

        $sql = 'UPDATE ' . self::db()->escapeColumn($this->table) . ' SET ' . implode(', ', $fields);
        return self::db()->exec($sql);
    }

    /**
     * @param Where[] $where
     * @return $this
     * @throws Exception
     */
    public function where(array $where): self
    {
        // si el array está vacío, no hacemos nada
        if (empty($where)) {
            return $this;
        }

        foreach ($where as $value) {
            // si no es una instancia de Where, lanzamos una excepción
            if (!($value instanceof Where)) {
                throw new Exception('Invalid where clause ' . print_r($value, true));

Are you sure print_r($value, true) of type string|true can be used in concatenation?

            }

            $this->where[] = $value;
        }

        return $this;
    }

    public function whereBetween(string $field, $value1, $value2): self
    {
        $this->where[] = Where::between($field, $value1, $value2);

        return $this;
    }

    public function whereEq(string $field, $value): self
    {
        $this->where[] = Where::eq($field, $value);

        return $this;
    }

    public function whereGt(string $field, $value): self
    {
        $this->where[] = Where::gt($field, $value);

        return $this;
    }

    public function whereGte(string $field, $value): self
    {
        $this->where[] = Where::gte($field, $value);

        return $this;
    }

    public function whereIn(string $field, array $values): self
    {
        $this->where[] = Where::in($field, $values);

        return $this;
    }

    public function whereLike(string $field, string $value): self
    {
        $this->where[] = Where::like($field, $value);

        return $this;
    }

    public function whereLt(string $field, $value): self
    {
        $this->where[] = Where::lt($field, $value);

        return $this;
    }

    public function whereLte(string $field, $value): self
    {
        $this->where[] = Where::lte($field, $value);

        return $this;
    }

    public function whereNotEq(string $field, $value): self
    {
        $this->where[] = Where::notEq($field, $value);

        return $this;
    }

    public function whereNotIn(string $field, array $values): self
    {
        $this->where[] = Where::notIn($field, $values);

        return $this;
    }

    public function whereNotNull(string $field): self
    {
        $this->where[] = Where::isNotNull($field);

        return $this;
    }

    public function whereNull(string $field): self
    {
        $this->where[] = Where::isNull($field);

        return $this;
    }

    private static function db(): DataBase
    {
        if (null === self::$db) {
            self::$db = new DataBase();
        }

        return self::$db;
    }
}