aso/app/Services/CorporateMemberService.php

<?php

namespace App\Services;

use App\Models\Member;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;

class CorporateMemberService
{
    public function getAllMemberDashboards(int $corporateId, Request $request)
    {
        $limit = $request->has('perPage') ? $request->input('perPage') : 10;

        return Member::query()
            ->joinCorporateEmployees('left')
            ->joinCorporateDivisions('left')
            ->with(['currentPlan', 'person'])
            ->where('corporate_employees.corporate_id', $corporateId)
            ->when($request->input('search'), function (Builder $query, $search) {
                $query->where(function (Builder $query) use ($search) {
                    $query->orWhere('members.member_id', 'like', "%" . $search . "%")
                        ->orWhere('members.name', 'like', "%" . $search . "%");
                });
            })
            ->when($request->input('division'), function (Builder $query, $value) {
                $query->where('corporate_employees.division_id', $value);
            })
            ->when($request->has('orderBy'), function (Builder $query) use ($request) {
                $orderBy = match ($request->input('orderBy')) {
                    'memberId' => 'member_id',
                    'fullName' => 'name',
                    'status' => 'active',
                    'division' => 'corporate_divisions.name',
                    default => ''
                };

                $query->getQuery()->orderBy($orderBy, $request->order);
            })
            ->select(['members.id', 'members.person_id', 'members.member_id', 'members.name', 'corporate_divisions.name AS division_name', 'members.active'])
            ->selectRaw("(select sum(`claims`.`total_claim`) from `claims` where `members`.`id` = `claims`.`member_id` AND `claims`.`deleted_at` IS NULL) AS `claims_sum_total_claim`")
            ->paginate($limit);
    }

    public function getAllMemberClaimReports(int $corporateId, Request $request)
    {
        $limit = $request->has('perPage') ? $request->input('perPage') : 10;

        return Member::query()
            ->joinClaimRequests('right')
            ->joinCorporateEmployees('left')
            ->joinCorporateDivisions('left')
            ->with('currentPlan')
            ->withSum('claims', 'total_claim')
            ->whereHas('employeds', function (Builder $corporateEmployee) use ($corporateId) {
                $corporateEmployee->where('corporate_id', $corporateId);
            })
            ->when($request->input('search'), function (Builder $query, $search) {
                $query->where(function (Builder $query) use ($search) {
                    $query->orWhere('members.member_id', 'like', "%" . $search . "%")
                        ->orWhere('members.name', 'like', "%" . $search . "%");
                });
            })
            ->when($request->input('division'), function (Builder $division, $value) {
                $division->whereHas('division', function (Builder $corporateEmployee) use ($value) {
                    $corporateEmployee->where('division_id', $value);
                });
            })
            ->when($request->has('orderBy'), function (Builder $query) use ($request) {
                $orderBy = match ($request->orderBy) {
                    'memberId' => 'member_id',
                    'fullName' => 'name',
                    default => ''
                };

                if (in_array($orderBy, ['member_id', 'name', 'active'])) {
                    $query->getQuery()->orderBy($orderBy, $request->order);
                } elseif ($request->orderBy === 'division') {
                    $query->getQuery()->orderBy('corporate_divisions.name', $request->order);
                }
            })
            ->select(['members.id', 'members.person_id', 'members.member_id', 'members.name', 'corporate_divisions.name AS division_name', 'members.active', 'claim_requests.id', 'claim_requests.member_id', 'claim_requests.submission_date'])
            ->paginate($limit);
    }

    public function getAllMemberAlarmCenter(int $corporateId, Request $request)
    {
        $limit = $request->has('perPage') ? $request->input('perPage') : 10;

        return Member::query()
            ->joinCorporateEmployees('left')
            ->joinMemberPlans('left')
            ->joinPlans('left')
            ->with(['currentPlan', 'person'])
            ->where('corporate_employees.corporate_id', $corporateId)
            ->when($request->input('search'), function (Builder $query, $search) {
                $query->where(function (Builder $query) use ($search) {
                    $query->orWhere('members.member_id', 'like', "%" . $search . "%")
                        ->orWhere('members.name', 'like', "%" . $search . "%");
                });
            })
            ->when($request->input('division'), function (Builder $query, $value) {
                $query->where('corporate_employees.division_id', $value);
            })
            ->when($request->has('orderBy'), function (Builder $query) use ($request) {
                $orderBy = match ($request->input('orderBy')) {
                    'memberId' => 'member_id',
                    'fullName' => 'name',
                    'status' => 'active',
                    'start_date' => 'member_plans.start',
                    'end_date' => 'member_plans.end',
                    'service' => 'plans.service_code',

                    default => ''
                };

                $query->getQuery()->orderBy($orderBy, $request->order);
            })
            ->select(['members.id', 'members.person_id', 'members.member_id', 'members.name', 'member_plans.start AS start_date',  'member_plans.end AS end_date', 'plans.active', 'plans.service_code'])
            ->selectRaw("(select sum(`claims`.`total_claim`) from `claims` where `members`.`id` = `claims`.`member_id` AND `claims`.`deleted_at` IS NULL) AS `claims_sum_total_claim`")
            ->paginate($limit);
    }
}