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

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

371 lines 12.9 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report;
4
5	use FluentCart\App\App;
6
7	class OrderReportService extends ReportService
8	{
9	public function groupBy($params = [])
10	{
11	$groupKey = ReportHelper::sanitizeGroupKey($params['groupKey'] ?? null);
12
13	$query = App::db()->table('fct_orders as o');
14
15	$query = $this->applyFilters($query, $params);
16
17	if (in_array($groupKey, ['billing_country', 'shipping_country'])) {
18	$type = $groupKey === 'billing_country' ? 'billing' : 'shipping';
19
20	$query->leftJoin(
21	'fct_order_addresses as a',
22	fn ($join) => $join->on('o.id', '=', 'a.order_id')->where('a.type', '=', $type)
23	);
24
25	$query->selectRaw("COALESCE(a.country, 'Uncategorized') AS `{$groupKey}`");
26	} else {
27	$query->selectRaw("COALESCE(o.{$groupKey}, 'Uncategorized') AS `{$groupKey}`");
28	}
29
30	$query->selectRaw("COUNT(o.id) AS orders,
31
32	SUM(o.total_paid) / 100 AS gross_sale,
33
34	SUM(
35	o.total_paid
36	- o.total_refund
37	- o.tax_total
38	- o.shipping_tax
39	) / 100 AS net_sale,
40
41	SUM(o.total_paid) / COUNT(o.id) / 100 AS average_order_gross,
42
43	SUM(
44	o.total_paid
45	- o.total_refund
46	- o.tax_total
47	- o.shipping_tax
48	) / COUNT(o.id) / 100 AS average_order_net")
49	->groupBy($groupKey)
50	->orderBy($groupKey);
51
52	return $query->get();
53	}
54
55	public function getOrderValueDistribution(array $params)
56	{
57	$query = App::db()->table('fct_orders as o')
58	->selectRaw("SUM(CASE WHEN total_amount <= 10000 THEN 1 ELSE 0 END) AS `0-100`,
59	SUM(CASE WHEN total_amount > 10000 AND total_amount <= 20000 THEN 1 ELSE 0 END) AS `100-200`,
60	SUM(CASE WHEN total_amount > 20000 AND total_amount <= 30000 THEN 1 ELSE 0 END) AS `200-300`,
61	SUM(CASE WHEN total_amount > 30000 AND total_amount <= 40000 THEN 1 ELSE 0 END) AS `300-400`,
62	SUM(CASE WHEN total_amount > 40000 AND total_amount <= 50000 THEN 1 ELSE 0 END) AS `400-500`,
63	SUM(CASE WHEN total_amount > 50000 AND total_amount <= 60000 THEN 1 ELSE 0 END) AS `500-600`,
64	SUM(CASE WHEN total_amount > 60000 AND total_amount <= 70000 THEN 1 ELSE 0 END) AS `600-700`,
65	SUM(CASE WHEN total_amount > 70000 AND total_amount <= 80000 THEN 1 ELSE 0 END) AS `700-800`,
66	SUM(CASE WHEN total_amount > 80000 AND total_amount <= 90000 THEN 1 ELSE 0 END) AS `800-900`,
67	SUM(CASE WHEN total_amount > 90000 AND total_amount <= 100000 THEN 1 ELSE 0 END) AS `900-1000`,
68	SUM(CASE WHEN total_amount > 100000 THEN 1 ELSE 0 END) AS `1000+`");
69
70	$query = $this->applyFilters($query, $params);
71
72	return $query->first();
73	}
74
75	public function getOrderLineChart($params = [])
76	{
77	$monthBetween = $params['endDate']->diffInMonths($params['startDate']) + 1;
78
79	$group = ReportHelper::processGroup(
80	$params['startDate'], $params['endDate'], $params['groupKey']
81	);
82
83	$variationIds = array_map('intval', $params['variationIds'] ?? []);
84
85	$itemsSub = App::db()->table('fct_order_items')
86	->selectRaw('order_id, SUM(quantity) AS total_items')
87	->groupBy('order_id')
88	->whereBetween('created_at', [$params['startDate'], $params['endDate']])
89	->when($variationIds, fn($q) => $q->whereIn('object_id', $variationIds));
90
91	$orderQuery = App::db()->table('fct_orders as o')
92	->joinSub($itemsSub, 'oi_sum', fn($join) => $join->on('oi_sum.order_id', '=', 'o.id'));
93
94	$orderQuery = $this->applyFilters($orderQuery, $params);
95
96	$orderData = $orderQuery->selectRaw("{$group['field']},
97
98	SUM(o.total_paid) / 100 AS gross_sale,
99
100	SUM(
101	o.total_paid
102	- o.total_refund
103	- o.tax_total
104	- o.shipping_tax
105	) / 100 AS net_revenue,
106
107	COUNT(o.id) AS order_count,
108
109	SUM(o.total_refund) / 100 AS total_refund,
110
111	SUM(o.shipping_total) / 100 AS shipping_total,
112
113	SUM(o.tax_total + o.shipping_tax) / 100 AS tax_total,
114
115	CASE
116	WHEN COUNT(o.id) = 0 THEN 0
117	ELSE SUM(
118	o.total_paid
119	- o.total_refund
120	- o.tax_total
121	- o.shipping_tax
122	) / COUNT(o.id) / 100
123	END AS average_net,
124
125	CASE
126	WHEN COUNT(o.id) = 0 THEN 0
127	ELSE SUM(o.total_paid) / COUNT(o.id) / 100
128	END AS average_gross,
129
130	SUM(COALESCE(oi_sum.total_items, 0)) AS total_item_count,
131
132	CASE
133	WHEN COUNT(o.id) = 0 THEN 0
134	ELSE oi_sum.total_items / COUNT(o.id)
135	END AS average_order_items_count,
136
137	ROUND(AVG(o.total_paid / 100)) AS average_order_gross")
138	->groupByRaw($group['by'])
139	->get();
140
141	$summary = [
142	'net_revenue' => 0,
143	'gross_sale' => 0,
144	'order_count' => 0,
145	'total_item_count' => 0,
146	'average_net' => 0,
147	'average_order_items_count' => 0,
148	'average_gross' => 0,
149	'total_refund' => 0,
150	'tax_total' => 0,
151	'shipping_total' => 0
152	];
153
154	$groups = $this->getPeriodRange(
155	$params['startDate'], $params['endDate'], $group['key'], array_keys($summary)
156	);
157
158	foreach ($orderData as $group) {
159	$summary['net_revenue'] += $group->net_revenue;
160	$summary['gross_sale'] += $group->gross_sale;
161	$summary['order_count'] += $group->order_count;
162	$summary['total_item_count'] += $group->total_item_count;
163	$summary['total_refund'] += $group->total_refund;
164	$summary['tax_total'] += $group->tax_total;
165	$summary['shipping_total'] += $group->shipping_total;
166
167	$groups[$group->group] = (array) $group;
168	}
169
170	if ($orderData->count()) {
171	$summary['average_net'] = $summary['net_revenue'] / $summary['order_count'];
172	$summary['average_order_items_count'] = $summary['total_item_count'] / $summary['order_count'];
173	$summary['average_gross'] = $summary['gross_sale'] / $summary['order_count'];
174
175	$summary['monthly_net'] = $summary['net_revenue'] / $monthBetween;
176	$summary['monthly_gross'] = $summary['gross_sale'] / $monthBetween;
177	$summary['monthly_orders'] = $summary['order_count'] / $monthBetween;
178	$summary['monthly_items'] = $summary['total_item_count'] / $monthBetween;
179	}
180
181	return [
182	'chartData' => array_values($groups),
183	'summary' => $summary,
184	];
185	}
186
187	public function getNewVsReturningCustomer($params = [])
188	{
189	$orders = App::db()->table('fct_orders as o')
190	->leftJoin('fct_customers as c', 'c.id', '=', 'o.customer_id');
191
192	$orders = $this->applyFilters($orders, $params);
193
194	$rows = $orders->selectRaw("CASE
195	WHEN
196	c.first_purchase_date IS NOT NULL
197	AND c.first_purchase_date >= '{$params['startDate']}'
198	THEN 'New'
199	ELSE 'Returning'
200	END AS customer_type,
201
202	COUNT(DISTINCT o.customer_id) AS customer_count,
203
204	COUNT(o.id) AS order_count,
205
206	SUM(o.total_paid) / 100 AS gross_sales,
207
208	SUM(
209	o.total_paid
210	- o.total_refund
211	- o.tax_total
212	- o.shipping_tax
213	) / 100 AS net_sales")
214	->groupBy('customer_type')
215	->get();
216
217	$result = [];
218
219	if ($rows->count()) {
220	foreach ($rows as $index => $item) {
221	$result[$index] = $item;
222
223	$result[$index]->average_net = $item->net_sales / $item->order_count;
224	$result[$index]->average_gross = $item->gross_sales / $item->order_count;
225	}
226	} else {
227	$metrics = [
228	'customer_type' => 'new',
229	'customer_count' => 0,
230	'order_count' => 0,
231	'net_sales' => 0,
232	'average_net' => 0,
233	'gross_sales' => 0,
234	'average_gross' => 0,
235	];
236
237	$result = [
238	$metrics,
239	wp_parse_args(['customer_type' => 'returning'], $metrics),
240	];
241	}
242
243	return $result;
244	}
245
246	public function getReportByDayAndHour(array $params): array
247	{
248	$query = App::db()->table('fct_orders as o')
249	->selectRaw("HOUR(o.created_at) AS hour_24,
250
251	DAYOFWEEK(o.created_at) AS day_of_week,
252
253	COUNT(o.id) AS order_count,
254
255	SUM(o.total_paid) / 100 AS gross_sale")
256	->groupByRaw('hour_24, day_of_week')
257	->orderByRaw('hour_24, day_of_week');
258
259	$query = $this->applyFilters($query, $params);
260
261	$ordersData = $query->get();
262
263	$daysOfWeek = [
264	1 => 'Sunday',
265	2 => 'Monday',
266	3 => 'Tuesday',
267	4 => 'Wednesday',
268	5 => 'Thursday',
269	6 => 'Friday',
270	7 => 'Saturday'
271	];
272
273	$structuredData = [];
274	$grossSaleByHour = [];
275
276	for ($hour = 0; $hour < 24; $hour++) {
277	$timeLabel = gmdate('g A', mktime($hour, 0));
278	$structuredData[$hour] = [
279	'hour' => $timeLabel,
280	'Sunday' => 0,
281	'Monday' => 0,
282	'Tuesday' => 0,
283	'Wednesday' => 0,
284	'Thursday' => 0,
285	'Friday' => 0,
286	'Saturday' => 0,
287	];
288
289	$grossSaleByHour[$hour] = [
290	'hour' => $timeLabel,
291	'gross_sale' => 0,
292	'order_count' => 0,
293	];
294	}
295
296	$grossSaleByDay = array_fill(1, 7, ['day' => 0, 'gross_sale' => 0, 'order_count' => 0]);
297
298	foreach ($ordersData as $order) {
299	$hour = (int) $order->hour_24;
300	$dayOfWeek = $daysOfWeek[$order->day_of_week];
301	$orderCount = $order->order_count;
302
303	$structuredData[$hour][$dayOfWeek] += $orderCount;
304
305	$grossSaleByDay[$order->day_of_week]['day'] = $order->day_of_week;
306	$grossSaleByDay[$order->day_of_week]['order_count'] += $orderCount;
307	$grossSaleByDay[$order->day_of_week]['gross_sale'] += $order->gross_sale;
308
309	$grossSaleByHour[$hour]['gross_sale'] += $order->gross_sale;
310	$grossSaleByHour[$hour]['order_count'] += $orderCount;
311	}
312
313	return [
314	'orderByDayAndHour' => $structuredData,
315	'grossSaleByDay' => array_values($grossSaleByDay),
316	'grossSaleByHour' => array_values($grossSaleByHour),
317	];
318	}
319
320	public function getItemCountDistribution(array $params): array
321	{
322	$itemsSub = App::db()->table('fct_order_items')
323	->selectRaw('order_id, SUM(quantity) AS item_count')
324	->groupBy('order_id')
325	->whereBetween('created_at', [$params['startDate'], $params['endDate']])
326	->when($params['variationIds'], fn($q) => $q->whereIn('object_id', $params['variationIds']));
327
328	$query = App::db()->table('fct_orders as o')
329	->selectRaw('COUNT(*) AS order_count, oi_sum.item_count')
330	->joinSub($itemsSub, 'oi_sum', fn($join) => $join->on('oi_sum.order_id', '=', 'o.id'))
331	->groupBy('oi_sum.item_count');
332
333	$query = $this->applyFilters($query, $params);
334
335	return $query->get()->toArray();
336	}
337
338	public function calculateFluctuations($currentData, $previousData)
339	{
340	$fluctuations = [];
341
342	foreach ($currentData as $key => $currentValue) {
343	$lastValue = $previousData[$key] ?? 0;
344	if ($lastValue > 0) {
345	$fluctuations[$key] = (($currentValue - $lastValue) / $lastValue) * 100;
346	} else {
347	$fluctuations[$key] = ($currentValue > 0) ? 100 : 0; // 100% increase if current value is greater than 0, else 0% change
348	}
349	}
350
351	return $fluctuations;
352	}
353
354	public function getOrderCompletionTime(array $params): array
355	{
356	$query = App::db()->table('fct_orders as o')
357	->selectRaw("TIMESTAMPDIFF(
358	HOUR, o.created_at, o.completed_at
359	) AS hour,
360
361	COUNT(o.id) AS orders")
362	->whereNotNull('o.completed_at')
363	->groupBy('hour')
364	->orderBy('hour');
365
366	$query = $this->applyFilters($query, $params);
367
368	return $query->get()->toArray();
369	}
370	}
371