Order Period Report Controller Documentation

File: /controllers/orderperiodreport.php

Purpose: Product ordering period analysis with stock movement tracking and reorder recommendations

Last Updated: December 20, 2024

Total Functions: 1

Lines of Code: ~145

---

📋 Overview

The Order Period Report Controller provides analysis for product ordering periods and stock movements. It identifies products that need to be reordered based on sales patterns and current inventory levels. The system:

• Analyzes product sales over specified periods
• Compares sales quantities with current inventory
• Identifies products with shortage requirements
• Considers pre-order periods from system settings
• Provides DataTables-based interface for efficient data handling

Primary Functions

✅ Analyze product sales patterns over time periods
✅ Calculate stock requirements based on sales velocity
✅ Identify products needing reorder
✅ Support configurable pre-order periods
✅ Provide AJAX-powered data grid interface
✅ Calculate net available quantities vs. sales demand

Related Controllers

• inventoryController.php - Inventory management
• sellbillController.php - Sales tracking
• productController.php - Product management
• storereportController.php - Stock reporting

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
sellbill	Sales bill headers	sellbillid, sellbilldate, conditions
sellbilldetail	Sales bill line items	sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity
returnsellbill	Sales return headers	returnsellbillid, returnsellbilldate, conditions
returnsellbilldetail	Sales return line items	returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity
storedetail	Inventory transactions	storedetailid, productid, productquantity, storedetaildate

### Reference Tables

Table Name	Purpose	Key Columns
product	Product master data	productId, productName, conditions
programsettings	System configuration	programsettingsid, preorderperiod

---

🔑 Key Functions

1. Default Action - Order Period Analysis Dashboard

Location: Lines 7-20

Purpose: Display the main order period analysis interface

Function Signature:

// Triggered when: empty $do
// Displays: orderperiodreportview/index.html

Process Flow:

1. Set current date as default

2. Assign date to template

3. Display analysis dashboard with date picker

Template Variables:

• $date - Current date for default display

---

2. showajax() - DataTables AJAX Endpoint

Location: Lines 78-143

Purpose: Provide server-side data processing for product order analysis

Function Signature:

function showajax()

Process Flow:

1. Parse date range parameters

2. Calculate pre-order period from settings

3. Build queries for sales, returns, and inventory

4. Execute analysis queries

5. Process results to identify reorder needs

6. Return formatted DataTables response

Key Features:

Pre-Order Period Calculation:

$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];
if ($todate != '') {
    $preorderperiod = R::getCell("SELECT preorderperiod FROM programsettings WHERE programsettingsid = 1");
    $fromdate = date('Y-m-d', strtotime('-'.$preorderperiod.' days', strtotime($fromdate))); 
}

Query Building:

$searchQuery = 'and sellbilldate >= "' . $fromdate . ' 00-00-00" and sellbilldate <= "' . $todate . ' 23-59-55" ';
$returnsellbilldate = 'and returnsellbilldate >= "' . $fromdate . ' 00-00-00" and returnsellbilldate <= "' . $todate . ' 23-59-55" ';
$storedetail = 'and storedetaildate >= "' . $fromdate . ' 00-00-00" and storedetaildate <= "' . $todate . ' 23-59-55" ';

Main Analysis Query:

$rResult = R::getAll("SELECT 
    sum(sellbilldetailquantity) as tsellbilldetailquantity, 
    productName, 
    productId 
FROM sellbill  
LEFT JOIN sellbilldetail ON sellbill.sellbillid = sellbilldetail.sellbillid  
LEFT JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId 
    and product.conditions = 0 
WHERE sellbill.conditions = 0 $searchQuery 
group by sellbilldetailproductid");

Stock Analysis Logic:

foreach ($rResult as $row) {
    // Get current inventory quantity
    $sumProductQuantity = R::getCell("SELECT SUM(storedetail.productquantity) 
        FROM storedetail 
        WHERE productid = ".$row["productId"]." $storedetail 
        group by storedetail.productid");
    
    // Get return quantities
    $sumProductQuantity1 = R::getCell("SELECT SUM(returnsellbilldetail.returnsellbilldetailquantity) 
        FROM returnsellbill 
        LEFT JOIN returnsellbilldetail ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid  
        WHERE returnsellbilldetailproductid = ".$row["productId"]." $returnsellbilldate  
        group by returnsellbilldetail.returnsellbilldetailproductid");
    
    // Calculate net requirement
    $Quantity = ((int)$row["tsellbilldetailquantity"] - (int)$sumProductQuantity1) - (int)$sumProductQuantity;
    
    // Only include products needing reorder
    if ($Quantity > 1) {
        $output['data'][] = $sub_array;
    }
}

Response Format:

$output = array(
    "recordsTotal" => $apps,
    "recordsFiltered" => count($rResult),
    "data" => array()
);

// Data array structure
$sub_array[] = $row["productId"];           // Product ID
$sub_array[] = $row["productName"];         // Product name
$sub_array[] = (int)$row["tsellbilldetailquantity"];  // Sales quantity
$sub_array[] = (int)$sumProductQuantity1;   // Return quantity
$sub_array[] = (int)$sumProductQuantity;    // Inventory quantity
$sub_array[] = $Quantity;                   // Net shortage quantity

---

🔄 Workflows

Workflow 1: Order Period Analysis

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

START: Order Period Analysis

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

▼

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

1Parse Date Parameters

- Extract fromdate and todate from request

- Validate date formats

- Set default dates if not provided

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

▼

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

2Calculate Pre-Order Period

- Query preorderperiod from programsettings

- Adjust fromdate backwards by preorderperiod days

- Extend analysis window for better forecasting

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

▼

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

3Query Sales Data

- Get sales quantities by product for period

- Join with product master data

- Filter for active products only

- Group by product ID

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

▼

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

4Analyze Each Product

FOR EACH product with sales:

→ Query current inventory levels

→ Query return quantities

→ Calculate net demand

│ └─→ Determine reorder requirement │

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

▼

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

5Filter and Format Results

- Include only products with shortage > 1

- Format data for DataTables display

- Calculate summary statistics

- Return JSON response

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default action	Order analysis dashboard
`do=showajax`	`showajax()`	AJAX data endpoint for DataTables

### AJAX Parameters

DataTables Parameters:

• fromdate - Analysis start date (POST)
• todate - Analysis end date (POST)
• start - Record offset for pagination
• length - Number of records per page
• order - Sorting parameters
• search - Search filtering

---

🧮 Calculation Methods

Pre-Order Period Adjustment

// Extend analysis period backwards to improve forecasting
$preorderperiod = R::getCell("SELECT preorderperiod FROM programsettings WHERE programsettingsid = 1");
$fromdate = date('Y-m-d', strtotime('-'.$preorderperiod.' days', strtotime($fromdate)));

Net Demand Calculation

// Formula: Net Shortage = Sales - Returns - Current_Inventory
$salesQuantity = (int)$row["tsellbilldetailquantity"];      // Total sales
$returnQuantity = (int)$sumProductQuantity1;                // Total returns
$inventoryQuantity = (int)$sumProductQuantity;              // Current stock

$netShortage = $salesQuantity - $returnQuantity - $inventoryQuantity;

// Only flag products with meaningful shortage
if ($netShortage > 1) {
    // Include in reorder recommendations
}

Date Range Processing

// Ensure proper date formatting for SQL queries
$searchQuery = 'and sellbilldate >= "' . $fromdate . ' 00-00-00" and sellbilldate <= "' . $todate . ' 23-59-55" ';
$returnsellbilldate = 'and returnsellbilldate >= "' . $fromdate . ' 00-00-00" and returnsellbilldate <= "' . $todate . ' 23-59-55" ';
$storedetail = 'and storedetaildate >= "' . $fromdate . ' 00-00-00" and storedetaildate <= "' . $todate . ' 23-59-55" ';

---

🔒 Security & Permissions

Input Sanitization

$fromdate = $_POST['fromdate'];  // Should be validated
$todate = $_POST['todate'];      // Should be validated

// Numeric sanitization for DataTables parameters
$start = intval($_POST['start']);
$length = intval($_POST['length']);

SQL Injection Prevention

• Direct string concatenation in queries (potential risk)
• Numeric IDs used in queries should be sanitized
• Date parameters should be validated before SQL usage

Recommended Security Improvements:

// Better date validation
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $fromdate)) {
    throw new InvalidArgumentException('Invalid date format');
}

// Parameterized queries
$stmt = $pdo->prepare("SELECT SUM(productquantity) FROM storedetail WHERE productid = ? AND storedetaildate >= ?");
$stmt->execute([$productId, $storedetaildate]);

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- sellbilldetail(sellbilldetailproductid, sellbillid)

- sellbill(sellbilldate, conditions)

- returnsellbilldetail(returnsellbilldetailproductid)

- returnsellbill(returnsellbilldate, conditions)

- storedetail(productid, storedetaildate)

- product(productId, conditions)

2. Query Optimization:

- Consider caching product analysis results

- Use appropriate date range filtering

- Optimize GROUP BY operations with proper indexes

3. Memory Management:

- Large date ranges may return many records

- Consider pagination for very active products

- Efficient processing of product calculations

Known Performance Issues

-- These individual product queries can be slow
SELECT SUM(storedetail.productquantity) FROM storedetail 
WHERE productid = [ID] AND storedetaildate >= '[DATE]' 
GROUP BY storedetail.productid;

-- Better: Single query with JOIN
SELECT 
    p.productId,
    p.productName,
    SUM(sd.sellqty) as total_sold,
    SUM(rd.returnqty) as total_returned,
    SUM(st.productquantity) as current_stock
FROM product p
LEFT JOIN (sales subquery) sd ON p.productId = sd.productId
LEFT JOIN (returns subquery) rd ON p.productId = rd.productId  
LEFT JOIN (inventory subquery) st ON p.productId = st.productId
WHERE p.conditions = 0;

---

🐛 Common Issues & Troubleshooting

1. No Products Showing in Analysis

Issue: Analysis returns empty results despite having sales

Cause: Date range issues or missing data joins

Debug Steps:

-- Check if sales data exists
SELECT COUNT(*) FROM sellbilldetail sd
JOIN sellbill sb ON sd.sellbillid = sb.sellbillid
WHERE sb.sellbilldate BETWEEN '[FROM]' AND '[TO]'
AND sb.conditions = 0;

-- Check product data
SELECT COUNT(*) FROM product WHERE conditions = 0;

2. Incorrect Shortage Calculations

Issue: Shortage quantities don't match manual calculations

Cause: Date range mismatches or incorrect quantity sums

Verification Query:

SELECT 
    p.productName,
    (SELECT SUM(sellbilldetailquantity) FROM sellbilldetail sd 
     JOIN sellbill sb ON sd.sellbillid = sb.sellbillid 
     WHERE sd.sellbilldetailproductid = p.productId 
     AND sb.sellbilldate BETWEEN '[FROM]' AND '[TO]') as sold,
     
    (SELECT SUM(returnsellbilldetailquantity) FROM returnsellbilldetail rd 
     JOIN returnsellbill rb ON rd.returnsellbillid = rb.returnsellbillid 
     WHERE rd.returnsellbilldetailproductid = p.productId 
     AND rb.returnsellbilldate BETWEEN '[FROM]' AND '[TO]') as returned,
     
    (SELECT SUM(productquantity) FROM storedetail st 
     WHERE st.productid = p.productId 
     AND st.storedetaildate BETWEEN '[FROM]' AND '[TO]') as inventory
FROM product p 
WHERE p.conditions = 0;

3. Pre-Order Period Not Applied

Issue: Date adjustment not working correctly

Cause: Missing program settings or incorrect date calculation

Fix:

-- Check program settings
SELECT * FROM programsettings WHERE programsettingsid = 1;

-- Verify pre-order period setting
SELECT preorderperiod FROM programsettings WHERE programsettingsid = 1;

4. DataTables Loading Issues

Issue: Frontend grid not loading or showing errors

Cause: AJAX response format issues or server errors

Debug:

// Check browser console for errors
// Verify AJAX response format
$.post('?do=showajax', {fromdate: '2024-01-01', todate: '2024-01-31'}, function(data) {
    console.log(data);
});

---

🧪 Testing Scenarios

Test Case 1: Basic Order Analysis

1. Create test products with sales history
2. Add inventory transactions for products
3. Set up return transactions
4. Run analysis for date range
5. Verify shortage calculations:
   - Sales qty - Returns qty - Inventory qty = Shortage
6. Check only products with shortage > 1 appear

Test Case 2: Pre-Order Period Testing

1. Set preorderperiod in programsettings to 30 days
2. Create sales data spanning 60 days
3. Run analysis for recent 30-day period
4. Verify analysis actually covers 60 days (30 + preorderperiod)
5. Check date calculations are correct

Test Case 3: Edge Case Handling

1. Test with products having no sales
2. Test with products having only returns
3. Test with negative inventory quantities
4. Test with very large date ranges
5. Verify system handles edge cases gracefully

Test Case 4: Performance Testing

1. Create large dataset (1000+ products, 10000+ transactions)
2. Run analysis with various date ranges
3. Monitor query execution times
4. Test DataTables pagination and sorting
5. Verify acceptable response times (< 5 seconds)

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• inventoryController.md - Inventory management
• sellbillController.md - Sales processing
• DataTables Documentation - Frontend framework

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Orderperiodreport Documentation

Order Period Report Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Order Period Analysis Dashboard

2. showajax() - DataTables AJAX Endpoint

Pre-Order Period Calculation:

Query Building:

Main Analysis Query:

Stock Analysis Logic:

Response Format:

🔄 Workflows

Workflow 1: Order Period Analysis

🌐 URL Routes & Actions

🧮 Calculation Methods

Pre-Order Period Adjustment

Net Demand Calculation

Date Range Processing

🔒 Security & Permissions

Input Sanitization

SQL Injection Prevention

📊 Performance Considerations

Database Optimization Tips

Known Performance Issues

🐛 Common Issues & Troubleshooting

1. No Products Showing in Analysis

2. Incorrect Shortage Calculations

3. Pre-Order Period Not Applied

4. DataTables Loading Issues

🧪 Testing Scenarios

Test Case 1: Basic Order Analysis

Test Case 2: Pre-Order Period Testing

Test Case 3: Edge Case Handling

Test Case 4: Performance Testing

📚 Related Documentation