yatra / app / Repositories / PaymentRepository.php

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

495 lines 14.5 KB

Raw Download Zip

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Yatra\Repositories;
6
7	use Yatra\Database\Tables\BookingPaymentsTable;
8	use Yatra\Database\Tables\BookingsTable;
9	use Yatra\Database\Tables\TripsTable;
10
11	/**
12	* Payment Repository
13	*
14	* Handles all database operations for booking payments.
15	*
16	* @package Yatra\Repositories
17	*/
18	class PaymentRepository extends BaseRepository
19	{
20	/**
21	* Table name without prefix
22	*/
23	// private const TABLE_NAME = 'yatra_booking_payments';
24
25	/** @var bool\|null */
26	private ?bool $customerColumnExists = null;
27
28	/**
29	* Get full table name with prefix
30	*/
31	protected function getTableName(): string
32	{
33	return BookingPaymentsTable::getTableName();
34	}
35
36	private function hasCustomerColumn(): bool
37	{
38	if ($this->customerColumnExists !== null) {
39	return $this->customerColumnExists;
40	}
41
42	$table = esc_sql($this->getTableName());
43	$column = $this->wpdb->get_var(
44	$this->wpdb->prepare(
45	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'customer_id'",
46	$table
47	)
48	);
49
50	$this->customerColumnExists = ((int) $column) > 0;
51	return $this->customerColumnExists;
52	}
53
54	/**
55	* Get bookings table name
56	*/
57	protected function getBookingsTable(): string
58	{
59	return BookingsTable::getTableName();
60	}
61
62	/**
63	* Get trips table name
64	*/
65	protected function getTripsTable(): string
66	{
67	return TripsTable::getTableName();
68	}
69
70	/**
71	* Get paginated payments with filters
72	*
73	* @param array $filters Filter options
74	* @return array {data: array, total: int, page: int, per_page: int, total_pages: int}
75	*/
76	public function paginate(array $filters = []): array
77	{
78	$table = $this->getTableName();
79	$bookings_table = $this->getBookingsTable();
80	$trips_table = $this->getTripsTable();
81
82	// Pagination
83	$page = max(1, (int) ($filters['page'] ?? 1));
84	$per_page = max(1, min(100, (int) ($filters['per_page'] ?? 20)));
85	$offset = ($page - 1) * $per_page;
86
87	// Build WHERE clause
88	$where_clauses = ['1=1'];
89	$where_values = [];
90
91	if (!empty($filters['booking_id'])) {
92	$where_clauses[] = 'p.booking_id = %d';
93	$where_values[] = (int) $filters['booking_id'];
94	}
95
96	if (!empty($filters['status'])) {
97	$where_clauses[] = 'p.status = %s';
98	$where_values[] = sanitize_text_field($filters['status']);
99	}
100
101	if (!empty($filters['gateway'])) {
102	$where_clauses[] = 'p.gateway = %s';
103	$where_values[] = sanitize_text_field($filters['gateway']);
104	}
105
106	if (!empty($filters['search'])) {
107	$search_like = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['search'])) . '%';
108	$where_clauses[] = '(p.transaction_id LIKE %s OR b.reference LIKE %s OR b.contact_email LIKE %s)';
109	$where_values = array_merge($where_values, [$search_like, $search_like, $search_like]);
110	}
111
112	if (!empty($filters['date_from'])) {
113	$where_clauses[] = 'p.created_at >= %s';
114	$where_values[] = sanitize_text_field($filters['date_from']);
115	}
116
117	if (!empty($filters['date_to'])) {
118	$where_clauses[] = 'p.created_at <= %s';
119	$where_values[] = sanitize_text_field($filters['date_to']);
120	}
121
122	$where_sql = implode(' AND ', $where_clauses);
123
124	// Get total count
125	$count_query = "SELECT COUNT(*)
126	FROM {$table} p
127	LEFT JOIN {$bookings_table} b ON p.booking_id = b.id
128	WHERE {$where_sql}";
129	if (!empty($where_values)) {
130	$count_query = $this->wpdb->prepare($count_query, ...$where_values);
131	}
132	$total = (int) $this->wpdb->get_var($count_query);
133
134	// Get payments with booking and trip info
135	$query = "SELECT p.*,
136	b.reference as booking_reference,
137	b.contact_email,
138	b.contact_first_name,
139	b.contact_last_name,
140	t.title as trip_title
141	FROM {$table} p
142	LEFT JOIN {$bookings_table} b ON p.booking_id = b.id
143	LEFT JOIN {$trips_table} t ON b.trip_id = t.id
144	WHERE {$where_sql}
145	ORDER BY p.created_at DESC
146	LIMIT %d OFFSET %d";
147
148	$query_values = array_merge($where_values, [$per_page, $offset]);
149	$payments = $this->wpdb->get_results($this->wpdb->prepare($query, ...$query_values));
150
151	return [
152	'data' => $payments ?: [],
153	'total' => $total,
154	'page' => $page,
155	'per_page' => $per_page,
156	'total_pages' => (int) ceil($total / $per_page),
157	];
158	}
159
160	/**
161	* Find payment by ID with booking info
162	*
163	* @param int $id Payment ID
164	* @return object\|null
165	*/
166	public function findWithBooking(int $id): ?object
167	{
168	$table = $this->getTableName();
169	$bookings_table = $this->getBookingsTable();
170	$trips_table = $this->getTripsTable();
171
172	$query = $this->wpdb->prepare(
173	"SELECT p.*,
174	b.reference as booking_reference,
175	b.user_id as booking_user_id,
176	b.contact_email,
177	b.contact_first_name,
178	b.contact_last_name,
179	b.total_amount as booking_total_amount,
180	b.amount_paid as booking_amount_paid,
181	b.amount_due as booking_amount_due,
182	b.travel_date as travel_date,
183	b.end_date as booking_end_date,
184	b.trip_id as trip_id,
185	b.payment_method as payment_method,
186	t.title as trip_title,
187	t.duration_days as trip_duration_days,
188	t.duration_nights as trip_duration_nights
189	FROM {$table} p
190	LEFT JOIN {$bookings_table} b ON p.booking_id = b.id
191	LEFT JOIN {$trips_table} t ON b.trip_id = t.id
192	WHERE p.id = %d",
193	$id
194	);
195
196	return $this->wpdb->get_row($query) ?: null;
197	}
198
199	/**
200	* Find payments by booking ID
201	*
202	* @param int $bookingId Booking ID
203	* @return array
204	*/
205	public function findByBookingId(int $bookingId): array
206	{
207	$table = $this->getTableName();
208
209	$query = $this->wpdb->prepare(
210	"SELECT * FROM {$table} WHERE booking_id = %d ORDER BY created_at DESC",
211	$bookingId
212	);
213
214	return $this->wpdb->get_results($query) ?: [];
215	}
216
217	/**
218	* Find the latest payment for a booking
219	*
220	* @param int $bookingId Booking ID
221	* @return object\|null
222	*/
223	public function findLatestByBookingId(int $bookingId): ?object
224	{
225	$table = $this->getTableName();
226
227	$query = $this->wpdb->prepare(
228	"SELECT * FROM {$table} WHERE booking_id = %d ORDER BY created_at DESC, id DESC LIMIT 1",
229	$bookingId
230	);
231
232	return $this->wpdb->get_row($query) ?: null;
233	}
234
235	/**
236	* Find payment by transaction ID
237	*
238	* @param string $transactionId Transaction ID
239	* @return object\|null
240	*/
241	public function findByTransactionId(string $transactionId): ?object
242	{
243	$table = $this->getTableName();
244
245	$query = $this->wpdb->prepare(
246	"SELECT * FROM {$table} WHERE transaction_id = %s",
247	sanitize_text_field($transactionId)
248	);
249
250	return $this->wpdb->get_row($query) ?: null;
251	}
252
253	/**
254	* Create a new payment
255	*
256	* @param array $data Payment data
257	* @return int Payment ID on success
258	* @throws \Exception on failure
259	*/
260	public function create(array $data): int
261	{
262	$table = $this->getTableName();
263
264	$insertData = $this->preparePaymentData($data);
265	$insertData['created_at'] = current_time('mysql');
266
267	$result = $this->wpdb->insert($table, $insertData);
268
269	if ($result === false) {
270	throw new \Exception('Failed to create payment: ' . $this->wpdb->last_error);
271	}
272
273	return $this->wpdb->insert_id;
274	}
275
276	/**
277	* Update a payment
278	*
279	* @param int $id Payment ID
280	* @param array $data Payment data to update
281	* @return bool
282	*/
283	public function update(int $id, array $data): bool
284	{
285	$table = $this->getTableName();
286
287	$updateData = $this->preparePaymentData($data);
288
289	$result = $this->wpdb->update(
290	$table,
291	$updateData,
292	['id' => $id],
293	null,
294	['%d']
295	);
296
297	return $result !== false;
298	}
299
300	/**
301	* Update payment status
302	*
303	* @param int $id Payment ID
304	* @param string $status New status
305	* @return bool
306	*/
307	public function updateStatus(int $id, string $status): bool
308	{
309	$table = $this->getTableName();
310
311	$data = ['status' => sanitize_text_field($status)];
312
313	if ($status === 'completed') {
314	$data['processed_at'] = current_time('mysql');
315	}
316
317	$result = $this->wpdb->update($table, $data, ['id' => $id]);
318
319	return $result !== false;
320	}
321
322	/**
323	* Delete a payment
324	*
325	* @param int $id Payment ID
326	* @return bool
327	*/
328	public function delete(int $id): bool
329	{
330	$table = $this->getTableName();
331
332	$result = $this->wpdb->delete($table, ['id' => $id], ['%d']);
333
334	return $result !== false;
335	}
336
337	/**
338	* Get total paid amount for a booking
339	*
340	* @param int $bookingId Booking ID
341	* @return float
342	*/
343	public function getTotalPaidForBooking(int $bookingId): float
344	{
345	$table = $this->getTableName();
346
347	$result = $this->wpdb->get_var($this->wpdb->prepare(
348	"SELECT SUM(amount) FROM {$table} WHERE booking_id = %d AND status = 'completed'",
349	$bookingId
350	));
351
352	return (float) ($result ?? 0);
353	}
354
355	/**
356	* Get payment statistics
357	*
358	* @return array
359	*/
360	public function getStats(): array
361	{
362	$table = $this->getTableName();
363
364	// Total by status
365	$statusStats = $this->wpdb->get_results(
366	"SELECT status, COUNT(*) as count, SUM(amount) as total_amount
367	FROM {$table} GROUP BY status",
368	OBJECT_K
369	);
370
371	// By gateway
372	$gatewayStats = $this->wpdb->get_results(
373	"SELECT gateway, COUNT(*) as count, SUM(amount) as total_amount
374	FROM {$table} WHERE status = 'completed' GROUP BY gateway",
375	OBJECT_K
376	);
377
378	// This month
379	$thisMonth = $this->wpdb->get_row($this->wpdb->prepare(
380	"SELECT COUNT(*) as count, SUM(amount) as total_amount
381	FROM {$table} WHERE status = 'completed' AND created_at >= %s",
382	date('Y-m-01 00:00:00')
383	));
384
385	return [
386	'by_status' => $statusStats,
387	'by_gateway' => $gatewayStats,
388	'this_month' => [
389	'count' => (int) ($thisMonth->count ?? 0),
390	'total_amount' => (float) ($thisMonth->total_amount ?? 0),
391	],
392	];
393	}
394
395	/**
396	* Flat counts for admin list toolbar (matches payment status filter keys).
397	*
398	* @return array{all:int,completed:int,pending:int,partial:int,failed:int,refunded:int,cancelled:int}
399	*/
400	public function getAdminStatusCounts(): array
401	{
402	$table = $this->getTableName();
403	$rows = $this->wpdb->get_results(
404	"SELECT status, COUNT(*) AS c FROM {$table} GROUP BY status",
405	ARRAY_A
406	) ?: [];
407
408	$out = [
409	'all' => 0,
410	'completed' => 0,
411	'pending' => 0,
412	'partial' => 0,
413	'failed' => 0,
414	'refunded' => 0,
415	'cancelled' => 0,
416	];
417
418	foreach ($rows as $row) {
419	$status = isset($row['status']) ? (string) $row['status'] : '';
420	$c = isset($row['c']) ? (int) $row['c'] : 0;
421	$out['all'] += $c;
422	if ($status !== '' && array_key_exists($status, $out)) {
423	$out[$status] = $c;
424	}
425	}
426
427	return $out;
428	}
429
430	/**
431	* Prepare payment data for insert/update
432	*
433	* @param array $data Raw data
434	* @return array Sanitized data
435	*/
436	private function preparePaymentData(array $data): array
437	{
438	$prepared = [];
439
440	if (array_key_exists('booking_id', $data)) {
441	$prepared['booking_id'] = (int) $data['booking_id'];
442	}
443
444	if (array_key_exists('customer_id', $data) && $this->hasCustomerColumn()) {
445	$prepared['customer_id'] = $data['customer_id'] !== null ? (int) $data['customer_id'] : null;
446	}
447
448	if (array_key_exists('transaction_id', $data)) {
449	$prepared['transaction_id'] = sanitize_text_field((string) $data['transaction_id']);
450	}
451
452	if (array_key_exists('gateway', $data)) {
453	$prepared['gateway'] = sanitize_text_field((string) $data['gateway']);
454	}
455
456	if (array_key_exists('amount', $data)) {
457	$prepared['amount'] = (float) $data['amount'];
458	}
459
460	if (array_key_exists('currency', $data)) {
461	$prepared['currency'] = sanitize_text_field((string) $data['currency']);
462	}
463
464	if (array_key_exists('status', $data)) {
465	$prepared['status'] = sanitize_text_field((string) $data['status']);
466	}
467
468	if (array_key_exists('payment_type', $data)) {
469	$prepared['payment_type'] = sanitize_text_field((string) $data['payment_type']);
470	}
471
472	if (array_key_exists('gateway_response', $data)) {
473	$prepared['gateway_response'] = is_string($data['gateway_response'])
474	? $data['gateway_response']
475	: wp_json_encode($data['gateway_response']);
476	}
477
478	if (array_key_exists('notes', $data)) {
479	$prepared['notes'] = sanitize_textarea_field((string) $data['notes']);
480	}
481
482	if (array_key_exists('meta', $data)) {
483	$prepared['meta'] = is_string($data['meta'])
484	? $data['meta']
485	: wp_json_encode($data['meta']);
486	}
487
488	if (array_key_exists('processed_at', $data) && $data['processed_at']) {
489	$prepared['processed_at'] = sanitize_text_field($data['processed_at']);
490	}
491
492	return $prepared;
493	}
494	}
495