yatra / app / Repositories / PaymentRepository.php

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

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