Viewing file:  ReportsController.php (15.81 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Campaign;
use App\Models\CampaignPerformance;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
class ReportsController extends Controller
{
/**
* Get KPIs with date filtering
*/
public function getKpis(Request $request)
{
$user = $request->user();
$dateRange = $request->input('date_range', '7d');
$campaignId = $request->input('campaign_id');
$dates = $this->parseDateRange($dateRange);
$previousDates = $this->getPreviousPeriod($dates);
// Build base query
$query = CampaignPerformance::query()
->join('campaigns', 'campaign_performances.campaign_id', '=', 'campaigns.id')
->where('campaigns.user_id', $user->id);
if ($campaignId && $campaignId !== 'all') {
$query->where('campaign_performances.campaign_id', $campaignId);
}
// Current period metrics
$currentMetrics = (clone $query)
->whereBetween('campaign_performances.date', [$dates['start'], $dates['end']])
->select([
DB::raw('SUM(campaign_performances.spend) as total_spend'),
DB::raw('SUM(campaign_performances.impressions) as total_impressions'),
DB::raw('SUM(campaign_performances.clicks) as total_clicks'),
DB::raw('AVG(campaign_performances.roi) as avg_roi'),
])
->first();
// Previous period metrics
$previousMetrics = (clone $query)
->whereBetween('campaign_performances.date', [$previousDates['start'], $previousDates['end']])
->select([
DB::raw('SUM(campaign_performances.spend) as total_spend'),
DB::raw('SUM(campaign_performances.impressions) as total_impressions'),
DB::raw('SUM(campaign_performances.clicks) as total_clicks'),
DB::raw('AVG(campaign_performances.roi) as avg_roi'),
])
->first();
return response()->json([
'total_spend' => [
'value' => '$' . number_format($currentMetrics->total_spend ?? 0, 2),
'change' => $this->calculateChange($currentMetrics->total_spend ?? 0, $previousMetrics->total_spend ?? 0),
'change_type' => $this->getChangeType($currentMetrics->total_spend ?? 0, $previousMetrics->total_spend ?? 0)
],
'total_impressions' => [
'value' => $this->formatNumber($currentMetrics->total_impressions ?? 0),
'change' => $this->calculateChange($currentMetrics->total_impressions ?? 0, $previousMetrics->total_impressions ?? 0),
'change_type' => $this->getChangeType($currentMetrics->total_impressions ?? 0, $previousMetrics->total_impressions ?? 0)
],
'total_clicks' => [
'value' => number_format($currentMetrics->total_clicks ?? 0),
'change' => $this->calculateChange($currentMetrics->total_clicks ?? 0, $previousMetrics->total_clicks ?? 0),
'change_type' => $this->getChangeType($currentMetrics->total_clicks ?? 0, $previousMetrics->total_clicks ?? 0)
],
'avg_roi' => [
'value' => number_format($currentMetrics->avg_roi ?? 0, 1) . 'x',
'change' => $this->calculateChange($currentMetrics->avg_roi ?? 0, $previousMetrics->avg_roi ?? 0),
'change_type' => $this->getChangeType($currentMetrics->avg_roi ?? 0, $previousMetrics->avg_roi ?? 0)
]
]);
}
/**
* Get campaign performance with filtering
*/
public function getCampaignPerformance(Request $request)
{
$user = $request->user();
$dateRange = $request->input('date_range', '7d');
$campaignId = $request->input('campaign_id');
$dates = $this->parseDateRange($dateRange);
$query = CampaignPerformance::query()
->join('campaigns', 'campaign_performances.campaign_id', '=', 'campaigns.id')
->where('campaigns.user_id', $user->id)
->whereBetween('campaign_performances.date', [$dates['start'], $dates['end']]);
if ($campaignId && $campaignId !== 'all') {
$query->where('campaign_performances.campaign_id', $campaignId);
}
$performance = $query
->select([
'campaigns.name as campaign',
DB::raw('SUM(campaign_performances.spend) as spend'),
DB::raw('SUM(campaign_performances.impressions) as impressions'),
DB::raw('SUM(campaign_performances.clicks) as clicks'),
DB::raw('SUM(campaign_performances.conversions) as conversions'),
DB::raw('AVG(campaign_performances.ctr) as ctr'),
DB::raw('AVG(campaign_performances.roi) as roi'),
])
->groupBy('campaigns.id', 'campaigns.name')
->orderBy('spend', 'desc')
->get()
->map(function ($item) {
return [
'campaign' => $item->campaign,
'spend' => '$' . number_format($item->spend, 2),
'impressions' => $this->formatNumber($item->impressions),
'clicks' => number_format($item->clicks),
'ctr' => number_format($item->ctr, 2) . '%',
'conversions' => number_format($item->conversions),
'roi' => number_format($item->roi, 1) . 'x',
];
});
return response()->json($performance);
}
/**
* Get geographic breakdown
*/
public function getGeoBreakdown(Request $request)
{
$user = $request->user();
$dateRange = $request->input('date_range', '7d');
$campaignId = $request->input('campaign_id');
$dates = $this->parseDateRange($dateRange);
$query = CampaignPerformance::query()
->join('campaigns', 'campaign_performances.campaign_id', '=', 'campaigns.id')
->where('campaigns.user_id', $user->id)
->whereBetween('campaign_performances.date', [$dates['start'], $dates['end']])
->whereNotNull('campaign_performances.country');
if ($campaignId && $campaignId !== 'all') {
$query->where('campaign_performances.campaign_id', $campaignId);
}
$geoData = $query
->select([
'campaign_performances.country',
DB::raw('SUM(campaign_performances.spend) as spend'),
DB::raw('SUM(campaign_performances.impressions) as impressions'),
DB::raw('SUM(campaign_performances.clicks) as clicks'),
DB::raw('SUM(campaign_performances.conversions) as conversions'),
])
->groupBy('campaign_performances.country')
->orderBy('spend', 'desc')
->get()
->map(function ($item) {
return [
'country' => $item->country,
'spend' => '$' . number_format($item->spend, 2),
'impressions' => $this->formatNumber($item->impressions),
'clicks' => number_format($item->clicks),
'conversions' => number_format($item->conversions),
];
});
return response()->json($geoData);
}
/**
* Get device breakdown
*/
public function getDeviceBreakdown(Request $request)
{
$user = $request->user();
$dateRange = $request->input('date_range', '7d');
$campaignId = $request->input('campaign_id');
$dates = $this->parseDateRange($dateRange);
$query = CampaignPerformance::query()
->join('campaigns', 'campaign_performances.campaign_id', '=', 'campaigns.id')
->where('campaigns.user_id', $user->id)
->whereBetween('campaign_performances.date', [$dates['start'], $dates['end']])
->whereNotNull('campaign_performances.device_type');
if ($campaignId && $campaignId !== 'all') {
$query->where('campaign_performances.campaign_id', $campaignId);
}
$deviceData = $query
->select([
'campaign_performances.device_type as device',
DB::raw('SUM(campaign_performances.spend) as spend'),
DB::raw('SUM(campaign_performances.conversions) as conversions'),
])
->groupBy('campaign_performances.device_type')
->get();
$totalSpend = $deviceData->sum('spend');
$deviceData = $deviceData->map(function ($item) use ($totalSpend) {
$percentage = $totalSpend > 0 ? ($item->spend / $totalSpend) * 100 : 0;
return [
'device' => $item->device,
'spend' => '$' . number_format($item->spend, 2),
'conversions' => number_format($item->conversions),
'percentage' => number_format($percentage, 0) . '%',
];
});
return response()->json($deviceData);
}
/**
* Export report as CSV
*/
public function exportReport(Request $request)
{
$user = $request->user();
$dateRange = $request->input('date_range', '7d');
$campaignId = $request->input('campaign_id');
$reportType = $request->input('report_type', 'campaign'); // campaign, geo, device
$dates = $this->parseDateRange($dateRange);
$filename = "report_{$reportType}_{$dates['start']}_{$dates['end']}.csv";
$query = CampaignPerformance::query()
->join('campaigns', 'campaign_performances.campaign_id', '=', 'campaigns.id')
->where('campaigns.user_id', $user->id)
->whereBetween('campaign_performances.date', [$dates['start'], $dates['end']]);
if ($campaignId && $campaignId !== 'all') {
$query->where('campaign_performances.campaign_id', $campaignId);
}
$headers = [
'Content-Type' => 'text/csv',
'Content-Disposition' => "attachment; filename=\"{$filename}\"",
];
$callback = function() use ($query, $reportType) {
$file = fopen('php://output', 'w');
switch ($reportType) {
case 'geo':
fputcsv($file, ['Country', 'Spend', 'Impressions', 'Clicks', 'Conversions']);
$data = $query
->whereNotNull('campaign_performances.country')
->select([
'campaign_performances.country',
DB::raw('SUM(campaign_performances.spend) as spend'),
DB::raw('SUM(campaign_performances.impressions) as impressions'),
DB::raw('SUM(campaign_performances.clicks) as clicks'),
DB::raw('SUM(campaign_performances.conversions) as conversions'),
])
->groupBy('campaign_performances.country')
->get();
foreach ($data as $row) {
fputcsv($file, [
$row->country,
$row->spend,
$row->impressions,
$row->clicks,
$row->conversions,
]);
}
break;
case 'device':
fputcsv($file, ['Device', 'Spend', 'Conversions']);
$data = $query
->whereNotNull('campaign_performances.device_type')
->select([
'campaign_performances.device_type',
DB::raw('SUM(campaign_performances.spend) as spend'),
DB::raw('SUM(campaign_performances.conversions) as conversions'),
])
->groupBy('campaign_performances.device_type')
->get();
foreach ($data as $row) {
fputcsv($file, [
$row->device_type,
$row->spend,
$row->conversions,
]);
}
break;
default: // campaign
fputcsv($file, ['Campaign', 'Spend', 'Impressions', 'Clicks', 'CTR', 'Conversions', 'ROI']);
$data = $query
->select([
'campaigns.name',
DB::raw('SUM(campaign_performances.spend) as spend'),
DB::raw('SUM(campaign_performances.impressions) as impressions'),
DB::raw('SUM(campaign_performances.clicks) as clicks'),
DB::raw('AVG(campaign_performances.ctr) as ctr'),
DB::raw('SUM(campaign_performances.conversions) as conversions'),
DB::raw('AVG(campaign_performances.roi) as roi'),
])
->groupBy('campaigns.id', 'campaigns.name')
->get();
foreach ($data as $row) {
fputcsv($file, [
$row->name,
$row->spend,
$row->impressions,
$row->clicks,
$row->ctr,
$row->conversions,
$row->roi,
]);
}
}
fclose($file);
};
return response()->stream($callback, 200, $headers);
}
/**
* Parse date range string to start and end dates
*/
protected function parseDateRange($dateRange)
{
$end = Carbon::now();
switch ($dateRange) {
case '24h':
$start = Carbon::now()->subDay();
break;
case '7d':
$start = Carbon::now()->subDays(7);
break;
case '30d':
$start = Carbon::now()->subDays(30);
break;
case '90d':
$start = Carbon::now()->subDays(90);
break;
default:
$start = Carbon::now()->subDays(7);
}
return [
'start' => $start->toDateString(),
'end' => $end->toDateString(),
];
}
/**
* Get previous period dates for comparison
*/
protected function getPreviousPeriod($dates)
{
$start = Carbon::parse($dates['start']);
$end = Carbon::parse($dates['end']);
$diff = $start->diffInDays($end);
return [
'start' => $start->copy()->subDays($diff + 1)->toDateString(),
'end' => $start->copy()->subDay()->toDateString(),
];
}
/**
* Calculate percentage change
*/
protected function calculateChange($current, $previous)
{
if ($previous == 0) {
return $current > 0 ? '+100%' : '0%';
}
$change = (($current - $previous) / $previous) * 100;
$sign = $change >= 0 ? '+' : '';
return $sign . number_format($change, 1) . '% vs last period';
}
/**
* Get change type (positive/negative)
*/
protected function getChangeType($current, $previous)
{
return $current >= $previous ? 'positive' : 'negative';
}
/**
* Format large numbers (e.g., 1000 -> 1K)
*/
protected function formatNumber($number)
{
if ($number >= 1000000) {
return number_format($number / 1000000, 1) . 'M';
} elseif ($number >= 1000) {
return number_format($number / 1000, 1) . 'K';
}
return number_format($number);
}
}
|