Return Bill Report Controller Documentation

File: /controllers/returnBillreportController.php

Purpose: Generates comprehensive return bill reports with filtering and analysis capabilities

Last Updated: December 21, 2024

Total Functions: 8

Lines of Code: ~610

---

📋 Overview

The Return Bill Report Controller is a specialized reporting module that provides comprehensive analysis of purchase return operations. It handles two main types of return bills:

• • Direct return bills (standalone returns)
• • Combined buy-and-return bills (partial returns within purchase transactions)

The controller provides sophisticated filtering, calculation, and reporting capabilities including:

• • Multi-criteria filtering (supplier, serial, date range, bill ID)
• • Tax and discount calculations with different discount types
• • Quantity tracking and aggregation
• • Time zone handling for reports
• • Integration with program settings for report customization

Primary Functions

• ✅ Generate return bill reports with multiple filter options
• ✅ Process direct return bills from returnbuybill table
• ✅ Handle combined buy-and-return operations
• ✅ Calculate taxes, discounts, and net amounts
• ✅ Aggregate quantities across multiple bills
• ✅ Time zone aware date filtering
• ✅ Integration with system settings for report customization

Related Controllers

• • buyBillController.php - Purchase operations
• • returnBuyBillController.php - Return processing
• • supplierController.php - Supplier management
• • reportsController.php - General reporting

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Report Display

Location: Line 240

Purpose: Display return bill report with filtering options

Process Flow:

1. Include authentication check

2. Load suppliers data for filter dropdown

3. Load bill serials for selection

4. Load YouTube tutorial links

5. Call main show() function to generate report

6. Display results with custom validation enabled

Data Loading:

$suppliersData = getSuppliers();       // Undeleted suppliers
$smarty->assign("suppliersData", $suppliersData);

$billSerials = showBuyBills();         // Available bill serials
$smarty->assign("billSerials", $billSerials);

$youtubes = $youtubeLinkDAO->queryAll();
$smarty->assign("youtubes", $youtubes);

Template: returnBillreportview/show.html

---

2. show() - Main Report Generator

Location: Line 277

Purpose: Process filters and generate comprehensive return bill report

Function Signature:

function show() {
    global $buyAndReturnBillExt, $buyAndReturnBillDetailExt;
    global $returnBuyBill, $returnBuyBillDAO, $returnBuyBillExt, $returnBuyBillDetailExt;
    global $ProgramsettingDAO, $smarty;
}

Process Flow:

1. Build dynamic WHERE clauses for both return types

2. Apply supplier, serial, bill ID, and date filters

3. Handle timezone adjustments from program settings

4. Query direct return bills and combined bills separately

5. Process tax and discount calculations for each bill type

6. Aggregate quantities and totals

7. Assign results to template variables

Filter Processing:

$queryString = ' WHERE';   // For direct returns
$queryString1 = ' WHERE';  // For combined bills

// Supplier filter
if (!empty($supplierId) && $supplierId != "-1") {
    $queryString .= '  returnbuybill.returnbuybillsupplierid = ' . $supplierId . ' AND';
    $queryString1 .= '  buybillsupplierid = ' . $supplierId . ' AND';
}

// Serial filter  
if (!empty($serial) && $serial != "-1") {
    $queryString .= '  returnbuybill.returnbuybillSerial = "' . $serial . '" AND';
    $queryString1 .= '  buybillSerial = "' . $serial . '" AND';
}

// Date range with timezone handling
if (!empty($from) && !empty($to)) {
    $Programsetting = $ProgramsettingDAO->load(1);
    if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
        $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
        $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
        $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
    }
}

---

3. Tax and Discount Calculations

Location: Lines 394-498

Purpose: Calculate taxes and discounts for different bill types

Direct Return Bills Calculation:

foreach ($returnBuyBillData1 as $myreturnData) {
    if ($myreturnData->conditions == 0) {  // Active bills only
        $buybilldiscount = $myreturnData->returnbuybilldiscount;
        $buybilldiscountrype = $myreturnData->returnbuybilldiscountrype; // 1=direct, 0=percentage
        $buybilltotalbill = $myreturnData->returnbuybilltotalbill;
        $buybillaftertotalbill = $myreturnData->returnbuybillaftertotalbill;
        
        $detaildiscount = $returnBuyBillDetailExt->getsumdiscount($myreturnData->returnbuybillid);
        
        if ($buybilldiscountrype == 0) {
            // Percentage discount
            $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
            $myreturnData->returnbuybilldiscount = ($buybilldiscount + $detaildiscount);
            
            // Tax calculation for percentage discount
            $taxvalue = (-1 * $buybillaftertotalbill) - (number_format(($buybilltotalbill * -1) - $buybilldiscount));
            $totaltax = $totaltax + $taxvalue;
            $myreturnData->tax = $taxvalue;
        } else {
            // Direct amount discount
            $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;
            $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
            $myreturnData->returnbuybilldiscount = ($discountvalue + $detaildiscount);
            
            // Tax calculation for direct discount
            $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($discountvalue));
            $totaltax = $totaltax + $taxvalue;
            $myreturnData->tax = $taxvalue;
        }
    }
}

Combined Bills Calculation:

foreach ($returnbuyBillData as $remyBillData) {
    if ($remyBillData->conditions == 0) {
        $buybilldiscount = 0;  // No discount for combined bills
        $detaildiscount = $buyAndReturnBillDetailExt->getsumdiscount($remyBillData->buybillid, 1);
        
        // Simplified calculation for combined bills
        $totaldiscount = $totaldiscount + $detaildiscount;
        $remyBillData->buybilldiscount = $detaildiscount;
        $remyBillData->tax = 0;  // No tax calculation for combined bills
    }
}

---

4. Quantity Aggregation

Location: Lines 435-510

Purpose: Calculate total quantities across all return operations

Direct Returns Quantity Processing:

foreach ($returnbuybilldetailes as $myreturnbuybilldetailes) {
    $productnumber = $myreturnbuybilldetailes->productnumber;
    $returnbuybilldetailquantity = $myreturnbuybilldetailes->returnbuybilldetailquantity;
    
    $totalqty = $totalqty + ($productnumber * $returnbuybilldetailquantity);
}

Combined Bills Quantity Processing:

foreach ($buybilldetailes as $mybuybilldetailes) {
    if ($mybuybilldetailes->billtype == 1) {  // Return type only
        $productnumber = $mybuybilldetailes->productnumber;
        $buybilldetailquantity = $mybuybilldetailes->buybilldetailquantity;
        
        $totalqty = $totalqty + ($productnumber * $buybilldetailquantity);
    }
}

---

5. getSuppliers() - Load Supplier Data

Location: Line 577

Purpose: Retrieve active suppliers for filter dropdown

Function Signature:

function getSuppliers() {
    global $supplierDAO;
    $suppliersData = $supplierDAO->queryByCondition(0);  // Active suppliers only
    return $suppliersData;
}

---

6. showBuyBills() - Load Bill Serials

Location: Line 604

Purpose: Retrieve available bill serials for filtering

Function Signature:

function showBuyBills() {
    global $buyBillExt;
    $buyBills = $buyBillExt->queryAllSerialsNotDeleted();
    return $buyBills;
}

---

7. Timezone Handling Functions

Purpose: Handle date filtering with timezone adjustments

Default Date Handling:

if (empty($from) && empty($to) && empty($buybillid) && empty($serial) && !isset($supplierId)) {
    $from = $to = $today = date("Y-m-d");
    
    $Programsetting = $ProgramsettingDAO->load(1);
    if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
        $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
        $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
        $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
    } else {
        $from = $from . " 00:00:00";
    }
}

---

🔄 Workflows

Workflow 1: Return Bill Report Generation

---

🌐 URL Routes & Actions

Return Bill Report (do=show):

• • supplier - Supplier ID filter (-1 for all)
• • serial - Bill serial filter (-1 for all)
• • buybillid - Specific bill ID filter (-1 for all)
• • from - Start date (YYYY-MM-DD format)
• • to - End date (YYYY-MM-DD format)

Template Variables Assigned

Report Data:

• • $returnBuyBillData1 - Direct return bills array
• • $returnbuyBillData - Combined bills array
• • $totalBills - Total monetary value of all returns
• • $totalqty - Total quantity of returned items
• • $totaldiscount - Total discount amount
• • $totaltax - Total tax amount

Filter Data:

• • $suppliersData - Available suppliers for dropdown
• • $billSerials - Available bill serials for selection
• • $youtubes - Tutorial video links
• • $today - Current date
• • $Programsettingdata - System configuration data

---

🧮 Calculation Methods

Discount Calculation Variants

// Percentage Discount (discountrype = 0)
if ($buybilldiscountrype == 0) {
    $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
    $myreturnData->returnbuybilldiscount = ($buybilldiscount + $detaildiscount);
    
    // Tax calculation for percentage
    $taxvalue = (-1 * $buybillaftertotalbill) - (number_format(($buybilltotalbill * -1) - $buybilldiscount));
}

// Direct Amount Discount (discountrype = 1) 
else {
    $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;
    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
    $myreturnData->returnbuybilldiscount = ($discountvalue + $detaildiscount);
    
    // Tax calculation for direct amount
    $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($discountvalue));
}

Timezone Adjustment Calculations

// Load timezone offset from program settings
$Programsetting = $ProgramsettingDAO->load(1);
if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    // Add 24 hours for end-of-day plus configured offset
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    
    // Adjust end date
    $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
    
    // Adjust start date  
    $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
}

Quantity Aggregation Formula

// For each bill detail line
$totalqty = $totalqty + ($productnumber * $returnbuybilldetailquantity);

// Where:
// - $productnumber = units per package/case
// - $returnbuybilldetailquantity = number of packages/cases returned
// - Result = total individual units returned

Query String Cleanup Algorithm

// Remove trailing AND/WHERE from dynamic query building
$arr = explode(' ', $queryString);
if (isset($arr) && count($arr) > 0) {
    $lastWord = end($arr);
    
    if ($lastWord == 'AND') {
        array_pop($arr);                        // Remove trailing AND
        $queryString = implode(' ', $arr);
    } else if ($lastWord == 'WHERE') {
        array_pop($arr);                        // Remove empty WHERE
        $queryString = ' ';
    }
}

---

🔒 Security & Permissions

Authentication Requirements

• • All actions require authentication via include_once("../public/authentication.php")
• • Session-based user validation

Input Sanitization

// Basic filtering via filter_input() - should be enhanced
$serial = $_REQUEST["serial"];
$supplierId = $_REQUEST["supplierId"];  
$buybillid = $_REQUEST["buybillid"];
$from = $_REQUEST["from"];
$to = $_REQUEST["to"];

// Should implement:
// $supplierId = (int) filter_input(INPUT_GET, 'supplierId', FILTER_VALIDATE_INT);

SQL Injection Prevention

• • Uses DAO layer with parameterized queries
• • Dynamic query building should be enhanced with proper parameter binding

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- returnbuybill(returnbuybillsupplierid, returnbuybilldate)

- returnbuybill(returnbuybillSerial)

- buyandruternbill(buybillsupplierid, buybilldate)

- returnbuybilldetail(returnbuybillid)

- buyandruternbilldetail(buybillid, billtype)

2. Query Optimization:

- Date range queries with proper timezone handling

- Efficient JOIN operations for detail tables

- Consider pre-calculating aggregates for large datasets

3. Memory Considerations:

- Large date ranges may return significant data

- Consider pagination for very large result sets

Known Performance Issues

// Separate detail queries for each bill (N+1 problem)
foreach ($returnBuyBillData1 as $myreturnData) {
    $returnbuybilldetailes = $returnBuyBillDetailExt->queryByBuybillidExt($returnbuybillid);
    // Individual query for each bill's details
}

// Could be optimized with batch queries or JOINs

---

🐛 Common Issues & Troubleshooting

1. Timezone Calculation Errors

Issue: Reports showing incorrect date ranges

Cause: Complex timezone offset calculations

Debug:

echo "Original from: $from<br>";
echo "Original to: $to<br>";
echo "Adjusted from: " . date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($from))) . "<br>";
echo "Adjusted to: " . date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($to))) . "<br>";

2. Discount Calculation Inconsistencies

Issue: Discount amounts not matching expected values

Cause: Mixed discount types (percentage vs direct)

Debug:

-- Check discount types distribution
SELECT returnbuybilldiscountrype, COUNT(*), AVG(returnbuybilldiscount) 
FROM returnbuybill 
GROUP BY returnbuybilldiscountrype;

3. Empty Result Sets

Issue: No data returned despite existing returns

Cause: Filter logic errors or date format issues

Debug:

// Debug query strings
echo "Return bills query: " . $queryString . "<br>";
echo "Combined bills query: " . $queryString1 . "<br>";

// Test without filters
$debugResults = $returnBuyBillExt->queryBystring(' WHERE 1=1 LIMIT 5');
var_dump($debugResults);

4. Tax Calculation Problems

Issue: Tax values appearing incorrect or negative

Cause: Complex tax formulas with negative amounts (returns)

Fix:

// Simplify tax calculation
$taxBase = abs($buybilltotalbill - $discountTotal);
$taxRate = 0.15; // Or from settings
$taxvalue = $taxBase * $taxRate;

---

🧪 Testing Scenarios

Test Case 1: Basic Return Report Generation

1. Create test return bills with known values
2. Set date range to include test data
3. Run report without filters
4. Verify totals match manual calculations
5. Check quantity aggregation accuracy

Test Case 2: Filter Combination Testing

1. Test supplier filter alone
2. Test serial number filter
3. Test date range filtering
4. Test combination of multiple filters
5. Verify each filter produces expected results

Test Case 3: Discount and Tax Calculations

1. Create returns with percentage discounts
2. Create returns with direct amount discounts
3. Test mixed discount types in same report
4. Verify tax calculations for each type
5. Check total aggregation accuracy

Test Case 4: Timezone Handling

1. Test with different reportsPlusHours settings
2. Verify date boundaries are handled correctly
3. Test edge cases (midnight boundaries)
4. Check default date handling (today)

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • buyBillController.php - Purchase operations
• • returnBuyBillController.php - Return processing
• • Reports System - General reporting framework

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When return bill reporting requirements change