Product Serial Report Controller Documentation

File: /controllers/productserailreportController.php

Purpose: Manages product serial number tracking, barcode reports, and product expiration reporting

Last Updated: December 20, 2024

Total Functions: 8

Lines of Code: ~398

---

📋 Overview

The Product Serial Report Controller is a specialized reporting module that provides comprehensive tracking and reporting for serialized products with barcodes and expiration dates. It handles:

• • Individual product serial number tracking
• • Product expiration date validation reports
• • Barcode-based product searches
• • Serial number lifecycle management (creation, tracking, deletion)
• • Product category path navigation
• • Validity period analysis for products
• • Serial number deletion management
• • Product quantity and expiration monitoring

Primary Functions

• ✅ Generate product serial reports by barcode
• ✅ Track product expiration dates
• ✅ Validate products approaching expiration
• ✅ Manage serial number deletion
• ✅ Display product category hierarchies
• ✅ Filter products by validity conditions
• ✅ Generate barcode-based reports
• ✅ Monitor product lifecycle status
• ✅ Calculate remaining product validity days
• ✅ Search products by expiration criteria

Related Controllers

• • productController.php - Product management
• • productReportsController.php - Product reporting
• • storedetailController.php - Stock management
• • productsParcodeController.php - Barcode management
• • sellbillController.php - Sales operations
• • buyBillController.php - Purchase operations

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Product Serial Report

Location: Line 279

Purpose: Generate detailed tracking report for a specific product serial number

Function Signature:

// Triggered when: do=show or empty $do
$productserailid = $_REQUEST["productserailid"];
function show($productserailid)

Process Flow:

1. Load product serial data with product information

2. Build product category path using recursive function

3. Calculate remaining days until expiration

4. Format dates and intervals for display

5. Return processed data with expiration calculations

Features:

• • Product category path display (full hierarchy)
• • Expiration date calculations with remaining days
• • Product name with complete category path
• • DateTime interval processing for accurate day calculations
• • Integration with sold product tracking

Date Calculation Logic:

$now = new DateTime(date('Y-m-d H:i:s'));
$endDate = $pro->enddate . ' ' . date('H:i:s');
$endDate = new DateTime($endDate);
$interval = $now->diff($endDate);
$pro->diff = (int) $interval->format('%r%a'); // Remaining days

---

2. showvalidity() - Expiration Validation Report

Location: Line 323

Purpose: Generate reports for products based on expiration conditions

Function Signature:

function showvalidity($condition, $daynumber, $productSearchId)

Process Flow:

1. Build query string with product filter if specified

2. Query products based on expiration conditions

3. Calculate remaining days for each product

4. Apply category path display based on settings

5. Assign processed data to template

Condition Types:

• • Products expiring within X days
• • Products already expired
• • Products with specific validity periods
• • Single product expiration analysis

Category Path Logic:

if ($_SESSION['hidecat'] == 1) {
    $parentId = $pro->productCatId;
    $pathArr = getProductPath_recursive($parentId, $categories);
    $pro->productName = $pro->productName . '/' . $pathArr;
} else {
    $pro->productName = $pro->productName;
}

---

3. getallproductserail() - Load All Product Serials

Location: Line 259

Purpose: Retrieve all product serial numbers with product information

Function Signature:

function getallproductserail()

Process Flow:

1. Call extended DAO method to get all serial data

2. Join with product table for complete information

3. Return array of product serial objects

Returns: Array of product serial objects with product names and details

---

4. delserail() - Serial Number Deletion

Location: Line 269

Purpose: Mark product serial numbers as deleted (soft delete)

Function Signature:

function delserail()

Process Flow:

1. Extract serial ID and deletion flag from GET parameters

2. Call extended DAO to update deletion status

3. Maintain data integrity by soft delete approach

Parameters:

• • $id - Product serial ID to delete
• • $del - Deletion flag (1 = deleted, 0 = active)

---

5. getProductPath_recursive() - Category Path Builder

Location: Line 308

Purpose: Build complete category hierarchy path for products

Function Signature:

function getProductPath_recursive($parentid, $categories)

Process Flow:

1. Get category data for current parent ID

2. Append category name to path string

3. Recursively call for parent category

4. Return complete path string

Recursion Logic:

if (count($catData) > 0) {
    $categories .= $catData->productCatName . '/';
    $newParentId = $catData->productCatParent;
    return getProductPath_recursive($newParentId, $categories);
}

---

6. loadProductCategories() - Product Category Navigation

Location: Line 356

Purpose: Load product categories for navigation and filtering

Function Signature:

function loadProductCategories()

Process Flow:

1. Query all products to get unique categories

2. For each product category, build complete path

3. Assign category data to template variables

4. Prepare navigation iteration counter

Template Variables:

• • $names{N} - Category path for product N
• • $parentId{N} - Parent category ID for product N
• • $itr - Total iteration counter

---

7. fetch_recursive() - Alternative Path Builder

Location: Line 381

Purpose: Alternative recursive function for category path building

Function Signature:

function fetch_recursive($parentid, $categories)

Process Flow:

1. Similar to getProductPath_recursive but with different structure

2. Includes parent name handling in recursion

3. Returns formatted category path string

Differences from getProductPath_recursive:

• • Includes explicit parent name handling
• • Different recursion termination logic
• • Used for specific navigation scenarios

---

8. getProductCatId() - Category ID Retrieval

Location: Line 253

Purpose: Get product category ID for a specific product

Function Signature:

function getProductCatId($productId)

Returns: Category ID for the specified product

---

🔄 Workflows

Workflow 1: Product Serial Report Generation

---

Workflow 2: Expiration Validity Report

---

🌐 URL Routes & Actions

Product Serial Report (do=show):

• • productserailid - Product serial ID

Validity Report (do=showvalidity):

• • condition - Expiration condition type
• • daynumber - Number of days for threshold
• • productId - Optional: specific product filter

Serial Deletion (do=tempdelete):

• • id - Product serial ID to delete
• • del - Deletion flag (0/1)

---

🧮 Calculation Methods

Expiration Days Calculation

$now = new DateTime(date('Y-m-d H:i:s'));
$endDate = $pro->enddate . ' ' . date('H:i:s');
$endDate = new DateTime($endDate);
$interval = $now->diff($endDate);
$pro->diff = (int) $interval->format('%r%a');
// %r = sign ("-" if negative), %a = total days

Category Path Building

function getProductPath_recursive($parentid, $categories) {
    $catData = $productCatExt->getCategoryAndParentByCatId($parentid);
    
    if (count($catData) > 0) {
        $categories .= $catData->productCatName . '/';
        $newParentId = $catData->productCatParent;
        return getProductPath_recursive($newParentId, $categories);
    }
    
    // Remove trailing slash
    $categories = substr($categories, 0, strlen($categories) - 1);
    return $categories;
}

Product Path Formatting

// Build complete product name with category path
$pro->productName = $pro->productName . '/' . $pathArr;

// Example output: "iPhone 13/Electronics/Mobile Phones/Smartphones"

---

🔒 Security & Permissions

Authentication Requirements

// All modification actions require authentication
include_once("../public/authentication.php");

Input Sanitization

• • Product serial IDs cast to integer for SQL safety
• • Date parameters validated before database queries
• • GET/POST parameters filtered through request handling
• • Soft delete approach maintains data integrity

Session Management

• • User session validation for all operations
• • Authentication check for deletion operations
• • Session-based category display preferences ($_SESSION['hidecat'])

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- productserial(productid, enddate)

- productserial(serialnumber)

- product(productcatid)

- productcat(productcatparent)

2. Query Optimization:

- Extended DAO methods use JOINs efficiently

- Recursive category functions cache results

- Date calculations done in PHP vs database

3. Memory Management:

- Recursive functions have depth limits

- Large product catalogs may need pagination

- Category path building cached per session

Known Performance Issues

-- Recursive category queries can be slow for deep hierarchies
-- Solution: Consider materialized path or closure table approaches

-- Date calculations on large datasets
-- Solution: Add computed columns for common date ranges

---

🐛 Common Issues & Troubleshooting

1. Missing Product Names in Reports

Issue: Product serials show without product information

Cause: Extended DAO queries failing to join properly

Debug:

SELECT ps.*, p.productname 
FROM productserial ps 
LEFT JOIN product p ON ps.productid = p.productid 
WHERE ps.productserialid = [ID];

2. Incorrect Expiration Calculations

Issue: Wrong remaining days displayed

Cause: Date format inconsistencies or timezone issues

Fix:

// Ensure consistent date format
$endDate = $pro->enddate . ' ' . date('H:i:s');
$endDate = new DateTime($endDate);

3. Category Path Building Errors

Issue: Infinite recursion or missing category names

Cause: Circular references in category hierarchy

Debug:

-- Check for circular references
SELECT * FROM productcat 
WHERE productcatid = productcatparent;

-- Verify hierarchy integrity
SELECT pc1.productcatname, pc2.productcatname as parent
FROM productcat pc1
LEFT JOIN productcat pc2 ON pc1.productcatparent = pc2.productcatid;

4. Serial Deletion Not Working

Issue: Products still appear after deletion

Cause: Soft delete flag not properly set

Fix:

// Verify deletion flag in queries
WHERE del = 0  // Only show active serials
WHERE del = 1  // Only show deleted serials

---

🧪 Testing Scenarios

Test Case 1: Basic Serial Report

1. Create product with serial number
2. Set expiration date in future
3. Generate serial report
4. Verify product name, category path, and days calculation
5. Check barcode display and serial number formatting

Test Case 2: Expiration Validation

1. Create products with various expiration dates
2. Test "within X days" condition
3. Test "expired" condition
4. Verify day calculations are accurate
5. Check product filtering works correctly

Test Case 3: Category Path Building

1. Create nested product categories (3+ levels deep)
2. Assign products to different category levels
3. Generate reports and verify complete paths
4. Test with hidecat session setting enabled/disabled

Test Case 4: Serial Deletion

1. Create product serial record
2. Mark as deleted via tempdelete action
3. Verify serial doesn't appear in active reports
4. Check data integrity maintained

Debug Mode Enable

// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug category recursion
echo "Building path for category: " . $parentId . "<br>";

// Debug date calculations
echo "End date: " . $endDate->format('Y-m-d H:i:s') . "<br>";
echo "Interval: " . $interval->format('%r%a') . " days<br>";

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • productController.md - Product management
• • productReportsController.md - Product reporting
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur