yatra / app / Repositories / EnquiryRepository.php

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

453 lines 12.9 KB

Raw Download Zip

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Yatra\Repositories;
6
7	use Yatra\Database\Tables\EnquiriesTable;
8
9	/**
10	* Enquiry Repository
11	*
12	* Handles all database operations for customer enquiries.
13	*
14	* @package Yatra\Repositories
15	*/
16	class EnquiryRepository extends BaseRepository
17	{
18	/**
19	* Get full table name with prefix
20	*/
21	protected function getTableName(): string
22	{
23	return EnquiriesTable::getTableName();
24	}
25
26	/**
27	* Get trips table name
28	*/
29	protected function getTripsTable(): string
30	{
31	// Avoid relying on TripRepository visibility/overrides; use the canonical table class.
32	return \Yatra\Database\Tables\TripsTable::getTableName();
33	}
34
35	/**
36	* Get paginated enquiries with filters
37	*
38	* @param array $filters Filter options
39	* @return array {data: array, total: int, page: int, per_page: int, total_pages: int}
40	*/
41	public function paginate(array $filters = []): array
42	{
43	$table = $this->getTableName();
44	$trips_table = $this->getTripsTable();
45
46	// Pagination
47	$page = max(1, (int) ($filters['page'] ?? 1));
48	$per_page = max(1, min(100, (int) ($filters['per_page'] ?? 20)));
49	$offset = ($page - 1) * $per_page;
50
51	// Build WHERE clause
52	$where_clauses = ['1=1'];
53	$where_values = [];
54
55	if (!empty($filters['status'])) {
56	$where_clauses[] = 'e.status = %s';
57	$where_values[] = sanitize_text_field($filters['status']);
58	}
59
60	if (!empty($filters['trip_id'])) {
61	$where_clauses[] = 'e.trip_id = %d';
62	$where_values[] = (int) $filters['trip_id'];
63	}
64
65	if (!empty($filters['search'])) {
66	$search_like = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['search'])) . '%';
67	$where_clauses[] = '(e.name LIKE %s OR e.email LIKE %s OR e.phone LIKE %s OR e.message LIKE %s)';
68	$where_values = array_merge($where_values, [$search_like, $search_like, $search_like, $search_like]);
69	}
70
71	if (!empty($filters['date_from'])) {
72	$where_clauses[] = 'e.created_at >= %s';
73	$where_values[] = sanitize_text_field($filters['date_from']);
74	}
75
76	if (!empty($filters['date_to'])) {
77	$where_clauses[] = 'e.created_at <= %s';
78	$where_values[] = sanitize_text_field($filters['date_to']);
79	}
80
81	$where_sql = implode(' AND ', $where_clauses);
82
83	// Get total count
84	$count_query = "SELECT COUNT(*) FROM {$table} e WHERE {$where_sql}";
85	if (!empty($where_values)) {
86	$count_query = $this->wpdb->prepare($count_query, ...$where_values);
87	}
88	$total = (int) $this->wpdb->get_var($count_query);
89
90	// Get enquiries with trip info
91	$query = "SELECT e.*, t.title as trip_title, t.slug as trip_slug
92	FROM {$table} e
93	LEFT JOIN {$trips_table} t ON e.trip_id = t.id
94	WHERE {$where_sql}
95	ORDER BY e.created_at DESC
96	LIMIT %d OFFSET %d";
97
98	$query_values = array_merge($where_values, [$per_page, $offset]);
99	$enquiries = $this->wpdb->get_results($this->wpdb->prepare($query, ...$query_values));
100
101	return [
102	'data' => $enquiries ?: [],
103	'total' => $total,
104	'page' => $page,
105	'per_page' => $per_page,
106	'total_pages' => (int) ceil($total / $per_page),
107	];
108	}
109
110	/**
111	* Find enquiry by ID with trip info
112	*
113	* @param int $id Enquiry ID
114	* @return object\|null
115	*/
116	public function findWithTrip(int $id): ?object
117	{
118	$table = $this->getTableName();
119	$trips_table = $this->getTripsTable();
120
121	$query = $this->wpdb->prepare(
122	"SELECT e.*, t.title as trip_title, t.slug as trip_slug
123	FROM {$table} e
124	LEFT JOIN {$trips_table} t ON e.trip_id = t.id
125	WHERE e.id = %d",
126	$id
127	);
128
129	return $this->wpdb->get_row($query) ?: null;
130	}
131
132	/**
133	* Find enquiries by trip ID
134	*
135	* @param int $tripId Trip ID
136	* @return array
137	*/
138	public function findByTripId(int $tripId): array
139	{
140	$table = $this->getTableName();
141
142	$query = $this->wpdb->prepare(
143	"SELECT * FROM {$table} WHERE trip_id = %d ORDER BY created_at DESC",
144	$tripId
145	);
146
147	return $this->wpdb->get_results($query) ?: [];
148	}
149
150	/**
151	* Find enquiries by email
152	*
153	* @param string $email Email address
154	* @return array
155	*/
156	public function findByEmail(string $email): array
157	{
158	$table = $this->getTableName();
159
160	$query = $this->wpdb->prepare(
161	"SELECT * FROM {$table} WHERE email = %s ORDER BY created_at DESC",
162	sanitize_email($email)
163	);
164
165	return $this->wpdb->get_results($query) ?: [];
166	}
167
168	/**
169	* Create a new enquiry
170	*
171	* @param array $data Enquiry data
172	* @return int Enquiry ID on success
173	* @throws \Exception on failure
174	*/
175	public function create(array $data): int
176	{
177	$table = $this->getTableName();
178
179	$insertData = $this->prepareEnquiryData($data);
180	$insertData['created_at'] = current_time('mysql');
181	$insertData['updated_at'] = current_time('mysql');
182
183	$result = $this->wpdb->insert($table, $insertData);
184
185	if ($result === false) {
186	throw new \Exception('Failed to create enquiry: ' . $this->wpdb->last_error);
187	}
188
189	return $this->wpdb->insert_id;
190	}
191
192	/**
193	* Update an enquiry
194	*
195	* @param int $id Enquiry ID
196	* @param array $data Enquiry data to update
197	* @return bool
198	*/
199	public function update(int $id, array $data): bool
200	{
201	$table = $this->getTableName();
202
203	$updateData = $this->prepareEnquiryData($data);
204	$updateData['updated_at'] = current_time('mysql');
205
206	$result = $this->wpdb->update(
207	$table,
208	$updateData,
209	['id' => $id],
210	null,
211	['%d']
212	);
213
214	return $result !== false;
215	}
216
217	/**
218	* Update enquiry status
219	*
220	* @param int $id Enquiry ID
221	* @param string $status New status
222	* @return bool
223	*/
224	public function updateStatus(int $id, string $status): bool
225	{
226	$table = $this->getTableName();
227
228	$result = $this->wpdb->update(
229	$table,
230	[
231	'status' => sanitize_text_field($status),
232	'updated_at' => current_time('mysql'),
233	],
234	['id' => $id]
235	);
236
237	return $result !== false;
238	}
239
240	/**
241	* Add response to enquiry
242	*
243	* @param int $id Enquiry ID
244	* @param string $response Response message
245	* @param int $userId User who responded
246	* @return bool
247	*/
248	public function addResponse(int $id, string $response, int $userId): bool
249	{
250	$table = $this->getTableName();
251
252	$result = $this->wpdb->update(
253	$table,
254	[
255	'response_notes' => sanitize_textarea_field($response),
256	'responded_by' => $userId,
257	'responded_at' => current_time('mysql'),
258	'status' => 'responded',
259	'updated_at' => current_time('mysql'),
260	],
261	['id' => $id]
262	);
263
264	return $result !== false;
265	}
266
267	/**
268	* Delete an enquiry
269	*
270	* @param int $id Enquiry ID
271	* @return bool
272	*/
273	public function delete(int $id): bool
274	{
275	$table = $this->getTableName();
276
277	$result = $this->wpdb->delete($table, ['id' => $id], ['%d']);
278
279	return $result !== false;
280	}
281
282	/**
283	* Bulk update enquiry status
284	*
285	* @param array $ids Enquiry IDs
286	* @param string $status New status
287	* @return int Number of affected rows
288	*/
289	public function bulkUpdateStatus(array $ids, string $status): int
290	{
291	if (empty($ids)) {
292	return 0;
293	}
294
295	$table = $this->getTableName();
296	$ids = array_map('intval', $ids);
297	$placeholders = implode(',', array_fill(0, count($ids), '%d'));
298
299	$query = $this->wpdb->prepare(
300	"UPDATE {$table} SET status = %s, updated_at = %s WHERE id IN ({$placeholders})",
301	array_merge([sanitize_text_field($status), current_time('mysql')], $ids)
302	);
303
304	$this->wpdb->query($query);
305
306	return (int) $this->wpdb->rows_affected;
307	}
308
309	/**
310	* Bulk delete enquiries
311	*
312	* @param array $ids Enquiry IDs
313	* @return int Number of deleted rows
314	*/
315	public function bulkDelete(array $ids): int
316	{
317	if (empty($ids)) {
318	return 0;
319	}
320
321	$table = $this->getTableName();
322	$ids = array_map('intval', $ids);
323	$placeholders = implode(',', array_fill(0, count($ids), '%d'));
324
325	$this->wpdb->query($this->wpdb->prepare(
326	"DELETE FROM {$table} WHERE id IN ({$placeholders})",
327	$ids
328	));
329
330	return (int) $this->wpdb->rows_affected;
331	}
332
333	/**
334	* Get enquiry statistics
335	*
336	* @return array
337	*/
338	public function getStats(): array
339	{
340	$table = $this->getTableName();
341
342	// Total by status
343	$statusStats = $this->wpdb->get_results(
344	"SELECT status, COUNT(*) as count FROM {$table} GROUP BY status",
345	OBJECT_K
346	);
347
348	// Unread count
349	$unread = (int) $this->wpdb->get_var(
350	"SELECT COUNT(*) FROM {$table} WHERE status = 'pending' OR status = 'new'"
351	);
352
353	// This week
354	$thisWeek = (int) $this->wpdb->get_var($this->wpdb->prepare(
355	"SELECT COUNT(*) FROM {$table} WHERE created_at >= %s",
356	date('Y-m-d 00:00:00', strtotime('-7 days'))
357	));
358
359	// Average response time (for responded enquiries)
360	$avgResponseTime = $this->wpdb->get_var(
361	"SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, responded_at))
362	FROM {$table}
363	WHERE responded_at IS NOT NULL"
364	);
365
366	return [
367	'total' => array_sum(array_column((array) $statusStats, 'count')),
368	'by_status' => $statusStats,
369	'unread' => $unread,
370	'this_week' => $thisWeek,
371	'avg_response_hours' => round((float) ($avgResponseTime ?? 0), 1),
372	];
373	}
374
375	/**
376	* Prepare enquiry data for insert/update
377	*
378	* @param array $data Raw data
379	* @return array Sanitized data
380	*/
381	private function prepareEnquiryData(array $data): array
382	{
383	$prepared = [];
384
385	if (array_key_exists('trip_id', $data)) {
386	$prepared['trip_id'] = $data['trip_id'] ? (int) $data['trip_id'] : null;
387	}
388
389	if (array_key_exists('name', $data)) {
390	$prepared['name'] = sanitize_text_field((string) $data['name']);
391	}
392
393	if (array_key_exists('email', $data)) {
394	$prepared['email'] = sanitize_email((string) $data['email']);
395	}
396
397	if (array_key_exists('phone', $data)) {
398	$prepared['phone'] = sanitize_text_field((string) $data['phone']);
399	}
400
401	if (array_key_exists('subject', $data)) {
402	$prepared['subject'] = sanitize_text_field((string) $data['subject']);
403	}
404
405	if (array_key_exists('message', $data)) {
406	$prepared['message'] = sanitize_textarea_field((string) $data['message']);
407	}
408
409	if (array_key_exists('status', $data)) {
410	$prepared['status'] = sanitize_text_field((string) $data['status']);
411	}
412
413	if (array_key_exists('response', $data)) {
414	$prepared['response_notes'] = sanitize_textarea_field((string) $data['response']);
415	}
416
417	if (array_key_exists('response_notes', $data)) {
418	$prepared['response_notes'] = sanitize_textarea_field((string) $data['response_notes']);
419	}
420
421	if (array_key_exists('responded_by', $data)) {
422	$prepared['responded_by'] = (int) $data['responded_by'];
423	}
424
425	if (array_key_exists('responded_at', $data) && $data['responded_at']) {
426	$prepared['responded_at'] = sanitize_text_field($data['responded_at']);
427	}
428
429	if (array_key_exists('travel_date', $data) && $data['travel_date']) {
430	$prepared['travel_date'] = sanitize_text_field($data['travel_date']);
431	}
432
433	if (array_key_exists('travelers_count', $data)) {
434	$prepared['travelers_count'] = (int) $data['travelers_count'];
435	}
436
437	if (array_key_exists('metadata', $data)) {
438	$prepared['metadata'] = is_null($data['metadata']) ? null : wp_unslash((string) $data['metadata']);
439	}
440
441	if (array_key_exists('ip_address', $data)) {
442	$prepared['ip_address'] = sanitize_text_field((string) $data['ip_address']);
443	}
444
445	if (array_key_exists('user_agent', $data)) {
446	$prepared['user_agent'] = sanitize_text_field((string) $data['user_agent']);
447	}
448
449	return $prepared;
450	}
451	}
452
453