admission

 avatar
unknown
php
3 years ago
36 kB
2
Indexable
    public function getCandidateByStepId($data)
    {
        try {
            $now = Carbon::now();

            $admissionBatchId = isset($data['admission_batch_id']) ? (int)$data['admission_batch_id'] : null;
            $academicYear = $data['academic_year'] ?? null;
            $schoolLocationId = isset($data['school_location_id']) ? (int)$data['school_location_id'] : null;
            $keyword = isset($data['keyword']) ? strtolower($data['keyword']) : null;

            $step = isset($data['step']) ? strtolower($data['step']) : null;
            if ($step === 'all candidate' || $step === 'all') {
                $step = isset($data['status']) ? strtolower($data['status']) : null;
            }

            $schoolLocationIds = [];
            $isSuperAdmin = isset($data["user_id"]) && ($data["user_id"] === 1 || $data["user_id"] === '1');
            if (! $isSuperAdmin && isset($data['user_id'])) {
                $userRoles = TUserRole::query()->where('user_id', $data['user_id'])->whereNull('deleted_at')->get();
                $schoolLocationIds = collect($userRoles)->pluck('school_location_id')->unique()->toArray();
            }

            $candidate = DB::table('tr_admission_candidate_status')->select([
                'ms_student.student_id',
                'tr_admission_batch.admission_batch_id',
                // 'tr_admission_batch_step_relation.admission_step_id',
                "ms_school_location.school_short_address as school_location_name",
                "ms_school_level.description as school_level_name",
                "ms_year_level.description as year_level_name",
                "ms_admission_candidate.admission_form_id as form_number",
                'ms_admission_candidate.candidate_id',
                'ms_admission_candidate.email as candidate_email',
                'ms_admission_candidate.student_name',
                'ms_admission_candidate.gender',
                'ms_admission_candidate.date_of_birth',
                'ms_admission_candidate.origin_school',
                'ms_admission_candidate.address as candidate_address',
                'ms_admission_candidate.cellular_number as candidate_number',
                'ms_admission_candidate.cellular_number',
                "ms_admission_candidate.created_at as registration_date",
                'ms_admission_candidate.nis_generated',
                'ms_religion.agama',
                'ms_school_level.school_level_id',
                'ms_school_location.school_location_id',
                't_user.user_id',
                't_user.username',
                't_user.email',
                't_user.uuid',
                't_user.is_temporary',
                't_user.email_verified_at',
                'tr_admission_candidate_status.unpaid',
                // 'tr_admission_candidate_status.paid',
                // 'tr_admission_candidate_status.verified',
                // 'tr_admission_candidate_status.complete',
                'tr_admission_candidate_status.canceled',
                // 'tr_admission_candidate_status.registration_payment_status',
//                'tr_admission_candidate_status.final_payment_status',
                'tr_admission_batch.batch_participant_category',
                'tr_admission_batch.final_payment_amount',
                'tr_admission_candidate_status.deleted_at',

                DB::raw("
                    json_build_object(
                        'payment', ROW_TO_JSON(tr_admission_payment)
                    ) AS candidate
                "),

                DB::raw("null as status_final_payment"),
                DB::raw("null as insufficient_payment"),
                DB::raw("null as current_status"),
                DB::raw("null as next_status"),

                DB::raw("MAX(tr_admission_batch_step_relation.admission_step_id) as admission_step_id"),

                DB::raw("JSON_AGG(ms_admission_step.step_rule ORDER by tr_admission_batch_step_relation.step_order) as step_rules"),
                DB::raw("COUNT(distinct ms_admission_step.step_rule) as total_step_rules"),

                DB::raw("JSON_AGG(distinct t_user2.username) as usernames"),
                DB::raw("JSON_AGG(distinct t_user2.email) as emails"),
                DB::raw("JSON_AGG(distinct ms_admission_parents.email) as parent_emails"),

                DB::raw("
                   JSON_AGG(distinct ms_admission_sibling.sibling_name) as sibling_names
                "),

                DB::raw("
                    JSON_AGG(ms_admission_parents.parent_name ORDER BY parent_type) as parent_names
                "),

                DB::raw("
                    JSON_AGG(ms_admission_parents.email ORDER BY parent_type) as parent_email
                "),

                DB::raw("
                    JSON_AGG(ms_admission_parents.cellular_number ORDER BY parent_type) as parent_number
                "),

                DB::raw("
                    JSON_AGG(ms_admission_parents.address ORDER BY parent_type) as parent_address
                "),

                DB::raw("COUNT(distinct ms_admission_sibling.sibling_name) as sibling_count"),

                // check having step X
                DB::raw("MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) has_step_registration"),
                DB::raw("MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) has_step_registration_payment"),
                DB::raw("MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) has_step_test_schedule"),
                DB::raw("MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) has_step_test_result"),
                DB::raw("MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) has_step_final_payment"),

                // registration payment
                DB::raw("MAX(case when tr_admission_payment.registration_payment_date is not null then 1 else 0 end) has_paid_registration_payment"),
                DB::raw("
                    CASE WHEN (
                        MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
                    ) THEN true
                    ELSE
                    tr_admission_candidate_status.paid
                    END
                "),
                DB::raw("
                    CASE WHEN (
                        MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
                    ) THEN true
                    ELSE
                    tr_admission_candidate_status.verified
                    END
                "),
                DB::raw("
                    CASE WHEN (
                        MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
                    ) THEN true
                    ELSE
                    tr_admission_candidate_status.registration_payment_status
                    END
                "),

                // test schedule
                DB::raw("MAX(case when tr_admission_test_schedule_participant.admission_test_schedule_participant_id is not null then 1 else 0 end) as has_test_schedule"),
                DB::raw("MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now' as has_test_schedule_begin"),

                // test result
                DB::raw("MAX(case when tr_admission_test_result.test_result is not null then 1 else 0 end) as has_test_result"),
                DB::raw("MAX(case when tr_admission_test_result.test_result is false then 1 else 0 end) as has_test_result_failed"),

                // final payment
                DB::raw("MAX(case when tr_admission_payment.final_payment_date is not null then 1 else 0 end) has_paid_final_payment"),

                // complete
                DB::raw("
                     CASE WHEN (
                         COUNT(distinct ms_admission_step.step_rule) = 1
                         AND
                         MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) = 1
                         AND
                         tr_admission_candidate_status.unpaid IS TRUE
                     ) THEN TRUE
                     WHEN (
                         MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1
                         AND
                         MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 0
                         AND
                         MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) = 0
                         AND
                         MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                     ) THEN TRUE
                     WHEN (
                         MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1
                         AND
                         MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) = 0
                         AND
                         MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1
                     ) THEN TRUE
                     ELSE
                        tr_admission_candidate_status.complete
                     END
                     AS complete
                "),

                // complete
                DB::raw("
                     CASE WHEN (
                         COUNT(distinct ms_admission_step.step_rule) = 1
                         AND
                         MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) = 1
                         AND
                         tr_admission_candidate_status.unpaid IS TRUE
                     ) THEN TRUE
                     ELSE
                        tr_admission_candidate_status.final_payment_status
                     END
                     AS final_payment_status
                "),

                // check in step X
                DB::raw("
                  case when (MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1)
                       then (case when (MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1)
                            then true
                            else false
                            end)
                       when (MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1)
                       then (
                            MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                       )
                       when (MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 1)
                       then (case when registration_payment_status is true
                            then true
                            else false
                            end
                       )
                    when tr_admission_candidate_status.unpaid IS FALSE THEN FALSE
                    else true
                    end as in_step_final_payment
                "),
            ])->join('ms_admission_candidate', function ($q) {
                $q->on('ms_admission_candidate.candidate_id', '=', 'tr_admission_candidate_status.candidate_id');
                $q->whereNull('ms_admission_candidate.deleted_at');
            })->join('tr_admission_batch', function ($q) use ($admissionBatchId, $schoolLocationId, $data, $isSuperAdmin, $schoolLocationIds, $academicYear) {
                $q->on('tr_admission_batch.admission_batch_id', '=', 'ms_admission_candidate.admission_batch_id');
                $q->whereNull('tr_admission_batch.deleted_at');

                if ($admissionBatchId && ($admissionBatchId !== 0)) {
                    $q->where('tr_admission_batch.admission_batch_id', $admissionBatchId);
                }

                if ($schoolLocationId && ($schoolLocationId !== 0)) {
                    $q->where('tr_admission_batch.school_location_id', $schoolLocationId);
                }

                if ($academicYear) {
                    $q->where('tr_admission_batch.academic_year', $academicYear);
                }

                if (! $isSuperAdmin && $schoolLocationIds) {
                    $q->whereIn('tr_admission_batch.school_location_id', $schoolLocationIds);
                }
            })->join('ms_school_level', function ($q) {
                $q->on('ms_school_level.school_level_id', '=', 'ms_admission_candidate.enroll_school_level_id');
                $q->whereNull('ms_school_level.deleted_at');
            })->join('ms_school_location', function ($q) {
                $q->on('ms_school_location.school_location_id', '=', 'ms_admission_candidate.enroll_school_location_id');
                $q->whereNull('ms_school_location.deleted_at');
            })->join('t_user', function ($q) {
                $q->on('t_user.uuid', '=', 'ms_admission_candidate.user_uuid');
                $q->whereNull('t_user.deleted_at');
            })->join('tr_admission_batch_step_relation', function ($q) {
                $q->on('tr_admission_batch_step_relation.admission_batch_id', '=', 'tr_admission_batch.admission_batch_id');
                $q->whereNull('tr_admission_batch_step_relation.deleted_at');
            })->join('ms_admission_step', function ($q) {
                $q->on('ms_admission_step.admission_step_id', '=', 'tr_admission_batch_step_relation.admission_step_id');
                $q->whereNull('ms_admission_step.deleted_at');
            })->leftJoin('ms_year_level', function ($q) {
                $q->on('ms_year_level.year_level_id', '=', 'ms_admission_candidate.enroll_year_level_id');
                $q->whereNull('ms_year_level.deleted_at');
            })->leftJoin('ms_student', function ($q) {
                $q->on('ms_student.user_id', '=', 't_user.user_id');
                $q->whereNull('ms_student.deleted_at');
            })->leftJoin('tr_admission_test_schedule_participant', function ($q) {
                $q->on('tr_admission_test_schedule_participant.candidate_id', '=', 'ms_admission_candidate.candidate_id');
                $q->whereNull('tr_admission_test_schedule_participant.deleted_at');
            })->leftJoin('tr_admission_test_schedule', function ($q) {
                $q->on('tr_admission_test_schedule.admission_batch_id', '=', 'tr_admission_batch.admission_batch_id');
                $q->on('tr_admission_test_schedule.admission_test_schedule_id', '=', 'tr_admission_test_schedule_participant.admission_test_schedule_id');
                $q->whereNull('tr_admission_test_schedule.deleted_at');
            })->leftJoin('tr_admission_test_schedule_criteria', function ($q) {
                $q->on('tr_admission_test_schedule_criteria.admission_test_schedule_id', '=', 'tr_admission_test_schedule.admission_test_schedule_id');
                $q->whereNull('tr_admission_test_schedule_criteria.deleted_at');
            })->leftJoin('tr_admission_test_result', function ($q) {
                $q->on('tr_admission_test_result.admission_test_schedule_participant_id', '=', 'tr_admission_test_schedule_participant.admission_test_schedule_participant_id');
                $q->whereNull('tr_admission_test_result.deleted_at');
            })->leftJoin('tr_admission_payment', function ($q) {
                $q->on('tr_admission_payment.candidate_id', '=', 'ms_admission_candidate.candidate_id');
                $q->whereNull('tr_admission_payment.deleted_at');
            })->leftJoin('t_user as t_user2', function ($q) {
                $q->on('t_user2.candidate_id', '=', 'ms_admission_candidate.candidate_id');
                $q->whereNull('t_user2.deleted_at');
            })->leftJoin('ms_admission_parents', function ($q) {
                $q->on('ms_admission_parents.candidate_id', '=', 'ms_admission_candidate.candidate_id');
                $q->whereNull('ms_admission_parents.deleted_at');
            })->leftJoin('ms_admission_sibling', function ($q) {
                $q->on('ms_admission_sibling.candidate_id', '=', 'ms_admission_candidate.candidate_id');
                $q->whereNull('ms_admission_sibling.deleted_at');
            })->leftJoin('ms_religion', function ($q) {
                $q->on('ms_religion.religion_id', '=', 'ms_admission_candidate.religion_id');
                $q->whereNull('ms_religion.deleted_at');
            });

            if ($keyword) {
                $candidate = $candidate
                    ->where(static function ($i) use ($keyword) {
                        $i->where('ms_admission_candidate.student_name', 'ilike', '%' . $keyword . '%');
                        $i->orWhere('ms_admission_candidate.admission_form_id', 'ilike', '%' . $keyword . '%');
                        $i->orWhere('t_user.username', 'ilike', '%' . $keyword . '%');
                    });
            }

            $candidate = $candidate->groupBy([
                'tr_admission_batch.admission_batch_id',
                'ms_admission_candidate.candidate_id',
                'tr_admission_candidate_status.admission_candidate_status_id',
                'tr_admission_payment.admission_payment_id',
                'ms_student.student_id',
                't_user.user_id',
                'ms_school_location.school_location_id',
                'ms_school_level.school_level_id',
                'ms_year_level.description',
                'ms_religion.agama',
            ]);

            if ($step === 'canceled') {
                $candidate = $candidate->whereRaw("
                    (
                        (canceled IS TRUE)
                        OR
                        (canceled IS FALSE AND tr_admission_candidate_status.deleted_at IS NOT NULL)
                    )
                ");
            } else {
                $candidate = $candidate->where('canceled', false)->whereNull('tr_admission_candidate_status.deleted_at');
            }

            if ($step !== null) {

                // filter tampilin candidate yang hanya ada di current tab yang active
                if ($step !== 'canceled' && $step !== 'completed' && $step !== 'report' && $step !== 'nis-completed') {
                    $candidate = $candidate->havingRaw("
                        MAX(case when ms_admission_step.step_rule = '$step' THEN 1 ELSE 0 END) = 1
                    ");
                }

                if ($step === 'registration') {
                    $candidate = $candidate
                        ->where('unpaid', false)
                        ->where('paid', false)
                        ->where('verified', false)
                        ->where('registration_payment_status', false)
                        ->where('final_payment_status', false)
                        ->where('complete', false);
                } else if ($step === 'registration_payment') {
                    $candidate = $candidate
                        ->where('unpaid', true)
                        // ->where('paid', true)
                        ->where('verified', false)
                        ->where('registration_payment_status', false)
                        ->where('final_payment_status', false)
                        ->where('complete', false);
                } else if ($step === 'test_schedule') {
                    $candidate = $candidate
                        ->where('unpaid', true)
                        // ->where('paid', true)
                        // ->where('verified', true)
                        // ->where('registration_payment_status', true)
                        ->where('final_payment_status', false)
                        ->where('complete', false)
                        ->havingRaw("
                            CASE WHEN (
                                MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
                                AND (
                                    MAX(case when tr_admission_test_schedule_participant.admission_test_schedule_participant_id is not null then 1 else 0 end) = 0
                                    OR
                                    MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) > '$now'
                                )
                            ) THEN true
                            ELSE (
                                tr_admission_candidate_status.paid IS TRUE
                                AND
                                tr_admission_candidate_status.verified IS TRUE
                                AND
                                tr_admission_candidate_status.registration_payment_status IS TRUE
                                AND (
                                    MAX(case when tr_admission_test_schedule_participant.admission_test_schedule_participant_id is not null then 1 else 0 end) = 0
                                    OR
                                    MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) > '$now'
                                )
                            )
                            END
                        ");
                } else if ($step === 'test_result') {
                    $candidate = $candidate
                        ->where('unpaid', true)
                        // ->where('paid', true)
                        // ->where('verified', true)
                        // ->where('registration_payment_status', true)
                        ->where('final_payment_status', false)
                        ->where('complete', false)
                        ->havingRaw("
                            CASE WHEN (
                                MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
                                AND
                                MAX(case when tr_admission_test_schedule_participant.admission_test_schedule_participant_id is not null then 1 else 0 end) = 1
                                AND
                                MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                            ) THEN true
                            ELSE (
                                tr_admission_candidate_status.paid IS TRUE
                                AND
                                tr_admission_candidate_status.verified IS TRUE
                                AND
                                tr_admission_candidate_status.registration_payment_status IS TRUE
                                AND (
                                    MAX(case when tr_admission_test_schedule_participant.admission_test_schedule_participant_id is not null then 1 else 0 end) = 1
                                    AND
                                    MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                                )
                            )
                            END
                        ");
                } else if ($step === 'final_payment') {
                    $candidate = $candidate
                        ->where('unpaid', true)
                        ->where('complete', false)
                        ->havingRaw("
                            case when (MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1)
                                then (case when (MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1)
                                    then true
                                    else false
                                    end)
                                when (MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1)
                                then (
                                    MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                                )
                               when (MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 1)
                               then (case when (registration_payment_status is true and verified is true)
                                    then true
                                    else false
                                    end
                               )
                            else true
                            end
                        ");
                } else if ($step === 'report') {

                    // khusus report, tampilin semua cuma yang udah isi form
                    $candidate = $candidate
                        ->where('unpaid', true);

                } else if ($step === 'completed') {
                    $candidate = $candidate->havingRaw("
                        CASE WHEN (
                            COUNT(distinct ms_admission_step.step_rule) = 1
                            AND
                            MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) = 1
                            AND
                            tr_admission_candidate_status.unpaid IS TRUE
                        ) THEN TRUE
                        WHEN (
                            MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1
                            AND
                            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 0
                            AND
                            MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                        ) THEN TRUE
                        WHEN (
                            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1
                            AND
                            MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) = 0
                            AND
                            MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1
                        ) THEN TRUE
                        ELSE
                            tr_admission_candidate_status.complete
                        END
                    ");
                    // Do not show completed generate nis
                    $candidate = $candidate->havingRaw("
                        CASE WHEN (
                            tr_admission_batch.batch_participant_category = 'External'
                        ) THEN
                            ms_admission_candidate.nis_generated ISNULL
                        ELSE
                            t_user.candidate_id IS NOT NULL AND ms_admission_candidate.nis_generated ISNULL
                        END
                    ");
                } else if ($step === 'nis-completed') {
                    $candidate = $candidate->havingRaw("
                        CASE WHEN (
                            COUNT(distinct ms_admission_step.step_rule) = 1
                            AND
                            MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) = 1
                            AND
                            tr_admission_candidate_status.unpaid IS TRUE
                        ) THEN TRUE
                        WHEN (
                            MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1
                            AND
                            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 0
                            AND
                            MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
                        ) THEN TRUE
                        WHEN (
                            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1
                            AND
                            MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) = 0
                            AND
                            MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1
                        ) THEN TRUE
                        ELSE
                            tr_admission_candidate_status.complete
                        END
                    ");
                    // Show only completed generate nis
                    $candidate = $candidate->havingRaw("
                        CASE WHEN (
                            tr_admission_batch.batch_participant_category = 'External'
                        ) THEN
                            ms_admission_candidate.nis_generated IS NOT NULL
                        ELSE
                            t_user.candidate_id IS NOT NULL AND ms_admission_candidate.nis_generated IS NOT NULL
                        END
                    ");
                }
            }

            if (isset($data['order_by'], $data['order_direction'])) {
                $candidate = $candidate->orderBy($data['order_by'], $data['order_direction']);
            } else {
                $candidate = $candidate->orderByDesc('ms_admission_candidate.created_at');
            }

            if (isset($data['paginate']) && ($data['paginate'] !== 'false')) {
                $candidateResult = $candidate->paginate();
                $candidateItems = collect($candidateResult->items());
            } else {
                $candidateResult = $candidate->get();
                $candidateItems = collect($candidateResult);
            }

            $tenantStorage = new TenantStorage();
            $candidates = $candidateItems->map(function ($item) use ($step, $tenantStorage) {

                $item->candidate = json_decode($item->candidate, true, 512, JSON_THROW_ON_ERROR);
                // PAYMENT
                $item->candidate['payment']['registration_payment_slip_pathfile_preview'] = isset($item->candidate['payment']['registration_payment_slip_pathfile']) ? $tenantStorage->url($item->candidate['payment']['registration_payment_slip_pathfile'], null) : null;
                $item->candidate['payment']['final_payment_slip_pathfile_preview'] = isset($item->candidate['payment']['final_payment_slip_pathfile']) ? $tenantStorage->url($item->candidate['payment']['final_payment_slip_pathfile'], null) : null;


                $finalStatus = '';
                $batchFinalPayment = $item->final_payment_amount ?: 0;
                $userFinalPayment = $item->candidate['payment']['final_payment_amount'] ?? 0;
                $finalAmount = $userFinalPayment ? ($batchFinalPayment - $userFinalPayment) : '-';
                $finalDate = $item->candidate['payment']['final_payment_date'] ?? null;
                if ($finalDate) {
                    if ($finalAmount > 0) {
                        $finalStatus = 'In Debt';
                    } else if ($finalAmount === 0) {
                        $finalStatus = 'Paid Off';
                    }
                }
                $item->status_final_payment = $finalStatus;
                $item->insufficient_payment = $finalAmount;

                $item->is_deleted = $item->deleted_at !== null;

                $item->step_rules = json_decode($item->step_rules, true, 512, JSON_THROW_ON_ERROR);
                $item->step_rules = array_values(array_unique($item->step_rules));
                $item->usernames = json_decode($item->usernames, true, 512, JSON_THROW_ON_ERROR);

                $item->sibling_count = $item->sibling_count == 0 ? 1 : $item->sibling_count;
                $item->sibling_names = json_decode($item->sibling_names, true, 512, JSON_THROW_ON_ERROR);
                $item->parent_names = array_values(array_unique(json_decode($item->parent_names, true, 512, JSON_THROW_ON_ERROR)));
                $item->parent_email = array_values(array_unique(json_decode($item->parent_email, true, 512, JSON_THROW_ON_ERROR)));
                $item->parent_number = array_values(array_unique(json_decode($item->parent_number, true, 512, JSON_THROW_ON_ERROR)));
                $item->parent_address = array_values(array_unique(json_decode($item->parent_address, true, 512, JSON_THROW_ON_ERROR)));

                if (! in_array($item->username, $item->usernames, true)) {
                    $item->usernames = array_merge([$item->username], $item->usernames);
                }

                /**
                 * MAPPING EMAIL
                 */
                $item->emails = json_decode($item->emails, true, 512, JSON_THROW_ON_ERROR);
                $parentEmails = json_decode($item->parent_emails, true, 512, JSON_THROW_ON_ERROR);
                foreach ($parentEmails as $parentEmail) {
                    if (! in_array($parentEmail, $item->emails, true)) {
                        $item->emails[] = $parentEmail;
                    }
                }
                if (! in_array($item->email, $item->emails, true)) {
                    $item->emails[] = $item->email;
                }
                if (! in_array($item->candidate_email, $item->emails, true)) {
                    $item->emails[] = $item->candidate_email;
                }
                $item->emails = array_values(array_filter($item->emails, static function ($email) {
                    return ! (filter_var($email, \FILTER_VALIDATE_EMAIL) === false);
                }));
                if (empty($item->emails)) {
                    $item->emails[] = '-';
                }
                unset($item->parent_emails, $item->candidate_email, $item->email);
                /**
                 * END MAPPING EMAIL
                 */

                // Handler new logic nis generated
                $item->is_temporary = !isset($item->nis_generated);
                $candidateStatus = TrAdmissionCandidateStatus::defineCandidateStatus($step, json_decode(json_encode($item, JSON_THROW_ON_ERROR), true, 512, JSON_THROW_ON_ERROR));

                $item->current_status_code = $candidateStatus['current_code'];
                $item->current_status = $candidateStatus['current'];
                $item->next_status_code = $candidateStatus['next_code'];
                $item->next_status = $candidateStatus['next'];

                return $item;
            });

            $result = $candidateResult;
        } catch (Exception $e) {
            throw $e;
        }

        return $result;
    }