ivyforms / backend / src / Repository / AbstractRepository.php

AbstractRepository.php in The Innovative Form Builder – IvyForms 0.2, at backend/src/Repository/AbstractRepository.php

292 lines 7.8 KB

Raw Download Zip

1	<?php
2
3	/**
4	* @copyright © TMS-Plugins. All rights reserved.
5	* @licence See LICENCE.md for license details.
6	*/
7
8	namespace IvyForms\Repository;
9
10	// phpcs:disable PSR1.Files.SideEffects
11	if (!defined('ABSPATH')) {
12	exit; // Exit if accessed directly
13	}
14
15	use IvyForms\Common\Exceptions\InvalidArgumentException;
16
17	/**
18	* Class AbstractRepository
19	*
20	* @package IvyForms\Repository
21	*/
22	class AbstractRepository
23	{
24	public const FACTORY = '';
25
26	/** @var string */
27	protected string $table;
28
29	/** @var mixed */
30	protected $wpdb;
31
32	public function __construct(string $table)
33	{
34	global $wpdb;
35	$this->wpdb = $wpdb;
36	$this->table = $table;
37	}
38
39	/**
40	* @param int $id
41	* @return object\|null
42	*
43	*/
44	public function getById(int $id): ?object
45	{
46	$row = $this->wpdb->get_row(
47	$this->wpdb->prepare(
48	$this->selectQuery() . " WHERE $this->table.id = %d",
49	$id
50	),
51	ARRAY_A
52	);
53
54	if (!$row) {
55	return null;
56	}
57
58	return call_user_func([static::FACTORY, 'create'], $row);
59	}
60
61	/**
62	* @return mixed[]
63	* @throws InvalidArgumentException
64	*/
65	public function getAll(): array
66	{
67	$rows = $this->wpdb->get_results(
68	$this->wpdb->prepare($this->selectQuery()),
69	ARRAY_A
70	);
71
72	$result = [];
73
74	foreach ($rows as $row) {
75	$result[] = call_user_func([static::FACTORY, 'create'], $row);
76	}
77
78	return $result;
79	}
80
81	public function delete(int $id): int
82	{
83	return $this->wpdb->query(
84	$this->wpdb->prepare(
85	"DELETE FROM $this->table WHERE id = %d",
86	$id
87	)
88	);
89	}
90
91	/**
92	* @param array<string, mixed>\|null $params
93	* @return array<string, mixed>
94	*/
95	public function search(?array $params): array
96	{
97	$queryParams = [];
98	$whereClauses = $this->buildWhereClauses($params, $queryParams);
99
100	$query = $this->buildSearchQuery($whereClauses, $params, $queryParams);
101	$results = $this->wpdb->get_results($this->wpdb->prepare($query, $queryParams));
102
103	$total = $this->getTotalCount($whereClauses, $queryParams);
104
105	return [
106	'data' => $results,
107	'meta' => [
108	'page' => $params['page'],
109	'perPage' => $params['perPage'],
110	'total' => $total,
111	],
112	];
113	}
114
115	/**
116	* @param array<string, mixed>\|null $params
117	* @param array<int\|string, mixed> $queryParams
118	* @return string
119	*/
120	private function buildWhereClauses(?array $params, array &$queryParams): string
121	{
122	$whereClauses = ['1=1'];
123
124	$this->addSearchClauses($params, $whereClauses, $queryParams);
125	$this->addFilterClauses($params, $whereClauses, $queryParams);
126	$this->addDateRangeClauses($params['dateRange'] ?? [], $whereClauses, $queryParams);
127
128	return implode(' AND ', $whereClauses);
129	}
130
131	/**
132	* @param array<string, mixed>\|null $params
133	* @param array<int, string> $whereClauses
134	* @param array<int\|string, mixed> $queryParams
135	*/
136	private function addSearchClauses(?array $params, array &$whereClauses, array &$queryParams): void
137	{
138	if (!empty($params['search'])) {
139	$searchEscaped = '%' . $this->wpdb->esc_like($params['search']) . '%';
140	$searchableColumns = $this->getSearchableColumns();
141	$searchConditions = array_map(fn($col) => "$col LIKE %s", $searchableColumns);
142	$whereClauses[] = '(' . implode(' OR ', $searchConditions) . ')';
143	foreach ($searchableColumns as $col) {
144	$queryParams[] = $searchEscaped;
145	}
146	}
147	}
148
149	/**
150	* @param array<string, mixed>\|null $params
151	* @param array<int, string> $whereClauses
152	* @param array<int\|string, mixed> $queryParams
153	*/
154	private function addFilterClauses(?array $params, array &$whereClauses, array &$queryParams): void
155	{
156	if (!empty($params['filters'])) {
157	$filterableColumns = $this->getFilterableColumns();
158	foreach ($params['filters'] as $key => $value) {
159	if (in_array($key, $filterableColumns, true) && $value !== null && $value !== '') {
160	$whereClauses[] = "{$key} = %s";
161	$queryParams[] = sanitize_text_field($value);
162	}
163	}
164	}
165	}
166
167	/**
168	* @param array<int, string> $dateRange
169	* @param array<int, string> $whereClauses
170	* @param array<int\|string, mixed> $queryParams
171	*/
172	private function addDateRangeClauses(array $dateRange, array &$whereClauses, array &$queryParams): void
173	{
174	$dateColumn = $this->getDateColumn();
175
176	if (!empty($dateRange[0])) {
177	$whereClauses[] = "$dateColumn >= %s";
178	$queryParams[] = gmdate('Y-m-d H:i:s', strtotime($dateRange[0]));
179	}
180
181	if (!empty($dateRange[1])) {
182	$whereClauses[] = "$dateColumn <= %s";
183	$queryParams[] = gmdate('Y-m-d H:i:s', strtotime($dateRange[1]));
184	}
185	}
186
187	/**
188	* @param string $whereClauses
189	* @param array<string, mixed>\|null $params
190	* @param array<int\|string, mixed> $queryParams
191	* @return string
192	*/
193	private function buildSearchQuery(string $whereClauses, ?array $params, array &$queryParams): string
194	{
195	$sortableColumns = $this->getSortableColumns();
196	$sortBy = in_array($params['orderBy'] ?? 'id', $sortableColumns, true) ? $params['orderBy'] : 'id';
197	$order = strtolower($params['order'] ?? 'asc') === 'asc' ? 'ASC' : 'DESC';
198	$page = max(($params['page'] ?? 1), 1);
199	$perPage = max(($params['perPage'] ?? 10), 1);
200	$offset = ($page - 1) * $perPage;
201
202	$queryParams[] = $perPage;
203	$queryParams[] = $offset;
204
205	return $this->selectQuery() . " WHERE {$whereClauses} ORDER BY {$sortBy} {$order} LIMIT %d OFFSET %d";
206	}
207
208	/**
209	* @param string $whereClauses
210	* @param array<int\|string, mixed> $queryParams
211	* @return int
212	*/
213	private function getTotalCount(string $whereClauses, array $queryParams): int
214	{
215	// Exclude only LIMIT and OFFSET, keeping filter parameters intact
216	$countParams = $queryParams;
217	if (isset($queryParams[count($queryParams) - 2]) && isset($queryParams[count($queryParams) - 1])) {
218	$countParams = array_slice($queryParams, 0, -2);
219	}
220
221	$countQuery = "SELECT COUNT(*) FROM $this->table WHERE {$whereClauses}";
222	return (int)$this->wpdb->get_var($this->wpdb->prepare($countQuery, $countParams));
223	}
224
225	public function selectQuery(): string
226	{
227	return "SELECT * FROM " . $this->table;
228	}
229
230	/**
231	* Start a database transaction.
232	*
233	* @return bool
234	*/
235	public function beginTransaction(): bool
236	{
237	return (bool) $this->wpdb->query('START TRANSACTION');
238	}
239
240	/**
241	* Commit a database transaction.
242	*
243	* @return bool
244	*/
245	public function commit(): bool
246	{
247	return (bool) $this->wpdb->query('COMMIT');
248	}
249
250	/**
251	* Rollback a database transaction.
252	*
253	* @return bool
254	*/
255	public function rollback(): bool
256	{
257	return (bool) $this->wpdb->query('ROLLBACK');
258	}
259
260	/**
261	* @return string[]
262	*/
263	protected function getSearchableColumns(): array
264	{
265	return ['id'];
266	}
267
268	/**
269	* @return array<string, string>
270	*/
271	protected function getFilterableColumns(): array
272	{
273	return [];
274	}
275
276	/**
277	* @return string[]
278	*/
279	protected function getSortableColumns(): array
280	{
281	return ['id'];
282	}
283
284	/**
285	* @return string
286	*/
287	protected function getDateColumn(): string
288	{
289	return 'dateCreated';
290	}
291	}
292