@php
// Collect all individual items from sales_invoice_items (no aggregation)
$allInvoiceItems = [];
$itemDescriptions = []; // Map item_name to descriptions for NTTN matching
// Create a map of sales_invoice_item_id => nttn_cost_item for quick lookup
$nttnCostItemsMap = [];
foreach($nttnCosts as $nttnCost) {
foreach($nttnCost->items as $nttnCostItem) {
if ($nttnCostItem->sales_invoice_item_id) {
$nttnCostItemsMap[$nttnCostItem->sales_invoice_item_id] = $nttnCostItem;
}
}
}
// Create a map of invoice_id => paid_amount for calculating paid amounts per item
$invoicePaidAmountMap = [];
foreach($salesInvoices as $invoice) {
$paidAmount = $invoice->transections()->where('transaction_type', 'payment')->sum('amount');
$invoicePaidAmountMap[$invoice->id] = $paidAmount;
}
foreach($salesInvoices as $invoice) {
foreach($invoice->items as $item) {
$itemName = $item->item_name;
$connectionTypeId = $item->connection_type_id ?? 'no_type';
$connectionTypeName = $item->connectionType ? $item->connectionType->name : 'No Connection Type';
$connectionTypeNote = $item->note ?? '';
// Get corresponding nttn_cost_item if exists
$nttnCostItem = $nttnCostItemsMap[$item->id] ?? null;
// Get Cost Price and Gross Cost from bandwidth_item_cost_prices table
$costPrice = $itemPricesMap[$itemName]['cost_rate'] ?? 0;
$grossCost = $itemPricesMap[$itemName]['gross_cost'] ?? 0;
// Calculate proportional paid amount for this item
$invoicePaidAmount = $invoicePaidAmountMap[$invoice->id] ?? 0;
$invoiceTotalAmount = $invoice->invoice_amount;
$itemPaidAmount = 0;
if ($invoiceTotalAmount > 0) {
// Calculate proportional paid amount: (item_revenue / invoice_total) * invoice_paid
$itemPaidAmount = ($item->total_amount / $invoiceTotalAmount) * $invoicePaidAmount;
}
// Calculate Cost Qty: If Connection Type is "NTTN Include", Cost Qty = Sales Qty
// Normalize connection type name for matching (remove extra spaces, convert to lowercase)
$connectionTypeNameLower = strtolower(preg_replace('/\s+/', ' ', trim($connectionTypeName)));
// Check if Connection Type contains both "nttn" and "include" (case-insensitive, handle spaces/hyphens)
$isNttnInclude = false;
if (strpos($connectionTypeNameLower, 'nttn') !== false && strpos($connectionTypeNameLower, 'include') !== false) {
$isNttnInclude = true;
} elseif (strpos($connectionTypeNameLower, 'nttn-include') !== false || strpos($connectionTypeNameLower, 'include-nttn') !== false) {
$isNttnInclude = true;
} elseif (strpos($connectionTypeNameLower, 'nttn include') !== false || strpos($connectionTypeNameLower, 'include nttn') !== false) {
$isNttnInclude = true;
}
// If Connection Type is "NTTN Include", Cost Qty = Sales Qty
if ($isNttnInclude) {
$costQtyValue = $item->quantity; // Cost Qty = Sales Qty
} else {
$costQtyValue = $nttnCostItem ? $nttnCostItem->quantity : 0;
}
// Add each item as a separate entry (no aggregation)
$allInvoiceItems[] = [
'item_id' => $item->id,
'item_name' => $itemName,
'bandwidth_item_id' => $item->product_id, // bandwidth_item_id from product_id
'quantity' => $item->quantity, // Sales Qty
'revenue' => $item->total_amount,
'rate' => $item->rate, // Sales Price
'connection_type_id' => $connectionTypeId,
'connection_type_name' => $connectionTypeName,
'connection_type_note' => $connectionTypeNote,
'description' => $item->description ?? '',
// Cost Price and Gross Cost from bandwidth_item_cost_prices
'cost_price' => $costPrice,
'gross_cost' => $grossCost,
// NTTN Cost Item data (only Cost Qty and NTTN Cost)
'cost_qty' => $costQtyValue,
'nttn_cost' => $nttnCostItem ? $nttnCostItem->total_cast_amount : 0,
// Paid Amount (proportional to invoice paid amount)
'paid_amount' => $itemPaidAmount,
'sales_invoice_id' => $invoice->id,
];
// Store descriptions for matching with nttn_links
if ($item->description) {
if (!isset($itemDescriptions[$itemName])) {
$itemDescriptions[$itemName] = [];
}
$itemDescriptions[$itemName][] = $item->description;
}
}
}
// Store link_names for matching with nttn_links (if needed for NTTN items)
$itemLinkNames = []; // Map item_name to link_names for matching
foreach($nttnCosts as $cost) {
foreach($cost->items as $item) {
$itemName = $item->item_name;
if (!isset($itemLinkNames[$itemName])) {
$itemLinkNames[$itemName] = [];
}
// Store link_name for matching with nttn_links
if ($item->link_name) {
$itemLinkNames[$itemName][] = $item->link_name;
}
}
}
// Separate NTTN items and Bandwidth items
$nttnItems = [];
$bandwidthItems = [];
foreach($allInvoiceItems as $itemData) {
$itemName = $itemData['item_name'];
if (stripos($itemName, 'NTTN') !== false || stripos($itemName, 'F@H') !== false || stripos($itemName, 'Summit') !== false) {
$nttnItems[] = $itemData;
} else {
$bandwidthItems[] = $itemData;
}
}
// Group bandwidth items by connection type
$bandwidthItemsByConnectionType = [];
foreach($bandwidthItems as $itemData) {
$connectionTypeName = $itemData['connection_type_name'];
$connectionTypeNote = $itemData['connection_type_note'] ?? '';
if (!isset($bandwidthItemsByConnectionType[$connectionTypeName])) {
$bandwidthItemsByConnectionType[$connectionTypeName] = [
'items' => [],
'note' => $connectionTypeNote,
];
}
$bandwidthItemsByConnectionType[$connectionTypeName]['items'][] = $itemData;
// Update note if we find one (prefer non-empty note)
if ($connectionTypeNote && !$bandwidthItemsByConnectionType[$connectionTypeName]['note']) {
$bandwidthItemsByConnectionType[$connectionTypeName]['note'] = $connectionTypeNote;
}
}
// Create $allItems array for compatibility with existing code
$allItems = array_merge($bandwidthItems, $nttnItems);
$totalRevenue = 0;
$totalCost = 0;
$totalProfit = 0;
$nttnRevenue = 0;
$nttnCost = 0;
$nttnSalesCost = 0;
$nttnProfit = 0;
$bandwidthRevenue = 0;
$bandwidthRevenueWithoutNttn = 0;
$bandwidthRevenueAfterCommission = 0;
$bandwidthCost = 0;
$bandwidthSalesCost = 0;
$bandwidthGrossCost = 0;
$bandwidthProfit = 0;
$bandwidthProfitWithoutGross = 0;
$bandwidthRevenueForMargin = 0;
$bandwidthRevenueForMargin = 0;
$bandwidthPaidAmount = 0;
$totalPaidAmount = 0;
@endphp
@if(count($bandwidthItems) > 0)
Bandwidth Items
| SN |
Item Name |
Sales Qty |
Sales Price |
Sales Revenue |
Paid Amount |
Sales Revenue without NTTN |
Sales Commission Item Price |
Sales Revenue After Commission |
Cost Qty |
Cost Price |
Gross Cost (%) |
Sales Cost |
Gross Cost |
NTTN Cost |
Profit/Loss |
Profit/Loss (Without Gross) |
Margin % |
@php
$rowIndex = 0;
@endphp
@foreach($bandwidthItemsByConnectionType as $connectionTypeName => $connectionTypeData)
@php
$connectionTypeNote = $connectionTypeData['note'] ?? '';
@endphp
|
{{ $connectionTypeName }}@if($connectionTypeNote) - {{ $connectionTypeNote }}@endif
|
@foreach($connectionTypeData['items'] as $itemData)
@php
$itemName = $itemData['item_name'];
$revenueQty = $itemData['quantity']; // Sales Qty
$revenue = $itemData['revenue'];
$salesPrice = $itemData['rate']; // Sales Price (rate from sales_invoice_items)
// Get Sales Commission Item Price by comparing three tables:
// 1. commission_items.bandwidth_item_id
// 2. commission_item_prices.price (from commission_item_prices table)
// 3. sales_invoice_items.product_id
// Match: commission_items.bandwidth_item_id = sales_invoice_items.product_id
$bandwidthItemId = $itemData['bandwidth_item_id'] ?? null; // This is from sales_invoice_items.product_id
$commissionPrice = 0;
// Sales Revenue After Commission = Sales Qty × Sales Commission Item Price
$revenueAfterCommission = 0;
if ($bandwidthItemId && isset($commissionItemPricesMap) && is_array($commissionItemPricesMap)) {
// Convert to integer for consistent type matching
$bandwidthItemIdInt = (int)$bandwidthItemId;
// Look up commission price from map where:
// commission_items.bandwidth_item_id = sales_invoice_items.product_id
// and price comes from commission_item_prices.price
if (isset($commissionItemPricesMap[$bandwidthItemIdInt])) {
$commissionPrice = $commissionItemPricesMap[$bandwidthItemIdInt];
// Calculate: Sales Revenue After Commission = Sales Qty × Sales Commission Item Price
$revenueAfterCommission = $revenueQty * $commissionPrice;
}
}
// Get Cost Price and Gross Cost from bandwidth_item_cost_prices (already in $itemData)
$costPrice = $itemData['cost_price']; // Cost Price from bandwidth_item_cost_prices
$grossCost = $itemData['gross_cost']; // Gross Cost (%) from bandwidth_item_cost_prices
// Get values from nttn_cost_items (already in $itemData)
// Cost Qty: If Connection Type is "NTTN Include", Cost Qty = Sales Qty
// Normalize connection type name for matching (remove extra spaces, convert to lowercase)
$currentConnectionTypeName = strtolower(preg_replace('/\s+/', ' ', trim($connectionTypeName)));
// Check if Connection Type contains both "nttn" and "include" (case-insensitive, handle spaces/hyphens)
$isNttnInclude = false;
if (strpos($currentConnectionTypeName, 'nttn') !== false && strpos($currentConnectionTypeName, 'include') !== false) {
$isNttnInclude = true;
} elseif (strpos($currentConnectionTypeName, 'nttn-include') !== false || strpos($currentConnectionTypeName, 'include-nttn') !== false) {
$isNttnInclude = true;
} elseif (strpos($currentConnectionTypeName, 'nttn include') !== false || strpos($currentConnectionTypeName, 'include nttn') !== false) {
$isNttnInclude = true;
}
// If Connection Type is "NTTN Include", Cost Qty = Sales Qty (override any existing value)
if ($isNttnInclude) {
$costQty = $revenueQty; // Cost Qty = Sales Qty
} else {
$costQty = $itemData['cost_qty']; // Cost Qty from itemData
}
$nttnCost = $itemData['nttn_cost']; // NTTN Cost (total_cast_amount from nttn_cost_items)
// Get Paid Amount from itemData
$paidAmount = $itemData['paid_amount'] ?? 0;
// Calculate Sales Revenue without NTTN = Sales Revenue - NTTN Cost
$revenueWithoutNttn = $revenue - $nttnCost;
// Calculate Sales Cost = Cost Qty × Cost Price (if Cost Qty exists)
// Otherwise: Sales Cost = Sales Qty × Cost Price
if ($costQty > 0) {
$salesCost = $costQty * $costPrice;
} else {
$salesCost = $revenueQty * $costPrice;
}
// Calculate Adjusted Cost Price with Gross Cost
// Adjusted Cost = Cost Price + (Cost Price × Gross Cost % / 100)
$adjustedCostPrice = $costPrice + ($costPrice * $grossCost / 100);
// Calculate Gross Cost amount based on Sales Revenue After Commission or Sales Revenue without NTTN
// If Sales Revenue After Commission exists, calculate percentage on it
// Otherwise, calculate percentage on Sales Revenue without NTTN
if ($revenueAfterCommission > 0) {
// Calculate Gross Cost = Sales Revenue After Commission × (Gross Cost % / 100)
$grossCostAmount = $revenueAfterCommission * ($grossCost / 100);
} else if ($revenueWithoutNttn > 0) {
// Calculate Gross Cost = Sales Revenue without NTTN × (Gross Cost % / 100)
$grossCostAmount = $revenueWithoutNttn * ($grossCost / 100);
} else {
// Fallback: use Sales Revenue if neither exists
$grossCostAmount = $revenue * ($grossCost / 100);
}
// Calculate Profit/Loss based on Sales Revenue After Commission or Sales Revenue
// If Sales Revenue After Commission exists: Profit/Loss = Sales Revenue After Commission - (Sales Cost + Gross Cost)
// Otherwise: Profit/Loss = Sales Revenue - (Sales Cost + Gross Cost + NTTN Cost)
if ($revenueAfterCommission > 0) {
// Profit/Loss = Sales Revenue After Commission - (Sales Cost + Gross Cost)
$profit = $revenueAfterCommission - ($salesCost + $grossCostAmount);
} else {
// Profit/Loss = Sales Revenue - (Sales Cost + Gross Cost + NTTN Cost)
$profit = $revenue - ($salesCost + $grossCostAmount + $nttnCost);
}
// Calculate Total Cost for reference (used in other calculations)
$totalItemCost = $salesCost + $grossCostAmount + $nttnCost;
// Calculate Profit/Loss (Without Gross) based on Sales Revenue After Commission or Sales Revenue
// If Sales Revenue After Commission exists: Profit/Loss (Without Gross) = Sales Revenue After Commission - Sales Cost
// Otherwise: Profit/Loss (Without Gross) = Sales Revenue - (Sales Cost + NTTN Cost)
if ($revenueAfterCommission > 0) {
// Profit/Loss (Without Gross) = Sales Revenue After Commission - Sales Cost
$profitWithoutGross = $revenueAfterCommission - $salesCost;
} else {
// Profit/Loss (Without Gross) = Sales Revenue - (Sales Cost + NTTN Cost)
$profitWithoutGross = $revenue - ($salesCost + $nttnCost);
}
// Calculate Margin % based on the revenue used for profit calculation
$revenueForMargin = $revenueAfterCommission > 0 ? $revenueAfterCommission : $revenue;
$margin = $revenueForMargin > 0 ? round(($profit / $revenueForMargin) * 100, 2) : 0;
$bandwidthRevenue += $revenue;
$bandwidthRevenueWithoutNttn += $revenueWithoutNttn;
$bandwidthRevenueAfterCommission += $revenueAfterCommission;
$bandwidthCost += $nttnCost;
$bandwidthSalesCost += $salesCost;
$bandwidthGrossCost += $grossCostAmount;
$bandwidthProfit += $profit;
$bandwidthProfitWithoutGross += $profitWithoutGross;
$bandwidthPaidAmount += $paidAmount;
// Track revenue for margin calculation
if ($revenueAfterCommission > 0) {
$bandwidthRevenueForMargin += $revenueAfterCommission;
} else {
$bandwidthRevenueForMargin += $revenue;
}
$totalRevenue += $revenue;
$totalCost += $nttnCost;
$totalProfit += $profit;
$totalPaidAmount += $paidAmount;
@endphp
| {{ $rowIndex + 1 }} |
{{ $itemName }} |
{{ number_format($revenueQty, 2) }}
|
৳ {{ number_format($salesPrice, 2) }}
|
৳ {{ number_format($revenue, 2) }}
|
৳ {{ number_format($paidAmount, 2) }}
|
৳ {{ number_format($revenueWithoutNttn, 2) }}
|
@if(isset($commissionPrice) && $commissionPrice > 0)
৳ {{ number_format($commissionPrice, 2) }}
@else
-
@endif
|
৳ {{ number_format($revenueAfterCommission, 2) }}
|
{{ number_format($costQty, 2) }}
|
|
|
৳ {{ number_format($salesCost, 2) }}
|
৳ {{ number_format($grossCostAmount, 2) }}
|
৳ {{ number_format($nttnCost, 2) }}
|
৳ {{ number_format($profit, 2) }}
|
৳ {{ number_format($profitWithoutGross, 2) }}
|
{{ number_format($margin, 2) }}%
|
@php $rowIndex++; @endphp
@endforeach
@endforeach
| Bandwidth Total: |
|
৳ {{ number_format($bandwidthRevenue, 2) }}
|
৳ {{ number_format($bandwidthPaidAmount, 2) }}
|
৳ {{ number_format($bandwidthRevenueWithoutNttn, 2) }}
|
|
৳ {{ number_format($bandwidthRevenueAfterCommission, 2) }}
|
|
|
|
৳ {{ number_format($bandwidthSalesCost, 2) }}
|
৳ {{ number_format($bandwidthGrossCost, 2) }}
|
৳ {{ number_format($bandwidthCost, 2) }}
|
৳ {{ number_format($bandwidthProfit, 2) }}
|
৳ {{ number_format($bandwidthProfitWithoutGross, 2) }}
|
{{ $bandwidthRevenueForMargin > 0 ? number_format(($bandwidthProfit / $bandwidthRevenueForMargin) * 100, 2) : 0 }}%
|
@endif
@if(count($nttnItems) > 0)
NTTN Items
| SN |
Item Name |
Sales Qty |
Sales Price |
Sales Revenue |
Cost Qty |
Cost Price |
Gross Cost (%) |
Sales Cost |
Profit/Loss |
Margin % |
@php
$rowIndex = 0;
@endphp
@foreach($nttnItems as $itemData)
@php
$itemName = $itemData['item_name'];
$revenueQty = $itemData['quantity']; // Sales Qty
$revenue = $itemData['revenue'];
$salesPrice = $itemData['rate']; // Sales Price (rate from sales_invoice_items)
// Get values from nttn_cost_items (already in $itemData)
$costQty = $itemData['cost_qty']; // Cost Qty from nttn_cost_items
$itemNttnCost = $itemData['nttn_cost']; // NTTN Cost (total_cast_amount from nttn_cost_items)
// Find all matching nttn_links for this item and calculate average
$matchedNttnLinks = [];
// First, try to match by link_name from nttn_cost_items (most accurate)
$linkNames = $itemLinkNames[$itemName] ?? [];
if (!empty($linkNames)) {
foreach($nttnLinks as $link) {
// Check if any stored link_name matches exactly
foreach($linkNames as $storedLinkName) {
if (trim(strtolower($storedLinkName)) === trim(strtolower($link->link_name))) {
$matchedNttnLinks[] = $link;
break; // Break inner loop, continue outer
}
}
}
}
// If no match found, try by item descriptions (from sales invoice items)
if (empty($matchedNttnLinks)) {
$descriptions = $itemDescriptions[$itemName] ?? [];
foreach($nttnLinks as $link) {
// Check if any description matches link_name
foreach($descriptions as $description) {
if (stripos($description, $link->link_name) !== false ||
stripos($link->link_name, $description) !== false) {
$matchedNttnLinks[] = $link;
break; // Break inner loop, continue outer
}
}
}
}
// Final fallback: check if item name contains link_name or vice versa
if (empty($matchedNttnLinks)) {
foreach($nttnLinks as $link) {
if (stripos($itemName, $link->link_name) !== false ||
stripos($link->link_name, $itemName) !== false) {
$matchedNttnLinks[] = $link;
}
}
}
// Calculate cost price from individual links
// For each link: cast = cast_rate + (cast_rate * cast_vat / 100)
// Sum all casts and divide by total quantity
if (!empty($matchedNttnLinks)) {
$totalCastAmount = 0;
$totalQuantity = 0;
foreach($matchedNttnLinks as $link) {
// Calculate cast for each individual link
$individualCast = $link->cast_rate + ($link->cast_rate * $link->cast_vat / 100);
// Add to total cast amount (cast * quantity for this link)
$totalCastAmount += $individualCast * $link->quantity;
$totalQuantity += $link->quantity;
}
// Calculate average cost price: total cast amount / total quantity
$defaultCostPrice = $totalQuantity > 0 ? $totalCastAmount / $totalQuantity : 0;
} else {
// Fallback 1: try direct match in nttnLinksMap
if (isset($nttnLinksMap[$itemName]) && $nttnLinksMap[$itemName]['cost_price'] > 0) {
$defaultCostPrice = $nttnLinksMap[$itemName]['cost_price'];
} else {
$defaultCostPrice = 0;
}
}
// Use cost price from nttn_links table (or fallback)
$costPrice = $defaultCostPrice;
// Gross cost for NTTN items is typically 0 or not applicable
$defaultGrossCost = 0;
// Calculate Sales Cost = Cost Price × Sales Quantity
$salesCost = $costPrice * $revenueQty;
// Calculate Total Cost = Sales Cost + NTTN Cost
$totalItemCost = $salesCost + $itemNttnCost;
// Calculate Profit/Loss = Sales Revenue - (Sales Cost + NTTN Cost)
$profit = $revenue - $totalItemCost;
$margin = $revenue > 0 ? round(($profit / $revenue) * 100, 2) : 0;
$nttnRevenue += $revenue;
$nttnSalesCost += $salesCost; // Accumulate NTTN sales cost
$nttnCost += $itemNttnCost; // Accumulate NTTN cost for this item
$nttnProfit += $profit;
@endphp
| {{ $rowIndex + 1 }} |
{{ $itemName }} |
{{ number_format($revenueQty, 2) }}
|
৳ {{ number_format($salesPrice, 2) }}
|
৳ {{ number_format($revenue, 2) }}
|
{{ number_format($costQty, 2) }}
|
|
|
৳ {{ number_format($salesCost, 2) }}
|
৳ {{ number_format($profit, 2) }}
|
{{ number_format($margin, 2) }}%
|
@php $rowIndex++; @endphp
@endforeach
| NTTN Total: |
|
৳ {{ number_format($nttnRevenue, 2) }}
|
|
|
|
৳ 0.00
|
৳ {{ number_format($nttnProfit, 2) }}
|
{{ $nttnRevenue > 0 ? number_format(($nttnProfit / $nttnRevenue) * 100, 2) : 0 }}%
|
@endif
৳ {{ number_format($bandwidthRevenue + $nttnRevenue, 2) }}
Sales Revenue
৳ {{ number_format($bandwidthRevenueWithoutNttn + $nttnRevenue, 2) }}
Sales Revenue without NTTN
৳ {{ number_format($bandwidthRevenueAfterCommission, 2) }}
Sales Revenue After Commission
৳ {{ number_format($bandwidthSalesCost + $nttnSalesCost, 2) }}
Sales Cost
৳ {{ number_format($bandwidthGrossCost, 2) }}
Gross Cost
৳ {{ number_format($bandwidthCost + $nttnCost, 2) }}
NTTN Cost
৳ {{ number_format($bandwidthProfit + $nttnProfit, 2) }}
Profit/Loss
৳ {{ number_format($bandwidthProfitWithoutGross + $nttnProfit, 2) }}
Profit/Loss (Without Gross)
@if(count($allItems) > 0)
@else
No data available for profit/loss calculation
@endif