Invoice Earnings Report Controller Documentation

File: /controllers/invoicesearningsreportController.php

Purpose: Generates detailed earnings reports with profit analysis using multiple pricing methods for accurate cost calculation

Last Updated: December 20, 2024

Total Functions: 10+

Lines of Code: ~579

---

📋 Overview

The Invoice Earnings Report Controller is a specialized financial reporting module that generates detailed earnings and profit analysis reports. It provides sophisticated profit calculations with:

• • Multiple cost pricing method support (first, last, mean, with/without discounts and taxes)
• • Comprehensive sales revenue analysis from multiple transaction types
• • Cost of goods sold calculations with various pricing strategies
• • VAT integration for accurate tax-inclusive reporting
• • Sales return processing with proper cost adjustments
• • Service transaction analysis (optical/restaurant bills)
• • Combined bill transaction handling (sell and return in one bill)
• • Real-time profit margin calculations

Primary Functions

• ✅ Generate comprehensive earnings reports
• ✅ Multiple buy price calculation methods for accurate COGS
• ✅ Sales revenue analysis from all transaction types
• ✅ Profit margin calculation with various cost bases
• ✅ VAT and tax-inclusive reporting
• ✅ Service transaction earnings analysis
• ✅ Combined transaction handling
• ✅ Return transaction cost adjustments

Related Controllers

• • incomeStatmentForPeriod.php - Comprehensive income statements
• • sellbillController.php - Sales transaction data
• • buyBillController.php - Purchase cost data
• • bills.md - Service transaction management

---

🗄️ Database Tables

Sales Revenue Tables

🔑 Key Functions

1. show() / Default Action - Generate Earnings Report

Location: Line 189

Purpose: Main function that orchestrates earnings report generation

Process Flow:

1. Date Range Processing:

   $startDate = $_REQUEST['from'];
   $endDate = $_REQUEST['to'];
   $buyPriceType = 'last'; // Fixed to last purchase price
   
   if (empty($startDate) && empty($endDate)) {
       $startDate = $endDate = date('Y-m-d');
   }
   ```

2. **Time Zone Adjustment**:
   ```php
   if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
       $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
       $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($endDate)));
       $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour', strtotime($startDate)));
   }
   ```

3. **Earnings Calculation Workflow**:
   ```php
   getallsellbill($startDate, $endDate, $buyPriceType);
   getallsellandreturnbill($startDate, $endDate, $buyPriceType);
   getallreturnsellbill($startDate, $endDate, $buyPriceType);
   ```

---

### 2. **getallsellbill()** - Sales Bill Earnings Analysis
**Location**: Line 238  
**Purpose**: Calculate earnings from regular sales bills and service bills

**Process Flow**:
1. **Regular Sales Processing**:
   ```php
   $allsellbilldata = $mySellbillEx->queryAllforrateall($startDate, $endDate);
   
   foreach ($allsellbilldata as $myallsellbilldata) {
       $totalpayprice = 0;
       $Sellbilldetaildata = $mySellbilldetailEx->queryWithSellBillIdforproft($myallsellbilldata->sellbillid);
       
       foreach ($Sellbilldetaildata as $mySellbilldetaildata) {
           $productNumber = (float) $mySellbilldetaildata->productnumber;
           $quantity = $mySellbilldetaildata->sellbilldetailquantity * $productNumber;
           
           switch ($buyPriceType) {
               case "first": $buyprice = (float) $mySellbilldetaildata->buyprice; break;
               case "last": $buyprice = (float) $mySellbilldetaildata->lastbuyprice; break;
               case "mean": $buyprice = (float) $mySellbilldetaildata->meanbuyprice; break;
               case "last_discount": $buyprice = (float) $mySellbilldetaildata->lastbuyprice_withDiscount; break;
               case "mean_discount": $buyprice = (float) $mySellbilldetaildata->meanbuyprice_withDiscount; break;
           }
       }
   }
   ```

2. **Discount Processing**:
   ```php
   $buydiscount = $mySellbilldetaildata->buydiscount;
   $buydiscounttype = $mySellbilldetaildata->buydiscounttype;
   
   // No discount
   if ($buydiscounttype == -1) {
       $totalpayprice += ($quantity * $buyprice);
   }
   // Percentage discount
   elseif ($buydiscounttype == 1) {
       $pay = $buyprice - (($buydiscount / 100) * $buyprice);
       $totalpayprice += ($quantity * $pay);
   }
   // Fixed amount discount
   else {
       $totalpayprice += ($quantity * ($buyprice - $buydiscount));
   }
   ```

3. **VAT Integration**:
   ```php
   // Include VAT in price calculation
   $totalpayprice = $totalpayprice * $vatValue;
   $myallsellbilldata->tax = $totalpayprice;
   $totalproft += $totalpayprice;
   ```

4. **Service Bills Processing**:
   ```php
   $allsellOpticdata = $billsEX->queryAllforrateall($startDate, $endDate);
   foreach ($allsellOpticdata as $myallsellbilldata) {
       $Sellbilldetaildata = $billsProductsEX->queryWithSellBillIdforproft($myallsellbilldata->id);
       foreach ($Sellbilldetaildata as $mySellbilldetaildata) {
           $buyprice = $mySellbilldetaildata->productBuyPrice;
           $quantity = $mySellbilldetaildata->productno;
           // Apply same discount logic as regular sales
       }
   }
   ```

---

### 3. **getallsellandreturnbill()** - Combined Bill Analysis
**Location**: Line 351  
**Purpose**: Process bills that contain both sales and returns in one transaction

**Process Flow**:
1. **Combined Bill Processing**:
   ```php
   $allsellandreturnsellbilldata = $mySellbillandruternEx->queryAllforrateall($startDate, $endDate);
   
   foreach ($allsellandreturnsellbilldata as $myallsellbilldata) {
       $totalpayprice = 0;
       $totalreturnpayprice = 0;
       
       $Sellbilldetaildata = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellTypeforproft($myallsellbilldata->sellbillid);
   }
   ```

2. **Transaction Type Classification**:
   ```php
   foreach ($Sellbilldetaildata as $mySellbilldetaildata) {
       $selltype = $mySellbilldetaildata->selltype;
       
       if ($selltype == 0) {
           // Sales portion - add to revenue
           $totalpayprice += ($quantity * $processedPrice);
       } elseif ($selltype == 1) {
           // Return portion - subtract from revenue
           $totalreturnpayprice += ($quantity * $processedPrice);
       }
   }
   ```

3. **Net Calculation**:
   ```php
   // Include VAT in both sales and returns
   $totalpayprice = $totalpayprice * $vatValue;
   $totalreturnpayprice = $totalreturnpayprice * $vatValue;
   
   // Calculate net sales
   $myallsellbilldata->sellQuantity = $totalpayprice - $totalreturnpayprice;
   $totalproft += $myallsellbilldata->sellQuantity;
   ```

---

### 4. **getallreturnsellbill()** - Return Bill Analysis
**Location**: Line 465  
**Purpose**: Process pure return bills and service returns

**Process Flow**:
1. **Return Bill Processing**:
   ```php
   $allreturnsellbilldata = $myReturnsellbillEx->queryAllforrateall($startDate, $endDate);
   
   foreach ($allreturnsellbilldata as $myallsellbilldata) {
       $Sellbilldetaildata = $myReturnsellbilldetailEx->queryWithReturnsellbillIdforproft($myallsellbilldata->returnsellbillid);
       
       foreach ($Sellbilldetaildata as $mySellbilldetaildata) {
           $quantity = $mySellbilldetaildata->returnsellbilldetailquantity * $productNumber;
           // Apply same pricing method as sales
       }
   }
   ```

2. **Service Return Processing**:
   ```php
   $allreturnOpticdata = $billsReturnEX->queryAllforrateall($startDate, $endDate);
   
   foreach ($allreturnOpticdata as $myallsellbilldata) {
       $Sellbilldetaildata = $billsReturnProductsEX->queryWithReturnsellbillIdforproft($myallsellbilldata->id);
       
       foreach ($Sellbilldetaildata as $mySellbilldetaildata) {
           // Simplified calculation for service returns
           $totalpayprice = $mySellbilldetaildata->productBuyPrice * $mySellbilldetaildata->productno;
       }
       
       // Include VAT in return cost
       $totalpayprice = $totalpayprice * $vatValue;
       $myallsellbilldata->tax = $totalpayprice;
   }
   ```

---

## 🔄 Workflows

### Workflow 1: Comprehensive Earnings Report Generation

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

│ START: Earnings Report Request │

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

│

▼

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

│ 1. Date Range and Configuration Setup │

│ - Parse from/to dates with defaults │

│ - Apply timezone adjustments from settings │

│ - Set buy price type (fixed to "last") │

│ - Load VAT configuration │

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

│

▼

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

│ 2. Regular Sales Bill Analysis │

│ A. Load Sales Bills for Date Range │

│ B. For Each Sales Bill: │

│ ├─ Load line item details │

│ ├─ Calculate unit conversions │

│ ├─ Apply selected pricing method │

│ │ ├─ First purchase price │

│ │ ├─ Last purchase price │

│ │ ├─ Mean purchase price │

│ │ ├─ Discounted prices │

│ │ └─ Tax-inclusive prices │

│ ├─ Process discount calculations │

│ │ ├─ No discount (-1) │

│ │ ├─ Percentage discount (1) │

│ │ └─ Fixed amount discount (0) │

│ └─ Apply VAT multiplier │

│ C. Include Service/Optical Bills │

│ └─ Process with same logic as regular sales │

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

│

▼

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

│ 3. Combined Bill Analysis (Sell + Return) │

│ A. Load Combined Bills for Date Range │

│ B. For Each Combined Bill: │

│ ├─ Load line items with sell type classification │

│ ├─ Separate sales items (selltype = 0) │

│ ├─ Separate return items (selltype = 1) │

│ ├─ Calculate costs using same pricing method │

│ ├─ Apply discount and VAT to both sides │

│ └─ Calculate net amount (sales - returns) │

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

│

▼

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

│ 4. Return Bill Analysis │

│ A. Load Pure Return Bills │

│ B. For Each Return Bill: │

│ ├─ Load return line item details │

│ ├─ Calculate return quantities │

│ ├─ Apply same pricing method as sales │

│ ├─ Process discount calculations │

│ └─ Apply VAT to return costs │

│ C. Include Service Return Bills │

│ └─ Process with simplified calculation logic │

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

│

▼

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

│ 5. Profit Calculation and Template Assignment │

│ - Aggregate total profits from all bill types │

│ - Assign processed data arrays to template │

│ - Include individual bill profit analysis │

│ - Display comprehensive earnings report │

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

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=show` or `do=` (empty) | Default action | Generate earnings report |
| `do=sucess` | Success page | Display success message |
| `do=error` | Error page | Display error message |

### Required Parameters
**Generate Earnings Report** (`do=show`):
- `from` - Start date (YYYY-MM-DD)
- `to` - End date (YYYY-MM-DD)

### URL Examples

Basic earnings report

invoicesearningsreportController.php?from=2024-01-01&to=2024-01-31

Current month report

invoicesearningsreportController.php?from=2024-01-01&to=2024-01-31

---

## 🧮 Calculation Methods

### Buy Price Method Selection

// Fixed to "last" pricing method

switch ($buyPriceType) {

case "first":

$buyprice = (float) $mySellbilldetaildata->buyprice;

break;

case "last":

$buyprice = (float) $mySellbilldetaildata->lastbuyprice;

break;

case "mean":

$buyprice = (float) $mySellbilldetaildata->meanbuyprice;

break;

case "last_discount":

$buyprice = (float) $mySellbilldetaildata->lastbuyprice_withDiscount;

break;

case "mean_discount":

$buyprice = (float) $mySellbilldetaildata->meanbuyprice_withDiscount;

break;

default:

$buyprice = (float) $mySellbilldetaildata->meanbuyprice;

break;

}

### Unit Conversion Processing

$productNumber = (float) $mySellbilldetaildata->productnumber;

$quantity = $mySellbilldetaildata->sellbilldetailquantity * $productNumber;

$productBuyPrice = $buyprice * $productNumber;

$totalCost = $quantity * $productBuyPrice;

### Discount Application Logic

$buydiscount = $mySellbilldetaildata->buydiscount;

$buydiscounttype = $mySellbilldetaildata->buydiscounttype;

// No discount applied

if ($buydiscounttype == -1) {

$totalpayprice += ($quantity * $buyprice);

}

// Percentage discount

elseif ($buydiscounttype == 1) {

$pay = $buyprice - (($buydiscount / 100) * $buyprice);

$totalpayprice += ($quantity * $pay);

}

// Fixed amount discount

else {

$totalpayprice += ($quantity * ($buyprice - $buydiscount));

}

### VAT Integration

// VAT calculation from system settings

$vatValue = 1 + ($programsettingsdata->vatValue / 100);

// Apply VAT to calculated cost

$totalpayprice = $totalpayprice * $vatValue;

### Combined Bill Net Calculation

// Separate sales and returns

if ($selltype == 0) {

$totalpayprice += ($quantity * $processedPrice);

} elseif ($selltype == 1) {

$totalreturnpayprice += ($quantity * $processedPrice);

}

// Apply VAT to both sides

$totalpayprice = $totalpayprice * $vatValue;

$totalreturnpayprice = $totalreturnpayprice * $vatValue;

// Net sales amount

$netSales = $totalpayprice - $totalreturnpayprice;

---

## 🔒 Security & Permissions

### Authentication Check

include_once("../public/authentication.php");

### Input Validation
- Date format validation for start/end dates
- Fixed pricing method ('last') reduces input validation needs
- SQL injection prevention via DAO layer
- Numeric validation for calculations

### Data Access Control
- All queries filtered by date range
- Condition-based filtering (conditions = 0) for active records
- User session validation for access rights

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Critical Indexes Required**:
   ```sql
   CREATE INDEX idx_sellbill_date_conditions ON sellbill(sellbilldate, conditions);
   CREATE INDEX idx_sellbilldetail_billid ON sellbilldetail(sellbillid);
   CREATE INDEX idx_sellbillandrutern_date ON sellbillandrutern(sellbilldate);
   CREATE INDEX idx_sellandruterndetail_billid ON sellandruternbilldetail(sellbillid);
   CREATE INDEX idx_returnsellbill_date ON returnsellbill(returnsellbillsysdate, conditions);
   CREATE INDEX idx_bills_date ON bills(billdate);
   CREATE INDEX idx_billsreturn_date ON billsreturn(billdate);
   ```

2. **Query Optimization**:
   - Use of specific column selection in extended DAOs
   - Efficient date range filtering
   - Minimal JOIN operations where possible

3. **Memory Management**:
   - Processing large datasets sequentially
   - Efficient array handling for calculations
   - Proper variable cleanup between bill processing

### Performance Monitoring

// Add performance monitoring

$start_time = microtime(true);

$memory_start = memory_get_usage();

getallsellbill($startDate, $endDate, $buyPriceType);

$end_time = microtime(true);

$memory_end = memory_get_usage();

echo "Sales processing: " . ($end_time - $start_time) . " seconds
";

echo "Memory used: " . (($memory_end - $memory_start) / 1024 / 1024) . " MB
";

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Profit Calculations**
**Issue**: Profit figures don't match expected values  
**Cause**: Wrong pricing method application or VAT calculation errors

**Debug**:

// Add detailed calculation debugging

echo "Buy Price Type: $buyPriceType
";

echo "VAT Value: $vatValue
";

echo "Quantity: $quantity
";

echo "Unit Price: $buyprice
";

echo "Discount Type: $buydiscounttype
";

echo "Discount Amount: $buydiscount
";

echo "Final Cost: $totalpayprice
";

### 2. **VAT Integration Issues**
**Issue**: VAT calculations appearing incorrect  
**Cause**: Improper VAT configuration or double application

**Debug**:

$programsettingsdata = $ProgramsettingDAO->load(1);

echo "VAT Setting: " . $programsettingsdata->vatValue . "%
";

echo "VAT Multiplier: $vatValue
";

echo "Cost before VAT: $costBeforeVAT
";

echo "Cost after VAT: $costAfterVAT
";

### 3. **Missing Transaction Data**
**Issue**: Some bills not appearing in report  
**Cause**: Date range issues or condition filtering

**Debug**:

-- Check data availability

SELECT COUNT(*) as total_bills,

MIN(sellbilldate) as earliest,

MAX(sellbilldate) as latest

FROM sellbill

WHERE conditions = 0

AND sellbilldate BETWEEN '2024-01-01' AND '2024-01-31';

### 4. **Combined Bill Processing Errors**
**Issue**: Combined bills showing incorrect net amounts  
**Cause**: Sell type classification errors or return processing issues

**Debug**:

foreach ($Sellbilldetaildata as $detail) {

echo "Bill ID: " . $detail->sellbillid . "
";

echo "Sell Type: " . $detail->selltype . " (0=sale, 1=return)
";

echo "Quantity: " . $detail->sellbilldetailquantity . "
";

echo "Amount: " . $detail->sellbilldetailtotalprice . "

";

}

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Earnings Calculation

1. Set date range with known sales transactions

2. Verify pricing method application (last price)

3. Check VAT calculation accuracy

4. Compare results with manual calculation

### Test Case 2: Discount Processing

1. Test bills with no discounts (type -1)

2. Test percentage discounts (type 1)

3. Test fixed amount discounts (type 0)

4. Verify correct application in each case

### Test Case 3: Combined Bill Analysis

1. Process bills with both sales and returns

2. Verify sell type classification (0 vs 1)

3. Check net amount calculations

4. Compare with individual transaction totals

### Test Case 4: Service Transaction Processing

1. Include optical/service bills in date range

2. Verify service bill cost calculations

3. Check service return processing

4. Compare with regular sales processing

### Manual Testing

Test with curl (if form handling exists)

curl -X POST "http://localhost/erp19/controllers/invoicesearningsreportController.php?do=show" \

-d "from=2024-01-01&to=2024-01-31"

---

## 🚀 Recommended Improvements

### 1. **Enhanced Pricing Method Support**

// Allow dynamic pricing method selection

$buyPriceType = filter_input(INPUT_POST, 'buyPriceType', FILTER_SANITIZE_STRING);

if (empty($buyPriceType)) {

$buyPriceType = 'last'; // Default

}

// Validate pricing method

$validMethods = ['first', 'last', 'mean', 'last_discount', 'mean_discount'];

if (!in_array($buyPriceType, $validMethods)) {

throw new InvalidArgumentException('Invalid pricing method');

}

### 2. **Performance Optimization**

function calculateEarningsOptimized($startDate, $endDate, $buyPriceType) {

// Single query for multiple bill types

$sql = "

SELECT

'sellbill' as bill_type,

sb.sellbillid as bill_id,

sb.sellbillaftertotalbill as bill_total,

sbd.sellbilldetailquantity as quantity,

p.lastbuyprice,

p.meanbuyprice,

pu.productnumber

FROM sellbill sb

JOIN sellbilldetail sbd ON sb.sellbillid = sbd.sellbillid

JOIN product p ON sbd.sellbilldetailproductid = p.productid

JOIN productunit pu ON sbd.productunitid = pu.productunitid

WHERE sb.conditions = 0

AND sb.sellbilldate BETWEEN ? AND ?

UNION ALL

-- Add other bill types...

";

return R::getAll($sql, [$startDate, $endDate]);

}

### 3. **JSON API Support**

if (isset($_REQUEST['format']) && $_REQUEST['format'] === 'json') {

header('Content-Type: application/json');

$data = array(

'period' => array(

'from' => $startDate,

'to' => $endDate

),

'earnings' => array(

'regular_sales' => $allsellbilldata,

'combined_bills' => $allsellandreturnsellbilldata,

'return_bills' => $allreturnsellbilldata,

'service_bills' => $allsellOpticdata

),

'totals' => array(

'total_profit' => $totalproft,

'vat_rate' => $programsettingsdata->vatValue

)

);

echo json_encode($data);

exit;

}

### 4. **Enhanced Error Handling**

try {

validateDateRange($startDate, $endDate);

$earnings = calculateEarningsReport($startDate, $endDate, $buyPriceType);

if (empty($earnings)) {

throw new Exception('No earnings data found for the specified period');

}

displayEarningsReport($earnings);

} catch (InvalidArgumentException $e) {

error_log("Invalid input: " . $e->getMessage());

header("location:?do=error");

} catch (Exception $e) {

error_log("Earnings calculation error: " . $e->getMessage());

header("location:?do=error");

}

```

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • incomeStatmentForPeriod.md - Comprehensive income statements
• • sellbillController.md - Sales transaction management
• • bills.md - Service transaction management
• • Earnings Analysis Guide - Business intelligence documentation

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur