| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Services\Report; |
| 4 |
|
| 5 |
use FluentCart\App\App; |
| 6 |
use FluentCart\App\Models\Product; |
| 7 |
use FluentCart\App\Models\ProductVariation; |
| 8 |
|
| 9 |
class DefaultReportService extends ReportService |
| 10 |
{ |
| 11 |
public function fetchTopSoldProducts(array $params) |
| 12 |
{ |
| 13 |
$query = App::db()->table('fct_orders as o') |
| 14 |
->selectRaw(' |
| 15 |
oi.post_id AS product_id, |
| 16 |
SUM(oi.quantity) AS quantity_sold, |
| 17 |
SUM(oi.line_total) / 100 AS total_amount |
| 18 |
') |
| 19 |
->join('fct_order_items as oi', 'oi.order_id', '=', 'o.id') |
| 20 |
->where('oi.post_id', '>', 0) |
| 21 |
->groupBy('oi.post_id') |
| 22 |
->orderByDesc('quantity_sold'); |
| 23 |
|
| 24 |
unset($params['variationIds']); |
| 25 |
|
| 26 |
$query = $this->applyFilters($query, $params); |
| 27 |
|
| 28 |
$topSoldProducts = $query->limit(20)->get(); |
| 29 |
|
| 30 |
$productIds = $topSoldProducts->pluck('product_id')->all(); |
| 31 |
|
| 32 |
$productNames = $this->getProductNames($productIds); |
| 33 |
$productImages = $this->getProductImages($productIds); |
| 34 |
|
| 35 |
$topSoldProducts = $topSoldProducts->map(fn ($item) => [ |
| 36 |
'product_id' => (int) $item->product_id, |
| 37 |
'product_name' => $productNames[(int) $item->product_id] ?? __('Unknown Product', 'fluent-cart'), |
| 38 |
'quantity_sold' => (int) $item->quantity_sold, |
| 39 |
'total_amount' => round((float) $item->total_amount, 2), |
| 40 |
'media' => $productImages[(int) $item->product_id] ?? null, |
| 41 |
]); |
| 42 |
|
| 43 |
return [ |
| 44 |
'topSoldProducts' => $topSoldProducts, |
| 45 |
]; |
| 46 |
} |
| 47 |
|
| 48 |
public function fetchTopSoldVariants(array $params): array |
| 49 |
{ |
| 50 |
$query = App::db()->table('fct_orders as o') |
| 51 |
->selectRaw(' |
| 52 |
oi.object_id AS variation_id, |
| 53 |
SUM(oi.quantity) AS quantity_sold, |
| 54 |
SUM(oi.line_total) / 100 AS total_amount |
| 55 |
') |
| 56 |
->join('fct_order_items as oi', 'oi.order_id', '=', 'o.id') |
| 57 |
->where('oi.object_id', '>', 0) |
| 58 |
->groupBy('oi.object_id') |
| 59 |
->orderByDesc('quantity_sold'); |
| 60 |
|
| 61 |
unset($params['variationIds']); |
| 62 |
|
| 63 |
$query = $this->applyFilters($query, $params); |
| 64 |
|
| 65 |
$topSoldVariants = $query->limit(10)->get(); |
| 66 |
|
| 67 |
$variationIds = $topSoldVariants->pluck('variation_id')->all(); |
| 68 |
$variantMeta = $this->getVariantMeta($variationIds); |
| 69 |
|
| 70 |
$productIds = array_values(array_unique(array_column($variantMeta, 'post_id'))); |
| 71 |
$productNames = $this->getProductNames($productIds); |
| 72 |
|
| 73 |
$variationImages = $this->getVariationImages($variationIds); |
| 74 |
$productImages = $this->getProductImages($productIds); |
| 75 |
|
| 76 |
$topSoldVariants = $topSoldVariants->map(fn ($item) => [ |
| 77 |
'product_id' => (int) ($variantMeta[(int) $item->variation_id]['post_id'] ?? 0), |
| 78 |
'product_name' => $productNames[(int) ($variantMeta[(int) $item->variation_id]['post_id'] ?? 0)] ?? __('Unknown Product', 'fluent-cart'), |
| 79 |
'variation_id' => (int) $item->variation_id, |
| 80 |
'variation_name' => $variantMeta[(int) $item->variation_id]['title'] ?? __('Unknown Variant', 'fluent-cart'), |
| 81 |
'quantity' => (int) $item->quantity_sold, |
| 82 |
'total_amount' => round((float) $item->total_amount, 2), |
| 83 |
'media_url' => $variationImages[(int) $item->variation_id] |
| 84 |
?? $productImages[(int) ($variantMeta[(int) $item->variation_id]['post_id'] ?? 0)] |
| 85 |
?? null, |
| 86 |
]); |
| 87 |
|
| 88 |
return [ |
| 89 |
'topSoldVariants' => $topSoldVariants, |
| 90 |
]; |
| 91 |
} |
| 92 |
|
| 93 |
public function calculateFluctuations($currentMetrics, $previousMetrics) |
| 94 |
{ |
| 95 |
// Calculate fluctuations for each metric |
| 96 |
$metrics = [ |
| 97 |
'gross_sale' => $this->calculateFluctuation($currentMetrics['gross_sale'], $previousMetrics['gross_sale']), |
| 98 |
'net_revenue' => $this->calculateFluctuation($currentMetrics['net_revenue'], $previousMetrics['net_revenue']), |
| 99 |
// 'subscription_revenue' => $this->calculateFluctuation($currentMetrics['subscription_revenue'], $previousMetrics['subscription_revenue']), |
| 100 |
'order_count' => $this->calculateFluctuation($currentMetrics['order_count'], $previousMetrics['order_count']), |
| 101 |
// 'new_customers' => $this->calculateFluctuation($currentMetrics['new_customers'], $previousMetrics['new_customers']), |
| 102 |
'total_item_count' => $this->calculateFluctuation($currentMetrics['total_item_count'], $previousMetrics['total_item_count']), |
| 103 |
'total_refunded' => $this->calculateFluctuation($currentMetrics['total_refunded'], $previousMetrics['total_refunded']), |
| 104 |
'total_refunded_amount' => $this->calculateFluctuation($currentMetrics['total_refunded_amount'], $previousMetrics['total_refunded_amount']), |
| 105 |
'average_order_net' => $this->calculateFluctuation($currentMetrics['average_order_net'], $previousMetrics['average_order_net']), |
| 106 |
'average_order_items' => $this->calculateFluctuation($currentMetrics['average_order_items'], $previousMetrics['average_order_items']), |
| 107 |
'average_customer_orders' => $this->calculateFluctuation($currentMetrics['average_customer_orders'], $previousMetrics['average_customer_orders']), |
| 108 |
'average_customer_ltv' => $this->calculateFluctuation($currentMetrics['average_customer_ltv'], $previousMetrics['average_customer_ltv']), |
| 109 |
]; |
| 110 |
|
| 111 |
return $metrics; |
| 112 |
} |
| 113 |
|
| 114 |
private function calculateFluctuation($currentValue, $previousValue) |
| 115 |
{ |
| 116 |
if ($previousValue > 0) { |
| 117 |
return round((($currentValue - $previousValue) / $previousValue) * 100, 2); |
| 118 |
} |
| 119 |
|
| 120 |
return $currentValue > 0 ? 100 : 0; |
| 121 |
} |
| 122 |
|
| 123 |
private function getProductNames(array $productIds): array |
| 124 |
{ |
| 125 |
$productIds = array_values(array_unique(array_filter(array_map('intval', $productIds)))); |
| 126 |
|
| 127 |
if (!$productIds) { |
| 128 |
return []; |
| 129 |
} |
| 130 |
|
| 131 |
$latestIds = App::db()->table('fct_order_items') |
| 132 |
->selectRaw('MAX(id) AS id') |
| 133 |
->whereIn('post_id', $productIds) |
| 134 |
->groupBy('post_id'); |
| 135 |
|
| 136 |
return App::db()->table('fct_order_items') |
| 137 |
->select(['post_id', 'post_title']) |
| 138 |
->whereIn('id', $latestIds) |
| 139 |
->get() |
| 140 |
->reduce(function ($names, $item) { |
| 141 |
$names[(int) $item->post_id] = $item->post_title; |
| 142 |
return $names; |
| 143 |
}, []); |
| 144 |
} |
| 145 |
|
| 146 |
private function getProductImages(array $productIds): array |
| 147 |
{ |
| 148 |
$productIds = array_values(array_unique(array_filter(array_map('intval', $productIds)))); |
| 149 |
|
| 150 |
if (!$productIds) { |
| 151 |
return []; |
| 152 |
} |
| 153 |
|
| 154 |
return Product::query() |
| 155 |
->whereIn('ID', $productIds) |
| 156 |
->with(['detail.galleryImage']) |
| 157 |
->get() |
| 158 |
->reduce(function ($images, Product $product) { |
| 159 |
$images[(int) $product->ID] = $product->thumbnail ?: null; |
| 160 |
|
| 161 |
return $images; |
| 162 |
}, []); |
| 163 |
} |
| 164 |
|
| 165 |
private function getVariantMeta(array $variationIds): array |
| 166 |
{ |
| 167 |
$variationIds = array_values(array_unique(array_filter(array_map('intval', $variationIds)))); |
| 168 |
|
| 169 |
if (!$variationIds) { |
| 170 |
return []; |
| 171 |
} |
| 172 |
|
| 173 |
$latestIds = App::db()->table('fct_order_items') |
| 174 |
->selectRaw('MAX(id) AS id') |
| 175 |
->whereIn('object_id', $variationIds) |
| 176 |
->groupBy('object_id'); |
| 177 |
|
| 178 |
return App::db()->table('fct_order_items') |
| 179 |
->select(['object_id', 'title', 'post_id']) |
| 180 |
->whereIn('id', $latestIds) |
| 181 |
->get() |
| 182 |
->reduce(function ($meta, $item) { |
| 183 |
$meta[(int) $item->object_id] = [ |
| 184 |
'title' => $item->title, |
| 185 |
'post_id' => (int) $item->post_id, |
| 186 |
]; |
| 187 |
return $meta; |
| 188 |
}, []); |
| 189 |
} |
| 190 |
|
| 191 |
private function getVariationImages(array $variationIds): array |
| 192 |
{ |
| 193 |
$variationIds = array_values(array_unique(array_filter(array_map('intval', $variationIds)))); |
| 194 |
|
| 195 |
if (!$variationIds) { |
| 196 |
return []; |
| 197 |
} |
| 198 |
|
| 199 |
return ProductVariation::query() |
| 200 |
->whereIn('id', $variationIds) |
| 201 |
->with(['media']) |
| 202 |
->get() |
| 203 |
->reduce(function ($images, ProductVariation $variation) { |
| 204 |
$images[(int) $variation->id] = $variation->thumbnail ?: null; |
| 205 |
|
| 206 |
return $images; |
| 207 |
}, []); |
| 208 |
} |
| 209 |
|
| 210 |
public function getAllGraphMetricsSeparate($params = []) |
| 211 |
{ |
| 212 |
$group = ReportHelper::processGroup( |
| 213 |
$params['startDate'], $params['endDate'], $params['groupKey'] |
| 214 |
); |
| 215 |
|
| 216 |
$variationIds = array_map('intval', $params['variationIds'] ?? []); |
| 217 |
|
| 218 |
$itemsSub = App::db()->table('fct_order_items') |
| 219 |
->selectRaw('order_id, SUM(quantity) AS items_sold') |
| 220 |
->groupBy('order_id') |
| 221 |
->whereBetween('created_at', [$params['startDate'], $params['endDate']]) |
| 222 |
->when($variationIds, fn ($q) => $q->whereIn('object_id', $variationIds)); |
| 223 |
|
| 224 |
$orderMetricsQuery = App::db()->table('fct_orders as o') |
| 225 |
->joinSub($itemsSub, 'oi_sum', fn ($join) => $join->on('oi_sum.order_id', '=', 'o.id')); |
| 226 |
|
| 227 |
$orderMetricsQuery = $orderMetricsQuery->selectRaw("{$group['field']}, |
| 228 |
|
| 229 |
SUM(o.total_paid) / 100 AS gross_sale, |
| 230 |
|
| 231 |
SUM( |
| 232 |
o.total_paid |
| 233 |
- o.total_refund |
| 234 |
- o.tax_total |
| 235 |
- o.shipping_tax |
| 236 |
) / 100 as net_revenue, |
| 237 |
|
| 238 |
SUM(o.total_refund) / 100 AS refund_amount, |
| 239 |
|
| 240 |
COUNT(CASE WHEN o.total_refund > 0 THEN 1 END) AS refund_count, |
| 241 |
|
| 242 |
COUNT(o.id) as order_count, |
| 243 |
|
| 244 |
COUNT(CASE WHEN o.parent_id > 0 THEN 1 END) as subscription_renewals, |
| 245 |
|
| 246 |
COUNT(CASE WHEN o.type = 'payment' THEN 1 END) AS onetime_count, |
| 247 |
COUNT(CASE WHEN o.type = 'renewal' THEN 1 END) AS renewal_count, |
| 248 |
COUNT(CASE WHEN o.type = 'subscription' THEN 1 END) AS subscription_count, |
| 249 |
|
| 250 |
SUM(CASE WHEN o.type = 'payment' THEN o.total_paid ELSE 0 END) / 100 AS onetime_gross, |
| 251 |
SUM(CASE WHEN o.type = 'renewal' THEN o.total_paid ELSE 0 END) / 100 AS renewal_gross, |
| 252 |
SUM(CASE WHEN o.type = 'subscription' THEN o.total_paid ELSE 0 END) / 100 AS subscription_gross, |
| 253 |
|
| 254 |
SUM( |
| 255 |
CASE WHEN o.type = 'payment' |
| 256 |
THEN (o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) |
| 257 |
ELSE 0 END |
| 258 |
) / 100 AS onetime_net, |
| 259 |
SUM( |
| 260 |
CASE WHEN o.type = 'renewal' |
| 261 |
THEN (o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) |
| 262 |
ELSE 0 END |
| 263 |
) / 100 AS renewal_net, |
| 264 |
SUM( |
| 265 |
CASE WHEN o.type = 'subscription' |
| 266 |
THEN (o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) |
| 267 |
ELSE 0 END |
| 268 |
) / 100 AS subscription_net, |
| 269 |
|
| 270 |
SUM(COALESCE(oi_sum.items_sold, 0)) AS items_sold, |
| 271 |
|
| 272 |
SUM( |
| 273 |
CASE |
| 274 |
WHEN o.parent_id > 0 |
| 275 |
THEN o.total_paid - o.total_refund - o.tax_total - o.shipping_tax |
| 276 |
ELSE 0 |
| 277 |
END |
| 278 |
) / 100 as subscription_revenue") |
| 279 |
->groupByRaw($group['by']) |
| 280 |
->orderByRaw($group['by']); |
| 281 |
|
| 282 |
$orderMetricsQuery = $this->applyFilters($orderMetricsQuery, $params); |
| 283 |
|
| 284 |
$orderMetrics = $orderMetricsQuery->get(); |
| 285 |
|
| 286 |
return $this->combineMetricsResults($orderMetrics); |
| 287 |
} |
| 288 |
|
| 289 |
private function combineMetricsResults($orderMetrics): array |
| 290 |
{ |
| 291 |
$metrics = [ |
| 292 |
'orderGraph' => [], |
| 293 |
'grossSaleGraph' => [], |
| 294 |
'refundsGraph' => [], |
| 295 |
'refundCountGraph' => [], |
| 296 |
'netRevenueGraph' => [], |
| 297 |
'itemsSoldGraph' => [], |
| 298 |
'subscriptionRenewalGraph' => [], |
| 299 |
'subscriptionRevenueGraph' => [], |
| 300 |
]; |
| 301 |
|
| 302 |
$summary = [ |
| 303 |
'gross_sale' => 0, |
| 304 |
'net_revenue' => 0, |
| 305 |
'order_count' => 0, |
| 306 |
'subscription_renewal_count' => 0, |
| 307 |
'total_item_count' => 0, |
| 308 |
'total_refunded_amount' => 0, |
| 309 |
'total_refunded' => 0, |
| 310 |
'average_order_net' => 0, |
| 311 |
'average_order_items' => 0, |
| 312 |
'average_customer_orders' => 0, |
| 313 |
'average_customer_ltv' => 0, |
| 314 |
'onetime_count' => 0, |
| 315 |
'renewal_count' => 0, |
| 316 |
'subscription_count' => 0, |
| 317 |
'onetime_gross' => 0, |
| 318 |
'renewal_gross' => 0, |
| 319 |
'subscription_gross' => 0, |
| 320 |
'onetime_net' => 0, |
| 321 |
'renewal_net' => 0, |
| 322 |
'subscription_net' => 0, |
| 323 |
]; |
| 324 |
|
| 325 |
// Process order metrics |
| 326 |
foreach ($orderMetrics as $row) { |
| 327 |
$period = $row->group; |
| 328 |
$metrics['grossSaleGraph'][$period] = (float) $row->gross_sale; |
| 329 |
$metrics['netRevenueGraph'][$period] = (float) $row->net_revenue; |
| 330 |
$metrics['orderGraph'][$period] = (int) $row->order_count; |
| 331 |
$metrics['subscriptionRenewalGraph'][$period] = (int) $row->subscription_renewals; |
| 332 |
$metrics['subscriptionRevenueGraph'][$period] = (float) $row->subscription_revenue; |
| 333 |
$metrics['refundsGraph'][$period] = (float) $row->refund_amount; |
| 334 |
$metrics['refundCountGraph'][$period] = (int) $row->refund_count; |
| 335 |
$metrics['itemsSoldGraph'][$row->group] = (int) $row->items_sold; |
| 336 |
|
| 337 |
$summary['gross_sale'] += (float) $row->gross_sale; |
| 338 |
$summary['net_revenue'] += (float) $row->net_revenue; |
| 339 |
$summary['order_count'] += (int) $row->order_count; |
| 340 |
$summary['total_refunded_amount'] += (float) $row->refund_amount; |
| 341 |
$summary['total_refunded'] += (int) $row->refund_count; |
| 342 |
$summary['total_item_count'] += (int) $row->items_sold; |
| 343 |
|
| 344 |
$summary['onetime_count'] += (int) $row->onetime_count; |
| 345 |
$summary['renewal_count'] += (int) $row->renewal_count; |
| 346 |
$summary['subscription_count'] += (int) $row->subscription_count; |
| 347 |
|
| 348 |
$summary['onetime_gross'] += (float) $row->onetime_gross; |
| 349 |
$summary['renewal_gross'] += (float) $row->renewal_gross; |
| 350 |
$summary['subscription_gross'] += (float) $row->subscription_gross; |
| 351 |
|
| 352 |
$summary['onetime_net'] += (float) $row->onetime_net; |
| 353 |
$summary['renewal_net'] += (float) $row->renewal_net; |
| 354 |
$summary['subscription_net'] += (float) $row->subscription_net; |
| 355 |
} |
| 356 |
|
| 357 |
return [ |
| 358 |
'metrics' => $metrics, |
| 359 |
'summary' => $summary, |
| 360 |
]; |
| 361 |
} |
| 362 |
} |
| 363 |
|
