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

✅ Generate product serial reports with filtering
✅ Category-based product search with tree structure
✅ AJAX-powered DataTables integration
✅ Product serial deletion management
✅ Tax and total price calculations
✅ Date range filtering capabilities
✅ 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:

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:

$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:

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:

if ($categoryid) {
    $allcutsup = '' . $categoryid;
    buildTree($categoryid); // Recursively add child categories
    $query_search .= ' AND product.productCatId in (' . $allcutsup . ')';
}

Product Query:

$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:

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:

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:

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:

// 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:

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:

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:

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

---

🔄 Workflows

Workflow 1: Stock Report Generation

┌─────────────────────────────────────────────────────────────┐

START: Report Request

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Parse Request Parameters

- Extract date range (from/to)

- Get category and product filters

- Parse DataTables parameters (pagination, search)

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Build Category Hierarchy

IF categoryid specified:

│

→ Initialize category list with selected ID

→ Call buildTree() recursively

│ └─→ Build IN clause with all child categories │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Construct 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

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Execute 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 │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

5Format 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)

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Query Child Categories

- Find all categories with productCatParent = parentId

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Process 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:

$linePrice = $row["buybilldetailprice"] * $row["don"];

Tax Amount Calculation:

$taxAmount = ($row["buybilldetailprice"] * $row["don"] * $row["payedtax"]) / 100;

Total with Tax:

$totalWithTax = $linePrice + $taxAmount;
$totalWithTax = round($totalWithTax, 2);

Aggregation Logic

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

Category Hierarchy Building

// 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:

// 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

-- 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:

// 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:

// 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:

-- 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 - 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

📚 ERP Docs

StockReports Documentation

Stock Reports Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Report Interface Display

2. select2categories() - Category Search for Select2

3. select2products() - Product Search with Category Filtering

4. buildTree() - Recursive Category Tree Builder

5. showajax() - Main Report Data Provider

6. deleteproductserial() - Serial Deletion Management

🔄 Workflows

Workflow 1: Stock Report Generation

Workflow 2: Category Tree Building

🌐 URL Routes & Actions

🧮 Calculation Methods

Financial Calculations

Aggregation Logic

Category Hierarchy Building

🔒 Security & Permissions

Input Sanitization

SQL Injection Risks

Authentication

📊 Performance Considerations

Database Optimization Tips

Known Performance Issues

🐛 Common Issues & Troubleshooting

1. Category Tree Not Building

2. Financial Calculations Incorrect

3. Serial Deletion Fails

4. Large Dataset Performance

🧪 Testing Scenarios

Test Case 1: Basic Report Generation

Test Case 2: Category Hierarchy Filtering

Test Case 3: Financial Calculations

Test Case 4: Serial Deletion

📚 Related Documentation