# Store Inquiries Report Controller Documentation

**File**: `/controllers/storeinquiriesreportController.php`  
**Purpose**: Generates inventory inquiry reports with product details, pricing, and discounts  
**Last Updated**: December 21, 2024  
**Total Functions**: 1  
**Lines of Code**: ~180

---

## 📋 Overview

The Store Inquiries Report Controller is a simple reporting module that provides detailed product information across multiple stores with discount calculations. It handles:
- Product search by ID or barcode
- Store-based product inquiries
- Discount application (fixed amount or percentage)
- Product pricing retrieval (all, half, unit prices)
- Cross-store product availability checking
- Multi-format discount calculations

### Primary Functions
- [x] Search products by ID or barcode
- [x] Apply discount calculations
- [x] Display product details with pricing
- [x] Handle size/color product variations
- [x] Store-based product filtering
- [x] Real-time price adjustments

### Related Controllers
- [storedetailController.php](#) - Store detail management
- [productController.php](#) - Product management
- [storereportController.php](#) - Store reports

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **storedetail** | Product quantities in stores | storedetailid, productid, storeid, productquantity |
| **product** | Product master data | productId, productName, productSellAllPrice, productSellHalfPrice, productSellUnitPrice |
| **store** | Store master data | storeId, storeName |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System configuration | programsettingsid, settingkey, settingvalue |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **showAll()** - Product Search and Display
**Location**: Line 124  
**Purpose**: Search for products by ID or barcode, apply discounts, and display detailed product information

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

**Process Flow**:
1. Parse input parameters:
   - `productId` - Product ID for search
   - `productparcode` - Product barcode for search  
   - `selldiscount` - Flag to apply discounts
2. Handle size/color product variants:
   - Extract product ID, size ID, and color ID from complex IDs
   - Format: `{productId}hasSizeColor-{sizeId}-{colorId}`
3. Build query string based on search criteria
4. Execute product search via `queryAllByProudctnameandParcode()`
5. Apply discount calculations if requested:
   - Fixed amount discount: Direct subtraction
   - Percentage discount: Calculate percentage and subtract
6. Round percentage discounts to 2 decimal places
7. Return processed product data

**Key Variables**:
- `$productId` - Primary product identifier
- `$productparcode` - Product barcode identifier  
- `$selldiscount` - Discount application flag
- `$queryString` - Dynamic SQL WHERE clause
- `$sizeId`, `$colorId` - Product variant identifiers

**Discount Calculation Logic**:
```php
if ($selldiscount == 1 && $item->selldiscount > 0) {
    if ($all->discounttype == 0) { // Fixed amount discount
        $item->productSellAllPrice -= $item->selldiscount;
        $item->productSellHalfPrice -= $item->selldiscount;
        $item->productSellUnitPrice -= $item->selldiscount;
    } elseif ($item->discounttype == 1) { // Percentage discount
        $item->productSellAllPrice -= $item->productSellAllPrice * ($item->selldiscount / 100);
        $item->productSellHalfPrice -= $item->productSellHalfPrice * ($item->selldiscount / 100);
        $item->productSellUnitPrice -= $item->productSellUnitPrice * ($item->selldiscount / 100);
        // Round to 2 decimal places
        $item->productSellAllPrice = round($item->productSellAllPrice, 2);
        $item->productSellHalfPrice = round($item->productSellHalfPrice, 2);
        $item->productSellUnitPrice = round($item->productSellUnitPrice, 2);
    }
}
```

---

## 🔄 Workflows

### Workflow 1: Product Search and Price Calculation
```
┌─────────────────────────────────────────────────────────────┐
│                START: Product Search Request               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Input Parameters                                  │
│     - Check if search by productId or barcode              │
│     - Extract discount flag                                 │
│     - Handle size/color product variants                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Query String                                      │
│     IF productId provided:                                  │
│       │                                                     │
│       ├─→ Check for size/color variant                     │
│       │   ├─ Extract productId, sizeId, colorId            │
│       │   └─ Add product filter to query                   │
│       │                                                     │
│     ELSIF barcode provided:                                 │
│       │                                                     │
│       └─→ Add barcode filter to query                      │
│                                                             │
│     ELSE: Return empty result                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Product Search                                  │
│     - Query database via queryAllByProudctnameandParcode() │
│     - Retrieve all matching products                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Discount Calculations                           │
│     IF selldiscount flag is set:                           │
│       │                                                     │
│       FOR EACH product with discount > 0:                  │
│         │                                                   │
│         IF discounttype == 0 (Fixed Amount):               │
│         │                                                   │
│         ├─→ Subtract discount from all price levels        │
│         │                                                   │
│         ELSIF discounttype == 1 (Percentage):              │
│         │                                                   │
│         ├─→ Calculate percentage discount                   │
│         ├─→ Apply to all price levels                      │
│         └─→ Round results to 2 decimal places              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Return Processed Results                                │
│     - Return array of products with adjusted prices        │
│     - Include all store details and quantities             │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display search form |
| `do=showAll` | `showAll()` | Execute product search and display results |

### Required Parameters

**Default Action** (`do=` empty):
- `productId` - Product ID for search (optional)
- `parcode` - Product barcode for search (optional)  
- `selldiscount` - Apply discounts flag (optional)

**Product Search** (`do=showAll`):
- `productId` - Product ID for search (required if no barcode)
- `parcode` - Product barcode for search (required if no product ID)
- `selldiscount` - Apply discounts flag (0 or 1)

### Special Parameter Handling
- **Size/Color Products**: Use format `{productId}hasSizeColor-{sizeId}-{colorId}`
- **Empty Search**: Returns empty result set if both productId and barcode are empty
- **Discount Flag**: Only applies if `selldiscount=1` AND product has discount > 0

---

## 🧮 Calculation Methods

### Discount Calculations

**Fixed Amount Discount**:
```php
if ($item->discounttype == 0) {
    $newPrice = $originalPrice - $discountAmount;
}
```

**Percentage Discount**:
```php
if ($item->discounttype == 1) {
    $discountAmount = $originalPrice * ($discountPercentage / 100);
    $newPrice = $originalPrice - $discountAmount;
    $newPrice = round($newPrice, 2); // Round to 2 decimal places
}
```

### Size/Color Product Parsing
```php
if (strpos($productId, "hasSizeColor") !== false) {
    $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
    $productId = $productIdComplex[0];    // Main product ID
    $sizeId = $productIdComplex[1];       // Size variant ID
    $colorId = $productIdComplex[2];      // Color variant ID
}
```

---

## 🔒 Security & Permissions

### Input Sanitization
- All `$_REQUEST` parameters are filtered through the framework
- Product IDs cast to appropriate types before database queries
- SQL injection prevented by DAO layer parameterized queries

### Authentication
- Uses standard authentication via `../public/authentication.php`
- Session-based user validation

### Data Access Control
- No user-specific restrictions implemented
- All authenticated users can access product inquiry data

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `product(productId)` - Primary key (already exists)
   - `storedetail(productid, storeid)` - Composite for joins
   - `product(productName)` - For name-based searches

2. **Query Optimization**:
   - Uses efficient DAO layer with prepared statements
   - Single query retrieves all needed product/store data
   - Minimal database round trips

### Known Performance Issues
- No pagination implemented - large product sets may cause memory issues
- Discount calculations performed in PHP rather than database
- No caching of frequently accessed product data

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Results Returned**
**Issue**: Search returns empty result set  
**Cause**: Both productId and barcode parameters are empty

**Debug**:
```php
// Check query string construction
if (empty($productId) && empty($productparcode)) {
    $queryString .= "product.productId = ''"; // Returns no results
}
```

### 2. **Discount Not Applied**
**Issue**: Expected discount calculations not showing  
**Cause**: `selldiscount` flag not set or product has no discount value

**Fix**:
```php
// Ensure conditions are met
if ($selldiscount == 1 && $item->selldiscount > 0) {
    // Discount will be applied
}
```

### 3. **Size/Color Products Not Found**
**Issue**: Complex product IDs not parsing correctly  
**Cause**: Incorrect format or missing size/color data

**Debug**:
```php
// Verify format: "123hasSizeColor-5-7"
if (strpos($productId, "hasSizeColor") !== false) {
    // Should extract: productId=123, sizeId=5, colorId=7
}
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Product Search
```
1. Use valid product ID
2. Set selldiscount=0
3. Verify product details returned
4. Check all price fields populated
```

### Test Case 2: Discount Application
```
1. Use product with configured discount
2. Set selldiscount=1  
3. Test both fixed amount and percentage discounts
4. Verify price calculations are correct
5. Check rounding for percentage discounts
```

### Test Case 3: Size/Color Product Search
```
1. Use complex ID format: "123hasSizeColor-5-7"
2. Verify parsing extracts correct IDs
3. Check query finds specific variant
4. Validate result contains size/color data
```

### Test Case 4: Barcode Search
```
1. Use product barcode instead of ID
2. Verify search finds correct product
3. Check all functionality works same as ID search
```

---

## 📚 Related Documentation

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

---

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