# 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
- [x] Analyze product sales patterns over time periods
- [x] Calculate stock requirements based on sales velocity
- [x] Identify products needing reorder
- [x] Support configurable pre-order periods
- [x] Provide AJAX-powered data grid interface
- [x] Calculate net available quantities vs. sales demand

### Related Controllers
- [inventoryController.php](inventoryController.md) - Inventory management
- [sellbillController.php](sellbillController.md) - Sales tracking
- [productController.php](productController.md) - Product management
- [storereportController.php](storereportController.md) - 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**:
```php
// 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**:
```php
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:
```php
$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:
```php
$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:
```php
$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:
```php
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:
```php
$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                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Date Parameters                                   │
│     - Extract fromdate and todate from request             │
│     - Validate date formats                                │
│     - Set default dates if not provided                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Calculate Pre-Order Period                             │
│     - Query preorderperiod from programsettings            │
│     - Adjust fromdate backwards by preorderperiod days     │
│     - Extend analysis window for better forecasting        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Query Sales Data                                        │
│     - Get sales quantities by product for period           │
│     - Join with product master data                        │
│     - Filter for active products only                      │
│     - Group by product ID                                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Analyze Each Product                                    │
│     FOR EACH product with sales:                            │
│       ├─→ Query current inventory levels                   │
│       ├─→ Query return quantities                          │
│       ├─→ Calculate net demand                             │
│       └─→ Determine reorder requirement                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Filter 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
```php
// 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
```php
// 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
```php
// 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
```php
$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**:
```php
// 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
```sql
-- 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**:
```sql
-- 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**:
```sql
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**:
```sql
-- 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**:
```javascript
// 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [inventoryController.md](inventoryController.md) - Inventory management
- [sellbillController.md](sellbillController.md) - Sales processing
- [DataTables Documentation](https://datatables.net/) - Frontend framework

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur