Buy and Return Bill Controller Documentation

File: /controllers/buyAndReturnBillController.php

Purpose: Manages combined buy and return purchase transactions, handling both buy operations and return operations within a single bill

Last Updated: December 20, 2024

Total Functions: 25+

Lines of Code: ~2,100

---

📋 Overview

The Buy and Return Bill Controller handles complex purchase transactions that include both buying new products and returning previously purchased products within a single bill. This controller manages:

• Combined buy and return operations on a single invoice
• Product serial number generation and tracking
• Store inventory management with quantity increases/decreases
• Supplier debt tracking and multi-currency support
• Average purchase price calculations
• Buy price history maintenance
• Cost center and store detail reporting
• Safe/cash register operations

Primary Functions

✅ Combined buy and return bill processing
✅ Product serial number generation
✅ Store quantity management (increase/decrease)
✅ Supplier debt tracking in multiple currencies
✅ Average purchase price calculations
✅ Product price history maintenance
✅ Store reporting and audit trails
✅ Safe/cash register transactions
✅ Cost center accounting
✅ Size/color variant management

Related Controllers

• buyBillController.php - Pure purchase operations
• returnBuyBillController.php - Pure return operations
• productController.php - Product management
• supplierController.php - Supplier management
• storeController.php - Store management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
buyandruternbill	Combined buy/return bills	buyandruternbillid, supplierid, buybilltotalbill, buybillaftertotalbill, conditions, billnameid
buyandruternbilldetail	Combined bill line items	buyandruternbilldetailid, buyandruternbillid, productid, quantity, buyprice, totalprice, operation
buyandruternbillcurr	Multi-currency for combined bills	buyandruternbillcurrid, buyandruternbillid, currencyid, buyandruternbillcurrencyprice

### Inventory Management Tables

Table Name	Purpose	Key Columns
storedetail	Main inventory quantities	storedetailid, storeid, productid, productquantity, lastbuyprice
sizecolorstoredetail	Size/color variant inventory	sizecolorstoredetailid, storeid, productid, sizeid, colorid, quantity
storereport	Inventory change audit log	storereportid, productid, storeid, storereporttype, storereportmodelid, processname

### Financial Tables

Table Name	Purpose	Key Columns
supplier	Supplier master data	supplierid, suppliername, supplierdebt, supplierdebtmaincurr
supplierdebtchange	Supplier debt transaction log	supplierdebtchangeid, supplierid, supplierdebtchangeamount, supplierdebtchangetype
save	Cash registers/safes	saveid, savename, savevalue
savedaily	Daily cash register operations	savedailyid, saveid, savedailychangeamount, savedailychangetype

### Reference Tables

Table Name	Purpose	Key Columns
product	Product master data	productid, productname, productisservice, averageprice
productserial	Product serial numbers	productserialid, productid, serialno, billid, tablename
buypriceshistorybook	Purchase price history	buypriceshistorybookid, productid, sizeid, colorid, buyprice, buyquantity
costcenter	Cost centers	costcenterid, costcentername
billname	Bill types/names	billnameid, billname

---

🔑 Key Functions

1. showBillDetails() - Load Bill Data

Location: Line 723

Purpose: Retrieve complete bill data including details for viewing/editing

Function Signature:

function showBillDetails($buyAndReturnBill_Id)

Process Flow:

1. Load main bill record from buyandruternbill table

2. Load all line item details from buyandruternbilldetail table

3. Join with product data for names and categories

4. Return array containing bill header and details

Return Value:

array(
    0 => $buyBillData,      // Main bill record
    1 => $buyBillDetails    // Array of line items with product data
)

---

2. increaseProductQuantity() - Add Inventory

Location: Line 1510

Purpose: Increase product quantity in store when items are purchased

Function Signature:

function increaseProductQuantity($storedetailId, $productquantityBefore, $productChangeAmount, 
    $lastBuyPrice, $productnumber, $colName, $detailId, $productId, $tableName, $operation, 
    $billnameId, $prototal, $rowDiscount, $billDiscountVal, $billTotalBeforeDiscount, 
    $sizeColorStoreDetailId, $sizeId, $colorId, $rowtaxval, $billPayedTaxPer, $cFactor)

Process Flow:

1. Calculate new quantity after increase

2. Update storedetail or sizecolorstoredetail table

3. Update last buy price for the product

4. Insert audit record in storereport table

5. Update average price calculations

6. Handle price history tracking

Key Variables:

• $productquantityBefore - Current inventory level
• $productChangeAmount - Quantity being added
• $lastBuyPrice - New purchase price to set
• $cFactor - Currency conversion factor

---

3. decreaseProductQuantity() - Remove Inventory

Location: Line 1565

Purpose: Decrease product quantity in store when items are returned

Function Signature:

function decreaseProductQuantity($storedetailId, $productquantityBefore, $productChangeAmount, 
    $lastBuyPrice, $productnumber, $colName, $detailId, $productId, $tableName, $operation, 
    $billnameId, $prototal, $rowDiscount, $billDiscountVal, $billTotalBeforeDiscount, 
    $sizeColorStoreDetailId, $sizeId, $colorId, $rowtaxval, $billPayedTaxPer, $cFactor)

Process Flow:

1. Calculate new quantity after decrease

2. Update inventory tables

3. Create audit trail

4. Handle negative inventory scenarios

5. Update price histories

---

4. insertSupplierDebtChange() - Track Supplier Debt

Location: Line 1697

Purpose: Log changes to supplier debt balances in multiple currencies

Function Signature:

function insertSupplierDebtChange($supplierId, $supplierDebtChangeBefore, 
    $supplierDebtChangeBeforeInMainCurr, $supplierDebtChangeAmountInMainCurr, 
    $supplierDebtChangeAmount, $supplierDebtChangeType, $processname, 
    $supplierDebtChangeModelId, $supplierDebtChangeAfter, 
    $supplierDebtChangeAfterInMainCurr, $tablename, $comment)

Process Flow:

1. Create debt change record

2. Set debt change type (0=increase debt, 1=decrease debt)

3. Record amounts in both transaction currency and main currency

4. Link to source transaction (bill ID)

5. Add descriptive comment

Debt Types:

• 0 - Debt increase (new purchases)
• 1 - Debt decrease (returns or payments)

---

5. getserail() - Generate Serial Numbers

Location: Line 1771

Purpose: Generate unique serial numbers for products that require tracking

Function Signature:

function getserail($length = 6, $productid)

Process Flow:

1. Generate random alphanumeric string

2. Check for uniqueness in productserial table

3. Regenerate if duplicate found

4. Return unique serial number

Serial Format: 6-character alphanumeric (e.g., "A1B2C3")

---

6. lastAndMeanBuyPrice() - Price Calculations

Location: Line 1856

Purpose: Calculate and maintain average purchase prices and price history

Function Signature:

function lastAndMeanBuyPrice($lastBuyPriceOnePiece, $colName, $detailId, $productId, 
    $sizeId, $colorId, $tableName, $productquantityBefore, $productquantityAfter, 
    $productChangeAmount, $operation = "add", $billnameId, $prototal, $rowDiscount, 
    $billDiscountVal, $billTotalBeforeDiscount, $rowtaxval, $billPayedTaxPer, $cFactor)

Process Flow:

1. Calculate effective price after discounts and taxes

2. Determine weighted average price calculation

3. Update product's average price field

4. Insert/update price history record

5. Handle different operations (add/subtract quantities)

Price Calculation:

// Effective price with discounts
$effectivePrice = ($prototal - $rowDiscount + $rowtaxval) / $productChangeAmount * $cFactor;

// Weighted average calculation
$newAveragePrice = (($productquantityBefore * $currentAvgPrice) + 
                   ($productChangeAmount * $effectivePrice)) / $productquantityAfter;

---

7. updateOverallAveragePriceOfProducts() - Batch Price Updates

Location: Line 2035

Purpose: Update average prices for all products in a bill

Function Signature:

function updateOverallAveragePriceOfProducts($billData)

Process Flow:

1. Loop through all bill details

2. For each product, calculate new average price

3. Update product table with new average

4. Handle both buy and return operations

5. Consider currency conversions

---

🔄 Workflows

Workflow 1: Combined Buy and Return Bill Processing

┌─────────────────────────────────────────────────────────────┐

START: Create Combined Buy/Return Bill

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Load Bill Template and Supplier Data

- Load bill settings and properties

- Get supplier information

- Initialize currency settings

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Process Each Line Item

FOR EACH product line:

│

→ Determine operation type (buy/return)

│

├─→ IF Buy Operation:

│ ├─ Increase product quantity

│ ├─ Update average purchase price

│ ├─ Generate serial numbers if required

│ │ └─ Increase supplier debt │

│

├─→ IF Return Operation:

│ ├─ Decrease product quantity

│ ├─ Update price history

│ │ └─ Decrease supplier debt │

│

│ └─→ Insert store audit records │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Financial Processing

- Calculate total bill amount

- Apply discounts and taxes

- Update supplier debt balance

- Process currency conversions

- Update safe/cash register if cash payment

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Audit and Reporting

- Insert supplier debt change records

- Create store movement reports

- Update daily cash register logs

- Generate cost center entries

└─────────────────────────────────────────────────────────────┘

---

Workflow 2: Serial Number Generation and Tracking

┌─────────────────────────────────────────────────────────────┐

START: Product Requires Serial Number

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Generate Unique Serial Number

- Create random 6-character alphanumeric string

- Check uniqueness against productserial table

- Regenerate if duplicate exists

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Associate Serial with Product and Bill

- Link serial number to product ID

- Link to source bill ID and table name

- Store creation timestamp

- Set initial status

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Enable Serial Tracking

- Serial number can be used for warranty tracking

- Enable product movement history

- Support for future sales tracking

- Return authorization based on serial

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default display	Show add/edit form
`do=editprint`	Display/Print	Show bill details for printing
`do=details`	`showBillDetails()`	View complete bill information
`do=addserial`	Serial form	Add serial numbers to products
`do=addoneserial`	Single serial	Add one serial number
`do=show`	`showAll()`	List all bills with filters
`do=delete`	`delete()`	Soft delete a bill
`do=saveserail`	`saveserail()`	Save serial number assignments

### Required Parameters by Action

View Bill Details (do=details):

• id - Bill ID to display

Add Serial Numbers (do=addserial):

• id - Bill ID
• productid - Product requiring serials
• quantity - Number of serials needed

Show Bills List (do=show):

• from - Start date (optional)
• to - End date (optional)
• supplierid - Supplier filter (optional)
• del - Include deleted bills (optional)

---

🧮 Calculation Methods

Discount Processing

// Fixed amount discount
if ($buybilldiscountrype == 1) {
    $discountvalue = $buybilldiscount;
}
// Percentage discount
else {
    $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;
}

Tax Calculation

$taxvalue = $buybillaftertotalbill - ($buybilltotalbill - $discountvalue);

Average Price Calculation

// Weighted average for new purchases
$newAveragePrice = (($currentQty * $currentAvgPrice) + ($newQty * $newPrice)) / 
                   ($currentQty + $newQty);

Multi-Currency Conversion

// Convert to main currency
$amountInMainCurrency = $foreignAmount * $currencyFactor;

---

🔒 Security & Permissions

User Permission Checks

// Authentication required for most operations
include_once("../public/authentication.php");

Input Sanitization

• All $_GET and $_POST parameters are filtered
• Numeric IDs cast to integer
• SQL injection prevented by DAO layer parameterized queries
• User session validation for all operations

---

🐛 Common Issues & Troubleshooting

1. Negative Inventory After Returns

Issue: Store quantity becomes negative after return operations

Cause: Return quantity exceeds available inventory

Debug:

SELECT storeid, productid, productquantity 
FROM storedetail 
WHERE productquantity < 0;

2. Average Price Calculation Errors

Issue: Product average prices become incorrect

Cause: Currency conversion issues or discount calculation problems

Fix:

// Verify currency factor
$cFactor = ($cFactor > 0) ? $cFactor : 1;

// Ensure positive quantities
$productChangeAmount = abs($productChangeAmount);

3. Serial Number Duplicates

Issue: Duplicate serial numbers generated

Cause: Race condition in serial generation

Solution: Enhanced uniqueness check in getserail() function

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• buyBillController.php - Pure purchase operations
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

BuyAndReturnBill Documentation

Buy and Return Bill Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. showBillDetails() - Load Bill Data

2. increaseProductQuantity() - Add Inventory

3. decreaseProductQuantity() - Remove Inventory

4. insertSupplierDebtChange() - Track Supplier Debt

5. getserail() - Generate Serial Numbers

6. lastAndMeanBuyPrice() - Price Calculations

7. updateOverallAveragePriceOfProducts() - Batch Price Updates

🔄 Workflows

Workflow 1: Combined Buy and Return Bill Processing

Workflow 2: Serial Number Generation and Tracking

🌐 URL Routes & Actions

🧮 Calculation Methods

Discount Processing

Tax Calculation

Average Price Calculation

Multi-Currency Conversion

🔒 Security & Permissions

User Permission Checks

Input Sanitization

🐛 Common Issues & Troubleshooting

1. Negative Inventory After Returns

2. Average Price Calculation Errors

3. Serial Number Duplicates

📚 Related Documentation