Laravel - Sums, Complex Relationships and Models Issue

I'm stuck at something I can't seem to readily wrap my mind around. I've got a customer index table that I would like to be able to show the current balance a customer has by comparing the payments made to bills against their total, and then sum up all of the remaining amounts.

At the moment my customer model looks like this (specifically for this question):

public function billToShipments()
{
    return $this->hasMany(Shipment::class, 'bill_to');
}

and then my shipment model looks like this (in relation to the payment distributions):

        public function paymentDistributions(){
    return $this->hasMany('App\Payments_Distribution', 'shipment_id','pro_number');
}

These are the necessary fields related to this specific question:

Under Payment Distributions -pro_number -amount (of distribution)

Under Shipments -balance_due -bill_to (which is the customer id)

So in the end, what I'd like to be able to do is get the sum of all balance_due's of bills that have less payment_distributions than the balance due for the customer.

For example, in the shipments (pretend under one customer):

SHIPMENT ID   |    Balance_Due
1234          |    10.00
1235          |    20.00
1236          |    30.00

and in the payment_distributions table:

PRO_NUMBER    |    AMOUNT
1234          |    2.00
1234          |    4.00
1235          |    20.00
1236          |    28.00

And then on the customer I'd like to say that they have a $6.00 balance (because 10.00 minus (2.00 plus 4.00) equals 4.00 for shipment # 1234, $20.00 pays off shipment #1235 completely and 2.00 remains for shipment #1236.

If you need any further clarification just ask, I know I might have made it a bit longer than it needed to be but I'm not sure how to go about this. Again, I'd like to use the balance in a table of customers (under an @foreach statement) to give a balance sheet.

I'd appreciate the help!

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2pJk3Wo
via IFTTT