All Reports On One Controller Documentation

File: /controllers/allReportsOnOne.php

Purpose: Unified reporting interface that consolidates sales and purchase reports in a single controller

Last Updated: December 20, 2024

Total Functions: 14

Lines of Code: ~2,400+ (Large multi-purpose controller)

---

📋 Overview

The All Reports On One Controller is a comprehensive reporting module that serves as a unified interface for multiple types of business reports. It consolidates functionality from several specialized report controllers into a single interface, providing:

• • Sales bill reports (individual and combined)
• • Sales return reports
• • Purchase bill reports
• • Purchase return reports
• • Combined sales/purchase reporting
• • Multi-criteria filtering capabilities
• • Detail views for individual transactions
• • Bulk report generation with checkboxes
• • Unified search interface across all report types
• • Performance-optimized data loading

Primary Functions

• ✅ Generate sales bill reports with advanced filtering
• ✅ Create sales return reports
• ✅ Generate purchase bill reports
• ✅ Create purchase return reports
• ✅ Combine multiple report types in single view
• ✅ Provide detailed transaction views
• ✅ Support multi-criteria search and filtering
• ✅ Handle date range filtering with timezone support
• ✅ Calculate totals across different bill types
• ✅ Support serial number and product tracking
• ✅ Generate comparative reports
• ✅ Export-ready data formatting

Related Controllers

• • sellbillController.php - Sales operations
• • returnsellbillController.php - Sales returns
• • buybillController.php - Purchase operations
• • returnbuybillController.php - Purchase returns
• • clientReportsController.php - Customer reports
• • supplierReportsController.php - Supplier reports

---

🗄️ Database Tables

Sales Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Unified Report Interface

Location: Line 381-525

Purpose: Main interface that handles multiple report types based on checkbox selections

Function Signature:

// Multiple report types can be selected via checkboxes
$rep1 = $_REQUEST['rep1']; // Sales reports
$rep2 = $_REQUEST['rep2']; // Sales return reports  
$rep3 = $_REQUEST['rep3']; // Combined sales/return reports
$rep4 = $_REQUEST['rep4']; // Purchase reports
$rep5 = $_REQUEST['rep5']; // Purchase return reports
$rep6 = $_REQUEST['rep6']; // Combined purchase/return reports

Process Flow:

1. Load dropdown data (stores, suppliers, clients)

2. Parse report type selections (rep1-rep6 checkboxes)

3. Route to appropriate report functions based on selections

4. Handle client-specific vs supplier-specific vs general reports

5. Display via appropriate templates

Report Type Routing:

if ($clientId > 0) {
    if ($rep1 == '1') showAll(); // Sales reports
    if ($rep2 == '1') returnshowAll(); // Return reports
    if ($rep3 == '1') { showAll(); returnshowAll(); } // Combined
}

if ($supplierId > 0) {
    if ($rep4 == '1') showBuyBill(); // Purchase reports
    if ($rep5 == '1') showBuyReturn(); // Purchase returns
    if ($rep6 == '1') showBuyReturnAndBuy(); // Combined purchase
}

---

2. showAll() - Sales Bill Report Generator

Location: Line 672-1022

Purpose: Comprehensive sales bill reporting with advanced filtering and calculations

Function Signature:

function showAll()

Process Flow:

1. Parse Search Parameters
   ├─ Date range (from/to with timezone support)
   ├─ Client ID filter
   ├─ Store ID filter  
   ├─ Bill serial/ID filters
   ├─ Bill type filter (obgyBillType)
   └─ Product serial tracking filter

2. Build Dynamic Queries
   ├─ Construct WHERE clauses for multiple tables
   ├─ Handle serial number filtering via detail tables
   ├─ Apply date range with timezone adjustments
   └─ Default to today's date if no criteria

3. Execute Optimized Queries
   ├─ Query sellbill table with filters
   ├─ Query sellbillandrutern table with filters
   ├─ Batch load related data (users, clients, discounts)
   └─ Use array indexing for performance

4. Process Sales Data
   ├─ Calculate totals and quantities
   ├─ Apply discount calculations (fixed vs percentage)
   ├─ Calculate tax amounts
   ├─ Convert arrays to objects for template use
   └─ Merge regular and combined bill data

5. Generate Report Output
   ├─ Assign processed data to templates
   ├─ Calculate summary statistics
   └─ Display via appropriate template

Performance Optimization:

// Batch load related data to avoid N+1 queries
$userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ') ');
$clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ');
$discountDataArr = R::getAll('SELECT sellbillid,sum(discountvalue) as sumdiscountvalue FROM sellbilldetail where sellbillid in(' . implode(',', $billIDs) . ') group by sellbillid');

// Index arrays for fast lookups
$userDataArr = customArrayIndexOne($userDataArr, 'userid');
$clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
$discountDataArr = customArrayIndexOne($discountDataArr, 'sellbillid');

---

3. returnshowAll() - Sales Return Report Generator

Location: Line 1024-1393

Purpose: Generate comprehensive sales return reports with similar filtering capabilities

Function Signature:

function returnshowAll()

Process Flow:

1. Parse same search parameters as showAll()

2. Build queries for returnsellbill and sellbillandrutern tables

3. Focus on return portions of combined bills

4. Calculate return totals, quantities, and discounts

5. Generate return-specific report output

Key Differences from Sales:

• • Focuses on return data rather than sales data
• • Processes returnsellbilldetail for serial tracking
• • Calculates return quantities and amounts
• • Handles return-specific discount calculations

---

4. showBuyBill() - Purchase Bill Report Generator

Location: Line 2183+

Purpose: Generate purchase bill reports with supplier and date filtering

Features:

• • Supplier-based filtering
• • Purchase date range analysis
• • Purchase quantity and amount totals
• • Purchase bill detail views
• • Store-based purchase analysis

---

5. showBuyReturn() - Purchase Return Report Generator

Location: Line 1559-1867

Purpose: Generate purchase return reports

Features:

• • Purchase return analysis
• • Supplier return patterns
• • Return quantity and amount calculations
• • Return reason tracking
• • Combined purchase/return analysis

---

6. showDetail() - Sales Bill Detail View

Location: Line 629-645

Purpose: Load detailed information for a specific sales bill

Function Signature:

function showDetail($sellbillid)

Returns:

return array(
    $sellbillData,        // Bill header information
    $sellbilldetailData,  // Line item details
    $quantity             // Total quantity
);

---

7. showsellAndReturnDetail() - Combined Bill Detail View

Location: Line 647-670

Purpose: Load detailed information for combined sales/return bills

Function Signature:

function showsellAndReturnDetail($sellbillid)

Returns:

return array(
    $sellbillandruternData, // Combined bill header
    $sellbilldetailData,    // Sales line items
    $ruternbilldetailData,  // Return line items  
    $sellQuantity,          // Sales quantity
    $returnQuantity         // Return quantity
);

---

8. Filter Functions - Search and Filter Utilities

Locations: Lines 1394-1558

Purpose: Specialized filtering functions for different search criteria

Available Functions:

• • showByClient($clientId) - Filter by specific client
• • showBySriral($serial) - Filter by bill serial number
• • showBySellbillId($sellbillId) - Filter by specific bill ID
• • showByDate($startDate, $endDate) - Filter by date range

---

9. Data Loading Functions - Reference Data Loaders

Locations: Lines 591-627

Purpose: Load reference data for dropdowns and lookups

Available Functions:

• • loadAllClient() - Load all clients
• • loadAllSellBill() - Load all sales bills
• • loadBillname() - Load bill format definitions
• • loadBillProperty($billnameid) - Load bill formatting settings

---

🔄 Workflows

Workflow 1: Multi-Report Generation

---

Workflow 2: Advanced Sales Report Generation

---

🌐 URL Routes & Actions

Unified Report Interface (do=show):

• • rep1 - Sales reports checkbox (0/1)
• • rep2 - Sales return reports checkbox (0/1)
• • rep3 - Combined sales/return checkbox (0/1)
• • rep4 - Purchase reports checkbox (0/1)
• • rep5 - Purchase return reports checkbox (0/1)
• • rep6 - Combined purchase reports checkbox (0/1)
• • clientid - Client filter (optional)
• • supplierId - Supplier filter (optional)
• • storeId - Store filter (optional)
• • from - Start date (optional, YYYY-MM-DD)
• • to - End date (optional, YYYY-MM-DD)
• • sellbillserial - Bill serial filter (optional)
• • sellbillid - Bill ID filter (optional)
• • proSellTrackingSerial - Product tracking serial (optional)

Sales Detail (do=sellDetail):

• • sellbillid - Sales bill ID (required)

Combined Detail (do=sellAndReturnDetail):

• • sellbillid - Combined bill ID (required)

---

🧮 Calculation Methods

Discount Calculation (Fixed vs Percentage)

// Fixed amount discount (type = 1)
if ($sellbilldiscounttype == 1) {
    $totalDiscount = $sellbilldiscount + $detailDiscount;
    $taxValue = $sellbillaftertotalbill - ($sellbilltotalbill - $sellbilldiscount);
}

// Percentage discount (type != 1)
else {
    $discountValue = ($sellbilltotalbill / 100) * $sellbilldiscount;
    $totalDiscount = $discountValue + $detailDiscount;
    $taxValue = $sellbillaftertotalbill - ($sellbilltotalbill - $discountValue);
}

Performance Optimization Techniques

// Batch loading to prevent N+1 queries
$billIDs = array_unique(array_column($sellbillData, 'sellbillid'));
$userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ')');

// Array indexing for fast lookups
$userDataArr = customArrayIndexOne($userDataArr, 'userid');

// Join data efficiently
foreach ($sellbillData as &$value) {
    $value['username'] = $userDataArr[$value['userid']]['employeename'];
    $value['clientname'] = $clientDataArr[$value['sellbillclientid']]['clientname'];
}

Serial Number Filtering

if (!empty($proSellTrackingSerial)) {
    // Find bills containing specific product serial
    $detailsData = R::getAll('SELECT distinct sellbillid FROM sellbilldetail where proSellTrackingSerial="' . $proSellTrackingSerial . '"');
    
    // Build IN clause for main query
    $sellbillIds = array_column($detailsData, 'sellbillid');
    $queryStringDetailIds = " and sellbillid in (" . implode(',', $sellbillIds) . ")";
}

---

🔒 Security & Permissions

Input Validation

• • All $_REQUEST parameters filtered through framework
• • Date format validation and timezone handling
• • Numeric ID validation and type casting
• • Serial number format validation
• • SQL injection prevention through parameterized queries

Data Access Control

• • Uses standard ERP session authentication
• • Respects user permissions from authentication.php
• • No additional access restrictions beyond standard login
• • Bill condition filtering (conditions = 0 for active bills)

Query Security

// Safe parameter binding
$queryString .= ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';

// Condition filtering for data integrity
$queryString .= ' AND sellbill.conditions=0';

// Array indexing prevents injection
$billIDs = array_unique($billIDs);
$queryString .= ' AND sellbillid IN (' . implode(',', $billIDs) . ')';

---

📊 Performance Considerations

Database Optimization Tips

1. Critical Indexes Required:

- sellbill(sellbillclientid, sellbilldate, conditions, sellbillstoreid)

- sellbilldetail(sellbillid, proSellTrackingSerial)

- sellbillandrutern(sellbillclientid, sellbilldate, conditions)

- returnsellbill(returnsellbillclientid, returnsellbilldate, conditions)

2. Query Optimization Techniques:

- Batch loading of related data to prevent N+1 queries

- Array indexing for fast data joins

- Efficient use of IN clauses for filtering

- Proper date range filtering with indexes

3. Memory Management:

- Large datasets may require pagination

- Array indexing reduces memory overhead

- Object conversion only when necessary

- Cleanup of temporary variables

Performance Monitoring

-- Monitor query performance
EXPLAIN SELECT * FROM sellbill 
WHERE sellbilldate >= '2024-01-01' AND sellbilldate <= '2024-12-31' 
AND conditions = 0 
AND sellbillclientid = 123;

-- Check index usage
SHOW INDEX FROM sellbill;
SHOW INDEX FROM sellbilldetail;

---

🐛 Common Issues & Troubleshooting

1. Large Dataset Performance

Issue: Reports timeout or run slowly with large date ranges

Cause: Missing indexes or inefficient queries

Solutions:

-- Add required indexes
CREATE INDEX idx_sellbill_date_client ON sellbill(sellbilldate, sellbillclientid, conditions);
CREATE INDEX idx_detail_serial ON sellbilldetail(proSellTrackingSerial, sellbillid);

-- Limit date ranges or add pagination
WHERE sellbilldate >= '2024-12-01' AND sellbilldate <= '2024-12-31';

2. Memory Exhaustion

Issue: PHP memory limit exceeded with large reports

Cause: Loading too much data into memory at once

Fix:

// Increase memory limit temporarily
ini_set('memory_limit', '512M');

// Or implement pagination
$limit = 1000;
$offset = $page * $limit;
$queryString .= " LIMIT $limit OFFSET $offset";

3. Incorrect Discount Calculations

Issue: Discount totals don't match expected values

Cause: Mixed discount types or missing detail discounts

Debug:

// Debug discount calculation
echo "Bill Discount: " . $sellbilldiscount . "<br>";
echo "Discount Type: " . $sellbilldiscounttype . "<br>";
echo "Detail Discount: " . $detaildiscount . "<br>";
echo "Total Before: " . $sellbilltotalbill . "<br>";
echo "Total After: " . $sellbillaftertotalbill . "<br>";

4. Serial Number Filtering Issues

Issue: Serial number search returns no results

Cause: Case sensitivity or formatting issues

Fix:

-- Use case-insensitive search
WHERE UPPER(proSellTrackingSerial) = UPPER('SERIAL123');

-- Check for leading/trailing spaces
WHERE TRIM(proSellTrackingSerial) = 'SERIAL123';

---

🧪 Testing Scenarios

Test Case 1: Multi-Report Generation

1. Select multiple report checkboxes (rep1, rep2, rep3)
2. Set date range and client filter
3. Submit form and verify all selected reports generate
4. Check that totals are calculated correctly across reports
5. Verify performance with large datasets

Test Case 2: Serial Number Tracking

1. Create sales bills with specific product serial numbers
2. Search by serial number in advanced filter
3. Verify only bills containing that serial appear
4. Test with partial serial matches
5. Confirm case sensitivity handling

Test Case 3: Performance Testing

1. Generate large dataset (1000+ bills)
2. Run reports with various filter combinations
3. Monitor query execution times
4. Test memory usage with large result sets
5. Verify batch loading optimization works

Debug Mode Enable

// Add at top of functions for debugging
echo "Memory Usage: " . memory_get_usage(true) / 1024 / 1024 . " MB<br>";
echo "Query String: " . $queryString . "<br>";
echo "Bill Count: " . count($sellbillData) . "<br>";

// Debug data processing
foreach ($sellbillData as $bill) {
    echo "Bill: " . $bill->sellbillid . " Total: " . $bill->sellbillaftertotalbill . "<br>";
}

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • clientReportsController.md - Customer reports
• • buybillController.php - Purchase operations
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When report consolidation changes or performance issues arise