<?php
 namespace App\Reports\Retention; use App\Reports\BaseReport; use Carbon\Carbon; use Illuminate\Support\Facades\DB; class Retention extends BaseReport { public function getName(): string { return __('admin/reports.retention_name'); } public function getDescription(): string { return __('admin/reports.retention_description'); } public function getCategory(): string { return __('admin/reports.retention_category'); } public function getIcon(): string { return 'fas fa-chart-line'; } public function getRouteName(): string { return 'retention'; } public function getRequiredPermission(): string { return 'generate reports'; } public function getData(array $filters = []): array { $filters = $this->validateFilters($filters); $startDate = $filters['start_date'] ?? now()->subYear(); $endDate = $filters['end_date'] ?? now(); $cohortSize = $filters['cohort_size'] ?? 30; $cohorts = DB::table('clients') ->whereNull('deleted_at') ->whereBetween('created_at', [$startDate, $endDate]) ->selectRaw('DATE(created_at) as cohort_date, COUNT(*) as cohort_size') ->groupBy('cohort_date') ->orderBy('cohort_date') ->get(); $retentionData = []; foreach ($cohorts as $cohort) { $cohortDate = $cohort->cohort_date; $retention = []; for ($i = 1; $i <= 12; $i++) { $checkDate = Carbon::parse($cohortDate)->addMonths($i); $activeUsers = DB::table('clients') ->whereNull('deleted_at') ->where('created_at', '<=', $cohortDate) ->whereExists(function ($query) use ($checkDate) { $query->select(DB::raw(1)) ->from('client_products') ->whereNull('client_products.deleted_at') ->whereColumn('client_products.client_id', 'clients.id') ->where('client_products.status', 'active') ->where('client_products.created_at', '<=', $checkDate); }) ->count(); $retention[$i] = $cohort->cohort_size > 0 ? round(($activeUsers / $cohort->cohort_size) * 100, 2) : 0; } $retentionData[] = [ 'cohort_date' => $cohortDate, 'cohort_size' => $cohort->cohort_size, 'retention_1m' => $retention[1], 'retention_3m' => $retention[3], 'retention_6m' => $retention[6], 'retention_12m' => $retention[12], ]; } return $retentionData; } public function getColumns(): array { return [ 'cohort_date' => [ 'title' => __('admin/reports.retention_column_cohort_date'), 'type' => 'string', 'sortable' => true, ], 'cohort_size' => [ 'title' => __('admin/reports.retention_column_cohort_size'), 'type' => 'number', 'sortable' => true, ], 'retention_1m' => [ 'title' => __('admin/reports.retention_column_retention_1m'), 'type' => 'number', 'sortable' => true, ], 'retention_3m' => [ 'title' => __('admin/reports.retention_column_retention_3m'), 'type' => 'number', 'sortable' => true, ], 'retention_6m' => [ 'title' => __('admin/reports.retention_column_retention_6m'), 'type' => 'number', 'sortable' => true, ], 'retention_12m' => [ 'title' => __('admin/reports.retention_column_retention_12m'), 'type' => 'number', 'sortable' => true, ], ]; } public function getFilters(): array { return [ 'start_date' => [ 'type' => 'date', 'label' => __('admin/reports.retention_filter_start_date'), 'required' => false, 'default' => now()->subYear()->format('Y-m-d'), ], 'end_date' => [ 'type' => 'date', 'label' => __('admin/reports.retention_filter_end_date'), 'required' => false, 'default' => now()->format('Y-m-d'), ], 'period' => [ 'type' => 'select', 'label' => __('admin/reports.retention_filter_period'), 'required' => false, 'options' => [ 'daily' => __('admin/reports.retention_period_daily'), 'weekly' => __('admin/reports.retention_period_weekly'), 'monthly' => __('admin/reports.retention_period_monthly'), 'yearly' => __('admin/reports.retention_period_yearly'), ], 'default' => 'monthly', ], 'cohort_size' => [ 'type' => 'number', 'label' => __('admin/reports.retention_filter_cohort_size'), 'required' => false, 'default' => 30, 'min' => 1, 'max' => 365, ], ]; } public function hasChart(): bool { return true; } public function getChartData(array $filters = []): ?array { $data = $this->getData($filters); return [ 'labels' => array_column($data, 'cohort_date'), 'datasets' => [ [ 'label' => '1 Ay Retention', 'data' => array_column($data, 'retention_1m'), 'backgroundColor' => 'rgba(255, 99, 132, 0.2)', 'borderColor' => 'rgba(255, 99, 132, 1)', 'borderWidth' => 2, ], [ 'label' => '3 Ay Retention', 'data' => array_column($data, 'retention_3m'), 'backgroundColor' => 'rgba(54, 162, 235, 0.2)', 'borderColor' => 'rgba(54, 162, 235, 1)', 'borderWidth' => 2, ], [ 'label' => '6 Ay Retention', 'data' => array_column($data, 'retention_6m'), 'backgroundColor' => 'rgba(255, 205, 86, 0.2)', 'borderColor' => 'rgba(255, 205, 86, 1)', 'borderWidth' => 2, ], [ 'label' => '12 Ay Retention', 'data' => array_column($data, 'retention_12m'), 'backgroundColor' => 'rgba(75, 192, 192, 0.2)', 'borderColor' => 'rgba(75, 192, 192, 1)', 'borderWidth' => 2, ], ], ]; } public function getChartType(): ?string { return 'line'; } } 