fluent-cart / app / Services / Report / Concerns / Subscription / CanCalculateSubscriptionCountTrend.php

CanCalculateSubscriptionCountTrend.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.5.1, at app/Services/Report/Concerns/Subscription/CanCalculateSubscriptionCountTrend.php

234 lines 9.6 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report\Concerns\Subscription;
4
5	use FluentCart\App\App;
6	use FluentCart\App\Services\DateTime\DateTime;
7
8	trait CanCalculateSubscriptionCountTrend
9	{
10	/**
11	* Retrieves the active subscription count trend over a specified period,
12	* aggregated by the given interval (daily, monthly, yearly).
13	*
14	* @param string\|null $period_start_date_str Optional. The start date of the period in 'Y-m-d H:i:s' format.
15	* If null, uses the earliest available date from the database.
16	* @param string\|null $period_end_date_str Optional. The end date of the period in 'Y-m-d H:i:s' format.
17	* If null, uses the latest available date from the database.
18	* @param string $interval_type The aggregation interval: 'daily', 'monthly', or 'yearly'.
19	* @return array An array of associative arrays, each containing 'trend_date' (formatted based on interval)
20	* and 'value' (total active subscription count for that interval).
21	*/
22	public function get_subscription_count_trend($period_start_date_str = null, $period_end_date_str = null, $interval_type = 'monthly', $currency = null)
23	{
24	$start_date_obj = DateTime::anyTimeToGmt($period_start_date_str);
25	$end_date_obj = DateTime::anyTimeToGmt($period_end_date_str);
26	$originalTimeZone = DateTime::extractTimezone($period_start_date_str);
27	$offsetMinutes = DateTime::getTimezoneOffsetMinutes($originalTimeZone->getName());
28
29	$data = [];
30	$snapshot_dates_to_fetch = [];
31
32	$temp_date_iterator = clone $start_date_obj;
33
34	$date_format = 'Y-m-d';
35	switch ($interval_type) {
36	case 'daily':
37	$temp_date_iterator->startOfDay();
38	$end_date_obj->endOfDay();
39	$date_format = 'Y-m-d';
40	break;
41	case 'monthly':
42	$temp_date_iterator->startOfMonth();
43	$end_date_obj->endOfMonth();
44	$date_format = 'Y-m';
45	break;
46	case 'yearly':
47	$temp_date_iterator->startOfYear();
48	$end_date_obj->endOfYear();
49	$date_format = 'Y';
50	break;
51	default:
52	$temp_date_iterator->startOfMonth();
53	$end_date_obj->endOfMonth();
54	$date_format = 'Y-m';
55	$interval_type = 'monthly';
56	break;
57	}
58
59	while ($temp_date_iterator <= $end_date_obj) {
60	$snapshot_date = clone $temp_date_iterator;
61	switch ($interval_type) {
62	case 'daily':
63	$snapshot_date->endOfDay();
64	break;
65	case 'monthly':
66	$snapshot_date->endOfMonth();
67	break;
68	case 'yearly':
69	$snapshot_date->endOfYear();
70	break;
71	}
72	$snapshot_dates_to_fetch[$temp_date_iterator->format($date_format)] = $snapshot_date->format('Y-m-d H:i:s');
73
74	switch ($interval_type) {
75	case 'daily':
76	$temp_date_iterator->addDays(1);
77	break;
78	case 'monthly':
79	$temp_date_iterator->addMonth();
80	break;
81	case 'yearly':
82	$temp_date_iterator->addYear();
83	break;
84	}
85	}
86
87	$subscription_counts_by_snapshot_date = $this->get_total_subscription_counts_for_multiple_dates(array_values($snapshot_dates_to_fetch), $offsetMinutes, $currency);
88
89	$current_date_iterator = clone $start_date_obj;
90	switch ($interval_type) {
91	case 'daily':
92	$current_date_iterator->startOfDay();
93	break;
94	case 'monthly':
95	$current_date_iterator->startOfMonth();
96	break;
97	case 'yearly':
98	$current_date_iterator->startOfYear();
99	break;
100	}
101
102	while ($current_date_iterator <= $end_date_obj) {
103	$trend_date_key = $current_date_iterator->format($date_format);
104	$snapshot_date_string = $snapshot_dates_to_fetch[$trend_date_key];
105
106	$count_value = isset($subscription_counts_by_snapshot_date[$snapshot_date_string])
107	? (int)$subscription_counts_by_snapshot_date[$snapshot_date_string]
108	: 0;
109
110	$data[] = [
111	'trend_date' => $trend_date_key,
112	'value' => $count_value
113	];
114
115	switch ($interval_type) {
116	case 'daily':
117	$current_date_iterator->addDays(1);
118	break;
119	case 'monthly':
120	$current_date_iterator->addMonth();
121	break;
122	case 'yearly':
123	$current_date_iterator->addYear();
124	break;
125	}
126	}
127	return $data;
128	}
129
130	public function get_daily_subscription_count_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
131	{
132	return $this->get_subscription_count_trend($period_start_date_str, $period_end_date_str, 'daily', $currency);
133	}
134
135	public function get_monthly_subscription_count_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
136	{
137	return $this->get_subscription_count_trend($period_start_date_str, $period_end_date_str, 'monthly', $currency);
138	}
139
140	public function get_yearly_subscription_count_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
141	{
142	return $this->get_subscription_count_trend($period_start_date_str, $period_end_date_str, 'yearly', $currency);
143	}
144
145	/**
146	* Fetches total active subscription counts for multiple snapshot dates efficiently.
147	* Assumes $this->wpdb and $this->table_subscriptions are available in the class using this trait.
148	*
149	* @param array $snapshot_dates An array of snapshot dates in 'Y-m-d H:i:s' GMT format.
150	* @return array An associative array where keys are snapshot dates ('Y-m-d H:i:s') and values are the total count.
151	*/
152	protected function get_total_subscription_counts_for_multiple_dates(array $snapshot_dates, $offsetMinutes = 0, $currency = null): array
153	{
154	if (empty($snapshot_dates)) {
155	return [];
156	}
157
158	$table_subscriptions ='fct_subscriptions';;
159	$table_orders = 'fct_orders';
160
161	$unique_snapshot_gmt_strings = array_unique($snapshot_dates);
162	$counts_data = array_fill_keys($unique_snapshot_gmt_strings, 0);
163
164	$min_snapshot_date_gmt = min($unique_snapshot_gmt_strings);
165	$max_snapshot_date_gmt = max($unique_snapshot_gmt_strings);
166
167	$subscriptions_query = App::db()->table($table_subscriptions . ' as s')
168	->select('s.id')
169	->selectRaw('s.created_at + INTERVAL ? MINUTE as created_at', [$offsetMinutes])
170	->selectRaw('s.expire_at + INTERVAL ? MINUTE as expire_at', [$offsetMinutes])
171	->selectRaw('s.canceled_at + INTERVAL ? MINUTE as canceled_at', [$offsetMinutes])
172	->select('s.status')
173	->join($table_orders . ' as o', 's.parent_order_id', '=', 'o.id')
174	->whereIn('s.status', ['active', 'trialling', 'pending'])
175	->whereRaw('(s.created_at <= ? AND (s.expire_at IS NULL OR s.expire_at >= ?) AND (s.canceled_at IS NULL OR s.canceled_at > ?))', [
176	$max_snapshot_date_gmt,
177	$min_snapshot_date_gmt,
178	$min_snapshot_date_gmt
179	]);
180
181	if (!empty($currency)) {
182	$subscriptions_query->where('o.currency', $currency);
183	}
184
185	// Execute query
186	$subscriptions = $subscriptions_query->get()->toArray();
187
188
189	foreach ($unique_snapshot_gmt_strings as $snapshot_gmt_str) {
190	$snapshot_date_obj = DateTime::parse($snapshot_gmt_str);
191	$current_count_for_snapshot = 0;
192
193	foreach ($subscriptions as $subscription) {
194	$sub_created_at_obj = DateTime::parse($subscription['created_at']);
195	$sub_expire_at_obj = null;
196	if (!empty($subscription['expire_at'])) {
197	$sub_expire_at_obj = DateTime::parse($subscription['expire_at']);
198	}
199	$sub_canceled_at_obj = null;
200	if (!empty($subscription['canceled_at'])) {
201	$sub_canceled_at_obj = DateTime::parse($subscription['canceled_at']);
202	}
203
204	$is_active_at_snapshot = false;
205	if (!in_array($subscription['status'], ['active', 'trialling', 'pending'])) {
206	continue;
207	}
208
209	if ($sub_created_at_obj <= $snapshot_date_obj) {
210	$not_expired = ($sub_expire_at_obj === null \|\| $sub_expire_at_obj >= $snapshot_date_obj);
211	$not_cancelled = ($sub_canceled_at_obj === null \|\| $sub_canceled_at_obj > $snapshot_date_obj);
212
213	if ($not_expired && $not_cancelled) {
214	$is_active_at_snapshot = true;
215	}
216	}
217
218	if ($is_active_at_snapshot) {
219	$current_count_for_snapshot++;
220	}
221	}
222	$counts_data[$snapshot_gmt_str] = $current_count_for_snapshot;
223	}
224
225	return $counts_data;
226	}
227
228	protected function get_total_subscription_count(string $snapshot_date, $currency = null): int
229	{
230	$results = $this->get_total_subscription_counts_for_multiple_dates([$snapshot_date], 0, $currency);
231	return isset($results[$snapshot_date]) ? (int)$results[$snapshot_date] : 0;
232	}
233	}
234