Auto Sales Report Controller Documentation

File: /controllers/autoSalesReport.php

Purpose: Generates automated sales reports comparing purchase and sale data to calculate profit margins

Last Updated: December 20, 2024

Total Functions: 4

Lines of Code: ~240

---

📋 Overview

The Auto Sales Report Controller is a specialized reporting module that provides comprehensive profit analysis by tracking products from purchase to sale. It handles:

• • Serial number-based product tracking
• • Purchase vs sale price comparison
• • Profit margin calculations
• • Supplier and client filtering
• • Date range analysis
• • Real-time data retrieval with AJAX
• • DataTables integration for dynamic reporting
• • Return exclusion logic

Primary Functions

• ✅ Generate profit analysis reports
• ✅ Track products by serial numbers
• ✅ Compare buy and sell prices
• ✅ Filter by supplier, client, product code
• ✅ Date range filtering
• ✅ AJAX-based data loading
• ✅ Return bill exclusion
• ✅ Real-time profit calculations

Related Controllers

• • sellbillController.php - Sales operations
• • buyBillController.php - Purchase operations
• • clientReportsController.php - Client reporting

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Report Interface

Location: Line 25

Purpose: Display the main report interface

Process Flow:

1. Display header template

2. Load main report interface (autoSalesReportview/index.html)

3. Display footer template

---

2. select2supplier() - Supplier Search

Location: Line 45

Purpose: AJAX endpoint for supplier autocomplete functionality

Function Signature:

function select2supplier()

Process Flow:

1. Get search term from POST data

2. Query suppliers with LIKE match on supplier name

3. Format results for Select2 dropdown

4. Return JSON response

SQL Query:

SELECT supplierid, suppliername as name
FROM supplier
WHERE suppliername LIKE '%{searchTerm}%' 
LIMIT 50

---

3. select2client() - Client Search

Location: Line 63

Purpose: AJAX endpoint for client autocomplete functionality

Function Signature:

function select2client()

Process Flow:

1. Get search term from POST data

2. Query clients with LIKE match on client name

3. Format results for Select2 dropdown

4. Return JSON response

SQL Query:

SELECT clientid, clientname as name
FROM client 
WHERE clientname LIKE '%{searchTerm}%' 
LIMIT 50

---

4. select2code() - Product Code Search

Location: Line 81

Purpose: AJAX endpoint for product code autocomplete

Function Signature:

function select2code()

Process Flow:

1. Get search term from POST data

2. Query unique product codes from sales details

3. Format results for Select2 dropdown

4. Return JSON response

SQL Query:

SELECT parcode
FROM sellbilldetail 
WHERE parcode LIKE '%{searchTerm}%' 
LIMIT 50

---

5. showajax() - Main Report Data

Location: Line 99

Purpose: Generate profit analysis report with DataTables integration

Function Signature:

function showajax()

Process Flow:

1. Parse filter parameters (dates, supplier, client, product code)

2. Build dynamic WHERE clause based on filters

3. Execute complex JOIN query linking purchases to sales

4. Filter out returned items

5. Calculate profit margins

6. Format data for DataTables

7. Add totals row

8. Return JSON response

Key Features:

• • Return Exclusion: Checks returnsellbill to exclude returned items
• • Profit Calculation: sellbillaftertotalbill - buybillaftertotalbill
• • Date Filtering: Defaults to current day if no dates provided
• • DataTables Integration: Supports sorting, pagination, search

Complex SQL Query:

SELECT *, sellbilldetail.sellbillid as sellbillidend, 
       sellbilldetail.parcode as parcodes, 
       sellbill.sellbilldate as sellbilldates 
FROM sellbilldetail
LEFT JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid 
    AND sellbill.conditions = 0 
LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
LEFT JOIN productserial ON sellbilldetail.parcode = productserial.serialnumber
LEFT JOIN buybill ON productserial.billid = buybill.buybillid
LEFT JOIN buybilldetail ON buybill.buybillid = buybilldetail.buybillid
LEFT JOIN supplier ON buybill.buybillsupplierid = supplier.supplierid
WHERE productserial.del = 0 {searchQuery}

Return Check Logic:

$countreturnsellbill = R::count("returnsellbill",
    " LEFT JOIN returnsellbilldetail ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid  
    WHERE returnsellbilldetail.parcode = ? 
    AND returnsellbill.returnsellbilldate >= ? 
    AND returnsellbill.conditions = 0 ", 
    [$row["parcodes"], $row["sellbilldates"]]);

---

🔄 Workflows

Workflow 1: Profit Analysis Report Generation

---

🌐 URL Routes & Actions

Client Search (do=select2client):

• • Method: POST
• • Parameter: searchTerm
• • Returns: JSON array for Select2

Supplier Search (do=select2supplier):

• • Method: POST
• • Parameter: searchTerm
• • Returns: JSON array for Select2

Report Data (do=showajax):

• • Method: POST
• • Parameters: fromdate, todate, data1 (supplier), data2 (client), data3 (code)
• • Returns: DataTables-formatted JSON

---

🧮 Calculation Methods

Profit Calculation

$finaltotals += round($row["sellbillaftertotalbill"] - $row["buybillaftertotalbill"]);

Return Detection Logic

$countreturnsellbill = R::count("returnsellbill",
    " LEFT JOIN returnsellbilldetail ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid  
    WHERE returnsellbilldetail.parcode = ? 
    AND returnsellbill.returnsellbilldate >= ? 
    AND returnsellbill.conditions = 0 ", 
    [$row["parcodes"], $row["sellbilldates"]]);
    
if ($countreturnsellbill == 0) {
    // Include in profit calculation
}

Date Range Handling

if ($fromdate != '' && $todate != '') {
    $searchQuery .= " and sellbilldate >= '$fromdate' and sellbilldate <= '$todate' ";
} else {
    $today = date("Y-m-d");
    $searchQuery .= 'and sellbilldate >= "' . $today . ' 00-00-00" and sellbilldate <= "' . $today . ' 23-59-55" ';
}

---

🔒 Security & Permissions

Input Sanitization

• • POST parameters are used directly in SQL queries
• • Security Risk: SQL injection vulnerability in search filters
• • Recommendation: Use parameterized queries

Current Security Issues

// VULNERABLE CODE
$searchQuery .= " and client.clientid = " . $clientid . " ";
$searchQuery .= " and supplier.supplierid = " . $supplierid . " ";

// SHOULD BE
$searchQuery .= " and client.clientid = ? ";
// With parameter binding

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- productserial(serialnumber, billid)

- sellbilldetail(parcode, sellbillid)

- sellbill(sellbilldate, conditions)

- buybill(buybillid, buybillsupplierid)

2. Query Performance:

- Complex JOIN across 6+ tables

- No LIMIT on main query (potential memory issues)

- Subquery for each row to check returns

Known Performance Issues

• • N+1 Query Problem: Return check executed for each result row
• • Large Joins: Multiple LEFT JOINs can be slow with large datasets
• • Missing Pagination: No LIMIT clause for large result sets

---

🐛 Common Issues & Troubleshooting

1. Missing Products in Report

Issue: Products don't appear in profit analysis

Cause: Product missing from productserial table or del = 1

Debug:

SELECT * FROM productserial WHERE serialnumber = '{code}';

2. Incorrect Profit Calculations

Issue: Profit margins don't match expected values

Cause: Return exclusion or wrong price fields

Check:

SELECT sellbillaftertotalbill, buybillaftertotalbill 
FROM sellbill s, buybill b 
WHERE s.sellbillid = {sellbillid} AND b.buybillid = {buybillid};

3. Performance Issues

Issue: Report loads slowly or times out

Cause: Large date ranges or missing indexes

Solutions:

• • Add date range limits
• • Create composite indexes
• • Implement pagination

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • sellbillController.md - Sales operations
• • buyBillController.php - Purchase operations

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur