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

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

198 lines 6.4 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report;
4
5	use FluentCart\App\App;
6
7	class RevenueReportService extends ReportService
8	{
9	public function revenueByGroup(array $params = []): array
10	{
11	$itemsSub = App::db()->table('fct_order_items')
12	->selectRaw('order_id, SUM(quantity) AS total_items')
13	->groupBy('order_id');
14
15	$query = App::db()->table('fct_orders as o')
16	->leftJoinSub($itemsSub, 'oi_sum', fn($join) => $join->on('oi_sum.order_id', '=', 'o.id'));
17
18	$query = $this->applyFilters($query, $params);
19
20	$groupKey = ReportHelper::sanitizeGroupKey($params['groupKey'] ?? null);
21
22	$groupKeyExpression = '';
23
24	if (in_array($groupKey, ['billing_country', 'shipping_country'])) {
25	$type = $groupKey === 'billing_country' ? 'billing' : 'shipping';
26
27	$query->leftJoin('fct_order_addresses as a', function ($join) use ($type) {
28	$join->on('o.id', 'a.order_id')->where('a.type', $type);
29	});
30
31	$groupKeyExpression = "COALESCE(a.country, 'Uncategorized') AS `{$groupKey}`";
32	} elseif ($groupKey === 'payment_method') {
33	$groupKeyExpression = "COALESCE(o.payment_method, 'Unknown') AS `{$groupKey}`";
34	} else {
35	$groupKeyExpression = "COALESCE(o.{$groupKey}, 'Unknown') AS `{$groupKey}`";
36	}
37
38	$query->selectRaw("{$groupKeyExpression},
39
40	COUNT(o.id) AS orders,
41
42	COUNT(CASE WHEN o.total_refund > 0 THEN 1 END) AS refunded_orders,
43
44	SUM(o.total_paid) / 100 AS gross_sale,
45
46	SUM(
47	o.total_paid
48	- o.total_refund
49	- o.tax_total
50	- o.shipping_tax
51	) / 100 AS net_sale,
52
53	CASE
54	WHEN COUNT(o.id) = 0 THEN 0
55	ELSE SUM(o.total_paid) / COUNT(o.id) / 100
56	END AS average_order_gross,
57
58	CASE
59	WHEN COUNT(o.id) = 0 THEN 0
60	ELSE SUM(
61	o.total_paid
62	- o.total_refund
63	- o.tax_total
64	- o.shipping_tax
65	) / COUNT(o.id) / 100
66	END AS average_order_net,
67
68	SUM(COALESCE(oi_sum.total_items, 0)) AS items,
69
70	SUM(o.shipping_total) / 100 AS shipping_total,
71
72	SUM(o.tax_total + o.shipping_tax) / 100 AS total_tax,
73
74	SUM(o.total_refund) / 100 AS total_refunds,
75
76	COUNT(DISTINCT o.customer_id) AS customer_count");
77
78	return $query->groupByRaw($groupKey)->orderByRaw($groupKey)->get()->toArray();
79	}
80
81	public function getRevenueData($params = []): array
82	{
83	$startDate = $params['startDate'];
84	$endDate = $params['endDate'];
85
86	$group = ReportHelper::processGroup($startDate, $endDate, $params['groupKey']);
87
88	$query = App::db()->table('fct_orders as o')
89	->selectRaw("{$group['field']},
90
91	SUM(o.total_paid) / 100 AS total_sales,
92
93	SUM(
94	o.total_paid
95	- o.total_refund
96	- o.tax_total
97	- o.shipping_tax
98	) / 100 AS net_revenue,
99
100	SUM(o.shipping_total) / 100 AS shipping_total,
101
102	SUM(o.tax_total + o.shipping_tax) / 100 AS total_tax,
103
104	SUM(o.total_refund) / 100 AS total_refunds,
105
106	CASE
107	WHEN COUNT(o.id) = 0 THEN 0
108	ELSE SUM(o.total_paid / 100) / COUNT(o.id)
109	END AS average_order_value,
110
111	COUNT(o.id) AS order_count,
112
113	-- COUNT(CASE WHEN o.total_paid = o.total_refund AND o.total_paid > 0 THEN 1 END) AS refunded_orders
114	COUNT(CASE WHEN o.total_refund > 0 THEN 1 END) AS refunded_orders");
115
116	$query = $this->applyFilters($query, $params);
117
118	$results = $query->groupByRaw($group['by'])->orderBy('group')->get();
119
120	$summary = [
121	'net_revenue' => 0,
122	'gross_sale' => 0,
123	'total_refunded_amount' => 0,
124	'tax_total' => 0,
125	'shipping_total' => 0,
126	'order_count' => 0,
127	'refunded_orders' => 0,
128	];
129
130	$keys = [
131	'total_sales',
132	'net_revenue',
133	'shipping_total',
134	'total_tax',
135	'total_refunds',
136	'order_count',
137	'refunded_orders'
138	];
139
140	$groups = $this->getPeriodRange($startDate, $endDate, $group['key'], $keys);
141
142	foreach ($results as $row) {
143	$groups[$row->group] = [
144	'year' => $row->year,
145	'group' => $row->group,
146	'total_sales' => $row->total_sales,
147	'net_revenue' => $row->net_revenue,
148	'shipping_total' => $row->shipping_total,
149	'total_tax' => $row->total_tax,
150	'total_refunds' => $row->total_refunds,
151	'order_count' => $row->order_count,
152	'refunded_orders' => $row->refunded_orders,
153	];
154
155	$summary['net_revenue'] += $row->net_revenue;
156	$summary['gross_sale'] += $row->total_sales;
157	$summary['total_refunded_amount'] += $row->total_refunds;
158	$summary['tax_total'] += $row->total_tax;
159	$summary['shipping_total'] += $row->shipping_total;
160	$summary['order_count'] += $row->order_count;
161	$summary['refunded_orders'] += $row->refunded_orders;
162	}
163
164	return [
165	'groups' => array_values($groups),
166	'summary' => $summary,
167	'groupKey' => $group['key'],
168	];
169	}
170
171	public function getFluctuations($currentMetrics, $previousMetrics)
172	{
173	$fluctuations = [];
174
175	$indices = [
176	'net_revenue',
177	'gross_sale',
178	'total_refunded_amount',
179	'tax_total',
180	'shipping_total',
181	'discount',
182	];
183
184	foreach ($indices as $key) {
185	$previousValue = $previousMetrics[$key] ?? 0;
186	$currentValue = $currentMetrics[$key] ?? 0;
187
188	if ($previousValue > 0) {
189	$fluctuations[$key] = (($currentValue - $previousValue) / $previousValue) * 100;
190	} else {
191	$fluctuations[$key] = $currentValue > 0 ? 100 : 0;
192	}
193	}
194
195	return $fluctuations;
196	}
197	}
198