# Store Parcode Controller Documentation

**File**: `/controllers/storeparcodeController.php`  
**Purpose**: Generates beginning period inventory reports with pricing calculations and category hierarchies  
**Last Updated**: December 21, 2024  
**Total Functions**: 10  
**Lines of Code**: ~511

---

## 📋 Overview

The Store Parcode Controller is a comprehensive inventory reporting module that provides beginning period stock analysis with advanced filtering and pricing calculations. It handles:
- Beginning period inventory reporting across multiple stores
- Product category hierarchy navigation with recursive path building
- Multiple pricing strategies (last price, mean price, general price)
- Store-based inventory filtering with user permissions
- Product categorization with full path display
- Dynamic query building for flexible reporting
- Comprehensive inventory valuation calculations

### Primary Functions
- [x] Generate beginning period inventory reports
- [x] Multi-store inventory analysis
- [x] Category hierarchy navigation and display
- [x] Multiple pricing strategy support
- [x] User permission-based store filtering
- [x] Product path construction for categorization
- [x] Dynamic query building for complex filtering
- [x] Comprehensive inventory valuation

### Related Controllers
- [storedetailController.php](#) - Store detail management
- [productController.php](#) - Product management
- [categoryController.php](#) - Product category management
- [inventoryController.php](#) - Inventory operations

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storedetail** | Store inventory details | storedetailid, productid, storeid, productquantity, storedetaildate |
| **product** | Product master data | productId, productName, productCatId, lastbuyprice, meanbuyprice, productBuyPrice |
| **store** | Store master data | storeId, storeName |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |

### Transaction Tables (Referenced)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **buybilldetail** | Purchase details for pricing | buybilldetailid, buybilldetailproductid, buybilldetailprice |
| **returnbuybilldetail** | Return purchase details | returnbuybilldetailid, returnbuybilldetailproductid |

### Configuration Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System pricing configuration | programsettingsid, lastprice |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action / show()** - Report Interface
**Location**: Line 111  
**Purpose**: Display the inventory report interface with store data and YouTube tutorials

**Process Flow**:
1. Load store data via `loadStore()`
2. Load YouTube tutorial links for user guidance
3. Parse request parameters for filtering
4. Display report interface template
5. Set up custom validation and display flags

**Features**:
- Store selection interface
- Tutorial integration
- Parameter handling for productId, storeId, productCatId

---

### 2. **loadProducts()** - Product Data Loader
**Location**: Line 142  
**Purpose**: Load all active product data for selection

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

**Returns**: Array of product objects with condition filtering

---

### 3. **loadStore()** - Store Data Loader
**Location**: Line 153  
**Purpose**: Load all active store data for selection

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

**Returns**: Array of store objects with condition filtering

---

### 4. **loadProductCategories()** - Category Hierarchy Builder
**Location**: Line 163  
**Purpose**: Build complete product category hierarchy with path construction

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

**Process Flow**:
1. Query all products via `queryAllProducts()`
2. For each product, get its category ID
3. Build recursive category path using `fetch_recursive()`
4. Assign category names and parent relationships to template
5. Return products data with category information

**Template Variables Created**:
- `names{N}` - Category path for product N
- `parentId{N}` - Parent category ID for product N
- `itr` - Iterator count for template loops

---

### 5. **fetch_recursive()** - Recursive Category Path Builder
**Location**: Line 188  
**Purpose**: Recursively build category path strings from leaf to root

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

**Process Flow**:
1. Load category data for given parent ID
2. Add current category name to path string
3. If parent has its own parent, recursively call for parent
4. Build complete path with '/' separators
5. Return cleaned path string (remove trailing '/')

**Path Construction Logic**:
```php
if (count($catData) > 0) {
    $categories .= $catData->productCatName . '/';
    $newParentId = $catData->productCatParent;
    
    if ($newParentId != 0) {
        $newParentName = $catData->parentName;
        $categories .= $newParentName . '/';
        fetch_recursive($newParentId, $categories);
    }
}
```

---

### 6. **show()** - Main Report Generation
**Location**: Line 207  
**Purpose**: Generate comprehensive beginning period inventory report with multiple filtering options

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

**Process Flow**:
1. Parse request parameters (productId, storeId, productCatId, order)
2. Build dynamic query string based on filters
3. Load store and product data for names/descriptions
4. Execute query via `queryWithqueryString()`
5. Calculate inventory values using configured pricing strategy
6. Build category paths for display
7. Return data array and sum value

**Filter Building Logic**:
```php
$queryString = ' AND';

if (isset($productId) && $productId != '-1' && $productId != '') {
    $myprodactdata = $myProductRecord->load($productId);
    $message = $message . " للمنتج  :" . $myprodactdata->productName . "  ";
    $queryString .= ' storedetail.productId = ' . $productId . ' AND';
}

if (isset($storeId) && $storeId != '-1') {
    $queryString .= '  storedetail.storeid = ' . $storeId . ' AND';
    $mystordata = $myStoreRecord->load($storeId);
    $message = $message . "    والمخزن : " . $mystordata->storeName . "";
}

if (isset($productCatId) && $productCatId != '-1') {
    $queryString .= '  product.productCatId = ' . $productCatId . ' AND';
    $myProductCatData = $productCatDAO->load($productCatId);
    $message = $message . $myProductCatData->productCatName . " والمخزن " . $mystordata->storeName . "";
}
```

**Pricing Strategy Implementation**:
```php
$Programsettingdata = $ProgramsettingDAO->load(1);
foreach ($storedetailData as $storedetail) {
    $myproduct = $myProductRecord->load($storedetail->productid);
    if ($Programsettingdata->lastprice == "0") {
        $storedetail->productBuyPrice = $myproduct->lastbuyprice;
    } else {
        $storedetail->productBuyPrice = $myproduct->meanbuyprice;
    }
    
    $productBuyPrice = $storedetail->productBuyPrice;
    $productQuantity = $storedetail->productquantity;
    $SumProductPrice = $productBuyPrice * $productQuantity;
    $sumValue = $SumProductPrice + $sumValue;
}
```

---

### 7. **showByProductNameAndStore()** - Product-Store Specific Report
**Location**: Line 319  
**Purpose**: Generate report for specific product in specific store

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

**Similar to `show()` but with hardcoded productId and storeId filtering**

---

### 8. **showBystoreName()** - Store-Specific Report
**Location**: Line 368  
**Purpose**: Generate report for all products in a specific store

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

**Uses `queryWithStoreId()` for store-specific inventory data**

---

### 9. **showByProductCatNameAndStoreId()** - Category-Store Report
**Location**: Line 419  
**Purpose**: Generate report for specific product category in specific store

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

**Uses `queryWithProductCatAndStoreId()` for filtered data**

---

### 10. **showAll()** - Complete Inventory Report
**Location**: Line 469  
**Purpose**: Generate comprehensive report for all inventory with ordering

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

**Features**:
- Complete inventory overview
- Ordering support via `queryWithOrder()`
- Category path construction for all products
- Total value calculation across all inventory

---

### 11. **getProductPath_recursive()** - Enhanced Path Builder
**Location**: Line 496  
**Purpose**: Build product category paths with enhanced recursive logic

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

**Difference from `fetch_recursive()`**:
- Simplified path building
- Direct return of recursive results
- No parent name handling
- Cleaner string termination

---

## 🔄 Workflows

### Workflow 1: Beginning Period Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Inventory Report Request               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Request Parameters                                │
│     - Extract productId, storeId, productCatId             │
│     - Get ordering preferences                              │
│     - Initialize message string                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Dynamic Query String                              │
│     START with ' AND'                                       │
│     │                                                       │
│     IF productId specified:                                 │
│       ├─→ Add product filter to query                      │
│       └─→ Update message with product name                 │
│     │                                                       │
│     IF storeId specified:                                   │
│       ├─→ Add store filter to query                        │
│       └─→ Update message with store name                   │
│     │                                                       │
│     IF productCatId specified:                              │
│       ├─→ Add category filter to query                     │
│       └─→ Update message with category name                │
│     │                                                       │
│     Clean up trailing 'AND' from query string              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Query and Load Data                             │
│     - Execute queryWithqueryString() with filters          │
│     - Load system pricing configuration                    │
│     - Initialize sum value calculator                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Inventory Record                           │
│     FOR EACH storedetail record:                            │
│       │                                                     │
│       ├─→ Load product data for pricing                    │
│       │                                                     │
│       ├─→ Determine pricing strategy:                      │
│       │   ├─ IF lastprice == "0": Use lastbuyprice         │
│       │   └─ ELSE: Use meanbuyprice                        │
│       │                                                     │
│       ├─→ Calculate line value:                            │
│       │   └─ productBuyPrice × productQuantity             │
│       │                                                     │
│       ├─→ Add to running sum                               │
│       │                                                     │
│       └─→ Build product category path:                     │
│           ├─ Get parentId from product                     │
│           ├─ Call getProductPath_recursive()                │
│           └─ Update productName with full path             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Return Results                                          │
│     - Return array with [storedetailData, sumValue]        │
│     - Assign message to template                            │
│     - Include all calculated pricing                       │
└─────────────────────────────────────────────────────────────┘
```

### Workflow 2: Category Path Construction
```
┌─────────────────────────────────────────────────────────────┐
│         START: getProductPath_recursive(parentid)          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Category Data                                      │
│     - Call getCategoryAndParentByCatId(parentid)           │
│     - Get category name and parent relationship            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Category Information                            │
│     IF category data exists:                                │
│       │                                                     │
│       ├─→ Add category name to path with '/' separator     │
│       │                                                     │
│       ├─→ Get parent category ID                           │
│       │                                                     │
│       └─→ IF parent exists (ID != 0):                      │
│           └─→ Recursively call for parent category        │
│                └─→ This builds the full hierarchy         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Clean and Return Path                                   │
│     - Remove trailing '/' character                        │
│     - Return complete category path string                 │
│     Example: "Electronics/Computers/Laptops"               │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=show` or `do=` (empty) | `show()` | Main report interface and generation |

### Required Parameters

**Main Report** (`do=show` or empty):
- `productId` - Product filter (optional, -1 for all)
- `storeId` - Store filter (optional, -1 for all)
- `productCatId` - Category filter (optional, -1 for all)
- `order` - Sort order specification (optional)

### Filter Combinations Supported
1. **All Products, All Stores**: No filters specified
2. **Specific Product**: `productId` only
3. **Specific Store**: `storeId` only  
4. **Specific Category**: `productCatId` only
5. **Product in Store**: `productId` + `storeId`
6. **Category in Store**: `productCatId` + `storeId`
7. **Product in Category**: `productId` + `productCatId`
8. **Full Filter**: `productId` + `storeId` + `productCatId`

---

## 🧮 Calculation Methods

### Inventory Valuation

**Pricing Strategy Selection**:
```php
$Programsettingdata = $ProgramsettingDAO->load(1);
if ($Programsettingdata->lastprice == "0") {
    $unitPrice = $product->lastbuyprice;    // Most recent purchase price
} else {
    $unitPrice = $product->meanbuyprice;    // Average purchase price
}
```

**Line Value Calculation**:
```php
$productBuyPrice = $storedetail->productBuyPrice;
$productQuantity = $storedetail->productquantity;
$lineValue = $productBuyPrice * $productQuantity;
```

**Total Inventory Value**:
```php
$totalValue = 0;
foreach ($inventoryItems as $item) {
    $lineValue = $item->unitPrice * $item->quantity;
    $totalValue += $lineValue;
}
```

### Category Path Building
```php
function buildCategoryPath($categoryId) {
    $path = "";
    $category = loadCategory($categoryId);
    
    while ($category && $category->productCatParent != 0) {
        $path = $category->productCatName . "/" . $path;
        $category = loadCategory($category->productCatParent);
    }
    
    return rtrim($path, "/"); // Remove trailing slash
}
```

---

## 🔒 Security & Permissions

### Authentication
```php
include_once("../public/authentication.php");
```
- Standard session-based authentication required
- All actions require valid user session

### Input Validation
- Basic parameter validation for numeric IDs
- No explicit SQL injection protection implemented
- Relies on DAO layer for query safety

### Data Access Control
- No user-specific data filtering implemented
- All authenticated users can access all store/product data
- Consider implementing store-based permissions for multi-tenant usage

**Security Improvements Needed**:
- Add input sanitization for all parameters
- Implement user-based store access controls
- Add validation for date ranges and numeric parameters

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Critical Indexes Required**:
   - `storedetail(productid, storeid)` - For filtered queries
   - `product(productCatId)` - For category filtering  
   - `productcat(productCatParent)` - For hierarchy navigation
   - `storedetail(storedetaildate)` - For date-based queries

2. **Query Optimization**:
   - Multiple separate queries for different filter combinations
   - Consider consolidating into single dynamic query
   - Category path building done in PHP vs database

3. **Memory Management**:
   - Recursive category path building can be memory intensive
   - No pagination for large inventory datasets
   - Consider caching category paths

### Known Performance Issues

**Category Path Building**:
- Recursive function calls for each product category
- Multiple database queries for deep category hierarchies
- No caching of previously built paths

**Large Dataset Handling**:
- No pagination implemented
- All inventory loaded into memory simultaneously
- Calculations performed in PHP rather than database

**Optimization Recommendations**:
```php
// Cache category paths
$categoryPaths = [];
if (!isset($categoryPaths[$categoryId])) {
    $categoryPaths[$categoryId] = buildCategoryPath($categoryId);
}

// Use database aggregation instead of PHP loops
SELECT 
    SUM(storedetail.productquantity * product.lastbuyprice) as totalValue
FROM storedetail 
JOIN product ON storedetail.productid = product.productId
WHERE [filters]
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Inventory Values**
**Issue**: Inventory totals don't match expected values  
**Cause**: Wrong pricing strategy or missing product prices

**Debug**:
```sql
-- Check pricing configuration
SELECT lastprice FROM programsettings WHERE programsettingsid = 1;

-- Verify product prices
SELECT productId, productName, lastbuyprice, meanbuyprice, productBuyPrice
FROM product 
WHERE productId = [PRODUCT_ID];
```

### 2. **Category Paths Not Displaying**
**Issue**: Products show without full category paths  
**Cause**: Recursive path building fails or missing parent relationships

**Debug**:
```sql
-- Check category hierarchy
SELECT productCatId, productCatName, productCatParent 
FROM productcat 
WHERE productCatId = [CATEGORY_ID];

-- Find orphaned categories
SELECT * FROM productcat 
WHERE productCatParent NOT IN (SELECT productCatId FROM productcat)
AND productCatParent != 0;
```

### 3. **Query Performance Issues**
**Issue**: Reports load slowly or timeout  
**Cause**: Missing indexes or inefficient query structure

**Solutions**:
```sql
-- Add required indexes
CREATE INDEX idx_storedetail_product_store ON storedetail(productid, storeid);
CREATE INDEX idx_product_category ON product(productCatId);
CREATE INDEX idx_category_parent ON productcat(productCatParent);
```

### 4. **Empty Results with Valid Filters**
**Issue**: No data returned despite valid filter parameters  
**Cause**: Incorrect query string building or data integrity issues

**Debug**:
```php
// Enable query debugging
echo "Query String: " . $queryString . "<br>";

// Check data exists
SELECT COUNT(*) FROM storedetail 
WHERE productid = [PRODUCT_ID] AND storeid = [STORE_ID];
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Inventory Report
```
1. Set no filters (all products, all stores)
2. Verify all active inventory appears
3. Check total values calculate correctly
4. Confirm category paths display properly
5. Validate pricing strategy is applied
```

### Test Case 2: Multi-Level Category Filtering
```
1. Select parent category with multiple child levels
2. Verify all products in category hierarchy appear
3. Check category path construction is complete
4. Validate parent-child relationships work correctly
```

### Test Case 3: Store-Specific Filtering
```
1. Select specific store
2. Verify only products in that store appear
3. Check store name appears in report message
4. Validate quantities are store-specific
```

### Test Case 4: Pricing Strategy Validation
```
1. Change programsettings.lastprice value
2. Generate same report with different setting
3. Verify prices change according to strategy
4. Check totals recalculate correctly
```

### Test Case 5: Complex Multi-Filter
```
1. Apply product + store + category filters simultaneously
2. Verify results match all criteria
3. Check message construction includes all filters
4. Validate no incorrect data leakage
```

---

## 📚 Related Documentation

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

---

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