# Stock Reports Controller Documentation

**File**: `/controllers/stockReportsController.php`  
**Purpose**: Generates detailed stock reports for product serials with advanced filtering and data management  
**Last Updated**: December 21, 2024  
**Total Functions**: 5  
**Lines of Code**: ~218

---

## 📋 Overview

The Stock Reports Controller is a specialized reporting module that provides comprehensive product serial tracking and inventory analysis. It handles:
- Product serial number tracking and reporting
- Category-based product filtering with hierarchical support
- Date range filtering for inventory analysis
- Advanced DataTables integration with AJAX
- Product serial deletion and management
- Tax and pricing calculations for serialized products
- Dynamic category tree building for multi-level filtering

### Primary Functions
- [x] Generate product serial reports with filtering
- [x] Category-based product search with tree structure
- [x] AJAX-powered DataTables integration
- [x] Product serial deletion management
- [x] Tax and total price calculations
- [x] Date range filtering capabilities
- [x] Advanced search across multiple fields

### Related Controllers
- [productController.php](#) - Product management
- [productserialController.php](#) - Serial number management
- [buybillController.php](#) - Purchase transactions
- [categoryController.php](#) - Product categories

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **productserial** | Product serial tracking | productserailid, productid, serialnumber, don, chassisNo, motorNo, theColor, del |
| **product** | Product master data | productid, productName, productCatId |
| **productcat** | Product categories | productCatId, productCatName, productCatParent |

### Transaction Tables (Referenced)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **buybilldetail** | Purchase bill details | buybilldetailid, buybillid, buybilldetailprice, buybilldetailquantity, payedtax |
| **buybill** | Purchase bills | buybillid, buybilldate |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Report Interface Display
**Location**: Line 25  
**Purpose**: Display the main stock reports interface with YouTube tutorial links

**Process Flow**:
1. Load YouTube tutorial links for user guidance
2. Display header template
3. Render stock reports interface (`stockReportsview/index.html`)
4. Display footer template

**Features**:
- Clean interface for report generation
- Integrated tutorial support
- Responsive template system

---

### 2. **select2categories()** - Category Search for Select2
**Location**: Line 52  
**Purpose**: Provide AJAX-powered category search for Select2 dropdown components

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

**Process Flow**:
1. Get search term from POST data
2. Query categories with LIKE search on category names
3. Format results for Select2 compatibility:
   - `id` field for category ID
   - `text` field for display name
4. Return JSON response with up to 50 results

**Query Logic**:
```php
$productsData = R::getAll("SELECT productCatId, productCatName as name
FROM productcat
WHERE productCatName LIKE '%" . $name . "%' limit 50");
```

---

### 3. **select2products()** - Product Search with Category Filtering
**Location**: Line 70  
**Purpose**: Provide hierarchical product search with category tree support

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

**Process Flow**:
1. Get search term and category ID from POST data
2. Build category hierarchy using `buildTree()` if category specified
3. Construct dynamic query with category filtering
4. Search products with concatenated product/category names
5. Format results for Select2 with combined names
6. Return JSON response with up to 50 results

**Category Tree Logic**:
```php
if ($categoryid) {
    $allcutsup = '' . $categoryid;
    buildTree($categoryid); // Recursively add child categories
    $query_search .= ' AND product.productCatId in (' . $allcutsup . ')';
}
```

**Product Query**:
```php
$productsData = R::getAll("SELECT productId,CONCAT(productName,'/',productCatName) as name
FROM product left join productcat on product.productCatId = productcat.productCatId
WHERE CONCAT(productName,'/',productCatName) LIKE '%" . $name . "%' $query_search limit 50");
```

---

### 4. **buildTree()** - Recursive Category Tree Builder
**Location**: Line 43  
**Purpose**: Build hierarchical category tree for filtering support

**Function Signature**:
```php
function buildTree($parentId = 0)
```

**Process Flow**:
1. Query all categories with specified parent ID
2. For each category found:
   - Add category ID to global category list
   - Recursively call `buildTree()` for child categories
3. Build complete category hierarchy for filtering

**Recursive Logic**:
```php
global $allcutsup;
$categories = R::getAll('select * from productcat where productCatParent = ?', [$parentId]);
foreach ($categories as $category) {
    $allcutsup .= "," . $category['productCatId'];
    buildTree($category['productCatId']); // Recursion for children
}
```

---

### 5. **showajax()** - Main Report Data Provider
**Location**: Line 95  
**Purpose**: Generate comprehensive stock report data with advanced filtering and calculations

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

**Process Flow**:
1. Parse DataTables parameters (pagination, sorting, search)
2. Build complex query with multiple filters:
   - Date range filtering
   - Category hierarchy filtering  
   - Product-specific filtering
   - General search across multiple fields
3. Execute query with proper joins
4. Calculate totals and taxes for each record
5. Format data for DataTables display
6. Add summary row with aggregated totals
7. Return JSON response

**Key Variables**:
- `$columns` - DataTables column mapping
- `$fromdate`, `$todate` - Date range filters
- `$categoryid`, `$productid` - Product filters
- `$searchQuery` - Dynamic WHERE clause
- `$buybilldetailquantity`, `$buybilldetailprice`, `$payedtax`, `$total` - Calculated totals

**Filter Building Logic**:
```php
// Category filtering with hierarchy
if ($categoryid != '') {
    $allcutsup = '' . $categoryid;
    buildTree($categoryid);
    $searchQuery .= ' AND product.productCatId in (' . $allcutsup . ')';
}

// Product filtering
if ($productid != '') {
    $searchQuery .= " and productserial.productid = " . $productid . " ";
}

// Date range filtering
if ($fromdate != '' && $todate != '') {
    $searchQuery .='and buybilldate >= "' . $fromdate . ' 00-00-00" and buybilldate <= "' . $todate . ' 23-59-55" ';
}
```

**Financial Calculations**:
```php
foreach ($rResult as $row) {
    $buybilldetailquantity += $row["don"];
    $buybilldetailprice += $row["buybilldetailprice"] * $row["don"];
    $payedtax += ($row["buybilldetailprice"] * $row["don"] * $row["payedtax"]) / 100;
    $total += round((($row["buybilldetailprice"] * $row["don"]) + 
                    (($row["buybilldetailprice"] * $row["don"] * $row["payedtax"]) / 100)), 2);
}
```

---

### 6. **deleteproductserial()** - Serial Deletion Management
**Location**: Line 209  
**Purpose**: Soft delete product serials for inventory management

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

**Process Flow**:
1. Get product serial ID from POST data
2. Update product serial record to set `del = 1` (soft delete)
3. Return success (1) or failure (0) status

**Deletion Logic**:
```php
R::exec("UPDATE `productserial` SET `del`= 1 WHERE productserailid = '" . $productserailid . "' ");
```

---

## 🔄 Workflows

### Workflow 1: Stock Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│                START: Report Request                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Request Parameters                                │
│     - Extract date range (from/to)                         │
│     - Get category and product filters                     │
│     - Parse DataTables parameters (pagination, search)     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Category Hierarchy                                │
│     IF categoryid specified:                               │
│       │                                                     │
│       ├─→ Initialize category list with selected ID        │
│       ├─→ Call buildTree() recursively                     │
│       └─→ Build IN clause with all child categories        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Construct Dynamic Query                                 │
│     - Add category filter if specified                     │
│     - Add product filter if specified                      │
│     - Add date range filter if specified                   │
│     - Add search filter for multiple fields                │
│     - Add sorting and pagination                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Execute Query and Calculate Totals                     │
│     - Run main query with all joins                        │
│     - Initialize totals (quantity, price, tax, total)      │
│     │                                                       │
│     FOR EACH result row:                                    │
│       │                                                     │
│       ├─→ Add quantity to total                            │
│       ├─→ Calculate line price (price * quantity)          │
│       ├─→ Calculate tax amount                             │
│       ├─→ Calculate total with tax                         │
│       └─→ Round financial figures                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Format DataTables Response                              │
│     - Build data array with formatted rows                 │
│     - Add delete button for each row                       │
│     - Add summary row with aggregated totals               │
│     - Return JSON response with metadata                   │
└─────────────────────────────────────────────────────────────┘
```

### Workflow 2: Category Tree Building
```
┌─────────────────────────────────────────────────────────────┐
│             START: buildTree(parentId)                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Query Child Categories                                  │
│     - Find all categories with productCatParent = parentId │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Each Child Category                             │
│     FOR EACH child category found:                         │
│       │                                                     │
│       ├─→ Add category ID to global list                   │
│       └─→ Recursively call buildTree(childId)              │
│           └─→ This builds the complete hierarchy          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display report interface |
| `do=select2products` | `select2products()` | AJAX product search for Select2 |
| `do=select2categories` | `select2categories()` | AJAX category search for Select2 |
| `do=showajax` | `showajax()` | Generate report data via AJAX |
| `do=deleteproductserial` | `deleteproductserial()` | Soft delete product serial |

### Required Parameters by Action

**Product Search** (`do=select2products`):
- `searchTerm` - Text to search in product names (via POST)
- `categoryid` - Category filter ID (optional, via POST)

**Category Search** (`do=select2categories`):
- `searchTerm` - Text to search in category names (via POST)

**Report Data** (`do=showajax`):
- `fromdate` - Start date filter (YYYY-MM-DD, via POST)
- `todate` - End date filter (YYYY-MM-DD, via POST)
- `data1` - Category ID filter (via POST)
- `data2` - Product ID filter (via POST)
- DataTables parameters: `start`, `length`, `order`, `search`

**Serial Deletion** (`do=deleteproductserial`):
- `productserailid` - Serial ID to delete (via POST)

---

## 🧮 Calculation Methods

### Financial Calculations

**Line Price Calculation**:
```php
$linePrice = $row["buybilldetailprice"] * $row["don"];
```

**Tax Amount Calculation**:
```php
$taxAmount = ($row["buybilldetailprice"] * $row["don"] * $row["payedtax"]) / 100;
```

**Total with Tax**:
```php
$totalWithTax = $linePrice + $taxAmount;
$totalWithTax = round($totalWithTax, 2);
```

### Aggregation Logic
```php
// Running totals for summary
$buybilldetailquantity += $row["don"];                    // Total quantity
$buybilldetailprice += $linePrice;                        // Total price
$payedtax += $taxAmount;                                  // Total tax
$total += $totalWithTax;                                  // Grand total
```

### Category Hierarchy Building
```php
// Recursive category inclusion
$allcutsup = '' . $categoryid;  // Start with selected category
buildTree($categoryid);          // Add all child categories
// Result: "1,5,6,12,15" for use in SQL IN clause
```

---

## 🔒 Security & Permissions

### Input Sanitization
- All POST parameters are used directly in SQL queries - **Security Risk**
- No explicit input validation or sanitization implemented
- Relies on framework-level protection

### SQL Injection Risks
**High Risk Areas**:
```php
// Direct string concatenation in SQL
"WHERE productCatName LIKE '%" . $name . "%'"
"and buybilldate >= \"" . $fromdate . " 00-00-00\""
```

**Recommendations**:
- Implement parameterized queries for all user inputs
- Add input validation for dates and numeric values
- Sanitize search terms before SQL inclusion

### Authentication
- No authentication check in AJAX functions
- Default action has no authentication requirement
- Consider adding authentication to sensitive operations

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Critical Indexes Required**:
   - `productserial(productid, del)` - For product filtering
   - `productcat(productCatParent)` - For hierarchy building
   - `buybill(buybilldate)` - For date range filtering
   - `buybilldetail(buybilldetailid)` - For join optimization

2. **Query Optimization**:
   - Complex joins may be slow on large datasets
   - Category hierarchy building can be expensive
   - Consider materialized path for categories

3. **Memory Management**:
   - No pagination limits enforced on large result sets
   - Summary calculations performed in PHP rather than SQL
   - Category tree building stores all IDs in memory

### Known Performance Issues
```sql
-- This query can be very slow with large datasets
SELECT productserial.*, productName, productCatName, 
       buybilldetailprice, buybilldetailquantity, payedtax, buybilldate
FROM productserial 
LEFT JOIN product ON product.productid = productserial.productid
LEFT JOIN productcat ON product.productCatId = productcat.productCatId
LEFT JOIN buybilldetail ON buybilldetail.buybilldetailid = productserial.buybilldetailid
LEFT JOIN buybill ON buybilldetail.buybillid = buybill.buybillid
WHERE productserial.don > 0 and productserial.del = 0
```

**Optimizations**:
- Add covering indexes for the SELECT columns
- Consider query result caching for static data
- Implement proper pagination limits

---

## 🐛 Common Issues & Troubleshooting

### 1. **Category Tree Not Building**
**Issue**: Child categories not included in search results  
**Cause**: `buildTree()` function not being called or incorrect parent relationships

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

### 2. **Financial Calculations Incorrect**
**Issue**: Tax or total amounts don't match expected values  
**Cause**: Missing tax rates or incorrect calculation logic

**Debug**:
```php
// Manual calculation check
$linePrice = $price * $quantity;
$taxAmount = $linePrice * $taxRate / 100;
$total = $linePrice + $taxAmount;
echo "Line: $linePrice, Tax: $taxAmount, Total: $total";
```

### 3. **Serial Deletion Fails**
**Issue**: Product serials not being deleted  
**Cause**: Database permissions or constraint violations

**Debug**:
```sql
-- Check if record exists and current del status
SELECT productserailid, del FROM productserial WHERE productserailid = [ID];

-- Verify update permissions
SHOW GRANTS FOR CURRENT_USER();
```

### 4. **Large Dataset Performance**
**Issue**: Reports timeout or run very slowly  
**Cause**: Missing indexes or inefficient queries

**Solutions**:
- Add proper indexes on join columns
- Implement query result caching
- Add pagination limits to prevent oversized results
- Consider database-level aggregation instead of PHP calculations

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Report Generation
```
1. Set date range covering known data
2. Leave category and product filters empty
3. Verify all expected serials appear
4. Check total calculations are accurate
5. Confirm pagination works correctly
```

### Test Case 2: Category Hierarchy Filtering
```
1. Select parent category with known children
2. Verify buildTree() includes all child categories
3. Check results include products from all levels
4. Validate category names display correctly
```

### Test Case 3: Financial Calculations
```
1. Use records with known price and tax values
2. Manually calculate expected totals
3. Compare with system calculations
4. Verify rounding is applied correctly
5. Check summary row matches detail totals
```

### Test Case 4: Serial Deletion
```
1. Identify active serial (del = 0)
2. Execute deletion via AJAX
3. Verify del flag updated to 1
4. Confirm serial no longer appears in reports
5. Check deletion is reversible if needed
```

---

## 📚 Related Documentation

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

---

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