ivyforms / backend / src / Repository / AbstractRepository.php

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

437 lines 12.6 KB

Raw Download Zip

1	<?php
2
3	/**
4	* @copyright © Melograno Venture Studio. 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	use IvyForms\Common\Exceptions\NotFoundException;
17	use IvyForms\Common\Exceptions\QueryExecutionException;
18	use IvyForms\Services\Translations\BackendStrings;
19
20	/**
21	* Class AbstractRepository
22	*
23	* @package IvyForms\Repository
24	*/
25	class AbstractRepository
26	{
27	public const FACTORY = '';
28
29	/** @var string */
30	protected string $table;
31
32	/** @var mixed */
33	protected $wpdb;
34
35	public function __construct(string $table)
36	{
37	global $wpdb;
38	$this->wpdb = $wpdb;
39	$this->table = $table;
40	}
41
42	/**
43	* @param int $id
44	* @return object\|null
45	*
46	*/
47	public function getById(int $id): ?object
48	{
49	$row = $this->wpdb->get_row(
50	$this->wpdb->prepare(
51	$this->selectQuery() . " WHERE $this->table.id = %d",
52	$id
53	),
54	ARRAY_A
55	);
56
57	if (!$row) {
58	return null;
59	}
60
61	return call_user_func([static::FACTORY, 'create'], $row);
62	}
63
64	/**
65	* @return mixed[]
66	* @throws InvalidArgumentException
67	*/
68	public function getAll(): array
69	{
70	$rows = $this->wpdb->get_results(
71	$this->selectQuery(),
72	ARRAY_A
73	);
74
75	$result = [];
76
77	foreach ($rows as $row) {
78	$result[] = call_user_func([static::FACTORY, 'create'], $row);
79	}
80
81	return $result;
82	}
83
84	/**
85	* @param int $id
86	* @return int
87	* @throws QueryExecutionException
88	*/
89	public function delete(int $id): int
90	{
91	$result = $this->wpdb->query(
92	$this->wpdb->prepare(
93	"DELETE FROM $this->table WHERE id = %d",
94	$id
95	)
96	);
97
98	if ($result === false) {
99	throw new QueryExecutionException(
100	sprintf(BackendStrings::getExceptionStrings()['failed_delete_by_id'], $id, $this->table)
101	);
102	}
103
104	return $result;
105	}
106
107	/**
108	* Delete multiple entities by their IDs in bulk.
109	*
110	* @param array<int> $ids
111	* @return int Number of deleted entities
112	* @throws QueryExecutionException
113	*/
114	public function deleteMany(array $ids): int
115	{
116	if (empty($ids)) {
117	return 0;
118	}
119	$placeholders = implode(',', array_fill(0, count($ids), '%d'));
120	$query = "DELETE FROM $this->table WHERE id IN ($placeholders)";
121	$result = $this->wpdb->query($this->wpdb->prepare($query, ...$ids));
122
123	if ($result === false) {
124	throw new QueryExecutionException(
125	sprintf(
126	BackendStrings::getExceptionStrings()['failed_delete_by_ids'],
127	implode(', ', $ids),
128	$this->table
129	)
130	);
131	}
132
133	return $result;
134	}
135
136	/**
137	* Delete multiple entities by foreign key values (e.g., form IDs).
138	*
139	* @param array<int> $foreignIds Array of foreign key values (e.g., form IDs)
140	* @param string $foreignColumn Foreign key column name (default: 'formId')
141	* @return int Number of deleted entities
142	* @throws InvalidArgumentException If no foreign IDs are provided
143	* @throws QueryExecutionException
144	*/
145	public function deleteManyByForeignKeyValues(array $foreignIds, string $foreignColumn = 'formId'): int
146	{
147	if (empty($foreignIds)) {
148	throw new InvalidArgumentException(
149	BackendStrings::getExceptionStrings()['no_form_ids_provided']
150	);
151	}
152
153	// Validate foreign column to prevent SQL injection
154	if (!in_array($foreignColumn, $this->getAllowedForeignColumns(), true)) {
155	throw new InvalidArgumentException(
156	BackendStrings::getExceptionStrings()['invalid_foreign_column']
157	);
158	}
159
160	$foreignIds = array_map('intval', $foreignIds);
161	$placeholders = implode(',', array_fill(0, count($foreignIds), '%d'));
162	$query = "DELETE FROM {$this->table} WHERE {$foreignColumn} IN ($placeholders)";
163	$result = $this->wpdb->query($this->wpdb->prepare($query, ...$foreignIds));
164
165	if ($result === false) {
166	throw new QueryExecutionException(
167	sprintf(
168	BackendStrings::getExceptionStrings()['failed_delete_by_foreign_keys'],
169	implode(', ', $foreignIds),
170	$this->table
171	)
172	);
173	}
174
175	return $result;
176	}
177
178	/**
179	* Delete entities by a single foreign key value (e.g., form ID).
180	*
181	* @param int $foreignId Foreign key value (e.g., form ID)
182	* @param string $foreignColumn Foreign key column name (default: 'formId')
183	* @return int Number of deleted entities
184	* @throws InvalidArgumentException If invalid foreign column is provided
185	* @throws QueryExecutionException
186	*/
187	public function deleteOneByForeignKeyValue(int $foreignId, string $foreignColumn = 'formId'): int
188	{
189	// Validate foreign column to prevent SQL injection
190	if (!in_array($foreignColumn, $this->getAllowedForeignColumns(), true)) {
191	throw new InvalidArgumentException(
192	BackendStrings::getExceptionStrings()['invalid_foreign_column']
193	);
194	}
195
196	$query = "DELETE FROM {$this->table} WHERE {$foreignColumn} = %d";
197	$result = $this->wpdb->query($this->wpdb->prepare($query, $foreignId));
198
199	if ($result === false) {
200	throw new QueryExecutionException(
201	sprintf(BackendStrings::getExceptionStrings()['failed_delete_by_foreign_key'], $foreignId, $this->table)
202	);
203	}
204
205	return $result;
206	}
207
208	/**
209	* @param array<string, mixed>\|null $params
210	* @return array<string, mixed>
211	*/
212	public function search(?array $params): array
213	{
214	$queryParams = [];
215	$whereClauses = $this->buildWhereClauses($params, $queryParams);
216
217	$query = $this->buildSearchQuery($whereClauses, $params, $queryParams);
218	$results = $this->wpdb->get_results($this->wpdb->prepare($query, $queryParams));
219
220	$total = $this->getTotalCount($whereClauses, $queryParams);
221
222	return [
223	'data' => $results,
224	'meta' => [
225	'page' => $params['page'],
226	'perPage' => $params['perPage'],
227	'total' => $total,
228	],
229	];
230	}
231
232	/**
233	* @param array<string, mixed>\|null $params
234	* @param array<int\|string, mixed> $queryParams
235	* @return string
236	*/
237	protected function buildWhereClauses(?array $params, array &$queryParams): string
238	{
239	$whereClauses = ['1=1'];
240
241	$this->addSearchClauses($params, $whereClauses, $queryParams);
242	$this->addFilterClauses($params, $whereClauses, $queryParams);
243	$this->addDateRangeClauses($params['dateRange'] ?? [], $whereClauses, $queryParams);
244
245	return implode(' AND ', $whereClauses);
246	}
247
248	/**
249	* @param array<string, mixed>\|null $params
250	* @param array<int, string> $whereClauses
251	* @param array<int\|string, mixed> $queryParams
252	*/
253	protected function addSearchClauses(?array $params, array &$whereClauses, array &$queryParams): void
254	{
255	if (!empty($params['search'])) {
256	$searchEscaped = '%' . $this->wpdb->esc_like($params['search']) . '%';
257	$searchableColumns = $this->getSearchableColumns();
258	$searchConditions = array_map(fn($col) => "$col LIKE %s", $searchableColumns);
259	$whereClauses[] = '(' . implode(' OR ', $searchConditions) . ')';
260	foreach ($searchableColumns as $col) {
261	$queryParams[] = $searchEscaped;
262	}
263	}
264	}
265
266	/**
267	* @param array<string, mixed>\|null $params
268	* @param array<int, string> $whereClauses
269	* @param array<int\|string, mixed> $queryParams
270	*/
271	protected function addFilterClauses(?array $params, array &$whereClauses, array &$queryParams): void
272	{
273	if (!empty($params['filters'])) {
274	$filterableColumns = $this->getFilterableColumns();
275	foreach ($params['filters'] as $key => $value) {
276	if (in_array($key, $filterableColumns, true) && $value !== null && $value !== '') {
277	$whereClauses[] = "{$key} = %s";
278	$queryParams[] = sanitize_text_field($value);
279	}
280	}
281	}
282	}
283
284	/**
285	* @param array<int, string> $dateRange
286	* @param array<int, string> $whereClauses
287	* @param array<int\|string, mixed> $queryParams
288	*/
289	protected function addDateRangeClauses(array $dateRange, array &$whereClauses, array &$queryParams): void
290	{
291
292	if (empty($dateRange) \|\| empty($dateRange[0])) {
293	return;
294	}
295
296	$dateColumn = $this->getDateColumn();
297
298	if (empty($dateRange[1])) {
299	$dateRange[1] = $dateRange[0];
300	}
301
302	$normalizeStartOfDay = function ($isoDate) {
303	$day = substr($isoDate, 0, 10);
304	return $day . ' 00:00:00';
305	};
306	$normalizeEndOfDay = function ($isoDate) {
307	$day = substr($isoDate, 0, 10);
308	return $day . ' 23:59:59';
309	};
310
311	$start = $normalizeStartOfDay($dateRange[0]);
312	$end = $normalizeEndOfDay($dateRange[1]);
313
314	$whereClauses[] = "$dateColumn >= %s";
315	$queryParams[] = $start;
316
317	$whereClauses[] = "$dateColumn <= %s";
318	$queryParams[] = $end;
319	}
320
321	/**
322	* @param string $whereClauses
323	* @param array<string, mixed>\|null $params
324	* @param array<int\|string, mixed> $queryParams
325	* @return string
326	*/
327	protected function buildSearchQuery(string $whereClauses, ?array $params, array &$queryParams): string
328	{
329	$sortableColumns = $this->getSortableColumns();
330	$sortBy = in_array($params['orderBy'] ?? 'id', $sortableColumns, true) ? $params['orderBy'] : 'id';
331	$order = strtolower($params['order'] ?? 'asc') === 'asc' ? 'ASC' : 'DESC';
332	$page = max(($params['page'] ?? 1), 1);
333	$perPage = max(($params['perPage'] ?? 10), 1);
334	$offset = ($page - 1) * $perPage;
335
336	$queryParams[] = $perPage;
337	$queryParams[] = $offset;
338
339	return $this->selectQuery() . " WHERE {$whereClauses} ORDER BY {$sortBy} {$order} LIMIT %d OFFSET %d";
340	}
341
342	/**
343	* @param string $whereClauses
344	* @param array<int\|string, mixed> $queryParams
345	* @return int
346	*/
347	protected function getTotalCount(string $whereClauses, array $queryParams): int
348	{
349	// Exclude only LIMIT and OFFSET, keeping filter parameters intact
350	$countParams = $queryParams;
351	if (isset($queryParams[count($queryParams) - 2]) && isset($queryParams[count($queryParams) - 1])) {
352	$countParams = array_slice($queryParams, 0, -2);
353	}
354
355	$countQuery = "SELECT COUNT(*) FROM $this->table WHERE {$whereClauses}";
356	return (int)$this->wpdb->get_var($this->wpdb->prepare($countQuery, $countParams));
357	}
358
359	public function selectQuery(): string
360	{
361	return "SELECT * FROM " . $this->table;
362	}
363
364	/**
365	* Start a database transaction.
366	*
367	* @return bool
368	*/
369	public function beginTransaction(): bool
370	{
371	return (bool) $this->wpdb->query('START TRANSACTION');
372	}
373
374	/**
375	* Commit a database transaction.
376	*
377	* @return bool
378	*/
379	public function commit(): bool
380	{
381	return (bool) $this->wpdb->query('COMMIT');
382	}
383
384	/**
385	* Rollback a database transaction.
386	*
387	* @return bool
388	*/
389	public function rollback(): bool
390	{
391	return (bool) $this->wpdb->query('ROLLBACK');
392	}
393
394	/**
395	* @return string[]
396	*/
397	protected function getSearchableColumns(): array
398	{
399	return ['id'];
400	}
401
402	/**
403	* @return array<string, string>
404	*/
405	protected function getFilterableColumns(): array
406	{
407	return [];
408	}
409
410	/**
411	* @return string[]
412	*/
413	protected function getSortableColumns(): array
414	{
415	return ['id'];
416	}
417
418	/**
419	* @return string
420	*/
421	protected function getDateColumn(): string
422	{
423	return 'dateCreated';
424	}
425
426	/**
427	* Get allowed foreign key columns for deletion operations.
428	* Override this method in child repositories to specify allowed columns.
429	*
430	* @return string[]
431	*/
432	protected function getAllowedForeignColumns(): array
433	{
434	return ['formId', 'entryId', 'fieldId'];
435	}
436	}
437