Income Statement For Period Controller Documentation

File: /controllers/incomeStatmentForPeriod.php

Purpose: Generates comprehensive income statements with profit/loss calculations for specified periods using multiple pricing methods

Last Updated: December 20, 2024

Total Functions: 25+

Lines of Code: ~991

---

📋 Overview

The Income Statement For Period Controller is a sophisticated financial reporting module that generates detailed profit and loss statements. It provides comprehensive financial analysis with:

• Multi-period income statement generation
• Multiple pricing method support (first, last, mean, with/without discounts and taxes)
• Cost of goods sold calculations with various pricing strategies
• Revenue recognition from sales, services, and returns
• Expense tracking including salaries and operational costs
• Restaurant-specific raw material destruction tracking
• VAT calculations and tax-inclusive reporting
• Automatic time zone adjustment for global operations

Primary Functions

✅ Generate comprehensive income statements
✅ Multiple buy price calculation methods
✅ Revenue analysis from multiple sources
✅ Cost of goods sold calculations
✅ Expense and salary tracking
✅ Profit margin analysis
✅ Restaurant cost tracking
✅ Tax and VAT calculations

Related Controllers

• sellbillController.php - Sales transaction data
• buyBillController.php - Purchase data for COGS
• expensesController.php - Operating expenses
• salaryReportController.php - Payroll costs

---

🗄️ Database Tables

Sales Revenue Tables

Table Name	Purpose	Key Columns
sellbill	Sales transactions	sellbillid, sellbillaftertotalbill, sellbilltotalpayed, sellbilldate, conditions
sellbilldetail	Sales line items	sellbilldetailid, sellbillid, sellbilldetailquantity, sellbilldetailtotalprice, productunitid
returnsellbill	Sales returns	returnsellbillid, returnsellbillaftertotalbill, returnsellbillsysdate
returnsellbilldetail	Return line items	returnsellbilldetailid, returnsellbillid, returnsellbilldetailquantity
sellbillandrutern	Combined sell/return bills	sellbillid, sellbillprice, returnsellbillprice, sellbilldate
sellandruternbilldetail	Combined bill details	sellandruternbilldetailid, sellbillid, selltype

### Cost of Goods Tables

Table Name	Purpose	Key Columns
product	Product master data	productid, lastbuyprice, meanbuyprice, lastbuyprice_withDiscount, meanbuyprice_withDiscount
productunit	Product units	productunitid, productnumber
buybilldetail	Purchase details for cost	buybilldetailid, buyprice, buydiscount, buydiscounttype
restaurantrawdestruction	Raw material waste	id, productid, quantity, productBuyPrice, lastbuyprice, meanbuyprice

### Expense Tables

Table Name	Purpose	Key Columns
expenses	Operating expenses	expensesid, expensesValue, expensesdate, expensestypeid
expensestype	Expense categories	expensestypeid, expensestypename
salaryreport	Payroll costs	salaryreportid, salaryreportnet, salaryreportdate
employeepersonnel	Employee transactions	employeepersonnelid, employeepersonneltype, employeepersonnelvalue

### Service Revenue Tables

Table Name	Purpose	Key Columns
bills	Service bills	billid, finalnetbillvalue, billdate
billsproducts	Service line items	billsproductsid, productno, productBuyPrice
billsreturn	Service returns	billsreturnid, finalnetbillvalue

### Configuration Tables

Table Name	Purpose	Key Columns
programsettings	System settings	programsettingsid, reportsPlusHours, vatValue
lastbillidspricesupdated	Price tracking	id, buybill, sell, retsell, buyquantity

---

🔑 Key Functions

1. show() / Default Action - Generate Income Statement

Location: Line 284

Purpose: Main function that orchestrates income statement generation

Process Flow:

1. Date Range Processing:

   $startDate = $_REQUEST['from'];
   $endDate = $_REQUEST['to'];
   $buyPriceType = $_POST["buyPriceType"];
   
   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. **Revenue Calculation**:
   ```php
   $totalSellPrice = getTotalSellbillByDate($startDate, $endDate) + getTotalAditionalSellbillByDate($startDate, $endDate);
   $totalReturnSellPrice = getTotalReturnSellbillByDate($startDate, $endDate) + getTotalAditionalReturnSellbillByDate($startDate, $endDate);
   $totalSellCost = $totalSellPrice - $totalReturnSellPrice;
   ```

4. **Cost of Goods Sold**:
   ```php
   $bills_sellcost = get_total_sellcost($startDate, $endDate);
   $total_sellcost = 0;
   foreach ($bills_sellcost as $value) {
       switch ($buyPriceType) {
           case "first": $pro_price = (float) $value->buyprice; break;
           case "last": $pro_price = (float) $value->lastbuyprice; break;
           case "mean": $pro_price = (float) $value->meanbuyprice; break;
           case "last_discount": $pro_price = (float) $value->lastbuyprice_withDiscount; break;
           case "mean_discount": $pro_price = (float) $value->meanbuyprice_withDiscount; break;
       }
       $total_sellcost += ($value->productno * $pro_price);
   }
   ```

5. **Expense and Salary Tracking**:
   ```php
   $totalExpenses = getTotalExpensesByDate($startDate, $endDate);
   $totalsalary = gettotalsalary($startDate, $endDate);
   ```

6. **Final Profit Calculation**:
   ```php
   $total_profit = $totalSellCost - $totalBuyCost - $restaurantRawDestruction;
   $profitFinal = $total_profit - ($totalExpenses + $totalsalary);
   ```

---

### 2. **getTotalSellbillByDate()** - Sales Revenue Calculation
**Location**: Line 500  
**Purpose**: Calculate total sales revenue for specified period

**Implementation**:

php

function getTotalSellbillByDate($startDate, $endDate) {

global $mySellbillEx;

$totalSellbill = $mySellbillEx->queryWithDateAndConditionsExt($startDate, $endDate);

return $totalSellbill;

}

---

### 3. **getTotalQuantityBuyPriceByDate()** - COGS Calculation
**Location**: Line 561  
**Purpose**: Calculate cost of goods sold using selected pricing method

**Process Flow**:
1. **Query Sales Data**:
   ```php
   $sellbilldetailData = $mySellbilldetailEx->queryWithDateAndConditionsExt($startDate, $endDate);
   ```

2. **Price Method Selection**:
   ```php
   switch ($buyPriceType) {
       case "first": $buyPrice = (float) $sellbilldetail->buyprice; break;
       case "last": $buyPrice = (float) $sellbilldetail->lastbuyprice; break;
       case "mean": $buyPrice = (float) $sellbilldetail->meanbuyprice; break;
       case "last_discount": $buyPrice = (float) $sellbilldetail->lastbuyprice_withDiscount; break;
       case "mean_discount": $buyPrice = (float) $sellbilldetail->meanbuyprice_withDiscount; break;
       case "tax": $buyPrice = (float) $sellbilldetail->lastbuyprice_withTax; break;
       case "mean_tax": $buyPrice = (float) $sellbilldetail->meanbuyprice_withTax; break;
   }
   ```

3. **Unit Conversion and Calculation**:
   ```php
   $quantity = $sellbilldetail->sellbilldetailquantity;
   $productnumber = $myProductunitEx->getProductNumber($productunitId);
   if ($productnumber == 0) $productnumber = 1;
   
   $productBuyPrice = $buyPrice * $productnumber;
   $totalQuantityBuyPrice += ($quantity * $productBuyPrice);
   ```

---

### 4. **gettotalsalary()** - Payroll Cost Calculation
**Location**: Line 870  
**Purpose**: Calculate total salary and employee-related costs

**Process Flow**:
1. **Base Salary Calculation**:
   ```php
   $allsalary = $SalaryreportEX->queryAllbydata($startDate, $endDate);
   $sumsalary = 0;
   foreach ($allsalary as $myallsalary) {
       $sumsalary += $myallsalary->salaryreportnet;
   }
   ```

2. **Employee Personnel Transactions**:
   ```php
   $allpersonaldata = $EmployeepersonnelEX->queryAllbydateforreport($startDate, $endDate);
   foreach ($allpersonaldata as $myallpersonaldata) {
       if ($myallpersonaldata->employeepersonneltype == 4) {
           $Borrow += $myallpersonaldata->employeepersonnelvalue;
       } elseif ($myallpersonaldata->employeepersonneltype == 3) {
           $Withdrawal += $myallpersonaldata->employeepersonnelvalue;
       } elseif ($myallpersonaldata->employeepersonneltype == 6) {
           $Repayment += $myallpersonaldata->employeepersonnelvalue;
       }
   }
   ```

3. **Final Calculation**:
   ```php
   $allfinal = ($sumsalary + $Withdrawal + $Borrow) - $Repayment;
   return $allfinal;
   ```

---

### 5. **getRestaurantRawDestruction()** - Material Waste Tracking
**Location**: Line 960  
**Purpose**: Calculate cost of destroyed/wasted raw materials

**Implementation**:

php

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

global $restaurantRawDestructionEX;

$restaurantRawDestruction = $restaurantRawDestructionEX->queryBySysdateBetween($startDate, $endDate);

$rawDestructionCost = 0;

foreach ($restaurantRawDestruction as $value) {

switch ($buyPriceType) {

case 'first': $rawDestructionCost += $value->productBuyPrice * $value->quantity; break;

case 'last': $rawDestructionCost += $value->lastbuyprice * $value->quantity; break;

case "last_discount": $rawDestructionCost += $value->lastbuyprice_withDiscount * $value->quantity; break;

case "mean_discount": $rawDestructionCost += $value->meanbuyprice_withDiscount * $value->quantity; break;

default: $rawDestructionCost += $value->meanbuyprice * $value->quantity; break;

}

return $rawDestructionCost;

}

---

## 🔄 Workflows

### Workflow 1: Complete Income Statement Generation

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

│ START: Income Statement Request │

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

│

▼

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

│ 1. Date Range Processing │

│ - Parse from/to dates │

│ - Apply timezone adjustments │

│ - Set default to current date if empty │

│ - Add time bounds (00:00:00 to 23:59:59) │

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

│

▼

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

│ 2. Revenue Calculation │

│ A. Sales Revenue: │

│ ├─ Regular sales (sellbill) │

│ ├─ Combined sell/return transactions │

│ └─ Service bills (optical/restaurant) │

│ B. Sales Returns: │

│ ├─ Return bills (returnsellbill) │

│ ├─ Return portions of combined bills │

│ └─ Service return bills │

│ C. Net Sales = Sales Revenue - Sales Returns │

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

│

▼

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

│ 3. Cost of Goods Sold Calculation │

│ A. Choose Pricing Method: │

│ ├─ First purchase price │

│ ├─ Last purchase price │

│ ├─ Mean (average) purchase price │

│ ├─ Prices with discount applied │

│ └─ Prices with tax included │

│ B. Calculate for Each Item Sold: │

│ ├─ Get quantity sold │

│ ├─ Get product unit conversion │

│ ├─ Apply selected pricing method │

│ └─ Sum: quantity × unit conversion × price │

│ C. Include Service Item Costs │

│ D. Subtract Return Item Costs │

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

│

▼

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

│ 4. Operating Expenses │

│ A. Direct Expenses: │

│ ├─ Query expenses table by date range │

│ ├─ Filter by expense type │

│ └─ Sum all expense values │

│ B. Payroll Costs: │

│ ├─ Base salaries from salary reports │

│ ├─ Employee withdrawals │

│ ├─ Employee loans/advances │

│ └─ Loan repayments (subtract) │

│ C. Restaurant-Specific: │

│ └─ Raw material destruction/waste costs │

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

│

▼

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

│ 5. Profit Calculations │

│ A. Gross Profit: │

│ └─ Net Sales - Cost of Goods Sold - Material Waste │

│ B. Net Profit: │

│ └─ Gross Profit - Operating Expenses - Payroll │

│ C. Assign All Values to Template │

│ D. Generate Report Display │

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

---

## 🌐 URL Routes & Actions

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

### Required Parameters
**Generate Report** (`do=show`):
- `from` - Start date (YYYY-MM-DD)
- `to` - End date (YYYY-MM-DD)
- `buyPriceType` - Pricing method (first/last/mean/last_discount/mean_discount/tax/mean_tax)

### URL Examples

Basic report for current month

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

With specific pricing method

incomeStatmentForPeriod.php?from=2024-01-01&to=2024-01-31&buyPriceType=mean_discount

---

## 🧮 Calculation Methods

### Buy Price Method Selection

php

switch ($buyPriceType) {

case "first":

$message .= " و الحساب بـ سعر شراء";

$buyPrice = (float) $sellbilldetail->buyprice;

break;

case "last":

$message .= " و الحساب بـ اخر سعر شراء";

$buyPrice = (float) $sellbilldetail->lastbuyprice;

break;

case "mean":

$message .= " و الحساب بـ متوسط سعر الشراء";

$buyPrice = (float) $sellbilldetail->meanbuyprice;

break;

case "last_discount":

$message .= " و الحساب بـ اخر سعر شراء بعد الخصم";

$buyPrice = (float) $sellbilldetail->lastbuyprice_withDiscount;

break;

case "mean_discount":

$message .= " و الحساب بـ متوسط سعر الشراء بعد الخصم";

$buyPrice = (float) $sellbilldetail->meanbuyprice_withDiscount;

break;

case "tax":

$message .= " و الحساب بـ اخر سعر شراء شامل القيمة المضافة";

$buyPrice = (float) $sellbilldetail->lastbuyprice_withTax;

break;

case "mean_tax":

$message .= " و الحساب بـ متوسط سعر الشراء شامل القيمة المضافة";

$buyPrice = (float) $sellbilldetail->meanbuyprice_withTax;

break;

}

### Unit Conversion Handling

php

$quantity = $sellbilldetail->sellbilldetailquantity;

$productunitId = $sellbilldetail->productunitid;

$productnumber = $myProductunitEx->getProductNumber($productunitId);

if ($productnumber == 0) {

$productnumber = 1; // Prevent division by zero

}

$productBuyPrice = $buyPrice * $productnumber;

$totalQuantityBuyPrice += ($quantity * $productBuyPrice);

### Salary Cost Calculation

php

// Base salaries

$sumsalary = 0;

foreach ($allsalary as $myallsalary) {

$sumsalary += $myallsalary->salaryreportnet;

}

// Employee transactions

$Withdrawal = 0; $Borrow = 0; $Repayment = 0;

foreach ($allpersonaldata as $myallpersonaldata) {

switch ($myallpersonaldata->employeepersonneltype) {

case 4: $Borrow += $myallpersonaldata->employeepersonnelvalue; break;

case 3: $Withdrawal += $myallpersonaldata->employeepersonnelvalue; break;

case 6: $Repayment += $myallpersonaldata->employeepersonnelvalue; break;

}

$totalSalaryCost = ($sumsalary + $Withdrawal + $Borrow) - $Repayment;

---

## 🔒 Security & Permissions

### Authentication Check

php

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

### Input Validation
- Date format validation for start/end dates
- Pricing method validation against allowed values
- 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_date ON sellbilldetail(sellbilldate);
   CREATE INDEX idx_returnsellbill_date ON returnsellbill(returnsellbillsysdate, conditions);
   CREATE INDEX idx_expenses_date ON expenses(expensesdate, conditions);
   CREATE INDEX idx_salaryreport_date ON salaryreport(salaryreportdate);
   ```

2. **Query Optimization**:
   - Use of proper date range queries
   - Efficient JOIN operations in extended DAOs
   - Minimal data retrieval with specific column selection

3. **Memory Management**:
   - Large datasets may consume significant memory
   - Consider pagination for very long date ranges
   - Efficient array processing for calculations

### Known Performance Bottlenecks

php

// This section can be slow for large datasets

foreach ($sellbilldetailData as $sellbilldetail) {

$productnumber = $myProductunitEx->getProductNumber($productunitId);

// Consider caching product unit numbers

}

**Solution**:

php

// Cache product unit numbers

static $unitCache = array();

if (!isset($unitCache[$productunitId])) {

$unitCache[$productunitId] = $myProductunitEx->getProductNumber($productunitId);

}

$productnumber = $unitCache[$productunitId];

---

## 🐛 Common Issues & Troubleshooting

### 1. **Incorrect Profit Calculations**
**Issue**: Profit figures don't match expected values  
**Cause**: Wrong pricing method or missing data

**Debug**:

php

// Add debugging output

echo "Sales Total: " . $totalSellPrice . "
";

echo "Returns Total: " . $totalReturnSellPrice . "
";

echo "COGS Total: " . $totalQuantityBuyPrice . "
";

echo "Expenses Total: " . $totalExpenses . "
";

echo "Salary Total: " . $totalsalary . "
";

### 2. **Date Range Issues**
**Issue**: No data returned for valid date range  
**Cause**: Timezone conversion or date format problems

**Debug**:

php

echo "Start Date: " . $startDate . "
";

echo "End Date: " . $endDate . "
";

echo "Reports Plus Hours: " . $Programsetting->reportsPlusHours . "
";

### 3. **Missing Revenue Data**
**Issue**: Sales data not appearing in report  
**Cause**: Condition filtering or date field mismatches

**Debug**:

sql

-- Check data availability

SELECT COUNT(*) FROM sellbill

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

AND conditions = 0;

### 4. **Memory Exhaustion**
**Issue**: Script timeout or memory errors for large date ranges  
**Cause**: Processing too much data at once

**Solution**:

php

// Add memory monitoring

echo "Memory usage: " . memory_get_usage(true) / 1024 / 1024 . " MB
";

ini_set('memory_limit', '512M');

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Income Statement

1. Set date range to known transaction period

2. Select "mean" pricing method

3. Verify all revenue sections populate

4. Check COGS calculations against manual calculation

5. Confirm final profit matches expected result

### Test Case 2: Different Pricing Methods

1. Run same date range with different pricing methods

2. Verify COGS changes appropriately

3. Compare first vs last vs mean price impacts

4. Test discount and tax inclusive methods

### Test Case 3: Edge Cases

1. Test empty date range (no transactions)

2. Test single day with mixed transaction types

3. Test period spanning year boundary

4. Verify timezone adjustment accuracy

### Debug Mode Enable

php

// Add at top of controller for debugging

error_reporting(E_ALL);

ini_set('display_errors', 1);

// Add calculation debugging

function debug_calculation($label, $value) {

echo "$label: " . number_format($value, 2) . "
";

}

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• sellbillController.md - Sales transaction management
• buyBillController.md - Purchase transaction management
• expensesController.md - Expense management
• Database Schema Documentation - Table relationships and indexes

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

IncomeStatmentForPeriod Documentation

Income Statement For Period Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Sales Revenue Tables

🔑 Key Functions

1. show() / Default Action - Generate Income Statement

Basic report for current month

With specific pricing method

📚 Related Documentation