# User Rate Report Controller Documentation

**File**: `/controllers/userratereportController.php`  
**Purpose**: Generates employee commission/reward rate reports based on sales performance and pricing types  
**Last Updated**: December 20, 2024  
**Total Functions**: 3  
**Lines of Code**: ~697

---

## 📋 Overview

The User Rate Report Controller is a specialized reporting module that calculates and displays employee commission/reward rates based on their sales performance across different pricing types. It handles:
- Individual employee commission calculations
- All employees summary reports
- Commission breakdown by pricing types (wholesale, retail, half-price)
- Sales vs returns commission calculations
- Date range filtering for commission periods
- Dynamic commission rate application
- Performance-based reward calculations

### Primary Functions
- [x] Calculate employee commission rates on sales
- [x] Generate individual employee commission reports
- [x] Create all-employees summary reports
- [x] Handle different pricing type commissions
- [x] Process sales and returns for commission calculation
- [x] Apply date range filtering
- [x] Calculate net commission after returns
- [x] Support seller-only vs all-user filtering
- [x] Display commission breakdowns by price types
- [x] Handle combined sell/return bill types

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [returnsellbillController.php](#) - Sales returns
- [sellbillandruternController.php](#) - Combined sales/returns
- [userController.php](#) - User management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | Employee/seller master data | userid, employeename, awardrate, awardrategomla, awardratehalf, userlevel |
| **sellbill** | Sales bills | sellbillid, sellerid, sellbillaftertotalbill, sellQuantity, sellbilldate, conditions, pricetype |
| **returnsellbill** | Return sales bills | returnsellbillid, sellerid, returnsellbillaftertotalbill, returnsellQuantity, returnsellbilldate, pricetype |
| **sellbillandrutern** | Combined sales/return bills | sellbillid, sellerid, sellbillaftertotalbill, sellQuantity, returnsellQuantity, sellbilldate, pricetype |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer information | clientid, clientname |
| **sellbilldetail** | Sales bill line items | sellbilldetailid, sellbillid, pricetype |
| **returnsellbilldetail** | Return bill line items | returnsellbilldetailid, returnsellbillid, pricetype |
| **sellandruternbilldetail** | Combined bill details | sellandruternbilldetailid, sellbillid, pricetype |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **show() / Default Action** - Basic Commission Report
**Location**: Line 114-126  
**Purpose**: Generate commission report for current month or specific criteria

**Function Signature**:
```php
// Triggered when: do=show or empty $do
// Calls: showAll() function for main processing
```

**Process Flow**:
1. Load all sellers via `loadseller()`
2. Call `showAll()` for main report generation
3. Display via `userratereportview/show.html` template
4. Include YouTube tutorial links

**Features**:
- Default monthly report generation
- Seller filtering capabilities
- Template-based output

---

### 2. **showAll()** - Main Commission Calculation Engine
**Location**: Line 166-513  
**Purpose**: Core logic for calculating employee commissions with multiple scenarios

**Function Signature**:
```php
function showAll()
```

**Process Flow**:
```
1. Parse Input Parameters
   ├─ sellerid: -1 (all), 0 (all), specific ID
   ├─ startDate/endDate: Date range filter
   └─ priceType: -1 (all), 0 (wholesale), 1 (retail), 2 (half)

2. Execute Scenario Logic
   ├─ Scenario #1: All sellers, no date filter (current month)
   ├─ Scenario #2: Specific seller with date range
   ├─ Scenario #3: All sellers with date range
   └─ Scenario #4: Specific seller, no date filter

3. For Each Scenario:
   ├─ Query sales data (sellbill)
   ├─ Query return data (returnsellbill)
   ├─ Query combined data (sellbillandrutern)
   ├─ Calculate commissions per transaction
   ├─ Handle different commission rates by price type
   └─ Calculate final totals

4. Output Results
   ├─ Total sales amount
   ├─ Total commission amount
   ├─ Average commission rate
   └─ Detailed transaction lists
```

**Commission Calculation Logic**:
```php
// For regular sales
$commission = $billAmount * ($employeeRate / 100);

// For returns (subtract commission)
$commission -= $returnAmount * ($employeeRate / 100);

// For combined bills
$netAmount = $sellAmount - $returnAmount;
$commission = $netAmount * ($employeeRate / 100);
```

**Four Main Scenarios**:

**Scenario #1** - All Employees, Current Month:
```php
if ((!$sellerid || $sellerid == -1) && !$startDate && !$endDate) {
    // Query current month data for all level-3 users
    // Calculate average commission rate across all sellers
    // Handle bills with and without assigned sellers
}
```

**Scenario #2** - Specific Employee, Date Range:
```php
elseif ($sellerid != '-1' && $sellerid != '0' && $startDate && $endDate) {
    // Query specific seller's data for date range
    // Calculate individual commission total
    // Apply seller's specific commission rates
}
```

**Scenario #3** - All Employees, Date Range:
```php
elseif ($sellerid == '0' && $startDate && $endDate) {
    // Query all level-3 users for date range
    // Calculate collective commission totals
    // Average commission rates across all sellers
}
```

**Scenario #4** - Specific Employee, No Date:
```php
elseif ($sellerid != '-1' && $sellerid != '0' && !$startDate && !$endDate) {
    // Query specific seller's all-time data
    // Calculate total career commission
}
```

---

### 3. **showAllWithpricesTypes()** - Price Type Breakdown Report
**Location**: Line 515-684  
**Purpose**: Detailed commission analysis broken down by pricing types

**Function Signature**:
```php
function showAllWithpricesTypes()
```

**Process Flow**:
1. Parse input parameters (seller, date range, sellersOnly flag)
2. Build dynamic query strings for sales and returns
3. Query sales data with price type breakdown
4. Query return data with price type breakdown  
5. Query combined bill data with price type breakdown
6. Calculate commission for each price type separately
7. Generate comprehensive breakdown report

**Price Type Commission Rates**:
```php
// Price Type 0: Wholesale (جملة)
$commission = $billAmount * ($awardrateyGomla / 100);

// Price Type 1: Retail (قطعي)  
$commission = $billAmount * ($awardrate / 100);

// Price Type 2: Half Price (نصف جملة)
$commission = $billAmount * ($awardrateHalf / 100);
```

**Data Structure**:
```php
$dataArr = [
    $sellerid => [
        'sellerName' => $employeeName,
        'totalGomla' => 0,     // Wholesale total
        'totalhalf' => 0,      // Half-price total  
        'totalUnit' => 0,      // Retail total
        'rateGomla' => 0,      // Wholesale commission
        'ratehalf' => 0,       // Half-price commission
        'rateUnit' => 0,       // Retail commission
        'totalBill' => 0,      // Grand total sales
        'totalrate' => 0       // Total commission
    ]
];
```

---

### 4. **loadseller()** - Load Seller Data
**Location**: Line 687-696  
**Purpose**: Retrieve all level-3 users (sellers) for dropdown population

**Function Signature**:
```php
function loadseller()
```

**Returns**: Array of user objects with userlevel = 3

---

### 5. **getUsers()** - AJAX User Loading
**Location**: Line 139-147  
**Purpose**: Dynamically load users based on seller-only filter

**Process Flow**:
1. Check `sellersOnly` parameter
2. If sellersOnly = 1: Load sellers via `loadseller()`
3. If sellersOnly = 0: Load all active users
4. Return AJAX template `userratereportview/usersData.html`

---

## 🔄 Workflows

### Workflow 1: Monthly Commission Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│                    START: Monthly Report                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Query Current Month Sales Data                          │
│     - Get all sellbill records for current month           │
│     - Filter by conditions = 0 (active bills)              │
│     - Include seller and client information                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Sales Bills                                     │
│     FOR EACH sales bill:                                    │
│       ├─ Calculate commission: amount × (rate/100)         │
│       ├─ Add to seller's total if seller assigned          │
│       ├─ Accumulate grand totals                           │
│       └─ Track quantity sold                               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Return Bills                                    │
│     FOR EACH return bill:                                   │
│       ├─ Subtract commission: amount × (rate/100)          │
│       ├─ Reduce seller's total if seller assigned          │
│       ├─ Subtract from grand totals                        │
│       └─ Subtract returned quantities                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Combined Bills                                  │
│     FOR EACH combined bill:                                 │
│       ├─ Calculate net amount (sell - return)              │
│       ├─ Calculate commission on net amount                │
│       ├─ Add to seller totals                              │
│       └─ Add to quantity totals                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Calculate Summary Statistics                            │
│     ├─ Total sales amount                                   │
│     ├─ Total commission amount                              │
│     ├─ Average commission rate                              │
│     ├─ Total quantity sold                                  │
│     └─ Bills with vs without sellers breakdown             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Report Output                                  │
│     ├─ Assign data to Smarty templates                     │
│     ├─ Display summary totals                              │
│     ├─ Show detailed transaction lists                     │
│     └─ Present via show.html template                      │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Price Type Commission Breakdown
```
┌─────────────────────────────────────────────────────────────┐
│              START: Price Type Analysis                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Initialize Data Structure                               │
│     ├─ Create array indexed by seller ID                   │
│     ├─ Initialize totals for each price type               │
│     └─ Set up commission tracking per type                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Sales by Price Type                            │
│     FOR EACH sales bill with price type:                   │
│       ├─ Identify price type (0=wholesale, 1=retail, 2=half) │
│       ├─ Apply appropriate commission rate                 │
│       │   ├─ Wholesale: awardrategomla                     │
│       │   ├─ Retail: awardrate                            │
│       │   └─ Half: awardratehalf                          │
│       ├─ Add to seller's type-specific totals             │
│       └─ Accumulate commission for this type              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Process Returns by Price Type                          │
│     FOR EACH return bill with price type:                  │
│       ├─ Identify price type                               │
│       ├─ Apply appropriate commission rate (negative)      │
│       ├─ Subtract from seller's type-specific totals      │
│       └─ Reduce commission for this type                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Process Combined Bills by Price Type                   │
│     FOR EACH combined bill with price type:                │
│       ├─ Identify price type                               │
│       ├─ Apply appropriate commission rate                │
│       ├─ Add to seller's type-specific totals             │
│       └─ Accumulate commission for this type              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Generate Comprehensive Report                          │
│     ├─ Show breakdown by seller and price type            │
│     ├─ Display commission totals per type                 │
│     ├─ Calculate overall performance metrics              │
│     └─ Present via showWithPriceTypes.html template       │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | `showAll()` | Basic commission report |
| `do=showWithPriceTypes` | `showAllWithpricesTypes()` | Price type breakdown report |
| `do=getUsers` | AJAX response | Dynamic user loading |
| `do=sucess` | Template only | Success message display |
| `do=error` | Template only | Error message display |

### Required Parameters by Action

**Basic Commission Report** (`do=show`):
- `sellerid` - Seller ID (-1=all, 0=all, specific ID)
- `startDate` - Start date (optional, YYYY-MM-DD)
- `endDate` - End date (optional, YYYY-MM-DD)  
- `priceType` - Price type filter (-1=all, 0/1/2=specific)

**Price Type Breakdown** (`do=showWithPriceTypes`):
- `sellerid` - Seller ID (-1=all, specific ID)
- `startDate` - Start date (optional)
- `endDate` - End date (optional)
- `sellersOnly` - Filter for sellers only (0/1)

**AJAX User Loading** (`do=getUsers`):
- `sellersOnly` - Include only sellers (1) or all users (0)

---

## 🧮 Calculation Methods

### Commission Calculation by Price Type
```php
// Wholesale Commission (pricetype = 0)
$commission = $billAmount * ($user->awardrategomla / 100);

// Retail Commission (pricetype = 1)  
$commission = $billAmount * ($user->awardrate / 100);

// Half-price Commission (pricetype = 2)
$commission = $billAmount * ($user->awardratehalf / 100);
```

### Average Commission Rate Calculation
```php
// For multiple sellers
$averageRate = $totalCommissionPercentage / $billCount;

// For single seller  
$averageRate = $totalCommission / $totalSales * 100;
```

### Net Commission After Returns
```php
// Sales commission
$totalCommission += $saleAmount * ($commissionRate / 100);

// Subtract returns commission
$totalCommission -= $returnAmount * ($commissionRate / 100);

// Net result
$netCommission = $totalCommission;
```

### Bills With Seller Analysis
```php
if ($bill->sellerid > 0) {
    $billsWithSeller++;
    $totalBillsWithSeller += $bill->amount;
    $totalCommissionFromSellers += $commission;
} else {
    // Bill without assigned seller - no commission
}
```

---

## 🔒 Security & Permissions

### User Level Restrictions
```php
// Only level 3 users (sellers) are included in commission calculations
$userData = $myUserEx->queryWithLevel(3);
```

### Session Management
- Uses standard ERP session authentication
- Respects user permissions from authentication.php
- No additional access control beyond standard login

### Data Validation
- Seller ID validation and type casting
- Date format validation
- Price type enumeration validation
- Input sanitization through framework

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `sellbill(sellerid, sellbilldate, conditions)`
   - `returnsellbill(sellerid, returnsellbilldate, conditions)`
   - `sellbillandrutern(sellerid, sellbilldate, conditions)`
   - `user(userlevel)`

2. **Query Optimization**:
   - Date filtering with proper timestamp handling
   - Condition filtering to exclude cancelled bills
   - Efficient joins for client name resolution

3. **Memory Management**:
   - Large date ranges may require significant memory
   - Consider pagination for high-volume sellers
   - Array processing optimization for price type breakdowns

### Known Performance Issues
```sql
-- This query can be slow with many transactions
SELECT * FROM sellbill 
WHERE MONTH(sellbilldate) = Month(CURRENT_DATE()) 
AND conditions = 0;

-- Solution: Add date range instead of MONTH() function
SELECT * FROM sellbill 
WHERE sellbilldate >= ? AND sellbilldate <= ? 
AND conditions = 0;
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Commission Calculations**
**Issue**: Commission amounts don't match expected values  
**Cause**: Mixed price types or missing commission rates

**Debug**:
```sql
-- Check user commission rates
SELECT userid, employeename, awardrate, awardrategomla, awardratehalf 
FROM user WHERE userlevel = 3;

-- Verify price type distribution
SELECT pricetype, COUNT(*), SUM(sellbillaftertotalbill) 
FROM sellbill WHERE conditions = 0 GROUP BY pricetype;
```

### 2. **Missing Transaction Data**
**Issue**: Some sales don't appear in commission reports  
**Cause**: Bills without assigned sellers or cancelled bills

**Fix**:
```sql
-- Check for bills without sellers
SELECT COUNT(*) FROM sellbill WHERE sellerid = 0 OR sellerid IS NULL;

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

### 3. **Date Range Issues**
**Issue**: Reports show no data for valid date ranges  
**Cause**: Date format mismatch or timezone issues

**Fix**:
```php
// Ensure proper date format in queries
if (!empty($startDate)) $startDate .= " 00:00:00";
if (!empty($endDate)) $endDate .= " 23:59:59";
```

### 4. **Price Type Breakdown Errors**
**Issue**: Commission breakdown by price type shows incorrect totals  
**Cause**: Missing price type data or incorrect rate application

**Debug**:
```sql
-- Check price type data consistency
SELECT pricetype, COUNT(*) FROM sellbilldetail GROUP BY pricetype;

-- Verify rate application
SELECT DISTINCT pricetype, awardrate, awardrategomla, awardratehalf 
FROM sellbill s JOIN user u ON s.sellerid = u.userid;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Monthly Commission Report
```
1. Create test sales bills for current month
2. Assign different sellers with varying commission rates
3. Add some returns to test commission deduction
4. Run monthly report and verify calculations
5. Check that bills without sellers are excluded from commission
```

### Test Case 2: Price Type Commission Breakdown
```
1. Create bills with different price types (0, 1, 2)
2. Set different commission rates per price type for test sellers
3. Run price type breakdown report
4. Verify each price type shows correct commission calculation
5. Confirm totals match individual type sums
```

### Test Case 3: Date Range Filtering
```
1. Create bills across multiple months
2. Test various date range combinations
3. Verify only bills within range are included
4. Test edge cases (same start/end date, partial months)
```

### Debug Mode Enable
```php
// Add at top of showAll() function for debugging
echo "Seller ID: " . $sellerid . "<br>";
echo "Date Range: " . $startDate . " to " . $endDate . "<br>";
echo "Price Type: " . $priceType . "<br>";

// Debug commission calculations
foreach ($selldata as $bill) {
    echo "Bill: " . $bill->sellbillid . 
         " Amount: " . $bill->sellbillaftertotalbill . 
         " Rate: " . $bill->awardrate . 
         " Commission: " . ($bill->sellbillaftertotalbill * $bill->awardrate / 100) . "<br>";
}
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations  
- [returnsellbillController.php](#) - Return operations
- [userController.php](#) - User management
- [Database Schema Documentation](#) - Table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When commission calculation methods change