Sales Returns Ratings Report Controller Documentation

File: /controllers/salesreturnsratingsreport.php

Purpose: Generates return rate analysis reports by product category with sales vs returns comparison

Last Updated: December 21, 2024

Total Functions: 2

Lines of Code: ~82

---

📋 Overview

The Sales Returns Ratings Report Controller is a lightweight reporting module that analyzes return patterns by product category. It provides:

• • Category-wise sales vs returns comparison
• • Return rate calculation and analysis
• • Simple AJAX-based data retrieval
• • Category selection with autocomplete search
• • Minimal footprint with focused functionality
• • Direct SQL queries for performance

Primary Functions

• ✅ Generate category-based return rate reports
• ✅ Compare sales volumes to return volumes
• ✅ AJAX data loading for responsive UI
• ✅ Category search with autocomplete
• ✅ Date range filtering
• ✅ Simple aggregated metrics

Related Controllers

• • salesreportcatstore.php - Category sales reports
• • returnsellbillController.php - Return bill management
• • sellbillController.php - Sales operations
• • productController.php - Product management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Display Report Interface

Location: Line 6

Purpose: Shows the main report interface template

Process Flow:

1. Display header template

2. Load main report template (salesreturnsratingsreportview/show.html)

3. Display footer template

Features:

• • Minimal setup - just template rendering
• • No complex initialization required

---

2. getdata Action - Generate Report Data

Location: Line 11

Purpose: Main report data generation with AJAX response

Process Flow:

1. Accept POST parameters (start_date, end_date, categoryid)

2. Build date filter conditions for sales and returns

3. Query specific category or all categories

4. Aggregate sales totals and return totals

5. Return results via HTML template

Function Signature:

$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');  
$categoryid = filter_input(INPUT_POST, 'categoryid');

3. select2category Action - Category Autocomplete

Location: Line 69

Purpose: Provides category search functionality for UI

Function Signature:

function select2category() {
    $name = $_POST['searchTerm'];
    // Returns JSON array of matching categories
}

Process Flow:

1. Accept search term from POST

2. Query productcat with LIKE search on category name

3. Limit results to 50 matches

4. Format as Select2-compatible JSON array

5. Return JSON response

---

🔄 Workflows

Workflow 1: Return Rate Analysis Report

---

🌐 URL Routes & Actions

Data Generation (do=getdata):

• • start_date - Report start date (YYYY-MM-DD)
• • end_date - Report end date (YYYY-MM-DD)
• • categoryid - Category ID (optional, all categories if empty)

Category Search (do=select2category):

• • searchTerm - Category name search string

---

🧮 Calculation Methods

Sales Aggregation Query

SELECT sum(sellbilldetailtotalprice) as total, 
       sum(sellbilldetailquantity) as qunt 
FROM sellbilldetail 
JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid
WHERE sellbill.conditions = 0 
  AND sellbilldetail.sellbilldetailcatid = [CATEGORY_ID]
  AND sellbill.sellbilldate >= '[START_DATE] 00:00:00' 
  AND sellbill.sellbilldate <= '[END_DATE] 23:59:55'

Returns Aggregation Query

SELECT sum(returnsellbilldetailtotalprice) as total, 
       sum(returnsellbilldetailquantity) as qunt 
FROM returnsellbilldetail  
JOIN returnsellbill ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
WHERE returnsellbill.conditions = 0  
  AND returnsellbilldetail.returnsellbilldetailcatid = [CATEGORY_ID]
  AND returnsellbill.returnsellbilldate >= '[START_DATE] 00:00:00' 
  AND returnsellbill.returnsellbilldate <= '[END_DATE] 23:59:55'

Data Structure for Templates

// For all categories summary
$productcatsdatas[] = [
    'productCatName' => $productcat['productCatName'],
    'squnt' => $sellbill['qunt'],        // Sales quantity
    'stotal' => $sellbill['total'],      // Sales total
    'rqunt' => $returnsellbill['qunt'],  // Returns quantity  
    'rtotal' => $returnsellbill['total'] // Returns total
];

---

🔒 Security & Permissions

Input Sanitization

// All inputs filtered through filter_input()
$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');
$categoryid = filter_input(INPUT_POST, 'categoryid');

SQL Injection Prevention

• • Uses RedBeanPHP R::getAll() and R::getRow() for safe queries
• • Direct variable concatenation present - should be parameterized
• • Category search uses LIKE with user input - potential vulnerability

Security Issue:

-- Current implementation vulnerable to SQL injection
WHERE productCatName LIKE '%$name%'

-- Should be:
WHERE productCatName LIKE :searchTerm

---

📊 Performance Considerations

Database Optimization

1. Required Indexes:

- sellbilldetail(sellbilldetailcatid, sellbillid)

- sellbill(sellbilldate, conditions)

- returnsellbilldetail(returnsellbilldetailcatid, returnsellbillid)

- returnsellbill(returnsellbilldate, conditions)

2. Query Efficiency:

- Uses SUM() aggregation for efficient totaling

- Proper JOIN relationships reduce data transfer

- Date filtering with appropriate indexes

3. Scalability Notes:

- All categories query may be slow with many categories

- Consider pagination for large category lists

- Date range filtering prevents runaway queries

---

🐛 Common Issues & Troubleshooting

1. Missing Category Data

Issue: Categories showing zero sales/returns when data exists

Cause: Category ID mismatches between bills and products

Debug:

-- Check category assignments in bill details
SELECT DISTINCT sellbilldetailcatid FROM sellbilldetail 
WHERE sellbilldetailcatid IS NOT NULL;

-- Verify category exists in productcat table
SELECT * FROM productcat WHERE productCatId = [MISSING_ID];

2. Date Range Issues

Issue: No data returned for valid date ranges

Cause: Date format or time stamp handling

Fix:

// Ensure proper date format in queries
$start_date . ' 00:00:00'  // Start of day
$end_date . ' 23:59:55'    // End of day (almost midnight)

3. AJAX Loading Problems

Issue: Category autocomplete not working

Cause: JSON format issues or server response problems

Debug:

// Check browser console for AJAX errors
console.log(response);

// Verify JSON format
{
    "id": "123",
    "text": "Category Name"
}

---

🧪 Testing Scenarios

Test Case 1: Category Return Rate Accuracy

1. Create test category with known products
2. Generate sales bills for test period
3. Create return bills for some of the sales
4. Run return rate report
5. Verify percentages match manual calculations

Test Case 2: Date Range Filtering

1. Create sales/returns on specific dates
2. Test various date ranges including:
   - Single day ranges
   - Month-long ranges  
   - Cross-month boundaries
3. Verify only appropriate data appears

Test Case 3: All Categories Summary

1. Create multiple categories with different return patterns
2. Run summary report (no category filter)
3. Verify each category's totals are correct
4. Check that sum of categories equals overall totals

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • salesreportcatstore.md - Category sales reports
• • sellbillController.md - Sales operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Security Review: ⚠️ SQL injection vulnerability in category search

Next Review: When major changes occur