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> </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>
Advertisement
Answer
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:
- Run
php artisan make:collection \App\Collections\BillingCollection
- Add
<?php namespace AppCollections; use DatePeriod; use IlluminateDatabaseEloquentCollection; 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); }