Recalculate Bill Prices Functions Documentation

File: /controllers/reCalcBillPricesFunctions.php

Purpose: Advanced utility functions for recalculating and updating historical bill prices, averages, and inventory valuation

Last Updated: December 20, 2024

Total Functions: 8+

Lines of Code: ~600

---

📋 Overview

The Recalculate Bill Prices Functions Controller provides sophisticated inventory valuation and price management capabilities. It handles:

• Historical price recalculation for all bill types (buy/sell/return)
• Weighted average cost calculation and maintenance
• Last price and mean price updates across the system
• Inventory quantity tracking before transactions
• Overall average price calculations for products
• Complex pricing formulas with discounts, taxes, and currency conversion
• Batch processing for large datasets with progress tracking

Primary Functions

✅ Recalculate buy bill prices with weighted averages
✅ Update sell bill pricing history with latest costs
✅ Calculate and maintain inventory quantities before transactions
✅ Handle complex discount and tax calculations
✅ Support multi-currency pricing with conversion factors
✅ Batch processing with progress tracking via database flags
✅ Overall product average price maintenance
✅ Historical price reconstruction from transaction history

Related Controllers

• buyBillController.php - Purchase transaction processing
• sellbillController.php - Sales transaction processing
• productController.php - Product master data management
• profitreportController.php - Profit analysis and reporting

---

🗄️ Database Tables

Progress Tracking Table

Table Name	Purpose	Key Columns
lastbillidspricesupdated	Batch processing progress	buybill, buyandret, retbuy, sell, sellandret, retsell, buyquantity

### Purchase Bill Tables

Table Name	Purpose	Key Columns
buybill	Purchase bill headers	buybillid, buybilltotalbill, buybilldiscount, buybilldiscountrype
buybilldetail	Purchase bill line items	buybilldetailid, buybilldetailproductid, buybilldetailquantity, buybilldetailtotalprice
buyandruternbill	Combined buy/return bills	Same structure as buybill
buyandruternbilldetail	Combined bill details	Same structure as buybilldetail
returnbuybill	Purchase return headers	returnbuybillid, returntotalbill
returnbuybilldetail	Return line items	returnbuybilldetailid, returnbuybilldetailproductid

### Sales Bill Tables

Table Name	Purpose	Key Columns
sellbilldetail	Sales bill line items	sellbilldetailid, sellbilldetailproductid, sellbilldetailquantity
returnsellbilldetail	Sales return details	returnsellbilldetailid, returnsellbilldetailproductid
sellandruternbilldetail	Combined sell/return	sellbilldetailid, sellbilldetailproductid

### Product & Pricing Tables

Table Name	Purpose	Key Columns
product	Product master data	productId, productBuyPrice, lastbuyprice, meanbuyprice, overAllAveragePrice
productunit	Product unit conversions	productid, productnumber, proUnitBuyPrice
storereport	Inventory movement log	productid, storeid, productbefore, productafter, tablename
billsettings	Bill configuration	billnameid, billpropertyid, billsettingsvalue

---

🔑 Key Functions

1. updatePricesBuyBills - Main Buy Bill Price Update

Location: Line 208

Purpose: Recalculate all buy bill prices and update product pricing information

Process Flow:

1. Get Pending Bills:

   $allbuyBills = $buyBillDetailExt->buyOnlyAllBills(
       $lastBillIdsPricesUpdated->buybill, 
       $lastBillIdsPricesUpdated->buyandret
   );
   ```

2. **Process Each Bill Product**:
   ```php
   foreach ($allbuyBills as $billProduct) {
       $isFirstBill = 0;
       
       // Check if this is the first bill for this product
       $firstBillDetailId = R::getCell('
           select min(buybilldetailid) from buybilldetail 
           join buybill on buybill.buybillid = buybilldetail.buybillid 
           where buybill.conditions = 0 and buybilldetailproductid = ' . $billProduct->buybilldetailproductid
       );
       
       if ($firstBillDetailId == $billProduct->buybilldetailid) {
           $isFirstBill = 1;
           // Get initial product quantity and price
           $firstQuantityAtAddPro = R::getCell('
               select sum(productquantity) from storereport 
               where tablename="productController.php" and productid=' . $billProduct->buybilldetailproductid
           );
           $totalcreditorAtAddPro = R::getCell('
               select totalcreditor from dailyentry 
               where operationDetailLink="productController.php?do=edit&id=' . $billProduct->buybilldetailproductid . '" 
               order by id asc limit 1'
           );
           $firstPriceAtAddPro = $totalcreditorAtAddPro / $firstQuantityAtAddPro;
       }
       
       fixPricesBuyBills($billProduct, $firstQuantityAtAddPro, $firstPriceAtAddPro, $isFirstBill);
   }
   ```

---

### 2. **fixPricesBuyBills()** - Core Price Calculation Logic
**Location**: Line 273  
**Purpose**: Calculate weighted average prices for a specific product transaction

**Function Signature**:

php

function fixPricesBuyBills($billProduct, $firstQuantityAtAddPro = 0, $firstPriceAtAddPro = 0, $isFirstBill = 0)

**Weighted Average Calculation**:

php

// Get all previous bills for this product

$previousProductBills = $buyBillDetailExt->buyOnlyAllBills(0, 0,

" where buybilldetailproductid = $billProduct->buybilldetailproductid

and buybillsysdate < '" . $billProduct->buybillsysdate . "' ");

$totalQuantity = $firstQuantityAtAddPro;

$TotalPriceTimesQuantity = $firstQuantityAtAddPro * $firstPriceAtAddPro;

foreach ($previousProductBills as $data) {

$prototal = $data->buybilldetailtotalprice;

$rowDiscount = $data->discountvalue;

$gendis = $data->buybilldiscount; // Bill-level discount

$discount_type = $data->buybilldiscountrype;

$billTotalBeforeDiscount = $data->buybilltotalbill;

// Calculate bill discount value

$billDiscountVal = $gendis;

if ($discount_type == "1") {

$billDiscountVal = ($gendis / 100) * $billTotalBeforeDiscount;

}

$finalquantity = $data->buybilldetailquantity * $data->productnumber;

$price = $prototal + $rowDiscount;

$priceWithDiscount = $prototal - ($billDiscountVal * ($prototal / $billTotalBeforeDiscount));

$totalQuantity += $finalquantity;

$TotalPriceTimesQuantity += $price;

}

---

### 3. **lastAndMeanBuyPrice()** - Advanced Price Calculation
**Location**: Line 421  
**Purpose**: Calculate last and mean buy prices with complex business rules

**Function Signature**:

php

function lastAndMeanBuyPrice($billId, $firstPriceAtAddPro, $isFirstBill, $lastBuyPriceOnePiece,

$colName, $detailId, $productId, $sizeId, $colorId, $tableName, $productquantityBefore,

$productquantityAfter, $productChangeAmount, $operation, $billnameId, $prototal,

$rowDiscount, $billDiscountVal, $billTotalBeforeDiscount, $rowtaxval, $billPayedTaxPer,

$cFactor, $probuyDiscountPer)

**Key Calculations**:

**A. Mean Buy Price Calculation**:

php

if ($billSettingData[0]->billsettingsvalue == 0) {

// Without discount percentage application

if ($productquantityAfter < $productquantityBefore) { // Deletion/Return

$Bast = ($productquantityBefore * $buyProduct->meanbuyprice) - ($prototal + $rowDiscount - $rowtaxval);

} else { // Addition

$Bast = ($productquantityBefore * $buyProduct->meanbuyprice) + ($prototal + $rowDiscount - $rowtaxval);

}

$meanBuyPrice = round(($Bast / $productquantityAfter), $noOfDecimalPlaces);

} else {

// With discount percentage application

$realPrice_precentage = $buyProduct->buypricereal_precentage;

$total_precntage = ($realPrice_precentage / 100) * ($prototal + $rowDiscount - $rowtaxval);

if ($productquantityAfter < $productquantityBefore) {

$Bast = ($productquantityBefore * $buyProduct->meanbuyprice) - ($prototal - $total_precntage);

} else {

$Bast = ($productquantityBefore * $buyProduct->meanbuyprice) + ($prototal - $total_precntage);

}

$meanBuyPrice = round(($Bast / $productquantityAfter), $noOfDecimalPlaces);

}

**B. Currency Conversion**:

php

// Convert to main currency

$cFactor = (isset($cFactor) && !empty($cFactor)) ? $cFactor : 1;

$billDiscountVal = $billDiscountVal / $cFactor;

$billTotalBeforeDiscount = $billTotalBeforeDiscount / $cFactor;

$rowtaxval = $rowtaxval / $cFactor;

**C. Tax Calculations**:

php

$billTaxShareForRow = $prototal * ($billPayedTaxPer / 100);

$BastWithTax = ($productquantityBefore * $buyProduct->meanbuyprice_withTax) + ($prototal + $billTaxShareForRow);

$meanBuyPriceWithTax = round(($BastWithTax / $productquantityAfter), $noOfDecimalPlaces);

---

### 4. **updatePricesSellBills** - Update Sales Bill Pricing
**Location**: Line 227  
**Purpose**: Update sell bills with current cost information for accurate profit calculations

**Process Flow**:

php

$sell = $sellbilldetailEX->queryAllNotDel(" and sellbilldetailid > $lastBillIdsPricesUpdated->sell order by sellbilldetailid asc");

foreach ($sell as $value) {

$prices = getLastPrices($value->sellbilldetailproductid, $value->sellbilldate);

$sellbilldetailEX->updatePrices_f("sellbilldetail",

$prices->buybilldetailprice,

$prices->lastbuyprice,

$prices->meanbuyprice,

$prices->lastbuyprice_withDiscount,

$prices->meanbuyprice_withDiscount,

"sellbilldetailid",

$value->sellbilldetailid

);

$lastBillIdsPricesUpdated->sell = $value->sellbilldetailid;

$lastBillIdsPricesUpdatedDAO->update($lastBillIdsPricesUpdated);

}

---

### 5. **getLastPrices()** - Retrieve Historical Pricing
**Location**: Line 398  
**Purpose**: Get the last buy prices for a product before a specific date

**Function Signature**:

php

function getLastPrices($productId, $date)

**Logic**:

php

$priceData = $buyBillDetailExt->buyOnlyAllBillsLatPricesBefore(

" where buybilldetailproductid = $productId and buybilldate < '" . $date . "'

order by buybilldate desc limit 1"

);

if (isset($priceData->buybilldetailid) && $priceData->buybilldetailid > 0) {

// Use historical prices from latest buy bill

$prices->buybilldetailprice = (float) $priceData->buybilldetailprice;

$prices->lastbuyprice = (float) $priceData->lastbuyprice;

$prices->meanbuyprice = (float) $priceData->meanbuyprice;

} else {

// Fall back to product master prices

$priceData = $ProductDAO->load($productId);

$prices->buybilldetailprice = (float) $priceData->productBuyPrice;

$prices->lastbuyprice = (float) $priceData->lastbuyprice;

$prices->meanbuyprice = (float) $priceData->meanbuyprice;

}

---

## 🔄 Workflows

### Workflow 1: Complete Bill Price Recalculation

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

│ START: Recalculate Buy Bill Prices │

│ URL: reCalcBillPricesFunctions.php?do=updatePricesBuyBills │

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

│

▼

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

│ 1. Load Progress Tracking Data │

│ - Load lastbillidspricesupdated record │

│ - Get last processed bill IDs for each type │

│ - Initialize if not exists (first run) │

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

│

▼

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

│ 2. Get Bills to Process │

│ - Query bills newer than last processed IDs │

│ - Include both buybill and buyandruternbill types │

│ - Order by bill ID for sequential processing │

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

│

▼

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

│ 3. Process Each Bill Product │

│ FOR EACH bill product line: │

│ │ │

│ ├─→ A. Determine if First Bill for Product: │

│ │ - Find earliest bill detail ID for product │

│ │ - If current = earliest: isFirstBill = 1 │

│ │ - Load initial product quantity and price │

│ │ │

│ ├─→ B. Get Historical Data: │

│ │ - Load all previous bills for this product │

│ │ - Filter by date before current bill │

│ │ - Build chronological transaction history │

│ │ │

│ ├─→ C. Calculate Running Totals: │

│ │ - Start with initial quantity/price │

│ │ - Add each historical transaction │

│ │ - Apply discounts and currency conversions │

│ │ - Track quantity and value totals │

│ │ │

│ └─→ D. Call fixPricesBuyBills(): │

│ - Pass all calculated data │

│ - Update product pricing information │

│ - Update progress tracking │

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

│

▼

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

│ 4. fixPricesBuyBills() - Core Logic │

│ A. Historical Transaction Processing: │

│ FOR EACH previous bill: │

│ - Extract bill totals and discounts │

│ - Calculate bill-level discount value │

│ - Apply product unit multipliers │

│ - Accumulate quantities and values │

│ │

│ B. Current Transaction Integration: │

│ - Add current bill to running totals │

│ - Calculate final quantities and prices │

│ - Determine operation type (add/subtract) │

│ │

│ C. Call lastAndMeanBuyPrice(): │

│ - Pass all transaction data │

│ - Execute complex pricing formulas │

│ - Update product master record │

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

│

▼

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

│ 5. lastAndMeanBuyPrice() - Advanced Calculations │

│ A. Currency & Tax Adjustments: │

│ - Convert all amounts to base currency │

│ - Calculate tax allocations │

│ - Apply currency conversion factors │

│ │

│ B. Inventory Quantity Determination: │

│ - Calculate quantity before transaction │

│ - Determine quantity after transaction │

│ - Handle multi-store inventory tracking │

│ │

│ C. Pricing Formula Application: │

│ IF discount setting = 0: │

│ - Use standard weighted average formula │

│ ELSE: │

│ - Apply real price percentage adjustments │

│ │

│ D. Calculate All Price Types: │

│ - meanbuyprice (without discount) │

│ - meanbuyprice_withDiscount │

│ - meanbuyprice_withTax │

│ - lastbuyprice (current transaction) │

│ - lastbuyprice_withDiscount │

│ - lastbuyprice_withTax │

│ - weightedDiscount (running average) │

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

│

▼

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

│ 6. Update Records & Progress Tracking │

│ A. Product Master Update: │

│ - Update all calculated price fields │

│ - Update weighted discount percentage │

│ - Validate against zero/null values │

│ │

│ B. Bill Detail Update: │

│ - Update bill detail with new price info │

│ - Store historical pricing data │

│ │

│ C. Product Unit Price Update: │

│ - Update productunit table if setting enabled │

│ - Recalculate unit buy prices │

│ │

│ D. Progress Tracking: │

│ - Update lastbillidspricesupdated │

│ - Store last processed bill ID │

│ - Enable resume capability │

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

│

▼

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

│ 7. Completion & Redirect │

│ - Final progress update │

│ - Redirect to profit report for validation │

│ - Log completion statistics │

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

---

## 🧮 Complex Pricing Formulas

### Weighted Average Cost Formula

php

// Standard weighted average calculation

$currentValue = $currentQuantity * $currentAveragePrice;

$newValue = $newQuantity * $newUnitPrice;

$totalValue = $currentValue + $newValue;

$totalQuantity = $currentQuantity + $newQuantity;

$newAveragePrice = $totalValue / $totalQuantity;

// With discounts applied

$discountAmount = calculateDiscountAmount($billTotal, $billDiscountPct, $lineTotal);

$adjustedLineTotal = $lineTotal - $discountAmount;

$newValue = $newQuantity * ($adjustedLineTotal / $newQuantity);

### Bill-Level Discount Allocation

php

if ($discount_type == "1") {

// Percentage discount

$billDiscountVal = ($gendis / 100) * $billTotalBeforeDiscount;

} else {

// Fixed amount discount

$billDiscountVal = $gendis;

}

// Allocate discount proportionally to each line

$lineDiscountShare = $billDiscountVal * ($prototal / $billTotalBeforeDiscount);

$adjustedLineTotal = $prototal - $lineDiscountShare;

### Tax Calculation with Multiple Levels

php

// Row-level tax (included in line total)

$rowTaxAmount = $rowtaxval;

// Bill-level tax (percentage of total)

$billTaxShareForRow = $prototal * ($billPayedTaxPer / 100);

// Total tax for this line

$totalLineTax = $rowTaxAmount + $billTaxShareForRow;

// Price with all taxes

$priceWithAllTaxes = $basePrice + ($totalLineTax / $quantity);

### Multi-Currency Support

php

// Convert foreign currency to base currency

$cFactor = getCurrencyConversionFactor($billCurrencyId);

if ($cFactor > 0) {

$baseCurrencyAmount = $foreignCurrencyAmount / $cFactor;

} else {

$baseCurrencyAmount = $foreignCurrencyAmount; // Default to 1:1

}

---

## 📊 Performance Optimization

### Batch Processing Strategy
1. **Progress Tracking**: Uses `lastbillidspricesupdated` to resume interrupted processes
2. **Sequential Processing**: Processes bills in chronological order
3. **Chunked Operations**: Can be run in batches to avoid timeouts

### Database Performance
**Critical Indexes**:

sql

CREATE INDEX idx_buydetail_product_date ON buybilldetail(buybilldetailproductid, buybilldate);

CREATE INDEX idx_selldetail_date ON sellbilldetail(sellbilldate, sellbilldetailid);

CREATE INDEX idx_storereport_product ON storereport(productid, tablename, storereportid);

CREATE INDEX idx_product_pricing ON product(productId, meanbuyprice, lastbuyprice);

**Query Optimization**:

php

// Avoid N+1 queries by batching related data

// Current: Individual queries per product

foreach ($products as $product) {

$prices = getLastPrices($product->id, $product->date); // Individual query

}

// Better: Single query with proper JOINs

$prices = getBulkPricesForProducts($productIds, $dates);

---

## ⚠️ Known Issues & Limitations

### 1. **Memory Usage for Large Datasets**
**Issue**: Processing many years of data can consume significant memory  
**Solution**: Implement chunked processing with configurable batch sizes

### 2. **Transaction History Integrity**
**Issue**: If historical data is corrupted, all calculations will be incorrect  
**Solution**: Add data validation and repair functions

### 3. **Currency Conversion Edge Cases**
**Issue**: Missing or zero conversion factors can cause calculation errors  
**Solution**: Add fallback logic and validation

### 4. **Performance on Large Datasets**
**Issue**: Can take hours to process years of transaction history  
**Solution**: Optimize queries and consider background processing

---

## 🔒 Security & Data Integrity

### Data Validation

php

// Ensure no division by zero

if ($makam <= 0) {

$makam = 1; // Prevent division by zero

error_log("Warning: Zero quantity detected in price calculation");

}

// Validate price ranges

if ($meanBuyPrice < 0) {

$meanBuyPrice = $buyProduct->productBuyPrice; // Fall back to master price

}

### Transaction Safety
- Updates are wrapped in database transactions
- Progress tracking allows resume on failure
- Validation checks prevent data corruption

### Access Control
- Requires session authentication
- Should be restricted to admin users
- Operations are logged for audit trail

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Price Recalculation

1. Create product with known initial price

2. Create several buy bills with different costs

3. Run price recalculation

4. Verify weighted average calculation manually

5. Check all price fields updated correctly

### Test Case 2: Complex Discount Scenarios

1. Create bills with various discount types

2. Mix percentage and fixed amount discounts

3. Include bill-level and line-level discounts

4. Run recalculation and verify formula accuracy

5. Test edge cases (100% discount, zero amounts)

### Test Case 3: Multi-Currency Processing

1. Set up products with foreign currency bills

2. Configure currency conversion factors

3. Run price updates

4. Verify currency conversion applied correctly

5. Test missing conversion factor handling

### Test Case 4: Large Dataset Performance

1. Create large number of transactions (10,000+)

2. Measure processing time and memory usage

3. Test batch processing with different chunk sizes

4. Verify data integrity after processing

5. Test resume functionality after interruption

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• buyBillController.md - Purchase transaction processing
• sellbillController.md - Sales transaction processing
• Inventory Valuation Guide - FIFO, LIFO, Weighted Average methods

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When pricing calculation methods change

📚 ERP Docs

ReCalcBillPricesFunctions Documentation

Recalculate Bill Prices Functions Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Progress Tracking Table

🔑 Key Functions

1. updatePricesBuyBills - Main Buy Bill Price Update

📚 Related Documentation