<?php
 namespace App\Reports\ClientSources; use App\Reports\BaseReport; use Illuminate\Support\Facades\DB; class ClientSources extends BaseReport { public function getName(): string { return __('admin/reports.client_sources_name'); } public function getDescription(): string { return __('admin/reports.client_sources_description'); } public function getCategory(): string { return __('admin/reports.client_sources_category'); } public function getIcon(): string { return 'fas fa-funnel-dollar'; } public function getRouteName(): string { return 'client-sources'; } 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(); $period = $filters['period'] ?? 'monthly'; $country = $filters['country'] ?? 'all'; $minRegistrations = $filters['min_registrations'] ?? 0; $query = DB::table('clients') ->whereNull('clients.deleted_at') ->whereBetween('clients.created_at', [$startDate, $endDate]) ->whereNotNull('clients.social_provider') ->where('clients.social_provider', '!=', ''); if ($country !== 'all') { $query->join('countries', 'clients.country_id', '=', 'countries.id') ->where('countries.a2_iso', $country); } switch ($period) { case 'daily': $query->selectRaw('
                    DATE(clients.created_at) as period,
                    clients.social_provider as source,
                    COUNT(*) as registrations,
                    COUNT(CASE WHEN clients.status = "active" THEN 1 END) as active_users,
                    COUNT(CASE WHEN clients.status = "inactive" THEN 1 END) as inactive_users
                ') ->groupByRaw('DATE(clients.created_at), clients.social_provider') ->orderBy('period') ->orderBy('registrations', 'desc'); break; case 'weekly': $query->selectRaw('
                    YEARWEEK(clients.created_at) as period,
                    clients.social_provider as source,
                    COUNT(*) as registrations,
                    COUNT(CASE WHEN clients.status = "active" THEN 1 END) as active_users,
                    COUNT(CASE WHEN clients.status = "inactive" THEN 1 END) as inactive_users
                ') ->groupByRaw('YEARWEEK(clients.created_at), clients.social_provider') ->orderBy('period') ->orderBy('registrations', 'desc'); break; case 'monthly': default: $query->selectRaw('
                    DATE_FORMAT(clients.created_at, "%Y-%m") as period,
                    clients.social_provider as source,
                    COUNT(*) as registrations,
                    COUNT(CASE WHEN clients.status = "active" THEN 1 END) as active_users,
                    COUNT(CASE WHEN clients.status = "active" THEN 1 END) / COUNT(*) * 100 as conversion_rate
                ') ->groupByRaw('DATE_FORMAT(clients.created_at, "%Y-%m"), clients.social_provider') ->orderBy('period') ->orderBy('registrations', 'desc'); break; } $data = $query->get(); if ($minRegistrations > 0) { $data = $data->filter(function ($item) use ($minRegistrations) { return $item->registrations >= $minRegistrations; }); } $totalRegistrations = $data->sum('registrations'); foreach ($data as $item) { $item->percentage = $totalRegistrations > 0 ? round(($item->registrations / $totalRegistrations) * 100, 2) : 0; } return $data->toArray(); } public function getColumns(): array { return [ 'period' => [ 'title' => __('admin/reports.client_sources_column_period'), 'type' => 'string', 'sortable' => true, ], 'source' => [ 'title' => __('admin/reports.client_sources_column_source'), 'type' => 'string', 'sortable' => true, ], 'registrations' => [ 'title' => __('admin/reports.client_sources_column_registrations'), 'type' => 'number', 'sortable' => true, ], 'percentage' => [ 'title' => __('admin/reports.client_sources_column_percentage'), 'type' => 'percentage', 'sortable' => true, ], 'active_users' => [ 'title' => __('admin/reports.client_sources_column_active_users'), 'type' => 'number', 'sortable' => true, ], 'inactive_users' => [ 'title' => __('admin/reports.client_sources_column_inactive_users'), 'type' => 'number', 'sortable' => true, ], 'conversion_rate' => [ 'title' => __('admin/reports.client_sources_column_conversion_rate'), 'type' => 'percentage', 'sortable' => true, ], ]; } public function getFilters(): array { return [ 'start_date' => [ 'type' => 'date', 'label' => __('admin/reports.client_sources_filter_start_date'), 'required' => false, 'default' => now()->subYear()->format('Y-m-d'), ], 'end_date' => [ 'type' => 'date', 'label' => __('admin/reports.client_sources_filter_end_date'), 'required' => false, 'default' => now()->format('Y-m-d'), ], 'period' => [ 'type' => 'select', 'label' => __('admin/reports.client_sources_filter_period'), 'required' => false, 'options' => [ 'daily' => __('admin/reports.client_sources_period_daily'), 'weekly' => __('admin/reports.client_sources_period_weekly'), 'monthly' => __('admin/reports.client_sources_period_monthly'), ], 'default' => 'monthly', ], 'country' => [ 'type' => 'select', 'label' => __('admin/reports.client_sources_filter_country'), 'required' => false, 'options' => [ 'all' => __('admin/reports.new_registrations_country_all'), 'TR' => __('admin/reports.new_registrations_country_tr'), 'US' => __('admin/reports.new_registrations_country_us'), 'DE' => __('admin/reports.new_registrations_country_de'), 'GB' => __('admin/reports.new_registrations_country_gb'), ], 'default' => 'all', ], 'min_registrations' => [ 'type' => 'number', 'label' => __('admin/reports.client_sources_filter_min_registrations'), 'required' => false, 'default' => 0, ], ]; } public function hasChart(): bool { return true; } public function getChartData(array $filters = []): ?array { $data = $this->getData($filters); $sourceData = collect($data)->groupBy('source')->map(function ($group) { return [ 'source' => $group->first()->source, 'total_registrations' => $group->sum('registrations'), 'total_active' => $group->sum('active_users'), 'total_inactive' => $group->sum('inactive_users'), ]; })->sortByDesc('total_registrations')->take(10); return [ 'labels' => $sourceData->pluck('source')->toArray(), 'datasets' => [ [ 'label' => __('admin/reports.client_sources_chart_total_registrations'), 'data' => $sourceData->pluck('total_registrations')->toArray(), 'backgroundColor' => [ 'rgba(255, 99, 132, 0.8)', 'rgba(54, 162, 235, 0.8)', 'rgba(255, 205, 86, 0.8)', 'rgba(75, 192, 192, 0.8)', 'rgba(153, 102, 255, 0.8)', 'rgba(255, 159, 64, 0.8)', 'rgba(199, 199, 199, 0.8)', 'rgba(83, 102, 255, 0.8)', 'rgba(78, 252, 3, 0.8)', 'rgba(252, 3, 244, 0.8)', ], 'borderWidth' => 1, ], [ 'label' => __('admin/reports.client_sources_chart_active_users'), 'data' => $sourceData->pluck('total_active')->toArray(), 'backgroundColor' => 'rgba(75, 192, 192, 0.8)', 'borderColor' => 'rgba(75, 192, 192, 1)', 'borderWidth' => 2, 'type' => 'line', 'yAxisID' => 'y1', ], ], ]; } public function getChartType(): string { return 'doughnut'; } } 