Online Downloads Controller Documentation

File: /controllers/onlineDownloadsController.php

Purpose: Product price list report generator with advanced filtering and inventory valuation

Last Updated: December 20, 2024

Total Functions: 12+

Lines of Code: ~991

---

📋 Overview

The Online Downloads Controller generates detailed product price lists and inventory reports with configurable columns, filtering options, and multiple valuation methods. Despite its name, it functions as a comprehensive product pricing and inventory reporting system. It handles:

• Product price list generation with customizable columns
• Advanced filtering by store, category, quantity ranges
• Multiple inventory valuation methods (FIFO, LIFO, weighted average)
• Hierarchical category navigation and filtering
• Discount application and calculation
• Product image and barcode management
• Multi-currency support via program settings
• Zero quantity filtering and stock analysis

Primary Functions

✅ Generate comprehensive product price lists
✅ Filter by store, category, product, quantity ranges
✅ Apply various inventory valuation methods
✅ Show/hide specific columns (prices, quantities, images)
✅ Calculate product totals and inventory values
✅ Handle product discounts and pricing tiers
✅ Generate product category paths
✅ Support multi-level category hierarchies
✅ Product barcode and parcode generation
✅ Integration with store management

Related Controllers

• storedetailController.php - Store inventory management
• productController.php - Product management
• productCatController.php - Category management
• storereportController.php - Store reports

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
storedetail	Product quantities by store	storedetailid, productid, storeid, productquantity, storedetaildate
product	Product master data	productid, productName, productBuyPrice, productSellAllPrice, productCatId, logo, parcode
store	Store/warehouse definitions	storeId, storeName, conditions
productcat	Product categories hierarchy	productCatId, productCatName, productCatParent

### Financial Tables

Table Name	Purpose	Key Columns
buybilldetail	Purchase history for pricing	buybilldetailid, buybilldetailproductid, buybilldetailprice
returnbuybilldetail	Purchase returns	returnbuybilldetailid, returnbuybilldetailproductid

### Configuration Tables

Table Name	Purpose	Key Columns
programsettings	System pricing configuration	programsettingsid, lastprice, Inventoryevaluation, usedParcode, parcodeDigits
youtubelink	Help and tutorial links	youtubelinkid, title, url

---

🔑 Key Functions

1. Default Action / Show Report Form

Location: Line 112

Purpose: Display price list report form with filters and options

Process Flow:

1. Load store data for filtering

2. Load category hierarchy for selection

3. Load program settings for pricing configuration

4. Display filter form with checkboxes for columns

5. Show tutorial links

Template Variables Assigned:

• $storeData - Available stores
• $catDataReport - Category hierarchy
• $Programsettingdata - System settings
• $youtubes - Help videos

---

2. show() - Main Report Generation

Location: Line 273

Purpose: Generate detailed product price list with filtering and calculations

Function Signature:

function show()

Process Flow:

1. Parse Filter Parameters:

- Product ID, store ID, category ID

- Quantity range filters

- Column display options

- Sorting preferences

2. Build Dynamic Query:

- Apply store filter

- Apply category filter (including subcategories)

- Apply quantity range filters

- Apply zero quantity filter

3. Execute Query and Process Results:

- Load product data with store details

- Calculate pricing based on valuation method

- Apply discounts if enabled

- Generate category paths

- Format barcodes

4. Calculate Totals:

- Sum inventory values

- Count products

- Apply inventory valuation rules

Key Features:

• Dynamic Column Selection: Show/hide prices, quantities, images based on checkboxes
• Category Hierarchy: Automatically includes all subcategories when filtering
• Multiple Valuation Methods: FIFO, LIFO, weighted average, with/without discounts
• Advanced Filtering: By store, category, quantity ranges

---

3. Inventory Valuation Logic

Location: Line 506-531

Purpose: Apply different inventory valuation methods

Valuation Methods:

switch ($Programsettingdata->Inventoryevaluation) {
    case "first":     // FIFO - First purchase price
        $pro_price = (float) $myproduct->productBuyPrice;
        break;
    case "last":      // LIFO - Last purchase price  
        $pro_price = (float) $myproduct->lastbuyprice;
        break;
    case "mean":      // Weighted average
        $pro_price = (float) $myproduct->meanbuyprice;
        break;
    case "last_discount":  // Last price with discount
        $pro_price = (float) $myproduct->lastbuyprice_withDiscount;
        break;
    case "mean_discount": // Average price with discount
        $pro_price = (float) $myproduct->meanbuyprice_withDiscount;
        break;
    case "tax":       // Last price with tax
        $pro_price = (float) $myproduct->lastbuyprice_withTax;
        break;
    case "mean_tax":  // Average price with tax
        $pro_price = (float) $myproduct->meanbuyprice_withTax;
        break;
    default:          // Overall average
        $pro_price = (float) $myproduct->overAllAveragePrice;
}

---

4. Discount Application

Location: Line 558-572

Purpose: Apply product-specific discounts to selling prices

Discount Types:

if ($selldiscount == 1 && $storedetail->selldiscount > 0) {
    if ($storedetail->discounttype == 0) {
        // Fixed amount discount
        $storedetail->productSellAllPrice -= $storedetail->selldiscount;
        $storedetail->productSellHalfPrice -= $storedetail->selldiscount;
        $storedetail->productSellUnitPrice -= $storedetail->selldiscount;
    } elseif ($storedetail->discounttype == 1) {
        // Percentage discount
        $storedetail->productSellAllPrice -= $storedetail->productSellAllPrice * ($storedetail->selldiscount / 100);
        $storedetail->productSellHalfPrice -= $storedetail->productSellHalfPrice * ($storedetail->selldiscount / 100);
        $storedetail->productSellUnitPrice -= $storedetail->productSellUnitPrice * ($storedetail->selldiscount / 100);
        
        // Round to 2 decimal places
        $storedetail->productSellAllPrice = round($storedetail->productSellAllPrice, 2);
        $storedetail->productSellHalfPrice = round($storedetail->productSellHalfPrice, 2);
        $storedetail->productSellUnitPrice = round($storedetail->productSellUnitPrice, 2);
    }
}

---

5. Category Hierarchy Processing

Location: Line 451-461

Purpose: Filter by category including all subcategories

Process:

if (isset($productCatId) && $productCatId != '-1') {
    $myProductCatData = $productCatDAO->load($productCatId);
    
    // Get all subcategories recursively
    $catsIDS = '' . $productCatId;
    getAllSubCat($productCatId, 1); // mode = 1 gets all sub cats
    
    // Apply filter including all subcategories
    $queryString .= '  product.productCatId in( ' . $catsIDS . ' ) AND';
}

---

6. getAllSubCat() - Recursive Category Search

Location: Line 952

Purpose: Recursively find all subcategories of a given category

Function Signature:

function getAllSubCat($catid, $mode)

Modes:

• Mode 1: Get all subcategories (adds to $catsIDS)
• Mode 2: Get only leaf categories (no children)

---

7. Barcode Generation Logic

Location: Line 582-587

Purpose: Generate product barcodes based on system settings

Barcode Logic:

if ($Programsettingdata->usedParcode == 1) {
    // Use product ID as barcode with leading zeros
    $storedetail->parcode = "i" . addPreDigitsToNum($myproduct->productId);
} else {
    // Use custom barcode
    $storedetail->parcode = $myproduct->parcode;
}

---

8. addPreDigitsToNum() - Barcode Formatting

Location: Line 922

Purpose: Add leading zeros to product IDs for barcode format

Function Signature:

function addPreDigitsToNum($num)

Example: Product ID 123 with 5-digit setting becomes "00123"

---

9. Alternative Show Functions

showByProductNameAndStore()

Location: Line 593

Purpose: Report for specific product in specific store

showBystoreName()

Location: Line 676

Purpose: Report for all products in specific store

showByProductCatNameAndStoreId()

Location: Line 759

Purpose: Report for category products in specific store

showAll()

Location: Line 841

Purpose: Report for all products across all stores

---

🔄 Workflows

Workflow 1: Product Price List Generation

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

START: Generate Price List

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

▼

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

1User Selects Filters

- Store selection

- Category selection

- Display column options

- Quantity range filters

- Sort preferences

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

▼

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

2Build Dynamic Query

│ ┌─────────────────────────────────────────────────────┐ │

│ Base Query: storedetail + product + store

│ + Apply store filter

│ + Apply category filter (with subcats)

│ + Apply quantity range filters

│ + Apply zero quantity filter

│ └─────────────────────────────────────────────────────┘ │

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

▼

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

3Execute Query & Load Data

- Query database with filters

- Load product details

- Load store information

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

▼

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

4Process Each Product

FOR EACH product record:

│

→ Apply inventory valuation method

│ ├─ FIFO (first purchase price)

│ ├─ LIFO (last purchase price)

│ ├─ Weighted average

│ │ └─ With/without discounts/tax │

│

→ Calculate product value

│ │ └─ Quantity × Unit Price │

│

→ Apply discounts (if enabled)

│ ├─ Fixed amount discounts

│ │ └─ Percentage discounts │

│

→ Generate category path

│ │ └─ Build hierarchical path string │

│

→ Format barcode/parcode

│ ├─ Use product ID with padding

│ │ └─ Or use custom barcode │

│

│ └─→ Set product description │

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

▼

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

5Calculate Totals & Display

- Sum total inventory value

- Count total products

- Apply template column settings

- Display formatted report

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

---

Workflow 2: Category Hierarchy Filtering

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

START: Category Filter Applied

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

▼

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

1Get Selected Category

- User selects category from hierarchy

- Category ID extracted from form

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

▼

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

2Initialize Category List

- Start with selected category ID

- $catsIDS = selected_category_id

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

▼

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

3Find All Subcategories

CALL getAllSubCat(categoryId, mode=1):

│

→ Query direct children

│

├─→ FOR EACH child category:

│ ├─ Add child ID to $catsIDS

│ │ └─ Recursively call getAllSubCat(child_id) │

│

│ └─→ Continue until no more children │

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

▼

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

4Build SQL Filter

- $catsIDS contains all relevant category IDs

- Add to query: "product.productCatId IN ($catsIDS)"

- Filter includes parent + all descendants

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

▼

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

5Execute Filtered Query

- Products from selected category

- Products from all subcategories

- Hierarchical category filtering complete

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty) or `do=show`	Default action	Display report form and generate report if filters applied

### Required Parameters

Report Generation (do=show):

• storeId - Store filter (-1 for all stores)
• productCatId[level] - Category filter (supports multi-level)
• level - Category hierarchy level
• productId - Specific product filter (optional)
• order - Sort order for results

Display Options:

• selectall - Select all columns
• productSellUnitPrice - Show unit price column
• productBuyPrice - Show buy price column
• productSellAllPrice - Show wholesale price column
• productSellHalfPrice - Show half price column
• storename - Show store name column
• amount - Show quantity column
• lastProcessDate - Show last process date
• selldiscount - Apply discount calculations
• image - Show product images
• description - Show product descriptions
• parcodaz - Show barcodes
• dasta - Show batch info
• ro3dasta - Show batch details

Filters:

• hideZeroQuantity - Hide products with zero stock
• quantityGreaterThan - Minimum quantity filter
• quantityLessThan - Maximum quantity filter

---

🧮 Calculation Methods

Inventory Valuation Calculation

// Get base price using selected valuation method
$productBuyPrice = $pro_price; // From valuation method
$productQuantity = $storedetail->productquantity;

// Calculate total value
$SumProductPrice = $productBuyPrice * $productQuantity;
$sumValue = $SumProductPrice + $sumValue; // Running total

Discount Application

// Fixed amount discount
if ($storedetail->discounttype == 0) {
    $discounted_price = $original_price - $storedetail->selldiscount;
}

// Percentage discount  
else {
    $discounted_price = $original_price - ($original_price * $storedetail->selldiscount / 100);
    $discounted_price = round($discounted_price, 2);
}

Category Path Building

function getProductPath_recursive($parentid, $categories) {
    $catData = $productCatExt->getCategoryAndParentByCatId($parentid);
    if (count($catData) > 0) {
        $categories .= $catData->productCatName . '/';
        return getProductPath_recursive($catData->productCatParent, $categories);
    }
    return substr($categories, 0, strlen($categories) - 1);
}

---

🔒 Security & Permissions

Authentication

include_once("../public/authentication.php");
// Applied before any report generation

Input Validation

• All numeric inputs cast to appropriate types
• Category level validation
• Store ID validation
• Quantity range validation

Query Security

• Uses parameterized queries through RedBeanPHP
• Input filtering for all user-provided data
• SQL injection prevention

---

📊 Performance Considerations

Database Optimization Tips

1. Critical Indexes:

- storedetail(storeid, productid) for store filtering

- storedetail(productquantity) for quantity filtering

- product(productCatId) for category filtering

- productcat(productCatParent) for hierarchy queries

2. Query Optimization:

- Dynamic WHERE clause building to avoid unnecessary joins

- Category hierarchy pre-calculation

- Efficient recursive category lookups

3. Memory Management:

- Large result sets may require pagination

- Category hierarchy caching

- Image loading optimization

Performance Issues

-- Slow query example
SELECT storedetail.*, product.* 
FROM storedetail 
JOIN product ON storedetail.productid = product.productId
WHERE product.productCatId IN (SELECT id FROM productcat WHERE parentid = ?)

-- Better approach: pre-calculate category list
SELECT storedetail.*, product.*
FROM storedetail 
JOIN product ON storedetail.productid = product.productId  
WHERE product.productCatId IN (1,2,3,4,5,6) -- Pre-built list

---

🐛 Common Issues & Troubleshooting

1. Incorrect Inventory Values

Issue: Totals don't match expected values

Cause: Wrong valuation method or missing price data

Debug:

-- Check product pricing data
SELECT productId, productName, productBuyPrice, lastbuyprice, meanbuyprice 
FROM product WHERE productId = [ID];

-- Check program settings
SELECT * FROM programsettings WHERE programsettingsid = 1;

2. Category Filtering Not Working

Issue: Products from wrong categories appear

Cause: Category hierarchy not properly calculated

Debug:

// Enable category debug
global $catsIDS;
echo "Category IDs: " . $catsIDS . "<br>";

// Check category relationships
$result = $productCatExt->queryByParentExt2($categoryId);
print_r($result);

3. Missing Products in Report

Issue: Expected products don't appear

Cause: Zero quantity filter or store restrictions

Debug:

// Check store filter
if ($storeId != '-1') {
    echo "Filtering by store: " . $storeId;
}

// Check quantity filter
if ($hideZeroQuantity == 1) {
    echo "Hiding zero quantity products";
}

4. Discount Calculations Wrong

Issue: Discounts not applied correctly

Cause: Discount type confusion

Debug:

SELECT productid, selldiscount, discounttype FROM storedetail 
WHERE selldiscount > 0;

---

🧪 Testing Scenarios

Test Case 1: Basic Price List

1. Select single store
2. Select root category (includes all subcategories)
3. Enable all display columns
4. Verify all products appear
5. Check total calculations

Test Case 2: Category Hierarchy

1. Create parent category with children  
2. Add products to different levels
3. Filter by parent category
4. Verify all children products included
5. Test leaf category filtering

Test Case 3: Valuation Methods

1. Set up products with different price history
2. Test each valuation method:
   - FIFO (first)
   - LIFO (last) 
   - Weighted average (mean)
   - With discounts/tax variants
3. Verify calculations match expected values

Test Case 4: Filtering Options

1. Test quantity range filters
2. Test zero quantity hiding
3. Test store combinations
4. Verify filter combinations work together

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• storedetailController.md - Store inventory management
• productController.md - Product management
• productCatController.md - Category management

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Documentation Viewer

Online Downloads Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action / Show Report Form

2. show() - Main Report Generation

3. Inventory Valuation Logic

4. Discount Application

5. Category Hierarchy Processing

6. getAllSubCat() - Recursive Category Search

7. Barcode Generation Logic

8. addPreDigitsToNum() - Barcode Formatting

9. Alternative Show Functions

showByProductNameAndStore()

showBystoreName()

showByProductCatNameAndStoreId()

showAll()

🔄 Workflows

Workflow 1: Product Price List Generation

Workflow 2: Category Hierarchy Filtering

🌐 URL Routes & Actions

🧮 Calculation Methods

Inventory Valuation Calculation

Discount Application

Category Path Building

🔒 Security & Permissions

Authentication

Input Validation

Query Security

📊 Performance Considerations

Database Optimization Tips

Performance Issues

🐛 Common Issues & Troubleshooting

1. Incorrect Inventory Values

2. Category Filtering Not Working

3. Missing Products in Report

4. Discount Calculations Wrong

🧪 Testing Scenarios

Test Case 1: Basic Price List

Test Case 2: Category Hierarchy

Test Case 3: Valuation Methods

Test Case 4: Filtering Options

📚 Related Documentation