yatra / app / Repositories / BaseRepository.php

BaseRepository.php in Yatra – Travel Booking & Tour Operator Software trunk, at app/Repositories/BaseRepository.php

393 lines 11.0 KB

Raw Download Zip

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Yatra\Repositories;
6
7	use Yatra\Repositories\Concerns\CachesQueryResults;
8
9	/**
10	* Base Repository Class
11	* Provides common database operations using $wpdb
12	*/
13	abstract class BaseRepository
14	{
15	use CachesQueryResults;
16
17	/**
18	* @var \wpdb
19	*/
20	protected $wpdb;
21
22	/**
23	* @var string Table name (without prefix)
24	*/
25	protected string $table;
26
27	/**
28	* @var array Fields that contain rich text/HTML content (e.g., from Quill editor)
29	* Child repositories MUST override this to specify their rich text fields
30	* Example: ['description', 'content', 'notes', 'details']
31	*/
32	protected array $richTextFields = [];
33
34	/**
35	* @var array Fields that should be treated as integers
36	* Child repositories can override this to add entity-specific integer fields
37	* Common fields like 'id', 'created_by', 'updated_by' should be added by child classes
38	*/
39	protected array $integerFields = [];
40
41	/**
42	* @var array Fields that contain JSON data
43	* Child repositories can override this to specify JSON fields
44	* These fields will not be sanitized with sanitize_text_field to preserve JSON structure
45	*/
46	protected array $jsonFields = [];
47
48	/**
49	* Constructor
50	*/
51	public function __construct()
52	{
53	global $wpdb;
54	$this->wpdb = $wpdb;
55	$this->table = $this->getTableName();
56	}
57
58	/**
59	* Get full table name with prefix
60	*/
61	abstract protected function getTableName(): string;
62
63	/**
64	* Get table name (without prefix)
65	*/
66	public function getTable(): string
67	{
68	return $this->table;
69	}
70
71	/**
72	* Find a record by ID
73	*/
74	public function find(int $id, bool $includeDeleted = false): ?\stdClass
75	{
76	$table = esc_sql($this->table);
77	$query = "SELECT * FROM `{$table}` WHERE id = %d";
78
79	if (!$includeDeleted && $this->hasSoftDelete()) {
80	$query .= " AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')";
81	}
82
83	$result = $this->wpdb->get_row(
84	$this->wpdb->prepare($query, $id)
85	);
86
87
88	return $result ?: null;
89	}
90
91	/**
92	* Check if table has soft delete column
93	*/
94	protected function hasSoftDelete(): bool
95	{
96	// Check if deleted_at column exists
97	$table = esc_sql($this->table);
98	$column = $this->wpdb->get_var(
99	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
100	WHERE TABLE_SCHEMA = DATABASE()
101	AND TABLE_NAME = '{$table}'
102	AND COLUMN_NAME = 'deleted_at'"
103	);
104
105	return (int) $column > 0;
106	}
107
108	/**
109	* Get all records
110	*/
111	public function all(array $args = []): array
112	{
113	$table = esc_sql($this->table);
114	$where = $this->buildWhereClause($args);
115	$order = $this->buildOrderClause($args);
116	$limit = $this->buildLimitClause($args);
117
118	$query = "SELECT * FROM `{$table}` {$where} {$order} {$limit}";
119
120	$results = $this->wpdb->get_results($query) ?: [];
121
122	return $results;
123	}
124
125	/**
126	* Create a new record
127	*/
128	public function create(array $data): int
129	{
130	$data = $this->sanitizeData($data);
131	$data['created_at'] = current_time('mysql');
132	$data['updated_at'] = current_time('mysql');
133
134
135	$result = $this->wpdb->insert($this->table, $data);
136
137	if ($result === false) {
138	throw new \Exception('Failed to create record: ' . $this->wpdb->last_error);
139	}
140
141	$newId = (int) $this->wpdb->insert_id;
142	$this->afterWrite('create', $newId, []);
143
144	return $newId;
145	}
146
147	/**
148	* Update a record
149	*/
150	public function update(int $id, array $data): bool
151	{
152	$data = $this->sanitizeData($data);
153	$data['updated_at'] = current_time('mysql');
154
155
156	$result = $this->wpdb->update(
157	$this->table,
158	$data,
159	['id' => $id],
160	null,
161	['%d']
162	);
163
164	$success = $result !== false;
165
166	if ($success) {
167	$this->afterWrite('update', $id, []);
168	}
169
170	return $success;
171	}
172
173	/**
174	* Delete a record
175	*/
176	public function delete(int $id): bool
177	{
178	// DEBUG: Log repository delete attempt
179	if (defined('WP_DEBUG') && WP_DEBUG) {
180	}
181
182	$result = $this->wpdb->delete(
183	$this->table,
184	['id' => $id],
185	['%d']
186	);
187
188	$success = $result !== false;
189
190	if ($success) {
191	$this->afterWrite('delete', $id, []);
192	}
193
194	return $success;
195	}
196
197	/**
198	* Called after a successful create, update, or delete. Override in entity repositories to
199	* invalidate caches or fire domain hooks; default is no-op.
200	*
201	* @param 'create'\|'update'\|'delete' $operation
202	* @param array<string, mixed> $context
203	*/
204	protected function afterWrite(string $operation, int $id, array $context = []): void
205	{
206	}
207
208	/**
209	* Build WHERE clause
210	*/
211	protected function buildWhereClause(array $args): string
212	{
213	$conditions = [];
214
215	if (isset($args['where'])) {
216	foreach ($args['where'] as $key => $value) {
217	// Sanitize column name to prevent SQL injection
218	$key = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
219
220	if ($value === null) {
221	$conditions[] = "`{$key}` IS NULL";
222	} elseif ($value === 'NOT NULL') {
223	$conditions[] = "`{$key}` IS NOT NULL";
224	} elseif (is_array($value) && !empty($value)) {
225	$placeholders = implode(',', array_fill(0, count($value), '%s'));
226	$conditions[] = $this->wpdb->prepare(
227	"`{$key}` IN ({$placeholders})",
228	...$value
229	);
230	} else {
231	$conditions[] = $this->wpdb->prepare("`{$key}` = %s", $value);
232	}
233	}
234	}
235
236	// DEBUG: Check for soft delete
237	if (defined('WP_DEBUG') && WP_DEBUG && strpos($this->table, 'yatra_trips') !== false) {
238	}
239
240	// Add soft delete condition if table has deleted_at column and not including deleted
241	if ($this->hasSoftDelete() && (!isset($args['include_deleted']) \|\| !$args['include_deleted'])) {
242	$conditions[] = "(deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')";
243	}
244
245	return !empty($conditions) ? 'WHERE ' . implode(' AND ', $conditions) : '';
246	}
247
248	/**
249	* Build ORDER BY clause
250	*/
251	protected function buildOrderClause(array $args): string
252	{
253	$order_by = $args['order_by'] ?? 'id';
254	$order = strtoupper($args['order'] ?? 'DESC');
255
256	// Map common order_by aliases to actual column names
257	$column_map = [
258	'name' => 'name',
259	'title' => 'title',
260	'status' => 'status',
261	'date' => 'created_at',
262	'created_at' => 'created_at',
263	'updated_at' => 'updated_at',
264	];
265
266	$order_by = $column_map[$order_by] ?? $order_by;
267
268	// Sanitize order_by to prevent SQL injection
269	$order_by = preg_replace('/[^a-zA-Z0-9_]/', '', $order_by);
270	$order = in_array($order, ['ASC', 'DESC'], true) ? $order : 'DESC';
271
272	return "ORDER BY {$order_by} {$order}";
273	}
274
275	/**
276	* Build LIMIT clause
277	*/
278	protected function buildLimitClause(array $args): string
279	{
280	if (isset($args['limit'])) {
281	$limit = (int) $args['limit'];
282	$offset = isset($args['offset']) ? (int) $args['offset'] : 0;
283	return "LIMIT {$offset}, {$limit}";
284	}
285
286	return '';
287	}
288
289	/**
290	* Sanitize data before insert/update
291	*/
292	protected function sanitizeData(array $data): array
293	{
294	$sanitized = [];
295
296	foreach ($data as $key => $value) {
297	// Skip internal fields that are handled separately
298	if (in_array($key, ['created_at', 'updated_at'], true)) {
299	$sanitized[$key] = $value;
300	continue;
301	}
302
303	if (is_string($value)) {
304	// Use appropriate sanitization based on field type
305	if (in_array($key, $this->richTextFields, true)) {
306	// Sanitize Quill HTML content (allows safe HTML tags)
307	$sanitized[$key] = \Yatra\Helpers\FormatHelper::sanitizeQuillHtml($value);
308	} elseif (in_array($key, $this->jsonFields, true)) {
309	// JSON fields - preserve the JSON string as-is
310	$sanitized[$key] = $value;
311	} elseif (in_array($key, $this->integerFields, true)) {
312	$sanitized[$key] = absint($value);
313	} else {
314	$sanitized[$key] = sanitize_text_field($value);
315	}
316	} elseif (is_numeric($value)) {
317	// Ensure integers are properly cast
318	if (in_array($key, $this->integerFields, true)) {
319	$sanitized[$key] = absint($value);
320	} else {
321	$sanitized[$key] = $value;
322	}
323	} elseif (is_array($value)) {
324	// Arrays should already be serialized by service layer
325	$sanitized[$key] = maybe_serialize($value);
326	} else {
327	$sanitized[$key] = $value;
328	}
329	}
330
331	return $sanitized;
332	}
333
334	/**
335	* Count records
336	*/
337	public function count(array $args = []): int
338	{
339	$table = esc_sql($this->table);
340	$where = $this->buildWhereClause($args);
341	$query = "SELECT COUNT(*) FROM `{$table}` {$where}";
342
343	// DEBUG: Log count method details for yatra_trips
344	if (defined('WP_DEBUG') && WP_DEBUG && strpos($this->table, 'yatra_trips') !== false) {
345	}
346
347	return (int) $this->wpdb->get_var($query);
348	}
349
350	/**
351	* Get status counts for admin list views
352	*/
353	public function getStatusCounts(array $args = []): array
354	{
355	$table = esc_sql($this->table);
356	$where = $this->buildWhereClause($args);
357
358	$sql = "SELECT status, COUNT(*) as count
359	FROM `{$table}`
360	{$where}
361	GROUP BY status";
362
363	$results = $this->wpdb->get_results($sql) ?: [];
364
365	$counts = [
366	'publish' => 0,
367	'draft' => 0,
368	'trash' => 0,
369	'total' => 0
370	];
371
372	foreach ($results as $row) {
373	$status = $row->status;
374	$count = (int) $row->count;
375
376	// Map old status values to new ones if needed
377	if ($status === 'active') {
378	$status = 'publish';
379	} elseif ($status === 'inactive') {
380	$status = 'trash';
381	}
382
383	if (isset($counts[$status])) {
384	$counts[$status] = $count;
385	}
386	$counts['total'] += $count;
387	}
388
389	return $counts;
390	}
391	}
392
393