sfp_api_laravel_serprati/app/Services/ReportService.php

<?php

namespace App\Services;

use App\Enums\UserTypeEnum;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Database\Eloquent\Collection;

class ReportService
{
    public function getCounters(): array
    {
        $now = Carbon::now();

        return [
            'novos_associados' => User::where('type', UserTypeEnum::ASSOCIADO)
                ->whereYear('created_at', $now->year)
                ->whereMonth('created_at', $now->month)
                ->count(),
            'contatos' => User::where('type', UserTypeEnum::ASSOCIADO)->count(),
            'exclusoes_mes' => User::where('type', UserTypeEnum::ASSOCIADO)
                ->whereNotNull('excluded_at')
                ->whereYear('excluded_at', $now->year)
                ->whereMonth('excluded_at', $now->month)
                ->count(),
        ];
    }

    public function getNovoAssociadosPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $now = Carbon::now();

        $query = User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereYear('created_at', $now->year)
            ->whereMonth('created_at', $now->month)
            ->orderBy('created_at', 'desc');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term, $filters) {
                $q->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(cpf)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw("TO_CHAR(created_at, 'DD/MM/YYYY') LIKE ?", [$term]);
            });
        }

        return $query->paginate($perPage);
    }

    public function getContatosAssociadosPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $query = User::where('type', UserTypeEnum::ASSOCIADO)
            ->orderBy('name', 'asc');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term) {
                $q->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(email)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(COALESCE(phone, \'\'))) LIKE UNACCENT(?)', [$term]);
            });
        }

        return $query->paginate($perPage);
    }

    public function getExclusoesMesPaginated(array $filters = [], int $perPage = 10): LengthAwarePaginator
    {
        $now = Carbon::now();

        $query = User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereNotNull('excluded_at')
            ->whereYear('excluded_at', $now->year)
            ->whereMonth('excluded_at', $now->month)
            ->orderBy('excluded_at', 'desc');

        if (!empty($filters['search'])) {
            $term = '%' . mb_strtolower($filters['search']) . '%';
            $query->where(function ($q) use ($term) {
                $q->whereRaw('UNACCENT(LOWER(name)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw('UNACCENT(LOWER(cpf)) LIKE UNACCENT(?)', [$term])
                  ->orWhereRaw("TO_CHAR(excluded_at, 'DD/MM/YYYY') LIKE ?", [$term]);
            });
        }

        return $query->paginate($perPage);
    }

    public function getAllNovoAssociados(): Collection
    {
        $now = Carbon::now();

        return User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereYear('created_at', $now->year)
            ->whereMonth('created_at', $now->month)
            ->orderBy('created_at', 'desc')
            ->get();
    }

    public function getAllContatosAssociados(): Collection
    {
        return User::where('type', UserTypeEnum::ASSOCIADO)
            ->orderBy('name', 'asc')
            ->get();
    }

    public function getAllExclusoesMes(): Collection
    {
        $now = Carbon::now();

        return User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereNotNull('excluded_at')
            ->whereYear('excluded_at', $now->year)
            ->whereMonth('excluded_at', $now->month)
            ->orderBy('excluded_at', 'desc')
            ->get();
    }
}