@php
    use App\Http\Controllers\GlobalMethods as SuperM;
    use App\Models\Loan\Loan;
    use Carbon\Carbon;

    $status = $_GET['status'];
    $date1 = Carbon::parse($_GET['start_date']);
    $date2 = Carbon::parse($_GET['end_date']);
    $type = $_GET['type'];
    $request_type = $_GET['request_type'];
    $with = $_GET['with'];
    $files = Loan::whereBetween('created_at', [$date1, $date2])
        ->orderBy('id', 'asc')
        ->with('repayments', 'top_ups', 'offer', 'agents', 'status', 'topped_up', 'user');
    if ($status != 'all') {
        $files = $files->whereHas('status', function ($query) use ($status) {
            $query->where('status', $status);
        });
    }
    if ($type != 'all') {
        $files = $files->where('type', $type);
    }
    if ($request_type != 'all') {
        $files = $files->where('request_type', $request_type);
    }
    $files = $files->get();
    if ($with == 'over') {
        $files = SuperM::sortOverDeductions($files);
    } elseif ($with == 'under') {
        $files = SuperM::sortUnderDeductions($files);
    } elseif ($with == 'allcompleted') {
        $files = SuperM::sortFullPayDeductions($files);
    } elseif ($with == 'failed') {
        $files = SuperM::sortFailedDeductions($files);
    }
    $data = "
    <table>
        <thead>
            <tr>
                <th></th>
                <th colspan='9'>USER DETAILS</th>
                <th colspan='20'>LOAN DETAILS</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <th>SN</th>
                <th>UNIQUE ID</th>
                <th>FIRST NAME</th>
                <th>LAST NAME</th>
                <th>OTHER NAME</th>
                <th>PHONE NUMBER</th>
                <th>E-MAIL</th>
                <th>STAFF ID</th>
                <th>DATE JOINED</th>
                <th>ACCOUNT OFFICER</th>

                <th>STATUS</th>
                <th>TENOR (MONTH)</th>
                <th>AMOUNT (N)</th>
                <th>INTEREST (%)</th>
                <th>LOAN TYPE</th>
                <th>REQUEST TYPE</th>
                <th>LOAN TYPE (AGENCY)</th>
                <th>MONTHLY REPAYMENT</th>
                <th>FIRST REPAYMENT</th>
                <th>LAST REPAYMENT</th>
                <th>SUCCESSFUL DEDUCTIONS</th>
                <th>SUCCESSFUL DEDUCTIONS SUM (N)</th>
                <th>FAILED DEDUCTIONS</th>
                <th>UNDER DEDUCTIONS</th>
                <th>OVER DEDUCTIONS</th>
                <th>TOPUP BALANCE</th>
                <th>TOPUP STATUS</th>
                <th>LIQUIDATION BALANCE</th>
                <th>DATE APPROVED</th>
                <th>DATE IMPORTED</th>
            </tr>";
    $i = 1;

    foreach ($files as $file) {
        $offer = $file->offer;
        $agent = $file->agents()->first();

        $data .= '<tr>';
        $data .= '<td>' . $i++ . '</td>';
        $data .= '<td>' . $file->unique_id . '</td>';
        $data .= '<td>' . ucwords($file->more->account_info->first_name) . '</td>';
        $data .= '<td>' . ucwords($file->more->account_info->last_name) . '</td>';
        $data .= '<td>' . ucwords($file->more->account_info->other_name) . '</td>';
        $data .= '<td>' . $file->more->account_info->phone_number . '</td>';
        if (isset($file->more->account_info->email)) {
            $data .= '<td>' . strtolower($file->more->account_info->email) . '</td>';
        } else {
            $data .= '<td>-</td>';
        }
        $data .= '<td>' . $file->more->account_info->staff_id . '</td>';
        $data .= '<td>' . $file->created_at->format('l F d, Y h:i A') . '</td>';
        if ($agent) {
            $data .= '<td>' . strtoupper($agent->first_name . ' ' . $agent->last_name) . '</td>';
        } else {
            if ($file->user->referred_by) {
                $ref = $file->user->referred_by;
                $data .= '<td>' . strtoupper($ref->first_name . ' ' . $ref->last_name) . '</td>';
            } else {
                $data .= '<td>-</td>';
            }
        }

        $data .= '<td>' . $file->status->status . '</td>';
        if ($offer) {
            $data .= '<td>' . $offer->more->tenor . '</td>';
            $data .= '<td>' . number_format($offer->more->amount, 2) . '</td>';
            $data .= '<td>' . number_format($offer->more->interest, 1) . '%</td>';
        } else {
            $data .= '<td>-</td>';
            $data .= '<td>-</td>';
            $data .= '<td>-</td>';
        }
        $data .= '<td>' . strtoupper($file->type) . '</td>';
        $data .= '<td>' . strtoupper($file->request_type) . '</td>';
        if ($file->type == 'public') {
            $data .= '<td>' . strtoupper($file->more->loan_type) . '</td>';
        } else {
            $data .= '<td>-</td>';
        }
        $data .= '<td>' . number_format(SuperM::getMonthlyRepayment($file), 2) . '</td>';
        $firstRepay = SuperM::getFirstRepayment($file);
        if ($firstRepay) {
            $data .= '<td>' . $firstRepay->format('Y-m-d') . '</td>';
        } else {
            $data .= '<td>-</td>';
        }
        $lastRepay = SuperM::getLastRepayment($file);
        if ($lastRepay) {
            $data .= '<td>' . $lastRepay->format('Y-m-d') . '</td>';
        } else {
            $data .= '<td>-</td>';
        }
        $data .= '<td>' . $file->repayments()->count() . '</td>';
        $data .= '<td>' . number_format($file->repayments()->sum('amount'), 2) . '</td>';
        $data .= '<td>' . SuperM::getFailedDeductions($file) . '</td>';
        $data .= '<td>' . SuperM::getUnderDeductions($file) . '</td>';
        $data .= '<td>' . SuperM::getOverDeductions($file) . '</td>';
        $data .= '<td>' . number_format(SuperM::getTopUpBalance($file), 2) . '</td>';
        $data .= '<td>' . SuperM::getTopUpStatus($file) . '</td>';
        $data .= '<td>' . number_format(SuperM::getLiquidation($file), 2) . '</td>';
        if ($offer) {
            $data .= '<td>' . Carbon::parse($offer->created_at)->format('Y-m-d') . '</td>';
        } else {
            $data .= '<td>-</td>';
        }
        $data .= '<td>' . $file->created_at->format('Y-m-d') . '</td>';
        $data .= '</tr>';
    }

    $data .= '</tbody></table>';
@endphp
{!! $data !!}