yatra / app / Repositories / PaymentRepository.php

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

498 lines 14.6 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.contact_data,
180	b.contact_country,
181	b.customer_id,
182	b.total_amount as booking_total_amount,
183	b.amount_paid as booking_amount_paid,
184	b.amount_due as booking_amount_due,
185	b.travel_date as travel_date,
186	b.end_date as booking_end_date,
187	b.trip_id as trip_id,
188	b.payment_method as payment_method,
189	t.title as trip_title,
190	t.duration_days as trip_duration_days,
191	t.duration_nights as trip_duration_nights
192	FROM {$table} p
193	LEFT JOIN {$bookings_table} b ON p.booking_id = b.id
194	LEFT JOIN {$trips_table} t ON b.trip_id = t.id
195	WHERE p.id = %d",
196	$id
197	);
198
199	return $this->wpdb->get_row($query) ?: null;
200	}
201
202	/**
203	* Find payments by booking ID
204	*
205	* @param int $bookingId Booking ID
206	* @return array
207	*/
208	public function findByBookingId(int $bookingId): array
209	{
210	$table = $this->getTableName();
211
212	$query = $this->wpdb->prepare(
213	"SELECT * FROM {$table} WHERE booking_id = %d ORDER BY created_at DESC",
214	$bookingId
215	);
216
217	return $this->wpdb->get_results($query) ?: [];
218	}
219
220	/**
221	* Find the latest payment for a booking
222	*
223	* @param int $bookingId Booking ID
224	* @return object\|null
225	*/
226	public function findLatestByBookingId(int $bookingId): ?object
227	{
228	$table = $this->getTableName();
229
230	$query = $this->wpdb->prepare(
231	"SELECT * FROM {$table} WHERE booking_id = %d ORDER BY created_at DESC, id DESC LIMIT 1",
232	$bookingId
233	);
234
235	return $this->wpdb->get_row($query) ?: null;
236	}
237
238	/**
239	* Find payment by transaction ID
240	*
241	* @param string $transactionId Transaction ID
242	* @return object\|null
243	*/
244	public function findByTransactionId(string $transactionId): ?object
245	{
246	$table = $this->getTableName();
247
248	$query = $this->wpdb->prepare(
249	"SELECT * FROM {$table} WHERE transaction_id = %s",
250	sanitize_text_field($transactionId)
251	);
252
253	return $this->wpdb->get_row($query) ?: null;
254	}
255
256	/**
257	* Create a new payment
258	*
259	* @param array $data Payment data
260	* @return int Payment ID on success
261	* @throws \Exception on failure
262	*/
263	public function create(array $data): int
264	{
265	$table = $this->getTableName();
266
267	$insertData = $this->preparePaymentData($data);
268	$insertData['created_at'] = current_time('mysql');
269
270	$result = $this->wpdb->insert($table, $insertData);
271
272	if ($result === false) {
273	throw new \Exception('Failed to create payment: ' . $this->wpdb->last_error);
274	}
275
276	return $this->wpdb->insert_id;
277	}
278
279	/**
280	* Update a payment
281	*
282	* @param int $id Payment ID
283	* @param array $data Payment data to update
284	* @return bool
285	*/
286	public function update(int $id, array $data): bool
287	{
288	$table = $this->getTableName();
289
290	$updateData = $this->preparePaymentData($data);
291
292	$result = $this->wpdb->update(
293	$table,
294	$updateData,
295	['id' => $id],
296	null,
297	['%d']
298	);
299
300	return $result !== false;
301	}
302
303	/**
304	* Update payment status
305	*
306	* @param int $id Payment ID
307	* @param string $status New status
308	* @return bool
309	*/
310	public function updateStatus(int $id, string $status): bool
311	{
312	$table = $this->getTableName();
313
314	$data = ['status' => sanitize_text_field($status)];
315
316	if ($status === 'completed') {
317	$data['processed_at'] = current_time('mysql');
318	}
319
320	$result = $this->wpdb->update($table, $data, ['id' => $id]);
321
322	return $result !== false;
323	}
324
325	/**
326	* Delete a payment
327	*
328	* @param int $id Payment ID
329	* @return bool
330	*/
331	public function delete(int $id): bool
332	{
333	$table = $this->getTableName();
334
335	$result = $this->wpdb->delete($table, ['id' => $id], ['%d']);
336
337	return $result !== false;
338	}
339
340	/**
341	* Get total paid amount for a booking
342	*
343	* @param int $bookingId Booking ID
344	* @return float
345	*/
346	public function getTotalPaidForBooking(int $bookingId): float
347	{
348	$table = $this->getTableName();
349
350	$result = $this->wpdb->get_var($this->wpdb->prepare(
351	"SELECT SUM(amount) FROM {$table} WHERE booking_id = %d AND status = 'completed'",
352	$bookingId
353	));
354
355	return (float) ($result ?? 0);
356	}
357
358	/**
359	* Get payment statistics
360	*
361	* @return array
362	*/
363	public function getStats(): array
364	{
365	$table = $this->getTableName();
366
367	// Total by status
368	$statusStats = $this->wpdb->get_results(
369	"SELECT status, COUNT(*) as count, SUM(amount) as total_amount
370	FROM {$table} GROUP BY status",
371	OBJECT_K
372	);
373
374	// By gateway
375	$gatewayStats = $this->wpdb->get_results(
376	"SELECT gateway, COUNT(*) as count, SUM(amount) as total_amount
377	FROM {$table} WHERE status = 'completed' GROUP BY gateway",
378	OBJECT_K
379	);
380
381	// This month
382	$thisMonth = $this->wpdb->get_row($this->wpdb->prepare(
383	"SELECT COUNT(*) as count, SUM(amount) as total_amount
384	FROM {$table} WHERE status = 'completed' AND created_at >= %s",
385	date('Y-m-01 00:00:00')
386	));
387
388	return [
389	'by_status' => $statusStats,
390	'by_gateway' => $gatewayStats,
391	'this_month' => [
392	'count' => (int) ($thisMonth->count ?? 0),
393	'total_amount' => (float) ($thisMonth->total_amount ?? 0),
394	],
395	];
396	}
397
398	/**
399	* Flat counts for admin list toolbar (matches payment status filter keys).
400	*
401	* @return array{all:int,completed:int,pending:int,partial:int,failed:int,refunded:int,cancelled:int}
402	*/
403	public function getAdminStatusCounts(): array
404	{
405	$table = $this->getTableName();
406	$rows = $this->wpdb->get_results(
407	"SELECT status, COUNT(*) AS c FROM {$table} GROUP BY status",
408	ARRAY_A
409	) ?: [];
410
411	$out = [
412	'all' => 0,
413	'completed' => 0,
414	'pending' => 0,
415	'partial' => 0,
416	'failed' => 0,
417	'refunded' => 0,
418	'cancelled' => 0,
419	];
420
421	foreach ($rows as $row) {
422	$status = isset($row['status']) ? (string) $row['status'] : '';
423	$c = isset($row['c']) ? (int) $row['c'] : 0;
424	$out['all'] += $c;
425	if ($status !== '' && array_key_exists($status, $out)) {
426	$out[$status] = $c;
427	}
428	}
429
430	return $out;
431	}
432
433	/**
434	* Prepare payment data for insert/update
435	*
436	* @param array $data Raw data
437	* @return array Sanitized data
438	*/
439	private function preparePaymentData(array $data): array
440	{
441	$prepared = [];
442
443	if (array_key_exists('booking_id', $data)) {
444	$prepared['booking_id'] = (int) $data['booking_id'];
445	}
446
447	if (array_key_exists('customer_id', $data) && $this->hasCustomerColumn()) {
448	$prepared['customer_id'] = $data['customer_id'] !== null ? (int) $data['customer_id'] : null;
449	}
450
451	if (array_key_exists('transaction_id', $data)) {
452	$prepared['transaction_id'] = sanitize_text_field((string) $data['transaction_id']);
453	}
454
455	if (array_key_exists('gateway', $data)) {
456	$prepared['gateway'] = sanitize_text_field((string) $data['gateway']);
457	}
458
459	if (array_key_exists('amount', $data)) {
460	$prepared['amount'] = (float) $data['amount'];
461	}
462
463	if (array_key_exists('currency', $data)) {
464	$prepared['currency'] = sanitize_text_field((string) $data['currency']);
465	}
466
467	if (array_key_exists('status', $data)) {
468	$prepared['status'] = sanitize_text_field((string) $data['status']);
469	}
470
471	if (array_key_exists('payment_type', $data)) {
472	$prepared['payment_type'] = sanitize_text_field((string) $data['payment_type']);
473	}
474
475	if (array_key_exists('gateway_response', $data)) {
476	$prepared['gateway_response'] = is_string($data['gateway_response'])
477	? $data['gateway_response']
478	: wp_json_encode($data['gateway_response']);
479	}
480
481	if (array_key_exists('notes', $data)) {
482	$prepared['notes'] = sanitize_textarea_field((string) $data['notes']);
483	}
484
485	if (array_key_exists('meta', $data)) {
486	$prepared['meta'] = is_string($data['meta'])
487	? $data['meta']
488	: wp_json_encode($data['meta']);
489	}
490
491	if (array_key_exists('processed_at', $data) && $data['processed_at']) {
492	$prepared['processed_at'] = sanitize_text_field($data['processed_at']);
493	}
494
495	return $prepared;
496	}
497	}
498