Client Profits Controller Documentation

File: /controllers/clientProfitsController.php

Purpose: Calculates and displays customer profit analysis across sales, returns, and combined operations with multiple pricing methodologies

Last Updated: December 20, 2024

Total Functions: 2 main actions + 1 utility function

Lines of Code: ~567

---

📋 Overview

The Client Profits Controller is a sophisticated financial analysis tool that calculates customer profitability using various buy price calculation methods. It processes sales bills, return bills, and combined sell-and-return operations to provide comprehensive profit/loss analysis per customer.

Primary Functions

✅ Multi-methodology profit calculation (8 different buy price methods)
✅ Customer-specific profit analysis with date filtering
✅ Sales vs returns profit comparison
✅ Combined bill operations analysis (sell-and-return bills)
✅ Discount impact calculation on profitability
✅ Product unit conversion and pricing analysis
✅ Real-time profit/loss reporting with detailed breakdowns
✅ Net profit calculation (sales profit - return losses)

Related Controllers

• sellbillController.php - Sales transaction processing
• returnsellbillController.php - Return transaction processing
• sellbillandruternController.php - Combined sell-and-return operations
• clientReportsController.php - Customer reporting
• productController.php - Product and pricing management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
sellbill	Sales bills master	sellbillid, sellbillclientid, sellbilldate, sellbilltotalbill, sellbillaftertotalbill, sellbilldiscount, sellbilldiscounttype
sellbilldetail	Sales line items	sellbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid
returnsellbill	Return bills master	returnsellbillid, returnsellbillclientid, returnsellbilldate, returnsellbilltotalbill, returnsellbillaftertotalbill
returnsellbilldetail	Return line items	returnsellbilldetailid, returnsellbillid, returnsellbilldetailproductid, returnsellbilldetailquantity, returnsellbilldetailtotalprice
sellbillandrutern	Combined bills	sellbillid, sellbillclientid, sellbilldate, sellbillprice, returnsellbillprice, sellbilldiscount, sellbilldiscounttype
sellandruternbilldetail	Combined bill details	sellandruternbilldetailid, sellbillid, sellbilldetailproductid, sellbilldetailquantity, sellbilldetailtotalprice, selltype

### Product & Pricing Tables

Table Name	Purpose	Key Columns
productunit	Product-unit combinations	productunitid, productid, unitid, productnumber, overAllAveragePrice
product	Products master	productid, productname, productcatid
unit	Units of measure	unitid, unitname

### Reference Tables

Table Name	Purpose	Key Columns
client	Customer master	clientid, clientname, clientdebt
programsettings	System configuration	programsettingsid, currancy
youtubelink	Tutorial links	youtubelinkid, title, url

---

🔑 Key Functions

1. show / Default Action - Customer Profit Analysis

Location: Lines 154-538

Purpose: Calculate comprehensive customer profitability using configurable pricing methods

Function Signature:

// Parameters: clientId, from, to, buyPriceType
$clientId = $_POST['clientId'];
$from = $_POST["from"];
$to = $_POST["to"];
$buyPriceType = $_POST["buyPriceType"]; // Pricing methodology

Buy Price Methodologies:

1. "first" - Original buy price from product

2. "last" - Last recorded buy price

3. "mean" - Average buy price over time

4. "last_discount" - Last buy price after discount

5. "mean_discount" - Average buy price after discount

6. "generalPrice" - Overall average price

7. "tax" - Last buy price including tax

8. "mean_tax" - Average buy price including tax

Process Flow:

1. Build dynamic query strings for different bill types

2. Process sales bills with profit calculations

3. Process return bills with loss calculations

4. Process combined sell-and-return bills

5. Apply discount calculations to adjust profits

6. Calculate net profit (sales profit - return losses)

---

2. Sales Bill Profit Calculation

Location: Lines 266-334

Purpose: Calculate profit for each sales bill using selected pricing method

Profit Calculation Logic:

foreach ($SellBillDetail as $mySellBillDetail) {
    // Determine buy price based on selected method
    switch ($buyPriceType) {
        case "first": $buyprice = $mySellBillDetail->buyprice; break;
        case "last": $buyprice = $mySellBillDetail->lastbuyprice; break;
        case "mean": $buyprice = $mySellBillDetail->meanbuyprice; break;
        // ... other methods
    }
    
    // Calculate total buy cost including unit conversion
    $myBuyPrice = $buyprice * ($mySellBillDetail->sellbilldetailquantity * $myproductNumber);
    
    // Calculate profit for this line item
    $thisProfits += $mySellBillDetail->sellbilldetailtotalprice - $myBuyPrice;
}

// Apply bill-level discount
if ($mysellBill->sellbilldiscounttype == 1) {
    $discount = $mysellBill->sellbilldiscount; // Fixed amount
} else {
    $discount = ($mysellBill->sellbilldiscount / 100) * $mysellBill->sellbilltotalbill; // Percentage
}
$thisProfits -= $discount;

---

3. Return Bill Loss Calculation

Location: Lines 337-413

Purpose: Calculate losses from return transactions

Key Differences from Sales:

• Returns reduce overall profitability
• Same pricing methodology options available
• Discount calculations applied to minimize losses
• Separate tracking for return-specific metrics

---

4. Combined Bill Processing

Location: Lines 416-522

Purpose: Handle bills that contain both sales and return line items

Dual Processing Logic:

foreach ($SellBillAndRuternDetail as $mySellBillAndRuternDetail) {
    if ($mySellBillAndRuternDetail->selltype == 0) { // Sale
        $thisProfitsSell += $sellPrice - $buyPrice;
    } elseif ($mySellBillAndRuternDetail->selltype == 1) { // Return
        $thisProfitsReturn += $sellPrice - $buyPrice;
    }
}

---

5. loadProductUnitWithProductAndUnit() - Utility Function

Location: Lines 557-566

Purpose: Load product unit data with pricing information

Function Signature:

function loadProductUnitWithProductAndUnit($productId, $unitId) {
    global $myProductunitEx;
    return $myProductunitEx->queryWithProductIdAndUnitId($productId, $unitId);
}

---

🔄 Workflows

Workflow 1: Customer Profit Analysis

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

START: Select Customer & Date Range

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

▼

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

1Configure Analysis Parameters

- Select customer (or all customers)

- Set date range for analysis

- Choose buy price calculation method

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

▼

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

2Build Dynamic Query Strings

- Sales bills query with filters

- Return bills query with filters

- Combined bills query with filters

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

▼

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

3Process Sales Bills

FOR EACH sales bill:

│

→ Load bill details (line items)

│

├─→ FOR EACH line item:

│ ├─ Apply selected buy price method

│ ├─ Calculate unit conversions

│ ├─ Calculate line profit

│ │ └─ Accumulate bill total │

│

→ Apply bill-level discount

│

│ └─→ Add to sales profit total │

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

▼

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

4Process Return Bills

FOR EACH return bill:

│

→ Load return details (line items)

│

→ Calculate return losses

│

→ Apply discount adjustments

│

│ └─→ Add to return loss total │

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

▼

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

5Process Combined Bills

FOR EACH combined bill:

│

→ Load combined details

│

→ Separate sales vs return line items

│

→ Calculate profits/losses per type

│

│ └─→ Apply discount allocation │

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

▼

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

6Calculate Final Metrics

- Total sales profit

- Total return losses

- Net profit (sales - returns)

- Customer current debt balance

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

▼

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

7Display Profit Analysis Report

- Show detailed bill-by-bill breakdown

- Include pricing method used

- Display net profitability summary

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty) or `do=show`	Default action	Customer profit analysis
`do=sucess`	success page	Success confirmation
`do=error`	error page	Error display

### Required Parameters

Customer Profit Analysis (default):

• clientId - Customer ID (-1 for all customers)
• from - Start date (YYYY-MM-DD, defaults to today if empty)
• to - End date (YYYY-MM-DD, defaults to today if empty)
• buyPriceType - Pricing method (required, see methodologies above)

---

🧮 Calculation Methods

Profit Calculation per Line Item

// Base calculation
$myBuyPrice = $buyprice * ($quantity * $productunit_conversion_factor);
$lineProfit = $sellPrice - $myBuyPrice;

// Apply to bill total
$billProfit += $lineProfit;

Discount Application

// Fixed amount discount
if ($discounttype == 1) {
    $discount = $discountAmount;
}
// Percentage discount  
else {
    $discount = ($discountPercent / 100) * $billTotal;
}

// Reduce profit by discount amount
$finalProfit = $billProfit - $discount;

Net Profitability

$netProfit = $totalSalesProfit - $totalReturnLosses;

Buy Price Method Selection

switch ($buyPriceType) {
    case "first": return $detail->buyprice;
    case "last": return $detail->lastbuyprice;
    case "mean": return $detail->meanbuyprice;
    case "last_discount": return $detail->lastbuyprice_withDiscount;
    case "mean_discount": return $detail->meanbuyprice_withDiscount;
    case "generalPrice": return $productunit->overAllAveragePrice;
    case "tax": return $detail->lastbuyprice_withTax;
    case "mean_tax": return $detail->meanbuyprice_withTax;
}

---

🔒 Security & Permissions

Authentication

• All actions require authentication via include_once("../public/authentication.php")
• User group permissions loaded for access control

Data Validation

• Date parameters validated and formatted
• Customer ID validation
• Buy price type validation against allowed methods

---

📊 Performance Considerations

Optimization Strategies

1. Efficient Query Building: Dynamic WHERE clause construction to avoid unnecessary data loading

2. Batch Processing: Process all bills of same type together

3. Memory Management: Process line items within bill loops to minimize memory usage

Required Indexes:

• sellbill(sellbillclientid, sellbilldate)
• returnsellbill(returnsellbillclientid, returnsellbilldate)
• sellbillandrutern(sellbillclientid, sellbilldate)
• sellbilldetail(sellbillid, sellbilldetailproductid)
• productunit(productid, unitid)

Performance Considerations for Large Datasets

// Avoid N+1 queries by preloading product unit data
$productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);

---

🐛 Common Issues & Troubleshooting

1. Incorrect Profit Calculations

Issue: Profit amounts don't match expected values

Cause: Wrong buy price method selected or missing product unit data

Debug Steps:

// Verify product unit data
$productunitData = loadProductUnitWithProductAndUnit($productId, $unitId);
echo "Product Number: " . $productunitData->productnumber;
echo "Selected Buy Price: " . $buyprice;

// Check discount calculations
echo "Discount Type: " . $sellBill->sellbilldiscounttype;
echo "Discount Amount: " . $discount;

2. Missing Bills in Analysis

Issue: Some bills not appearing in profit analysis

Cause: Date range filtering or query string construction issues

Fix: Verify date formatting and query string building:

echo "Query String: " . $queryString;
// Ensure dates include time components
// Verify WHERE clause construction

3. Unit Conversion Errors

Issue: Incorrect profit calculations due to unit conversion

Cause: Missing or incorrect productnumber in productunit table

Solution: Verify product unit relationships:

SELECT productunitid, productnumber, overAllAveragePrice 
FROM productunit 
WHERE productid = [ID] AND unitid = [UNIT_ID];

---

🧪 Testing Scenarios

Test Case 1: Single Customer Profit Analysis

1. Create customer with known transactions
2. Set specific date range covering transactions
3. Run analysis with "last" buy price method
4. Verify calculations manually
5. Compare with different buy price methods

Test Case 2: Discount Impact Testing

1. Create sales bill with percentage discount
2. Create identical bill with fixed amount discount
3. Compare profit calculations
4. Verify discount is properly subtracted from profits

Test Case 3: Combined Bill Analysis

1. Create sell-and-return bill with mixed line items
2. Verify profits calculated separately for sales vs returns
3. Check total matches individual bill calculations

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• sellbillController.md - Sales bill management
• clientReportsController.md - Customer reporting

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

ClientProfits Documentation

Client Profits Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. show / Default Action - Customer Profit Analysis

2. Sales Bill Profit Calculation

3. Return Bill Loss Calculation

4. Combined Bill Processing

5. loadProductUnitWithProductAndUnit() - Utility Function

🔄 Workflows

Workflow 1: Customer Profit Analysis

🌐 URL Routes & Actions

🧮 Calculation Methods

Profit Calculation per Line Item

Discount Application

Net Profitability

Buy Price Method Selection

🔒 Security & Permissions

Authentication

Data Validation

📊 Performance Considerations

Optimization Strategies

Performance Considerations for Large Datasets

🐛 Common Issues & Troubleshooting

1. Incorrect Profit Calculations

2. Missing Bills in Analysis

3. Unit Conversion Errors

🧪 Testing Scenarios

Test Case 1: Single Customer Profit Analysis

Test Case 2: Discount Impact Testing

Test Case 3: Combined Bill Analysis

📚 Related Documentation