Category Sell Rate Report Controller Documentation

File: /controllers/catsellrateReportController.php

Purpose: Generates category-wise sales rate analysis and slow-moving inventory reports

Last Updated: December 20, 2024

Total Functions: 3

Lines of Code: ~399

---

📋 Overview

The Category Sell Rate Report Controller is a comprehensive inventory analysis module that provides detailed insights into product category performance and sales velocity. It handles:

• • Category-wise sales performance analysis
• • Slow-moving inventory identification
• • Product velocity tracking by quantity thresholds
• • Sales vs returns analysis by category
• • Multi-system integration (sales & optical bills)
• • Date range performance comparison
• • Product-level drill-down analysis
• • Zero-sales product identification

Primary Functions

• ✅ Generate category sales velocity reports
• ✅ Identify slow-moving inventory
• ✅ Track sales vs return patterns
• ✅ Product performance analysis
• ✅ Multi-system transaction integration
• ✅ Date range filtering
• ✅ Threshold-based analysis
• ✅ Zero-movement product detection

Related Controllers

• • sellbillController.php - Sales operations
• • returnsellbillController.php - Return operations
• • clientReportsController.php - Client reporting
• • productController.php - Product management

---

🗄️ Database Tables

Primary Sales Tables

🔑 Key Functions

1. Default Action (show) - Report Interface

Location: Line 189

Purpose: Display category sales rate analysis interface

Function Signature:

if ($do == "show" || empty($do))

Process Flow:

1. Authentication Check:

   include_once("../public/authentication.php");
   ```

2. **Load Support Data**:
   ```php
   $catData = $productCatDAO->queryByCondition(0);
   $smarty->assign("catData", $catData);
   
   $youtubes = $youtubeLinkDAO->queryAll();
   $smarty->assign("youtubes", $youtubes);
   ```

3. **Parse Report Parameters**:
   ```php
   $startDate = $_REQUEST['from'];
   $endDate = $_REQUEST['to'];
   $catid = $_REQUEST['catid'];
   $numberid = $_REQUEST['numberid'];  // Quantity threshold
   ```

4. **Generate Report If Parameters Present**:
   ```php
   if (isset($catid) && $catid != "-1" && !empty($numberid) && (!empty($startDate) && !empty($endDate))) {
       getallcatsellratex($catid, $numberid, $startDate, $endDate);
   }
   ```

**Features**:
- **Parameter Validation**: Ensures all required filters are present
- **Category Filtering**: Supports category-specific analysis
- **Threshold Analysis**: Uses numberid as movement threshold
- **Tutorial Integration**: Includes helpful video links

---

### 2. **getClients()** - Client Data Loader
**Location**: Line 236  
**Purpose**: Load active client list for reference

**Function Signature**:

function getClients()

**Implementation**:

function getClients() {

global $clientDAO;

$clientsData = $clientDAO->queryByCondition(0);

return $clientsData;

}

---

### 3. **getallcatsellratex()** - Main Analysis Engine
**Location**: Line 327  
**Purpose**: Generate comprehensive category sales rate analysis with slow-moving inventory identification

**Function Signature**:

function getallcatsellratex($catid, $numberid, $startDate, $endDate)

**Process Flow**:

1. **Define Transaction Sources**:
   ```php
   $tablename = "sellbillController.php";      // Regular sales
   $tablename1 = "returnsellbillController.php"; // Returns
   $tablename2 = "sellbillandruternController.php"; // Combined bills
   $tablename3 = "bills.php";                  // Optical bills
   $tablename4 = "billsreturn.php";           // Optical returns
   ```

2. **Get Category Movement Data**:
   ```php
   $allData = $StorereportEx->queryWithtablenameforcatrate($catid, $startDate, $endDate, 
                                                         $tablename, $tablename1, $tablename2, 
                                                         $tablename3, $tablename4);
   ```

3. **Process Each Product in Category**:
   ```php
   foreach ($allData as $data) {
       // Get sell quantity
       $allselldata = $StorereportEx->loadWithtablenamexandtypeforcatrateWithDate($data->productid, 1, 
                                    $tablename, $tablename1, $tablename2, $tablename3, $tablename4, 
                                    $startDate, $endDate);
       $sellquantity = $allselldata->productquantity;
       
       // Get return quantity  
       $allreturndata = $StorereportEx->loadWithtablenamexandtypeforcatrateWithDate($data->productid, 0,
                                      $tablename, $tablename1, $tablename2, $tablename3, $tablename4,
                                      $startDate, $endDate);
       $returnquantity = $allreturndata->productquantity;
       
       // Calculate net movement
       $sellandreturn = $sellquantity - $returnquantity;
       
       // Apply threshold filter
       if ($sellandreturn <= $numberid) {
           $newarray[$data->productid] = $data;
           $totalnumofsells += $sellandreturn;
       }
   }
   ```

4. **Include Zero-Movement Products**:
   ```php
   $productsArray = $productExt->getCatProducts($catid);
   if (!empty($productsArray)) {
       foreach ($productsArray as $product) {
           if (!in_array($product->productId, $productArr)) {
               $proObj = new stdClass();
               $proObj->productid = $product->productId;
               $proObj->productName = $product->productName;
               $proObj->sellquantity = 0;
               $proObj->returnquantity = 0;
               $proObj->sellandreturn = 0;
               $newarray[$product->productId] = $proObj;
           }
       }
   }
   ```

**Key Features**:
- **Multi-System Integration**: Combines regular sales and optical system data
- **Threshold Filtering**: Shows products with movement <= specified threshold
- **Zero-Movement Detection**: Includes products with no sales activity
- **Net Movement Calculation**: Accounts for returns to show true velocity
- **Category Coverage**: Ensures all category products are analyzed

---

## 🔄 Workflows

### Workflow 1: Slow-Moving Inventory Analysis

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

│ START: Category Sales Rate Analysis │

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

│

▼

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

│ 1. Set Analysis Parameters │

│ - Select product category (catid) │

│ - Set movement threshold (numberid) │

│ - Define date range (from/to) │

│ - Submit analysis request │

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

│

▼

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

│ 2. Validate Parameters │

│ - Ensure category is selected (≠ -1) │

│ - Verify threshold is specified │

│ - Check date range is complete │

│ - Proceed if all parameters valid │

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

│

▼

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

│ 3. Query Product Movement Data │

│ - Get products with movement in category │

│ - Include data from multiple systems: │

│ ├─ Regular sales (sellbillController.php) │

│ ├─ Returns (returnsellbillController.php) │

│ ├─ Combined bills (sellbillandruternController.php) │

│ ├─ Optical sales (bills.php) │

│ └─ Optical returns (billsreturn.php) │

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

│

▼

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

│ 4. Calculate Movement Metrics │

│ FOR EACH product with activity: │

│ │ │

│ ├─→ Get total sales quantity │

│ │ └─ Query across all sales systems │

│ │ │

│ ├─→ Get total return quantity │

│ │ └─ Query across all return systems │

│ │ │

│ ├─→ Calculate net movement │

│ │ └─ Sales quantity - Return quantity │

│ │ │

│ └─→ Apply threshold filter │

│ └─ Include if movement <= threshold │

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

│

▼

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

│ 5. Include Zero-Movement Products │

│ - Get all products in selected category │

│ - Identify products with no sales activity │

│ - Add to analysis with zero quantities │

│ - Ensure complete category coverage │

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

│

▼

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

│ 6. Generate Analysis Report │

│ - Display slow-moving/non-moving products │

│ - Show sales vs return patterns │

│ - Include total movement summary │

│ - Present via catsellrateReportview template │

└─────────────────────────────────────────────────────────────┘

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Category analysis interface |

### Report Parameters (REQUEST)
**Category Sales Rate Analysis**:
- `from` - Start date for analysis period
- `to` - End date for analysis period  
- `catid` - Product category ID (-1 for all)
- `numberid` - Movement threshold (show products with movement <= this number)

### Usage Examples

Show slow-moving products in category 5 (threshold: 10 units)

catsellrateReportController.php?do=show&catid=5&numberid=10&from=2024-01-01&to=2024-12-31

Show non-moving products (threshold: 0)

catsellrateReportController.php?do=show&catid=3&numberid=0&from=2024-01-01&to=2024-12-31

---

## 🧮 Calculation Methods

### Net Movement Calculation

// For each product, calculate net sales movement

$sellquantity = $StorereportEx->loadWithtablenamexandtypeforcatrateWithDate($productid, 1,

$tablename, $tablename1, $tablename2, $tablename3, $tablename4,

$startDate, $endDate)->productquantity;

$returnquantity = $StorereportEx->loadWithtablenamexandtypeforcatrateWithDate($productid, 0,

$tablename, $tablename1, $tablename2, $tablename3, $tablename4,

$startDate, $endDate)->productquantity;

$sellandreturn = $sellquantity - $returnquantity;

### Slow-Moving Product Identification

// Apply threshold filter to identify slow-moving products

if ($sellandreturn <= $numberid) {

$newarray[$data->productid] = $data;

$totalnumofsells += $sellandreturn;

}

### Zero-Movement Product Detection

$productsArray = $productExt->getCatProducts($catid);

foreach ($productsArray as $product) {

if (!in_array($product->productId, $productArr)) {

// Product has no movement in the period

$proObj = new stdClass();

$proObj->productid = $product->productId;

$proObj->productName = $product->productName;

$proObj->sellquantity = 0;

$proObj->returnquantity = 0;

$proObj->sellandreturn = 0;

$newarray[$product->productId] = $proObj;

}

}

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `storereport(productid, tablename, storereporttype)`
   - `product(productCatId, productId)`
   - `sellbilldetail(sellbilldetailproductid)`
   - `returnsellbilldetail(returnsellbilldetailproductid)`

2. **Query Performance Issues**:
   - **N+1 Query Problem**: Each product requires 2 separate queries (sell + return)
   - **Multiple System Queries**: Each calculation hits 5 different table sources
   - **Large Category Analysis**: Categories with many products can be slow

### Optimization Recommendations

-- Instead of individual product queries, use aggregated approach

SELECT

productid,

SUM(CASE WHEN storereporttype = 1 THEN productquantity ELSE 0 END) as sellquantity,

SUM(CASE WHEN storereporttype = 0 THEN productquantity ELSE 0 END) as returnquantity,

SUM(CASE WHEN storereporttype = 1 THEN productquantity ELSE 0 END) -

SUM(CASE WHEN storereporttype = 0 THEN productquantity ELSE 0 END) as netmovement

FROM storereport

WHERE productid IN (SELECT productId FROM product WHERE productCatId = ?)

AND tablename IN (?, ?, ?, ?, ?)

AND storereportdate BETWEEN ? AND ?

GROUP BY productid

HAVING netmovement <= ?;

---

## 🔒 Security & Permissions

### Access Control

include_once("../public/authentication.php");

### Input Handling

$startDate = $_REQUEST['from'];

$endDate = $_REQUEST['to'];

$catid = $_REQUEST['catid'];

$numberid = $_REQUEST['numberid'];

**Security Note**: Direct use of `$_REQUEST` without sanitization poses potential security risks.

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Products in Report**
**Issue**: Report shows no products despite category having items  
**Cause**: All products exceed movement threshold or date range has no activity

**Debug**:

-- Check if products exist in category

SELECT COUNT(*) FROM product WHERE productCatId = {catid} AND conditions = 0;

-- Check movement in date range

SELECT productid, SUM(productquantity) FROM storereport

WHERE productid IN (SELECT productId FROM product WHERE productCatId = {catid})

AND storereportdate BETWEEN '{startDate}' AND '{endDate}'

GROUP BY productid;

### 2. **Performance Issues with Large Categories**
**Issue**: Report times out or loads very slowly  
**Cause**: N+1 query problem with large product counts

**Solution**: Implement batch processing or aggregated queries

### 3. **Inconsistent Movement Calculations**
**Issue**: Net movement doesn't match expected values  
**Cause**: Missing data from one of the 5 transaction sources

**Debug**: Check each transaction source individually:

-- Check each table source

SELECT tablename, COUNT(*) FROM storereport WHERE productid = {id} GROUP BY tablename;

---

## 🧪 Testing Scenarios

### Test Case 1: Slow-Moving Product Analysis

1. Select category with mixed activity products

2. Set threshold to 5 units

3. Set 3-month date range

4. Verify products with ≤5 net sales appear

5. Check calculations: sells - returns = net movement

### Test Case 2: Zero-Movement Detection

1. Select category with some inactive products

2. Set threshold to 0

3. Verify products with no sales appear in results

4. Confirm zero quantities are displayed correctly

### Test Case 3: Multi-System Integration

1. Create test data in both sales and optical systems

2. Run analysis covering both systems

3. Verify movements from all sources are included

4. Check that returns are properly subtracted

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • productController.php - Product management
• • StorereportController.php - Inventory reporting

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: Optimize query performance and add input sanitization