# Executors User Report Controller Documentation

**File**: `/controllers/executorsuserreport.php`  
**Purpose**: User-specific financial analysis and profitability reporting for sales bill execution assignments  
**Last Updated**: December 20, 2024  
**Total Functions**: 4+  
**Lines of Code**: ~164

---

## 📋 Overview

The Executors User Report Controller provides user-specific financial analysis for sales bill execution assignments. It's nearly identical to the executorsreport.php but focuses on individual user performance analysis, enabling management to track the financial performance of specific users or teams in executing sales orders.

### Key Capabilities
- User-centric profitability analysis for executed sales bills
- Individual user performance tracking and KPI analysis
- Integration of sales amounts, client payments, and execution costs per user
- Multi-dimensional financial calculations focused on user performance
- Date range filtering for user performance analysis
- Client and sales bill filtering for targeted user reporting
- Ajax-powered DataTables integration for user-specific reporting

### Primary Functions
- [x] Calculate user-specific gross profit (Sales Amount - Execution Costs)
- [x] Track user performance in client payment collection
- [x] Calculate user-specific net profit (Client Payments - Execution Costs)
- [x] Provide individual user financial analysis for executions
- [x] Support user performance benchmarking
- [x] Ajax search for clients and sales bills (user context)
- [x] DataTables integration for user performance reporting

### Related Controllers
- [executors.php](executors.md) - Execution assignment management
- [executorsreport.php](executorsreport.md) - General execution reporting
- [userController.php](userController.md) - User management
- [sellbillController.php](sellbillController.md) - Sales bill operations

---

## 🗄️ Database Tables

### Core Execution Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **executors** | Execution assignments | id, executorsclientid, executorssellid, executorsuserids, executorsdate, userid, del |
| **executorsusers** | Individual user assignments | id, executorsid, executorsuserid, today, del |

### Financial Analysis Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **sellbill** | Sales bill master data | sellbillid, sellbillclientid, sellbillaftertotalbill, sellbilldate, datestarting, conditions |
| **expenses** | Execution-related costs | id, sellbillid, expensesValue, expensesdate, conditions |
| **clientdebtchange** | Payment tracking | id, billid, clientdebtchangeamount, tablename, debtchangedate |

### User Management Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | System users | userid, employeename, conditions |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer information | clientid, clientname, conditions |

---

## 🔑 Key Functions

### 1. **Default Display** - User Report Interface
**Location**: Line 8-12  
**Purpose**: Display the user-specific executors financial report interface

**Implementation**:
```php
if (empty($do)) {  
    $smarty->display("header.html");
    $smarty->display("executorsuserreport/show.html");
    $smarty->display("footer.html");
}
```

**Features**:
- User-focused report interface
- Filter controls for user performance analysis
- Date range and entity filtering
- Export capabilities for user metrics

---

### 2. **select2client()** - Client Search for User Context
**Location**: Line 21-37  
**Purpose**: Provide Ajax-powered client search for user-specific report filtering

**Function Signature**:
```php
function select2client()
// POST Parameter: searchTerm - Client name search
```

**Implementation**:
```php
$name = $_POST['searchTerm'];

$productsData = R::getAll("SELECT clientid, clientname as name
FROM client 
WHERE conditions = 0 and clientname LIKE '%" . $name . "%'  limit 50");

foreach ($productsData as $pro) {
    $row_array['id'] = $pro['clientid'];
    $row_array['text'] = $pro['name'];
    array_push($return_arr, $row_array);
}

echo json_encode($return_arr);
```

**Features**:
- Real-time client search for user analysis
- Select2 dropdown integration
- Performance-optimized result limiting
- Active client filtering

---

### 3. **select2sellbill()** - Sales Bill Search with User Context
**Location**: Line 41-57  
**Purpose**: Search sales bills for user-specific execution analysis

**Function Signature**:
```php
function select2sellbill()
// POST Parameters: searchTerm, clientid
```

**Implementation**:
```php
$name = $_POST['searchTerm'];
$clientid = $_POST['clientid'];

$productsData = R::getAll("SELECT sellbillid, CONCAT(sellbillid,'/',datestarting) as texts
FROM sellbill 
WHERE conditions = 0 and CONCAT(sellbillid,'/',datestarting) LIKE '%" . $name . "%' 
and sellbill.sellbillclientid = $clientid limit 50");

foreach ($productsData as $pro) {
    $row_array['id'] = $pro['sellbillid'];
    $row_array['text'] = $pro['texts'];
    array_push($return_arr, $row_array);
}
```

**Features**:
- Client-filtered bill search for user context
- Bill ID and date display
- User-specific execution context
- Integration with user performance analysis

---

### 4. **showajax()** - User Performance Financial Report
**Location**: Line 61-157  
**Purpose**: Generate detailed user-specific financial analysis for execution assignments

**Function Signature**:
```php
function showajax()
// POST Parameters: fromdate, todate, data1 (client), data2 (sellbill), DataTables parameters
```

**Filter Building**:
```php
$searchQuery = " ";
if($data1 != ''){
  $searchQuery .=  " and executors.executorsclientid = ".$data1. " ";
}

if($data2 != ''){
   $searchQuery .= " and executors.executorssellid = ".$data2. " ";
}

if($start_date != '' && $end_date != ''){
   $searchQuery .='and sellbill.datestarting >= "' . $start_date . '" and sellbill.datestarting <= "' . $end_date . '" ';
}
```

**Main Query**:
```php
$rResult = R::getAll('SELECT executors.* ,clientname, expensesValue, sellbillaftertotalbill, sellbilldate, datestarting 
    FROM `executors`
    LEFT JOIN sellbill ON executors.executorssellid = sellbill.sellbillid 
    LEFT JOIN client ON executors.executorsclientid = client.clientid   
    LEFT JOIN expenses ON expenses.sellbillid = sellbill.sellbillid 
    WHERE 1 '.$searchQuery.' ');
```

**User Performance Calculations**:
```php
foreach ($rResult as $row) {
    // 1. Get client payments for this bill
    $clientdebtchangeamount = R::getCell('SELECT sum(clientdebtchangeamount) as clientdebtchangeamount 
        FROM clientdebtchange 
        WHERE billid = '. $row["executorssellid"]. ' 
        and tablename = "clientPayedDeptSellBillsController.php"');
    
    // 2. User performance metrics
    $sub_array[] = $row["id"];                    // Execution ID
    $sub_array[] = $row["clientname"];            // Client Name
    $sub_array[] = $row["executorssellid"];       // Sales Bill ID
    $sub_array[] = $row["sellbilldate"];          // Bill Date
    $sub_array[] = $row["sellbillaftertotalbill"]; // Sales Amount
    $sub_array[] = $clientdebtchangeamount;       // Client Payments (user collection performance)
    $sub_array[] = $row["expensesValue"];         // Execution Costs (user cost efficiency)
    $sub_array[] = $row["sellbillaftertotalbill"] - $clientdebtchangeamount; // Outstanding Amount
    $sub_array[] = $row["sellbillaftertotalbill"] - $row["expensesValue"];   // Gross Profit
    $sub_array[] = $clientdebtchangeamount - $row["expensesValue"];          // Net Profit
}
```

---

## 🧮 User Performance Metrics

### 1. **User Sales Performance**
```php
$userSalesAmount = $row["sellbillaftertotalbill"];
```
- Total sales value executed by user
- Measures user sales effectiveness

### 2. **User Collection Performance**
```php
$userCollections = $clientdebtchangeamount;
```
- Payments collected by user or their team
- Tracks user effectiveness in payment collection

### 3. **User Cost Efficiency**
```php
$userExecutionCosts = $row["expensesValue"];
```
- Costs incurred in user's execution assignments
- Measures user cost management skills

### 4. **User Outstanding Management**
```php
$userOutstanding = $salesAmount - $clientPayments;
```
- Uncollected amounts under user responsibility
- Indicates user's credit management effectiveness

### 5. **User Gross Profit Generation**
```php
$userGrossProfit = $salesAmount - $executionCosts;
```
- Profit generated before collection issues
- Measures user execution efficiency

### 6. **User Net Profit Realization**
```php
$userNetProfit = $clientPayments - $executionCosts;
```
- Actual profit realized by user performance
- Key indicator of user financial contribution

---

## 🔄 User Performance Analysis Workflow

### Workflow 1: Individual User Performance Assessment
```
┌─────────────────────────────────────────────────────────────┐
│           START: Assess User Performance                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Define Analysis Parameters                              │
│     - Set evaluation period (date range)                   │
│     - Select specific user (if individual assessment)      │
│     - Choose client focus (optional)                       │
│     - Define performance criteria                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Load User Execution Data                                │
│     - Query executions assigned to user                    │
│     - Include sales bills and client information           │
│     - Load execution costs and payment data                │
│     - Apply period and entity filters                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Calculate User Performance KPIs                        │
│     FOR EACH User Execution:                               │
│       │                                                     │
│       ├─→ Sales Volume: Total sales value                  │
│       ├─→ Collection Rate: Payments/Sales ratio            │
│       ├─→ Cost Efficiency: Costs/Sales ratio               │
│       ├─→ Outstanding Ratio: Outstanding/Sales             │
│       ├─→ Gross Margin: Gross profit percentage            │
│       └─→ Net Margin: Net profit percentage                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate User Performance Report                       │
│     - Display individual execution performance             │
│     - Calculate aggregated user metrics                    │
│     - Compare with benchmarks/targets                      │
│     - Provide actionable insights                          │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: User Performance Benchmarking
```
┌─────────────────────────────────────────────────────────────┐
│         START: Benchmark User Performance                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Collect User Performance Data                           │
│     - Load all user execution assignments                  │
│     - Calculate individual user KPIs                       │
│     - Aggregate performance by user                        │
│     - Apply consistent measurement period                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Calculate Benchmark Metrics                             │
│     - Average sales volume per user                        │
│     - Average collection rate across users                 │
│     - Average cost efficiency per user                     │
│     - Average profit margins by user                       │
│     - Identify top and bottom performers                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Performance Comparison Analysis                         │
│     - Rank users by each KPI                              │
│     - Identify performance gaps                           │
│     - Analyze best practices from top performers          │
│     - Identify improvement opportunities                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate Actionable Insights                           │
│     - Performance improvement recommendations              │
│     - Training needs identification                        │
│     - Resource allocation optimization                     │
│     - Performance target setting                          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description | Ajax |
|---------------|----------------|-------------|------|
| (empty) | Default display | Show user performance report interface | No |
| `do=select2client` | `select2client()` | Ajax client search for user context | Yes |
| `do=select2sellbill` | `select2sellbill()` | Ajax sales bill search for users | Yes |
| `do=showajax` | `showajax()` | DataTables user performance data provider | Yes |

### Required Parameters by Action

**Client Search** (`do=select2client`):
- `searchTerm` - Client name search string

**Sales Bill Search** (`do=select2sellbill`):
- `searchTerm` - Bill search string
- `clientid` - Client filter for user context

**User Performance Report** (`do=showajax`):
- Standard DataTables parameters (draw, start, length, search, order)
- `fromdate` - User performance analysis period start
- `todate` - User performance analysis period end
- `data1` - Client filter for user analysis
- `data2` - Sales bill filter for user focus

---

## 📊 User Performance KPIs

### Individual User Metrics
1. **Sales Volume**: Total sales value executed by user
2. **Collection Efficiency**: Percentage of sales amount collected
3. **Cost Control**: Execution costs as percentage of sales
4. **Outstanding Management**: Uncollected amounts ratio
5. **Gross Profit Generation**: Profit before collection issues
6. **Net Profit Realization**: Actual realized profit

### Comparative User Analytics
```php
// User Performance Ranking Calculations
$userSalesTotal = array_sum($userSalesData);
$userCollectionRate = ($userCollections / $userSalesTotal) * 100;
$userCostEfficiency = ($userCosts / $userSalesTotal) * 100;
$userProfitMargin = (($userCollections - $userCosts) / $userCollections) * 100;

// Benchmarking Metrics
$avgSalesPerUser = $totalSales / $activeUsers;
$avgCollectionRate = $totalCollections / $totalSales * 100;
$avgCostRate = $totalCosts / $totalSales * 100;

// Performance Variance
$userPerformanceScore = (
    ($userCollectionRate / $avgCollectionRate) +
    ($avgCostRate / $userCostEfficiency) +
    ($userSalesTotal / $avgSalesPerUser)
) / 3;
```

### User Performance Categories
- **Top Performers**: Above average in sales, collection, and cost control
- **Sales Leaders**: High sales volume but may need collection improvement
- **Efficiency Experts**: Best cost control and collection rates
- **Development Needed**: Below average in multiple metrics
- **Balanced Performers**: Consistent average performance across metrics

---

## 🔒 Security & Permissions

### User-Specific Access Control
```php
// Potential user filtering (if implemented)
if ($_SESSION['role'] == 'user') {
    $searchQuery .= ' AND executors.userid = ' . $_SESSION['userid'];
}
```

### Input Validation
```php
$searchTerm = $_POST['searchTerm'];
$clientid = $_POST['clientid'];
$start_date = $_POST['fromdate'];
$end_date = $_POST['todate'];
```

**Security Features**:
- Session-based access control
- RedBean ORM SQL injection protection
- Input parameter validation
- User context preservation
- Read-only operations (no data modification)

---

## 🧪 Testing Scenarios

### Test Case 1: User Performance Calculation
```
1. Create execution assignments for specific user
2. Add known sales amounts, costs, and payments
3. Verify user performance metrics calculated correctly
4. Check collection rate = payments / sales * 100
5. Validate cost efficiency = costs / sales * 100
6. Confirm profit calculations accurate
```

### Test Case 2: User Comparison Analysis
```
1. Create executions for multiple users
2. Generate performance report for all users
3. Verify relative performance calculations
4. Check user ranking accuracy
5. Validate benchmark calculations
```

### Test Case 3: Date Range Performance
```
1. Generate user performance report for specific period
2. Test with different date ranges
3. Verify date filtering affects only relevant executions
4. Check period-based performance trends
5. Validate cumulative metrics
```

### Test Case 4: User-Specific Filtering
```
1. Test client search in user context
2. Test sales bill search for user assignments
3. Verify filtering maintains user focus
4. Check DataTables search functionality
5. Validate export capabilities for user data
```

---

## 🔍 Differences from executorsreport.php

While the code is nearly identical to executorsreport.php, the key differences are:

### 1. **Template Focus**
- Uses `executorsuserreport/show.html` instead of `executorsreport/show.html`
- Template likely includes user-specific UI elements
- May include user comparison features

### 2. **Intended Usage**
- **executorsreport.php**: General financial analysis of all executions
- **executorsuserreport.php**: User-centric performance analysis

### 3. **Reporting Context**
- General report focuses on overall profitability
- User report focuses on individual performance metrics

### 4. **Analysis Perspective**
- General: "How profitable are our executions?"
- User: "How well are our users performing in executions?"

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [executors.md](executors.md) - Execution assignment management
- [executorsreport.md](executorsreport.md) - General execution reporting
- [userController.md](userController.md) - User management
- [sellbillController.md](sellbillController.md) - Sales bill operations

---

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