fluent-cart / app / Http / Controllers / Reports / OverviewReportController.php

OverviewReportController.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at app/Http/Controllers/Reports/OverviewReportController.php

302 lines 10.6 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Http\Controllers\Reports;
4
5	use FluentCart\App\App;
6	use FluentCart\Api\StoreSettings;
7	use FluentCart\App\Helpers\Status;
8	use FluentCart\Framework\Http\Request\Request;
9	use FluentCart\App\Http\Controllers\Controller;
10
11	class OverviewReportController extends Controller
12	{
13	public function getOverview(Request $request): \WP_REST_Response
14	{
15	// We need 24 hours of data to compare the last 12 months
16	$start = gmdate('Y-m-01 00:00:00', strtotime('first day of 30 months ago'));
17	$end = gmdate('Y-m-t 23:59:59', strtotime('last day of this month'));
18
19	$currency = $request->get('params.currency');
20	if (empty($currency)) {
21	$currency = (new StoreSettings)->getCurrency();
22	}
23
24	$orderStats = $this->getMonthToMonthStats($start, $end, $currency);
25
26	$grossData = $this->generateComparesMonths($orderStats['gross']);
27	$netData = $this->generateComparesMonths($orderStats['net']);
28
29	$countryWiseStats = $this->getCountryWiseStatsImproved(
30	gmdate('Y-m-01 00:00:00', strtotime('first day of 11 months ago')), $end, 5, $currency
31	);
32
33	return $this->sendSuccess([
34	'data' => [
35	'gross_revenue' => $grossData,
36	'gross_revenue_quarterly' => $this->calculateQuaterlyGrowth($orderStats['gross']),
37	'net_revenue' => $netData,
38	'net_revenue_quarterly' => $this->calculateQuaterlyGrowth($orderStats['net']),
39	'gross_summary' => $this->calculateOverallSummary($grossData),
40	'net_summary' => $this->calculateOverallSummary($netData),
41	'top_country_net' => $countryWiseStats['net'],
42	'top_country_gross' => $countryWiseStats['gross'],
43	],
44	]);
45	}
46
47	protected function getMonthToMonthStats($start, $end, $currency = null)
48	{
49	$paymentStatuses = [
50	Status::PAYMENT_PAID,
51	Status::PAYMENT_PARTIALLY_PAID,
52	Status::PAYMENT_REFUNDED,
53	Status::PAYMENT_PARTIALLY_REFUNDED,
54	];
55
56	$orders = App::db()->table('fct_orders')
57	->selectRaw("
58	DATE_FORMAT(created_at, '%Y-%m') AS month,
59
60	SUM(total_paid) AS gross,
61
62	SUM(total_paid - total_refund - tax_total - shipping_tax) AS net
63	")
64	->whereIn('payment_status', $paymentStatuses)
65	->whereBetween('created_at', [$start, $end])
66	// ->where('currency', $currency)
67	->groupBy('month')
68	->orderBy('month')
69	->get();
70
71	return $this->fillData([$start, $end], $orders);
72	}
73
74	private function fillData($dateRange, $results)
75	{
76	$gross = [];
77	$net = [];
78
79	$startDate = new \DateTime($dateRange[0]);
80	$endDate = new \DateTime($dateRange[1]);
81	$interval = new \DateInterval('P1M'); // 1 month interval
82	$period = new \DatePeriod($startDate, $interval, $endDate);
83
84	foreach ($period as $date) {
85	$formattedDate = $date->format('Y-m');
86
87	// Initialize all months to 0
88	$gross[$formattedDate] = 0;
89	$net[$formattedDate] = 0;
90	}
91	// Fill in the results
92	foreach ($results as $row) {
93	$month = $row->month;
94	if (isset($gross[$month])) {
95	$gross[$month] = (int) $row->gross;
96	$net[$month] = (int) $row->net;
97	}
98	}
99
100	return [
101	'gross' => $gross,
102	'net' => $net,
103	];
104	}
105
106	private function generateComparesMonths($results)
107	{
108	// Get the last 12 months (May 2024 to May 2025)
109	$last_12_months = \array_slice($results, -12, 12, true);
110
111	$result = [];
112
113	foreach ($last_12_months as $month => $current_value) {
114	// Extract year and month
115	[$current_year, $month_num] = explode('-', $month);
116	// Calculate previous year's month (e.g., 2025-05 -> 2024-05)
117	$prev_year = $current_year - 1;
118	$prev_month = sprintf('%d-%02d', $prev_year, $month_num);
119
120	// Get previous year's value if it exists
121	$prev_value = isset($results[$prev_month]) ? $results[$prev_month] : null;
122
123	// Calculate YoY growth if previous value exists
124	$yy_growth = null;
125	if ($prev_value !== null && $prev_value != 0) {
126	$yy_growth = number_format((($current_value - $prev_value) / $prev_value) * 100, 2, '.', '');
127	}
128
129	// Build the result array for this month
130	$result[$month] = [
131	'current' => $current_value,
132	'prev' => $prev_value,
133	'yoy_growth' => $yy_growth,
134	];
135	}
136
137	return $result;
138	}
139
140	private function calculateOverallSummary($data)
141	{
142	$TotalRevenue = array_sum(array_column($data, 'current'));
143	$TotalRevenuePrev = array_sum(array_column($data, 'prev'));
144	$YoYGrowth = $TotalRevenuePrev ? number_format((($TotalRevenue - $TotalRevenuePrev) / $TotalRevenuePrev) * 100, 2, '.', '') : 100;
145
146	return [
147	'total' => $TotalRevenue,
148	'total_prev' => $TotalRevenuePrev,
149	'yoy_growth' => $YoYGrowth,
150	];
151	}
152
153	private function calculateQuaterlyGrowth($data)
154	{
155	$getQuarterName = function ($date) {
156	$month = (int) substr($date, 5, 2);
157	$year = substr($date, 0, 4);
158	$quarter = ceil($month / 3);
159
160	return "Q$quarter-$year";
161	};
162
163	$allQuarters = [];
164
165	foreach ($data as $date => $amount) {
166	$quarter = $getQuarterName($date);
167	if (!isset($allQuarters[$quarter])) {
168	$allQuarters[$quarter] = 0;
169	}
170	$allQuarters[$quarter] += $amount;
171	}
172
173	// get the last 4 quarters
174	$last4Quarters = array_slice($allQuarters, -4, 4, true);
175	$formattedLast4Quarters = [];
176	foreach ($last4Quarters as $quarter => $amount) {
177	[$q, $year] = explode('-', $quarter);
178	$prevYearQuarter = $q . '-' . ($year - 1);
179	$prevQAmount = isset($allQuarters[$prevYearQuarter]) ? $allQuarters[$prevYearQuarter] : 0;
180	$formattedLast4Quarters[$quarter] = [
181	'current' => $amount,
182	'prev_year' => $prevQAmount,
183	'yy_growth' => $prevQAmount ? number_format((($amount - $prevQAmount) / $prevQAmount) * 100, 2, '.', '') : null,
184	];
185	}
186
187	return $formattedLast4Quarters;
188	}
189
190	protected function getCountryWiseStatsImproved($start_date, $end_date, $limit = 5, $currency = null)
191	{
192	global $wpdb;
193
194	$net_revenue_column = 'SUM(o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) AS net_revenue';
195	$gross_revenue_column = 'SUM(o.total_paid) AS gross_revenue';
196
197	$currencyFilter = $currency ? 'AND o.currency = ?' : '';
198
199	$top_countries_query = "WITH monthly_country_revenue AS (
200	SELECT
201	DATE_FORMAT(o.created_at, '%Y-%m') AS month,
202	a.country,
203	$net_revenue_column,
204	$gross_revenue_column,
205	RANK() OVER (PARTITION BY DATE_FORMAT(o.created_at, '%Y-%m') ORDER BY SUM(o.total_paid - o.total_refund) DESC) AS revenue_rank
206	FROM {$wpdb->prefix}fct_orders o
207	INNER JOIN {$wpdb->prefix}fct_order_addresses a ON o.id = a.order_id
208	WHERE
209	o.payment_status IN ('paid', 'partially-paid', 'refunded', 'partially-refunded')
210	AND o.created_at >= ?
211	AND o.created_at < ?
212	AND a.type = 'billing'
213	AND a.country IS NOT NULL
214	AND a.country != ''
215	{$currencyFilter}
216	GROUP BY DATE_FORMAT(o.created_at, '%Y-%m'), a.country
217	)
218	SELECT
219	month,
220	country,
221	net_revenue,
222	gross_revenue,
223	revenue_rank
224	FROM monthly_country_revenue
225	WHERE revenue_rank <= 5
226	ORDER BY month, revenue_rank";
227
228	$bindings = [$start_date, $end_date];
229	if ($currency) {
230	$bindings[] = $currency;
231	}
232
233	$top_countries_results = App::db()->select(App::db()->raw($top_countries_query), $bindings);
234
235	$grossByMonth = [];
236	$netByMonth = [];
237
238	$startDate = new \DateTime($start_date);
239	$endDate = new \DateTime($end_date);
240	$interval = new \DateInterval('P1M'); // 1 month interval
241	$period = new \DatePeriod($startDate, $interval, $endDate);
242	foreach ($period as $date) {
243	$formattedDate = $date->format('Y-m');
244
245	$grossByMonth[$formattedDate] = [];
246	$netByMonth[$formattedDate] = [];
247	}
248
249	$grossByCountries = [];
250	$netByCountries = [];
251
252	foreach ($top_countries_results as $result) {
253	$arrKey = $result->month;
254	if (!isset($grossByMonth[$arrKey])) {
255	continue; // Skip if the month key does not exist
256	}
257
258	$netRevenue = (int) $result->net_revenue;
259	$grossRevenue = (int) $result->gross_revenue;
260
261	if (!isset($grossByCountries[$result->country])) {
262	$grossByCountries[$result->country] = 0;
263	$netByCountries[$result->country] = 0;
264	}
265
266	$grossByCountries[$result->country] += $grossRevenue;
267	$netByCountries[$result->country] += $netRevenue;
268
269	$grossByMonth[$arrKey][$result->country] = $grossRevenue;
270	$netByMonth[$arrKey][$result->country] = $netRevenue;
271	}
272
273	// sort by revenue in descending order $byCountries
274	arsort($grossByCountries);
275	arsort($netByCountries);
276
277	$grossByCountries = array_slice($grossByCountries, 0, $limit, true);
278	$netByCountries = array_slice($netByCountries, 0, $limit, true);
279
280	foreach ($grossByMonth as $month => $countries) {
281	// Sort countries by revenue in descending order
282	arsort($countries);
283	// Limit to top $limit countries
284	$grossByMonth[$month] = array_slice($countries, 0, $limit, true);
285
286	arsort($netByMonth[$month]);
287	$netByMonth[$month] = array_slice($netByMonth[$month], 0, $limit, true);
288	}
289
290	return [
291	'gross' => [
292	'by_month' => $grossByMonth,
293	'by_countries' => $grossByCountries,
294	],
295	'net' => [
296	'by_month' => $netByMonth,
297	'by_countries' => $netByCountries,
298	],
299	];
300	}
301	}
302