Return Sell Bill Reports Controller Documentation

File: /controllers/ReturnSellBillReportsController.php

Purpose: Generates comprehensive reports for sales returns and combined sales/return operations

Last Updated: December 20, 2024

Total Functions: 8

Lines of Code: ~717

---

📋 Overview

The Return Sell Bill Reports Controller is a specialized reporting module that provides detailed analysis of sales return operations and combined sales/return transactions. It handles:

• • Sales return bill reports with multiple filtering options
• • Combined sales/return bill analysis
• • Return bill detail views with bill settings
• • Date range filtering with timezone support
• • Client-based return analysis
• • Serial number and bill ID searching
• • Discount and tax calculations for returns
• • User permission-based data filtering
• • Multi-format return bill display

Primary Functions

• ✅ Generate return bill summary reports
• ✅ Display detailed return bill information
• ✅ Show combined sales/return bill details
• ✅ Filter by client, date, serial, or bill ID
• ✅ Calculate return totals and quantities
• ✅ Apply discount and tax calculations
• ✅ Handle user permission restrictions
• ✅ Process both pure returns and combined bills
• ✅ Generate bill receipt formats
• ✅ Support timezone-adjusted reporting

Related Controllers

• • sellbillController.php - Sales operations
• • clientReportsController.php - Customer reports
• • sellbillandruternController.php - Combined sales/returns
• • billreceiptController.php - Bill receipts

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show() / Default Action - Main Report Display

Location: Line 120-167

Purpose: Generate filtered return bill reports based on search criteria

Function Signature:

// Triggered when: do=show or empty $do
$startDate = $_REQUEST['from'];
$endDate = $_REQUEST['to'];
$sellbillSerial = $_REQUEST['sellbillserial'];
$clientId = $_REQUEST['clientid'];

Process Flow:

1. Load client data for dropdown filters

2. Parse search parameters (date range, serial, client)

3. Call showAll() for main report generation

4. Display via returnSellBillReportsview/show.html template

5. Include YouTube tutorial links

Features:

• • Multi-criteria filtering capabilities
• • Client dropdown population
• • Default report generation

---

2. showAll() - Core Report Generation Logic

Location: Line 294-540

Purpose: Build comprehensive return bill report with advanced filtering

Function Signature:

function showAll()

Process Flow:

1. Initialize Query Strings
   ├─ Build WHERE clauses for returns and combined bills
   ├─ Apply user permission restrictions
   └─ Set up date range handling

2. Apply Search Filters
   ├─ Client ID filter
   ├─ Bill serial filter
   ├─ Bill ID filter
   ├─ Date range with timezone support
   └─ Default to today if no criteria

3. Process Return Bills
   ├─ Query returnsellbill table with filters
   ├─ Calculate totals and quantities
   ├─ Apply discount calculations
   ├─ Calculate tax amounts
   └─ Mark as type "returnSell"

4. Process Combined Bills
   ├─ Query sellbillandrutern table
   ├─ Extract return portions only
   ├─ Calculate combined bill totals
   ├─ Apply discount calculations
   └─ Mark as type "sellAndReturn"

5. Generate Final Report
   ├─ Merge both datasets
   ├─ Calculate grand totals
   ├─ Assign to template variables
   └─ Display comprehensive results

Permission Handling:

if ($userData->viewbills == 0) {
    // User can only see their own bills
    $queryString .= ' sellbillandrutern.userid =' . $_SESSION['userid'] . ' AND';
    $returnqueryString .= ' returnsellbill.userid =' . $_SESSION['userid'] . ' AND';
} elseif ($userData->viewbills == 2) {
    // User can see bills from their user group
    $queryString .= ' u.usergroupid =' . $_SESSION['usergroupid'] . ' AND';
    $returnqueryString .= ' user2.usergroupid =' . $_SESSION['usergroupid'] . ' AND';
}

Timezone Support:

$Programsetting = $ProgramsettingDAO->load(1);
if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($endDate)));
    $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($startDate)));
}

---

3. returnDetail() - Return Bill Detail View

Location: Line 168-187

Purpose: Display detailed information for a specific return bill

Function Signature:

// Triggered when: do=returnDetail
$returnsellbillId = $_GET['returnsellbillid'];

Process Flow:

1. Call showDetail() to load return bill data

2. Load bill formatting settings via loadBillProperty()

3. Load bill name configuration

4. Load program settings for display formatting

5. Display via returnSellBillReportsview/returnDetail.html

Data Loaded:

• • Return bill header information
• • Return bill detail line items
• • Total quantity returned
• • Bill formatting settings
• • Program configuration

---

4. sellAndReturnDetail() - Combined Bill Detail View

Location: Line 188-211

Purpose: Display detailed information for combined sales/return bills

Function Signature:

// Triggered when: do=sellAndReturnDetail
$sellbillId = $_GET['sellbillid'];

Process Flow:

1. Call showsellAndReturnDetail() to load combined bill data

2. Load bill formatting settings

3. Load bill name configuration

4. Load program settings

5. Display via returnSellBillReportsview/sellAndReturnDetail.html

Data Loaded:

• • Combined bill header information
• • Sales detail line items (selltype = 0)
• • Return detail line items (selltype = 1)
• • Separate quantity totals for sales and returns

---

5. showDetail() - Return Bill Data Loader

Location: Line 250-267

Purpose: Load comprehensive data for a specific return bill

Function Signature:

function showDetail($returnsellbillId)

Process Flow:

1. Load return bill header via loadReturnsellbillById()

2. Load return bill details via queryWithReturnsellbillId()

3. Calculate total returned quantity

4. Return array with all data components

Return Value:

return array(
    $returnsellbillData,      // Header information
    $returnsellbilldetailData, // Line item details  
    $quantity                  // Total quantity
);

---

6. showsellAndReturnDetail() - Combined Bill Data Loader

Location: Line 269-292

Purpose: Load comprehensive data for a combined sales/return bill

Function Signature:

function showsellAndReturnDetail($sellbillid)

Process Flow:

1. Load combined bill header via loadSellbillandruternById()

2. Load sales details via queryWithSellBillIdAndSellType(billid, 0)

3. Calculate total sales quantity

4. Load return details via queryWithSellBillIdAndSellType(billid, 1)

5. Calculate total return quantity

6. Return array with all data components

Return Value:

return array(
    $sellbillandruternData,    // Header information
    $sellbilldetailData,       // Sales line items
    $ruternbilldetailData,     // Return line items
    $sellQuantity,             // Total sales quantity
    $returnQuantity            // Total return quantity
);

---

7. loadAllClient() - Client Data Loader

Location: Line 222-228

Purpose: Load all clients for dropdown filter population

Function Signature:

function loadAllClient()

Returns: Array of all client objects

---

8. loadBillProperty() - Bill Settings Loader

Location: Line 241-248

Purpose: Load bill formatting settings for a specific bill type

Function Signature:

function loadBillProperty($billnameid)

Returns: Array of bill setting configurations

---

🔄 Workflows

Workflow 1: Return Bill Report Generation

---

Workflow 2: Return Bill Detail Display

---

🌐 URL Routes & Actions

Main Report (do=show):

• • from - Start date (optional, YYYY-MM-DD)
• • to - End date (optional, YYYY-MM-DD)
• • clientid - Customer ID filter (optional, -1 for all)
• • sellbillserial - Bill serial number filter (optional)
• • sellbillid - Specific bill ID filter (optional)

Return Detail (do=returnDetail):

• • returnsellbillid - Return bill ID (required)

Combined Detail (do=sellAndReturnDetail):

• • sellbillid - Combined bill ID (required)

---

🧮 Calculation Methods

Discount Calculation

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

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

Tax Calculation

// Tax calculated after discount application
$taxValue = $billAfterTotal - ($billBeforeDiscount - $discountAmount);

Total Quantity Calculation

// For return bills
foreach ($returnsellbilldetailData as $detail) {
    $quantity = $quantity + $detail->returnsellbilldetailquantity;
}

// For combined bills (separate sales and returns)
foreach ($sellbilldetailData as $detail) {
    $sellQuantity = $sellQuantity + $detail->sellbilldetailquantity;
}
foreach ($returnbilldetailData as $detail) {
    $returnQuantity = $returnQuantity + $detail->sellbilldetailquantity;
}

---

🔒 Security & Permissions

User Permission Levels

// ViewBills Permission Check
$userData = $myUserRecord->load($_SESSION['userid']);

if ($userData->viewbills == 0) {
    // User can only see their own bills
    $queryString .= ' returnsellbill.userid =' . $_SESSION['userid'] . ' AND';
} elseif ($userData->viewbills == 2) {
    // User can see bills from their user group
    $queryString .= ' user2.usergroupid =' . $_SESSION['usergroupid'] . ' AND';
}

Permission Levels:

• • viewbills = 0 - User can only see their own return bills
• • viewbills = 1 - User can see all return bills
• • viewbills = 2 - User can see return bills from their user group

Input Sanitization

• • All $_REQUEST and $_GET parameters filtered through framework
• • Date validation and format checking
• • ID parameters cast to integers where appropriate
• • SQL injection prevented by DAO layer

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- returnsellbill(returnsellbillclientid, returnsellbilldate, conditions)

- sellbillandrutern(sellbillclientid, sellbilldate, conditions)

- returnsellbilldetail(returnsellbillid)

- sellandruternbilldetail(sellbillid, selltype)

2. Query Optimization:

- Use of efficient date range filtering

- Proper condition filtering to exclude cancelled bills

- Efficient joins for client name resolution

- Separate queries for different bill types to optimize indexes

3. Memory Management:

- Large date ranges may return significant data

- Consider pagination for high-volume return operations

- Efficient array merging for combined datasets

Known Performance Issues

-- This pattern can be slow for large datasets
SELECT * FROM returnsellbill r 
LEFT JOIN client c ON r.returnsellbillclientid = c.clientid
WHERE conditions = 0 
ORDER BY returnsellbilldate DESC;

-- Solution: Add composite indexes and limit results
CREATE INDEX idx_return_client_date ON returnsellbill(returnsellbillclientid, returnsellbilldate, conditions);

---

🐛 Common Issues & Troubleshooting

1. Missing Return Data

Issue: Some return bills don't appear in reports

Cause: Bills marked as cancelled or permission restrictions

Debug:

-- Check for cancelled bills
SELECT COUNT(*) FROM returnsellbill WHERE conditions != 0;

-- Check user permission restrictions
SELECT userid, viewbills FROM user WHERE userid = [SESSION_USERID];

2. Incorrect Discount Calculations

Issue: Discount amounts don't match bill totals

Cause: Mixed discount types or missing detail discounts

Debug:

-- Check discount configuration
SELECT returnsellbillid, returnsellbilldiscount, returnsellbilldiscounttype,
       returnsellbilltotalbill, returnsellbillaftertotalbill
FROM returnsellbill WHERE returnsellbillid = [BILL_ID];

-- Check detail-level discounts
SELECT SUM(returnsellbilldetaildiscount) 
FROM returnsellbilldetail WHERE returnsellbillid = [BILL_ID];

3. Date Range Issues

Issue: Reports show no data for valid date ranges

Cause: Timezone configuration or date format problems

Fix:

// Check timezone settings
$Programsetting = $ProgramsettingDAO->load(1);
echo "Report Plus Hours: " . $Programsetting->reportsPlusHours;

// Ensure proper date format
if (!empty($startDate)) $startDate .= " 00:00:00";
if (!empty($endDate)) $endDate .= " 23:59:59";

4. Combined Bill Display Issues

Issue: Combined bills show incorrect separation between sales and returns

Cause: Selltype field confusion or missing detail records

Debug:

-- Check selltype values in combined bill details
SELECT selltype, COUNT(*) 
FROM sellandruternbilldetail 
WHERE sellbillid = [BILL_ID] 
GROUP BY selltype;

-- Verify data consistency
SELECT * FROM sellandruternbilldetail WHERE sellbillid = [BILL_ID];

---

🧪 Testing Scenarios

Test Case 1: Basic Return Report

1. Create test return bills with different clients and dates
2. Apply various discount types and amounts
3. Run report with different date ranges
4. Verify calculations match bill totals
5. Check that cancelled bills are excluded

Test Case 2: Permission Enforcement

1. Login as user with viewbills = 0 (own bills only)
2. Create bills for different users
3. Run return report and verify only own bills appear
4. Test with different permission levels
5. Verify user group restrictions work correctly

Test Case 3: Combined Bill Processing

1. Create combined sales/return bills
2. Add both sales and return line items
3. Run combined bill detail report
4. Verify correct separation of sales vs returns
5. Check quantity calculations for each type

Debug Mode Enable

// Add at top of showAll() function for debugging
echo "Query String: " . $queryString . "<br>";
echo "Return Query String: " . $returnqueryString . "<br>";
echo "Date Range: " . $startDate . " to " . $endDate . "<br>";

// Debug data processing
foreach ($returnsellbillData as $bill) {
    echo "Bill ID: " . $bill->returnsellbillid . 
         " Amount: " . $bill->returnsellbillaftertotalbill . 
         " Discount: " . $bill->returnsellbilldiscount . "<br>";
}

---

📚 Related Documentation

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

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When return processing logic changes