# 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
- [x] Recalculate buy bill prices with weighted averages
- [x] Update sell bill pricing history with latest costs
- [x] Calculate and maintain inventory quantities before transactions
- [x] Handle complex discount and tax calculations
- [x] Support multi-currency pricing with conversion factors
- [x] Batch processing with progress tracking via database flags
- [x] Overall product average price maintenance
- [x] Historical price reconstruction from transaction history

### Related Controllers
- [buyBillController.php](buyBillController.md) - Purchase transaction processing
- [sellbillController.php](sellbillController.md) - 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**:
   ```php
   $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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [buyBillController.md](buyBillController.md) - Purchase transaction processing
- [sellbillController.md](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