<table>
    <thead>
        <tr>
            <th rowspan="2" align="center">No</th>
            <th rowspan="2" align="center">Project ID</th>
            <th rowspan="2" align="center">Project Name</th>
            <th rowspan="2" align="center">Sales</th>
            <th rowspan="2" align="center">Cost</th>
            <th rowspan="2" align="center">Profit</th>
            <th colspan="3" align="center"><b>Commission</b></th>
        </tr>
        <tr>
            <th>Name</th>
            <th>Percentage Calculation</th>
            <th>Amount</th>
        </tr>
    </thead>
    <tbody>
    @php
        $numberSequence = 1;
        $moreThanOneStatus = false;
        $moreThanOne = array();
    @endphp
    @foreach($projects as $project)

        @php
            $agreementSalesAmount = 0;
            $agreementCostAmount = 0;
            $variationOrderSalesAmount = 0;
            $variationOrderCostAmount = 0;
            $supplierInvoiceCostAmout = 0;

            // Check Agreement
            $agreement = \App\Models\Agreement::where('project_id', $project->id)->first();
            if ($agreement) {
                if ($agreement->status == 'approved' || $agreement->status == 'accepted') {
                    $agreementItem = \App\Models\AgreementItem::where('agreement_id', $agreement->id)->where('revision', 'like', '%REV%')->orderBy('created_at', 'desc')->first();
                    $agreementItemLists = \App\Models\AgreementItemList::where('agreement_item_id', $agreementItem->id)->with('margin')->get();

                    if ($agreementItemLists->count() > 0) {
                        foreach ($agreementItemLists as $agreementItemList) {
                            if ($agreementItemList->tier_level == 1) {
                                $agreementSalesAmount += $agreementItemList->amount;
                            }
                            if(!empty($agreementItemList->margin)) {
                                foreach ($agreementItemList->margin as $agreementItemMargin) {
                                    $agreementCostAmount += $agreementItemList->quantity*($agreementItemMargin->price * $agreementItemMargin->quantity);
                                }
                            } 
                        }
                        if ($agreement->discount) {
                            $agreementSalesAmount = $agreementSalesAmount - ($agreementSalesAmount * ($agreement->discount / 100));
                        }
                    }
                }
            }

            // Check Variation
            $variationOrders = \App\Models\VariationOrder::where('project_id', $project->id)->get();
            if ($variationOrders->count() > 0) {
                foreach ($variationOrders as $variationOrder) {
                    if ($variationOrder->status == 'accepted') {
                        $variationOrderItem = \App\Models\VariationOrderItem::where('variation_order_id', $variationOrder->id)->where('revision', 'like', '%REV%')->orderBy('created_at', 'desc')->first();
                        $variationOrderItemLists = \App\Models\VariationOrderItemList::where('variation_order_item_id', $variationOrderItem->id)->with('margin')->get();

                        if ($variationOrderItemLists->count() > 0) {
                            foreach ($variationOrderItemLists as $variationOrderItemList) {
                                if ($variationOrderItemList->tier_level == 1) {
                                    $variationOrderSalesAmount += $variationOrderItemList->amount;
                                }
                                if(!empty($variationOrderItemList->margin)) {
                                    foreach ($variationOrderItemList->margin as $variationOrderItemMargin) {
                                        $variationOrderCostAmount += $variationOrderItemList->quantity * ($variationOrderItemMargin->price * $variationOrderItemMargin->quantity);
                                    }
                                } 
                            }
                            if ($variationOrder->discount) {
                                $variationOrderSalesAmount = $variationOrderSalesAmount - ($variationOrderSalesAmount * ($variationOrder->discount / 100));
                            }
                        }
                    }
                }
            }

            $supplierInvoices = \App\Models\SupplierInvoice::where('project_id', $project->id)->where('is_approved', true)->get();
            if ($supplierInvoices->count() > 0) {
                foreach ($supplierInvoices as $supplierInvoice) {
                    $supplierInvoiceCostAmout += $supplierInvoice->amount;
                }
            }
            
            $totalSales = $agreementSalesAmount + $variationOrderSalesAmount;
            $totalCost = $supplierInvoiceCostAmout;
            $totalProfit = $totalSales-$totalCost;

        @endphp

        <tr>
            <td>{{ $numberSequence }}</td>
            <td>{{ $project->project_no }}</td>
            <td>{{ $project->name ?? '' }}</td>
            <td>${{ number_format($totalSales, 2) }}</td>
            <td>${{ number_format($totalCost, 2) }}</td>
            <td>${{ number_format($totalProfit, 2) }}</td>
            @if ($project->member_commissions_count > 1)
                @php
                    $index = 0;
                @endphp
                @foreach ($project->memberCommissions as $memberCommission)
                    @php
                        $newPercentage = ($memberCommission->commission/100)*($memberCommission->user->commission/100) * 100;
                        $newCommission = ($newPercentage / 100) * $totalProfit;
                    @endphp
                    @if($loop->first)
                        <td>{{$memberCommission->user->name}}</td>
                        <td>{{$newPercentage}}%</td>
                        <td>${{number_format($newCommission, 2)}}</td>
                    @else
                        @php
                            $moreThanOneStatus = true;
                            $moreThanOne[$index]['name'] = $memberCommission->user->name;
                            $moreThanOne[$index]['commission'] = $newPercentage;
                            $moreThanOne[$index]['amount'] = number_format($newCommission, 2);
                            $index++;
                        @endphp   
                    @endif
                @endforeach 
            @elseif ($project->member_commissions_count == 1)
                @foreach ($project->memberCommissions as $memberCommission)
                    @php
                        $newCommission = ($memberCommission->commission / 100) * $totalProfit;
                    @endphp
                    <td>{{$memberCommission->user->name}}</td>
                    <td>{{$memberCommission->commission}}%</td>
                    <td>${{number_format($newCommission, 2)}}</td>
                @endforeach
            @else
                <td colspan="3" align="center">- Empty -</td>
            @endif
        </tr>
        @if ($moreThanOneStatus)
            @foreach ((array) $moreThanOne as $key => $item)
                <tr>
                    <td colspan="6" align="center"></td>
                    <td>{{$item['name']}}</td>
                    <td>{{$item['commission']}}%</td>
                    <td>${{$item['amount']}}</td>
                </tr>
            @endforeach
        @endif
        @php
            $numberSequence++;
            $moreThanOneStatus = false;
            unset($moreThanOne);
        @endphp
    @endforeach
    </tbody>
</table>
No	Project ID	Project Name	Sales	Cost	Profit	Commission
No	Project ID	Project Name	Sales	Cost	Profit	Name	Percentage Calculation	Amount
{{ $numberSequence }}	{{ $project->project_no }}	{{ $project->name ?? '' }}	${{ number_format($totalSales, 2) }}	${{ number_format($totalCost, 2) }}	${{ number_format($totalProfit, 2) }}	{{$memberCommission->user->name}}	{{$newPercentage}}%	${{number_format($newCommission, 2)}}	{{$memberCommission->user->name}}	{{$memberCommission->commission}}%	${{number_format($newCommission, 2)}}	- Empty -
						{{$item['name']}}	{{$item['commission']}}%	${{$item['amount']}}