# Real Estate Units Alerts Rents Controller

**File**: `/controllers/realestateunitsalertsrents.php`
**Purpose**: Monitors and alerts on real estate rental payments due within specified date ranges
**Last Updated**: December 20, 2024
**Total Functions**: 2
**Lines of Code**: ~70

---

## 📋 Overview

The Real Estate Units Alerts Rents Controller serves as a rental payment alert system that tracks and monitors rental payments that are due within specified time periods. It provides:

- Supplier rental payment alerts
- Client rental payment alerts  
- Date-based payment due monitoring
- Property and unit-specific filtering
- Installment payment tracking
- Proactive rental payment management

This controller is essential for property managers to stay on top of rental payment schedules and ensure timely collection from clients and payments to suppliers.

### Primary Functions
- [x] Monitor supplier rental payments due
- [x] Track client rental payments due
- [x] Date range-based payment alerts
- [x] Property and unit filtering
- [x] Installment payment details
- [x] Real-time payment due reporting

### Related Controllers
- [realestatepayments.php](realestatepayments.md) - Pending payments tracking
- [realestateaveragerevenue.php](realestateaveragerevenue.md) - Revenue analysis
- [realestateunits.php](realestateunits.md) - Property management
- [realestateunitsalertsemptys.php](realestateunitsalertsemptys.md) - Empty units monitoring

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **realestateunitspaids** | Real estate payment records | id, realestateid, realestateunitid, supplierid, clientid, addtoday, del |
| **realestateunitpaidinstallments** | Payment installment details | realestateunitpaidid, del |

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

---

## 🔑 Key Functions

### 1. **Default Action** - Rental Alerts Interface
**Location**: Lines 6-9
**Purpose**: Display the main rental payment alerts monitoring interface

**Process Flow**:
1. Display header template
2. Load rental alerts view (`realestateunitsalertsrentsview/show.html`)
3. Display footer template

---

### 2. **getdata** - Generate Rental Payment Alerts Report
**Location**: Lines 10-68
**Purpose**: Generate comprehensive report of rental payments due within specified date range

**Function Signature**:
```php
// 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**:
```php
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 for urgent alerts
    $searchQuery .=' and realestateunitspaids.addtoday >= "' . $date . ' 00-00-00" 
                    and realestateunitspaids.addtoday <= "' . $date . ' 23-59-55" ';
}
```

2. **Property and Unit Filtering**:
```php
if($realestateid != ''){
    $searchQuery .=' and realestateunitspaids.realestateid = ' . $realestateid . ' ';
}

if($realestateunitid != ''){
    $searchQuery .=' and realestateunitspaids.realestateunitid = ' . $realestateunitid . ' ';
}
```

3. **Party-Specific Filtering**:
```php
if($supplierid != ''){
    $ssearchQuery .=' and realestateunitspaids.supplierid = ' . $supplierid . ' ';
}

if($clientid != ''){
    $csearchQuery .=' and realestateunitspaids.clientid = ' . $clientid . ' ';
}
```

4. **Supplier Payment Alerts Query**:
```php
// Supplier payments due within date range
$srealestateunitspaids = R::findAll('realestateunitspaids', 
    "where realestateunitspaids.del < 2 and realestateunitspaids.clientid = 0 
     $searchQuery $ssearchQuery");

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

5. **Client Payment Alerts Query**:
```php
// Client payments due within date range
$crealestateunitspaids = R::findAll('realestateunitspaids', 
    "where realestateunitspaids.del < 2 and realestateunitspaids.supplierid = 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 related installments  
    $realestateunitpaidinstallments = R::findAll('realestateunitpaidinstallments',
        'realestateunitpaidid = ? and del < 2', [$crealestateunitspaid->id]);
    
    $crealestateunitspaid->realestate = $realestate;
    $crealestateunitspaid->realestatesunit = $realestatesunit;
    $crealestateunitspaid->client = $client;
    $crealestateunitspaid->realestateunitpaidinstallments = $realestateunitpaidinstallments;
}
```

6. **Template Assignment and Display**:
```php
$smarty->assign('srealestateunitspaids', $srealestateunitspaids);
$smarty->assign('crealestateunitspaids', $crealestateunitspaids);
$smarty->display("realestateunitsalertsrentsview/getdata.html");
```

---

## 🔄 Workflows

### Workflow 1: Rental Payment Alerts Generation
```
┌─────────────────────────────────────────────────────────────┐
│            START: Generate Rental Payment Alerts          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Alert Parameters                               │
│     - Date range (start_date, end_date)                    │
│     - Property filters (realestateid, unitid)             │
│     - Party filters (supplierid, clientid)                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Build Date-Based Query Filters                         │
│     IF date range provided:                                │
│       └─ Filter by start_date to end_date                 │
│     ELSE:                                                   │
│       └─ Default to current date (urgent alerts)          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Add Property and Party Filters                         │
│     - Apply property filter (if specified)                 │
│     - Apply unit filter (if specified)                     │
│     - Build separate supplier and client filters           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Query Supplier Payment Alerts                          │
│     - Find payments where clientid = 0                     │
│     - Within specified date range                          │
│     - Load related property and supplier details           │
│     - Include installment payment schedules                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Query Client Payment Alerts                            │
│     - Find payments where supplierid = 0                   │
│     - Within specified date range                          │
│     - Load related property, unit, and client details      │
│     - Include installment payment schedules                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Generate Comprehensive Alerts Report                   │
│     - Assign supplier payment alerts                       │
│     - Assign client payment alerts                         │
│     - Display via realestateunitsalertsrentsview template  │
│     - Include installment breakdowns                       │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display rental alerts monitoring interface |
| `do=getdata` | getdata processing | Generate rental payment alerts report |

### Required Parameters for `do=getdata`

**POST Parameters** (all optional):
- `start_date` - Alert start date (YYYY-MM-DD format)
- `end_date` - Alert end date (YYYY-MM-DD format)  
- `supplierid` - Filter by specific supplier
- `clientid` - Filter by specific client
- `realestateid` - Filter by specific property
- `realestateunitid` - Filter by specific unit

---

## 🧮 Calculation Methods

### Date Range Alert Logic
```php
// User-specified date range
if($start_date != '' && $end_date != ''){
    $searchQuery .=' and realestateunitspaids.addtoday >= "' . $start_date . ' 00-00-00" 
                    and realestateunitspaids.addtoday <= "' . $end_date . ' 23-59-55" ';
}
// Default to current date (urgent alerts)
else {
    $searchQuery .=' and realestateunitspaids.addtoday >= "' . $date . ' 00-00-00" 
                    and realestateunitspaids.addtoday <= "' . $date . ' 23-59-55" ';
}
```

### Payment Type Categorization
```
Supplier Payments: clientid = 0
- Property lease payments to landlords
- Construction/maintenance payments
- Property purchase installments

Client Payments: supplierid = 0  
- Rental payments from tenants
- Lease payments from clients
- Security deposit payments
```

### Alert Priority Logic
```
Default Behavior (no date range):
- Shows payments due TODAY
- High priority urgent alerts
- Immediate attention required

Date Range Specified:
- Shows payments due within range
- Planning and scheduling alerts  
- Medium priority monitoring
```

### Installment Integration
```php
// Load all installment details for each payment
$realestateunitpaidinstallments = R::findAll('realestateunitpaidinstallments',
    'realestateunitpaidid = ? and del < 2', [$payment->id]);
```

---

## 🔒 Security & Permissions

### Input Validation
```php
// Secure input filtering
$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');
```

### SQL Injection Prevention
- RedBeanPHP ORM with parameterized queries
- Proper date format validation
- Numeric ID filtering for entity references

### Data Access Control
```php
// Soft delete protection
"where realestateunitspaids.del < 2"

// Payment category validation  
"and realestateunitspaids.clientid = 0"    // Supplier payments
"and realestateunitspaids.supplierid = 0"  // Client payments
```

---

## 📊 Performance Considerations

### Database Optimization
1. **Critical Indexes**:
   - `realestateunitspaids(addtoday, del, clientid, supplierid)`
   - `realestateunitspaids(realestateid, realestateunitid)`
   - `realestateunitpaidinstallments(realestateunitpaidid, del)`

2. **Query Efficiency**:
   - Date range filtering with proper timestamp format
   - Efficient payment type separation
   - Optimized installment loading

### Memory Management
- Iterative processing of payment records
- Efficient loading of related entities
- Structured template variable assignment

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Alerts Showing for Current Date**
**Issue**: Report shows no alerts despite having payments due today
**Cause**: Date comparison or payment creation date issues

**Debug**:
```sql
-- Check payments for current date
SELECT addtoday, COUNT(*) FROM realestateunitspaids 
WHERE DATE(addtoday) = CURDATE() AND del < 2 
GROUP BY DATE(addtoday);

-- Check date format in database
SELECT addtoday FROM realestateunitspaids 
WHERE del < 2 LIMIT 5;
```

### 2. **Date Range Filtering Issues**
**Issue**: Date range filter doesn't return expected results
**Cause**: Time component in date comparison

**Debug**:
```php
// Verify date range construction
echo "Start: " . $start_date . " 00-00-00<br>";
echo "End: " . $end_date . " 23-59-55<br>";

// Check if payments exist in range
$test_query = "SELECT COUNT(*) as count FROM realestateunitspaids 
               WHERE addtoday >= '{$start_date} 00-00-00' 
               AND addtoday <= '{$end_date} 23-59-55' 
               AND del < 2";
```

### 3. **Missing Installment Details**
**Issue**: Payments show without installment breakdown
**Cause**: Orphaned installment records

**Debug**:
```sql
-- Check installment linkage
SELECT rp.id, COUNT(ri.id) as installment_count
FROM realestateunitspaids rp
LEFT JOIN realestateunitpaidinstallments ri ON rp.id = ri.realestateunitpaidid AND ri.del < 2
WHERE rp.del < 2
GROUP BY rp.id;
```

### 4. **Party Filter Not Working**
**Issue**: Supplier or client filters don't affect results
**Cause**: Filter query construction

**Debug**:
```php
// Check filter construction
echo "Supplier search query: " . $ssearchQuery . "<br>";
echo "Client search query: " . $csearchQuery . "<br>";

// Verify filter values
var_dump($_POST['supplierid']);
var_dump($_POST['clientid']);
```

---

## 🧪 Testing Scenarios

### Test Case 1: Current Date Urgent Alerts
```
1. Create payment records with addtoday = current date
2. Include both supplier and client payments
3. Run alerts report without date range
4. Verify only today's payments appear
5. Check both payment types are included
6. Verify installment details loaded
```

### Test Case 2: Date Range Planning Alerts
```
1. Create payment records across multiple dates
2. Set specific date range (e.g., next 7 days)
3. Run alerts report with date range
4. Verify only payments in range appear
5. Check edge cases (start/end date boundaries)
6. Verify time handling (00-00-00 to 23-59-55)
```

### Test Case 3: Property and Unit Filtering
```
1. Create payments for multiple properties/units
2. Apply property filter (specific ID)
3. Verify only related payments appear
4. Apply unit filter within property
5. Verify filtering narrows results correctly
6. Test with invalid property/unit IDs
```

### Test Case 4: Party-Specific Filtering
```
1. Create payments for multiple suppliers/clients
2. Apply supplier filter
3. Verify only supplier payments (clientid = 0) appear
4. Apply client filter  
5. Verify only client payments (supplierid = 0) appear
6. Test with non-existent supplier/client IDs
```

### Test Case 5: Installment Integration
```
1. Create payment records with multiple installments
2. Ensure installments have different del statuses
3. Run alerts report
4. Verify only active installments (del < 2) appear
5. Check installment details properly attached
6. Verify orphaned installments handled correctly
```

### Test Case 6: Combined Filtering
```
1. Create diverse payment data set
2. Apply multiple filters simultaneously:
   - Date range + property filter
   - Supplier filter + unit filter
   - Date range + client filter + property
3. Verify intersection logic works correctly
4. Test edge cases with no matching results
5. Verify performance with complex filters
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [realestatepayments.md](realestatepayments.md) - Pending payments tracking
- [realestateaveragerevenue.md](realestateaveragerevenue.md) - Revenue analysis
- [realestateunits.md](realestateunits.md) - Property management
- [realestateunitsalertsemptys.md](realestateunitsalertsemptys.md) - Empty units monitoring

---

**Key Differences from Other Real Estate Controllers**:

1. **vs realestatepayments.php**:
   - **Date Focus**: Specific date range alerts vs payment status
   - **Purpose**: Proactive alerts vs pending payment tracking
   - **Default Behavior**: Current date vs all pending

2. **vs realestateaveragerevenue.php**:
   - **Payment Status**: All payments vs only paid amounts
   - **Purpose**: Alert generation vs revenue calculation
   - **Data Scope**: Alerts-focused vs comprehensive analysis

3. **vs realestateunitsalertsemptys.php**:
   - **Data Type**: Payment alerts vs availability status
   - **Time Dimension**: Date-based vs current state
   - **Purpose**: Payment management vs occupancy tracking

---

**Alert System Features**:

1. **Urgent Mode**: Default to current date for immediate attention
2. **Planning Mode**: Date range specification for advance planning  
3. **Comprehensive Coverage**: Both supplier and client payments
4. **Detailed Context**: Full property, unit, and party information
5. **Installment Awareness**: Breakdown of payment schedules
6. **Flexible Filtering**: Multiple filter criteria support

---

**Documented By**: AI Assistant
**Review Status**: ✅ Complete
**Next Review**: When major changes occur