Completed
Push — master ( 467d47...6f2bb4 )
by Anton
14s
created

SqlSource::applyOrders()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 10
Code Lines 6

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 4
CRAP Score 2.1481

Importance

Changes 0
Metric Value
nc 2
dl 0
loc 10
ccs 4
cts 6
cp 0.6667
c 0
b 0
f 0
cc 2
eloc 6
nop 1
crap 2.1481
rs 9.4285
1
<?php
2
/**
3
 * Bluz Framework Component
4
 *
5
 * @copyright Bluz PHP Team
6
 * @link      https://github.com/bluzphp/framework
7
 */
8
9
declare(strict_types=1);
10
11
namespace Bluz\Grid\Source;
12
13
use Bluz\Db;
14
use Bluz\Grid;
15
use Bluz\Grid\Data;
16
use Bluz\Proxy;
17
18
/**
19
 * SQL Source Adapter for Grid package
20
 *
21
 * @package  Bluz\Grid
22
 * @author   Anton Shevchuk
23
 *
24
 * @method   string getSource() SQL query
25
 */
26
class SqlSource extends AbstractSource
27
{
28
    /**
29
     * Set SQL source
30
     *
31
     * @param  string $source
32
     *
33
     * @return void
34
     * @throws \Bluz\Grid\GridException
35
     */
36 2
    public function setSource($source) : void
37
    {
38 2
        if (!is_string($source)) {
39 1
            throw new Grid\GridException('Source of `SqlSource` should be string with SQL query');
40
        }
41 1
        parent::setSource($source);
42 1
    }
43
44
    /**
45
     * {@inheritdoc}
46
     */
47 1
    public function process(int $page, int $limit, array $filters = [], array $orders = []) : Data
48
    {
49
        // process filters
50 1
        $filters = $this->applyFilters($filters);
51
52
        // process orders
53 1
        $orders = $this->applyOrders($orders);
54
55
        // prepare query
56 1
        $type = Proxy\Db::getOption('connect', 'type');
57
58 1
        if (strtolower($type) === 'mysql') {
59
            // MySQL
60 1
            $dataSql = preg_replace('/SELECT\s(.*?)\sFROM/is', 'SELECT SQL_CALC_FOUND_ROWS $1 FROM', $this->source, 1);
61 1
            $totalSql = 'SELECT FOUND_ROWS()';
62
        } else {
63
            // other
64
            $dataSql = $this->source;
65
            $totalSql = preg_replace('/SELECT\s(.*?)\sFROM/is', 'SELECT COUNT(*) FROM', $this->source, 1);
66
            if (count($filters)) {
67
                $totalSql .= ' WHERE ' . implode(' AND ', $filters);
68
            }
69
        }
70
71 1
        if (count($filters)) {
72 1
            $dataSql .= ' WHERE ' . implode(' AND ', $filters);
73
        }
74 1
        if (count($orders)) {
75
            $dataSql .= ' ORDER BY ' . implode(', ', $orders);
76
        }
77
        // process pages
78 1
        $dataSql .= ' LIMIT ' . ($page - 1) * $limit . ', ' . $limit;
79
80
        // run queries
81
        // use transaction to avoid errors
82 1
        Proxy\Db::transaction(
83 1
            function () use (&$data, &$total, $dataSql, $totalSql) {
84 1
                $data = Proxy\Db::fetchAll($dataSql);
85 1
                $total = (int)Proxy\Db::fetchOne($totalSql);
86 1
            }
87
        );
88
89 1
        $gridData = new Data($data);
90 1
        $gridData->setTotal($total);
91 1
        return $gridData;
92
    }
93
94
    /**
95
     * Apply filters to SQL query
96
     *
97
     * @param  array[] $settings
98
     *
99
     * @return array
100
     */
101 1
    private function applyFilters(array $settings) : array
102
    {
103 1
        $where = [];
104 1
        foreach ($settings as $column => $filters) {
105 1
            foreach ($filters as $filter => $value) {
106 1
                if ($filter === Grid\Grid::FILTER_LIKE) {
107 1
                    $value = '%' . $value . '%';
108
                }
109 1
                $where[] = $column . ' ' .
110 1
                    $this->filters[$filter] . ' ' .
111 1
                    Proxy\Db::quote((string)$value);
112
            }
113
        }
114 1
        return $where;
115
    }
116
117
    /**
118
     * Apply order to SQL query
119
     *
120
     * @param  array $settings
121
     *
122
     * @return array
123
     */
124 1
    private function applyOrders(array $settings) : array
125
    {
126 1
        $orders = [];
127
        // Obtain a list of columns
128 1
        foreach ($settings as $column => $order) {
129
            $column = Proxy\Db::quoteIdentifier($column);
130
            $orders[] = $column . ' ' . $order;
131
        }
132 1
        return $orders;
133
    }
134
}
135