contributte / datagrid-nette-database-data-source / #6

Committed 14 Mar 2026 11:10AM UTC coverage: 75.0% (-1.1%) from 76.05%

Build # #6

Build Type

push

github

Committed by

f3l1x

Commit Message

Makefile: run coverage targets with php

Use the PHP CLI for coverage targets to match the org-wide phpdbg removal tracked in contributte/contributte#73.

Coverage Stats

165 of 220 relevant lines covered (75.0%)

0.75 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

71.78

/src/NetteDatabaseDataSource.php

<?php

declare(strict_types=1);

namespace Ublaboo\NetteDatabaseDataSource;

use Nette\Database\Context;
use Nette\Database\ResultSet;
use PHPSQLParser\PHPSQLParser;
use Ublaboo\DataGrid\DataSource\IDataSource;
use Ublaboo\DataGrid\Filter\FilterDate;
use Ublaboo\DataGrid\Filter\FilterDateRange;
use Ublaboo\DataGrid\Filter\FilterMultiSelect;
use Ublaboo\DataGrid\Filter\FilterRange;
use Ublaboo\DataGrid\Filter\FilterSelect;
use Ublaboo\DataGrid\Filter\FilterText;
use Ublaboo\DataGrid\Utils\DateTimeHelper;
use Ublaboo\DataGrid\Utils\Sorting;

class NetteDatabaseDataSource implements IDataSource
{

        /**
         * @var Context
         */
        protected $connection;

        /**
         * @var mixed[]
         */
        protected $data = [];

        /**
         * Query parameters
         * @var mixed[]
         */
        protected $queryParameters;

        /**
         * Own query
         * @var string
         */
        protected $sql;

        /**
         * @var PHPSQLParser
         */
        protected $sqlParser;

        /**
         * @var QueryHelper
         */
        protected $queryHelper;

        /**
         * @param mixed[] $params
         */
        public function __construct(Context $connection, string $sql, array $params = [])
        {
                $this->connection = $connection;
                $this->sql = $sql;

                $this->queryParameters = $params;

                $this->queryHelper = new QueryHelper($this->sql);
        }

        /**
         * Get current sql + query parameters
         *
         * @return mixed[]
         */
        public function getQuery(): array
        {
                $sql = preg_replace('/_\?\w{13}\?_/', '?', $this->sql);

                return [$sql, $this->queryParameters];
        }

        /**
         * {@inheritDoc}
         */
        public function filter(array $filters): void
        {
                foreach ($filters as $filter) {
                        if ($filter->isValueSet()) {
                                if ($filter->getConditionCallback() !== null) {
                                        $this->sql = call_user_func_array(
                                                $filter->getConditionCallback(),
                                                [$this->sql, $filter->getValue(), &$this->queryParameters]
                                        );
                                        $this->queryHelper->resetQuery($this->sql);
                                } else {
                                        if ($filter instanceof FilterText) {
                                                $this->applyFilterText($filter);
                                        } elseif ($filter instanceof FilterMultiSelect) {
                                                $this->applyFilterMultiSelect($filter);
                                        } elseif ($filter instanceof FilterSelect) {
                                                $this->applyFilterSelect($filter);
                                        } elseif ($filter instanceof FilterDate) {
                                                $this->applyFilterDate($filter);
                                        } elseif ($filter instanceof FilterDateRange) {
                                                $this->applyFilterDateRange($filter);
                                        } elseif ($filter instanceof FilterRange) {
                                                $this->applyFilterRange($filter);
                                        }
                                }
                        }
                }
        }

        public function getCount(): int
        {
                $sql = $this->queryHelper->getCountSelect();
                $query = $this->query($sql)->fetch();

                return $query === null

                        ? 0

                        : $query['count'];
        }


        /**
         * @return mixed[]
         */
        public function getData(): array
        {
                if ($this->data === []) {
                        $this->data = $this->query($this->sql)->fetchAll();
                }

                return $this->data;
        }


        /**
         * @param mixed[] $condition
         */
        public function filterOne(array $condition): IDataSource
        {
                foreach ($condition as $column => $value) {
                        $this->applyWhere($column, $value);
                }

                return $this;
        }

        public function limit(int $offset, int $limit): IDataSource
        {
                $sql = $this->queryHelper->limit($limit, $offset);

                $this->data = $this->query($sql)->fetchAll();

                return $this;
        }

        public function sort(Sorting $sorting): IDataSource
        {
                if (is_callable($sorting->getSortCallback())) {
                        call_user_func(
                                $sorting->getSortCallback(),
                                $this->sql,
                                $sorting->getSort()
                        );

                        return $this;
                }

                $sort = $sorting->getSort();

                if ($sort !== []) {
                        foreach ($sort as $column => $order) {
                                $this->sql = $this->queryHelper->orderBy((string) $column, $order);
                        }
                }

                return $this;
        }

        /**
         * @return mixed[]
         */
        protected function addParams(string $sql): array
        {
                $params = $this->queryParameters;

                array_unshift($params, $sql);

                return $params;
        }

        /**
         * Call Context::query() with current sql + params
         *
         * @return ResultSet<mixed>
         */
        protected function query(string $sql): ResultSet
        {
                $sql = preg_replace('/_\?\w{13}\?_/', '?', $sql);

                if ($sql === null) {
                        throw new \UnexpectedValueException;
                }

                return $this->connection->query(...$this->addParams($sql));
        }

        /**
         * @param mixed $value
         */
        protected function applyWhere(string $column, $value, string $operator = '='): void
        {
                $id = '_?' . uniqid() . '?_';

                $this->sql = $this->queryHelper->where($column, $id, $operator);

                /**
                 * Find occurances of placeholders ('?') before inserted placeholder
                 */
                [$before,] = explode($id, $this->sql);

                if ($before === null) {
                        throw new \UnexpectedValueException;
                }

                $occurances = substr_count($before, '?');

                /**
                 * Add $value to query parameters at proper place
                 */
                if ($occurances === 0) {
                        array_unshift($this->queryParameters, $value);
                } else {
                        array_splice($this->queryParameters, $occurances, 0, $value);
                }
        }
        /********************************************************************************
         *                          IDataSource implementation *
         ********************************************************************************/


        protected function applyFilterDate(FilterDate $filter): void
        {
                $conditions = $filter->getCondition();

                $date = \DateTime::createFromFormat(
                        $filter->getPhpFormat(),
                        $conditions[$filter->getColumn()]
                );

                if ($date === false) {
                        throw new \UnexpectedValueException;
                }

                $this->applyWhere("DATE({$filter->getColumn()})", $date->format('Y-m-d'));
        }

        protected function applyFilterDateRange(FilterDateRange $filter): void
        {
                $conditions = $filter->getCondition();

                $valueFrom = $conditions[$filter->getColumn()]['from'];
                $valueTo = $conditions[$filter->getColumn()]['to'];

                if ($valueFrom) {
                        $dateFrom = DateTimeHelper::tryConvertToDateTime($valueFrom, [$filter->getPhpFormat()]);
                        $dateFrom->setTime(0, 0, 0);

                        $dateFrom->setTime(0, 0, 0);

                        $this->applyWhere("DATE({$filter->getColumn()})", $dateFrom->format('Y-m-d'), '>=');
                }

                if ($valueTo) {
                        $dateTo = DateTimeHelper::tryConvertToDateTime($valueTo, [$filter->getPhpFormat()]);
                        $dateTo->setTime(23, 59, 59);

                        $this->applyWhere("DATE({$filter->getColumn()})", $dateTo->format('Y-m-d'), '<=');
                }
        }

        protected function applyFilterRange(FilterRange $filter): void
        {
                $conditions = $filter->getCondition();

                $valueFrom = $conditions[$filter->getColumn()]['from'];
                $valueTo = $conditions[$filter->getColumn()]['to'];

                if ($valueFrom !== null && $valueFrom !== '') {
                        $this->applyWhere($filter->getColumn(), $valueFrom, '>=');
                }

                if ($valueTo !== null && $valueTo !== '') {
                        $this->applyWhere($filter->getColumn(), $valueTo, '<=');
                }
        }

        protected function applyFilterText(FilterText $filter): void
        {
                $or = [];
                $args = [];
                $big_or = '(';
                $big_or_args = [];
                $condition = $filter->getCondition();
                $isSeachExact = $filter->isExactSearch();
                $operator = $isSeachExact
                        ? '='
                        : 'LIKE';

                foreach ($condition as $column => $value) {

                        $like = '(';
                        $args = [];

                        if ($filter->hasSplitWordsSearch() === false) {
                                $like .= "$column $operator ? OR ";
                                $args[] = $isSeachExact
                                        ? $value
                                        : "%$value%";
                        } else {
                                $words = explode(' ', $value);

                                foreach ($words as $word) {
                                        $like .= "$column $operator ? OR ";
                                        $args[] = $isSeachExact
                                                ? $word
                                                : "%$word%";
                                }
                        }

                        $like = substr($like, 0, strlen($like) - 4) . ')';

                        $or[] = $like;
                        $big_or .= "$like OR ";
                        $big_or_args = array_merge($big_or_args, $args);
                }

                if (sizeof($or) > 1) {
                        $or = substr($big_or, 0, strlen($big_or) - 4) . ')';

                        $args = $big_or_args;
                } else {
                        $or = reset($or);
                }

                if ($or === false) {
                        throw new \LogicException;
                }

                $this->sql = $this->queryHelper->whereSql($or);

                foreach ($args as $arg) {
                        $this->queryParameters[] = $arg;
                }
        }

        protected function applyFilterSelect(FilterSelect $filter): void
        {
                foreach ($filter->getCondition() as $column => $value) {
                        $this->applyWhere($column, $value);
                }
        }

        protected function applyFilterMultiSelect(FilterMultiSelect $filter): void
        {
                $or = [];
                $args = [];
                $big_or = '';
                $big_or_args = [];
                $condition = $filter->getCondition();
                foreach ($condition as $column => $values) {
                        $queryPart = '(';
                        foreach ($values as $value) {
                                $queryPart .= "$column = ? OR ";
                                $args[] = $value;
                        }

                        $queryPart = substr($queryPart, 0, strlen($queryPart) - 4) . ')';
                        $or[] = $queryPart;
                        $big_or .= "$queryPart OR ";
                        $big_or_args = array_merge($big_or_args, $args);
                }

                if (sizeof($or) > 1) {
                        $or = substr($big_or, 0, strlen($big_or) - 4) . ')';
                        $args = $big_or_args;
                } else {
                        $or = reset($or);
                }

                if ($or === false) {
                        throw new \LogicException;
                }

                $this->sql = $this->queryHelper->whereSql($or);

                foreach ($args as $arg) {
                        $this->queryParameters[] = $arg;
                }
        }
}

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Ublaboo\NetteDatabaseDataSource;
6
7	use Nette\Database\Context;
8	use Nette\Database\ResultSet;
9	use PHPSQLParser\PHPSQLParser;
10	use Ublaboo\DataGrid\DataSource\IDataSource;
11	use Ublaboo\DataGrid\Filter\FilterDate;
12	use Ublaboo\DataGrid\Filter\FilterDateRange;
13	use Ublaboo\DataGrid\Filter\FilterMultiSelect;
14	use Ublaboo\DataGrid\Filter\FilterRange;
15	use Ublaboo\DataGrid\Filter\FilterSelect;
16	use Ublaboo\DataGrid\Filter\FilterText;
17	use Ublaboo\DataGrid\Utils\DateTimeHelper;
18	use Ublaboo\DataGrid\Utils\Sorting;
19
20	class NetteDatabaseDataSource implements IDataSource	1 ✔
21	{
22
23	/**
24	* @var Context
25	*/
26	protected $connection;
27
28	/**
29	* @var mixed[]
30	*/
31	protected $data = [];
32
33	/**
34	* Query parameters
35	* @var mixed[]
36	*/
37	protected $queryParameters;
38
39	/**
40	* Own query
41	* @var string
42	*/
43	protected $sql;
44
45	/**
46	* @var PHPSQLParser
47	*/
48	protected $sqlParser;
49
50	/**
51	* @var QueryHelper
52	*/
53	protected $queryHelper;
54
55	/**
56	* @param mixed[] $params
57	*/
58	public function __construct(Context $connection, string $sql, array $params = [])
59	{
60	$this->connection = $connection;	1 ✔
61	$this->sql = $sql;	1 ✔
62
63	$this->queryParameters = $params;	1 ✔
64
65	$this->queryHelper = new QueryHelper($this->sql);	1 ✔
66	}	1 ✔
67
68	/**
69	* Get current sql + query parameters
70	*
71	* @return mixed[]
72	*/
73	public function getQuery(): array
74	{
75	$sql = preg_replace('/_\?\w{13}\?_/', '?', $this->sql);	1 ✔
76
77	return [$sql, $this->queryParameters];	1 ✔
78	}
79
80	/**
81	* {@inheritDoc}
82	*/
83	public function filter(array $filters): void
84	{
85	foreach ($filters as $filter) {	1 ✔
86	if ($filter->isValueSet()) {	1 ✔
87	if ($filter->getConditionCallback() !== null) {	1 ✔
88	$this->sql = call_user_func_array(	×
89	$filter->getConditionCallback(),	×
90	[$this->sql, $filter->getValue(), &$this->queryParameters]	×
91	);
92	$this->queryHelper->resetQuery($this->sql);	×
93	} else {
94	if ($filter instanceof FilterText) {	1 ✔
95	$this->applyFilterText($filter);	1 ✔
96	} elseif ($filter instanceof FilterMultiSelect) {	1 ✔
97	$this->applyFilterMultiSelect($filter);	×
98	} elseif ($filter instanceof FilterSelect) {	1 ✔
99	$this->applyFilterSelect($filter);	1 ✔
100	} elseif ($filter instanceof FilterDate) {	1 ✔
101	$this->applyFilterDate($filter);	1 ✔
102	} elseif ($filter instanceof FilterDateRange) {	1 ✔
103	$this->applyFilterDateRange($filter);	1 ✔
104	} elseif ($filter instanceof FilterRange) {	1 ✔
105	$this->applyFilterRange($filter);	1 ✔
106	}
107	}
108	}
109	}
110	}	1 ✔
111
112	public function getCount(): int
113	{
114	$sql = $this->queryHelper->getCountSelect();	×
115	$query = $this->query($sql)->fetch();	×
116
117	return $query === null	×
118
119	? 0	×
120
121	: $query['count'];	×
122	}
123
124
125	/**
126	* @return mixed[]
127	*/
128	public function getData(): array
129	{
130	if ($this->data === []) {	1 ✔
131	$this->data = $this->query($this->sql)->fetchAll();	1 ✔
132	}
133
134	return $this->data;	1 ✔
135	}
136
137
138	/**
139	* @param mixed[] $condition
140	*/
141	public function filterOne(array $condition): IDataSource
142	{
143	foreach ($condition as $column => $value) {	1 ✔
144	$this->applyWhere($column, $value);	1 ✔
145	}
146
147	return $this;	1 ✔
148	}
149
150	public function limit(int $offset, int $limit): IDataSource
151	{
152	$sql = $this->queryHelper->limit($limit, $offset);	×
153
154	$this->data = $this->query($sql)->fetchAll();	×
155
156	return $this;	×
157	}
158
159	public function sort(Sorting $sorting): IDataSource
160	{
161	if (is_callable($sorting->getSortCallback())) {	1 ✔
162	call_user_func(	×
163	$sorting->getSortCallback(),	×
164	$this->sql,	×
165	$sorting->getSort()	×
166	);
167
168	return $this;	×
169	}
170
171	$sort = $sorting->getSort();	1 ✔
172
173	if ($sort !== []) {	1 ✔
174	foreach ($sort as $column => $order) {	1 ✔
175	$this->sql = $this->queryHelper->orderBy((string) $column, $order);	1 ✔
176	}
177	}
178
179	return $this;	1 ✔
180	}
181
182	/**
183	* @return mixed[]
184	*/
185	protected function addParams(string $sql): array
186	{
187	$params = $this->queryParameters;	1 ✔
188
189	array_unshift($params, $sql);	1 ✔
190
191	return $params;	1 ✔
192	}
193
194	/**
195	* Call Context::query() with current sql + params
196	*
197	* @return ResultSet<mixed>
198	*/
199	protected function query(string $sql): ResultSet
200	{
201	$sql = preg_replace('/_\?\w{13}\?_/', '?', $sql);	1 ✔
202
203	if ($sql === null) {	1 ✔
204	throw new \UnexpectedValueException;	×
205	}
206
207	return $this->connection->query(...$this->addParams($sql));	1 ✔
208	}
209
210	/**
211	* @param mixed $value
212	*/
213	protected function applyWhere(string $column, $value, string $operator = '='): void
214	{
215	$id = '_?' . uniqid() . '?_';	1 ✔
216
217	$this->sql = $this->queryHelper->where($column, $id, $operator);	1 ✔
218
219	/**
220	* Find occurances of placeholders ('?') before inserted placeholder
221	*/
222	[$before,] = explode($id, $this->sql);	1 ✔
223
224	if ($before === null) {	1 ✔
225	throw new \UnexpectedValueException;	×
226	}
227
228	$occurances = substr_count($before, '?');	1 ✔
229
230	/**
231	* Add $value to query parameters at proper place
232	*/
233	if ($occurances === 0) {	1 ✔
234	array_unshift($this->queryParameters, $value);	1 ✔
235	} else {
236	array_splice($this->queryParameters, $occurances, 0, $value);	1 ✔
237	}
238	}	1 ✔
239	/********************************************************************************
240	* IDataSource implementation *
241	********************************************************************************/
242
243
244	protected function applyFilterDate(FilterDate $filter): void
245	{
246	$conditions = $filter->getCondition();	1 ✔
247
248	$date = \DateTime::createFromFormat(	1 ✔
249	$filter->getPhpFormat(),	1 ✔
250	$conditions[$filter->getColumn()]	1 ✔
251	);
252
253	if ($date === false) {	1 ✔
254	throw new \UnexpectedValueException;	×
255	}
256
257	$this->applyWhere("DATE({$filter->getColumn()})", $date->format('Y-m-d'));	1 ✔
258	}	1 ✔
259
260	protected function applyFilterDateRange(FilterDateRange $filter): void
261	{
262	$conditions = $filter->getCondition();	1 ✔
263
264	$valueFrom = $conditions[$filter->getColumn()]['from'];	1 ✔
265	$valueTo = $conditions[$filter->getColumn()]['to'];	1 ✔
266
267	if ($valueFrom) {	1 ✔
268	$dateFrom = DateTimeHelper::tryConvertToDateTime($valueFrom, [$filter->getPhpFormat()]);	1 ✔
269	$dateFrom->setTime(0, 0, 0);	1 ✔
270
271	$dateFrom->setTime(0, 0, 0);	1 ✔
272
273	$this->applyWhere("DATE({$filter->getColumn()})", $dateFrom->format('Y-m-d'), '>=');	1 ✔
274	}
275
276	if ($valueTo) {	1 ✔
277	$dateTo = DateTimeHelper::tryConvertToDateTime($valueTo, [$filter->getPhpFormat()]);	1 ✔
278	$dateTo->setTime(23, 59, 59);	1 ✔
279
280	$this->applyWhere("DATE({$filter->getColumn()})", $dateTo->format('Y-m-d'), '<=');	1 ✔
281	}
282	}	1 ✔
283
284	protected function applyFilterRange(FilterRange $filter): void
285	{
286	$conditions = $filter->getCondition();	1 ✔
287
288	$valueFrom = $conditions[$filter->getColumn()]['from'];	1 ✔
289	$valueTo = $conditions[$filter->getColumn()]['to'];	1 ✔
290
291	if ($valueFrom !== null && $valueFrom !== '') {	1 ✔
292	$this->applyWhere($filter->getColumn(), $valueFrom, '>=');	1 ✔
293	}
294
295	if ($valueTo !== null && $valueTo !== '') {	1 ✔
296	$this->applyWhere($filter->getColumn(), $valueTo, '<=');	1 ✔
297	}
298	}	1 ✔
299
300	protected function applyFilterText(FilterText $filter): void
301	{
302	$or = [];	1 ✔
303	$args = [];	1 ✔
304	$big_or = '(';	1 ✔
305	$big_or_args = [];	1 ✔
306	$condition = $filter->getCondition();	1 ✔
307	$isSeachExact = $filter->isExactSearch();	1 ✔
308	$operator = $isSeachExact	1 ✔
309	? '='	1 ✔
310	: 'LIKE';	1 ✔
311
312	foreach ($condition as $column => $value) {	1 ✔
313
314	$like = '(';	1 ✔
315	$args = [];	1 ✔
316
317	if ($filter->hasSplitWordsSearch() === false) {	1 ✔
318	$like .= "$column $operator ? OR ";	1 ✔
319	$args[] = $isSeachExact	1 ✔
320	? $value	1 ✔
321	: "%$value%";	1 ✔
322	} else {
323	$words = explode(' ', $value);	1 ✔
324
325	foreach ($words as $word) {	1 ✔
326	$like .= "$column $operator ? OR ";	1 ✔
327	$args[] = $isSeachExact	1 ✔
328	? $word	1 ✔
329	: "%$word%";	1 ✔
330	}
331	}
332
333	$like = substr($like, 0, strlen($like) - 4) . ')';	1 ✔
334
335	$or[] = $like;	1 ✔
336	$big_or .= "$like OR ";	1 ✔
337	$big_or_args = array_merge($big_or_args, $args);	1 ✔
338	}
339
340	if (sizeof($or) > 1) {	1 ✔
341	$or = substr($big_or, 0, strlen($big_or) - 4) . ')';	1 ✔
342
343	$args = $big_or_args;	1 ✔
344	} else {
345	$or = reset($or);	1 ✔
346	}
347
348	if ($or === false) {	1 ✔
349	throw new \LogicException;	×
350	}
351
352	$this->sql = $this->queryHelper->whereSql($or);	1 ✔
353
354	foreach ($args as $arg) {	1 ✔
355	$this->queryParameters[] = $arg;	1 ✔
356	}
357	}	1 ✔
358
359	protected function applyFilterSelect(FilterSelect $filter): void
360	{
361	foreach ($filter->getCondition() as $column => $value) {	1 ✔
362	$this->applyWhere($column, $value);	1 ✔
363	}
364	}	1 ✔
365
366	protected function applyFilterMultiSelect(FilterMultiSelect $filter): void
367	{
368	$or = [];	×
369	$args = [];	×
370	$big_or = '';	×
371	$big_or_args = [];	×
372	$condition = $filter->getCondition();	×
373	foreach ($condition as $column => $values) {	×
374	$queryPart = '(';	×
375	foreach ($values as $value) {	×
376	$queryPart .= "$column = ? OR ";	×
377	$args[] = $value;	×
378	}
379
380	$queryPart = substr($queryPart, 0, strlen($queryPart) - 4) . ')';	×
381	$or[] = $queryPart;	×
382	$big_or .= "$queryPart OR ";	×
383	$big_or_args = array_merge($big_or_args, $args);	×
384	}
385
386	if (sizeof($or) > 1) {	×
387	$or = substr($big_or, 0, strlen($big_or) - 4) . ')';	×
388	$args = $big_or_args;	×
389	} else {
390	$or = reset($or);	×
391	}
392
393	if ($or === false) {	×
394	throw new \LogicException;	×
395	}
396
397	$this->sql = $this->queryHelper->whereSql($or);	×
398
399	foreach ($args as $arg) {	×
400	$this->queryParameters[] = $arg;	×
401	}
402	}	×
403	}

contributte / datagrid-nette-database-data-source / #6

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous