yatra / app / Repositories / PaymentRepository.php

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

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