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

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

359 lines 12.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report;
4
5	use FluentCart\App\App;
6	use FluentCart\App\Helpers\Helper;
7	use FluentCart\App\Helpers\Status;
8	use FluentCart\App\Models\Activity;
9	use FluentCart\App\Models\Order;
10	use FluentCart\App\Services\Report\Concerns\CanParseAddressField;
11	use FluentCart\App\Services\Report\Concerns\HasRange;
12	use FluentCart\Framework\Database\Orm\Builder;
13	use FluentCart\Framework\Support\Arr;
14	use FluentCart\Framework\Support\DateTime;
15
16	class DashBoardReportService extends ReportService
17	{
18	use HasRange, CanParseAddressField;
19
20	protected $salesGrowthChart = [];
21
22	protected $dashBoardStats = [];
23
24
25	protected $totalOrders = 0;
26
27	protected $totalPaidOrders = 0;
28
29	protected $totalOrderItems = 0;
30
31	protected $totalOrderValue = 0;
32
33
34	protected function modifyQuery(Builder $query): Builder
35	{
36	return $query
37	->withCount('order_items')
38	->with('transactions')
39	->withCount([
40	'transactions',
41	'transactions as refund_count' => function ($query) {
42	$query->where('transaction_type', Status::TRANSACTION_TYPE_REFUND);
43	}
44	])
45	->orderBy('created_at');
46	}
47
48
49	public function getModel(): string
50	{
51	return Order::class;
52	}
53
54	/**
55	* Prepares report data by calculating total orders, total paid orders, total order items, and total order value.
56	*
57	* This method performs the following calculations:
58	* - Counts the total number of orders.
59	* - Counts the total number of paid orders.
60	* - Calculates the total number of items across all orders.
61	* - Calculates the total number of items in paid orders.
62	* - Calculates the total value of paid orders.
63	*
64	* @return void
65	*/
66	protected function prepareReportData(): void
67	{
68
69	$this->totalOrders = $this->data->count();
70	$this->totalPaidOrders = $this->data->where('payment_status', 'paid')->count();
71
72	$this->totalOrderItems = $this->data->where('payment_status', 'paid')->sum('order_items_count');
73	$this->totalOrderValue = $this->data->where('payment_status', 'paid')->sum('subtotal');
74	}
75
76	/**
77	* Get dashboard statistics for the given date range.
78	*
79	* @param string $previousStartDate The start date of the previous period.
80	* @param string $previousEndDate The end date of the previous period.
81	* @return array An array containing the dashboard statistics.
82	*/
83
84	public function getDashBoardStats($startDate, $endDate, $previousStartDate, $previousEndDate)
85	{
86	global $wpdb;
87
88	$currency = $this->filters['currency'] ?? null;
89	$currencyFilter = $currency ? 'AND currency = ?' : '';
90
91	$query = "
92	SELECT
93	COUNT(*) AS total_orders,
94	SUM(CASE WHEN payment_status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
95	(SELECT COUNT(*) FROM {$wpdb->prefix}fct_order_items WHERE order_id IN (
96	SELECT id FROM {$wpdb->prefix}fct_orders WHERE payment_status = 'paid'
97	AND created_at BETWEEN ? AND ?
98	{$currencyFilter}
99	)) AS total_paid_order_items,
100	SUM(CASE WHEN payment_status = 'paid' THEN total_paid ELSE 0 END) AS total_paid_amounts
101	FROM {$wpdb->prefix}fct_orders
102	WHERE created_at BETWEEN ? AND ?
103	{$currencyFilter}
104	";
105
106	$currentBindings = [
107	$startDate->format('Y-m-d'),
108	$endDate->format('Y-m-d 23:59:59'),
109	];
110	if ($currency) {
111	$currentBindings[] = $currency;
112	}
113	$currentBindings[] = $startDate->format('Y-m-d');
114	$currentBindings[] = $endDate->format('Y-m-d 23:59:59');
115	if ($currency) {
116	$currentBindings[] = $currency;
117	}
118
119	$currentStats = App::db()->select($query, $currentBindings)[0];
120
121	$previousBindings = [
122	$previousStartDate->format('Y-m-d'),
123	$previousEndDate->format('Y-m-d 23:59:59'),
124	];
125	if ($currency) {
126	$previousBindings[] = $currency;
127	}
128	$previousBindings[] = $previousStartDate->format('Y-m-d');
129	$previousBindings[] = $previousEndDate->format('Y-m-d 23:59:59');
130	if ($currency) {
131	$previousBindings[] = $currency;
132	}
133
134	$previousStats = App::db()->select($query, $previousBindings)[0];
135	$this->dashBoardStats = [
136	'total_orders' => [
137	'title' => __('All Orders', 'fluent-cart'),
138	'icon' => 'AllOrdersIcon',
139	'current_count' => (int)$currentStats->total_orders ?? 0,
140	'compare_count' => (int)$previousStats->total_orders ?? 0,
141	],
142	'paid_orders' => [
143	'title' => __('Paid Orders', 'fluent-cart'),
144	'icon' => 'Money',
145	'current_count' => (int)$currentStats->paid_orders ?? 0,
146	'compare_count' => (int)$previousStats->paid_orders ?? 0,
147	],
148	'total_paid_order_items' => [
149	'title' => __('Paid Order Items', 'fluent-cart'),
150	'icon' => 'OrderItemsIcon',
151	'current_count' => (int)$currentStats->total_paid_order_items ?? 0,
152	'compare_count' => (int)$previousStats->total_paid_order_items ?? 0,
153	],
154	'total_paid_amounts' => [
155	'title' => __('Order Value (Paid)', 'fluent-cart'),
156	'icon' => 'OrderValueIcon',
157	'current_count' => (int)$currentStats->total_paid_amounts ?? 0,
158	'compare_count' => (int)$previousStats->total_paid_amounts ?? 0,
159	'is_cents' => true,
160	],
161	];
162
163	return ['dashBoardStats' => $this->dashBoardStats];
164	}
165
166	public function getSalesGrowthChart(array $params)
167	{
168	$group = ReportHelper::processGroup(
169	$params['startDate'], $params['endDate'], $params['groupKey']
170	);
171
172	$query = App::db()->table('fct_orders as o')
173	->selectRaw("{$group['field']},
174
175	COUNT(o.id) AS orders,
176
177	SUM(
178	o.total_paid
179	- o.total_refund
180	- o.tax_total
181	- o.shipping_tax
182	) / 100 AS net_revenue")
183	->groupByRaw($group['by'])
184	->orderByRaw($group['by']);
185
186	$query = $this->applyFilters($query, $params);
187
188	$result = $query->get();
189
190	$keys = ['orders', 'net_revenue'];
191	$groups = $this->getPeriodRange(
192	$params['startDate'], $params['endDate'], $group['key'], $keys
193	);
194
195	foreach ($result as $item) {
196	$groups[$item->group] = [
197	'year' => $item->year,
198	'group' => $item->group,
199	'orders' => (int)$item->orders,
200	'net_revenue' => (float)$item->net_revenue,
201	];
202	}
203
204	return array_values($groups);
205	}
206
207	public function getCountryHeatMap()
208	{
209	$results = App::db()->table('fct_order_addresses as ao')
210	->select('ao.country', App::db()->raw('COUNT(ao.id) as value'))
211	->join('fct_orders as o', 'ao.order_id', '=', 'o.id')
212	->where('ao.type', 'billing')
213	->groupBy('ao.country')
214	->get();
215
216
217	//$results = $wpdb->get_results($query);
218
219	$countryLists = Helper::getCountyIsoLists();
220
221	$other = __('Uncategorized', 'fluent-cart');
222	$transformedData = $results->map(function ($item) use ($countryLists, $other) {
223	$countryCode = strtoupper((string)$item->country);
224	$isKnown = isset($countryLists[$countryCode]);
225
226	// `name` is the translated label; the map matches on the locale-stable `country_code`.
227	return [
228	'country_code' => $isKnown ? $countryCode : null,
229	'name' => $isKnown ? $countryLists[$countryCode] : $other,
230	'value' => (int)$item->value,
231	];
232	})->toArray();
233
234	// sort by value
235	usort($transformedData, function ($a, $b) {
236	return $a['value'] <=> $b['value'];
237	});
238
239	return [
240	'countryHeatMap' => $transformedData,
241	];
242	}
243
244	public static function getRecentOrders()
245	{
246
247	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Static query with no variables
248	//$recentOrders = $wpdb->get_results($query, ARRAY_A);
249	global $wpdb;
250	$prefix = $wpdb->prefix;
251	$recentOrders = App::db()->table("fct_orders as o")
252	->select([
253	'o.id',
254	'o.customer_id',
255	App::db()->raw("CONCAT(c.first_name, ' ', c.last_name) AS customer_name"),
256	App::db()->raw("(o.total_amount) / 100 as total_amount"),
257	'o.created_at',
258	App::db()->raw("(SELECT COUNT(*) FROM {$prefix}fct_order_items WHERE order_id = o.id) AS order_items_count"),
259	])
260	->join("fct_customers as c", 'o.customer_id', '=', 'c.id')
261	->orderBy('o.created_at', 'desc')
262	->limit(10)
263	->get()
264	->map(fn($item) => (array)$item) // convert each object to array
265	->toArray();
266	return [
267	'recentOrders' => $recentOrders
268	];
269	}
270
271	public static function getRecentActivities($groupKey)
272	{
273	// Build the base query
274	$query = Activity::query()->select('title', 'content', 'created_at', 'created_by', 'module_name', 'module_id');
275
276	// Apply date conditions based on the groupKey
277	switch ($groupKey) {
278	case 'today':
279	$query->whereDate('created_at', DateTime::now()->today());
280	break;
281	case 'yesterday':
282	$query->whereDate('created_at', DateTime::now()->subDays(1));
283	break;
284	case 'this_week':
285	$query->whereBetween('created_at', [
286	DateTime::now()->startOfWeek(),
287	DateTime::now()->endOfWeek()
288	]);
289	break;
290	case 'all':
291	default:
292	// No date filter for 'all'
293	break;
294	}
295
296	// Execute the query
297	$recentActivities = $query->orderBy('created_at', 'desc')
298	->limit(10)
299	->get()
300	->toArray();
301
302	return [
303	'recentActivities' => $recentActivities
304	];
305	}
306
307	public static function getSummary()
308	{
309
310	$postsSummary = App::db()->table("posts")
311	->select([
312	App::db()->raw("COUNT(*) AS total_products"),
313	App::db()->raw("SUM(CASE WHEN post_status = 'draft' THEN 1 ELSE 0 END) AS draft_products"),
314	])
315	->where('post_type', 'fluent-products')
316	->first(); // single row as object
317
318
319	$postsSummary = (array)$postsSummary;
320
321	// Query to count active and expired coupons from the fct_coupons table
322	$couponsSummary = App::db()->table("fct_coupons")
323	->select([
324	App::db()->raw("
325	SUM(
326	CASE
327	WHEN (end_date IS NULL OR end_date = '0000-00-00 00:00:00' OR end_date >= CURDATE())
328	AND status = 'active'
329	THEN 1 ELSE 0
330	END
331	) AS active_coupons
332	"),
333	App::db()->raw("
334	SUM(
335	CASE
336	WHEN (end_date < CURDATE() AND end_date != '0000-00-00 00:00:00'
337	AND status IN ('expired', 'disabled'))
338	THEN 1 ELSE 0
339	END
340	) AS expired_coupons
341	"),
342	])
343	->first();
344
345	$couponsSummary = (array)$couponsSummary;
346
347
348
349	return [
350	'summaryData' => [
351	'total_products' => (int)Arr::get($postsSummary, 'total_products', 0),
352	'draft_products' => (int)Arr::get($postsSummary, 'draft_products', 0),
353	'active_coupons' => (int)Arr::get($couponsSummary, 'active_coupons', 0),
354	'expired_coupons' => (int)Arr::get($couponsSummary, 'expired_coupons', 0),
355	]
356	];
357	}
358	}
359