CODEWITHSUNDEEP
laraveleloquent
Mahdi Jafari
Mahdi Jafari

Reputation: 415

How to calculate bank balance and total banks balance in Laravel 8?

I have bank table and transaction table which has one to many relationship. I want to calculate balance of each bank and also total banks balance.

bank model:

class Bank extends Model
{
    use HasFactory;

    protected $fillable = [
        'name',
        'code',
        'accountNumber',
        'swiftCode',
        'currency',
        'country',
        'city',
        'street',
        'zipcode',
    ];

    public function transactions()
    {
        return $this->hasMany(Transaction::class);
    }
}

transactions model:

class Transaction extends Model
{
    use HasFactory;

    protected $fillable = [
        'amount',
        'currency',
        'exchangeRate',
        'reference',
        'type',
        'adjustment',
        'partner_id',
        'bank_id',
    ];
    public function bank()
    {
        return $this->belongsTo(Bank::class);
    }
}

I wrote the code below for balance which is not correct yet, because I can't access transactions columns using this collection.

 <td>{{$bank->transactions->where('type', 'credit')->sum('amount') - $bank->transactions->where('type', 'debit')->sum('amount')}}</td>

there are many currencies which needs to be normalized using exchangRate. I need to multiply amount with exchangeRate and sum all it's results. so it gives bank balance.

I should pass it from index function. it should be returned with bank collection. BankController function index:

 public function index()
    {
        $banks = Bank::all();
        return view('inc.banks', compact('banks'));
    }

based on these, how can I calculate each bank balance and total banks balance?

Upvotes: 1

Views: 1480

Answers (3)

Mahdi Jafari
Mahdi Jafari

Reputation: 415

I have solved this in bank and transaction model. I defined a custom attribute and then append it to model.

Transaction Model:

 protected $appends = [
        'final_amount'
    ];
 public function getFinalAmountAttribute() {
        return $this->amount * $this->exchangeRate;
    }

Bank Model:

 public function getCreditSumAttribute(){
        $sum = 0;
        if($this->transactions){
            $sum = $this->transactions->where('type', 'credit')->sum('final_amount');
        }
        return $sum;
    }

    public function getDebitSumAttribute(){
        $sum = 0;
        if($this->transactions){
            $sum = $this->transactions->where('type', 'debit')->sum('final_amount');
        }
        return $sum;
    }

    public function getSubCreditDebitAttribute(){
        return $this->credit_sum - $this->debit_sum;
    }

and in banks view I put this code:

<td>{{'$ '}}{{$bank->sub_credit_debit}}</td>

Upvotes: 1

Farhan Ibn Wahid
Farhan Ibn Wahid

Reputation: 1022

You can make two relations (credits and debits) and with them, calculate the bank balances and total balance.

Bank model

public function credits(){
    return $this->hasMany(Transaction::class, foreign_id, primary_id)->where('type', 'credit);
}

public function debits(){
    return $this->hasMany(Transaction::class, foreign_id, primary_id)->where('type', 'debit);
}

Blade:

Initialize $total variable first. Then loop through $banks to get balance for each bank and add them up for getting total balance.

@php( $total = 0) 

@foreach($banks as $key => $bank)
    $balance = $bank->credits->sum('amount') - $bank->debits->sum('amount'); // $balance will have each bank balance 
    $total += $balance;
@endforeach

{{ $total }} // get total balance for all banks

Edit

If you want to calculate using exchangeRate on amount:

@php($total = 0)
@foreach($banks as $key => $bank)
    $credits = 0;
    $debits = 0;
    @foreach($bank->transactions as $key => $transaction)
        @if($transaction->type  == 'credit')
            $credits += $transaction->amount*$transaction->exchangeRate;
        @elseif($transaction->type  == 'dedit')
            $dedits += $transaction->amount*$transaction->exchangeRate;
        @endif
    @endforeach
    $balance = $credits-$debits;
    $total += $balance;
@endforeach
{{ $total }}

Upvotes: 1

khn Rzk
khn Rzk

Reputation: 1292

You have to fetch it from controller and pass it to the view, Create a function to get data

//Here You will get all transactions relevant to the bank as per your relationship

 $banks =Transaction::with('transactions.bank')->get();
    return view('inc.banks', compact('banks'));

with the array of output you can compute manually the amounts you need, Or else you can do it manually querying it.

To get a clear picture of your output try manually querying it on database with SQL Syntax. Once you got the right SQL syntax you can go for laravel eloquent (This method will help you a lot in understanding what your doing) !! Good Luck !

Upvotes: 1

Related Questions