How to calculate balance in payment and invoice page in laravel?

Question

I have this page showing invoice and payments, I want to calculate balance at end each row of the table. I have my records sorted on DESC order that's why I am unable to get it right.

The result is in below picture, It is sorted by created at, I want that the type Payment is deducted from the balance and the last row balance column show $-30, in the second column it show $70. The invoice amount should be added to the balance.

Student Modal

    public function billings()
    {
        return $this->hasMany(Billing::class)->orderBy('created_at', 'desc');
    }

Billing Modal

    public function billingRecords()
    {
        return $this->hasMany(BillingRecord::class, 'billing_id');
    }

DataTable / HTML

<table class=" table table-bordered table-striped table-hover datatable datatable-Room">
   <thead>
     <tr>
       <th>Date</th>
       <th>Type</th>
       <th>Amount</th>
       <th>Balance</th>
       <th>&nbsp;</th>
     </tr>
   </thead>             
   <tbody>
     @php
       $balance = 0;
     @endphp

     @foreach($student->billings as $key => $billing)
        @php
          $total = 0;
          $tooltip = "";
        @endphp

        <tr data-entry-id="{{ $billing->id }}">
           <td>
             <p class="m-0">{{ $billing->created_at->format('M d, Y') ?? '' }}</p>
             <p class="small m-0">{{ $billing->created_at->format('h:i A') ?? '' }}</p>
           </td>

           <td>{{ $billing->type ?? '' }}</td>

           <td>
             @foreach ($billing->billingRecords as $record)
                @php
                  $total += $record->amount;
                @endphp
             @endforeach

             @if($billing->type == "Payment")
                 $-{{ $total }}
             @elseif($billing->type == "Invoice")
                 ${{ $total }}
             @endif
           </td>

           <td>
             $ {{ $balance += $total }}
           </td>
                 
         </tr>
       @endforeach
     </tbody>
   </table>

Current Result

Answer 1

What about directly adjusting the $total?

<tr data-entry-id="{{ $billing->id }}">
    <td>
        <p class="m-0">{{ $billing->created_at->format('M d, Y') ?? '' }}</p>
        <p class="small m-0">{{ $billing->created_at->format('h:i A') ?? '' }}</p>
    </td>

    <td>{{ $billing->type ?? '' }}</td>
     
    <td>
        @foreach ($billing->billingRecords as $record)
            @php
                $total += $record->amount;
            @endphp
        @endforeach
        @php
            if ($billing->type == "Payment") {
                $total *= -1;
            }
        @endphp

        ${{ $total }}
    </td>

     <td>
         $ {{ $balance += $total }}
     </td>
                 
</tr>

---

public function billings()
    {
        return $this->hasMany(Billing::class)->orderBy('created_at', 'asc');
    }

@php
    $balance = 0;
    $billings = $student->billings->map(function($billing) use(&$balance) {
        $billing->total = $billing->billingRecords->sum('amount');
        if ($billing->type == "Payment") {
            $billing->total *= -1;
        }
        $billing->balance = ($balance += $billing->total);
        return $billing;
    })->reverse();
@endphp
@foreach($billings as $key => $billing)
    @php
        $tooltip = "";
    @endphp

    <tr data-entry-id="{{ $billing->id }}">
        <td>
            <p class="m-0">{{ $billing->created_at->format('M d, Y') ?? '' }}</p>
            <p class="small m-0">{{ $billing->created_at->format('h:i A') ?? '' }}</p>
        </td>

        <td>{{ $billing->type ?? '' }}</td>

        <td>${{ $billing->total }}</td>

        <td>$ {{ $billing->balance }}</td>
                
    </tr>
@endforeach

You can also clean this a bit up:

1. Run php artisan make:collection \\App\\Collections\\BillingCollection
2. Add

<?php

namespace App\Collections;

use DatePeriod;
use Illuminate\Database\Eloquent\Collection;
 
class ExampleCollection extends Collection
{
    public function processed()
    {
        $balance = 0;
        return $this->map(function($billing) use(&$balance) {
            $billing->total = $billing->billingRecords->sum('amount');
            if ($billing->type == "Payment") {
                $billing->total *= -1;
            }
            $billing->balance = ($balance += $billing->total);
            return $billing;
        })->reverse();
    }
}

Inside your Billing model add this:

    public function newCollection(array $models = [])
    {   
        return new BillingCollection($models);
    }