# 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
- [x] Generate profit analysis reports
- [x] Track products by serial numbers
- [x] Compare buy and sell prices
- [x] Filter by supplier, client, product code
- [x] Date range filtering
- [x] AJAX-based data loading
- [x] Return bill exclusion
- [x] Real-time profit calculations

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [buyBillController.php](buyBillController.md) - Purchase operations
- [clientReportsController.php](clientReportsController.md) - Client reporting

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, sellbilldetailproductid, parcode, sellbilldetailquantity |
| **productserial** | Product serial number tracking | serialnumber, billid, del |
| **sellbill** | Sales bills master | sellbillid, sellbillclientid, sellbillaftertotalbill, sellbilldate, conditions |
| **buybill** | Purchase bills master | buybillid, buybillsupplierid, buybillaftertotalbill, buybilldate |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer information | clientid, clientname |
| **supplier** | Supplier information | supplierid, suppliername |
| **buybilldetail** | Purchase bill line items | buybilldetailid, buybillid |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilldate, conditions |
| **returnsellbilldetail** | Return bill details | returnsellbilldetailid, returnsellbillid, parcode |

---

## 🔑 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**:
```php
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**:
```sql
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**:
```php
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**:
```sql
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**:
```php
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**:
```sql
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**:
```php
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**:
```sql
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**:
```php
$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
```
┌─────────────────────────────────────────────────────────────┐
│              START: Load Report Interface                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. User Sets Filters                                       │
│     - Date range (from/to)                                  │
│     - Supplier (optional)                                   │
│     - Client (optional)                                     │
│     - Product code (optional)                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. AJAX Call to showajax()                                 │
│     - Parse filter parameters                               │
│     - Build search query string                             │
│     - Apply date filters (default: today)                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute Complex JOIN Query                              │
│     - Link sellbilldetail to productserial via parcode     │
│     - Link productserial to buybill via billid             │
│     - Include supplier and client information               │
│     - Filter by search criteria                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Each Result Row                                 │
│     FOR EACH matching product:                              │
│       │                                                     │
│       ├─→ Check for returns after sale date                │
│       │   └─ Exclude if product was returned               │
│       │                                                     │
│       ├─→ Calculate profit margin                          │
│       │   └─ Sell price - Buy price                        │
│       │                                                     │
│       ├─→ Format data for DataTable                        │
│       │                                                     │
│       └─→ Add to totals                                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Generate Summary Row                                    │
│     - Total buy amount                                      │
│     - Total sell amount                                     │
│     - Total profit                                          │
│     - Current timestamp                                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Return JSON Response                                    │
│     - Data array for DataTable                             │
│     - Record counts                                         │
│     - Summary totals                                        │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Main report interface |
| `do=select2client` | `select2client()` | Client autocomplete |
| `do=select2supplier` | `select2supplier()` | Supplier autocomplete |
| `do=showajax` | `showajax()` | Report data via AJAX |
| `do=select2code` | `select2code()` | Product code autocomplete |

### AJAX Endpoints
**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
```php
$finaltotals += round($row["sellbillaftertotalbill"] - $row["buybillaftertotalbill"]);
```

### Return Detection Logic
```php
$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
```php
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
```php
// 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**:
```sql
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**:
```sql
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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations
- [buyBillController.php](buyBillController.md) - Purchase operations

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur