| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Services\Report; |
| 4 |
|
| 5 |
use FluentCart\App\App; |
| 6 |
|
| 7 |
class SourceReportService extends ReportService |
| 8 |
{ |
| 9 |
public function getSourceReportData($params = []) |
| 10 |
{ |
| 11 |
$query = App::db()->table('fct_orders as o') |
| 12 |
->selectRaw("oo.utm_campaign, |
| 13 |
oo.utm_source, |
| 14 |
oo.utm_medium, |
| 15 |
oo.utm_term, |
| 16 |
oo.utm_content, |
| 17 |
oo.utm_id, |
| 18 |
CONCAT(COALESCE(oo.utm_campaign, ''), '|', COALESCE(oo.utm_source, ''), '|', COALESCE(oo.utm_medium, '')) as utm_key, |
| 19 |
COUNT(o.id) as orders, |
| 20 |
SUM(o.total_paid) / 100 as gross_sales, |
| 21 |
SUM(o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) / 100 as net_sales, |
| 22 |
AVG(o.total_paid) / 100 as average_order, |
| 23 |
AVG(o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) / 100 as average_net_order") |
| 24 |
->leftJoin('fct_order_operations as oo', 'o.id', '=', 'oo.order_id') |
| 25 |
->whereNotNull('oo.utm_source') |
| 26 |
->where('oo.utm_source', '!=', '') |
| 27 |
->groupBy([ |
| 28 |
'oo.utm_campaign', |
| 29 |
'oo.utm_source', |
| 30 |
'oo.utm_medium', |
| 31 |
]) |
| 32 |
->orderByRaw('gross_sales DESC'); |
| 33 |
|
| 34 |
$query = $this->applyFilters($query, $params); |
| 35 |
|
| 36 |
return $query->get(); |
| 37 |
} |
| 38 |
|
| 39 |
public function calculateFluctuations($currentData, $comparisonData) |
| 40 |
{ |
| 41 |
// Create lookup array for comparison data |
| 42 |
$comparisonLookup = []; |
| 43 |
foreach ($comparisonData as $item) { |
| 44 |
$comparisonLookup[$item->utm_key] = $item; |
| 45 |
} |
| 46 |
|
| 47 |
// Create fluctuations lookup array |
| 48 |
$fluctuations = []; |
| 49 |
|
| 50 |
foreach ($currentData as $currentItem) { |
| 51 |
$key = $currentItem->utm_key; |
| 52 |
$comparisonItem = $comparisonLookup[$key] ?? null; |
| 53 |
|
| 54 |
if ($comparisonItem) { |
| 55 |
$fluctuations[$key] = [ |
| 56 |
'previous_orders' => (float) $comparisonItem->orders, |
| 57 |
'previous_gross_sales' => (float) $comparisonItem->gross_sales, |
| 58 |
'previous_net_sales' => (float) $comparisonItem->net_sales, |
| 59 |
'previous_average_order' => (float) $comparisonItem->average_order, |
| 60 |
'previous_average_net_order' => (float) $comparisonItem->average_net_order, |
| 61 |
'orders_fluctuation' => $this->calculatePercentageChange( |
| 62 |
$comparisonItem->orders, |
| 63 |
$currentItem->orders |
| 64 |
), |
| 65 |
'gross_sales_fluctuation' => $this->calculatePercentageChange( |
| 66 |
$comparisonItem->gross_sales, |
| 67 |
$currentItem->gross_sales |
| 68 |
), |
| 69 |
'net_sales_fluctuation' => $this->calculatePercentageChange( |
| 70 |
$comparisonItem->net_sales, |
| 71 |
$currentItem->net_sales |
| 72 |
), |
| 73 |
'average_order_fluctuation' => $this->calculatePercentageChange( |
| 74 |
$comparisonItem->average_order, |
| 75 |
$currentItem->average_order |
| 76 |
), |
| 77 |
'average_net_order_fluctuation' => $this->calculatePercentageChange( |
| 78 |
$comparisonItem->average_net_order, |
| 79 |
$currentItem->average_net_order |
| 80 |
), |
| 81 |
]; |
| 82 |
} else { |
| 83 |
// New UTM combination - no comparison data |
| 84 |
$fluctuations[$key] = [ |
| 85 |
'previous_orders' => 0, |
| 86 |
'previous_gross_sales' => 0, |
| 87 |
'previous_net_sales' => 0, |
| 88 |
'previous_average_order' => 0, |
| 89 |
'previous_average_net_order' => 0, |
| 90 |
'orders_fluctuation' => 100, |
| 91 |
'gross_sales_fluctuation' => 100, |
| 92 |
'net_sales_fluctuation' => 100, |
| 93 |
'average_order_fluctuation' => 100, |
| 94 |
'average_net_order_fluctuation' => 100, |
| 95 |
]; |
| 96 |
} |
| 97 |
} |
| 98 |
|
| 99 |
return $fluctuations; |
| 100 |
} |
| 101 |
|
| 102 |
protected function calculatePercentageChange($oldValue, $newValue) |
| 103 |
{ |
| 104 |
if ($oldValue == 0) { |
| 105 |
return $newValue > 0 ? 100 : 0; |
| 106 |
} |
| 107 |
|
| 108 |
return round((($newValue - $oldValue) / $oldValue) * 100, 2); |
| 109 |
} |
| 110 |
} |
| 111 |
|
