I have 4 tables with timestamps of laravel:
- orders - order_code, user_id, invoicer_id, moderator_id, affiliate_id, payable_amount, product_id
- order_invoicer_shippings - orer_code, invoicer_id, shipping_weight, shipping_amount
- order_commissions - order_code, moderator_id, affiliate_id, moderator_commission_amount, affiliate_commission_amount
- 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