Product Price Fix Controller Documentation

File: /controllers/productpricefix.php

Purpose: Utility script to bulk update product prices using multiplier formulas

Last Updated: December 20, 2024

Total Functions: 1+

Lines of Code: ~68

---

📋 Overview

This utility script performs bulk price updates for products using predefined multipliers. Used for:

• • Bulk price adjustments across product catalog
• • Price tier calculations (wholesale, semi-wholesale, retail)
• • Automated pricing strategies
• • Database maintenance operations

Primary Functions

• ✅ Bulk price updates
• ✅ Multi-tier pricing calculation
• ✅ Batch processing for performance
• ✅ SQL optimization

---

🔑 Key Functions

1. Main Processing Loop - Bulk Price Update

Location: Lines 42-61

Purpose: Update product prices in batches using multipliers

$count = 0;
$start = 0;
$limit = 30;
$all_products_count = $productExt->getProductsCount();

for ($i = $start; $i <= $all_products_count->productId; $i += $limit) {
    $allproduct = $productExt->queryAllOrderedLimitedSimple($start, $limit);
    $update_sql = '';
    
    foreach ($allproduct as $value) {
        // Calculate new prices based on wholesale price
        $value->productSellHalfPrice = $value->productSellAllPrice * 1.12;  // 12% markup
        $value->productSellUnitPrice = $value->productSellAllPrice * 1.3;   // 30% markup
        
        // Build bulk update SQL
        $update_sql .= "UPDATE product SET 
                          productSellHalfPrice = $value->productSellHalfPrice,
                          productSellUnitPrice = $value->productSellUnitPrice 
                        WHERE productId = $value->productId;";
        $count++;
    }
    
    // Execute bulk update
    if ($update_sql != "") {
        $conn = mysqli_connect(ConnectionProperty::getHost(), ConnectionProperty::getUser(), 
                              ConnectionProperty::getPassword(), ConnectionProperty::getDatabase());
        $res = mysqli_multi_query($conn, $update_sql);
        mysqli_close($conn);  // Note: typo in original ($con vs $conn)
    }
    
    $start += $limit;
}

---

🔄 Workflows

Workflow: Bulk Price Update

---

🧮 Calculation Methods

Price Tier Calculations

// Base price: productSellAllPrice (wholesale)
$wholesale_price = $product->productSellAllPrice;

// Semi-wholesale: 12% markup
$semi_wholesale = $wholesale_price * 1.12;

// Retail: 30% markup  
$retail = $wholesale_price * 1.3;

Pricing Example

// Example: Wholesale = 100
$productSellAllPrice = 100.00;

$productSellHalfPrice = 100.00 * 1.12 = 112.00;  // Semi-wholesale
$productSellUnitPrice = 100.00 * 1.30 = 130.00;  // Retail

// Final pricing structure:
// Wholesale: 100.00
// Semi-wholesale: 112.00 (12% markup)
// Retail: 130.00 (30% markup)

---

🔒 Security & Permissions

Database Connection

$conn = mysqli_connect(
    ConnectionProperty::getHost(), 
    ConnectionProperty::getUser(), 
    ConnectionProperty::getPassword(), 
    ConnectionProperty::getDatabase()
) or die('unable to connect to db');

SQL Execution

• • Uses mysqli_multi_query() for batch operations
• • No parameterized queries (potential SQL injection risk)
• • Direct value interpolation in SQL

---

⚠️ Issues Identified

1. Variable Name Typo

Location: Line 55

Issue: Incorrect variable name in mysqli_close()

mysqli_close($con);  // Should be $conn

2. SQL Injection Risk

Issue: Direct value interpolation without escaping

$update_sql .= "UPDATE product SET productSellHalfPrice = $value->productSellHalfPrice";
// Values not escaped or parameterized

3. Error Handling

Issue: No error checking for database operations

• • No validation of mysqli_multi_query() result
• • No error reporting for failed updates
• • No transaction rollback capability

4. Hard-coded Multipliers

Issue: Price multipliers are hard-coded

$value->productSellHalfPrice = $value->productSellAllPrice * 1.12;  // Fixed 12%
$value->productSellUnitPrice = $value->productSellAllPrice * 1.3;   // Fixed 30%

---

📊 Performance Considerations

Optimizations

• • Batch processing (30 products per batch)
• • Bulk SQL updates using mysqli_multi_query()
• • Direct database connection for speed

Potential Issues

• • Large transactions may cause timeouts
• • No progress reporting during execution
• • Memory usage with large product catalogs

---

🧪 Testing Scenarios

Test Case 1: Small Batch

1. Run with limited product set (< 30)
2. Verify price calculations
3. Check database updates
4. Validate final pricing structure

Test Case 2: Large Catalog

1. Test with full product catalog
2. Monitor execution time and memory
3. Verify batch processing works correctly
4. Check for any failed updates

---

📚 Recommendations

Fix Variable Typo

mysqli_close($conn);  // Fix variable name

Add Error Handling

if (!$res) {
    error_log("Failed to update prices: " . mysqli_error($conn));
    // Handle error appropriately
}

Use Parameterized Queries

$stmt = $conn->prepare("UPDATE product SET productSellHalfPrice = ?, productSellUnitPrice = ? WHERE productId = ?");
foreach ($allproduct as $value) {
    $halfPrice = $value->productSellAllPrice * 1.12;
    $unitPrice = $value->productSellAllPrice * 1.3;
    $stmt->bind_param("ddi", $halfPrice, $unitPrice, $value->productId);
    $stmt->execute();
}

Make Multipliers Configurable

// Load from configuration
$SEMI_WHOLESALE_MULTIPLIER = 1.12;
$RETAIL_MULTIPLIER = 1.30;

$value->productSellHalfPrice = $value->productSellAllPrice * $SEMI_WHOLESALE_MULTIPLIER;
$value->productSellUnitPrice = $value->productSellAllPrice * $RETAIL_MULTIPLIER;

Add Transaction Support

mysqli_autocommit($conn, FALSE);
try {
    $res = mysqli_multi_query($conn, $update_sql);
    if (!$res) throw new Exception(mysqli_error($conn));
    mysqli_commit($conn);
} catch (Exception $e) {
    mysqli_rollback($conn);
    error_log("Price update failed: " . $e->getMessage());
}
mysqli_autocommit($conn, TRUE);

---

Documented By: AI Assistant

Review Status: ⚠️ Needs Review - Contains typos and security issues

Next Review: Fix variable typo, add error handling, and improve security