Real Estate Average Revenue Controller

File: /controllers/realestateaveragerevenue.php

Purpose: Calculates average monthly revenue and expenses for real estate properties

Last Updated: December 20, 2024

Total Functions: 2

Lines of Code: ~118

---

📋 Overview

The Real Estate Average Revenue Controller provides comprehensive revenue and expense analysis for real estate properties over specified time periods. It calculates:

• Supplier payments for real estate properties
• Client rental payments and installments
• Property-related expenses
• Raw material destruction/depreciation costs
• Monthly average calculations
• Net profit/loss analysis

Primary Functions

✅ Calculate supplier payments per property
✅ Track client rental payments
✅ Monitor property-related expenses
✅ Handle installment payment tracking
✅ Calculate monthly averages
✅ Support date range filtering
✅ Property-specific reporting
✅ Multi-property comparison

Related Controllers

• realestatepayments.php - Payment tracking
• realestateunits.php - Unit management
• expenses.php - Expense management
• supplier.php - Supplier management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
realestateunitspaids	Real estate unit payments	id, realestateid, realestateunitid, supplierid, clientid, payed, addtoday, del
realestateunitpaidinstallments	Payment installments	realestateunitpaidid, payed, del
expenses	Property expenses	realestateid, realestateunitid, expensesdate, conditions
restaurantrawdestruction	Material depreciation/destruction	productid, realestateid, realestateunitid, quantity, sysdate

### Reference Tables

Table Name	Purpose	Key Columns
realestates	Property master data	id, realestatename
realestatesunits	Property units	id, realestateid, unitname, unitarea
supplier	Supplier information	supplierid, suppliername, supplierphone
client	Client information	clientid, clientname, clientphone
product	Product catalog	productId, productBuyPrice, lastbuyprice, meanbuyprice
programsettings	System settings	programsettingsid, Inventoryevaluation

---

🔑 Key Functions

1. Default Action - Main Report Display

Location: Lines 6-9

Purpose: Display the main report interface

Process Flow:

1. Display header template

2. Load main report view (realestateaveragerevenueview/show.html)

3. Display footer template

---

2. getdata - Revenue Analysis Engine

Location: Lines 10-116

Purpose: Generate comprehensive revenue and expense analysis

Function Signature:

// Triggered when: do=getdata (POST)
$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');
$supplierid = filter_input(INPUT_POST, 'supplierid');
$clientid = filter_input(INPUT_POST, 'clientid');
$realestateid = filter_input(INPUT_POST, 'realestateid');
$realestateunitid = filter_input(INPUT_POST, 'realestateunitid');

Process Flow:

1. Date Range Processing:

if($start_date != '' && $end_date != ''){
    $searchQuery .=' and realestateunitspaids.addtoday >= "' . $start_date . ' 00-00-00" 
                    and realestateunitspaids.addtoday <= "' . $end_date . ' 23-59-55" ';
} else {
    // Default to current date
    $searchQuery .=' and realestateunitspaids.addtoday >= "' . $date . ' 00-00-00" 
                    and realestateunitspaids.addtoday <= "' . $date . ' 23-59-55" ';
}

2. Supplier Payments Analysis:

$srealestateunitspaids = R::findAll('realestateunitspaids', 
    "where realestateunitspaids.del < 2 and realestateunitspaids.clientid = 0 
     and realestateunitspaids.payed > 0 $searchQuery $ssearchQuery");

foreach($srealestateunitspaids as $srealestateunitspaid){
    $realestate = R::load('realestates', $srealestateunitspaid->realestateid);
    $supplier = R::getRow('select * from supplier WHERE supplierid = ?', 
                         [$srealestateunitspaid->supplierid]);
    $realestateunitpaidinstallments = R::findAll('realestateunitpaidinstallments',
        'realestateunitpaidid = ? and del < 2 and payed > 0',
        [$srealestateunitspaid->id]);
    
    $srealestateunitspaid->realestate = $realestate;
    $srealestateunitspaid->supplier = $supplier;
    $srealestateunitspaid->realestateunitpaidinstallments = $realestateunitpaidinstallments;
}

3. Client Rental Payments Analysis:

$crealestateunitspaids = R::findAll('realestateunitspaids', 
    "where realestateunitspaids.del < 2 and realestateunitspaids.supplierid = 0 
     and realestateunitspaids.payed > 0 $searchQuery $csearchQuery");

foreach($crealestateunitspaids as $crealestateunitspaid){
    $realestate = R::load('realestates', $crealestateunitspaid->realestateid);
    $realestatesunit = R::load('realestatesunits', $crealestateunitspaid->realestateunitid);
    $client = R::getRow('select * from client WHERE clientid = ?', 
                       [$crealestateunitspaid->clientid]);
    // Load installment details...
}

4. Expenses Analysis:

$expenses = R::getAll("SELECT * FROM expenses 
    left join realestates on expenses.realestateid = realestates.id
    left join realestatesunits on expenses.realestateunitid = realestatesunits.id
    where expenses.conditions = 0 $esearchQuery");

5. Inventory Evaluation Processing:

$programsettings = R::getRow('select * from programsettings WHERE programsettingsid = 1');
switch ($programsettings['Inventoryevaluation']) {
    case "first":
        $buyprice = 'restaurantrawdestruction.productBuyPrice';
        break;
    case "last":
        $buyprice = 'restaurantrawdestruction.lastbuyprice';
        break;
    case "mean":
    case "generalPrice":
        $buyprice = 'restaurantrawdestruction.meanbuyprice';
        break;
    case "last_discount":
        $buyprice = 'restaurantrawdestruction.lastbuyprice_withDiscount';
        break;
    case "mean_discount":
        $buyprice = 'restaurantrawdestruction.meanbuyprice_withDiscount';
        break;
}

6. Monthly Average Calculation:

$months = (int)abs((strtotime($end_date) - strtotime($start_date))/(60*60*24*30));
$smarty->assign('months', $months);

---

🔄 Workflows

Workflow 1: Revenue Analysis Generation

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

START: Select Date Range & Filters

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

▼

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

1Process Input Parameters

- Parse date range (start_date, end_date)

- Extract property filters (realestateid, unitid)

- Extract party filters (supplierid, clientid)

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

▼

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

2Build Dynamic SQL Queries

- Date range filters for all tables

- Property-specific filters

- Party-specific filters

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

▼

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

3Query Supplier Payments

- Get payments where clientid = 0

- Load related property and supplier data

- Include installment details

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

▼

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

4Query Client Rental Payments

- Get payments where supplierid = 0

- Load related property, unit, and client data

- Include installment details

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

▼

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

5Query Property Expenses

- Get expenses with property joins

- Filter by date range and property

- Include expense details

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

▼

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

6Process Material Depreciation

- Apply inventory evaluation method

- Calculate depreciation costs

- Link to product and property data

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

▼

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

7Calculate Monthly Averages

- Determine month count from date range

- Assign all data to template variables

- Generate comprehensive report

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

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default action	Display main report interface
`do=getdata`	getdata processing	Generate revenue analysis report

### Required Parameters for do=getdata

POST Parameters:

• start_date - Start date (YYYY-MM-DD format)
• end_date - End date (YYYY-MM-DD format)
• supplierid - Optional: Filter by specific supplier
• clientid - Optional: Filter by specific client
• realestateid - Optional: Filter by specific property
• realestateunitid - Optional: Filter by specific unit

---

🧮 Calculation Methods

Monthly Average Calculation

// Calculate number of months in date range
$months = (int)abs((strtotime($end_date) - strtotime($start_date))/(60*60*24*30));

Inventory Valuation Methods

switch ($programsettings['Inventoryevaluation']) {
    case "first": // FIFO
        $buyprice = 'restaurantrawdestruction.productBuyPrice';
        break;
    case "last": // LIFO
        $buyprice = 'restaurantrawdestruction.lastbuyprice';
        break;
    case "mean": // Average Cost
    case "generalPrice":
        $buyprice = 'restaurantrawdestruction.meanbuyprice';
        break;
    case "last_discount": // LIFO with discounts
        $buyprice = 'restaurantrawdestruction.lastbuyprice_withDiscount';
        break;
    case "mean_discount": // Average with discounts
        $buyprice = 'restaurantrawdestruction.meanbuyprice_withDiscount';
        break;
}

Revenue vs Expense Calculation

Total Revenue = Supplier Payments + Client Rental Payments + Installments
Total Expenses = Direct Expenses + Material Depreciation
Net Profit = Total Revenue - Total Expenses
Average Monthly Profit = Net Profit / Number of Months

---

🔒 Security & Permissions

Input Validation

// All inputs filtered through PHP filter_input
$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');
$supplierid = filter_input(INPUT_POST, 'supplierid');
// ... etc

SQL Injection Prevention

• Uses RedBeanPHP ORM for database queries
• Parameterized queries for supplier/client lookups
• Proper date format validation

Date Range Security

// Prevents malicious date injection
if($start_date != '' && $end_date != ''){
    // Only proceeds with valid date range
    $searchQuery .=' and realestateunitspaids.addtoday >= "' . $start_date . ' 00-00-00"';
}

---

📊 Performance Considerations

Database Optimization

1. Indexes Required:

- realestateunitspaids(addtoday, realestateid)

- realestateunitspaids(supplierid, clientid)

- expenses(expensesdate, realestateid)

- restaurantrawdestruction(sysdate, realestateid)

2. Query Optimization:

- Date filtering with proper timestamp format

- Efficient LEFT JOIN operations

- Filtered del < 2 conditions for soft deletes

Memory Management

• Iterative processing of payment records
• Efficient data structure for installments
• Template variable cleanup

---

🐛 Common Issues & Troubleshooting

1. Missing Monthly Averages

Issue: Monthly calculation shows 0 or incorrect values

Cause: Date range calculation error

Debug:

$months = (int)abs((strtotime($end_date) - strtotime($start_date))/(60*60*24*30));
echo "Months calculated: " . $months;

2. Incorrect Revenue Totals

Issue: Revenue doesn't match expected amounts

Cause: Mixed supplier/client payment filtering

Debug:

-- Check supplier payments (clientid = 0)
SELECT COUNT(*) FROM realestateunitspaids 
WHERE del < 2 AND clientid = 0 AND payed > 0;

-- Check client payments (supplierid = 0)  
SELECT COUNT(*) FROM realestateunitspaids 
WHERE del < 2 AND supplierid = 0 AND payed > 0;

3. Inventory Valuation Issues

Issue: Material costs showing as NULL

Cause: Missing inventory evaluation setting

Fix:

-- Check program settings
SELECT Inventoryevaluation FROM programsettings WHERE programsettingsid = 1;

-- Update if needed
UPDATE programsettings SET Inventoryevaluation = 'mean' WHERE programsettingsid = 1;

---

🧪 Testing Scenarios

Test Case 1: Basic Revenue Calculation

1. Create test property with units
2. Add supplier payment records
3. Add client rental payments
4. Run revenue report
5. Verify totals match database sums

Test Case 2: Monthly Average Accuracy

1. Set specific date range (e.g., 3 months)
2. Add known revenue amounts
3. Verify monthly average = total / 3
4. Test with different date ranges

Test Case 3: Filtering Functionality

1. Add payments for multiple properties
2. Filter by specific property ID
3. Verify only related payments appear
4. Test supplier and client filters

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• realestatepayments.md - Payment tracking
• realestateunits.md - Unit management
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Realestateaveragerevenue Documentation

Real Estate Average Revenue Controller

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Main Report Display

2. getdata - Revenue Analysis Engine

🔄 Workflows

Workflow 1: Revenue Analysis Generation

🌐 URL Routes & Actions

🧮 Calculation Methods

Monthly Average Calculation

Inventory Valuation Methods

Revenue vs Expense Calculation

🔒 Security & Permissions

Input Validation

SQL Injection Prevention

Date Range Security

📊 Performance Considerations

Database Optimization

Memory Management

🐛 Common Issues & Troubleshooting

1. Missing Monthly Averages

2. Incorrect Revenue Totals

3. Inventory Valuation Issues

🧪 Testing Scenarios

Test Case 1: Basic Revenue Calculation

Test Case 2: Monthly Average Accuracy

Test Case 3: Filtering Functionality

📚 Related Documentation