| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Services\Report; |
| 4 |
|
| 5 |
use FluentCart\App\App; |
| 6 |
use FluentCart\Framework\Database\Query\Builder as Query; |
| 7 |
|
| 8 |
class SourceReportService extends ReportService |
| 9 |
{ |
| 10 |
public function getSourceReportData($params = []) |
| 11 |
{ |
| 12 |
$query = App::db()->table('fct_orders as o') |
| 13 |
->selectRaw("oo.utm_campaign, |
| 14 |
oo.utm_source, |
| 15 |
oo.utm_medium, |
| 16 |
oo.utm_term, |
| 17 |
oo.utm_content, |
| 18 |
oo.utm_id, |
| 19 |
CONCAT(COALESCE(oo.utm_campaign, ''), '|', COALESCE(oo.utm_source, ''), '|', COALESCE(oo.utm_medium, '')) as utm_key, |
| 20 |
COUNT(o.id) as orders, |
| 21 |
SUM(o.total_paid) / 100 as gross_sales, |
| 22 |
SUM(o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) / 100 as net_sales, |
| 23 |
AVG(o.total_paid) / 100 as average_order, |
| 24 |
AVG(o.total_paid - o.total_refund - o.tax_total - o.shipping_tax) / 100 as average_net_order") |
| 25 |
->leftJoin('fct_order_operations as oo', 'o.id', '=', 'oo.order_id') |
| 26 |
->whereNotNull('oo.utm_source') |
| 27 |
->where('oo.utm_source', '!=', '') |
| 28 |
->groupBy([ |
| 29 |
'oo.utm_campaign', |
| 30 |
'oo.utm_source', |
| 31 |
'oo.utm_medium', |
| 32 |
]) |
| 33 |
->orderByRaw('gross_sales DESC'); |
| 34 |
|
| 35 |
$query = $this->applyFilters($query, $params); |
| 36 |
|
| 37 |
/** |
| 38 |
* Let listeners narrow the Order Sources report query. |
| 39 |
* |
| 40 |
* The free plugin only forwards the request params ('filter_type' and the |
| 41 |
* raw 'advanced_filters' JSON among them) — it never parses or applies the |
| 42 |
* advanced filters itself. FluentCart Pro owns that: it reads the params, |
| 43 |
* builds the condition groups through the Orders filter engine and adds |
| 44 |
* the constraints to this query. |
| 45 |
* |
| 46 |
* @param \FluentCart\Framework\Database\Query\Builder $query |
| 47 |
* @param array $params Processed report params. |
| 48 |
*/ |
| 49 |
$filteredQuery = apply_filters('fluent_cart/report/sources_query', $query, $params); |
| 50 |
|
| 51 |
// A callback that returns something other than a query builder (null, |
| 52 |
// an array, a thrown-away clone of the wrong type) would fatal the whole |
| 53 |
// report, so fall back to the unmodified query instead. |
| 54 |
if ($filteredQuery instanceof Query) { |
| 55 |
$query = $filteredQuery; |
| 56 |
} |
| 57 |
|
| 58 |
return $query->get(); |
| 59 |
} |
| 60 |
|
| 61 |
public function calculateFluctuations($currentData, $comparisonData) |
| 62 |
{ |
| 63 |
// Create lookup array for comparison data |
| 64 |
$comparisonLookup = []; |
| 65 |
foreach ($comparisonData as $item) { |
| 66 |
$comparisonLookup[$item->utm_key] = $item; |
| 67 |
} |
| 68 |
|
| 69 |
// Create fluctuations lookup array |
| 70 |
$fluctuations = []; |
| 71 |
|
| 72 |
foreach ($currentData as $currentItem) { |
| 73 |
$key = $currentItem->utm_key; |
| 74 |
$comparisonItem = $comparisonLookup[$key] ?? null; |
| 75 |
|
| 76 |
if ($comparisonItem) { |
| 77 |
$fluctuations[$key] = [ |
| 78 |
'previous_orders' => (float) $comparisonItem->orders, |
| 79 |
'previous_gross_sales' => (float) $comparisonItem->gross_sales, |
| 80 |
'previous_net_sales' => (float) $comparisonItem->net_sales, |
| 81 |
'previous_average_order' => (float) $comparisonItem->average_order, |
| 82 |
'previous_average_net_order' => (float) $comparisonItem->average_net_order, |
| 83 |
'orders_fluctuation' => $this->calculatePercentageChange( |
| 84 |
$comparisonItem->orders, |
| 85 |
$currentItem->orders |
| 86 |
), |
| 87 |
'gross_sales_fluctuation' => $this->calculatePercentageChange( |
| 88 |
$comparisonItem->gross_sales, |
| 89 |
$currentItem->gross_sales |
| 90 |
), |
| 91 |
'net_sales_fluctuation' => $this->calculatePercentageChange( |
| 92 |
$comparisonItem->net_sales, |
| 93 |
$currentItem->net_sales |
| 94 |
), |
| 95 |
'average_order_fluctuation' => $this->calculatePercentageChange( |
| 96 |
$comparisonItem->average_order, |
| 97 |
$currentItem->average_order |
| 98 |
), |
| 99 |
'average_net_order_fluctuation' => $this->calculatePercentageChange( |
| 100 |
$comparisonItem->average_net_order, |
| 101 |
$currentItem->average_net_order |
| 102 |
), |
| 103 |
]; |
| 104 |
} else { |
| 105 |
// New UTM combination - no comparison data |
| 106 |
$fluctuations[$key] = [ |
| 107 |
'previous_orders' => 0, |
| 108 |
'previous_gross_sales' => 0, |
| 109 |
'previous_net_sales' => 0, |
| 110 |
'previous_average_order' => 0, |
| 111 |
'previous_average_net_order' => 0, |
| 112 |
'orders_fluctuation' => 100, |
| 113 |
'gross_sales_fluctuation' => 100, |
| 114 |
'net_sales_fluctuation' => 100, |
| 115 |
'average_order_fluctuation' => 100, |
| 116 |
'average_net_order_fluctuation' => 100, |
| 117 |
]; |
| 118 |
} |
| 119 |
} |
| 120 |
|
| 121 |
return $fluctuations; |
| 122 |
} |
| 123 |
|
| 124 |
protected function calculatePercentageChange($oldValue, $newValue) |
| 125 |
{ |
| 126 |
if ($oldValue == 0) { |
| 127 |
return $newValue > 0 ? 100 : 0; |
| 128 |
} |
| 129 |
|
| 130 |
return round((($newValue - $oldValue) / $oldValue) * 100, 2); |
| 131 |
} |
| 132 |
} |
| 133 |
|
