fluent-cart / app / Services / Report / RefundReportService.php

RefundReportService.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at app/Services/Report/RefundReportService.php

159 lines 5.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report;
4
5	use FluentCart\App\App;
6
7	class RefundReportService extends ReportService
8	{
9	public function getRefundDataGroupedBy($params = []): array
10	{
11	$groupKey = $params['groupKey'];
12
13	$query = App::db()->query()
14	->from('fct_orders as o')
15	->join('fct_order_transactions as ot', 'o.id', '=', 'ot.order_id')
16	->selectRaw("COUNT(DISTINCT ot.order_id) as total_refunded,
17
18	SUM(ot.total) / 100 as total_refunded_amount,
19
20	(SUM(ot.total) / COUNT(DISTINCT ot.order_id)) / 100 as average_refunded_amount")
21	->whereIn('ot.status', ['refunded', 'partially_refunded'])
22	->where('o.total_refund', '>', 0);
23
24	$query = $this->applyFilters($query, $params);
25
26	if ($groupKey === 'payment_method') {
27	$query = $query->selectRaw("COALESCE(NULLIF(o.{$groupKey}, ''), 'Uncategorized') as group_key")->groupBy("o.{$groupKey}");
28	} else {
29	$type = $groupKey === 'billing_country' ? 'billing' : 'shipping';
30
31	$query = $query->join('fct_order_addresses as a', 'o.id', '=', 'a.order_id')
32	->selectRaw("COALESCE(a.country, 'Uncategorized') as group_key")
33	->where('a.type', $type)
34	->whereNotNull('a.country')
35	->groupBy('a.country');
36	}
37
38	$results = $query->get()->toArray();
39
40	$formattedResults = array_map(function ($row) use ($groupKey) {
41	return [
42	$groupKey => $row->group_key,
43	'totalRefunded' => (int) $row->total_refunded,
44	'totalRefundedAmount' => [
45	'total' => (float) $row->total_refunded_amount,
46	'average' => (float) $row->average_refunded_amount,
47	],
48	];
49	}, $results);
50
51	return $formattedResults;
52	}
53
54	public function getRefundData($params = []): array
55	{
56	$group = ReportHelper::processGroup(
57	$params['startDate'], $params['endDate'], $params['groupKey']
58	);
59
60	$orderSubQuery = App::db()->query()
61	->from('fct_orders as o')
62	->selectRaw("{$group['field']},
63
64	o.total_refund,
65
66	o.total_paid");
67
68	$orderSubQuery = $this->applyFilters($orderSubQuery, $params);
69
70	$refundData = App::db()->query()
71	->fromSub($orderSubQuery, 'subq')
72	->selectRaw("COALESCE(`group`, 'TOTAL') as `group`,
73
74	COUNT(
75	CASE WHEN total_refund > 0 THEN 1 END
76	) as refund_count,
77
78	SUM(total_refund) / 100 as refunded_amount,
79
80	CASE
81	WHEN COUNT(total_refund) > 0
82	THEN SUM(total_refund) / COUNT(total_refund) / 100
83	ELSE 0
84	END as average_refunded_amount,
85
86	CASE
87	WHEN SUM(total_paid) > 0
88	THEN (SUM(total_refund) / SUM(total_paid)) * 100
89	ELSE 0
90	END as refund_rate")
91	->groupByRaw("`group` WITH ROLLUP")
92	->get()
93	->toArray();
94
95	$summary = [
96	'refund_rate' => 0,
97	'refund_count' => 0,
98	'refunded_amount' => 0,
99	'average_refunded_amount' => 0,
100	];
101
102	if (!empty($refundData)) {
103	$summary = (array) array_pop($refundData);
104	}
105
106	return [
107	'summary' => $summary,
108	'grouped' => $refundData,
109	];
110	}
111
112	public function calculateFluctuations($currentMetrics, $previousMetrics)
113	{
114	$metrics = ['refund_count', 'refunded_amount', 'average_refunded_amount', 'refund_rate'];
115
116	$result = [];
117
118	foreach ($metrics as $metric) {
119	if ($previousMetrics[$metric] != 0) {
120	$result[$metric] = (($currentMetrics[$metric] - $previousMetrics[$metric]) / $previousMetrics[$metric]) * 100;
121	} else {
122	$result[$metric] = $currentMetrics[$metric] > 0 ? 100 : 0;
123	}
124	}
125
126	return $result;
127	}
128
129	public function weeksBetweenRefund($params = []): array
130	{
131	$startDate = $params['startDate'];
132	$endDate = $params['endDate'];
133
134	$subquery = App::db()->table('fct_order_transactions')
135	->selectRaw('
136	order_id,
137	MIN(created_at) AS refund_date
138	')
139	->whereIn('status', ['refunded', 'partially_refunded'])
140	->whereBetween('created_at', [$startDate, $endDate])
141	->groupBy('order_id');
142
143	$orderQuery = $this->applyFilters(
144	App::db()->table('fct_orders as o'), $params
145	);
146
147	return $orderQuery
148	->selectRaw('
149	COUNT(*) AS occurrence,
150	IFNULL(((GREATEST(TIMESTAMPDIFF(DAY, o.created_at, r.refund_date), 1) + 6) DIV 7), 1) AS weekBetween
151	')
152	->joinSub($subquery, 'r', fn ($join) => $join->on('o.id', '=', 'r.order_id'))
153	->groupBy('weekBetween')
154	->orderBy('weekBetween')
155	->get()
156	->toArray();
157	}
158	}
159