| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Services\Report\Concerns\Subscription; |
| 4 |
|
| 5 |
use FluentCart\App\Services\DateTime\DateTime; |
| 6 |
|
| 7 |
trait CanCalculateMrr |
| 8 |
{ |
| 9 |
// /** |
| 10 |
// * Retrieves the Monthly Recurring Revenue (MRR) trend over a specified period, |
| 11 |
// * aggregated by the given interval (daily, monthly, yearly). |
| 12 |
// * |
| 13 |
// * @param string|null $period_start_date_str Optional. The start date of the period in 'Y-m-d H:i:s' format. |
| 14 |
// * If null, uses the earliest available date from the database. |
| 15 |
// * @param string|null $period_end_date_str Optional. The end date of the period in 'Y-m-d H:i:s' format. |
| 16 |
// * If null, uses the latest available date from the database. |
| 17 |
// * @param string $interval_type The aggregation interval: 'daily', 'monthly', or 'yearly'. |
| 18 |
// * @return array An array of associative arrays, each containing 'trend_date' (formatted based on interval) |
| 19 |
// * and 'value' (total MRR for that interval). |
| 20 |
// */ |
| 21 |
// public function get_mrr_trend($period_start_date_str = null, $period_end_date_str = null, $interval_type = 'monthly', $currency = null) |
| 22 |
// { |
| 23 |
// $start_date_obj = DateTime::anyTimeToGmt($period_start_date_str ?: (defined('static::db_min_date') ? static::$db_min_date : '2000-01-01 00:00:00')); |
| 24 |
// $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'))); |
| 25 |
|
| 26 |
// $data = []; |
| 27 |
// $snapshot_dates_to_fetch = []; |
| 28 |
|
| 29 |
// $temp_date_iterator = clone $start_date_obj; |
| 30 |
|
| 31 |
// $date_format = 'Y-m-d'; |
| 32 |
// switch ($interval_type) { |
| 33 |
// case 'daily': |
| 34 |
// $temp_date_iterator->startOfDay(); |
| 35 |
// $end_date_obj->endOfDay(); |
| 36 |
// $date_format = 'Y-m-d'; |
| 37 |
// break; |
| 38 |
// case 'monthly': |
| 39 |
// $temp_date_iterator->startOfMonth(); |
| 40 |
// $end_date_obj->endOfMonth(); |
| 41 |
// $date_format = 'Y-m'; |
| 42 |
// break; |
| 43 |
// case 'yearly': |
| 44 |
// $temp_date_iterator->startOfYear(); |
| 45 |
// $end_date_obj->endOfYear(); |
| 46 |
// $date_format = 'Y'; |
| 47 |
// break; |
| 48 |
// default: |
| 49 |
// $temp_date_iterator->startOfMonth(); |
| 50 |
// $end_date_obj->endOfMonth(); |
| 51 |
// $date_format = 'Y-m'; |
| 52 |
// $interval_type = 'monthly'; |
| 53 |
// break; |
| 54 |
// } |
| 55 |
|
| 56 |
// while ($temp_date_iterator <= $end_date_obj) { |
| 57 |
// $snapshot_date = clone $temp_date_iterator; |
| 58 |
// switch ($interval_type) { |
| 59 |
// case 'daily': |
| 60 |
// $snapshot_date->endOfDay(); |
| 61 |
// break; |
| 62 |
// case 'monthly': |
| 63 |
// $snapshot_date->endOfMonth(); |
| 64 |
// break; |
| 65 |
// case 'yearly': |
| 66 |
// $snapshot_date->endOfYear(); |
| 67 |
// break; |
| 68 |
// } |
| 69 |
// $snapshot_dates_to_fetch[$temp_date_iterator->format($date_format)] = $snapshot_date->format('Y-m-d H:i:s'); |
| 70 |
|
| 71 |
// switch ($interval_type) { |
| 72 |
// case 'daily': |
| 73 |
// $temp_date_iterator->addDays(1); |
| 74 |
// break; |
| 75 |
// case 'monthly': |
| 76 |
// $temp_date_iterator->addMonth(); |
| 77 |
// break; |
| 78 |
// case 'yearly': |
| 79 |
// $temp_date_iterator->addYear(); |
| 80 |
// break; |
| 81 |
// } |
| 82 |
// } |
| 83 |
|
| 84 |
// $mrr_values_by_snapshot_date = $this->get_total_mrr_for_multiple_dates(array_values($snapshot_dates_to_fetch), $currency); |
| 85 |
|
| 86 |
// $current_date_iterator = clone $start_date_obj; |
| 87 |
// switch ($interval_type) { |
| 88 |
// case 'daily': |
| 89 |
// $current_date_iterator->startOfDay(); |
| 90 |
// break; |
| 91 |
// case 'monthly': |
| 92 |
// $current_date_iterator->startOfMonth(); |
| 93 |
// break; |
| 94 |
// case 'yearly': |
| 95 |
// $current_date_iterator->startOfYear(); |
| 96 |
// break; |
| 97 |
// } |
| 98 |
|
| 99 |
// while ($current_date_iterator <= $end_date_obj) { |
| 100 |
// $trend_date_key = $current_date_iterator->format($date_format); |
| 101 |
// $snapshot_date_string = $snapshot_dates_to_fetch[$trend_date_key]; |
| 102 |
|
| 103 |
// $mrr_value = isset($mrr_values_by_snapshot_date[$snapshot_date_string]) |
| 104 |
// ? (float) $mrr_values_by_snapshot_date[$snapshot_date_string] |
| 105 |
// : 0.00; |
| 106 |
|
| 107 |
// $data[] = [ |
| 108 |
// 'trend_date' => $trend_date_key, |
| 109 |
// 'value' => $mrr_value |
| 110 |
// ]; |
| 111 |
|
| 112 |
// switch ($interval_type) { |
| 113 |
// case 'daily': |
| 114 |
// $current_date_iterator->addDays(1); |
| 115 |
// break; |
| 116 |
// case 'monthly': |
| 117 |
// $current_date_iterator->addMonth(); |
| 118 |
// break; |
| 119 |
// case 'yearly': |
| 120 |
// $current_date_iterator->addYear(); |
| 121 |
// break; |
| 122 |
// } |
| 123 |
// } |
| 124 |
// return $data; |
| 125 |
// } |
| 126 |
|
| 127 |
// public function get_daily_total_mrr_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null) |
| 128 |
// { |
| 129 |
// return $this->get_mrr_trend($period_start_date_str, $period_end_date_str, 'daily', $currency); |
| 130 |
// } |
| 131 |
|
| 132 |
// public function get_monthly_total_mrr_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null) |
| 133 |
// { |
| 134 |
// return $this->get_mrr_trend($period_start_date_str, $period_end_date_str, 'monthly', $currency); |
| 135 |
// } |
| 136 |
|
| 137 |
// public function get_yearly_total_mrr_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null) |
| 138 |
// { |
| 139 |
// return $this->get_mrr_trend($period_start_date_str, $period_end_date_str, 'yearly', $currency); |
| 140 |
// } |
| 141 |
|
| 142 |
// /** |
| 143 |
// * Fetches MRR for multiple snapshot dates efficiently. |
| 144 |
// * Assumes $this->wpdb and $this->table_subscriptions are available in the class using this trait. |
| 145 |
// * |
| 146 |
// * @param array $snapshot_dates An array of snapshot dates in 'Y-m-d H:i:s' GMT format. |
| 147 |
// * @return array An associative array where keys are snapshot dates ('Y-m-d H:i:s') and values are the total MRR. |
| 148 |
// */ |
| 149 |
// protected function get_total_mrr_for_multiple_dates(array $snapshot_dates, $currency = null): array |
| 150 |
// { |
| 151 |
// if (empty($snapshot_dates)) { |
| 152 |
// return []; |
| 153 |
// } |
| 154 |
|
| 155 |
// global $wpdb; |
| 156 |
// $table_subscriptions = $wpdb->prefix . 'fct_subscriptions'; |
| 157 |
// $table_orders = $wpdb->prefix . 'fct_orders'; |
| 158 |
|
| 159 |
// $unique_snapshot_gmt_strings = array_unique($snapshot_dates); |
| 160 |
// $mrr_data = array_fill_keys($unique_snapshot_gmt_strings, 0.00); |
| 161 |
|
| 162 |
// $min_snapshot_date_gmt = min($unique_snapshot_gmt_strings); |
| 163 |
// $max_snapshot_date_gmt = max($unique_snapshot_gmt_strings); |
| 164 |
|
| 165 |
// // Fetch subscriptions using accurate column names from SubscriptionsMigrator.php |
| 166 |
// // Columns: recurring_amount, billing_interval, created_at, expire_at, canceled_at, status |
| 167 |
// $currency_filter = ''; |
| 168 |
// if (!empty($currency)) { |
| 169 |
// $currency_filter = $wpdb->prepare(" AND o.currency = %s", esc_sql($currency)); |
| 170 |
// } |
| 171 |
// // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 172 |
// $subscriptions = $wpdb->get_results( |
| 173 |
// $wpdb->prepare( |
| 174 |
// "SELECT |
| 175 |
// s.id, |
| 176 |
// s.recurring_amount, |
| 177 |
// s.billing_interval, |
| 178 |
// s.created_at, -- Subscription start date |
| 179 |
// s.expire_at, -- End of fixed term/trial |
| 180 |
// s.canceled_at, -- Explicit cancellation date |
| 181 |
// s.status |
| 182 |
// FROM {$table_subscriptions} s |
| 183 |
// INNER JOIN {$table_orders} o ON s.parent_order_id = o.id |
| 184 |
// WHERE s.status IN (%s, %s, %s) -- Consider 'active', 'trialling', 'pending' for potential MRR |
| 185 |
// AND ( |
| 186 |
// s.created_at <= %s -- Subscription started on or before the latest snapshot |
| 187 |
// AND ( |
| 188 |
// s.expire_at IS NULL OR s.expire_at >= %s -- Hasn't expired before the earliest snapshot |
| 189 |
// ) |
| 190 |
// AND ( |
| 191 |
// s.canceled_at IS NULL OR s.canceled_at > %s -- Not cancelled before or on the earliest snapshot |
| 192 |
// ) |
| 193 |
// ) |
| 194 |
// {$currency_filter}", |
| 195 |
// 'active', 'trialling', 'pending', // Adjust statuses that contribute to MRR |
| 196 |
// $max_snapshot_date_gmt, |
| 197 |
// $min_snapshot_date_gmt, |
| 198 |
// $min_snapshot_date_gmt |
| 199 |
// ), |
| 200 |
// ARRAY_A |
| 201 |
// ); |
| 202 |
|
| 203 |
// foreach ($unique_snapshot_gmt_strings as $snapshot_gmt_str) { |
| 204 |
// $snapshot_date_obj = DateTime::anyTimeToGmt($snapshot_gmt_str); |
| 205 |
// $current_mrr_for_snapshot = 0.00; |
| 206 |
|
| 207 |
// foreach ($subscriptions as $subscription) { |
| 208 |
// // Use recurring_amount for MRR |
| 209 |
// if (empty($subscription['recurring_amount']) || empty($subscription['billing_interval'])) { |
| 210 |
// continue; |
| 211 |
// } |
| 212 |
|
| 213 |
// $sub_created_at_obj = DateTime::anyTimeToGmt($subscription['created_at']); |
| 214 |
// $sub_expire_at_obj = null; |
| 215 |
// if (!empty($subscription['expire_at'])) { |
| 216 |
// $sub_expire_at_obj = DateTime::anyTimeToGmt($subscription['expire_at']); |
| 217 |
// } |
| 218 |
// $sub_canceled_at_obj = null; |
| 219 |
// if (!empty($subscription['canceled_at'])) { |
| 220 |
// $sub_canceled_at_obj = DateTime::anyTimeToGmt($subscription['canceled_at']); |
| 221 |
// } |
| 222 |
|
| 223 |
// $is_active_at_snapshot = false; |
| 224 |
|
| 225 |
// // Check active statuses |
| 226 |
// if (!in_array($subscription['status'], ['active', 'trialling', 'pending'])) { |
| 227 |
// continue; |
| 228 |
// } |
| 229 |
|
| 230 |
// // Check if created_at is on or before snapshot_date |
| 231 |
// if ($sub_created_at_obj <= $snapshot_date_obj) { |
| 232 |
// // Check if not expired before snapshot_date |
| 233 |
// $not_expired = ($sub_expire_at_obj === null || $sub_expire_at_obj >= $snapshot_date_obj); |
| 234 |
|
| 235 |
// // Check if not cancelled before or on snapshot_date |
| 236 |
// $not_cancelled = ($sub_canceled_at_obj === null || $sub_canceled_at_obj > $snapshot_date_obj); |
| 237 |
|
| 238 |
// if ($not_expired && $not_cancelled) { |
| 239 |
// $is_active_at_snapshot = true; |
| 240 |
// } |
| 241 |
// } |
| 242 |
|
| 243 |
// if ($is_active_at_snapshot) { |
| 244 |
// $monthly_recurring_amount = (float) $subscription['recurring_amount']; |
| 245 |
// // Normalize to monthly recurring revenue |
| 246 |
// switch ($subscription['billing_interval']) { |
| 247 |
// case 'yearly': |
| 248 |
// $monthly_recurring_amount /= 12; |
| 249 |
// break; |
| 250 |
// case 'weekly': |
| 251 |
// $monthly_recurring_amount = ($monthly_recurring_amount * 52) / 12; |
| 252 |
// break; |
| 253 |
// case 'daily': |
| 254 |
// $monthly_recurring_amount = ($monthly_recurring_amount * 365) / 12; |
| 255 |
// break; |
| 256 |
// case 'monthly': |
| 257 |
// break; |
| 258 |
// } |
| 259 |
// $current_mrr_for_snapshot += $monthly_recurring_amount; |
| 260 |
// } |
| 261 |
// } |
| 262 |
// $mrr_data[$snapshot_gmt_str] = $current_mrr_for_snapshot; |
| 263 |
// } |
| 264 |
|
| 265 |
// return $mrr_data; |
| 266 |
// } |
| 267 |
|
| 268 |
// protected function get_total_mrr(string $snapshot_date, $currency = null): float |
| 269 |
// { |
| 270 |
// $results = $this->get_total_mrr_for_multiple_dates([$snapshot_date], $currency); |
| 271 |
// return isset($results[$snapshot_date]) ? (float) $results[$snapshot_date] : 0.00; |
| 272 |
// } |
| 273 |
} |
| 274 |
|
