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

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

169 lines 5.8 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report;
4
5	use FluentCart\App\App;
6
7	class ProductReportService extends ReportService
8	{
9	public function getProductTopChart(array $params)
10	{
11	$orderItemsSubQuery = App::db()->table('fct_order_items')
12	->selectRaw('order_id, object_id, post_id, title, post_title, SUM(quantity) AS sum_quantity')
13	->groupByRaw('order_id, object_id, post_id')
14	->whereBetween('created_at', [$params['startDate'], $params['endDate']])
15	->when(!empty($params['variationIds']), fn($q) =>
16	$q->whereIn('object_id', $params['variationIds'])
17	);
18
19	$query = App::db()->table('fct_orders as o')
20	->joinSub($orderItemsSubQuery, 'oi', function ($join) {
21	$join->on('oi.order_id', '=', 'o.id');
22	})
23	->leftJoin('posts as p', 'p.ID', '=', 'oi.post_id')
24	->leftJoin('fct_product_variations as pv', 'pv.id', '=', 'oi.object_id')
25	->selectRaw("
26	DATE_FORMAT(o.created_at, '%Y-%m') as month,
27
28	oi.object_id,
29
30	SUM(oi.sum_quantity) as total_quantity,
31
32	pv.variation_title AS latest_title,
33
34	p.post_title AS latest_post_title
35	")
36	->groupByRaw("DATE_FORMAT(o.created_at, '%Y-%m'), oi.object_id")
37	->orderByRaw("DATE_FORMAT(o.created_at, '%Y-%m')");
38
39	$query = $this->applyFilters($query, $params);
40
41	$raw = $query->get();
42
43	$result = [];
44
45	foreach ($raw as $item) {
46	$month = $item->month;
47	if (!isset($result[$month])) {
48	$result[$month] = [];
49	}
50	$result[$month][] = [
51	'name' => $item->latest_title,
52	'post_title' => $item->latest_post_title,
53	'value' => (int)$item->total_quantity,
54	'variation_id' => (int)$item->object_id,
55	];
56	}
57
58	return $result;
59	}
60
61	public function getProductReportData($params = [])
62	{
63	$group = ReportHelper::processGroup(
64	$params['startDate'], $params['endDate'], $params['groupKey']
65	);
66
67	$orderItemsSubQuery = App::db()->table('fct_order_items')
68	->selectRaw('order_id, object_id, SUM(quantity) AS sum_quantity')
69	->groupByRaw('order_id, object_id')
70	->whereBetween('created_at', [$params['startDate'], $params['endDate']])
71	->when(!empty($params['variationIds']), fn($q) =>
72	$q->whereIn('object_id', $params['variationIds'])
73	);
74
75	$query = App::db()->table('fct_orders as o')
76	->joinSub($orderItemsSubQuery, 'oi', function ($join) {
77	$join->on('oi.order_id', '=', 'o.id');
78	})
79	->selectRaw("
80	{$group['field']},
81
82	SUM(oi.sum_quantity) AS units_sold,
83
84	SUM(o.total_paid) / 100 AS gross_sale,
85
86	SUM(o.total_refund) / 100 AS total_refunds,
87
88	SUM(
89	o.total_paid
90	- o.total_refund
91	- o.tax_total
92	- o.shipping_tax
93	) / 100 AS net_sale,
94
95	ROUND(SUM(o.total_paid) / NULLIF(SUM(oi.sum_quantity), 0) / 100, 2) AS average_selling_price,
96
97	COUNT(DISTINCT o.customer_id) AS customers_count
98	")
99	->groupByRaw($group['by'])
100	->orderByRaw($group['by']);
101
102	$query = $this->applyFilters($query, $params);
103
104	$results = $query->get();
105
106	$summary = [
107	'units_sold' => 0,
108	'gross_sale' => 0,
109	'total_refunds' => 0,
110	'net_sale' => 0,
111	'average_selling_price' => 0,
112	'customer_count' => 0,
113	];
114
115	$groups = $this->getPeriodRange(
116	$params['startDate'], $params['endDate'], $group['key'], array_keys($summary)
117	);
118
119	foreach ($results as $row) {
120	$item = [
121	'year' => (int) $row->year,
122	'group' => $row->group,
123	'units_sold' => (int) $row->units_sold,
124	'gross_sale' => (float) $row->gross_sale,
125	'total_refunds' => (float) $row->total_refunds,
126	'net_sale' => (float) $row->net_sale,
127	'average_selling_price' => (float) $row->average_selling_price,
128	'customer_count' => (int) $row->customers_count,
129	];
130
131	$groups[$row->group] = $item;
132
133	$summary['units_sold'] += $item['units_sold'];
134	$summary['gross_sale'] += $item['gross_sale'];
135	$summary['total_refunds'] += $item['total_refunds'];
136	$summary['net_sale'] += $item['net_sale'];
137	$summary['customer_count'] += $item['customer_count'];
138	}
139
140	if ($summary['units_sold']) {
141	$summary['average_selling_price'] = $summary['gross_sale'] / $summary['units_sold'];
142	}
143
144	return [
145	'summary' => $summary,
146	'grouped' => array_values($groups),
147	];
148	}
149
150	public function calculateFluctuations($currentMetrics, $previousMetrics)
151	{
152	$fluctuations = [];
153
154	$indices = ['units_sold', 'gross_sale', 'average_selling_price', 'customer_count', 'total_refunds', 'net_sale'];
155
156	foreach ($indices as $key) {
157	$previousValue = $previousMetrics[$key] ?? 0;
158	$currentValue = $currentMetrics[$key] ?? 0;
159
160	if ($previousValue > 0) {
161	$fluctuations[$key] = (($currentValue - $previousValue) / $previousValue) * 100;
162	} else {
163	$fluctuations[$key] = $currentValue > 0 ? 100 : 0;
164	}
165	}
166
167	return $fluctuations;
168	}
169	}