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

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

Related Controllers

• • sellbillController.php - Sales operations
• • returnsellbillController.php - Sales returns
• • sellbillandruternController.php - Combined sales/returns
• • userController.php - User management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 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:

// 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:

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:

// 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:

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:

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:

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:

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:

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:

// 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:

$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:

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

---

Workflow 2: Price Type Commission Breakdown

---

🌐 URL Routes & Actions

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

// 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

// For multiple sellers
$averageRate = $totalCommissionPercentage / $billCount;

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

Net Commission After Returns

// Sales commission
$totalCommission += $saleAmount * ($commissionRate / 100);

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

// Net result
$netCommission = $totalCommission;

Bills With Seller Analysis

if ($bill->sellerid > 0) {
    $billsWithSeller++;
    $totalBillsWithSeller += $bill->amount;
    $totalCommissionFromSellers += $commission;
} else {
    // Bill without assigned seller - no commission
}

---

🔒 Security & Permissions

User Level Restrictions

// 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

-- 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:

-- 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:

-- 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:

// 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:

-- 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

// 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 - PHP 8.2 migration guide
• • 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