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

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

355 lines 12.0 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	$countryName = Arr::get($countryLists, $item->country, $other);
224	return [
225	'name' => $countryName,
226	'value' => (int)$item->value,
227	];
228	})->toArray();
229
230	// sort by value
231	usort($transformedData, function ($a, $b) {
232	return $a['value'] <=> $b['value'];
233	});
234
235	return [
236	'countryHeatMap' => $transformedData,
237	];
238	}
239
240	public static function getRecentOrders()
241	{
242
243	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Static query with no variables
244	//$recentOrders = $wpdb->get_results($query, ARRAY_A);
245	global $wpdb;
246	$prefix = $wpdb->prefix;
247	$recentOrders = App::db()->table("fct_orders as o")
248	->select([
249	'o.id',
250	'o.customer_id',
251	App::db()->raw("CONCAT(c.first_name, ' ', c.last_name) AS customer_name"),
252	App::db()->raw("(o.total_amount) / 100 as total_amount"),
253	'o.created_at',
254	App::db()->raw("(SELECT COUNT(*) FROM {$prefix}fct_order_items WHERE order_id = o.id) AS order_items_count"),
255	])
256	->join("fct_customers as c", 'o.customer_id', '=', 'c.id')
257	->orderBy('o.created_at', 'desc')
258	->limit(10)
259	->get()
260	->map(fn($item) => (array)$item) // convert each object to array
261	->toArray();
262	return [
263	'recentOrders' => $recentOrders
264	];
265	}
266
267	public static function getRecentActivities($groupKey)
268	{
269	// Build the base query
270	$query = Activity::query()->select('title', 'content', 'created_at', 'created_by', 'module_name', 'module_id');
271
272	// Apply date conditions based on the groupKey
273	switch ($groupKey) {
274	case 'today':
275	$query->whereDate('created_at', DateTime::now()->today());
276	break;
277	case 'yesterday':
278	$query->whereDate('created_at', DateTime::now()->subDays(1));
279	break;
280	case 'this_week':
281	$query->whereBetween('created_at', [
282	DateTime::now()->startOfWeek(),
283	DateTime::now()->endOfWeek()
284	]);
285	break;
286	case 'all':
287	default:
288	// No date filter for 'all'
289	break;
290	}
291
292	// Execute the query
293	$recentActivities = $query->orderBy('created_at', 'desc')
294	->limit(10)
295	->get()
296	->toArray();
297
298	return [
299	'recentActivities' => $recentActivities
300	];
301	}
302
303	public static function getSummary()
304	{
305
306	$postsSummary = App::db()->table("posts")
307	->select([
308	App::db()->raw("COUNT(*) AS total_products"),
309	App::db()->raw("SUM(CASE WHEN post_status = 'draft' THEN 1 ELSE 0 END) AS draft_products"),
310	])
311	->where('post_type', 'fluent-products')
312	->first(); // single row as object
313
314
315	$postsSummary = (array)$postsSummary;
316
317	// Query to count active and expired coupons from the fct_coupons table
318	$couponsSummary = App::db()->table("fct_coupons")
319	->select([
320	App::db()->raw("
321	SUM(
322	CASE
323	WHEN (end_date IS NULL OR end_date = '0000-00-00 00:00:00' OR end_date >= CURDATE())
324	AND status = 'active'
325	THEN 1 ELSE 0
326	END
327	) AS active_coupons
328	"),
329	App::db()->raw("
330	SUM(
331	CASE
332	WHEN (end_date < CURDATE() AND end_date != '0000-00-00 00:00:00'
333	AND status IN ('expired', 'disabled'))
334	THEN 1 ELSE 0
335	END
336	) AS expired_coupons
337	"),
338	])
339	->first();
340
341	$couponsSummary = (array)$couponsSummary;
342
343
344
345	return [
346	'summaryData' => [
347	'total_products' => (int)Arr::get($postsSummary, 'total_products', 0),
348	'draft_products' => (int)Arr::get($postsSummary, 'draft_products', 0),
349	'active_coupons' => (int)Arr::get($couponsSummary, 'active_coupons', 0),
350	'expired_coupons' => (int)Arr::get($couponsSummary, 'expired_coupons', 0),
351	]
352	];
353	}
354	}
355