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

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

211 lines 8.7 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 CanCalculateChurnRevenue
9	{
10	/**
11	* Retrieves the Churn Revenue 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 Churn Revenue for that interval).
21	*/
22	public function get_churn_revenue_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 ?: (defined('static::db_min_date') ? static::$db_min_date : '2000-01-01 00:00:00'));
25	$end_date_obj = DateTime::anyTimeToGmt($period_end_date_str ?: (defined('static::db_max_date') ? static::$db_max_date : gmdate('Y-m-d H:i:s')));
26
27	$data = [];
28	$period_intervals = [];
29
30	$temp_date_iterator = clone $start_date_obj;
31
32	$date_format = 'Y-m-d';
33	switch ($interval_type) {
34	case 'daily':
35	$temp_date_iterator->startOfDay();
36	$end_date_obj->endOfDay();
37	$date_format = 'Y-m-d';
38	break;
39	case 'monthly':
40	$temp_date_iterator->startOfMonth();
41	$end_date_obj->endOfMonth();
42	$date_format = 'Y-m';
43	break;
44	case 'yearly':
45	$temp_date_iterator->startOfYear();
46	$end_date_obj->endOfYear();
47	$date_format = 'Y';
48	break;
49	default:
50	$temp_date_iterator->startOfMonth();
51	$end_date_obj->endOfMonth();
52	$date_format = 'Y-m';
53	$interval_type = 'monthly';
54	break;
55	}
56
57	while ($temp_date_iterator <= $end_date_obj) {
58	$interval_start_date_obj = clone $temp_date_iterator;
59	$interval_end_date_obj = clone $temp_date_iterator;
60
61	switch ($interval_type) {
62	case 'daily':
63	$interval_start_date_obj->startOfDay();
64	$interval_end_date_obj->endOfDay();
65	break;
66	case 'monthly':
67	$interval_start_date_obj->startOfMonth();
68	$interval_end_date_obj->endOfMonth();
69	break;
70	case 'yearly':
71	$interval_start_date_obj->startOfYear();
72	$interval_end_date_obj->endOfYear();
73	break;
74	}
75
76	$period_intervals[$temp_date_iterator->format($date_format)] = [
77	'start' => $interval_start_date_obj->format('Y-m-d H:i:s'),
78	'end' => $interval_end_date_obj->format('Y-m-d H:i:s')
79	];
80
81	switch ($interval_type) {
82	case 'daily':
83	$temp_date_iterator->addDays(1);
84	break;
85	case 'monthly':
86	$temp_date_iterator->addMonth();
87	break;
88	case 'yearly':
89	$temp_date_iterator->addYear();
90	break;
91	}
92	}
93
94	$churn_revenue_events = $this->get_churn_revenue_events_in_range(
95	min(array_column($period_intervals, 'start')),
96	max(array_column($period_intervals, 'end')),
97	$currency
98	);
99
100	$trend_results = array_fill_keys(array_keys($period_intervals), 0.00);
101
102	foreach ($churn_revenue_events as $event) {
103	$churn_date_obj = DateTime::anyTimeToGmt($event['churn_date']);
104	$churn_amount = (float) $event['normalized_mrr'];
105
106	foreach ($period_intervals as $trend_date_key => $interval_dates) {
107	$interval_start_obj = DateTime::anyTimeToGmt($interval_dates['start']);
108	$interval_end_obj = DateTime::anyTimeToGmt($interval_dates['end']);
109
110	if ($churn_date_obj >= $interval_start_obj && $churn_date_obj <= $interval_end_obj) {
111	$trend_results[$trend_date_key] += $churn_amount;
112	break;
113	}
114	}
115	}
116
117	foreach ($period_intervals as $trend_date_key => $dates) {
118	$data[] = [
119	'trend_date' => $trend_date_key,
120	'value' => $trend_results[$trend_date_key]
121	];
122	}
123
124	return $data;
125	}
126
127	public function get_daily_churn_revenue_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
128	{
129	return $this->get_churn_revenue_trend($period_start_date_str, $period_end_date_str, 'daily', $currency);
130	}
131
132	public function get_monthly_churn_revenue_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
133	{
134	return $this->get_churn_revenue_trend($period_start_date_str, $period_end_date_str, 'monthly', $currency);
135	}
136
137	public function get_yearly_churn_revenue_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
138	{
139	return $this->get_churn_revenue_trend($period_start_date_str, $period_end_date_str, 'yearly', $currency);
140	}
141
142	/**
143	* Fetches churn revenue events within a given date range efficiently.
144	* Assumes $this->wpdb and $this->table_subscriptions are available in the class using this trait.
145	*
146	* @param string $range_start_gmt The overall start date of the range (GMT).
147	* @param string $range_end_gmt The overall end date of the range (GMT).
148	* @return array An array of associative arrays, each containing 'churn_date' and 'normalized_mrr'.
149	*/
150	protected function get_churn_revenue_events_in_range(string $range_start_gmt, string $range_end_gmt, $currency = null): array
151	{
152
153	$table_subscriptions = 'fct_subscriptions';
154	$churned_subscriptions_query = App::db()->table($table_subscriptions)
155	->select('id', 'recurring_amount', 'billing_interval', 'status')
156	->selectRaw('COALESCE(canceled_at, expire_at) as churn_date')
157	->whereIn('status', ['cancelled', 'expired', 'failed'])
158	->whereRaw('(COALESCE(canceled_at, expire_at) >= ? AND COALESCE(canceled_at, expire_at) <= ?)', [$range_start_gmt, $range_end_gmt]);
159	if ($currency) {
160	$churned_subscriptions_query->where('currency', $currency);
161	}
162
163	$churned_subscriptions = $churned_subscriptions_query->get()->toArray();
164
165
166	$churn_events = [];
167	foreach ($churned_subscriptions as $subscription) {
168	// Use recurring_amount for MRR
169	if (empty($subscription['recurring_amount']) \|\| empty($subscription['billing_interval']) \|\| empty($subscription['churn_date'])) {
170	continue;
171	}
172
173	$monthly_recurring_amount = (float) $subscription['recurring_amount'];
174	// Normalize to monthly recurring revenue
175	switch ($subscription['billing_interval']) {
176	case 'year':
177	$monthly_recurring_amount /= 12;
178	break;
179	case 'week':
180	$monthly_recurring_amount = ($monthly_recurring_amount * 52) / 12;
181	break;
182	case 'day':
183	$monthly_recurring_amount = ($monthly_recurring_amount * 365) / 12;
184	break;
185	case 'month':
186	break;
187	}
188
189	$churn_events[] = [
190	'churn_date' => $subscription['churn_date'],
191	'normalized_mrr' => $monthly_recurring_amount,
192	];
193	}
194
195	return $churn_events;
196	}
197
198	protected function get_total_churn_revenue(string $churn_date, $currency = null): float
199	{
200	$start_of_day = DateTime::anyTimeToGmt($churn_date)->startOfDay()->format('Y-m-d H:i:s');
201	$end_of_day = DateTime::anyTimeToGmt($churn_date)->endOfDay()->format('Y-m-d H:i:s');
202
203	$events = $this->get_churn_revenue_events_in_range($start_of_day, $end_of_day, $currency);
204	$total_churn_revenue = 0.00;
205	foreach ($events as $event) {
206	$total_churn_revenue += $event['normalized_mrr'];
207	}
208	return $total_churn_revenue;
209	}
210	}
211