Calculation of values from different database tables in laravel

I have 4 tables with timestamps of laravel:

1. orders - order_code, user_id, invoicer_id, moderator_id, affiliate_id, payable_amount, product_id
2. order_invoicer_shippings - orer_code, invoicer_id, shipping_weight, shipping_amount
3. order_commissions - order_code, moderator_id, affiliate_id, moderator_commission_amount, affiliate_commission_amount
4. order_payments - order_code, invoicer_id, moderator_id, affiliate_id, credit, deductions, payments

Scenario: On placing the order, order details are inserted in the orders table, shipping details are inserted in the order_invoicer_shippings table. Also the commissions on the orders and/or products are inserted in the order_commissions_table. Now admin decides to pay them (affiliate, moderator) their dues (shipping amount, commissions), for that he wants to know the balance of each user.

I want to show table data in descending order of created_at field. It should be displayed in the descending order of the event that is happening, irrespective of the order placed and/or task done by the admin.

Along with this, there is a calculation that should happen. Calculation of the current balance of the user (affiliate, moderator).

Formula for calculating the current balance is:

Available Balance + Credit - Deductions - Payments

Example: order_invoicers_table contains:

invoicer_id    shipping_weight    shipping_amount    created_at
    1                50                100         2016-03-18 03:05:32
    2                75                150         2016-03-20 11:50:05
    1               150                300         2016-03-25 06:35:30

order_commissions_table contains:

moderator_id    moderator_comm_amount    affiliate_id    affiliate_comm_amount    created_at
    1                   150                  5                   200            2016-03-18 03:05:32
    9                   350                  4                   225            2016-03-20 11:50:05
    6                   300                  3                   205            2016-03-25 06:35:30

Now in admin, it should be like this for user_id: 1:

details    payable_amt    credits    deductions    payments    balance    created_at
shipping      200            200         0            0          200    2016-03-18 03:05:32
mod_comm      150            150         0            0          350    2016-03-18 03:05:32

Admin now decides to pay the above user (id: 1) the shipping amount (Desired Output):

details    payable_amt    credits    deductions    payments    balance    created_at
payment                                              200         150    2016-03-24 08:08:10
mod_comm      150            150         0            0          350    2016-03-18 03:05:32
shipping      200            200         0            0          200    2016-03-18 03:05:32

The payments/credits/deductions gets inserted in the order_payments table.

The code that I have tried so far is:

Controller:

public function fetchUser($userCode)
{
    $member = User::where('code', $userCode)->first();

    $accountTypes = $member->getAllAccountTypes();

    $invoicerOrders = $moderatorOrders = $affiliateOrders = null;

    foreach($accountTypes as $key => $account) {
        if($account->type === 'invoicer') {
            $invoicerOrders = Order::with('invoicerShippings')
                          ->where('invoicer_id', $account->pivot->user_id)
                          ->latest()->groupBy('code')->get();
        }

        if($account->type === 'moderator') {
            $moderatorOrders = Order::where('moderator_id', $account->pivot->user_id)
                           ->latest()->groupBy('code')->get();
        }

        if($account->type === 'affiliate') {
            $affiliateOrders = Order::where('affiliate_id', $account->pivot->user_id)
                           ->latest()->groupBy('code')->get();
        }
    }

    $allOrders = [
        'invoicerOrders'           => $invoicerOrders,
        'principalModeratorOrders' => $principalModeratorOrders,
        'affiliateOrders'          => $affiliateOrders,
   ];

    return view('admin.orders.payments.user', compact('allOrders', 'member'));
}

View File:

<?php
$totalPayableWeight = $totalNonPayableWeight = $payableWeight = $nonPayableWeight = 0;
$totalPayableAmount = $totalNonPayableAmount = $payableAmount = $nonPayableAmount = 0;
$totalPayableCommissionAmount = $totalNonPayableCommissionAmount = $payableCommissionAmount = $nonPayableCommissionAmount = 0;
$totalBalance = 0;
?>
@if($allOrders['invoicerOrders'] !== null)

    @foreach($allOrders['invoicerOrders'] as $key => $order)
        <?php
        $tempInvPayOrder = App\OrderCommission::where('invoicer_id', $member->id)
                           ->orderBy('created_at', 'DESC')->get();
        $balance = 0;

        foreach($tempInvPayOrder as $ord) {
           $o = App\Order::find($ord->order_id);
           if($o->product_payability !== 'Non-Payable') {
               $payableCommissionAmount = $ord->seller_total_commission_amount;
               $totalPayableCommissionAmount += $payableCommissionAmount;
           }

           if($o->product_payability === 'Non-Payable') {
               $nonPayableCommissionAmount = $ord->seller_total_commission_amount;
               $totalNonPayableCommissionAmount += $nonPayableCommissionAmount;
           }
       }
   ?>
   <tr>
       <td>{{ $order->code }} / Invoicer Shipping</td>
       <td>{{ $totalPayableCommissionAmount + $totalNonPayableCommissionAmount }}</td>
       <td>{{ $totalPayableCommissionAmount }}</td>
       <td>{{ $totalNonPayableCommissionAmount }}</td>
       <td>{{ $totalPayableCommissionAmount }}</td>
       <td>
           <?php
           $balance += $totalPayableCommissionAmount;
           $invoicerTotalPaymentBalance += $balance;
           ?>
           {{ $totalBalance = $invoicerTotalPaymentBalance }}
       </td>
       <td>
           {{ App\OrderCommission::where('invoicer_id', $member->id)->where('order_code', $order->code)->first()->created_at }}
       </td>
   </tr>
@endforeach


<?php
$moderatorTotalPayableCommissionAmount = $moderatorTotalNonPayableCommissionAmount = $moderatorPayableCommissionAmount = $moderaotrNonPayableCommissionAmount = 0;
 ?>
 @if($allOrders['moderatorOrders'] !== null && ! $allOrders['moderatorOrders']->isEmpty())
     @foreach($allOrders['moderatorOrders'] as $key => $order)
         <?php
         $tempOrderCommissions = App\OrderCommission::where('moderator_id', $member->id)->latest()->get();

         foreach($tempOrderCommissions as $ord) {
             $o = App\Order::find($ord->order_id);
             if($o->product_payability !== 'Non-Payable') {
                 $moderatorPayableCommissionAmount = $ord->moderator_total_commission_amount;
                 $moderatorTotalPayableCommissionAmount += $moderatorPayableCommissionAmount;
             }

             if($o->product_payability === 'Non-Payable') {
                 $moderaotrNonPayableCommissionAmount = $ord->moderator_total_commission_amount;
                 $moderatorTotalNonPayableCommissionAmount += $moderaotrNonPayableCommissionAmount;
             }
        }
        ?>
        <tr>
            <td>{{ $order->code }} / Moderator Commission</td>
            <td>{{ $moderatorTotalPayableCommissionAmount + $moderatorTotalNonPayableCommissionAmount }}</td>
            <td>{{ $moderatorTotalPayableCommissionAmount }}</td>
            <td>{{ $moderatorTotalNonPayableCommissionAmount }}</td>
            <td>{{ $moderatorTotalPayableCommissionAmount }}</td>
            <td>{{ $tempOrderCommissions->first()->created_at }}</td>
        </tr>
    @endforeach
@endif

Sorry for such a long question, but I had to give the detailed info in order to solve the issue..

Any help is highly appreciated.

via Chebli Mohamed