# Proportional Discount Allocation Implementation Guide ## Overview This document outlines the implementation of a proportional discount allocation system where invoice-level discounts are distributed across items based on their contribution to the total invoice value. --- ## 1. Database Schema Changes ### Add New Fields to `invoice_detail` Table ```sql ALTER TABLE invoice_detail ADD COLUMN allocated_discount DECIMAL(10,2) DEFAULT 0.00 AFTER discount, ADD COLUMN net_price DECIMAL(10,2) DEFAULT 0.00 AFTER allocated_discount, ADD COLUMN tax_amount DECIMAL(10,2) DEFAULT 0.00 AFTER net_price, ADD COLUMN total_with_tax DECIMAL(10,2) DEFAULT 0.00 AFTER tax_amount; ``` **Field Descriptions:** - `allocated_discount`: The item's proportional share of the invoice-level discount - `net_price`: Item subtotal minus allocated discount (before tax) - `tax_amount`: 15% VAT calculated on net_price - `total_with_tax`: Final amount for this item (net_price + tax_amount) --- ## 2. Backend Implementation ### 2.1 Invoice Creation (SalesController.php / InvoiceController.php) Update the `createInvoice` method to calculate and store proportional allocations: ```php // After receiving items and calculating totals $items = $request->items; $invoiceDiscount = $request->salesDiscount; // Total invoice discount $discountType = $request->discountType; // 'percentage' or 'fixed' $discountValue = $request->discountValue; // percentage or EGP amount // Calculate total before discount $totalBeforeDiscount = 0; foreach ($items as $index => $item) { $totalBeforeDiscount += $rtps[$index] * $qtys[$index]; } // Calculate invoice discount amount if ($discountType === 'percentage') { $invoiceDiscount = $totalBeforeDiscount * ($discountValue / 100); } else { $invoiceDiscount = $discountValue; // Fixed amount } // Calculate proportional allocation for each item $itemAllocations = []; foreach ($items as $index => $item) { $itemSubtotal = $rtps[$index] * $qtys[$index]; $itemShareRatio = $totalBeforeDiscount > 0 ? $itemSubtotal / $totalBeforeDiscount : 0; // Allocate proportional discount $allocatedDiscount = $invoiceDiscount * $itemShareRatio; // Calculate net price (after discount, before tax) $netPrice = $itemSubtotal - $allocatedDiscount; // Calculate tax on net price $taxAmount = $netPrice * 0.15; // 15% VAT // Calculate total with tax $totalWithTax = $netPrice + $taxAmount; $itemAllocations[$index] = [ 'allocated_discount' => round($allocatedDiscount, 2), 'net_price' => round($netPrice, 2), 'tax_amount' => round($taxAmount, 2), 'total_with_tax' => round($totalWithTax, 2), ]; } // Save to database for ($i = 0; $i < count($items); $i++) { $invoiceDetail = new InvoiceDetail; $invoiceDetail->invo_no = $invoNo; $invoiceDetail->loc_id = $locId; $invoiceDetail->serial = $serialNo; $invoiceDetail->item_id = Item::where('barcode', $items[$i])->pluck('id')->first(); $invoiceDetail->qty = $qtys[$i]; $invoiceDetail->rtp = $rtps[$i]; $invoiceDetail->cost = $costs[$i]; $invoiceDetail->discount = $discounts[$i]; // Keep original item discount if any $invoiceDetail->allocated_discount = $itemAllocations[$i]['allocated_discount']; $invoiceDetail->net_price = $itemAllocations[$i]['net_price']; $invoiceDetail->tax_amount = $itemAllocations[$i]['tax_amount']; $invoiceDetail->total_with_tax = $itemAllocations[$i]['total_with_tax']; $invoiceDetail->type = $invoiceType; $invoiceDetail->invoice_header_id = $invoiceHeader->id; $invoiceDetail->save(); $serialNo++; } ``` ### 2.2 Return Controller Update to use allocated discount from database: ```php public function processReturn(Request $request) { // ... existing code ... // Fetch original invoice details with allocated discounts $originalInvoice = InvoiceHeader::with('details')->find($invoiceId); $totalRefund = 0; foreach ($request->items as $itemId => $quantity) { $originalDetail = $originalInvoice->details->firstWhere('item_id', $itemId); if ($originalDetail) { // Calculate refund based on stored values $itemRefund = ($originalDetail->net_price + $originalDetail->tax_amount) * ($quantity / $originalDetail->qty); $totalRefund += $itemRefund; } } // Create return invoice with negative values // ... rest of return logic ... } ``` ### 2.3 Exchange Controller Update to calculate balance using allocated discounts: ```php public function processExchange(Request $request) { // Calculate refund for returned items $returnRefund = 0; foreach ($request->returnedItems as $item) { $returnRefund += $item['net_price'] + $item['tax_amount']; } // Calculate cost for new items $newItemsCost = 0; foreach ($request->newItems as $item) { $itemNet = $item['price'] * $item['quantity']; $itemTax = $itemNet * 0.15; $newItemsCost += $itemNet + $itemTax; } // Calculate difference $balanceDue = $newItemsCost - $returnRefund; // ... rest of exchange logic ... } ``` --- ## 3. Frontend Implementation ### 3.1 Update Invoice Display Component File: `resources/js/components/Pages/InvoiceDisplay.tsx` Add new columns to the receipt table: ```typescript interface InvoiceItem { id: number; item: { id: number; barcode: string; description: string; }; qty: number; rtp: number; discount: number; // Original item discount allocated_discount?: number; // NEW: Proportional share of invoice discount net_price?: number; // NEW: Price after all discounts tax_amount?: number; // NEW: 15% tax on net price total_with_tax?: number; // NEW: Final total for this item } // Update table structure {invoiceDetails.map((item, index) => ( ))}
# Item Price Qty Allocated Disc. Net Price Tax (15%) Total
{index + 1} {item.item.description} {formatCurrency(item.rtp * item.qty)} {item.qty} {formatCurrency(item.allocated_discount || 0)} {formatCurrency(item.net_price || 0)} {formatCurrency(item.tax_amount || 0)} {formatCurrency(item.total_with_tax || 0)}
; ``` ### 3.2 Update Return Summary File: `resources/js/components/Pages/Returns/ReturnSummary.tsx` Update calculations to use allocated discounts: ```typescript // Calculate totals based on selected items with allocated discounts const subtotal = selectedItemsData.reduce( (sum, item) => sum + item.price * item.selectedQuantity, 0 ); // Use allocated discount from original invoice const discountTotal = selectedItemsData.reduce( (sum, item) => sum + (item.allocated_discount || 0) * item.selectedQuantity, 0 ); // Calculate tax on net price const taxAmount = selectedItemsData.reduce((sum, item) => { const netPrice = item.price * item.selectedQuantity - (item.allocated_discount || 0) * item.selectedQuantity; return sum + netPrice * 0.15; }, 0); const netValue = subtotal - discountTotal + taxAmount; ``` --- ## 4. Receipt Structure ### 4.1 Sales Receipt ``` ===================================== INVOICE #123 ===================================== Customer: John Doe Date: 2025-01-03 ------------------------------------- Item Price Qty Total ------------------------------------- F SHIRT 950.00 × 1 950.00 Allocated Disc: -60.00 Net Price: 890.00 Tax (15%): 133.50 Item Total: 1,023.50 SUT FORMAL 5,400.00 × 2 10,800.00 Allocated Disc: -240.00 Net Price: 10,560.00 Tax (15%): 1,584.00 Item Total: 12,144.00 ===================================== Subtotal: 11,750.00 Invoice Discount: -300.00 Net Total: 11,450.00 Tax (15%): 1,717.50 ------------------------------------- TOTAL TO PAY: EGP 13,167.50 ===================================== Payment: Cash ``` ### 4.2 Return Receipt ``` ===================================== RETURN RECEIPT Original Invoice: #123 ===================================== ------------------------------------- Returned Items ------------------------------------- F SHIRT 1 item returned Original Price: 950.00 Allocated Disc: -60.00 Net Price: 890.00 Refunded Tax (15%): 133.50 Refund Amount: 1,023.50 ===================================== TOTAL REFUND: EGP 1,023.50 ===================================== ``` ### 4.3 Exchange Receipt ``` ===================================== EXCHANGE RECEIPT Original Invoice: #123 ===================================== RETURNED ITEMS: F SHIRT (returned) 1,023.50 NEW ITEMS: T SHIRT 580.00 Tax (15%): 87.00 Total: 667.00 ===================================== Returned Total: 1,023.50 New Items Total: 667.00 ------------------------------------- REFUND DUE: EGP 356.50 ===================================== ``` --- ## 5. Testing Scenarios ### Test Case 1: 10% Invoice Discount - Items: 2 × 1,000 EGP = 2,000 EGP - Discount: 10% = 200 EGP - Item 1 allocation: 100 EGP - Item 2 allocation: 100 EGP - Each item net: 900 EGP - Each item tax: 135 EGP - Each item total: 1,035 EGP - **Total: 2,070 EGP** ### Test Case 2: Fixed 300 EGP Discount - Item 1: 950 EGP (38.83% of 2,445) - Item 2: 1,495 EGP (61.17% of 2,445) - Total before discount: 2,445 EGP - Item 1 allocation: 116.50 EGP (38.83% of 300) - Item 2 allocation: 183.50 EGP (61.17% of 300) - Item 1 net: 833.50 EGP - Item 2 net: 1,311.50 EGP - **Total: 2,466.75 EGP** (after tax) ### Test Case 3: Return One Item from Multi-Item Invoice - Original invoice: 2 items, 300 EGP total discount - Item 1: allocated discount 116.50 EGP - Return Item 1 only - Refund: (950 - 116.50) × 1.15 = 958.53 EGP --- ## 6. Migration Strategy ### Phase 1: Database Update 1. Run ALTER TABLE migration 2. Backfill existing invoices (optional, or leave as NULL for historical data) ### Phase 2: Backend Update 1. Update InvoiceController 2. Update SalesController 3. Update ReturnController 4. Update ExchangeController ### Phase 3: Frontend Update 1. Update InvoiceDisplay component 2. Update ReturnSummary component 3. Update receipt printing logic ### Phase 4: Testing 1. Test new invoice creation with % discount 2. Test new invoice creation with fixed discount 3. Test returns with allocated discounts 4. Test exchanges with allocated discounts 5. Test receipt printing --- ## 7. Backward Compatibility For existing invoices without `allocated_discount`: - Fallback to using `discount` field directly - Display "N/A" or calculate on-the-fly if needed - Add migration script to populate historical data if required ```php // Fallback logic in calculations $allocatedDiscount = $item->allocated_discount ?? $item->discount ?? 0; ``` --- ## Notes - Always round to 2 decimal places - Consider edge cases (zero-value items, 100% discounts) - Test with multiple payment methods - Ensure proper handling of negative values in returns - Validate that sum of allocated discounts equals invoice discount