yatra / app / Repositories / EnquiryRepository.php

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

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