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

### Related Controllers
- [storedetailController.php](storedetailController.md) - Store inventory management
- [productController.php](productController.md) - Product management
- [productCatController.php](productCatController.md) - Category management
- [storereportController.php](storereportController.md) - 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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
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**:
```php
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                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. User Selects Filters                                    │
│     - Store selection                                       │
│     - Category selection                                    │
│     - Display column options                                │
│     - Quantity range filters                                │
│     - Sort preferences                                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Dynamic Query                                     │
│     ┌─────────────────────────────────────────────────────┐ │
│     │ Base Query: storedetail + product + store           │ │
│     │ + Apply store filter                                 │ │
│     │ + Apply category filter (with subcats)              │ │
│     │ + Apply quantity range filters                      │ │
│     │ + Apply zero quantity filter                        │ │
│     └─────────────────────────────────────────────────────┘ │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Query & Load Data                               │
│     - Query database with filters                          │
│     - Load product details                                  │
│     - Load store information                                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process 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                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate 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                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Get Selected Category                                   │
│     - User selects category from hierarchy                  │
│     - Category ID extracted from form                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Initialize Category List                                │
│     - Start with selected category ID                       │
│     - $catsIDS = selected_category_id                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Find 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                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Build SQL Filter                                        │
│     - $catsIDS contains all relevant category IDs          │
│     - Add to query: "product.productCatId IN ($catsIDS)"   │
│     - Filter includes parent + all descendants             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Execute 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
```php
// 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
```php
// 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
```php
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
```php
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
```sql
-- 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**:
```sql
-- 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**:
```php
// 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**:
```php
// 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**:
```sql
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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [storedetailController.md](storedetailController.md) - Store inventory management
- [productController.md](productController.md) - Product management
- [productCatController.md](productCatController.md) - Category management

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur