Sell Bill Reports Controller Documentation

File: /controllers/sellBillReportsController.php

Purpose: Generates comprehensive sales reports with filtering, search, and detailed transaction analysis

Last Updated: December 20, 2024

Total Functions: 15+

Lines of Code: ~1,143

---

📋 Overview

The Sell Bill Reports Controller is a specialized reporting module that provides detailed sales transaction analysis and reporting capabilities. It handles:

• • Sales bill listing and filtering
• • Date range and client-based filtering
• • Sales and return bill combined reports
• • Detailed bill analysis with line items
• • Tax and discount calculations
• • Search functionality by serial number, client, and comments
• • Combined sell & return transaction reports
• • Bill detail views with print capabilities
• • YouTube tutorial integration for user guidance

Primary Functions

• ✅ Generate sales bill reports with filtering
• ✅ Combined sales and return reports
• ✅ Detailed bill views with line items
• ✅ Search by serial number, client, date range
• ✅ Tax and discount calculations
• ✅ User permission filtering
• ✅ Multiple date format support
• ✅ Bill printing and detail views
• ✅ YouTube tutorial integration
• ✅ Real-time total calculations

Related Controllers

• • sellbillController.php - Sales operations and bill creation
• • clientReportsController.php - Customer analysis
• • returnsellbillController.php - Sales returns processing
• • buyBillReportsController.php - Purchase reports

---

🗄️ Database Tables

Primary Sales Tables (Direct Operations)

🔑 Key Functions

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

Location: Line 242

Purpose: Generate comprehensive sales bill report with multiple filtering options

Function Signature:

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

Process Flow:

1. Load client data for dropdown filtering

2. Load YouTube tutorial links

3. Call showAll() with filter parameters

4. Display via sellBillReportsview/show.html template

Features:

• • Multi-parameter filtering (date, client, serial, comment)
• • User permission enforcement
• • YouTube tutorial integration
• • Real-time total calculations

---

2. showAll() - Core Sales Report Logic

Location: Line 445

Purpose: Build detailed sales report with dynamic filtering and calculations

Function Signature:

function showAll()

Process Flow:

1. Parse request parameters for filtering

2. Build dynamic SQL query strings for different bill types

3. Apply user permission restrictions (viewbills check)

4. Handle date range processing with timezone support

5. Query sales bills from sellbill table

6. Query combined bills from sellbillandrutern table

7. Calculate totals, discounts, and taxes

8. Merge datasets and assign to template

Key Variables:

• • $queryString - Dynamic WHERE clause for sellbill
• • $sallandreturnqueryString - WHERE clause for combined bills
• • $totalBills - Sum of all bill amounts
• • $totalQTY - Total quantity sold
• • $totaldiscount - Total discount amount
• • $totaltax - Total tax amount

Filtering Capabilities:

// Client filtering
if (isset($clientId) && $clientId != '-1') {
    $queryString .= ' sellbillclientid = ' . $clientId . ' AND';
}

// Serial number filtering
if (isset($sellbillSerial) && $sellbillSerial != '') {
    $queryString .= ' sellbillSerial = "' . $sellbillSerial . '" AND';
}

// Comment search
if (isset($sellbillcomment) && $sellbillcomment != '') {
    $queryString .= " sellbill.comment LIKE '%" . $sellbillcomment . "%' AND";
}

Discount Calculation Logic:

if ($sellbilldiscounttype == 1) {
    // Fixed amount discount
    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
} else {
    // Percentage discount
    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
}

---

3. sellandreturnreport - Combined Sales & Returns Report

Location: Line 293

Purpose: Generate unified report showing both sales and return transactions

Process Flow:

1. Load client data for filtering

2. Load YouTube tutorials

3. Call showAll() for sales data

4. Call returnshowAll() for return data

5. Display via sellBillReportsview/sellandreturnreport.html

Template Variables:

• • $sellbillData - Sales transactions
• • $returnsellbillData - Return transactions
• • $totalBills - Sales total
• • $returntotalBills - Returns total

---

4. returnshowAll() - Return Bills Report Logic

Location: Line 742

Purpose: Generate return bill report with same filtering capabilities as sales

Function Signature:

function returnshowAll()

Process Flow:

1. Build query strings for return bill filtering

2. Query returnsellbill table for pure returns

3. Query sellbillandrutern table for return portions

4. Calculate return totals, discounts, and taxes

5. Merge return datasets

6. Assign to template variables

Return-Specific Logic:

// Return bills from returnsellbill table
$returnsellbillData = $myReturnsellbillEx->queryallWithqueryString2($returnqueryString);

// Return portions from combined bills
$sellbillandruternData = $mySellbillandruternEx->GetSellBillAndReturnUsingQueryString($queryString);

---

5. sellDetail - Individual Bill Detail View

Location: Line 302

Purpose: Show detailed view of a single sales bill with line items

Function Signature:

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

Process Flow:

1. Call showDetail($sellbillId) to load bill data

2. Load bill formatting settings if available

3. Load bill name configuration

4. Display via sellBillReportsview/sellDetail.html

Data Retrieved:

• • Bill master data
• • All line items with quantities and prices
• • Total quantity calculation
• • Bill formatting settings
• • Print-ready layout

---

6. showDetail() - Load Bill Detail Data

Location: Line 402

Purpose: Retrieve comprehensive bill data for detail view

Function Signature:

function showDetail($sellbillid)

Process Flow:

1. Load bill master record via loadSellBillById()

2. Load all bill line items via queryWithSellBillId()

3. Calculate total quantities

4. Return array with bill data, detail data, and totals

Return Array:

return array($sellbillData, $sellbilldetailData, $quantity);
// [0] = Bill master data
// [1] = Line items array
// [2] = Total quantity

---

7. sellAndReturnDetail - Combined Bill Detail View

Location: Line 324

Purpose: Show detailed view of combined sell & return bill

Function Signature:

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

Process Flow:

1. Call showsellAndReturnDetail($sellbillId)

2. Load bill formatting settings

3. Display via sellBillReportsview/sellAndReturnDetail.html

Template Variables:

• • $sellbillandruternData - Combined bill master
• • $sellbilldetailData - Sales line items
• • $ruternbilldetailData - Return line items
• • $sellQuantity - Total sold quantity
• • $returnQuantity - Total returned quantity

---

8. showsellAndReturnDetail() - Load Combined Bill Data

Location: Line 420

Purpose: Retrieve data for combined sell & return bill detail view

Function Signature:

function showsellAndReturnDetail($sellbillid)

Process Flow:

1. Load combined bill master data

2. Query sales line items (selltype = 0)

3. Query return line items (selltype = 1)

4. Calculate separate quantities for sales and returns

5. Return comprehensive data array

Sell Type Logic:

// Sales items
$sellbilldetailData = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellType($sellbillid, 0);

// Return items  
$ruternbilldetailData = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellType($sellbillid, 1);

---

9. loadAllClient() - Client Data for Filtering

Location: Line 358

Purpose: Load customer list for report filtering dropdowns

Function Signature:

function loadAllClient()

Process Flow:

1. Check user permissions (viewclients)

2. Apply user restriction if needed

3. Query client data via queryByCondition()

4. Return filtered client array

Permission Logic:

$userData = $myUserRecord->load($_SESSION['userid']);
if ($userData->viewclients == 0) {
    $queryString .= ' AND client.userid =' . $_SESSION['userid'];
}

---

10. loadBillProperty() - Bill Formatting Settings

Location: Line 392

Purpose: Load bill formatting settings for print layouts

Function Signature:

function loadBillProperty($billnameid)

Process Flow:

1. Query bill settings via queryWithBillnameId()

2. Return settings array for template use

---

11. Date Range Processing Functions

Location: Various (showByDate, showBySriral, etc.)

Purpose: Legacy filtering functions for backward compatibility

Functions:

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

---

🔄 Workflows

Workflow 1: Sales Report Generation

---

Workflow 2: Bill Detail View

---

🌐 URL Routes & Actions

Main Sales Report (do=show):

• • from - Start date (YYYY-MM-DD) - optional
• • to - End date (YYYY-MM-DD) - optional
• • clientid - Customer ID filter - optional
• • sellbillserial - Serial number filter - optional
• • sellbillid - Bill ID filter - optional
• • sellbillcomment - Comment search terms - optional

Combined Report (do=sellandreturnreport):

• • Same parameters as main report
• • Shows both sales and returns data

Bill Detail View (do=sellDetail):

• • sellbillid - Required: Bill ID to display

Combined Bill Detail (do=sellAndReturnDetail):

• • sellbillid - Required: Combined bill ID to display

---

🧮 Calculation Methods

Discount Processing

// Bill-level discount calculation
if ($sellbilldiscounttype == 1) {
    // Fixed amount discount
    $discount = $sellbilldiscount;
} else {
    // Percentage discount
    $discount = ($sellbilltotalbill / 100) * $sellbilldiscount;
}

// Include detail-level discounts
$detaildiscount = $mySellbilldetailEx->getsumdiscount($sellbillid);
$totalDiscount = $discount + $detaildiscount;

Tax Calculation

// Tax calculated after discount
$taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - $discount);

Quantity Totals

$quantity = 0;
foreach ($sellbilldetailData as $sellbilldetail) {
    $quantity = $quantity + ($sellbilldetail->sellbilldetailquantity);
}

Date Range Processing

// Handle timezone offset from program settings
$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)));
} else {
    $endDate = $endDate . ' 23:59:59';
    $startDate = $startDate . " 00:00:00";
}

---

🔒 Security & Permissions

User Permission Levels

// ViewBills Permission Check
$userData = $myUserRecord->load($_SESSION['userid']);
if ($userData->viewbills == 0) {
    $queryString .= ' client.userid =' . $_SESSION['userid'] . ' AND';
}

Permission Levels:

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

Input Sanitization

• • All $_REQUEST parameters are filtered through the framework
• • Numeric IDs cast to integer where appropriate
• • SQL injection prevented by DAO layer parameterized queries
• • String escaping handled by MySQL DAO classes

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- sellbill(sellbillclientid, sellbilldate)

- sellbill(sellbillserial)

- sellbill(sellbillid)

- sellbilldetail(sellbillid)

- returnsellbill(returnsellbillclientid, returnsellbilldate)

- sellbillandrutern(sellbillclientid, sellbilldate)

2. Query Optimization:

- Date filtering with proper datetime format

- Efficient WHERE clause building

- Avoid N+1 queries by batching related data

3. Memory Management:

- Large date ranges may return thousands of bills

- Consider pagination for high-volume periods

- Limit detail view to necessary data

Known Performance Issues

-- This query can be slow without proper indexing
SELECT * FROM sellbill 
WHERE sellbilldate BETWEEN ? AND ?
AND sellbillclientid = ?
ORDER BY sellbilldate DESC;

-- Solution: Composite index
CREATE INDEX idx_sellbill_client_date ON sellbill(sellbillclientid, sellbilldate);

---

🐛 Common Issues & Troubleshooting

1. Missing Bills in Report

Issue: Bills don't appear in report despite being in date range

Cause: conditions field not equal to 0 (bill is cancelled/draft)

Debug:

SELECT conditions, COUNT(*) FROM sellbill 
WHERE sellbilldate BETWEEN '[START]' AND '[END]'
GROUP BY conditions;

2. Incorrect Total Calculations

Issue: Report totals don't match individual bill totals

Cause: Discount calculation errors or mixed bill types

Debug:

-- Check discount types and amounts
SELECT sellbilldiscounttype, sellbilldiscount, 
       sellbilltotalbill, sellbillaftertotalbill
FROM sellbill WHERE sellbillid = [ID];

3. Date Range Issues

Issue: No results for valid date ranges

Cause: DateTime format mismatch or timezone settings

Fix:

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

4. Permission Errors

Issue: Users see bills they shouldn't access

Cause: Permission checks not properly applied

Fix:

-- Check user permission settings
SELECT userid, viewbills, viewclients FROM user WHERE userid = [USER_ID];

---

🧪 Testing Scenarios

Test Case 1: Basic Sales Report

1. Create test sales bills with known amounts
2. Set date range covering test bills
3. Verify bill count and totals match database
4. Test different filter combinations
5. Check permission filtering works

Test Case 2: Discount Calculations

1. Create bills with fixed amount discounts
2. Create bills with percentage discounts  
3. Add line-item level discounts
4. Verify total discount calculations
5. Check tax calculations after discount

Test Case 3: Combined Bill Types

1. Create regular sales bills
2. Create combined sell/return bills
3. Run report covering both types
4. Verify no double-counting occurs
5. Check totals are properly segregated

Debug Mode Enable

// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug query building
echo "Sales Query: " . $queryString . "<br>";
echo "Combined Query: " . $sallandreturnqueryString . "<br>";

// Debug totals
echo "Total Bills: " . $totalBills . "<br>";
echo "Total Quantity: " . $totalQTY . "<br>";

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide and performance fixes
• • sellbillController.md - Sales bill creation and management
• • clientReportsController.md - Customer analysis reports
• • Database Schema Documentation - Table relationships and structure

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur