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

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

Related Controllers

• executors.php - Execution assignment management
• executorsreport.php - General execution reporting
• userController.php - User management
• sellbillController.php - 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:

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:

function select2client()
// POST Parameter: searchTerm - Client name search

Implementation:

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

function select2sellbill()
// POST Parameters: searchTerm, clientid

Implementation:

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

function showajax()
// POST Parameters: fromdate, todate, data1 (client), data2 (sellbill), DataTables parameters

Filter Building:

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

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

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

$userSalesAmount = $row["sellbillaftertotalbill"];

• Total sales value executed by user
• Measures user sales effectiveness

2. User Collection Performance

$userCollections = $clientdebtchangeamount;

• Payments collected by user or their team
• Tracks user effectiveness in payment collection

3. User Cost Efficiency

$userExecutionCosts = $row["expensesValue"];

• Costs incurred in user's execution assignments
• Measures user cost management skills

4. User Outstanding Management

$userOutstanding = $salesAmount - $clientPayments;

• Uncollected amounts under user responsibility
• Indicates user's credit management effectiveness

5. User Gross Profit Generation

$userGrossProfit = $salesAmount - $executionCosts;

• Profit generated before collection issues
• Measures user execution efficiency

6. User Net Profit Realization

$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

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Define Analysis Parameters

- Set evaluation period (date range)

- Select specific user (if individual assessment)

- Choose client focus (optional)

- Define performance criteria

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

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

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Calculate 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 │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

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

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Collect User Performance Data

- Load all user execution assignments

- Calculate individual user KPIs

- Aggregate performance by user

- Apply consistent measurement period

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

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

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Performance Comparison Analysis

- Rank users by each KPI

- Identify performance gaps

- Analyze best practices from top performers

- Identify improvement opportunities

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

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

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

// Potential user filtering (if implemented)
if ($_SESSION['role'] == 'user') {
    $searchQuery .= ' AND executors.userid = ' . $_SESSION['userid'];
}

Input Validation

$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 - PHP 8.2 migration guide
• executors.md - Execution assignment management
• executorsreport.md - General execution reporting
• userController.md - User management
• sellbillController.md - Sales bill operations

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Executorsuserreport Documentation

Executors User Report Controller Documentation

📋 Overview

Key Capabilities

Primary Functions

Related Controllers

🗄️ Database Tables

Core Execution Tables

🔑 Key Functions

1. Default Display - User Report Interface

2. select2client() - Client Search for User Context

3. select2sellbill() - Sales Bill Search with User Context

4. showajax() - User Performance Financial Report

🧮 User Performance Metrics

1. User Sales Performance

2. User Collection Performance

3. User Cost Efficiency

4. User Outstanding Management

5. User Gross Profit Generation

6. User Net Profit Realization

🔄 User Performance Analysis Workflow

Workflow 1: Individual User Performance Assessment

Workflow 2: User Performance Benchmarking

🌐 URL Routes & Actions

📊 User Performance KPIs

Individual User Metrics

Comparative User Analytics

User Performance Categories

🔒 Security & Permissions

User-Specific Access Control

Input Validation

🧪 Testing Scenarios

Test Case 1: User Performance Calculation

Test Case 2: User Comparison Analysis

Test Case 3: Date Range Performance

Test Case 4: User-Specific Filtering

🔍 Differences from executorsreport.php

1. Template Focus

2. Intended Usage

3. Reporting Context

4. Analysis Perspective

📚 Related Documentation