# Store Detail Price Controller Documentation

**File**: `/controllers/storedetailpriceController.php`  
**Purpose**: Product inventory price list reporting and analysis system  
**Last Updated**: December 20, 2024  
**Total Functions**: 15+  
**Lines of Code**: ~1,133

---

## 📋 Overview

The Store Detail Price Controller is a specialized inventory reporting module that provides detailed product price analysis and inventory valuation capabilities. It handles:
- Product price list reports with multiple price levels
- Inventory valuation using different costing methods
- Store-based product quantity analysis  
- Category-based product filtering
- Multi-level product pricing displays
- Product image and description reporting
- Barcode/parcode generation and display
- Quantity filtering and zero-stock options
- Discount calculations and application
- Comprehensive inventory pricing reports

### Primary Functions
- [x] Generate product price lists
- [x] Inventory valuation calculations
- [x] Store-based product filtering
- [x] Category-based product grouping
- [x] Multi-level pricing displays (price4-price13)
- [x] Quantity range filtering
- [x] Zero stock hiding options
- [x] Product image management
- [x] Barcode/parcode handling
- [x] Discount application logic

### Related Controllers
- [storedetailController.php](storedetailController.md) - Basic inventory operations
- [productController.php](productController.md) - Product management
- [storeController.php](storeController.md) - Store management
- [productReportsController.php](productReportsController.md) - Product reporting

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storedetail** | Store inventory quantities | storedetailid, storeid, productid, productquantity, storedetaildate |
| **product** | Product master data | productId, productName, productCatId, productBuyPrice, productSellAllPrice, logo |
| **store** | Store/warehouse definitions | storeid, storeName |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |

### Price Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Multi-level pricing | price4, price5, price6, price7, price8, price9, price10, price11, price12, price13 |
| **product** | Buy price tracking | lastbuyprice, lastbuyprice_withDiscount, meanbuyprice, meanbuyprice_withDiscount |
| **product** | Sell pricing | productSellUnitPrice, productSellAllPrice, productSellHalfPrice, selldiscount |

### System Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System configuration | lastprice, Inventoryevaluation, usedParcode, parcodeDigits |
| **buybilldetail** | Purchase history | buybilldetailproductid, buybilldetailprice |
| **returnbuybilldetail** | Purchase returns | returnbuybilldetailproductid |
| **usergroup** | User permissions | usergroupid, hidecat |
| **youtubelink** | Help tutorials | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **show() / Default Action** - Main Price Report
**Location**: Line 129  
**Purpose**: Generate comprehensive product price list with filtering options

**Function Signature**:
```php
// Triggered when: do=show or empty $do
if ($do == "show" || empty($do))
```

**Process Flow**:
1. Load store data for dropdown selection
2. Get category hierarchy for filtering
3. Process POST parameters for filtering criteria
4. Call `show()` function with filters
5. Display results via `showprice.html` template

**Features**:
- Store selection filtering
- Category hierarchical filtering  
- Product selection options
- Custom column selection
- Price calculation options

---

### 2. **show()** - Core Report Generation Logic
**Location**: Line 291  
**Purpose**: Build detailed product price report with customizable columns

**Function Signature**:
```php
function show()
```

**Process Flow**:
1. Parse request parameters for filters and display options
2. Build dynamic SELECT columns based on user choices
3. Construct WHERE clause with filters:
   - Product ID filter
   - Store ID filter  
   - Category ID filter (with subcategories)
   - Quantity range filters
   - Zero quantity hiding
4. Execute query via `queryWithqueryStringprice()`
5. Process each product record:
   - Apply inventory valuation method
   - Calculate product paths/categories
   - Apply discount calculations
   - Generate barcodes
6. Return data array and sum value

**Dynamic Column Building**:
```php
if (isset($productSellUnitPrice) && $productSellUnitPrice == '1') {
    $str2 .= ',product.productSellUnitPrice';
}
// Repeated for all optional columns
```

**Inventory Valuation Methods**:
```php
switch ($Programsettingdata->Inventoryevaluation) {
    case "first": $pro_price = (float) $myproduct->productBuyPrice; break;
    case "last": $pro_price = (float) $myproduct->lastbuyprice; break;
    case "mean": $pro_price = (float) $myproduct->meanbuyprice; break;
    case "last_discount": $pro_price = (float) $myproduct->lastbuyprice_withDiscount; break;
    case "mean_discount": $pro_price = (float) $myproduct->meanbuyprice_withDiscount; break;
    default: $pro_price = (float) $myproduct->overAllAveragePrice; break;
}
```

---

### 3. **showByProductNameAndStore()** - Product/Store Report
**Location**: Line 736  
**Purpose**: Generate report for specific product in specific store

**Function Signature**:
```php
function showByProductNameAndStore()
```

**Process Flow**:
1. Get product ID and store ID from request
2. Query `storedetail` by product and store
3. Apply inventory valuation calculations
4. Calculate product category paths
5. Return formatted data array

---

### 4. **showBystoreName()** - Store-Based Report
**Location**: Line 819  
**Purpose**: Show all products in a specific store

**Function Signature**:
```php
function showBystoreName()
```

**Process Flow**:
1. Get store ID from request
2. Query all products in specified store
3. Apply valuation calculations for each product
4. Return comprehensive store inventory report

---

### 5. **showByProductCatNameAndStoreId()** - Category/Store Report
**Location**: Line 902  
**Purpose**: Filter products by category within specific store

**Function Signature**:
```php
function showByProductCatNameAndStoreId()
```

**Process Flow**:
1. Get category and store IDs
2. Query products matching category in store
3. Process inventory valuations
4. Generate category-specific report

---

### 6. **getCategoryChilds()** - Category Hierarchy
**Location**: Line 1084  
**Purpose**: Build hierarchical category structure for filtering

**Function Signature**:
```php
function getCategoryChilds($parentid = 0)
```

**Returns**:
```php
return array($parentobject, $childsArray);
// [0] = Parent category object
// [1] = Array of child categories
```

---

### 7. **getAllSubCat()** - Recursive Category Processing
**Location**: Line 1094  
**Purpose**: Recursively get all subcategories for filtering

**Function Signature**:
```php
function getAllSubCat($catid, $mode)
```

**Modes**:
- `mode = 1`: Get all subcategories
- `mode = 2`: Get only leaf-level categories

**Global Variables Updated**:
- `$catsIDS`: Comma-separated category IDs
- `$lastLevelCatIDS`: Array of leaf categories

---

### 8. **getProductPath_recursive()** - Product Category Path
**Location**: Line 1049  
**Purpose**: Build full category path for product display

**Function Signature**:
```php
function getProductPath_recursive($parentid, $categories)
```

**Returns**: String like "Electronics/Computers/Laptops"

---

## 🔄 Workflows

### Workflow 1: Basic Price List Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Access Price Report                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Initial Form                                       │
│     - Load store dropdown data                              │
│     - Load category hierarchy                               │
│     - Set default display options                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. User Selects Filters                                    │
│     - Choose store (optional)                               │
│     - Select category (optional)                            │
│     - Choose display columns                                │
│     - Set quantity filters                                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Dynamic Query                                     │
│     - Construct SELECT clause with chosen columns           │
│     - Build WHERE clause with filters                       │
│     - Include category subcategories if applicable          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Execute Query and Process Results                       │
│     FOR EACH product found:                                 │
│       │                                                     │
│       ├─→ Apply inventory valuation method                 │
│       │   (first/last/mean/discount/tax)                   │
│       │                                                     │
│       ├─→ Calculate product category path                  │
│       │   (recursive parent lookup)                        │
│       │                                                     │
│       ├─→ Apply discount calculations                      │
│       │   (direct amount or percentage)                    │
│       │                                                     │
│       └─→ Generate barcode if needed                       │
│           (ID-based or custom parcode)                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Totals and Display                            │
│     - Sum inventory values                                   │
│     - Apply template formatting                             │
│     - Display via showprice.html                           │
└─────────────────────────────────────────────────────────────┘
```

---

## 🧮 Calculation Methods

### Inventory Valuation Options
```php
switch ($Programsettingdata->Inventoryevaluation) {
    case "first":           // Original purchase price
    case "last":            // Latest purchase price  
    case "mean":            // Average purchase price
    case "last_discount":   // Latest with discount
    case "mean_discount":   // Average with discount
    case "tax":             // Latest with tax
    case "mean_tax":        // Average with tax
    default:                // Overall average
}
```

### Discount Processing
```php
if ($selldiscount == 1 && $storedetail->selldiscount > 0) {
    if ($storedetail->discounttype == 0) { // Direct discount
        $storedetail->productSellAllPrice -= $storedetail->selldiscount;
    } elseif ($storedetail->discounttype == 1) { // Percentage
        $storedetail->productSellAllPrice -= 
            $storedetail->productSellAllPrice * ($storedetail->selldiscount / 100);
    }
}
```

### Barcode Generation
```php
if ($Programsettingdata->usedParcode == 1) { // ID-based
    $storedetail->parcode = "i" . addPreDigitsToNum($myproduct->productId);
} else { // Custom parcode
    $storedetail->parcode = $myproduct->parcode;
}
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Main price report form and results |

### Required Parameters

**Main Price Report**:
- `storeId` - Store filter (-1 for all stores)
- `productCatId` - Category filter (-1 for all categories)
- `productId` - Specific product filter (-1 for all products)
- Display options: `productSellUnitPrice`, `productBuyPrice`, etc.
- Filters: `hideZeroQuantity`, `quantityGreaterThan`, `quantityLessThan`

---

## 🔒 Security & Permissions

### User Permission Levels
```php
$usergroupdata = $myUserGroupRecord->load($_SESSION['usergroupid']);
if ($usergroupdata->hidecat == 1) {
    // Show full product category paths
    $storedetail->productName = $storedetail->productName . '/' . $pathArr;
} else {
    // Show product name only
    $storedetail->productName = $storedetail->productName;
}
```

### Input Sanitization
- Category ID cast to integer: `(int) $productCatId`
- Quantity filters cast to float: `(float) $quantityGreaterThan`
- SQL injection prevented by DAO layer parameterization

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `storedetail(storeid, productid)`
   - `product(productCatId)`
   - `productcat(productCatParent)`

2. **Query Optimization**:
   - Dynamic column selection reduces data transfer
   - Category hierarchy pre-built to avoid N+1 queries
   - Price calculations done in PHP vs database

3. **Memory Management**:
   - Large product catalogs may require pagination
   - Category recursion depth should be monitored

### Known Performance Issues
```sql
-- This recursive category lookup can be slow
SELECT * FROM productcat WHERE productCatParent = ?
-- Solution: Consider materialized category paths or LTREE
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Product Prices**
**Issue**: Products show zero or null prices  
**Cause**: Inventory valuation method references missing price fields

**Debug**:
```sql
SELECT productId, productBuyPrice, lastbuyprice, meanbuyprice 
FROM product WHERE productId = [ID];
```

### 2. **Category Filter Not Working**
**Issue**: Category filter doesn't include subcategories  
**Cause**: `getAllSubCat()` not properly building category list

**Fix**: Check `$catsIDS` global variable building

### 3. **Quantity Filters Ignored**
**Issue**: Quantity range filters don't work  
**Cause**: Float conversion or SQL syntax errors

**Debug**:
```php
echo "Query: " . $queryString; // Check built query
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Price Report
```
1. Access controller without filters
2. Verify all stores and categories load
3. Check default column display
4. Confirm price calculations match settings
```

### Test Case 2: Category Filtering
```
1. Select parent category
2. Verify subcategories included
3. Test leaf category selection
4. Check category path display
```

### Test Case 3: Discount Application
```
1. Enable discount display option
2. Test both direct and percentage discounts
3. Verify calculations across all price levels
4. Check rounding accuracy
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [storedetailController.md](storedetailController.md) - Basic inventory operations  
- [productController.md](productController.md) - Product management
- [Database Schema Documentation](#) - Table relationships

---

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